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

Example 91 with SqlPattern

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

the class FastBatchingCellReaderTest method testDistinctCountBug1785406.

/**
 * Test case for bug 1785406 to fix "query already contains alias"
 * exception.
 *
 * <p>Note: 1785406 is a regression from checkin 9710. Code changes made in
 * 9710 is no longer in use (and removed). So this bug will not occur;
 * however, keeping the test case here to get some coverage for a query with
 * a slicer.
 */
public void testDistinctCountBug1785406() {
    String query = "With \n" + "Set [*BASE_MEMBERS_Product] as {[Product].[All Products].[Food].[Deli]}\n" + "Set [*BASE_MEMBERS_Store] as {[Store].[All Stores].[USA].[WA]}\n" + "Member [Product].[*CTX_MEMBER_SEL~SUM] As Aggregate([*BASE_MEMBERS_Product])\n" + "Select\n" + "{[Measures].[Customer Count]} on columns,\n" + "NonEmptyCrossJoin([*BASE_MEMBERS_Store],{([Product].[*CTX_MEMBER_SEL~SUM])})\n" + "on rows\n" + "From [Sales]\n" + "where ([Time].[1997])";
    assertQueryReturns(query, "Axis #0:\n" + "{[Time].[1997]}\n" + "Axis #1:\n" + "{[Measures].[Customer Count]}\n" + "Axis #2:\n" + "{[Store].[USA].[WA], [Product].[*CTX_MEMBER_SEL~SUM]}\n" + "Row #0: 889\n");
    String mysqlSql = "select " + "`store`.`store_state` as `c0`, `time_by_day`.`the_year` as `c1`, " + "count(distinct `sales_fact_1997`.`customer_id`) as `m0` " + "from " + "`store` as `store`, `sales_fact_1997` as `sales_fact_1997`, " + "`time_by_day` as `time_by_day`, `product_class` as `product_class`, " + "`product` as `product` " + "where " + "`sales_fact_1997`.`store_id` = `store`.`store_id` " + "and `store`.`store_state` = 'WA' " + "and `sales_fact_1997`.`time_id` = `time_by_day`.`time_id` " + "and `time_by_day`.`the_year` = 1997 " + "and `sales_fact_1997`.`product_id` = `product`.`product_id` " + "and `product`.`product_class_id` = `product_class`.`product_class_id` " + "and (`product_class`.`product_department` = 'Deli' " + "and `product_class`.`product_family` = 'Food') " + "group by `store`.`store_state`, `time_by_day`.`the_year`";
    String accessSql = "select `d0` as `c0`," + " `d1` as `c1`," + " count(`m0`) as `c2` " + "from (select distinct `store`.`store_state` as `d0`," + " `time_by_day`.`the_year` as `d1`," + " `sales_fact_1997`.`customer_id` as `m0` " + "from `store` as `store`," + " `sales_fact_1997` as `sales_fact_1997`," + " `time_by_day` as `time_by_day`," + " `product_class` as `product_class`," + " `product` as `product` " + "where `sales_fact_1997`.`store_id` = `store`.`store_id` " + "and `store`.`store_state` = 'WA' " + "and `sales_fact_1997`.`time_id` = `time_by_day`.`time_id` " + "and `time_by_day`.`the_year` = 1997 " + "and `sales_fact_1997`.`product_id` = `product`.`product_id` " + "and `product`.`product_class_id` = `product_class`.`product_class_id` " + "and (`product_class`.`product_department` = 'Deli' " + "and `product_class`.`product_family` = 'Food')) as `dummyname` " + "group by `d0`, `d1`";
    String derbySql = "select " + "\"store\".\"store_state\" as \"c0\", " + "\"time_by_day\".\"the_year\" as \"c1\", " + "count(distinct \"sales_fact_1997\".\"customer_id\") as \"m0\" " + "from " + "\"store\" as \"store\", " + "\"sales_fact_1997\" as \"sales_fact_1997\", " + "\"time_by_day\" as \"time_by_day\", " + "\"product_class\" as \"product_class\", " + "\"product\" as \"product\" " + "where " + "\"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" " + "and \"store\".\"store_state\" = 'WA' " + "and \"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" " + "and \"time_by_day\".\"the_year\" = 1997 " + "and \"sales_fact_1997\".\"product_id\" = \"product\".\"product_id\" " + "and \"product\".\"product_class_id\" = \"product_class\".\"product_class_id\" " + "and (\"product_class\".\"product_department\" = 'Deli' " + "and \"product_class\".\"product_family\" = 'Food') " + "group by \"store\".\"store_state\", \"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 92 with SqlPattern

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

the class FastBatchingCellReaderTest method testAggregateDistinctCount3.

/**
 * As {@link #testAggregateDistinctCount2()}, but with two calc members
 * simultaneously.
 */
public void testAggregateDistinctCount3() {
    assertQueryReturns("WITH\n" + "  MEMBER [Promotion Media].[TV plus Radio] AS 'AGGREGATE({[Promotion Media].[TV], [Promotion Media].[Radio]})', solve_order=1\n" + "  MEMBER [Time].[Time].[1997 Q1 plus July] AS 'AGGREGATE({[Time].[1997].[Q1], [Time].[1997].[Q3].[7]})', solve_order=1\n" + "SELECT {[Promotion Media].[TV plus Radio],\n" + "        [Promotion Media].[TV],\n" + "        [Promotion Media].[Radio]} ON COLUMNS,\n" + "       {[Time].[1997],\n" + "        [Time].[1997].[Q1],\n" + "        [Time].[1997 Q1 plus July]} ON ROWS\n" + "FROM Sales\n" + "WHERE [Measures].[Customer Count]", "Axis #0:\n" + "{[Measures].[Customer Count]}\n" + "Axis #1:\n" + "{[Promotion Media].[TV plus Radio]}\n" + "{[Promotion Media].[TV]}\n" + "{[Promotion Media].[Radio]}\n" + "Axis #2:\n" + "{[Time].[1997]}\n" + "{[Time].[1997].[Q1]}\n" + "{[Time].[1997 Q1 plus July]}\n" + "Row #0: 455\n" + "Row #0: 274\n" + "Row #0: 186\n" + "Row #1: 139\n" + "Row #1: 99\n" + "Row #1: 40\n" + "Row #2: 139\n" + "Row #2: 99\n" + "Row #2: 40\n");
    // There are 9 cells in the result. 6 sql statements have to be issued
    // to fetch all of them, with each loading these cells:
    // (1) ([1997], [TV Plus radio])
    // 
    // (2) ([1997], [TV])
    // ([1997], [radio])
    // 
    // (3) ([1997].[Q1], [TV Plus radio])
    // 
    // (4) ([1997].[Q1], [TV])
    // ([1997].[Q1], [radio])
    // 
    // (5) ([1997 Q1 plus July], [TV Plus radio])
    // 
    // (6) ([1997 Q1 Plus July], [TV])
    // ([1997 Q1 Plus July], [radio])
    final String oracleSql = "select " + "\"time_by_day\".\"the_year\" as \"c0\", \"time_by_day\".\"quarter\" as \"c1\", " + "\"promotion\".\"media_type\" as \"c2\", " + "count(distinct \"sales_fact_1997\".\"customer_id\") as \"m0\" " + "from " + "\"time_by_day\" \"time_by_day\", \"sales_fact_1997\" \"sales_fact_1997\", " + "\"promotion\" \"promotion\" " + "where " + "\"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" and " + "\"time_by_day\".\"the_year\" = 1997 and " + "\"time_by_day\".\"quarter\" = 'Q1' and " + "\"sales_fact_1997\".\"promotion_id\" = \"promotion\".\"promotion_id\" and " + "\"promotion\".\"media_type\" in ('Radio', 'TV') " + "group by " + "\"time_by_day\".\"the_year\", \"time_by_day\".\"quarter\", " + "\"promotion\".\"media_type\"";
    final String mysqlSql = "select " + "`time_by_day`.`the_year` as `c0`, `time_by_day`.`quarter` as `c1`, " + "`promotion`.`media_type` as `c2`, count(distinct `sales_fact_1997`.`customer_id`) as `m0` " + "from " + "`time_by_day` as `time_by_day`, `sales_fact_1997` as `sales_fact_1997`, " + "`promotion` as `promotion` " + "where " + "`sales_fact_1997`.`time_id` = `time_by_day`.`time_id` and " + "`time_by_day`.`the_year` = 1997 and `time_by_day`.`quarter` = 'Q1' and `" + "sales_fact_1997`.`promotion_id` = `promotion`.`promotion_id` and " + "`promotion`.`media_type` in ('Radio', 'TV') " + "group by " + "`time_by_day`.`the_year`, `time_by_day`.`quarter`, `promotion`.`media_type`";
    final String derbySql = "select " + "\"time_by_day\".\"the_year\" as \"c0\", \"time_by_day\".\"quarter\" as \"c1\", " + "\"promotion\".\"media_type\" as \"c2\", " + "count(distinct \"sales_fact_1997\".\"customer_id\") as \"m0\" " + "from " + "\"time_by_day\" as \"time_by_day\", \"sales_fact_1997\" as \"sales_fact_1997\", " + "\"promotion\" as \"promotion\" " + "where " + "\"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" and " + "\"time_by_day\".\"the_year\" = 1997 and \"time_by_day\".\"quarter\" = 'Q1' and " + "\"sales_fact_1997\".\"promotion_id\" = \"promotion\".\"promotion_id\" and " + "\"promotion\".\"media_type\" in ('Radio', 'TV') " + "group by " + "\"time_by_day\".\"the_year\", \"time_by_day\".\"quarter\", " + "\"promotion\".\"media_type\"";
    assertQuerySql("WITH\n" + "  MEMBER [Promotion Media].[TV plus Radio] AS 'AGGREGATE({[Promotion Media].[TV], [Promotion Media].[Radio]})', solve_order=1\n" + "  MEMBER [Time].[Time].[1997 Q1 plus July] AS 'AGGREGATE({[Time].[1997].[Q1], [Time].[1997].[Q3].[7]})', solve_order=1\n" + "SELECT {[Promotion Media].[TV plus Radio],\n" + "        [Promotion Media].[TV],\n" + "        [Promotion Media].[Radio]} ON COLUMNS,\n" + "       {[Time].[1997],\n" + "        [Time].[1997].[Q1],\n" + "        [Time].[1997 Q1 plus July]} ON ROWS\n" + "FROM Sales\n" + "WHERE [Measures].[Customer Count]", new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.ORACLE, oracleSql, oracleSql), new SqlPattern(Dialect.DatabaseProduct.MYSQL, mysqlSql, mysqlSql), new SqlPattern(Dialect.DatabaseProduct.DERBY, derbySql, derbySql) });
}

