From dd34042ec17557f50994bdf09846b52755a6fd6b Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 29 Apr 2007 16:04:43 -0700 Subject: Fixed bug #24856: the result set of a ROLLUP query with DISTINCT could lack some rollup rows (rows with NULLs for grouping attributes) if GROUP BY list contained constant expressions. This happened because the results of constant expressions were not put in the temporary table used for duplicate elimination. In fact a constant item from the GROUP BY list of a ROLLUP query can be replaced for an Item_null_result object when a rollup row is produced . Now the JOIN::rollup_init function wraps any constant item referenced in the GROYP BY list of a ROLLUP query into an Item_func object of a special class that is never detected as constant item. This ensures creation of fields for such constant items in temporary tables and guarantees right results when the result of the rollup operation first has to be written into a temporary table, e.g. in the cases when duplicate elimination is required. mysql-test/r/olap.result: Added a test case for bug #24856. mysql-test/t/olap.test: Added a test case for bug #24856. sql/item_func.h: Fixed bug #24856: the result set of a ROLLUP query with DISTINCT could lack some rollup rows (rows with NULLs for grouping attributes) if GROUP BY list contained constant expressions. Itroduced class Item_func_rollup_const derived from Item_func. The object of this class are never detected as constant items. We use them for wrapping constant items from the GROUP BY list of any ROLLUP query. This wrapping allows us to ensure writing constant items into temporary tables whenever the result of the ROLLUP operation has to be written into a temporary table, e.g. when ROLLUP is used together with DISTINCT in the SELECT list. sql/sql_select.cc: Fixed bug #24856: the result set of a ROLLUP query with DISTINCT could lack some rollup rows (rows with NULLs for grouping attributes) if GROUP BY list contained constant expressions. Now the JOIN::rollup_init function wraps any constant item referenced in the GROYP BY list of a ROLLUP query into an Item_func object of a special class that is never detected as constant item. This ensures creation of fields for such constant items in temporary tables and guarantees right results when the result of the rollup operation first has to be written into a temporary table, e.g. in the cases when duplicate elimination is required. --- mysql-test/t/olap.test | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) (limited to 'mysql-test/t/olap.test') diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test index 683e1402678..99db3874883 100644 --- a/mysql-test/t/olap.test +++ b/mysql-test/t/olap.test @@ -281,4 +281,23 @@ select left(a,10), a, sum(b) from t1 group by 1,2 with rollup; select left(a,10) x, a, sum(b) from t1 group by x,a with rollup; drop table t1; +# +# Bug #20825: ROLLUP by const item in a query with DISTINCT +# + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 + VALUES (2,10),(3,30),(2,40),(1,10),(2,30),(1,20),(2,10); + +SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP; +SELECT DISTINCT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP; +SELECT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP; +SELECT DISTINCT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP; + +SELECT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP; +SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP; +SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP; + +DROP TABLE t1; + # End of 4.1 tests -- cgit v1.2.1 From c004ad09444b8b3be367b6620aa4f0716684bdc3 Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 29 Apr 2007 18:28:58 -0700 Subject: Fix in comments. --- mysql-test/t/olap.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/t/olap.test') diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test index 99db3874883..61c1bd45e5f 100644 --- a/mysql-test/t/olap.test +++ b/mysql-test/t/olap.test @@ -282,7 +282,7 @@ select left(a,10) x, a, sum(b) from t1 group by x,a with rollup; drop table t1; # -# Bug #20825: ROLLUP by const item in a query with DISTINCT +# Bug #24856: ROLLUP by const item in a query with DISTINCT # CREATE TABLE t1 (a int, b int); -- cgit v1.2.1