List:Commits« Previous MessageNext Message »
From:mhansson Date:August 24 2007 1:40pm
Subject:bk commit into 5.0 tree (mhansson:1.2502) BUG#30234
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of martin. When martin does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-08-24 15:40:16+02:00, mhansson@stripped +4 -0
  Bug #30234: Unexpected behavior using DELETE with AS and USING
  
  DELETE FROM ... USING ... statements with the following type of 
  ambiguous aliasing gave unexpected results:
  DELETE FROM t1 AS alias USING t1, t2 AS alias WHERE t1.a = alias.a;
  This query would leave table t1 intact but delete rows from t2.
  Fixed by changing DELETE FROM ... USING syntax so that only alias 
  references (as opposed to alias declarations) may be used in FROM.

  BitKeeper/etc/ignore@stripped, 2007-08-24 15:40:14+02:00, mhansson@stripped +5 -0
    Added support-files/mysqld_multi.server tests/bug25714 cscope.in.out cscope.out cscope.po.out to the ignore list

  mysql-test/r/delete.result@stripped, 2007-08-24 15:40:14+02:00, mhansson@stripped +37 -0
    Bug#30234: Test Result

  mysql-test/t/delete.test@stripped, 2007-08-24 15:40:14+02:00, mhansson@stripped +44 -0
    Bug#30234: Test Case

  sql/sql_yacc.yy@stripped, 2007-08-24 15:40:14+02:00, mhansson@stripped +17 -2
    Bug#30234:
    - Added parser rule table_alias_ref_list that contains a list of table 
      aliases only.
    - Added parser rule table_alias_ref that sets the TL_OPTION_ALIAS in 
      order to turn off semantic checking that applies only for table names.

