| List: | Internals | « Previous MessageNext Message » | |
| From: | sanja | Date: | March 30 2005 9:07am |
| Subject: | bk commit into 4.1 tree (bell:1.2130) BUG#9350 | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local 4.1 repository of bell. When bell does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet 1.2130 05/03/30 10:07:08 bell@stripped +3 -0 postreview changes fixed bug in IN/ALL/ANY subqeries with HAVING clause (BUG#9350) sql/item_subselect.cc 1.131 05/03/30 10:07:04 bell@stripped +20 -15 fixed typo in comment removed unused code fixed bug with HAVING clause detectiuon (BUG#9350) postreview changes mysql-test/t/subselect.test 1.143 05/03/30 10:07:04 bell@stripped +176 -0 added new subquery tests to make code covarage better mysql-test/r/subselect.result 1.164 05/03/30 10:07:04 bell@stripped +421 -0 added new subquery tests to make code covarage better # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: bell # Host: sanja.is.com.ua # Root: /home/bell/mysql/bk/work-union_merge-4.1 --- 1.163/mysql-test/r/subselect.result Thu Mar 10 14:00:30 2005 +++ 1.164/mysql-test/r/subselect.result Wed Mar 30 10:07:04 2005 @@ -1873,6 +1873,380 @@ a 1 3 +SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2); +a +3 +SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2); +a +1 +SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2); +a +2 +SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2); +a +2 +3 +SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2); +a +1 +2 +SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2); +a +1 +3 +SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2); +a +3 +SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2); +a +1 +SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2); +a +2 +SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2); +a +2 +3 +SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2); +a +1 +2 +SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2); +a +1 +3 +SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +a +3 +SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +a +1 +SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +a +2 +SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +a +2 +3 +SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +a +1 +2 +SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +a +1 +3 +SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +a +3 +SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +a +1 +SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +a +2 +SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +a +2 +3 +SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +a +1 +2 +SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +a +1 +3 +SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +a +3 +SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +a +1 +SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +a +2 +SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +a +2 +3 +SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +a +1 +2 +SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +a +1 +3 +SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +a +3 +SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +a +1 +SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +a +2 +SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +a +2 +3 +SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +a +1 +2 +SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +a +1 +3 +SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2); +ERROR 21000: Operand should contain 1 column(s) +SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2); +ERROR 21000: Operand should contain 1 column(s) +SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2); +ERROR 21000: Operand should contain 1 column(s) +SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a FROM t1 WHERE b = 2); +ERROR 21000: Operand should contain 1 column(s) +SELECT a FROM t1 WHERE a > ALL (SELECT a,2 FROM t1 WHERE b = 2); +ERROR 21000: Operand should contain 1 column(s) +SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a,2 FROM t1 WHERE b = 2); +ERROR 21000: Operand should contain 1 column(s) +SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2); +ERROR 21000: Operand should contain 1 column(s) +SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2); +ERROR 21000: Operand should contain 1 column(s) +SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2); +ERROR 21000: Operand should contain 2 column(s) +SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2); +ERROR 21000: Operand should contain 1 column(s) +SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2); +a +SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2); +ERROR 21000: Operand should contain 2 column(s) +SELECT a FROM t1 WHERE a <> ALL (SELECT a,2 FROM t1 WHERE b = 2); +ERROR 21000: Operand should contain 1 column(s) +SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a,2 FROM t1 WHERE b = 2); +a +1 +2 +3 +SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2); +a +2 +SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2); +a +1 +3 +SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2); +a +2 +SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2); +a +1 +3 +SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2); +a +2 +SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2); +a +1 +3 +SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2); +a +2 +SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2); +a +1 +3 +SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a); +a +3 +SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a); +a +1 +SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a); +a +2 +SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a); +a +2 +3 +SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a); +a +1 +2 +SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a); +a +1 +3 +SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 group by a); +a +3 +SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 group by a); +a +1 +SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 group by a); +a +2 +SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 group by a); +a +2 +3 +SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 group by a); +a +1 +2 +SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 group by a); +a +1 +3 +SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2); +a +3 +SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2); +a +1 +SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2); +a +2 +SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2); +a +2 +3 +SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2); +a +1 +2 +SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2); +a +1 +3 +SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 group by a HAVING a = 2); +a +3 +SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 group by a HAVING a = 2); +a +1 +SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 group by a HAVING a = 2); +a +2 +SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 group by a HAVING a = 2); +a +2 +3 +SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 group by a HAVING a = 2); +a +1 +2 +SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2); +a +1 +3 +SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a; +concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') +0- +0- +1- +SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a; +concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') +1- +0- +0- +SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a; +concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') +0- +1- +0- +DROP TABLE t1; +CREATE TABLE t1 ( a double, b double ); +INSERT INTO t1 VALUES (1,1),(2,2),(3,3); +SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0); +a +3 +SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0); +a +1 +SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0); +a +2 +SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0); +a +2 +3 +SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0); +a +1 +2 +SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0); +a +1 +3 +SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2e0); +a +3 +SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2e0); +a +1 +SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2e0); +a +2 +SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2e0); +a +2 +3 +SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2e0); +a +1 +2 +SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2e0); +a +1 +3 +DROP TABLE t1; +CREATE TABLE t1 ( a char(1), b char(1)); +INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3'); +SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2'); +a +3 +SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2'); +a +1 +SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2'); +a +2 +SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2'); +a +2 +3 +SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2'); +a +1 +2 +SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2'); +a +1 +3 +SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = '2'); +a +3 +SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = '2'); +a +1 +SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = '2'); +a +2 +SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = '2'); +a +2 +3 +SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = '2'); +a +1 +2 +SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = '2'); +a +1 +3 DROP TABLE t1; create table t1 (a int, b int); insert into t1 values (1,2),(3,4); @@ -2276,3 +2650,50 @@ 1 1 drop table t1; +create table t1 (a int); +create table t2 (a int); +insert into t1 values (1),(2); +insert into t2 values (0),(1),(2),(3); +select a from t2 where a in (select a from t1); +a +1 +2 +select a from t2 having a in (select a from t1); +a +1 +2 +prepare stmt1 from "select a from t2 where a in (select a from t1)"; +execute stmt1; +a +1 +2 +execute stmt1; +a +1 +2 +deallocate prepare stmt1; +prepare stmt1 from "select a from t2 having a in (select a from t1)"; +execute stmt1; +a +1 +2 +execute stmt1; +a +1 +2 +deallocate prepare stmt1; +drop table t1, t2; +create table t1 (a int, b int); +insert into t1 values (1,2); +select 1 = (select * from t1); +ERROR 21000: Operand should contain 1 column(s) +select (select * from t1) = 1; +ERROR 21000: Operand should contain 2 column(s) +select (1,2) = (select a from t1); +ERROR 21000: Operand should contain 2 column(s) +select (select a from t1) = (1,2); +ERROR 21000: Operand should contain 1 column(s) +select (1,2,3) = (select * from t1); +ERROR 21000: Operand should contain 3 column(s) +select (select * from t1) = (1,2,3); +ERROR 21000: Operand should contain 2 column(s) --- 1.142/mysql-test/t/subselect.test Thu Mar 10 14:00:30 2005 +++ 1.143/mysql-test/t/subselect.test Wed Mar 30 10:07:04 2005 @@ -1148,6 +1148,7 @@ SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 ); SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 ); SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 ); +# with index ALTER TABLE t1 ADD INDEX (a); SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 ); SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 ); @@ -1161,8 +1162,145 @@ SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 ); SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 ); SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 ); +# having clause test +SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2); +# union test +SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2); +# union + having test +SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2); +# row tests +# < > >= <= and = ALL/ <> ANY do not support row operation +-- error 1241 +SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2); +-- error 1241 +SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2); +-- error 1241 +SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2); +-- error 1241 +SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a FROM t1 WHERE b = 2); +-- error 1241 +SELECT a FROM t1 WHERE a > ALL (SELECT a,2 FROM t1 WHERE b = 2); +-- error 1241 +SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a,2 FROM t1 WHERE b = 2); +-- error 1241 +SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2); +-- error 1241 +SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2); +# following should be converted to IN +-- error 1241 +SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2); +-- error 1241 +SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2); +-- error 1241 +SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2); +-- error 1241 +SELECT a FROM t1 WHERE a <> ALL (SELECT a,2 FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a,2 FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2); +SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2); +SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2); +# without optimisation +SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a); +SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a); +SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a); +SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a); +SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a); +SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a); +SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 group by a); +SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 group by a); +SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 group by a); +SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 group by a); +SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 group by a); +SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 group by a); +# without optimisation + having +SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2); +SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2); +SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2); +SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2); +SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2); +SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2); +SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 group by a HAVING a = 2); +SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 group by a HAVING a = 2); +SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 group by a HAVING a = 2); +SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 group by a HAVING a = 2); +SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 group by a HAVING a = 2); +SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2); +# EXISTS in string contence +SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a; +SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a; +SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a; +DROP TABLE t1; +CREATE TABLE t1 ( a double, b double ); +INSERT INTO t1 VALUES (1,1),(2,2),(3,3); +SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0); +SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0); +SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0); +SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0); +SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0); +SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0); +SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2e0); +SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2e0); +SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2e0); +SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2e0); +SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2e0); +SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2e0); +DROP TABLE t1; +CREATE TABLE t1 ( a char(1), b char(1)); +INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3'); +SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2'); +SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2'); +SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2'); +SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2'); +SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2'); +SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2'); +SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = '2'); +SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = '2'); +SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = '2'); +SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = '2'); +SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = '2'); +SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = '2'); DROP TABLE t1; + # # SELECT(EXISTS * ...)optimisation # @@ -1546,3 +1684,41 @@ insert into t1 values (1), (2), (3); SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1); drop table t1; + +# +# subselect into HAVING clause (code covarage improvement) +# +create table t1 (a int); +create table t2 (a int); +insert into t1 values (1),(2); +insert into t2 values (0),(1),(2),(3); +select a from t2 where a in (select a from t1); +select a from t2 having a in (select a from t1); +prepare stmt1 from "select a from t2 where a in (select a from t1)"; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; +prepare stmt1 from "select a from t2 having a in (select a from t1)"; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; +drop table t1, t2; + +# +# single row subqueries and row operations (code covarage improvement) +# +create table t1 (a int, b int); +insert into t1 values (1,2); +-- error 1241 +select 1 = (select * from t1); +-- error 1241 +select (select * from t1) = 1; +-- error 1241 +select (1,2) = (select a from t1); +-- error 1241 +select (select a from t1) = (1,2); +-- error 1241 +select (1,2,3) = (select * from t1); +-- error 1241 +select (select * from t1) = (1,2,3); +drop table t1 --- 1.130/sql/item_subselect.cc Wed Mar 23 08:36:42 2005 +++ 1.131/sql/item_subselect.cc Wed Mar 30 10:07:04 2005 @@ -369,25 +369,13 @@ } substitution= select_lex->item_list.head(); /* - as far as we moved content to upper leven, field which depend of + as far as we moved content to upper level, field which depend of 'upper' select is not really dependent => we remove this dependence */ substitution->walk(&Item::remove_dependence_processor, (byte *) select_lex->outer_select()); - if (join->conds || join->having) - { - Item *cond; - if (!join->having) - cond= join->conds; - else if (!join->conds) - cond= join->having; - else - if (!(cond= new Item_cond_and(join->conds, join->having))) - goto err; - if (!(substitution= new Item_func_if(cond, substitution, - new Item_null()))) - goto err; - } + /* SELECT without FROM clause can't have WHERE or HAVING clause */ + DBUG_ASSERT(join->conds == 0 && join->having == 0); return RES_REDUCE; } return RES_OK; @@ -616,8 +604,14 @@ return str; } + double Item_in_subselect::val() { + /* + As far as Item_in_subselect called only from Item_in_optimizer this + method should not be used + */ + DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); if (exec()) { @@ -630,6 +624,7 @@ return (double) value; } + longlong Item_in_subselect::val_int() { DBUG_ASSERT(fixed == 1); @@ -644,8 +639,14 @@ return value; } + String *Item_in_subselect::val_str(String *str) { + /* + As far as Item_in_subselect called only from Item_in_optimizer this + method should not be used + */ + DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); if (exec()) { @@ -705,6 +706,7 @@ Item *subs; if (!select_lex->group_list.elements && + !select_lex->having && !select_lex->with_sum_func && !(select_lex->next_select())) { @@ -1040,6 +1042,7 @@ bool result; DBUG_ENTER("Item_in_subselect::select_in_like_transformer"); + if (changed) { DBUG_RETURN(RES_OK); @@ -1089,6 +1092,8 @@ /* we do not support row operation for ALL/ANY/SOME */ if (func != &eq_creator) { + if (arena) + thd->restore_backup_item_arena(arena, &backup); my_error(ER_OPERAND_COLUMNS, MYF(0), 1); DBUG_RETURN(RES_ERROR); }
| Thread | ||
|---|---|---|
| • bk commit into 4.1 tree (bell:1.2130) BUG#9350 | sanja | 30 Mar |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.688 sec. using MySQL 5.1.14-beta-log