diff options
author | Monty <monty@mariadb.org> | 2020-06-05 12:51:01 +0300 |
---|---|---|
committer | Monty <monty@mariadb.org> | 2020-06-08 23:57:40 +0300 |
commit | 2fa58fa89e89cfbc9ff11a9afb30429ecbf874e6 (patch) | |
tree | 8fe3adfaeb8b7e122b091d050204dd50e3702217 | |
parent | 4e8f1bfb0633f42da8ee6e8b2a51da3e3b536319 (diff) | |
download | mariadb-git-2fa58fa89e89cfbc9ff11a9afb30429ecbf874e6.tar.gz |
Fixed error messages from DROP VIEW to align with DROP TABLE
- Produce a "Note" for all wrongly dropped objects
(Like doing DROP VIEW on a table).
- IF EXISTS ends with a list of all not existing objects, instead of a
separate note for every not existing object.
Other things:
- Fixed bug where one could do CREATE TEMPORARY SEQUENCE multiple times
and create multiple temporary sequences with the same name.
-rw-r--r-- | mysql-test/main/create_drop_view.result | 1 | ||||
-rw-r--r-- | mysql-test/main/drop.result | 5 | ||||
-rw-r--r-- | mysql-test/main/drop_combinations.inc | 24 | ||||
-rw-r--r-- | mysql-test/main/drop_combinations.result | 765 | ||||
-rw-r--r-- | mysql-test/main/drop_combinations.test | 332 | ||||
-rw-r--r-- | mysql-test/main/view.result | 8 | ||||
-rw-r--r-- | mysql-test/main/view.test | 5 | ||||
-rw-r--r-- | sql/sql_view.cc | 47 |
8 files changed, 1150 insertions, 37 deletions
diff --git a/mysql-test/main/create_drop_view.result b/mysql-test/main/create_drop_view.result index 92fbf5ac9e5..7cfa818ccbf 100644 --- a/mysql-test/main/create_drop_view.result +++ b/mysql-test/main/create_drop_view.result @@ -61,5 +61,6 @@ Warnings: Note 4092 Unknown VIEW: 'test.v1' DROP VIEW IF EXISTS t1; Warnings: +Warning 1347 'test.t1' is not of type 'VIEW' Note 4092 Unknown VIEW: 'test.t1' DROP TABLE t1; diff --git a/mysql-test/main/drop.result b/mysql-test/main/drop.result index b239dce4e62..9200efead38 100644 --- a/mysql-test/main/drop.result +++ b/mysql-test/main/drop.result @@ -209,10 +209,7 @@ Note 1051 Unknown table 'test.table1' Note 1051 Unknown table 'test.table2' DROP VIEW IF EXISTS view1,view2,view3,view4; Warnings: -Note 4092 Unknown VIEW: 'test.view1' -Note 4092 Unknown VIEW: 'test.view2' -Note 4092 Unknown VIEW: 'test.view3' -Note 4092 Unknown VIEW: 'test.view4' +Note 4092 Unknown VIEW: 'test.view1,test.view2,test.view3,test.view4' # Test error message when trigger does not find table CREATE TABLE table1(a int); diff --git a/mysql-test/main/drop_combinations.inc b/mysql-test/main/drop_combinations.inc new file mode 100644 index 00000000000..f1d8627f04f --- /dev/null +++ b/mysql-test/main/drop_combinations.inc @@ -0,0 +1,24 @@ +# +# Create all tables, sequences and views needed for drop combinations; +# + +if ($create_option != 'temporary') +{ + show tables; +} + +--echo # Generating test tables +--disable_query_log +--disable_warnings +eval create $create_option table if not exists t1 (a int); +eval create $create_option table if not exists t2 (a int); +eval create $create_option sequence if not exists s1; +eval create $create_option sequence if not exists s2; +if ($create_option != 'temporary') +{ + eval create view if not exists v1 as SELECT * from t1; + eval create view if not exists v2 as SELECT * from t2; +} + +--enable_warnings +--enable_query_log diff --git a/mysql-test/main/drop_combinations.result b/mysql-test/main/drop_combinations.result new file mode 100644 index 00000000000..079a312d70a --- /dev/null +++ b/mysql-test/main/drop_combinations.result @@ -0,0 +1,765 @@ +SET @save_default_engine= @@DEFAULT_STORAGE_ENGINE; +SET default_storage_engine=myisam; +show tables; +Tables_in_test +# Generating test tables +# +# DROP TABLE +# +drop table t1,t2,t3; +ERROR 42S02: Unknown table 'test.t3' +show tables; +Tables_in_test +s1 +s2 +v1 +v2 +# Generating test tables +drop table t1,t3,t2; +ERROR 42S02: Unknown table 'test.t3' +show tables; +Tables_in_test +s1 +s2 +v1 +v2 +# Generating test tables +drop table t1,t4,t2,t3; +ERROR 42S02: Unknown table 'test.t4,test.t3' +show tables; +Tables_in_test +s1 +s2 +v1 +v2 +# Generating test tables +drop table t1,s1,s2,t2; +show tables; +Tables_in_test +v1 +v2 +# Generating test tables +drop table t1,v1,v2,t2; +ERROR 42S02: Unknown table 'test.v1,test.v2' +show tables; +Tables_in_test +s1 +s2 +v1 +v2 +# Generating test tables +drop table v1,s1,s2,v2,t2; +ERROR 42S02: Unknown table 'test.v1,test.v2' +show tables; +Tables_in_test +t1 +v1 +v2 +# Generating test tables +drop table t1,s1,v1,t3,t4; +ERROR 42S02: Unknown table 'test.v1,test.t3,test.t4' +drop table s2,v2,t2,t1; +ERROR 42S02: Unknown table 'test.v2,test.t1' +show tables; +Tables_in_test +v1 +v2 +# Generating test tables +drop table s1,v1,s2,v2; +ERROR 42S02: Unknown table 'test.v1,test.v2' +show tables; +Tables_in_test +t1 +t2 +v1 +v2 +# Generating test tables +# +# DROP TABLE with if exists +# +drop table if exists t1,t2,t3; +Warnings: +Note 1051 Unknown table 'test.t3' +show tables; +Tables_in_test +s1 +s2 +v1 +v2 +# Generating test tables +drop table if exists t1,t3,t2; +Warnings: +Note 1051 Unknown table 'test.t3' +show tables; +Tables_in_test +s1 +s2 +v1 +v2 +# Generating test tables +drop table if exists t1,t4,t2,t3; +Warnings: +Note 1051 Unknown table 'test.t4' +Note 1051 Unknown table 'test.t3' +show tables; +Tables_in_test +s1 +s2 +v1 +v2 +# Generating test tables +drop table if exists t1,s1,s2,t2; +show tables; +Tables_in_test +v1 +v2 +# Generating test tables +drop table if exists t1,v1,v2,t2; +Warnings: +Note 1051 Unknown table 'test.v1' +Note 1051 Unknown table 'test.v2' +show tables; +Tables_in_test +s1 +s2 +v1 +v2 +# Generating test tables +drop table if exists v1,s1,s2,v2,t2; +Warnings: +Note 1051 Unknown table 'test.v1' +Note 1051 Unknown table 'test.v2' +show tables; +Tables_in_test +t1 +v1 +v2 +# Generating test tables +drop table if exists t1,s1,v1,t3,t4; +Warnings: +Warning 1017 Can't find file: './test/t1.MYI' (errno: 2 "No such file or directory") +Note 1051 Unknown table 'test.v1' +Note 1051 Unknown table 'test.t3' +Note 1051 Unknown table 'test.t4' +drop table if exists s2,v2,t2,t1; +Warnings: +Note 1051 Unknown table 'test.v2' +Note 1051 Unknown table 'test.t1' +show tables; +Tables_in_test +v1 +v2 +# Generating test tables +drop table if exists s1,v1,s2,v2; +Warnings: +Note 1051 Unknown table 'test.v1' +Note 1051 Unknown table 'test.v2' +show tables; +Tables_in_test +t1 +t2 +v1 +v2 +# Generating test tables +# +# DROP TEMPORARY TABLE +# +drop table if exists t1,t2; +drop temporary table if exists tt1,tt2; +drop sequence if exists s1,s2; +drop temporary sequence if exists ss1,ss2; +drop view if exists v1,v2; +drop temporary table t1,t2,t3; +ERROR 42S02: Unknown table 'test.t1,test.t2,test.t3' +# Generating test tables +drop temporary table t1,t3,t2; +ERROR 42S02: Unknown table 'test.t3' +# Generating test tables +drop temporary table t1,t4,t2,t3; +ERROR 42S02: Unknown table 'test.t4,test.t3' +# Generating test tables +drop temporary table t1,s1,s2,t2; +# Generating test tables +drop temporary table t1,v1,v2,t2; +ERROR 42S02: Unknown table 'test.v1,test.v2' +# Generating test tables +drop temporary table v1,s1,s2,v2,t2; +ERROR 42S02: Unknown table 'test.v1,test.v2' +# Generating test tables +drop temporary table t1,s1,v1,t3,t4; +ERROR 42S02: Unknown table 'test.v1,test.t3,test.t4' +# Generating test tables +drop temporary table s2,v2,t2,t1; +ERROR 42S02: Unknown table 'test.v2' +# Generating test tables +drop temporary table s1,v1,s2,v2; +ERROR 42S02: Unknown table 'test.v1,test.v2' +# Generating test tables +# +# DROP TEMPORARY TABLE with if exists +# +drop temporary table if exists t1,t2,t3; +Warnings: +Note 1051 Unknown table 'test.t3' +# Generating test tables +drop temporary table if exists t1,t3,t2; +Warnings: +Note 1051 Unknown table 'test.t3' +# Generating test tables +drop temporary table if exists t1,t4,t2,t3; +Warnings: +Note 1051 Unknown table 'test.t4' +Note 1051 Unknown table 'test.t3' +# Generating test tables +drop temporary table if exists t1,s1,s2,t2; +# Generating test tables +drop temporary table if exists t1,v1,v2,t2; +Warnings: +Note 1051 Unknown table 'test.v1' +Note 1051 Unknown table 'test.v2' +# Generating test tables +drop temporary table if exists v1,s1,s2,v2,t2; +Warnings: +Note 1051 Unknown table 'test.v1' +Note 1051 Unknown table 'test.v2' +# Generating test tables +drop temporary table if exists t1,s1,v1,t3,t4; +Warnings: +Note 1051 Unknown table 'test.v1' +Note 1051 Unknown table 'test.t3' +Note 1051 Unknown table 'test.t4' +drop temporary table if exists s2,v2,t2,t1; +Warnings: +Note 1051 Unknown table 'test.v2' +Note 1051 Unknown table 'test.t1' +# Generating test tables +drop temporary table if exists s1,v1,s2,v2; +Warnings: +Note 1051 Unknown table 'test.v1' +Note 1051 Unknown table 'test.v2' +# Generating test tables +drop temporary table t1,t2; +drop temporary sequence s1,s2; +show tables; +Tables_in_test +# Generating test tables +# +# DROP SEQUENCE +# +drop sequence s1,s2,s3; +ERROR 42S02: Unknown SEQUENCE: 'test.s3' +show tables; +Tables_in_test +t1 +t2 +v1 +v2 +# Generating test tables +drop sequence s1,s3,s2; +ERROR 42S02: Unknown SEQUENCE: 'test.s3' +show tables; +Tables_in_test +t1 +t2 +v1 +v2 +# Generating test tables +drop sequence s1,s4,s2,s3; +ERROR 42S02: Unknown SEQUENCE: 'test.s4,test.s3' +show tables; +Tables_in_test +t1 +t2 +v1 +v2 +# Generating test tables +drop sequence s1,t1,t2,s2; +ERROR 42S02: Unknown SEQUENCE: 'test.t1,test.t2' +show tables; +Tables_in_test +t1 +t2 +v1 +v2 +# Generating test tables +drop sequence s1,v1,v2,s2; +ERROR 42S02: Unknown SEQUENCE: 'test.v1,test.v2' +show tables; +Tables_in_test +t1 +t2 +v1 +v2 +# Generating test tables +drop sequence v1,t1,t2,v2,s2; +ERROR 42S02: Unknown SEQUENCE: 'test.v1,test.t1,test.t2,test.v2' +show tables; +Tables_in_test +s1 +t1 +t2 +v1 +v2 +# Generating test tables +drop sequence s1,t1,v1,t3,s4; +ERROR 42S02: Unknown SEQUENCE: 'test.t1,test.v1,test.t3,test.s4' +drop sequence t2,v2,s2,s1; +ERROR 42S02: Unknown SEQUENCE: 'test.t2,test.v2,test.s1' +show tables; +Tables_in_test +t1 +t2 +v1 +v2 +# Generating test tables +drop sequence t1,v1,t2,v2; +ERROR 42S02: Unknown SEQUENCE: 'test.t1,test.v1,test.t2,test.v2' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +v1 +v2 +# Generating test tables +# +# DROP SEQUENCE with if exists +# +drop sequence if exists s1,s2,s3; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.s3' +show tables; +Tables_in_test +t1 +t2 +v1 +v2 +# Generating test tables +drop sequence if exists s1,s3,s2; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.s3' +show tables; +Tables_in_test +t1 +t2 +v1 +v2 +# Generating test tables +drop sequence if exists s1,s4,s2,s3; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.s4' +Note 4091 Unknown SEQUENCE: 'test.s3' +show tables; +Tables_in_test +t1 +t2 +v1 +v2 +# Generating test tables +drop sequence if exists s1,t1,t2,s2; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.t1' +Note 4091 Unknown SEQUENCE: 'test.t2' +show tables; +Tables_in_test +t1 +t2 +v1 +v2 +# Generating test tables +drop sequence if exists s1,v1,v2,s2; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.v1' +Note 4091 Unknown SEQUENCE: 'test.v2' +show tables; +Tables_in_test +t1 +t2 +v1 +v2 +# Generating test tables +drop sequence if exists v1,t1,t2,v2,s2; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.v1' +Note 4091 Unknown SEQUENCE: 'test.t1' +Note 4091 Unknown SEQUENCE: 'test.t2' +Note 4091 Unknown SEQUENCE: 'test.v2' +show tables; +Tables_in_test +s1 +t1 +t2 +v1 +v2 +# Generating test tables +drop sequence if exists s1,t1,v1,t3,s4; +Warnings: +Warning 1017 Can't find file: './test/s1.MYI' (errno: 2 "No such file or directory") +Note 4091 Unknown SEQUENCE: 'test.t1' +Note 4091 Unknown SEQUENCE: 'test.v1' +Note 4091 Unknown SEQUENCE: 'test.t3' +Note 4091 Unknown SEQUENCE: 'test.s4' +drop sequence if exists t2,v2,s2,s1; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.t2' +Note 4091 Unknown SEQUENCE: 'test.v2' +Note 4091 Unknown SEQUENCE: 'test.s1' +show tables; +Tables_in_test +t1 +t2 +v1 +v2 +# Generating test tables +drop sequence if exists t1,v1,t2,v2; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.t1' +Note 4091 Unknown SEQUENCE: 'test.v1' +Note 4091 Unknown SEQUENCE: 'test.t2' +Note 4091 Unknown SEQUENCE: 'test.v2' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +v1 +v2 +# Generating test tables +# +# DROP TEMPORARY SEQUENCE +# +drop table if exists t1,t2; +drop temporary table if exists tt1,tt2; +drop sequence if exists s1,s2; +drop temporary sequence if exists ss1,ss2; +drop view if exists v1,v2; +# Generating test tables +drop temporary sequence s1,s2,s3; +ERROR 42S02: Unknown SEQUENCE: 'test.s3' +# Generating test tables +drop temporary sequence s1,s3,s2; +ERROR 42S02: Unknown SEQUENCE: 'test.s3' +# Generating test tables +drop temporary sequence s1,s4,s2,s3; +ERROR 42S02: Unknown SEQUENCE: 'test.s4,test.s3' +# Generating test tables +drop temporary sequence s1,t1,t2,s2; +ERROR 42S02: Unknown SEQUENCE: 'test.t1,test.t2' +# Generating test tables +drop temporary sequence s1,v1,v2,s2; +ERROR 42S02: Unknown SEQUENCE: 'test.v1,test.v2' +# Generating test tables +drop temporary sequence v1,t1,t2,v2,s2; +ERROR 42S02: Unknown SEQUENCE: 'test.v1,test.t1,test.t2,test.v2' +# Generating test tables +drop temporary sequence s1,t1,v1,t3,s4; +ERROR 42S02: Unknown SEQUENCE: 'test.t1,test.v1,test.t3,test.s4' +drop temporary sequence t2,v2,s2,s1; +ERROR 42S02: Unknown SEQUENCE: 'test.t2,test.v2,test.s1' +# Generating test tables +drop temporary sequence t1,v1,t2,v2; +ERROR 42S02: Unknown SEQUENCE: 'test.t1,test.v1,test.t2,test.v2' +# Generating test tables +# +# DROP TEMPORARY SEQUENCE with if exists +# +drop temporary sequence if exists s1,s2,s3; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.s3' +# Generating test tables +drop temporary sequence if exists s1,s3,s2; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.s3' +# Generating test tables +drop temporary sequence if exists s1,s4,s2,s3; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.s4' +Note 4091 Unknown SEQUENCE: 'test.s3' +# Generating test tables +drop temporary sequence if exists s1,t1,t2,s2; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.t1' +Note 4091 Unknown SEQUENCE: 'test.t2' +# Generating test tables +drop temporary sequence if exists s1,v1,v2,s2; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.v1' +Note 4091 Unknown SEQUENCE: 'test.v2' +# Generating test tables +drop temporary sequence if exists v1,t1,t2,v2,s2; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.v1' +Note 4091 Unknown SEQUENCE: 'test.t1' +Note 4091 Unknown SEQUENCE: 'test.t2' +Note 4091 Unknown SEQUENCE: 'test.v2' +# Generating test tables +drop temporary sequence if exists s1,t1,v1,t3,s4; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.t1' +Note 4091 Unknown SEQUENCE: 'test.v1' +Note 4091 Unknown SEQUENCE: 'test.t3' +Note 4091 Unknown SEQUENCE: 'test.s4' +drop temporary sequence if exists t2,v2,s2,s1; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.t2' +Note 4091 Unknown SEQUENCE: 'test.v2' +Note 4091 Unknown SEQUENCE: 'test.s1' +# Generating test tables +drop temporary sequence if exists t1,v1,t2,v2; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.t1' +Note 4091 Unknown SEQUENCE: 'test.v1' +Note 4091 Unknown SEQUENCE: 'test.t2' +Note 4091 Unknown SEQUENCE: 'test.v2' +# Generating test tables +drop temporary table t1,t2; +drop temporary sequence s1,s2; +show tables; +Tables_in_test +# Generating test tables +# +# DROP VIEW +# +drop view v1,v2,v3; +ERROR 42S02: Unknown VIEW: 'test.v3' +show warnings; +Level Code Message +Error 4092 Unknown VIEW: 'test.v3' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +# Generating test tables +drop view v1,v3,v2; +ERROR 42S02: Unknown VIEW: 'test.v3' +show warnings; +Level Code Message +Error 4092 Unknown VIEW: 'test.v3' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +# Generating test tables +drop view v1,v4,v2,v3; +ERROR 42S02: Unknown VIEW: 'test.v4,test.v3' +show warnings; +Level Code Message +Error 4092 Unknown VIEW: 'test.v4,test.v3' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +# Generating test tables +drop view v1,t1,t2,v2; +ERROR 42S02: Unknown VIEW: 'test.t1,test.t2' +show warnings; +Level Code Message +Warning 1347 'test.t1' is not of type 'VIEW' +Warning 1347 'test.t2' is not of type 'VIEW' +Error 4092 Unknown VIEW: 'test.t1,test.t2' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +# Generating test tables +drop view v1,s1,s2,v2; +ERROR 42S02: Unknown VIEW: 'test.s1,test.s2' +show warnings; +Level Code Message +Warning 1347 'test.s1' is not of type 'VIEW' +Warning 1347 'test.s2' is not of type 'VIEW' +Error 4092 Unknown VIEW: 'test.s1,test.s2' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +# Generating test tables +drop view s1,t1,t2,s2,v2; +ERROR 42S02: Unknown VIEW: 'test.s1,test.t1,test.t2,test.s2' +show warnings; +Level Code Message +Warning 1347 'test.s1' is not of type 'VIEW' +Warning 1347 'test.t1' is not of type 'VIEW' +Warning 1347 'test.t2' is not of type 'VIEW' +Warning 1347 'test.s2' is not of type 'VIEW' +Error 4092 Unknown VIEW: 'test.s1,test.t1,test.t2,test.s2' +drop table t1; +Warnings: +Warning 1017 Can't find file: './test/t1.MYI' (errno: 2 "No such file or directory") +show warnings; +Level Code Message +Warning 1017 Can't find file: './test/t1.MYI' (errno: 2 "No such file or directory") +drop view v1,t1; +ERROR 42S02: Unknown VIEW: 'test.t1' +show warnings; +Level Code Message +Error 4092 Unknown VIEW: 'test.t1' +show tables; +Tables_in_test +s1 +s2 +t2 +# Generating test tables +drop view v1,t1,s1,t3,v4; +ERROR 42S02: Unknown VIEW: 'test.t1,test.s1,test.t3,test.v4' +show warnings; +Level Code Message +Warning 1347 'test.t1' is not of type 'VIEW' +Warning 1347 'test.s1' is not of type 'VIEW' +Error 4092 Unknown VIEW: 'test.t1,test.s1,test.t3,test.v4' +drop view t2,s2,v2,v1; +ERROR 42S02: Unknown VIEW: 'test.t2,test.s2,test.v1' +show warnings; +Level Code Message +Warning 1347 'test.t2' is not of type 'VIEW' +Warning 1347 'test.s2' is not of type 'VIEW' +Error 4092 Unknown VIEW: 'test.t2,test.s2,test.v1' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +# Generating test tables +drop view t1,s1,t2,s2; +ERROR 42S02: Unknown VIEW: 'test.t1,test.s1,test.t2,test.s2' +show warnings; +Level Code Message +Warning 1347 'test.t1' is not of type 'VIEW' +Warning 1347 'test.s1' is not of type 'VIEW' +Warning 1347 'test.t2' is not of type 'VIEW' +Warning 1347 'test.s2' is not of type 'VIEW' +Error 4092 Unknown VIEW: 'test.t1,test.s1,test.t2,test.s2' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +v1 +v2 +# Generating test tables +# +# DROP VIEW with if exists +# +drop view if exists v1,v2,v3; +Warnings: +Note 4092 Unknown VIEW: 'test.v3' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +# Generating test tables +drop view if exists v1,v3,v2; +Warnings: +Note 4092 Unknown VIEW: 'test.v3' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +# Generating test tables +drop view if exists v1,v4,v2,v3; +Warnings: +Note 4092 Unknown VIEW: 'test.v4,test.v3' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +# Generating test tables +drop view if exists v1,t1,t2,v2; +Warnings: +Warning 1347 'test.t1' is not of type 'VIEW' +Warning 1347 'test.t2' is not of type 'VIEW' +Note 4092 Unknown VIEW: 'test.t1,test.t2' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +# Generating test tables +drop view if exists v1,s1,s2,v2; +Warnings: +Warning 1347 'test.s1' is not of type 'VIEW' +Warning 1347 'test.s2' is not of type 'VIEW' +Note 4092 Unknown VIEW: 'test.s1,test.s2' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +# Generating test tables +drop view if exists s1,t1,t2,s2,v2; +Warnings: +Warning 1347 'test.s1' is not of type 'VIEW' +Warning 1347 'test.t1' is not of type 'VIEW' +Warning 1347 'test.t2' is not of type 'VIEW' +Warning 1347 'test.s2' is not of type 'VIEW' +Note 4092 Unknown VIEW: 'test.s1,test.t1,test.t2,test.s2' +drop table t1; +drop view if exists v1,t1; +Warnings: +Note 4092 Unknown VIEW: 'test.t1' +show tables; +Tables_in_test +s1 +s2 +t2 +# Generating test tables +drop view if exists v1,t1,s1,t3,v4; +Warnings: +Warning 1347 'test.t1' is not of type 'VIEW' +Warning 1347 'test.s1' is not of type 'VIEW' +Note 4092 Unknown VIEW: 'test.t1,test.s1,test.t3,test.v4' +drop view if exists t2,s2,v2,v1; +Warnings: +Warning 1347 'test.t2' is not of type 'VIEW' +Warning 1347 'test.s2' is not of type 'VIEW' +Note 4092 Unknown VIEW: 'test.t2,test.s2,test.v1' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +# Generating test tables +drop view if exists t1,s1,t2,s2; +Warnings: +Warning 1347 'test.t1' is not of type 'VIEW' +Warning 1347 'test.s1' is not of type 'VIEW' +Warning 1347 'test.t2' is not of type 'VIEW' +Warning 1347 'test.s2' is not of type 'VIEW' +Note 4092 Unknown VIEW: 'test.t1,test.s1,test.t2,test.s2' +show tables; +Tables_in_test +s1 +s2 +t1 +t2 +v1 +v2 +# Generating test tables +# cleanup +drop table if exists t1,t2; +drop sequence if exists s1,s2; +drop view if exists v1,v2; +SET default_storage_engine=@save_default_engine; diff --git a/mysql-test/main/drop_combinations.test b/mysql-test/main/drop_combinations.test new file mode 100644 index 00000000000..1c3971b9520 --- /dev/null +++ b/mysql-test/main/drop_combinations.test @@ -0,0 +1,332 @@ +# +# Test different combinations of doing DROP TABLE/SEQUENCE/VIEW +# + +SET @save_default_engine= @@DEFAULT_STORAGE_ENGINE; +SET default_storage_engine=myisam; +let $DATADIR= `select @@datadir`; + +# Create tables t1,t2, sequences s1,s2 and views v1,v2 +let $create_option=; +--source drop_combinations.inc + +--echo # +--echo # DROP TABLE +--echo # + +--error ER_BAD_TABLE_ERROR +drop table t1,t2,t3; +--source drop_combinations.inc +--error ER_BAD_TABLE_ERROR +drop table t1,t3,t2; +--source drop_combinations.inc +--error ER_BAD_TABLE_ERROR +drop table t1,t4,t2,t3; +--source drop_combinations.inc +drop table t1,s1,s2,t2; +--source drop_combinations.inc +--error ER_BAD_TABLE_ERROR +drop table t1,v1,v2,t2; +--source drop_combinations.inc +--error ER_BAD_TABLE_ERROR +drop table v1,s1,s2,v2,t2; +--source drop_combinations.inc +--remove_file $DATADIR/test/t1.MYD +--error ER_BAD_TABLE_ERROR +drop table t1,s1,v1,t3,t4; +--error ER_BAD_TABLE_ERROR +drop table s2,v2,t2,t1; +--source drop_combinations.inc +--error ER_BAD_TABLE_ERROR +drop table s1,v1,s2,v2; +--source drop_combinations.inc + +--echo # +--echo # DROP TABLE with if exists +--echo # + +drop table if exists t1,t2,t3; +--source drop_combinations.inc +drop table if exists t1,t3,t2; +--source drop_combinations.inc +drop table if exists t1,t4,t2,t3; +--source drop_combinations.inc +drop table if exists t1,s1,s2,t2; +--source drop_combinations.inc +drop table if exists t1,v1,v2,t2; +--source drop_combinations.inc +drop table if exists v1,s1,s2,v2,t2; +--source drop_combinations.inc +--remove_file $DATADIR/test/t1.MYD +drop table if exists t1,s1,v1,t3,t4; +drop table if exists s2,v2,t2,t1; +--source drop_combinations.inc +drop table if exists s1,v1,s2,v2; +--source drop_combinations.inc + +--echo # +--echo # DROP TEMPORARY TABLE +--echo # + +--disable_warnings +drop table if exists t1,t2; +drop temporary table if exists tt1,tt2; +drop sequence if exists s1,s2; +drop temporary sequence if exists ss1,ss2; +drop view if exists v1,v2; +--enable_warnings +let $create_option=temporary; + +--error ER_BAD_TABLE_ERROR +drop temporary table t1,t2,t3; +--source drop_combinations.inc +--error ER_BAD_TABLE_ERROR +drop temporary table t1,t3,t2; +--source drop_combinations.inc +--error ER_BAD_TABLE_ERROR +drop temporary table t1,t4,t2,t3; +--source drop_combinations.inc +drop temporary table t1,s1,s2,t2; +--source drop_combinations.inc +--error ER_BAD_TABLE_ERROR +drop temporary table t1,v1,v2,t2; +--source drop_combinations.inc +--error ER_BAD_TABLE_ERROR +drop temporary table v1,s1,s2,v2,t2; +--source drop_combinations.inc +--error ER_BAD_TABLE_ERROR +drop temporary table t1,s1,v1,t3,t4; +--source drop_combinations.inc +--error ER_BAD_TABLE_ERROR +drop temporary table s2,v2,t2,t1; +--source drop_combinations.inc +--error ER_BAD_TABLE_ERROR +drop temporary table s1,v1,s2,v2; +--source drop_combinations.inc + +--echo # +--echo # DROP TEMPORARY TABLE with if exists +--echo # + +drop temporary table if exists t1,t2,t3; +--source drop_combinations.inc +drop temporary table if exists t1,t3,t2; +--source drop_combinations.inc +drop temporary table if exists t1,t4,t2,t3; +--source drop_combinations.inc +drop temporary table if exists t1,s1,s2,t2; +--source drop_combinations.inc +drop temporary table if exists t1,v1,v2,t2; +--source drop_combinations.inc +drop temporary table if exists v1,s1,s2,v2,t2; +--source drop_combinations.inc +drop temporary table if exists t1,s1,v1,t3,t4; +drop temporary table if exists s2,v2,t2,t1; +--source drop_combinations.inc +drop temporary table if exists s1,v1,s2,v2; +--source drop_combinations.inc + +let $create_option=; +drop temporary table t1,t2; +drop temporary sequence s1,s2; +--source drop_combinations.inc + +--echo # +--echo # DROP SEQUENCE +--echo # + +--error ER_UNKNOWN_SEQUENCES +drop sequence s1,s2,s3; +--source drop_combinations.inc +--error ER_UNKNOWN_SEQUENCES +drop sequence s1,s3,s2; +--source drop_combinations.inc +--error ER_UNKNOWN_SEQUENCES +drop sequence s1,s4,s2,s3; +--source drop_combinations.inc +--error ER_UNKNOWN_SEQUENCES +drop sequence s1,t1,t2,s2; +--source drop_combinations.inc +--error ER_UNKNOWN_SEQUENCES +drop sequence s1,v1,v2,s2; +--source drop_combinations.inc +--error ER_UNKNOWN_SEQUENCES +drop sequence v1,t1,t2,v2,s2; +--source drop_combinations.inc +--remove_file $DATADIR/test/s1.MYD +--error ER_UNKNOWN_SEQUENCES +drop sequence s1,t1,v1,t3,s4; +--error ER_UNKNOWN_SEQUENCES +drop sequence t2,v2,s2,s1; +--source drop_combinations.inc +--error ER_UNKNOWN_SEQUENCES +drop sequence t1,v1,t2,v2; +--source drop_combinations.inc + +--echo # +--echo # DROP SEQUENCE with if exists +--echo # + +drop sequence if exists s1,s2,s3; +--source drop_combinations.inc +drop sequence if exists s1,s3,s2; +--source drop_combinations.inc +drop sequence if exists s1,s4,s2,s3; +--source drop_combinations.inc +drop sequence if exists s1,t1,t2,s2; +--source drop_combinations.inc +drop sequence if exists s1,v1,v2,s2; +--source drop_combinations.inc +drop sequence if exists v1,t1,t2,v2,s2; +--source drop_combinations.inc +--remove_file $DATADIR/test/s1.MYD +drop sequence if exists s1,t1,v1,t3,s4; +drop sequence if exists t2,v2,s2,s1; +--source drop_combinations.inc +drop sequence if exists t1,v1,t2,v2; +--source drop_combinations.inc + +--echo # +--echo # DROP TEMPORARY SEQUENCE +--echo # + +--disable_warnings +drop table if exists t1,t2; +drop temporary table if exists tt1,tt2; +drop sequence if exists s1,s2; +drop temporary sequence if exists ss1,ss2; +drop view if exists v1,v2; +--enable_warnings +let $create_option=temporary; +--source drop_combinations.inc + +--error ER_UNKNOWN_SEQUENCES +drop temporary sequence s1,s2,s3; +--source drop_combinations.inc +--error ER_UNKNOWN_SEQUENCES +drop temporary sequence s1,s3,s2; +--source drop_combinations.inc +--error ER_UNKNOWN_SEQUENCES +drop temporary sequence s1,s4,s2,s3; +--source drop_combinations.inc +--error ER_UNKNOWN_SEQUENCES +drop temporary sequence s1,t1,t2,s2; +--source drop_combinations.inc +--error ER_UNKNOWN_SEQUENCES +drop temporary sequence s1,v1,v2,s2; +--source drop_combinations.inc +--error ER_UNKNOWN_SEQUENCES +drop temporary sequence v1,t1,t2,v2,s2; +--source drop_combinations.inc +--error ER_UNKNOWN_SEQUENCES +drop temporary sequence s1,t1,v1,t3,s4; +--error ER_UNKNOWN_SEQUENCES +drop temporary sequence t2,v2,s2,s1; +--source drop_combinations.inc +--error ER_UNKNOWN_SEQUENCES +drop temporary sequence t1,v1,t2,v2; +--source drop_combinations.inc + +--echo # +--echo # DROP TEMPORARY SEQUENCE with if exists +--echo # + +drop temporary sequence if exists s1,s2,s3; +--source drop_combinations.inc +drop temporary sequence if exists s1,s3,s2; +--source drop_combinations.inc +drop temporary sequence if exists s1,s4,s2,s3; +--source drop_combinations.inc +drop temporary sequence if exists s1,t1,t2,s2; +--source drop_combinations.inc +drop temporary sequence if exists s1,v1,v2,s2; +--source drop_combinations.inc +drop temporary sequence if exists v1,t1,t2,v2,s2; +--source drop_combinations.inc +drop temporary sequence if exists s1,t1,v1,t3,s4; +drop temporary sequence if exists t2,v2,s2,s1; +--source drop_combinations.inc +drop temporary sequence if exists t1,v1,t2,v2; +--source drop_combinations.inc + +let $create_option=; +drop temporary table t1,t2; +drop temporary sequence s1,s2; +--source drop_combinations.inc + +--echo # +--echo # DROP VIEW +--echo # + +--error ER_UNKNOWN_VIEW +drop view v1,v2,v3; +show warnings; +--source drop_combinations.inc +--error ER_UNKNOWN_VIEW +drop view v1,v3,v2; +show warnings; +--source drop_combinations.inc +--error ER_UNKNOWN_VIEW +drop view v1,v4,v2,v3; +show warnings; +--source drop_combinations.inc +--error ER_UNKNOWN_VIEW +drop view v1,t1,t2,v2; +show warnings; +--source drop_combinations.inc +--error ER_UNKNOWN_VIEW +drop view v1,s1,s2,v2; +show warnings; +--source drop_combinations.inc +--remove_file $DATADIR/test/t1.MYD +--error ER_UNKNOWN_VIEW +drop view s1,t1,t2,s2,v2; +show warnings; +drop table t1; +show warnings; +--error ER_UNKNOWN_VIEW +drop view v1,t1; +show warnings; +--source drop_combinations.inc +--error ER_UNKNOWN_VIEW +drop view v1,t1,s1,t3,v4; +show warnings; +--error ER_UNKNOWN_VIEW +drop view t2,s2,v2,v1; +show warnings; +--source drop_combinations.inc +--error ER_UNKNOWN_VIEW +drop view t1,s1,t2,s2; +show warnings; +--source drop_combinations.inc + +--echo # +--echo # DROP VIEW with if exists +--echo # + +drop view if exists v1,v2,v3; +--source drop_combinations.inc +drop view if exists v1,v3,v2; +--source drop_combinations.inc +drop view if exists v1,v4,v2,v3; +--source drop_combinations.inc +drop view if exists v1,t1,t2,v2; +--source drop_combinations.inc +drop view if exists v1,s1,s2,v2; +--source drop_combinations.inc +drop view if exists s1,t1,t2,s2,v2; +drop table t1; +drop view if exists v1,t1; +--source drop_combinations.inc +drop view if exists v1,t1,s1,t3,v4; +drop view if exists t2,s2,v2,v1; +--source drop_combinations.inc +drop view if exists t1,s1,t2,s2; +--source drop_combinations.inc + +--echo # cleanup +drop table if exists t1,t2; +drop sequence if exists s1,s2; +drop view if exists v1,v2; + +SET default_storage_engine=@save_default_engine; diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 55d95b2bb3b..c1ed5d72d68 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -198,7 +198,7 @@ c d drop view v100; ERROR 42S02: Unknown VIEW: 'test.v100' drop view t1; -ERROR HY000: 'test.t1' is not of type 'VIEW' +ERROR 42S02: Unknown VIEW: 'test.t1' drop table v1; ERROR 42S02: 'test.v1' is a view drop view v1,v2; @@ -2848,7 +2848,11 @@ Tables_in_test t1 CREATE VIEW v1 AS SELECT id FROM t1; DROP VIEW t1,v1; -ERROR HY000: 'test.t1' is not of type 'VIEW' +ERROR 42S02: Unknown VIEW: 'test.t1' +show warnings; +Level Code Message +Warning 1347 'test.t1' is not of type 'VIEW' +Error 4092 Unknown VIEW: 'test.t1' SHOW TABLES; Tables_in_test t1 diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 1632e6105ff..0545810fa69 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -130,7 +130,7 @@ select * from v2; drop view v100; # try to drop table with DROP VIEW --- error ER_WRONG_OBJECT +-- error ER_UNKNOWN_VIEW drop view t1; # try to drop VIEW with DROP TABLE @@ -2730,8 +2730,9 @@ DROP VIEW v2,v1; SHOW TABLES; CREATE VIEW v1 AS SELECT id FROM t1; ---error ER_WRONG_OBJECT +--error ER_UNKNOWN_VIEW DROP VIEW t1,v1; +show warnings; SHOW TABLES; DROP TABLE t1; diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 30fadcc63d2..d4f6613f2f0 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -1809,10 +1809,10 @@ bool mysql_drop_view(THD *thd, TABLE_LIST *views, enum_drop_mode drop_mode) char path[FN_REFLEN + 1]; TABLE_LIST *view; String non_existant_views; - const char *wrong_object_db= NULL, *wrong_object_name= NULL; - bool error= FALSE; + bool delete_error= FALSE, wrong_object_name= FALSE; bool some_views_deleted= FALSE; bool something_wrong= FALSE; + uint not_exists_count= 0; DBUG_ENTER("mysql_drop_view"); /* @@ -1842,32 +1842,22 @@ bool mysql_drop_view(THD *thd, TABLE_LIST *views, enum_drop_mode drop_mode) char name[FN_REFLEN]; my_snprintf(name, sizeof(name), "%s.%s", view->db.str, view->table_name.str); - if (thd->lex->if_exists()) - { - push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE, - ER_UNKNOWN_VIEW, - ER_THD(thd, ER_UNKNOWN_VIEW), - name); - continue; - } - if (not_exist) + if (non_existant_views.length()) + non_existant_views.append(','); + non_existant_views.append(name); + + if (!not_exist) { - if (non_existant_views.length()) - non_existant_views.append(','); - non_existant_views.append(name); + wrong_object_name= 1; + my_error(ER_WRONG_OBJECT, MYF(ME_WARNING), view->db.str, + view->table_name.str, "VIEW"); } else - { - if (!wrong_object_name) - { - wrong_object_db= view->db.str; - wrong_object_name= view->table_name.str; - } - } + not_exists_count++; continue; } if (unlikely(mysql_file_delete(key_file_frm, path, MYF(MY_WME)))) - error= TRUE; + delete_error= TRUE; some_views_deleted= TRUE; @@ -1880,17 +1870,16 @@ bool mysql_drop_view(THD *thd, TABLE_LIST *views, enum_drop_mode drop_mode) sp_cache_invalidate(); } - if (unlikely(wrong_object_name)) - { - my_error(ER_WRONG_OBJECT, MYF(0), wrong_object_db, wrong_object_name, - "VIEW"); - } + something_wrong= (delete_error || + (!thd->lex->if_exists() && (not_exists_count || + wrong_object_name))); + if (unlikely(non_existant_views.length())) { - my_error(ER_UNKNOWN_VIEW, MYF(0), non_existant_views.c_ptr_safe()); + my_error(ER_UNKNOWN_VIEW, MYF(something_wrong ? 0 : ME_NOTE), + non_existant_views.c_ptr_safe()); } - something_wrong= error || wrong_object_name || non_existant_views.length(); if (some_views_deleted || !something_wrong) { /* if something goes wrong, bin-log with possible error code, |