Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 324 insertions, 0 deletions

diff --git a/mysql-test/main/alter_table_combinations.result b/mysql-test/main/alter_table_combinations.result
new file mode 100644
index 00000000..cd47c78d
--- /dev/null
+++ b/mysql-test/main/alter_table_combinations.result
@@ -0,0 +1,324 @@
+set @save_default_engine= @@default_storage_engine;
+#
+# MDEV-25803 Inplace ALTER breaks MyISAM/Aria tables when order of keys is changed
+#
+create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x)) engine myisam;
+alter table t1 change x xx int, algorithm=inplace;
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x));
+alter table t1 change x xx int, algorithm=inplace;
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+drop table t1;
+#
+# End of 10.3 tests
+#
+#
+# MDEV-16290 ALTER TABLE ... RENAME COLUMN syntax
+#
+CREATE TABLE t1(a INT, b VARCHAR(30), c FLOAT);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` varchar(30) DEFAULT NULL,
+  `c` float DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+INSERT INTO t1 VALUES(1,'abcd',1.234);
+CREATE TABLE t2(a INT, b VARCHAR(30), c FLOAT) ENGINE=MyIsam;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` int(11) DEFAULT NULL,
+  `b` varchar(30) DEFAULT NULL,
+  `c` float DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+INSERT INTO t2 VALUES(1,'abcd',1.234);
+ALTER TABLE t1 RENAME COLUMN a TO a;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` varchar(30) DEFAULT NULL,
+  `c` float DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t1 RENAME COLUMN a TO m;
+ALTER TABLE t1 RENAME COLUMN a TO m;
+ERROR 42S22: Unknown column 'a' in 't1'
+ALTER TABLE t1 RENAME COLUMN IF EXISTS a TO m;
+Warnings:
+Note	1054	Unknown column 'a' in 't1'
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `m` int(11) DEFAULT NULL,
+  `b` varchar(30) DEFAULT NULL,
+  `c` float DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT * FROM t1;
+m	b	c
+1	abcd	1.234
+ALTER TABLE t1 RENAME COLUMN m TO x,
+RENAME COLUMN b TO y,
+RENAME COLUMN c TO z;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL,
+  `y` varchar(30) DEFAULT NULL,
+  `z` float DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT * FROM t1;
+x	y	z
+1	abcd	1.234
+ALTER TABLE t2 RENAME COLUMN a TO d, RENAME COLUMN b TO e, RENAME COLUMN c to f;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `d` int(11) DEFAULT NULL,
+  `e` varchar(30) DEFAULT NULL,
+  `f` float DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT * FROM t2;
+d	e	f
+1	abcd	1.234
+ALTER TABLE t1 CHANGE COLUMN x a INT, RENAME COLUMN y TO b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` varchar(30) DEFAULT NULL,
+  `z` float DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t1 CHANGE COLUMN z c DOUBLE, RENAME COLUMN b to b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` varchar(30) DEFAULT NULL,
+  `c` double DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t1 CHANGE COLUMN a b int, RENAME COLUMN b TO c, CHANGE COLUMN c d FLOAT;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `c` varchar(30) DEFAULT NULL,
+  `d` float DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t1 ADD COLUMN zz INT, RENAME COLUMN d TO f;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `c` varchar(30) DEFAULT NULL,
+  `f` float DEFAULT NULL,
+  `zz` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t1 DROP COLUMN zz, RENAME COLUMN c TO zz;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `zz` varchar(30) DEFAULT NULL,
+  `f` float DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t1 RENAME COLUMN zz to c, DROP COLUMN f;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `c` varchar(30) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t1 ADD COLUMN d INT DEFAULT 5, RENAME COLUMN c TO b, DROP COLUMN b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` varchar(30) DEFAULT NULL,
+  `d` int(11) DEFAULT 5
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t1 RENAME COLUMN b TO d, RENAME COLUMN d TO b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` varchar(30) DEFAULT NULL,
+  `b` int(11) DEFAULT 5
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t1 ADD KEY(b);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` varchar(30) DEFAULT NULL,
+  `b` int(11) DEFAULT 5,
+  KEY `b` (`b`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t1 RENAME COLUMN b TO bb;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` varchar(30) DEFAULT NULL,
+  `bb` int(11) DEFAULT 5,
+  KEY `b` (`bb`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT * FROM t1;
+d	bb
+abcd	5
+CREATE TABLE t3(a int, b int, KEY(b));
+ALTER TABLE t3 ADD CONSTRAINT FOREIGN KEY(b) REFERENCES t1(bb);
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  KEY `b` (`b`),
+  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`bb`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t1 RENAME COLUMN bb TO b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` varchar(30) DEFAULT NULL,
+  `b` int(11) DEFAULT 5,
+  KEY `b` (`b`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t3 RENAME COLUMN b TO c;
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `a` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  KEY `b` (`c`),
+  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`b`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+CREATE TABLE t4(a int);
+ALTER TABLE t4 RENAME COLUMN a TO aa, ALGORITHM = INPLACE;
+SHOW CREATE TABLE t4;
+Table	Create Table
+t4	CREATE TABLE `t4` (
+  `aa` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t4 RENAME COLUMN aa TO a, ALGORITHM = COPY;
+SHOW CREATE TABLE t4;
+Table	Create Table
+t4	CREATE TABLE `t4` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE t4;
+CREATE VIEW v1 AS SELECT d,e,f FROM t2;
+CREATE TRIGGER trg1 BEFORE UPDATE on t2 FOR EACH ROW SET NEW.d=OLD.d + 10;
+CREATE PROCEDURE sp1() INSERT INTO t2(d) VALUES(10);
+ALTER TABLE t2 RENAME COLUMN d TO g;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `g` int(11) DEFAULT NULL,
+  `e` varchar(30) DEFAULT NULL,
+  `f` float DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t2`.`d` AS `d`,`test`.`t2`.`e` AS `e`,`test`.`t2`.`f` AS `f` from `t2`	latin1	latin1_swedish_ci
+Warnings:
+Warning	1356	View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+SELECT * FROM v1;
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+UPDATE t2 SET f = f + 10;
+ERROR 42S22: Unknown column 'd' in 'NEW'
+CALL sp1();
+ERROR 42S22: Unknown column 'd' in 'field list'
+DROP TRIGGER trg1;
+DROP PROCEDURE sp1;
+CREATE TABLE t_gen(a INT, b DOUBLE GENERATED ALWAYS AS (SQRT(a)));
+INSERT INTO t_gen(a) VALUES(4);
+SELECT * FROM t_gen;
+a	b
+4	2
+SHOW CREATE TABLE t_gen;
+Table	Create Table
+t_gen	CREATE TABLE `t_gen` (
+  `a` int(11) DEFAULT NULL,
+  `b` double GENERATED ALWAYS AS (sqrt(`a`)) VIRTUAL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t_gen RENAME COLUMN a TO c, CHANGE COLUMN b b DOUBLE GENERATED ALWAYS AS (SQRT(c));
+SELECT * FROM t_gen;
+c	b
+4	2
+SHOW CREATE TABLE t_gen;
+Table	Create Table
+t_gen	CREATE TABLE `t_gen` (
+  `c` int(11) DEFAULT NULL,
+  `b` double GENERATED ALWAYS AS (sqrt(`c`)) VIRTUAL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t_gen CHANGE COLUMN c x INT;
+show create table t_gen;
+Table	Create Table
+t_gen	CREATE TABLE `t_gen` (
+  `x` int(11) DEFAULT NULL,
+  `b` double GENERATED ALWAYS AS (sqrt(`x`)) VIRTUAL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t_gen RENAME COLUMN x TO a;
+DROP TABLE t_gen;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` varchar(30) DEFAULT NULL,
+  `b` int(11) DEFAULT 5,
+  KEY `b` (`b`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ALTER TABLE t1 RENAME COLUMN b z;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'z' at line 1
+ALTER TABLE t1 RENAME COLUMN FROM b TO z;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM b TO z' at line 1
+ALTER TABLE t1 RENAME COLUMN b TO 1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1' at line 1
+ALTER TABLE t1 RENAME COLUMN b TO e, RENAME COLUMN c TO e;
+ERROR 42S22: Unknown column 'c' in 't1'
+ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN b TO z;
+ERROR 42S21: Duplicate column name 'z'
+ALTER TABLE t1 DROP COLUMN b, RENAME COLUMN b TO z;
+ERROR 42S22: Unknown column 'b' in 't1'
+ALTER TABLE t1 RENAME COLUMN b TO b, RENAME COLUMN b TO b;
+ERROR 42S22: Unknown column 'b' in 't1'
+ALTER TABLE t1 RENAME COLUMN b TO c3, DROP COLUMN c3;
+ERROR 42000: Can't DROP COLUMN `c3`; check that it exists
+ALTER TABLE t1 ADD COLUMN z INT, CHANGE COLUMN z y INT, DROP COLUMN y;
+ERROR 42S22: Unknown column 'z' in 't1'
+ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN z TO y, DROP COLUMN y;
+ERROR 42S22: Unknown column 'z' in 't1'
+ALTER TABLE t1 RENAME COLUMN b TO `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn`;
+ERROR 42000: Incorrect column name 'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn'
+ALTER TABLE t1 CHANGE b `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn` int;
+ERROR 42000: Identifier name 'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn' is too long
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` varchar(30) DEFAULT NULL,
+  `b` int(11) DEFAULT 5,
+  KEY `b` (`b`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT * FROM t1;
+d	b
+abcd	5
+DROP VIEW v1;
+DROP TABLE t3,t1,t2;
+#
+# MDEV-25803 Inplace ALTER breaks MyISAM/Aria tables when order of keys is changed
+#
+create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x)) engine myisam;
+alter table t1 change x xx int, algorithm=inplace;
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x));
+alter table t1 change x xx int, algorithm=inplace;
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+drop table t1;
+#
+# End of 10.5 tests
+#
+set @@default_storage_engine= @save_default_engine;