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

Example 41 with TestContext

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

the class NonEmptyTest method testMondrian1133WithAggs.

/**
 * Test case for
 * <a href="http://jira.pentaho.com/browse/MONDRIAN-1133">MONDRIAN-1133</a>
 *
 * <p>RolapNativeFilter would force the join to the fact table.
 * Some queries don't need to be joined to it and gain in performance.
 *
 * <p>This one is for agg tables turned on.
 */
public void testMondrian1133WithAggs() {
    propSaver.set(propSaver.properties.UseAggregates, true);
    propSaver.set(propSaver.properties.ReadAggregates, true);
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    final String schema = "<?xml version=\"1.0\"?>\n" + "<Schema name=\"custom\">\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_name\" uniqueMembers=\"true\">\n" + "      </Level>\n" + "    </Hierarchy>\n" + "  </Dimension>\n" + "  <Dimension name=\"Time\" type=\"TimeDimension\">\n" + "    <Hierarchy hasAll=\"true\" primaryKey=\"time_id\">\n" + "      <Table name=\"time_by_day\"/>\n" + "      <Level name=\"Year\" column=\"the_year\" type=\"Numeric\" uniqueMembers=\"true\"\n" + "          levelType=\"TimeYears\"/>\n" + "      <Level name=\"Quarter\" column=\"quarter\" uniqueMembers=\"false\"\n" + "          levelType=\"TimeQuarters\"/>\n" + "      <Level name=\"Month\" column=\"month_of_year\" uniqueMembers=\"false\" type=\"Numeric\"\n" + "          levelType=\"TimeMonths\"/>\n" + "    </Hierarchy>\n" + "  </Dimension>\n" + "  <Cube name=\"Sales1\" defaultMeasure=\"Unit Sales\">\n" + "    <Table name=\"sales_fact_1997\">\n" + "        <AggExclude name=\"agg_c_special_sales_fact_1997\" />" + "    </Table>\n" + "    <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\"/>\n" + "    <DimensionUsage name=\"Time\" source=\"Time\" foreignKey=\"time_id\"/>\n" + "    <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\"/>\n" + "  </Cube>\n" + "<Role name=\"Role1\" >\n" + "  <SchemaGrant access=\"none\">\n" + "    <CubeGrant cube=\"Sales1\" access=\"all\">\n" + "      <HierarchyGrant hierarchy=\"[Time]\" access=\"custom\" rollupPolicy=\"partial\">\n" + "        <MemberGrant member=\"[Time].[Year].[1997]\" access=\"all\"/>\n" + "      </HierarchyGrant>\n" + "    </CubeGrant>\n" + "  </SchemaGrant>\n" + "</Role> \n" + "</Schema>\n";
    final String query = "With\n" + "Set [*BASE_MEMBERS_Product] as 'Filter([Store].[Store State].Members,[Store].CurrentMember.Caption Matches (\"(?i).*CA.*\"))'\n" + "Select\n" + "[*BASE_MEMBERS_Product] on columns\n" + "From [Sales1] \n";
    final String nonEmptyQuery = "Select\n" + "NON EMPTY Filter([Store].[Store State].Members,[Store].CurrentMember.Caption Matches (\"(?i).*CA.*\")) on columns\n" + "From [Sales1] \n";
    final String mysql = "select\n" + "    `store`.`store_country` as `c0`,\n" + "    `store`.`store_state` as `c1`\n" + "from\n" + "    `store` as `store`\n" + "group by\n" + "    `store`.`store_country`,\n" + "    `store`.`store_state`\n" + "having\n" + "    c1 IS NOT NULL AND UPPER(c1) REGEXP '.*CA.*'\n" + "order by\n" + (TestContext.instance().getDialect().requiresOrderByAlias() ? "    ISNULL(`c0`) ASC, `c0` ASC,\n" + "    ISNULL(`c1`) ASC, `c1` ASC" : "    ISNULL(`store`.`store_country`) ASC, `store`.`store_country` ASC,\n" + "    ISNULL(`store`.`store_state`) ASC, `store`.`store_state` ASC");
    final String mysqlWithFactJoin = "select\n" + "    `store`.`store_country` as `c0`,\n" + "    `store`.`store_state` as `c1`\n" + "from\n" + "    `store` as `store`,\n" + "    `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`\n" + "where\n" + "    `agg_c_14_sales_fact_1997`.`store_id` = `store`.`store_id`\n" + "and\n" + "    `agg_c_14_sales_fact_1997`.`the_year` = 1997\n" + "group by\n" + "    `store`.`store_country`,\n" + "    `store`.`store_state`\n" + "having\n" + "    c1 IS NOT NULL AND UPPER(c1) REGEXP '.*CA.*'\n" + "order by\n" + (TestContext.instance().getDialect().requiresOrderByAlias() ? "    ISNULL(`c0`) ASC, `c0` ASC,\n" + "    ISNULL(`c1`) ASC, `c1` ASC" : "    ISNULL(`store`.`store_country`) ASC, `store`.`store_country` ASC,\n" + "    ISNULL(`store`.`store_state`) ASC, `store`.`store_state` ASC");
    final String oracle = "select\n" + "    \"store\".\"store_country\" as \"c0\",\n" + "    \"store\".\"store_state\" as \"c1\"\n" + "from\n" + "    \"store\" \"store\"\n" + "group by\n" + "    \"store\".\"store_country\",\n" + "    \"store\".\"store_state\"\n" + "having\n" + "    \"store\".\"store_state\" IS NOT NULL AND REGEXP_LIKE(\"store\".\"store_state\", '.*CA.*', 'i')\n" + "order by\n" + "    \"store\".\"store_country\" ASC NULLS LAST,\n" + "    \"store\".\"store_state\" ASC NULLS LAST";
    final String oracleWithFactJoin = "select\n" + "    \"store\".\"store_country\" as \"c0\",\n" + "    \"store\".\"store_state\" as \"c1\"\n" + "from\n" + "    \"store\" \"store\",\n" + "    \"agg_c_14_sales_fact_1997\" \"agg_c_14_sales_fact_1997\"\n" + "where\n" + "    \"agg_c_14_sales_fact_1997\".\"store_id\" = \"store\".\"store_id\"\n" + "and\n" + "    \"agg_c_14_sales_fact_1997\".\"the_year\" = 1997\n" + "group by\n" + "    \"store\".\"store_country\",\n" + "    \"store\".\"store_state\"\n" + "having\n" + "    \"store\".\"store_state\" IS NOT NULL AND REGEXP_LIKE(\"store\".\"store_state\", '.*CA.*', 'i')\n" + "order by\n" + "    \"store\".\"store_country\" ASC NULLS LAST,\n" + "    \"store\".\"store_state\" ASC NULLS LAST";
    final SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, mysql, mysql), new SqlPattern(Dialect.DatabaseProduct.ORACLE, oracle, oracle) };
    final SqlPattern[] patternsWithFactJoin = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, mysqlWithFactJoin, mysqlWithFactJoin), new SqlPattern(Dialect.DatabaseProduct.ORACLE, oracleWithFactJoin, oracleWithFactJoin) };
    final TestContext context = TestContext.instance().withSchema(schema);
    // The filter condition does not require a join to the fact table.
    assertQuerySql(context, query, patterns);
    assertQuerySql(context.withRole("Role1"), query, patterns);
    // in a non-empty context where a role is in effect, the query
    // will pessimistically join the fact table and apply the
    // constraint, since the filter condition could be influenced by
    // role limitations.
    assertQuerySql(context.withRole("Role1"), nonEmptyQuery, patternsWithFactJoin);
}

