diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/include/wait_condition.inc | 59 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 229 |
2 files changed, 180 insertions, 108 deletions
diff --git a/mysql-test/include/wait_condition.inc b/mysql-test/include/wait_condition.inc new file mode 100644 index 00000000000..a41d8080c9b --- /dev/null +++ b/mysql-test/include/wait_condition.inc @@ -0,0 +1,59 @@ +# include/wait_condition.inc +# +# SUMMARY +# +# Waits until the passed statement returns true, or the operation +# times out. +# +# USAGE +# +# let $wait_condition= +# SELECT c = 3 FROM t; +# --source include/wait_condition.inc +# +# OR +# +# let $wait_timeout= 60; # Override default 30 seconds with 60. +# let $wait_condition= +# SELECT c = 3 FROM t; +# --source include/wait_condition.inc +# --echo Executed the test condition $wait_condition_reps times +# +# EXAMPLE +# events_bugs.test, events_time_zone.test +# + +--disable_query_log + +let $wait_counter= 300; +if ($wait_timeout) +{ + let $wait_counter= `SELECT $wait_timeout * 10`; +} +# Reset $wait_timeout so that its value won't be used on subsequent +# calls, and default will be used instead. +let $wait_timeout= 0; + +# Keep track of how many times the wait condition is tested +# This is used by some tests (e.g., main.status) +let $wait_condition_reps= 0; +while ($wait_counter) +{ + let $success= `$wait_condition`; + inc $wait_condition_reps; + if ($success) + { + let $wait_counter= 0; + } + if (!$success) + { + real_sleep 0.1; + dec $wait_counter; + } +} +if (!$success) +{ + echo Timeout in wait_condition.inc for $wait_condition; +} + +--enable_query_log diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 78b4de6e816..4d9507f1231 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -9,28 +9,28 @@ SELECT (SELECT 1) UNION SELECT (SELECT 2); explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2); SELECT (SELECT (SELECT 0 UNION SELECT 0)); explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0)); --- error 1247 +-- error ER_ILLEGAL_REFERENCE SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a; --- error 1247 +-- error ER_ILLEGAL_REFERENCE SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) as c HAVING a=1) as b; SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a; --- error 1247 +-- error ER_ILLEGAL_REFERENCE SELECT (SELECT a) as a; EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; --- error 1054 +-- error ER_BAD_FIELD_ERROR SELECT (SELECT 1), a; SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1; --- error 1054 +-- error ER_BAD_FIELD_ERROR SELECT 1 FROM (SELECT (SELECT a) b) c; SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id); --- error 1241 +-- error ER_OPERAND_COLUMNS SELECT * FROM (SELECT 1) a WHERE 1 IN (SELECT 1,1); SELECT 1 IN (SELECT 1); SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); --- error 1221 +-- error ER_WRONG_USAGE select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); --- error 1108 +-- error ER_WRONG_PARAMETERS_TO_PROCEDURE SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); -- error ER_BAD_FIELD_ERROR SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; @@ -53,7 +53,7 @@ SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a'); SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a'); SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a'); --- error 1241 +-- error ER_OPERAND_COLUMNS SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a); SELECT 1 as a,(SELECT a+a) b,(SELECT b); @@ -65,7 +65,7 @@ create table t4 (a int not null, b int not null); insert into t1 values (2); insert into t2 values (1,7),(2,7); insert into t4 values (4,8),(3,8),(5,9); --- error 1247 +-- error ER_ILLEGAL_REFERENCE select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1; select (select a from t1 where t1.a=t2.a), a from t2; select (select a from t1 where t1.a=t2.b), a from t2; @@ -105,9 +105,9 @@ select * from t3 where a >= any (select b from t2); explain extended select * from t3 where a >= any (select b from t2); select * from t3 where a >= all (select b from t2); delete from t2 where a=100; --- error 1241 +-- error ER_OPERAND_COLUMNS select * from t3 where a in (select a,b from t2); --- error 1241 +-- error ER_OPERAND_COLUMNS select * from t3 where a in (select * from t2); insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10); # empty set @@ -123,7 +123,7 @@ select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) insert into t5 values (2); select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; explain extended select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; --- error 1242 +-- error ER_SUBQUERY_NO_1_ROW select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)); create table t7( uq int primary key, name char(25)); @@ -133,7 +133,7 @@ select * from t6 where exists (select * from t7 where uq = clinic_uq); explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq); # not unique fields --- error 1052 +-- error ER_NON_UNIQ_ERROR select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); # different tipes & group functions @@ -160,14 +160,14 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce','test'); INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); --- error 1241 +-- error ER_OPERAND_COLUMNS SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); --- error 1241 +-- error ER_OPERAND_COLUMNS SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE pseudo='joce'); SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); --- error 1242 +-- error ER_SUBQUERY_NO_1_ROW SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%'); drop table if exists t1,t2,t3,t4,t5,t6,t7,t8; @@ -187,7 +187,7 @@ EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1; --- error 1242 +-- error ER_SUBQUERY_NO_1_ROW SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1; EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1); drop table t1; @@ -213,9 +213,9 @@ CREATE TABLE `t2` ( INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce'); select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1); SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND date >= '2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20; --- error 1054 +-- error ER_BAD_FIELD_ERROR SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b; --- error 1054 +-- error ER_BAD_FIELD_ERROR SELECT 1 IN (SELECT 1 FROM t2 HAVING a); SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic); @@ -244,9 +244,9 @@ CREATE TABLE `t1` ( ) ENGINE=MyISAM ROW_FORMAT=FIXED; INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1); --- error 1242 +-- error ER_SUBQUERY_NO_1_ROW select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1); --- error 1242 +-- error ER_SUBQUERY_NO_1_ROW select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1); drop table t1; @@ -258,7 +258,7 @@ drop table t1; #iftest CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b'); INSERT INTO t1 VALUES (); --- error 1242 +-- error ER_SUBQUERY_NO_1_ROW SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b'); drop table t1; @@ -271,13 +271,13 @@ CREATE TABLE `t1` ( UNIQUE KEY `numreponse` (`numreponse`), KEY `pseudo` (`pseudo`,`numeropost`) ) ENGINE=MyISAM; --- error 1247 +-- error ER_ILLEGAL_REFERENCE SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a; --- error 1054 +-- error ER_BAD_FIELD_ERROR SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a; SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a; INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); --- error 1242 +-- error ER_SUBQUERY_NO_1_ROW EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); @@ -294,9 +294,9 @@ create table t2 (a int NOT NULL, b int, primary key (a)); insert into t1 values (0, 10),(1, 11),(2, 12); insert into t2 values (1, 21),(2, 22),(3, 23); select * from t1; --- error 1093 +-- error ER_UPDATE_TABLE_USED update t1 set b= (select b from t1); --- error 1242 +-- error ER_SUBQUERY_NO_1_ROW update t1 set b= (select b from t2); update t1 set b= (select b from t2 where t1.a = t2.a); select * from t1; @@ -309,9 +309,9 @@ insert into t1 values (0, 10),(1, 11),(2, 12); insert into t2 values (1, 21),(2, 12),(3, 23); select * from t1; select * from t1 where b = (select b from t2 where t1.a = t2.a); --- error 1093 +-- error ER_UPDATE_TABLE_USED delete from t1 where b = (select b from t1); --- error 1242 +-- error ER_SUBQUERY_NO_1_ROW delete from t1 where b = (select b from t2); delete from t1 where b = (select b from t2 where t1.a = t2.a); select * from t1; @@ -327,9 +327,9 @@ insert into t12 values (33, 10),(22, 11),(2, 12); insert into t2 values (1, 21),(2, 12),(3, 23); select * from t11; select * from t12; --- error 1093 +-- error ER_UPDATE_TABLE_USED delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); --- error 1242 +-- error ER_SUBQUERY_NO_1_ROW delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); select * from t11; @@ -342,25 +342,29 @@ create table t2 (a int); create table t3 (b int); insert into t2 values (1); insert into t3 values (1),(2); --- error 1093 +-- error ER_UPDATE_TABLE_USED INSERT INTO t1 (x) VALUES ((SELECT x FROM t1)); --- error 1242 +-- error ER_SUBQUERY_NO_1_ROW INSERT INTO t1 (x) VALUES ((SELECT b FROM t3)); INSERT INTO t1 (x) VALUES ((SELECT a FROM t2)); select * from t1; insert into t2 values (1); +let $row_count_before= `SELECT COUNT(*) FROM t1`; INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2)); --- sleep 1 +let $wait_condition= SELECT COUNT(*) <> $row_count_before FROM t1; +--source include/wait_condition.inc select * from t1; INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2; select * from t1; # After this, only data based on old t1 records should have been added. INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2; select * from t1; --- error 1054 +-- error ER_BAD_FIELD_ERROR INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2)); +let $row_count_before= `SELECT COUNT(*) FROM t1`; INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2)); --- sleep 1 +let $wait_condition= SELECT COUNT(*) <> $row_count_before FROM t1; +--source include/wait_condition.inc select * from t1; # #TODO: should be uncommented after bug 380 fix pushed @@ -375,25 +379,30 @@ create table t3 (a int); insert into t2 values (1); insert into t3 values (1),(2); select * from t1; --- error 1093 +-- error ER_UPDATE_TABLE_USED replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2)); --- error 1242 +-- error ER_SUBQUERY_NO_1_ROW replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2)); replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2)); select * from t1; replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2)); select * from t1; +let $row_count_before= `SELECT COUNT(*) FROM t1`; replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2)); --- sleep 1 +# We get one additional row +let $wait_condition= SELECT COUNT(*) <> $row_count_before FROM t1; +--source include/wait_condition.inc select * from t1; +let $row_count_before= `SELECT COUNT(*) FROM t1 WHERE y = 2`; replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2)); --- sleep 1 +let $wait_condition= SELECT COUNT(*) <> $row_count_before FROM t1 WHERE y = 2; +--source include/wait_condition.inc select * from t1; replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2)); select * from t1; drop table t1, t2, t3; --- error 1096 +-- error ER_NO_TABLES_USED SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *); CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1; @@ -406,14 +415,14 @@ EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3); SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); --- error 1093 +-- error ER_UPDATE_TABLE_USED INSERT INTO t2 VALUES ((SELECT * FROM t2)); --- error 1093 +-- error ER_UPDATE_TABLE_USED INSERT INTO t2 VALUES ((SELECT id FROM t2)); SELECT * FROM t2; CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 values (1),(1); --- error 1242 +-- error ER_SUBQUERY_NO_1_ROW UPDATE t2 SET id=(SELECT * FROM t1); drop table t2, t1; @@ -481,9 +490,9 @@ drop table t1,t2,t3; #LIMIT is not supported now create table t1 (a float); --- error 1235 +-- error ER_NOT_SUPPORTED_YET select 10.5 IN (SELECT * from t1 LIMIT 1); --- error 1235 +-- error ER_NOT_SUPPORTED_YET select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5); drop table t1; @@ -507,7 +516,7 @@ select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null); select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a'); select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a'); select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a'); --- error 1235 +-- error ER_NOT_SUPPORTED_YET select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2); drop table t1; @@ -522,14 +531,14 @@ set @a:=2; set @a:=(SELECT a from t1); select @a; drop table t1; --- error 1146 +-- error ER_NO_SUCH_TABLE do (SELECT a from t1); --- error 1146 +-- error ER_NO_SUCH_TABLE set @a:=(SELECT a from t1); CREATE TABLE t1 (a int, KEY(a)); HANDLER t1 OPEN; --- error 1064 +-- error ER_PARSE_ERROR HANDLER t1 READ a=((SELECT 1)); HANDLER t1 CLOSE; drop table t1; @@ -627,7 +636,7 @@ drop table t1; # # error in IN # --- error 1146 +-- error ER_NO_SUCH_TABLE select t1.Continent, t2.Name, t2.Population from t1 LEFT JOIN t2 ON t1.Code = t2.Country where t2.Population IN (select max(t2.Population) AS Population from t2, t1 where t2.Country = t1.Code group by Continent); # @@ -698,7 +707,7 @@ CREATE TABLE `t1` ( INSERT INTO t1 VALUES (1); UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i)); UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i)); --- error 1054 +-- error ER_BAD_FIELD_ERROR UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t); select * from t1; drop table t1; @@ -855,7 +864,7 @@ create table t1 (s1 int); create table t2 (s1 int); insert into t1 values (1); insert into t2 values (1); --- error 1054 +-- error ER_BAD_FIELD_ERROR update t1 set s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A); DROP TABLE t1, t2; @@ -865,9 +874,9 @@ DROP TABLE t1, t2; CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci, s2 CHAR(5) COLLATE latin1_swedish_ci); INSERT INTO t1 VALUES ('z','?'); --- error 1267 +-- error ER_CANT_AGGREGATE_2COLLATIONS select * from t1 where s1 > (select max(s2) from t1); --- error 1267 +-- error ER_CANT_AGGREGATE_2COLLATIONS select * from t1 where s1 > any (select max(s2) from t1); drop table t1; @@ -885,7 +894,7 @@ drop table t1, t2; # row union # create table t1 (s1 char(5)); --- error 1241 +-- error ER_OPERAND_COLUMNS select (select 'a','b' from t1 union select 'a','b' from t1) from t1; insert into t1 values ('tttt'); select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1); @@ -963,7 +972,7 @@ CREATE TABLE t1 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES (1),(5); CREATE TABLE t2 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t2 VALUES (2),(6); --- error 1241 +-- error ER_OPERAND_COLUMNS select * from t1 where (1,2,6) in (select * from t2); DROP TABLE t1,t2; @@ -973,7 +982,7 @@ DROP TABLE t1,t2; create table t1 (s1 int); insert into t1 values (1); insert into t1 values (2); --- error 1242 +-- error ER_SUBQUERY_NO_1_ROW set sort_buffer_size = (select s1 from t1); do (select * from t1); drop table t1; @@ -1003,11 +1012,11 @@ drop table t1, t2; # create table t1 (s1 int); create table t2 (s1 int); --- error 1054 +-- error ER_BAD_FIELD_ERROR select * from t1 where (select count(*) from t2 where t1.s2) = 1; --- error 1054 +-- error ER_BAD_FIELD_ERROR select * from t1 where (select count(*) from t2 group by t1.s2) = 1; --- error 1054 +-- error ER_BAD_FIELD_ERROR select count(*) from t2 group by t1.s2; drop table t1, t2; @@ -1032,9 +1041,13 @@ DROP TABLE t1; create table t1 (a int, b decimal(13, 3)); insert into t1 values (1, 0.123); +let $outfile = $MYSQLTEST_VARDIR/master-data/test/subselect.out.file.1; +--error 0,1 +--remove_file $outfile select a, (select max(b) from t1) into outfile "subselect.out.file.1" from t1; delete from t1; load data infile "subselect.out.file.1" into table t1; +--remove_file $outfile select * from t1; drop table t1; @@ -1123,7 +1136,7 @@ drop table t1; create table t1(id int); create table t2(id int); create table t3(flag int); --- error 1064 +-- error ER_PARSE_ERROR select (select * from t3 where id not null) from t1, t2; drop table t1,t2,t3; @@ -1212,31 +1225,31 @@ SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FR 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 +-- error ER_OPERAND_COLUMNS SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2); --- error 1241 +-- error ER_OPERAND_COLUMNS SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2); --- error 1241 +-- error ER_OPERAND_COLUMNS SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2); --- error 1241 +-- error ER_OPERAND_COLUMNS SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a FROM t1 WHERE b = 2); --- error 1241 +-- error ER_OPERAND_COLUMNS SELECT a FROM t1 WHERE a > ALL (SELECT a,2 FROM t1 WHERE b = 2); --- error 1241 +-- error ER_OPERAND_COLUMNS SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a,2 FROM t1 WHERE b = 2); --- error 1241 +-- error ER_OPERAND_COLUMNS SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2); --- error 1241 +-- error ER_OPERAND_COLUMNS SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2); # following should be converted to IN --- error 1241 +-- error ER_OPERAND_COLUMNS SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2); --- error 1241 +-- error ER_OPERAND_COLUMNS 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 +-- error ER_OPERAND_COLUMNS SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2); --- error 1241 +-- error ER_OPERAND_COLUMNS 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); @@ -1441,7 +1454,7 @@ CREATE TABLE `t1` ( `master` int(10) unsigned NOT NULL default '0', `map` smalli INSERT INTO `t1` VALUES (1,0,0,700),(1,1,1,400),(1,5,5,400),(1,12,12,400),(1,12,32,400),(4,12,32,400); CREATE TABLE `t2` ( `id` int(10) unsigned NOT NULL default '0', `pid` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `level` tinyint(4) unsigned NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ; INSERT INTO `t2` VALUES (6,5,12,7,'a'),(12,0,0,7,'a'),(12,1,0,7,'a'),(12,5,5,7,'a'),(12,5,12,7,'a'); --- error 1054 +-- error ER_BAD_FIELD_ERROR SELECT b.sc FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b; SELECT b.ac FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b; drop tables t1,t2; @@ -1545,7 +1558,7 @@ drop table t1; # create table t1 (a1 int); create table t2 (b1 int); ---error 1054 +--error ER_BAD_FIELD_ERROR select * from t1 where a2 > any(select b1 from t2); select * from t1 where a1 > any(select b1 from t2); drop table t1,t2; @@ -1566,11 +1579,11 @@ drop table t1; # Comparison subquery and row with nested rows # create table t1 (a integer, b integer); --- error 1241 +-- error ER_OPERAND_COLUMNS select row(1,(2,2)) in (select * from t1 ); --- error 1241 +-- error ER_OPERAND_COLUMNS select row(1,(2,2)) = (select * from t1 ); --- error 1241 +-- error ER_OPERAND_COLUMNS select (select * from t1) = row(1,(2,2)); drop table t1; @@ -1579,14 +1592,14 @@ drop table t1; # create table t1 (a integer); insert into t1 values (1); --- error 1247 +-- error ER_ILLEGAL_REFERENCE select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx ; --- error 1247 +-- error ER_ILLEGAL_REFERENCE select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx; select 1 as xx, 1 = ALL ( select 1 from t1 where 1 = xx ); --- error 1247 +-- error ER_ILLEGAL_REFERENCE select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx; --- error 1247 +-- error ER_ILLEGAL_REFERENCE select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx from DUAL; drop table t1; @@ -1723,17 +1736,17 @@ drop table t1, t2; # create table t1 (a int, b int); insert into t1 values (1,2); --- error 1241 +-- error ER_OPERAND_COLUMNS select 1 = (select * from t1); --- error 1241 +-- error ER_OPERAND_COLUMNS select (select * from t1) = 1; --- error 1241 +-- error ER_OPERAND_COLUMNS select (1,2) = (select a from t1); --- error 1241 +-- error ER_OPERAND_COLUMNS select (select a from t1) = (1,2); --- error 1241 +-- error ER_OPERAND_COLUMNS select (1,2,3) = (select * from t1); --- error 1241 +-- error ER_OPERAND_COLUMNS select (select * from t1) = (1,2,3); drop table t1; @@ -2014,21 +2027,21 @@ INSERT INTO t2 VALUES (2,1), (1,3), (2,1), (4,4), (2,2), (1,4); SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 ); ---error 1242 +--error ER_SUBQUERY_NO_1_ROW SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1); SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a; ---error 1242 +--error ER_SUBQUERY_NO_1_ROW SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a; SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2); ---error 1242 +--error ER_SUBQUERY_NO_1_ROW SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1); SELECT a FROM t1 GROUP BY a HAVING IFNULL((SELECT b FROM t2 WHERE b > 2), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; ---error 1242 +--error ER_SUBQUERY_NO_1_ROW SELECT a FROM t1 GROUP BY a HAVING IFNULL((SELECT b FROM t2 WHERE b > 1), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; @@ -2036,7 +2049,7 @@ SELECT a FROM t1 GROUP BY a SELECT a FROM t1 GROUP BY a HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; ---error 1242 +--error ER_SUBQUERY_NO_1_ROW SELECT a FROM t1 GROUP BY a HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3; @@ -2044,7 +2057,7 @@ SELECT a FROM t1 GROUP BY a SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); ---error 1242 +--error ER_SUBQUERY_NO_1_ROW SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); @@ -2052,7 +2065,7 @@ SELECT a FROM t1 SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); ---error 1242 +--error ER_SUBQUERY_NO_1_ROW SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); @@ -2263,12 +2276,12 @@ SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a > ALL(SELECT t2.c FROM t2 WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e HAVING SUM(t1.a+t2.c) < t3.e/4)); --- error 1111 +-- error ER_INVALID_GROUP_FUNC_USE SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a > ALL(SELECT t2.c FROM t2 WHERE EXISTS(SELECT t3.e FROM t3 WHERE SUM(t1.a+t2.c) < t3.e/4)); --- error 1111 +-- error ER_INVALID_GROUP_FUNC_USE SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20; SELECT t1.a FROM t1 GROUP BY t1.a @@ -2330,11 +2343,11 @@ drop table t1, t2; CREATE TABLE t1 (a INT); ---error 1054 +--error ER_BAD_FIELD_ERROR CREATE VIEW v1 AS SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); ---error 1054 +--error ER_BAD_FIELD_ERROR CREATE VIEW v2 AS SELECT * FROM t1 WHERE no_such_column = (SELECT 1); ---error 1054 +--error ER_BAD_FIELD_ERROR SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); DROP TABLE t1; @@ -2469,7 +2482,7 @@ SELECT * FROM t1 WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); #TODO:not supported ---error 1064 +--error ER_PARSE_ERROR explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) from t1; @@ -2839,12 +2852,12 @@ SELECT a FROM t1 t0 WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a; SET @@sql_mode='ansi'; ---error 1111 +--error ER_INVALID_GROUP_FUNC_USE SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a; ---error 1111 +--error ER_INVALID_GROUP_FUNC_USE SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a; ---error 1111 +--error ER_INVALID_GROUP_FUNC_USE SELECT a FROM t1 t0 WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a; @@ -3032,9 +3045,9 @@ while ($should_work_nesting) while ($nesting) { --echo $nesting ---error 0,1436 +--error 0,ER_STACK_OVERRUN_NEED_MORE eval $start $end; ---error 0,1436 +--error 0,ER_STACK_OVERRUN_NEED_MORE eval explain $start $end; let $start= $start $start_app; |