diff -Nrup a/BitKeeper/etc/ignore b/BitKeeper/etc/ignore
--- a/BitKeeper/etc/ignore	2007-06-20 18:13:51 +02:00
+++ b/BitKeeper/etc/ignore	2007-08-24 15:40:14 +02:00
@@ -1344,3 +1344,8 @@ zlib/*.vcproj
 debian/control
 debian/defs.mk
 include/abi_check
+support-files/mysqld_multi.server
+tests/bug25714
+cscope.in.out
+cscope.out
+cscope.po.out
diff -Nrup a/mysql-test/r/delete.result b/mysql-test/r/delete.result
--- a/mysql-test/r/delete.result	2007-02-23 17:49:33 +01:00
+++ b/mysql-test/r/delete.result	2007-08-24 15:40:14 +02:00
@@ -223,3 +223,40 @@ ERROR 42S22: Unknown column 't2.x' in 'o
 DELETE FROM t1 ORDER BY (SELECT x);
 ERROR 42S22: Unknown column 'x' in 'field list'
 DROP TABLE t1;
+CREATE TABLE t1 (
+a INT
+);
+CREATE TABLE t2 (
+a INT
+);
+CREATE DATABASE db1;
+CREATE TABLE db1.t1 (
+a INT
+);
+INSERT INTO db1.t1 (a) SELECT * FROM t1;
+CREATE DATABASE db2;
+CREATE TABLE db2.t1 (
+a INT
+);
+INSERT INTO db2.t1 (a) SELECT * FROM t2;
+DELETE FROM t1 alias USING t1, t2 alias WHERE t1.a = alias.a;
+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 'alias USING t1, t2 alias WHERE t1.a = alias.a' at line 1
+DELETE FROM alias USING t1, t2 alias WHERE t1.a = alias.a;
+DELETE FROM t1, alias USING t1, t2 alias WHERE t1.a = alias.a;
+DELETE FROM t1, t2 USING t1, t2 alias WHERE t1.a = alias.a;
+ERROR 42S02: Unknown table 't2' in MULTI DELETE
+DELETE FROM db1.t1 alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a;
+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 'alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a' at line 1
+DELETE FROM alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a;
+ERROR 42S02: Unknown table 'alias' in MULTI DELETE
+DELETE FROM db2.alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a;
+DELETE FROM t1 USING t1 WHERE a = 1;
+SELECT * FROM t1;
+a
+DELETE FROM t1 alias USING t1 alias WHERE a = 2;
+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 'alias USING t1 alias WHERE a = 2' at line 1
+SELECT * FROM t1;
+a
+DROP TABLE t1, t2;
+DROP DATABASE db1;
+DROP DATABASE db2;
diff -Nrup a/mysql-test/t/delete.test b/mysql-test/t/delete.test
--- a/mysql-test/t/delete.test	2007-02-23 17:49:34 +01:00
+++ b/mysql-test/t/delete.test	2007-08-24 15:40:14 +02:00
@@ -221,3 +221,47 @@ DELETE FROM t1 ORDER BY t2.x;
 DELETE FROM t1 ORDER BY (SELECT x);
 
 DROP TABLE t1;
+
+#
+# Bug #30234: Unexpected behavior using DELETE with AS and USING
+# '
+CREATE TABLE t1 (
+  a INT
+);
+
+CREATE TABLE t2 (
+  a INT
+);
+
+CREATE DATABASE db1;
+CREATE TABLE db1.t1 (
+  a INT
+);
+INSERT INTO db1.t1 (a) SELECT * FROM t1;
+
+CREATE DATABASE db2;
+CREATE TABLE db2.t1 (
+  a INT
+);
+INSERT INTO db2.t1 (a) SELECT * FROM t2;
+
+--error ER_PARSE_ERROR
+DELETE FROM t1 alias USING t1, t2 alias WHERE t1.a = alias.a;
+DELETE FROM alias USING t1, t2 alias WHERE t1.a = alias.a;
+DELETE FROM t1, alias USING t1, t2 alias WHERE t1.a = alias.a;
+--error ER_UNKNOWN_TABLE
+DELETE FROM t1, t2 USING t1, t2 alias WHERE t1.a = alias.a;
+--error ER_PARSE_ERROR
+DELETE FROM db1.t1 alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a;
+--error ER_UNKNOWN_TABLE
+DELETE FROM alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a;
+DELETE FROM db2.alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a;
+DELETE FROM t1 USING t1 WHERE a = 1;
+SELECT * FROM t1;
+--error ER_PARSE_ERROR
+DELETE FROM t1 alias USING t1 alias WHERE a = 2;
+SELECT * FROM t1;
+
+DROP TABLE t1, t2;
+DROP DATABASE db1;
+DROP DATABASE db2;
diff -Nrup a/sql/sql_yacc.yy b/sql/sql_yacc.yy
--- a/sql/sql_yacc.yy	2007-07-30 01:33:07 +02:00
+++ b/sql/sql_yacc.yy	2007-08-24 15:40:14 +02:00
@@ -1159,7 +1159,8 @@ bool my_yyoverflow(short **a, YYSTYPE **
 	field_opt_list opt_binary table_lock_list table_lock
 	ref_list opt_on_delete opt_on_delete_list opt_on_delete_item use
 	opt_delete_options opt_delete_option varchar nchar nvarchar
-	opt_outer table_list table_name opt_option opt_place
+	opt_outer table_list table_name table_alias_ref_list table_alias_ref
+	opt_option opt_place
 	opt_attribute opt_attribute_list attribute column_list column_list_id
 	opt_column_list grant_privileges grant_ident grant_list grant_option
 	object_privilege object_privilege_list user_list rename_list
@@ -6504,6 +6505,20 @@ table_name:
 	}
 	;
 
+table_alias_ref_list:
+        table_alias_ref
+        | table_alias_ref_list ',' table_alias_ref;
+
+table_alias_ref:
+	table_ident
+	{
+	  if (!Select->add_table_to_list(YYTHD, $1, NULL,
+                                         TL_OPTION_UPDATING | TL_OPTION_ALIAS,
+                                         Lex->lock_option ))
+	    MYSQL_YYABORT;
+	}
+	;
+
 if_exists:
 	/* empty */ { $$= 0; }
 	| IF EXISTS { $$= 1; }
@@ -6774,7 +6789,7 @@ single_multi:
             if (multi_delete_set_locks_and_link_aux_tables(Lex))
               MYSQL_YYABORT;
           }
-	| FROM table_wild_list
+	| FROM table_alias_ref_list
 	  { mysql_init_multi_delete(Lex); }
 	  USING join_table_list where_clause
           { 
Thread
bk commit into 5.0 tree (mhansson:1.2502) BUG#30234mhansson24 Aug
  • Re: bk commit into 5.0 tree (mhansson:1.2502) BUG#30234Marc Alff31 Aug