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

Example 31 with SqlPattern

use of mondrian.test.SqlPattern 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 32 with SqlPattern

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

the class NonEmptyTest method testNativeCrossjoinWillExpandFirstLastChild.

public void testNativeCrossjoinWillExpandFirstLastChild() {
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    String mdx = "select " + "non empty Crossjoin({[Gender].firstChild,[Gender].lastChild},{[Measures].[Unit Sales]}) on 0," + "non empty Crossjoin({[Time].[1997]},{[Promotions].[All Promotions].[Bag Stuffers],[Promotions].[All Promotions].[Best Savings]}) on 1" + " from [Warehouse and Sales]";
    final SqlPattern pattern = new SqlPattern(Dialect.DatabaseProduct.ORACLE, propSaver.properties.UseAggregates.get() ? "select\n" + "    \"agg_c_14_sales_fact_1997\".\"the_year\" as \"c0\",\n" + "    \"promotion\".\"promotion_name\" as \"c1\"\n" + "from\n" + "    \"agg_c_14_sales_fact_1997\" \"agg_c_14_sales_fact_1997\",\n" + "    \"promotion\" \"promotion\",\n" + "    \"customer\" \"customer\"\n" + "where\n" + "    \"agg_c_14_sales_fact_1997\".\"promotion_id\" = \"promotion\".\"promotion_id\"\n" + "and\n" + "    \"agg_c_14_sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\"\n" + "and\n" + "    (\"customer\".\"gender\" in ('F', 'M'))\n" + "and\n" + "    (\"agg_c_14_sales_fact_1997\".\"the_year\" = 1997)\n" + "and\n" + "    (\"promotion\".\"promotion_name\" in ('Bag Stuffers', 'Best Savings'))\n" + "group by\n" + "    \"agg_c_14_sales_fact_1997\".\"the_year\",\n" + "    \"promotion\".\"promotion_name\"\n" + "order by\n" + "    \"agg_c_14_sales_fact_1997\".\"the_year\" ASC NULLS LAST,\n" + "    \"promotion\".\"promotion_name\" ASC NULLS LAST" : "select\n" + "    \"time_by_day\".\"the_year\" as \"c0\",\n" + "    \"promotion\".\"promotion_name\" as \"c1\"\n" + "from\n" + "    \"time_by_day\" \"time_by_day\",\n" + "    \"sales_fact_1997\" \"sales_fact_1997\",\n" + "    \"promotion\" \"promotion\",\n" + "    \"customer\" \"customer\"\n" + "where\n" + "    \"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\"\n" + "and\n" + "    \"sales_fact_1997\".\"promotion_id\" = \"promotion\".\"promotion_id\"\n" + "and\n" + "    \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\"\n" + "and\n" + "    (\"customer\".\"gender\" in ('F', 'M'))\n" + "and\n" + "    (\"time_by_day\".\"the_year\" = 1997)\n" + "and\n" + "    (\"promotion\".\"promotion_name\" in ('Bag Stuffers', 'Best Savings'))\n" + "group by\n" + "    \"time_by_day\".\"the_year\",\n" + "    \"promotion\".\"promotion_name\"\n" + "order by\n" + "    \"time_by_day\".\"the_year\" ASC NULLS LAST,\n" + "    \"promotion\".\"promotion_name\" ASC NULLS LAST", 611);
    assertQuerySql(mdx, new SqlPattern[] { pattern });
}

Example 33 with SqlPattern

use of mondrian.test.SqlPattern 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);
}

Example 34 with SqlPattern

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

the class NonEmptyTest method testNativeCrossjoinWillConstrainUsingArgsFromAllAxes.

