List:Commits« Previous MessageNext Message »
From:Davi Arnaut Date:February 27 2008 10:59pm
Subject:bk commit into 6.0 tree (davi:1.2575) BUG#27525
View as plain text  
Below is the list of changes that have just been committed into a local
6.0 repository of davi.  When davi 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, 2008-02-27 19:59:48-03:00, davi@stripped +9 -0
  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@stripped, 2008-02-27 19:59:43-03:00, davi@stripped +142 -1
    Add test case result for Bug#27525 and Bug#21148

  mysql-test/r/derived.result@stripped, 2008-02-27 19:59:43-03:00, davi@stripped +1 -1
    Update error.

  mysql-test/suite/rpl/r/rpl_multi_delete2.result@stripped, 2008-02-27 19:59:43-03:00, davi@stripped +1 -1
    Update syntax.

  mysql-test/suite/rpl/t/rpl_multi_delete2.test@stripped, 2008-02-27 19:59:43-03:00, davi@stripped +1 -1
    Update syntax.

  mysql-test/t/delete.test@stripped, 2008-02-27 19:59:43-03:00, davi@stripped +154 -1
    Add test case for Bug#27525 and Bug#21148

  mysql-test/t/derived.test@stripped, 2008-02-27 19:59:43-03:00, davi@stripped +1 -1
    Update statement error, alias is properly resolved now.

  sql/sql_parse.cc@stripped, 2008-02-27 19:59:43-03:00, davi@stripped +62 -12
    Implement new algorithm for the resolution of alias in
    a multiple table delete statement.

  sql/sql_yacc.yy@stripped, 2008-02-27 19:59:44-03:00, davi@stripped +10 -10
    Rework multi-delete parser rules to not accept table alias
    for the table source list.

  sql/table.h@stripped, 2008-02-27 19:59:44-03:00, davi@stripped +6 -0
    Add flag to signal that the table has a alias set or
    that fully qualified table name was given.

diff -Nrup a/mysql-test/r/delete.result b/mysql-test/r/delete.result
--- a/mysql-test/r/delete.result	2007-12-07 13:40:45 -02:00
+++ b/mysql-test/r/delete.result	2008-02-27 19:59:43 -03:00
@@ -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,3 +279,144 @@ 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;
diff -Nrup a/mysql-test/r/derived.result b/mysql-test/r/derived.result
--- a/mysql-test/r/derived.result	2007-10-29 12:37:16 -02:00
+++ b/mysql-test/r/derived.result	2008-02-27 19:59:43 -03:00
@@ -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;
diff -Nrup a/mysql-test/suite/rpl/r/rpl_multi_delete2.result b/mysql-test/suite/rpl/r/rpl_multi_delete2.result
--- a/mysql-test/suite/rpl/r/rpl_multi_delete2.result	2007-06-27 09:27:26 -03:00
+++ b/mysql-test/suite/rpl/r/rpl_multi_delete2.result	2008-02-27 19:59:43 -03:00
@@ -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
diff -Nrup a/mysql-test/suite/rpl/t/rpl_multi_delete2.test b/mysql-test/suite/rpl/t/rpl_multi_delete2.test
--- a/mysql-test/suite/rpl/t/rpl_multi_delete2.test	2007-06-27 09:27:32 -03:00
+++ b/mysql-test/suite/rpl/t/rpl_multi_delete2.test	2008-02-27 19:59:43 -03:00
@@ -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;
diff -Nrup a/mysql-test/t/delete.test b/mysql-test/t/delete.test
--- a/mysql-test/t/delete.test	2007-12-07 13:40:45 -02:00
+++ b/mysql-test/t/delete.test	2008-02-27 19:59:43 -03:00
@@ -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;
@@ -292,3 +292,156 @@ DROP TABLE t1;
 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;
diff -Nrup a/mysql-test/t/derived.test b/mysql-test/t/derived.test
--- a/mysql-test/t/derived.test	2007-10-29 08:58:08 -02:00
+++ b/mysql-test/t/derived.test	2008-02-27 19:59:43 -03:00
@@ -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;
diff -Nrup a/sql/sql_parse.cc b/sql/sql_parse.cc
--- a/sql/sql_parse.cc	2008-02-26 13:54:31 -03:00
+++ b/sql/sql_parse.cc	2008-02-27 19:59:43 -03:00
@@ -5880,13 +5880,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;
@@ -6889,6 +6893,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.
@@ -6914,20 +6975,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;
diff -Nrup a/sql/sql_yacc.yy b/sql/sql_yacc.yy
--- a/sql/sql_yacc.yy	2008-02-26 13:45:26 -03:00
+++ b/sql/sql_yacc.yy	2008-02-27 19:59:44 -03:00
@@ -1345,7 +1345,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
 %%
 
 /*
@@ -9167,7 +9167,7 @@ delete:
             lex->ignore= 0;
             lex->select_lex.init_order();
           }
-          opt_delete_options single_multi {}
+          opt_delete_options single_multi
         ;
 
 single_multi:
@@ -9182,38 +9182,38 @@ 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
           {
             if (!Select->add_table_to_list(YYTHD, new Table_ident($1),
-                                           $3,
+                                           NULL,
                                            TL_OPTION_UPDATING | TL_OPTION_ALIAS,
                                            Lex->lock_option))
               MYSQL_YYABORT;
           }
-        | ident '.' ident opt_wild opt_table_alias
+        | ident '.' ident opt_wild
           {
             if (!Select->add_table_to_list(YYTHD,
                                            new Table_ident(YYTHD, $1, $3, 0),
-                                           $5, 
+                                           NULL,
                                            TL_OPTION_UPDATING | TL_OPTION_ALIAS,
                                            Lex->lock_option))
               MYSQL_YYABORT;
diff -Nrup a/sql/table.h b/sql/table.h
--- a/sql/table.h	2008-02-26 13:45:29 -03:00
+++ b/sql/table.h	2008-02-27 19:59:44 -03:00
@@ -1117,6 +1117,12 @@ struct TABLE_LIST
   int           lock_timeout;           /* NOWAIT or WAIT [X]               */
   bool          lock_transactional;     /* If transactional lock requested. */
   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. */
Thread
bk commit into 6.0 tree (davi:1.2575) BUG#27525Davi Arnaut28 Feb