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

Example 16 with SqlPattern

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

the class AggregationOnDistinctCountMeasuresTest method testDistinctCountInNonGroupingSetsQuery.

/**
 * Test distinct count agg happens in non gs query for subset of members
 * with mixed measures.
 */
public void testDistinctCountInNonGroupingSetsQuery() {
    propSaver.set(props.EnableGroupingSets, true);
    String mdxQueryWithFewMembers = "WITH " + "MEMBER [Store].[COG_OQP_USR_Aggregate(Store)] AS " + "'AGGREGATE({[Store].[All Stores].[USA].[CA], [Store].[All Stores].[USA].[OR]})', SOLVE_ORDER = 8" + "SELECT {[Measures].[Customer Count],[Measures].[Unit Sales]} ON AXIS(0), " + "{[Store].[All Stores].[USA].[CA], [Store].[All Stores].[USA].[OR], [Store].[COG_OQP_USR_Aggregate(Store)]} " + "ON AXIS(1) " + "FROM [Sales]";
    String desiredResult = "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Customer Count]}\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Store].[USA].[CA]}\n" + "{[Store].[USA].[OR]}\n" + "{[Store].[COG_OQP_USR_Aggregate(Store)]}\n" + "Row #0: 2,716\n" + "Row #0: 74,748\n" + "Row #1: 1,037\n" + "Row #1: 67,659\n" + "Row #2: 3,753\n" + "Row #2: 142,407\n";
    String oraTeraSqlForDetail = "select \"store\".\"store_state\" as \"c0\", " + "\"time_by_day\".\"the_year\" as \"c1\", " + "sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\", " + "count(distinct \"sales_fact_1997\".\"customer_id\") as \"m1\" " + "from \"store\" =as= \"store\", \"sales_fact_1997\" =as= \"sales_fact_1997\", " + "\"time_by_day\" =as= \"time_by_day\" " + "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 " + "group by \"store\".\"store_state\", \"time_by_day\".\"the_year\"";
    String oraTeraSqlForDistinctCountAgg = "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\" in ('CA', 'OR') " + "group by \"time_by_day\".\"the_year\"";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.ORACLE, oraTeraSqlForDetail, oraTeraSqlForDetail), new SqlPattern(Dialect.DatabaseProduct.TERADATA, oraTeraSqlForDetail, oraTeraSqlForDetail), new SqlPattern(Dialect.DatabaseProduct.ORACLE, oraTeraSqlForDistinctCountAgg, oraTeraSqlForDistinctCountAgg), new SqlPattern(Dialect.DatabaseProduct.TERADATA, oraTeraSqlForDistinctCountAgg, oraTeraSqlForDistinctCountAgg) };
    assertQueryReturns(mdxQueryWithFewMembers, desiredResult);
    assertQuerySql(mdxQueryWithFewMembers, patterns);
}

Example 17 with SqlPattern

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

the class SelectNotInGroupByTest method testGroupByNotSkippedIfIndependentProperty.

public void testGroupByNotSkippedIfIndependentProperty() {
    SqlPattern[] sqlPatterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlWithAllGroupBy, sqlWithAllGroupBy) };
    // Use dimension with unique level but level-indpendent property
    TestContext tc = TestContext.instance().create(storeDimensionUniqueLevelIndependentProp, cubeA, null, null, null, null);
    assertQuerySqlOrNot(tc, queryCubeA, sqlPatterns, false, false, true);
}

Example 18 with SqlPattern

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

the class SelectNotInGroupByTest method testDependentPropertySkipped.

public void testDependentPropertySkipped() {
    // Property group by should be skipped only if dialect supports it
    String sqlpat;
    if (dialectAllowsSelectNotInGroupBy()) {
        sqlpat = sqlWithLevelGroupBy;
    } else {
        sqlpat = sqlWithAllGroupBy;
    }
    SqlPattern[] sqlPatterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlpat, sqlpat) };
    // Use dimension with level-dependent property
    TestContext tc = TestContext.instance().create(storeDimensionLevelDependent, cubeA, null, null, null, null);
    assertQuerySqlOrNot(tc, queryCubeA, sqlPatterns, false, false, true);
}

