MDEV-5317 Compound statement / anonymous blocks

originally based on the patch by Antony T Curtis

1 files changed, 121 insertions, 0 deletions

diff --git a/mysql-test/r/compound.result b/mysql-test/r/compound.result
new file mode 100644
index 00000000000..84c3cdbfc04
--- /dev/null
+++ b/mysql-test/r/compound.result
@@ -0,0 +1,121 @@
+CREATE TABLE t1 (a INT PRIMARY KEY)|
+BEGIN NOT ATOMIC
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (2);
+INSERT INTO t1 VALUES (3);
+END|
+SELECT * FROM t1|
+a
+1
+2
+3
+PREPARE stmt FROM "BEGIN NOT ATOMIC
+  INSERT INTO t1 VALUES (4);
+  INSERT INTO t1 VALUES (5);
+  INSERT INTO t1 VALUES (?);
+END";
+SET @val = 6|
+EXECUTE stmt USING @val|
+SELECT * FROM t1|
+a
+1
+2
+3
+4
+5
+6
+PREPARE stmt FROM "BEGIN NOT ATOMIC
+  DECLARE v_res INT;
+  SELECT COUNT(*) INTO v_res FROM t1;
+  SELECT 'Hello World', v_res INTO ?,?;
+END"|
+SET @val="", @val2=""|
+EXECUTE stmt USING @val, @val2|
+SELECT @val, @val2|
+@val	@val2
+Hello World	6
+DROP TABLE t1|
+CREATE DATABASE mysqltest1|
+CREATE PROCEDURE mysqltest1.sp1()
+BEGIN
+PREPARE stmt FROM "BEGIN NOT ATOMIC CREATE TABLE t1 AS SELECT DATABASE(); END";
+EXECUTE stmt;
+END|
+CALL mysqltest1.sp1()|
+SELECT * FROM mysqltest1.t1|
+DATABASE()
+mysqltest1
+USE mysqltest1|
+DROP DATABASE mysqltest1|
+BEGIN NOT ATOMIC CREATE TABLE t1(a int); END|
+ERROR 3D000: No database selected
+BEGIN NOT ATOMIC SET @a=1; CREATE TABLE test.t1(a int); END|
+USE test|
+show tables|
+Tables_in_test
+t1
+drop table t1|
+/**/ if (select count(*) from information_schema.tables
+where table_schema='test' and table_name='t1') = 0
+then
+create table t1 (a int);
+end if|
+show tables|
+Tables_in_test
+t1
+/**/ if (select count(*) from information_schema.tables
+where table_schema='test' and table_name='t1') = 0
+then
+create table t1 (a int);
+end if|
+show tables|
+Tables_in_test
+t1
+case (select table_name from information_schema.tables where table_schema='test')
+when 't1' then create table t2 (b int);
+when 't2' then create table t3 (b int);
+else signal sqlstate '42S02';
+end case|
+show tables|
+Tables_in_test
+t1
+t2
+case
+when database() = 'test' then create table t3 (test text);
+when now() < date'2001-02-03' then create table oops (machine time);
+end case|
+show tables|
+Tables_in_test
+t1
+t2
+t3
+loop
+create table t4 (a int);
+end loop|
+ERROR 42S01: Table 't4' already exists
+show tables|
+Tables_in_test
+t1
+t2
+t3
+t4
+set @a=0;
+repeat
+set @a = @a + 1;
+until @a > 5
+end repeat|
+select @a|
+@a
+6
+/**/ while (select count(*) from information_schema.tables where table_schema='test')
+do
+select concat('drop table ', table_name) into @a
+from information_schema.tables where table_schema='test' limit 1;
+select @a as 'executing:';
+prepare dt from @a;
+execute dt;
+end while|
+executing:	drop table t1
+executing:	drop table t2
+executing:	drop table t3
+executing:	drop table t4