Examples with SqlPattern mondrian.test.SqlPattern used on opensource projects

Example 86 with SqlPattern

use of mondrian.test.SqlPattern in project mondrian by pentaho.

the class NonEmptyTest method testMultiLevelMemberConstraintWithMixedNullNonNullChild.

/**
 * Check that multi-level member list generates compact form of SQL where
 * clause:
 * (1) Use IN list if possible(not possible if there are null values because
 *     NULLs in IN lists do not match)
 * (2) Group members sharing the same parent
 * (3) Only need to compare up to the first unique parent level.
 * (4) Can handle predicates correctly if the member list contains both NULL
 * and non NULL child levels.
 */
public void testMultiLevelMemberConstraintWithMixedNullNonNullChild() {
    if (!isDefaultNullMemberRepresentation()) {
        return;
    }
    if (!MondrianProperties.instance().FilterChildlessSnowflakeMembers.get()) {
        return;
    }
    String dimension = "<Dimension name=\"Warehouse2\">\n" + "  <Hierarchy hasAll=\"true\" primaryKey=\"warehouse_id\">\n" + "    <Table name=\"warehouse\"/>\n" + "    <Level name=\"address3\" column=\"wa_address3\" uniqueMembers=\"true\"/>\n" + "    <Level name=\"address2\" column=\"wa_address2\" uniqueMembers=\"false\"/>\n" + "    <Level name=\"fax\" column=\"warehouse_fax\" uniqueMembers=\"false\"/>\n" + "  </Hierarchy>\n" + "</Dimension>\n";
    String cube = "<Cube name=\"Warehouse2\">\n" + "  <Table name=\"inventory_fact_1997\"/>\n" + "  <DimensionUsage name=\"Product\" source=\"Product\" foreignKey=\"product_id\"/>\n" + "  <DimensionUsage name=\"Warehouse2\" source=\"Warehouse2\" foreignKey=\"warehouse_id\"/>\n" + "  <Measure name=\"Warehouse Cost\" column=\"warehouse_cost\" aggregator=\"sum\"/>\n" + "  <Measure name=\"Warehouse Sales\" column=\"warehouse_sales\" aggregator=\"sum\"/>\n" + "</Cube>";
    String query = "with\n" + "set [Filtered Warehouse Set] as " + "{[Warehouse2].[#null].[#null].[#null]," + " [Warehouse2].[#null].[#null].[971-555-6213]} " + "set [NECJ] as NonEmptyCrossJoin([Filtered Warehouse Set], {[Product].[Product Family].Food}) " + "select [NECJ] on columns from [Warehouse2]";
    String necjSqlDerby = "select \"warehouse\".\"wa_address3\", \"warehouse\".\"wa_address2\", \"warehouse\".\"warehouse_fax\", \"product_class\".\"product_family\" " + "from \"warehouse\" as \"warehouse\", \"inventory_fact_1997\" as \"inventory_fact_1997\", \"product\" as \"product\", \"product_class\" as \"product_class\" " + "where \"inventory_fact_1997\".\"warehouse_id\" = \"warehouse\".\"warehouse_id\" and " + "\"product\".\"product_class_id\" = \"product_class\".\"product_class_id\" and \"inventory_fact_1997\".\"product_id\" = \"product\".\"product_id\" " + "and ((\"warehouse\".\"warehouse_fax\" = '971-555-6213' or \"warehouse\".\"warehouse_fax\" is null) and " + "\"warehouse\".\"wa_address2\" is null and \"warehouse\".\"wa_address3\" is null) and " + "(\"product_class\".\"product_family\" = 'Food') " + "group by \"warehouse\".\"wa_address3\", \"warehouse\".\"wa_address2\", \"warehouse\".\"warehouse_fax\", \"product_class\".\"product_family\" " + "order by CASE WHEN \"warehouse\".\"wa_address3\" IS NULL THEN 1 ELSE 0 END, \"warehouse\".\"wa_address3\" ASC, CASE WHEN \"warehouse\".\"wa_address2\" IS NULL THEN 1 ELSE 0 END, \"warehouse\".\"wa_address2\" ASC, CASE WHEN \"warehouse\".\"warehouse_fax\" IS NULL THEN 1 ELSE 0 END, \"warehouse\".\"warehouse_fax\" ASC, CASE WHEN \"product_class\".\"product_family\" IS NULL THEN 1 ELSE 0 END, \"product_class\".\"product_family\" ASC";
    String necjSqlMySql = "select `warehouse`.`wa_address3` as `c0`, `warehouse`.`wa_address2` as `c1`, `warehouse`.`warehouse_fax` as `c2`, " + "`product_class`.`product_family` as `c3` from `warehouse` as `warehouse`, `inventory_fact_1997` as `inventory_fact_1997`, " + "`product` as `product`, `product_class` as `product_class` " + "where `inventory_fact_1997`.`warehouse_id` = `warehouse`.`warehouse_id` and `product`.`product_class_id` = `product_class`.`product_class_id` and " + "`inventory_fact_1997`.`product_id` = `product`.`product_id` and " + "((`warehouse`.`warehouse_fax` = '971-555-6213' or `warehouse`.`warehouse_fax` is null) and " + "`warehouse`.`wa_address2` is null and `warehouse`.`wa_address3` is null) and " + "(`product_class`.`product_family` = 'Food') " + "group by `warehouse`.`wa_address3`, `warehouse`.`wa_address2`, `warehouse`.`warehouse_fax`, " + "`product_class`.`product_family` " + (TestContext.instance().getDialect().requiresOrderByAlias() ? "order by ISNULL(`c0`) ASC, `c0` ASC, ISNULL(`c1`) ASC, " + "`c1` ASC, ISNULL(`c2`) ASC, `c2` ASC, " + "ISNULL(`c3`) ASC, `c3` ASC" : "order by ISNULL(`warehouse`.`wa_address3`) ASC, `warehouse`.`wa_address3` ASC, ISNULL(`warehouse`.`wa_address2`) ASC, " + "`warehouse`.`wa_address2` ASC, ISNULL(`warehouse`.`warehouse_fax`) ASC, `warehouse`.`warehouse_fax` ASC, " + "ISNULL(`product_class`.`product_family`) ASC, `product_class`.`product_family` ASC");
    TestContext testContext = TestContext.instance().create(dimension, cube, null, null, null, null);
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.DERBY, necjSqlDerby, necjSqlDerby), new SqlPattern(Dialect.DatabaseProduct.MYSQL, necjSqlMySql, necjSqlMySql) };
    assertQuerySql(testContext, query, patterns);
}

