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

Example 46 with SqlPattern

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

the class FastBatchingCellReaderTest method testDistinctCountBug1785406_2.

public void testDistinctCountBug1785406_2() {
    String query = "With " + "Member [Product].[x] as 'Aggregate({Gender.CurrentMember})'\n" + "member [Measures].[foo] as '([Product].[x],[Measures].[Customer Count])'\n" + "select Filter([Gender].members,(Not IsEmpty([Measures].[foo]))) on 0 " + "from Sales";
    assertQueryReturns(query, "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Gender].[All Gender]}\n" + "{[Gender].[F]}\n" + "{[Gender].[M]}\n" + "Row #0: 266,773\n" + "Row #0: 131,558\n" + "Row #0: 135,215\n");
    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` " + "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`";
    String accessSql = "select `d0` as `c0`," + " count(`m0`) as `c1` " + "from (select distinct `time_by_day`.`the_year` as `d0`," + " `sales_fact_1997`.`customer_id` 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) as `dummyname` group by `d0`";
    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\" " + "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\"";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.ACCESS, accessSql, accessSql), new SqlPattern(Dialect.DatabaseProduct.DERBY, derbySql, derbySql), new SqlPattern(Dialect.DatabaseProduct.MYSQL, mysqlSql, mysqlSql) };
    assertQuerySql(query, patterns);
}

Example 47 with SqlPattern

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

the class FilterTest method testMultiValueInWithNullVals.

/**
 * http://jira.pentaho.com/browse/MONDRIAN-1458
 * When using a multi value IN clause which includes null values
 * against a collapsed field on an aggregate table, the dimension table
 * field was referenced as the column expression, causing sql errors.
 */
public void testMultiValueInWithNullVals() {
    // MONDRIAN-1458 - Native exclusion predicate doesn't use agg table
    // when checking for nulls
    TestContext context = getTestContext();
    if (!propSaver.properties.EnableNativeCrossJoin.get() || !propSaver.properties.ReadAggregates.get() || !propSaver.properties.UseAggregates.get()) {
        return;
    }
    String sql;
    if (!context.getDialect().supportsMultiValueInExpr()) {
        sql = "select `agg_g_ms_pcat_sales_fact_1997`.`product_family` " + "as `c0`," + " `agg_g_ms_pcat_sales_fact_1997`.`product_department` as " + "`c1`," + " `agg_g_ms_pcat_sales_fact_1997`.`gender` as `c2` " + "from `agg_g_ms_pcat_sales_fact_1997` as " + "`agg_g_ms_pcat_sales_fact_1997` " + "where (not ((`agg_g_ms_pcat_sales_fact_1997`." + "`product_family` = 'Food'" + " and `agg_g_ms_pcat_sales_fact_1997`." + "`product_department` = 'Baked Goods') " + "or (`agg_g_ms_pcat_sales_fact_1997`.`product_family` " + "= 'Drink' " + "and `agg_g_ms_pcat_sales_fact_1997`." + "`product_department` = 'Dairy')) " + "or ((`agg_g_ms_pcat_sales_fact_1997`." + "`product_department` is null " + "or `agg_g_ms_pcat_sales_fact_1997`." + "`product_family` is null) " + "and not((`agg_g_ms_pcat_sales_fact_1997`.`product_family`" + " = 'Food' " + "and `agg_g_ms_pcat_sales_fact_1997`.`product_department` " + "= 'Baked Goods') " + "or (`agg_g_ms_pcat_sales_fact_1997`.`product_family` = " + "'Drink' " + "and `agg_g_ms_pcat_sales_fact_1997`.`product_department` " + "= 'Dairy')))) " + "group by `agg_g_ms_pcat_sales_fact_1997`.`product_family`," + " `agg_g_ms_pcat_sales_fact_1997`.`product_department`," + " `agg_g_ms_pcat_sales_fact_1997`.`gender` " + "order by ISNULL(`agg_g_ms_pcat_sales_fact_1997`." + "`product_family`) ASC," + " `agg_g_ms_pcat_sales_fact_1997`.`product_family` ASC," + " ISNULL(`agg_g_ms_pcat_sales_fact_1997`." + "`product_department`) ASC," + " `agg_g_ms_pcat_sales_fact_1997`.`product_department` ASC," + " ISNULL(`agg_g_ms_pcat_sales_fact_1997`.`gender`) ASC," + " `agg_g_ms_pcat_sales_fact_1997`.`gender` ASC";
    } else {
        sql = "select `agg_g_ms_pcat_sales_fact_1997`." + "`product_family` as `c0`," + " `agg_g_ms_pcat_sales_fact_1997`.`product_department` as `c1`," + " `agg_g_ms_pcat_sales_fact_1997`.`gender` as `c2` " + "from `agg_g_ms_pcat_sales_fact_1997` as " + "`agg_g_ms_pcat_sales_fact_1997` " + "where (not ((`agg_g_ms_pcat_sales_fact_1997`.`product_department`," + " `agg_g_ms_pcat_sales_fact_1997`.`product_family`) in " + "(('Baked Goods'," + " 'Food')," + " ('Dairy'," + " 'Drink'))) or (`agg_g_ms_pcat_sales_fact_1997`." + "`product_department` " + "is null or `agg_g_ms_pcat_sales_fact_1997`.`product_family` " + "is null)) " + "group by `agg_g_ms_pcat_sales_fact_1997`.`product_family`," + " `agg_g_ms_pcat_sales_fact_1997`.`product_department`," + " `agg_g_ms_pcat_sales_fact_1997`.`gender` order by " + "ISNULL(`agg_g_ms_pcat_sales_fact_1997`.`product_family`) ASC," + " `agg_g_ms_pcat_sales_fact_1997`.`product_family` ASC," + " ISNULL(`agg_g_ms_pcat_sales_fact_1997`.`product_department`) ASC," + " `agg_g_ms_pcat_sales_fact_1997`.`product_department` ASC," + " ISNULL(`agg_g_ms_pcat_sales_fact_1997`.`gender`) ASC," + " `agg_g_ms_pcat_sales_fact_1997`.`gender` ASC";
    }
    String mdx = "select NonEmptyCrossjoin( \n" + "   filter ( product.[product department].members,\n" + "      NOT ([Product].CurrentMember IN  \n" + "  { [Product].[Food].[Baked Goods], Product.Drink.Dairy})),\n" + "   gender.gender.members\n" + ")\n" + "on 0 from sales\n";
    assertQuerySql(mdx, new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.MYSQL, sql, null) });
}

