List:Commits« Previous MessageNext Message »
From:Davi Arnaut Date:November 10 2009 6:48pm
Subject:bzr commit into mysql-5.5-next-mr-runtime branch (davi:2919) Bug#27525
Bug#30234
View as plain text  
# At a local mysql-5.5-next-mr-runtime repository of davi

 2919 Davi Arnaut	2009-11-10
      Backport of Bug#27525 to mysql-next-mr
      ------------------------------------------------------------
      revno: 2572.2.1
      revision-id: sp1r-davi@stripped/endora.local-20080227225948-16317
      parent: sp1r-anozdrin/alik@stripped
      committer: davi@stripped/endora.local
      timestamp: Wed 2008-02-27 19:59:48 -0300
      message:
        Bug#27525 table not found when using multi-table-deletes with aliases over several databas
        Bug#30234 Unexpected behavior using DELETE with AS and USING
      
        The multi-delete statement has a documented limitation that
        cross-database multiple-table deletes using aliases are not
        supported because it fails to find the tables by alias if it
        belongs to a different database. The problem is that when
        building the list of tables to delete from, if a database
        name is not specified (maybe an alias) it defaults to the
        name of the current selected database, making impossible to
        to properly resolve tables by alias later. Another problem
        is a inconsistency of the multiple table delete syntax that
        permits ambiguities in a delete statement (aliases that refer
        to multiple different tables or vice-versa).
      
        The first step for a solution and proper implementation of
        the cross-databse multiple table delete is to get rid of any
        ambiguities in a multiple table statement. Currently, the parser
        is accepting multiple table delete statements that have no obvious
        meaning, such as:
      
        DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1;
        DELETE a1 AS a1 FROM db1.t1 AS a1, db2.t2 AS a1;
      
        The solution is to resolve the left part of a delete statement
        using the right part, if the a table on right has an alias,
        it must be referenced in the left using the given alias. Also,
        each table on the left side must match unambiguously only one
        table in the right side.
     @ mysql-test/r/delete.result
        Add test case result for Bug#27525 and Bug#21148
     @ mysql-test/r/derived.result
        Update error.
     @ mysql-test/suite/rpl/r/rpl_multi_delete2.result
        Update syntax.
     @ mysql-test/suite/rpl/t/rpl_multi_delete2.test
        Update syntax.
     @ mysql-test/t/delete.test
        Add test case for Bug#27525 and Bug#21148
     @ mysql-test/t/derived.test
        Update statement error, alias is properly resolved now.
     @ sql/sql_parse.cc
        Implement new algorithm for the resolution of alias in
        a multiple table delete statement.
     @ sql/sql_yacc.yy
        Rework multi-delete parser rules to not accept table alias
        for the table source list.
     @ sql/table.h
        Add flag to signal that the table has a alias set or
        that fully qualified table name was given.

    modified:
      mysql-test/r/delete.result
      mysql-test/r/derived.result
      mysql-test/suite/rpl/r/rpl_multi_delete2.result
      mysql-test/suite/rpl/t/rpl_multi_delete2.test
      mysql-test/t/delete.test
      mysql-test/t/derived.test
      sql/sql_parse.cc
      sql/sql_yacc.yy
      sql/table.h
=== modified file 'mysql-test/r/delete.result'
--- a/mysql-test/r/delete.result	2009-09-28 10:48:52 +0000
+++ b/mysql-test/r/delete.result	2009-11-10 18:48:46 +0000
@@ -259,8 +259,8 @@ ERROR 42S02: Unknown table 't2' in MULTI
 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;
+ERROR 42S02: Unknown table 'alias' in MULTI DELETE
 DELETE FROM t1 USING t1 WHERE a = 1;
 SELECT * FROM t1;
 a
@@ -279,6 +279,147 @@ ERROR 42000: Incorrect number of argumen
 DROP TABLE t1;
 DROP FUNCTION f1;
 End of 5.0 tests