Example 87 with SqlPattern

use of mondrian.test.SqlPattern in project mondrian by pentaho.

the class NonEmptyTest method testNonEmptyAggregateSlicerIsNative.

/**
 * Native CrossJoin with a ranged slicer.
 */
public void testNonEmptyAggregateSlicerIsNative() {
    final String mdx = "select NON EMPTY\n" + " Crossjoin([Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Portsmouth]\n" + " , [Customers].[USA].[WA].[Puyallup].Children) ON COLUMNS\n" + "from [Sales]\n" + "where ([Time].[1997].[Q1].[2] : [Time].[1997].[Q2].[5])";
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    String mysqlNativeCrossJoinQuery = "select\n" + "    `time_by_day`.`the_year` as `c0`,\n" + "    `time_by_day`.`quarter` as `c1`,\n" + "    `time_by_day`.`month_of_year` as `c2`,\n" + "    `product_class`.`product_family` as `c3`,\n" + "    `product_class`.`product_department` as `c4`,\n" + "    `product_class`.`product_category` as `c5`,\n" + "    `product_class`.`product_subcategory` as `c6`,\n" + "    `product`.`brand_name` as `c7`,\n" + "    `customer`.`customer_id` as `c8`,\n" + "    sum(`sales_fact_1997`.`unit_sales`) as `m0`\n" + "from\n" + "    `time_by_day` as `time_by_day`,\n" + "    `sales_fact_1997` as `sales_fact_1997`,\n" + "    `product_class` as `product_class`,\n" + "    `product` as `product`,\n" + "    `customer` as `customer`\n" + "where\n" + "    `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`\n" + "and\n" + "    `time_by_day`.`the_year` = 1997\n" + "and\n" + "    `time_by_day`.`quarter` in ('Q1', 'Q2')\n" + "and\n" + "    `time_by_day`.`month_of_year` in (2, 3, 4, 5)\n" + "and\n" + "    `sales_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `product_class`.`product_family` = 'Drink'\n" + "and\n" + "    `product_class`.`product_department` = 'Alcoholic Beverages'\n" + "and\n" + "    `product_class`.`product_category` = 'Beer and Wine'\n" + "and\n" + "    `product_class`.`product_subcategory` = 'Beer'\n" + "and\n" + "    `product`.`brand_name` = 'Portsmouth'\n" + "and\n" + "    `sales_fact_1997`.`customer_id` = `customer`.`customer_id`\n" + "and\n" + "    `customer`.`customer_id` = 5219\n" + "group by\n" + "    `time_by_day`.`the_year`,\n" + "    `time_by_day`.`quarter`,\n" + "    `time_by_day`.`month_of_year`,\n" + "    `product_class`.`product_family`,\n" + "    `product_class`.`product_department`,\n" + "    `product_class`.`product_category`,\n" + "    `product_class`.`product_subcategory`,\n" + "    `product`.`brand_name`,\n" + "    `customer`.`customer_id`";
    String triggerSql = "select\n" + "    `time_by_day`.`the_year` as `c0`,\n" + "    `time_by_day`.`quarter` as `c1`,\n" + "    `time_by_day`.`month_of_year` as `c2`,\n" + "    `product_class`.`product_family` as `c3`,\n";
    if (MondrianProperties.instance().UseAggregates.get() && MondrianProperties.instance().ReadAggregates.get()) {
        mysqlNativeCrossJoinQuery = "select\n" + "    `agg_c_14_sales_fact_1997`.`the_year` as `c0`,\n" + "    `agg_c_14_sales_fact_1997`.`quarter` as `c1`,\n" + "    `agg_c_14_sales_fact_1997`.`month_of_year` as `c2`,\n" + "    `product_class`.`product_family` as `c3`,\n" + "    `product_class`.`product_department` as `c4`,\n" + "    `product_class`.`product_category` as `c5`,\n" + "    `product_class`.`product_subcategory` as `c6`,\n" + "    `product`.`brand_name` as `c7`,\n" + "    `customer`.`customer_id` as `c8`,\n" + "    sum(`agg_c_14_sales_fact_1997`.`unit_sales`) as `m0`\n" + "from\n" + "    `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`,\n" + "    `product_class` as `product_class`,\n" + "    `product` as `product`,\n" + "    `customer` as `customer`\n" + "where\n" + "    `agg_c_14_sales_fact_1997`.`the_year` = 1997\n" + "and\n" + "    `agg_c_14_sales_fact_1997`.`quarter` in ('Q1', 'Q2')\n" + "and\n" + "    `agg_c_14_sales_fact_1997`.`month_of_year` in (2, 3, 4, 5)\n" + "and\n" + "    `agg_c_14_sales_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `product_class`.`product_family` = 'Drink'\n" + "and\n" + "    `product_class`.`product_department` = 'Alcoholic Beverages'\n" + "and\n" + "    `product_class`.`product_category` = 'Beer and Wine'\n" + "and\n" + "    `product_class`.`product_subcategory` = 'Beer'\n" + "and\n" + "    `product`.`brand_name` = 'Portsmouth'\n" + "and\n" + "    `agg_c_14_sales_fact_1997`.`customer_id` = `customer`.`customer_id`\n" + "and\n" + "    `customer`.`customer_id` = 5219\n" + "group by\n" + "    `agg_c_14_sales_fact_1997`.`the_year`,\n" + "    `agg_c_14_sales_fact_1997`.`quarter`,\n" + "    `agg_c_14_sales_fact_1997`.`month_of_year`,\n" + "    `product_class`.`product_family`,\n" + "    `product_class`.`product_department`,\n" + "    `product_class`.`product_category`,\n" + "    `product_class`.`product_subcategory`,\n" + "    `product`.`brand_name`,\n" + "    `customer`.`customer_id`";
        triggerSql = "select\n" + "    `agg_c_14_sales_fact_1997`.`the_year` as `c0`,\n" + "    `agg_c_14_sales_fact_1997`.`quarter` as `c1`,\n" + "    `agg_c_14_sales_fact_1997`.`month_of_year` as `c2`,\n" + "    `product_class`.`product_family` as `c3`,\n";
    }
    SqlPattern mysqlPattern = new SqlPattern(DatabaseProduct.MYSQL, mysqlNativeCrossJoinQuery, triggerSql);
    assertQuerySql(mdx, new SqlPattern[] { mysqlPattern });
    checkNative(20, 1, "select NON EMPTY\n" + " Crossjoin([Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Portsmouth]\n" + " , [Customers].[USA].[WA].[Puyallup].Children) ON COLUMNS\n" + "from [Sales]\n" + "where ([Time].[1997].[Q1].[2] : [Time].[1997].[Q2].[5])", "Axis #0:\n" + "{[Time].[1997].[Q1].[2]}\n" + "{[Time].[1997].[Q1].[3]}\n" + "{[Time].[1997].[Q2].[4]}\n" + "{[Time].[1997].[Q2].[5]}\n" + "Axis #1:\n" + "{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Portsmouth], [Customers].[USA].[WA].[Puyallup].[Diane Biondo]}\n" + "Row #0: 2\n", true);
}