Example 48 with SqlPattern

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

the class FilterTest method testBugMondrian706.

/**
 * Testcase for
 * <a href="http://jira.pentaho.com/browse/MONDRIAN-706">bug MONDRIAN-706,
 * "SQL using hierarchy attribute 'Column Name' instead of 'Column' in the
 * filter"</a>.
 */
public void testBugMondrian706() {
    propSaver.set(MondrianProperties.instance().UseAggregates, false);
    propSaver.set(MondrianProperties.instance().ReadAggregates, false);
    propSaver.set(MondrianProperties.instance().DisableCaching, false);
    propSaver.set(MondrianProperties.instance().EnableNativeNonEmpty, true);
    propSaver.set(MondrianProperties.instance().CompareSiblingsByOrderKey, true);
    propSaver.set(MondrianProperties.instance().NullDenominatorProducesNull, true);
    propSaver.set(MondrianProperties.instance().ExpandNonNative, true);
    propSaver.set(MondrianProperties.instance().EnableNativeFilter, true);
    // With bug MONDRIAN-706, would generate
    // 
    // ((`store`.`store_name`, `store`.`store_city`, `store`.`store_state`)
    // in (('11', 'Portland', 'OR'), ('14', 'San Francisco', 'CA'))
    // 
    // Notice that the '11' and '14' store ID is applied on the store_name
    // instead of the store_id. So it would return no rows.
    final String badMysqlSQL = "select `store`.`store_country` as `c0`, `store`.`store_state` as `c1`, `store`.`store_city` as `c2`, `store`.`store_id` as `c3`, `store`.`store_name` as `c4`, `store`.`store_type` as `c5`, `store`.`store_manager` as `c6`, `store`.`store_sqft` as `c7`, `store`.`grocery_sqft` as `c8`, `store`.`frozen_sqft` as `c9`, `store`.`meat_sqft` as `c10`, `store`.`coffee_bar` as `c11`, `store`.`store_street_address` as `c12` from `FOODMART`.`store` as `store` where (`store`.`store_state` in ('CA', 'OR')) and ((`store`.`store_name`,`store`.`store_city`,`store`.`store_state`) in (('11','Portland','OR'),('14','San Francisco','CA'))) group by `store`.`store_country`, `store`.`store_state`, `store`.`store_city`, `store`.`store_id`, `store`.`store_name`, `store`.`store_type`, `store`.`store_manager`, `store`.`store_sqft`, `store`.`grocery_sqft`, `store`.`frozen_sqft`, `store`.`meat_sqft`, `store`.`coffee_bar`, `store`.`store_street_address` having NOT((sum(`store`.`store_sqft`) is null)) " + (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(`store`.`store_country`) ASC, `store`.`store_country` ASC, " + "ISNULL(`store`.`store_state`) ASC, `store`.`store_state` ASC, " + "ISNULL(`store`.`store_city`) ASC, `store`.`store_city` ASC, " + "ISNULL(`store`.`store_id`) ASC, `store`.`store_id` ASC");
    final String goodMysqlSQL = "select `store`.`store_country` as `c0`, `store`.`store_state` as `c1`, `store`.`store_city` as `c2`, `store`.`store_id` as `c3`, `store`.`store_name` as `c4` from `store` as `store` where (`store`.`store_state` in ('CA', 'OR')) and ((`store`.`store_id`, `store`.`store_city`, `store`.`store_state`) in ((11, 'Portland', 'OR'), (14, 'San Francisco', 'CA'))) group by `store`.`store_country`, `store`.`store_state`, `store`.`store_city`, `store`.`store_id`, `store`.`store_name` having NOT((sum(`store`.`store_sqft`) is null)) " + (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(`store`.`store_country`) ASC, `store`.`store_country` ASC, " + "ISNULL(`store`.`store_state`) ASC, `store`.`store_state` ASC, " + "ISNULL(`store`.`store_city`) ASC, `store`.`store_city` ASC, " + "ISNULL(`store`.`store_id`) ASC, `store`.`store_id` ASC");
    final String mdx = "With\n" + "Set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Store], Not IsEmpty ([Measures].[Store Sqft]))'\n" + "Set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS],Ancestor([Store].CurrentMember, [Store].[Store Country]).OrderKey,BASC,Ancestor([Store].CurrentMember, [Store].[Store State]).OrderKey,BASC,Ancestor([Store].CurrentMember,\n" + "[Store].[Store City]).OrderKey,BASC,[Store].CurrentMember.OrderKey,BASC)'\n" + "Set [*NATIVE_MEMBERS_Store] as 'Generate([*NATIVE_CJ_SET], {[Store].CurrentMember})'\n" + "Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'\n" + "Set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Store].currentMember)})'\n" + "Set [*BASE_MEMBERS_Store] as 'Filter([Store].[Store Name].Members,(Ancestor([Store].CurrentMember, [Store].[Store State]) In {[Store].[All Stores].[USA].[CA],[Store].[All Stores].[USA].[OR]}) AND ([Store].CurrentMember In\n" + "{[Store].[All Stores].[USA].[OR].[Portland].[Store 11],[Store].[All Stores].[USA].[CA].[San Francisco].[Store 14]}))'\n" + "Set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'\n" + "Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Store Sqft]', FORMAT_STRING = '#,###', SOLVE_ORDER=400\n" + "Select\n" + "[*BASE_MEMBERS_Measures] on columns,\n" + "[*SORTED_ROW_AXIS] on rows\n" + "From [Store] \n";
    final SqlPattern[] badPatterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, badMysqlSQL, null) };
    final SqlPattern[] goodPatterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, goodMysqlSQL, null) };
    final TestContext testContext = TestContext.instance().createSubstitutingCube("Store", "<Dimension name='Store Type'>\n" + "    <Hierarchy name='Store Types Hierarchy' allMemberName='All Store Types Member Name' hasAll='true'>\n" + "      <Level name='Store Type' column='store_type' uniqueMembers='true'/>\n" + "    </Hierarchy>\n" + "  </Dimension>\n" + "  <Dimension name='Store'>\n" + "    <Hierarchy hasAll='true' primaryKey='store_id'>\n" + "      <Table name='store'/>\n" + "      <Level name='Store Country' column='store_country' uniqueMembers='true'/>\n" + "      <Level name='Store State' column='store_state' uniqueMembers='true'/>\n" + "      <Level name='Store City' column='store_city' uniqueMembers='false'/>\n" + "      <Level name='Store Name' column='store_id' type='Numeric' nameColumn='store_name' uniqueMembers='false'/>\n" + "    </Hierarchy>\n" + "  </Dimension>\n");
    assertQuerySqlOrNot(testContext, mdx, badPatterns, true, true, true);
    assertQuerySqlOrNot(testContext, mdx, goodPatterns, false, true, true);
    testContext.assertQueryReturns(mdx, "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[*FORMATTED_MEASURE_0]}\n" + "Axis #2:\n" + "{[Store].[USA].[CA].[San Francisco].[Store 14]}\n" + "{[Store].[USA].[OR].[Portland].[Store 11]}\n" + "Row #0: 22,478\n" + "Row #1: 20,319\n");
}

