From 8b062c1fefd14cfa22e625d098b92c8ac3c1b28f Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 5 Sep 2011 09:29:49 +0300 Subject: Fix of LP BUG#780386. ALL subquery should return TRUE if subquery rowa set is empty independently of left part. The problem was that Item_func_(eq,ne,gt,ge,lt,le) do not call execution of second argument if first is NULL no in this case subquery will not be executed and when Item_func_not_all calls any_value() of the subquery or aggregation function which report that there was rows. So for NULL < ALL (SELECT...) result was FALSE instead of TRUE. Fix is just swapping of arguments of Item_func_(eq,ne,gt,ge,lt,le) (with changing the operation if it is needed) so that result will be the same (for examole a < b is equal to b > a). This fix exploit the fact that first argument will be executed in any case. --- mysql-test/r/subselect.result | 41 +++++++++++++++++++++++--------- mysql-test/suite/pbxt/r/subselect.result | 14 +++++------ mysql-test/t/subselect.test | 16 +++++++++++++ 3 files changed, 53 insertions(+), 18 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 593c606a3e9..85f5bd55371 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -270,7 +270,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where (((select min(`test`.`t2`.`b`) from `test`.`t2`) <= `test`.`t3`.`a`)) select * from t3 where a >= all (select b from t2); a 7 @@ -1532,7 +1532,7 @@ INSERT INTO t1 VALUES ('z','?'); select * from t1 where s1 > (select max(s2) from t1); ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>' select * from t1 where s1 > any (select max(s2) from t1); -ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>' +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_german1_ci,IMPLICIT) for operation '<' drop table t1; create table t1(toid int,rd int); create table t2(userid int,pmnew int,pmtotal int); @@ -1621,7 +1621,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` < (select max(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where (((select max(NULL) from `test`.`t2`) > `test`.`t3`.`a`)) select * from t3 where a >= some (select b from t2); a explain extended select * from t3 where a >= some (select b from t2); @@ -1629,7 +1629,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` >= (select min(NULL) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where (((select min(NULL) from `test`.`t2`) <= `test`.`t3`.`a`)) select * from t3 where a >= all (select b from t2 group by 1); a 6 @@ -1640,7 +1640,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` < (select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where (((select NULL from `test`.`t2` group by 1) > `test`.`t3`.`a`)) select * from t3 where a >= some (select b from t2 group by 1); a explain extended select * from t3 where a >= some (select b from t2 group by 1); @@ -1648,13 +1648,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` >= (select NULL from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where (((select NULL from `test`.`t2` group by 1) <= `test`.`t3`.`a`)) select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); 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 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= any (select b from t2 group by 1); @@ -1662,7 +1662,7 @@ a explain extended select * from t3 where NULL >= any (select b from t2 group by 1); 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 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2); @@ -1670,7 +1670,7 @@ a explain extended select * from t3 where NULL >= some (select b from t2); 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 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2 group by 1); @@ -1678,7 +1678,7 @@ a explain extended select * from t3 where NULL >= some (select b from t2 group by 1); 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 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 insert into t2 values (2,2), (2,1), (3,3), (3,1); @@ -1691,7 +1691,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` <= (select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where (((select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= `test`.`t3`.`a`)) drop table t2, t3; CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now()); @@ -5045,4 +5045,23 @@ a SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); a drop table t1; +# +# Fix of LP BUG#780386 (NULL left part with empty ALL subquery). +# +CREATE TABLE t1 ( f11 int) ; +INSERT IGNORE INTO t1 VALUES (0),(0); +CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ; +INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0); +DROP TABLE IF EXISTS t3; +Warnings: +Note 1051 Unknown table 't3' +CREATE TABLE t3 ( f3 int) ; +INSERT INTO t3 VALUES (0),(0); +SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ; +r +NULL +5 +NULL +5 +DROP TABLE t1, t2, t3; End of 5.2 tests diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result index 8c3d1223b0a..0cdec48e192 100644 --- a/mysql-test/suite/pbxt/r/subselect.result +++ b/mysql-test/suite/pbxt/r/subselect.result @@ -270,7 +270,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where (((select min(`test`.`t2`.`b`) from `test`.`t2`) <= `test`.`t3`.`a`)) select * from t3 where a >= all (select b from t2); a 7 @@ -1407,7 +1407,7 @@ INSERT INTO t1 VALUES ('z','?'); select * from t1 where s1 > (select max(s2) from t1); ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>' select * from t1 where s1 > any (select max(s2) from t1); -ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>' +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_german1_ci,IMPLICIT) for operation '<' drop table t1; create table t1(toid int,rd int); create table t2(userid int,pmnew int,pmtotal int); @@ -1496,7 +1496,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` < (select max(`test`.`t2`.`b`) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where (((select max(`test`.`t2`.`b`) from `test`.`t2`) > `test`.`t3`.`a`)) select * from t3 where a >= some (select b from t2); a explain extended select * from t3 where a >= some (select b from t2); @@ -1504,7 +1504,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where (((select min(`test`.`t2`.`b`) from `test`.`t2`) <= `test`.`t3`.`a`)) select * from t3 where a >= all (select b from t2 group by 1); a 6 @@ -1515,7 +1515,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` < (select `test`.`t2`.`b` from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where (((select `test`.`t2`.`b` from `test`.`t2` group by 1) > `test`.`t3`.`a`)) select * from t3 where a >= some (select b from t2 group by 1); a explain extended select * from t3 where a >= some (select b from t2 group by 1); @@ -1523,7 +1523,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` >= (select `test`.`t2`.`b` from `test`.`t2` group by 1))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where (((select `test`.`t2`.`b` from `test`.`t2` group by 1) <= `test`.`t3`.`a`)) select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); @@ -1566,7 +1566,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where ((`test`.`t3`.`a` <= (select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where (((select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= `test`.`t3`.`a`)) drop table t2, t3; CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now()); diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 08790104082..03504050f65 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3909,4 +3909,20 @@ SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); drop table t1; +--echo # +--echo # Fix of LP BUG#780386 (NULL left part with empty ALL subquery). +--echo # +CREATE TABLE t1 ( f11 int) ; +INSERT IGNORE INTO t1 VALUES (0),(0); + +CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ; +INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0); + +DROP TABLE IF EXISTS t3; +CREATE TABLE t3 ( f3 int) ; +INSERT INTO t3 VALUES (0),(0); + +SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ; +DROP TABLE t1, t2, t3; + --echo End of 5.2 tests -- cgit v1.2.1