+DROP DATABASE IF EXISTS db1;
+DROP DATABASE IF EXISTS db2;
+DROP DATABASE IF EXISTS db3;
+DROP DATABASE IF EXISTS db4;
+DROP TABLE IF EXISTS t1, t2;
+DROP PROCEDURE IF EXISTS count;
+USE test;
+CREATE DATABASE db1;
+CREATE DATABASE db2;
+CREATE TABLE db1.t1 (a INT, b INT);
+INSERT INTO db1.t1 VALUES (1,1),(2,2),(3,3);
+CREATE TABLE db1.t2 AS SELECT * FROM db1.t1;
+CREATE TABLE db2.t1 AS SELECT * FROM db1.t2;
+CREATE TABLE db2.t2 AS SELECT * FROM db2.t1;
+CREATE TABLE t1 AS SELECT * FROM db2.t2;
+CREATE TABLE t2 AS SELECT * FROM t1;
+CREATE PROCEDURE count_rows()
+BEGIN
+SELECT COUNT(*) AS "COUNT(db1.t1)" FROM db1.t1;
+SELECT COUNT(*) AS "COUNT(db1.t2)" FROM db1.t2;
+SELECT COUNT(*) AS "COUNT(db2.t1)" FROM db2.t1;
+SELECT COUNT(*) AS "COUNT(db2.t2)" FROM db2.t2;
+SELECT COUNT(*) AS "COUNT(test.t1)" FROM test.t1;
+SELECT COUNT(*) AS "COUNT(test.t2)" FROM test.t2;
+END|
+CREATE DATABASE db3;
+USE db3;
+DROP DATABASE db3;
+SELECT * FROM t1;
+ERROR 3D000: No database selected
+DELETE a1,a2 FROM db1.t1, db2.t2;
+ERROR 3D000: No database selected
+DELETE a1,a2 FROM db1.t1, db2.t2;
+ERROR 3D000: No database selected
+DELETE a1,a2 FROM db1.t1 AS a1, db2.t2;
+ERROR 3D000: No database selected
+DELETE a1,a2 FROM db1.t1, db2.t2 AS a2;
+ERROR 3D000: No database selected
+DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2;
+ERROR 3D000: No database selected
+DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2;
+ERROR 3D000: No database selected
+DELETE FROM a1,a2 USING db1.t1, db2.t2;
+ERROR 3D000: No database selected
+DELETE FROM a1,a2 USING db1.t1, db2.t2;
+ERROR 3D000: No database selected
+DELETE FROM a1,a2 USING db1.t1 AS a1, db2.t2;
+ERROR 3D000: No database selected
+DELETE FROM a1,a2 USING db1.t1, db2.t2 AS a2;
+ERROR 3D000: No database selected
+DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2;
+ERROR 3D000: No database selected
+DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2;
+ERROR 3D000: No database selected
+DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1;
+ERROR 3D000: No database selected
+DELETE a1 FROM db1.a1, db2.t2 AS a1;
+ERROR 3D000: No database selected
+DELETE a1 FROM a1, db1.t1 AS a1;
+ERROR 3D000: No database selected
+DELETE t1 FROM db1.t1, db2.t1 AS a1;
+ERROR 3D000: No database selected
+DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2;
+ERROR 3D000: No database selected
+DELETE t1 FROM db1.t1, db2.t1;
+ERROR 3D000: No database selected
+USE test;
+DELETE a1,a2 FROM db1.t1, db2.t2;
+ERROR 42S02: Unknown table 'a1' in MULTI DELETE
+DELETE a1,a2 FROM db1.t1, db2.t2;
+ERROR 42S02: Unknown table 'a1' in MULTI DELETE
+DELETE a1,a2 FROM db1.t1 AS a1, db2.t2;
+ERROR 42S02: Unknown table 'a2' in MULTI DELETE
+DELETE a1,a2 FROM db1.t1, db2.t2 AS a2;
+ERROR 42S02: Unknown table 'a1' in MULTI DELETE
+DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2;
+ERROR 42S02: Table 'db3.t1' doesn't exist
+DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2;
+ERROR 42S02: Table 'db3.t1' doesn't exist
+DELETE FROM a1,a2 USING db1.t1, db2.t2;
+ERROR 42S02: Unknown table 'a1' in MULTI DELETE
+DELETE FROM a1,a2 USING db1.t1, db2.t2;
+ERROR 42S02: Unknown table 'a1' in MULTI DELETE
+DELETE FROM a1,a2 USING db1.t1 AS a1, db2.t2;
+ERROR 42S02: Unknown table 'a2' in MULTI DELETE
+DELETE FROM a1,a2 USING db1.t1, db2.t2 AS a2;
+ERROR 42S02: Unknown table 'a1' in MULTI DELETE
+DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2;
+ERROR 42S02: Table 'db3.t1' doesn't exist
+DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2;
+ERROR 42S02: Table 'db3.t1' doesn't exist
+DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1;
+ERROR 42000: Not unique table/alias: 'a1'
+DELETE a1 FROM db1.a1, db2.t2 AS a1;
+ERROR 42S02: Table 'db1.a1' doesn't exist
+DELETE a1 FROM a1, db1.t1 AS a1;
+ERROR 42000: Not unique table/alias: 'a1'
+DELETE t1 FROM db1.t1, db2.t1 AS a1;
+ERROR 42S02: Unknown table 't1' in MULTI DELETE
+DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2;
+ERROR 42S02: Unknown table 't1' in MULTI DELETE
+DELETE t1 FROM db1.t1, db2.t1;
+ERROR 42S02: Unknown table 't1' in MULTI DELETE
+DELETE t1 FROM db1.t2 AS t1, db2.t2 AS t2 WHERE t2.a = 1 AND t1.a = t2.a;
+SELECT ROW_COUNT();
+ROW_COUNT()
+1
+CALL count_rows();
+COUNT(db1.t1)
+3
+COUNT(db1.t2)
+2
+COUNT(db2.t1)
+3
+COUNT(db2.t2)
+3
+COUNT(test.t1)
+3
+COUNT(test.t2)
+3
+DELETE a1, a2 FROM db2.t1 AS a1, t2 AS a2 WHERE a1.a = 2 AND a2.a = 2;
+SELECT ROW_COUNT();
+ROW_COUNT()
+2
+CALL count_rows();
+COUNT(db1.t1)
+3
+COUNT(db1.t2)
+2
+COUNT(db2.t1)
+2
+COUNT(db2.t2)
+3
+COUNT(test.t1)
+3
+COUNT(test.t2)
+2
+DROP DATABASE db1;
+DROP DATABASE db2;
+DROP PROCEDURE count_rows;
+DROP TABLE t1, t2;
 #
 # Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger, 
 # merge table

