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

Example 96 with SqlPattern

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

the class GroupingSetQueryTest method testNotUsingGroupingSetWhenGroupUsesDifferentAggregateTable.

public void testNotUsingGroupingSetWhenGroupUsesDifferentAggregateTable() {
    if (!(prop.UseAggregates.get() && prop.ReadAggregates.get())) {
        return;
    }
    CellRequest request1 = createRequest(cubeNameSales, measureUnitSales, tableCustomer, fieldGender, "M");
    CellRequest request2 = createRequest(cubeNameSales, measureUnitSales, tableCustomer, fieldGender, "F");
    CellRequest request3 = createRequest(cubeNameSales, measureUnitSales, null, "", "");
    propSaver.set(prop.EnableGroupingSets, true);
    SqlPattern[] patternsWithoutGsets = { new SqlPattern(Dialect.DatabaseProduct.ACCESS, "select \"agg_g_ms_pcat_sales_fact_1997\".\"gender\" as \"c0\", " + "sum(\"agg_g_ms_pcat_sales_fact_1997\".\"unit_sales\") as \"m0\" " + "from \"agg_g_ms_pcat_sales_fact_1997\" as \"agg_g_ms_pcat_sales_fact_1997\" " + "group by \"agg_g_ms_pcat_sales_fact_1997\".\"gender\"", 26), new SqlPattern(ORACLE_TERADATA, "select \"agg_g_ms_pcat_sales_fact_1997\".\"gender\" as \"c0\", " + "sum(\"agg_g_ms_pcat_sales_fact_1997\".\"unit_sales\") as \"m0\" " + "from \"agg_g_ms_pcat_sales_fact_1997\" \"agg_g_ms_pcat_sales_fact_1997\" " + "group by \"agg_g_ms_pcat_sales_fact_1997\".\"gender\"", 26) };
    assertRequestSql(new CellRequest[] { request3, request1, request2 }, patternsWithoutGsets);
}

Example 97 with SqlPattern

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

the class GroupingSetQueryTest method testGroupingSetForMultipleMeasureAndSingleConstraint.

