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

Example 41 with SqlPattern

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

the class NativizeSetFunDefTest method testEvaluationIsNonNativeWhenBelowHighcardThreshoold.

public void testEvaluationIsNonNativeWhenBelowHighcardThreshoold() {
    propSaver.set(MondrianProperties.instance().NativizeMinThreshold, 10000);
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.ACCESS, "select `customer`.`gender` as `c0` " + "from `customer` as `customer`, `sales_fact_1997` as `sales_fact_1997` " + "where `sales_fact_1997`.`customer_id` = `customer`.`customer_id` " + "and `customer`.`marital_status` = 'S' " + "group by `customer`.`gender` order by 1 ASC", 251) };
    String mdxQuery = "select non empty NativizeSet(" + "Crossjoin([Gender].[Gender].members,{[Time].[1997]})) on 0 " + "from [Warehouse and Sales] " + "where [Marital Status].[Marital Status].[S]";
    assertQuerySqlOrNot(getTestContext(), mdxQuery, patterns, true, false, true);
}

Example 42 with SqlPattern

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

the class NativizeSetFunDefTest method testSingleLevelDotMembersIsNativelyEvaluated.

public void testSingleLevelDotMembersIsNativelyEvaluated() {
    String mdx1 = "with member [Customers].[agg] as '" + "AGGREGATE({[Customers].[name].MEMBERS}, [Measures].[Unit Sales])'" + "select non empty NativizeSet({{[Customers].[name].members}, {[Customers].[agg]}}) on 0," + "non empty NativizeSet(" + "Crossjoin({[Gender].[Gender].[M]}," + "[Measures].[Unit Sales])) on 1 " + "from Sales";
    String mdx2 = "select non empty NativizeSet({[Customers].[name].members}) on 0," + "non empty NativizeSet(" + "Crossjoin({[Gender].[Gender].[M]}," + "[Measures].[Unit Sales])) on 1 " + "from Sales";
    String sql = "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') " + "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 oraclePattern = new SqlPattern(Dialect.DatabaseProduct.ORACLE, sql, sql.length());
    assertQuerySql(mdx1, new SqlPattern[] { oraclePattern });
    assertQuerySql(mdx2, new SqlPattern[] { oraclePattern });
}

Example 43 with SqlPattern

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

the class BatchTestCase method assertQuerySqlOrNot.

/**
 * During MDX query parse and execution, checks that the query results
 * (or does not result) in a particular SQL statement being generated.
 *
 * <p>Parses and executes the MDX query once for each SQL
 * pattern in the current dialect. If there are multiple patterns, runs the
 * MDX query multiple times, and expects to see each SQL statement appear.
 * If there are no patterns in this dialect, the test trivially succeeds.
 *
 * @param testContext non-default test context if required
 * @param mdxQuery MDX query
 * @param patterns Set of patterns
 * @param negative false to assert if SQL is generated;
 *                 true to assert if SQL is NOT generated
 * @param bypassSchemaCache whether to grab a new connection and bypass the
 *        schema cache before parsing the MDX query
 * @param clearCache whether to clear cache before executing the MDX query
 */