=== modified file 'mysql-test/r/derived.result'
--- a/mysql-test/r/derived.result	2009-07-11 18:44:29 +0000
+++ b/mysql-test/r/derived.result	2009-11-10 18:48:46 +0000
@@ -277,7 +277,7 @@ select * from t1;
 N	M
 3	0
 delete P1.*,p2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS p2 ON P1.N = p2.N;
-ERROR 42S02: Unknown table 'p2' in MULTI DELETE
+ERROR HY000: The target table p2 of the DELETE is not updatable
 delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
 ERROR 42S22: Unknown column 'aaa' in 'field list'
 drop table t1;

=== modified file 'mysql-test/suite/rpl/r/rpl_multi_delete2.result'
--- a/mysql-test/suite/rpl/r/rpl_multi_delete2.result	2007-06-27 12:28:02 +0000
+++ b/mysql-test/suite/rpl/r/rpl_multi_delete2.result	2009-11-10 18:48:46 +0000
@@ -16,7 +16,7 @@ DELETE alias FROM a alias WHERE alias.i=
 SELECT * FROM a;
 i
 insert into a values(2),(3);
-delete a alias FROM a alias where alias.i=2;
+delete alias FROM a alias where alias.i=2;
 select * from a;
 i
 3

=== modified file 'mysql-test/suite/rpl/t/rpl_multi_delete2.test'
--- a/mysql-test/suite/rpl/t/rpl_multi_delete2.test	2007-06-27 12:28:02 +0000
+++ b/mysql-test/suite/rpl/t/rpl_multi_delete2.test	2009-11-10 18:48:46 +0000
@@ -25,7 +25,7 @@ INSERT INTO a VALUES(1);
 DELETE alias FROM a alias WHERE alias.i=1;
 SELECT * FROM a;
 insert into a values(2),(3);
