diff options
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 36 |
1 files changed, 29 insertions, 7 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 56df7576fb5..cef25645eea 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -55,7 +55,7 @@ SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 SELECT (SELECT 1), a; -ERROR 42S22: Unknown column 'a' in 'checking transformed subquery' +ERROR 42S22: Unknown column 'a' in 'field list' SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1; a 1 @@ -176,13 +176,13 @@ a b a b 1 7 2 7 -3 8 4 8 +3 8 explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using filesort -3 UNION t4 ALL NULL NULL NULL NULL 3 Using where; Using filesort +3 UNION t4 ALL NULL NULL NULL NULL 3 Using where 4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL Warnings: @@ -1007,7 +1007,9 @@ drop table t1, t2; CREATE TABLE `t1` (`i` int(11) NOT NULL default '0',PRIMARY KEY (`i`)) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES (1); UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i)); -ERROR HY000: Invalid use of group function +select * from t1; +i +1 drop table t1; CREATE TABLE t1 (a int(1)); EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1; @@ -1194,11 +1196,12 @@ PRIMARY KEY (`i`) ) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES (1); UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i)); -ERROR HY000: Invalid use of group function UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i)); -ERROR HY000: Invalid use of group function UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t); ERROR 42S02: Unknown table 't' in field list +select * from t1; +i +1 drop table t1; CREATE TABLE t1 ( id int(11) default NULL @@ -1974,5 +1977,24 @@ howmanyvalues mycount 3 3 4 4 SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues; -ERROR 42S22: Unknown column 'a.avalue' in 'where clause' +howmanyvalues mycount +1 1 +2 1 +3 1 +4 1 +drop table t1; +create table t1 (x int); +select (select b.x from t1 as b where b.x=a.x) from t1 as a where a.x=2 group by a.x; +(select b.x from t1 as b where b.x=a.x) drop table t1; +CREATE TABLE `t1` ( `master` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `slave` int(10) unsigned NOT NULL default '0', `access` int(10) unsigned NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`)); +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'); +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; +ERROR 42S22: Unknown column 'b.sc' in 'field list' +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; +ac +700 +NULL +drop tables t1,t2; |