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

Example 36 with SqlPattern

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

the class NonEmptyTest method testMultiLevelMemberConstraintNonNullParent.

/**
 * Checks that multi-level member list generates compact form of SQL where
 * clause:
 * (1) Use IN list if possible
 * (2) Group members sharing the same parent
 * (3) Only need to compare up to the first unique parent level.
 */
public void testMultiLevelMemberConstraintNonNullParent() {
    String query = "with " + "set [Filtered Store City Set] as " + "{[Store].[USA].[OR].[Portland], " + " [Store].[USA].[OR].[Salem], " + " [Store].[USA].[CA].[San Francisco], " + " [Store].[USA].[WA].[Tacoma]} " + "set [NECJ] as NonEmptyCrossJoin([Filtered Store City Set], {[Product].[Product Family].Food}) " + "select [NECJ] on columns from [Sales]";
    String necjSqlDerby = "select " + "\"store\".\"store_country\", \"store\".\"store_state\", \"store\".\"store_city\", " + "\"product_class\".\"product_family\" " + "from " + "\"store\" as \"store\", \"sales_fact_1997\" as \"sales_fact_1997\", " + "\"product\" as \"product\", \"product_class\" as \"product_class\" " + "where " + "\"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" " + "and \"product\".\"product_class_id\" = \"product_class\".\"product_class_id\" " + "and \"sales_fact_1997\".\"product_id\" = \"product\".\"product_id\" " + "and ((\"store\".\"store_state\" = 'OR' and \"store\".\"store_city\" in ('Portland', 'Salem'))" + " or (\"store\".\"store_state\" = 'CA' and \"store\".\"store_city\" = 'San Francisco')" + " or (\"store\".\"store_state\" = 'WA' and \"store\".\"store_city\" = 'Tacoma')) " + "and (\"product_class\".\"product_family\" = 'Food') " + "group by \"store\".\"store_country\", \"store\".\"store_state\", \"store\".\"store_city\", \"product_class\".\"product_family\" " + "order by CASE WHEN \"store\".\"store_country\" IS NULL THEN 1 ELSE 0 END, \"store\".\"store_country\" ASC, CASE WHEN \"store\".\"store_state\" IS NULL THEN 1 ELSE 0 END, \"store\".\"store_state\" ASC, CASE WHEN \"store\".\"store_city\" IS NULL THEN 1 ELSE 0 END, \"store\".\"store_city\" ASC, CASE WHEN \"product_class\".\"product_family\" IS NULL THEN 1 ELSE 0 END, \"product_class\".\"product_family\" ASC";
    String necjSqlMySql = "select " + "`store`.`store_country` as `c0`, `store`.`store_state` as `c1`, " + "`store`.`store_city` as `c2`, `product_class`.`product_family` as `c3` " + "from " + "`store` as `store`, `sales_fact_1997` as `sales_fact_1997`, " + "`product` as `product`, `product_class` as `product_class` " + "where " + "`sales_fact_1997`.`store_id` = `store`.`store_id` " + "and `product`.`product_class_id` = `product_class`.`product_class_id` " + "and `sales_fact_1997`.`product_id` = `product`.`product_id` " + "and ((`store`.`store_city`, `store`.`store_state`) in (('Portland', 'OR'), ('Salem', 'OR'), ('San Francisco', 'CA'), ('Tacoma', 'WA'))) " + "and (`product_class`.`product_family` = 'Food') " + "group by `store`.`store_country`, `store`.`store_state`, `store`.`store_city`, `product_class`.`product_family` order by " + (TestContext.instance().getDialect().requiresOrderByAlias() ? "ISNULL(`c0`) ASC, `c0` ASC, ISNULL(`c1`) ASC, `c1` ASC, " + "ISNULL(`c2`) ASC, `c2` ASC, ISNULL(`c3`) ASC, `c3` ASC" : "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(`product_class`.`product_family`) ASC, `product_class`.`product_family` ASC");
    if (MondrianProperties.instance().UseAggregates.get() && MondrianProperties.instance().ReadAggregates.get()) {
        // slightly different sql expected, uses agg table now for join
        necjSqlMySql = necjSqlMySql.replaceAll("sales_fact_1997", "agg_c_14_sales_fact_1997");
        necjSqlDerby = necjSqlDerby.replaceAll("sales_fact_1997", "agg_c_14_sales_fact_1997");
    }
    if (!MondrianProperties.instance().FilterChildlessSnowflakeMembers.get()) {
        necjSqlMySql = necjSqlMySql.replaceAll("`product` as `product`, `product_class` as `product_class`", "`product_class` as `product_class`, `product` as `product`");
        necjSqlMySql = necjSqlMySql.replaceAll("`product`.`product_class_id` = `product_class`.`product_class_id` and " + "`sales_fact_1997`.`product_id` = `product`.`product_id` and ", "`sales_fact_1997`.`product_id` = `product`.`product_id` and " + "`product`.`product_class_id` = `product_class`.`product_class_id` and ");
        necjSqlDerby = necjSqlDerby.replaceAll("\"product\" as \"product\", \"product_class\" as \"product_class\"", "\"product_class\" as \"product_class\", \"product\" as \"product\"");
        necjSqlDerby = necjSqlDerby.replaceAll("\"product\".\"product_class_id\" = \"product_class\".\"product_class_id\" and " + "\"sales_fact_1997\".\"product_id\" = \"product\".\"product_id\" and ", "\"sales_fact_1997\".\"product_id\" = \"product\".\"product_id\" and " + "\"product\".\"product_class_id\" = \"product_class\".\"product_class_id\" and ");
    }
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.DERBY, necjSqlDerby, necjSqlDerby), new SqlPattern(Dialect.DatabaseProduct.MYSQL, necjSqlMySql, necjSqlMySql) };
    assertQuerySql(query, patterns);
}

