1--source include/default_charset.inc 2 3--echo # 4--echo # Start of 10.3 tests 5--echo # 6 7--echo # 8--echo # MDEV-12461 TYPE OF and ROW TYPE OF anchored data types 9--echo # 10 11--echo # 12--echo # Missing table 13--echo # 14 15DELIMITER $$; 16CREATE PROCEDURE p1() 17BEGIN 18 DECLARE a TYPE OF t1.a; 19END; 20$$ 21DELIMITER ;$$ 22--error ER_NO_SUCH_TABLE 23CALL p1(); 24DROP PROCEDURE p1; 25 26 27--echo # 28--echo # Missing column 29--echo # 30 31CREATE TABLE t1 (b INT); 32DELIMITER $$; 33CREATE PROCEDURE p1() 34BEGIN 35 DECLARE a TYPE OF t1.a; 36END; 37$$ 38DELIMITER ;$$ 39--error ER_BAD_FIELD_ERROR 40CALL p1(); 41DROP PROCEDURE p1; 42DROP TABLE t1; 43 44 45--echo # 46--echo # One TYPE OF variable 47--echo # 48 49CREATE TABLE t1 (a INT); 50DELIMITER $$; 51CREATE PROCEDURE p1() 52BEGIN 53 DECLARE a TYPE OF t1.a; 54 SET a= 123; 55 SELECT a; 56END; 57$$ 58DELIMITER ;$$ 59CALL p1(); 60DROP PROCEDURE p1; 61DROP TABLE t1; 62 63--echo # 64--echo # Two TYPE OF variables, with a truncation warning on assignment 65--echo # 66 67SET sql_mode=''; 68CREATE TABLE t1 (a TINYINT, b INT); 69DELIMITER $$; 70CREATE PROCEDURE p1() 71BEGIN 72 DECLARE a TYPE OF t1.a; 73 DECLARE b TYPE OF t1.b; 74 SET a= 200; 75 SET b= 200; 76 SELECT a, b; 77END; 78$$ 79DELIMITER ;$$ 80CALL p1(); 81DROP PROCEDURE p1; 82DROP TABLE t1; 83SET sql_mode=DEFAULT; 84 85 86--echo # 87--echo # TYPE OF variables for fields with various attributes 88--echo # 89 90CREATE TABLE t1 ( 91 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 92 a TINYINT NOT NULL, 93 b INT NOT NULL, 94 ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 95 UNIQUE(a) 96); 97DELIMITER $$; 98CREATE PROCEDURE p1() 99BEGIN 100 DECLARE id TYPE OF t1.id; 101 DECLARE a TYPE OF t1.a; 102 DECLARE b TYPE OF t1.b; 103 DECLARE ts TYPE OF t1.ts; 104 SELECT id, a, b, ts; 105 CREATE TABLE t2 AS SELECT id, a, b, ts; 106 SHOW CREATE TABLE t2; 107 DROP TABLE t2; 108END; 109$$ 110DELIMITER ;$$ 111CALL p1(); 112DROP PROCEDURE p1; 113DROP TABLE t1; 114 115 116--echo # 117--echo # TYPE OF + virtual columns 118--echo # 119 120CREATE TABLE t1 ( 121 a INT NOT NULL, 122 b VARCHAR(32), 123 c INT AS (a + 10) VIRTUAL, 124 d VARCHAR(5) AS (left(b,5)) PERSISTENT 125); 126DELIMITER $$; 127CREATE PROCEDURE p1() 128BEGIN 129 DECLARE c TYPE OF t1.c; 130 DECLARE d TYPE OF t1.d; 131 SELECT c, d; 132 CREATE TABLE t2 AS SELECT c, d; 133 SHOW CREATE TABLE t2; 134 DROP TABLE t2; 135END; 136$$ 137DELIMITER ;$$ 138CALL p1(); 139DROP PROCEDURE p1; 140DROP TABLE t1; 141 142 143--echo # 144--echo # TYPE OF + the ZEROFILL attribute 145--echo # 146 147CREATE TABLE t1 ( 148 dz DECIMAL(10,3) ZEROFILL 149); 150DELIMITER $$; 151CREATE PROCEDURE p1() 152BEGIN 153 DECLARE dzr TYPE OF t1.dz DEFAULT 10; 154 DECLARE dzt DECIMAL(10,3) ZEROFILL DEFAULT 10; 155 SELECT dzr, dzt; 156 CREATE TABLE t2 AS SELECT dzr,dzt; 157 SHOW CREATE TABLE t2; 158 DROP TABLE t2; 159END; 160$$ 161DELIMITER ;$$ 162CALL p1(); 163DROP PROCEDURE p1; 164DROP TABLE t1; 165 166 167--echo # 168--echo # Temporary tables shadow real tables for TYPE OF purposes 169--echo # 170CREATE TABLE t1 (a VARCHAR(10)); 171INSERT INTO t1 VALUES ('t1'); 172CREATE TEMPORARY TABLE t1 (a INT); 173INSERT INTO t1 VALUES (10); 174SELECT * FROM t1; 175 176DELIMITER $$; 177CREATE PROCEDURE p1() 178BEGIN 179 DECLARE a TYPE OF t1.a DEFAULT 11; 180 CREATE TABLE t2 AS SELECT a; 181END; 182$$ 183DELIMITER ;$$ 184--echo # 185--echo # Should use INT(11) as TYPE OF, as in the temporary table 186--echo # 187CALL p1(); 188SHOW CREATE TABLE t2; 189SELECT * FROM t2; 190DROP TABLE t2; 191SELECT * FROM t1; 192DROP TEMPORARY TABLE t1; 193SELECT * FROM t1; 194--echo # 195--echo # Should use VARCHAR(10) as TYPE OF, as in the real table 196--echo # 197CALL p1(); 198SHOW CREATE TABLE t2; 199SELECT * FROM t2; 200DROP TABLE t2; 201DROP PROCEDURE p1; 202DROP TABLE t1; 203 204 205--echo # 206--echo # "TYPE OF t1.a" searches for "t1" in the current database 207--echo # 208 209CREATE TABLE t1 (a VARCHAR(10)); 210CREATE DATABASE test1; 211CREATE TABLE test1.t1 (a INT); 212DELIMITER $$; 213CREATE PROCEDURE p1() 214BEGIN 215 DECLARE a TYPE OF t1.a DEFAULT 11; 216 CREATE TABLE test.t2 AS SELECT a; 217END; 218$$ 219DELIMITER ;$$ 220 221--echo # 222--echo # This interprets "TYPE OF t1.a" as VARCHAR(10), as in test.t1.a 223--echo # 224 225USE test; 226CALL test.p1(); 227SHOW CREATE TABLE test.t2; 228DROP TABLE test.t2; 229 230--echo # 231--echo # This interprets "TYPE OF t1.a" as INT, as in test1.t1.a 232--echo # 233 234USE test1; 235CALL test.p1(); 236SHOW CREATE TABLE test.t2; 237DROP TABLE test.t2; 238 239--echo # 240--echo # Error if there is no an active database 241--echo # 242 243DROP DATABASE test1; 244--error ER_NO_DB_ERROR 245CALL test.p1(); 246 247USE test; 248DROP PROCEDURE p1; 249DROP TABLE t1; 250 251 252--echo # 253--echo # A reference to a table in a non-existing database 254--echo # 255DELIMITER $$; 256CREATE PROCEDURE p1() 257BEGIN 258 DECLARE a TYPE OF test1.t1.a; 259 CREATE TABLE t1 AS SELECT a; 260END; 261$$ 262DELIMITER ;$$ 263--error ER_NO_SUCH_TABLE 264CALL p1; 265DROP PROCEDURE p1; 266 267 268--echo # 269--echo # A reference to a table in a different database 270--echo # 271CREATE TABLE t1(a INT); 272CREATE DATABASE test1; 273CREATE TABLE test1.t1 (a VARCHAR(10)); 274DELIMITER $$; 275CREATE PROCEDURE p1() 276BEGIN 277 DECLARE a TYPE OF t1.a; 278 DECLARE b TYPE OF test1.t1.a; 279 CREATE TABLE t2 AS SELECT a,b; 280END; 281$$ 282DELIMITER ;$$ 283CALL p1; 284SHOW CREATE TABLE t2; 285DROP PROCEDURE p1; 286DROP TABLE t2; 287DROP DATABASE test1; 288DROP TABLE t1; 289 290 291--echo # 292--echo # Using a table before it appears in a TYPE OF declaration + multiple TYPE OF declarations 293--echo # 294CREATE TABLE t1 (a INT, b VARCHAR(10)); 295INSERT INTO t1 (a,b) VALUES (10,'b10'); 296DELIMITER $$; 297CREATE PROCEDURE p1() 298BEGIN 299 INSERT INTO t1 (a,b) VALUES (11, 'b11'); 300 SELECT * FROM t1; 301 BEGIN 302 DECLARE va TYPE OF t1.a DEFAULT 30; 303 DECLARE vb TYPE OF t1.b DEFAULT 'b30'; 304 INSERT INTO t1 (a,b) VALUES (12,'b12'); 305 SELECT * FROM t1; 306 INSERT INTO t1 (a,b) VALUES (va, vb); 307 SELECT * FROM t1; 308 END; 309 BEGIN 310 DECLARE va TYPE OF t1.a DEFAULT 40; 311 DECLARE vb TYPE OF t1.b DEFAULT 'b40'; 312 INSERT INTO t1 (a,b) VALUES (va,vb); 313 SELECT * FROM t1; 314 END; 315END; 316$$ 317DELIMITER ;$$ 318CALL p1; 319DROP TABLE t1; 320DROP PROCEDURE p1; 321 322 323--echo # 324--echo # TYPE OF variables + TABLE vs VIEW 325--echo # 326 327CREATE TABLE t1 ( 328 bit6 BIT(6), 329 bit7 BIT(7), 330 bit8 BIT(8), 331 i1 TINYINT, 332 i2 SMALLINT, 333 i3 MEDIUMINT, 334 i4 INT, 335 i8 BIGINT, 336 ff FLOAT, 337 fd DOUBLE, 338 cc CHAR(10), 339 cv VARCHAR(10), 340 cvu VARCHAR(10) CHARACTER SET utf8, 341 t1 TINYTEXT, 342 t2 TEXT, 343 t3 MEDIUMTEXT, 344 t4 LONGTEXT, 345 enum1 ENUM('a','b','c'), 346 set1 SET('a','b','c'), 347 blob1 TINYBLOB, 348 blob2 BLOB, 349 blob3 MEDIUMBLOB, 350 blob4 LONGBLOB, 351 yy YEAR, 352 dd DATE, 353 tm0 TIME, 354 tm3 TIME(3), 355 tm6 TIME(6), 356 dt0 DATETIME, 357 dt3 DATETIME(3), 358 dt6 DATETIME(6), 359 ts0 TIMESTAMP, 360 ts3 TIMESTAMP(3), 361 ts6 TIMESTAMP(6), 362 dc100 DECIMAL(10,0), 363 dc103 DECIMAL(10,3), 364 dc209 DECIMAL(20,9) 365); 366 367 368DELIMITER $$; 369CREATE PROCEDURE p1(command enum('create','select')) 370BEGIN 371 DECLARE bit6 TYPE OF t1.bit6 DEFAULT 0x30; 372 DECLARE bit7 TYPE OF t1.bit7 DEFAULT 0x41; 373 DECLARE bit8 TYPE OF t1.bit8 DEFAULT 0x7E; 374 DECLARE i1 TYPE OF t1.i1 DEFAULT 11; 375 DECLARE i2 TYPE OF t1.i2 DEFAULT 12; 376 DECLARE i3 TYPE OF t1.i3 DEFAULT 13; 377 DECLARE i4 TYPE OF t1.i4 DEFAULT 14; 378 DECLARE i8 TYPE OF t1.i8 DEFAULT 18; 379 DECLARE ff TYPE OF t1.ff DEFAULT 21; 380 DECLARE fd TYPE OF t1.fd DEFAULT 22; 381 DECLARE cc TYPE OF t1.cc DEFAULT 'char'; 382 DECLARE cv TYPE OF t1.cv DEFAULT 'varchar'; 383 DECLARE cvu TYPE OF t1.cvu DEFAULT 'varcharu8'; 384 DECLARE t1 TYPE OF t1.t1 DEFAULT 'text1'; 385 DECLARE t2 TYPE OF t1.t2 DEFAULT 'text2'; 386 DECLARE t3 TYPE OF t1.t3 DEFAULT 'text3'; 387 DECLARE t4 TYPE OF t1.t4 DEFAULT 'text4'; 388 DECLARE enum1 TYPE OF t1.enum1 DEFAULT 'b'; 389 DECLARE set1 TYPE OF t1.set1 DEFAULT 'a,c'; 390 DECLARE blob1 TYPE OF t1.blob1 DEFAULT 'blob1'; 391 DECLARE blob2 TYPE OF t1.blob2 DEFAULT 'blob2'; 392 DECLARE blob3 TYPE OF t1.blob3 DEFAULT 'blob3'; 393 DECLARE blob4 TYPE OF t1.blob4 DEFAULT 'blob4'; 394 DECLARE yy TYPE OF t1.yy DEFAULT 2001; 395 DECLARE dd TYPE OF t1.dd DEFAULT '2001-01-01'; 396 DECLARE tm0 TYPE OF t1.tm0 DEFAULT '00:00:01'; 397 DECLARE tm3 TYPE OF t1.tm3 DEFAULT '00:00:03.333'; 398 DECLARE tm6 TYPE OF t1.tm6 DEFAULT '00:00:06.666666'; 399 DECLARE dt0 TYPE OF t1.dt0 DEFAULT '2001-01-01 00:00:01'; 400 DECLARE dt3 TYPE OF t1.dt3 DEFAULT '2001-01-03 00:00:01.333'; 401 DECLARE dt6 TYPE OF t1.dt6 DEFAULT '2001-01-06 00:00:01.666666'; 402 DECLARE ts0 TYPE OF t1.ts0 DEFAULT '2002-01-01 00:00:01'; 403 DECLARE ts3 TYPE OF t1.ts3 DEFAULT '2002-01-03 00:00:01.333'; 404 DECLARE ts6 TYPE OF t1.ts6 DEFAULT '2002-01-06 00:00:01.666666'; 405 DECLARE dc100 TYPE OF t1.dc100 DEFAULT 10; 406 DECLARE dc103 TYPE OF t1.dc103 DEFAULT 10.123; 407 DECLARE dc209 TYPE OF t1.dc209 DEFAULT 10.123456789; 408 CASE 409 WHEN command='create' THEN 410 CREATE TABLE t2 AS SELECT 411 bit6, bit7, bit8, 412 i1,i2,i3,i4,i8, 413 ff,fd, dc100, dc103, dc209, 414 cc,cv,cvu, 415 t1,t2,t3,t4, 416 enum1, set1, 417 blob1, blob2, blob3, blob4, 418 dd, yy, 419 tm0, tm3, tm6, 420 dt0, dt3, dt6, 421 ts0, ts3, ts6; 422 WHEN command='select' THEN 423 SELECT 424 bit6, bit7, bit8, 425 i1,i2,i3,i4,i8, 426 ff,fd, dc100, dc103, dc209, 427 cc,cv,cvu, 428 t1,t2,t3,t4, 429 enum1, set1, 430 blob1, blob2, blob3, blob4, 431 dd, yy, 432 tm0, tm3, tm6, 433 dt0, dt3, dt6, 434 ts0, ts3, ts6; 435 END CASE; 436END; 437$$ 438DELIMITER ;$$ 439 440--echo # 441--echo # TABLE 442--echo # 443CALL p1('create'); 444SHOW CREATE TABLE t2; 445--vertical_results 446SELECT * FROM t2; 447--horizontal_results 448DROP TABLE t2; 449 450--disable_ps_protocol 451--enable_metadata 452--vertical_results 453CALL p1('select'); 454--horizontal_results 455--disable_metadata 456--enable_ps_protocol 457 458--echo # 459--echo # VIEW 460--echo # 461ALTER TABLE t1 RENAME t0; 462CREATE VIEW t1 AS SELECT * FROM t0; 463 464CALL p1('create'); 465SHOW CREATE TABLE t2; 466--vertical_results 467SELECT * FROM t2; 468--horizontal_results 469DROP TABLE t2; 470 471--disable_ps_protocol 472--enable_metadata 473--vertical_results 474CALL p1('select'); 475--horizontal_results 476--disable_metadata 477--enable_ps_protocol 478 479DROP VIEW t1; 480DROP TABLE t0; 481 482DROP PROCEDURE p1; 483 484--echo # 485--echo # VIEW with subqueries 486--echo # 487CREATE TABLE t1 (a INT,b INT); 488INSERT INTO t1 VALUES (10,1),(20,2),(30,3),(40,4); 489SELECT AVG(a) FROM t1; 490CREATE VIEW v1 AS SELECT a,1 as b FROM t1 WHERE a>(SELECT AVG(a) FROM t1) AND b>(SELECT 1); 491SELECT * FROM v1; 492DELIMITER $$; 493CREATE PROCEDURE p1() 494BEGIN 495 DECLARE a TYPE OF v1.a DEFAULT 10; 496 DECLARE b TYPE OF v1.b DEFAULT 1; 497 SELECT a,b; 498END; 499$$ 500DELIMITER ;$$ 501CALL p1; 502DROP PROCEDURE p1; 503DELIMITER $$; 504CREATE FUNCTION f1() RETURNS INT 505BEGIN 506 DECLARE a TYPE OF v1.a DEFAULT 10; 507 DECLARE b TYPE OF v1.b DEFAULT 1; 508 RETURN a+b; 509END; 510$$ 511DELIMITER ;$$ 512SELECT f1(); 513DROP FUNCTION f1; 514DROP VIEW v1; 515DROP TABLE t1; 516 517 518--echo # 519--echo # TYPE OF variables + INFORMATION_SCHEMA 520--echo # 521DELIMITER $$; 522CREATE PROCEDURE p1() 523BEGIN 524 DECLARE tables_table_name TYPE OF INFORMATION_SCHEMA.TABLES.TABLE_NAME; 525 DECLARE tables_table_rows TYPE OF INFORMATION_SCHEMA.TABLES.TABLE_ROWS; 526 DECLARE processlist_info TYPE OF INFORMATION_SCHEMA.PROCESSLIST.INFO; 527 DECLARE processlist_info_binary TYPE OF INFORMATION_SCHEMA.PROCESSLIST.INFO_BINARY; 528 CREATE TABLE t1 AS SELECT 529 tables_table_name, 530 tables_table_rows, 531 processlist_info, 532 processlist_info_binary; 533END; 534$$ 535DELIMITER ;$$ 536CALL p1(); 537SHOW CREATE TABLE t1; 538DROP TABLE t1; 539DROP PROCEDURE p1; 540 541 542--echo # 543--echo # TYPE OF + Table structure change 544--echo # Data type for both a0 and a1 is chosen in the very beginning 545--echo # 546DELIMITER $$; 547CREATE PROCEDURE p1() 548BEGIN 549 DECLARE a0 TYPE OF t1.a; 550 ALTER TABLE t1 MODIFY a VARCHAR(10); -- This does not affect a1 551 BEGIN 552 DECLARE a1 TYPE OF t1.a; 553 CREATE TABLE t2 AS SELECT a0, a1; 554 SHOW CREATE TABLE t2; 555 DROP TABLE t2; 556 END; 557END 558$$ 559DELIMITER ;$$ 560CREATE TABLE t1 (a INT); 561CALL p1; 562DROP TABLE t1; 563DROP PROCEDURE p1; 564 565 566--echo # 567--echo # TYPE OF in parameters 568--echo # 569CREATE TABLE t1 (a VARCHAR(10)); 570CREATE DATABASE test1; 571CREATE TABLE test1.t1 (b SMALLINT); 572DELIMITER $$; 573CREATE PROCEDURE p1(a TYPE OF t1.a, b TYPE OF test1.t1.b) 574BEGIN 575 CREATE TABLE t2 AS SELECT a, b; 576END; 577$$ 578DELIMITER ;$$ 579CALL p1('test', 123); 580SHOW CREATE TABLE t2; 581SELECT * FROM t2; 582DROP TABLE t2; 583DROP PROCEDURE p1; 584DROP TABLE test1.t1; 585DROP DATABASE test1; 586DROP TABLE t1; 587 588--echo # 589--echo # TYPE OF in a stored function variables and arguments 590--echo # 591 592CREATE TABLE t1 (a INT); 593DELIMITER $$; 594CREATE FUNCTION f1 (prm TYPE OF t1.a) RETURNS INT 595BEGIN 596 DECLARE a TYPE OF t1.a DEFAULT prm; 597 RETURN a; 598END; 599$$ 600DELIMITER ;$$ 601SELECT f1(20); 602DROP FUNCTION f1; 603DROP TABLE t1; 604 605 606--echo # 607--echo # TYPE OF in function RETURN clause is not supported yet 608--echo # 609DELIMITER $$; 610--error ER_PARSE_ERROR 611CREATE FUNCTION f1() RETURN TYPE OF t1.a 612BEGIN 613 RETURN 0; 614END; 615$$ 616DELIMITER ;$$ 617 618 619--echo # 620--echo # Using TYPE OF variables as FETCH targets 621--echo # 622 623CREATE TABLE t1 (a INT, b VARCHAR(10),c DATETIME(3)); 624INSERT INTO t1 VALUES (1,'b1','2001-01-01 10:20:30.123'); 625INSERT INTO t1 VALUES (2,'b2','2001-01-02 10:20:30.123'); 626CREATE TABLE t2 LIKE t1; 627DELIMITER $$; 628CREATE PROCEDURE p1() 629BEGIN 630 DECLARE done INT DEFAULT 0; 631 DECLARE v_a TYPE OF t1.a; 632 DECLARE v_b TYPE OF t1.b; 633 DECLARE v_c TYPE OF t1.c; 634 DECLARE c CURSOR FOR SELECT a,b,c FROM t1; 635 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; 636 OPEN c; 637 read_loop: LOOP 638 FETCH c INTO v_a, v_b, v_c; 639 IF done THEN 640 LEAVE read_loop; 641 END IF; 642 INSERT INTO t2 (a,b,c) VALUES (v_a, v_b, v_c); 643 END LOOP; 644 CLOSE c; 645END; 646$$ 647DELIMITER ;$$ 648CALL p1(); 649SELECT * FROM t2; 650DROP TABLE t2; 651DROP PROCEDURE p1; 652DROP TABLE t1; 653 654 655--echo # 656--echo # Using TYPE OF variables as SELECT INTO targets 657--echo # 658 659CREATE TABLE t1 (a INT, b VARCHAR(10),c DATETIME(3)); 660INSERT INTO t1 VALUES (1,'b1','2001-01-01 10:20:30.123'); 661DELIMITER $$; 662CREATE PROCEDURE p1() 663BEGIN 664 DECLARE v_a TYPE OF t1.a; 665 DECLARE v_b TYPE OF t1.b; 666 DECLARE v_c TYPE OF t1.c; 667 SELECT * FROM t1 INTO v_a, v_b, v_c; 668 SELECT v_a, v_b, v_c; 669END; 670$$ 671DELIMITER ;$$ 672CALL p1(); 673DROP PROCEDURE p1; 674DROP TABLE t1; 675 676--echo # 677--echo # End of MDEV-12461 TYPE OF and ROW TYPE OF anchored data types 678--echo # 679 680--echo # 681--echo # MDEV-14139 Anchored data types for variables 682--echo # 683 684DELIMITER $$; 685--error ER_SP_UNDECLARED_VAR 686BEGIN NOT ATOMIC 687 DECLARE a TYPE OF a; 688END; 689$$ 690DELIMITER ;$$ 691 692 693DELIMITER $$; 694BEGIN NOT ATOMIC 695 DECLARE int11 INT; 696 DECLARE dec103 DECIMAL(10,3); 697 DECLARE flt0 FLOAT; 698 DECLARE dbl0 DOUBLE; 699 DECLARE enum0 ENUM('a','b'); 700 DECLARE bit3 BIT(3); 701 702 DECLARE varchar10 VARCHAR(10); 703 DECLARE text1 TEXT; 704 DECLARE tinytext1 TINYTEXT; 705 DECLARE mediumtext1 MEDIUMTEXT; 706 DECLARE longtext1 LONGTEXT; 707 708 DECLARE time3 TIME(3); 709 DECLARE datetime4 DATETIME(4); 710 DECLARE timestamp5 TIMESTAMP(5); 711 DECLARE date0 DATE; 712 713 DECLARE a_int11 TYPE OF int11; 714 DECLARE a_dec103 TYPE OF dec103; 715 DECLARE a_flt0 TYPE OF flt0; 716 DECLARE a_dbl0 TYPE OF dbl0; 717 DECLARE a_bit3 TYPE OF bit3; 718 DECLARE a_enum0 TYPE OF enum0; 719 DECLARE a_varchar10 TYPE OF varchar10; 720 DECLARE a_text1 TYPE OF text1; 721 DECLARE a_tinytext1 TYPE OF tinytext1; 722 DECLARE a_mediumtext1 TYPE OF mediumtext1; 723 DECLARE a_longtext1 TYPE OF longtext1; 724 DECLARE a_time3 TYPE OF time3; 725 DECLARE a_datetime4 TYPE OF datetime4; 726 DECLARE a_timestamp5 TYPE OF timestamp5; 727 DECLARE a_date0 TYPE OF date0; 728 729 DECLARE aa_int11 TYPE OF a_int11; 730 DECLARE aa_dec103 TYPE OF a_dec103; 731 DECLARE aa_flt0 TYPE OF a_flt0; 732 DECLARE aa_dbl0 TYPE OF a_dbl0; 733 DECLARE aa_bit3 TYPE OF a_bit3; 734 DECLARE aa_enum0 TYPE OF a_enum0; 735 DECLARE aa_varchar10 TYPE OF a_varchar10; 736 DECLARE aa_text1 TYPE OF a_text1; 737 DECLARE aa_tinytext1 TYPE OF a_tinytext1; 738 DECLARE aa_mediumtext1 TYPE OF a_mediumtext1; 739 DECLARE aa_longtext1 TYPE OF a_longtext1; 740 DECLARE aa_time3 TYPE OF a_time3; 741 DECLARE aa_datetime4 TYPE OF a_datetime4; 742 DECLARE aa_timestamp5 TYPE OF a_timestamp5; 743 DECLARE aa_date0 TYPE OF a_date0; 744 745 CREATE TABLE t1 AS 746 SELECT a_int11,a_dec103,a_flt0,a_dbl0,a_bit3, 747 a_enum0,a_varchar10, 748 a_text1,a_tinytext1,a_mediumtext1,a_longtext1, 749 a_time3,a_datetime4,a_timestamp5,a_date0; 750 SHOW CREATE TABLE t1; 751 DROP TABLE t1; 752 753 CREATE TABLE t1 AS 754 SELECT aa_int11,aa_dec103,aa_flt0,aa_dbl0,aa_bit3, 755 aa_enum0,aa_varchar10, 756 aa_text1,aa_tinytext1,aa_mediumtext1,aa_longtext1, 757 aa_time3,aa_datetime4,aa_timestamp5,aa_date0; 758 SHOW CREATE TABLE t1; 759 DROP TABLE t1; 760 761END; 762$$ 763DELIMITER ;$$ 764