public void testNativeFilterWithCompoundSlicer_1() { propSaver.set(MondrianProperties.instance().GenerateFormattedSql, true); final String mdx = "with member [measures].[avgQtrs] as 'count(filter([Customers].[Name].Members, [Measures].[Unit Sales] > 0))' " + "select [measures].[avgQtrs] on 0 from sales where ( {[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer], [Product].[Food].[Baked Goods].[Bread].[Muffins]} )"; if (MondrianProperties.instance().EnableNativeFilter.get() && MondrianProperties.instance().EnableNativeNonEmpty.get()) { boolean requiresOrderByAlias = TestContext.instance().getDialect().requiresOrderByAlias(); final String sqlMysql = propSaver.properties.UseAggregates.get() == false ? "select\n" + " `customer`.`country` as `c0`,\n" + " `customer`.`state_province` as `c1`,\n" + " `customer`.`city` as `c2`,\n" + " `customer`.`customer_id` as `c3`,\n" + " CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c4`,\n" + " CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c5`,\n" + " `customer`.`gender` as `c6`,\n" + " `customer`.`marital_status` as `c7`,\n" + " `customer`.`education` as `c8`,\n" + " `customer`.`yearly_income` as `c9`\n" + "from\n" + " `customer` as `customer`,\n" + " `sales_fact_1997` as `sales_fact_1997`,\n" + " `time_by_day` as `time_by_day`,\n" + " `product_class` as `product_class`,\n" + " `product` as `product`\n" + "where\n" + " `sales_fact_1997`.`customer_id` = `customer`.`customer_id`\n" + "and\n" + " `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`\n" + "and\n" + " `time_by_day`.`the_year` = 1997\n" + "and\n" + " `sales_fact_1997`.`product_id` = `product`.`product_id`\n" // + "and\n" + " `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + " `product_class`.`product_family` in ('Drink', 'Food')\n" + "and\n" + " `product_class`.`product_department` in ('Alcoholic Beverages', 'Baked Goods')\n" + "and\n" + " `product_class`.`product_category` in ('Beer and Wine', 'Bread')\n" + "and\n" + " `product_class`.`product_subcategory` in ('Beer', 'Muffins')\n" + "group by\n" + " `customer`.`country`,\n" + " `customer`.`state_province`,\n" + " `customer`.`city`,\n" + " `customer`.`customer_id`,\n" + " CONCAT(`customer`.`fname`, ' ', `customer`.`lname`),\n" + " `customer`.`gender`,\n" + " `customer`.`marital_status`,\n" + " `customer`.`education`,\n" + " `customer`.`yearly_income`\n" + "having\n" + " (sum(`sales_fact_1997`.`unit_sales`) > 0)\n" // ^^^^ This is what we are interested in. ^^^^ + "order by\n" + (requiresOrderByAlias ? " ISNULL(`c0`) ASC, `c0` ASC,\n" + " ISNULL(`c1`) ASC, `c1` ASC,\n" + " ISNULL(`c2`) ASC, `c2` ASC,\n" + " ISNULL(`c4`) ASC, `c4` ASC" : " ISNULL(`customer`.`country`) ASC, `customer`.`country` ASC,\n" + " ISNULL(`customer`.`state_province`) ASC, `customer`.`state_province` ASC,\n" + " ISNULL(`customer`.`city`) ASC, `customer`.`city` ASC,\n" + " ISNULL(CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)) ASC, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) ASC") : "select\n" + " `customer`.`country` as `c0`,\n" + " `customer`.`state_province` as `c1`,\n" + " `customer`.`city` as `c2`,\n" + " `customer`.`customer_id` as `c3`,\n" + " CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c4`,\n" + " CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c5`,\n" + " `customer`.`gender` as `c6`,\n" + " `customer`.`marital_status` as `c7`,\n" + " `customer`.`education` as `c8`,\n" + " `customer`.`yearly_income` as `c9`\n" + "from\n" + " `customer` as `customer`,\n" + " `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`,\n" + " `product_class` as `product_class`,\n" + " `product` as `product`\n" + "where\n" + " `agg_c_14_sales_fact_1997`.`customer_id` = `customer`.`customer_id`\n" + "and\n" + " `agg_c_14_sales_fact_1997`.`the_year` = 1997\n" + "and\n" + " `agg_c_14_sales_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + " `product`.`product_class_id` = `product_class`.`product_class_id`\n" + "and\n" + " `product_class`.`product_family` in ('Drink', 'Food')\n" + "and\n" + " `product_class`.`product_department` in ('Alcoholic Beverages', 'Baked Goods')\n" + "and\n" + " `product_class`.`product_category` in ('Beer and Wine', 'Bread')\n" + "and\n" + " `product_class`.`product_subcategory` in ('Beer', 'Muffins')\n" + "group by\n" + " `customer`.`country`,\n" + " `customer`.`state_province`,\n" + " `customer`.`city`,\n" + " `customer`.`customer_id`,\n" + " CONCAT(`customer`.`fname`, ' ', `customer`.`lname`),\n" + " `customer`.`gender`,\n" + " `customer`.`marital_status`,\n" + " `customer`.`education`,\n" + " `customer`.`yearly_income`\n" + "having\n" + " (sum(`agg_c_14_sales_fact_1997`.`unit_sales`) > 0)\n" // ^^^^ This is what we are interested in. ^^^^ + "order by\n" + " ISNULL(`customer`.`country`) ASC, `customer`.`country` ASC,\n" + " ISNULL(`customer`.`state_province`) ASC, `customer`.`state_province` ASC,\n" + " ISNULL(`customer`.`city`) ASC, `customer`.`city` ASC,\n" + " ISNULL(CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)) ASC, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) ASC"; final SqlPattern[] patterns = mysqlPattern(sqlMysql); // Make sure the tuples list is using the HAVING clause. assertQuerySqlOrNot(getTestContext(), mdx, patterns, false, true, true); } // Make sure the numbers are right assertQueryReturns( mdx, "Axis #0:\n" + "{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer]}\n" + "{[Product].[Food].[Baked Goods].[Bread].[Muffins]}\n" + "Axis #1:\n" + "{[Measures].[avgQtrs]}\n" + "Row #0: 1,281\n"); }
public void testNativeFilterWithCompoundSlicer() { propSaver.set(MondrianProperties.instance().GenerateFormattedSql, true); final String mdx = "with member measures.avgQtrs as 'avg( filter( time.quarter.members, measures.[unit sales] > 80))' " + "select measures.avgQtrs * gender.members on 0 from sales where head( product.[product name].members, 3)"; if (MondrianProperties.instance().EnableNativeFilter.get() && MondrianProperties.instance().EnableNativeNonEmpty.get()) { boolean requiresOrderByAlias = TestContext.instance().getDialect().requiresOrderByAlias(); final String sqlMysql = propSaver.properties.UseAggregates.get() == false ? "select\n" + " `time_by_day`.`the_year` as `c0`,\n" + " `time_by_day`.`quarter` as `c1`\n" + "from\n" + " `time_by_day` as `time_by_day`,\n" + " `sales_fact_1997` as `sales_fact_1997`,\n" + " `product` as `product`,\n" + " `customer` as `customer`\n" + "where\n" + " `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`\n" + "and\n" + " `sales_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + " `product`.`product_name` in ('Good Imported Beer', 'Good Light Beer', 'Pearl Imported Beer')\n" + "and\n" + " `sales_fact_1997`.`customer_id` = `customer`.`customer_id`\n" + "and\n" + " `customer`.`gender` = 'M'\n" + "group by\n" + " `time_by_day`.`the_year`,\n" + " `time_by_day`.`quarter`\n" + "having\n" + " (sum(`sales_fact_1997`.`unit_sales`) > 80)\n" + "order by\n" + (requiresOrderByAlias ? " ISNULL(`c0`) ASC, `c0` ASC,\n" + " ISNULL(`c1`) ASC, `c1` ASC" : " ISNULL(`time_by_day`.`the_year`) ASC, `time_by_day`.`the_year` ASC,\n" + " ISNULL(`time_by_day`.`quarter`) ASC, `time_by_day`.`quarter` ASC") : "select\n" + " `agg_c_14_sales_fact_1997`.`the_year` as `c0`,\n" + " `agg_c_14_sales_fact_1997`.`quarter` as `c1`\n" + "from\n" + " `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`,\n" + " `product` as `product`,\n" + " `customer` as `customer`\n" + "where\n" + " `agg_c_14_sales_fact_1997`.`product_id` = `product`.`product_id`\n" + "and\n" + " `product`.`product_name` in ('Good Imported Beer', 'Good Light Beer', 'Pearl Imported Beer')\n" + "and\n" + " `agg_c_14_sales_fact_1997`.`customer_id` = `customer`.`customer_id`\n" + "and\n" + " `customer`.`gender` = 'M'\n" + "group by\n" + " `agg_c_14_sales_fact_1997`.`the_year`,\n" + " `agg_c_14_sales_fact_1997`.`quarter`\n" + "having\n" + " (sum(`agg_c_14_sales_fact_1997`.`unit_sales`) > 80)\n" + "order by\n" + (requiresOrderByAlias ? " ISNULL(`c0`) ASC, `c0` ASC,\n" + " ISNULL(`c1`) ASC, `c1` ASC" : " ISNULL(`agg_c_14_sales_fact_1997`.`the_year`) ASC, `agg_c_14_sales_fact_1997`.`the_year` ASC,\n" + " ISNULL(`agg_c_14_sales_fact_1997`.`quarter`) ASC, `agg_c_14_sales_fact_1997`.`quarter` ASC"); final SqlPattern[] patterns = mysqlPattern(sqlMysql); // Make sure the tuples list is using the HAVING clause. assertQuerySqlOrNot(getTestContext(), mdx, patterns, false, true, true); } // Make sure the numbers are right assertQueryReturns( mdx, "Axis #0:\n" + "{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good].[Good Imported Beer]}\n" + "{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good].[Good Light Beer]}\n" + "{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Pearl].[Pearl Imported Beer]}\n" + "Axis #1:\n" + "{[Measures].[avgQtrs], [Gender].[All Gender]}\n" + "{[Measures].[avgQtrs], [Gender].[F]}\n" + "{[Measures].[avgQtrs], [Gender].[M]}\n" + "Row #0: 111\n" + "Row #0: \n" + "Row #0: \n"); }