Example 49 with SqlPattern

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

the class FilterTest method testNotInMultiLevelMemberConstraintNonNullParent.

/**
 * Test that null members are included when the filter excludes members
 * that contain multiple levels, but none being null.
 */
public void testNotInMultiLevelMemberConstraintNonNullParent() {
    if (MondrianProperties.instance().ReadAggregates.get()) {
        // agg tables.
        return;
    }
    String query = "With " + "Set [*NATIVE_CJ_SET] as 'NonEmptyCrossJoin([*BASE_MEMBERS_Customers],[*BASE_MEMBERS_Quarters])' " + "Set [*BASE_MEMBERS_Customers] as 'Filter([Customers].[Country].Members, [Customers].CurrentMember In {[Customers].[All Customers].[USA]})' " + "Set [*BASE_MEMBERS_Quarters] as 'Filter([Time].[Quarter].Members, " + "[Time].currentMember not in {[Time].[1997].[Q1], [Time].[1998].[Q3]})' " + "Set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Customers].currentMember,[Time].currentMember)})' " + "Set [*ORDERED_CJ_ROW_AXIS] as 'Order([*CJ_ROW_AXIS], [Time].currentmember.OrderKey, BASC)' " + "Select " + "{[Measures].[Customer Count]} on columns, " + "Non Empty [*ORDERED_CJ_ROW_AXIS] on rows " + "From [Sales]";
    String necjSqlDerby = "select \"customer\".\"country\", \"time_by_day\".\"the_year\", " + "\"time_by_day\".\"quarter\" from \"customer\" as \"customer\", " + "\"sales_fact_1997\" as \"sales_fact_1997\", \"time_by_day\" as " + "\"time_by_day\" where \"sales_fact_1997\".\"customer_id\" = " + "\"customer\".\"customer_id\" and \"sales_fact_1997\".\"time_id\" = " + "\"time_by_day\".\"time_id\" and (\"customer\".\"country\" = 'USA') and " + "(not ((\"time_by_day\".\"the_year\" = 1997 and \"time_by_day\".\"quarter\" " + "= 'Q1') or (\"time_by_day\".\"the_year\" = 1998 and " + "\"time_by_day\".\"quarter\" = 'Q3')) or ((\"time_by_day\".\"quarter\" is " + "null or \"time_by_day\".\"the_year\" is null) and " + "not((\"time_by_day\".\"the_year\" = 1997 and \"time_by_day\".\"quarter\" " + "= 'Q1') or (\"time_by_day\".\"the_year\" = 1998 and " + "\"time_by_day\".\"quarter\" = 'Q3')))) group by \"customer\".\"country\", " + "\"time_by_day\".\"the_year\", \"time_by_day\".\"quarter\" " + "order by CASE WHEN \"customer\".\"country\" IS NULL THEN 1 ELSE 0 END, \"customer\".\"country\" ASC, CASE WHEN \"time_by_day\".\"the_year\" IS NULL THEN 1 ELSE 0 END, \"time_by_day\".\"the_year\" ASC, CASE WHEN \"time_by_day\".\"quarter\" IS NULL THEN 1 ELSE 0 END, \"time_by_day\".\"quarter\" ASC";
    String necjSqlMySql = "select `customer`.`country` as `c0`, `time_by_day`.`the_year` as `c1`, " + "`time_by_day`.`quarter` as `c2` from `customer` as `customer`, " + "`sales_fact_1997` as `sales_fact_1997`, `time_by_day` as `time_by_day` " + "where `sales_fact_1997`.`customer_id` = `customer`.`customer_id` " + "and `sales_fact_1997`.`time_id` = `time_by_day`.`time_id` and " + "(`customer`.`country` = 'USA') and " + "(not ((`time_by_day`.`quarter`, `time_by_day`.`the_year`) in " + "(('Q1', 1997), ('Q3', 1998))) or (`time_by_day`.`quarter` is null or " + "`time_by_day`.`the_year` is null)) " + "group by `customer`.`country`, `time_by_day`.`the_year`, `time_by_day`.`quarter` " + (TestContext.instance().getDialect().requiresOrderByAlias() ? "order by ISNULL(`c0`) ASC, " + "`c0` ASC, ISNULL(`c1`) ASC, " + "`c1` ASC, ISNULL(`c2`) ASC, " + "`c2` ASC" : "order by ISNULL(`customer`.`country`) ASC, " + "`customer`.`country` ASC, ISNULL(`time_by_day`.`the_year`) ASC, " + "`time_by_day`.`the_year` ASC, ISNULL(`time_by_day`.`quarter`) ASC, " + "`time_by_day`.`quarter` ASC");
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.DERBY, necjSqlDerby, necjSqlDerby), new SqlPattern(Dialect.DatabaseProduct.MYSQL, necjSqlMySql, necjSqlMySql) };
    assertQuerySql(query, patterns);
}

