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

Example 76 with SqlPattern

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

the class AggregationOnDistinctCountMeasuresTest method testCrossJoinMembersWithASingleMember.

public void testCrossJoinMembersWithASingleMember() {
    // make sure tuple optimization will be used
    propSaver.set(propSaver.properties.MaxConstraints, 1);
    String query = "WITH MEMBER GENDER.X AS 'AGGREGATE({[GENDER].[GENDER].members} * " + "{[STORE].[ALL STORES].[USA].[CA]})', solve_order=100 " + "SELECT GENDER.X ON 0, [MEASURES].[CUSTOMER COUNT] ON 1 FROM SALES";
    String result = "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Gender].[X]}\n" + "Axis #2:\n" + "{[Measures].[Customer Count]}\n" + "Row #0: 2,716\n";
    assertQueryReturns(query, result);
    // Check aggregate loading sql pattern
    String mysqlSql = "select `time_by_day`.`the_year` as `c0`, " + "count(distinct `sales_fact_1997`.`customer_id`) as `m0` " + "from `time_by_day` as `time_by_day`, `sales_fact_1997` as `sales_fact_1997`, `store` as `store` " + "where `sales_fact_1997`.`time_id` = `time_by_day`.`time_id` " + "and `time_by_day`.`the_year` = 1997 " + "and `sales_fact_1997`.`store_id` = `store`.`store_id` and `store`.`store_state` = 'CA' " + "group by `time_by_day`.`the_year`";
    String oraTeraSql = "select \"time_by_day\".\"the_year\" as \"c0\", " + "count(distinct \"sales_fact_1997\".\"customer_id\") as \"m0\" " + "from \"time_by_day\" =as= \"time_by_day\", \"sales_fact_1997\" =as= \"sales_fact_1997\", \"store\" =as= \"store\" " + "where \"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" and \"time_by_day\".\"the_year\" = 1997 " + "and \"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" and \"store\".\"store_state\" = 'CA' " + "group by \"time_by_day\".\"the_year\"";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, mysqlSql, mysqlSql), new SqlPattern(Dialect.DatabaseProduct.ORACLE, oraTeraSql, oraTeraSql), new SqlPattern(Dialect.DatabaseProduct.TERADATA, oraTeraSql, oraTeraSql) };
    assertQuerySql(query, patterns);
}

Example 77 with SqlPattern

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

the class AggregationOnDistinctCountMeasuresTest method testCanNotBatchForDifferentCompoundPredicate.

public void testCanNotBatchForDifferentCompoundPredicate() {
    propSaver.set(props.EnableGroupingSets, true);
    String mdxQueryWithFewMembers = "WITH " + "MEMBER [Store].[COG_OQP_USR_Aggregate(Store)] AS " + "'AGGREGATE({[Store].[All Stores].[USA].[CA], [Store].[All Stores].[USA].[OR],[Store].[All Stores].[USA].[WA]})', SOLVE_ORDER = 8" + "SELECT {[Measures].[Customer Count]} ON AXIS(0), " + "{[Store].[All Stores].[USA].[CA], [Store].[All Stores].[USA].[OR], [Store].[COG_OQP_USR_Aggregate(Store)]} " + "ON AXIS(1) " + "FROM [Sales]";
    String desiredResult = "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Customer Count]}\n" + "Axis #2:\n" + "{[Store].[USA].[CA]}\n" + "{[Store].[USA].[OR]}\n" + "{[Store].[COG_OQP_USR_Aggregate(Store)]}\n" + "Row #0: 2,716\n" + "Row #1: 1,037\n" + "Row #2: 5,581\n";
    String oraTeraSqlForAgg = "select \"time_by_day\".\"the_year\" as \"c0\", " + "count(distinct \"sales_fact_1997\".\"customer_id\") as \"m0\" " + "from \"time_by_day\" =as= \"time_by_day\", " + "\"sales_fact_1997\" =as= \"sales_fact_1997\", \"store\" =as= \"store\" " + "where \"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" " + "and \"time_by_day\".\"the_year\" = 1997 and " + "\"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" " + "and \"store\".\"store_state\" in (\"CA\", \"OR\", \"WA\") " + "group by \"time_by_day\".\"the_year\"";
    String oraTeraSqlForDetail = "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\" " + "where \"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" " + "and \"store\".\"store_state\" in ('CA', 'OR') " + "and \"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" " + "and \"time_by_day\".\"the_year\" = 1997 " + "group by \"store\".\"store_state\", \"time_by_day\".\"the_year\"";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.ORACLE, oraTeraSqlForAgg, oraTeraSqlForAgg), new SqlPattern(Dialect.DatabaseProduct.TERADATA, oraTeraSqlForAgg, oraTeraSqlForAgg), new SqlPattern(Dialect.DatabaseProduct.ORACLE, oraTeraSqlForDetail, oraTeraSqlForDetail), new SqlPattern(Dialect.DatabaseProduct.TERADATA, oraTeraSqlForDetail, oraTeraSqlForDetail) };
    assertQueryReturns(mdxQueryWithFewMembers, desiredResult);
    assertQuerySql(mdxQueryWithFewMembers, patterns);
}