protected void assertQuerySqlOrNot(TestContext testContext, String mdxQuery, SqlPattern[] patterns, boolean negative, boolean bypassSchemaCache, boolean clearCache) {
    Connection connection = testContext.getConnection();
    mdxQuery = testContext.upgradeQuery(mdxQuery);
    // Run the test once for each pattern in this dialect.
    // (We could optimize and run it once, collecting multiple queries, and
    // comparing all queries at the end.)
    Dialect dialect = testContext.getDialect();
    Dialect.DatabaseProduct d = dialect.getDatabaseProduct();
    boolean patternFound = false;
    for (SqlPattern sqlPattern : patterns) {
        if (!sqlPattern.hasDatabaseProduct(d)) {
            // message if required.
            continue;
        }
        patternFound = true;
        String sql = sqlPattern.getSql();
        String trigger = sqlPattern.getTriggerSql();
        sql = dialectize(d, sql);
        trigger = dialectize(d, trigger);
        // Create a dummy DataSource which will throw a 'bomb' if it is
        // asked to execute a particular SQL statement, but will otherwise
        // behave exactly the same as the current DataSource.
        final TriggerHook hook = new TriggerHook(trigger);
        RolapUtil.setHook(hook);
        Bomb bomb = null;
        try {
            if (bypassSchemaCache) {
                connection = testContext.withSchemaPool(false).getConnection();
            }
            final Query query = connection.parseQuery(mdxQuery);
            if (clearCache) {
                clearCache((RolapCube) query.getCube());
            }
            final Result result = connection.execute(query);
            Util.discard(result);
            bomb = null;
        } catch (Bomb e) {
            bomb = e;
        } catch (RuntimeException e) {
            // Walk up the exception tree and see if the root cause
            // was a SQL bomb.
            bomb = Util.getMatchingCause(e, Bomb.class);
            if (bomb == null) {
                throw e;
            }
        } finally {
            RolapUtil.setHook(null);
        }
        if (negative) {
            if (bomb != null || hook.foundMatch()) {
                fail("forbidden query [" + sql + "] detected");
            }
        } else {
            if (bomb == null && !hook.foundMatch()) {
                fail("expected query [" + sql + "] did not occur");
            }
            if (bomb != null) {
                assertEquals(replaceQuotes(sql.replaceAll("\r\n", "\n")), replaceQuotes(bomb.sql.replaceAll("\r\n", "\n")));
            }
        }
    }
    // dialect.
    if (!patternFound) {
        String warnDialect = MondrianProperties.instance().WarnIfNoPatternForDialect.get();
        if (warnDialect.equals(d.toString())) {
            System.out.println("[No expected SQL statements found for dialect \"" + dialect.toString() + "\" and test not run]");
        }
    }
}

Example 44 with SqlPattern

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

the class FastBatchingCellReaderTest method testAggregateDistinctCount5.

/**
 * Fix a problem when genergating predicates for distinct count aggregate
 * loading and using the aggregate function in the slicer.
 */