Example 42 with TestContext

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

the class NonEmptyTest method testBugMondrian897DoubleNamedSetDefinitions.

/**
 * Test case for <a href="http://jira.pentaho.com/browse/MONDRIAN-897">
 * MONDRIAN-897, "ClassCastException in
 * CrossJoinArgFactory.allArgsCheapToExpand when defining a NamedSet as
 * another NamedSet"</a>.
 */
public void testBugMondrian897DoubleNamedSetDefinitions() {
    TestContext ctx = getTestContext();
    ctx.assertQueryReturns("WITH SET [CustomerSet] as {[Customers].[Canada].[BC].[Burnaby].[Alexandra Wellington], [Customers].[USA].[WA].[Tacoma].[Eric Coleman]} " + "SET [InterestingCustomers] as [CustomerSet] " + "SET [TimeRange] as {[Time].[1998].[Q1], [Time].[1998].[Q2]} " + "SELECT {[Measures].[Store Sales]} ON COLUMNS, " + "CrossJoin([InterestingCustomers], [TimeRange]) ON ROWS " + "FROM [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Store Sales]}\n" + "Axis #2:\n" + "{[Customers].[Canada].[BC].[Burnaby].[Alexandra Wellington], [Time].[1998].[Q1]}\n" + "{[Customers].[Canada].[BC].[Burnaby].[Alexandra Wellington], [Time].[1998].[Q2]}\n" + "{[Customers].[USA].[WA].[Tacoma].[Eric Coleman], [Time].[1998].[Q1]}\n" + "{[Customers].[USA].[WA].[Tacoma].[Eric Coleman], [Time].[1998].[Q2]}\n" + "Row #0: \n" + "Row #1: \n" + "Row #2: \n" + "Row #3: \n");
}

