List:Commits« Previous MessageNext Message »
From:mhansson Date:August 15 2007 12:41pm
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-15 14:41:19+02:00, mhansson@stripped +4 -0
  Bug #30234: Unexpected behavior using DELETE with AS and USING
  
  DELETE statements with the following type of non-unique aliases
  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 prohibiting non-unique aliases.

  BitKeeper/etc/ignore@stripped, 2007-08-15 14:41:13+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-15 14:41:13+02:00, mhansson@stripped +48 -0
    Bug#30234: Test Result

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

  sql/sql_parse.cc@stripped, 2007-08-15 14:41:13+02:00, mhansson@stripped +34 -0
    Bug#30234: Added a check, for every table alias declaration in USING clause, 
    if FROM list contains alias declarations where this alias maps 
    to a different table, throw an error.

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-15 14:41:13 +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-15 14:41:13 +02:00
@@ -223,3 +223,51 @@ 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
+);
+INSERT INTO t1 (a) VALUES (1), (2);
+CREATE TABLE t2 (
+a INT
+);
+INSERT INTO t2 (a) VALUES (2), (3);
+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: Not unique table/alias: 'alias'
+DELETE FROM db1.t1 alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a;
+ERROR 42000: Not unique table/alias: 'alias'
+SELECT * FROM t1;
+a
+1
+2
+SELECT * FROM t2;
+a
+2
+3
+SELECT * FROM db1.t1;
+a
+1
+2
+SELECT * FROM db2.t1;
+a
+2
+3
+DELETE FROM t1 USING t1 WHERE a = 1;
+SELECT * FROM t1;
+a
+2
+DELETE FROM t1 alias USING t1 alias WHERE a = 2;
+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-15 14:41:13 +02:00
@@ -221,3 +221,45 @@ 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
+);
+INSERT INTO t1 (a) VALUES (1), (2);
+
+CREATE TABLE t2 (
+  a INT
+);
+INSERT INTO t2 (a) VALUES (2), (3);
+
+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_NONUNIQ_TABLE
+DELETE FROM t1 alias USING t1, t2 alias WHERE t1.a = alias.a;
+--error ER_NONUNIQ_TABLE
+DELETE FROM db1.t1 alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a;
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM db1.t1;
+SELECT * FROM db2.t1;
+DELETE FROM t1 USING t1 WHERE a = 1;
+SELECT * FROM t1;
+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_parse.cc b/sql/sql_parse.cc
--- a/sql/sql_parse.cc	2007-08-02 23:02:58 +02:00
+++ b/sql/sql_parse.cc	2007-08-15 14:41:13 +02:00
@@ -6338,6 +6338,18 @@ bool add_to_list(THD *thd, SQL_LIST &lis
 
 /*
   Add a table to list of used tables
+  
+  For a statement such as 
+  
+  DELETE FROM <tables> USING <tables> WHERE <condition>
+
+  A table alias may be declared in either the FROM clause, the USING clause,
+  or both, and can then be used freely in the other (and in the WHERE clause,
+  obviously). However, the same alias may only be declared in both clauses if
+  it refers to the same table, with the following exceptions:
+
+    - the aliases refer to the same table in both declarations.
+    - the alias is the same name as the table.
 
   SYNOPSIS
     add_table_to_list()
@@ -6457,6 +6469,28 @@ TABLE_LIST *st_select_lex::add_table_to_
       {
 	my_error(ER_NONUNIQ_TABLE, MYF(0), alias_str); /* purecov: tested */
 	DBUG_RETURN(0);				/* purecov: tested */
+      }
+    }
+    for (TABLE_LIST *tables= (TABLE_LIST*)thd->lex->auxiliary_table_list.first;
+	 tables;
+	 tables= tables->next_local)
+    {
+      /*
+        Check the validity of 'DELETE FROM <table-list1> USING <table-list2>
+        ...'.  If we come here <table-list1> is already filled in and is in
+        auxiliary_table_list, and we are considering adding a table from
+        <table-list2>. We forbid the case where alias and database are equal
+        if table names are different
+        that alias.
+      */
+      if (!my_strcasecmp(table_alias_charset, alias_str, tables->alias) &&
+          (strcmp(ptr->db, tables->db) ||
+           my_strcasecmp(table_alias_charset, table->table.str, 
+                         tables->table_name)) &&
+          my_strcasecmp(table_alias_charset, tables->table_name, tables->alias))
+      {
+        my_error(ER_NONUNIQ_TABLE, MYF(0), alias_str); /* purecov: tested */
+        DBUG_RETURN(0);				/* purecov: tested */
       }
     }
   }
Thread
bk commit into 5.0 tree (mhansson:1.2502) BUG#30234mhansson15 Aug