Example 37 with SqlPattern

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

the class NonEmptyTest method testMultiLevelMemberConstraintNullParent.

/**
 * Checks that multi-level member list generates compact form of SQL where
 * clause:
 * (1) Use IN list if possible(not possible if there are null values because
 *     NULLs in IN lists do not match)
 * (2) Group members sharing the same parent, including parents with NULLs.
 * (3) If parent levels include NULLs, comparision includes any unique
 * level.
 */
public void testMultiLevelMemberConstraintNullParent() {
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    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=\"address3\" column=\"wa_address3\" uniqueMembers=\"true\"/>\n" + "    <Level name=\"address2\" column=\"wa_address2\" 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 " + "{[Warehouse2].[#null].[#null].[5617 Saclan Terrace].[Arnold and Sons]," + " [Warehouse2].[#null].[#null].[3377 Coachman Place].[Jones International]} " + "set [NECJ] as NonEmptyCrossJoin([Filtered Warehouse Set], {[Product].[Product Family].Food}) " + "select [NECJ] on columns from [Warehouse2]";
    String necjSqlMySql = "select\n" + "    `warehouse`.`wa_address3` as `c0`,\n" + "    `warehouse`.`wa_address2` as `c1`,\n" + "    `warehouse`.`wa_address1` as `c2`,\n" + "    `warehouse`.`warehouse_name` as `c3`,\n" + "    `product_class`.`product_family` as `c4`\n" + "from\n" + "    `warehouse` as `warehouse`,\n" + "    `inventory_fact_1997` as `inventory_fact_1997`,\n" + "    `product` as `product`,\n" + "    `product_class` as `product_class`\n" + "where\n" + "    `inventory_fact_1997`.`warehouse_id` = `warehouse`.`warehouse_id`\n" + "and\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `inventory_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    ((( `warehouse`.`wa_address2` IS NULL ) and (`warehouse`.`warehouse_name`, `warehouse`.`wa_address1`) in (('Arnold and Sons', '5617 Saclan Terrace'), ('Jones International', '3377 Coachman Place'))))\n" + "and\n" + "    (`product_class`.`product_family` = 'Food')\n" + "group by\n" + "    `warehouse`.`wa_address3`,\n" + "    `warehouse`.`wa_address2`,\n" + "    `warehouse`.`wa_address1`,\n" + "    `warehouse`.`warehouse_name`,\n" + "    `product_class`.`product_family`\n" + "order by\n" + (TestContext.instance().getDialect().requiresOrderByAlias() ? "    ISNULL(`c0`) ASC, `c0` ASC,\n" + "    ISNULL(`c1`) ASC, `c1` ASC,\n" + "    ISNULL(`c2`) ASC, `c2` ASC,\n" + "    ISNULL(`c3`) ASC, `c3` ASC,\n" + "    ISNULL(`c4`) ASC, `c4` ASC" : "    ISNULL(`warehouse`.`wa_address3`) ASC, `warehouse`.`wa_address3` ASC,\n" + "    ISNULL(`warehouse`.`wa_address2`) ASC, `warehouse`.`wa_address2` ASC,\n" + "    ISNULL(`warehouse`.`wa_address1`) ASC, `warehouse`.`wa_address1` ASC,\n" + "    ISNULL(`warehouse`.`warehouse_name`) ASC, `warehouse`.`warehouse_name` ASC,\n" + "    ISNULL(`product_class`.`product_family`) ASC, `product_class`.`product_family` ASC");
    TestContext testContext = TestContext.instance().create(dimension, cube, null, null, null, null);
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, necjSqlMySql, necjSqlMySql) };
    assertQuerySql(testContext, query, patterns);
}

Example 38 with SqlPattern

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

the class NonEmptyTest method testFilterChildlessSnowflakeMembers.

