Backport of Bug#27249 to mysql-next-mr

------------------------------------------------------------
revno: 2476.784.4
revision-id: sp1r-davi@moksha.local-20071008114751-46069
parent: sp1r-davi@moksha.local-20071003002731-48537
committer: davi@moksha.local
timestamp: Mon 2007-10-08 08:47:51 -0300
message:
  Bug#27249 table_wild with alias: select t1.* as something

  Aliases to table wildcards are silently ignored, but they should
  not be allowed as it is non-standard and currently useless. There
  is not point in having a alias to a wildcard of column names.

  The solution is to rewrite the select_item rule so that aliases
  for table wildcards are not accepted.

  Contribution by Martin Friebe

mysql-test/r/alias.result:
  Add test case result for Bug#27249
mysql-test/t/alias.test:
  Add test case for Bug#27249
sql/sql_yacc.yy:
  Split up select_item rule so that aliases for table wildcards
  are not accepted by the parser.

2 files changed, 268 insertions, 0 deletions

diff --git a/mysql-test/r/alias.result b/mysql-test/r/alias.result
index 6f0315da234..3190e8994e5 100644
--- a/mysql-test/r/alias.result
+++ b/mysql-test/r/alias.result
@@ -73,3 +73,142 @@ UPDATE t1 SET t1.xstatus_vor = Greatest(t1.xstatus_vor,1) WHERE t1.aufnr =
 "40004712" AND t1.plnfl = "000001" AND t1.vornr > "0010" ORDER BY t1.vornr
 ASC LIMIT 1;
 drop table t1;