Example 93 with SqlPattern

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

the class FilterTest method testNotInMultiLevelMemberConstraintSingleNullParent.

/**
 * Test that null members are included when the filter explicitly excludes
 * a single member that has a null.  The members span multiple levels.
 */
public void testNotInMultiLevelMemberConstraintSingleNullParent() {
    if (!isDefaultNullMemberRepresentation()) {
        return;
    }
    if (MondrianProperties.instance().FilterChildlessSnowflakeMembers.get()) {
        return;
    }
    String dimension = "<Dimension name=\"Warehouse2\">\n" + "  <Hierarchy hasAll=\"true\" primaryKey=\"warehouse_id\">\n" + "    <Table name=\"warehouse\"/>\n" + "    <Level name=\"fax\" column=\"warehouse_fax\" uniqueMembers=\"true\"/>\n" + "    <Level name=\"address1\" column=\"wa_address1\" uniqueMembers=\"false\"/>\n" + "    <Level name=\"name\" column=\"warehouse_name\" uniqueMembers=\"false\"/>\n" + "  </Hierarchy>\n" + "</Dimension>\n";
    String cube = "<Cube name=\"Warehouse2\">\n" + "  <Table name=\"inventory_fact_1997\"/>\n" + "  <DimensionUsage name=\"Product\" source=\"Product\" foreignKey=\"product_id\"/>\n" + "  <DimensionUsage name=\"Warehouse2\" source=\"Warehouse2\" foreignKey=\"warehouse_id\"/>\n" + "  <Measure name=\"Warehouse Cost\" column=\"warehouse_cost\" aggregator=\"sum\"/>\n" + "  <Measure name=\"Warehouse Sales\" column=\"warehouse_sales\" aggregator=\"sum\"/>\n" + "</Cube>";
    String query = "with\n" + "set [Filtered Warehouse Set] as 'Filter([Warehouse2].[name].Members, [Warehouse2].CurrentMember Not In" + "{[Warehouse2].[#null].[234 West Covina Pkwy].[Freeman And Co]})' " + "set [NECJ] as NonEmptyCrossJoin([Filtered Warehouse Set], {[Product].[Product Family].Food}) " + "select [NECJ] on 0 from [Warehouse2]";
    String necjSqlDerby = "select \"warehouse\".\"warehouse_fax\", \"warehouse\".\"wa_address1\", " + "\"warehouse\".\"warehouse_name\", \"product_class\".\"product_family\" " + "from \"warehouse\" as \"warehouse\", \"inventory_fact_1997\" as " + "\"inventory_fact_1997\", \"product\" as \"product\", \"product_class\" " + "as \"product_class\" where \"inventory_fact_1997\".\"warehouse_id\" = " + "\"warehouse\".\"warehouse_id\" and \"product\".\"product_class_id\" = " + "\"product_class\".\"product_class_id\" and " + "\"inventory_fact_1997\".\"product_id\" = \"product\".\"product_id\" and " + "(\"product_class\".\"product_family\" = 'Food') and ((not " + "(\"warehouse\".\"warehouse_name\" = 'Freeman And Co') or " + "(\"warehouse\".\"warehouse_name\" is null)) or (not " + "(\"warehouse\".\"wa_address1\" = '234 West Covina Pkwy') or " + "(\"warehouse\".\"wa_address1\" is null)) or not " + "(\"warehouse\".\"warehouse_fax\" is null)) group by " + "\"warehouse\".\"warehouse_fax\", \"warehouse\".\"wa_address1\", " + "\"warehouse\".\"warehouse_name\", \"product_class\".\"product_family\" " + "order by \"warehouse\".\"warehouse_fax\" ASC, " + "\"warehouse\".\"wa_address1\" ASC, \"warehouse\".\"warehouse_name\" ASC, " + "\"product_class\".\"product_family\" ASC";
    String necjSqlMySql = "select `warehouse`.`warehouse_fax` as `c0`, " + "`warehouse`.`wa_address1` as `c1`, `warehouse`.`warehouse_name` " + "as `c2`, `product_class`.`product_family` as `c3` from " + "`warehouse` as `warehouse`, `inventory_fact_1997` as " + "`inventory_fact_1997`, `product` as `product`, `product_class` " + "as `product_class` where `inventory_fact_1997`.`warehouse_id` = " + "`warehouse`.`warehouse_id` and `product`.`product_class_id` = " + "`product_class`.`product_class_id` and " + "`inventory_fact_1997`.`product_id` = `product`.`product_id` and " + "(`product_class`.`product_family` = 'Food') and " + "((not (`warehouse`.`warehouse_name` = 'Freeman And Co') or " + "(`warehouse`.`warehouse_name` is null)) or (not " + "(`warehouse`.`wa_address1` = '234 West Covina Pkwy') or " + "(`warehouse`.`wa_address1` is null)) or not " + "(`warehouse`.`warehouse_fax` is null)) group by " + "`warehouse`.`warehouse_fax`, `warehouse`.`wa_address1`, " + "`warehouse`.`warehouse_name`, `product_class`.`product_family` " + "order by ISNULL(`warehouse`.`warehouse_fax`), " + "`warehouse`.`warehouse_fax` ASC, " + "ISNULL(`warehouse`.`wa_address1`), `warehouse`.`wa_address1` ASC, " + "ISNULL(`warehouse`.`warehouse_name`), " + "`warehouse`.`warehouse_name` ASC, " + "ISNULL(`product_class`.`product_family`), " + "`product_class`.`product_family` ASC";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.DERBY, necjSqlDerby, necjSqlDerby), new SqlPattern(Dialect.DatabaseProduct.MYSQL, necjSqlMySql, necjSqlMySql) };
    TestContext testContext = TestContext.instance().create(dimension, cube, null, null, null, null);
    assertQuerySql(testContext, query, patterns);
}