Example 88 with SqlPattern

use of mondrian.test.SqlPattern in project mondrian by pentaho.

the class NativizeSetFunDefTest method testAxisWithArityOneIsNotNativelyEvaluated.

public void testAxisWithArityOneIsNotNativelyEvaluated() {
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.ACCESS, "select `promotion`.`media_type` as `c0` " + "from `promotion` as `promotion`, `sales_fact_1997` as `sales_fact_1997` " + "where `sales_fact_1997`.`promotion_id` = `promotion`.`promotion_id` " + "group by `promotion`.`media_type` " + "order by Iif(`promotion`.`media_type` IS NULL, 1, 0), " + "`promotion`.`media_type` ASC", 296) };
    String query = "select " + "  NON EMPTY " + "  NativizeSet(" + "    Except(" + "      {[Promotion Media].[Promotion Media].Members},\n" + "      {[Promotion Media].[Bulk Mail],[Promotion Media].[All Media].[Daily Paper]}" + "    )" + "  ) ON COLUMNS," + "  NON EMPTY " + "  {[Measures].[Unit Sales]} ON ROWS " + "from [Sales] \n" + "where [Time].[1997]";
    assertQuerySqlOrNot(getTestContext(), query, patterns, true, false, true);
}

Example 89 with SqlPattern

