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

Example 81 with SqlPattern

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

the class GroupingSetQueryTest method testGroupingSetForASummaryCanBeGroupedWith2DetailBatch.

public void testGroupingSetForASummaryCanBeGroupedWith2DetailBatch() {
    if (prop.ReadAggregates.get() && prop.UseAggregates.get()) {
        return;
    }
    propSaver.set(prop.EnableGroupingSets, true);
    CellRequest request1 = createRequest(cubeNameSales2, measureUnitSales, tableCustomer, fieldGender, "M");
    CellRequest request2 = createRequest(cubeNameSales2, measureUnitSales, tableCustomer, fieldGender, "F");
    CellRequest request3 = createRequest(cubeNameSales2, measureUnitSales, null, "", "");
    CellRequest request4 = createRequest(cubeNameSales2, measureUnitSales, tableCustomer, fieldNameMaritalStatus, "M");
    CellRequest request5 = createRequest(cubeNameSales2, measureUnitSales, tableCustomer, fieldNameMaritalStatus, "S");
    CellRequest request6 = createRequest(cubeNameSales2, measureUnitSales, null, "", "");
    SqlPattern[] patternWithGsets = { new SqlPattern(ORACLE_TERADATA, "select \"customer\".\"gender\" as \"c0\", sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\", " + "grouping(\"customer\".\"gender\") as \"g0\" " + "from \"customer\" =as= \"customer\", \"sales_fact_1997\" =as= \"sales_fact_1997\" " + "where \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\" " + "group by grouping sets ((\"customer\".\"gender\"), ())", 26), new SqlPattern(ORACLE_TERADATA, "select \"customer\".\"marital_status\" as \"c0\", sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\" " + "from \"customer\" =as= \"customer\", \"sales_fact_1997\" =as= \"sales_fact_1997\" " + "where \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\" " + "group by \"customer\".\"marital_status\"", 26) };
    assertRequestSql(new CellRequest[] { request1, request2, request3, request4, request5, request6 }, patternWithGsets);
    propSaver.set(prop.EnableGroupingSets, false);
    SqlPattern[] patternWithoutGsets = { new SqlPattern(Dialect.DatabaseProduct.ACCESS, "select sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\" " + "from \"sales_fact_1997\" as \"sales_fact_1997\"", 40), new SqlPattern(ORACLE_TERADATA, "select sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\" " + "from \"sales_fact_1997\" =as= \"sales_fact_1997\"", 40) };
    assertRequestSql(new CellRequest[] { request1, request2, request3, request4, request5, request6 }, patternWithoutGsets);
}

Example 82 with SqlPattern

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

the class NativeEvalVirtualCubeTest method testShouldUseCache.

