diff options
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 58 |
1 files changed, 56 insertions, 2 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 4ee6d3088a5..c6a31856a24 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1011,7 +1011,7 @@ INSERT INTO t1 VALUES (1); UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i)); select * from t1; i -1 +2 drop table t1; CREATE TABLE t1 (a int(1)); EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1; @@ -1203,7 +1203,7 @@ UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t); ERROR 42S22: Unknown column 't.i' in 'field list' select * from t1; i -1 +3 drop table t1; CREATE TABLE t1 ( id int(11) default NULL @@ -2937,6 +2937,35 @@ retailerID statusID changed 0048 1 2006-01-06 12:37:50 0059 1 2006-01-06 12:37:50 drop table t1; +create table t1(a int, primary key (a)); +insert into t1 values (10); +create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b)); +insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989'); +explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r +ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' +ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where +SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r +ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' +ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; +a a b +10 3 35989 +explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r +ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' +ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where +SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r +ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' +ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; +a a b +10 1 359 +drop table t1,t2; create table t1 (df decimal(5,1)); insert into t1 values(1.1); insert into t1 values(2.2); @@ -3368,3 +3397,28 @@ 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) +); +i +SELECT * FROM t1 +WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); +i +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)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +4 UNION t12 system NULL NULL NULL NULL 0 const row not found +NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL +DROP TABLE t1; |