Example 19 with SqlPattern

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

the class SqlQueryTest method testPredicatesAreOptimizedWhenAllTheMembersAreIncluded.

public void testPredicatesAreOptimizedWhenAllTheMembersAreIncluded() {
    if (prop.ReadAggregates.get() && prop.UseAggregates.get()) {
        // only check sql pattern when aggregate tables are not used.
        return;
    }
    String mdx = "select {[Time].[1997].[Q1],[Time].[1997].[Q2]," + "[Time].[1997].[Q3],[Time].[1997].[Q4]} on 0 from sales";
    String accessSql = "select `time_by_day`.`the_year` as `c0`, " + "`time_by_day`.`quarter` as `c1`, " + "sum(`sales_fact_1997`.`unit_sales`) as `m0` from " + "`time_by_day` as `time_by_day`, `sales_fact_1997` as" + " `sales_fact_1997` where `sales_fact_1997`.`time_id`" + " = `time_by_day`.`time_id` and `time_by_day`." + "`the_year` = 1997 group by `time_by_day`.`the_year`," + " `time_by_day`.`quarter`";
    String mysqlSql = "select " + "`time_by_day`.`the_year` as `c0`, `time_by_day`.`quarter` as `c1`, " + "sum(`sales_fact_1997`.`unit_sales`) as `m0` " + "from " + "`time_by_day` as `time_by_day`, `sales_fact_1997` as `sales_fact_1997` " + "where " + "`sales_fact_1997`.`time_id` = `time_by_day`.`time_id` and " + "`time_by_day`.`the_year` = 1997 " + "group by `time_by_day`.`the_year`, `time_by_day`.`quarter`";
    SqlPattern[] sqlPatterns = { new SqlPattern(Dialect.DatabaseProduct.ACCESS, accessSql, accessSql), new SqlPattern(MYSQL, mysqlSql, mysqlSql) };
    assertSqlEqualsOptimzePredicates(true, mdx, sqlPatterns);
    assertSqlEqualsOptimzePredicates(false, mdx, sqlPatterns);
}

Example 20 with SqlPattern

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

the class SqlQueryTest method testToStringForForcedIndexHint.

public void testToStringForForcedIndexHint() {
    Map<String, String> hints = new HashMap<String, String>();
    hints.put("force_index", "myIndex");
    String unformattedMysql = "select c1 as `c0`, c2 as `c1` " + "from `s`.`t1` as `t1alias`" + " FORCE INDEX (myIndex)" + " where a=b";
    String formattedMysql = "select\n" + "    c1 as `c0`,\n" + "    c2 as `c1`\n" + "from\n" + "    `s`.`t1` as `t1alias` FORCE INDEX (myIndex)\n" + "where\n" + "    a=b";
    SqlPattern[] unformattedSqlPatterns = { new SqlPattern(MYSQL, unformattedMysql, null) };
    SqlPattern[] formattedSqlPatterns = { new SqlPattern(MYSQL, formattedMysql, null) };
    for (boolean formatted : new boolean[] { false, true }) {
        Dialect dialect = getTestContext().getDialect();
        SqlQuery sqlQuery = new SqlQuery(dialect, formatted);
        sqlQuery.setAllowHints(true);
        sqlQuery.addSelect("c1", null);
        sqlQuery.addSelect("c2", null);
        sqlQuery.addGroupingFunction("gf0");
        sqlQuery.addFromTable("s", "t1", "t1alias", null, hints, true);
        sqlQuery.addWhere("a=b");
        SqlPattern[] expected;
        if (!formatted) {
            expected = unformattedSqlPatterns;
        } else {
            expected = formattedSqlPatterns;
        }
        assertSqlQueryToStringMatches(sqlQuery, expected);
    }
}