public void testShouldUseCache() {
    // verify cache does get used for applicable grouped target tuple queries
    propSaver.set(propSaver.properties.GenerateFormattedSql, true);
    String mySqlGenderQuery = "select\n" + "    `customer`.`gender` as `c0`\n" + "from\n" + "    `customer` as `customer`,\n" + "    `sales_fact_1997` as `sales_fact_1997`\n" + "where\n" + "    `sales_fact_1997`.`customer_id` = `customer`.`customer_id`\n" + "group by\n" + "    `customer`.`gender`\n" + "order by\n" + "    ISNULL(`customer`.`gender`) ASC, `customer`.`gender` ASC";
    TestContext tc = getTestContext().withFreshConnection();
    SqlPattern mysqlPattern = new SqlPattern(Dialect.DatabaseProduct.MYSQL, mySqlGenderQuery, mySqlGenderQuery);
    String mdx = "with member measures.vm as 'validmeasure(measures.[unit sales])' " + "select non empty " + "crossjoin(gender.gender.members, warehouse.[USA].[CA]) on 0, " + "measures.vm on 1 from [warehouse and sales]";
    // first MDX with a fresh query should result in gender query.
    assertQuerySqlOrNot(tc, mdx, new SqlPattern[] { mysqlPattern }, 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 gender query.
    assertQuerySqlOrNot(tc, mdx, new SqlPattern[] { mysqlPattern }, true, false, false);
}

Example 83 with SqlPattern

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

the class NativeFilterMatchingTest method testNegativeMatching.

public void testNegativeMatching() throws Exception {
    if (!MondrianProperties.instance().EnableNativeFilter.get()) {
        // are turned off.
        return;
    }
    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\" group by \"customer\".\"country\", \"customer\".\"state_province\", \"customer\".\"city\", \"customer\".\"customer_id\", \"fname\" || ' ' || \"lname\", \"customer\".\"gender\", \"customer\".\"marital_status\", \"customer\".\"education\", \"customer\".\"yearly_income\" having NOT(\"fname\" || ' ' || \"lname\" IS NOT NULL AND REGEXP_LIKE(\"fname\" || ' ' || \"lname\", '.*jeanne.*', 'i'))  order by \"customer\".\"country\" ASC NULLS LAST, \"customer\".\"state_province\" ASC NULLS LAST, \"customer\".\"city\" ASC NULLS LAST, \"fname\" || ' ' || \"lname\" ASC NULLS LAST";
    final String sqlPgsql = "select \"customer\".\"country\" as \"c0\", \"customer\".\"state_province\" as \"c1\", \"customer\".\"city\" as \"c2\", \"customer\".\"customer_id\" as \"c3\", fullname as \"c4\", fullname as \"c5\", \"customer\".\"gender\" as \"c6\", \"customer\".\"marital_status\" as \"c7\", \"customer\".\"education\" as \"c8\", \"customer\".\"yearly_income\" as \"c9\" from \"customer\" as \"customer\" group by \"customer\".\"country\", \"customer\".\"state_province\", \"customer\".\"city\", \"customer\".\"customer_id\", fullname, \"customer\".\"gender\", \"customer\".\"marital_status\", \"customer\".\"education\", \"customer\".\"yearly_income\" having NOT(cast(fullname as text) is not null and cast(fullname as text) ~ '(?i).*jeanne.*')  order by \"customer\".\"country\" ASC NULLS LAST, \"customer\".\"state_province\" ASC NULLS LAST, \"customer\".\"city\" ASC NULLS LAST, fullname ASC NULLS LAST";
    final String sqlMysql = "select `customer`.`country` as `c0`, `customer`.`state_province` as `c1`, `customer`.`city` as `c2`, `customer`.`customer_id` as `c3`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c4`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c5`, `customer`.`gender` as `c6`, `customer`.`marital_status` as `c7`, `customer`.`education` as `c8`, `customer`.`yearly_income` as `c9` from `customer` as `customer` group by `customer`.`country`, `customer`.`state_province`, `customer`.`city`, `customer`.`customer_id`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`), `customer`.`gender`, `customer`.`marital_status`, `customer`.`education`, `customer`.`yearly_income` having NOT(c5 IS NOT NULL AND UPPER(c5) REGEXP '.*JEANNE.*')  order by " + (TestContext.instance().getDialect().requiresOrderByAlias() ? "ISNULL(`c0`) ASC, `c0` ASC, " + "ISNULL(`c1`) ASC, `c1` ASC, " + "ISNULL(`c2`) ASC, `c2` ASC, " + "ISNULL(`c4`) ASC, `c4` ASC" : "ISNULL(`customer`.`country`) ASC, `customer`.`country` ASC, ISNULL(`customer`.`state_province`) ASC, `customer`.`state_province` ASC, ISNULL(`customer`.`city`) ASC, `customer`.`city` ASC, ISNULL(CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)) ASC, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) ASC");
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.ORACLE, sqlOracle, sqlOracle.length()), new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysql, sqlMysql.length()), new SqlPattern(Dialect.DatabaseProduct.POSTGRESQL, sqlPgsql, sqlPgsql.length()) };
    final String query = "With\n" + "Set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Customers], Not IsEmpty ([Measures].[Unit Sales]))'\n" + "Set [*SORTED_COL_AXIS] as 'Order([*CJ_COL_AXIS],[Customers].CurrentMember.OrderKey,BASC,Ancestor([Customers].CurrentMember,[Customers].[City]).OrderKey,BASC)'\n" + "Set [*BASE_MEMBERS_Customers] as 'Filter([Customers].[Name].Members,[Customers].CurrentMember.Caption Not Matches (\"(?i).*\\Qjeanne\\E.*\"))'\n" + "Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'\n" + "Set [*CJ_COL_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Customers].currentMember)})'\n" + "Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Unit Sales]', FORMAT_STRING = 'Standard', SOLVE_ORDER=400\n" + "Select\n" + "CrossJoin([*SORTED_COL_AXIS],[*BASE_MEMBERS_Measures]) on columns\n" + "From [Sales]";
    assertQuerySqlOrNot(getTestContext(), query, patterns, false, true, true);
    final Result result = executeQuery(query);
    final String resultString = TestContext.toString(result);
    assertFalse(resultString.contains("Jeanne"));
    verifySameNativeAndNot(query, null, getTestContext());
}

