summaryrefslogtreecommitdiff
path: root/mysql-test/r/having.result
diff options
context:
space:
mode:
authorunknown <sanja@askmonty.org>2012-02-25 09:03:06 +0200
committerunknown <sanja@askmonty.org>2012-02-25 09:03:06 +0200
commit6a72fbe3968befc63baa3cfed6f0deb76c0b8937 (patch)
tree5ecafd276ae926f826f92e79e743d2c698ae047b /mysql-test/r/having.result
parent0919edf32d29b9371288ce1a5f4e03f051bb8093 (diff)
downloadmariadb-git-6a72fbe3968befc63baa3cfed6f0deb76c0b8937.tar.gz
Fix of LP bug:938518 (also LP bug:791761 and LP bug:806955)
Cause of the bug is uninitialized items before evaluation HAVING clasue in case of empty result.
Diffstat (limited to 'mysql-test/r/having.result')
-rw-r--r--mysql-test/r/having.result61
1 files changed, 61 insertions, 0 deletions
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result
index fb229cf9e3b..b5d3d6e30c2 100644
--- a/mysql-test/r/having.result
+++ b/mysql-test/r/having.result
@@ -570,3 +570,64 @@ ORDER BY t1.f1;
f1
DROP TABLE t1,t2;
End of 5.1 tests
+#
+# LP bug:938518 HAVING does not reject the result of aggregation
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY, a INT);
+INSERT INTO t1 VALUES (2,7), (4,7), (6,2), (17,0);
+SELECT MIN(t.pk) FROM t1, t1 as t WHERE t1.pk = 1;
+MIN(t.pk)
+NULL
+SELECT MIN(t.pk) FROM t1, t1 as t WHERE t1.pk = 1 HAVING MIN(t.pk) < 10;
+MIN(t.pk)
+drop table t1;
+#
+# LP bug #791761: MAX over an empty join + HAVING
+#
+CREATE TABLE t1 (a int, b int , KEY (b)) ;
+INSERT INTO t1 VALUES (3,1);
+CREATE TABLE t2 (a int NOT NULL ) ;
+INSERT INTO t2 VALUES (29);
+SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a > 0 HAVING MAX(t1.b) <> 6;
+MAX(t1.b)
+1
+SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a > 0 HAVING MAX(t1.b) IS NULL;
+MAX(t1.b)
+EXPLAIN
+SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a < 0 HAVING MAX(t1.b) <> 6;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a < 0 HAVING MAX(t1.b) <> 6;
+MAX(t1.b)
+CREATE TABLE t3 ( f3 int) ;
+INSERT INTO t3 VALUES (NULL);
+SELECT MAX(t1.b) AS f FROM t1 JOIN t2 ON t2.a != 0
+WHERE (SELECT f3 FROM t3) <> 0 HAVING f <> 6 ;
+f
+DROP TABLE t1,t2,t3;
+#
+# LP bug:806955 HAVING not observed with aggregate +subquery
+#
+CREATE TABLE t1 (f3 int, f10 varchar(1), f11 int, KEY (f10) );
+INSERT INTO t1 VALUES (NULL,'a',0),(8,'b',0);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (7);
+CREATE TABLE t3 (f3 int);
+INSERT INTO t3 VALUES (0),(8);
+SELECT MIN( t1.f10 ) AS field1
+FROM t1 , t2
+WHERE t2.f2 IN ( SELECT f3 FROM t3 )
+HAVING field1 < 's';
+field1
+explain extended
+SELECT MIN( t1.f10 ) AS field1
+FROM t1 , t2
+WHERE t2.f2 IN ( SELECT f3 FROM t3 )
+HAVING field1 < 's';
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select min(`test`.`t1`.`f10`) AS `field1` from `test`.`t1` join `test`.`t2` where <in_optimizer>('7',<exists>(select 1 from `test`.`t3` where (<cache>('7') = `test`.`t3`.`f3`))) having (`field1` < 's')
+drop table t1,t2,t3;
+End of 5.2 tests