summaryrefslogtreecommitdiff
path: root/mysql-test/t/having.test
diff options
context:
space:
mode:
authorunknown <igor@olga.mysql.com>2007-07-20 22:56:19 -0700
committerunknown <igor@olga.mysql.com>2007-07-20 22:56:19 -0700
commit72c6c789cf206977894fc531ada2c253bc0b1806 (patch)
tree8efd94c8aa5aa66dec81f0d3185617236b009ba4 /mysql-test/t/having.test
parentc20595d8a0f2a18c7b6d7d85c6c2b42b5025df8d (diff)
downloadmariadb-git-72c6c789cf206977894fc531ada2c253bc0b1806.tar.gz
Fixed bug #29911.
This bug manifested itself for join queries with GROUP BY and HAVING clauses whose SELECT lists contained DISTINCT. It occurred when the optimizer could deduce that the result set would have not more than one row. The bug could lead to wrong result sets for queries of this type because HAVING conditions were erroneously ignored in some cases in the function remove_duplicates. mysql-test/r/having.result: Added a test case for bug #29911. mysql-test/t/having.test: Added a test case for bug #29911.
Diffstat (limited to 'mysql-test/t/having.test')
-rw-r--r--mysql-test/t/having.test26
1 files changed, 26 insertions, 0 deletions
diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test
index 8b39e3bd454..c0ce3cbace7 100644
--- a/mysql-test/t/having.test
+++ b/mysql-test/t/having.test
@@ -151,4 +151,30 @@ EXPLAIN SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1;
DROP table t1;
+#
+# Bug #29911: HAVING clause depending on constant table and evaluated to false
+#
+
+CREATE TABLE t1 (a int PRIMARY KEY);
+CREATE TABLE t2 (b int PRIMARY KEY, a int);
+CREATE TABLE t3 (b int, flag int);
+
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1), (3,1);
+INSERT INTO t3(b,flag) VALUES (2, 1);
+
+SELECT t1.a
+ FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
+ GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
+
+SELECT DISTINCT t1.a, MAX(t3.flag)
+ FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
+ GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
+
+SELECT DISTINCT t1.a
+ FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
+ GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
+
+DROP TABLE t1,t2,t3;
+
# End of 4.1 tests