1SET sql_mode=ORACLE; 2# 3# MDEV-10914 ROW data type for stored routine variables 4# 5CREATE TABLE t1 (a INT, b INT); 6CREATE PROCEDURE p1 7AS 8rec ROW(a INT,b INT); 9BEGIN 10rec.a:=100; 11rec.b:=200; 12INSERT INTO t1 VALUES (rec.a,rec.b); 13INSERT INTO t1 VALUES (10, rec=ROW(100,200)); 14INSERT INTO t1 VALUES (10, ROW(100,200)=rec); 15INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE rec=ROW(100,200); 16INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=rec; 17rec.a:=NULL; 18INSERT INTO t1 VALUES (11, rec=ROW(100,200)); 19INSERT INTO t1 VALUES (11, rec=ROW(100,201)); 20INSERT INTO t1 VALUES (11, ROW(100,200)=rec); 21INSERT INTO t1 VALUES (11, ROW(100,201)=rec); 22INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE rec=ROW(100,200); 23INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=rec; 24rec.b:=NULL; 25INSERT INTO t1 VALUES (12, rec=ROW(100,200)); 26INSERT INTO t1 VALUES (12, ROW(100,200)=rec); 27INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE rec=ROW(100,200); 28INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=rec; 29END; 30$$ 31CALL p1(); 32SELECT * FROM t1; 33a b 34100 200 3510 1 3610 1 3710 20 3810 21 3911 NULL 4011 0 4111 NULL 4211 0 4312 NULL 4412 NULL 45DROP TABLE t1; 46DROP PROCEDURE p1; 47include/show_binlog_events.inc 48Log_name Pos Event_type Server_id End_log_pos Info 49master-bin.000001 # Gtid # # GTID #-#-# 50master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b INT) 51master-bin.000001 # Gtid # # GTID #-#-# 52master-bin.000001 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PROCEDURE "p1"() 53AS 54rec ROW(a INT,b INT); 55BEGIN 56rec.a:=100; 57rec.b:=200; 58INSERT INTO t1 VALUES (rec.a,rec.b); 59INSERT INTO t1 VALUES (10, rec=ROW(100,200)); 60INSERT INTO t1 VALUES (10, ROW(100,200)=rec); 61INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE rec=ROW(100,200); 62INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=rec; 63rec.a:=NULL; 64INSERT INTO t1 VALUES (11, rec=ROW(100,200)); 65INSERT INTO t1 VALUES (11, rec=ROW(100,201)); 66INSERT INTO t1 VALUES (11, ROW(100,200)=rec); 67INSERT INTO t1 VALUES (11, ROW(100,201)=rec); 68INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE rec=ROW(100,200); 69INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=rec; 70rec.b:=NULL; 71INSERT INTO t1 VALUES (12, rec=ROW(100,200)); 72INSERT INTO t1 VALUES (12, ROW(100,200)=rec); 73INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE rec=ROW(100,200); 74INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=rec; 75END 76master-bin.000001 # Gtid # # BEGIN GTID #-#-# 77master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('rec.a',100), NAME_CONST('rec.b',200)) 78master-bin.000001 # Query # # COMMIT 79master-bin.000001 # Gtid # # BEGIN GTID #-#-# 80master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (10, ROW(100,200)=ROW(100,200)) 81master-bin.000001 # Query # # COMMIT 82master-bin.000001 # Gtid # # BEGIN GTID #-#-# 83master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (10, ROW(100,200)=ROW(100,200)) 84master-bin.000001 # Query # # COMMIT 85master-bin.000001 # Gtid # # BEGIN GTID #-#-# 86master-bin.000001 # Query # # use `test`; INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE ROW(100,200)=ROW(100,200) 87master-bin.000001 # Query # # COMMIT 88master-bin.000001 # Gtid # # BEGIN GTID #-#-# 89master-bin.000001 # Query # # use `test`; INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=ROW(100,200) 90master-bin.000001 # Query # # COMMIT 91master-bin.000001 # Gtid # # BEGIN GTID #-#-# 92master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (11, ROW(NULL,200)=ROW(100,200)) 93master-bin.000001 # Query # # COMMIT 94master-bin.000001 # Gtid # # BEGIN GTID #-#-# 95master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (11, ROW(NULL,200)=ROW(100,201)) 96master-bin.000001 # Query # # COMMIT 97master-bin.000001 # Gtid # # BEGIN GTID #-#-# 98master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (11, ROW(100,200)=ROW(NULL,200)) 99master-bin.000001 # Query # # COMMIT 100master-bin.000001 # Gtid # # BEGIN GTID #-#-# 101master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (11, ROW(100,201)=ROW(NULL,200)) 102master-bin.000001 # Query # # COMMIT 103master-bin.000001 # Gtid # # BEGIN GTID #-#-# 104master-bin.000001 # Query # # use `test`; INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE ROW(NULL,200)=ROW(100,200) 105master-bin.000001 # Query # # COMMIT 106master-bin.000001 # Gtid # # BEGIN GTID #-#-# 107master-bin.000001 # Query # # use `test`; INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=ROW(NULL,200) 108master-bin.000001 # Query # # COMMIT 109master-bin.000001 # Gtid # # BEGIN GTID #-#-# 110master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (12, ROW(NULL,NULL)=ROW(100,200)) 111master-bin.000001 # Query # # COMMIT 112master-bin.000001 # Gtid # # BEGIN GTID #-#-# 113master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (12, ROW(100,200)=ROW(NULL,NULL)) 114master-bin.000001 # Query # # COMMIT 115master-bin.000001 # Gtid # # BEGIN GTID #-#-# 116master-bin.000001 # Query # # use `test`; INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE ROW(NULL,NULL)=ROW(100,200) 117master-bin.000001 # Query # # COMMIT 118master-bin.000001 # Gtid # # BEGIN GTID #-#-# 119master-bin.000001 # Query # # use `test`; INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=ROW(NULL,NULL) 120master-bin.000001 # Query # # COMMIT 121master-bin.000001 # Gtid # # GTID #-#-# 122master-bin.000001 # Query # # use `test`; DROP TABLE "t1" /* generated by server */ 123master-bin.000001 # Gtid # # GTID #-#-# 124master-bin.000001 # Query # # use `test`; DROP PROCEDURE p1 125# 126# Testing ROW fields in LIMIT 127# 128FLUSH LOGS; 129CREATE TABLE t1 (a INT); 130INSERT INTO t1 VALUES (10),(10); 131CREATE TABLE t2 (a INT); 132CREATE PROCEDURE p1() 133AS 134a INT:= 1; 135rec ROW(a INT); 136BEGIN 137rec.a:= 1; 138INSERT INTO t2 SELECT 1 FROM t1 LIMIT a; 139INSERT INTO t2 SELECT 2 FROM t1 LIMIT rec.a; 140END; 141$$ 142CALL p1(); 143Warnings: 144Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted 145DROP TABLE t1,t2; 146DROP PROCEDURE p1; 147include/show_binlog_events.inc 148Log_name Pos Event_type Server_id End_log_pos Info 149master-bin.000002 # Binlog_checkpoint # # master-bin.000002 150master-bin.000002 # Gtid # # GTID #-#-# 151master-bin.000002 # Query # # use `test`; CREATE TABLE t1 (a INT) 152master-bin.000002 # Gtid # # BEGIN GTID #-#-# 153master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (10),(10) 154master-bin.000002 # Query # # COMMIT 155master-bin.000002 # Gtid # # GTID #-#-# 156master-bin.000002 # Query # # use `test`; CREATE TABLE t2 (a INT) 157master-bin.000002 # Gtid # # GTID #-#-# 158master-bin.000002 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PROCEDURE "p1"() 159AS 160a INT:= 1; 161rec ROW(a INT); 162BEGIN 163rec.a:= 1; 164INSERT INTO t2 SELECT 1 FROM t1 LIMIT a; 165INSERT INTO t2 SELECT 2 FROM t1 LIMIT rec.a; 166END 167master-bin.000002 # Gtid # # BEGIN GTID #-#-# 168master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT 1 FROM t1 LIMIT 1 169master-bin.000002 # Query # # COMMIT 170master-bin.000002 # Gtid # # BEGIN GTID #-#-# 171master-bin.000002 # Query # # use `test`; INSERT INTO t2 SELECT 2 FROM t1 LIMIT 1 172master-bin.000002 # Query # # COMMIT 173master-bin.000002 # Gtid # # GTID #-#-# 174master-bin.000002 # Query # # use `test`; DROP TABLE "t1","t2" /* generated by server */ 175master-bin.000002 # Gtid # # GTID #-#-# 176master-bin.000002 # Query # # use `test`; DROP PROCEDURE p1 177# 178# End of MDEV-10914 ROW data type for stored routine variables 179# 180# 181# MDEV-12291 Allow ROW variables as SELECT INTO targets 182# 183FLUSH LOGS; 184SET sql_mode=DEFAULT; 185CREATE TABLE t1 (a INT, b VARCHAR(32)); 186INSERT INTO t1 VALUES (10, 'b10'); 187CREATE TABLE t2 LIKE t1; 188CREATE PROCEDURE p1() 189BEGIN 190DECLARE rec1 ROW(a INT, b VARCHAR(32)); 191SELECT * INTO rec1 FROM t1; 192INSERT INTO t2 VALUES (rec1.a, rec1.b); 193END; 194$$ 195CALL p1(); 196SELECT * FROM t1; 197a b 19810 b10 199DROP TABLE t1; 200DROP TABLE t2; 201DROP PROCEDURE p1; 202include/show_binlog_events.inc 203Log_name Pos Event_type Server_id End_log_pos Info 204master-bin.000003 # Binlog_checkpoint # # master-bin.000003 205master-bin.000003 # Gtid # # GTID #-#-# 206master-bin.000003 # Query # # use `test`; CREATE TABLE t1 (a INT, b VARCHAR(32)) 207master-bin.000003 # Gtid # # BEGIN GTID #-#-# 208master-bin.000003 # Query # # use `test`; INSERT INTO t1 VALUES (10, 'b10') 209master-bin.000003 # Query # # COMMIT 210master-bin.000003 # Gtid # # GTID #-#-# 211master-bin.000003 # Query # # use `test`; CREATE TABLE t2 LIKE t1 212master-bin.000003 # Gtid # # GTID #-#-# 213master-bin.000003 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() 214BEGIN 215DECLARE rec1 ROW(a INT, b VARCHAR(32)); 216SELECT * INTO rec1 FROM t1; 217INSERT INTO t2 VALUES (rec1.a, rec1.b); 218END 219master-bin.000003 # Gtid # # BEGIN GTID #-#-# 220master-bin.000003 # Query # # use `test`; INSERT INTO t2 VALUES ( NAME_CONST('rec1.a',10), NAME_CONST('rec1.b',_latin1'b10' COLLATE 'latin1_swedish_ci')) 221master-bin.000003 # Query # # COMMIT 222master-bin.000003 # Gtid # # GTID #-#-# 223master-bin.000003 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ 224master-bin.000003 # Gtid # # GTID #-#-# 225master-bin.000003 # Query # # use `test`; DROP TABLE `t2` /* generated by server */ 226master-bin.000003 # Gtid # # GTID #-#-# 227master-bin.000003 # Query # # use `test`; DROP PROCEDURE p1 228