# At a local mysql-5.5-next-mr-runtime repository of davi
2923 Davi Arnaut 2009-11-10
Backport of Bug#27249 to mysql-next-mr
------------------------------------------------------------
revno: 2476.784.4
revision-id: sp1r-davi@stripped
parent: sp1r-davi@stripped
committer: davi@stripped
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.
modified:
mysql-test/r/alias.result
mysql-test/t/alias.test
sql/sql_yacc.yy
=== modified file 'mysql-test/r/alias.result'
--- a/mysql-test/r/alias.result 2005-06-27 11:12:10 +0000
+++ b/mysql-test/r/alias.result 2009-11-10 20:43:43 +0000
@@ -73,3 +73,142 @@ UPDATE t1 SET t1.xstatus_vor = Greatest(
"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;
=== modified file 'mysql-test/t/alias.test'
--- a/mysql-test/t/alias.test 2005-10-31 11:25:08 +0000
+++ b/mysql-test/t/alias.test 2009-11-10 20:43:43 +0000
@@ -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
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2009-11-10 18:48:46 +0000
+++ b/sql/sql_yacc.yy 2009-11-10 20:43:43 +0000
@@ -1193,7 +1193,7 @@ bool my_yyoverflow(short **a, YYSTYPE **
%type <item>
literal text_literal insert_ident order_ident
- simple_ident select_item2 expr opt_expr opt_else sum_expr in_sum_expr
+ simple_ident expr opt_expr opt_else sum_expr in_sum_expr
variable variable_aux bool_pri
predicate bit_expr
table_wild simple_expr udf_expr
@@ -6891,7 +6891,14 @@ select_item_list:
;
select_item:
- remember_name select_item2 remember_end select_alias
+ remember_name table_wild remember_end
+ {
+ THD *thd= YYTHD;
+
+ if (add_item_to_list(thd, $2))
+ MYSQL_YYABORT;
+ }
+ | remember_name expr remember_end select_alias
{
THD *thd= YYTHD;
DBUG_ASSERT($1 < $3);
@@ -6928,11 +6935,6 @@ remember_end:
}
;
-select_item2:
- table_wild { $$=$1; /* table.* */ }
- | expr { $$=$1; }
- ;
-
select_alias:
/* empty */ { $$=null_lex_str;}
| AS ident { $$=$2; }
Attachment: [text/bzr-bundle] bzr/davi.arnaut@sun.com-20091110204343-josj0c8p4rww2pq8.bundle
| Thread |
|---|
| • bzr commit into mysql-5.5-next-mr-runtime branch (davi:2923) Bug#27249 | Davi Arnaut | 10 Nov |
