From 39bf2b966214eebc79ca13a255c63e9685eab164 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 20 Jun 2007 12:43:14 -0700 Subject: Fixed bug #29104: assertion abort for grouping queries using views. The abort happened when a query contained a conjunctive predicate of the form 'view column = constant' in the WHERE condition and the grouping list also contained a reference to a view column yet a different one. Removed the failing assertion as invalid in a general case. Also fixed a bug that prevented applying some optimization for grouping queries using views. If the WHERE condition of such a query contains a conjunctive condition of the form 'view column = constant' and this view column is used in the grouping list then grouping by this column can be eliminated. The bug blocked performing this elimination. mysql-test/r/view.result: Added a test case for bug #29104. mysql-test/t/view.test: Added a test case for bug #29104. sql/item.cc: Fixed bug #29104: assertion abort for grouping queries using views. The abort happened when a query contained a conjunctive predicate of the form 'view column = constant' in the WHERE condition and the grouping list also contained a reference to a view column yet a different one. Removed the failing assertion as invalid in a general case. Also fixed a bug that prevented applying some optimization for grouping queries using views. If the WHERE condition of such a query contains a conjunctive condition of the form 'view column = constant' and this view column is used in the grouping list then grouping by this column can be eliminated. The bug blocked performing this elimination. This bug was in the function Item_field::eq while the failing assertion was in the function Item_direct_view_ref::eq. --- mysql-test/r/view.result | 24 ++++++++++++++++++++++++ 1 file changed, 24 insertions(+) (limited to 'mysql-test/r/view.result') diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 043057f64f7..9adb3f96142 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -3476,4 +3476,28 @@ a1 c 2 0 DROP VIEW v1,v2; DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (1,2), (2,2), (1,3), (1,2); +CREATE VIEW v1 AS SELECT a, b+1 as b FROM t1; +SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b; +b SUM(a) +3 4 +EXPLAIN SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a; +a SUM(b) +1 6 +2 3 +EXPLAIN SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort +SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a; +a SUM(b) +1 10 +EXPLAIN SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +DROP VIEW v1; +DROP TABLE t1; End of 5.0 tests. -- cgit v1.2.1