use of mondrian.test.SqlPattern in project mondrian by pentaho.

the class BatchTestCase method assertRequestSql.

/**
 * Checks that a given sequence of cell requests results in a
 * particular SQL statement being generated.
 *
 * <p>Always clears the cache before running the requests.
 *
 * <p>Runs the requests once for each SQL pattern in the current
 * dialect. If there are multiple patterns, runs the MDX query multiple
 * times, and expects to see each SQL statement appear. If there are no
 * patterns in this dialect, the test trivially succeeds.
 *
 * @param requests Sequence of cell requests
 * @param patterns Set of patterns
 * @param negative Set to false in order to 'expect' a query or
 * true to 'forbid' a query.
 */
protected void assertRequestSql(CellRequest[] requests, SqlPattern[] patterns, boolean negative) {
    final RolapStar star = requests[0].getMeasure().getStar();
    final String cubeName = requests[0].getMeasure().getCubeName();
    final RolapCube cube = lookupCube(cubeName);
    final Dialect sqlDialect = star.getSqlQueryDialect();
    Dialect.DatabaseProduct d = sqlDialect.getDatabaseProduct();
    SqlPattern sqlPattern = SqlPattern.getPattern(d, patterns);
    if (d == Dialect.DatabaseProduct.UNKNOWN) {
        // test. We do not print any warning message.
        return;
    }
    boolean patternFound = false;
    for (SqlPattern pattern : patterns) {
        if (!pattern.hasDatabaseProduct(d)) {
            continue;
        }
        patternFound = true;
        clearCache(cube);
        String sql = sqlPattern.getSql();
        String trigger = sqlPattern.getTriggerSql();
        switch(d) {
            case ORACLE:
                sql = sql.replaceAll(" =as= ", " ");
                trigger = trigger.replaceAll(" =as= ", " ");
                break;
            case TERADATA:
                sql = sql.replaceAll(" =as= ", " as ");
                trigger = trigger.replaceAll(" =as= ", " as ");
                break;
        }
        // Create a dummy DataSource which will throw a 'bomb' if it is
        // asked to execute a particular SQL statement, but will otherwise
        // behave exactly the same as the current DataSource.
        RolapUtil.setHook(new TriggerHook(trigger));
        Bomb bomb;
        final Execution execution = new Execution(((RolapConnection) getConnection()).getInternalStatement(), 1000);
        final AggregationManager aggMgr = execution.getMondrianStatement().getMondrianConnection().getServer().getAggregationManager();
        final Locus locus = new Locus(execution, "BatchTestCase", "BatchTestCase");
        try {
            FastBatchingCellReader fbcr = new FastBatchingCellReader(execution, getCube(cubeName), aggMgr);
            for (CellRequest request : requests) {
                fbcr.recordCellRequest(request);
            }
            // The FBCR will presume there is a current Locus in the stack,
            // so let's create a mock one.
            Locus.push(locus);
            fbcr.loadAggregations();
            bomb = null;
        } catch (Bomb e) {
            bomb = e;
        } catch (RuntimeException e) {
            // Walk up the exception tree and see if the root cause
            // was a SQL bomb.
            bomb = Util.getMatchingCause(e, Bomb.class);
            if (bomb == null) {
                throw e;
            }
        } finally {
            RolapUtil.setHook(null);
            Locus.pop(locus);
        }
        if (!negative && bomb == null) {
            fail("expected query [" + sql + "] did not occur");
        } else if (negative && bomb != null) {
            fail("forbidden query [" + sql + "] detected");
        }
        TestContext.assertEqualsVerbose(replaceQuotes(sql), replaceQuotes(bomb.sql));
    }
    // dialect.
    if (!patternFound) {
        String warnDialect = MondrianProperties.instance().WarnIfNoPatternForDialect.get();
        if (warnDialect.equals(d.toString())) {
            System.out.println("[No expected SQL statements found for dialect \"" + sqlDialect.toString() + "\" and test not run]");
        }
    }
}

