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

Example 71 with SqlPattern

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

the class VirtualCubeTest method testNativeSetCaching.

/**
 * Checks that native set caching considers base cubes in the cache key.
 * Native sets referencing different base cubes do not share the cached
 * result.
 */
public void testNativeSetCaching() {
    // Only need to run this against one db to verify caching
    // behavior is correct.
    final Dialect dialect = getTestContext().getDialect();
    if (dialect.getDatabaseProduct() != Dialect.DatabaseProduct.DERBY) {
        return;
    }
    if (!MondrianProperties.instance().EnableNativeCrossJoin.get() && !MondrianProperties.instance().EnableNativeNonEmpty.get()) {
        // is enabled.
        return;
    }
    String query1 = "With " + "Set [*NATIVE_CJ_SET] as 'NonEmptyCrossJoin([Product].[Product Family].Members, [Store].[Store Country].Members)' " + "Select " + "{[Store Sales]} on columns, " + "Non Empty Generate([*NATIVE_CJ_SET], {([Product].CurrentMember,[Store].CurrentMember)}) on rows " + "From [Warehouse and Sales]";
    String query2 = "With " + "Set [*NATIVE_CJ_SET] as 'NonEmptyCrossJoin([Product].[Product Family].Members, [Store].[Store Country].Members)' " + "Select " + "{[Warehouse Sales]} on columns, " + "Non Empty Generate([*NATIVE_CJ_SET], {([Product].CurrentMember,[Store].CurrentMember)}) on rows " + "From [Warehouse and Sales]";
    String derbyNecjSql1, derbyNecjSql2;
    if (MondrianProperties.instance().EnableNativeCrossJoin.get()) {
        derbyNecjSql1 = "select " + "\"product_class\".\"product_family\", " + "\"store\".\"store_country\" " + "from " + "\"product\" as \"product\", " + "\"product_class\" as \"product_class\", " + "\"sales_fact_1997\" as \"sales_fact_1997\", " + "\"store\" as \"store\" " + "where " + "\"product\".\"product_class_id\" = \"product_class\".\"product_class_id\" " + "and \"sales_fact_1997\".\"product_id\" = \"product\".\"product_id\" " + "and \"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" " + "group by \"product_class\".\"product_family\", \"store\".\"store_country\" " + "order by 1 ASC, 2 ASC";
        derbyNecjSql2 = "select " + "\"product_class\".\"product_family\", " + "\"store\".\"store_country\" " + "from " + "\"product\" as \"product\", " + "\"product_class\" as \"product_class\", " + "\"inventory_fact_1997\" as \"inventory_fact_1997\", " + "\"store\" as \"store\" " + "where " + "\"product\".\"product_class_id\" = \"product_class\".\"product_class_id\" " + "and \"inventory_fact_1997\".\"product_id\" = \"product\".\"product_id\" " + "and \"inventory_fact_1997\".\"store_id\" = \"store\".\"store_id\" " + "group by \"product_class\".\"product_family\", \"store\".\"store_country\" " + "order by 1 ASC, 2 ASC";
    } else {
        // NECJ is truend off so native NECJ SQL will not be generated;
        // however, because the NECJ set should not find match in the cache,
        // each NECJ input will still be joined with the correct
        // fact table if NonEmpty condition is natively evaluated.
        derbyNecjSql1 = "select " + "\"store\".\"store_country\" " + "from " + "\"store\" as \"store\", " + "\"sales_fact_1997\" as \"sales_fact_1997\" " + "where " + "\"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" " + "group by \"store\".\"store_country\" " + "order by 1 ASC";
        derbyNecjSql2 = "select " + "\"store\".\"store_country\" " + "from " + "\"store\" as \"store\", " + "\"inventory_fact_1997\" as \"inventory_fact_1997\" " + "where " + "\"inventory_fact_1997\".\"store_id\" = \"store\".\"store_id\" " + "group by \"store\".\"store_country\" " + "order by 1 ASC";
    }
    SqlPattern[] patterns1 = { new SqlPattern(Dialect.DatabaseProduct.DERBY, derbyNecjSql1, derbyNecjSql1) };
    SqlPattern[] patterns2 = { new SqlPattern(Dialect.DatabaseProduct.DERBY, derbyNecjSql2, derbyNecjSql2) };
    // Run query 1 with cleared cache;
    // Make sure NECJ 1 is evaluated natively.
    assertQuerySql(query1, patterns1, true);
    // Now run query 2 with warm cache;
    // Make sure NECJ 2 does not reuse the cache result from NECJ 1, and
    // NECJ 2 is evaluated natively.
    assertQuerySql(query2, patterns2, false);
}