public void testNativeCrossjoinWillConstrainUsingArgsFromAllAxes() {
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    String mdx = "select " + "non empty Crossjoin({[Gender].[Gender].[F]},{[Measures].[Unit Sales]}) on 0," + "non empty Crossjoin({[Time].[1997]},{[Promotions].[All Promotions].[Bag Stuffers],[Promotions].[All Promotions].[Best Savings]}) on 1" + " from [Warehouse and Sales]";
    SqlPattern oraclePattern = new SqlPattern(Dialect.DatabaseProduct.ORACLE, propSaver.properties.UseAggregates.get() ? "select\n" + "    \"agg_c_14_sales_fact_1997\".\"the_year\" as \"c0\",\n" + "    \"promotion\".\"promotion_name\" as \"c1\"\n" + "from\n" + "    \"agg_c_14_sales_fact_1997\" \"agg_c_14_sales_fact_1997\",\n" + "    \"promotion\" \"promotion\",\n" + "    \"customer\" \"customer\"\n" + "where\n" + "    \"agg_c_14_sales_fact_1997\".\"promotion_id\" = \"promotion\".\"promotion_id\"\n" + "and\n" + "    \"agg_c_14_sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\"\n" + "and\n" + "    (\"customer\".\"gender\" = 'F')\n" + "and\n" + "    (\"agg_c_14_sales_fact_1997\".\"the_year\" = 1997)\n" + "and\n" + "    (\"promotion\".\"promotion_name\" in ('Bag Stuffers', 'Best Savings'))\n" + "group by\n" + "    \"agg_c_14_sales_fact_1997\".\"the_year\",\n" + "    \"promotion\".\"promotion_name\"\n" + "order by\n" + "    \"agg_c_14_sales_fact_1997\".\"the_year\" ASC NULLS LAST,\n" + "    \"promotion\".\"promotion_name\" ASC NULLS LAST" : "select\n" + "    \"time_by_day\".\"the_year\" as \"c0\",\n" + "    \"promotion\".\"promotion_name\" as \"c1\"\n" + "from\n" + "    \"time_by_day\" \"time_by_day\",\n" + "    \"sales_fact_1997\" \"sales_fact_1997\",\n" + "    \"promotion\" \"promotion\",\n" + "    \"customer\" \"customer\"\n" + "where\n" + "    \"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\"\n" + "and\n" + "    \"sales_fact_1997\".\"promotion_id\" = \"promotion\".\"promotion_id\"\n" + "and\n" + "    \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\"\n" + "and\n" + "    (\"customer\".\"gender\" = 'F')\n" + "and\n" + "    (\"time_by_day\".\"the_year\" = 1997)\n" + "and\n" + "    (\"promotion\".\"promotion_name\" in ('Bag Stuffers', 'Best Savings'))\n" + "group by\n" + "    \"time_by_day\".\"the_year\",\n" + "    \"promotion\".\"promotion_name\"\n" + "order by\n" + "    \"time_by_day\".\"the_year\" ASC NULLS LAST,\n" + "    \"promotion\".\"promotion_name\" ASC NULLS LAST", 611);
    assertQuerySql(mdx, new SqlPattern[] { oraclePattern });
}

Example 35 with SqlPattern

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

the class NonEmptyTest method testNonUniformNestedMeasureConstraintsGetOptimized.

public void testNonUniformNestedMeasureConstraintsGetOptimized() {
    if (MondrianProperties.instance().UseAggregates.get()) {
        // the aggregate table doesn't include member properties.
        return;
    }
    String mdx = "with member [Measures].[unit sales Male] as '([Measures].[Unit Sales],[Gender].[Gender].[M])' " + "member [Measures].[unit sales Female] as '([Measures].[Unit Sales],[Gender].[Gender].[F])' " + "member [Measures].[unit sales Male Married] as '([Measures].[unit sales Male],[Marital Status].[Marital Status].[M])' " + "select " + "non empty {[Measures].[unit sales Male Married],[Measures].[unit sales Female]} on 0, " + "non empty [Customers].[name].members on 1 " + "from Sales";
    final SqlPattern pattern = new SqlPattern(Dialect.DatabaseProduct.ORACLE, "select \"customer\".\"country\" as \"c0\", " + "\"customer\".\"state_province\" as \"c1\", " + "\"customer\".\"city\" as \"c2\", " + "\"customer\".\"customer_id\" as \"c3\", " + "\"fname\" || ' ' || \"lname\" as \"c4\", " + "\"fname\" || ' ' || \"lname\" as \"c5\", " + "\"customer\".\"gender\" as \"c6\", " + "\"customer\".\"marital_status\" as \"c7\", " + "\"customer\".\"education\" as \"c8\", " + "\"customer\".\"yearly_income\" as \"c9\" " + "from \"customer\" \"customer\", \"sales_fact_1997\" \"sales_fact_1997\" " + "where \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\" " + "and (\"customer\".\"gender\" in ('M', 'F')) " + "group by \"customer\".\"country\", " + "\"customer\".\"state_province\", " + "\"customer\".\"city\", " + "\"customer\".\"customer_id\", " + "\"fname\" || ' ' || \"lname\", " + "\"customer\".\"gender\", " + "\"customer\".\"marital_status\", " + "\"customer\".\"education\", " + "\"customer\".\"yearly_income\" " + "order by \"customer\".\"country\" ASC NULLS LAST," + " \"customer\".\"state_province\" ASC NULLS LAST," + " \"customer\".\"city\" ASC NULLS LAST, " + "\"fname\" || ' ' || \"lname\" ASC NULLS LAST", 852);
    assertQuerySql(mdx, new SqlPattern[] { pattern });
}