From 19b4760f19ee2551e38ed086c20e15d29dee644a Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 13 Aug 2005 07:45:14 +0300 Subject: fixed convertion and handling IN subqueries with rows (BUG#11867) mysql-test/r/subselect.result: testst of IN subqueries with row mysql-test/t/subselect.test: tests of ion subqueries with row sql/item.h: add method to prevent of removing Item_ref_null_helper from HAVING sql/item_cmpfunc.h: Prevented removing of Item_test_isnotnull from HAVING sql/item_subselect.cc: fixed converting row IN subqueries sql/sql_select.cc: fixed debug print --- mysql-test/r/subselect.result | 60 ++++++++++++++++++++++++++++++++++++++++--- 1 file changed, 57 insertions(+), 3 deletions(-) (limited to 'mysql-test/r/subselect.result') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index f0b4a8cc06b..8a2ae8e171a 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -2742,13 +2742,26 @@ one two flag 5 6 N 7 8 N insert into t2 values (null,null,'N'); +insert into t2 values (null,3,'0'); +insert into t2 values (null,5,'0'); +insert into t2 values (10,null,'0'); +insert into t1 values (10,3,'0'); +insert into t1 values (10,5,'0'); +insert into t1 values (10,10,'0'); SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1; one two test -1 2 0 -2 3 0 -3 4 0 +1 2 NULL +2 3 NULL +3 4 NULL 5 6 1 7 8 1 +10 3 NULL +10 5 NULL +10 10 NULL +SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); +one two +5 6 +7 8 SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1; one two test 1 2 NULL @@ -2756,6 +2769,47 @@ one two test 3 4 NULL 5 6 1 7 8 1 +10 3 NULL +10 5 NULL +10 10 NULL +SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1; +one two test +1 2 0 +2 3 NULL +3 4 0 +5 6 0 +7 8 0 +10 3 NULL +10 5 NULL +10 10 NULL +SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; +one two test +1 2 0 +2 3 NULL +3 4 0 +5 6 0 +7 8 0 +10 3 NULL +10 5 NULL +10 10 NULL +explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where +Warnings: +Note 1003 select test.t1.one AS `one`,test.t1.two AS `two`,((test.t1.one,test.t1.two),(select test.t2.one AS `one`,test.t2.two AS `two` from test.t2 where ((test.t2.flag = _latin1'0') and (((test.t1.one) = test.t2.one) or isnull(test.t2.one)) and (((test.t1.two) = test.t2.two) or isnull(test.t2.two))) having ((test.t2.one) and (test.t2.two)))) AS `test` from test.t1 +explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where +Warnings: +Note 1003 select test.t1.one AS `one`,test.t1.two AS `two` from test.t1 where ((test.t1.one,test.t1.two),(select test.t2.one AS `one`,test.t2.two AS `two` from test.t2 where ((test.t2.flag = _latin1'N') and ((test.t1.one) = test.t2.one) and ((test.t1.two) = test.t2.two)))) +explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select test.t1.one AS `one`,test.t1.two AS `two`,((test.t1.one,test.t1.two),(select test.t2.one AS `one`,test.t2.two AS `two` from test.t2 where (test.t2.flag = _latin1'0') group by test.t2.one,test.t2.two having ((((test.t1.one) = test.t2.one) or isnull(test.t2.one)) and (((test.t1.two) = test.t2.two) or isnull(test.t2.two)) and (test.t2.one) and (test.t2.two)))) AS `test` from test.t1 DROP TABLE t1,t2; CREATE TABLE t1 (a char(5), b char(5)); INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa'); -- cgit v1.2.1