Example 72 with SqlPattern

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

the class VirtualCubeTest method testNonEmptyConstraintOnVirtualCubeWithCalcMeasure.

/**
 * Tests that the logic to apply non empty context constraint in virtual
 * cube is correct.  The joins shouldn't be cartesian product.
 */
public void testNonEmptyConstraintOnVirtualCubeWithCalcMeasure() {
    if (!MondrianProperties.instance().EnableNativeNonEmpty.get()) {
        // Generated SQL is different if NON EMPTY is evaluated in memory.
        return;
    }
    // we want to make sure a SqlConstraint is used for retrieving
    // [Product Family].members
    propSaver.set(propSaver.properties.LevelPreCacheThreshold, 0);
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    String query = "with " + "set [bar] as {[Store].[USA]} " + "member [Measures].[CalcMeasure] as '[Measures].[Warehouse Sales] / [Measures].[Store Sales]' " + "Select " + "{[Measures].[CalcMeasure]} on columns, " + "non empty([Product].[Product Family].Members) on rows " + "From [Warehouse and Sales] " + "where [bar]";
    // Comments as for testNonEmptyCJConstraintOnVirtualCube. The ORDER BY
    // clause should be "order by ISNULL(1), 1 ASC" but we will settle for
    // "order by 1 ASC" and forego correct sorting of NULL values.
    String mysqlSQL = MondrianProperties.instance().UseAggregates.get() ? "select\n" + "    *\n" + "from\n" + "    (select\n" + "    `product_class`.`product_family` as `c0`\n" + "from\n" + "    `product` as `product`,\n" + "    `product_class` as `product_class`,\n" + "    `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`,\n" + "    `store` as `store`\n" + "where\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `agg_c_14_sales_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    `agg_c_14_sales_fact_1997`.`store_id` = `store`.`store_id`\n" + "and\n" + "    `store`.`store_country` = 'USA'\n" + "group by\n" + "    `product_class`.`product_family`\n" + "union\n" + "select\n" + "    `product_class`.`product_family` as `c0`\n" + "from\n" + "    `product` as `product`,\n" + "    `product_class` as `product_class`,\n" + "    `inventory_fact_1997` as `inventory_fact_1997`,\n" + "    `store` as `store`\n" + "where\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `inventory_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    `inventory_fact_1997`.`store_id` = `store`.`store_id`\n" + "and\n" + "    `store`.`store_country` = 'USA'\n" + "group by\n" + "    `product_class`.`product_family`) as `unionQuery`\n" + "order by\n" + "    ISNULL(1) ASC, 1 ASC" : "select\n" + "    *\n" + "from\n" + "    (select\n" + "    `product_class`.`product_family` as `c0`\n" + "from\n" + "    `product` as `product`,\n" + "    `product_class` as `product_class`,\n" + "    `sales_fact_1997` as `sales_fact_1997`,\n" + "    `store` as `store`\n" + "where\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `sales_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    `sales_fact_1997`.`store_id` = `store`.`store_id`\n" + "and\n" + "    `store`.`store_country` = 'USA'\n" + "group by\n" + "    `product_class`.`product_family`\n" + "union\n" + "select\n" + "    `product_class`.`product_family` as `c0`\n" + "from\n" + "    `product` as `product`,\n" + "    `product_class` as `product_class`,\n" + "    `inventory_fact_1997` as `inventory_fact_1997`,\n" + "    `store` as `store`\n" + "where\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `inventory_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    `inventory_fact_1997`.`store_id` = `store`.`store_id`\n" + "and\n" + "    `store`.`store_country` = 'USA'\n" + "group by\n" + "    `product_class`.`product_family`) as `unionQuery`\n" + "order by\n" + "    ISNULL(1) ASC, 1 ASC";
    String result = "Axis #0:\n" + "{[Store].[USA]}\n" + "Axis #1:\n" + "{[Measures].[CalcMeasure]}\n" + "Axis #2:\n" + "{[Product].[Drink]}\n" + "{[Product].[Food]}\n" + "{[Product].[Non-Consumable]}\n" + "Row #0: 0.369\n" + "Row #1: 0.345\n" + "Row #2: 0.35\n";
    SqlPattern[] mysqlPattern = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, mysqlSQL, mysqlSQL) };
    assertQuerySql(query, mysqlPattern, true);
    assertQueryReturns(query, result);
}

