summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/include/view_alias.inc25
-rw-r--r--mysql-test/r/compare.result2
-rw-r--r--mysql-test/r/explain.result2
-rw-r--r--mysql-test/r/group_by.result4
-rw-r--r--mysql-test/r/ps.result4
-rw-r--r--mysql-test/r/subselect.result62
-rw-r--r--mysql-test/r/subselect3.result18
-rw-r--r--mysql-test/r/type_datetime.result8
-rw-r--r--mysql-test/r/union.result2
-rw-r--r--mysql-test/r/view.result4
-rw-r--r--mysql-test/r/view_alias.result111
-rw-r--r--mysql-test/t/view_alias.test92
-rw-r--r--sql/sql_select.cc12
-rw-r--r--sql/sql_view.cc32
14 files changed, 324 insertions, 54 deletions
diff --git a/mysql-test/include/view_alias.inc b/mysql-test/include/view_alias.inc
new file mode 100644
index 00000000000..17adcf5f0ab
--- /dev/null
+++ b/mysql-test/include/view_alias.inc
@@ -0,0 +1,25 @@
+# Routine to be called by t/view.inc
+#
+# The variable $after_select must be set before calling this routine.
+
+eval CREATE VIEW v1 AS SELECT $after_select;
+SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1';
+#
+# Extract the VIEW's SELECT from INFORMATION_SCHEMA.VIEWS
+let $query1 = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+#
+# Extract the VIEW's SELECT from SHOW CREATE VIEW
+# SHOW CREATE VIEW v1
+# View Create View character_set_client collation_connection
+# v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select '<--- .....
+let $value= query_get_value(SHOW CREATE VIEW v1, Create View, 1);
+let $query2 = `SELECT SUBSTR("$value",INSTR("$value",' as select ') + CHAR_LENGTH(' as '))`;
+DROP VIEW v1;
+
+# Recreate the view based on SELECT from INFORMATION_SCHEMA.VIEWS
+eval CREATE VIEW v1 AS $query1;
+DROP VIEW v1;
+# Recreate the view based on SHOW CREATE VIEW
+eval CREATE VIEW v1 AS $query2;
+DROP VIEW v1;
+
diff --git a/mysql-test/r/compare.result b/mysql-test/r/compare.result
index f9563b89b76..796821a87bd 100644
--- a/mysql-test/r/compare.result
+++ b/mysql-test/r/compare.result
@@ -88,7 +88,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
Warnings:
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t2`.`a` AS `a`,(select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (concat(`test`.`t1`.`b`,`test`.`t1`.`c`) = concat('0',`test`.`t2`.`a`,'01')))) AS `x` from `test`.`t2` order by `test`.`t2`.`a`
+Note 1003 select `test`.`t2`.`a` AS `a`,(select count(0) from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (concat(`test`.`t1`.`b`,`test`.`t1`.`c`) = concat('0',`test`.`t2`.`a`,'01')))) AS `x` from `test`.`t2` order by `test`.`t2`.`a`
DROP TABLE t1,t2;
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES (NOW()),(NOW()),(NOW());
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index 7784372de68..b8db8b53e06 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -224,6 +224,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
-Note 1003 select (select 1 AS `1` from `test`.`t2` where (`test`.`t2`.`d` = NULL)) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1`
+Note 1003 select (select 1 from `test`.`t2` where (`test`.`t2`.`d` = NULL)) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1`
DROP TABLE t1, t2;
End of 5.1 tests.
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 6ca08df40ea..90503300065 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1742,7 +1742,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
-Note 1003 select (select `test`.`t1`.`a` AS `a`) AS `aa`,count(distinct `test`.`t1`.`b`) AS `COUNT(DISTINCT b)` from `test`.`t1` group by ((select `test`.`t1`.`a` AS `a`) + 0)
+Note 1003 select (select `test`.`t1`.`a`) AS `aa`,count(distinct `test`.`t1`.`b`) AS `COUNT(DISTINCT b)` from `test`.`t1` group by ((select `test`.`t1`.`a`) + 0)
EXPLAIN EXTENDED
SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY -aa;
id select_type table type possible_keys key key_len ref rows filtered Extra
@@ -1750,7 +1750,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
-Note 1003 select (select `test`.`t1`.`a` AS `a`) AS `aa`,count(distinct `test`.`t1`.`b`) AS `COUNT(DISTINCT b)` from `test`.`t1` group by -((select `test`.`t1`.`a` AS `a`))
+Note 1003 select (select `test`.`t1`.`a`) AS `aa`,count(distinct `test`.`t1`.`b`) AS `COUNT(DISTINCT b)` from `test`.`t1` group by -((select `test`.`t1`.`a`))
# should return only one record
SELECT (SELECT tt.a FROM t1 tt LIMIT 1) aa, COUNT(DISTINCT b) FROM t1
GROUP BY aa;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 1e67bfa7d37..cf08d763e5c 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -1786,7 +1786,7 @@ prepare stmt from "create view v1 (c,d,e,f) as select a,b,a in (select a+2 from
execute stmt;
show create view v1;
View Create View character_set_client collation_connection
-v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `c`,`t1`.`b` AS `d`,`t1`.`a` in (select (`t1`.`a` + 2) AS `a+2` from `t1`) AS `e`,`t1`.`a` = all (select `t1`.`a` AS `a` from `t1`) AS `f` from `t1` latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `c`,`t1`.`b` AS `d`,`t1`.`a` in (select (`t1`.`a` + 2) from `t1`) AS `e`,`t1`.`a` = all (select `t1`.`a` from `t1`) AS `f` from `t1` latin1 latin1_swedish_ci
select * from v1;
c d e f
drop view v1;
@@ -1794,7 +1794,7 @@ execute stmt;
deallocate prepare stmt;
show create view v1;
View Create View character_set_client collation_connection
-v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `c`,`t1`.`b` AS `d`,`t1`.`a` in (select (`t1`.`a` + 2) AS `a+2` from `t1`) AS `e`,`t1`.`a` = all (select `t1`.`a` AS `a` from `t1`) AS `f` from `t1` latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `c`,`t1`.`b` AS `d`,`t1`.`a` in (select (`t1`.`a` + 2) from `t1`) AS `e`,`t1`.`a` = all (select `t1`.`a` from `t1`) AS `f` from `t1` latin1 latin1_swedish_ci
select * from v1;
c d e f
drop view v1;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index bda33109524..b9edd1df85c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -32,7 +32,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1249 Select 2 was reduced during optimization
-Note 1003 select (select 0 AS `0` union select 0 AS `0`) AS `(SELECT (SELECT 0 UNION SELECT 0))`
+Note 1003 select (select 0 union select 0) AS `(SELECT (SELECT 0 UNION SELECT 0))`
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) as c HAVING a=1) as b;
@@ -50,7 +50,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
Warnings:
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' AS `a`) = 1)
+Note 1003 select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1') = 1)
SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
1
1
@@ -187,7 +187,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` AS `a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) AS `max(t2.a)*4` from `test`.`t2`)) order by `a`)
+Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = (select `test`.`t3`.`a` from `test`.`t3` order by 1 desc limit 1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) from `test`.`t2`)) order by `a`)
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
(select a from t3 where a<t2.a*4 order by 1 desc limit 1) a
3 1
@@ -203,7 +203,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
Warnings:
-Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
+Note 1003 select (select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
a
2
@@ -224,7 +224,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`
+Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`
select * from t3 where exists (select * from t2 where t2.b=t3.a);
a
7
@@ -314,7 +314,7 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
-Note 1003 select (select '2' AS `a` from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
+Note 1003 select (select '2' from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
ERROR 21000: Subquery returns more than 1 row
create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
@@ -332,7 +332,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
Warnings:
Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
+Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
ERROR 23000: Column 'a' in field list is ambiguous
drop table t1,t2,t3;
@@ -367,7 +367,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00
3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index
Warnings:
-Note 1003 select 'joce' AS `pseudo`,(select 'test' AS `email` from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1
+Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
t8 WHERE pseudo='joce');
ERROR 21000: Operand should contain 1 column(s)
@@ -399,7 +399,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
Warnings:
-Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = '2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
+Note 1003 select (select distinct `test`.`t1`.`date` from `test`.`t1` where (`test`.`t1`.`date` = '2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
date
2002-08-03
@@ -743,7 +743,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 AS `3` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))
+Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
id
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
@@ -906,7 +906,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
drop table t1,t2,t3;
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
@@ -1018,19 +1018,19 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 select (select rand() AS `RAND()` from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1`
+Note 1003 select (select rand() from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1`
EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 select (select encrypt('test') AS `ENCRYPT('test')` from `test`.`t1`) AS `(SELECT ENCRYPT('test') FROM t1)` from `test`.`t1`
+Note 1003 select (select encrypt('test') from `test`.`t1`) AS `(SELECT ENCRYPT('test') FROM t1)` from `test`.`t1`
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 select (select benchmark(1,1) AS `BENCHMARK(1,1)` from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1`
+Note 1003 select (select benchmark(1,1) from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1`
drop table t1;
CREATE TABLE `t1` (
`mot` varchar(30) character set latin1 NOT NULL default '',
@@ -1125,7 +1125,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
3 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,(select (select rand() AS `rand()` from `test`.`t1` limit 1) AS `(select rand() from t1 limit 1)` from `test`.`t1` limit 1) AS `(select (select rand() from t1 limit 1) from t1 limit 1)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`a` AS `a`,(select (select rand() from `test`.`t1` limit 1) from `test`.`t1` limit 1) AS `(select (select rand() from t1 limit 1) from t1 limit 1)` from `test`.`t1`
drop table t1;
select t1.Continent, t2.Name, t2.Population from t1 LEFT JOIN t2 ON t1.Code = t2.Country where t2.Population IN (select max(t2.Population) AS Population from t2, t1 where t2.Country = t1.Code group by Continent);
ERROR 42S02: Table 'test.t1' doesn't exist
@@ -1179,7 +1179,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
Warnings:
-Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
@@ -1189,7 +1189,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
Warnings:
-Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
drop table t1;
CREATE TABLE `t1` (
`i` int(11) NOT NULL default '0',
@@ -1234,7 +1234,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
-Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) AS `MAX(salary)` from `test`.`t1`))
+Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
drop table t1;
CREATE TABLE t1 (
ID int(10) unsigned NOT NULL auto_increment,
@@ -1317,7 +1317,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00
2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
drop table t1, t2, t3;
create table t1 (a int, b int, index a (a,b));
create table t2 (a int, index a (a));
@@ -1356,7 +1356,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
insert into t1 values (3,31);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
@@ -1515,7 +1515,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select NULL AS `b` from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select NULL from `test`.`t2` group by 1)))
select * from t3 where a >= some (select b from t2 group by 1);
a
explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1523,7 +1523,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select NULL AS `b` from `test`.`t2` group by 1)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select NULL from `test`.`t2` group by 1)))
select * from t3 where NULL >= any (select b from t2);
a
explain extended select * from t3 where NULL >= any (select b from t2);
@@ -1566,7 +1566,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) AS `max(b)` from `test`.`t2` group by `test`.`t2`.`a`)))
+Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`)))
drop table t2, t3;
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
@@ -1743,7 +1743,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index
Warnings:
Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))
+Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');
@@ -2279,7 +2279,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))
+Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))
drop table t1;
CREATE TABLE t1 (t1_a int);
INSERT INTO t1 VALUES (1);
@@ -2820,19 +2820,19 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 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` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
+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`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<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 filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 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` = 'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`))))
+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`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '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 filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 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` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
+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`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<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');
@@ -4275,7 +4275,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
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`))
+Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 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));
@@ -4353,13 +4353,13 @@ 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
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1))))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 from `test`.`t1` group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1))))
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1))))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1))))
DROP TABLE t1;
#
# Bug#45061: Incorrectly market field caused wrong result.
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index d5fb1a7bbde..e19160fe4ee 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -30,7 +30,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2`
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2`
explain extended
select a, oref from t2
where a in (select max(ie) from t1 where oref=t2.oref group by grp);
@@ -39,7 +39,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
select a, oref, a in (
select max(ie) from t1 where oref=t2.oref group by grp union
select max(ie) from t1 where oref=t2.oref group by grp
@@ -68,7 +68,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3`
+Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3`
drop table t1, t2, t3;
create table t1 (a int, oref int, key(a));
insert into t1 values
@@ -157,7 +157,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3`
+Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3`
drop table t1, t2, t3;
create table t1 (a int NOT NULL, b int NOT NULL, key(a));
insert into t1 values
@@ -185,7 +185,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3`
+Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3`
drop table t1,t2,t3;
create table t1 (oref int, grp int);
insert into t1 (oref, grp) values
@@ -209,7 +209,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2`
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2`
drop table t1, t2;
create table t1 (a int, b int, primary key (a));
insert into t1 values (1,1), (3,1),(100,1);
@@ -258,7 +258,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
select a,b, oref,
(a,b) in (select a,b from t1,t4 where c=t2.oref) Z
from t2;
@@ -703,7 +703,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (not(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` where ((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)) having <is_not_null_test>(`test`.`t1`.`a`))))))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (not(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t1` where ((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)) having <is_not_null_test>(`test`.`t1`.`a`))))))
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
a
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));
@@ -864,7 +864,7 @@ Level Code Message
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
Note 1276 Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2
Error 1054 Unknown column 'c' in 'field list'
-Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `c`) AS `(SELECT COUNT(a) FROM
+Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `c`) AS `(SELECT COUNT(a) FROM
(SELECT COUNT(b) FROM t1) AS x GROUP BY c
)` from `test`.`t1` group by `test`.`t1`.`b`) `y`
DROP TABLE t1;
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index b6281443751..9b18f250d21 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -517,7 +517,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
Warnings:
Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
-Note 1003 select '1' AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t1` `x1` where 0))
+Note 1003 select '1' AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` where <in_optimizer>('1',<exists>(select 1 from `test`.`t1` `x1` where 0))
select * from t1
where id in (select id from t1 as x1 where (t1.cur_date is null));
id cur_date
@@ -529,7 +529,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
Warnings:
Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
-Note 1003 select '1' AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t2` `x1` where 0))
+Note 1003 select '1' AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` where <in_optimizer>('1',<exists>(select 1 from `test`.`t2` `x1` where 0))
select * from t2
where id in (select id from t2 as x1 where (t2.cur_date is null));
id cur_date
@@ -543,7 +543,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY x1 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`id`,<exists>(select 1 AS `Not_used` from `test`.`t1` `x1` where ((`test`.`t1`.`cur_date` = 0) and (<cache>(`test`.`t1`.`id`) = `test`.`x1`.`id`))))
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`id`,<exists>(select 1 from `test`.`t1` `x1` where ((`test`.`t1`.`cur_date` = 0) and (<cache>(`test`.`t1`.`id`) = `test`.`x1`.`id`))))
select * from t1
where id in (select id from t1 as x1 where (t1.cur_date is null));
id cur_date
@@ -555,7 +555,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY x1 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `Not_used` from `test`.`t2` `x1` where ((`test`.`t2`.`cur_date` = 0) and (<cache>(`test`.`t2`.`id`) = `test`.`x1`.`id`))))
+Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 from `test`.`t2` `x1` where ((`test`.`t2`.`cur_date` = 0) and (<cache>(`test`.`t2`.`id`) = `test`.`x1`.`id`))))
select * from t2
where id in (select id from t2 as x1 where (t2.cur_date is null));
id cur_date
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 98d177a5448..1ee313a2b46 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -1636,7 +1636,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by (select `test`.`t1`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`b` = 12))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by (select `test`.`t1`.`a` from `test`.`t2` where (`test`.`t2`.`b` = 12))
# Should not crash
SELECT * FROM t1 UNION SELECT * FROM t1
ORDER BY (SELECT a FROM t2 WHERE b = 12);
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index f12c9d6a31d..fa6f1939592 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -717,7 +717,7 @@ create view v1 as select a from t1;
create view v2 as select a from t2 where a in (select a from v1);
show create view v2;
View Create View character_set_client collation_connection
-v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where `t2`.`a` in (select `v1`.`a` AS `a` from `v1`) latin1 latin1_swedish_ci
+v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where `t2`.`a` in (select `v1`.`a` from `v1`) latin1 latin1_swedish_ci
drop view v2, v1;
drop table t1, t2;
CREATE VIEW `v 1` AS select 5 AS `5`;
@@ -2982,7 +2982,7 @@ SHOW WARNINGS;
Level Code Message
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
-v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`pk` AS `pk` from (`t1` join `t2` on(((`t2`.`fk` = `t1`.`pk`) and (`t2`.`ver` = (select max(`t`.`ver`) AS `MAX(t.ver)` from `t2` `t` where (`t`.`org` = `t2`.`org`)))))) latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`pk` AS `pk` from (`t1` join `t2` on(((`t2`.`fk` = `t1`.`pk`) and (`t2`.`ver` = (select max(`t`.`ver`) from `t2` `t` where (`t`.`org` = `t2`.`org`)))))) latin1 latin1_swedish_ci
DROP VIEW v1;
DROP TABLE t1, t2;
DROP FUNCTION IF EXISTS f1;
diff --git a/mysql-test/r/view_alias.result b/mysql-test/r/view_alias.result
new file mode 100644
index 00000000000..72c4bf29f25
--- /dev/null
+++ b/mysql-test/r/view_alias.result
@@ -0,0 +1,111 @@
+#
+# Bug#40277 SHOW CREATE VIEW returns invalid SQL
+# Bug#41999 SHOW CREATE VIEW returns invalid SQL if subquery is used in SELECT list
+#
+# 65 characters exceed the maximum length of a column identifier. The system cannot derive the name from statement.
+# Constant with length = 65 . Expect to get the identifier 'Name_exp_1'.
+CREATE VIEW v1 AS SELECT '<--- 65 char including the arrows --->';
+SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1';
+COLUMN_NAME
+Name_exp_1
+DROP VIEW v1;
+CREATE VIEW v1 AS select '<--- 65 char including the arrows --->' AS `Name_exp_1`;
+DROP VIEW v1;
+CREATE VIEW v1 AS select '<--- 65 char including the arrows --->' AS `Name_exp_1`;
+DROP VIEW v1;
+# Subquery with length = 65 . Expect to get the identifier 'Name_exp_1'.
+# Attention: Identifier for the column within the subquery will be not generated.
+CREATE VIEW v1 AS SELECT (SELECT '<--- 54 char including the arrows (+ 11 outside) -->');
+SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1';
+COLUMN_NAME
+Name_exp_1
+DROP VIEW v1;
+CREATE VIEW v1 AS select (select '<--- 54 char including the arrows (+ 11 outside) -->') AS `Name_exp_1`;
+DROP VIEW v1;
+CREATE VIEW v1 AS select (select '<--- 54 char including the arrows (+ 11 outside) -->') AS `Name_exp_1`;
+DROP VIEW v1;
+# -----------------------------------------------------------------------------------------------------------------
+# 64 characters are the maximum length of a column identifier. The system can derive the name from the statement.
+CREATE VIEW v1 AS SELECT '<--- 64 char including the arrows --->';
+SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1';
+COLUMN_NAME
+<--- 64 char including the arrows --->
+DROP VIEW v1;
+CREATE VIEW v1 AS select '<--- 64 char including the arrows --->' AS `<--- 64 char including the arrows --->`;
+DROP VIEW v1;
+CREATE VIEW v1 AS select '<--- 64 char including the arrows --->' AS `<--- 64 char including the arrows --->`;
+DROP VIEW v1;
+CREATE VIEW v1 AS SELECT (SELECT '<--- 53 char including the arrows (+ 11 outside) --->');
+SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1';
+COLUMN_NAME
+(SELECT '<--- 53 char including the arrows (+ 11 outside) --->')
+DROP VIEW v1;
+CREATE VIEW v1 AS select (select '<--- 53 char including the arrows (+ 11 outside) --->') AS `(SELECT '<--- 53 char including the arrows (+ 11 outside) --->')`;
+DROP VIEW v1;
+CREATE VIEW v1 AS select (select '<--- 53 char including the arrows (+ 11 outside) --->') AS `(SELECT '<--- 53 char including the arrows (+ 11 outside) --->')`;
+DROP VIEW v1;
+# -----------------------------------------------------------------------------------------------------------------
+# Identifiers must not have trailing spaces. The system cannot derive the name from a constant with trailing space.
+# Generated identifiers have at their end the position within the select column list.
+# 'c2 ' -> 'Name_exp_1' , ' c4 ' -> 'Name_exp_2'
+CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ';
+SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1';
+COLUMN_NAME
+c1
+Name_exp_2
+c3
+Name_exp_4
+DROP VIEW v1;
+CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`;
+DROP VIEW v1;
+CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`;
+DROP VIEW v1;
+#
+# Bug#40277 SHOW CREATE VIEW returns invalid SQL
+#
+DROP VIEW IF EXISTS v1;
+DROP TABLE IF EXISTS t1,t2;
+# Column name exceeds the maximum length.
+CREATE VIEW v1 AS SELECT '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555';
+DROP VIEW v1;
+CREATE VIEW v1 AS select '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555' AS `Name_exp_1`;
+DROP VIEW v1;
+# Column names with leading trailing spaces.
+CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ';
+DROP VIEW v1;
+CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`;
+DROP VIEW v1;
+# Column name conflicts with a auto-generated one.
+CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ', 'Name_exp_2';
+DROP VIEW v1;
+CREATE VIEW v1 AS select 'c1' AS `c1`,'c2 ' AS `Name_exp_2`,' c3' AS `c3`,' c4 ' AS `Name_exp_4`,'Name_exp_2' AS `My_exp_Name_exp_2`;
+DROP VIEW v1;
+# Invalid conlumn name in subquery.
+CREATE VIEW v1 AS SELECT (SELECT ' c1 ');
+DROP VIEW v1;
+CREATE VIEW v1 AS select (select ' c1 ') AS `(SELECT ' c1 ')`;
+DROP VIEW v1;
+CREATE TABLE t1(a INT);
+CREATE TABLE t2 LIKE t1;
+# Test alias in subquery
+CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 AS b WHERE b.a = 0);
+DROP VIEW v1;
+CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select 1 from `test`.`t2` `b` where (`b`.`a` = 0));
+DROP VIEW v1;
+# Test column alias in subquery
+CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT a AS alias FROM t1 GROUP BY alias);
+SHOW CREATE VIEW v1;
+View Create View character_set_client collation_connection
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where exists(select `t1`.`a` AS `alias` from `t1` group by `t1`.`a`) latin1 latin1_swedish_ci
+DROP VIEW v1;
+CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select `test`.`t1`.`a` AS `alias` from `test`.`t1` group by `test`.`t1`.`a`);
+DROP VIEW v1;
+# Alias as the expression column name.
+CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT ' a ' AS alias FROM t1 GROUP BY alias);
+SHOW CREATE VIEW v1;
+View Create View character_set_client collation_connection
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where exists(select ' a ' AS `alias` from `t1` group by ' a ') latin1 latin1_swedish_ci
+DROP VIEW v1;
+CREATE VIEW v1 AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select ' a ' AS `alias` from `test`.`t1` group by ' a ');
+DROP VIEW v1;
+DROP TABLE t1, t2;
diff --git a/mysql-test/t/view_alias.test b/mysql-test/t/view_alias.test
new file mode 100644
index 00000000000..b155ba6c2a9
--- /dev/null
+++ b/mysql-test/t/view_alias.test
@@ -0,0 +1,92 @@
+--echo #
+--echo # Bug#40277 SHOW CREATE VIEW returns invalid SQL
+--echo # Bug#41999 SHOW CREATE VIEW returns invalid SQL if subquery is used in SELECT list
+--echo #
+
+--echo # 65 characters exceed the maximum length of a column identifier. The system cannot derive the name from statement.
+--echo # Constant with length = 65 . Expect to get the identifier 'Name_exp_1'.
+let $after_select= '<--- 65 char including the arrows --->';
+--source include/view_alias.inc
+--echo # Subquery with length = 65 . Expect to get the identifier 'Name_exp_1'.
+--echo # Attention: Identifier for the column within the subquery will be not generated.
+let $after_select= (SELECT '<--- 54 char including the arrows (+ 11 outside) -->');
+--source include/view_alias.inc
+--echo # -----------------------------------------------------------------------------------------------------------------
+#
+--echo # 64 characters are the maximum length of a column identifier. The system can derive the name from the statement.
+let $after_select= '<--- 64 char including the arrows --->';
+--source include/view_alias.inc
+let $after_select= (SELECT '<--- 53 char including the arrows (+ 11 outside) --->');
+--source include/view_alias.inc
+--echo # -----------------------------------------------------------------------------------------------------------------
+#
+--echo # Identifiers must not have trailing spaces. The system cannot derive the name from a constant with trailing space.
+--echo # Generated identifiers have at their end the position within the select column list.
+--echo # 'c2 ' -> 'Name_exp_1' , ' c4 ' -> 'Name_exp_2'
+let $after_select= 'c1', 'c2 ', ' c3', ' c4 ';
+--source include/view_alias.inc
+
+--echo #
+--echo # Bug#40277 SHOW CREATE VIEW returns invalid SQL
+--echo #
+
+--disable_warnings
+DROP VIEW IF EXISTS v1;
+DROP TABLE IF EXISTS t1,t2;
+--enable_warnings
+
+--echo # Column name exceeds the maximum length.
+CREATE VIEW v1 AS SELECT '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555';
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+--echo # Column names with leading trailing spaces.
+CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ';
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+--echo # Column name conflicts with a auto-generated one.
+CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ', 'Name_exp_2';
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+--echo # Invalid conlumn name in subquery.
+CREATE VIEW v1 AS SELECT (SELECT ' c1 ');
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+CREATE TABLE t1(a INT);
+CREATE TABLE t2 LIKE t1;
+
+--echo # Test alias in subquery
+CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 AS b WHERE b.a = 0);
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+--echo # Test column alias in subquery
+CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT a AS alias FROM t1 GROUP BY alias);
+SHOW CREATE VIEW v1;
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+--echo # Alias as the expression column name.
+CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT ' a ' AS alias FROM t1 GROUP BY alias);
+SHOW CREATE VIEW v1;
+let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`;
+DROP VIEW v1;
+eval CREATE VIEW v1 AS $query;
+DROP VIEW v1;
+
+DROP TABLE t1, t2;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index bc68d3b03e0..2be706cb55e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -16950,7 +16950,17 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type)
first= 0;
else
str->append(',');
- item->print_item_w_name(str, query_type);
+
+ if (master_unit()->item && item->is_autogenerated_name)
+ {
+ /*
+ Do not print auto-generated aliases in subqueries. It has no purpose
+ in a view definition or other contexts where the query is printed.
+ */
+ item->print(str, query_type);
+ }
+ else
+ item->print_item_w_name(str, query_type);
}
/*
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index c66990c54c6..5ec80dfb621 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -155,6 +155,35 @@ err:
DBUG_RETURN(TRUE);
}
+
+/**
+ Check if auto generated column names are conforming and
+ possibly generate a conforming name for them if not.
+
+ @param item_list List of Items which should be checked
+*/
+
+static void make_valid_column_names(List<Item> &item_list)
+{
+ Item *item;
+ uint name_len;
+ List_iterator_fast<Item> it(item_list);
+ char buff[NAME_LEN];
+ DBUG_ENTER("make_valid_column_names");
+
+ for (uint column_no= 1; (item= it++); column_no++)
+ {
+ if (!item->is_autogenerated_name || !check_column_name(item->name))
+ continue;
+ name_len= my_snprintf(buff, NAME_LEN, "Name_exp_%u", column_no);
+ item->orig_name= item->name;
+ item->set_name(buff, name_len, system_charset_info);
+ }
+
+ DBUG_VOID_RETURN;
+}
+
+
/*
Fill defined view parts
@@ -548,6 +577,9 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views,
}
}
+ /* Check if the auto generated column names are conforming. */
+ make_valid_column_names(select_lex->item_list);
+
if (check_duplicate_names(select_lex->item_list, 1))
{
res= TRUE;
View Lorry Controller Status