Example 90 with SqlPattern

use of mondrian.test.SqlPattern in project mondrian by pentaho.

the class FastBatchingCellReaderTest method testAggregateDistinctCountInDimensionFilter.

public void testAggregateDistinctCountInDimensionFilter() {
    String query = "With " + "Set [Products] as '{[Product].[All Products].[Drink], [Product].[All Products].[Food]}' " + "Set [States] as '{[Store].[All Stores].[USA].[CA], [Store].[All Stores].[USA].[OR]}' " + "Member [Product].[Selected Products] as 'Aggregate([Products])', SOLVE_ORDER=2 " + "Select " + "Filter([States], not IsEmpty([Measures].[Customer Count])) on rows, " + "{[Measures].[Customer Count]} on columns " + "From [Sales] " + "Where ([Product].[Selected Products])";
    assertQueryReturns(query, "Axis #0:\n" + "{[Product].[Selected Products]}\n" + "Axis #1:\n" + "{[Measures].[Customer Count]}\n" + "Axis #2:\n" + "{[Store].[USA].[CA]}\n" + "{[Store].[USA].[OR]}\n" + "Row #0: 2,692\n" + "Row #1: 1,036\n");
    String mysqlSql = "select " + "`store`.`store_state` as `c0`, `time_by_day`.`the_year` as `c1`, " + "count(distinct `sales_fact_1997`.`customer_id`) as `m0` " + "from " + "`store` as `store`, `sales_fact_1997` as `sales_fact_1997`, " + "`time_by_day` as `time_by_day`, `product_class` as `product_class`, " + "`product` as `product` " + "where " + "`sales_fact_1997`.`store_id` = `store`.`store_id` and " + "`store`.`store_state` in ('CA', 'OR') and " + "`sales_fact_1997`.`time_id` = `time_by_day`.`time_id` and " + "`time_by_day`.`the_year` = 1997 and " + "`sales_fact_1997`.`product_id` = `product`.`product_id` and " + "`product`.`product_class_id` = `product_class`.`product_class_id` and " + "`product_class`.`product_family` in ('Drink', 'Food') " + "group by " + "`store`.`store_state`, `time_by_day`.`the_year`";
    String derbySql = "select " + "\"store\".\"store_state\" as \"c0\", \"time_by_day\".\"the_year\" as \"c1\", " + "count(distinct \"sales_fact_1997\".\"customer_id\") as \"m0\" " + "from " + "\"store\" as \"store\", \"sales_fact_1997\" as \"sales_fact_1997\", " + "\"time_by_day\" as \"time_by_day\", \"product_class\" as \"product_class\", " + "\"product\" as \"product\" " + "where " + "\"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" and " + "\"store\".\"store_state\" in ('CA', 'OR') and " + "\"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" and " + "\"time_by_day\".\"the_year\" = 1997 and " + "\"sales_fact_1997\".\"product_id\" = \"product\".\"product_id\" and " + "\"product\".\"product_class_id\" = \"product_class\".\"product_class_id\" and " + "\"product_class\".\"product_family\" in ('Drink', 'Food') " + "group by " + "\"store\".\"store_state\", \"time_by_day\".\"the_year\"";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.DERBY, derbySql, derbySql), new SqlPattern(Dialect.DatabaseProduct.MYSQL, mysqlSql, mysqlSql) };
    assertQuerySql(query, patterns);
}