Example 94 with SqlPattern

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

the class FilterTest method testNotInMultiLevelMemberConstraintMixedNullNonNullParent.

/**
 * Test that null members are included when the filter explicitly excludes
 * certain members that contain nulls.  The members span multiple levels.
 */
public void testNotInMultiLevelMemberConstraintMixedNullNonNullParent() {
    if (!isDefaultNullMemberRepresentation()) {
        return;
    }
    if (MondrianProperties.instance().FilterChildlessSnowflakeMembers.get()) {
        return;
    }
    String dimension = "<Dimension name=\"Warehouse2\">\n" + "  <Hierarchy hasAll=\"true\" primaryKey=\"warehouse_id\">\n" + "    <Table name=\"warehouse\"/>\n" + "    <Level name=\"fax\" column=\"warehouse_fax\" uniqueMembers=\"true\"/>\n" + "    <Level name=\"address1\" column=\"wa_address1\" uniqueMembers=\"false\"/>\n" + "    <Level name=\"name\" column=\"warehouse_name\" uniqueMembers=\"false\"/>\n" + "  </Hierarchy>\n" + "</Dimension>\n";
    String cube = "<Cube name=\"Warehouse2\">\n" + "  <Table name=\"inventory_fact_1997\"/>\n" + "  <DimensionUsage name=\"Product\" source=\"Product\" foreignKey=\"product_id\"/>\n" + "  <DimensionUsage name=\"Warehouse2\" source=\"Warehouse2\" foreignKey=\"warehouse_id\"/>\n" + "  <Measure name=\"Warehouse Cost\" column=\"warehouse_cost\" aggregator=\"sum\"/>\n" + "  <Measure name=\"Warehouse Sales\" column=\"warehouse_sales\" aggregator=\"sum\"/>\n" + "</Cube>";
    String query = "with\n" + "set [Filtered Warehouse Set] as 'Filter([Warehouse2].[name].Members, [Warehouse2].CurrentMember Not In" + "{[Warehouse2].[#null].[234 West Covina Pkwy].[Freeman And Co]," + " [Warehouse2].[971-555-6213].[3377 Coachman Place].[Jones International]})' " + "set [NECJ] as NonEmptyCrossJoin([Filtered Warehouse Set], {[Product].[Product Family].Food}) " + "select [NECJ] on 0 from [Warehouse2]";
    String necjSqlDerby = "select \"warehouse\".\"warehouse_fax\", \"warehouse\".\"wa_address1\", " + "\"warehouse\".\"warehouse_name\", \"product_class\".\"product_family\" " + "from \"warehouse\" as \"warehouse\", \"inventory_fact_1997\" as " + "\"inventory_fact_1997\", \"product\" as \"product\", \"product_class\" as " + "\"product_class\" where \"inventory_fact_1997\".\"warehouse_id\" = " + "\"warehouse\".\"warehouse_id\" and \"product\".\"product_class_id\" = " + "\"product_class\".\"product_class_id\" and " + "\"inventory_fact_1997\".\"product_id\" = \"product\".\"product_id\" and " + "(\"product_class\".\"product_family\" = 'Food') and " + "(not ((\"warehouse\".\"wa_address1\" = '234 West Covina Pkwy' and " + "\"warehouse\".\"warehouse_fax\" is null and " + "\"warehouse\".\"warehouse_name\" = 'Freeman And Co') or " + "(\"warehouse\".\"wa_address1\" = '3377 Coachman Place' and " + "\"warehouse\".\"warehouse_fax\" = '971-555-6213' and " + "\"warehouse\".\"warehouse_name\" = 'Jones International')) or " + "((\"warehouse\".\"warehouse_name\" is null or " + "\"warehouse\".\"wa_address1\" is null or \"warehouse\".\"warehouse_fax\" " + "is null) and not((\"warehouse\".\"wa_address1\" = " + "'234 West Covina Pkwy' and \"warehouse\".\"warehouse_fax\" is null " + "and \"warehouse\".\"warehouse_name\" = 'Freeman And Co') or " + "(\"warehouse\".\"wa_address1\" = '3377 Coachman Place' and " + "\"warehouse\".\"warehouse_fax\" = '971-555-6213' and " + "\"warehouse\".\"warehouse_name\" = 'Jones International')))) " + "group by \"warehouse\".\"warehouse_fax\", \"warehouse\".\"wa_address1\", " + "\"warehouse\".\"warehouse_name\", \"product_class\".\"product_family\" " + "order by \"warehouse\".\"warehouse_fax\" ASC, " + "\"warehouse\".\"wa_address1\" ASC, \"warehouse\".\"warehouse_name\" ASC, " + "\"product_class\".\"product_family\" ASC";
    String necjSqlMySql = "select `warehouse`.`warehouse_fax` as `c0`, `warehouse`.`wa_address1` as `c1`, " + "`warehouse`.`warehouse_name` as `c2`, `product_class`.`product_family` as `c3` " + "from `warehouse` as `warehouse`, `inventory_fact_1997` as `inventory_fact_1997`, " + "`product` as `product`, `product_class` as `product_class` where " + "`inventory_fact_1997`.`warehouse_id` = `warehouse`.`warehouse_id` " + "and `product`.`product_class_id` = `product_class`.`product_class_id` " + "and `inventory_fact_1997`.`product_id` = `product`.`product_id` " + "and (`product_class`.`product_family` = 'Food') and " + "(not ((`warehouse`.`warehouse_name`, `warehouse`.`wa_address1`, `warehouse`.`warehouse_fax`) " + "in (('Jones International', '3377 Coachman Place', '971-555-6213')) " + "or (`warehouse`.`warehouse_fax` is null and (`warehouse`.`warehouse_name`, `warehouse`.`wa_address1`) " + "in (('Freeman And Co', '234 West Covina Pkwy')))) or " + "((`warehouse`.`warehouse_name` is null or `warehouse`.`wa_address1` is null " + "or `warehouse`.`warehouse_fax` is null) and not((`warehouse`.`warehouse_fax` is null " + "and (`warehouse`.`warehouse_name`, `warehouse`.`wa_address1`) in " + "(('Freeman And Co', '234 West Covina Pkwy')))))) " + "group by `warehouse`.`warehouse_fax`, `warehouse`.`wa_address1`, " + "`warehouse`.`warehouse_name`, `product_class`.`product_family` " + "order by ISNULL(`warehouse`.`warehouse_fax`), `warehouse`.`warehouse_fax` ASC, " + "ISNULL(`warehouse`.`wa_address1`), `warehouse`.`wa_address1` ASC, " + "ISNULL(`warehouse`.`warehouse_name`), `warehouse`.`warehouse_name` ASC, " + "ISNULL(`product_class`.`product_family`), `product_class`.`product_family` ASC";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.DERBY, necjSqlDerby, necjSqlDerby), new SqlPattern(Dialect.DatabaseProduct.MYSQL, necjSqlMySql, necjSqlMySql) };
    TestContext testContext = TestContext.instance().create(dimension, cube, null, null, null, null);
    assertQuerySql(testContext, query, patterns);
}

