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

Example 26 with SqlPattern

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

the class NativeEvalVirtualCubeTest method testTupleQueryShouldBeCachedForVirtualCube.

/**
 * Testcase for bug
 * <a href="http://jira.pentaho.com/browse/MONDRIAN-2597">MONDRIAN-2597,
 * "readTuples and cardinality queries sent twice to the database
 * when using Virtual Cube (Not cached)"</a>.
 */
public void testTupleQueryShouldBeCachedForVirtualCube() {
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    String mySqlMembersQuery = "select\n" + "    *\n" + "from\n" + "    (select\n" + "    `product_class`.`product_family` as `c0`,\n" + "    `product_class`.`product_department` as `c1`,\n" + "    `time_by_day`.`the_year` as `c2`\n" + "from\n" + "    `product` as `product`,\n" + "    `product_class` as `product_class`,\n" + "    `sales_fact_1997` as `sales_fact_1997`,\n" + "    `time_by_day` as `time_by_day`\n" + "where\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `sales_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`\n" + "group by\n" + "    `product_class`.`product_family`,\n" + "    `product_class`.`product_department`,\n" + "    `time_by_day`.`the_year`\n" + "union\n" + "select\n" + "    `product_class`.`product_family` as `c0`,\n" + "    `product_class`.`product_department` as `c1`,\n" + "    `time_by_day`.`the_year` as `c2`\n" + "from\n" + "    `product` as `product`,\n" + "    `product_class` as `product_class`,\n" + "    `inventory_fact_1997` as `inventory_fact_1997`,\n" + "    `time_by_day` as `time_by_day`\n" + "where\n" + "    `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + "    `inventory_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + "    `inventory_fact_1997`.`time_id` = `time_by_day`.`time_id`\n" + "group by\n" + "    `product_class`.`product_family`,\n" + "    `product_class`.`product_department`,\n" + "    `time_by_day`.`the_year`) as `unionQuery`\n" + "order by\n" + "    ISNULL(1) ASC, 1 ASC,\n" + "    ISNULL(2) ASC, 2 ASC,\n" + "    ISNULL(3) ASC, 3 ASC";
    TestContext tc = getTestContext().withFreshConnection();
    SqlPattern mysqlPatternMembers = new SqlPattern(Dialect.DatabaseProduct.MYSQL, mySqlMembersQuery, mySqlMembersQuery);
    // The MDX with default measure of [Warehouse and Sales] virtual cube:
    // Store Sales that belongs to the redular [Sale] cube
    String mdx = "WITH\n" + "SET [*NATIVE_CJ_SET] AS 'NONEMPTYCROSSJOIN([*BASE_MEMBERS__Product_],[*BASE_MEMBERS__Time_])'\n" + "SET [*SORTED_ROW_AXIS] AS 'ORDER([*CJ_ROW_AXIS],ANCESTOR([Product].CURRENTMEMBER, [Product].[Product Family]).ORDERKEY,BASC,[Product].CURRENTMEMBER.ORDERKEY,BASC,[Time].CURRENTMEMBER.ORDERKEY,BASC)'\n" + "SET [*BASE_MEMBERS__Measures_] AS '{[Measures].[*FORMATTED_MEASURE_0]}'\n" + "SET [*BASE_MEMBERS__Product_] AS '[Product].[Product Department].MEMBERS'\n" + "SET [*BASE_MEMBERS__Time_] AS '[Time].[Year].MEMBERS'\n" + "SET [*CJ_ROW_AXIS] AS 'GENERATE([*NATIVE_CJ_SET], {([Product].CURRENTMEMBER,[Time].CURRENTMEMBER)})'\n" + "MEMBER [Measures].[*FORMATTED_MEASURE_0] AS '[Measures].[Store Sales]', FORMAT_STRING = '#,###.00', SOLVE_ORDER=500\n" + "SELECT\n" + "[*BASE_MEMBERS__Measures_] ON COLUMNS\n" + ", NON EMPTY\n" + "[*SORTED_ROW_AXIS] ON ROWS\n" + "FROM [Warehouse and Sales]";
    // first MDX with a fresh query should result in product_family,
    // product_department and the_year query.
    assertQuerySqlOrNot(tc, mdx, new SqlPattern[] { mysqlPatternMembers }, false, false, false);
    // rerun the MDX, since the previous assert aborts when it hits the SQL.
    tc.executeQuery(mdx);
    // Subsequent query should pull from cache, not rerun product_family,
    // product_department and the_year query.
    assertQuerySqlOrNot(tc, mdx, new SqlPattern[] { mysqlPatternMembers }, true, false, false);
    // The MDX with added Warehouse Sales measure
    // that belongs to the regulr [Warehouse].
    String mdx1 = "WITH\n" + "SET [*NATIVE_CJ_SET] AS 'NONEMPTYCROSSJOIN([*BASE_MEMBERS__Product_],[*BASE_MEMBERS__Time_])'\n" + "SET [*SORTED_ROW_AXIS] AS 'ORDER([*CJ_ROW_AXIS],ANCESTOR([Product].CURRENTMEMBER, [Product].[Product Family]).ORDERKEY,BASC,[Product].CURRENTMEMBER.ORDERKEY,BASC,[Time].CURRENTMEMBER.ORDERKEY,BASC)'\n" + "SET [*BASE_MEMBERS__Measures_] AS '{[Measures].[Warehouse Sales]}'\n" + "SET [*BASE_MEMBERS__Product_] AS '[Product].[Product Department].MEMBERS'\n" + "SET [*BASE_MEMBERS__Time_] AS '[Time].[Year].MEMBERS'\n" + "SET [*CJ_ROW_AXIS] AS 'GENERATE([*NATIVE_CJ_SET], {([Product].CURRENTMEMBER,[Time].CURRENTMEMBER)})'\n" + "SELECT\n" + "[*BASE_MEMBERS__Measures_] ON COLUMNS\n" + ", NON EMPTY\n" + "[*SORTED_ROW_AXIS] ON ROWS\n" + "FROM [Warehouse and Sales]";
    // Subsequent query should pull from cache, not rerun product_family,
    // product_department and the_year query.
    assertQuerySqlOrNot(tc, mdx1, new SqlPattern[] { mysqlPatternMembers }, true, false, false);
}

Example 27 with SqlPattern

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

the class NonEmptyTest method testNonUniformConstraintsAreNotUsedForOptimization.

public void testNonUniformConstraintsAreNotUsedForOptimization() {
    String mdx = "with member [Measures].[unit sales Male] as '([Measures].[Unit Sales],[Gender].[Gender].[M])' " + "member [Measures].[unit sales Married] as '([Measures].[Unit Sales],[Marital Status].[Marital Status].[M])' " + "select " + "non empty {[Measures].[unit sales Male], [Measures].[unit sales Married]} on 0, " + "non empty [Customers].[name].members on 1 " + "from Sales";
    final String sqlOracle = "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";
    final SqlPattern pattern = new SqlPattern(Dialect.DatabaseProduct.ORACLE, sqlOracle, sqlOracle.length());
    assertQuerySqlOrNot(getTestContext(), mdx, new SqlPattern[] { pattern }, true, false, true);
}

Example 28 with SqlPattern

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

the class NonEmptyTest method testNativeCrossjoinWillExpandLagInNamedSet.

public void testNativeCrossjoinWillExpandLagInNamedSet() {
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    String mdx = "with set [blah] as '{[Gender].lastChild.lag(1),[Gender].[M]}' " + "select " + "non empty Crossjoin([blah],{[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 29 with SqlPattern

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

the class NonEmptyTest method testMultiLevelMemberConstraintMixedNullNonNullParent.

/**
 * Check 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.
 * (4) Can handle predicates correctly if the member list contains both NULL
 * and non NULL parent levels.
 */
public void testMultiLevelMemberConstraintMixedNullNonNullParent() {
    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=\"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 " + "{[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 columns from [Warehouse2]";
    String necjSqlMySql = "select\n" + "    `warehouse`.`warehouse_fax` as `c0`,\n" + "    `warehouse`.`wa_address1` as `c1`,\n" + "    `warehouse`.`warehouse_name` as `c2`,\n" + "    `product_class`.`product_family` as `c3`\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`.`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'))))\n" + "and\n" + "    (`product_class`.`product_family` = 'Food')\n" + "group by\n" + "    `warehouse`.`warehouse_fax`,\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" : "    ISNULL(`warehouse`.`warehouse_fax`) ASC, `warehouse`.`warehouse_fax` 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 30 with SqlPattern

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

the class NonEmptyTest method testConstrainedMeasureGetsOptimized.

public void testConstrainedMeasureGetsOptimized() {
    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].[store sales Female] as '([Measures].[Store Sales],[Gender].[Gender].[F])' " + "member [Measures].[literal one] as '1' " + "select " + "non empty {{[Measures].[unit sales Male]}, {([Measures].[literal one])}, " + "[Measures].[unit sales Female], [Measures].[store sales Female]} on 0, " + "non empty [Customers].[name].members on 1 " + "from Sales";
    final String sqlOracle = MondrianProperties.instance().UseAggregates.get() ? "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\", \"agg_l_03_sales_fact_1997\" \"agg_l_03_sales_fact_1997\" where \"agg_l_03_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" : "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";
    assertQuerySql(mdx, new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.ORACLE, sqlOracle, sqlOracle.length()) });
}