bzr commit into mysql-5.5-next-mr-runtime branch (davi:2923) Bug#27249

[Davi Arnaut]

# 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
[phone]
+select t1.*, a, t1.* from t1;
+a	b	c	a	a	b	c
+1	2	3	1	1	2	3
[phone]
+select a, t1.* from t1;
+a	a	b	c
+1	1	2	3
[phone]
+select t1.*, a from t1;
+a	b	c	a
+1	2	3	1
[phone]
+select a, t1.*, b from t1;
+a	a	b	c	b
+1	1	2	3	2
[phone]
+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
[phone]
+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
[phone]
+select a as 'x', t1.* from t1;
+x	a	b	c
+1	1	2	3
[phone]
+select t1.*, a as 'x' from t1;
+a	b	c	x
+1	2	3	1
[phone]
+select a as 'x', t1.*, b as 'x' from t1;
+x	a	b	c	x
+1	1	2	3	2
[phone]
+select (select d from t2 where d > a) as 'x', t1.* from t1;
+x	a	b	c
+99	1	2	3
[phone]
+select t1.*, (select a from t2 where d > a) as 'x' from t1;
+a	b	c	x
+1	2	3	1
[phone]
+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
[phone]
+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/[e-mail]