From: Date: December 19 2007 8:30pm Subject: bk commit into 6.0 tree (davi:1.2762) BUG#27525 List-Archive: http://lists.mysql.com/commits/40229 X-Bug: 27525 Message-Id: <20071219193037.7523742C94A@endora.local> 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, 2007-12-19 17:30:30-02:00, davi@stripped +11 -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; or that can change depending on the selected database. The syntax of the multiple table delete statement is: DELETE [..] tbl_name[.*] [, tbl_name[.*]] FROM table_references DELETE [..] FROM tbl_name[.*] [, tbl_name[.*]] USING table_references The first take at this syntax is to disallow the specification of aliases in the tbl_name[.*] part (no more DELETE a1 AS a1 FROM ..) and only allow it in the table_references part. Even this way there is still ambiguities such as: DELETE t1 FROM t2 AS t1, t3 AS t1; DELETE t1 FROM t1, t1 AS t2; DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2; To properly detect/resolve ambiguities the multiple table delete statement uses the following (very pseudo) algorithm: 1. FOR EACH table in the tbl_name[.*] name part DO: 2. IF table is fully qualified table name (db.table) DO 3. SCAN table_references FOR one exact (db.table) match. 4. IF match not found OR more than one match, ERROR. 5. ELSE OK. This first part is intended to deal with tables that are not in the currently selected database and to detect ambiguities such as: # We must have only one correspondent table! DELETE db1.t1 FROM db1.t1, t1 AS t2, db1.t1 AS t4; 6. ELSE try alias match 7. SCAN table_references FOR one and only one alias match. This is intended to match tables with a explicit alias (table_name AS alias) in the table references list. 8. IF match not found, GOTO 11 9. OR more than one match, ERROR. 10. ELSE OK. It must be unique to avoid case where a single alias might refer to different tables, such as: DELETE t1 FROM db1.t1 AS t1, db2.t3 AS t1; 11. SCAN table_references FOR one and only one name match. Intended to match table names (without alias) in the table references: DELETE t1 FROM t2, t1; 12. IF match found, OK. OR more than one match, ERROR. 13. IF match not found: 14. Append selected database to table name. 15. GOTO 3. This is intended for cases where there is a table name in the tbl_name part but in the table_references part it's a FQTN: DELETE t1 FROM db1.t1, t2; This is possible because tables without explicit schema name are marked so that during the alias resolving phase the schema name is set to the one of the corresponding table in the table references list. Duplicate bug reports: 23413, 26370 and 21148. mysql-test/r/delete.result@stripped, 2007-12-19 17:30:27-02:00, davi@stripped +159 -2 Add test case result for Bug#27525 and Bug#21148 mysql-test/r/derived.result@stripped, 2007-12-19 17:30:27-02:00, davi@stripped +1 -1 Update error. mysql-test/r/multi_update.result@stripped, 2007-12-19 17:30:27-02:00, davi@stripped +0 -1 Remove error. mysql-test/suite/rpl/r/rpl_multi_delete2.result@stripped, 2007-12-19 17:30:27-02:00, davi@stripped +1 -1 Update syntax. mysql-test/suite/rpl/t/rpl_multi_delete2.test@stripped, 2007-12-19 17:30:27-02:00, davi@stripped +1 -1 Update syntax. mysql-test/t/delete.test@stripped, 2007-12-19 17:30:27-02:00, davi@stripped +157 -2 Add test case for Bug#27525 and Bug#21148 mysql-test/t/derived.test@stripped, 2007-12-19 17:30:27-02:00, davi@stripped +1 -1 Update statement error, alias is properly resolved now. mysql-test/t/multi_update.test@stripped, 2007-12-19 17:30:27-02:00, davi@stripped +0 -1 Statement is valid now. sql/sql_parse.cc@stripped, 2007-12-19 17:30:27-02:00, davi@stripped +181 -27 Implement new algorithm for the resolution of alias in a multiple table delete statement. sql/sql_yacc.yy@stripped, 2007-12-19 17:30:27-02:00, davi@stripped +10 -10 Rework multi-delete parser rules to no accept table alias for the table source list. sql/table.h@stripped, 2007-12-19 17:30:28-02:00, davi@stripped +2 -0 Add flag to signal that the table has a alias. 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 2007-12-19 17:30:27 -02:00 @@ -255,12 +255,11 @@ ERROR 42000: You have an error in your S 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; +ERROR 42S02: Unknown table 'alias' in MULTI DELETE DELETE FROM t1 USING t1 WHERE a = 1; SELECT * FROM t1; a @@ -279,3 +278,161 @@ 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 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 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 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 3D000: No database selected +DELETE t1 FROM db1.t1, db2.t1 AS a1; +ERROR 42000: Not unique table/alias: 't1' +DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2; +ERROR 42000: Not unique table/alias: 't1' +DELETE t1 FROM db1.t1, db2.t1; +ERROR 42000: Not unique table/alias: 't1' +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 42S02: Table 'test.a1' doesn't exist +DELETE t1 FROM db1.t1, db2.t1 AS a1; +ERROR 42000: Not unique table/alias: 't1' +DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2; +ERROR 42000: Not unique table/alias: 't1' +DELETE t1 FROM db1.t1, db2.t1; +ERROR 42000: Not unique table/alias: 't1' +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 test.t2, t2 FROM t2 AS a1, db2.t2 AS t2 WHERE a1.a = 1 AND t2.a = 1; +SELECT ROW_COUNT(); +ROW_COUNT() +2 +CALL count_rows(); +COUNT(db1.t1) +3 +COUNT(db1.t2) +2 +COUNT(db2.t1) +3 +COUNT(db2.t2) +2 +COUNT(test.t1) +3 +COUNT(test.t2) +2 +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) +2 +COUNT(test.t1) +3 +COUNT(test.t2) +1 +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 2007-12-19 17:30:27 -02: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/r/multi_update.result b/mysql-test/r/multi_update.result --- a/mysql-test/r/multi_update.result 2007-10-13 18:12:45 -02:00 +++ b/mysql-test/r/multi_update.result 2007-12-19 17:30:27 -02:00 @@ -407,7 +407,6 @@ DELETE t1 FROM t1, t2 AS t3; DELETE t4 FROM t1, t1 AS t4; DELETE t3 FROM t1 AS t3, t1 AS t4; DELETE t1 FROM t1 AS t3, t2 AS t4; -ERROR 42S02: Unknown table 't1' in MULTI DELETE INSERT INTO t1 values (1),(2); INSERT INTO t2 values (1),(2); DELETE t1 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=1; 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 2007-12-19 17:30:27 -02: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 a 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 2007-12-19 17:30:27 -02: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 a 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 2007-12-19 17:30:27 -02:00 @@ -261,12 +261,11 @@ INSERT INTO db2.t1 (a) SELECT * FROM t2; 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; +--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 +291,159 @@ 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_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_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_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_NO_DB_ERROR +DELETE a1 FROM a1, db1.t1 AS a1; +--error ER_NONUNIQ_TABLE +DELETE t1 FROM db1.t1, db2.t1 AS a1; +--error ER_NONUNIQ_TABLE +DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2; +--error ER_NONUNIQ_TABLE +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_NO_SUCH_TABLE +DELETE a1 FROM a1, db1.t1 AS a1; +--error ER_NONUNIQ_TABLE +DELETE t1 FROM db1.t1, db2.t1 AS a1; +--error ER_NONUNIQ_TABLE +DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2; +--error ER_NONUNIQ_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 test.t2, t2 FROM t2 AS a1, db2.t2 AS t2 WHERE a1.a = 1 AND t2.a = 1; +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 2007-12-19 17:30:27 -02: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/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test --- a/mysql-test/t/multi_update.test 2007-10-13 18:12:46 -02:00 +++ b/mysql-test/t/multi_update.test 2007-12-19 17:30:27 -02:00 @@ -367,7 +367,6 @@ CREATE TABLE t2 ( a int ); DELETE t1 FROM t1, t2 AS t3; DELETE t4 FROM t1, t1 AS t4; DELETE t3 FROM t1 AS t3, t1 AS t4; ---error 1109 DELETE t1 FROM t1 AS t3, t2 AS t4; INSERT INTO t1 values (1),(2); INSERT INTO t2 values (1),(2); diff -Nrup a/sql/sql_parse.cc b/sql/sql_parse.cc --- a/sql/sql_parse.cc 2007-12-18 22:46:32 -02:00 +++ b/sql/sql_parse.cc 2007-12-19 17:30:27 -02:00 @@ -5719,10 +5719,20 @@ TABLE_LIST *st_select_lex::add_table_to_ ptr->db= table->db.str; ptr->db_length= table->db.length; } + else if (test(table_options & TL_OPTION_ALIAS)) + { + /* + Multiple-table DELETE needs to detect fully qualified table names. + It will update/copy the selected database name later if required. + */ + ptr->db= (char*) any_db; + ptr->db_length= sizeof(any_db) - 1; + } else if (lex->copy_db_to(&ptr->db, &ptr->db_length)) DBUG_RETURN(0); ptr->alias= alias_str; + ptr->alias_set= 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; @@ -6754,54 +6764,198 @@ bool multi_delete_precheck(THD *thd, TAB /* + Non-unique (but same table) matching with precedence constraints. + + @param ref New table match. + @param match Previous table match. + + @return FALSE on success, TRUE otherwise. +*/ + +static bool nonuniq_match(TABLE_LIST *ref, TABLE_LIST **match) +{ + DBUG_ENTER("nonuniq_match"); + + /* + Due to backwards compatibility, allow one special case of ambiguity + when the duplicated matches refer to the same table and one of the + matches does not specify a explicit alias. This scheme is used to + remove duplicates from a table: + + DELETE t1 FROM t1, t1 AS t2 WHERE t1.b = t2.b AND t1.a > t2.a; + + Since t1 can be correspondent to 't1' and 't1 AS t2', give precedence + to the first one because it does not specify a alias. Otherwise it + would delete different rows depending on the position of the match + in the table references list. + */ + + if (my_strcasecmp(table_alias_charset, ref->table_name, + (*match)->table_name) || strcmp(ref->db, (*match)->db)) + DBUG_RETURN(TRUE); + + if (!(*match)->alias_set) + { + if (ref->table_name != ref->alias) + DBUG_RETURN(FALSE); + } + else if (!ref->alias_set) + { + *match= ref; + DBUG_RETURN(FALSE); + } + + DBUG_RETURN(TRUE); +} + + +/* + 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 *src, + TABLE_LIST *ref) +{ + TABLE_LIST *match= NULL, *head= ref; + enum { FQTN, TALIAS, TNAME } pass; + DBUG_ENTER("multi_delete_table_match"); + + if (src->db == any_db) + pass= TALIAS; + else + pass= FQTN; + +loop: + for (ref= head; ref; ref= ref->next_local) + { + switch (pass) { + case FQTN: + if (!my_strcasecmp(table_alias_charset, src->table_name, ref->table_name) + && !strcmp(src->db, ref->db)) + { + if (match) + { + if (nonuniq_match(ref, &match)) + goto nonuniq; + } + else + match= ref; + } + break; + case TALIAS: + /* Skip alias derived from table name. */ + if (!ref->alias_set) + continue; + if (!my_strcasecmp(table_alias_charset, src->alias, ref->alias)) + { + if (match) + goto nonuniq; + match= ref; + } + break; + case TNAME: + if (!my_strcasecmp(table_alias_charset, src->table_name, ref->table_name)) + { + if (match) + { + if (nonuniq_match(ref, &match)) + goto nonuniq; + } + else + match= ref; + } + } + } + + if (match) + DBUG_RETURN(match); + + if (pass == TALIAS) + { + pass= TNAME; + goto loop; + } + else if (pass == TNAME) + { + if (lex->copy_db_to(&src->db, &src->db_length)) + DBUG_RETURN(NULL); + pass= FQTN; + goto loop; + } + + my_error(ER_UNKNOWN_TABLE, MYF(0), src->alias, "MULTI DELETE"); + + DBUG_RETURN(NULL); + +nonuniq: + my_error(ER_NONUNIQ_TABLE, MYF(0), src->alias); + DBUG_RETURN(NULL); +} + + +/* Link tables in auxilary table list of multi-delete with corresponding elements in main table list, and set proper locks for them. - SYNOPSIS - multi_delete_set_locks_and_link_aux_tables() - lex - pointer to LEX representing multi-delete + @param lex pointer to LEX representing multi-delete - RETURN VALUE - FALSE - success - TRUE - error + @remark The multiple-table DELETE statement uses the target table + list when searching for rows to delete, but deletes matching rows + only from tables in the source table list. + + @return FALSE on success, TRUE otherwise. */ bool multi_delete_set_locks_and_link_aux_tables(LEX *lex) { - TABLE_LIST *tables= (TABLE_LIST*)lex->select_lex.table_list.first; - TABLE_LIST *target_tbl; + TABLE_LIST *src, *ref; + /* The target table list, tables to search for rows to delete. */ + TABLE_LIST *ref_tbl= (TABLE_LIST *) lex->select_lex.table_list.first; + /* The source table list, tables to delete matching rows from. */ + TABLE_LIST *src_tbl= (TABLE_LIST *) lex->auxiliary_table_list.first; DBUG_ENTER("multi_delete_set_locks_and_link_aux_tables"); lex->table_count= 0; - for (target_tbl= (TABLE_LIST *)lex->auxiliary_table_list.first; - target_tbl; target_tbl= target_tbl->next_local) + /* Match source tables to table references. */ + for (src= src_tbl; src; src= src->next_local) { 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) + /* Do we have a winner? */ + if (!(ref= multi_delete_table_match(lex, src, ref_tbl))) { - if (!my_strcasecmp(table_alias_charset, - target_tbl->alias, walk->alias) && - !strcmp(walk->db, target_tbl->db)) - break; + my_error(ER_UNKNOWN_TABLE, MYF(0), src->alias, "MULTI DELETE"); + DBUG_RETURN(TRUE); } - if (!walk) + if (!ref->derived) { - my_error(ER_UNKNOWN_TABLE, MYF(0), - target_tbl->table_name, "MULTI DELETE"); - DBUG_RETURN(TRUE); + src->table_name= ref->table_name; + src->table_name_length= ref->table_name_length; } - if (!walk->derived) + if (src->db == any_db) { - target_tbl->table_name= walk->table_name; - target_tbl->table_name_length= walk->table_name_length; + src->db= ref->db; + src->db_length= ref->db_length; } - walk->updating= target_tbl->updating; - walk->lock_type= target_tbl->lock_type; - target_tbl->correspondent_table= walk; // Remember corresponding table + ref->updating= src->updating; + ref->lock_type= src->lock_type; + src->correspondent_table= ref; // Remember corresponding table + src->derived= ref->derived; // Skip grants check } + DBUG_RETURN(FALSE); } diff -Nrup a/sql/sql_yacc.yy b/sql/sql_yacc.yy --- a/sql/sql_yacc.yy 2007-12-14 05:19:47 -02:00 +++ b/sql/sql_yacc.yy 2007-12-19 17:30:27 -02:00 @@ -1326,7 +1326,7 @@ END_OF_INPUT %type '-' '+' '*' '/' '%' '(' ')' ',' '!' '{' '}' '&' '|' 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 %% /* @@ -8928,7 +8928,7 @@ delete: lex->ignore= 0; lex->select_lex.init_order(); } - opt_delete_options single_multi {} + opt_delete_options single_multi ; single_multi: @@ -8943,38 +8943,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 2007-12-18 22:46:32 -02:00 +++ b/sql/table.h 2007-12-19 17:30:28 -02:00 @@ -1116,6 +1116,8 @@ struct TABLE_LIST int lock_timeout; /* NOWAIT or WAIT [X] */ bool lock_transactional; /* If transactional lock requested. */ bool internal_tmp_table; + /* Query specified a alias for the table. */ + bool alias_set; /* View creation context. */