+drop table if exists t1,t2,t3;
+create table t1 (a int, b int, c int);
+create table t2 (d int);
+create table t3 (a1 int, b1 int, c1 int);
+insert into t1 values(1,2,3);
+insert into t1 values(11,22,33);
+insert into t2 values(99);
+select t1.* as 'with_alias' 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 'as 'with_alias' from t1' at line 1
+select t2.* as 'with_alias' from t2;
+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 'as 'with_alias' from t2' at line 1
+select t1.*, t1.* as 'with_alias' 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 'as 'with_alias' from t1' at line 1
+select t1.* as 'with_alias', t1.* 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 'as 'with_alias', t1.* from t1' at line 1
+select t1.* as 'with_alias', t1.* as 'alias2' 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 'as 'with_alias', t1.* as 'alias2' from t1' at line 1
+select t1.* as 'with_alias', a, t1.* as 'alias2' 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 'as 'with_alias', a, t1.* as 'alias2' from t1' at line 1
+select a, t1.* as 'with_alias' 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 'as 'with_alias' from t1' at line 1
+select t1.* as 'with_alias', a 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 'as 'with_alias', a from t1' at line 1
+select a, t1.* as 'with_alias', b 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 'as 'with_alias', b from t1' at line 1
+select (select d from t2 where d > a), t1.* as 'with_alias' 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 'as 'with_alias' from t1' at line 1
+select t1.* as 'with_alias', (select a from t2 where d > a) 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 'as 'with_alias', (select a from t2 where d > a) from t1' at line 1
+select a as 'x', t1.* as 'with_alias' 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 'as 'with_alias' from t1' at line 1
+select t1.* as 'with_alias', a as 'x' 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 'as 'with_alias', a as 'x' from t1' at line 1
+select a as 'x', t1.* as 'with_alias', b as 'x' 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 'as 'with_alias', b as 'x' from t1' at line 1
+select (select d from t2 where d > a) as 'x', t1.* as 'with_alias' 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 'as 'with_alias' from t1' at line 1
+select t1.* as 'with_alias', (select a from t2 where d > a) as 'x' 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 'as 'with_alias', (select a from t2 where d > a) as 'x' from t1' at line 1
+select (select t2.* as 'x' from t2) 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 'as 'x' from t2) from t1' at line 1
+select a, (select t2.* as 'x' from t2) 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 'as 'x' from t2) from t1' at line 1
+select t1.*, (select t2.* as 'x' from t2) 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 'as 'x' from t2) from t1' at line 1
+insert into t3 select t1.* as 'with_alias' 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 'as 'with_alias' from t1' at line 1
+insert into t3 select t2.* as 'with_alias', 1, 2 from t2;
+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 'as 'with_alias', 1, 2 from t2' at line 1
+insert into t3 select t2.* as 'with_alias', d as 'x', d as 'z' from t2;
+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 'as 'with_alias', d as 'x', d as 'z' from t2' at line 1
+insert into t3 select t2.*, t2.* as 'with_alias', 3 from t2;
+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 'as 'with_alias', 3 from t2' at line 1
+create table t3 select t1.* as 'with_alias' 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 'as 'with_alias' from t1' at line 1
+create table t3 select t2.* as 'with_alias', 1, 2 from t2;
+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 'as 'with_alias', 1, 2 from t2' at line 1
+create table t3 select t2.* as 'with_alias', d as 'x', d as 'z' from t2;
+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 'as 'with_alias', d as 'x', d as 'z' from t2' at line 1
+create table t3 select t2.*, t2.* as 'with_alias', 3 from t2;
+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 'as 'with_alias', 3 from t2' at line 1
+select t1.* from t1;
+a	b	c
+1	2	3
+11	22	33
+select t2.* from t2;
+d
+99
+select t1.*, t1.* from t1;
+a	b	c	a	b	c
+1	2	3	1	2	3
+11	22	33	11	22	33
+select t1.*, a, t1.* from t1;
+a	b	c	a	a	b	c
+1	2	3	1	1	2	3
+11	22	33	11	11	22	33
+select a, t1.* from t1;
+a	a	b	c
+1	1	2	3
+11	11	22	33
+select t1.*, a from t1;
+a	b	c	a
+1	2	3	1
+11	22	33	11
+select a, t1.*, b from t1;
+a	a	b	c	b
+1	1	2	3	2
+11	11	22	33	22
+select (select d from t2 where d > a), t1.* from t1;
+(select d from t2 where d > a)	a	b	c
+99	1	2	3
+99	11	22	33
+select t1.*, (select a from t2 where d > a) from t1;
+a	b	c	(select a from t2 where d > a)
+1	2	3	1
+11	22	33	11
+select a as 'x', t1.* from t1;
+x	a	b	c
+1	1	2	3
+11	11	22	33
+select t1.*, a as 'x' from t1;
+a	b	c	x
+1	2	3	1
+11	22	33	11
+select a as 'x', t1.*, b as 'x' from t1;
+x	a	b	c	x
+1	1	2	3	2
+11	11	22	33	22
+select (select d from t2 where d > a) as 'x', t1.* from t1;
+x	a	b	c
+99	1	2	3
+99	11	22	33
+select t1.*, (select a from t2 where d > a) as 'x' from t1;
+a	b	c	x
+1	2	3	1
+11	22	33	11
+select (select t2.* from t2) from t1;
+(select t2.* from t2)
+99
+99
+select a, (select t2.* from t2) from t1;
+a	(select t2.* from t2)
+1	99
+11	99
+select t1.*, (select t2.* from t2) from t1;
+a	b	c	(select t2.* from t2)
+1	2	3	99
+11	22	33	99
+insert into t3 select t1.* from t1;
+insert into t3 select t2.*, 1, 2 from t2;
+insert into t3 select t2.*, d as 'x', d as 'z' from t2;
+insert into t3 select t2.*, t2.*, 3 from t2;
+create table t4 select t1.* from t1;
+drop table t4;
+create table t4 select t2.*, 1, 2 from t2;
+drop table t4;
+create table t4 select t2.*, d as 'x', d as 'z' from t2;
+drop table t4;
+drop table t1,t2,t3;
diff --git a/mysql-test/t/alias.test b/mysql-test/t/alias.test
index 6546581eef2..0e2d57598e2 100644
--- a/mysql-test/t/alias.test
+++ b/mysql-test/t/alias.test
@@ -86,3 +86,132 @@ ASC LIMIT 1;
 drop table t1;
 
 # End of 4.1 tests