-delete a alias FROM a alias where alias.i=2;
+delete alias FROM a alias where alias.i=2;
 select * from a;
 save_master_pos;
 connection slave;

=== modified file 'mysql-test/t/delete.test'
--- a/mysql-test/t/delete.test	2009-09-28 10:48:52 +0000
+++ b/mysql-test/t/delete.test	2009-11-10 18:48:46 +0000
@@ -265,8 +265,8 @@ DELETE FROM t1, alias USING t1, t2 alias
 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;
+--error ER_UNKNOWN_TABLE
 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;
@@ -293,6 +293,159 @@ DROP FUNCTION f1;
 
 --echo End of 5.0 tests
 
+#
+# Bug#27525: table not found when using multi-table-deletes with aliases over
+#            several databas
+# Bug#21148: MULTI-DELETE fails to resolve a table by alias if it's from a
+#            different database
+#
+
+--disable_warnings
+DROP DATABASE IF EXISTS db1;
+DROP DATABASE IF EXISTS db2;
+DROP DATABASE IF EXISTS db3;
+DROP DATABASE IF EXISTS db4;
+DROP TABLE IF EXISTS t1, t2;
+DROP PROCEDURE IF EXISTS count;
+--enable_warnings
+USE test;
+CREATE DATABASE db1;
+CREATE DATABASE db2;
+
+CREATE TABLE db1.t1 (a INT, b INT);
+INSERT INTO db1.t1 VALUES (1,1),(2,2),(3,3);
+CREATE TABLE db1.t2 AS SELECT * FROM db1.t1;
+CREATE TABLE db2.t1 AS SELECT * FROM db1.t2;
+CREATE TABLE db2.t2 AS SELECT * FROM db2.t1;
+CREATE TABLE t1 AS SELECT * FROM db2.t2;
+CREATE TABLE t2 AS SELECT * FROM t1;
+
+delimiter |;
+CREATE PROCEDURE count_rows()
+BEGIN
+  SELECT COUNT(*) AS "COUNT(db1.t1)" FROM db1.t1;
+  SELECT COUNT(*) AS "COUNT(db1.t2)" FROM db1.t2;
+  SELECT COUNT(*) AS "COUNT(db2.t1)" FROM db2.t1;
+  SELECT COUNT(*) AS "COUNT(db2.t2)" FROM db2.t2;
+  SELECT COUNT(*) AS "COUNT(test.t1)" FROM test.t1;
+  SELECT COUNT(*) AS "COUNT(test.t2)" FROM test.t2;
+END|
+delimiter ;|
+
+#
+# Testing without a selected database
+#
+
+CREATE DATABASE db3;
+USE db3;
+DROP DATABASE db3;
+--error ER_NO_DB_ERROR
+SELECT * FROM t1;
+
+# Detect missing table references
+
+--error ER_NO_DB_ERROR
+DELETE a1,a2 FROM db1.t1, db2.t2;
+--error ER_NO_DB_ERROR
+DELETE a1,a2 FROM db1.t1, db2.t2;
+--error ER_NO_DB_ERROR
+DELETE a1,a2 FROM db1.t1 AS a1, db2.t2;
+--error ER_NO_DB_ERROR
+DELETE a1,a2 FROM db1.t1, db2.t2 AS a2;
+--error ER_NO_DB_ERROR
+DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2;
+--error ER_NO_DB_ERROR
+DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2;
+
+--error ER_NO_DB_ERROR
+DELETE FROM a1,a2 USING db1.t1, db2.t2;
+--error ER_NO_DB_ERROR
+DELETE FROM a1,a2 USING db1.t1, db2.t2;
+--error ER_NO_DB_ERROR
+DELETE FROM a1,a2 USING db1.t1 AS a1, db2.t2;
+--error ER_NO_DB_ERROR
+DELETE FROM a1,a2 USING db1.t1, db2.t2 AS a2;
+--error ER_NO_DB_ERROR
+DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2;
+--error ER_NO_DB_ERROR
+DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2;
+
+# Ambiguous table references
+
+--error ER_NO_DB_ERROR
+DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1;
+--error ER_NO_DB_ERROR
+DELETE a1 FROM db1.a1, db2.t2 AS a1;
+--error ER_NO_DB_ERROR
+DELETE a1 FROM a1, db1.t1 AS a1;
+--error ER_NO_DB_ERROR
+DELETE t1 FROM db1.t1, db2.t1 AS a1;
+--error ER_NO_DB_ERROR
+DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2;
+--error ER_NO_DB_ERROR
+DELETE t1 FROM db1.t1, db2.t1;
+
+# Test all again, now with a selected database
+
+USE test;
+
+# Detect missing table references
+
+--error ER_UNKNOWN_TABLE
+DELETE a1,a2 FROM db1.t1, db2.t2;
+--error ER_UNKNOWN_TABLE
+DELETE a1,a2 FROM db1.t1, db2.t2;
+--error ER_UNKNOWN_TABLE
+DELETE a1,a2 FROM db1.t1 AS a1, db2.t2;
+--error ER_UNKNOWN_TABLE
+DELETE a1,a2 FROM db1.t1, db2.t2 AS a2;
+--error ER_NO_SUCH_TABLE
+DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2;
+--error ER_NO_SUCH_TABLE
+DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2;
+
+--error ER_UNKNOWN_TABLE
+DELETE FROM a1,a2 USING db1.t1, db2.t2;
+--error ER_UNKNOWN_TABLE
+DELETE FROM a1,a2 USING db1.t1, db2.t2;
+--error ER_UNKNOWN_TABLE
+DELETE FROM a1,a2 USING db1.t1 AS a1, db2.t2;
+--error ER_UNKNOWN_TABLE
+DELETE FROM a1,a2 USING db1.t1, db2.t2 AS a2;
+--error ER_NO_SUCH_TABLE
+DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2;
+--error ER_NO_SUCH_TABLE
+DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2;
+
+# Ambiguous table references
+
+--error ER_NONUNIQ_TABLE
+DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1;
+--error ER_NO_SUCH_TABLE
+DELETE a1 FROM db1.a1, db2.t2 AS a1;
+--error ER_NONUNIQ_TABLE
+DELETE a1 FROM a1, db1.t1 AS a1;
+--error ER_UNKNOWN_TABLE
+DELETE t1 FROM db1.t1, db2.t1 AS a1;
+--error ER_UNKNOWN_TABLE
+DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2;
+--error ER_UNKNOWN_TABLE
+DELETE t1 FROM db1.t1, db2.t1;
+
+# Test multiple-table cross database deletes
+
+DELETE t1 FROM db1.t2 AS t1, db2.t2 AS t2 WHERE t2.a = 1 AND t1.a = t2.a;
+SELECT ROW_COUNT();
+CALL count_rows();
+DELETE a1, a2 FROM db2.t1 AS a1, t2 AS a2 WHERE a1.a = 2 AND a2.a = 2;
+SELECT ROW_COUNT();
+CALL count_rows();
+
+DROP DATABASE db1;
+DROP DATABASE db2;
+DROP PROCEDURE count_rows;
+DROP TABLE t1, t2;
+
 --echo #
 --echo # Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger, 
 --echo # merge table

