[Commits] 8aa4cff7b5b: Revert incorrect merge.

1 Feb 2019

revision-id: 8aa4cff7b5bd3876138dbaf12cbcc7402c95cfa0 (mariadb-galera-10.0.37-5-g8aa4cff7b5b)
parent(s): 448c35a4c57b1739d72f28fa3412bed0011b60ac
author: Jan Lindström
committer: Jan Lindström
timestamp: 2019-02-01 13:41:23 +0200
message:

Revert incorrect merge.

---
 mysql-test/r/alter_table.test      | 1908 ------------------------------
 mysql-test/r/grant.test            | 2291 ------------------------------------
 mysql-test/r/lowercase_fs_off.test |  124 --
 mysql-test/r/sp-security.test      | 1069 -----------------
 mysql-test/r/type_float.test       |  417 -------
 5 files changed, 5809 deletions(-)

diff --git a/mysql-test/r/alter_table.test b/mysql-test/r/alter_table.test
deleted file mode 100644
index aea1a880a81..00000000000
--- a/mysql-test/r/alter_table.test
+++ /dev/null
@@ -1,1908 +0,0 @@
-if (`select plugin_auth_version < "5.6.26" from information_schema.plugins where plugin_name='innodb'`)
-{
-  --skip Not fixed in XtraDB below 5.6.26
-}
---source include/have_innodb.inc
-#
-# Test of alter table
-#
---disable_warnings
-drop table if exists t1,t2;
-drop database if exists mysqltest;
---enable_warnings
-
-create table t1 (
-col1 int not null auto_increment primary key,
-col2 varchar(30) not null,
-col3 varchar (20) not null,
-col4 varchar(4) not null,
-col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null,
-col6 int not null, to_be_deleted int);
-insert into t1 values (2,4,3,5,"PENDING",1,7);
-alter table t1
-add column col4_5 varchar(20) not null after col4,
-add column col7 varchar(30) not null after col5,
-add column col8 datetime not null, drop column to_be_deleted,
-change column col2 fourth varchar(30) not null after col3,
-modify column col6 int not null first;
-select * from t1;
-drop table t1;
-
-create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL);
-insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12);
-alter table t1 add column new_col int, order by payoutid,bandid;
-select * from t1;
-alter table t1 order by bandid,payoutid;
-select * from t1;
-drop table t1;
-
-# Check that pack_keys and dynamic length rows are not forced. 
-
-CREATE TABLE t1 (
-GROUP_ID int(10) unsigned DEFAULT '0' NOT NULL,
-LANG_ID smallint(5) unsigned DEFAULT '0' NOT NULL,
-NAME varchar(80) DEFAULT '' NOT NULL,
-PRIMARY KEY (GROUP_ID,LANG_ID),
-KEY NAME (NAME));
-#show table status like "t1";
-ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null;
---replace_column 8 #
-SHOW FULL COLUMNS FROM t1;
-DROP TABLE t1;
-
-#
-# Test of ALTER TABLE ... ORDER BY
-#
-
-create table t1 (n int);
-insert into t1 values(9),(3),(12),(10);
-alter table t1 order by n;
-select * from t1;
-drop table t1;
-
-CREATE TABLE t1 (
-  id int(11) unsigned NOT NULL default '0',
-  category_id tinyint(4) unsigned NOT NULL default '0',
-  type_id tinyint(4) unsigned NOT NULL default '0',
-  body text NOT NULL,
-  user_id int(11) unsigned NOT NULL default '0',
-  status enum('new','old') NOT NULL default 'new',
-  PRIMARY KEY (id)
-) ENGINE=MyISAM;
-
-ALTER TABLE t1 ORDER BY t1.id, t1.status, t1.type_id, t1.user_id, t1.body;
-DROP TABLE t1;
-
-#
-# The following combination found a hang-bug in MyISAM
-#
-
-CREATE TABLE t1 (AnamneseId int(10) unsigned NOT NULL auto_increment,B BLOB,PRIMARY KEY (AnamneseId)) engine=myisam;
-insert into t1 values (null,"hello");
-LOCK TABLES t1 WRITE;
-ALTER TABLE t1 ADD Column new_col int not null;
-UNLOCK TABLES;
-OPTIMIZE TABLE t1;
-DROP TABLE t1;
-
-#
-# Drop and add an auto_increment column
-#
-
-create table t1 (i int unsigned not null auto_increment primary key);
-insert into t1 values (null),(null),(null),(null);
-alter table t1 drop i,add i int unsigned not null auto_increment, drop primary key, add primary key (i);
-select * from t1;
-drop table t1;
-
-#
-# Bug #2628: 'alter table t1 rename mysqltest.t1' silently drops mysqltest.t1 
-# if it exists
-#
-create table t1 (name char(15));
-insert into t1 (name) values ("current");
-create database mysqltest;
-create table mysqltest.t1 (name char(15));
-insert into mysqltest.t1 (name) values ("mysqltest");
-select * from t1;
-select * from mysqltest.t1;
---error ER_TABLE_EXISTS_ERROR
-alter table t1 rename mysqltest.t1;
-select * from t1;
-select * from mysqltest.t1;
-drop table t1;
-drop database mysqltest;
-
-#
-# ALTER TABLE ... ENABLE/DISABLE KEYS
-
-create table t1 (n1 int not null, n2 int, n3 int, n4 float,
-                unique(n1),
-                key (n1, n2, n3, n4),
-                key (n2, n3, n4, n1),
-                key (n3, n4, n1, n2),
-                key (n4, n1, n2, n3) );
-alter table t1 disable keys;
-show keys from t1;
-#let $1=10000;
-let $1=10;
---disable_query_log
-begin;
-while ($1)
-{
- eval insert into t1 values($1,RAND()*1000,RAND()*1000,RAND());
- dec $1;
-}
-commit;
---enable_query_log
-alter table t1 enable keys;
-show keys from t1;
-drop table t1;
-
-#
-# Alter table and rename
-#
-
-create table t1 (i int unsigned not null auto_increment primary key);
-alter table t1 rename t2;
-alter table t2 rename t1, add c char(10) comment "no comment";
-show columns from t1;
-drop table t1;
-
-# implicit analyze
-
-create table t1 (a int, b int);
-let $1=100;
---disable_query_log
-begin;
-while ($1)
-{
- eval insert into t1 values(1,$1), (2,$1), (3, $1);
- dec $1;
-}
-commit;
---enable_query_log
-alter table t1 add unique (a,b), add key (b);
-show keys from t1;
-analyze table t1;
-show keys from t1;
-drop table t1;
-
-#
-# Test of ALTER TABLE DELAYED
-#
-
-CREATE TABLE t1 (i int(10), index(i) ) ENGINE=MyISAM;
-ALTER TABLE t1 DISABLE KEYS;
-INSERT DELAYED INTO t1 VALUES(1),(2),(3);
-ALTER TABLE t1 ENABLE KEYS;
-drop table t1;
-
-#
-# Test ALTER TABLE ENABLE/DISABLE keys when things are locked
-#
-
-CREATE TABLE t1 (
-  Host varchar(16) binary NOT NULL default '',
-  User varchar(16) binary NOT NULL default '',
-  PRIMARY KEY  (Host,User)
-) ENGINE=MyISAM;
-
-ALTER TABLE t1 DISABLE KEYS;
-LOCK TABLES t1 WRITE;
-INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty');
-SHOW INDEX FROM t1;
-ALTER TABLE t1 ENABLE KEYS;
-UNLOCK TABLES;
-CHECK TABLES t1;
-DROP TABLE t1;
-
-#
-# Test with two keys
-#
-
-CREATE TABLE t1 (
-  Host varchar(16) binary NOT NULL default '',
-  User varchar(16) binary NOT NULL default '',
-  PRIMARY KEY  (Host,User),
-  KEY  (Host)
-) ENGINE=MyISAM;
-
-ALTER TABLE t1 DISABLE KEYS;
-SHOW INDEX FROM t1;
-LOCK TABLES t1 WRITE;
-INSERT INTO t1 VALUES ('localhost','root'),('localhost','');
-SHOW INDEX FROM t1;
-ALTER TABLE t1 ENABLE KEYS;
-SHOW INDEX FROM t1;
-UNLOCK TABLES;
-CHECK TABLES t1;
-
-# Test RENAME with LOCK TABLES
-LOCK TABLES t1 WRITE;
-ALTER TABLE t1 RENAME t2;
-UNLOCK TABLES;
-select * from t2;
-DROP TABLE t2;
-
-#
-# Test disable keys with locking
-#
-CREATE TABLE t1 (
-  Host varchar(16) binary NOT NULL default '',
-  User varchar(16) binary NOT NULL default '',
-  PRIMARY KEY  (Host,User),
-  KEY  (Host)
-) ENGINE=MyISAM;
-
-LOCK TABLES t1 WRITE;
-ALTER TABLE t1 DISABLE KEYS;
-SHOW INDEX FROM t1;
-DROP TABLE t1;
-
-#
-# BUG#4717 - check for valid table names
-#
-create table t1 (a int);
---error ER_WRONG_TABLE_NAME
-alter table t1 rename to ``;
---error ER_WRONG_TABLE_NAME
-rename table t1 to ``;
-drop table t1;
-
-#
-# BUG#6236 - ALTER TABLE MODIFY should set implicit NOT NULL on PK columns
-#
-drop table if exists t1, t2;
-create table t1 ( a varchar(10) not null primary key ) engine=myisam;
-create table t2 ( a varchar(10) not null primary key ) engine=merge union=(t1);
-flush tables;
-alter table t1 modify a varchar(10);
-show create table t2;
-flush tables;
-alter table t1 modify a varchar(10) not null;
-show create table t2;
-drop table if exists t1, t2;
-
-# The following is also part of bug #6236 (CREATE TABLE didn't properly count
-# not null columns for primary keys)
-
-create table t1 (a int, b int, c int, d int, e int, f int, g int, h int,i int, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;
-insert into t1 (a) values(1);
---replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X
-show table status like 't1';
-alter table t1 modify a int;
---replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X
-show table status like 't1';
-drop table t1;
-create table t1 (a int not null, b int not null, c int not null, d int not null, e int not null, f int not null, g int not null, h int not null,i int not null, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;
-insert into t1 (a) values(1);
---replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X
-show table status like 't1';
-drop table t1;
-
-#
-# Test that data get converted when character set is changed
-# Test that data doesn't get converted when src or dst is BINARY/BLOB
-#
-set names koi8r;
-create table t1 (a char(10) character set koi8r);
-insert into t1 values ('����');
-select a,hex(a) from t1;
-alter table t1 change a a char(10) character set cp1251;
-select a,hex(a) from t1;
-alter table t1 change a a binary(4);
-select a,hex(a) from t1;
-alter table t1 change a a char(10) character set cp1251;
-select a,hex(a) from t1;
-alter table t1 change a a char(10) character set koi8r;
-select a,hex(a) from t1;
-alter table t1 change a a varchar(10) character set cp1251;
-select a,hex(a) from t1;
-alter table t1 change a a char(10) character set koi8r;
-select a,hex(a) from t1;
-alter table t1 change a a text character set cp1251;
-select a,hex(a) from t1;
-alter table t1 change a a char(10) character set koi8r;
-select a,hex(a) from t1;
-delete from t1;
-
-#
-# Test ALTER TABLE .. CHARACTER SET ..
-#
-show create table t1;
-alter table t1 DEFAULT CHARACTER SET latin1;
-show create table t1;
-alter table t1 CONVERT TO CHARACTER SET latin1;
-show create table t1;
-alter table t1 DEFAULT CHARACTER SET cp1251;
-show create table t1;
-
-drop table t1;
-
-#
-# Bug#2821
-# Test that table CHARACTER SET does not affect blobs
-#
-create table t1 (myblob longblob,mytext longtext) 
-default charset latin1 collate latin1_general_cs;
-show create table t1;
-alter table t1 character set latin2;
-show create table t1;
-drop table t1;
-
-#
-# Bug 2361 (Don't drop UNIQUE with DROP PRIMARY KEY)
-#
-
-CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE);
-ALTER TABLE t1 DROP PRIMARY KEY;
-SHOW CREATE TABLE t1;
---error ER_CANT_DROP_FIELD_OR_KEY
-ALTER TABLE t1 DROP PRIMARY KEY;
-DROP TABLE t1;
-
-# BUG#3899
-create table t1 (a int, b int, key(a));
-insert into t1 values (1,1), (2,2);
---error ER_CANT_DROP_FIELD_OR_KEY
-alter table t1 drop key no_such_key;
-alter table t1 drop key a;
-drop table t1;
-
-#
-# BUG 12207 alter table ... discard table space on MyISAM table causes ERROR 2013 (HY000)
-#
-# Some platforms (Mac OS X, Windows) will send the error message using small letters.
-CREATE TABLE T12207(a int) ENGINE=MYISAM;
---replace_result t12207 T12207
---error ER_ILLEGAL_HA
-ALTER TABLE T12207 DISCARD TABLESPACE;
-DROP TABLE T12207;
-
-#
-# Bug #6479  ALTER TABLE ... changing charset fails for TEXT columns
-#
-# The column's character set was changed but the actual data was not
-# modified. In other words, the values were reinterpreted
-# as UTF8 instead of being converted.
-create table t1 (a text) character set koi8r;
-insert into t1 values (_koi8r'����');
-select hex(a) from t1;
-alter table t1 convert to character set cp1251;
-select hex(a) from t1;
-drop table t1;
-
-#
-# Test for bug #7884 "Able to add invalid unique index on TIMESTAMP prefix"
-# MySQL should not think that packed field with non-zero decimals is
-# geometry field and allow to create prefix index which is
-# shorter than packed field length.
-#
-create table t1 ( a timestamp );
---error ER_WRONG_SUB_KEY
-alter table t1 add unique ( a(1) );
-drop table t1;
-
-#
-# Bug #24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table
-#
-# This problem happens if the data change is compatible.
-# Changing to the same type is compatible for example.
-#
---disable_warnings
-drop table if exists t1;
---enable_warnings
-create table t1 (a int, key(a));
-show indexes from t1;
---echo "this used not to disable the index"
-alter table t1 modify a int, disable keys;
-show indexes from t1;
-
-alter table t1 enable keys;
-show indexes from t1;
-
-alter table t1 modify a bigint, disable keys;
-show indexes from t1;
-
-alter table t1 enable keys;
-show indexes from t1;
-
-alter table t1 add b char(10), disable keys;
-show indexes from t1;
-
-alter table t1 add c decimal(10,2), enable keys;
-show indexes from t1;
-
---echo "this however did"
-alter table t1 disable keys;
-show indexes from t1;
-
-desc t1;
-
-alter table t1 add d decimal(15,5);
---echo "The key should still be disabled"
-show indexes from t1;
-
-drop table t1;
-
---echo "Now will test with one unique index"
-create table t1(a int, b char(10), unique(a));
-show indexes from t1;
-alter table t1 disable keys;
-show indexes from t1;
-alter table t1 enable keys;
-
---echo "If no copy on noop change, this won't touch the data file"
---echo "Unique index, no change"
-alter table t1 modify a int, disable keys;
-show indexes from t1;
-
---echo "Change the type implying data copy"
---echo "Unique index, no change"
-alter table t1 modify a bigint, disable keys;
-show indexes from t1;
-
-alter table t1 modify a bigint;
-show indexes from t1;
-
-alter table t1 modify a int;
-show indexes from t1;
-
-drop table t1;
-
---echo "Now will test with one unique and one non-unique index"
-create table t1(a int, b char(10), unique(a), key(b));
-show indexes from t1;
-alter table t1 disable keys;
-show indexes from t1;
-alter table t1 enable keys;
-
-
---echo "If no copy on noop change, this won't touch the data file"
---echo "The non-unique index will be disabled"
-alter table t1 modify a int, disable keys;
-show indexes from t1;
-alter table t1 enable keys;
-show indexes from t1;
-
---echo "Change the type implying data copy"
---echo "The non-unique index will be disabled"
-alter table t1 modify a bigint, disable keys;
-show indexes from t1;
-
---echo "Change again the type, but leave the indexes as_is"
-alter table t1 modify a int;
-show indexes from t1;
---echo "Try the same. When data is no copied on similar tables, this is noop"
-alter table t1 modify a int;
-show indexes from t1;
-
-drop table t1;
-
-
-#
-# Bug#11493 - Alter table rename to default database does not work without
-#             db name qualifying
-#
-create database mysqltest;
-create table t1 (c1 int);
-# Move table to other database.
-alter table t1 rename mysqltest.t1;
-# Assure that it has moved.
---error ER_BAD_TABLE_ERROR
-drop table t1;
-# Move table back.
-alter table mysqltest.t1 rename t1;
-# Assure that it is back.
-drop table t1;
-# Now test for correct message if no database is selected.
-# Create t1 in 'test'.
-create table t1 (c1 int);
-# Change to other db.
-use mysqltest;
-# Drop the current db. This de-selects any db.
-drop database mysqltest;
-# Now test for correct message.
---error ER_NO_DB_ERROR
-alter table test.t1 rename t1;
-# Check that explicit qualifying works even with no selected db.
-alter table test.t1 rename test.t1;
-# Go back to standard 'test' db.
-use test;
-drop table t1;
-
-#
-# BUG#23404 - ROW_FORMAT=FIXED option is lost is an index is added to the
-# table
-#
-CREATE TABLE t1(a INT) ROW_FORMAT=FIXED;
-CREATE INDEX i1 ON t1(a);
-SHOW CREATE TABLE t1;
-DROP INDEX i1 ON t1;
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-#
-# Bug#24219 - ALTER TABLE ... RENAME TO ... , DISABLE KEYS leads to crash
-#
---disable_warnings
-DROP TABLE IF EXISTS bug24219;
-DROP TABLE IF EXISTS bug24219_2;
---enable_warnings
-
-CREATE TABLE bug24219 (a INT, INDEX(a));
-
-SHOW INDEX FROM bug24219;
-
-ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS;
-
-SHOW INDEX FROM bug24219_2;
-
-DROP TABLE bug24219_2;
-
-#
-# Bug#24562 (ALTER TABLE ... ORDER BY ... with complex expression asserts)
-#
-
---disable_warnings
-drop table if exists table_24562;
---enable_warnings
-
-create table table_24562(
-  section int,
-  subsection int,
-  title varchar(50));
-
-insert into table_24562 values
-(1, 0, "Introduction"),
-(1, 1, "Authors"),
-(1, 2, "Acknowledgements"),
-(2, 0, "Basics"),
-(2, 1, "Syntax"),
-(2, 2, "Client"),
-(2, 3, "Server"),
-(3, 0, "Intermediate"),
-(3, 1, "Complex queries"),
-(3, 2, "Stored Procedures"),
-(3, 3, "Stored Functions"),
-(4, 0, "Advanced"),
-(4, 1, "Replication"),
-(4, 2, "Load balancing"),
-(4, 3, "High availability"),
-(5, 0, "Conclusion");
-
-select * from table_24562;
-
-alter table table_24562 add column reviewer varchar(20),
-order by title;
-
-select * from table_24562;
-
-update table_24562 set reviewer="Me" where section=2;
-update table_24562 set reviewer="You" where section=3;
-
-alter table table_24562
-order by section ASC, subsection DESC;
-
-select * from table_24562;
-
-alter table table_24562
-order by table_24562.subsection ASC, table_24562.section DESC;
-
-select * from table_24562;
-
---error ER_PARSE_ERROR
-alter table table_24562 order by 12;
---error ER_PARSE_ERROR
-alter table table_24562 order by (section + 12);
---error ER_PARSE_ERROR
-alter table table_24562 order by length(title);
---error ER_PARSE_ERROR
-alter table table_24562 order by (select 12 from dual);
-
---error ER_BAD_FIELD_ERROR
-alter table table_24562 order by no_such_col;
-
-drop table table_24562;
-
-# End of 4.1 tests
-
-#
-# Bug #14693 (ALTER SET DEFAULT doesn't work)
-#
-
-create table t1 (mycol int(10) not null);
-alter table t1 alter column mycol set default 0;
-desc t1;
-drop table t1;
-
-#
-# Bug#25262 Auto Increment lost when changing Engine type
-#
-
-create table t1(id int(8) primary key auto_increment) engine=heap;
-
-insert into t1 values (null);
-insert into t1 values (null);
-
-select * from t1;
-
-# Set auto increment to 50
-alter table t1 auto_increment = 50;
-
-# Alter to myisam
-alter table t1 engine = myisam;
-
-# This insert should get id 50
-insert into t1 values (null);
-select * from t1;
-
-# Alter to heap again
-alter table t1 engine = heap;
-insert into t1 values (null);
-select * from t1;
-
-drop table t1;
-
-#
-# Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the
-#            NO_ZERO_DATE mode.
-#
-set @orig_sql_mode = @@sql_mode;
-set sql_mode="no_zero_date";
-create table t1(f1 int);
-alter table t1 add column f2 datetime not null, add column f21 date not null;
-insert into t1 values(1,'2000-01-01','2000-01-01');
---error 1292
-alter table t1 add column f3 datetime not null;
---error 1292
-alter table t1 add column f3 date not null;
---error 1292
-alter table t1 add column f4 datetime not null default '2002-02-02',
-  add column f41 date not null;
-alter table t1 add column f4 datetime not null default '2002-02-02',
-  add column f41 date not null default '2002-02-02';
-select * from t1;
-drop table t1;
-set sql_mode= @orig_sql_mode;
-
-#
-# Some additional tests for new, faster alter table.  Note that most of the
-# whole alter table code is being tested all around the test suite already.
-#
-
-create table t1 (v varchar(32));
-insert into t1 values ('def'),('abc'),('hij'),('3r4f');
-select * from t1;
-# Fast alter, no copy performed
-alter table t1 change v v2 varchar(32);
-select * from t1;
-# Fast alter, no copy performed
-alter table t1 change v2 v varchar(64);
-select * from t1;
-update t1 set v = 'lmn' where v = 'hij';
-select * from t1;
-# Regular alter table
-alter table t1 add i int auto_increment not null primary key first;
-select * from t1;
-update t1 set i=5 where i=3;
-select * from t1;
-alter table t1 change i i bigint;
-select * from t1;
-alter table t1 add unique key (i, v);
-select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn');
-drop table t1;
-
-#
-# Bug#6073 "ALTER table minor glich": ALTER TABLE complains that an index
-# without # prefix is not allowed for TEXT columns, while index
-# is defined with prefix.
-# 
-create table t1 (t varchar(255) default null, key t (t(80)))
-engine=myisam default charset=latin1;
-alter table t1 change t t text;
-drop table t1;
-
-#
-# Bug #26794: Adding an index with a prefix on a SPATIAL type breaks ALTER
-# TABLE
-#
-CREATE TABLE t1 (a varchar(500));
-
-ALTER TABLE t1 ADD b GEOMETRY NOT NULL, ADD SPATIAL INDEX(b);
-SHOW CREATE TABLE t1;
-ALTER TABLE t1 ADD KEY(b(50));
-SHOW CREATE TABLE t1;
-
-ALTER TABLE t1 ADD c POINT;
-SHOW CREATE TABLE t1;
-
---error ER_WRONG_SUB_KEY
-CREATE TABLE t2 (a INT, KEY (a(20)));
-
-ALTER TABLE t1 ADD d INT;
---error ER_WRONG_SUB_KEY
-ALTER TABLE t1 ADD KEY (d(20));
-
-# the 5.1 part of the test
---error ER_WRONG_SUB_KEY
-ALTER TABLE t1 ADD e GEOMETRY NOT NULL, ADD SPATIAL KEY (e(30));
-
-DROP TABLE t1;
-
-#
-# Bug#18038  MySQL server corrupts binary columns data
-#
-
-CREATE TABLE t1 (s CHAR(8) BINARY);
-INSERT INTO t1 VALUES ('test');
-SELECT LENGTH(s) FROM t1;
-ALTER TABLE t1 MODIFY s CHAR(10) BINARY;
-SELECT LENGTH(s) FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (s BINARY(8));
-INSERT INTO t1 VALUES ('test');
-SELECT LENGTH(s) FROM t1;
-SELECT HEX(s) FROM t1;
-ALTER TABLE t1 MODIFY s BINARY(10);
-SELECT HEX(s) FROM t1;
-SELECT LENGTH(s) FROM t1;
-DROP TABLE t1;
-
-#
-# Bug#19386: Multiple alter causes crashed table
-# The trailing column would get corrupted data, or server could not even read
-# it.
-#
-
-CREATE TABLE t1 (v VARCHAR(3), b INT);
-INSERT INTO t1 VALUES ('abc', 5);
-SELECT * FROM t1;
-ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-
-#
-# Bug#31291 ALTER TABLE CONVERT TO CHARACTER SET does not change some data types
-#
-create table t1 (a tinytext character set latin1);
-alter table t1 convert to character set utf8;
-show create table t1;
-drop table t1;
-create table t1 (a mediumtext character set latin1);
-alter table t1 convert to character set utf8;
-show create table t1;
-drop table t1;
-
---echo End of 5.0 tests
-
-#
-# Extended test coverage for ALTER TABLE behaviour under LOCK TABLES
-# It should be consistent across all platforms and for all engines
-# (Before 5.1 this was not true as behavior was different between 
-# Unix/Windows and transactional/non-transactional tables).
-# See also innodb_mysql.test
-#
---disable_warnings
-drop table if exists t1, t2, t3;
---enable_warnings
-create table t1 (i int);
-create table t3 (j int);
-insert into t1 values ();
-insert into t3 values ();
-# Table which is altered under LOCK TABLES it should stay in list of locked
-# tables and be available after alter takes place unless ALTER contains RENAME
-# clause. We should see the new definition of table, of course.
-lock table t1 write, t3 read;
-# Example of so-called 'fast' ALTER TABLE
-alter table t1 modify i int default 1;
-insert into t1 values ();
-select * from t1;
-# And now full-blown ALTER TABLE
-alter table t1 change i c char(10) default "Two";
-insert into t1 values ();
-select * from t1;
-# If table is renamed then it should be removed from the list
-# of locked tables. 'Fast' ALTER TABLE with RENAME clause:
-alter table t1 modify c char(10) default "Three", rename to t2;
---error ER_TABLE_NOT_LOCKED
-select * from t1;
---error ER_TABLE_NOT_LOCKED
-select * from t2;
-select * from t3;
-unlock tables;
-insert into t2 values ();
-select * from t2;
-lock table t2 write, t3 read;
-# Full ALTER TABLE with RENAME
-alter table t2 change c vc varchar(100) default "Four", rename to t1;
---error ER_TABLE_NOT_LOCKED
-select * from t1;
---error ER_TABLE_NOT_LOCKED
-select * from t2;
-select * from t3;
-unlock tables;
-insert into t1 values ();
-select * from t1;
-drop tables t1, t3;
-
-
-#
-# Bug#18775 - Temporary table from alter table visible to other threads
-#
-# Check if special characters work and duplicates are detected.
---disable_warnings
-DROP TABLE IF EXISTS `t+1`, `t+2`;
---enable_warnings
-CREATE TABLE `t+1` (c1 INT);
-ALTER TABLE  `t+1` RENAME `t+2`;
-CREATE TABLE `t+1` (c1 INT);
---error ER_TABLE_EXISTS_ERROR
-ALTER TABLE  `t+1` RENAME `t+2`;
-DROP TABLE   `t+1`, `t+2`;
-#
-# Same for temporary tables though these names do not become file names.
-CREATE TEMPORARY TABLE `tt+1` (c1 INT);
-ALTER TABLE  `tt+1` RENAME `tt+2`;
-CREATE TEMPORARY TABLE `tt+1` (c1 INT);
---error ER_TABLE_EXISTS_ERROR
-ALTER TABLE  `tt+1` RENAME `tt+2`;
-SHOW CREATE TABLE `tt+1`;
-SHOW CREATE TABLE `tt+2`;
-DROP TABLE   `tt+1`, `tt+2`;
-#
-# Check if special characters as in tmp_file_prefix work.
-CREATE TABLE `#sql1` (c1 INT);
-CREATE TABLE `@0023sql2` (c1 INT);
-SHOW TABLES;
-RENAME TABLE `#sql1`     TO `@0023sql1`;
-RENAME TABLE `@0023sql2` TO `#sql2`;
-SHOW TABLES;
-ALTER TABLE `@0023sql1`  RENAME `#sql-1`;
-ALTER TABLE `#sql2`      RENAME `@0023sql-2`;
-SHOW TABLES;
-INSERT INTO `#sql-1`     VALUES (1);
-INSERT INTO `@0023sql-2` VALUES (2);
-DROP TABLE `#sql-1`, `@0023sql-2`;
-#
-# Same for temporary tables though these names do not become file names.
-CREATE TEMPORARY TABLE `#sql1` (c1 INT);
-CREATE TEMPORARY TABLE `@0023sql2` (c1 INT);
-SHOW TABLES;
-ALTER TABLE `#sql1`      RENAME `@0023sql1`;
-ALTER TABLE `@0023sql2`  RENAME `#sql2`;
-SHOW TABLES;
-INSERT INTO `#sql2`      VALUES (1);
-INSERT INTO `@0023sql1`  VALUES (2);
-SHOW CREATE TABLE `#sql2`;
-SHOW CREATE TABLE `@0023sql1`;
-DROP TABLE `#sql2`, `@0023sql1`;
-
-#
-# Bug #22369: Alter table rename combined with other alterations causes lost tables
-#
-# This problem happens if the data change is compatible.
-# Changing to the same type is compatible for example.
-#
---disable_warnings
-DROP TABLE IF EXISTS t1;
-DROP TABLE IF EXISTS t2;
---enable_warnings
-CREATE TABLE t1 (
-  int_field INTEGER UNSIGNED NOT NULL,
-  char_field CHAR(10),
-  INDEX(`int_field`)
-);
-
-DESCRIBE t1;
-
-SHOW INDEXES FROM t1;
-
-INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet"); 
---echo "Non-copy data change - new frm, but old data and index files"
-ALTER TABLE t1
-  CHANGE int_field unsigned_int_field INTEGER UNSIGNED NOT NULL,
-  RENAME t2;
-
---error ER_NO_SUCH_TABLE
-SELECT * FROM t1 ORDER BY int_field;
-SELECT * FROM t2 ORDER BY unsigned_int_field;
-DESCRIBE t2;
-DESCRIBE t2;
-ALTER TABLE t2 MODIFY unsigned_int_field BIGINT UNSIGNED NOT NULL;
-DESCRIBE t2;
-
-DROP TABLE t2;
-
-#
-# Bug#28427: Columns were renamed instead of moving by ALTER TABLE.
-#
-CREATE TABLE t1 (f1 INT, f2 INT, f3 INT);
-INSERT INTO t1 VALUES (1, 2, NULL);
-SELECT * FROM t1;
-ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1;
-SELECT * FROM t1;
-ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2;
-SELECT * FROM t1;
-DROP TABLE t1;
-
-#
-# BUG#29957 - alter_table.test fails
-#
-create table t1 (c char(10) default "Two");
-lock table t1 write;
-insert into t1 values ();
-alter table t1 modify c char(10) default "Three";
-unlock tables;
-select * from t1;
-check table t1;
-drop table t1;
-
-#
-# Bug#33873: Fast ALTER TABLE doesn't work with multibyte character sets
-#
-
---disable_warnings
-DROP TABLE IF EXISTS t1;
---enable_warnings
-CREATE TABLE t1 (id int, c int) character set latin1;
-INSERT INTO t1 VALUES (1,1);
---enable_info
-ALTER TABLE t1 CHANGE c d int;
-ALTER TABLE t1 CHANGE d c int;
-ALTER TABLE t1 MODIFY c VARCHAR(10);
-ALTER TABLE t1 CHANGE c d varchar(10);
-ALTER TABLE t1 CHANGE d c varchar(10);
---disable_info
-DROP TABLE t1;
-
---disable_warnings
-DROP TABLE IF EXISTS t1;
---enable_warnings
-CREATE TABLE t1 (id int, c int) character set utf8;
-INSERT INTO t1 VALUES (1,1);
---enable_info
-ALTER TABLE t1 CHANGE c d int;
-ALTER TABLE t1 CHANGE d c int;
-ALTER TABLE t1 MODIFY c VARCHAR(10);
-ALTER TABLE t1 CHANGE c d varchar(10);
-ALTER TABLE t1 CHANGE d c varchar(10);
---disable_info
-DROP TABLE t1;
-
-#
-# Bug#39372 "Smart" ALTER TABLE not so smart after all.
-#
-create table t1(f1 int not null, f2 int not null, key  (f1), key (f2));
-let $count= 50;
---disable_query_log
-begin;
-while ($count)
-{
-  EVAL insert into t1 values (1,1),(1,1),(1,1),(1,1),(1,1);
-  EVAL insert into t1 values (2,2),(2,2),(2,2),(2,2),(2,2);
-  dec $count ;
-}
-commit;
---enable_query_log
-
-select index_length into @unpaked_keys_size from
-information_schema.tables where table_name='t1';
-alter table t1 pack_keys=1;
-select index_length into @paked_keys_size from
-information_schema.tables where table_name='t1';
-select (@unpaked_keys_size > @paked_keys_size);
-
-select max_data_length into @orig_max_data_length from
-information_schema.tables where table_name='t1';
-alter table t1 max_rows=100;
-select max_data_length into @changed_max_data_length from
-information_schema.tables where table_name='t1';
-select (@orig_max_data_length > @changed_max_data_length);
-
-drop table t1;
-
-#
-# Bug #23113: Different behavior on altering ENUM fields between 5.0 and 5.1
-#
-CREATE TABLE t1(a INT AUTO_INCREMENT PRIMARY KEY, 
-  b ENUM('a', 'b', 'c') NOT NULL);
-INSERT INTO t1 (b) VALUES ('a'), ('c'), ('b'), ('b'), ('a');
-ALTER TABLE t1 MODIFY b ENUM('a', 'z', 'b', 'c') NOT NULL;
-SELECT * FROM t1;
-DROP TABLE t1;
-
-#
-# Test for ALTER column DROP DEFAULT
-#
-
-SET @save_sql_mode=@@sql_mode;
-SET sql_mode=strict_all_tables;
-
-CREATE TABLE t1 (a int NOT NULL default 42);
-INSERT INTO t1 values ();
-SELECT * FROM t1;
-ALTER TABLE t1 ALTER COLUMN a DROP DEFAULT;
---error 1364
-INSERT INTO t1 values ();
-INSERT INTO t1 (a) VALUES (11);
-SELECT * FROM t1 ORDER BY a;
-DROP TABLE t1;
-SET @@sql_mode=@save_sql_mode;
---echo #
---echo # Bug#45567: Fast ALTER TABLE broken for enum and set
---echo #
-
---disable_warnings
-DROP TABLE IF EXISTS t1;
---enable_warnings
-
-CREATE TABLE t1 (a ENUM('a1','a2'));
-INSERT INTO t1 VALUES ('a1'),('a2');
---enable_info
---echo # No copy: No modification
-ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2');
---echo # No copy: Add new enumeration to the end
-ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a3');
---echo # Copy: Modify and add new to the end
-ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx','a5');
---echo # Copy: Remove from the end
-ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx');
---echo # Copy: Add new enumeration
-ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx');
---echo # No copy: Add new enumerations to the end
-ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx','a5','a6');
---disable_info
-DROP TABLE t1;
-
-CREATE TABLE t1 (a SET('a1','a2'));
-INSERT INTO t1 VALUES ('a1'),('a2');
---enable_info
---echo # No copy: No modification
-ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2');
---echo # No copy: Add new to the end
-ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a3');
---echo # Copy: Modify and add new to the end
-ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx','a5');
---echo # Copy: Remove from the end
-ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx');
---echo # Copy: Add new member
-ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx');
---echo # No copy: Add new to the end
-ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6');
---echo # Copy: Numerical incrase (pack lenght)
-ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6','a7','a8','a9','a10');
---disable_info
-DROP TABLE t1;
-
-#
-# Bug#43508: Renaming timestamp or date column triggers table copy
-#
-
-CREATE TABLE t1 (f1 TIMESTAMP NULL DEFAULT NULL,
-                 f2 INT(11) DEFAULT NULL) ENGINE=MYISAM DEFAULT CHARSET=utf8;
-
-INSERT INTO t1 VALUES (NULL, NULL), ("2009-10-09 11:46:19", 2);
-
---echo this should affect no rows as there is no real change
---enable_info
-ALTER TABLE t1 CHANGE COLUMN f1 f1_no_real_change TIMESTAMP NULL DEFAULT NULL;
---disable_info
-DROP TABLE t1;
-
-
---echo #
---echo # Bug #31145: ALTER TABLE DROP COLUMN, ADD COLUMN crashes (linux) 
---echo #   or freezes (win) the server
---echo #
-
-CREATE TABLE t1 (a TEXT, id INT, b INT);
-ALTER TABLE t1 DROP COLUMN a, ADD COLUMN c TEXT FIRST;
-
-DROP TABLE t1;
-
-
---echo #
---echo # Test for bug #12652385 - "61493: REORDERING COLUMNS TO POSITION
---echo #                           FIRST CAN CAUSE DATA TO BE CORRUPTED".
---echo #
---disable_warnings
-drop table if exists t1;
---enable_warnings
---echo # Use MyISAM engine as the fact that InnoDB doesn't support
---echo # in-place ALTER TABLE in cases when columns are being renamed
---echo # hides some bugs.
-create table t1 (i int, j int) engine=myisam;
-insert into t1 value (1, 2);
---echo # First, test for original problem described in the bug report.
-select * from t1;
---echo # Change of column order by the below ALTER TABLE statement should
---echo # affect both column names and column contents.
-alter table t1 modify column j int first;
-select * from t1;
---echo # Now test for similar problem with the same root.
---echo # The below ALTER TABLE should change not only the name but
---echo # also the value for the last column of the table.
-alter table t1 drop column i, add column k int default 0;
-select * from t1;
---echo # Clean-up.
-drop table t1;
-
-
---echo End of 5.1 tests
-
-#
-# Bug #31031 ALTER TABLE regression in 5.0
-#
-#  The ALTER TABLE operation failed with 
-#  ERROR 1089 (HY000): Incorrect sub part key; ...
-#
-CREATE TABLE t1(c CHAR(10), 
-  i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY);
-INSERT INTO t1 VALUES('a',2),('b',4),('c',6);
-ALTER TABLE t1
-  DROP i,
-  ADD i INT UNSIGNED NOT NULL AUTO_INCREMENT,
-  AUTO_INCREMENT = 1;
-DROP TABLE t1;
-
-
-#
-# Bug#50542 5.5.x doesn't check length of key prefixes: 
-#           corruption and crash results
-#
-# This case is related to Bug#31031 (above)
-# A statement where the index key is larger/wider than
-# the column type, should cause an error
-#
---error ER_WRONG_SUB_KEY
-CREATE TABLE t1 (a CHAR(1), PRIMARY KEY (a(255)));
-
-# Test other variants of creating indices
-CREATE TABLE t1 (a CHAR(1));
-#  ALTER TABLE
---error ER_WRONG_SUB_KEY
-ALTER TABLE t1 ADD PRIMARY KEY (a(20));
---error ER_WRONG_SUB_KEY
-ALTER TABLE t1 ADD KEY (a(20));
-#  CREATE INDEX
---error ER_WRONG_SUB_KEY
-CREATE UNIQUE INDEX i1 ON t1 (a(20));
---error ER_WRONG_SUB_KEY
-CREATE INDEX i2 ON t1 (a(20));
-# cleanup
-DROP TABLE t1;
-
-
-#
-# Bug #45052    ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns
-#   The alter table fails if 2 or more new fields added and
-#   also added a key with these fields
-#
-CREATE TABLE t1 (id int);
-INSERT INTO t1 VALUES (1), (2);
-ALTER TABLE t1 ADD COLUMN (f1 INT), ADD COLUMN (f2 INT), ADD KEY f2k(f2);
-DROP TABLE t1;
-
-
---echo #
---echo # Test for bug #53820 "ALTER a MEDIUMINT column table causes full
---echo #                      table copy".
---echo #
---disable_warnings
-DROP TABLE IF EXISTS t1;
---enable_warnings
-CREATE TABLE t1 (a INT, b MEDIUMINT);
-INSERT INTO t1 VALUES (1, 1), (2, 2);
---echo # The below ALTER should not copy table and so no rows should
---echo # be shown as affected.
---enable_info
-ALTER TABLE t1 CHANGE a id INT;
---disable_info
-DROP TABLE t1;
-
-
---echo #
---echo # Bug#11754461 CANNOT ALTER TABLE WHEN KEY PREFIX TOO LONG
---echo #
-
---disable_warnings
-DROP DATABASE IF EXISTS db1;
---enable_warnings
-
-CREATE DATABASE db1 CHARACTER SET utf8;
-CREATE TABLE db1.t1 (bar TINYTEXT, KEY (bar(100)));
-ALTER TABLE db1.t1 ADD baz INT;
-
-DROP DATABASE db1;
-
-
---echo # Additional coverage for refactoring which is made as part
---echo # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege
---echo # to allow temp table operations".
---echo #
---echo # At some point the below test case failed on assertion.
-
---disable_warnings
-DROP TABLE IF EXISTS t1;
---enable_warnings
-
-CREATE TEMPORARY TABLE t1 (i int) ENGINE=MyISAM;
-
---error ER_ILLEGAL_HA
-ALTER TABLE t1 DISCARD TABLESPACE;
-
-DROP TABLE t1;
-
-
---echo #
---echo # Bug#11938039 RE-EXECUTION OF FRM-ONLY ALTER TABLE WITH RENAME
---echo #              CLAUSE FAILS OR ABORTS SERVER.
---echo #
---disable_warnings
-drop table if exists t1;
---enable_warnings
-create table t1 (a int);
-prepare stmt1 from 'alter table t1 alter column a set default 1, rename to t2';
-execute stmt1;
-rename table t2 to t1;
---echo # The below statement should succeed and not emit error or abort server.
-execute stmt1;
-deallocate prepare stmt1;
-drop table t2;
-
---echo #
---echo # MDEV-8960 Can't refer the same column twice in one ALTER TABLE
---echo #
-
-CREATE TABLE t1 (
-  `a` int(11) DEFAULT NULL
-) DEFAULT CHARSET=utf8;
-
-ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL,
-ALTER COLUMN `consultant_id` DROP DEFAULT;
-
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (
-  `a` int(11) DEFAULT NULL
-) DEFAULT CHARSET=utf8;
-
-ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL,
-ALTER COLUMN `consultant_id` SET DEFAULT 2;
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (
-  `a` int(11) DEFAULT NULL
-) DEFAULT CHARSET=utf8;
-
-ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2,
-ALTER COLUMN `consultant_id` DROP DEFAULT;
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (
-  `a` int(11) DEFAULT NULL
-) DEFAULT CHARSET=utf8;
-
-ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2,
-ALTER COLUMN `consultant_id` DROP DEFAULT,
-MODIFY COLUMN `consultant_id` BIGINT;
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
---echo #
---echo # BUG#27788685: NO WARNING WHEN TRUNCATING A STRING WITH DATA LOSS
---echo #
-
-SET GLOBAL max_allowed_packet=17825792;
-
---connect(con1, localhost, root,,)
-CREATE TABLE t1 (t1_fld1 TEXT);
-CREATE TABLE t2 (t2_fld1 MEDIUMTEXT);
-CREATE TABLE t3 (t3_fld1 LONGTEXT);
-
-INSERT INTO t1 VALUES (REPEAT('a',300));
-INSERT INTO t2 VALUES (REPEAT('b',65680));
-INSERT INTO t3 VALUES (REPEAT('c',16777300));
-
-SELECT LENGTH(t1_fld1) FROM t1;
-SELECT LENGTH(t2_fld1) FROM t2;
-SELECT LENGTH(t3_fld1) FROM t3;
-
---echo # With strict mode
-SET SQL_MODE='STRICT_ALL_TABLES';
-
---error ER_DATA_TOO_LONG
-ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT;
---error ER_DATA_TOO_LONG
-ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT;
---error ER_DATA_TOO_LONG
-ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT;
-
---echo # With non-strict mode
-SET SQL_MODE='';
-
-ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT;
-ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT;
-ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT;
-
-SELECT LENGTH(my_t1_fld1) FROM t1;
-SELECT LENGTH(my_t2_fld1) FROM t2;
-SELECT LENGTH(my_t3_fld1) FROM t3;
-
-# Cleanup
---disconnect con1
---source include/wait_until_disconnected.inc
-
---connection default
-DROP TABLE t1, t2, t3;
-
-SET SQL_MODE=default;
-SET GLOBAL max_allowed_packet=default;
-
-#
-# Test of ALTER TABLE IF [NOT] EXISTS
-#
-
-CREATE TABLE t1 (
-  id INT(11) NOT NULL,
-  x_param INT(11) DEFAULT NULL,
-  PRIMARY KEY (id)
-) ENGINE=MYISAM;
-
-ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT,
-               ADD COLUMN IF NOT EXISTS lol INT AFTER id;
-ALTER TABLE t1 ADD COLUMN IF NOT EXISTS lol INT AFTER id;
-ALTER TABLE t1 DROP COLUMN IF EXISTS lol;
-ALTER TABLE t1 DROP COLUMN IF EXISTS lol;
-
-ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param);
-ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param);
-ALTER TABLE t1 MODIFY IF EXISTS lol INT;
-
-DROP INDEX IF EXISTS x_param ON t1;
-DROP INDEX IF EXISTS x_param ON t1;
-CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param);
-CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param);
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (
-  id INT(11) NOT NULL,
-  x_param INT(11) DEFAULT NULL,
-  PRIMARY KEY (id)
-) ENGINE=INNODB;
-
-CREATE TABLE t2 (
-  id INT(11) NOT NULL) ENGINE=INNODB;
-
-ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT,
-               ADD COLUMN IF NOT EXISTS lol INT AFTER id;
-ALTER TABLE t1 ADD COLUMN IF NOT EXISTS lol INT AFTER id;
-ALTER TABLE t1 DROP COLUMN IF EXISTS lol;
-ALTER TABLE t1 DROP COLUMN IF EXISTS lol;
-
-ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param);
-ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param);
-ALTER TABLE t1 MODIFY IF EXISTS lol INT;
-
-DROP INDEX IF EXISTS x_param ON t1;
-DROP INDEX IF EXISTS x_param ON t1;
-CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param);
-CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param);
-SHOW CREATE TABLE t1;
-
-ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS fk(id) REFERENCES t1(id);
-ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS fk(id) REFERENCES t1(id);
-ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS fk;
-ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS fk;
-SHOW CREATE TABLE t2;
-ALTER TABLE t2 ADD FOREIGN KEY (id) REFERENCES t1(id);
-ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS t2_ibfk_1(id) REFERENCES t1(id);
-ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS t2_ibfk_1;
-ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS t2_ibfk_1;
-SHOW CREATE TABLE t2;
-
-DROP TABLE t2;
-CREATE TABLE t2 (
-  id INT(11) NOT NULL);
-ALTER TABLE t2 ADD COLUMN a INT, ADD COLUMN IF NOT EXISTS a INT;
-ALTER TABLE t2 ADD KEY k_id(id), ADD KEY IF NOT EXISTS k_id(id);
-SHOW CREATE TABLE t2;
-ALTER TABLE t2 DROP KEY k_id, DROP KEY IF EXISTS k_id;
-ALTER TABLE t2 DROP COLUMN a, DROP COLUMN IF EXISTS a;
-SHOW CREATE TABLE t2;
-
-DROP TABLE t2;
-DROP TABLE t1;
-
-CREATE TABLE t1 (
-    `transaction_id` int(11) NOT NULL DEFAULT '0', 
-    KEY `transaction_id` (`transaction_id`));
-ALTER TABLE t1 DROP KEY IF EXISTS transaction_id, ADD PRIMARY KEY IF NOT EXISTS (transaction_id);
-SHOW CREATE TABLE t1;
-
-DROP TABLE t1;
-
---echo # Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't
---echo #                                         identify correct column name.
---echo #
-
-CREATE TABLE t1 (c1 int unsigned , c2 char(100) not null default '');
-ALTER TABLE t1 ADD c3 char(16) NOT NULL DEFAULT '' AFTER c2,
-               MODIFY c2 char(100) NOT NULL DEFAULT '' AFTER c1;
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
---echo #
---echo # WL#5534 Online ALTER, Phase 1
---echo #
-
---echo # Single thread tests.
---echo # See innodb_mysql_sync.test for multi thread tests.
-
---disable_warnings
-DROP TABLE IF EXISTS t1;
---enable_warnings
-
-CREATE TABLE t1(a INT PRIMARY KEY, b INT) engine=InnoDB;
-CREATE TABLE m1(a INT PRIMARY KEY, b INT) engine=MyISAM;
-INSERT INTO t1 VALUES (1,1), (2,2);
-INSERT INTO m1 VALUES (1,1), (2,2);
-
---echo #
---echo # 1: Test ALGORITHM keyword
---echo #
-
---echo # --enable_info allows us to see how many rows were updated
---echo # by ALTER TABLE. in-place will show 0 rows, while copy > 0.
-
---enable_info
-ALTER TABLE t1 ADD INDEX i1(b);
-ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT;
-ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY;
-ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE;
---error ER_UNKNOWN_ALTER_ALGORITHM
-ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= INVALID;
-
-ALTER TABLE m1 ENABLE KEYS;
-ALTER TABLE m1 ENABLE KEYS, ALGORITHM= DEFAULT;
-ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY;
-ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE;
---disable_info
-
-ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4;
-
---echo #
---echo # 2: Test ALGORITHM + old_alter_table
---echo #
-
---enable_info
-SET SESSION old_alter_table= 1;
-ALTER TABLE t1 ADD INDEX i1(b);
-ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT;
-ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY;
-ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE;
-SET SESSION old_alter_table= 0;
---disable_info
-
-ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4;
-
---echo #
---echo # 3: Test unsupported in-place operation
---echo #
-
-ALTER TABLE t1 ADD COLUMN (c1 INT);
-ALTER TABLE t1 ADD COLUMN (c2 INT), ALGORITHM= DEFAULT;
-ALTER TABLE t1 ADD COLUMN (c3 INT), ALGORITHM= COPY;
-ALTER TABLE t1 ADD COLUMN (c4 INT), ALGORITHM= INPLACE;
-
-ALTER TABLE t1 DROP COLUMN c1, DROP COLUMN c2, DROP COLUMN c3, DROP COLUMN c4;
-
---echo #
---echo # 4: Test LOCK keyword
---echo #
-
---enable_info
-ALTER TABLE t1 ADD INDEX i1(b), LOCK= DEFAULT;
-ALTER TABLE t1 ADD INDEX i2(b), LOCK= NONE;
-ALTER TABLE t1 ADD INDEX i3(b), LOCK= SHARED;
-ALTER TABLE t1 ADD INDEX i4(b), LOCK= EXCLUSIVE;
---error ER_UNKNOWN_ALTER_LOCK
-ALTER TABLE t1 ADD INDEX i5(b), LOCK= INVALID;
---disable_info
-
-ALTER TABLE m1 ENABLE KEYS, LOCK= DEFAULT;
---error ER_ALTER_OPERATION_NOT_SUPPORTED
-ALTER TABLE m1 ENABLE KEYS, LOCK= NONE;
---error ER_ALTER_OPERATION_NOT_SUPPORTED
-ALTER TABLE m1 ENABLE KEYS, LOCK= SHARED;
-ALTER TABLE m1 ENABLE KEYS, LOCK= EXCLUSIVE;
-
-ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4;
-
---echo #
---echo # 5: Test ALGORITHM + LOCK
---echo #
-
---enable_info
-ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= NONE;
-ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= INPLACE, LOCK= SHARED;
-ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE;
---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
-ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= COPY, LOCK= NONE;
-ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= COPY, LOCK= SHARED;
-ALTER TABLE t1 ADD INDEX i6(b), ALGORITHM= COPY, LOCK= EXCLUSIVE;
-
---error ER_ALTER_OPERATION_NOT_SUPPORTED
-ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= NONE;
---error ER_ALTER_OPERATION_NOT_SUPPORTED
-ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= SHARED;
-ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= EXCLUSIVE;
---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
-ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= NONE;
-# This works because the lock will be SNW for the copy phase.
-# It will still require exclusive lock for actually enabling keys.
-ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= SHARED;
-ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= EXCLUSIVE;
---disable_info
-
-DROP TABLE t1, m1;
-
---echo #
---echo # 6: Possible deadlock involving thr_lock.c
---echo #
-
-CREATE TABLE t1(a INT PRIMARY KEY, b INT);
-INSERT INTO t1 VALUES (1,1), (2,2);
-
-START TRANSACTION;
-INSERT INTO t1 VALUES (3,3);
-
---echo # Connection con1
-connect (con1, localhost, root);
---echo # Sending:
---send ALTER TABLE t1 DISABLE KEYS
-
---echo # Connection default
-connection default;
---echo # Waiting until ALTER TABLE is blocked.
-let $wait_condition=
-  SELECT COUNT(*) = 1 FROM information_schema.processlist
-  WHERE state = "Waiting for table metadata lock" AND
-        info = "ALTER TABLE t1 DISABLE KEYS";
---source include/wait_condition.inc
-UPDATE t1 SET b = 4;
-COMMIT;
-
---echo # Connection con1
-connection con1;
---echo # Reaping: ALTER TABLE t1 DISABLE KEYS
---reap
-disconnect con1;
---source include/wait_until_disconnected.inc
-
---echo # Connection default
-connection default;
-DROP TABLE t1;
-
---echo #
---echo # 7: Which operations require copy and which can be done in-place?
---echo #
---echo # Test which ALTER TABLE operations are done in-place and
---echo # which operations are done using temporary table copy.
---echo #
---echo # --enable_info allows us to see how many rows were updated
---echo # by ALTER TABLE. in-place will show 0 rows, while copy > 0.
---echo #
-
---disable_warnings
-DROP TABLE IF EXISTS ti1, ti2, ti3, tm1, tm2, tm3;
---enable_warnings
-
---echo # Single operation tests
-
-CREATE TABLE ti1(a INT NOT NULL, b INT, c INT) engine=InnoDB;
-CREATE TABLE tm1(a INT NOT NULL, b INT, c INT) engine=MyISAM;
-CREATE TABLE ti2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=InnoDB;
-CREATE TABLE tm2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=MyISAM;
-INSERT INTO ti1 VALUES (1,1,1), (2,2,2);
-INSERT INTO ti2 VALUES (1,1,1), (2,2,2);
-INSERT INTO tm1 VALUES (1,1,1), (2,2,2);
-INSERT INTO tm2 VALUES (1,1,1), (2,2,2);
-
---enable_info
-ALTER TABLE ti1;
-ALTER TABLE tm1;
-
-ALTER TABLE ti1 ADD COLUMN d VARCHAR(200);
-ALTER TABLE tm1 ADD COLUMN d VARCHAR(200);
-ALTER TABLE ti1 ADD COLUMN d2 VARCHAR(200);
-ALTER TABLE tm1 ADD COLUMN d2 VARCHAR(200);
-ALTER TABLE ti1 ADD COLUMN e ENUM('a', 'b') FIRST;
-ALTER TABLE tm1 ADD COLUMN e ENUM('a', 'b') FIRST;
-ALTER TABLE ti1 ADD COLUMN f INT AFTER a;
-ALTER TABLE tm1 ADD COLUMN f INT AFTER a;
-
-ALTER TABLE ti1 ADD INDEX ii1(b);
-ALTER TABLE tm1 ADD INDEX im1(b);
-ALTER TABLE ti1 ADD UNIQUE INDEX ii2 (c);
-ALTER TABLE tm1 ADD UNIQUE INDEX im2 (c);
-ALTER TABLE ti1 ADD FULLTEXT INDEX ii3 (d);
-ALTER TABLE tm1 ADD FULLTEXT INDEX im3 (d);
-ALTER TABLE ti1 ADD FULLTEXT INDEX ii4 (d2);
-ALTER TABLE tm1 ADD FULLTEXT INDEX im4 (d2);
-
-# Bug#14140038 INCONSISTENT HANDLING OF FULLTEXT INDEXES IN ALTER TABLE
---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
-ALTER TABLE ti1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
-ALTER TABLE ti1 ADD PRIMARY KEY(a);
-ALTER TABLE tm1 ADD PRIMARY KEY(a);
-
-ALTER TABLE ti1 DROP INDEX ii3;
-ALTER TABLE tm1 DROP INDEX im3;
-
-ALTER TABLE ti1 DROP COLUMN d2;
-ALTER TABLE tm1 DROP COLUMN d2;
-
-ALTER TABLE ti1 ADD CONSTRAINT fi1 FOREIGN KEY (b) REFERENCES ti2(a);
-ALTER TABLE tm1 ADD CONSTRAINT fm1 FOREIGN KEY (b) REFERENCES tm2(a);
-
-ALTER TABLE ti1 ALTER COLUMN b SET DEFAULT 1;
-ALTER TABLE tm1 ALTER COLUMN b SET DEFAULT 1;
-ALTER TABLE ti1 ALTER COLUMN b DROP DEFAULT;
-ALTER TABLE tm1 ALTER COLUMN b DROP DEFAULT;
-
-# This will set both ALTER_COLUMN_NAME and COLUMN_DEFAULT_VALUE
-ALTER TABLE ti1 CHANGE COLUMN f g INT;
-ALTER TABLE tm1 CHANGE COLUMN f g INT;
-ALTER TABLE ti1 CHANGE COLUMN g h VARCHAR(20);
-ALTER TABLE tm1 CHANGE COLUMN g h VARCHAR(20);
-ALTER TABLE ti1 MODIFY COLUMN e ENUM('a', 'b', 'c');
-ALTER TABLE tm1 MODIFY COLUMN e ENUM('a', 'b', 'c');
-ALTER TABLE ti1 MODIFY COLUMN e INT;
-ALTER TABLE tm1 MODIFY COLUMN e INT;
-# This will set both ALTER_COLUMN_ORDER and COLUMN_DEFAULT_VALUE
-ALTER TABLE ti1 MODIFY COLUMN e INT AFTER h;
-ALTER TABLE tm1 MODIFY COLUMN e INT AFTER h;
-ALTER TABLE ti1 MODIFY COLUMN e INT FIRST;
-ALTER TABLE tm1 MODIFY COLUMN e INT FIRST;
-# This will set both ALTER_COLUMN_NOT_NULLABLE and COLUMN_DEFAULT_VALUE
---disable_info
-# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
-SET @orig_sql_mode = @@sql_mode;
-SET @@sql_mode = 'STRICT_TRANS_TABLES';
---enable_info
-ALTER TABLE ti1 MODIFY COLUMN c INT NOT NULL;
---disable_info
-SET @@sql_mode = @orig_sql_mode;
---enable_info
-ALTER TABLE tm1 MODIFY COLUMN c INT NOT NULL;
-# This will set both ALTER_COLUMN_NULLABLE and COLUMN_DEFAULT_VALUE
-ALTER TABLE ti1 MODIFY COLUMN c INT NULL;
-ALTER TABLE tm1 MODIFY COLUMN c INT NULL;
-# This will set both ALTER_COLUMN_EQUAL_PACK_LENGTH and COLUMN_DEFAULT_VALUE
-ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30);
-ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30);
-ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30) AFTER d;
-ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30) AFTER d;
-
-ALTER TABLE ti1 DROP COLUMN h;
-ALTER TABLE tm1 DROP COLUMN h;
-
-ALTER TABLE ti1 DROP INDEX ii2;
-ALTER TABLE tm1 DROP INDEX im2;
-ALTER TABLE ti1 DROP PRIMARY KEY;
-ALTER TABLE tm1 DROP PRIMARY KEY;
-
-ALTER TABLE ti1 DROP FOREIGN KEY fi1;
-ALTER TABLE tm1 DROP FOREIGN KEY fm1;
-
-ALTER TABLE ti1 RENAME TO ti3;
-ALTER TABLE tm1 RENAME TO tm3;
-ALTER TABLE ti3 RENAME TO ti1;
-ALTER TABLE tm3 RENAME TO tm1;
-
-ALTER TABLE ti1 ORDER BY b;
-ALTER TABLE tm1 ORDER BY b;
-
-ALTER TABLE ti1 CONVERT TO CHARACTER SET utf16;
-ALTER TABLE tm1 CONVERT TO CHARACTER SET utf16;
-ALTER TABLE ti1 DEFAULT CHARACTER SET utf8;
-ALTER TABLE tm1 DEFAULT CHARACTER SET utf8;
-
-ALTER TABLE ti1 FORCE;
-ALTER TABLE tm1 FORCE;
-
-ALTER TABLE ti1 AUTO_INCREMENT 3;
-ALTER TABLE tm1 AUTO_INCREMENT 3;
-ALTER TABLE ti1 AVG_ROW_LENGTH 10;
-ALTER TABLE tm1 AVG_ROW_LENGTH 10;
-ALTER TABLE ti1 CHECKSUM 1;
-ALTER TABLE tm1 CHECKSUM 1;
-ALTER TABLE ti1 COMMENT 'test';
-ALTER TABLE tm1 COMMENT 'test';
-ALTER TABLE ti1 MAX_ROWS 100;
-ALTER TABLE tm1 MAX_ROWS 100;
-ALTER TABLE ti1 MIN_ROWS 1;
-ALTER TABLE tm1 MIN_ROWS 1;
-ALTER TABLE ti1 PACK_KEYS 1;
-ALTER TABLE tm1 PACK_KEYS 1;
-
---disable_info
-DROP TABLE ti1, ti2, tm1, tm2;
-
---echo # Tests of >1 operation (InnoDB)
-
-CREATE TABLE ti1(a INT PRIMARY KEY AUTO_INCREMENT, b INT) engine=InnoDB;
-INSERT INTO ti1(b) VALUES (1), (2);
-
---enable_info
-ALTER TABLE ti1 RENAME TO ti3, ADD INDEX ii1(b);
-
-ALTER TABLE ti3 DROP INDEX ii1, AUTO_INCREMENT 5;
---disable_info
-INSERT INTO ti3(b) VALUES (5);
---enable_info
-ALTER TABLE ti3 ADD INDEX ii1(b), AUTO_INCREMENT 7;
---disable_info
-INSERT INTO ti3(b) VALUES (7);
-SELECT * FROM ti3;
-
-DROP TABLE ti3;
-
---echo #
---echo # 8: Scenario in which ALTER TABLE was returning an unwarranted
---echo #    ER_ILLEGAL_HA error at some point during work on this WL.
---echo #
-
-CREATE TABLE tm1(i INT DEFAULT 1) engine=MyISAM;
-ALTER TABLE tm1 ADD INDEX ii1(i), ALTER COLUMN i DROP DEFAULT;
-DROP TABLE tm1;
-
-#
-# MDEV-4435 Server crashes in my_strcasecmp_utf8 on ADD KEY IF NOT EXISTS with implicit name when the key exists.
-#
-create table if not exists t1 (i int);
-alter table t1 add key (i);
-alter table t1 add key if not exists (i);
-DROP TABLE t1;
-
-#
-# MDEV-4436 CHANGE COLUMN IF EXISTS does not work and throws wrong warning.
-#
-create table t1 (a int);
-alter table t1 change column if exists a b bigint;
-show create table t1;
-DROP TABLE t1;
-
-#
-# MDEV-4437 ALTER TABLE .. ADD UNIQUE INDEX IF NOT EXISTS causes syntax error.
-#
-
-create table t1 (i int);
-alter table t1 add unique index if not exists idx(i);
-alter table t1 add unique index if not exists idx(i);
-show create table t1;
-DROP TABLE t1;
-
-#
-# MDEV-8358 ADD PRIMARY KEY IF NOT EXISTS -> ERROR 1068 (42000): Multiple primary key
-#
-
-CREATE TABLE t1 (
-   `event_id` bigint(20) unsigned NOT NULL DEFAULT '0',
-   `market_id` bigint(20) unsigned NOT NULL DEFAULT '0',
- PRIMARY KEY (`event_id`,`market_id`)
- );
-ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS event_id (event_id,market_id);
-DROP TABLE t1;
-
---echo #
---echo # MDEV-11126 Crash while altering persistent virtual column
---echo #
-
-CREATE TABLE `tab1` (
-  `id` bigint(20) NOT NULL AUTO_INCREMENT,
-  `field2` set('option1','option2','option3','option4') NOT NULL,
-  `field3` set('option1','option2','option3','option4','option5') NOT NULL,
-  `field4` set('option1','option2','option3','option4') NOT NULL,
-  `field5` varchar(32) NOT NULL,
-  `field6` varchar(32) NOT NULL,
-  `field7` varchar(32) NOT NULL,
-  `field8` varchar(32) NOT NULL,
-  `field9` int(11) NOT NULL DEFAULT '1',
-  `field10` varchar(16) NOT NULL,
-  `field11` enum('option1','option2','option3') NOT NULL DEFAULT 'option1',
-  `v_col` varchar(128) AS (IF(field11='option1',CONCAT_WS(":","field1",field2,field3,field4,field5,field6,field7,field8,field9,field10), CONCAT_WS(":","field1",field11,field2,field3,field4,field5,field6,field7,field8,field9,field10))) PERSISTENT,
-  PRIMARY KEY (`id`)
-) DEFAULT CHARSET=latin1;
-
-ALTER TABLE `tab1` CHANGE COLUMN v_col `v_col` varchar(128);
-SHOW CREATE TABLE `tab1`;
-ALTER TABLE `tab1` CHANGE COLUMN v_col `v_col` varchar(128) AS (IF(field11='option1',CONCAT_WS(":","field1",field2,field3,field4,field5,field6,field7,field8,field9,field10), CONCAT_WS(":","field1",field11,field2,field3,field4,field5,field6,field7,field8,field9,field10))) PERSISTENT;
-SHOW CREATE TABLE `tab1`;
-DROP TABLE `tab1`;
-
---echo #
---echo # MDEV-11548 Reproducible server crash after the 2nd ALTER TABLE ADD FOREIGN KEY IF NOT EXISTS
---echo #
- 
-CREATE TABLE t1 (id INT UNSIGNED NOT NULL PRIMARY KEY);
-CREATE TABLE t2 (id1 INT UNSIGNED NOT NULL);
- 
-ALTER TABLE t2
-ADD FOREIGN KEY IF NOT EXISTS (id1)
-  REFERENCES t1 (id);
-   
-ALTER TABLE t2
-ADD FOREIGN KEY IF NOT EXISTS (id1)
-REFERENCES t1 (id);
-
-DROP TABLE t2;
-DROP TABLE t1;
-
-
---echo #
---echo # MDEV-6390 CONVERT TO CHARACTER SET utf8 doesn't change DEFAULT CHARSET.
---echo #
- 
-CREATE TABLE t1 (id int(11) NOT NULL, a int(11) NOT NULL, b int(11))
-  ENGINE=InnoDB DEFAULT CHARSET=latin1;
-SHOW CREATE TABLE t1;
-ALTER TABLE t1 CONVERT TO CHARACTER SET utf8;
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
---echo #
---echo #
---echo # MDEV-15308
---echo # Assertion `ha_alter_info->alter_info->drop_list.elements > 0' failed
---echo # in ha_innodb::prepare_inplace_alter_table
---echo #
-
-CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB;
-ALTER TABLE t1 DROP FOREIGN KEY IF EXISTS fk, DROP COLUMN b;
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB;
-ALTER TABLE t1 DROP INDEX IF EXISTS fk, DROP COLUMN b;
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c INT, KEY(c)) ENGINE=InnoDB;
-ALTER TABLE t1 DROP FOREIGN KEY IF EXISTS fk, DROP COLUMN c;
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c INT, KEY c1(c)) ENGINE=InnoDB;
-ALTER TABLE t1 DROP FOREIGN KEY IF EXISTS fk, DROP INDEX c1;
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB;
-ALTER TABLE t1 DROP INDEX IF EXISTS fk, DROP COLUMN IF EXISTS c;
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
---echo #
---echo # MDEV-14668 ADD PRIMARY KEY IF NOT EXISTS on composite key
---echo #
-CREATE TABLE t1 (
-        `ID` BIGINT(20) NOT NULL,
-            `RANK` MEDIUMINT(4) NOT NULL,
-                `CHECK_POINT` BIGINT(20) NOT NULL,
-                    UNIQUE INDEX `HORIZON_UIDX01` (`ID`, `RANK`)
-    ) ENGINE=InnoDB;
-
-ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`);
-SHOW CREATE TABLE t1;
-ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`);
-DROP TABLE t1;
diff --git a/mysql-test/r/grant.test b/mysql-test/r/grant.test
deleted file mode 100644
index f2dfb01cc39..00000000000
--- a/mysql-test/r/grant.test
+++ /dev/null
@@ -1,2291 +0,0 @@
-# Test of GRANT commands
-
-# Grant tests not performed with embedded server
--- source include/not_embedded.inc
-
-# Save the initial number of concurrent sessions
---source include/count_sessions.inc
-
-SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
-SET GLOBAL log_bin_trust_function_creators = 1;
-
-# Cleanup
---disable_warnings
-drop table if exists t1;
-drop database if exists mysqltest;
---enable_warnings
-
-connect (master,localhost,root,,);
-connection master;
-SET NAMES binary;
-
-#
-# Test that SSL options works properly
-#
-
-delete from mysql.user where user='mysqltest_1';
-delete from mysql.db where user='mysqltest_1';
-flush privileges;
-grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA";
-show grants for mysqltest_1@localhost;
-grant delete on mysqltest.* to mysqltest_1@localhost;
-query_vertical select * from mysql.user where user="mysqltest_1";
-show grants for mysqltest_1@localhost;
-revoke delete on mysqltest.* from mysqltest_1@localhost;
-show grants for mysqltest_1@localhost;
-grant select on mysqltest.* to mysqltest_1@localhost require NONE;
-show grants for mysqltest_1@localhost;
-grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "Monty Program Ab";
-show grants for mysqltest_1@localhost;
-revoke all privileges on mysqltest.* from mysqltest_1@localhost;
-show grants for mysqltest_1@localhost;
-delete from mysql.user where user='mysqltest_1';
-flush privileges;
-
-#
-# Test of GRANTS specifying user limits
-#
-delete from mysql.user where user='mysqltest_1';
-flush privileges;
-grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 10;
-query_vertical select * from mysql.user where user="mysqltest_1";
-show grants for mysqltest_1@localhost;
-grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30;
-query_vertical select * from mysql.user where user="mysqltest_1";
-show grants for mysqltest_1@localhost;
-# This is just to double check that one won't ignore results of selects
-flush privileges;
-show grants for mysqltest_1@localhost;
-delete from mysql.user where user='mysqltest_1';
-flush privileges;
-
-#
-# Test that the new db privileges are stored/retrieved correctly
-#
-
-grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost;
-show grants for mysqltest_1@localhost;
-flush privileges;
-show grants for mysqltest_1@localhost;
-revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost;
-show grants for mysqltest_1@localhost;
-grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION;
-flush privileges;
-show grants for mysqltest_1@localhost;
-revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost;
-show grants for mysqltest_1@localhost;
-revoke all privileges on mysqltest.* from mysqltest_1@localhost;
-delete from mysql.user where user='mysqltest_1';
-flush privileges;
-grant usage on test.* to mysqltest_1@localhost with grant option;
-show grants for mysqltest_1@localhost;
-delete from mysql.user where user='mysqltest_1';
-delete from mysql.db where user='mysqltest_1';
-delete from mysql.tables_priv where user='mysqltest_1';
-delete from mysql.columns_priv where user='mysqltest_1';
-flush privileges;
---error ER_NONEXISTING_GRANT
-show grants for mysqltest_1@localhost;
-
-#
-# Test what happens when you have same table and colum level grants
-#
-
-create table t1 (a int);
-GRANT select,update,insert on t1 to mysqltest_1@localhost;
-GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost;
-show grants for mysqltest_1@localhost;
-select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
-REVOKE select (a), update on t1 from mysqltest_1@localhost;
-show grants for mysqltest_1@localhost;
-REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost;
-show grants for mysqltest_1@localhost;
-GRANT select,references on t1 to mysqltest_1@localhost;
-select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
-grant all on test.* to mysqltest_3@localhost with grant option;
-revoke all on test.* from mysqltest_3@localhost;
-show grants for mysqltest_3@localhost;
-revoke grant option on test.* from mysqltest_3@localhost;
-show grants for mysqltest_3@localhost;
-grant all on test.t1 to mysqltest_2@localhost with grant option;
-revoke all on test.t1 from mysqltest_2@localhost;
-show grants for mysqltest_2@localhost;
-revoke grant option on test.t1 from mysqltest_2@localhost;
-show grants for mysqltest_2@localhost;
-delete from mysql.user where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
-delete from mysql.db where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
-delete from mysql.tables_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
-delete from mysql.columns_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
-flush privileges;
-drop table t1;
-
-#
-# Test some error conditions
-#
---error ER_WRONG_USAGE
-GRANT FILE on mysqltest.*  to mysqltest_1@localhost;
-select 1;     # To test that the previous command didn't cause problems
-
-#
-# Bug#4898 User privileges depending on ORDER BY Settings of table db
-#
-insert into mysql.user (host, user) values ('localhost', 'test11');
-insert into mysql.db (host, db, user, select_priv) values
-('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y');
-alter table mysql.db order by db asc;
-flush privileges;
-show grants for test11@localhost;
-alter table mysql.db order by db desc;
-flush privileges;
-show grants for test11@localhost;
-delete from mysql.user where user='test11';
-delete from mysql.db where user='test11';
-
-#
-# Bug#6123 GRANT USAGE inserts useless Db row
-#
-create database mysqltest1;
-grant usage on mysqltest1.* to test6123 identified by 'magic123';
-select host,db,user,select_priv,insert_priv from mysql.db where db="mysqltest1";
-delete from mysql.user where user='test6123';
-drop database mysqltest1;
-
-#
-# Test for 'drop user', 'revoke privileges, grant'
-#
-
-create table t1 (a int);
-grant ALL PRIVILEGES on *.* to drop_user2@localhost with GRANT OPTION;
-show grants for drop_user2@localhost;
-revoke all privileges, grant option from drop_user2@localhost;
-drop user drop_user2@localhost;
-
-grant ALL PRIVILEGES on *.* to drop_user@localhost with GRANT OPTION;
-grant ALL PRIVILEGES on test.* to drop_user@localhost with GRANT OPTION;
-grant select(a) on test.t1 to drop_user@localhost;
-show grants for drop_user@localhost;
-
-#
-# Bug#3086 SHOW GRANTS doesn't follow ANSI_QUOTES
-#
-set sql_mode=ansi_quotes;
-show grants for drop_user@localhost;
-set sql_mode=default;
-
-set sql_quote_show_create=0;
-show grants for drop_user@localhost;
-set sql_mode="ansi_quotes";
-show grants for drop_user@localhost;
-set sql_quote_show_create=1;
-show grants for drop_user@localhost;
-set sql_mode="";
-show grants for drop_user@localhost;
-
-revoke all privileges, grant option from drop_user@localhost;
-show grants for drop_user@localhost;
-drop user drop_user@localhost;
---error ER_REVOKE_GRANTS
-revoke all privileges, grant option from drop_user@localhost;
-
-grant select(a) on test.t1 to drop_user1@localhost;
-grant select on test.t1 to drop_user2@localhost;
-grant select on test.* to drop_user3@localhost;
-grant select on *.* to drop_user4@localhost;
-# Drop user now implicitly revokes all privileges.
-drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,
-drop_user4@localhost;
---error ER_REVOKE_GRANTS
-revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost,
-drop_user3@localhost, drop_user4@localhost;
---error ER_CANNOT_USER
-drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,
-drop_user4@localhost;
-drop table t1;
-grant usage on *.* to mysqltest_1@localhost identified by "password";
-grant select, update, insert on test.* to mysqltest_1@localhost;
-show grants for mysqltest_1@localhost;
-drop user mysqltest_1@localhost;
-
-#
-# Bug#3403 Wrong encoding in SHOW GRANTS output
-#
-SET NAMES koi8r;
-CREATE DATABASE ��;
-USE ��;
-CREATE TABLE ��� (��� INT);
-
-GRANT SELECT ON ��.* TO ����@localhost;
-SHOW GRANTS FOR ����@localhost;
-REVOKE SELECT ON ��.* FROM ����@localhost;
-
-GRANT SELECT ON ��.��� TO ����@localhost;
-SHOW GRANTS FOR ����@localhost;
-REVOKE SELECT ON ��.��� FROM ����@localhost;
-
-GRANT SELECT (���) ON ��.��� TO ����@localhost;
-SHOW GRANTS FOR ����@localhost;
-REVOKE SELECT (���) ON ��.��� FROM ����@localhost;
-
-# Revoke does not drop user. Leave a clean user table for the next tests.
-DROP USER ����@localhost;
-
-DROP DATABASE ��;
-SET NAMES latin1;
-
-#
-# Bug#5831 REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke everything
-#
-USE test;
-CREATE TABLE t1 (a int );
-CREATE TABLE t2 LIKE t1;
-CREATE TABLE t3 LIKE t1;
-CREATE TABLE t4 LIKE t1;
-CREATE TABLE t5 LIKE t1;
-CREATE TABLE t6 LIKE t1;
-CREATE TABLE t7 LIKE t1;
-CREATE TABLE t8 LIKE t1;
-CREATE TABLE t9 LIKE t1;
-CREATE TABLE t10 LIKE t1;
-CREATE DATABASE testdb1;
-CREATE DATABASE testdb2;
-CREATE DATABASE testdb3;
-CREATE DATABASE testdb4;
-CREATE DATABASE testdb5;
-CREATE DATABASE testdb6;
-CREATE DATABASE testdb7;
-CREATE DATABASE testdb8;
-CREATE DATABASE testdb9;
-CREATE DATABASE testdb10;
-GRANT ALL ON testdb1.* TO testuser@localhost;
-GRANT ALL ON testdb2.* TO testuser@localhost;
-GRANT ALL ON testdb3.* TO testuser@localhost;
-GRANT ALL ON testdb4.* TO testuser@localhost;
-GRANT ALL ON testdb5.* TO testuser@localhost;
-GRANT ALL ON testdb6.* TO testuser@localhost;
-GRANT ALL ON testdb7.* TO testuser@localhost;
-GRANT ALL ON testdb8.* TO testuser@localhost;
-GRANT ALL ON testdb9.* TO testuser@localhost;
-GRANT ALL ON testdb10.* TO testuser@localhost;
-GRANT SELECT ON test.t1 TO testuser@localhost;
-GRANT SELECT ON test.t2 TO testuser@localhost;
-GRANT SELECT ON test.t3 TO testuser@localhost;
-GRANT SELECT ON test.t4 TO testuser@localhost;
-GRANT SELECT ON test.t5 TO testuser@localhost;
-GRANT SELECT ON test.t6 TO testuser@localhost;
-GRANT SELECT ON test.t7 TO testuser@localhost;
-GRANT SELECT ON test.t8 TO testuser@localhost;
-GRANT SELECT ON test.t9 TO testuser@localhost;
-GRANT SELECT ON test.t10 TO testuser@localhost;
-GRANT SELECT (a) ON test.t1 TO testuser@localhost;
-GRANT SELECT (a) ON test.t2 TO testuser@localhost;
-GRANT SELECT (a) ON test.t3 TO testuser@localhost;
-GRANT SELECT (a) ON test.t4 TO testuser@localhost;
-GRANT SELECT (a) ON test.t5 TO testuser@localhost;
-GRANT SELECT (a) ON test.t6 TO testuser@localhost;
-GRANT SELECT (a) ON test.t7 TO testuser@localhost;
-GRANT SELECT (a) ON test.t8 TO testuser@localhost;
-GRANT SELECT (a) ON test.t9 TO testuser@localhost;
-GRANT SELECT (a) ON test.t10 TO testuser@localhost;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser@localhost;
-SHOW GRANTS FOR testuser@localhost;
-DROP USER testuser@localhost;
-DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10;
-DROP DATABASE testdb1;
-DROP DATABASE testdb2;
-DROP DATABASE testdb3;
-DROP DATABASE testdb4;
-DROP DATABASE testdb5;
-DROP DATABASE testdb6;
-DROP DATABASE testdb7;
-DROP DATABASE testdb8;
-DROP DATABASE testdb9;
-DROP DATABASE testdb10;
-
-#
-# Bug#6932 a problem with 'revoke ALL PRIVILEGES'
-#
-
-create table t1(a int, b int, c int, d int);
-grant insert(b), insert(c), insert(d), insert(a) on t1 to grant_user@localhost;
-show grants for grant_user@localhost;
-select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv order by Column_name;
-revoke ALL PRIVILEGES on t1 from grant_user@localhost;
-show grants for grant_user@localhost;
-select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv;
-drop user grant_user@localhost;
-drop table t1;
-
-#
-# Bug#7391 Cross-database multi-table UPDATE security problem
-#
-create database mysqltest_1;
-create database mysqltest_2;
-create table mysqltest_1.t1 select 1 a, 2 q;
-create table mysqltest_1.t2 select 1 b, 2 r;
-create table mysqltest_2.t1 select 1 c, 2 s;
-create table mysqltest_2.t2 select 1 d, 2 t;
-
-# test the column privileges
-grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost;
-grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost;
-grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost;
-grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost;
-connect (conn1,localhost,mysqltest_3,,);
-connection conn1;
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
- WHERE GRANTEE = '''mysqltest_3''@''localhost'''
- ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
- WHERE GRANTEE = '''mysqltest_3''@''localhost'''
- ORDER BY TABLE_NAME,PRIVILEGE_TYPE;
-SELECT * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
- WHERE GRANTEE = '''mysqltest_3''@''localhost'''
- ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE;
-SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES
- WHERE GRANTEE = '''mysqltest_3''@''localhost'''
- ORDER BY TABLE_CATALOG,PRIVILEGE_TYPE;
---error ER_COLUMNACCESS_DENIED_ERROR
-update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1;
---error ER_COLUMNACCESS_DENIED_ERROR
-update mysqltest_1.t2, mysqltest_2.t2 set d=20 where d=1;
---error ER_TABLEACCESS_DENIED_ERROR
-update mysqltest_1.t1, mysqltest_2.t2 set d=20 where d=1;
---error ER_TABLEACCESS_DENIED_ERROR
-update mysqltest_2.t1, mysqltest_1.t2 set c=20 where b=1;
---error ER_COLUMNACCESS_DENIED_ERROR
-update mysqltest_2.t1, mysqltest_2.t2 set d=10 where s=2;
-# the following two should work
-update mysqltest_1.t1, mysqltest_2.t2 set a=10,d=10;
-update mysqltest_1.t1, mysqltest_2.t1 set a=20 where c=20;
-connection master;
-select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2;
-select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2;
-revoke all on mysqltest_1.t1 from mysqltest_3@localhost;
-revoke all on mysqltest_1.t2 from mysqltest_3@localhost;
-revoke all on mysqltest_2.t1 from mysqltest_3@localhost;
-revoke all on mysqltest_2.t2 from mysqltest_3@localhost;
-
-# test the db/table level privileges
-grant all on mysqltest_2.* to mysqltest_3@localhost;
-grant select on *.* to mysqltest_3@localhost;
-# Next grant is needed to trigger bug#7391. Do not optimize!
-grant select on mysqltest_2.t1 to mysqltest_3@localhost;
-flush privileges;
-disconnect conn1;
-connect (conn2,localhost,mysqltest_3,,);
-connection conn2;
-use mysqltest_1;
-update mysqltest_2.t1, mysqltest_2.t2 set c=500,d=600;
-# the following failed before, should fail now.
---error ER_TABLEACCESS_DENIED_ERROR
-update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200;
-use mysqltest_2;
-# the following used to succeed, it must fail now.
---error ER_TABLEACCESS_DENIED_ERROR
-update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200;
---error ER_TABLEACCESS_DENIED_ERROR
-update mysqltest_2.t1, mysqltest_1.t2 set c=100,b=200;
---error ER_TABLEACCESS_DENIED_ERROR
-update mysqltest_1.t1, mysqltest_2.t2 set a=100,d=200;
-# lets see the result
-connection master;
-select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2;
-select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2;
-
-delete from mysql.user where user='mysqltest_3';
-delete from mysql.db where user="mysqltest_3";
-delete from mysql.tables_priv where user="mysqltest_3";
-delete from mysql.columns_priv where user="mysqltest_3";
-flush privileges;
-drop database mysqltest_1;
-drop database mysqltest_2;
-disconnect conn2;
-
-#
-# just SHOW PRIVILEGES test
-#
-SHOW PRIVILEGES;
-
-#
-# Rights for renaming test (Bug#3270)
-#
-connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
-connection root;
---disable_warnings
-create database mysqltest;
---enable_warnings
-create table mysqltest.t1 (a int,b int,c int);
-grant all on mysqltest.t1 to mysqltest_1@localhost;
-connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);
-connection user1;
--- error ER_TABLEACCESS_DENIED_ERROR
-alter table t1 rename t2;
-disconnect user1;
-connection root;
-revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
-delete from mysql.user where user=_binary'mysqltest_1';
-drop database mysqltest;
-connection default;
-disconnect root;
-
-#
-# check all new table privileges
-#
-CREATE USER dummy@localhost;
-CREATE DATABASE mysqltest;
-CREATE TABLE mysqltest.dummytable (dummyfield INT);
-CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable;
-GRANT ALL PRIVILEGES ON mysqltest.dummytable TO dummy@localhost;
-GRANT ALL PRIVILEGES ON mysqltest.dummyview TO dummy@localhost;
-SHOW GRANTS FOR dummy@localhost;
-use INFORMATION_SCHEMA;
-SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
-PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
-= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
-FLUSH PRIVILEGES;
-SHOW GRANTS FOR dummy@localhost;
-SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
-PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
-= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
-SHOW FIELDS FROM mysql.tables_priv;
-use test;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost;
-DROP USER dummy@localhost;
-DROP DATABASE mysqltest;
-# check view only privileges
-CREATE USER dummy@localhost;
-CREATE DATABASE mysqltest;
-CREATE TABLE mysqltest.dummytable (dummyfield INT);
-CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable;
-GRANT CREATE VIEW ON mysqltest.dummytable TO dummy@localhost;
-GRANT CREATE VIEW ON mysqltest.dummyview TO dummy@localhost;
-SHOW GRANTS FOR dummy@localhost;
-use INFORMATION_SCHEMA;
-SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
-PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
-= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
-FLUSH PRIVILEGES;
-SHOW GRANTS FOR dummy@localhost;
-SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
-PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
-= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
-use test;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost;
-DROP USER dummy@localhost;
-DROP DATABASE mysqltest;
-CREATE USER dummy@localhost;
-CREATE DATABASE mysqltest;
-CREATE TABLE mysqltest.dummytable (dummyfield INT);
-CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable;
-GRANT SHOW VIEW ON mysqltest.dummytable TO dummy@localhost;
-GRANT SHOW VIEW ON mysqltest.dummyview TO dummy@localhost;
-SHOW GRANTS FOR dummy@localhost;
-use INFORMATION_SCHEMA;
-SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
-PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
-= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
-FLUSH PRIVILEGES;
-SHOW GRANTS FOR dummy@localhost;
-SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
-PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
-= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
-use test;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost;
-DROP USER dummy@localhost;
-DROP DATABASE mysqltest;
-#
-# Bug#11330 Entry in tables_priv with host = '' causes crash
-#
-connection default;
-use mysql;
-insert into tables_priv values ('','test_db','mysqltest_1','test_table','test_grantor',CURRENT_TIMESTAMP,'Select','Select');
-flush privileges;
-delete from tables_priv where host = '' and user = 'mysqltest_1';
-flush privileges;
-use test;
-
-#
-# Bug#10892 user variables not auto cast for comparisons
-# Check that we don't get illegal mix of collations
-#
-set @user123="non-existent";
-select * from mysql.db where user=@user123;
-
-set names koi8r;
-create database ��;
-grant select on ��.* to root@localhost;
-select hex(Db) from mysql.db where Db='��';
-show grants for root@localhost;
-flush privileges;
-show grants for root@localhost;
-drop database ��;
-revoke all privileges on ��.* from root@localhost;
-show grants for root@localhost;
-set names latin1;
-
-#
-# Bug#15598 Server crashes in specific case during setting new password
-# - Caused by a user with host ''
-#
-create user mysqltest_7@;
-set password for mysqltest_7@ = password('systpass');
-show grants for mysqltest_7@;
-drop user mysqltest_7@;
---error ER_NONEXISTING_GRANT
-show grants for mysqltest_7@;
-
-#
-# Bug#14385 GRANT and mapping to correct user account problems
-#
-create database mysqltest;
-use mysqltest;
-create table t1(f1 int);
-GRANT DELETE ON mysqltest.t1 TO mysqltest1@'%';
-GRANT SELECT ON mysqltest.t1 TO mysqltest1@'192.%';
-show grants for mysqltest1@'192.%';
-show grants for mysqltest1@'%';
-delete from mysql.user where user='mysqltest1';
-delete from mysql.db where user='mysqltest1';
-delete from mysql.tables_priv where user='mysqltest1';
-flush privileges;
-drop database mysqltest;
-
-#
-# Bug#27515 DROP previlege is not required for RENAME TABLE
-#
-connection master;
-create database db27515;
-use db27515;
-create table t1 (a int);
-grant alter on db27515.t1 to user27515@localhost;
-grant insert, create on db27515.t2 to user27515@localhost;
-
-connect (conn27515, localhost, user27515, , db27515);
-connection conn27515;
---error ER_TABLEACCESS_DENIED_ERROR
-rename table t1 to t2;
-disconnect conn27515;
-
-connection master;
-revoke all privileges, grant option from user27515@localhost;
-drop user user27515@localhost;
-drop database db27515;
-
---echo End of 4.1 tests
-
-#
-# Bug#16297 In memory grant tables not flushed when users's hostname is ""
-#
-use test;
-create table t1 (a int);
-
-# Backup anonymous users and remove them. (They get in the way of
-# the one we test with here otherwise.)
-create table t2 as select * from mysql.user where user='';
-delete from mysql.user where user='';
-flush privileges;
-
-# Create some users with different hostnames
-create user mysqltest_8@'';
-create user mysqltest_8@host8;
-
-# Try to create them again
---error ER_CANNOT_USER
-create user mysqltest_8@'';
---error ER_CANNOT_USER
-create user mysqltest_8;
---error ER_CANNOT_USER
-create user mysqltest_8@host8;
-
-select user, QUOTE(host) from mysql.user where user="mysqltest_8";
-
---echo Schema privileges
-grant select on mysqltest.* to mysqltest_8@'';
-show grants for mysqltest_8@'';
-grant select on mysqltest.* to mysqltest_8@;
-show grants for mysqltest_8@;
-grant select on mysqltest.* to mysqltest_8;
-show grants for mysqltest_8;
-select * from  information_schema.schema_privileges
-where grantee like "'mysqltest_8'%";
-connect (conn3,localhost,mysqltest_8,,);
-select * from t1;
-disconnect conn3;
-connection master;
-revoke select on mysqltest.* from mysqltest_8@'';
-show grants for mysqltest_8@'';
-show grants for mysqltest_8;
-select * from  information_schema.schema_privileges
-where grantee like "'mysqltest_8'%";
-flush privileges;
-show grants for mysqltest_8@'';
-show grants for mysqltest_8@;
-grant select on mysqltest.* to mysqltest_8@'';
-flush privileges;
-show grants for mysqltest_8@;
-revoke select on mysqltest.* from mysqltest_8@'';
-flush privileges;
-
---echo Column privileges
-grant update (a) on t1 to mysqltest_8@'';
-grant update (a) on t1 to mysqltest_8;
-show grants for mysqltest_8@'';
-show grants for mysqltest_8;
-flush privileges;
-show grants for mysqltest_8@'';
-show grants for mysqltest_8;
-select * from  information_schema.column_privileges;
-connect (conn4,localhost,mysqltest_8,,);
-select * from t1;
-disconnect conn4;
-connection master;
-revoke update (a) on t1 from mysqltest_8@'';
-show grants for mysqltest_8@'';
-show grants for mysqltest_8;
-select * from  information_schema.column_privileges;
-flush privileges;
-show grants for mysqltest_8@'';
-show grants for mysqltest_8;
-
---echo Table privileges
-grant update on t1 to mysqltest_8@'';
-grant update on t1 to mysqltest_8;
-show grants for mysqltest_8@'';
-show grants for mysqltest_8;
-flush privileges;
-show grants for mysqltest_8@'';
-show grants for mysqltest_8;
-select * from  information_schema.table_privileges;
-connect (conn5,localhost,mysqltest_8,,);
-select * from t1;
-disconnect conn5;
-connection master;
-revoke update on t1 from mysqltest_8@'';
-show grants for mysqltest_8@'';
-show grants for mysqltest_8;
-select * from  information_schema.table_privileges;
-flush privileges;
-show grants for mysqltest_8@'';
-show grants for mysqltest_8;
-
---echo "DROP USER" should clear privileges
-grant all privileges on mysqltest.* to mysqltest_8@'';
-grant select on mysqltest.* to mysqltest_8@'';
-grant update on t1 to mysqltest_8@'';
-grant update (a) on t1 to mysqltest_8@'';
-grant all privileges on mysqltest.* to mysqltest_8;
-show grants for mysqltest_8@'';
-show grants for mysqltest_8;
-select * from  information_schema.user_privileges
-where grantee like "'mysqltest_8'%";
-connect (conn5,localhost,mysqltest_8,,);
-select * from t1;
-disconnect conn5;
-connection master;
-flush privileges;
-show grants for mysqltest_8@'';
-show grants for mysqltest_8;
-drop user mysqltest_8@'';
---error ER_NONEXISTING_GRANT
-show grants for mysqltest_8@'';
---replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
---error ER_ACCESS_DENIED_ERROR
-connect (conn6,localhost,mysqltest_8,,);
-connection master;
---error ER_NONEXISTING_GRANT
-show grants for mysqltest_8;
-drop user mysqltest_8@host8;
---error ER_NONEXISTING_GRANT
-show grants for mysqltest_8@host8;
-
-# Restore the anonymous users.
-insert into mysql.user select * from t2;
-flush privileges;
-drop table t2;
-drop table t1;
-
-#
-# Bug#20214 Incorrect error when user calls SHOW CREATE VIEW on non
-#           privileged view
-#
-
-connection master;
-
-CREATE DATABASE mysqltest3;
-USE mysqltest3;
-
-CREATE TABLE t_nn (c1 INT);
-CREATE VIEW  v_nn AS SELECT * FROM t_nn;
-
-CREATE DATABASE mysqltest2;
-USE mysqltest2;
-
-CREATE TABLE t_nn (c1 INT);
-CREATE VIEW  v_nn AS SELECT * FROM t_nn;
-CREATE VIEW  v_yn AS SELECT * FROM t_nn;
-CREATE VIEW  v_gy AS SELECT * FROM t_nn;
-CREATE VIEW  v_ny AS SELECT * FROM t_nn;
-CREATE VIEW  v_yy AS SELECT * FROM t_nn WHERE c1=55;
-
-GRANT SHOW VIEW        ON mysqltest2.v_ny TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
-GRANT SELECT           ON mysqltest2.v_yn TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
-GRANT SELECT           ON mysqltest2.*    TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
-GRANT SHOW VIEW,SELECT ON mysqltest2.v_yy TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
-
-connect (mysqltest_1, localhost, mysqltest_1, mysqltest_1,);
-
-# fail because of missing SHOW VIEW (have generic SELECT)
---error ER_TABLEACCESS_DENIED_ERROR
-SHOW CREATE VIEW  mysqltest2.v_nn;
---error ER_TABLEACCESS_DENIED_ERROR
-SHOW CREATE TABLE mysqltest2.v_nn;
-
-# fail because of missing SHOW VIEW
---error ER_TABLEACCESS_DENIED_ERROR
-SHOW CREATE VIEW  mysqltest2.v_yn;
---error ER_TABLEACCESS_DENIED_ERROR
-SHOW CREATE TABLE mysqltest2.v_yn;
-
-# succeed (despite of missing SELECT, having SHOW VIEW bails us out)
-SHOW CREATE TABLE mysqltest2.v_ny;
-
-# succeed (despite of missing SELECT, having SHOW VIEW bails us out)
-SHOW CREATE VIEW  mysqltest2.v_ny;
-
-# fail because of missing (specific or generic) SELECT
---error ER_TABLEACCESS_DENIED_ERROR
-SHOW CREATE TABLE mysqltest3.t_nn;
-
-# fail because of missing (specific or generic) SELECT (not because it's not a view!)
---error ER_TABLEACCESS_DENIED_ERROR
-SHOW CREATE VIEW  mysqltest3.t_nn;
-
-# fail because of missing missing (specific or generic) SELECT (and SHOW VIEW)
---error ER_TABLEACCESS_DENIED_ERROR
-SHOW CREATE VIEW  mysqltest3.v_nn;
---error ER_TABLEACCESS_DENIED_ERROR
-SHOW CREATE TABLE mysqltest3.v_nn;
-
-# succeed thanks to generic SELECT
-SHOW CREATE TABLE mysqltest2.t_nn;
-
-# fail because it's not a view!  (have generic SELECT though)
---error ER_WRONG_OBJECT
-SHOW CREATE VIEW  mysqltest2.t_nn;
-
-# succeed, have SELECT and SHOW VIEW
-SHOW CREATE VIEW mysqltest2.v_yy;
-
-# succeed, have SELECT and SHOW VIEW
-SHOW CREATE TABLE mysqltest2.v_yy;
-
-# clean-up
-connection master;
-
-# succeed, we're root
-SHOW CREATE TABLE mysqltest2.v_nn;
-SHOW CREATE VIEW  mysqltest2.v_nn;
-
-SHOW CREATE TABLE mysqltest2.t_nn;
-
-# fail because it's not a view!
---error ER_WRONG_OBJECT
-SHOW CREATE VIEW mysqltest2.t_nn;
-
-DROP VIEW  mysqltest2.v_nn;
-DROP VIEW  mysqltest2.v_yn;
-DROP VIEW  mysqltest2.v_ny;
-DROP VIEW  mysqltest2.v_yy;
-DROP TABLE mysqltest2.t_nn;
-DROP DATABASE mysqltest2;
-DROP VIEW  mysqltest3.v_nn;
-DROP TABLE mysqltest3.t_nn;
-DROP DATABASE mysqltest3;
-disconnect mysqltest_1;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost';
-DROP USER 'mysqltest_1'@'localhost';
-
-# restore the original database
-USE test;
-connection default;
-disconnect master;
-
-
-#
-# Bug#10668 CREATE USER does not enforce username length limit
-#
---error ER_WRONG_STRING_LENGTH
-create user longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789;
-
-#
-# Test for Bug#16899 Possible buffer overflow in handling of DEFINER-clause.
-#
-# These checks are intended to ensure that appropriate errors are risen when
-# illegal user name or hostname is specified in user-clause of GRANT/REVOKE
-# statements.
-#
-
-#
-# Bug#22369 Alter table rename combined with other alterations causes lost tables
-#
-CREATE DATABASE mysqltest1;
-CREATE TABLE mysqltest1.t1 (
-  int_field INTEGER UNSIGNED NOT NULL,
-  char_field CHAR(10),
-  INDEX(`int_field`)
-);
-CREATE TABLE mysqltest1.t2 (int_field INT);
-
---echo "Now check that we require equivalent grants for "
---echo "RENAME TABLE and ALTER TABLE"
-CREATE USER mysqltest_1@localhost;
-GRANT SELECT ON mysqltest1.t1 TO mysqltest_1@localhost;
-
-connect (conn42,localhost,mysqltest_1,,mysqltest1);
-SELECT USER();
-SHOW GRANTS;
---error ER_TABLEACCESS_DENIED_ERROR
-RENAME TABLE t1 TO t2;
---error ER_TABLEACCESS_DENIED_ERROR
-ALTER TABLE t1 RENAME TO t2;
---disconnect conn42
---connection default
-GRANT DROP ON mysqltest1.t1 TO mysqltest_1@localhost;
-
-connect (conn42,localhost,mysqltest_1,,mysqltest1);
---error ER_TABLEACCESS_DENIED_ERROR
-RENAME TABLE t1 TO t2;
---error ER_TABLEACCESS_DENIED_ERROR
-ALTER TABLE t1 RENAME TO t2;
---disconnect conn42
---connection default
-GRANT ALTER ON mysqltest1.t1 TO mysqltest_1@localhost;
-
-connect (conn42,localhost,mysqltest_1,,mysqltest1);
-SHOW GRANTS;
---error ER_TABLEACCESS_DENIED_ERROR
-RENAME TABLE t1 TO t2;
---error ER_TABLEACCESS_DENIED_ERROR
-ALTER TABLE t1 RENAME TO t2;
---disconnect conn42
---connection default
-GRANT INSERT, CREATE ON mysqltest1.t1 TO mysqltest_1@localhost;
-connect (conn42,localhost,mysqltest_1,,mysqltest1);
-SHOW GRANTS;
---error ER_TABLEACCESS_DENIED_ERROR
---disconnect conn42
---connection default
-GRANT INSERT, SELECT, CREATE, ALTER, DROP ON mysqltest1.t2 TO mysqltest_1@localhost;
-DROP TABLE mysqltest1.t2;
-
-connect (conn42,localhost,mysqltest_1,,mysqltest1);
-SHOW GRANTS;
-RENAME TABLE t1 TO t2;
-RENAME TABLE t2 TO t1;
-ALTER TABLE t1 RENAME TO t2;
-ALTER TABLE t2 RENAME TO t1;
---disconnect conn42
---connection default
-REVOKE DROP, INSERT ON mysqltest1.t1 FROM mysqltest_1@localhost;
-REVOKE DROP, INSERT ON mysqltest1.t2 FROM mysqltest_1@localhost;
-
-connect (conn42,localhost,mysqltest_1,,mysqltest1);
-SHOW GRANTS;
---error ER_TABLEACCESS_DENIED_ERROR
-RENAME TABLE t1 TO t2;
---error ER_TABLEACCESS_DENIED_ERROR
-ALTER TABLE t1 RENAME TO t2;
---disconnect conn42
---connection default
-
-DROP USER mysqltest_1@localhost;
-DROP DATABASE mysqltest1;
-USE test;
-
-# Working with database-level privileges.
-
---error ER_WRONG_STRING_LENGTH
-GRANT CREATE ON mysqltest.* TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost;
-
---error ER_WRONG_STRING_LENGTH
-GRANT CREATE ON mysqltest.* TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
-
---error ER_WRONG_STRING_LENGTH
-REVOKE CREATE ON mysqltest.* FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost;
-
---error ER_WRONG_STRING_LENGTH
-REVOKE CREATE ON mysqltest.* FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
-
-# Working with table-level privileges.
-
---error ER_WRONG_STRING_LENGTH
-GRANT CREATE ON t1 TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost;
-
---error ER_WRONG_STRING_LENGTH
-GRANT CREATE ON t1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
-
---error ER_WRONG_STRING_LENGTH
-REVOKE CREATE ON t1 FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost;
-
---error ER_WRONG_STRING_LENGTH
-REVOKE CREATE ON t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
-
-# Working with routine-level privileges.
-
---error ER_WRONG_STRING_LENGTH
-GRANT EXECUTE ON PROCEDURE p1 TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost;
-
---error ER_WRONG_STRING_LENGTH
-GRANT EXECUTE ON PROCEDURE p1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
-
---error ER_WRONG_STRING_LENGTH
-REVOKE EXECUTE ON PROCEDURE p1 FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost;
-
---error ER_WRONG_STRING_LENGTH
-REVOKE EXECUTE ON PROCEDURE t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
-
-
-#
-# Bug#23556 TRUNCATE TABLE still maps to DELETE
-#
-CREATE USER bug23556@localhost;
-CREATE DATABASE bug23556;
-GRANT SELECT ON bug23556.* TO bug23556@localhost;
-connect (bug23556,localhost,bug23556,,bug23556);
-
-connection default;
-USE bug23556;
-CREATE TABLE t1 (a INT PRIMARY KEY); INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
-GRANT DELETE ON t1 TO bug23556@localhost;
-
-connection bug23556;
-USE bug23556;
---error ER_TABLEACCESS_DENIED_ERROR
-TRUNCATE t1;
-
-connection default;
-USE bug23556;
-REVOKE DELETE ON t1 FROM bug23556@localhost;
-GRANT DROP ON t1 TO bug23556@localhost;
-
-connection bug23556;
-USE bug23556;
-TRUNCATE t1;
-
-connection default;
-USE bug23556;
-DROP TABLE t1;
-USE test;
-DROP DATABASE bug23556;
-DROP USER bug23556@localhost;
-connection default;
-disconnect bug23556;
-
-
-#
-# Bug#6774 Replication fails with Wrong usage of DB GRANT and GLOBAL PRIVILEGES
-#
-# Check if GRANT ... ON * ... fails when no database is selected
-connect (con1, localhost, root,,*NO-ONE*);
-connection con1;
---error ER_NO_DB_ERROR
-GRANT PROCESS ON * TO user@localhost;
-disconnect con1;
-connection default;
-
-
-#
-# Bug#9504 Stored procedures: execute privilege doesn't make 'use database'
-# okay.
-#
-
-# Prepare.
-
---disable_warnings
-DROP DATABASE IF EXISTS mysqltest1;
-DROP DATABASE IF EXISTS mysqltest2;
-DROP DATABASE IF EXISTS mysqltest3;
-DROP DATABASE IF EXISTS mysqltest4;
---enable_warnings
-
-CREATE DATABASE mysqltest1;
-CREATE DATABASE mysqltest2;
-CREATE DATABASE mysqltest3;
-CREATE DATABASE mysqltest4;
-
-CREATE PROCEDURE mysqltest1.p_def() SQL SECURITY DEFINER
-  SELECT 1;
-
-CREATE PROCEDURE mysqltest2.p_inv() SQL SECURITY INVOKER
-  SELECT 1;
-
-CREATE FUNCTION mysqltest3.f_def() RETURNS INT SQL SECURITY DEFINER
-  RETURN 1;
-
-CREATE FUNCTION mysqltest4.f_inv() RETURNS INT SQL SECURITY INVOKER
-  RETURN 1;
-
-GRANT EXECUTE ON PROCEDURE mysqltest1.p_def TO mysqltest_1@localhost;
-GRANT EXECUTE ON PROCEDURE mysqltest2.p_inv TO mysqltest_1@localhost;
-GRANT EXECUTE ON FUNCTION mysqltest3.f_def TO mysqltest_1@localhost;
-GRANT EXECUTE ON FUNCTION mysqltest4.f_inv TO mysqltest_1@localhost;
-
-GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost;
-
-# Test.
-
---connect (bug9504_con1,localhost,mysqltest_1,,)
---echo
---echo ---> connection: bug9504_con1
-
-# - Check that we can switch to the db;
-
-use mysqltest1;
-
-use mysqltest2;
-
-use mysqltest3;
-
-use mysqltest4;
-
-# - Check that we can call stored routines;
-
-use test;
-
-CALL mysqltest1.p_def();
-
-CALL mysqltest2.p_inv();
-
-SELECT mysqltest3.f_def();
-
-SELECT mysqltest4.f_inv();
-
-# Cleanup.
-
---connection default
---echo
---echo ---> connection: default
-
---disconnect bug9504_con1
-
-DROP DATABASE mysqltest1;
-DROP DATABASE mysqltest2;
-DROP DATABASE mysqltest3;
-DROP DATABASE mysqltest4;
-
-DROP USER mysqltest_1@localhost;
-
-
-#
-# Bug#27337 Privileges are not restored properly.
-#
-# Actually, the patch for this bugs fixes two problems. So, here are two test
-# cases.
-
-# Test case 1: privileges are not restored properly after calling a stored
-# routine defined with SQL SECURITY INVOKER clause.
-
-# Prepare.
-
---disable_warnings
-DROP DATABASE IF EXISTS mysqltest1;
-DROP DATABASE IF EXISTS mysqltest2;
---enable_warnings
-
-CREATE DATABASE mysqltest1;
-CREATE DATABASE mysqltest2;
-
-GRANT ALL PRIVILEGES ON mysqltest1.* TO mysqltest_1@localhost;
-GRANT SELECT ON mysqltest2.* TO mysqltest_1@localhost;
-
-CREATE PROCEDURE mysqltest1.p1() SQL SECURITY INVOKER
-  SELECT 1;
-
-# Test.
-
---connect (bug27337_con1,localhost,mysqltest_1,,mysqltest2)
---echo
---echo ---> connection: bug27337_con1
-
---error ER_TABLEACCESS_DENIED_ERROR
-CREATE TABLE t1(c INT);
-
-CALL mysqltest1.p1();
-
---error ER_TABLEACCESS_DENIED_ERROR
-CREATE TABLE t1(c INT);
-
---disconnect bug27337_con1
-
---connect (bug27337_con2,localhost,mysqltest_1,,mysqltest2)
---echo
---echo ---> connection: bug27337_con2
-
---error ER_TABLEACCESS_DENIED_ERROR
-CREATE TABLE t1(c INT);
-
-SHOW TABLES;
-
-# Cleanup.
-
---connection default
---echo
---echo ---> connection: default
-
---disconnect bug27337_con2
-
-DROP DATABASE mysqltest1;
-DROP DATABASE mysqltest2;
-
-DROP USER mysqltest_1@localhost;
-
-# Test case 2: privileges are not checked properly for prepared statements.
-
-# Prepare.
-
---disable_warnings
-DROP DATABASE IF EXISTS mysqltest1;
-DROP DATABASE IF EXISTS mysqltest2;
---enable_warnings
-
-CREATE DATABASE mysqltest1;
-CREATE DATABASE mysqltest2;
-
-CREATE TABLE mysqltest1.t1(c INT);
-CREATE TABLE mysqltest2.t2(c INT);
-
-GRANT SELECT ON mysqltest1.t1 TO mysqltest_1@localhost;
-GRANT SELECT ON mysqltest2.t2 TO mysqltest_2@localhost;
-
-# Test.
-
---connect (bug27337_con1,localhost,mysqltest_1,,mysqltest1)
---echo
---echo ---> connection: bug27337_con1
-
-SHOW TABLES FROM mysqltest1;
-
-PREPARE stmt1 FROM 'SHOW TABLES FROM mysqltest1';
-
-EXECUTE stmt1;
-
---connect (bug27337_con2,localhost,mysqltest_2,,mysqltest2)
---echo
---echo ---> connection: bug27337_con2
-
-SHOW COLUMNS FROM mysqltest2.t2;
-
-PREPARE stmt2 FROM 'SHOW COLUMNS FROM mysqltest2.t2';
-
-EXECUTE stmt2;
-
---connection default
---echo
---echo ---> connection: default
-
-REVOKE SELECT ON mysqltest1.t1 FROM mysqltest_1@localhost;
-REVOKE SELECT ON mysqltest2.t2 FROM mysqltest_2@localhost;
-
---connection bug27337_con1
---echo
---echo ---> connection: bug27337_con1
-
---error ER_DBACCESS_DENIED_ERROR
-SHOW TABLES FROM mysqltest1;
-
---error ER_DBACCESS_DENIED_ERROR
-EXECUTE stmt1;
-
---connection bug27337_con2
---echo
---echo ---> connection: bug27337_con2
-
---error ER_TABLEACCESS_DENIED_ERROR
-SHOW COLUMNS FROM mysqltest2.t2;
-
---error ER_TABLEACCESS_DENIED_ERROR
-EXECUTE stmt2;
-
-# Cleanup.
-
---connection default
---echo
---echo ---> connection: default
-
---disconnect bug27337_con1
---disconnect bug27337_con2
-
-DROP DATABASE mysqltest1;
-DROP DATABASE mysqltest2;
-
-DROP USER mysqltest_1@localhost;
-DROP USER mysqltest_2@localhost;
-
-#
-# Bug#27878 Unchecked privileges on a view referring to a table from another
-#           database.
-#
-USE test;
-CREATE TABLE t1 (f1 int, f2 int);
-INSERT INTO t1 VALUES(1,1), (2,2);
-CREATE DATABASE db27878;
-GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost';
-GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost';
-GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost';
-USE db27878;
-CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1;
-connect (user1,localhost,mysqltest_1,,test);
-connection user1;
-USE db27878;
---error 1356
-UPDATE v1 SET f2 = 4;
-SELECT * FROM test.t1;
-disconnect user1;
-connection default;
-REVOKE UPDATE (f1) ON `test`.`t1` FROM 'mysqltest_1'@'localhost';
-REVOKE SELECT ON `test`.* FROM 'mysqltest_1'@'localhost';
-REVOKE ALL ON db27878.* FROM 'mysqltest_1'@'localhost';
-DROP USER mysqltest_1@localhost;
-DROP DATABASE db27878;
-USE test;
-DROP TABLE t1;
-
---echo #
---echo #  Bug#33275 Server crash when creating temporary table mysql.user
---echo #
-CREATE TEMPORARY TABLE mysql.user (id INT);
-FLUSH PRIVILEGES;
-DROP TABLE mysql.user;
-
-
-#
-# Bug#33201 Crash occurs when granting update privilege on one column of a view
-#
-drop table if exists test;
-drop function if exists test_function;
-drop view if exists v1;
-create table test (col1 varchar(30));
-delimiter |;
-create function test_function() returns varchar(30)
-begin
-        declare tmp varchar(30);
-        select col1 from test limit 1 into tmp;
-        return '1';
-end|
-delimiter ;|
-create view v1 as select test.* from test where test.col1=test_function();
-grant update (col1) on v1 to 'greg'@'localhost';
-drop user 'greg'@'localhost';
-drop view v1;
-drop table test;
-drop function test_function;
-
-#
-# Bug#41456 SET PASSWORD hates CURRENT_USER()
-#
-SELECT CURRENT_USER();
-SET PASSWORD FOR CURRENT_USER() = PASSWORD("admin");
-SET PASSWORD FOR CURRENT_USER() = PASSWORD("");
-
-#
-# Bug#57952: privilege change is not taken into account by EXECUTE.
-#
-
---echo
---echo # Bug#57952
---echo
-
---disable_warnings
-DROP DATABASE IF EXISTS mysqltest1;
-DROP DATABASE IF EXISTS mysqltest2;
---enable_warnings
-
-CREATE DATABASE mysqltest1;
-CREATE DATABASE mysqltest2;
-
-use mysqltest1;
-CREATE TABLE t1(a INT, b INT);
-INSERT INTO t1 VALUES (1, 1);
-
-CREATE TABLE t2(a INT);
-INSERT INTO t2 VALUES (2);
-
-CREATE TABLE mysqltest2.t3(a INT);
-INSERT INTO mysqltest2.t3 VALUES (4);
-
-CREATE USER testuser@localhost;
-GRANT CREATE ROUTINE, EXECUTE ON mysqltest1.* TO testuser@localhost;
-GRANT SELECT(b) ON t1 TO testuser@localhost;
-GRANT SELECT    ON t2 TO testuser@localhost;
-GRANT SELECT    ON mysqltest2.* TO testuser@localhost;
-
---echo
---echo # Connection: bug57952_con1 (testuser@localhost, db: mysqltest1)
---connect (bug57952_con1,localhost,testuser,,mysqltest1)
-PREPARE s1 FROM 'SELECT b FROM t1';
-PREPARE s2 FROM 'SELECT a FROM t2';
-PREPARE s3 FROM 'SHOW TABLES FROM mysqltest2';
-
-CREATE PROCEDURE p1() SELECT b FROM t1;
-CREATE PROCEDURE p2() SELECT a FROM t2;
-CREATE PROCEDURE p3() SHOW TABLES FROM mysqltest2;
-
-CALL p1;
-CALL p2;
-CALL p3;
-
---echo
---echo # Connection: default
---connection default
-REVOKE SELECT ON t1 FROM testuser@localhost;
-GRANT SELECT(a) ON t1 TO testuser@localhost;
-REVOKE SELECT ON t2 FROM testuser@localhost;
-REVOKE SELECT ON mysqltest2.* FROM testuser@localhost;
-
---echo
---echo # Connection: bug57952_con1 (testuser@localhost, db: mysqltest1)
---connection bug57952_con1
---echo #   - Check column-level privileges...
---error ER_COLUMNACCESS_DENIED_ERROR
-EXECUTE s1;
-
---error ER_COLUMNACCESS_DENIED_ERROR
-SELECT b FROM t1;
-
---error ER_COLUMNACCESS_DENIED_ERROR
-EXECUTE s1;
-
---error ER_COLUMNACCESS_DENIED_ERROR
-CALL p1;
-
---echo #   - Check table-level privileges...
---error ER_TABLEACCESS_DENIED_ERROR
-SELECT a FROM t2;
-
---error ER_TABLEACCESS_DENIED_ERROR
-EXECUTE s2;
-
---error ER_TABLEACCESS_DENIED_ERROR
-CALL p2;
-
---echo #   - Check database-level privileges...
---error ER_DBACCESS_DENIED_ERROR
-SHOW TABLES FROM mysqltest2;
-
---error ER_DBACCESS_DENIED_ERROR
-EXECUTE s3;
-
---error ER_DBACCESS_DENIED_ERROR
-CALL p3;
-
---echo
---echo # Connection: default
---connection default
---disconnect bug57952_con1
-DROP DATABASE mysqltest1;
-DROP DATABASE mysqltest2;
-DROP USER testuser@localhost;
-use test;
---echo
-
-
---echo #
---echo # Test for bug #36544 "DROP USER does not remove stored function
---echo #                      privileges".
---echo #
-create database mysqltest1;
-create function mysqltest1.f1() returns int return 0;
-create procedure mysqltest1.p1() begin end;
---echo #
---echo # 1) Check that DROP USER properly removes privileges on both
---echo #    stored procedures and functions.
---echo #
-create user mysqluser1@localhost;
-grant execute on function mysqltest1.f1 to mysqluser1@localhost;
-grant execute on procedure mysqltest1.p1 to mysqluser1@localhost;
-
---echo # Quick test that granted privileges are properly reflected
---echo # in privilege tables and in in-memory structures.
-show grants for mysqluser1@localhost;
-select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost';
---echo #
---echo # Create connection 'bug_36544_con1' as 'mysqluser1@localhost'.
---connect (bug36544_con1,localhost,mysqluser1,,)
-call mysqltest1.p1();
-select mysqltest1.f1();
-
---echo #
---echo # Switch to connection 'default'.
---connection default
-drop user mysqluser1@localhost;
-
---echo #
---echo # Test that dropping of user is properly reflected in
---echo # both privilege tables and in in-memory structures.
---echo #
---echo # Switch to connection 'bug36544_con1'.
---connection bug36544_con1
---echo # The connection cold be alive but should not be able to
---echo # access to any of the stored routines.
---error ER_PROCACCESS_DENIED_ERROR
-call mysqltest1.p1();
---error ER_PROCACCESS_DENIED_ERROR
-select mysqltest1.f1();
---disconnect bug36544_con1
-
---echo #
---echo # Switch to connection 'default'.
---connection default
---echo #
---echo # Now create user with the same name and check that he
---echo # has not inherited privileges.
-create user mysqluser1@localhost;
-show grants for mysqluser1@localhost;
-select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost';
---echo #
---echo # Create connection 'bug_36544_con2' as 'mysqluser1@localhost'.
---connect (bug36544_con2,localhost,mysqluser1,,)
---echo # Newly created user should not be able to access any of the routines.
---error ER_PROCACCESS_DENIED_ERROR
-call mysqltest1.p1();
---error ER_PROCACCESS_DENIED_ERROR
-select mysqltest1.f1();
---echo #
---echo # Switch to connection 'default'.
---connection default
-
---echo #
---echo # 2) Check that RENAME USER properly updates privileges on both
---echo #    stored procedures and functions.
---echo #
-grant execute on function mysqltest1.f1 to mysqluser1@localhost;
-grant execute on procedure mysqltest1.p1 to mysqluser1@localhost;
---echo #
---echo # Create one more user to make in-memory hashes non-trivial.
---echo # User names 'mysqluser11' and 'mysqluser10' were selected
---echo # to trigger bug discovered during code inspection.
-create user mysqluser11@localhost;
-grant execute on function mysqltest1.f1 to mysqluser11@localhost;
-grant execute on procedure mysqltest1.p1 to mysqluser11@localhost;
---echo # Also create a couple of tables to test for another bug
---echo # discovered during code inspection (again table names were
---echo # chosen especially to trigger the bug).
-create table mysqltest1.t11 (i int);
-create table mysqltest1.t22 (i int);
-grant select on mysqltest1.t22 to mysqluser1@localhost;
-grant select on mysqltest1.t11 to mysqluser1@localhost;
-
---echo # Quick test that granted privileges are properly reflected
---echo # in privilege tables and in in-memory structures.
-show grants for mysqluser1@localhost;
-select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost';
-select db, table_name, table_priv from mysql.tables_priv where user='mysqluser1' and host='localhost';
---echo #
---echo # Switch to connection 'bug36544_con2'.
---connection bug36544_con2
-call mysqltest1.p1();
-select mysqltest1.f1();
-select * from mysqltest1.t11;
-select * from mysqltest1.t22;
-
---echo #
---echo # Switch to connection 'default'.
---connection default
-rename user mysqluser1@localhost to mysqluser10@localhost;
-
---echo #
---echo # Test that there are no privileges left for mysqluser1.
---echo #
---echo # Switch to connection 'bug36544_con2'.
---connection bug36544_con2
---echo # The connection cold be alive but should not be able to
---echo # access to any of the stored routines or tables.
---error ER_PROCACCESS_DENIED_ERROR
-call mysqltest1.p1();
---error ER_PROCACCESS_DENIED_ERROR
-select mysqltest1.f1();
---error ER_TABLEACCESS_DENIED_ERROR
-select * from mysqltest1.t11;
---error ER_TABLEACCESS_DENIED_ERROR
-select * from mysqltest1.t22;
---disconnect bug36544_con2
-
---echo #
---echo # Switch to connection 'default'.
---connection default
---echo #
---echo # Now create user with the old name and check that he
---echo # has not inherited privileges.
-create user mysqluser1@localhost;
-show grants for mysqluser1@localhost;
-select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost';
-select db, table_name, table_priv from mysql.tables_priv where user='mysqluser1' and host='localhost';
---echo #
---echo # Create connection 'bug_36544_con3' as 'mysqluser1@localhost'.
---connect (bug36544_con3,localhost,mysqluser1,,)
---echo # Newly created user should not be able to access to any of the
---echo # stored routines or tables.
---error ER_PROCACCESS_DENIED_ERROR
-call mysqltest1.p1();
---error ER_PROCACCESS_DENIED_ERROR
-select mysqltest1.f1();
---error ER_TABLEACCESS_DENIED_ERROR
-select * from mysqltest1.t11;
---error ER_TABLEACCESS_DENIED_ERROR
-select * from mysqltest1.t22;
---disconnect bug36544_con3
-
---echo #
---echo # Switch to connection 'default'.
---connection default
---echo #
---echo # Now check that privileges became associated with a new user
---echo # name - mysqluser10.
---echo #
-show grants for mysqluser10@localhost;
-select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser10' and host='localhost';
-select db, table_name, table_priv from mysql.tables_priv where user='mysqluser10' and host='localhost';
---echo #
---echo # Create connection 'bug_36544_con4' as 'mysqluser10@localhost'.
---connect (bug36544_con4,localhost,mysqluser10,,)
-call mysqltest1.p1();
-select mysqltest1.f1();
-select * from mysqltest1.t11;
-select * from mysqltest1.t22;
---disconnect bug36544_con4
-
---echo #
---echo # Switch to connection 'default'.
---connection default
---echo #
---echo # Clean-up.
-drop user mysqluser1@localhost;
-drop user mysqluser10@localhost;
-drop user mysqluser11@localhost;
-drop database mysqltest1;
-
-
---echo End of 5.0 tests
-set names utf8;
---error ER_WRONG_STRING_LENGTH
-grant select on test.* to очень_длинный_юзер890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890@localhost;
-set names default;
-
-#
-# Bug#20901 CREATE privilege is enough to insert into a table
-#
-
-create database mysqltest;
-use mysqltest;
-
-grant create on mysqltest.* to mysqltest@localhost;
-create table t1 (i INT);
-
-connect (user1,localhost,mysqltest,,mysqltest);
-connection user1;
-# show we don't have INSERT
---error ER_TABLEACCESS_DENIED_ERROR
-insert into t1 values (1);
-# show we have CREATE
-create table t2 (i INT);
-create table t4 (i INT);
-
-connection default;
-grant select, insert on mysqltest.t2 to mysqltest@localhost;
-grant         insert on mysqltest.t4 to mysqltest@localhost;
-# to specify ACLs for non-existent objects, must explictly |CREATE
-grant create, insert on mysqltest.t5 to mysqltest@localhost;
-grant create, insert on mysqltest.t6 to mysqltest@localhost;
-flush privileges;
-
-connection user1;
-insert into t2 values (1);
-
-
-# CREATE IF NOT EXISTS...SELECT, t1 exists, no INSERT, must fail
---error ER_TABLEACCESS_DENIED_ERROR
-create table if not exists t1 select * from t2;
-
-# CREATE IF NOT EXISTS...SELECT, no t3 yet, no INSERT, must fail
---error ER_TABLEACCESS_DENIED_ERROR
-create table if not exists t3 select * from t2;
-
-# CREATE IF NOT EXISTS...SELECT, t4 exists, have INSERT, must succeed
-create table if not exists t4 select * from t2;
-
-# CREATE IF NOT EXISTS...SELECT, no t5 yet, have INSERT, must succeed
-create table if not exists t5 select * from t2;
-
-
-# CREATE...SELECT, no t6 yet, have INSERT, must succeed
-create table t6 select * from t2;
-
-# CREATE...SELECT, no t7 yet, no INSERT, must fail
---error ER_TABLEACCESS_DENIED_ERROR
-create table t7 select * from t2;
-
-# CREATE...SELECT, t4 exists, have INSERT, must still fail (exists)
---error 1050
-create table t4 select * from t2;
-
-# CREATE...SELECT, t1 exists, no INSERT, must fail
---error ER_TABLEACCESS_DENIED_ERROR
-create table t1 select * from t2;
-
-
-connection default;
-drop table t1,t2,t4,t5,t6;
-
-revoke create         on mysqltest.*  from mysqltest@localhost;
-revoke select, insert on mysqltest.t2 from mysqltest@localhost;
-revoke insert         on mysqltest.t4 from mysqltest@localhost;
-revoke create, insert on mysqltest.t5 from mysqltest@localhost;
-revoke create, insert on mysqltest.t6 from mysqltest@localhost;
-drop user mysqltest@localhost;
-
-disconnect user1;
-drop database mysqltest;
-use test;
-
-
-#
-# Bug#16470 crash on grant if old grant tables
-#
-
-call mtr.add_suppression("Can't open and lock privilege tables");
-
---echo FLUSH PRIVILEGES without procs_priv table.
-RENAME TABLE mysql.procs_priv TO mysql.procs_gone;
-FLUSH PRIVILEGES;
---echo Assigning privileges without procs_priv table.
-CREATE DATABASE mysqltest1;
-CREATE PROCEDURE mysqltest1.test() SQL SECURITY DEFINER
-  SELECT 1;
-CREATE FUNCTION mysqltest1.test() RETURNS INT RETURN 1;
---error ER_NO_SUCH_TABLE
-GRANT EXECUTE ON FUNCTION mysqltest1.test TO mysqltest_1@localhost;
-GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost;
-CALL mysqltest1.test();
-DROP DATABASE mysqltest1;
-RENAME TABLE mysql.procs_gone TO mysql.procs_priv;
-DROP USER mysqltest_1@localhost;
-FLUSH PRIVILEGES;
-
-
-#
-# Bug#33464 DROP FUNCTION caused a crash.
-#
-CREATE DATABASE dbbug33464;
-CREATE USER 'userbug33464'@'localhost';
-
-GRANT CREATE ROUTINE ON dbbug33464.* TO 'userbug33464'@'localhost';
-
---replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
-connect (connbug33464, localhost, userbug33464, , dbbug33464);
---source suite/funcs_1/include/show_connection.inc
-
-delimiter //;
-CREATE PROCEDURE sp3(v1 char(20))
-BEGIN
-   SELECT * from dbbug33464.t6 where t6.f2= 'xyz';
-END//
-delimiter ;//
-
-delimiter //;
-CREATE FUNCTION fn1() returns char(50) SQL SECURITY INVOKER
-BEGIN
-   return 1;
-END//
-delimiter ;//
-
-delimiter //;
-CREATE FUNCTION fn2() returns char(50) SQL SECURITY DEFINER
-BEGIN
-   return 2;
-END//
-delimiter ;//
-
-disconnect connbug33464;
-
-# cleanup
-connection default;
-USE dbbug33464;
---source suite/funcs_1/include/show_connection.inc
-
-SELECT fn1();
-SELECT fn2();
-
---error 0, ER_CANNOT_USER
-DROP USER 'userbug33464'@'localhost';
-
-DROP FUNCTION fn1;
-DROP FUNCTION fn2;
-DROP PROCEDURE sp3;
-
---error 0, ER_CANNOT_USER
-DROP USER 'userbug33464'@'localhost';
-
-USE test;
-DROP DATABASE dbbug33464;
-
-
-SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
-
-#
-# Bug#44658 Create procedure makes server crash when user does not have ALL privilege
-#
-CREATE USER user1;
-CREATE USER user2;
-GRANT CREATE ON db1.* TO 'user1'@'localhost';
-GRANT CREATE ROUTINE ON db1.* TO 'user1'@'localhost';
-GRANT CREATE ON db1.* TO 'user2'@'%';
-GRANT CREATE ROUTINE ON db1.* TO 'user2'@'%';
-FLUSH PRIVILEGES;
-SHOW GRANTS FOR 'user1'@'localhost';
-connect (con1,localhost,user1,,);
---echo ** Connect as user1 and create a procedure.
---echo ** The creation will imply implicitly assigned
---echo ** EXECUTE and ALTER ROUTINE privileges to
---echo ** the current user user1@localhost. 
-SELECT @@GLOBAL.sql_mode;
-SELECT @@SESSION.sql_mode;
-CREATE DATABASE db1;
-DELIMITER ||;
-CREATE PROCEDURE db1.proc1(p1 INT)
- BEGIN
- SET @x = 0;
- REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
- END ;||
-DELIMITER ;||
-
-connect (con2,localhost,user2,,);
---echo ** Connect as user2 and create a procedure.
---echo ** Implicitly assignment of privileges will
---echo ** fail because the user2@localhost is an
---echo ** unknown user.
-DELIMITER ||;
-CREATE PROCEDURE db1.proc2(p1 INT)
- BEGIN
- SET @x = 0;
- REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
- END ;||
-DELIMITER ;||
-
-connection default;
-SHOW GRANTS FOR 'user1'@'localhost';
-SHOW GRANTS FOR 'user2';
-disconnect con1;
-disconnect con2;
-DROP PROCEDURE db1.proc1;
-DROP PROCEDURE db1.proc2;
-REVOKE ALL ON db1.* FROM 'user1'@'localhost';
-REVOKE ALL ON db1.* FROM 'user2'@'%';
-DROP USER 'user1';
-DROP USER 'user1'@'localhost';
-DROP USER 'user2';
-DROP DATABASE db1;
-
-
---echo #
---echo # Bug #25863 No database selected error, but documentation 
---echo #            says * for global allowed
---echo #
-
-connect(conn1,localhost,root,,*NO-ONE*);
-
---error ER_NO_DB_ERROR
-GRANT ALL ON * TO mysqltest_1;
-
-GRANT ALL ON *.* TO mysqltest_1;
-SHOW GRANTS FOR mysqltest_1;
-DROP USER mysqltest_1;
-
-USE test;
-
-GRANT ALL ON * TO mysqltest_1;
-SHOW GRANTS FOR mysqltest_1;
-DROP USER mysqltest_1;
-
-GRANT ALL ON *.* TO mysqltest_1;
-SHOW GRANTS FOR mysqltest_1;
-DROP USER mysqltest_1;
-
-connection default;
-disconnect conn1;
-
-
-#
-# Bug #53371: COM_FIELD_LIST can be abused to bypass table level grants.
-#
-
-CREATE DATABASE db1;
-CREATE DATABASE db2;
-GRANT SELECT ON db1.* to 'testbug'@localhost;
-USE db2;
-CREATE TABLE t1 (a INT);
-USE test;
-connect (con1,localhost,testbug,,db1);
---error ER_NO_SUCH_TABLE
-SELECT * FROM `../db2/tb2`;
---error ER_TABLEACCESS_DENIED_ERROR
-SELECT * FROM `../db2`.tb2;
---error ER_WRONG_TABLE_NAME
-SELECT * FROM `#mysql50#/../db2/tb2`;
-connection default;
-disconnect con1;
-DROP USER 'testbug'@localhost;
-DROP TABLE db2.t1;
-DROP DATABASE db1;
-DROP DATABASE db2;
-
---echo #
---echo # Bug #36742
---echo #
-grant usage on Foo.* to myuser@Localhost identified by 'foo';
-grant select on Foo.* to myuser@localhost;
-select host,user from mysql.user where User='myuser';
-revoke select on Foo.* from myuser@localhost;
-delete from mysql.user where User='myuser';
-flush privileges;
-
---echo #########################################################################
---echo #
---echo # Bug#38347: ALTER ROUTINE privilege allows SHOW CREATE TABLE.
---echo #
---echo #########################################################################
-
---echo
---echo # --
---echo # -- Prepare the environment.
---echo # --
-
-DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
-FLUSH PRIVILEGES;
-
---disable_warnings
-DROP DATABASE IF EXISTS mysqltest_db1;
---enable_warnings
-
-CREATE DATABASE mysqltest_db1;
-
-CREATE TABLE mysqltest_db1.t1(a INT);
-
---echo
---echo # --
---echo # -- Check that global privileges don't allow SHOW CREATE TABLE.
---echo # --
-
-GRANT EVENT                   ON mysqltest_db1.* TO mysqltest_u1@localhost;
-GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost;
-GRANT LOCK TABLES             ON mysqltest_db1.* TO mysqltest_u1@localhost;
-GRANT ALTER ROUTINE           ON mysqltest_db1.* TO mysqltest_u1@localhost;
-GRANT CREATE ROUTINE          ON mysqltest_db1.* TO mysqltest_u1@localhost;
-GRANT EXECUTE                 ON mysqltest_db1.* TO mysqltest_u1@localhost;
-
-GRANT FILE                    ON *.* TO mysqltest_u1@localhost;
-GRANT CREATE USER             ON *.* TO mysqltest_u1@localhost;
-GRANT PROCESS                 ON *.* TO mysqltest_u1@localhost;
-GRANT RELOAD                  ON *.* TO mysqltest_u1@localhost;
-GRANT REPLICATION CLIENT      ON *.* TO mysqltest_u1@localhost;
-GRANT REPLICATION SLAVE       ON *.* TO mysqltest_u1@localhost;
-GRANT SHOW DATABASES          ON *.* TO mysqltest_u1@localhost;
-GRANT SHUTDOWN                ON *.* TO mysqltest_u1@localhost;
-GRANT USAGE                   ON *.* TO mysqltest_u1@localhost;
-
---echo
-SHOW GRANTS FOR mysqltest_u1@localhost;
-
---echo
---echo # connection: con1 (mysqltest_u1@mysqltest_db1)
---connect (con1,localhost,mysqltest_u1,,mysqltest_db1)
---connection con1
-
---echo
---error ER_TABLEACCESS_DENIED_ERROR
-SHOW CREATE TABLE t1;
-
---echo
---echo # connection: default
---connection default
-
---disconnect con1
-
---echo
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
-SHOW GRANTS FOR mysqltest_u1@localhost;
-
---echo
---echo # -- 
---echo # -- Check that global SELECT allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT SELECT ON mysqltest_db1.* TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that global INSERT allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT INSERT ON mysqltest_db1.* TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that global UPDATE allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT UPDATE ON mysqltest_db1.* TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that global DELETE allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that global CREATE allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT CREATE ON mysqltest_db1.* TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that global DROP allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT DROP ON mysqltest_db1.* TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that global ALTER allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT ALTER ON mysqltest_db1.* TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that global INDEX allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT INDEX ON mysqltest_db1.* TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that global REFERENCES allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT REFERENCES ON mysqltest_db1.* TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that global GRANT OPTION allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT GRANT OPTION ON mysqltest_db1.* TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that global CREATE VIEW allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT CREATE VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that global SHOW VIEW allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT SHOW VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that table-level SELECT allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that table-level INSERT allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT INSERT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that table-level UPDATE allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that table-level DELETE allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT DELETE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that table-level CREATE allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT CREATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that table-level DROP allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT DROP ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that table-level ALTER allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT ALTER ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that table-level INDEX allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT INDEX ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that table-level REFERENCES allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT REFERENCES ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that table-level GRANT OPTION allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT GRANT OPTION ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that table-level CREATE VIEW allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT CREATE VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Check that table-level SHOW VIEW allows SHOW CREATE TABLE.
---echo # -- 
-
---echo
-GRANT SHOW VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-
---source include/bug38347.inc
-
---echo
---echo # -- 
---echo # -- Cleanup.
---echo # -- 
-
---echo
-DROP DATABASE mysqltest_db1;
-
-DROP USER mysqltest_u1@localhost;
-
---echo
---echo # End of Bug#38347.
---echo
-
-
---echo #
---echo # BUG#11759114 - '51401: GRANT TREATS NONEXISTENT FUNCTIONS/PRIVILEGES
---echo #                 DIFFERENTLY'.
---echo #
---disable_warnings
-drop database if exists mysqltest_db1;
---enable_warnings
-create database mysqltest_db1;
-create user mysqltest_u1;
---echo # Both GRANT statements below should fail with the same error.
---error ER_SP_DOES_NOT_EXIST
-grant execute on function mysqltest_db1.f1 to mysqltest_u1;
---error ER_SP_DOES_NOT_EXIST
-grant execute on procedure mysqltest_db1.p1 to mysqltest_u1;
---echo # Let us show that GRANT behaviour for routines is consistent
---echo # with GRANT behaviour for tables. Attempt to grant privilege
---echo # on non-existent table also results in an error.
---error ER_NO_SUCH_TABLE
-grant select on mysqltest_db1.t1 to mysqltest_u1;
-show grants for mysqltest_u1;
-drop database mysqltest_db1;
-drop user mysqltest_u1;
-
-
---echo #
---echo # Bug#12766319 - 61865: RENAME USER DOES NOT WORK CORRECTLY -
---echo #                       REQUIRES FLUSH PRIVILEGES
---echo #
-
-CREATE USER foo@'127.0.0.1';
-GRANT ALL ON *.* TO foo@'127.0.0.1';
-
---echo # First attempt, should connect successfully
-connect (conn1, '127.0.0.1', foo,,test);
-SELECT user(), current_user();
-
---echo # Rename the user
-RENAME USER foo@'127.0.0.1' to foo@'127.0.0.0/255.0.0.0';
-
---echo # Second attempt, should connect successfully as its valid mask
---echo # This was failing without fix
-connect (conn2, '127.0.0.1', foo,,test);
-SELECT user(), current_user();
-
---echo # Rename the user back to original
-RENAME USER foo@'127.0.0.0/255.0.0.0' to foo@'127.0.0.1';
-
---echo # Third attempt, should connect successfully
-connect (conn3, '127.0.0.1', foo,,test);
-SELECT user(), current_user();
-
---echo # Clean-up
-connection default;
-disconnect conn1;
-disconnect conn2;
-disconnect conn3;
-DROP USER foo@'127.0.0.1';
-
---echo # End of Bug#12766319
-
-#
-# Bug#27230925: HANDLE_FATAL_SIGNAL (SIG=11) IN SHOW_ROUTINE_GRANTS
-#
-create user foo@localhost;
-create database foodb;
-grant create routine on foodb.* to foo@localhost;
-connect con1,localhost,foo;
-create procedure fooproc() select 'i am fooproc';
-show grants;
-disconnect con1;
-connection default;
-rename table mysql.procs_priv to mysql.procs_priv1;
-flush privileges;
-show grants for foo@localhost;
-rename table mysql.procs_priv1 to mysql.procs_priv;
-show grants for foo@localhost;
-flush privileges;
-show grants for foo@localhost;
-drop user foo@localhost;
-drop procedure fooproc;
-drop database foodb;
-
-
---echo #
---echo # Bug#11756966 - 48958: STORED PROCEDURES CAN BE LEVERAGED TO BYPASS
---echo #                DATABASE SECURITY
---echo #
-
---disable_warnings
-DROP DATABASE IF EXISTS secret;
-DROP DATABASE IF EXISTS no_such_db;
---enable_warnings
-
-CREATE DATABASE secret;
-GRANT USAGE ON *.* TO untrusted@localhost;
-
---echo # Connection con1
-connect (con1, localhost, untrusted);
-SHOW GRANTS;
-SHOW DATABASES;
-
---echo # Both statements below should fail with the same error.
---echo # They used to give different errors, thereby
---echo # hinting that the secret database exists.
---error ER_DBACCESS_DENIED_ERROR
-CREATE PROCEDURE no_such_db.foo() BEGIN END;
---error ER_DBACCESS_DENIED_ERROR
-CREATE PROCEDURE secret.peek_at_secret() BEGIN END;
-
---echo # Connection default
---connection default
-disconnect con1;
-DROP USER untrusted@localhost;
-DROP DATABASE secret;
-
-# Wait till we reached the initial number of concurrent sessions
---source include/wait_until_count_sessions.inc
diff --git a/mysql-test/r/lowercase_fs_off.test b/mysql-test/r/lowercase_fs_off.test
deleted file mode 100644
index f4df5e8188d..00000000000
--- a/mysql-test/r/lowercase_fs_off.test
+++ /dev/null
@@ -1,124 +0,0 @@
-#
-# Specific tests for case sensitive file systems
-# i.e. lower_case_filesystem=OFF
-#
--- source include/have_case_sensitive_file_system.inc
--- source include/not_embedded.inc
-
-connect (master,localhost,root,,);
-connection master;
-create database d1;
-grant all on d1.* to 'sample'@'localhost' identified by 'password';
-flush privileges;
-
-connect (sample,localhost,sample,password,d1);
-connection sample;
-select database();
---error ER_DBACCESS_DENIED_ERROR
-create database d2;
---error ER_DBACCESS_DENIED_ERROR
-create database D1;
-disconnect sample;
---source include/wait_until_disconnected.inc
-
-connection master;
-drop user 'sample'@'localhost';
-drop database if exists d1;
-disconnect master;
---source include/wait_until_disconnected.inc
-connection default;
-
-# End of 4.1 tests
-
-#
-# Bug#41049 does syntax "grant" case insensitive?
-#
-CREATE DATABASE d1;
-USE d1;
-CREATE TABLE T1(f1 INT);
-CREATE TABLE t1(f1 INT);
-GRANT SELECT ON T1 to user_1@localhost;
-
-connect (con1,localhost,user_1,,d1);
---error ER_TABLEACCESS_DENIED_ERROR
-select * from t1;
-select * from T1;
-connection default;
-GRANT SELECT ON t1 to user_1@localhost;
-connection con1;
-select * from information_schema.table_privileges;
-connection default;
-disconnect con1;
-
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost;
-DROP USER user_1@localhost;
-DROP DATABASE d1;
-USE test;
-
-CREATE DATABASE db1;
-USE db1;
-CREATE PROCEDURE p1() BEGIN END;
-CREATE FUNCTION f1(i INT) RETURNS INT RETURN i+1;
-
-GRANT USAGE ON db1.* to user_1@localhost;
-GRANT EXECUTE ON PROCEDURE db1.P1 to user_1@localhost;
-GRANT EXECUTE ON FUNCTION db1.f1 to user_1@localhost;
-GRANT UPDATE ON db1.* to USER_1@localhost;
-
-connect (con1,localhost,user_1,,db1);
-call p1();
-call P1();
-select f1(1);
-connect (con2,localhost,USER_1,,db1);
---error ER_PROCACCESS_DENIED_ERROR
-call p1();
---error ER_PROCACCESS_DENIED_ERROR
-call P1();
---error ER_PROCACCESS_DENIED_ERROR
-select f1(1);
-
-connection default;
-disconnect con1;
-disconnect con2;
-
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER_1@localhost;
-DROP FUNCTION f1;
-DROP PROCEDURE p1;
-DROP USER user_1@localhost;
-DROP USER USER_1@localhost;
-DROP DATABASE db1;
-use test;
-
-# End of 5.0 tests
-
-
---echo #
---echo # Extra test coverage for Bug#56595 RENAME TABLE causes assert on OS X
---echo #
-
-CREATE TABLE t1(a INT);
-CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a= 1;
-RENAME TABLE t1 TO T1;
-ALTER TABLE T1 RENAME t1;
-DROP TABLE t1;
-
-#
-# MDEV-13912 mysql_upgrade: case (in)sensitivity for stored procedures
-#
-create database TEST;
-create procedure TEST.pr() begin end;
-create procedure test.pr() begin end;
---exec $MYSQL_UPGRADE --force 2>&1
-drop procedure test.pr;
-drop database TEST;
-
-# End of 5.5 tests
-
-#
-# MDEV-9014 SHOW TRIGGERS not case sensitive
-#
-create table t1 (a int);
-create trigger t1_bi before insert on t1 for each row set new.a= 1;
-show triggers like '%T1%';
-drop table t1;
diff --git a/mysql-test/r/sp-security.test b/mysql-test/r/sp-security.test
deleted file mode 100644
index 53dc4f8c7ac..00000000000
--- a/mysql-test/r/sp-security.test
+++ /dev/null
@@ -1,1069 +0,0 @@
-#
-# Testing SQL SECURITY of stored procedures
-#
-
-# Can't test with embedded server that doesn't support grants
--- source include/not_embedded.inc
-
-# Save the initial number of concurrent sessions
---source include/count_sessions.inc
-
-connect (con1root,localhost,root,,);
-
-connection con1root;
-use test;
-
-# Create user user1 with no particular access rights
-grant usage on *.* to user1@localhost;
-flush privileges;
-
---disable_warnings
-drop table if exists t1;
-drop database if exists db1_secret;
---enable_warnings
-# Create our secret database
-create database db1_secret;
-
-# Can create a procedure in other db
-create procedure db1_secret.dummy() begin end;
-drop procedure db1_secret.dummy;
-
-use db1_secret;
-
-create table t1 ( u varchar(64), i int );
-insert into t1 values('test', 0);
-
-# A test procedure and function
-create procedure stamp(i int)
-  insert into db1_secret.t1 values (user(), i);
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-show procedure status like 'stamp';
-
-delimiter |;
-create function db() returns varchar(64)
-begin
-  declare v varchar(64);
-
-  select u into v from t1 limit 1;
-
-  return v;
-end|
-delimiter ;|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-show function status like 'db';
-
-# root can, of course
-call stamp(1);
-select * from t1;
-select db();
-
-grant execute on procedure db1_secret.stamp to user1@'%';
-grant execute on function db1_secret.db to user1@'%';
-grant execute on procedure db1_secret.stamp to ''@'%';
-grant execute on function db1_secret.db to ''@'%';
-
-connect (con2user1,localhost,user1,,);
-connect (con3anon,localhost,anon,,);
-
-
-#
-# User1 can
-#
-connection con2user1;
-
-# This should work...
-call db1_secret.stamp(2);
-select db1_secret.db();
-
-# ...but not this
---error ER_TABLEACCESS_DENIED_ERROR
-select * from db1_secret.t1;
-
-# ...and not this
---error ER_DBACCESS_DENIED_ERROR
-create procedure db1_secret.dummy() begin end;
---error ER_PROCACCESS_DENIED_ERROR
-drop procedure db1_secret.dummy;
---error ER_PROCACCESS_DENIED_ERROR
-drop procedure db1_secret.stamp;
---error ER_PROCACCESS_DENIED_ERROR
-drop function db1_secret.db;
-
-
-#
-# Anonymous can
-#
-connection con3anon;
-
-# This should work...
-call db1_secret.stamp(3);
-select db1_secret.db();
-
-# ...but not this
---error ER_TABLEACCESS_DENIED_ERROR
-select * from db1_secret.t1;
-
-# ...and not this
---error ER_DBACCESS_DENIED_ERROR
-create procedure db1_secret.dummy() begin end;
---error ER_PROCACCESS_DENIED_ERROR
-drop procedure db1_secret.dummy;
---error ER_PROCACCESS_DENIED_ERROR
-drop procedure db1_secret.stamp;
---error ER_PROCACCESS_DENIED_ERROR
-drop function db1_secret.db;
-
-
-#
-# Check it out
-#
-connection con1root;
-select * from t1;
-
-#
-# Change to invoker's rights
-#
-alter procedure stamp sql security invoker;
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-show procedure status like 'stamp';
-
-alter function db sql security invoker;
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-show function status like 'db';
-
-# root still can
-call stamp(4);
-select * from t1;
-select db();
-
-#
-# User1 cannot
-#
-connection con2user1;
-
-# This should not work
---error ER_TABLEACCESS_DENIED_ERROR
-call db1_secret.stamp(5);
---error ER_TABLEACCESS_DENIED_ERROR
-select db1_secret.db();
-
-#
-# Anonymous cannot
-#
-connection con3anon;
-
-# This should not work
---error ER_TABLEACCESS_DENIED_ERROR
-call db1_secret.stamp(6);
---error ER_TABLEACCESS_DENIED_ERROR
-select db1_secret.db();
-
-#
-# Bug#2777 Stored procedure doesn't observe definer's rights
-#
-
-connection con1root;
---disable_warnings
-drop database if exists db2;
---enable_warnings
-create database db2;
-
-use db2;
-
-create table t2 (s1 int);
-insert into t2 values (0);
-
-grant usage on db2.* to user1@localhost;
-grant select on db2.* to user1@localhost;
-grant usage on db2.* to user2@localhost;
-grant select,insert,update,delete,create routine on db2.* to user2@localhost;
-grant create routine on db2.* to user1@localhost;
-flush privileges;
-
-connection con2user1;
-use db2;
-
-create procedure p () insert into t2 values (1);
-
-# Check that this doesn't work.
---error ER_TABLEACCESS_DENIED_ERROR
-call p();
-
-connect (con4user2,localhost,user2,,);
-
-connection con4user2;
-use db2;
-
-# This should not work, since p is executed with definer's (user1's) rights.
---error ER_PROCACCESS_DENIED_ERROR
-call p();
-select * from t2;
-
-create procedure q () insert into t2 values (2);
-
-call q();
-select * from t2;
-
-connection con1root;
-grant usage on procedure db2.q to user2@localhost with grant option;
-
-connection con4user2;
-grant execute on procedure db2.q to user1@localhost;
-
-connection con2user1;
-use db2;
-
-# This should work
-call q();
-select * from t2;
-
-#
-# Bug#6030 Stored procedure has no appropriate DROP privilege
-# (or ALTER for that matter)
-
-# still connection con2user1 in db2
-
-# This should work:
-alter procedure p modifies sql data;
-drop procedure p;
-
-# This should NOT work
---error ER_PROCACCESS_DENIED_ERROR
-alter procedure q modifies sql data;
---error ER_PROCACCESS_DENIED_ERROR
-drop procedure q;
-
-connection con1root;
-use db2;
-# But root always can
-alter procedure q modifies sql data;
-drop procedure q;
-
-
-# Clean up
-#Still connection con1root;
-disconnect con2user1;
-disconnect con3anon;
-disconnect con4user2;
-use test;
-select type,db,name from mysql.proc where db like 'db%';
-drop database db1_secret;
-drop database db2;
-# Make sure the routines are gone
-select type,db,name from mysql.proc where db like 'db%';
-# Get rid of the users
-delete from mysql.user where user='user1' or user='user2';
-delete from mysql.user where user='' and host='%';
-# And any routine privileges
-delete from mysql.procs_priv where user='user1' or user='user2';
-# Delete the grants to user ''@'%' that was created above
-delete from mysql.procs_priv where user='' and host='%';
-delete from mysql.db where user='user2';
-flush privileges;
-#
-# Test the new security acls
-#
-grant usage on *.* to usera@localhost;
-grant usage on *.* to userb@localhost;
-grant usage on *.* to userc@localhost;
-create database sptest;
-create table t1 ( u varchar(64), i int );
-create procedure sptest.p1(i int) insert into test.t1 values (user(), i);
-grant insert on t1 to usera@localhost;
-grant execute on procedure sptest.p1 to usera@localhost;
-show grants for usera@localhost;
-grant execute on procedure sptest.p1 to userc@localhost with grant option;
-show grants for userc@localhost;
-
-connect (con2usera,localhost,usera,,);
-connect (con3userb,localhost,userb,,);
-connect (con4userc,localhost,userc,,);
-
-connection con2usera;
-call sptest.p1(1);
---error ER_PROCACCESS_DENIED_ERROR
-grant execute on procedure sptest.p1 to userb@localhost;
---error ER_PROCACCESS_DENIED_ERROR
-drop procedure sptest.p1;
-
-connection con3userb;
---error ER_PROCACCESS_DENIED_ERROR
-call sptest.p1(2);
---error ER_PROCACCESS_DENIED_ERROR
-grant execute on procedure sptest.p1 to userb@localhost;
---error ER_PROCACCESS_DENIED_ERROR
-drop procedure sptest.p1;
-
-connection con4userc;
-call sptest.p1(3);
-grant execute on procedure sptest.p1 to userb@localhost;
---error ER_PROCACCESS_DENIED_ERROR
-drop procedure sptest.p1;
-
-connection con3userb;
-call sptest.p1(4);
---error ER_PROCACCESS_DENIED_ERROR
-grant execute on procedure sptest.p1 to userb@localhost;
---error ER_PROCACCESS_DENIED_ERROR
-drop procedure sptest.p1;
-
-connection con1root;
-select * from t1;
-
-grant all privileges on procedure sptest.p1 to userc@localhost;
-show grants for userc@localhost;
-show grants for userb@localhost;
-
-connection con4userc;
-revoke all privileges on procedure sptest.p1 from userb@localhost;
-
-connection con1root;
-show grants for userb@localhost;
-
-#cleanup
-disconnect con4userc;
-disconnect con3userb;
-disconnect con2usera;
-use test;
-drop database sptest;
-delete from mysql.user where user='usera' or user='userb' or user='userc';
-delete from mysql.procs_priv where user='usera' or user='userb' or user='userc';
-delete from mysql.tables_priv where user='usera';
-flush privileges;
-drop table t1;
-
-#
-# Bug#9503 reseting correct parameters of thread after error in SP function
-#
-connect (root,localhost,root,,test);
-connection root;
-
---disable_warnings
-drop function if exists bug_9503;
---enable_warnings
-delimiter //;
-create database mysqltest//
-use mysqltest//
-create table t1 (s1 int)//
-grant select on t1 to user1@localhost//
-create function bug_9503 () returns int sql security invoker begin declare v int;
-select min(s1) into v from t1; return v; end//
-delimiter ;//
-
-connect (user1,localhost,user1,,test);
-connection user1;
-use mysqltest;
--- error ER_PROCACCESS_DENIED_ERROR
-select bug_9503();
-
-connection root;
-grant execute on function bug_9503 to user1@localhost;
-
-connection user1;
-do 1;
-use test;
-
-disconnect user1;
-connection root;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost;
-drop function bug_9503;
-use test;
-drop database mysqltest;
-connection default;
-disconnect root;
-
-#
-# correct value from current_user() in function run from "security definer"
-# (Bug#7291 Stored procedures: wrong CURRENT_USER value)
-#
-connection con1root;
-use test;
-
-select current_user();
-select user();
-create procedure bug7291_0 () sql security invoker select current_user(), user();
-create procedure bug7291_1 () sql security definer call bug7291_0();
-create procedure bug7291_2 () sql security invoker call bug7291_0();
-grant execute on procedure bug7291_0 to user1@localhost;
-grant execute on procedure bug7291_1 to user1@localhost;
-grant execute on procedure bug7291_2 to user1@localhost;
-
-connect (user1,localhost,user1,,);
-connection user1;
-
-call bug7291_2();
-call bug7291_1();
-
-connection con1root;
-drop procedure bug7291_1;
-drop procedure bug7291_2;
-drop procedure bug7291_0;
-disconnect user1;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost;
-drop user user1@localhost;
-
-#
-# Bug#12318 Wrong error message when accessing an inaccessible stored
-# procedure in another database when the current database is
-# information_schema.
-#
-
---disable_warnings
-drop database if exists mysqltest_1;
---enable_warnings
-
-create database mysqltest_1;
-delimiter //;
-create procedure mysqltest_1.p1()
-begin
-   select 1 from dual;
-end//
-delimiter ;//
-
-grant usage on *.* to mysqltest_1@localhost;
-
-connect (n1,localhost,mysqltest_1,,information_schema,$MASTER_MYPORT,$MASTER_MYSOCK);
-connection n1;
---error ER_PROCACCESS_DENIED_ERROR
-call mysqltest_1.p1();
-disconnect n1;
-# Test also without a current database
-connect (n2,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK);
-connection n2;
---error ER_PROCACCESS_DENIED_ERROR
-call mysqltest_1.p1();
-disconnect n2;
-
-connection default;
-
-drop procedure mysqltest_1.p1;
-drop database mysqltest_1;
-
-revoke usage on *.* from mysqltest_1@localhost;
-drop user mysqltest_1@localhost;
-
-#
-# Bug#12812 create view calling a function works without execute right
-#           on function
-delimiter |;
---disable_warnings
-drop function if exists bug12812|
---enable_warnings
-create function bug12812() returns char(2)
-begin
-  return 'ok';
-end;
-create user user_bug12812@localhost IDENTIFIED BY 'ABC'|
---replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
-connect (test_user_12812,localhost,user_bug12812,ABC,test)|
---error ER_PROCACCESS_DENIED_ERROR
-SELECT test.bug12812()|
---error ER_PROCACCESS_DENIED_ERROR
-CREATE VIEW v1 AS SELECT test.bug12812()|
-# Cleanup
-connection default|
-disconnect test_user_12812|
-DROP USER user_bug12812@localhost|
-drop function bug12812|
-delimiter ;|
-
-
-#
-# Bug#14834 Server denies to execute Stored Procedure
-#
-# The problem here was with '_' in the database name.
-#
-create database db_bug14834;
-
-create user user1_bug14834@localhost identified by '';
-# The exact name of the database (no wildcard)
-grant all on `db\_bug14834`.* to user1_bug14834@localhost;
-
-create user user2_bug14834@localhost identified by '';
-# The exact name of the database (no wildcard)
-grant all on `db\_bug14834`.* to user2_bug14834@localhost;
-
-create user user3_bug14834@localhost identified by '';
-# Wildcards in the database name
-grant all on `db__ug14834`.* to user3_bug14834@localhost;
-
-connect (user1_bug14834,localhost,user1_bug14834,,db_bug14834);
-# Create the procedure and check that we can call it
-create procedure p_bug14834() select user(), current_user();
-call p_bug14834();
-
-connect (user2_bug14834,localhost,user2_bug14834,,db_bug14834);
-# This didn't work before
-call p_bug14834();
-
-connect (user3_bug14834,localhost,user3_bug14834,,db_bug14834);
-# Should also work
-call p_bug14834();
-
-# Cleanup
-connection default;
-disconnect user1_bug14834;
-disconnect user2_bug14834;
-disconnect user3_bug14834;
-drop user user1_bug14834@localhost;
-drop user user2_bug14834@localhost;
-drop user user3_bug14834@localhost;
-drop database db_bug14834;
-
-
-#
-# Bug#14533 'desc tbl' in stored procedure causes error
-# ER_TABLEACCESS_DENIED_ERROR
-#
-create database db_bug14533;
-use db_bug14533;
-create table t1 (id int);
-create user user_bug14533@localhost identified by '';
-
-create procedure bug14533_1()
-    sql security definer
-  desc db_bug14533.t1;
-
-create procedure bug14533_2()
-    sql security definer
-   select * from db_bug14533.t1;
-
-grant execute on procedure db_bug14533.bug14533_1 to user_bug14533@localhost;
-grant execute on procedure db_bug14533.bug14533_2 to user_bug14533@localhost;
-
-connect (user_bug14533,localhost,user_bug14533,,test);
-
-# These should work
-call db_bug14533.bug14533_1();
-call db_bug14533.bug14533_2();
-
-# For reference, these should not work
---error ER_TABLEACCESS_DENIED_ERROR
-desc db_bug14533.t1;
---error ER_TABLEACCESS_DENIED_ERROR
-select * from db_bug14533.t1;
-
-# Cleanup
-connection default;
-disconnect user_bug14533;
-drop user user_bug14533@localhost;
-drop database db_bug14533;
-
-
-#
-# WL#2897 Complete definer support in the stored routines.
-#
-# The following cases are tested:
-#   1. check that if DEFINER-clause is not explicitly specified, stored routines
-#     are created with CURRENT_USER privileges;
-#   2. check that if DEFINER-clause specifies non-current user, SUPER privilege
-#     is required to create a stored routine;
-#   3. check that if DEFINER-clause specifies non-existent user, a warning is
-#     emitted.
-#   4. check that SHOW CREATE PROCEDURE | FUNCTION works correctly;
-#
-# The following cases are tested in other test suites:
-#   - check that mysqldump dumps new attribute correctly;
-#   - check that slave replicates CREATE-statements with explicitly specified
-#     DEFINER correctly.
-#
-
-# Setup the environment.
-
---echo
---echo ---> connection: root
---connection con1root
-
---disable_warnings
-DROP DATABASE IF EXISTS mysqltest;
---enable_warnings
-
-CREATE DATABASE mysqltest;
-
-CREATE USER mysqltest_1@localhost;
-GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost;
-
-CREATE USER mysqltest_2@localhost;
-GRANT SUPER ON *.* TO mysqltest_2@localhost;
-GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost;
-
---connect (mysqltest_2_con,localhost,mysqltest_2,,mysqltest)
---connect (mysqltest_1_con,localhost,mysqltest_1,,mysqltest)
-
-# test case (1).
-
---echo
---echo ---> connection: mysqltest_2_con
---connection mysqltest_2_con
-
-USE mysqltest;
-
-CREATE PROCEDURE wl2897_p1() SELECT 1;
-
-CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1;
-
-# test case (2).
-
---echo
---echo ---> connection: mysqltest_1_con
---connection mysqltest_1_con
-
-USE mysqltest;
-
---error ER_SPECIFIC_ACCESS_DENIED_ERROR
-CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2;
-
---error ER_SPECIFIC_ACCESS_DENIED_ERROR
-CREATE DEFINER=root@localhost FUNCTION wl2897_f2() RETURNS INT RETURN 2;
-
-# test case (3).
-
---echo
---echo ---> connection: mysqltest_2_con
---connection mysqltest_2_con
-
-use mysqltest;
-
-CREATE DEFINER='a @ b @ c'@localhost PROCEDURE wl2897_p3() SELECT 3;
-
-CREATE DEFINER='a @ b @ c'@localhost FUNCTION wl2897_f3() RETURNS INT RETURN 3;
-
-# test case (4).
-
---echo
---echo ---> connection: con1root
---connection con1root
-
-USE mysqltest;
-
-SHOW CREATE PROCEDURE wl2897_p1;
-SHOW CREATE PROCEDURE wl2897_p3;
-
-SHOW CREATE FUNCTION wl2897_f1;
-SHOW CREATE FUNCTION wl2897_f3;
-
-# Cleanup.
-
-DROP USER mysqltest_1@localhost;
-DROP USER mysqltest_2@localhost;
-
-DROP DATABASE mysqltest;
-
---disconnect mysqltest_1_con
---disconnect mysqltest_2_con
-
-
-#
-# Bug#13198 SP executes if definer does not exist
-#
-
-# Prepare environment.
-
---echo
---echo ---> connection: root
---connection con1root
-
---disable_warnings
-DROP DATABASE IF EXISTS mysqltest;
---enable_warnings
-
-CREATE DATABASE mysqltest;
-
-CREATE USER mysqltest_1@localhost;
-GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost;
-
-CREATE USER mysqltest_2@localhost;
-GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost;
-
---connect (mysqltest_1_con,localhost,mysqltest_1,,mysqltest)
---connect (mysqltest_2_con,localhost,mysqltest_2,,mysqltest)
-
-# Create a procedure/function under u1.
-
---echo
---echo ---> connection: mysqltest_1_con
---connection mysqltest_1_con
-
-USE mysqltest;
-
-CREATE PROCEDURE bug13198_p1()
-  SELECT 1;
-
-CREATE FUNCTION bug13198_f1() RETURNS INT
-  RETURN 1;
-
-CALL bug13198_p1();
-
-SELECT bug13198_f1();
-
-# Check that u2 can call the procedure/function.
-
---echo
---echo ---> connection: mysqltest_2_con
---connection mysqltest_2_con
-
-USE mysqltest;
-
-CALL bug13198_p1();
-
-SELECT bug13198_f1();
-
-# Drop user u1 (definer of the object);
-
---echo
---echo ---> connection: root
---connection con1root
-
---disconnect mysqltest_1_con
-
-DROP USER mysqltest_1@localhost;
-
-# Check that u2 can not call the procedure/function.
-
---echo
---echo ---> connection: mysqltest_2_con
---connection mysqltest_2_con
-
-USE mysqltest;
-
---error ER_NO_SUCH_USER
-CALL bug13198_p1();
-
---error ER_NO_SUCH_USER
-SELECT bug13198_f1();
-
-# Cleanup.
-
---echo
---echo ---> connection: root
---connection con1root
-
---disconnect mysqltest_2_con
-
-DROP USER mysqltest_2@localhost;
-
-DROP DATABASE mysqltest;
-
-#
-# Bug#19857 When a user with CREATE ROUTINE priv creates a routine,
-#           it results in NULL p/w
-#
-
-# Can't test with embedded server that doesn't support grants
-
-GRANT USAGE ON *.* TO user19857@localhost IDENTIFIED BY 'meow';
-GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ROUTINE, ALTER ROUTINE ON test.* TO
-user19857@localhost;
-SELECT Host,User,Password FROM mysql.user WHERE User='user19857';
-
---connect (mysqltest_2_con,localhost,user19857,meow,test)
---echo
---echo ---> connection: mysqltest_2_con
---connection mysqltest_2_con
-
-USE test;
-
-DELIMITER //;
-  CREATE PROCEDURE sp19857() DETERMINISTIC
-  BEGIN
-    DECLARE a INT;
-    SET a=1;
-    SELECT a;
-  END //
-DELIMITER ;//
-
-SHOW CREATE PROCEDURE test.sp19857;
-
---disconnect mysqltest_2_con
---connect (mysqltest_2_con,localhost,user19857,meow,test)
---connection mysqltest_2_con
-
-DROP PROCEDURE IF EXISTS test.sp19857;
-
---echo
---echo ---> connection: root
---connection con1root
-
---disconnect mysqltest_2_con
-
-SELECT Host,User,Password FROM mysql.user WHERE User='user19857';
-
-DROP USER user19857@localhost;
-
---disconnect con1root
---connection default
-use test;
-
-#
-# Bug#18630 Arguments of suid routine calculated in wrong security context
-#
-# Arguments of suid routines were calculated in definer's security
-# context instead of caller's context thus creating security hole.
-#
---disable_warnings
-DROP TABLE IF EXISTS t1;
-DROP VIEW IF EXISTS v1;
-DROP FUNCTION IF EXISTS f_suid;
-DROP PROCEDURE IF EXISTS p_suid;
-DROP FUNCTION IF EXISTS f_evil;
---enable_warnings
-DELETE FROM mysql.user WHERE user LIKE 'mysqltest\_%';
-DELETE FROM mysql.db WHERE user LIKE 'mysqltest\_%';
-DELETE FROM mysql.tables_priv WHERE user LIKE 'mysqltest\_%';
-DELETE FROM mysql.columns_priv WHERE user LIKE 'mysqltest\_%';
-FLUSH PRIVILEGES;
-
-CREATE TABLE t1 (i INT);
-CREATE FUNCTION f_suid(i INT) RETURNS INT SQL SECURITY DEFINER RETURN 0;
-CREATE PROCEDURE p_suid(IN i INT) SQL SECURITY DEFINER SET @c:= 0;
-
-CREATE USER mysqltest_u1@localhost;
-# Thanks to this grant statement privileges of anonymous users on
-# 'test' database are not applicable for mysqltest_u1@localhost.
-GRANT EXECUTE ON test.* TO mysqltest_u1@localhost;
-
-delimiter |;
-CREATE DEFINER=mysqltest_u1@localhost FUNCTION f_evil () RETURNS INT
-  SQL SECURITY INVOKER
-BEGIN
-  SET @a:= CURRENT_USER();
-  SET @b:= (SELECT COUNT(*) FROM t1);
-  RETURN @b;
-END|
-delimiter ;|
-
-CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT f_evil();
-
-connect (conn1, localhost, mysqltest_u1,,);
-
---error ER_TABLEACCESS_DENIED_ERROR
-SELECT COUNT(*) FROM t1;
-
---error ER_TABLEACCESS_DENIED_ERROR
-SELECT f_evil();
-SELECT @a, @b;
-
---error ER_TABLEACCESS_DENIED_ERROR
-SELECT f_suid(f_evil());
-SELECT @a, @b;
-
---error ER_TABLEACCESS_DENIED_ERROR
-CALL p_suid(f_evil());
-SELECT @a, @b;
-
---error ER_TABLEACCESS_DENIED_ERROR
-SELECT * FROM v1;
-SELECT @a, @b;
-
-disconnect conn1;
-connection default;
-
-DROP VIEW v1;
-DROP FUNCTION f_evil;
-DROP USER mysqltest_u1@localhost;
-DROP PROCEDURE p_suid;
-DROP FUNCTION f_suid;
-DROP TABLE t1;
-
---echo #
---echo # Bug #48872 : Privileges for stored functions ignored if function name 
---echo #  is mixed case
---echo #
-
-CREATE DATABASE B48872;
-USE B48872;
-CREATE TABLE `TestTab` (id INT);
-INSERT INTO `TestTab` VALUES (1),(2);
-CREATE FUNCTION `f_Test`() RETURNS INT RETURN 123;
-CREATE FUNCTION `f_Test_denied`() RETURNS INT RETURN 123;
-CREATE USER 'tester';
-CREATE USER 'Tester';
-GRANT SELECT ON TABLE `TestTab` TO 'tester';
-GRANT EXECUTE ON FUNCTION `f_Test` TO 'tester';
-GRANT EXECUTE ON FUNCTION `f_Test_denied` TO 'Tester';
-
-SELECT f_Test();
-SELECT * FROM TestTab;
-
-CONNECT (con_tester,localhost,tester,,B48872);
-CONNECT (con_tester_denied,localhost,Tester,,B48872);
-CONNECTION con_tester;
-
-SELECT * FROM TestTab;
-SELECT `f_Test`();
-SELECT `F_TEST`();
-SELECT f_Test();
-SELECT F_TEST();
-
-CONNECTION con_tester_denied;
-
---disable_result_log
---error ER_TABLEACCESS_DENIED_ERROR
-SELECT * FROM TestTab;
---error ER_PROCACCESS_DENIED_ERROR
-SELECT `f_Test`();
---error ER_PROCACCESS_DENIED_ERROR
-SELECT `F_TEST`();
---error ER_PROCACCESS_DENIED_ERROR
-SELECT f_Test();
---error ER_PROCACCESS_DENIED_ERROR
-SELECT F_TEST();
---enable_result_log
-SELECT `f_Test_denied`();
-SELECT `F_TEST_DENIED`();
-
-CONNECTION default;
-DISCONNECT con_tester;
-DISCONNECT con_tester_denied;
-DROP TABLE `TestTab`;
-DROP FUNCTION `f_Test`;
-DROP FUNCTION `f_Test_denied`;
-
-USE test;
-DROP USER 'tester';
-DROP USER 'Tester';
-DROP DATABASE B48872;
-
---echo End of 5.0 tests.
-
-
---echo #
---echo # Test for bug#57061 "User without privilege on routine can discover
---echo # its existence."
---echo #
---disable_warnings
-drop database if exists mysqltest_db;
---enable_warnings
-create database mysqltest_db;
---echo # Create user with no privileges on mysqltest_db database.
-create user bug57061_user@localhost;
-create function mysqltest_db.f1() returns int return 0;
-create procedure mysqltest_db.p1() begin end;
---echo # Connect as user 'bug57061_user@localhost'
-connect (conn1, localhost, bug57061_user,,);
---echo # Attempt to drop routine on which user doesn't have privileges
---echo # should result in the same 'access denied' type of error whether
---echo # routine exists or not.
---error ER_PROCACCESS_DENIED_ERROR
-drop function if exists mysqltest_db.f_does_not_exist;
---error ER_PROCACCESS_DENIED_ERROR
-drop procedure if exists mysqltest_db.p_does_not_exist;
---error ER_PROCACCESS_DENIED_ERROR
-drop function if exists mysqltest_db.f1;
---error ER_PROCACCESS_DENIED_ERROR
-drop procedure if exists mysqltest_db.p1;
---echo # Connection 'default'.
-connection default;
-disconnect conn1;
-drop user bug57061_user@localhost;
-drop database mysqltest_db;
-
-
---echo #
---echo # Bug#11882603 SELECT_ACL ON ANY COLUMN IN MYSQL.PROC ALLOWS TO SEE
---echo #              DEFINITION OF ANY ROUTINE. 
---echo #
-
---disable_warnings
-DROP DATABASE IF EXISTS db1;
---enable_warnings
-
-CREATE DATABASE db1;
-CREATE PROCEDURE db1.p1() SELECT 1;
-CREATE USER user2@localhost IDENTIFIED BY '';
-GRANT SELECT(db) ON mysql.proc TO user2@localhost;
- 
---echo # Connection con2 as user2
-connect (con2, localhost, user2);
---echo # The statement below before disclosed info from body_utf8 column.
---error ER_SP_DOES_NOT_EXIST
-SHOW CREATE PROCEDURE db1.p1;
-
---echo # Check that SHOW works with SELECT grant on whole table
---echo # Connection default
-connection default;
-GRANT SELECT ON mysql.proc TO user2@localhost;
-
---echo # Connection con2
-connection con2;
---echo # This should work
-SHOW CREATE PROCEDURE db1.p1;
-
---echo # Connection default
-connection default;
-disconnect con2;
-DROP USER user2@localhost;
-DROP DATABASE db1;
-
-#
-# Bug#27407480: AUTOMATIC_SP_PRIVILEGES REQUIRES NEED THE INSERT PRIVILEGES FOR MYSQL.USER TABLE
-#
-create user foo@local_ost;
-#
-# Create a user with an authentification plugin 'foobar'.
-# Instead of using a normal "CREATE USER <user> IDENTIFIED VIA <plugin>"
-# we do CREATE (without VIA) followed by UPDATE and FLUSH.
-# This is to avoid installing a real plugin and thus avoid the test dependency.
-# We won't login under this user in the below test, so this is fine.
-#
-create user foo@`local\_ost`;
-update mysql.user set plugin='foobar' where host='local\\_ost';
-flush privileges;
-create database foodb;
-grant create routine on foodb.* to foo@local_ost;
-connect con1,localhost,foo;
-select user(), current_user();
-show grants;
-create procedure fooproc() select 'i am fooproc';
-show grants;
-disconnect con1;
-connection default;
-drop user foo@local_ost;
-drop user foo@`local\_ost`;
-drop procedure fooproc;
-drop database foodb;
-
---echo #
---echo # Test for bug#12602983 - User without privilege on routine can discover
---echo # its existence by executing "select non_existing_func();" or by 
---echo # "call non_existing_proc()";
---echo #
---disable_warnings
-drop database if exists mysqltest_db;
---enable_warnings
-create database mysqltest_db;
-create function mysqltest_db.f1() returns int return 0;
-create procedure mysqltest_db.p1() begin end;
-
---echo # Create user with no privileges on mysqltest_db database.
-create user bug12602983_user@localhost;
-
---echo # Connect as user 'bug12602983_user@localhost'
-connect (conn1, localhost, bug12602983_user,,);
-
---echo # Attempt to execute routine on which user doesn't have privileges
---echo # should result in the same 'access denied' error whether
---echo # routine exists or not.
---error ER_PROCACCESS_DENIED_ERROR
-select mysqltest_db.f_does_not_exist();
---error ER_PROCACCESS_DENIED_ERROR
-call mysqltest_db.p_does_not_exist();
-
---error ER_PROCACCESS_DENIED_ERROR
-select mysqltest_db.f1();
---error ER_PROCACCESS_DENIED_ERROR
-call mysqltest_db.p1();
-
---error ER_PROCACCESS_DENIED_ERROR
-create view bug12602983_v1 as select mysqltest_db.f_does_not_exist();
---error ER_PROCACCESS_DENIED_ERROR
-create view bug12602983_v1 as select mysqltest_db.f1();
-
---echo # Connection 'default'.
-connection default;
-disconnect conn1;
-drop user bug12602983_user@localhost;
-drop database mysqltest_db;
-
-# Wait till all disconnects are completed
---source include/wait_until_count_sessions.inc
diff --git a/mysql-test/r/type_float.test b/mysql-test/r/type_float.test
deleted file mode 100644
index 5dfb4a75bb3..00000000000
--- a/mysql-test/r/type_float.test
+++ /dev/null
@@ -1,417 +0,0 @@
-# Description
-# -----------
-# Numeric floating point.
-
---disable_warnings
-drop table if exists t1,t2;
---enable_warnings
-
-SELECT 10,10.0,10.,.1e+2,100.0e-1;
-SELECT 6e-16, -6e-16, --6e-16, -6e-16+1.000000;
-SELECT 1e1,1.e1,1.0e1,1e+1,1.e+1,1.0e+1,1e-1,1.e-1,1.0e-1;
-SELECT 0.001e+1,0.001e-1, -0.001e+01,-0.001e-01;
-SELECT 123.23E+02,-123.23E-02,"123.23E+02"+0.0,"-123.23E-02"+0.0;
-SELECT 2147483647E+02,21474836.47E+06;
-
-create table t1 (f1 float(24),f2 float(52));
-# We mask out Privileges column because it differs for embedded server
---replace_column 8 #
-show full columns from t1;
-insert into t1 values(10,10),(1e+5,1e+5),(1234567890,1234567890),(1e+10,1e+10),(1e+15,1e+15),(1e+20,1e+20),(1e+50,1e+50),(1e+150,1e+150);
-insert into t1 values(-10,-10),(1e-5,1e-5),(1e-10,1e-10),(1e-15,1e-15),(1e-20,1e-20),(1e-50,1e-50),(1e-150,1e-150);
-select * from t1;
-drop table t1;
-
-create table t1 (datum double);
-insert into t1 values (0.5),(1.0),(1.5),(2.0),(2.5);
-select * from t1;
-select * from t1 where datum < 1.5;
-select * from t1 where datum > 1.5;
-select * from t1 where datum = 1.5;
-drop table t1;
-
-create table t1 (a  decimal(7,3) not null, key (a));
-insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1");
-select a from t1 order by a;
-select min(a) from t1;
-drop table t1;
-
-#
-# BUG#3612, BUG#4393, BUG#4356, BUG#4394
-#
-
-create table t1 (c1 double, c2 varchar(20));
-insert t1 values (121,"16");
-select c1 + c1 * (c2 / 100) as col from t1;
-create table t2 select c1 + c1 * (c2 / 100) as col1, round(c1, 5) as col2, round(c1, 35) as col3, sqrt(c1*1e-15) col4 from t1;
-# Floats are a bit different in PS
---disable_ps_protocol
-select * from t2;
---enable_ps_protocol
-show create table t2;
-drop table t1,t2;
-
-# Bug #1022: When a table contains a 'float' field, 
-# and one of the functions MAX, MIN, or AVG is used on that field,
-# the system crashes.
-
-create table t1 (a float);
-insert into t1 values (1);
-select max(a),min(a),avg(a) from t1;
-drop table t1;
-
-#
-# FLOAT/DOUBLE/DECIMAL handling
-#
-
-create table t1 (f float, f2 float(24), f3 float(6,2), d double, d2 float(53), d3 double(10,3), de decimal, de2 decimal(6), de3 decimal(5,2), n numeric, n2 numeric(8), n3 numeric(7,6));
-# We mask out Privileges column because it differs for embedded server
---replace_column 8 #
-show full columns from t1;
-drop table t1;
-
-create table t1 (a  decimal(7,3) not null, key (a));
-insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1");
-select a from t1 order by a;
-select min(a) from t1;
-drop table t1;
-
---error 1425
-create table t1 (a float(200,100), b double(200,100));
-
-#
-# float in a char(1) field
-#
-create table t1 (c20 char);
-insert into t1 values (5000.0);
-insert into t1 values (0.5e4);
-drop table t1;
-
-# Errors
-
---error 1063
-create table t1 (f float(54));	# Should give an error
---disable_warnings
-drop table if exists t1;
---enable_warnings
-
-# Don't allow 'double unsigned' to be set to a negative value (Bug #7700)
-create table t1 (d1 double, d2 double unsigned);
-insert into t1 set d1 = -1.0;
-update t1 set d2 = d1;
-select * from t1;
-drop table t1;
-
-# Ensure that maximum values as the result of number of decimals
-# being specified in table schema are enforced (Bug #7361)
-create table t1 (f float(4,3));
-insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
-select * from t1;
-drop table if exists t1;
-create table t1 (f double(4,3));
-insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
-select * from t1;
-drop table if exists t1;
-
-# Check conversion of floats to character field (Bug #7774)
-create table t1 (c char(20));
-insert into t1 values (5e-28);
-select * from t1;
-drop table t1;
-create table t1 (c char(6));
-insert into t1 values (2e5),(2e6),(2e-4),(2e-5);
-select * from t1;
-drop table t1;
-
-#
-# Test of comparison of integer with float-in-range (Bug #7840)
-# This is needed because some ODBC applications (like Foxpro) uses
-# floats for everything.
-#
-
-CREATE TABLE t1 (
-  reckey int unsigned NOT NULL,
-  recdesc varchar(50) NOT NULL,
-  PRIMARY KEY  (reckey)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-
-INSERT INTO t1 VALUES (108, 'Has 108 as key');
-INSERT INTO t1 VALUES (109, 'Has 109 as key');
-select * from t1 where reckey=108;
-select * from t1 where reckey=1.08E2;
-select * from t1 where reckey=109;
-select * from t1 where reckey=1.09E2;
-drop table t1;
-
-#
-# Bug #13372 (decimal union)
-#
-create table t1 (d double(10,1));
-create table t2 (d double(10,9));
-insert into t1 values ("100000000.0");
-insert into t2 values ("1.23456780");
-create table t3 select * from t2 union select * from t1;
-select * from t3;
-show create table t3;
-drop table t1, t2, t3;
-
-
-#
-# Bug #9855 (inconsistent column type for create select
-#
-create table t1 select  105213674794682365.00 + 0.0 x;
-show warnings;
-desc  t1;
-drop table t1;
-
-create table t1 select 0.0 x;
-desc t1;
-create table t2 select 105213674794682365.00 y;
-desc t2;
-create table t3 select x+y a from t1,t2;
-show warnings;
-desc t3;
-drop table t1,t2,t3;
-
-#
-# Bug #22129: A small double precision number becomes zero
-#
-# check if underflows are detected correctly
-select 1e-308, 1.00000001e-300, 100000000e-300;
-
-# check if overflows are detected correctly
-select 10e307;
-
-#
-# Bug #19690: ORDER BY eliminates rows from the result
-#
-create table t1(a int, b double(8, 2));
-insert into t1 values
-(1, 28.50), (1, 121.85), (1, 157.23), (1, 1351.00), (1, -1965.35), (1, 81.75), 
-(1, 217.08), (1, 7.94), (4, 96.07), (4, 6404.65), (4, -6500.72), (2, 100.00),
-(5, 5.00), (5, -2104.80), (5, 2033.80), (5, 0.07), (5, 65.93),
-(3, -4986.24), (3, 5.00), (3, 4857.34), (3, 123.74), (3,  0.16),
-(6, -1695.31), (6, 1003.77), (6, 499.72), (6, 191.82);
-explain select sum(b) s from t1 group by a;
-select sum(b) s from t1 group by a;
-select sum(b) s from t1 group by a having s <> 0;
-select sum(b) s from t1 group by a having s <> 0 order by s;
-select sum(b) s from t1 group by a having s <=> 0;
-select sum(b) s from t1 group by a having s <=> 0 order by s;
-alter table t1 add key (a, b);
-explain select sum(b) s from t1 group by a;
-select sum(b) s from t1 group by a;
-select sum(b) s from t1 group by a having s <> 0;
-select sum(b) s from t1 group by a having s <> 0 order by s;
-select sum(b) s from t1 group by a having s <=> 0;
-select sum(b) s from t1 group by a having s <=> 0 order by s;
-drop table t1;
-
---echo End of 4.1 tests
-
-#
-# bug #12694 (float(m,d) specifications)
-#
-
---error 1427
-create table t1 (s1 float(0,2));
---error 1427
-create table t1 (s1 float(1,2));
-
-#
-# MySQL Bugs: #11589: mysqltest --ps-protocol, strange output, float/double/real with zerofill
-#
-
-CREATE TABLE t1 (
-  f1 real zerofill,
-  f2 double zerofill,
-  f3 float zerofill);
-INSERT INTO t1 VALUES ( 0.314152e+1, 0.314152e+1, 0.314152e+1);
-
-let $my_stmt= select f1, f2, f3 FROM t1;
-eval PREPARE stmt1 FROM '$my_stmt';
-select f1, f2, f3 FROM t1;
-eval $my_stmt;
-EXECUTE stmt1;
-
-DROP TABLE t1;
-# Bug #28121 "INSERT or UPDATE into DOUBLE(200,0) field being truncated to 31 digits"
-#
-
-create table t1 (f1 double(200, 0));
-insert into t1 values (1e199), (-1e199);
-insert into t1 values (1e200), (-1e200);
-insert into t1 values (2e200), (-2e200);
-select f1 + 0e0 from t1;
-drop table t1;
-
-create table t1 (f1 float(30, 0));
-insert into t1 values (1e29), (-1e29);
-insert into t1 values (1e30), (-1e30);
-insert into t1 values (2e30), (-2e30);
-select f1 + 0e0 from t1;
-drop table t1;
-
-#
-# Bug #12860 "Difference in zero padding of exponent between Unix and Windows"
-#
-
-create table t1 (c char(6));
-insert into t1 values (2e6),(2e-5);
-select * from t1;
-drop table t1;
-
-#
-# Bug #21497 "DOUBLE truncated to unusable value"
-#
-
-CREATE TABLE d1 (d DOUBLE);
-INSERT INTO d1 VALUES (1.7976931348623157E+308);
-SELECT * FROM d1;
---error ER_ILLEGAL_VALUE_FOR_TYPE
-INSERT INTO d1 VALUES (1.79769313486232e+308);
-SELECT * FROM d1;
-DROP TABLE d1;
-
-#
-# Bug #26788 "mysqld (debug) aborts when inserting specific numbers into char 
-#             fields"
-#
-
-create table t1 (a char(20));
-insert into t1 values (1.225e-05);
-select a+0 from t1;
-drop table t1;
-
-# 
-# Bug #27483: Casting 'scientific notation type' to 'unsigned bigint' fails on 
-#             windows.
-#
-
-create table t1(d double, u bigint unsigned);
-
-insert into t1(d) values (9.22337203685479e18),
-                         (1.84e19);
-
-update t1 set u = d;
-select u from t1;
-
-drop table t1;
-
-#
-# Bug #21205: Different number of digits for float/doble/real in --ps-protocol
-#
-
-CREATE TABLE t1 (f1 DOUBLE);
-INSERT INTO t1 VALUES(-1.79769313486231e+308);
-SELECT f1 FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # Bug#12406055 BUFFER OVERFLOW OF VARIABLE 'BUFF' IN STRING::SET_REAL
---echo # 
-
---echo # Ignoring output from misc. float operations
---disable_result_log
-
-let $nine_65=
-99999999999999999999999999999999999999999999999999999999999999999;
-
-select format(-1.7976931348623157E+307,256) as foo;
-select least(-1.1111111111111111111111111,
-             - group_concat(1.7976931348623157E+308)) as foo;
-eval select concat((truncate((-1.7976931348623157E+307),(0x1e))),
-                   ($nine_65)) into @a;
---enable_result_log
-
---echo End of 5.0 tests
-
---echo #
---echo # Bug#12368853 FORMAT() CRASHES WITH LARGE NUMBERS AFTER TRUNCATE...
---echo #
-
-select format(truncate('1.7976931348623157E+308',-12),1,'fr_BE') as foo;
-
-
---echo #
---echo # MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result
---echo #
-
-SELECT LEFT('a',EXP(50));
-SELECT LEFT('a', COALESCE(1e30));
-
-CREATE TABLE t1 (a FLOAT);
-INSERT INTO t1 VALUES (1e30);
-SELECT LEFT('a',a), LEFT('a',1e30) FROM t1;
-DROP TABLE t1;
-
-PREPARE stmt FROM 'SELECT LEFT(111,?)';
-SET @a=1e30;
-EXECUTE stmt USING @a;
-DEALLOCATE PREPARE stmt;
-
-CREATE TABLE t1 (a INT);
-INSERT INTO t1 VALUES (1),(2),(3);
-SELECT LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1));
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DOUBLE);
-INSERT INTO t1 VALUES (1e30),(0);
-SELECT LEFT('a', SUM(a)) FROM t1;
-SELECT LEFT('a', AVG(a)) FROM t1;
-DROP TABLE t1;
-
-
---echo #
---echo # Bug #13500371 63704: CONVERSION OF '1.' TO A NUMBER GIVES ERROR 1265
---echo # (WARN_DATA_TRUNCATED)
---echo #
-
-CREATE TABLE t1 (f FLOAT);
-INSERT INTO t1 VALUES ('1.');
-INSERT INTO t1 VALUES ('2.0.');
-INSERT INTO t1 VALUES ('.');
-SELECT * FROM t1 ORDER BY f;
-DROP TABLE t1;
-
-
---echo #
---echo # Start of 10.0 tests
---echo #
-
---echo #
---echo # MDEV-6950 Bad results with joins comparing DATE/DATETIME and INT/DECIMAL/DOUBLE/ENUM/VARCHAR columns
---echo #
-CREATE TABLE t1 (a DATETIME PRIMARY KEY);
-INSERT INTO t1 VALUES ('1999-01-01 00:00:00');
-CREATE TABLE t2 (a DOUBLE);
-INSERT INTO t2 VALUES (19990101000000);
-INSERT INTO t2 VALUES (990101000000);  
-SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
-SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
-ALTER TABLE t2 ADD PRIMARY KEY(a);
-SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
-SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
---echo # t2 should NOT be eliminated
-EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
-DROP TABLE t1,t2; 
-
---echo #
---echo # MDEV-6971 Bad results with joins comparing TIME and DOUBLE/DECIMAL columns
---echo #
-CREATE TABLE t1 (a TIME(6) PRIMARY KEY);
-INSERT INTO t1 VALUES ('10:20:30');
-CREATE TABLE t2 (a DOUBLE);
-INSERT INTO t2 VALUES (102030),(102030.000000001);
-SELECT t1.* FROM t1 JOIN t2 USING(a);
-SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
-ALTER TABLE t2 ADD PRIMARY KEY(a);
-SELECT t1.* FROM t1 JOIN t2 USING(a);
-SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
---echo # t2 should NOT be elimitated
-EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
-DROP TABLE t1,t2;
-
---echo #
---echo # End of 10.0 tests
---echo #

    

[Commits] 8aa4cff7b5b: Revert incorrect merge.

jan