diff options
author | Alexey Kopytov <Alexey.Kopytov@Sun.com> | 2010-03-24 18:03:44 +0300 |
---|---|---|
committer | Alexey Kopytov <Alexey.Kopytov@Sun.com> | 2010-03-24 18:03:44 +0300 |
commit | d95c1e3b470506c7df6dfce3fe6dc7e5b46930ee (patch) | |
tree | 9f13d4fcc3ac732dc94fe2cae446f6f8c2b4e02b /mysql-test/t/delete.test | |
parent | abc6846d5b1df4846c4ffc03f4c93c82f874dd96 (diff) | |
parent | ae715642f46d4ed9ea8b5dd9b5cc9f3cace7f437 (diff) | |
download | mariadb-git-d95c1e3b470506c7df6dfce3fe6dc7e5b46930ee.tar.gz |
Manual merge of mysql-trunk into mysql-trunk-merge.
Conflicts:
Text conflict in client/mysqlbinlog.cc
Text conflict in mysql-test/Makefile.am
Text conflict in mysql-test/collections/default.daily
Text conflict in mysql-test/r/mysqlbinlog_row_innodb.result
Text conflict in mysql-test/suite/rpl/r/rpl_typeconv_innodb.result
Text conflict in mysql-test/suite/rpl/t/rpl_get_master_version_and_clock.test
Text conflict in mysql-test/suite/rpl/t/rpl_row_create_table.test
Text conflict in mysql-test/suite/rpl/t/rpl_slave_skip.test
Text conflict in mysql-test/suite/rpl/t/rpl_typeconv_innodb.test
Text conflict in mysys/charset.c
Text conflict in sql/field.cc
Text conflict in sql/field.h
Text conflict in sql/item.h
Text conflict in sql/item_func.cc
Text conflict in sql/log.cc
Text conflict in sql/log_event.cc
Text conflict in sql/log_event_old.cc
Text conflict in sql/mysqld.cc
Text conflict in sql/rpl_utility.cc
Text conflict in sql/rpl_utility.h
Text conflict in sql/set_var.cc
Text conflict in sql/share/Makefile.am
Text conflict in sql/sql_delete.cc
Text conflict in sql/sql_plugin.cc
Text conflict in sql/sql_select.cc
Text conflict in sql/sql_table.cc
Text conflict in storage/example/ha_example.h
Text conflict in storage/federated/ha_federated.cc
Text conflict in storage/myisammrg/ha_myisammrg.cc
Text conflict in storage/myisammrg/myrg_open.c
Diffstat (limited to 'mysql-test/t/delete.test')
-rw-r--r-- | mysql-test/t/delete.test | 155 |
1 files changed, 154 insertions, 1 deletions
diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 7bbc470137a..acd67e0e766 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -265,8 +265,8 @@ 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 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; @@ -308,6 +308,159 @@ DROP TABLE t1; --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 |