diff options
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 156 |
1 files changed, 150 insertions, 6 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index bbca9c905df..14806279362 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -923,7 +923,7 @@ select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a') (select c from t1 where a=t2.a) 1 1 a 2 0 b -NULL NULL NULL +NULL 0 NULL select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2; a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b') (select c from t1 where a=t2.a) 1 0 a @@ -933,7 +933,7 @@ select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c') (select c from t1 where a=t2.a) 1 0 a 2 0 b -NULL NULL NULL +NULL 0 NULL drop table t1,t2; create table t1 (a int, b real, c varchar(10)); insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b'); @@ -942,7 +942,7 @@ ROW(1, 1, 'a') IN (select a,b,c from t1) 1 select ROW(1, 2, 'a') IN (select a,b,c from t1); ROW(1, 2, 'a') IN (select a,b,c from t1) -NULL +0 select ROW(1, 1, 'a') IN (select b,a,c from t1); ROW(1, 1, 'a') IN (select b,a,c from t1) 1 @@ -960,7 +960,7 @@ ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a') 1 select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a'); ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a') -NULL +0 select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a'); ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a') 1 @@ -1200,7 +1200,7 @@ 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)); UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t); -ERROR 42S02: Unknown table 't' in field list +ERROR 42S22: Unknown column 't.i' in 'field list' select * from t1; i 1 @@ -1638,7 +1638,7 @@ ERROR 42S22: Unknown column 't1.s2' in 'where clause' select * from t1 where (select count(*) from t2 group by t1.s2) = 1; ERROR 42S22: Unknown column 't1.s2' in 'group statement' select count(*) from t2 group by t1.s2; -ERROR 42S02: Unknown table 't1' in group statement +ERROR 42S22: Unknown column 't1.s2' in 'group statement' drop table t1, t2; CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB)); CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA)); @@ -1793,10 +1793,18 @@ SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t id c 1 1 2 0 +SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id); +id c +1 1 +2 0 SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY t1.id; id c 1 1 2 0 +SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY id; +id c +1 1 +2 0 DROP TABLE t1,t2; CREATE TABLE t1 ( a int, b int ); INSERT INTO t1 VALUES (1,1),(2,2),(3,3); @@ -2709,7 +2717,143 @@ 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) +drop table t1 +#; +CREATE TABLE `t1` ( +`itemid` bigint(20) unsigned NOT NULL auto_increment, +`sessionid` bigint(20) unsigned default NULL, +`time` int(10) unsigned NOT NULL default '0', +`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT +NULL default '', +`data` text collate latin1_general_ci NOT NULL, +PRIMARY KEY (`itemid`) +) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; +INSERT INTO `t1` VALUES (1, 1, 1, 'D', ''); +CREATE TABLE `t2` ( +`sessionid` bigint(20) unsigned NOT NULL auto_increment, +`pid` int(10) unsigned NOT NULL default '0', +`date` int(10) unsigned NOT NULL default '0', +`ip` varchar(15) collate latin1_general_ci NOT NULL default '', +PRIMARY KEY (`sessionid`) +) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; +INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1'); +SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30; +ip count( e.itemid ) +10.10.10.1 1 +drop tables t1,t2; +create table t1 (fld enum('0','1')); +insert into t1 values ('1'); +select * from (select max(fld) from t1) as foo; +max(fld) +1 drop table t1; +CREATE TABLE t1 (one int, two int, flag char(1)); +CREATE TABLE t2 (one int, two int, flag char(1)); +INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N'); +INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N'); +SELECT * FROM t1 +WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N'); +one two flag +5 6 N +7 8 N +SELECT * FROM t1 +WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N'); +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 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 +2 3 NULL +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`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and ((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) having (<is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`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 <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`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`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(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 (((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and <is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`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'); +SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb')); +a b +aaa aaa +DROP TABLE t1; +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int, b int); +CREATE TABLE t3 (b int NOT NULL); +INSERT INTO t1 VALUES (1), (2), (3), (4); +INSERT INTO t2 VALUES (1,10), (3,30); +SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b +WHERE t3.b IS NOT NULL OR t2.a > 10; +a b b +SELECT * FROM t1 +WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b +WHERE t3.b IS NOT NULL OR t2.a > 10); +a +1 +2 +3 +4 +DROP TABLE t1,t2,t3; create table t1 (df decimal(5,1)); insert into t1 values(1.1); insert into t1 values(2.2); |