Example 43 with TestContext

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

the class NonEmptyTest method testBugMondrian412.

/**
 * Test case for <a href="http://jira.pentaho.com/browse/MONDRIAN-412">
 * MONDRIAN-412, "NON EMPTY and Filter() breaking aggregate
 * calculations"</a>.
 */
public void testBugMondrian412() {
    TestContext ctx = getTestContext();
    ctx.assertQueryReturns("with member [Measures].[AvgRevenue] as 'Avg([Store].[Store Name].Members, [Measures].[Store Sales])' " + "select NON EMPTY {[Measures].[Store Sales], [Measures].[AvgRevenue]} ON COLUMNS, " + "NON EMPTY Filter([Store].[Store Name].Members, ([Measures].[AvgRevenue] < [Measures].[Store Sales])) ON ROWS " + "from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Store Sales]}\n" + "{[Measures].[AvgRevenue]}\n" + "Axis #2:\n" + "{[Store].[USA].[CA].[Beverly Hills].[Store 6]}\n" + "{[Store].[USA].[CA].[Los Angeles].[Store 7]}\n" + "{[Store].[USA].[CA].[San Diego].[Store 24]}\n" + "{[Store].[USA].[OR].[Portland].[Store 11]}\n" + "{[Store].[USA].[OR].[Salem].[Store 13]}\n" + "{[Store].[USA].[WA].[Bremerton].[Store 3]}\n" + "{[Store].[USA].[WA].[Seattle].[Store 15]}\n" + "{[Store].[USA].[WA].[Spokane].[Store 16]}\n" + "{[Store].[USA].[WA].[Tacoma].[Store 17]}\n" + "Row #0: 45,750.24\n" + "Row #0: 43,479.86\n" + "Row #1: 54,545.28\n" + "Row #1: 43,479.86\n" + "Row #2: 54,431.14\n" + "Row #2: 43,479.86\n" + "Row #3: 55,058.79\n" + "Row #3: 43,479.86\n" + "Row #4: 87,218.28\n" + "Row #4: 43,479.86\n" + "Row #5: 52,896.30\n" + "Row #5: 43,479.86\n" + "Row #6: 52,644.07\n" + "Row #6: 43,479.86\n" + "Row #7: 49,634.46\n" + "Row #7: 43,479.86\n" + "Row #8: 74,843.96\n" + "Row #8: 43,479.86\n");
}

Example 44 with TestContext

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

the class NonEmptyTest method testBug1515302.