Example 50 with SqlPattern

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

the class FilterTest method testNotInMultiLevelMemberConstraintNonNullSameParent.

/**
 * Test that null members are included when the filter excludes members
 * that contain multiple levels, but none being null.  The members have
 * the same parent.
 */
public void testNotInMultiLevelMemberConstraintNonNullSameParent() {
    if (MondrianProperties.instance().ReadAggregates.get()) {
        // agg tables.
        return;
    }
    String query = "With " + "Set [*NATIVE_CJ_SET] as 'NonEmptyCrossJoin([*BASE_MEMBERS_Customers],[*BASE_MEMBERS_Quarters])' " + "Set [*BASE_MEMBERS_Customers] as 'Filter([Customers].[Country].Members, [Customers].CurrentMember In {[Customers].[All Customers].[USA]})' " + "Set [*BASE_MEMBERS_Quarters] as 'Filter([Time].[Quarter].Members, " + "[Time].currentMember not in {[Time].[1997].[Q1], [Time].[1997].[Q3]})' " + "Set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Customers].currentMember,[Time].currentMember)})' " + "Set [*ORDERED_CJ_ROW_AXIS] as 'Order([*CJ_ROW_AXIS], [Time].currentmember.OrderKey, BASC)' " + "Select " + "{[Measures].[Customer Count]} on columns, " + "Non Empty [*ORDERED_CJ_ROW_AXIS] on rows " + "From [Sales]";
    String necjSqlDerby = "select \"customer\".\"country\", \"time_by_day\".\"the_year\", " + "\"time_by_day\".\"quarter\" from \"customer\" as \"customer\", " + "\"sales_fact_1997\" as \"sales_fact_1997\", \"time_by_day\" as " + "\"time_by_day\" where \"sales_fact_1997\".\"customer_id\" = " + "\"customer\".\"customer_id\" and \"sales_fact_1997\".\"time_id\" = " + "\"time_by_day\".\"time_id\" and (\"customer\".\"country\" = 'USA') and " + "((not (\"time_by_day\".\"quarter\" in ('Q1', 'Q3')) or " + "(\"time_by_day\".\"quarter\" is null)) or (not " + "(\"time_by_day\".\"the_year\" = 1997) or (\"time_by_day\".\"the_year\" is " + "null))) group by \"customer\".\"country\", \"time_by_day\".\"the_year\", " + "\"time_by_day\".\"quarter\" " + "order by CASE WHEN \"customer\".\"country\" IS NULL THEN 1 ELSE 0 END, \"customer\".\"country\" ASC, CASE WHEN \"time_by_day\".\"the_year\" IS NULL THEN 1 ELSE 0 END, \"time_by_day\".\"the_year\" ASC, CASE WHEN \"time_by_day\".\"quarter\" IS NULL THEN 1 ELSE 0 END, \"time_by_day\".\"quarter\" ASC";
    String necjSqlMySql = "select `customer`.`country` as `c0`, `time_by_day`.`the_year` as " + "`c1`, `time_by_day`.`quarter` as `c2` from `customer` as " + "`customer`, `sales_fact_1997` as `sales_fact_1997`, `time_by_day` " + "as `time_by_day` where `sales_fact_1997`.`customer_id` = " + "`customer`.`customer_id` and `sales_fact_1997`.`time_id` = " + "`time_by_day`.`time_id` and (`customer`.`country` = 'USA') and " + "((not (`time_by_day`.`quarter` in ('Q1', 'Q3')) or " + "(`time_by_day`.`quarter` is null)) or (not " + "(`time_by_day`.`the_year` = 1997) or (`time_by_day`.`the_year` " + "is null))) group by `customer`.`country`, `time_by_day`.`the_year`, `time_by_day`.`quarter` " + (TestContext.instance().getDialect().requiresOrderByAlias() ? "order by ISNULL(`c0`) ASC, " + "`c0` ASC, ISNULL(`c1`) ASC, " + "`c1` ASC, ISNULL(`c2`) ASC, " + "`c2` ASC" : "order by ISNULL(`customer`.`country`) ASC, " + "`customer`.`country` ASC, ISNULL(`time_by_day`.`the_year`) ASC, " + "`time_by_day`.`the_year` ASC, ISNULL(`time_by_day`.`quarter`) ASC, " + "`time_by_day`.`quarter` ASC");
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.DERBY, necjSqlDerby, necjSqlDerby), new SqlPattern(Dialect.DatabaseProduct.MYSQL, necjSqlMySql, necjSqlMySql) };
    assertQuerySql(query, patterns);
}