public void testFilterChildlessSnowflakeMembers() {
    propSaver.set(MondrianProperties.instance().FilterChildlessSnowflakeMembers, false);
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, "select `product_class`.`product_family` as `c0` " + "from `product_class` as `product_class` " + "group by `product_class`.`product_family` " + (TestContext.instance().getDialect().requiresOrderByAlias() ? "order by ISNULL(`c0`) ASC," + " `c0` ASC" : "order by ISNULL(`product_class`.`product_family`) ASC," + " `product_class`.`product_family` ASC"), null) };
    final TestContext context = getTestContext().withFreshConnection();
    try {
        assertQuerySql(context, "select [Product].[Product Family].Members on 0\n" + "from [Sales]", patterns);
        // note that returns an extra member,
        // [Product].[Drink].[Baking Goods]
        context.assertQueryReturns("select [Product].[Drink].Children on 0\n" + "from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Product].[Drink].[Alcoholic Beverages]}\n" + "{[Product].[Drink].[Baking Goods]}\n" + "{[Product].[Drink].[Beverages]}\n" + "{[Product].[Drink].[Dairy]}\n" + "Row #0: 6,838\n" + "Row #0: \n" + "Row #0: 13,573\n" + "Row #0: 4,186\n");
        // [Product].[Drink].[Baking Goods] has one child, but no fact data
        context.assertQueryReturns("select [Product].[Drink].[Baking Goods].Children on 0\n" + "from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Product].[Drink].[Baking Goods].[Dry Goods]}\n" + "Row #0: \n");
        // NON EMPTY filters out that child
        context.assertQueryReturns("select non empty [Product].[Drink].[Baking Goods].Children on 0\n" + "from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n");
        // [Product].[Drink].[Baking Goods].[Dry Goods] has one child, but
        // no fact data
        context.assertQueryReturns("select [Product].[Drink].[Baking Goods].[Dry Goods].Children on 0\n" + "from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Product].[Drink].[Baking Goods].[Dry Goods].[Coffee]}\n" + "Row #0: \n");
        // NON EMPTY filters out that child
        context.assertQueryReturns("select non empty [Product].[Drink].[Baking Goods].[Dry Goods].Children on 0\n" + "from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n");
        // [Coffee] has no children
        context.assertQueryReturns("select [Product].[Drink].[Baking Goods].[Dry Goods].[Coffee].Children on 0\n" + "from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n");
        context.assertQueryReturns("select [Measures].[Unit Sales] on 0,\n" + " [Product].[Product Family].Members on 1\n" + "from [Sales]", "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Unit Sales]}\n" + "Axis #2:\n" + "{[Product].[Drink]}\n" + "{[Product].[Food]}\n" + "{[Product].[Non-Consumable]}\n" + "Row #0: 24,597\n" + "Row #1: 191,940\n" + "Row #2: 50,236\n");
    } finally {
        context.close();
    }
}

Example 39 with SqlPattern

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

the class NonEmptyTest method testLevelMembersWillConstrainUsingArgsFromAllAxes.

public void testLevelMembersWillConstrainUsingArgsFromAllAxes() {
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    String mdx = "select " + "non empty Crossjoin({[Gender].[Gender].[F]},{[Measures].[Unit Sales]}) on 0," + "non empty [Promotions].[Promotions].members on 1" + " from [Warehouse and Sales]";
    SqlPattern oraclePattern = new SqlPattern(Dialect.DatabaseProduct.ORACLE, propSaver.properties.UseAggregates.get() ? "select\n" + "    \"promotion\".\"promotion_name\" as \"c0\"\n" + "from\n" + "    \"promotion\" \"promotion\",\n" + "    \"agg_c_14_sales_fact_1997\" \"agg_c_14_sales_fact_1997\",\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" + "group by\n" + "    \"promotion\".\"promotion_name\"\n" + "order by\n" + "    \"promotion\".\"promotion_name\" ASC NULLS LAST" : "select\n" + "    \"promotion\".\"promotion_name\" as \"c0\"\n" + "from\n" + "    \"promotion\" \"promotion\",\n" + "    \"sales_fact_1997\" \"sales_fact_1997\",\n" + "    \"customer\" \"customer\"\n" + "where\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" + "group by\n" + "    \"promotion\".\"promotion_name\"\n" + "order by\n" + "    \"promotion\".\"promotion_name\" ASC NULLS LAST", 347);
    assertQuerySql(mdx, new SqlPattern[] { oraclePattern });
}

Example 40 with SqlPattern

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

the class NativizeSetFunDefTest method testCardinalityQueriesOnlyExecuteOnce.

public void testCardinalityQueriesOnlyExecuteOnce() {
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.ORACLE, "select count(*) as \"c0\" " + "from (select " + "distinct \"customer\".\"gender\" as \"c0\" " + "from \"customer\" \"customer\") \"init\"", 108), new SqlPattern(Dialect.DatabaseProduct.ACCESS, "select count(*) as `c0` " + "from (select " + "distinct `customer`.`gender` as `c0` " + "from `customer` as `customer`) as `init`", 108) };
    String mdxQuery = "select" + " non empty" + " NativizeSet(Crossjoin(" + "[Gender].[Gender].members,[Marital Status].[Marital Status].members" + ")) on 0 from Sales";
    getConnection().execute(getConnection().parseQuery(mdxQuery));
    assertQuerySqlOrNot(getTestContext(), mdxQuery, patterns, true, false, false);
}