Example 95 with SqlPattern

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

the class GroupingSetQueryTest method testGroupingSetForMultipleColumnConstraint.

public void testGroupingSetForMultipleColumnConstraint() {
    if (prop.ReadAggregates.get() && prop.UseAggregates.get()) {
        return;
    }
    propSaver.set(prop.EnableGroupingSets, true);
    CellRequest request1 = createRequest(cubeNameSales2, measureUnitSales, new String[] { tableCustomer, tableTime }, new String[] { fieldGender, fieldYear }, new String[] { "M", "1997" });
    CellRequest request2 = createRequest(cubeNameSales2, measureUnitSales, new String[] { tableCustomer, tableTime }, new String[] { fieldGender, fieldYear }, new String[] { "F", "1997" });
    CellRequest request3 = createRequest(cubeNameSales2, measureUnitSales, tableTime, fieldYear, "1997");
    SqlPattern[] patternsWithGsets = { new SqlPattern(ORACLE_TERADATA, "select \"time_by_day\".\"the_year\" as \"c0\", \"customer\".\"gender\" as \"c1\", " + "sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\", grouping(\"customer\".\"gender\") as \"g0\" " + "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 grouping sets ((\"time_by_day\".\"the_year\", \"customer\".\"gender\"), (\"time_by_day\".\"the_year\"))", 150) };
    // Sometimes this query causes Oracle 10.2 XE to give
    // ORA-12516, TNS:listener could not find available handler with
    // matching protocol stack
    // 
    // You need to configure Oracle:
    // $ su - oracle
    // $ sqlplus / as sysdba
    // SQL> ALTER SYSTEM SET sessions=320 SCOPE=SPFILE;
    // SQL> SHUTDOWN
    assertRequestSql(new CellRequest[] { request3, request1, request2 }, patternsWithGsets);
    propSaver.set(prop.EnableGroupingSets, false);
    SqlPattern[] patternsWithoutGsets = { new SqlPattern(Dialect.DatabaseProduct.ACCESS, "select \"time_by_day\".\"the_year\" as \"c0\", \"customer\".\"gender\" 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\", " + "\"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\"", 50), new SqlPattern(ORACLE_TERADATA, "select \"time_by_day\".\"the_year\" as \"c0\", \"customer\".\"gender\" 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\", " + "\"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\"", 50) };
    assertRequestSql(new CellRequest[] { request3, request1, request2 }, patternsWithoutGsets);
}