diff options
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 62 |
1 files changed, 16 insertions, 46 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 8e9ccfcb4eb..7bf6f70eb11 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1323,6 +1323,10 @@ create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); create table t3 (a int, b int, index a (a)); insert into t1 values (1,10), (2,20), (3,30), (4,40); +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +insert into t1 +select rand()*100000+200,rand()*100000 from t0 A, t0 B, t0 C, t0 D; insert into t2 values (2), (3), (4), (5); insert into t3 values (10,3), (20,4), (30,5); select * from t2 where t2.a in (select a from t1); @@ -3554,28 +3558,6 @@ ORDER BY t1.t DESC LIMIT 1); i1 i2 t i1 i2 t 24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40 DROP TABLE t1, t2; -CREATE TABLE t1 (i INT); -(SELECT i FROM t1) UNION (SELECT i FROM t1); -i -SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS -( -(SELECT i FROM t1) UNION -(SELECT i FROM t1) -); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION -(SELECT i FROM t1) -)' at line 3 -SELECT * FROM t1 -WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 2 -explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) -from t1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12)) -from t1' at line 1 -explain select * from t1 where not exists -((select t11.i from t1 t11) union (select t12.i from t1 t12)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))' at line 2 -DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); insert into t1 (a) values (FLOOR(rand() * 100)); insert into t1 (a) select FLOOR(rand() * 100) from t1; @@ -3666,6 +3648,11 @@ DROP TABLE t1,t2; CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)); CREATE TABLE t2 (x int auto_increment, y int, z int, PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)); +create table t3 (a int); +insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +insert into t1 select RAND()*1000, A.a + 10*(B.a+10*(C.a+10*D.a)) +from t3 A, t3 B, t3 C, t3 D where D.a<3; +insert into t2(y,z) select t1.b, RAND()*1000 from t1, t3; SET SESSION sort_buffer_size = 32 * 1024; Warnings: Warning 1292 Truncated incorrect sort_buffer_size value: '32768' @@ -3680,7 +3667,7 @@ FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c FROM t1) t; COUNT(*) 3000 -DROP TABLE t1,t2; +DROP TABLE t1,t2,t3; CREATE TABLE t1 (id char(4) PRIMARY KEY, c int); CREATE TABLE t2 (c int); INSERT INTO t1 VALUES ('aa', 1); @@ -4260,37 +4247,20 @@ out_a MIN(b) 1 2 2 4 DROP TABLE t1; -CREATE TABLE t1 (a INT); -CREATE TABLE t2 (a INT); -INSERT INTO t1 VALUES (1),(2); -INSERT INTO t2 VALUES (1),(2); -SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); -2 -2 -2 -EXPLAIN EXTENDED -SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -Warnings: -Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) -EXPLAIN EXTENDED -SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION -(SELECT 1 FROM t2 WHERE t1.a = t2.a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION -(SELECT 1 FROM t2 WHERE t1.a = t2.a))' at line 2 -DROP TABLE t1,t2; +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(f11 int, f12 int); create table t2(f21 int unsigned not null, f22 int, f23 varchar(10)); insert into t1 values(1,1),(2,2), (3, 3); +insert into t2 +select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a +from t0 A, t0 B, t0 C, t0 D; set session sort_buffer_size= 33*1024; select count(*) from t1 where f12 = (select f22 from t2 where f22 = f12 order by f21 desc, f22, f23 limit 1); count(*) 3 -drop table t1,t2; +drop table t0,t1,t2; CREATE TABLE t4 ( f7 varchar(32) collate utf8_bin NOT NULL default '', f10 varchar(32) collate utf8_bin default NULL, |