public void testAggregateDistinctCount5() {
    // make sure tuple optimization will be used
    propSaver.set(propSaver.properties.MaxConstraints, 2);
    String query = "With " + "Set [Products] as " + " '{[Product].[Drink], " + "   [Product].[Food], " + "   [Product].[Non-Consumable]}' " + "Member [Product].[Selected Products] as " + " 'Aggregate([Products])', SOLVE_ORDER=2 " + "Select " + " {[Store].[Store State].Members} on rows, " + " {[Measures].[Customer Count]} on columns " + "From [Sales] " + "Where ([Product].[Selected Products])";
    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\" " + "where \"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" " + "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\"";
    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` " + "where `sales_fact_1997`.`store_id` = `store`.`store_id` " + "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.DERBY, derbySql, derbySql), new SqlPattern(Dialect.DatabaseProduct.MYSQL, mysqlSql, mysqlSql) };
    assertQuerySql(query, patterns);
}

Example 45 with SqlPattern

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

the class FastBatchingCellReaderTest method testLoadDistinctSqlMeasure.

/**
 * Checks that in dialects that request it (e.g. LucidDB),
 * distinct aggregates based on SQL expressions,
 * e.g. <code>count(distinct "col1" + "col2"), count(distinct query)</code>,
 * are loaded individually, and separately from the other aggregates.
 */
public void testLoadDistinctSqlMeasure() {
    // Some databases cannot handle scalar subqueries inside
    // count(distinct).
    final Dialect dialect = getTestContext().getDialect();
    switch(dialect.getDatabaseProduct()) {
        case ORACLE:
        // Oracle gives 'feature not supported' in Express 10.2
        case ACCESS:
        case TERADATA:
        // the 'select' keyword." in 12.0.
        case NEOVIEW:
        // aggregate function."
        case NETEZZA:
        // supported"
        case GREENPLUM:
        // Greenplum says 'Does not support yet that query'
        case VERTICA:
            // Vertica says "Aggregate function calls cannot contain subqueries"
            return;
    }
    String cube = "<Cube name=\"Warehouse2\">" + "   <Table name=\"warehouse\"/>" + "   <DimensionUsage name=\"Store Type\" source=\"Store Type\" foreignKey=\"stores_id\"/>" + "   <Measure name=\"Count Distinct of Warehouses (Large Owned)\" aggregator=\"distinct count\" formatString=\"#,##0\">" + "       <MeasureExpression>" + "       <SQL dialect=\"generic\">(select `warehouse_class`.`warehouse_class_id` AS `warehouse_class_id` from `warehouse_class` AS `warehouse_class` where `warehouse_class`.`warehouse_class_id` = `warehouse`.`warehouse_class_id` and `warehouse_class`.`description` = 'Large Owned')</SQL>" + "       </MeasureExpression>" + "   </Measure>" + "   <Measure name=\"Count Distinct of Warehouses (Large Independent)\" aggregator=\"distinct count\" formatString=\"#,##0\">" + "       <MeasureExpression>" + "       <SQL dialect=\"generic\">(select `warehouse_class`.`warehouse_class_id` AS `warehouse_class_id` from `warehouse_class` AS `warehouse_class` where `warehouse_class`.`warehouse_class_id` = `warehouse`.`warehouse_class_id` and `warehouse_class`.`description` = 'Large Independent')</SQL>" + "       </MeasureExpression>" + "   </Measure>" + "   <Measure name=\"Count All of Warehouses (Large Independent)\" aggregator=\"count\" formatString=\"#,##0\">" + "       <MeasureExpression>" + "           <SQL dialect=\"generic\">(select `warehouse_class`.`warehouse_class_id` AS `warehouse_class_id` from `warehouse_class` AS `warehouse_class` where `warehouse_class`.`warehouse_class_id` = `warehouse`.`warehouse_class_id` and `warehouse_class`.`description` = 'Large Independent')</SQL>" + "       </MeasureExpression>" + "   </Measure>" + "   <Measure name=\"Count Distinct Store+Warehouse\" aggregator=\"distinct count\" formatString=\"#,##0\">" + "       <MeasureExpression><SQL dialect=\"generic\">`store_id`+`warehouse_id`</SQL></MeasureExpression>" + "   </Measure>" + "   <Measure name=\"Count All Store+Warehouse\" aggregator=\"count\" formatString=\"#,##0\">" + "       <MeasureExpression><SQL dialect=\"generic\">`store_id`+`warehouse_id`</SQL></MeasureExpression>" + "   </Measure>" + "   <Measure name=\"Store Count\" column=\"stores_id\" aggregator=\"count\" formatString=\"#,###\"/>" + "</Cube>";
    cube = cube.replaceAll("`", dialect.getQuoteIdentifierString());
    if (dialect.getDatabaseProduct() == Dialect.DatabaseProduct.ORACLE) {
        cube = cube.replaceAll(" AS ", " ");
    }
    String query = "select " + "   [Store Type].Children on rows, " + "   {[Measures].[Count Distinct of Warehouses (Large Owned)]," + "    [Measures].[Count Distinct of Warehouses (Large Independent)]," + "    [Measures].[Count All of Warehouses (Large Independent)]," + "    [Measures].[Count Distinct Store+Warehouse]," + "    [Measures].[Count All Store+Warehouse]," + "    [Measures].[Store Count]} on columns " + "from [Warehouse2]";
    TestContext testContext = TestContext.instance().create(null, cube, null, null, null, null);
    String desiredResult = "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Measures].[Count Distinct of Warehouses (Large Owned)]}\n" + "{[Measures].[Count Distinct of Warehouses (Large Independent)]}\n" + "{[Measures].[Count All of Warehouses (Large Independent)]}\n" + "{[Measures].[Count Distinct Store+Warehouse]}\n" + "{[Measures].[Count All Store+Warehouse]}\n" + "{[Measures].[Store Count]}\n" + "Axis #2:\n" + "{[Store Type].[Deluxe Supermarket]}\n" + "{[Store Type].[Gourmet Supermarket]}\n" + "{[Store Type].[HeadQuarters]}\n" + "{[Store Type].[Mid-Size Grocery]}\n" + "{[Store Type].[Small Grocery]}\n" + "{[Store Type].[Supermarket]}\n" + "Row #0: 1\n" + "Row #0: 0\n" + "Row #0: 0\n" + "Row #0: 6\n" + "Row #0: 6\n" + "Row #0: 6\n" + "Row #1: 1\n" + "Row #1: 0\n" + "Row #1: 0\n" + "Row #1: 2\n" + "Row #1: 2\n" + "Row #1: 2\n" + "Row #2: \n" + "Row #2: \n" + "Row #2: \n" + "Row #2: \n" + "Row #2: \n" + "Row #2: \n" + "Row #3: 0\n" + "Row #3: 1\n" + "Row #3: 1\n" + "Row #3: 4\n" + "Row #3: 4\n" + "Row #3: 4\n" + "Row #4: 0\n" + "Row #4: 1\n" + "Row #4: 1\n" + "Row #4: 4\n" + "Row #4: 4\n" + "Row #4: 4\n" + "Row #5: 0\n" + "Row #5: 1\n" + "Row #5: 3\n" + "Row #5: 8\n" + "Row #5: 8\n" + "Row #5: 8\n";
    testContext.assertQueryReturns(query, desiredResult);
    String loadCountDistinct_luciddb1 = "select " + "\"store\".\"store_type\" as \"c0\", " + "count(distinct " + "(select \"warehouse_class\".\"warehouse_class_id\" AS \"warehouse_class_id\" " + "from \"warehouse_class\" AS \"warehouse_class\" " + "where \"warehouse_class\".\"warehouse_class_id\" = \"warehouse\".\"warehouse_class_id\" and \"warehouse_class\".\"description\" = 'Large Owned')) as \"m0\" " + "from \"store\" as \"store\", \"warehouse\" as \"warehouse\" " + "where \"warehouse\".\"stores_id\" = \"store\".\"store_id\" " + "group by \"store\".\"store_type\"";
    String loadCountDistinct_luciddb2 = "select " + "\"store\".\"store_type\" as \"c0\", " + "count(distinct " + "(select \"warehouse_class\".\"warehouse_class_id\" AS \"warehouse_class_id\" " + "from \"warehouse_class\" AS \"warehouse_class\" " + "where \"warehouse_class\".\"warehouse_class_id\" = \"warehouse\".\"warehouse_class_id\" and \"warehouse_class\".\"description\" = 'Large Independent')) as \"m0\" " + "from \"store\" as \"store\", \"warehouse\" as \"warehouse\" " + "where \"warehouse\".\"stores_id\" = \"store\".\"store_id\" " + "group by \"store\".\"store_type\"";
    String loadOtherAggs_luciddb = "select " + "\"store\".\"store_type\" as \"c0\", " + "count(" + "(select \"warehouse_class\".\"warehouse_class_id\" AS \"warehouse_class_id\" " + "from \"warehouse_class\" AS \"warehouse_class\" " + "where \"warehouse_class\".\"warehouse_class_id\" = \"warehouse\".\"warehouse_class_id\" and \"warehouse_class\".\"description\" = 'Large Independent')) as \"m0\", " + "count(distinct \"store_id\"+\"warehouse_id\") as \"m1\", " + "count(\"store_id\"+\"warehouse_id\") as \"m2\", " + "count(\"warehouse\".\"stores_id\") as \"m3\" " + "from \"store\" as \"store\", \"warehouse\" as \"warehouse\" " + "where \"warehouse\".\"stores_id\" = \"store\".\"store_id\" " + "group by \"store\".\"store_type\"";
    // Derby splits into multiple statements.
    String loadCountDistinct_derby1 = "select \"store\".\"store_type\" as \"c0\", count(distinct (select \"warehouse_class\".\"warehouse_class_id\" AS \"warehouse_class_id\" from \"warehouse_class\" AS \"warehouse_class\" where \"warehouse_class\".\"warehouse_class_id\" = \"warehouse\".\"warehouse_class_id\" and \"warehouse_class\".\"description\" = 'Large Owned')) as \"m0\" from \"store\" as \"store\", \"warehouse\" as \"warehouse\" where \"warehouse\".\"stores_id\" = \"store\".\"store_id\" group by \"store\".\"store_type\"";
    String loadCountDistinct_derby2 = "select \"store\".\"store_type\" as \"c0\", count(distinct (select \"warehouse_class\".\"warehouse_class_id\" AS \"warehouse_class_id\" from \"warehouse_class\" AS \"warehouse_class\" where \"warehouse_class\".\"warehouse_class_id\" = \"warehouse\".\"warehouse_class_id\" and \"warehouse_class\".\"description\" = 'Large Independent')) as \"m0\" from \"store\" as \"store\", \"warehouse\" as \"warehouse\" where \"warehouse\".\"stores_id\" = \"store\".\"store_id\" group by \"store\".\"store_type\"";
    String loadCountDistinct_derby3 = "select \"store\".\"store_type\" as \"c0\", count(distinct \"store_id\"+\"warehouse_id\") as \"m0\" from \"store\" as \"store\", \"warehouse\" as \"warehouse\" where \"warehouse\".\"stores_id\" = \"store\".\"store_id\" group by \"store\".\"store_type\"";
    String loadOtherAggs_derby = "select \"store\".\"store_type\" as \"c0\", count((select \"warehouse_class\".\"warehouse_class_id\" AS \"warehouse_class_id\" from \"warehouse_class\" AS \"warehouse_class\" where \"warehouse_class\".\"warehouse_class_id\" = \"warehouse\".\"warehouse_class_id\" and \"warehouse_class\".\"description\" = 'Large Independent')) as \"m0\", count(\"store_id\"+\"warehouse_id\") as \"m1\", count(\"warehouse\".\"stores_id\") as \"m2\" from \"store\" as \"store\", \"warehouse\" as \"warehouse\" where \"warehouse\".\"stores_id\" = \"store\".\"store_id\" group by \"store\".\"store_type\"";
    // MySQL does it in one statement.
    String load_mysql = "select" + " `store`.`store_type` as `c0`," + " count(distinct (select `warehouse_class`.`warehouse_class_id` AS `warehouse_class_id` from `warehouse_class` AS `warehouse_class` where `warehouse_class`.`warehouse_class_id` = `warehouse`.`warehouse_class_id` and `warehouse_class`.`description` = 'Large Owned')) as `m0`," + " count(distinct (select `warehouse_class`.`warehouse_class_id` AS `warehouse_class_id` from `warehouse_class` AS `warehouse_class` where `warehouse_class`.`warehouse_class_id` = `warehouse`.`warehouse_class_id` and `warehouse_class`.`description` = 'Large Independent')) as `m1`," + " count((select `warehouse_class`.`warehouse_class_id` AS `warehouse_class_id` from `warehouse_class` AS `warehouse_class` where `warehouse_class`.`warehouse_class_id` = `warehouse`.`warehouse_class_id` and `warehouse_class`.`description` = 'Large Independent')) as `m2`," + " count(distinct `store_id`+`warehouse_id`) as `m3`," + " count(`store_id`+`warehouse_id`) as `m4`," + " count(`warehouse`.`stores_id`) as `m5` " + "from `store` as `store`," + " `warehouse` as `warehouse` " + "where `warehouse`.`stores_id` = `store`.`store_id` " + "group by `store`.`store_type`";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.LUCIDDB, loadCountDistinct_luciddb1, loadCountDistinct_luciddb1), new SqlPattern(Dialect.DatabaseProduct.LUCIDDB, loadCountDistinct_luciddb2, loadCountDistinct_luciddb2), new SqlPattern(Dialect.DatabaseProduct.LUCIDDB, loadOtherAggs_luciddb, loadOtherAggs_luciddb), new SqlPattern(Dialect.DatabaseProduct.DERBY, loadCountDistinct_derby1, loadCountDistinct_derby1), new SqlPattern(Dialect.DatabaseProduct.DERBY, loadCountDistinct_derby2, loadCountDistinct_derby2), new SqlPattern(Dialect.DatabaseProduct.DERBY, loadCountDistinct_derby3, loadCountDistinct_derby3), new SqlPattern(Dialect.DatabaseProduct.DERBY, loadOtherAggs_derby, loadOtherAggs_derby), new SqlPattern(Dialect.DatabaseProduct.MYSQL, load_mysql, load_mysql) };
    assertQuerySql(testContext, query, patterns);
}