Example 84 with SqlPattern

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

the class NativeFilterMatchingTest method testPositiveMatching.

public void testPositiveMatching() throws Exception {
    if (!MondrianProperties.instance().EnableNativeFilter.get()) {
        // are turned off.
        return;
    }
    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\" group by \"customer\".\"country\", \"customer\".\"state_province\", \"customer\".\"city\", \"customer\".\"customer_id\", \"fname\" || ' ' || \"lname\", \"customer\".\"gender\", \"customer\".\"marital_status\", \"customer\".\"education\", \"customer\".\"yearly_income\" having \"fname\" || ' ' || \"lname\" IS NOT NULL AND REGEXP_LIKE(\"fname\" || ' ' || \"lname\", '.*jeanne.*', 'i') order by \"customer\".\"country\" ASC NULLS LAST, \"customer\".\"state_province\" ASC NULLS LAST, \"customer\".\"city\" ASC NULLS LAST, \"fname\" || ' ' || \"lname\" ASC NULLS LAST";
    final String sqlPgsql = "select \"customer\".\"country\" as \"c0\", \"customer\".\"state_province\" as \"c1\", \"customer\".\"city\" as \"c2\", \"customer\".\"customer_id\" as \"c3\", fullname as \"c4\", fullname as \"c5\", \"customer\".\"gender\" as \"c6\", \"customer\".\"marital_status\" as \"c7\", \"customer\".\"education\" as \"c8\", \"customer\".\"yearly_income\" as \"c9\" from \"customer\" as \"customer\" group by \"customer\".\"country\", \"customer\".\"state_province\", \"customer\".\"city\", \"customer\".\"customer_id\", fullname, \"customer\".\"gender\", \"customer\".\"marital_status\", \"customer\".\"education\", \"customer\".\"yearly_income\" having cast(fullname as text) is not null and cast(fullname as text) ~ '(?i).*jeanne.*'  order by \"customer\".\"country\" ASC NULLS LAST, \"customer\".\"state_province\" ASC NULLS LAST, \"customer\".\"city\" ASC NULLS LAST, fullname ASC NULLS LAST";
    final String sqlMysql = "select `customer`.`country` as `c0`, `customer`.`state_province` as `c1`, `customer`.`city` as `c2`, `customer`.`customer_id` as `c3`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c4`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c5`, `customer`.`gender` as `c6`, `customer`.`marital_status` as `c7`, `customer`.`education` as `c8`, `customer`.`yearly_income` as `c9` from `customer` as `customer` group by `customer`.`country`, `customer`.`state_province`, `customer`.`city`, `customer`.`customer_id`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`), `customer`.`gender`, `customer`.`marital_status`, `customer`.`education`, `customer`.`yearly_income` having c5 IS NOT NULL AND UPPER(c5) REGEXP '.*JEANNE.*' order by " + (TestContext.instance().getDialect().requiresOrderByAlias() ? "ISNULL(`c0`) ASC, `c0` ASC, " + "ISNULL(`c1`) ASC, `c1` ASC, " + "ISNULL(`c2`) ASC, `c2` ASC, " + "ISNULL(`c4`) ASC, `c4` ASC" : "ISNULL(`customer`.`country`) ASC, `customer`.`country` ASC, ISNULL(`customer`.`state_province`) ASC, `customer`.`state_province` ASC, ISNULL(`customer`.`city`) ASC, `customer`.`city` ASC, ISNULL(CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)) ASC, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) ASC");
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.ORACLE, sqlOracle, sqlOracle.length()), new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysql, sqlMysql.length()), new SqlPattern(Dialect.DatabaseProduct.POSTGRESQL, sqlPgsql, sqlPgsql.length()) };
    final String queryResults = "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Customers].[USA].[WA].[Issaquah].[Jeanne Derry], [Measures].[*FORMATTED_MEASURE_0]}\n" + "{[Customers].[USA].[CA].[Los Angeles].[Jeannette Eldridge], [Measures].[*FORMATTED_MEASURE_0]}\n" + "{[Customers].[USA].[CA].[Burbank].[Jeanne Bohrnstedt], [Measures].[*FORMATTED_MEASURE_0]}\n" + "{[Customers].[USA].[OR].[Portland].[Jeanne Zysko], [Measures].[*FORMATTED_MEASURE_0]}\n" + "{[Customers].[USA].[WA].[Everett].[Jeanne McDill], [Measures].[*FORMATTED_MEASURE_0]}\n" + "{[Customers].[USA].[CA].[West Covina].[Jeanne Whitaker], [Measures].[*FORMATTED_MEASURE_0]}\n" + "{[Customers].[USA].[WA].[Everett].[Jeanne Turner], [Measures].[*FORMATTED_MEASURE_0]}\n" + "{[Customers].[USA].[WA].[Puyallup].[Jeanne Wentz], [Measures].[*FORMATTED_MEASURE_0]}\n" + "{[Customers].[USA].[OR].[Albany].[Jeannette Bura], [Measures].[*FORMATTED_MEASURE_0]}\n" + "{[Customers].[USA].[WA].[Lynnwood].[Jeanne Ibarra], [Measures].[*FORMATTED_MEASURE_0]}\n" + "Row #0: 50\n" + "Row #0: 21\n" + "Row #0: 31\n" + "Row #0: 42\n" + "Row #0: 110\n" + "Row #0: 59\n" + "Row #0: 42\n" + "Row #0: 157\n" + "Row #0: 146\n" + "Row #0: 78\n";
    final String query = "With\n" + "Set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Customers], Not IsEmpty ([Measures].[Unit Sales]))'\n" + "Set [*SORTED_COL_AXIS] as 'Order([*CJ_COL_AXIS],[Customers].CurrentMember.OrderKey,BASC,Ancestor([Customers].CurrentMember,[Customers].[City]).OrderKey,BASC)'\n" + "Set [*BASE_MEMBERS_Customers] as 'Filter([Customers].[Name].Members,[Customers].CurrentMember.Caption Matches (\"(?i).*\\Qjeanne\\E.*\"))'\n" + "Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'\n" + "Set [*CJ_COL_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Customers].currentMember)})'\n" + "Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Unit Sales]', FORMAT_STRING = 'Standard', SOLVE_ORDER=400\n" + "Select\n" + "CrossJoin([*SORTED_COL_AXIS],[*BASE_MEMBERS_Measures]) on columns\n" + "From [Sales]";
    assertQuerySqlOrNot(getTestContext(), query, patterns, false, true, true);
    assertQueryReturns(query, queryResults);
    verifySameNativeAndNot(query, null, getTestContext());
}

Example 85 with SqlPattern

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

the class NonEmptyTest method testNestedMeasureConstraintsGetOptimized.

public void testNestedMeasureConstraintsGetOptimized() {
    String mdx = "with member [Measures].[unit sales Male] as '([Measures].[Unit Sales],[Gender].[Gender].[M])' " + "member [Measures].[unit sales Male Married] as '([Measures].[unit sales Male],[Marital Status].[Marital Status].[M])' " + "select " + "non empty {[Measures].[unit sales Male Married]} 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\" = 'M') and (\"customer\".\"marital_status\" = 'M') 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\" = \"M\") " + "and (\"customer\".\"marital_status\" = \"M\") " + "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";
    SqlPattern pattern = new SqlPattern(Dialect.DatabaseProduct.ORACLE, sqlOracle, sqlOracle.length());
    assertQuerySql(mdx, new SqlPattern[] { pattern });
}