1# 2# Start of 10.3 tests 3# 4# 5# MDEV-12461 TYPE OF and ROW TYPE OF anchored data types 6# 7# 8# Referring to a table in a non-existing database 9# 10CREATE PROCEDURE p1() 11BEGIN 12DECLARE rec ROW TYPE OF test2.t1; 13END; 14$$ 15CALL p1(); 16ERROR 42S02: Table 'test2.t1' doesn't exist 17CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10)); 18CALL p1(); 19ERROR 42S02: Table 'test2.t1' doesn't exist 20DROP TABLE t1; 21DROP PROCEDURE p1; 22# 23# Referring to a table in the current database 24# 25CREATE PROCEDURE p1() 26BEGIN 27DECLARE rec ROW TYPE OF t1; 28CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d; 29SHOW CREATE TABLE t2; 30DROP TABLE t2; 31END; 32$$ 33CALL p1(); 34ERROR 42S02: Table 'test.t1' doesn't exist 35CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10)); 36CALL p1(); 37Table Create Table 38t2 CREATE TABLE `t2` ( 39 `rec.a` int(11) DEFAULT NULL, 40 `rec.b` varchar(10) DEFAULT NULL, 41 `rec.c` double DEFAULT NULL, 42 `rec.d` decimal(10,0) DEFAULT NULL 43) ENGINE=MyISAM DEFAULT CHARSET=latin1 44DROP TABLE t1; 45DROP PROCEDURE p1; 46# 47# Referring to a table in an explicitly specified database 48# 49CREATE PROCEDURE p1() 50BEGIN 51DECLARE rec ROW TYPE OF test.t1; 52CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d; 53SHOW CREATE TABLE t2; 54DROP TABLE t2; 55END; 56$$ 57CALL p1(); 58ERROR 42S02: Table 'test.t1' doesn't exist 59CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10)); 60CALL p1(); 61Table Create Table 62t2 CREATE TABLE `t2` ( 63 `rec.a` int(11) DEFAULT NULL, 64 `rec.b` varchar(10) DEFAULT NULL, 65 `rec.c` double DEFAULT NULL, 66 `rec.d` decimal(10,0) DEFAULT NULL 67) ENGINE=MyISAM DEFAULT CHARSET=latin1 68DROP TABLE t1; 69DROP PROCEDURE p1; 70# 71# Referring to a view in the current database 72# 73CREATE PROCEDURE p1() 74BEGIN 75DECLARE rec ROW TYPE OF v1; 76CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d; 77SHOW CREATE TABLE t2; 78DROP TABLE t2; 79END; 80$$ 81CALL p1(); 82ERROR 42S02: Table 'test.v1' doesn't exist 83CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10)); 84CREATE VIEW v1 AS SELECT * FROM t1; 85CALL p1(); 86Table Create Table 87t2 CREATE TABLE `t2` ( 88 `rec.a` int(11) DEFAULT NULL, 89 `rec.b` varchar(10) DEFAULT NULL, 90 `rec.c` double DEFAULT NULL, 91 `rec.d` decimal(10,0) DEFAULT NULL 92) ENGINE=MyISAM DEFAULT CHARSET=latin1 93DROP VIEW v1; 94DROP TABLE t1; 95DROP PROCEDURE p1; 96# 97# Referring to a view in an explicitly specified database 98# 99CREATE PROCEDURE p1() 100BEGIN 101DECLARE rec ROW TYPE OF test.v1; 102CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d; 103SHOW CREATE TABLE t2; 104DROP TABLE t2; 105END; 106$$ 107CALL p1(); 108ERROR 42S02: Table 'test.v1' doesn't exist 109CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10)); 110CREATE VIEW v1 AS SELECT * FROM t1; 111CALL p1(); 112Table Create Table 113t2 CREATE TABLE `t2` ( 114 `rec.a` int(11) DEFAULT NULL, 115 `rec.b` varchar(10) DEFAULT NULL, 116 `rec.c` double DEFAULT NULL, 117 `rec.d` decimal(10,0) DEFAULT NULL 118) ENGINE=MyISAM DEFAULT CHARSET=latin1 119DROP VIEW v1; 120DROP TABLE t1; 121DROP PROCEDURE p1; 122# 123# Checking that all table ROW TYPE fields are NULL by default 124# 125CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2)); 126CREATE PROCEDURE p1() 127BEGIN 128DECLARE rec1 ROW TYPE OF t1; 129SELECT rec1.a, rec1.b, rec1.c, rec1.d; 130END; 131$$ 132CALL p1(); 133rec1.a rec1.b rec1.c rec1.d 134NULL NULL NULL NULL 135DROP TABLE t1; 136DROP PROCEDURE p1; 137# 138# A table ROW TYPE variable with a ROW expression as a default 139# 140CREATE TABLE t1 (a INT, b VARCHAR(10)); 141CREATE PROCEDURE p1() 142BEGIN 143DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bbb'); 144SELECT rec1.a, rec1.b; 145END; 146$$ 147CALL p1(); 148rec1.a rec1.b 14910 bbb 150DROP TABLE t1; 151DROP PROCEDURE p1; 152# 153# A table ROW TYPE variable with an incompatible ROW expression as a default 154# 155CREATE TABLE t1 (a INT, b VARCHAR(10)); 156CREATE PROCEDURE p1() 157BEGIN 158DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bbb','ccc'); 159SELECT rec1.a, rec1.b; 160END; 161$$ 162CALL p1(); 163ERROR 21000: Operand should contain 2 column(s) 164DROP TABLE t1; 165DROP PROCEDURE p1; 166# 167# A table ROW TYPE variable with a ROW variable as a default 168# 169CREATE TABLE t1 (a INT, b VARCHAR(10)); 170CREATE PROCEDURE p1() 171BEGIN 172DECLARE rec1 ROW(a INT, b VARCHAR(10)) DEFAULT ROW(10,'bbb'); 173DECLARE rec2 ROW TYPE OF t1 DEFAULT rec1; 174SELECT rec2.a, rec2.b; 175END; 176$$ 177CALL p1(); 178rec2.a rec2.b 17910 bbb 180DROP TABLE t1; 181DROP PROCEDURE p1; 182# 183# A ROW variable using a table ROW TYPE variable as a default 184# 185CREATE TABLE t1 (a INT, b VARCHAR(10)); 186CREATE PROCEDURE p1() 187BEGIN 188DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bbb'); 189DECLARE rec2 ROW(a INT, b VARCHAR(10)) DEFAULT rec1; 190SELECT rec2.a, rec2.b; 191END; 192$$ 193CALL p1(); 194rec2.a rec2.b 19510 bbb 196DROP TABLE t1; 197DROP PROCEDURE p1; 198# 199# Assigning table ROW TYPE variables with a different column count 200# 201CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE); 202CREATE TABLE t2 (a INT, b VARCHAR(10)); 203CREATE PROCEDURE p1() 204BEGIN 205DECLARE rec1 ROW TYPE OF t1; 206DECLARE rec2 ROW TYPE OF t2; 207SET rec2=rec1; 208END; 209$$ 210CALL p1(); 211ERROR 21000: Operand should contain 2 column(s) 212DROP PROCEDURE p1; 213CREATE PROCEDURE p1() 214BEGIN 215DECLARE rec1 ROW TYPE OF t1; 216DECLARE rec2 ROW TYPE OF t2; 217SET rec1=rec2; 218END; 219$$ 220CALL p1(); 221ERROR 21000: Operand should contain 3 column(s) 222DROP TABLE t2; 223DROP TABLE t1; 224DROP PROCEDURE p1; 225# 226# Assigning compatible table ROW TYPE variables (equal number of fields) 227# 228CREATE TABLE t1 (a INT, b VARCHAR(10)); 229CREATE TABLE t2 (x INT, y VARCHAR(10)); 230CREATE PROCEDURE p1() 231BEGIN 232DECLARE rec1 ROW TYPE OF t1; 233DECLARE rec2 ROW TYPE OF t2; 234SET rec1.a= 10; 235SET rec1.b= 'bbb'; 236SET rec2=rec1; 237SELECT rec2.x, rec2.y; 238END; 239$$ 240CALL p1(); 241rec2.x rec2.y 24210 bbb 243DROP TABLE t2; 244DROP TABLE t1; 245DROP PROCEDURE p1; 246# 247# Assigning between incompatible table ROW TYPE and explicit ROW variables 248# 249CREATE TABLE t1 (a INT, b VARCHAR(10)); 250CREATE PROCEDURE p1() 251BEGIN 252DECLARE rec1 ROW TYPE OF t1; 253DECLARE rec2 ROW(x INT,y INT,z INT); 254SET rec2.x= 10; 255SET rec2.y= 20; 256SET rec2.z= 30; 257SET rec1= rec2; 258END; 259$$ 260CALL p1(); 261ERROR 21000: Operand should contain 2 column(s) 262DROP TABLE t1; 263DROP PROCEDURE p1; 264# 265# Assigning between compatible table ROW TYPE and explicit ROW variables 266# 267CREATE TABLE t1 (a INT, b VARCHAR(10)); 268CREATE PROCEDURE p1() 269BEGIN 270DECLARE rec1 ROW TYPE OF t1; 271DECLARE rec2 ROW(x INT,y INT); 272SET rec2.x= 10; 273SET rec2.y= 20; 274SET rec1= rec2; 275SELECT rec1.a, rec1.b; 276SET rec1.a= 11; 277SET rec1.b= 21; 278SET rec2= rec1; 279SELECT rec2.x, rec2.y; 280END; 281$$ 282CALL p1(); 283rec1.a rec1.b 28410 20 285rec2.x rec2.y 28611 21 287DROP TABLE t1; 288DROP PROCEDURE p1; 289# 290# Assigning table ROW TYPE from a ROW expression 291# 292CREATE TABLE t1 (a INT, b VARCHAR(10)); 293CREATE PROCEDURE p1() 294BEGIN 295DECLARE rec1 ROW TYPE OF t1; 296SET rec1= ROW(10,20); 297SELECT rec1.a, rec1.b; 298END; 299$$ 300CALL p1(); 301rec1.a rec1.b 30210 20 303DROP TABLE t1; 304DROP PROCEDURE p1; 305# 306# Fetching a cursor into a table ROW TYPE variable with a wrong field count 307# 308CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2)); 309CREATE TABLE t2 (a INT, b VARCHAR(10)); 310INSERT INTO t1 VALUES (10,'bb1',111.111e2, 12.31); 311CREATE PROCEDURE p1() 312BEGIN 313DECLARE rec2 ROW TYPE OF t2; 314DECLARE cur1 CURSOR FOR SELECT * FROM t1; 315OPEN cur1; 316FETCH cur1 INTO rec2; 317CLOSE cur1; 318END; 319$$ 320CALL p1(); 321ERROR HY000: Incorrect number of FETCH variables 322DROP TABLE t2; 323DROP TABLE t1; 324DROP PROCEDURE p1; 325# 326# Fetching a cursor into a table ROW TYPE variable 327# 328CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2)); 329CREATE TABLE t2 LIKE t1; 330INSERT INTO t1 VALUES (10,'bb1',111.111e2, 12.31); 331INSERT INTO t1 VALUES (20,'bb2',222.222e2, 12.32); 332INSERT INTO t1 VALUES (30,'bb3',333.333e2, 12.33); 333CREATE PROCEDURE p1() 334BEGIN 335DECLARE done INT DEFAULT 0; 336DECLARE rec ROW TYPE OF t1; 337DECLARE cur CURSOR FOR SELECT * FROM t1; 338DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; 339OPEN cur; 340read_loop: LOOP 341FETCH cur INTO rec; 342IF done THEN 343LEAVE read_loop; 344END IF; 345SELECT rec.a, rec.b, rec.c, rec.d; 346INSERT INTO t2 VALUES (rec.a, rec.b, rec.c, rec.d); 347END LOOP; 348CLOSE cur; 349END; 350$$ 351CALL p1(); 352rec.a rec.b rec.c rec.d 35310 bb1 11111.1 12.31 354rec.a rec.b rec.c rec.d 35520 bb2 22222.2 12.32 356rec.a rec.b rec.c rec.d 35730 bb3 33333.3 12.33 358SELECT * FROM t2; 359a b c d 36010 bb1 11111.1 12.31 36120 bb2 22222.2 12.32 36230 bb3 33333.3 12.33 363DROP TABLE t2; 364DROP TABLE t1; 365DROP PROCEDURE p1; 366# 367# Fetching a cursor into a table ROW TYPE variable with different column names 368# 369CREATE TABLE t1 (a INT, b VARCHAR(10)); 370CREATE TABLE t2 (x INT, y VARCHAR(10)); 371INSERT INTO t1 VALUES (10,'bbb'); 372CREATE PROCEDURE p1() 373BEGIN 374DECLARE rec2 ROW TYPE OF t2; 375DECLARE cur1 CURSOR FOR SELECT * FROM t1; 376OPEN cur1; 377FETCH cur1 INTO rec2; 378SELECT rec2.x, rec2.y; 379CLOSE cur1; 380END; 381$$ 382CALL p1(); 383rec2.x rec2.y 38410 bbb 385DROP TABLE t2; 386DROP TABLE t1; 387DROP PROCEDURE p1; 388# 389# Fetching a cursor into a table ROW TYPE variable, with truncation 390# 391SET sql_mode=''; 392CREATE TABLE t1 (a INT, b VARCHAR(10)); 393CREATE TABLE t2 (a INT, b INT); 394INSERT INTO t1 VALUES (10,'11x'); 395CREATE PROCEDURE p1() 396BEGIN 397DECLARE rec2 ROW TYPE OF t2; 398DECLARE cur1 CURSOR FOR SELECT * FROM t1; 399OPEN cur1; 400FETCH cur1 INTO rec2; 401SELECT rec2.a, rec2.b; 402CLOSE cur1; 403END; 404$$ 405CALL p1(); 406rec2.a rec2.b 40710 11 408Warnings: 409Warning 1265 Data truncated for column 'b' at row 1 410DROP TABLE t2; 411DROP TABLE t1; 412DROP PROCEDURE p1; 413SET sql_mode=DEFAULT; 414# 415# table ROW TYPE variables are not allowed in LIMIT 416# 417CREATE TABLE t1 (a INT, b INT); 418INSERT INTO t1 VALUES (1,2); 419CREATE PROCEDURE p1() 420BEGIN 421DECLARE rec1 ROW TYPE OF t1 DEFAULT (1,2); 422SELECT * FROM t1 LIMIT rec1.a; 423END; 424$$ 425ERROR HY000: A variable of a non-integer based type in LIMIT clause 426DROP TABLE t1; 427# 428# table ROW TYPE variable fields as OUT parameters 429# 430CREATE TABLE t1 (a INT, b VARCHAR(10)); 431CREATE PROCEDURE p1(OUT a INT,OUT b VARCHAR(10)) 432BEGIN 433SET a=10; 434SET b='bb'; 435END; 436$$ 437CREATE PROCEDURE p2() 438BEGIN 439DECLARE rec1 ROW TYPE OF t1; 440CALL p1(rec1.a, rec1.b); 441SELECT rec1.a, rec1.b; 442END; 443$$ 444CALL p2(); 445rec1.a rec1.b 44610 bb 447DROP PROCEDURE p2; 448DROP PROCEDURE p1; 449DROP TABLE t1; 450# 451# Passing the entire table ROW TYPE variable 452# 453CREATE TABLE t1 (a INT, b VARCHAR(10)); 454CREATE PROCEDURE p1(a ROW(a INT, b VARCHAR(10))) 455BEGIN 456SELECT a.a, a.b; 457END; 458$$ 459CREATE PROCEDURE p2() 460BEGIN 461DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bb'); 462CALL p1(rec1); 463END; 464$$ 465CALL p2(); 466a.a a.b 46710 bb 468DROP PROCEDURE p2; 469DROP PROCEDURE p1; 470DROP TABLE t1; 471# 472# Passing the entire table ROW TYPE variable as an OUT parameter 473# 474CREATE TABLE t1 (a INT, b VARCHAR(10)); 475CREATE PROCEDURE p1(OUT a ROW(a INT, b VARCHAR(10))) 476BEGIN 477SET a= ROW(10,'bb'); 478END; 479$$ 480CREATE PROCEDURE p2() 481BEGIN 482DECLARE rec1 ROW TYPE OF t1; 483CALL p1(rec1); 484SELECT rec1.a, rec1.b; 485END; 486$$ 487CALL p2(); 488rec1.a rec1.b 48910 bb 490DROP PROCEDURE p2; 491DROP PROCEDURE p1; 492DROP TABLE t1; 493# 494# Assigning a table ROW TYPE field to an OUT parameter 495# 496CREATE TABLE t1 (a INT, b VARCHAR(10)); 497CREATE PROCEDURE p1 (INOUT res INTEGER) 498BEGIN 499DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'b0'); 500SET res=rec1.a; 501END; 502$$ 503CALL p1(@res); 504SELECT @res; 505@res 50610 507SET @res=NULL; 508DROP PROCEDURE p1; 509DROP TABLE t1; 510# 511# Testing Item_splocal_row_field_by_name::print 512# 513CREATE TABLE t1 (a INT, b VARCHAR(10)); 514CREATE PROCEDURE p1() 515BEGIN 516DECLARE rec ROW TYPE OF t1 DEFAULT ROW(10,'bb'); 517EXPLAIN EXTENDED SELECT rec.a, rec.b; 518END; 519$$ 520CALL p1(); 521id select_type table type possible_keys key key_len ref rows filtered Extra 5221 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 523Warnings: 524Note 1003 select rec.a@0["a"] AS `rec.a`,rec.b@0["b"] AS `rec.b` 525DROP PROCEDURE p1; 526DROP TABLE t1; 527# 528# Non-existing field 529# 530CREATE TABLE t1 (a INT, b VARCHAR(10)); 531CREATE PROCEDURE p1() 532BEGIN 533DECLARE rec ROW TYPE OF t1; 534SELECT rec.c; 535END; 536$$ 537CALL p1(); 538ERROR HY000: Row variable 'rec' does not have a field 'c' 539ALTER TABLE t1 ADD c INT; 540CALL p1(); 541rec.c 542NULL 543DROP PROCEDURE p1; 544DROP TABLE t1; 545# 546# Testing that field names are case insensitive 547# 548CREATE TABLE t1 (a INT, b VARCHAR(10)); 549CREATE PROCEDURE p1() 550BEGIN 551DECLARE rec ROW TYPE OF t1 DEFAULT ROW(10,'bb'); 552SELECT rec.A, rec.B; 553END; 554$$ 555CALL p1(); 556rec.A rec.B 55710 bb 558DROP PROCEDURE p1; 559DROP TABLE t1; 560# 561# Testing that table ROW TYPE uses temporary tables vs shadowed real tables 562# 563CREATE TABLE t1 (a INT, b VARCHAR(10)); 564CREATE TEMPORARY TABLE t1 (x INT, y VARCHAR(10)); 565CREATE PROCEDURE p1() 566BEGIN 567DECLARE rec ROW TYPE OF t1 DEFAULT ROW(10,'bb'); 568SELECT rec.A, rec.B; 569END; 570$$ 571CALL p1(); 572ERROR HY000: Row variable 'rec' does not have a field 'A' 573DROP TEMPORARY TABLE t1; 574CALL p1(); 575rec.A rec.B 57610 bb 577DROP PROCEDURE p1; 578DROP TABLE t1; 579# 580# Testing that the structure of table ROW TYPE variables is determined at the very beginning and is not changed after ALTER 581# 582CREATE TABLE t1 (a INT, b VARCHAR(32)); 583INSERT INTO t1 VALUES (10,'b10'); 584CREATE PROCEDURE p1() 585BEGIN 586ALTER TABLE t1 ADD c INT; 587BEGIN 588DECLARE rec ROW TYPE OF t1; -- this will not have column "c" 589 SET rec.c=10; 590END; 591END; 592$$ 593CALL p1(); 594ERROR HY000: Row variable 'rec' does not have a field 'c' 595DROP TABLE t1; 596DROP PROCEDURE p1; 597# 598# SELECT INTO + table ROW TYPE variable with a wrong column count 599# 600CREATE TABLE t1 (a INT, b VARCHAR(32)); 601INSERT INTO t1 VALUES (10,'b10'); 602CREATE PROCEDURE p1() 603BEGIN 604DECLARE rec1 ROW TYPE OF t1; 605SELECT 10,'a','b' FROM t1 INTO rec1; 606SELECT rec1.a, rec1.b; 607END; 608$$ 609Warnings: 610Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 611CALL p1(); 612ERROR 21000: The used SELECT statements have a different number of columns 613DROP TABLE t1; 614DROP PROCEDURE p1; 615# 616# SELECT INTO + multiple table ROW TYPE variables 617# 618CREATE TABLE t1 (a INT, b VARCHAR(32)); 619INSERT INTO t1 VALUES (10,'b10'); 620CREATE PROCEDURE p1() 621BEGIN 622DECLARE rec1 ROW TYPE OF t1; 623SELECT 10,'a' FROM t1 INTO rec1, rec1; 624SELECT rec1.a, rec1.b; 625END; 626$$ 627Warnings: 628Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 629CALL p1(); 630ERROR 21000: Operand should contain 2 column(s) 631DROP TABLE t1; 632DROP PROCEDURE p1; 633# 634# SELECT INTO + table ROW TYPE working example 635# 636CREATE TABLE t1 (a INT, b VARCHAR(32)); 637INSERT INTO t1 VALUES (10,'b10'); 638CREATE PROCEDURE p1() 639BEGIN 640DECLARE rec1 ROW TYPE OF t1; 641SELECT * FROM t1 INTO rec1; 642SELECT rec1.a, rec1.b; 643END; 644$$ 645Warnings: 646Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 647CALL p1(); 648rec1.a rec1.b 64910 b10 650DROP TABLE t1; 651DROP PROCEDURE p1; 652# 653# End of MDEV-12461 TYPE OF and ROW TYPE OF anchored data types 654# 655# 656# MDEV-12347 Valgrind reports invalid read errors in Item_field_row::element_index_by_name 657# 658CREATE TABLE t1 (a INT, b ENUM('b0','b1','b12','b3')); 659CREATE PROCEDURE p1() 660BEGIN 661BEGIN 662DECLARE rec ROW TYPE OF t1; 663SET rec.b='b0'; 664SELECT rec.b; 665END; 666END; 667$$ 668CALL p1(); 669rec.b 670b0 671DROP TABLE t1; 672DROP PROCEDURE p1; 673CREATE TABLE t1 (a INT, b SET('b0','b1','b12','b3')); 674CREATE PROCEDURE p1() 675BEGIN 676BEGIN 677DECLARE rec ROW TYPE OF t1; 678SET rec.b='b0'; 679SELECT rec.b; 680END; 681END; 682$$ 683CALL p1(); 684rec.b 685b0 686DROP TABLE t1; 687DROP PROCEDURE p1; 688# 689# MDEV-13581 ROW TYPE OF t1 and t1%ROWTYPE for routine parameters 690# 691CREATE TABLE t1 (a INT, b TEXT, c ENUM('a','b','c')); 692CREATE PROCEDURE p1 (a ROW TYPE OF t1) 693BEGIN 694CREATE TABLE t2 AS SELECT a.a AS a, a.b AS b, a.c AS c; 695SHOW CREATE TABLE t2; 696DROP TABLE t2; 697END; 698$$ 699CREATE PROCEDURE p2() 700BEGIN 701DECLARE a ROW TYPE OF t1; 702CALL p1(a); 703END; 704$$ 705CALL p2(); 706Table Create Table 707t2 CREATE TABLE `t2` ( 708 `a` int(11) DEFAULT NULL, 709 `b` text DEFAULT NULL, 710 `c` char(1) DEFAULT NULL 711) ENGINE=MyISAM DEFAULT CHARSET=latin1 712DROP PROCEDURE p2; 713DROP PROCEDURE p1; 714DROP TABLE t1; 715CREATE TABLE t1 (a INT, b TEXT); 716CREATE PROCEDURE p1 (OUT a ROW TYPE OF t1) 717BEGIN 718SET a.a=10; 719SET a.b='text'; 720END; 721$$ 722CREATE PROCEDURE p2() 723BEGIN 724DECLARE a ROW TYPE OF t1; 725CALL p1(a); 726SELECT a.a, a.b; 727END; 728$$ 729CREATE FUNCTION f1(a ROW TYPE OF t1) RETURNS TEXT 730BEGIN 731RETURN CONCAT(a.a, ' ', a.b); 732END; 733$$ 734CREATE FUNCTION f2() RETURNS TEXT 735BEGIN 736DECLARE a ROW TYPE OF t1; 737CALL p1(a); 738RETURN f1(a); 739END; 740$$ 741CALL p2(); 742a.a a.b 74310 text 744SELECT f2(); 745f2() 74610 text 747DROP PROCEDURE p2; 748DROP PROCEDURE p1; 749DROP FUNCTION f2; 750DROP FUNCTION f1; 751DROP TABLE t1; 752CREATE DATABASE db1; 753CREATE TABLE db1.t1 (a INT, b TEXT); 754CREATE PROCEDURE p1 (OUT a ROW TYPE OF db1.t1) 755BEGIN 756SET a.a=10; 757SET a.b='text'; 758END; 759$$ 760CREATE PROCEDURE p2() 761BEGIN 762DECLARE a ROW TYPE OF db1.t1; 763CALL p1(a); 764SELECT a.a, a.b; 765END; 766$$ 767CREATE FUNCTION f1(a ROW TYPE OF db1.t1) RETURNS TEXT 768BEGIN 769RETURN CONCAT(a.a, ' ', a.b); 770END; 771$$ 772CREATE FUNCTION f2() RETURNS TEXT 773BEGIN 774DECLARE a ROW TYPE OF db1.t1; 775CALL p1(a); 776RETURN f1(a); 777END; 778$$ 779CALL p2(); 780a.a a.b 78110 text 782SELECT f2(); 783f2() 78410 text 785DROP PROCEDURE p2; 786DROP PROCEDURE p1; 787DROP FUNCTION f2; 788DROP FUNCTION f1; 789DROP DATABASE db1; 790# 791# MDEV-14139 Anchored data types for variables 792# 793CREATE TABLE t1 (int11 INT, text0 TEXT); 794BEGIN NOT ATOMIC 795DECLARE row1 ROW TYPE OF t1; 796DECLARE a_row1 TYPE OF row1; 797DECLARE aa_row1 TYPE OF a_row1; 798CREATE TABLE t2 AS SELECT a_row1.int11 AS int11, a_row1.text0 AS text0; 799SHOW CREATE TABLE t2; 800DROP TABLE t2; 801CREATE TABLE t2 AS SELECT aa_row1.int11 AS int11, aa_row1.text0 AS text0; 802SHOW CREATE TABLE t2; 803DROP TABLE t2; 804END; 805$$ 806Table Create Table 807t2 CREATE TABLE `t2` ( 808 `int11` int(11) DEFAULT NULL, 809 `text0` text DEFAULT NULL 810) ENGINE=MyISAM DEFAULT CHARSET=latin1 811Table Create Table 812t2 CREATE TABLE `t2` ( 813 `int11` int(11) DEFAULT NULL, 814 `text0` text DEFAULT NULL 815) ENGINE=MyISAM DEFAULT CHARSET=latin1 816DROP TABLE t1; 817