+
+#
+# Bug#27249 table_wild with alias: select t1.* as something
+#
+
+--disable_warnings
+drop table if exists t1,t2,t3;
+--enable_warnings
+
+create table t1 (a int, b int, c int);
+create table t2 (d int);
+create table t3 (a1 int, b1 int, c1 int);
+insert into t1 values(1,2,3);
+insert into t1 values(11,22,33);
+insert into t2 values(99);
+
+# Invalid queries with alias on wild
+--error ER_PARSE_ERROR
+select t1.* as 'with_alias' from t1;
+--error ER_PARSE_ERROR
+select t2.* as 'with_alias' from t2;
+--error ER_PARSE_ERROR
+select t1.*, t1.* as 'with_alias' from t1;
+--error ER_PARSE_ERROR
+select t1.* as 'with_alias', t1.* from t1;
+--error ER_PARSE_ERROR
+select t1.* as 'with_alias', t1.* as 'alias2' from t1;
+--error ER_PARSE_ERROR
+select t1.* as 'with_alias', a, t1.* as 'alias2' from t1;
+
+# other fields without alias
+--error ER_PARSE_ERROR
+select a, t1.* as 'with_alias' from t1;
+--error ER_PARSE_ERROR
+select t1.* as 'with_alias', a from t1;
+--error ER_PARSE_ERROR
+select a, t1.* as 'with_alias', b from t1;
+--error ER_PARSE_ERROR
+select (select d from t2 where d > a), t1.* as 'with_alias' from t1;
+--error ER_PARSE_ERROR
+select t1.* as 'with_alias', (select a from t2 where d > a) from t1;
+
+# other fields with alias
+--error ER_PARSE_ERROR
+select a as 'x', t1.* as 'with_alias' from t1;
+--error ER_PARSE_ERROR
+select t1.* as 'with_alias', a as 'x' from t1;
+--error ER_PARSE_ERROR
+select a as 'x', t1.* as 'with_alias', b as 'x' from t1;
+--error ER_PARSE_ERROR
+select (select d from t2 where d > a) as 'x', t1.* as 'with_alias' from t1;
+--error ER_PARSE_ERROR
+select t1.* as 'with_alias', (select a from t2 where d > a) as 'x' from t1;
+
+# some more subquery
+--error ER_PARSE_ERROR
+select (select t2.* as 'x' from t2) from t1;
+--error ER_PARSE_ERROR
+select a, (select t2.* as 'x' from t2) from t1;
+--error ER_PARSE_ERROR
+select t1.*, (select t2.* as 'x' from t2) from t1;
+
+# insert
+--error ER_PARSE_ERROR
+insert into t3 select t1.* as 'with_alias' from t1;
+--error ER_PARSE_ERROR
+insert into t3 select t2.* as 'with_alias', 1, 2 from t2;
+--error ER_PARSE_ERROR
+insert into t3 select t2.* as 'with_alias', d as 'x', d as 'z' from t2;
+--error ER_PARSE_ERROR
+insert into t3 select t2.*, t2.* as 'with_alias', 3 from t2;
+
+# create
+--error ER_PARSE_ERROR
+create table t3 select t1.* as 'with_alias' from t1;
+--error ER_PARSE_ERROR
+create table t3 select t2.* as 'with_alias', 1, 2 from t2;
+--error ER_PARSE_ERROR
+create table t3 select t2.* as 'with_alias', d as 'x', d as 'z' from t2;
+--error ER_PARSE_ERROR
+create table t3 select t2.*, t2.* as 'with_alias', 3 from t2;
+
+#
+# Valid queries without alias on wild
+# (proof the above fail due to invalid aliasing)
+#
+
+select t1.* from t1;
+select t2.* from t2;
+select t1.*, t1.* from t1;
+select t1.*, a, t1.* from t1;
+
+# other fields without alias
+select a, t1.* from t1;
+select t1.*, a from t1;
+select a, t1.*, b from t1;
+select (select d from t2 where d > a), t1.* from t1;
+select t1.*, (select a from t2 where d > a) from t1;
+
+# other fields with alias
+select a as 'x', t1.* from t1;
+select t1.*, a as 'x' from t1;
+select a as 'x', t1.*, b as 'x' from t1;
+select (select d from t2 where d > a) as 'x', t1.* from t1;
+select t1.*, (select a from t2 where d > a) as 'x' from t1;
+
+# some more subquery
+select (select t2.* from t2) from t1;
+select a, (select t2.* from t2) from t1;
+select t1.*, (select t2.* from t2) from t1;
+
+# insert
+insert into t3 select t1.* from t1;
+insert into t3 select t2.*, 1, 2 from t2;
+insert into t3 select t2.*, d as 'x', d as 'z' from t2;
+insert into t3 select t2.*, t2.*, 3 from t2;
+
+# create
+create table t4 select t1.* from t1;
+drop table t4;
+create table t4 select t2.*, 1, 2 from t2;
+drop table t4;
+create table t4 select t2.*, d as 'x', d as 'z' from t2;
+drop table t4;
+
+# end
+drop table t1,t2,t3;
+
+# End of 5.2 tests