From 6a72fbe3968befc63baa3cfed6f0deb76c0b8937 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 25 Feb 2012 09:03:06 +0200 Subject: 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. --- mysql-test/r/having.result | 61 ++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 61 insertions(+) (limited to 'mysql-test/r/having.result') 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 ('7',(select 1 from `test`.`t3` where (('7') = `test`.`t3`.`f3`))) having (`field1` < 's') +drop table t1,t2,t3; +End of 5.2 tests -- cgit v1.2.1 From d2e3b33b8c9c18304e652656df22af1401481376 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sun, 26 Feb 2012 00:19:07 -0800 Subject: Rolled back the patch for bug 791761. A better fix for this bug will be pulled from mariadb-5.2. --- mysql-test/r/having.result | 24 ------------------------ 1 file changed, 24 deletions(-) (limited to 'mysql-test/r/having.result') diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index e631c7dbe5a..fc2df2b7fc2 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -570,27 +570,3 @@ ORDER BY t1.f1; f1 DROP TABLE t1,t2; End of 5.1 tests -# -# 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; -- cgit v1.2.1