Example 73 with SqlPattern

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

the class AggregationOnDistinctCountMeasuresTest method testCrossJoinMembersWithSetOfMembers.

public void testCrossJoinMembersWithSetOfMembers() {
    // make sure tuple optimization will be used
    propSaver.set(propSaver.properties.MaxConstraints, 2);
    String query = "WITH MEMBER GENDER.X AS 'AGGREGATE({[GENDER].[GENDER].members} * " + "{[STORE].[ALL STORES].[USA].[CA], [Store].[All Stores].[Canada]})', solve_order=100 " + "SELECT GENDER.X ON 0, [MEASURES].[CUSTOMER COUNT] ON 1 FROM SALES";
    String result = "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Gender].[X]}\n" + "Axis #2:\n" + "{[Measures].[Customer Count]}\n" + "Row #0: 2,716\n";
    assertQueryReturns(query, result);
    // Check aggregate loading sql pattern.  Note Derby does not support
    // multicolumn IN list, so the predicates remain in AND/OR form.
    String derbySql = "select \"time_by_day\".\"the_year\" as \"c0\", " + "count(distinct \"sales_fact_1997\".\"customer_id\") as \"m0\" " + "from \"time_by_day\" as \"time_by_day\", \"sales_fact_1997\" as \"sales_fact_1997\", \"store\" as \"store\" " + "where \"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" " + "and \"time_by_day\".\"the_year\" = 1997 " + "and \"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" " + "and (\"store\".\"store_state\" = 'CA' or \"store\".\"store_country\" = 'Canada') " + "group by \"time_by_day\".\"the_year\"";
    String mysqlSql = "select `time_by_day`.`the_year` as `c0`, " + "count(distinct `sales_fact_1997`.`customer_id`) as `m0` " + "from `time_by_day` as `time_by_day`, `sales_fact_1997` as `sales_fact_1997`, `store` as `store` " + "where `sales_fact_1997`.`time_id` = `time_by_day`.`time_id` and `time_by_day`.`the_year` = 1997 " + "and `sales_fact_1997`.`store_id` = `store`.`store_id` " + "and (`store`.`store_state` = 'CA' or `store`.`store_country` = 'Canada') " + "group by `time_by_day`.`the_year`";
    String oraTeraSql = "select \"time_by_day\".\"the_year\" as \"c0\", " + "count(distinct \"sales_fact_1997\".\"customer_id\") as \"m0\" " + "from \"time_by_day\" =as= \"time_by_day\", \"sales_fact_1997\" =as= \"sales_fact_1997\", \"store\" =as= \"store\" " + "where \"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" and \"time_by_day\".\"the_year\" = 1997 " + "and \"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" " + "and (\"store\".\"store_state\" = 'CA' or \"store\".\"store_country\" = 'Canada') " + "group by \"time_by_day\".\"the_year\"";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.DERBY, derbySql, derbySql), new SqlPattern(Dialect.DatabaseProduct.MYSQL, mysqlSql, mysqlSql), new SqlPattern(Dialect.DatabaseProduct.ORACLE, oraTeraSql, oraTeraSql), new SqlPattern(Dialect.DatabaseProduct.TERADATA, oraTeraSql, oraTeraSql) };
    assertQuerySql(query, patterns);
}

