diff options
| -rw-r--r-- | mysql-test/r/derived.result | 26 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 12 | ||||
| -rw-r--r-- | mysql-test/t/derived.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 4 | ||||
| -rw-r--r-- | sql/sql_derived.cc | 5 |
5 files changed, 39 insertions, 10 deletions
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 0dce2b50819..95640e69d8b 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -30,6 +30,29 @@ SELECT 1 FROM (SELECT 1) a WHERE a=2; Unknown column 'a' in 'where clause' SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) as a; Unknown column 'a' in 'having clause' +select * from t1 as x1, (select * from t1) as x2; +a b a b +1 a 1 a +2 b 1 a +3 c 1 a +3 c 1 a +1 a 2 b +2 b 2 b +3 c 2 b +3 c 2 b +1 a 3 c +2 b 3 c +3 c 3 c +3 c 3 c +1 a 3 c +2 b 3 c +3 c 3 c +3 c 3 c +explain select * from t1 as x1, (select * from t1) as x2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY x1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 +2 DERIVED x1 ALL NULL NULL NULL NULL 4 drop table if exists t1.t2,t3; select * from (select 1) as a; 1 @@ -66,7 +89,8 @@ a t 20 20 explain select count(*) from t1 as tt1, (select * from t1) as tt2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE Select tables optimized away +1 PRIMARY Select tables optimized away +2 DERIVED tt1 index NULL a 4 NULL 10000 Using index drop table if exists t1; SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b; (SELECT * FROM (SELECT 1 as a) as a ) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 65221ab3f4b..3617f8c7782 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -32,15 +32,15 @@ a 1 SELECT 1 FROM (SELECT (SELECT a) b) c; Unknown column 'a' in 'field list' -SELECT * FROM (SELECT 1 as id) WHERE id IN (SELECT * FROM (SELECT 1 as id) ORDER BY id LIMIT 1); +SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id LIMIT 1); id 1 -SELECT * FROM (SELECT 1) WHERE 1 IN (SELECT 1,1); +SELECT * FROM (SELECT 1) a WHERE 1 IN (SELECT 1,1); Cardinality error (more/less than 1 columns) SELECT 1 IN (SELECT 1); 1 IN (SELECT 1) 1 -SELECT 1 FROM (SELECT 1 as a) WHERE 1 IN (SELECT (SELECT a)); +SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); 1 1 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8; @@ -524,7 +524,7 @@ x y 4 2 2 1 drop table t1, t2; -SELECT * FROM (SELECT 1) WHERE 1 IN (SELECT *); +SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *); No tables used drop table if exists t; CREATE TABLE t (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1; @@ -536,7 +536,7 @@ EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ref id id 5 const 1 Using where; Using index Warnings: -Note 1246 Select 2 was reduced during optimisation +Note 1247 Select 2 was reduced during optimisation SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3); id 1 @@ -548,7 +548,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ref id id 5 const 1 Using where; Using index 3 SUBSELECT No tables used Warnings: -Note 1246 Select 2 was reduced during optimisation +Note 1247 Select 2 was reduced during optimisation EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t index NULL id 5 NULL 2 Using where; Using index diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 5c2d0157f14..8f187dd46ba 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -18,6 +18,8 @@ SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=1; SELECT 1 FROM (SELECT 1) a WHERE a=2; --error 1054 SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) as a; +select * from t1 as x1, (select * from t1) as x2; +explain select * from t1 as x1, (select * from t1) as x2; drop table if exists t1.t2,t3; select * from (select 1) as a; select a from (select 1 as a) as b; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 3f997aa3d35..3e01dc5f5e9 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -17,7 +17,7 @@ SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1; 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 LIMIT 1); -- error 1239 -SELECT * FROM (SELECT 1)a WHERE 1 IN (SELECT 1,1); +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)); drop table if exists t1,t2,t3,t4,t5,t6,t7,t8; @@ -313,7 +313,7 @@ select * from t1; drop table t1, t2; -- error 1096 -SELECT * FROM (SELECT 1) WHERE 1 IN (SELECT *); +SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *); drop table if exists t; CREATE TABLE t (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1; INSERT INTO t VALUES (1),(2); diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 78356a0d725..76a97a2e4b8 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -107,7 +107,10 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t) table->derived_select_number= sl->select_number; table->tmp_table=TMP_TABLE; if (lex->describe) - tables->table_list->table=tables->table; // to fix a problem in EXPLAIN + { + if (tables) + tables->table_list->table=tables->table; // to fix a problem in EXPLAIN + } else sl->exclude(); t->db=(char *)""; |