Below is the list of changes that have just been committed into a local
5.2 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-10-08 08:38:07-03:00, davi@stripped +6 -0
Bug#27525 table not found when using multi-table-deletes with aliases over several databas
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.
The solution is to mark tables without explicit schema name
so that during the alias resolving phase the schema name is
set to the one of the corresponding table in the main table
list.
Duplicate bug reports: 23413, 26370 and 21148.
Docs team alert: don't forget to remove the note about the
limitation from the manual (DELETE Syntax).
mysql-test/r/delete.result@stripped, 2007-10-08 08:38:01-03:00, davi@stripped +43 -0
Add test case result for Bug#27525
mysql-test/r/derived.result@stripped, 2007-10-08 08:38:01-03:00, davi@stripped +1 -1
Fix test case result, cannot delete from a table and select from the same
table in a subquery.
mysql-test/t/delete.test@stripped, 2007-10-08 08:38:01-03:00, davi@stripped +38 -0
Add test case for Bug#27525
mysql-test/t/derived.test@stripped, 2007-10-08 08:38:01-03:00, davi@stripped +1 -1
Fix test case error, cannot delete from a table and select from the same
table in a subquery.
sql/sql_parse.cc@stripped, 2007-10-08 08:38:01-03:00, davi@stripped +7 -1
Update the target table database to the database of a matching entry
of corresponding table in the main table list. Skip grant checks for
derived tables, they are not updatable anyway.
sql/sql_yacc.yy@stripped, 2007-10-08 08:38:02-03:00, davi@stripped +4 -1
Mark tables without explicit schema name with a special symbol.
diff -Nrup a/mysql-test/r/delete.result b/mysql-test/r/delete.result
--- a/mysql-test/r/delete.result 2007-02-23 14:49:33 -02:00
+++ b/mysql-test/r/delete.result 2007-10-08 08:38:01 -03:00
@@ -223,3 +223,46 @@ 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;
+drop table if exists t1,t2,t3,t4,t5;
+drop database if exists db1;
+drop database if exists db2;
+create database db1;
+use db1;
+create table t1 (a int, b int);
+insert into t1 values (1,1),(2,2),(3,3);
+create table t2 as select * from t1;
+create database db2;
+use db2;
+create table t4 as select * from db1.t1;
+create table t5 as select * from db1.t1;
+delete p1 from db1.t1 as p1 inner join t4 p2 on p1.a = p2.a;
+select * from db1.t1;
+a b
+select * from db2.t4;
+a b
+1 1
+2 2
+3 3
+delete p1,p3 from db1.t1 as p1 inner join t4 p2 on p1.a = p2.a;
+ERROR 42S02: Unknown table 'p3' in MULTI DELETE
+delete from p1 using db1.t1 as p1, t4 as p1 where 1 = 1;
+delete p1.*,p2.* from db1.t1 p1 inner join db2.t4 p2 on p1.a = p2.a where p1.a = 1;
+delete db1.t2,t5 from t4 as t5 where t5.a = db1.t2.a;
+ERROR 42S02: Unknown table 't2' in MULTI DELETE
+delete db1.t2,t5 from t4 as t5, db1.t2 where 1 = db1.t2.a and t5.a = 1;
+select * from db1.t2;
+a b
+2 2
+3 3
+select * from t4;
+a b
+2 2
+3 3
+select * from t5;
+a b
+1 1
+2 2
+3 3
+drop database db1;
+drop database db2;
+use test;
diff -Nrup a/mysql-test/r/derived.result b/mysql-test/r/derived.result
--- a/mysql-test/r/derived.result 2007-05-29 09:57:13 -03:00
+++ b/mysql-test/r/derived.result 2007-10-08 08:38:01 -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/t/delete.test b/mysql-test/t/delete.test
--- a/mysql-test/t/delete.test 2007-02-23 14:49:34 -02:00
+++ b/mysql-test/t/delete.test 2007-10-08 08:38:01 -03:00
@@ -221,3 +221,41 @@ DELETE FROM t1 ORDER BY t2.x;
DELETE FROM t1 ORDER BY (SELECT x);
DROP TABLE t1;
+
+#
+# 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 table if exists t1,t2,t3,t4,t5;
+drop database if exists db1;
+drop database if exists db2;
+--enable_warnings
+create database db1;
+use db1;
+create table t1 (a int, b int);
+insert into t1 values (1,1),(2,2),(3,3);
+create table t2 as select * from t1;
+create database db2;
+use db2;
+create table t4 as select * from db1.t1;
+create table t5 as select * from db1.t1;
+delete p1 from db1.t1 as p1 inner join t4 p2 on p1.a = p2.a;
+select * from db1.t1;
+select * from db2.t4;
+--error ER_UNKNOWN_TABLE
+delete p1,p3 from db1.t1 as p1 inner join t4 p2 on p1.a = p2.a;
+delete from p1 using db1.t1 as p1, t4 as p1 where 1 = 1;
+delete p1.*,p2.* from db1.t1 p1 inner join db2.t4 p2 on p1.a = p2.a where p1.a = 1;
+--error ER_UNKNOWN_TABLE
+delete db1.t2,t5 from t4 as t5 where t5.a = db1.t2.a;
+delete db1.t2,t5 from t4 as t5, db1.t2 where 1 = db1.t2.a and t5.a = 1;
+select * from db1.t2;
+select * from t4;
+select * from t5;
+drop database db1;
+drop database db2;
+use test;
diff -Nrup a/mysql-test/t/derived.test b/mysql-test/t/derived.test
--- a/mysql-test/t/derived.test 2007-02-26 07:49:23 -03:00
+++ b/mysql-test/t/derived.test 2007-10-08 08:38:01 -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 2007-08-29 20:35:10 -03:00
+++ b/sql/sql_parse.cc 2007-10-08 08:38:01 -03:00
@@ -6711,7 +6711,7 @@ bool multi_delete_set_locks_and_link_aux
{
if (!my_strcasecmp(table_alias_charset,
target_tbl->alias, walk->alias) &&
- !strcmp(walk->db, target_tbl->db))
+ (!strcmp(walk->db, target_tbl->db) || target_tbl->db == any_db))
break;
}
if (!walk)
@@ -6725,9 +6725,15 @@ bool multi_delete_set_locks_and_link_aux
target_tbl->table_name= walk->table_name;
target_tbl->table_name_length= walk->table_name_length;
}
+ if (target_tbl->db == any_db)
+ {
+ target_tbl->db= walk->db;
+ target_tbl->db_length= walk->db_length;
+ }
walk->updating= target_tbl->updating;
walk->lock_type= target_tbl->lock_type;
target_tbl->correspondent_table= walk; // Remember corresponding table
+ target_tbl->derived= walk->derived; // Skip grants checks
}
DBUG_RETURN(FALSE);
}
diff -Nrup a/sql/sql_yacc.yy b/sql/sql_yacc.yy
--- a/sql/sql_yacc.yy 2007-09-04 11:52:03 -03:00
+++ b/sql/sql_yacc.yy 2007-10-08 08:38:02 -03:00
@@ -9094,7 +9094,10 @@ table_wild_list:
table_wild_one:
ident opt_wild opt_table_alias
{
- if (!Select->add_table_to_list(YYTHD, new Table_ident($1),
+ LEX_STRING db={(char*) any_db,3};
+
+ if (!Select->add_table_to_list(YYTHD,
+ new Table_ident(YYTHD, db, $1, 0),
$3,
TL_OPTION_UPDATING | TL_OPTION_ALIAS,
Lex->lock_option))