Example 74 with SqlPattern

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

the class AggregationOnDistinctCountMeasuresTest method testAggregationOfMembersAndDefaultMemberWithoutGroupingSets.

public void testAggregationOfMembersAndDefaultMemberWithoutGroupingSets() {
    propSaver.set(props.EnableGroupingSets, false);
    String mdxQueryWithMembers = "WITH " + "MEMBER [Gender].[COG_OQP_USR_Aggregate(Gender)] AS " + "'AGGREGATE({[Gender].MEMBERS})', SOLVE_ORDER = 8" + "SELECT {[Measures].[Customer Count]} ON AXIS(0), " + "{[Gender].MEMBERS, [Gender].[COG_OQP_USR_Aggregate(Gender)]} " + "ON AXIS(1) " + "FROM [Sales]";
    String mdxQueryWithDefaultMember = "WITH " + "MEMBER [Gender].[COG_OQP_USR_Aggregate(Gender)] AS " + "'AGGREGATE({[Gender].DEFAULTMEMBER})', SOLVE_ORDER = 8" + "SELECT {[Measures].[Customer Count]} ON AXIS(0), \n" + "{[Gender].MEMBERS, [Gender].[COG_OQP_USR_Aggregate(Gender)]} " + "ON AXIS(1) \n" + "FROM [sales]";
    String desiredResult = "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Customer Count]}\n" + "Axis #2:\n" + "{[Gender].[All Gender]}\n" + "{[Gender].[F]}\n" + "{[Gender].[M]}\n" + "{[Gender].[COG_OQP_USR_Aggregate(Gender)]}\n" + "Row #0: 5,581\n" + "Row #1: 2,755\n" + "Row #2: 2,826\n" + "Row #3: 5,581\n";
    String oraTeraSql = "select \"time_by_day\".\"the_year\" as \"c0\", " + "\"customer\".\"gender\" as \"c1\", " + "count(distinct \"sales_fact_1997\".\"customer_id\") as \"m0\" " + "from \"time_by_day\" =as= \"time_by_day\", " + "\"sales_fact_1997\" =as= \"sales_fact_1997\", \"customer\" =as= \"customer\" " + "where \"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" " + "and \"time_by_day\".\"the_year\" = 1997 " + "and \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\" " + "group by \"time_by_day\".\"the_year\", \"customer\".\"gender\"";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.ORACLE, oraTeraSql, oraTeraSql), new SqlPattern(Dialect.DatabaseProduct.TERADATA, oraTeraSql, oraTeraSql) };
    assertQueryReturns(mdxQueryWithMembers, desiredResult);
    assertQuerySql(mdxQueryWithMembers, patterns);
    assertQueryReturns(mdxQueryWithDefaultMember, desiredResult);
    assertQuerySql(mdxQueryWithDefaultMember, patterns);
}

Example 75 with SqlPattern

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

the class AggregationOnDistinctCountMeasuresTest method testDistinctCountAggMeasure.

/**
 * Test case for
 * <a href="http://jira.pentaho.com/browse/MONDRIAN-1370">MONDRIAN-1370</a>
 * <br> Wrong results for aggregate with distinct count measure.
 */
