*** mysql-5.0.bk-orig/sql/sql_yacc.yy Mon Mar 19 20:59:22 2007 --- mysql-5.0.bk-wild_alias/sql/sql_yacc.yy Sat Mar 24 15:05:55 2007 *************** *** 1061,1080 **** %type replace_lock_option opt_low_priority insert_lock_option load_data_lock %type ! literal text_literal insert_ident order_ident ! simple_ident select_item2 expr opt_expr opt_else sum_expr in_sum_expr ! variable variable_aux bool_term bool_factor bool_test bool_pri ! predicate bit_expr bit_term bit_factor value_expr term factor ! table_wild simple_expr udf_expr ! expr_or_default set_expr_or_default interval_expr ! param_marker geometry_function ! signed_literal now_or_signed_literal opt_escape ! sp_opt_default ! simple_ident_nospvar simple_ident_q field_or_var limit_option %type NUM_literal --- 1061,1080 ---- %type replace_lock_option opt_low_priority insert_lock_option load_data_lock %type ! literal text_literal insert_ident order_ident ! simple_ident expr opt_expr opt_else sum_expr in_sum_expr ! variable variable_aux bool_term bool_factor bool_test bool_pri ! predicate bit_expr bit_term bit_factor value_expr term factor ! table_wild simple_expr udf_expr ! expr_or_default set_expr_or_default interval_expr ! param_marker geometry_function ! signed_literal now_or_signed_literal opt_escape ! sp_opt_default ! simple_ident_nospvar simple_ident_q field_or_var limit_option %type NUM_literal *************** *** 4423,4459 **** MYSQL_YYABORT; (thd->lex->current_select->with_wild)++; }; select_item: ! remember_name select_item2 remember_end select_alias ! { ! if (add_item_to_list(YYTHD, $2)) ! MYSQL_YYABORT; ! if ($4.str) { $2->is_autogenerated_name= FALSE; ! $2->set_name($4.str, $4.length, system_charset_info); } ! else if (!$2->name) { ! char *str = $1; ! if (str[-1] == '`') ! str--; ! $2->set_name(str,(uint) ($3 - str), YYTHD->charset()); ! } ! }; remember_name: { $$=(char*) Lex->tok_start; }; remember_end: { $$=(char*) Lex->tok_end; }; - - select_item2: - table_wild { $$=$1; } /* table.* */ - | expr { $$=$1; }; select_alias: /* empty */ { $$=null_lex_str;} | AS ident { $$=$2; } | AS TEXT_STRING_sys { $$=$2; } --- 4423,4461 ---- MYSQL_YYABORT; (thd->lex->current_select->with_wild)++; }; + /* need to remember_name for table to avoid shift/reduce conflict */ select_item: ! remember_name table_wild remember_end ! { ! if (add_item_to_list(YYTHD, $2)) ! MYSQL_YYABORT; ! } ! | remember_name expr remember_end select_alias ! { ! if (add_item_to_list(YYTHD, $2)) ! MYSQL_YYABORT; ! if ($4.str) { $2->is_autogenerated_name= FALSE; ! $2->set_name($4.str, $4.length, system_charset_info); } ! else if (!$2->name) { ! char *str = $1; ! if (str[-1] == '`') ! str--; ! $2->set_name(str,(uint) ($3 - str), YYTHD->charset()); ! } ! }; remember_name: { $$=(char*) Lex->tok_start; }; remember_end: { $$=(char*) Lex->tok_end; }; select_alias: /* empty */ { $$=null_lex_str;} | AS ident { $$=$2; } | AS TEXT_STRING_sys { $$=$2; } *** mysql-5.0.bk-orig/mysql-test/t/alias.test Fri Mar 23 10:21:52 2007 --- mysql-5.0.bk-wild_alias/mysql-test/t/alias.test Sat Mar 24 15:48:59 2007 *************** *** 1,7 **** --disable_warnings ! DROP TABLE IF EXISTS t1; --enable_warnings CREATE TABLE t1 ( cont_nr int(11) NOT NULL auto_increment, ver_nr int(11) NOT NULL default '0', --- 1,7 ---- --disable_warnings ! DROP TABLE IF EXISTS t1,t2; --enable_warnings CREATE TABLE t1 ( cont_nr int(11) NOT NULL auto_increment, ver_nr int(11) NOT NULL default '0', *************** *** 62,71 **** --- 62,213 ---- # This died because we used the field Kundentyp twice SELECT ELT(FIELD(kundentyp,'PP','PPA','PG','PGA','FK','FKA','FP','FPA','K','KA','V','VA',''), 'Privat (Private Nutzung)','Privat (Private Nutzung) Sitz im Ausland','Privat (geschaeftliche Nutzung)','Privat (geschaeftliche Nutzung) Sitz im Ausland','Firma (Kapitalgesellschaft)','Firma (Kapitalgesellschaft) Sitz im Ausland','Firma (Personengesellschaft)','Firma (Personengesellschaft) Sitz im Ausland','oeff. rechtl. Koerperschaft','oeff. rechtl. Koerperschaft Sitz im Ausland','Eingetragener Verein','Eingetragener Verein Sitz im Ausland','Typ unbekannt') AS Kundentyp ,kategorie FROM t1 WHERE hdl_nr < 2000000 AND kategorie IN ('Prepaid','Mobilfunk') AND st_klasse = 'Workflow' GROUP BY kundentyp ORDER BY kategorie; drop table t1; + + # + # Bug 27249: table.* as alias + # + # Test plan: + # -test for error with allias / success without alias + # -test T.* with/without other columns/subselect in select list + # apply various order of other select items (dependency on states in parser) + # apply various aliasing + # -test in subquery + # -test in insert/create-select + + create table t1 (a int, b int, c int); + insert into t1 values(1,2,3); + insert into t1 values(11,22,33); + + create table t2 (d int); + insert into t2 values(99); + + create table t3 (a1 int, b1 int, c1 int); + + # 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 of Bug 27249: table.* as alias + # + + drop table t1; + drop table t2; + drop table t3; # # test case for #570 # *** mysql-5.0.bk-orig/mysql-test/r/alias.result Fri Mar 23 10:21:56 2007 --- mysql-5.0.bk-wild_alias/mysql-test/r/alias.result Sat Mar 24 15:45:48 2007 *************** *** 1,6 **** ! DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( cont_nr int(11) NOT NULL auto_increment, ver_nr int(11) NOT NULL default '0', aufnr int(11) NOT NULL default '0', username varchar(50) NOT NULL default '', --- 1,6 ---- ! DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 ( cont_nr int(11) NOT NULL auto_increment, ver_nr int(11) NOT NULL default '0', aufnr int(11) NOT NULL default '0', username varchar(50) NOT NULL default '', *************** *** 59,68 **** --- 59,208 ---- Kundentyp kategorie Privat (Private Nutzung) Mobilfunk Warnings: Warning 1052 Column 'kundentyp' in group statement is ambiguous drop table t1; + create table t1 (a int, b int, c int); + insert into t1 values(1,2,3); + insert into t1 values(11,22,33); + create table t2 (d int); + insert into t2 values(99); + create table t3 (a1 int, b1 int, c1 int); + 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; + drop table t2; + drop table t3; CREATE TABLE t1 ( AUFNR varchar(12) NOT NULL default '', PLNFL varchar(6) NOT NULL default '', VORNR varchar(4) NOT NULL default '', xstatus_vor smallint(5) unsigned NOT NULL default '0'