=== modified file 'mysql-test/t/derived.test'
--- a/mysql-test/t/derived.test	2009-07-11 18:44:29 +0000
+++ b/mysql-test/t/derived.test	2009-11-10 18:48:46 +0000
@@ -158,7 +158,7 @@ UPDATE `t1` AS P1 INNER JOIN (SELECT aaa
 delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
 select * from t1;
 --replace_result P2 p2
---error ER_UNKNOWN_TABLE 
+--error ER_NON_UPDATABLE_TABLE
 delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
 -- error 1054
 delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2009-11-10 18:11:27 +0000
+++ b/sql/sql_parse.cc	2009-11-10 18:48:46 +0000
@@ -6486,13 +6486,17 @@ TABLE_LIST *st_select_lex::add_table_to_
     DBUG_RETURN(0);				/* purecov: inspected */
   if (table->db.str)
   {
+    ptr->is_fqtn= TRUE;
     ptr->db= table->db.str;
     ptr->db_length= table->db.length;
   }
   else if (lex->copy_db_to(&ptr->db, &ptr->db_length))
     DBUG_RETURN(0);
+  else
+    ptr->is_fqtn= FALSE;
 
   ptr->alias= alias_str;
+  ptr->is_alias= alias ? TRUE : FALSE;
   if (lower_case_table_names && table->table.length)
     table->table.length= my_casedn_str(files_charset_info, table->table.str);
   ptr->table_name=table->table.str;
@@ -7539,6 +7543,63 @@ bool multi_delete_precheck(THD *thd, TAB
 }
 
 
+/*
+  Given a table in the source list, find a correspondent table in the
+  table references list.
+
+  @param lex Pointer to LEX representing multi-delete.
+  @param src Source table to match.
+  @param ref Table references list.
+
+  @remark The source table list (tables listed before the FROM clause
+  or tables listed in the FROM clause before the USING clause) may
+  contain table names or aliases that must match unambiguously one,
+  and only one, table in the target table list (table references list,
+  after FROM/USING clause).
+
+  @return Matching table, NULL otherwise.
+*/
+
+static TABLE_LIST *multi_delete_table_match(LEX *lex, TABLE_LIST *tbl,
+                                            TABLE_LIST *tables)
+{
+  TABLE_LIST *match= NULL;
+  DBUG_ENTER("multi_delete_table_match");
+
+  for (TABLE_LIST *elem= tables; elem; elem= elem->next_local)
+  {
+    int cmp;
+
+    if (tbl->is_fqtn && elem->is_alias)
+      continue; /* no match */
+    if (tbl->is_fqtn && elem->is_fqtn)
+      cmp= my_strcasecmp(table_alias_charset, tbl->table_name, elem->table_name) ||
+           strcmp(tbl->db, elem->db);
+    else if (elem->is_alias)
+      cmp= my_strcasecmp(table_alias_charset, tbl->alias, elem->alias);
+    else
+      cmp= my_strcasecmp(table_alias_charset, tbl->table_name, elem->table_name) ||
+           strcmp(tbl->db, elem->db);
+
+    if (cmp)
+      continue;
+
+    if (match)
+    {
+      my_error(ER_NONUNIQ_TABLE, MYF(0), elem->alias);
+      DBUG_RETURN(NULL);
+    }
+
+    match= elem;
+  }
+
+  if (!match)
+    my_error(ER_UNKNOWN_TABLE, MYF(0), tbl->table_name, "MULTI DELETE");
+
+  DBUG_RETURN(match);
+}
+
+
 /**
   Link tables in auxilary table list of multi-delete with corresponding
   elements in main table list, and set proper locks for them.
@@ -7564,20 +7625,9 @@ bool multi_delete_set_locks_and_link_aux
   {
     lex->table_count++;
     /* All tables in aux_tables must be found in FROM PART */
-    TABLE_LIST *walk;
-    for (walk= tables; walk; walk= walk->next_local)
-    {
-      if (!my_strcasecmp(table_alias_charset,
-			 target_tbl->alias, walk->alias) &&
-	  !strcmp(walk->db, target_tbl->db))
-	break;
-    }
+    TABLE_LIST *walk= multi_delete_table_match(lex, target_tbl, tables);
     if (!walk)
-    {
-      my_error(ER_UNKNOWN_TABLE, MYF(0),
-               target_tbl->table_name, "MULTI DELETE");
       DBUG_RETURN(TRUE);
-    }
     if (!walk->derived)
     {
       target_tbl->table_name= walk->table_name;

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2009-11-02 11:10:04 +0000
+++ b/sql/sql_yacc.yy	2009-11-10 18:48:46 +0000
@@ -1352,7 +1352,7 @@ END_OF_INPUT
 %type <NONE>
         '-' '+' '*' '/' '%' '(' ')'
         ',' '!' '{' '}' '&' '|' AND_SYM OR_SYM OR_OR_SYM BETWEEN_SYM CASE_SYM
-        THEN_SYM WHEN_SYM DIV_SYM MOD_SYM OR2_SYM AND_AND_SYM
+        THEN_SYM WHEN_SYM DIV_SYM MOD_SYM OR2_SYM AND_AND_SYM DELETE_SYM
 %%
 
 /*
@@ -9994,7 +9994,7 @@ delete:
             lex->ignore= 0;
             lex->select_lex.init_order();
           }
-          opt_delete_options single_multi {}
+          opt_delete_options single_multi
         ;
 
 single_multi:
@@ -10009,45 +10009,45 @@ single_multi:
         | table_wild_list
           { mysql_init_multi_delete(Lex); }
           FROM join_table_list where_clause
-          { 
+          {
             if (multi_delete_set_locks_and_link_aux_tables(Lex))
               MYSQL_YYABORT;
           }
         | FROM table_alias_ref_list
           { mysql_init_multi_delete(Lex); }
           USING join_table_list where_clause
-          { 
+          {
             if (multi_delete_set_locks_and_link_aux_tables(Lex))
               MYSQL_YYABORT;
           }
         ;
 
 table_wild_list:
-          table_wild_one {}
-        | table_wild_list ',' table_wild_one {}
+          table_wild_one
+        | table_wild_list ',' table_wild_one
         ;
 
 table_wild_one:
-          ident opt_wild opt_table_alias
+          ident opt_wild
           {
             Table_ident *ti= new Table_ident($1);
             if (ti == NULL)
               MYSQL_YYABORT;
             if (!Select->add_table_to_list(YYTHD,
                                            ti,
-                                           $3,
+                                           NULL,
                                            TL_OPTION_UPDATING | TL_OPTION_ALIAS,
                                            Lex->lock_option))
               MYSQL_YYABORT;
           }
-        | ident '.' ident opt_wild opt_table_alias
+        | ident '.' ident opt_wild
           {
             Table_ident *ti= new Table_ident(YYTHD, $1, $3, 0);
             if (ti == NULL)
               MYSQL_YYABORT;
             if (!Select->add_table_to_list(YYTHD,
                                            ti,
-                                           $5, 
+                                           NULL,
                                            TL_OPTION_UPDATING | TL_OPTION_ALIAS,
                                            Lex->lock_option))
               MYSQL_YYABORT;

=== modified file 'sql/table.h'
--- a/sql/table.h	2009-11-02 11:10:04 +0000
+++ b/sql/table.h	2009-11-10 18:48:46 +0000
@@ -1332,6 +1332,12 @@ struct TABLE_LIST
   */
   bool          create;
   bool          internal_tmp_table;
+  /** TRUE if an alias for this table was specified in the SQL. */
+  bool          is_alias;
+  /** TRUE if the table is referred to in the statement using a fully
+      qualified name (<db_name>.<table_name>).
+  */
+  bool          is_fqtn;
 
 
   /* View creation context. */


Attachment: [text/bzr-bundle] bzr/davi.arnaut@sun.com-20091110184846-fogbt0pzci98fxgu.bundle
Thread
bzr commit into mysql-5.5-next-mr-runtime branch (davi:2919) Bug#27525Bug#30234Davi Arnaut10 Nov