Example 78 with SqlPattern

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

the class AggregationOnDistinctCountMeasuresTest method testMultiLevelMembersNullParents.

public void testMultiLevelMembersNullParents() {
    if (!isDefaultNullMemberRepresentation()) {
        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=\"Cost Count\" column=\"warehouse_cost\" aggregator=\"distinct-count\"/>\n" + "</Cube>";
    String query = "with set [Filtered Warehouse Set] as " + "{[Warehouse2].[#null].[#null].[5617 Saclan Terrace].[Arnold and Sons]," + " [Warehouse2].[#null].[#null].[3377 Coachman Place].[Jones International]} " + "member [Warehouse2].[TwoMembers] as 'AGGREGATE([Filtered Warehouse Set])' " + "select {[Measures].[Cost Count]} on columns, {[Warehouse2].[TwoMembers]} on rows " + "from [Warehouse2]";
    String necjSqlDerby = "select count(distinct \"inventory_fact_1997\".\"warehouse_cost\") as \"m0\" " + "from \"warehouse\" as \"warehouse\", " + "\"inventory_fact_1997\" as \"inventory_fact_1997\" " + "where \"inventory_fact_1997\".\"warehouse_id\" = \"warehouse\".\"warehouse_id\" " + "and ((\"warehouse\".\"warehouse_name\" = 'Arnold and Sons' " + "and \"warehouse\".\"wa_address1\" = '5617 Saclan Terrace' " + "and \"warehouse\".\"wa_address2\" is null) " + "or (\"warehouse\".\"warehouse_name\" = 'Jones International' " + "and \"warehouse\".\"wa_address1\" = '3377 Coachman Place' " + "and \"warehouse\".\"wa_address2\" is null))";
    String necjSqlMySql = "select count(distinct `inventory_fact_1997`.`warehouse_cost`) as `m0` " + "from `warehouse` as `warehouse`, `inventory_fact_1997` as `inventory_fact_1997` " + "where `inventory_fact_1997`.`warehouse_id` = `warehouse`.`warehouse_id` " + "and ((`warehouse`.`wa_address2` is null " + "and (`warehouse`.`wa_address1`, `warehouse`.`warehouse_name`) " + "in (('5617 Saclan Terrace', 'Arnold and Sons'), " + "('3377 Coachman Place', 'Jones International'))))";
    TestContext testContext = TestContext.instance().create(dimension, cube, null, null, null, null);
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.DERBY, necjSqlDerby, necjSqlDerby), new SqlPattern(Dialect.DatabaseProduct.MYSQL, necjSqlMySql, necjSqlMySql) };
    assertQuerySql(testContext, query, patterns);
}

Example 79 with SqlPattern

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

the class SegmentLoaderTest method testRollup.

public void testRollup() throws Exception {
    for (boolean rollup : new Boolean[] { true, false }) {
        PrintWriter pw = new PrintWriter(System.out);
        getConnection().getCacheControl(pw).flushSchemaCache();
        pw.flush();
        propSaver.set(MondrianProperties.instance().DisableCaching, true);
        propSaver.set(MondrianProperties.instance().EnableInMemoryRollup, rollup);
        final String queryOracle = "select \"time_by_day\".\"the_year\" as \"c0\", sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\" from \"time_by_day\" \"time_by_day\", \"sales_fact_1997\" \"sales_fact_1997\" where \"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" group by \"time_by_day\".\"the_year\"";
        final String queryMySQL = "select `time_by_day`.`the_year` as `c0`, sum(`sales_fact_1997`.`unit_sales`) as `m0` from `time_by_day` as `time_by_day`, `sales_fact_1997` as `sales_fact_1997` where `sales_fact_1997`.`time_id` = `time_by_day`.`time_id` group by `time_by_day`.`the_year`";
        executeQuery("select {[Store].[Store Country].Members} on rows, {[Time].[Time].[Year].Members} on columns from [Sales]");
        assertQuerySqlOrNot(getTestContext(), "select {[Time].[Time].[Year].Members} on columns from [Sales]", new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.ORACLE, queryOracle, queryOracle.length()), new SqlPattern(Dialect.DatabaseProduct.MYSQL, queryMySQL, queryMySQL.length()) }, rollup, false, false);
    }
}

Example 80 with SqlPattern

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

the class AggMeasureFactCountTest method assertQuerySql.

private void assertQuerySql(String query, String schema, String sql) {
    TestContext testContext = getTestContext().withSchema(schema).withFreshConnection();
    assertQuerySql(testContext, query, new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.MYSQL, sql, sql.length()) });
}