public void testGroupingSetForMultipleMeasureAndSingleConstraint() {
    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, measureStoreSales, tableCustomer, fieldGender, "M");
    CellRequest request5 = createRequest(cubeNameSales2, measureStoreSales, tableCustomer, fieldGender, "F");
    CellRequest request6 = createRequest(cubeNameSales2, measureStoreSales, null, "", "");
    SqlPattern[] patternsWithGsets = { new SqlPattern(ORACLE_TERADATA, "select \"customer\".\"gender\" as \"c0\", sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\", " + "sum(\"sales_fact_1997\".\"store_sales\") as \"m1\", 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) };
    assertRequestSql(new CellRequest[] { request1, request2, request3, request4, request5, request6 }, patternsWithGsets);
    propSaver.set(prop.EnableGroupingSets, false);
    SqlPattern[] patternsWithoutGsets = { new SqlPattern(Dialect.DatabaseProduct.ACCESS, "select \"customer\".\"gender\" as \"c0\", sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\", " + "sum(\"sales_fact_1997\".\"store_sales\") as \"m1\" " + "from \"customer\" as \"customer\", \"sales_fact_1997\" as \"sales_fact_1997\" " + "where \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\" " + "group by \"customer\".\"gender\"", 26), new SqlPattern(ORACLE_TERADATA, "select \"customer\".\"gender\" as \"c0\", sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\", " + "sum(\"sales_fact_1997\".\"store_sales\") as \"m1\" " + "from \"customer\" =as= \"customer\", \"sales_fact_1997\" =as= \"sales_fact_1997\" " + "where \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\" " + "group by \"customer\".\"gender\"", 26) };
    assertRequestSql(new CellRequest[] { request1, request2, request3, request4, request5, request6 }, patternsWithoutGsets);
}

Example 98 with SqlPattern

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

the class MultipleColsInTupleAggTest method testNativeFilterWithoutMeasuresAndLevelWithProps.

public void testNativeFilterWithoutMeasuresAndLevelWithProps() throws Exception {
    if (!isApplicable()) {
        return;
    }
    // similar to the previous test, but verifies a case where
    // a level property is the extra column that requires joining
    // agg star back to the dim table.  This test also uses the bottom
    // level of the dim
    final String query = "select " + "Filter([Product].[Product Name].members, " + "Product.CurrentMember.Caption MATCHES (\"(?i).*Two.*\") )" + " on columns " + "from [Fact] ";
    getTestContext().assertQueryReturns(query, "Axis #0:\n" + "{}\n" + "Axis #1:\n" + "{[Product].[Cat One].[Prod Cat One].[Two]}\n" + "Row #0: 6\n");
    // check generated sql only for native evaluation
    if (MondrianProperties.instance().EnableNativeFilter.get()) {
        assertQuerySql(query, new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.MYSQL, "select\n" + "    `cat`.`cat` as `c0`,\n" + "    `cat`.`cap` as `c1`,\n" + "    `cat`.`ord` as `c2`,\n" + "    `cat`.`name3` as `c3`,\n" + "    `product_cat`.`name2` as `c4`,\n" + "    `product_cat`.`cap` as `c5`,\n" + "    `product_cat`.`ord` as `c6`,\n" + "    `test_lp_xx2_fact`.`prodname` as `c7`,\n" + "    `product_csv`.`color` as `c8`\n" + "from\n" + "    `product_csv` as `product_csv`,\n" + "    `product_cat` as `product_cat`,\n" + "    `cat` as `cat`,\n" + "    `test_lp_xx2_fact` as `test_lp_xx2_fact`\n" + "where\n" + "    `product_cat`.`cat` = `cat`.`cat`\n" + "and\n" + "    `product_csv`.`prod_cat` = `product_cat`.`prod_cat`\n" + "and\n" + "    `product_csv`.`name1` = `test_lp_xx2_fact`.`prodname`\n" + "group by\n" + "    `cat`.`cat`,\n" + "    `cat`.`cap`,\n" + "    `cat`.`ord`,\n" + "    `cat`.`name3`,\n" + "    `product_cat`.`name2`,\n" + "    `product_cat`.`cap`,\n" + "    `product_cat`.`ord`,\n" + "    `test_lp_xx2_fact`.`prodname`,\n" + "    `product_csv`.`color`\n" + "having\n" + "    c7 IS NOT NULL AND UPPER(c7) REGEXP '.*TWO.*'\n" + "order by\n" + (TestContext.instance().getDialect().requiresOrderByAlias() ? "    ISNULL(`c2`) ASC, `c2` ASC,\n" + "    ISNULL(`c6`) ASC, `c6` ASC,\n" + "    ISNULL(`c7`) ASC, `c7` ASC" : "    ISNULL(`cat`.`ord`) ASC, `cat`.`ord` ASC,\n" + "    ISNULL(`product_cat`.`ord`) ASC, `product_cat`.`ord` ASC,\n" + "    ISNULL(`test_lp_xx2_fact`.`prodname`) ASC, " + "`test_lp_xx2_fact`.`prodname` ASC"), null) });
    }
    Axis axis = getTestContext().withCube("Fact").executeAxis("Filter([Product].[Product Name].members, " + "Product.CurrentMember.Caption MATCHES (\"(?i).*Two.*\") )");
    assertEquals("Member property value was not loaded correctly.", "Black", ((RolapAxis) axis).getTupleList().get(0).get(0).getPropertyValue("Product Color"));
}

Example 99 with SqlPattern

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

the class SelectNotInGroupByTest method testGroupBySkippedIfUniqueLevel.

public void testGroupBySkippedIfUniqueLevel() {
    // If unique level is included and all properties are level
    // dependent, then group by can be skipped regardless of dialect
    SqlPattern[] sqlPatterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlWithNoGroupBy, sqlWithNoGroupBy) };
    // Use dimension with unique level & level-dependent properties
    TestContext tc = TestContext.instance().create(storeDimensionUniqueLevelDependentProp, cubeA, null, null, null, null);
    assertQuerySqlOrNot(tc, queryCubeA, sqlPatterns, false, false, true);
}

Example 100 with SqlPattern

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

the class SelectNotInGroupByTest method testIndependentPropertyNotSkipped.

public void testIndependentPropertyNotSkipped() {
    SqlPattern[] sqlPatterns = { new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlWithAllGroupBy, sqlWithAllGroupBy) };
    // Use dimension with level-independent property
    TestContext tc = TestContext.instance().create(storeDimensionLevelIndependent, cubeA, null, null, null, null);
    assertQuerySqlOrNot(tc, queryCubeA, sqlPatterns, false, false, true);
}