public void testDistinctCountAggMeasure() {
    String dimension = "<Dimension name=\"Time\" type=\"TimeDimension\"> " + "  <Hierarchy hasAll=\"false\" primaryKey=\"time_id\"> " + "    <Table name=\"time_by_day\"/> " + "    <Level name=\"Year\" column=\"the_year\" type=\"Numeric\" uniqueMembers=\"true\" levelType=\"TimeYears\"/> " + "    <Level name=\"Quarter\" column=\"quarter\" uniqueMembers=\"false\" levelType=\"TimeQuarters\"/> " + "    <Level name=\"Month\" column=\"month_of_year\" uniqueMembers=\"false\" type=\"Numeric\" levelType=\"TimeMonths\"/> " + "  </Hierarchy> " + "</Dimension>";
    String cube = "<Cube name=\"Sales\" defaultMeasure=\"Unit Sales\"> " + "  <Table name=\"sales_fact_1997\"> " + "      <AggExclude name=\"agg_c_special_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_g_ms_pcat_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_c_14_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_l_05_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_lc_06_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_l_04_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_ll_01_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_lc_100_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_l_03_sales_fact_1997\"/>" + "      <AggExclude name=\"agg_pl_01_sales_fact_1997\"/>" + "      <AggName name=\"agg_c_10_sales_fact_1997\">" + "           <AggFactCount column=\"FACT_COUNT\"/>" + "           <AggMeasure name=\"[Measures].[Store Sales]\" column=\"store_sales\"/>" + "           <AggMeasure name=\"[Measures].[Store Cost]\" column=\"store_cost\"/>" + "           <AggMeasure name=\"[Measures].[Unit Sales]\" column=\"unit_sales\"/>" + "           <AggMeasure name=\"[Measures].[Customer Count]\" column=\"customer_count\" />" + "           <AggLevel name=\"[Time].[Year]\" column=\"the_year\" />" + "           <AggLevel name=\"[Time].[Quarter]\" column=\"quarter\" />" + "           <AggLevel name=\"[Time].[Month]\" column=\"month_of_year\" />" + "      </AggName>" + "  </Table>" + "  <DimensionUsage name=\"Time\" source=\"Time\" foreignKey=\"time_id\"/> " + "<Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n" + "      formatString=\"Standard\"/>\n" + "  <Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"\n" + "      formatString=\"#,###.00\"/>\n" + "  <Measure name=\"Store Sales\" column=\"store_sales\" aggregator=\"sum\"\n" + "      formatString=\"#,###.00\"/>" + "  <Measure name=\"Customer Count\" column=\"customer_id\" aggregator=\"distinct-count\" formatString=\"#,###\" />" + "</Cube>";
    final String query = "select " + "  NON EMPTY {[Measures].[Customer Count]} ON COLUMNS, " + "  NON EMPTY {[Time].[Year].Members} ON ROWS " + "from [Sales]";
    final String monthsQuery = "select " + "  NON EMPTY {[Measures].[Customer Count]} ON COLUMNS, " + "  NON EMPTY {[Time].[1997].[Q1].Children} ON ROWS " + "from [Sales]";
    String simpleSchema = "<Schema name=\"FoodMart\">" + dimension + cube + "</Schema>";
    // should skip aggregate table, cannot aggregate
    propSaver.set(propSaver.properties.UseAggregates, true);
    propSaver.set(propSaver.properties.ReadAggregates, true);
    TestContext withAggDistinctCount = TestContext.instance().withSchema(simpleSchema);
    withAggDistinctCount.assertQueryReturns(query, "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Customer Count]}\n" + "Axis #2:\n" + "{[Time].[1997]}\n" + "Row #0: 5,581\n");
    // aggregate table has count for months, make sure it is used
    propSaver.set(propSaver.properties.UseAggregates, true);
    propSaver.set(propSaver.properties.ReadAggregates, true);
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    final String expectedSql = "select\n" + "    `agg_c_10_sales_fact_1997`.`the_year` as `c0`,\n" + "    `agg_c_10_sales_fact_1997`.`quarter` as `c1`,\n" + "    `agg_c_10_sales_fact_1997`.`month_of_year` as `c2`,\n" + "    `agg_c_10_sales_fact_1997`.`customer_count` as `m0`\n" + "from\n" + "    `agg_c_10_sales_fact_1997` as `agg_c_10_sales_fact_1997`\n" + "where\n" + "    `agg_c_10_sales_fact_1997`.`the_year` = 1997\n" + "and\n" + "    `agg_c_10_sales_fact_1997`.`quarter` = 'Q1'\n" + "and\n" + "    `agg_c_10_sales_fact_1997`.`month_of_year` in (1, 2, 3)";
    assertQuerySqlOrNot(withAggDistinctCount, monthsQuery, new SqlPattern[] { new SqlPattern(DatabaseProduct.MYSQL, expectedSql, expectedSql.indexOf("from")) }, false, true, true);
}