public void testBug1515302() {
    TestContext ctx = TestContext.instance().create(null, "<Cube name=\"Bug1515302\"> \n" + "  <Table name=\"sales_fact_1997\"/> \n" + "  <Dimension name=\"Promotions\" foreignKey=\"promotion_id\"> \n" + "    <Hierarchy hasAll=\"false\" primaryKey=\"promotion_id\"> \n" + "      <Table name=\"promotion\"/> \n" + "      <Level name=\"Promotion Name\" column=\"promotion_name\" uniqueMembers=\"true\"/> \n" + "    </Hierarchy> \n" + "  </Dimension> \n" + "  <Dimension name=\"Customers\" foreignKey=\"customer_id\"> \n" + "    <Hierarchy hasAll=\"true\" allMemberName=\"All Customers\" primaryKey=\"customer_id\"> \n" + "      <Table name=\"customer\"/> \n" + "      <Level name=\"Country\" column=\"country\" uniqueMembers=\"true\"/> \n" + "      <Level name=\"State Province\" column=\"state_province\" uniqueMembers=\"true\"/> \n" + "      <Level name=\"City\" column=\"city\" uniqueMembers=\"false\"/> \n" + "      <Level name=\"Name\" column=\"customer_id\" type=\"Numeric\" uniqueMembers=\"true\"/> \n" + "    </Hierarchy> \n" + "  </Dimension> \n" + "  <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"/> \n" + "</Cube> \n", null, null, null, null);
    ctx.assertQueryReturns("select {[Measures].[Unit Sales]} on columns, " + "non empty crossjoin({[Promotions].[Big Promo]}, " + "Descendants([Customers].[USA], [City], " + "SELF_AND_BEFORE)) on rows " + "from [Bug1515302]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Promotions].[Big Promo], [Customers].[USA]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Anacortes]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Ballard]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Bellingham]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Burien]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Everett]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Issaquah]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Kirkland]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Lynnwood]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Marysville]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Olympia]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Puyallup]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Redmond]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Renton]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Seattle]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Sedro Woolley]}\n" + "{[Promotions].[Big Promo], [Customers].[USA].[WA].[Tacoma]}\n" + "Row #0: 1,789\n" + "Row #1: 1,789\n" + "Row #2: 20\n" + "Row #3: 35\n" + "Row #4: 15\n" + "Row #5: 18\n" + "Row #6: 60\n" + "Row #7: 42\n" + "Row #8: 36\n" + "Row #9: 79\n" + "Row #10: 58\n" + "Row #11: 520\n" + "Row #12: 438\n" + "Row #13: 14\n" + "Row #14: 20\n" + "Row #15: 65\n" + "Row #16: 3\n" + "Row #17: 366\n");
}

Example 45 with TestContext

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

the class NonEmptyTest method testMondrian1133.

/**
 * Test case for
 * <a href="http://jira.pentaho.com/browse/MONDRIAN-1133">MONDRIAN-1133</a>
 *
 * <p>RolapNativeFilter would force the join to the fact table.
 * Some queries don't need to be joined to it and gain in performance.
 */
public void testMondrian1133() {
    propSaver.set(propSaver.properties.UseAggregates, false);
    propSaver.set(propSaver.properties.ReadAggregates, false);
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    final String schema = "<?xml version=\"1.0\"?>\n" + "<Schema name=\"custom\">\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_name\" uniqueMembers=\"true\">\n" + "      </Level>\n" + "    </Hierarchy>\n" + "  </Dimension>\n" + "  <Dimension name=\"Time\" type=\"TimeDimension\">\n" + "    <Hierarchy hasAll=\"true\" primaryKey=\"time_id\">\n" + "      <Table name=\"time_by_day\"/>\n" + "      <Level name=\"Year\" column=\"the_year\" type=\"Numeric\" uniqueMembers=\"true\"\n" + "          levelType=\"TimeYears\"/>\n" + "      <Level name=\"Quarter\" column=\"quarter\" uniqueMembers=\"false\"\n" + "          levelType=\"TimeQuarters\"/>\n" + "      <Level name=\"Month\" column=\"month_of_year\" uniqueMembers=\"false\" type=\"Numeric\"\n" + "          levelType=\"TimeMonths\"/>\n" + "    </Hierarchy>\n" + "  </Dimension>\n" + "  <Cube name=\"Sales1\" defaultMeasure=\"Unit Sales\">\n" + "    <Table name=\"sales_fact_1997\">\n" + "        <AggExclude name=\"agg_c_special_sales_fact_1997\" />" + "    </Table>\n" + "    <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\"/>\n" + "    <DimensionUsage name=\"Time\" source=\"Time\" foreignKey=\"time_id\"/>\n" + "    <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\"/>\n" + "  </Cube>\n" + "<Role name=\"Role1\">\n" + "  <SchemaGrant access=\"none\">\n" + "    <CubeGrant cube=\"Sales1\" access=\"all\">\n" + "      <HierarchyGrant hierarchy=\"[Time]\" access=\"custom\" rollupPolicy=\"partial\">\n" + "        <MemberGrant member=\"[Time].[Year].[1997]\" access=\"all\"/>\n" + "      </HierarchyGrant>\n" + "    </CubeGrant>\n" + "  </SchemaGrant>\n" + "</Role> \n" + "</Schema>\n";
    final String query = "With\n" + "Set [*BASE_MEMBERS_Product] as 'Filter([Store].[Store State].Members,[Store].CurrentMember.Caption Matches (\"(?i).*CA.*\"))'\n" + "Select\n" + "[*BASE_MEMBERS_Product] on columns\n" + "From [Sales1] \n";
    final String nonEmptyQuery = "Select\n" + "NON EMPTY Filter([Store].[Store State].Members,[Store].CurrentMember.Caption Matches (\"(?i).*CA.*\")) on columns\n" + "From [Sales1] \n";
    final String mysql = "select\n" + "    `store`.`store_country` as `c0`,\n" + "    `store`.`store_state` as `c1`\n" + "from\n" + "    `store` as `store`\n" + "group by\n" + "    `store`.`store_country`,\n" + "    `store`.`store_state`\n" + "having\n" + "    c1 IS NOT NULL AND UPPER(c1) REGEXP '.*CA.*'\n" + "order by\n" + (TestContext.instance().getDialect().requiresOrderByAlias() ? "    ISNULL(`c0`) ASC, `c0` ASC,\n" + "    ISNULL(`c1`) ASC, `c1` ASC" : "    ISNULL(`store`.`store_country`) ASC, `store`.`store_country` ASC,\n" + "    ISNULL(`store`.`store_state`) ASC, `store`.`store_state` ASC");
    final String mysqlWithFactJoin = "select\n" + "    `store`.`store_country` as `c0`,\n" + "    `store`.`store_state` as `c1`\n" + "from\n" + "    `store` as `store`,\n" + "    `sales_fact_1997` as `sales_fact_1997`,\n" + "    `time_by_day` as `time_by_day`\n" + "where\n" + "    `sales_fact_1997`.`store_id` = `store`.`store_id`\n" + "and\n" + "    `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`\n" + "and\n" + "    `time_by_day`.`the_year` = 1997\n" + "group by\n" + "    `store`.`store_country`,\n" + "    `store`.`store_state`\n" + "having\n" + "    c1 IS NOT NULL AND UPPER(c1) REGEXP '.*CA.*'\n" + "order by\n" + (TestContext.instance().getDialect().requiresOrderByAlias() ? "    ISNULL(`c0`) ASC, `c0` ASC,\n" + "    ISNULL(`c1`) ASC, `c1` ASC" : "    ISNULL(`store`.`store_country`) ASC, `store`.`store_country` ASC,\n" + "    ISNULL(`store`.`store_state`) ASC, `store`.`store_state` ASC");
    final String oracle = "select\n" + "    \"store\".\"store_country\" as \"c0\",\n" + "    \"store\".\"store_state\" as \"c1\"\n" + "from\n" + "    \"store\" \"store\"\n" + "group by\n" + "    \"store\".\"store_country\",\n" + "    \"store\".\"store_state\"\n" + "having\n" + "    \"store\".\"store_state\" IS NOT NULL AND REGEXP_LIKE(\"store\".\"store_state\", '.*CA.*', 'i')\n" + "order by\n" + "    \"store\".\"store_country\" ASC NULLS LAST,\n" + "    \"store\".\"store_state\" ASC NULLS LAST";
    final String oracleWithFactJoin = "select\n" + "    \"store\".\"store_country\" as \"c0\",\n" + "    \"store\".\"store_state\" as \"c1\"\n" + "from\n" + "    \"store\" \"store\",\n" + "    \"sales_fact_1997\" \"sales_fact_1997\",\n" + "    \"time_by_day\" \"time_by_day\"\n" + "where\n" + "    \"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\"\n" + "and\n" + "    \"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\"\n" + "and\n" + "    \"time_by_day\".\"the_year\" = 1997\n" + "group by\n" + "    \"store\".\"store_country\",\n" + "    \"store\".\"store_state\"\n" + "having\n" + "    \"store\".\"store_state\" IS NOT NULL AND REGEXP_LIKE(\"store\".\"store_state\", '.*CA.*', 'i')\n" + "order by\n" + "    \"store\".\"store_country\" ASC NULLS LAST,\n" + "    \"store\".\"store_state\" ASC NULLS LAST";
    final SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, mysql, mysql), new SqlPattern(Dialect.DatabaseProduct.ORACLE, oracle, oracle) };
    final SqlPattern[] patternsWithFactJoin = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, mysqlWithFactJoin, mysqlWithFactJoin), new SqlPattern(Dialect.DatabaseProduct.ORACLE, oracleWithFactJoin, oracleWithFactJoin) };
    final TestContext context = TestContext.instance().withSchema(schema);
    // The filter condition does not require a join to the fact table.
    assertQuerySql(context, query, patterns);
    assertQuerySql(context.withRole("Role1"), query, patterns);
    // in a non-empty context where a role is in effect, the query
    // will pessimistically join the fact table and apply the
    // constraint, since the filter condition could be influenced by
    // role limitations.
    assertQuerySql(context.withRole("Role1"), nonEmptyQuery, patternsWithFactJoin);
}