1call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); 2drop table if exists t1,t2,t3,t4; 3drop database if exists mysqltest1; 4drop database if exists client_test_db; 5create table t1 6( 7a int primary key, 8b char(10) 9); 10insert into t1 values (1,'one'); 11insert into t1 values (2,'two'); 12insert into t1 values (3,'three'); 13insert into t1 values (4,'four'); 14set @a=2; 15prepare stmt1 from 'select * from t1 where a <= ?'; 16execute stmt1 using @a; 17a b 181 one 192 two 20set @a=3; 21execute stmt1 using @a; 22a b 231 one 242 two 253 three 26deallocate prepare no_such_statement; 27ERROR HY000: Unknown prepared statement handler (no_such_statement) given to DEALLOCATE PREPARE 28execute stmt1; 29ERROR HY000: Incorrect arguments to EXECUTE 30prepare stmt2 from 'prepare nested_stmt from "select 1"'; 31ERROR HY000: This command is not supported in the prepared statement protocol yet 32prepare stmt2 from 'execute stmt1'; 33ERROR HY000: This command is not supported in the prepared statement protocol yet 34prepare stmt2 from 'deallocate prepare z'; 35ERROR HY000: This command is not supported in the prepared statement protocol yet 36prepare stmt3 from 'insert into t1 values (?,?)'; 37set @arg1=5, @arg2='five'; 38execute stmt3 using @arg1, @arg2; 39select * from t1 where a>3; 40a b 414 four 425 five 43prepare stmt4 from 'update t1 set a=? where b=?'; 44set @arg1=55, @arg2='five'; 45execute stmt4 using @arg1, @arg2; 46select * from t1 where a>3; 47a b 484 four 4955 five 50prepare stmt4 from 'create table t2 (a int)'; 51execute stmt4; 52prepare stmt4 from 'drop table t2'; 53execute stmt4; 54execute stmt4; 55ERROR 42S02: Unknown table 'test.t2' 56prepare stmt5 from 'select ? + a from t1'; 57set @a=1; 58execute stmt5 using @a; 59? + a 602 613 624 635 6456 65execute stmt5 using @no_such_var; 66? + a 67NULL 68NULL 69NULL 70NULL 71NULL 72set @nullvar=1; 73set @nullvar=NULL; 74execute stmt5 using @nullvar; 75? + a 76NULL 77NULL 78NULL 79NULL 80NULL 81set @nullvar2=NULL; 82execute stmt5 using @nullvar2; 83? + a 84NULL 85NULL 86NULL 87NULL 88NULL 89prepare stmt6 from 'select 1; select2'; 90ERROR 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 'select2' at line 1 91prepare stmt6 from 'insert into t1 values (5,"five"); select2'; 92ERROR 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 'select2' at line 1 93explain prepare stmt6 from 'insert into t1 values (5,"five"); select2'; 94ERROR 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 'insert into t1 values (5,"five"); select2'' at line 1 95create table t2 96( 97a int 98); 99insert into t2 values (0); 100set @arg00=NULL ; 101prepare stmt1 from 'select 1 FROM t2 where a=?' ; 102execute stmt1 using @arg00 ; 1031 104prepare stmt1 from @nosuchvar; 105ERROR 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 'NULL' at line 1 106set @ivar= 1234; 107prepare stmt1 from @ivar; 108ERROR 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 '1234' at line 1 109set @fvar= 123.4567; 110prepare stmt1 from @fvar; 111ERROR 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 '123.4567' at line 1 112drop table t1,t2; 113deallocate prepare stmt3; 114deallocate prepare stmt4; 115deallocate prepare stmt5; 116PREPARE stmt1 FROM "select _utf8 'A' collate utf8_bin = ?"; 117set @var='A'; 118EXECUTE stmt1 USING @var; 119_utf8 'A' collate utf8_bin = ? 1201 121DEALLOCATE PREPARE stmt1; 122create table t1 (id int); 123prepare stmt1 from "select FOUND_ROWS()"; 124select SQL_CALC_FOUND_ROWS * from t1; 125id 126execute stmt1; 127FOUND_ROWS() 1280 129insert into t1 values (1); 130select SQL_CALC_FOUND_ROWS * from t1; 131id 1321 133execute stmt1; 134FOUND_ROWS() 1351 136execute stmt1; 137FOUND_ROWS() 1381 139deallocate prepare stmt1; 140drop table t1; 141create table t1 142( 143c1 tinyint, c2 smallint, c3 mediumint, c4 int, 144c5 integer, c6 bigint, c7 float, c8 double, 145c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), 146c13 date, c14 datetime, c15 timestamp, c16 time, 147c17 year, c18 bit, c19 bool, c20 char, 148c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, 149c25 blob, c26 text, c27 mediumblob, c28 mediumtext, 150c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'), 151c32 set('monday', 'tuesday', 'wednesday') 152) engine = MYISAM ; 153create table t2 like t1; 154set @save_optimizer_switch=@@optimizer_switch; 155set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; 156set @tmp_optimizer_switch=@@optimizer_switch; 157set optimizer_switch='derived_merge=off,derived_with_keys=off'; 158set @stmt= ' explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25 ' ; 159prepare stmt1 from @stmt ; 160execute stmt1 ; 161id select_type table type possible_keys key key_len ref rows Extra 1621 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1636 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1645 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1654 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1663 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1672 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 168execute stmt1 ; 169id select_type table type possible_keys key key_len ref rows Extra 1701 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1716 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1725 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1734 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1743 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1752 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 176explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25; 177id select_type table type possible_keys key key_len ref rows Extra 1781 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1796 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1805 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1814 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1823 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1832 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 184deallocate prepare stmt1; 185set optimizer_switch=@tmp_optimizer_switch; 186drop tables t1,t2; 187set @@optimizer_switch=@save_optimizer_switch; 188set @arg00=1; 189prepare stmt1 from ' create table t1 (m int) as select 1 as m ' ; 190execute stmt1 ; 191select m from t1; 192m 1931 194drop table t1; 195prepare stmt1 from ' create table t1 (m int) as select ? as m ' ; 196execute stmt1 using @arg00; 197select m from t1; 198m 1991 200deallocate prepare stmt1; 201drop table t1; 202create table t1 (id int(10) unsigned NOT NULL default '0', 203name varchar(64) NOT NULL default '', 204PRIMARY KEY (id), UNIQUE KEY `name` (`name`)); 205insert into t1 values (1,'1'),(2,'2'),(3,'3'),(4,'4'),(5,'5'),(6,'6'),(7,'7'); 206prepare stmt1 from 'select name from t1 where id=? or id=?'; 207set @id1=1,@id2=6; 208execute stmt1 using @id1, @id2; 209name 2101 2116 212select name from t1 where id=1 or id=6; 213name 2141 2156 216deallocate prepare stmt1; 217drop table t1; 218create table t1 ( a int primary key, b varchar(30)) engine = MYISAM ; 219prepare stmt1 from ' show table status from test like ''t1%'' '; 220execute stmt1; 221Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary 222t1 MyISAM 10 Dynamic 0 0 0 4294967295 1024 0 NULL # # # latin1_swedish_ci NULL 288230376151710720 N 223show table status from test like 't1%' ; 224Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary 225t1 MyISAM 10 Dynamic 0 0 0 4294967295 1024 0 NULL # # # latin1_swedish_ci NULL 288230376151710720 N 226deallocate prepare stmt1 ; 227drop table t1; 228create table t1(a varchar(2), b varchar(3)); 229prepare stmt1 from "select a, b from t1 where (not (a='aa' and b < 'zzz'))"; 230execute stmt1; 231a b 232execute stmt1; 233a b 234deallocate prepare stmt1; 235drop table t1; 236prepare stmt1 from "select 1 into @var"; 237execute stmt1; 238execute stmt1; 239prepare stmt1 from "create table t1 select 1 as i"; 240execute stmt1; 241drop table t1; 242execute stmt1; 243prepare stmt1 from "insert into t1 select i from t1"; 244execute stmt1; 245execute stmt1; 246prepare stmt1 from "select * from t1 into outfile '<MYSQLTEST_VARDIR>/tmp/f1.txt'"; 247Warnings: 248Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 249execute stmt1; 250deallocate prepare stmt1; 251drop table t1; 252prepare stmt1 from 'select 1'; 253prepare STMT1 from 'select 2'; 254execute sTmT1; 2552 2562 257deallocate prepare StMt1; 258deallocate prepare Stmt1; 259ERROR HY000: Unknown prepared statement handler (Stmt1) given to DEALLOCATE PREPARE 260set names utf8; 261prepare `ü` from 'select 1234'; 262execute `ü` ; 2631234 2641234 265set names latin1; 266execute `ü`; 2671234 2681234 269deallocate prepare `ü`; 270set names default; 271create table t1 (a varchar(10)) charset=utf8; 272insert into t1 (a) values ('yahoo'); 273set character_set_connection=latin1; 274prepare stmt from 'select a from t1 where a like ?'; 275set @var='google'; 276execute stmt using @var; 277a 278execute stmt using @var; 279a 280deallocate prepare stmt; 281drop table t1; 282create table t1 (a bigint(20) not null primary key auto_increment); 283insert into t1 (a) values (null); 284select * from t1; 285a 2861 287prepare stmt from "insert into t1 (a) values (?)"; 288set @var=null; 289execute stmt using @var; 290select * from t1; 291a 2921 2932 294drop table t1; 295create table t1 (a timestamp not null); 296prepare stmt from "insert into t1 (a) values (?)"; 297execute stmt using @var; 298select * from t1; 299deallocate prepare stmt; 300drop table t1; 301prepare stmt from "select 'abc' like convert('abc' using utf8)"; 302execute stmt; 303'abc' like convert('abc' using utf8) 3041 305execute stmt; 306'abc' like convert('abc' using utf8) 3071 308deallocate prepare stmt; 309create table t1 ( a bigint ); 310prepare stmt from 'select a from t1 where a between ? and ?'; 311set @a=1; 312execute stmt using @a, @a; 313a 314execute stmt using @a, @a; 315a 316execute stmt using @a, @a; 317a 318drop table t1; 319deallocate prepare stmt; 320create table t1 (a int); 321prepare stmt from "select * from t1 where 1 > (1 in (SELECT * FROM t1))"; 322execute stmt; 323a 324execute stmt; 325a 326execute stmt; 327a 328drop table t1; 329deallocate prepare stmt; 330create table t1 (a int, b int); 331insert into t1 (a, b) values (1,1), (1,2), (2,1), (2,2); 332prepare stmt from 333"explain select * from t1 where t1.a=2 and t1.a=t1.b and t1.b > 1 + ?"; 334set @v=5; 335execute stmt using @v; 336id select_type table type possible_keys key key_len ref rows Extra 337- - - - - - - - NULL Impossible WHERE 338set @v=0; 339execute stmt using @v; 340id select_type table type possible_keys key key_len ref rows Extra 341- - - - - - - - 4 Using where 342set @v=5; 343execute stmt using @v; 344id select_type table type possible_keys key key_len ref rows Extra 345- - - - - - - - NULL Impossible WHERE 346drop table t1; 347deallocate prepare stmt; 348create table t1 (a int); 349insert into t1 (a) values (1), (2), (3), (4); 350set @precision=10000000000; 351select rand(), 352cast(rand(10)*@precision as unsigned integer) from t1; 353rand() cast(rand(10)*@precision as unsigned integer) 354- 6570515220 355- 1282061302 356- 6698761160 357- 9647622201 358prepare stmt from 359"select rand(), 360 cast(rand(10)*@precision as unsigned integer), 361 cast(rand(?)*@precision as unsigned integer) from t1"; 362set @var=1; 363execute stmt using @var; 364rand() cast(rand(10)*@precision as unsigned integer) cast(rand(?)*@precision as unsigned integer) 365- 6570515220 - 366- 1282061302 - 367- 6698761160 - 368- 9647622201 - 369set @var=2; 370execute stmt using @var; 371rand() cast(rand(10)*@precision as unsigned integer) cast(rand(?)*@precision as unsigned integer) 372- 6570515220 6555866465 373- 1282061302 1223466193 374- 6698761160 6449731874 375- 9647622201 8578261098 376set @var=3; 377execute stmt using @var; 378rand() cast(rand(10)*@precision as unsigned integer) cast(rand(?)*@precision as unsigned integer) 379- 6570515220 9057697560 380- 1282061302 3730790581 381- 6698761160 1480860535 382- 9647622201 6211931236 383drop table t1; 384deallocate prepare stmt; 385create database mysqltest1; 386create table t1 (a int); 387create table mysqltest1.t1 (a int); 388select * from t1, mysqltest1.t1; 389a a 390prepare stmt from "select * from t1, mysqltest1.t1"; 391execute stmt; 392a a 393execute stmt; 394a a 395execute stmt; 396a a 397drop table t1; 398drop table mysqltest1.t1; 399drop database mysqltest1; 400deallocate prepare stmt; 401select '1.1' as a, '1.2' as a UNION SELECT '2.1', '2.2'; 402a a 4031.1 1.2 4042.1 2.2 405prepare stmt from 406"select '1.1' as a, '1.2' as a UNION SELECT '2.1', '2.2'"; 407execute stmt; 408a a 4091.1 1.2 4102.1 2.2 411execute stmt; 412a a 4131.1 1.2 4142.1 2.2 415execute stmt; 416a a 4171.1 1.2 4182.1 2.2 419deallocate prepare stmt; 420create table t1 (a int); 421insert into t1 values (1),(2),(3); 422create table t2 select * from t1; 423prepare stmt FROM 'create table t2 select * from t1'; 424drop table t2; 425execute stmt; 426drop table t2; 427execute stmt; 428execute stmt; 429ERROR 42S01: Table 't2' already exists 430drop table t2; 431execute stmt; 432drop table t1,t2; 433deallocate prepare stmt; 434create table t1 (a int); 435insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 436prepare stmt from "select sql_calc_found_rows * from t1 limit 2"; 437execute stmt; 438a 4391 4402 441select found_rows(); 442found_rows() 44310 444execute stmt; 445a 4461 4472 448select found_rows(); 449found_rows() 45010 451execute stmt; 452a 4531 4542 455select found_rows(); 456found_rows() 45710 458deallocate prepare stmt; 459drop table t1; 460CREATE TABLE t1 (N int, M tinyint); 461INSERT INTO t1 VALUES (1,0),(1,0),(2,0),(2,0),(3,0); 462PREPARE stmt FROM 'UPDATE t1 AS P1 INNER JOIN (SELECT N FROM t1 GROUP BY N HAVING COUNT(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2'; 463EXECUTE stmt; 464DEALLOCATE PREPARE stmt; 465DROP TABLE t1; 466prepare stmt from "select ? is null, ? is not null, ?"; 467select @no_such_var is null, @no_such_var is not null, @no_such_var; 468@no_such_var is null @no_such_var is not null @no_such_var 4691 0 NULL 470execute stmt using @no_such_var, @no_such_var, @no_such_var; 471? is null ? is not null ? 4721 0 NULL 473set @var='abc'; 474select @var is null, @var is not null, @var; 475@var is null @var is not null @var 4760 1 abc 477execute stmt using @var, @var, @var; 478? is null ? is not null ? 4790 1 abc 480set @var=null; 481select @var is null, @var is not null, @var; 482@var is null @var is not null @var 4831 0 NULL 484execute stmt using @var, @var, @var; 485? is null ? is not null ? 4861 0 NULL 487create table t1 (pnum char(3)); 488create table t2 (pnum char(3)); 489prepare stmt from "select pnum from t2 having pnum in (select 'p1' from t1)"; 490execute stmt; 491pnum 492execute stmt; 493pnum 494execute stmt; 495pnum 496deallocate prepare stmt; 497drop table t1, t2; 498drop table if exists t1; 499create temporary table if not exists t1 (a1 int); 500prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1"; 501drop temporary table t1; 502create temporary table if not exists t1 (a1 int); 503execute stmt; 504drop temporary table t1; 505create temporary table if not exists t1 (a1 int); 506execute stmt; 507drop temporary table t1; 508create temporary table if not exists t1 (a1 int); 509execute stmt; 510drop temporary table t1; 511deallocate prepare stmt; 512create table t1 (a varchar(20)); 513insert into t1 values ('foo'); 514prepare stmt FROM 'SELECT char_length (a) FROM t1'; 515prepare stmt2 FROM 'SELECT not_a_function (a) FROM t1'; 516ERROR 42000: FUNCTION test.not_a_function does not exist 517drop table t1; 518prepare stmt from "SELECT SQL_CALC_FOUND_ROWS 'foo' UNION SELECT 'bar' LIMIT 0"; 519execute stmt; 520foo 521SELECT FOUND_ROWS(); 522FOUND_ROWS() 5232 524execute stmt; 525foo 526SELECT FOUND_ROWS(); 527FOUND_ROWS() 5282 529deallocate prepare stmt; 530drop table if exists t1; 531Warnings: 532Note 1051 Unknown table 'test.t1' 533create table t1 (c1 int(11) not null, c2 int(11) not null, 534primary key (c1,c2), key c2 (c2), key c1 (c1)); 535insert into t1 values (200887, 860); 536insert into t1 values (200887, 200887); 537select * from t1 where (c1=200887 and c2=200887) or c2=860; 538c1 c2 539200887 860 540200887 200887 541prepare stmt from 542"select * from t1 where (c1=200887 and c2=200887) or c2=860"; 543execute stmt; 544c1 c2 545200887 860 546200887 200887 547prepare stmt from 548"select * from t1 where (c1=200887 and c2=?) or c2=?"; 549set @a=200887, @b=860; 550execute stmt using @a, @b; 551c1 c2 552200887 860 553200887 200887 554deallocate prepare stmt; 555drop table t1; 556create table t1 ( 557id bigint(20) not null auto_increment, 558code varchar(20) character set utf8 collate utf8_bin not null default '', 559company_name varchar(250) character set utf8 collate utf8_bin default null, 560setup_mode tinyint(4) default null, 561start_date datetime default null, 562primary key (id), unique key code (code) 563); 564create table t2 ( 565id bigint(20) not null auto_increment, 566email varchar(250) character set utf8 collate utf8_bin default null, 567name varchar(250) character set utf8 collate utf8_bin default null, 568t1_id bigint(20) default null, 569password varchar(250) character set utf8 collate utf8_bin default null, 570primary_contact tinyint(4) not null default '0', 571email_opt_in tinyint(4) not null default '1', 572primary key (id), unique key email (email), key t1_id (t1_id), 573constraint t2_fk1 foreign key (t1_id) references t1 (id) 574); 575insert into t1 values 576(1, 'demo', 'demo s', 0, current_date()), 577(2, 'code2', 'name 2', 0, current_date()), 578(3, 'code3', 'name 3', 0, current_date()); 579insert into t2 values 580(2, 'email1', 'name1', 3, 'password1', 0, 0), 581(3, 'email2', 'name1', 1, 'password2', 1, 0), 582(5, 'email3', 'name3', 2, 'password3', 0, 0); 583prepare stmt from 'select t2.id from t2, t1 where (t1.id=? and t2.t1_id=t1.id)'; 584set @a=1; 585execute stmt using @a; 586id 5873 588select t2.id from t2, t1 where (t1.id=1 and t2.t1_id=t1.id); 589id 5903 591deallocate prepare stmt; 592drop table t1, t2; 593create table t1 (id int); 594prepare stmt from "insert into t1 (id) select id from t1 union select id from t1"; 595execute stmt; 596execute stmt; 597deallocate prepare stmt; 598drop table t1; 599create table t1 ( 600id int(11) unsigned not null primary key auto_increment, 601partner_id varchar(35) not null, 602t1_status_id int(10) unsigned 603); 604insert into t1 values ("1", "partner1", "10"), ("2", "partner2", "10"), 605("3", "partner3", "10"), ("4", "partner4", "10"); 606create table t2 ( 607id int(11) unsigned not null default '0', 608t1_line_id int(11) unsigned not null default '0', 609article_id varchar(20), 610sequence int(11) not null default '0', 611primary key (id,t1_line_id) 612); 613insert into t2 values ("1", "1", "sup", "0"), ("2", "1", "sup", "1"), 614("2", "2", "sup", "2"), ("2", "3", "sup", "3"), 615("2", "4", "imp", "4"), ("3", "1", "sup", "0"), 616("4", "1", "sup", "0"); 617create table t3 ( 618id int(11) not null default '0', 619preceding_id int(11) not null default '0', 620primary key (id,preceding_id) 621); 622create table t4 ( 623user_id varchar(50) not null, 624article_id varchar(20) not null, 625primary key (user_id,article_id) 626); 627insert into t4 values("nicke", "imp"); 628prepare stmt from 629'select distinct t1.partner_id 630from t1 left join t3 on t1.id = t3.id 631 left join t1 pp on pp.id = t3.preceding_id 632where 633 exists ( 634 select * 635 from t2 as pl_inner 636 where pl_inner.id = t1.id 637 and pl_inner.sequence <= ( 638 select min(sequence) from t2 pl_seqnr 639 where pl_seqnr.id = t1.id 640 ) 641 and exists ( 642 select * from t4 643 where t4.article_id = pl_inner.article_id 644 and t4.user_id = ? 645 ) 646 ) 647 and t1.id = ? 648group by t1.id 649having count(pp.id) = 0'; 650set @user_id = 'nicke'; 651set @id = '2'; 652execute stmt using @user_id, @id; 653partner_id 654execute stmt using @user_id, @id; 655partner_id 656deallocate prepare stmt; 657drop table t1, t2, t3, t4; 658prepare stmt from 'select ?=?'; 659set @a='CHRISTINE '; 660set @b='CHRISTINE'; 661execute stmt using @a, @b; 662?=? 6631 664execute stmt using @a, @b; 665?=? 6661 667set @a=1, @b=2; 668execute stmt using @a, @b; 669?=? 6700 671set @a='CHRISTINE '; 672set @b='CHRISTINE'; 673execute stmt using @a, @b; 674?=? 6751 676deallocate prepare stmt; 677create table t1 (a int); 678prepare stmt from "select ??"; 679ERROR 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 '?' at line 1 680prepare stmt from "select ?FROM t1"; 681ERROR 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 t1' at line 1 682prepare stmt from "select FROM t1 WHERE?=1"; 683ERROR 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 t1 WHERE?=1' at line 1 684prepare stmt from "update t1 set a=a+?WHERE 1"; 685ERROR 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 '?WHERE 1' at line 1 686select ?; 687ERROR 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 '?' at line 1 688select ??; 689ERROR 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 '??' at line 1 690select ? from t1; 691ERROR 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 t1' at line 1 692drop table t1; 693prepare stmt from "select @@time_zone"; 694execute stmt; 695@@time_zone 696SYSTEM 697set @@time_zone:='Japan'; 698execute stmt; 699@@time_zone 700Japan 701prepare stmt from "select @@tx_isolation"; 702execute stmt; 703@@tx_isolation 704REPEATABLE-READ 705set transaction isolation level read committed; 706execute stmt; 707@@tx_isolation 708REPEATABLE-READ 709set transaction isolation level serializable; 710execute stmt; 711@@tx_isolation 712REPEATABLE-READ 713set @@tx_isolation=default; 714execute stmt; 715@@tx_isolation 716REPEATABLE-READ 717deallocate prepare stmt; 718prepare stmt from "create temporary table t1 (letter enum('','a','b','c') 719not null)"; 720execute stmt; 721drop table t1; 722execute stmt; 723drop table t1; 724execute stmt; 725drop table t1; 726set names latin1; 727prepare stmt from "create table t1 (a enum('test') default 'test') 728 character set utf8"; 729execute stmt; 730drop table t1; 731execute stmt; 732drop table t1; 733execute stmt; 734drop table t1; 735set names default; 736deallocate prepare stmt; 737create table t1 ( 738word_id mediumint(8) unsigned not null default '0', 739formatted varchar(20) not null default '' 740); 741insert into t1 values 742(80,'pendant'), (475,'pretendants'), (989,'tendances'), 743(1019,'cependant'),(1022,'abondance'),(1205,'independants'), 744(13,'lessiver'),(25,'lambiner'),(46,'situer'),(71,'terminer'), 745(82,'decrocher'); 746select count(*) from t1 where formatted like '%NDAN%'; 747count(*) 7486 749select count(*) from t1 where formatted like '%ER'; 750count(*) 7515 752prepare stmt from "select count(*) from t1 where formatted like ?"; 753set @like="%NDAN%"; 754execute stmt using @like; 755count(*) 7566 757set @like="%ER"; 758execute stmt using @like; 759count(*) 7605 761set @like="%NDAN%"; 762execute stmt using @like; 763count(*) 7646 765set @like="%ER"; 766execute stmt using @like; 767count(*) 7685 769deallocate prepare stmt; 770drop table t1; 771prepare stmt from 'create table t1 (a varchar(10) character set utf8)'; 772execute stmt; 773insert ignore into t1 (a) values (repeat('a', 20)); 774select length(a) from t1; 775length(a) 77610 777drop table t1; 778execute stmt; 779insert ignore into t1 (a) values (repeat('a', 20)); 780select length(a) from t1; 781length(a) 78210 783drop table t1; 784deallocate prepare stmt; 785create table t1 (col1 integer, col2 integer); 786insert into t1 values(100,100),(101,101),(102,102),(103,103); 787prepare stmt from 'select col1, col2 from t1 where (col1, col2) in ((?,?))'; 788set @a=100, @b=100; 789execute stmt using @a,@b; 790col1 col2 791100 100 792set @a=101, @b=101; 793execute stmt using @a,@b; 794col1 col2 795101 101 796set @a=102, @b=102; 797execute stmt using @a,@b; 798col1 col2 799102 102 800set @a=102, @b=103; 801execute stmt using @a,@b; 802col1 col2 803deallocate prepare stmt; 804drop table t1; 805set @old_max_prepared_stmt_count= @@max_prepared_stmt_count; 806show variables like 'max_prepared_stmt_count'; 807Variable_name Value 808max_prepared_stmt_count 16382 809show status like 'prepared_stmt_count'; 810Variable_name Value 811Prepared_stmt_count 0 812select @@max_prepared_stmt_count; 813@@max_prepared_stmt_count 81416382 815set global max_prepared_stmt_count=-1; 816Warnings: 817Warning 1292 Truncated incorrect max_prepared_stmt_count value: '-1' 818select @@max_prepared_stmt_count; 819@@max_prepared_stmt_count 8200 821set global max_prepared_stmt_count=10000000000000000; 822Warnings: 823Warning 1292 Truncated incorrect max_prepared_stmt_count value: '10000000000000000' 824select @@max_prepared_stmt_count; 825@@max_prepared_stmt_count 8264294967295 827set global max_prepared_stmt_count=default; 828select @@max_prepared_stmt_count; 829@@max_prepared_stmt_count 83016382 831set @@max_prepared_stmt_count=1; 832ERROR HY000: Variable 'max_prepared_stmt_count' is a GLOBAL variable and should be set with SET GLOBAL 833set max_prepared_stmt_count=1; 834ERROR HY000: Variable 'max_prepared_stmt_count' is a GLOBAL variable and should be set with SET GLOBAL 835set local max_prepared_stmt_count=1; 836ERROR HY000: Variable 'max_prepared_stmt_count' is a GLOBAL variable and should be set with SET GLOBAL 837set global max_prepared_stmt_count=1; 838select @@max_prepared_stmt_count; 839@@max_prepared_stmt_count 8401 841set global max_prepared_stmt_count=0; 842select @@max_prepared_stmt_count; 843@@max_prepared_stmt_count 8440 845show status like 'prepared_stmt_count'; 846Variable_name Value 847Prepared_stmt_count 0 848prepare stmt from "select 1"; 849ERROR 42000: Can't create more than max_prepared_stmt_count statements (current value: 0) 850show status like 'prepared_stmt_count'; 851Variable_name Value 852Prepared_stmt_count 0 853set global max_prepared_stmt_count=1; 854prepare stmt from "select 1"; 855show status like 'prepared_stmt_count'; 856Variable_name Value 857Prepared_stmt_count 1 858prepare stmt1 from "select 1"; 859ERROR 42000: Can't create more than max_prepared_stmt_count statements (current value: 1) 860show status like 'prepared_stmt_count'; 861Variable_name Value 862Prepared_stmt_count 1 863deallocate prepare stmt; 864show status like 'prepared_stmt_count'; 865Variable_name Value 866Prepared_stmt_count 0 867prepare stmt from "select 1"; 868show status like 'prepared_stmt_count'; 869Variable_name Value 870Prepared_stmt_count 1 871prepare stmt from "select 2"; 872show status like 'prepared_stmt_count'; 873Variable_name Value 874Prepared_stmt_count 1 875show status like 'prepared_stmt_count'; 876Variable_name Value 877Prepared_stmt_count 1 878select @@max_prepared_stmt_count; 879@@max_prepared_stmt_count 8801 881set global max_prepared_stmt_count=0; 882prepare stmt from "select 1"; 883ERROR 42000: Can't create more than max_prepared_stmt_count statements (current value: 0) 884execute stmt; 885ERROR HY000: Unknown prepared statement handler (stmt) given to EXECUTE 886show status like 'prepared_stmt_count'; 887Variable_name Value 888Prepared_stmt_count 0 889prepare stmt from "select 1"; 890ERROR 42000: Can't create more than max_prepared_stmt_count statements (current value: 0) 891show status like 'prepared_stmt_count'; 892Variable_name Value 893Prepared_stmt_count 0 894set global max_prepared_stmt_count=3; 895select @@max_prepared_stmt_count; 896@@max_prepared_stmt_count 8973 898show status like 'prepared_stmt_count'; 899Variable_name Value 900Prepared_stmt_count 0 901prepare stmt from "select 1"; 902connect con1,localhost,root,,; 903connection con1; 904prepare stmt from "select 2"; 905prepare stmt1 from "select 3"; 906prepare stmt2 from "select 4"; 907ERROR 42000: Can't create more than max_prepared_stmt_count statements (current value: 3) 908connection default; 909prepare stmt2 from "select 4"; 910ERROR 42000: Can't create more than max_prepared_stmt_count statements (current value: 3) 911select @@max_prepared_stmt_count; 912@@max_prepared_stmt_count 9133 914show status like 'prepared_stmt_count'; 915Variable_name Value 916Prepared_stmt_count 3 917disconnect con1; 918connection default; 919deallocate prepare stmt; 920select @@max_prepared_stmt_count; 921@@max_prepared_stmt_count 9223 923show status like 'prepared_stmt_count'; 924Variable_name Value 925Prepared_stmt_count 0 926set global max_prepared_stmt_count= @old_max_prepared_stmt_count; 927drop table if exists t1; 928create temporary table if not exists t1 (a1 int); 929prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1"; 930drop temporary table t1; 931create temporary table if not exists t1 (a1 int); 932execute stmt; 933drop temporary table t1; 934create temporary table if not exists t1 (a1 int); 935execute stmt; 936drop temporary table t1; 937create temporary table if not exists t1 (a1 int); 938execute stmt; 939drop temporary table t1; 940deallocate prepare stmt; 941CREATE TABLE t1( 942ID int(10) unsigned NOT NULL auto_increment, 943Member_ID varchar(15) NOT NULL default '', 944Action varchar(12) NOT NULL, 945Action_Date datetime NOT NULL, 946Track varchar(15) default NULL, 947User varchar(12) default NULL, 948Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update 949CURRENT_TIMESTAMP, 950PRIMARY KEY (ID), 951KEY Action (Action), 952KEY Action_Date (Action_Date) 953); 954INSERT INTO t1(Member_ID, Action, Action_Date, Track) VALUES 955('111111', 'Disenrolled', '2006-03-01', 'CAD' ), 956('111111', 'Enrolled', '2006-03-01', 'CAD' ), 957('111111', 'Disenrolled', '2006-07-03', 'CAD' ), 958('222222', 'Enrolled', '2006-03-07', 'CAD' ), 959('222222', 'Enrolled', '2006-03-07', 'CHF' ), 960('222222', 'Disenrolled', '2006-08-02', 'CHF' ), 961('333333', 'Enrolled', '2006-03-01', 'CAD' ), 962('333333', 'Disenrolled', '2006-03-01', 'CAD' ), 963('444444', 'Enrolled', '2006-03-01', 'CAD' ), 964('555555', 'Disenrolled', '2006-03-01', 'CAD' ), 965('555555', 'Enrolled', '2006-07-21', 'CAD' ), 966('555555', 'Disenrolled', '2006-03-01', 'CHF' ), 967('666666', 'Enrolled', '2006-02-09', 'CAD' ), 968('666666', 'Enrolled', '2006-05-12', 'CHF' ), 969('666666', 'Disenrolled', '2006-06-01', 'CAD' ); 970PREPARE STMT FROM 971"SELECT GROUP_CONCAT(Track SEPARATOR ', ') FROM t1 972 WHERE Member_ID=? AND Action='Enrolled' AND 973 (Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t1 974 WHERE Member_ID=? 975 GROUP BY Track 976 HAVING Track>='CAD' AND 977 MAX(Action_Date)>'2006-03-01')"; 978SET @id='111111'; 979EXECUTE STMT USING @id,@id; 980GROUP_CONCAT(Track SEPARATOR ', ') 981NULL 982SET @id='222222'; 983EXECUTE STMT USING @id,@id; 984GROUP_CONCAT(Track SEPARATOR ', ') 985CAD 986DEALLOCATE PREPARE STMT; 987DROP TABLE t1; 988DROP TABLE IF EXISTS t1; 989CREATE TABLE t1 (i INT, INDEX(i)); 990INSERT INTO t1 VALUES (1); 991PREPARE stmt FROM "SELECT (COUNT(i) = 1), COUNT(i) FROM t1 WHERE i = ?"; 992SET @a = 0; 993EXECUTE stmt USING @a; 994(COUNT(i) = 1) COUNT(i) 9950 0 996SET @a = 1; 997EXECUTE stmt USING @a; 998(COUNT(i) = 1) COUNT(i) 9991 1 1000SET @a = 0; 1001EXECUTE stmt USING @a; 1002(COUNT(i) = 1) COUNT(i) 10030 0 1004PREPARE stmt FROM "SELECT (AVG(i) = 1), AVG(i) FROM t1 WHERE i = ?"; 1005SET @a = 0; 1006EXECUTE stmt USING @a; 1007(AVG(i) = 1) AVG(i) 1008NULL NULL 1009SET @a = 1; 1010EXECUTE stmt USING @a; 1011(AVG(i) = 1) AVG(i) 10121 1.0000 1013SET @a = 0; 1014EXECUTE stmt USING @a; 1015(AVG(i) = 1) AVG(i) 1016NULL NULL 1017PREPARE stmt FROM "SELECT (VARIANCE(i) = 1), VARIANCE(i) FROM t1 WHERE i = ?"; 1018SET @a = 0; 1019EXECUTE stmt USING @a; 1020(VARIANCE(i) = 1) VARIANCE(i) 1021NULL NULL 1022SET @a = 1; 1023EXECUTE stmt USING @a; 1024(VARIANCE(i) = 1) VARIANCE(i) 10250 0.0000 1026SET @a = 0; 1027EXECUTE stmt USING @a; 1028(VARIANCE(i) = 1) VARIANCE(i) 1029NULL NULL 1030PREPARE stmt FROM "SELECT (STDDEV(i) = 1), STDDEV(i) FROM t1 WHERE i = ?"; 1031SET @a = 0; 1032EXECUTE stmt USING @a; 1033(STDDEV(i) = 1) STDDEV(i) 1034NULL NULL 1035SET @a = 1; 1036EXECUTE stmt USING @a; 1037(STDDEV(i) = 1) STDDEV(i) 10380 0.0000 1039SET @a = 0; 1040EXECUTE stmt USING @a; 1041(STDDEV(i) = 1) STDDEV(i) 1042NULL NULL 1043PREPARE stmt FROM "SELECT (BIT_OR(i) = 1), BIT_OR(i) FROM t1 WHERE i = ?"; 1044SET @a = 0; 1045EXECUTE stmt USING @a; 1046(BIT_OR(i) = 1) BIT_OR(i) 10470 0 1048SET @a = 1; 1049EXECUTE stmt USING @a; 1050(BIT_OR(i) = 1) BIT_OR(i) 10511 1 1052SET @a = 0; 1053EXECUTE stmt USING @a; 1054(BIT_OR(i) = 1) BIT_OR(i) 10550 0 1056PREPARE stmt FROM "SELECT (BIT_AND(i) = 1), BIT_AND(i) FROM t1 WHERE i = ?"; 1057SET @a = 0; 1058EXECUTE stmt USING @a; 1059(BIT_AND(i) = 1) BIT_AND(i) 10600 18446744073709551615 1061SET @a = 1; 1062EXECUTE stmt USING @a; 1063(BIT_AND(i) = 1) BIT_AND(i) 10641 1 1065SET @a = 0; 1066EXECUTE stmt USING @a; 1067(BIT_AND(i) = 1) BIT_AND(i) 10680 18446744073709551615 1069PREPARE stmt FROM "SELECT (BIT_XOR(i) = 1), BIT_XOR(i) FROM t1 WHERE i = ?"; 1070SET @a = 0; 1071EXECUTE stmt USING @a; 1072(BIT_XOR(i) = 1) BIT_XOR(i) 10730 0 1074SET @a = 1; 1075EXECUTE stmt USING @a; 1076(BIT_XOR(i) = 1) BIT_XOR(i) 10771 1 1078SET @a = 0; 1079EXECUTE stmt USING @a; 1080(BIT_XOR(i) = 1) BIT_XOR(i) 10810 0 1082DEALLOCATE PREPARE stmt; 1083DROP TABLE t1; 1084DROP TABLE IF EXISTS t1, t2; 1085CREATE TABLE t1 (i INT); 1086PREPARE st_19182 1087FROM "CREATE TABLE t2 (i INT, j INT, KEY (i), KEY(j)) SELECT i FROM t1"; 1088EXECUTE st_19182; 1089DESC t2; 1090Field Type Null Key Default Extra 1091j int(11) YES MUL NULL 1092i int(11) YES MUL NULL 1093DROP TABLE t2; 1094EXECUTE st_19182; 1095DESC t2; 1096Field Type Null Key Default Extra 1097j int(11) YES MUL NULL 1098i int(11) YES MUL NULL 1099DEALLOCATE PREPARE st_19182; 1100DROP TABLE t2, t1; 1101drop database if exists mysqltest; 1102drop table if exists t1, t2; 1103create database mysqltest character set utf8; 1104prepare stmt1 from "create table mysqltest.t1 (c char(10))"; 1105prepare stmt2 from "create table mysqltest.t2 select 'test'"; 1106execute stmt1; 1107execute stmt2; 1108show create table mysqltest.t1; 1109Table Create Table 1110t1 CREATE TABLE `t1` ( 1111 `c` char(10) DEFAULT NULL 1112) ENGINE=MyISAM DEFAULT CHARSET=utf8 1113show create table mysqltest.t2; 1114Table Create Table 1115t2 CREATE TABLE `t2` ( 1116 `test` varchar(4) CHARACTER SET latin1 NOT NULL 1117) ENGINE=MyISAM DEFAULT CHARSET=utf8 1118drop table mysqltest.t1; 1119drop table mysqltest.t2; 1120alter database mysqltest character set latin1; 1121execute stmt1; 1122execute stmt2; 1123show create table mysqltest.t1; 1124Table Create Table 1125t1 CREATE TABLE `t1` ( 1126 `c` char(10) DEFAULT NULL 1127) ENGINE=MyISAM DEFAULT CHARSET=latin1 1128show create table mysqltest.t2; 1129Table Create Table 1130t2 CREATE TABLE `t2` ( 1131 `test` varchar(4) NOT NULL 1132) ENGINE=MyISAM DEFAULT CHARSET=latin1 1133drop database mysqltest; 1134deallocate prepare stmt1; 1135deallocate prepare stmt2; 1136execute stmt; 1137show create table t1; 1138drop table t1; 1139execute stmt; 1140show create table t1; 1141drop table t1; 1142deallocate prepare stmt; 1143CREATE TABLE t1(a int); 1144INSERT INTO t1 VALUES (2), (3), (1); 1145PREPARE st1 FROM 1146'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a'; 1147EXECUTE st1; 1148a 11491 11502 11513 115211 115312 115413 1155EXECUTE st1; 1156a 11571 11582 11593 116011 116112 116213 1163DEALLOCATE PREPARE st1; 1164DROP TABLE t1; 1165create table t1 (a int, b tinyint); 1166prepare st1 from 'update t1 set b= (str_to_date(a, a))'; 1167execute st1; 1168deallocate prepare st1; 1169drop table t1; 1170End of 4.1 tests. 1171create table t1 (a varchar(20)); 1172insert into t1 values ('foo'); 1173prepare stmt FROM 'SELECT char_length (a) FROM t1'; 1174prepare stmt2 FROM 'SELECT not_a_function (a) FROM t1'; 1175ERROR 42000: FUNCTION test.not_a_function does not exist 1176drop table t1; 1177create table t1 (a char(3) not null, b char(3) not null, 1178c char(3) not null, primary key (a, b, c)); 1179create table t2 like t1; 1180prepare stmt from 1181"select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b) 1182 where t1.a=1"; 1183execute stmt; 1184a 1185execute stmt; 1186a 1187execute stmt; 1188a 1189prepare stmt from 1190"select t1.a, t1.b, t1.c, t2.a, t2.b, t2.c from 1191(t1 left outer join t2 on t2.a=? and t1.b=t2.b) 1192left outer join t2 t3 on t3.a=? where t1.a=?"; 1193set @a:=1, @b:=1, @c:=1; 1194execute stmt using @a, @b, @c; 1195a b c a b c 1196execute stmt using @a, @b, @c; 1197a b c a b c 1198execute stmt using @a, @b, @c; 1199a b c a b c 1200deallocate prepare stmt; 1201drop table t1,t2; 1202SET @aux= "SELECT COUNT(*) 1203 FROM INFORMATION_SCHEMA.COLUMNS A, 1204 INFORMATION_SCHEMA.COLUMNS B 1205 WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA 1206 AND A.TABLE_NAME = B.TABLE_NAME 1207 AND A.COLUMN_NAME = B.COLUMN_NAME AND 1208 A.TABLE_NAME = 'user'"; 1209prepare my_stmt from @aux; 1210execute my_stmt; 1211COUNT(*) 121247 1213Warnings: 1214Warning 1286 Unknown storage engine 'InnoDB' 1215Warning 1286 Unknown storage engine 'InnoDB' 1216Warning 1286 Unknown storage engine 'InnoDB' 1217execute my_stmt; 1218COUNT(*) 121947 1220Warnings: 1221Warning 1286 Unknown storage engine 'InnoDB' 1222Warning 1286 Unknown storage engine 'InnoDB' 1223Warning 1286 Unknown storage engine 'InnoDB' 1224execute my_stmt; 1225COUNT(*) 122647 1227Warnings: 1228Warning 1286 Unknown storage engine 'InnoDB' 1229Warning 1286 Unknown storage engine 'InnoDB' 1230Warning 1286 Unknown storage engine 'InnoDB' 1231deallocate prepare my_stmt; 1232drop procedure if exists p1| 1233drop table if exists t1| 1234create table t1 (id int)| 1235insert into t1 values(1)| 1236create procedure p1(a int, b int) 1237begin 1238declare c int; 1239select max(id)+1 into c from t1; 1240insert into t1 select a+b; 1241insert into t1 select a-b; 1242insert into t1 select a-c; 1243end| 1244set @a= 3, @b= 4| 1245prepare stmt from "call p1(?, ?)"| 1246execute stmt using @a, @b| 1247execute stmt using @a, @b| 1248select * from t1| 1249id 12501 12517 1252-1 12531 12547 1255-1 1256-5 1257deallocate prepare stmt| 1258drop procedure p1| 1259drop table t1| 1260create table t1 (a int); 1261insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 1262prepare stmt from "select * from t1 limit ?, ?"; 1263set @offset=0, @limit=1; 1264execute stmt using @offset, @limit; 1265a 12661 1267select * from t1 limit 0, 1; 1268a 12691 1270set @offset=3, @limit=2; 1271execute stmt using @offset, @limit; 1272a 12734 12745 1275select * from t1 limit 3, 2; 1276a 12774 12785 1279prepare stmt from "select * from t1 limit ?"; 1280execute stmt using @limit; 1281a 12821 12832 1284prepare stmt from "select * from t1 where a in (select a from t1 limit ?)"; 1285ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' 1286prepare stmt from "select * from t1 union all select * from t1 limit ?, ?"; 1287set @offset=9; 1288set @limit=2; 1289execute stmt using @offset, @limit; 1290a 129110 12921 1293prepare stmt from "(select * from t1 limit ?, ?) union all 1294 (select * from t1 limit ?, ?) order by a limit ?"; 1295execute stmt using @offset, @limit, @offset, @limit, @limit; 1296a 129710 129810 1299drop table t1; 1300deallocate prepare stmt; 1301CREATE TABLE b12651_T1(a int) ENGINE=MYISAM; 1302CREATE TABLE b12651_T2(b int) ENGINE=MYISAM; 1303CREATE VIEW b12651_V1 as SELECT b FROM b12651_T2; 1304PREPARE b12651 FROM 'SELECT 1 FROM b12651_T1 WHERE a IN (SELECT b FROM b12651_V1)'; 1305EXECUTE b12651; 13061 1307DROP VIEW b12651_V1; 1308DROP TABLE b12651_T1, b12651_T2; 1309DEALLOCATE PREPARE b12651; 1310create table t1 (id int); 1311prepare ins_call from "insert into t1 (id) values (1)"; 1312execute ins_call; 1313select row_count(); 1314row_count() 13151 1316drop table t1; 1317create table t1 (a int, b int); 1318insert into t1 (a,b) values (2,8),(1,9),(3,7); 1319prepare stmt from "select * from t1 order by ?"; 1320set @a=NULL; 1321execute stmt using @a; 1322a b 13232 8 13241 9 13253 7 1326set @a=1; 1327execute stmt using @a; 1328a b 13291 9 13302 8 13313 7 1332set @a=2; 1333execute stmt using @a; 1334a b 13353 7 13362 8 13371 9 1338deallocate prepare stmt; 1339select * from t1 order by 1; 1340a b 13411 9 13422 8 13433 7 1344prepare stmt from "select * from t1 order by ?+1"; 1345set @a=0; 1346execute stmt using @a; 1347a b 13482 8 13491 9 13503 7 1351set @a=1; 1352execute stmt using @a; 1353a b 13542 8 13551 9 13563 7 1357deallocate prepare stmt; 1358select * from t1 order by 1+1; 1359a b 13602 8 13611 9 13623 7 1363drop table t1; 1364create table t1 (a int); 1365create table t2 like t1; 1366create table t3 like t2; 1367prepare stmt from "repair table t1"; 1368execute stmt; 1369Table Op Msg_type Msg_text 1370test.t1 repair status OK 1371execute stmt; 1372Table Op Msg_type Msg_text 1373test.t1 repair status OK 1374prepare stmt from "optimize table t1"; 1375execute stmt; 1376Table Op Msg_type Msg_text 1377test.t1 optimize status OK 1378execute stmt; 1379Table Op Msg_type Msg_text 1380test.t1 optimize status Table is already up to date 1381prepare stmt from "analyze table t1"; 1382execute stmt; 1383Table Op Msg_type Msg_text 1384test.t1 analyze status Engine-independent statistics collected 1385test.t1 analyze status Table is already up to date 1386execute stmt; 1387Table Op Msg_type Msg_text 1388test.t1 analyze status Engine-independent statistics collected 1389test.t1 analyze status Table is already up to date 1390prepare stmt from "repair table t1, t2, t3"; 1391execute stmt; 1392Table Op Msg_type Msg_text 1393test.t1 repair status OK 1394test.t2 repair status OK 1395test.t3 repair status OK 1396execute stmt; 1397Table Op Msg_type Msg_text 1398test.t1 repair status OK 1399test.t2 repair status OK 1400test.t3 repair status OK 1401prepare stmt from "optimize table t1, t2, t3"; 1402execute stmt; 1403Table Op Msg_type Msg_text 1404test.t1 optimize status OK 1405test.t2 optimize status OK 1406test.t3 optimize status OK 1407execute stmt; 1408Table Op Msg_type Msg_text 1409test.t1 optimize status Table is already up to date 1410test.t2 optimize status Table is already up to date 1411test.t3 optimize status Table is already up to date 1412prepare stmt from "analyze table t1, t2, t3"; 1413execute stmt; 1414Table Op Msg_type Msg_text 1415test.t1 analyze status Engine-independent statistics collected 1416test.t1 analyze status Table is already up to date 1417test.t2 analyze status Engine-independent statistics collected 1418test.t2 analyze status Table is already up to date 1419test.t3 analyze status Engine-independent statistics collected 1420test.t3 analyze status Table is already up to date 1421execute stmt; 1422Table Op Msg_type Msg_text 1423test.t1 analyze status Engine-independent statistics collected 1424test.t1 analyze status Table is already up to date 1425test.t2 analyze status Engine-independent statistics collected 1426test.t2 analyze status Table is already up to date 1427test.t3 analyze status Engine-independent statistics collected 1428test.t3 analyze status Table is already up to date 1429prepare stmt from "repair table t1, t4, t3"; 1430execute stmt; 1431Table Op Msg_type Msg_text 1432test.t1 repair status OK 1433test.t4 repair Error Table 'test.t4' doesn't exist 1434test.t4 repair status Operation failed 1435test.t3 repair status OK 1436execute stmt; 1437Table Op Msg_type Msg_text 1438test.t1 repair status OK 1439test.t4 repair Error Table 'test.t4' doesn't exist 1440test.t4 repair status Operation failed 1441test.t3 repair status OK 1442prepare stmt from "optimize table t1, t3, t4"; 1443execute stmt; 1444Table Op Msg_type Msg_text 1445test.t1 optimize status OK 1446test.t3 optimize status OK 1447test.t4 optimize Error Table 'test.t4' doesn't exist 1448test.t4 optimize status Operation failed 1449execute stmt; 1450Table Op Msg_type Msg_text 1451test.t1 optimize status Table is already up to date 1452test.t3 optimize status Table is already up to date 1453test.t4 optimize Error Table 'test.t4' doesn't exist 1454test.t4 optimize status Operation failed 1455prepare stmt from "analyze table t4, t1"; 1456execute stmt; 1457Table Op Msg_type Msg_text 1458test.t4 analyze Error Table 'test.t4' doesn't exist 1459test.t4 analyze status Operation failed 1460test.t1 analyze status Engine-independent statistics collected 1461test.t1 analyze status Table is already up to date 1462execute stmt; 1463Table Op Msg_type Msg_text 1464test.t4 analyze Error Table 'test.t4' doesn't exist 1465test.t4 analyze status Operation failed 1466test.t1 analyze status Engine-independent statistics collected 1467test.t1 analyze status Table is already up to date 1468deallocate prepare stmt; 1469drop table t1, t2, t3; 1470create database mysqltest_long_database_name_to_thrash_heap; 1471use test; 1472create table t1 (i int); 1473prepare stmt from "alter table test.t1 rename t1"; 1474use mysqltest_long_database_name_to_thrash_heap; 1475execute stmt; 1476show tables like 't1'; 1477Tables_in_mysqltest_long_database_name_to_thrash_heap (t1) 1478prepare stmt from "alter table test.t1 rename t1"; 1479use test; 1480execute stmt; 1481show tables like 't1'; 1482Tables_in_test (t1) 1483use mysqltest_long_database_name_to_thrash_heap; 1484show tables like 't1'; 1485Tables_in_mysqltest_long_database_name_to_thrash_heap (t1) 1486t1 1487deallocate prepare stmt; 1488use mysqltest_long_database_name_to_thrash_heap; 1489prepare stmt_create from "create table t1 (i int)"; 1490prepare stmt_insert from "insert into t1 (i) values (1)"; 1491prepare stmt_update from "update t1 set i=2"; 1492prepare stmt_delete from "delete from t1 where i=2"; 1493prepare stmt_select from "select * from t1"; 1494prepare stmt_alter from "alter table t1 add column (b int)"; 1495prepare stmt_alter1 from "alter table t1 drop column b"; 1496prepare stmt_analyze from "analyze table t1"; 1497prepare stmt_optimize from "optimize table t1"; 1498prepare stmt_show from "show tables like 't1'"; 1499prepare stmt_truncate from "truncate table t1"; 1500prepare stmt_drop from "drop table t1"; 1501drop table t1; 1502use test; 1503execute stmt_create; 1504show tables like 't1'; 1505Tables_in_test (t1) 1506use mysqltest_long_database_name_to_thrash_heap; 1507show tables like 't1'; 1508Tables_in_mysqltest_long_database_name_to_thrash_heap (t1) 1509t1 1510use test; 1511execute stmt_insert; 1512select * from mysqltest_long_database_name_to_thrash_heap.t1; 1513i 15141 1515execute stmt_update; 1516select * from mysqltest_long_database_name_to_thrash_heap.t1; 1517i 15182 1519execute stmt_delete; 1520execute stmt_select; 1521i 1522execute stmt_alter; 1523show columns from mysqltest_long_database_name_to_thrash_heap.t1; 1524Field Type Null Key Default Extra 1525i int(11) YES NULL 1526b int(11) YES NULL 1527execute stmt_alter1; 1528show columns from mysqltest_long_database_name_to_thrash_heap.t1; 1529Field Type Null Key Default Extra 1530i int(11) YES NULL 1531execute stmt_analyze; 1532Table Op Msg_type Msg_text 1533mysqltest_long_database_name_to_thrash_heap.t1 analyze status Engine-independent statistics collected 1534mysqltest_long_database_name_to_thrash_heap.t1 analyze status Table is already up to date 1535execute stmt_optimize; 1536Table Op Msg_type Msg_text 1537mysqltest_long_database_name_to_thrash_heap.t1 optimize status Table is already up to date 1538execute stmt_show; 1539Tables_in_mysqltest_long_database_name_to_thrash_heap (t1) 1540t1 1541execute stmt_truncate; 1542execute stmt_drop; 1543show tables like 't1'; 1544Tables_in_test (t1) 1545use mysqltest_long_database_name_to_thrash_heap; 1546show tables like 't1'; 1547Tables_in_mysqltest_long_database_name_to_thrash_heap (t1) 1548drop database mysqltest_long_database_name_to_thrash_heap; 1549prepare stmt_create from "create table t1 (i int)"; 1550ERROR 3D000: No database selected 1551prepare stmt_insert from "insert into t1 (i) values (1)"; 1552ERROR 3D000: No database selected 1553prepare stmt_update from "update t1 set i=2"; 1554ERROR 3D000: No database selected 1555prepare stmt_delete from "delete from t1 where i=2"; 1556ERROR 3D000: No database selected 1557prepare stmt_select from "select * from t1"; 1558ERROR 3D000: No database selected 1559prepare stmt_alter from "alter table t1 add column (b int)"; 1560ERROR 3D000: No database selected 1561prepare stmt_alter1 from "alter table t1 drop column b"; 1562ERROR 3D000: No database selected 1563prepare stmt_analyze from "analyze table t1"; 1564ERROR 3D000: No database selected 1565prepare stmt_optimize from "optimize table t1"; 1566ERROR 3D000: No database selected 1567prepare stmt_show from "show tables like 't1'"; 1568ERROR 3D000: No database selected 1569prepare stmt_truncate from "truncate table t1"; 1570ERROR 3D000: No database selected 1571prepare stmt_drop from "drop table t1"; 1572ERROR 3D000: No database selected 1573create temporary table t1 (i int); 1574ERROR 3D000: No database selected 1575use test; 1576DROP TABLE IF EXISTS t1, t2, t3; 1577CREATE TABLE t1 (i BIGINT, j BIGINT); 1578CREATE TABLE t2 (i BIGINT); 1579CREATE TABLE t3 (i BIGINT, j BIGINT); 1580PREPARE stmt FROM "SELECT * FROM t1 JOIN t2 ON (t2.i = t1.i) 1581 LEFT JOIN t3 ON ((t3.i, t3.j) = (t1.i, t1.j)) 1582 WHERE t1.i = ?"; 1583SET @a= 1; 1584EXECUTE stmt USING @a; 1585i j i i j 1586EXECUTE stmt USING @a; 1587i j i i j 1588DEALLOCATE PREPARE stmt; 1589DROP TABLE IF EXISTS t1, t2, t3; 1590DROP TABLE IF EXISTS t1, t2; 1591CREATE TABLE t1 (i INT KEY); 1592CREATE TABLE t2 (i INT); 1593INSERT INTO t1 VALUES (1), (2); 1594INSERT INTO t2 VALUES (1); 1595PREPARE stmt FROM "SELECT t2.i FROM t1 LEFT JOIN t2 ON t2.i = t1.i 1596 WHERE t1.i = ?"; 1597SET @arg= 1; 1598EXECUTE stmt USING @arg; 1599i 16001 1601SET @arg= 2; 1602EXECUTE stmt USING @arg; 1603i 1604NULL 1605SET @arg= 1; 1606EXECUTE stmt USING @arg; 1607i 16081 1609DEALLOCATE PREPARE stmt; 1610DROP TABLE t1, t2; 1611CREATE TABLE t1 (i INT); 1612CREATE VIEW v1 AS SELECT * FROM t1; 1613INSERT INTO t1 VALUES (1), (2); 1614SELECT t1.i FROM t1 JOIN v1 ON t1.i = v1.i 1615WHERE EXISTS (SELECT * FROM t1 WHERE v1.i = 1); 1616i 16171 1618PREPARE stmt FROM "SELECT t1.i FROM t1 JOIN v1 ON t1.i = v1.i 1619WHERE EXISTS (SELECT * FROM t1 WHERE v1.i = 1)"; 1620EXECUTE stmt; 1621i 16221 1623EXECUTE stmt; 1624i 16251 1626DEALLOCATE PREPARE stmt; 1627DROP VIEW v1; 1628DROP TABLE t1; 1629DROP PROCEDURE IF EXISTS p1; 1630flush status; 1631prepare sq from 'show status like "slow_queries"'; 1632execute sq; 1633Variable_name Value 1634Slow_queries 0 1635prepare no_index from 'select 1 from information_schema.tables limit 1'; 1636execute sq; 1637Variable_name Value 1638Slow_queries 0 1639execute no_index; 16401 16411 1642execute sq; 1643Variable_name Value 1644Slow_queries 1 1645deallocate prepare no_index; 1646deallocate prepare sq; 1647CREATE TABLE t1 (a int); 1648INSERT INTO t1 VALUES (1), (2); 1649CREATE TABLE t2 (b int); 1650INSERT INTO t2 VALUES (NULL); 1651SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL; 1652a 16531 16542 1655PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL'; 1656EXECUTE stmt; 1657a 16581 16592 1660DEALLOCATE PREPARE stmt; 1661PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2 limit ?) IS NULL'; 1662SET @arg=1; 1663EXECUTE stmt USING @arg; 1664a 16651 16662 1667DEALLOCATE PREPARE stmt; 1668DROP TABLE t1,t2; 1669drop table if exists t1; 1670create table t1 (s1 char(20)); 1671prepare stmt from "alter table t1 modify s1 int"; 1672execute stmt; 1673execute stmt; 1674drop table t1; 1675deallocate prepare stmt; 1676drop table if exists t1; 1677create table t1 (a int, b int); 1678prepare s_6895 from "alter table t1 drop column b"; 1679execute s_6895; 1680show columns from t1; 1681Field Type Null Key Default Extra 1682a int(11) YES NULL 1683drop table t1; 1684create table t1 (a int, b int); 1685execute s_6895; 1686show columns from t1; 1687Field Type Null Key Default Extra 1688a int(11) YES NULL 1689drop table t1; 1690create table t1 (a int, b int); 1691execute s_6895; 1692show columns from t1; 1693Field Type Null Key Default Extra 1694a int(11) YES NULL 1695deallocate prepare s_6895; 1696drop table t1; 1697create table t1 (i int primary key auto_increment) comment='comment for table t1'; 1698create table t2 (i int, j int, k int); 1699prepare stmt from "alter table t1 auto_increment=100"; 1700execute stmt; 1701show create table t1; 1702Table Create Table 1703t1 CREATE TABLE `t1` ( 1704 `i` int(11) NOT NULL AUTO_INCREMENT, 1705 PRIMARY KEY (`i`) 1706) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 COMMENT='comment for table t1' 1707flush tables; 1708select * from t2; 1709i j k 1710execute stmt; 1711show create table t1; 1712Table Create Table 1713t1 CREATE TABLE `t1` ( 1714 `i` int(11) NOT NULL AUTO_INCREMENT, 1715 PRIMARY KEY (`i`) 1716) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 COMMENT='comment for table t1' 1717deallocate prepare stmt; 1718drop table t1, t2; 1719set @old_character_set_server= @@character_set_server; 1720set @@character_set_server= latin1; 1721prepare stmt from "create database mysqltest_1"; 1722execute stmt; 1723show create database mysqltest_1; 1724Database Create Database 1725mysqltest_1 CREATE DATABASE `mysqltest_1` /*!40100 DEFAULT CHARACTER SET latin1 */ 1726drop database mysqltest_1; 1727set @@character_set_server= utf8; 1728execute stmt; 1729show create database mysqltest_1; 1730Database Create Database 1731mysqltest_1 CREATE DATABASE `mysqltest_1` /*!40100 DEFAULT CHARACTER SET utf8 */ 1732drop database mysqltest_1; 1733deallocate prepare stmt; 1734set @@character_set_server= @old_character_set_server; 1735drop tables if exists t1; 1736create table t1 (id int primary key auto_increment, value varchar(10)); 1737insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD'); 1738prepare stmt from "insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as v) as y on duplicate key update v = 'DUP'"; 1739execute stmt; 1740ERROR 42S22: Unknown column 'v' in 'field list' 1741execute stmt; 1742ERROR 42S22: Unknown column 'v' in 'field list' 1743deallocate prepare stmt; 1744prepare stmt from "insert into t1 (id, value) select * from (select 4 as id, 'FOURTH' as value) as y on duplicate key update y.value = 'DUP'"; 1745execute stmt; 1746ERROR 42S22: Unknown column 'y.value' in 'field list' 1747execute stmt; 1748ERROR 42S22: Unknown column 'y.value' in 'field list' 1749deallocate prepare stmt; 1750drop tables t1; 1751prepare stmt from "create table t1 select ?"; 1752set @a=1.0; 1753execute stmt using @a; 1754show create table t1; 1755Table Create Table 1756t1 CREATE TABLE `t1` ( 1757 `?` decimal(2,1) NOT NULL 1758) ENGINE=MyISAM DEFAULT CHARSET=latin1 1759drop table t1; 1760drop table if exists t1; 1761create table t1 (a bigint unsigned, b bigint(20) unsigned); 1762prepare stmt from "insert into t1 values (?,?)"; 1763set @a= 9999999999999999; 1764set @b= 14632475938453979136; 1765insert into t1 values (@a, @b); 1766select * from t1 where a = @a and b = @b; 1767a b 17689999999999999999 14632475938453979136 1769execute stmt using @a, @b; 1770select * from t1 where a = @a and b = @b; 1771a b 17729999999999999999 14632475938453979136 17739999999999999999 14632475938453979136 1774deallocate prepare stmt; 1775drop table t1; 1776drop view if exists v1; 1777drop table if exists t1; 1778create table t1 (a int, b int); 1779insert into t1 values (1,1), (2,2), (3,3); 1780insert into t1 values (3,1), (1,2), (2,3); 1781prepare stmt from "create view v1 as select * from t1"; 1782execute stmt; 1783drop table t1; 1784create table t1 (a int, b int); 1785drop view v1; 1786execute stmt; 1787show create view v1; 1788View Create View character_set_client collation_connection 1789v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` latin1 latin1_swedish_ci 1790drop view v1; 1791prepare stmt from "create view v1 (c,d) as select a,b from t1"; 1792execute stmt; 1793show create view v1; 1794View Create View character_set_client collation_connection 1795v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `c`,`t1`.`b` AS `d` from `t1` latin1 latin1_swedish_ci 1796select * from v1; 1797c d 1798drop view v1; 1799execute stmt; 1800deallocate prepare stmt; 1801show create view v1; 1802View Create View character_set_client collation_connection 1803v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `c`,`t1`.`b` AS `d` from `t1` latin1 latin1_swedish_ci 1804select * from v1; 1805c d 1806drop view v1; 1807prepare stmt from "create view v1 (c) as select b+1 from t1"; 1808execute stmt; 1809show create view v1; 1810View Create View character_set_client collation_connection 1811v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`b` + 1 AS `c` from `t1` latin1 latin1_swedish_ci 1812select * from v1; 1813c 1814drop view v1; 1815execute stmt; 1816deallocate prepare stmt; 1817show create view v1; 1818View Create View character_set_client collation_connection 1819v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`b` + 1 AS `c` from `t1` latin1 latin1_swedish_ci 1820select * from v1; 1821c 1822drop view v1; 1823prepare stmt from "create view v1 (c,d,e,f) as select a,b,a in (select a+2 from t1), a = all (select a from t1) from t1"; 1824execute stmt; 1825show create view v1; 1826View Create View character_set_client collation_connection 1827v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `c`,`t1`.`b` AS `d`,`t1`.`a` in (select `t1`.`a` + 2 from `t1`) AS `e`,`t1`.`a` = all (select `t1`.`a` from `t1`) AS `f` from `t1` latin1 latin1_swedish_ci 1828select * from v1; 1829c d e f 1830drop view v1; 1831execute stmt; 1832deallocate prepare stmt; 1833show create view v1; 1834View Create View character_set_client collation_connection 1835v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `c`,`t1`.`b` AS `d`,`t1`.`a` in (select `t1`.`a` + 2 from `t1`) AS `e`,`t1`.`a` = all (select `t1`.`a` from `t1`) AS `f` from `t1` latin1 latin1_swedish_ci 1836select * from v1; 1837c d e f 1838drop view v1; 1839prepare stmt from "create or replace view v1 as select 1"; 1840execute stmt; 1841show create view v1; 1842View Create View character_set_client collation_connection 1843v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci 1844select * from v1; 18451 18461 1847execute stmt; 1848show create view v1; 1849View Create View character_set_client collation_connection 1850v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci 1851deallocate prepare stmt; 1852show create view v1; 1853View Create View character_set_client collation_connection 1854v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci 1855select * from v1; 18561 18571 1858drop view v1; 1859prepare stmt from "create view v1 as select 1, 1"; 1860execute stmt; 1861show create view v1; 1862View Create View character_set_client collation_connection 1863v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1`,1 AS `My_exp_1` latin1 latin1_swedish_ci 1864select * from v1; 18651 My_exp_1 18661 1 1867drop view v1; 1868execute stmt; 1869deallocate prepare stmt; 1870show create view v1; 1871View Create View character_set_client collation_connection 1872v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1`,1 AS `My_exp_1` latin1 latin1_swedish_ci 1873select * from v1; 18741 My_exp_1 18751 1 1876drop view v1; 1877prepare stmt from "create view v1 (x) as select a from t1 where a > 1"; 1878execute stmt; 1879show create view v1; 1880View Create View character_set_client collation_connection 1881v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `x` from `t1` where `t1`.`a` > 1 latin1 latin1_swedish_ci 1882select * from v1; 1883x 1884drop view v1; 1885execute stmt; 1886deallocate prepare stmt; 1887show create view v1; 1888View Create View character_set_client collation_connection 1889v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `x` from `t1` where `t1`.`a` > 1 latin1 latin1_swedish_ci 1890select * from v1; 1891x 1892drop view v1; 1893prepare stmt from "create view v1 as select * from `t1` `b`"; 1894execute stmt; 1895show create view v1; 1896View Create View character_set_client collation_connection 1897v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `b`.`a` AS `a`,`b`.`b` AS `b` from `t1` `b` latin1 latin1_swedish_ci 1898select * from v1; 1899a b 1900drop view v1; 1901execute stmt; 1902deallocate prepare stmt; 1903show create view v1; 1904View Create View character_set_client collation_connection 1905v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `b`.`a` AS `a`,`b`.`b` AS `b` from `t1` `b` latin1 latin1_swedish_ci 1906select * from v1; 1907a b 1908drop view v1; 1909prepare stmt from "create view v1 (a,b,c) as select * from t1"; 1910execute stmt; 1911ERROR HY000: View's SELECT and view's field list have different column counts 1912execute stmt; 1913ERROR HY000: View's SELECT and view's field list have different column counts 1914deallocate prepare stmt; 1915drop table t1; 1916create temporary table t1 (a int, b int); 1917prepare stmt from "create view v1 as select * from t1"; 1918execute stmt; 1919ERROR HY000: View's SELECT refers to a temporary table 't1' 1920execute stmt; 1921ERROR HY000: View's SELECT refers to a temporary table 't1' 1922deallocate prepare stmt; 1923drop table t1; 1924prepare stmt from "create view v1 as select * from t1"; 1925ERROR 42S02: Table 'test.t1' doesn't exist 1926prepare stmt from "create view v1 as select * from `t1` `b`"; 1927ERROR 42S02: Table 'test.t1' doesn't exist 1928prepare stmt from "select ?"; 1929set @arg= 123456789.987654321; 1930select @arg; 1931@arg 1932123456789.987654321 1933execute stmt using @arg; 1934? 1935123456789.987654321 1936set @arg= "string"; 1937select @arg; 1938@arg 1939string 1940execute stmt using @arg; 1941? 1942string 1943set @arg= 123456; 1944select @arg; 1945@arg 1946123456 1947execute stmt using @arg; 1948? 1949123456 1950set @arg= cast(-12345.54321 as decimal(20, 10)); 1951select @arg; 1952@arg 1953-12345.5432100000 1954execute stmt using @arg; 1955? 1956-12345.5432100000 1957deallocate prepare stmt; 1958# 1959# Bug#48508: Crash on prepared statement re-execution. 1960# 1961create table t1(b int); 1962insert into t1 values (0); 1963create view v1 AS select 1 as a from t1 where b; 1964prepare stmt from "select * from v1 where a"; 1965execute stmt; 1966a 1967execute stmt; 1968a 1969deallocate prepare stmt; 1970drop table t1; 1971drop view v1; 1972create table t1(a bigint); 1973create table t2(b tinyint); 1974insert into t2 values (null); 1975prepare stmt from "select 1 from t1 join t2 on a xor b where b > 1 and a =1"; 1976execute stmt; 19771 1978execute stmt; 19791 1980deallocate prepare stmt; 1981drop table t1,t2; 1982# 1983# 1984# Bug #49570: Assertion failed: !(order->used & map) 1985# on re-execution of prepared statement 1986# 1987CREATE TABLE t1(a INT PRIMARY KEY); 1988INSERT INTO t1 VALUES(0), (1); 1989PREPARE stmt FROM 1990"SELECT 1 FROM t1 JOIN t1 t2 USING(a) GROUP BY t2.a, t1.a"; 1991EXECUTE stmt; 19921 19931 19941 1995EXECUTE stmt; 19961 19971 19981 1999EXECUTE stmt; 20001 20011 20021 2003DEALLOCATE PREPARE stmt; 2004DROP TABLE t1; 2005End of 5.0 tests. 2006create procedure proc_1() reset query cache; 2007call proc_1(); 2008call proc_1(); 2009call proc_1(); 2010create function func_1() returns int deterministic begin reset query cache; return 1; end| 2011ERROR 0A000: RESET is not allowed in stored function or trigger 2012create function func_1() returns int deterministic begin call proc_1(); return 1; end| 2013select func_1(), func_1(), func_1() from dual; 2014ERROR 0A000: RESET is not allowed in stored function or trigger 2015drop function func_1; 2016drop procedure proc_1; 2017prepare abc from "reset query cache"; 2018execute abc; 2019execute abc; 2020execute abc; 2021deallocate prepare abc; 2022create procedure proc_1() reset master; 2023create function func_1() returns int begin reset master; return 1; end| 2024ERROR 0A000: RESET is not allowed in stored function or trigger 2025create function func_1() returns int begin call proc_1(); return 1; end| 2026select func_1(), func_1(), func_1() from dual; 2027ERROR 0A000: RESET is not allowed in stored function or trigger 2028drop function func_1; 2029drop procedure proc_1; 2030prepare abc from "reset master"; 2031execute abc; 2032execute abc; 2033execute abc; 2034deallocate prepare abc; 2035create procedure proc_1() reset slave; 2036call proc_1(); 2037call proc_1(); 2038call proc_1(); 2039create function func_1() returns int begin reset slave; return 1; end| 2040ERROR 0A000: RESET is not allowed in stored function or trigger 2041create function func_1() returns int begin call proc_1(); return 1; end| 2042select func_1(), func_1(), func_1() from dual; 2043ERROR 0A000: RESET is not allowed in stored function or trigger 2044drop function func_1; 2045drop procedure proc_1; 2046prepare abc from "reset slave"; 2047execute abc; 2048execute abc; 2049execute abc; 2050deallocate prepare abc; 2051create procedure proc_1(a integer) kill a; 2052call proc_1(0); 2053ERROR HY000: Unknown thread id: 0 2054call proc_1(0); 2055ERROR HY000: Unknown thread id: 0 2056call proc_1(0); 2057ERROR HY000: Unknown thread id: 0 2058drop procedure proc_1; 2059create function func_1() returns int begin kill 0; return 1; end| 2060select func_1() from dual; 2061ERROR HY000: Unknown thread id: 0 2062select func_1() from dual; 2063ERROR HY000: Unknown thread id: 0 2064select func_1() from dual; 2065ERROR HY000: Unknown thread id: 0 2066drop function func_1; 2067prepare abc from "kill 0"; 2068execute abc; 2069ERROR HY000: Unknown thread id: 0 2070execute abc; 2071ERROR HY000: Unknown thread id: 0 2072execute abc; 2073ERROR HY000: Unknown thread id: 0 2074deallocate prepare abc; 2075create procedure proc_1() flush hosts; 2076call proc_1(); 2077call proc_1(); 2078call proc_1(); 2079call proc_1(); 2080create function func_1() returns int begin flush hosts; return 1; end| 2081ERROR 0A000: FLUSH is not allowed in stored function or trigger 2082create function func_1() returns int begin call proc_1(); return 1; end| 2083select func_1(), func_1(), func_1() from dual; 2084ERROR 0A000: FLUSH is not allowed in stored function or trigger 2085drop function func_1; 2086drop procedure proc_1; 2087prepare abc from "flush hosts"; 2088execute abc; 2089execute abc; 2090execute abc; 2091deallocate prepare abc; 2092create procedure proc_1() flush privileges; 2093call proc_1(); 2094call proc_1(); 2095call proc_1(); 2096create function func_1() returns int begin flush privileges; return 1; end| 2097ERROR 0A000: FLUSH is not allowed in stored function or trigger 2098create function func_1() returns int begin call proc_1(); return 1; end| 2099select func_1(), func_1(), func_1() from dual; 2100ERROR 0A000: FLUSH is not allowed in stored function or trigger 2101drop function func_1; 2102drop procedure proc_1; 2103prepare abc from "flush privileges"; 2104deallocate prepare abc; 2105create procedure proc_1() flush tables with read lock; 2106call proc_1(); 2107unlock tables; 2108call proc_1(); 2109unlock tables; 2110call proc_1(); 2111unlock tables; 2112create function func_1() returns int begin flush tables with read lock; return 1; end| 2113ERROR 0A000: FLUSH is not allowed in stored function or trigger 2114create function func_1() returns int begin call proc_1(); return 1; end| 2115select func_1(), func_1(), func_1() from dual; 2116ERROR 0A000: FLUSH is not allowed in stored function or trigger 2117drop function func_1; 2118drop procedure proc_1; 2119prepare abc from "flush tables with read lock"; 2120execute abc; 2121execute abc; 2122execute abc; 2123deallocate prepare abc; 2124unlock tables; 2125create procedure proc_1() flush tables; 2126call proc_1(); 2127call proc_1(); 2128call proc_1(); 2129create function func_1() returns int begin flush tables; return 1; end| 2130ERROR 0A000: FLUSH is not allowed in stored function or trigger 2131create function func_1() returns int begin call proc_1(); return 1; end| 2132select func_1(), func_1(), func_1() from dual; 2133ERROR 0A000: FLUSH is not allowed in stored function or trigger 2134drop function func_1; 2135drop procedure proc_1; 2136prepare abc from "flush tables"; 2137execute abc; 2138execute abc; 2139execute abc; 2140deallocate prepare abc; 2141create procedure proc_1() flush tables; 2142flush tables; 2143show open tables from mysql; 2144Database Table In_use Name_locked 2145mysql general_log 0 0 2146select Host, User from mysql.user limit 0; 2147Host User 2148show open tables from mysql; 2149Database Table In_use Name_locked 2150mysql column_stats 0 0 2151mysql general_log 0 0 2152mysql global_priv 0 0 2153mysql index_stats 0 0 2154mysql table_stats 0 0 2155mysql user 0 0 2156call proc_1(); 2157show open tables from mysql; 2158Database Table In_use Name_locked 2159mysql general_log 0 0 2160select Host, User from mysql.user limit 0; 2161Host User 2162show open tables from mysql; 2163Database Table In_use Name_locked 2164mysql column_stats 0 0 2165mysql general_log 0 0 2166mysql global_priv 0 0 2167mysql index_stats 0 0 2168mysql table_stats 0 0 2169mysql user 0 0 2170call proc_1(); 2171show open tables from mysql; 2172Database Table In_use Name_locked 2173mysql general_log 0 0 2174select Host, User from mysql.user limit 0; 2175Host User 2176show open tables from mysql; 2177Database Table In_use Name_locked 2178mysql column_stats 0 0 2179mysql general_log 0 0 2180mysql global_priv 0 0 2181mysql index_stats 0 0 2182mysql table_stats 0 0 2183mysql user 0 0 2184call proc_1(); 2185show open tables from mysql; 2186Database Table In_use Name_locked 2187mysql general_log 0 0 2188select Host, User from mysql.user limit 0; 2189Host User 2190show open tables from mysql; 2191Database Table In_use Name_locked 2192mysql column_stats 0 0 2193mysql general_log 0 0 2194mysql global_priv 0 0 2195mysql index_stats 0 0 2196mysql table_stats 0 0 2197mysql user 0 0 2198flush tables; 2199create function func_1() returns int begin flush tables; return 1; end| 2200ERROR 0A000: FLUSH is not allowed in stored function or trigger 2201create function func_1() returns int begin call proc_1(); return 1; end| 2202select func_1(), func_1(), func_1() from dual; 2203ERROR 0A000: FLUSH is not allowed in stored function or trigger 2204drop function func_1; 2205drop procedure proc_1; 2206flush tables; 2207select Host, User from mysql.user limit 0; 2208Host User 2209show open tables from mysql; 2210Database Table In_use Name_locked 2211mysql column_stats 0 0 2212mysql general_log 0 0 2213mysql global_priv 0 0 2214mysql index_stats 0 0 2215mysql table_stats 0 0 2216mysql user 0 0 2217prepare abc from "flush tables"; 2218execute abc; 2219show open tables from mysql; 2220Database Table In_use Name_locked 2221mysql general_log 0 0 2222select Host, User from mysql.user limit 0; 2223Host User 2224show open tables from mysql; 2225Database Table In_use Name_locked 2226mysql column_stats 0 0 2227mysql general_log 0 0 2228mysql global_priv 0 0 2229mysql index_stats 0 0 2230mysql table_stats 0 0 2231mysql user 0 0 2232execute abc; 2233show open tables from mysql; 2234Database Table In_use Name_locked 2235mysql general_log 0 0 2236select Host, User from mysql.user limit 0; 2237Host User 2238show open tables from mysql; 2239Database Table In_use Name_locked 2240mysql column_stats 0 0 2241mysql general_log 0 0 2242mysql global_priv 0 0 2243mysql index_stats 0 0 2244mysql table_stats 0 0 2245mysql user 0 0 2246execute abc; 2247show open tables from mysql; 2248Database Table In_use Name_locked 2249mysql general_log 0 0 2250select Host, User from mysql.user limit 0; 2251Host User 2252show open tables from mysql; 2253Database Table In_use Name_locked 2254mysql column_stats 0 0 2255mysql general_log 0 0 2256mysql global_priv 0 0 2257mysql index_stats 0 0 2258mysql table_stats 0 0 2259mysql user 0 0 2260flush tables; 2261deallocate prepare abc; 2262create procedure proc_1() flush logs; 2263call proc_1(); 2264call proc_1(); 2265call proc_1(); 2266create function func_1() returns int begin flush logs; return 1; end| 2267ERROR 0A000: FLUSH is not allowed in stored function or trigger 2268create function func_1() returns int begin call proc_1(); return 1; end| 2269select func_1(), func_1(), func_1() from dual; 2270ERROR 0A000: FLUSH is not allowed in stored function or trigger 2271drop function func_1; 2272drop procedure proc_1; 2273prepare abc from "flush logs"; 2274execute abc; 2275execute abc; 2276execute abc; 2277deallocate prepare abc; 2278create procedure proc_1() flush status; 2279call proc_1(); 2280call proc_1(); 2281call proc_1(); 2282create function func_1() returns int begin flush status; return 1; end| 2283ERROR 0A000: FLUSH is not allowed in stored function or trigger 2284create function func_1() returns int begin call proc_1(); return 1; end| 2285select func_1(), func_1(), func_1() from dual; 2286ERROR 0A000: FLUSH is not allowed in stored function or trigger 2287drop function func_1; 2288drop procedure proc_1; 2289prepare abc from "flush status"; 2290execute abc; 2291execute abc; 2292execute abc; 2293deallocate prepare abc; 2294create procedure proc_1() flush slave; 2295call proc_1(); 2296call proc_1(); 2297call proc_1(); 2298create function func_1() returns int begin flush slave; return 1; end| 2299ERROR 0A000: FLUSH is not allowed in stored function or trigger 2300create function func_1() returns int begin call proc_1(); return 1; end| 2301select func_1(), func_1(), func_1() from dual; 2302ERROR 0A000: FLUSH is not allowed in stored function or trigger 2303drop function func_1; 2304drop procedure proc_1; 2305prepare abc from "flush slave"; 2306execute abc; 2307execute abc; 2308execute abc; 2309deallocate prepare abc; 2310create procedure proc_1() flush master; 2311create function func_1() returns int begin flush master; return 1; end| 2312ERROR 0A000: FLUSH is not allowed in stored function or trigger 2313create function func_1() returns int begin call proc_1(); return 1; end| 2314select func_1(), func_1(), func_1() from dual; 2315ERROR 0A000: FLUSH is not allowed in stored function or trigger 2316drop function func_1; 2317drop procedure proc_1; 2318prepare abc from "flush master"; 2319deallocate prepare abc; 2320create procedure proc_1() flush des_key_file; 2321call proc_1(); 2322call proc_1(); 2323call proc_1(); 2324create function func_1() returns int begin flush des_key_file; return 1; end| 2325ERROR 0A000: FLUSH is not allowed in stored function or trigger 2326create function func_1() returns int begin call proc_1(); return 1; end| 2327select func_1(), func_1(), func_1() from dual; 2328ERROR 0A000: FLUSH is not allowed in stored function or trigger 2329drop function func_1; 2330drop procedure proc_1; 2331prepare abc from "flush des_key_file"; 2332execute abc; 2333execute abc; 2334execute abc; 2335deallocate prepare abc; 2336create procedure proc_1() flush user_resources; 2337call proc_1(); 2338call proc_1(); 2339call proc_1(); 2340create function func_1() returns int begin flush user_resources; return 1; end| 2341ERROR 0A000: FLUSH is not allowed in stored function or trigger 2342create function func_1() returns int begin call proc_1(); return 1; end| 2343select func_1(), func_1(), func_1() from dual; 2344ERROR 0A000: FLUSH is not allowed in stored function or trigger 2345drop function func_1; 2346drop procedure proc_1; 2347prepare abc from "flush user_resources"; 2348execute abc; 2349execute abc; 2350execute abc; 2351deallocate prepare abc; 2352create procedure proc_1() start slave; 2353drop procedure proc_1; 2354create function func_1() returns int begin start slave; return 1; end| 2355drop function func_1; 2356prepare abc from "start slave"; 2357deallocate prepare abc; 2358create procedure proc_1() stop slave; 2359drop procedure proc_1; 2360create function func_1() returns int begin stop slave; return 1; end| 2361drop function func_1; 2362prepare abc from "stop slave"; 2363deallocate prepare abc; 2364create procedure proc_1() show binlog events; 2365drop procedure proc_1; 2366create function func_1() returns int begin show binlog events; return 1; end| 2367ERROR 0A000: Not allowed to return a result set from a function 2368select func_1(), func_1(), func_1() from dual; 2369ERROR 42000: FUNCTION test.func_1 does not exist 2370drop function func_1; 2371ERROR 42000: FUNCTION test.func_1 does not exist 2372prepare abc from "show binlog events"; 2373deallocate prepare abc; 2374create procedure proc_1() show slave status; 2375drop procedure proc_1; 2376create function func_1() returns int begin show slave status; return 1; end| 2377ERROR 0A000: Not allowed to return a result set from a function 2378select func_1(), func_1(), func_1() from dual; 2379ERROR 42000: FUNCTION test.func_1 does not exist 2380drop function func_1; 2381ERROR 42000: FUNCTION test.func_1 does not exist 2382prepare abc from "show slave status"; 2383deallocate prepare abc; 2384create procedure proc_1() show master status; 2385drop procedure proc_1; 2386create function func_1() returns int begin show master status; return 1; end| 2387ERROR 0A000: Not allowed to return a result set from a function 2388select func_1(), func_1(), func_1() from dual; 2389ERROR 42000: FUNCTION test.func_1 does not exist 2390drop function func_1; 2391ERROR 42000: FUNCTION test.func_1 does not exist 2392prepare abc from "show master status"; 2393deallocate prepare abc; 2394create procedure proc_1() show master logs; 2395drop procedure proc_1; 2396create function func_1() returns int begin show master logs; return 1; end| 2397ERROR 0A000: Not allowed to return a result set from a function 2398select func_1(), func_1(), func_1() from dual; 2399ERROR 42000: FUNCTION test.func_1 does not exist 2400drop function func_1; 2401ERROR 42000: FUNCTION test.func_1 does not exist 2402prepare abc from "show master logs"; 2403deallocate prepare abc; 2404create procedure proc_1() show events; 2405call proc_1(); 2406Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 2407call proc_1(); 2408Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 2409call proc_1(); 2410Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 2411drop procedure proc_1; 2412create function func_1() returns int begin show events; return 1; end| 2413ERROR 0A000: Not allowed to return a result set from a function 2414select func_1(), func_1(), func_1() from dual; 2415ERROR 42000: FUNCTION test.func_1 does not exist 2416drop function func_1; 2417ERROR 42000: FUNCTION test.func_1 does not exist 2418prepare abc from "show events"; 2419execute abc; 2420Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 2421execute abc; 2422Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 2423execute abc; 2424Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 2425deallocate prepare abc; 2426drop procedure if exists a; 2427create procedure a() select 42; 2428create procedure proc_1(a char(2)) show create procedure a; 2429call proc_1("bb"); 2430Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 2431a STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `a`() 2432select 42 latin1 latin1_swedish_ci latin1_swedish_ci 2433call proc_1("bb"); 2434Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 2435a STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `a`() 2436select 42 latin1 latin1_swedish_ci latin1_swedish_ci 2437call proc_1("bb"); 2438Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 2439a STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `a`() 2440select 42 latin1 latin1_swedish_ci latin1_swedish_ci 2441drop procedure proc_1; 2442create function func_1() returns int begin show create procedure a; return 1; end| 2443ERROR 0A000: Not allowed to return a result set from a function 2444select func_1(), func_1(), func_1() from dual; 2445ERROR 42000: FUNCTION test.func_1 does not exist 2446drop function func_1; 2447ERROR 42000: FUNCTION test.func_1 does not exist 2448prepare abc from "show create procedure a"; 2449execute abc; 2450Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 2451a STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `a`() 2452select 42 latin1 latin1_swedish_ci latin1_swedish_ci 2453execute abc; 2454Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 2455a STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `a`() 2456select 42 latin1 latin1_swedish_ci latin1_swedish_ci 2457execute abc; 2458Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 2459a STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `a`() 2460select 42 latin1 latin1_swedish_ci latin1_swedish_ci 2461deallocate prepare abc; 2462drop procedure a; 2463drop function if exists a; 2464create function a() returns int return 42+13; 2465create procedure proc_1(a char(2)) show create function a; 2466call proc_1("bb"); 2467Function sql_mode Create Function character_set_client collation_connection Database Collation 2468a STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11) 2469return 42+13 latin1 latin1_swedish_ci latin1_swedish_ci 2470call proc_1("bb"); 2471Function sql_mode Create Function character_set_client collation_connection Database Collation 2472a STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11) 2473return 42+13 latin1 latin1_swedish_ci latin1_swedish_ci 2474call proc_1("bb"); 2475Function sql_mode Create Function character_set_client collation_connection Database Collation 2476a STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11) 2477return 42+13 latin1 latin1_swedish_ci latin1_swedish_ci 2478drop procedure proc_1; 2479create function func_1() returns int begin show create function a; return 1; end| 2480ERROR 0A000: Not allowed to return a result set from a function 2481select func_1(), func_1(), func_1() from dual; 2482ERROR 42000: FUNCTION test.func_1 does not exist 2483drop function func_1; 2484ERROR 42000: FUNCTION test.func_1 does not exist 2485prepare abc from "show create function a"; 2486execute abc; 2487Function sql_mode Create Function character_set_client collation_connection Database Collation 2488a STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11) 2489return 42+13 latin1 latin1_swedish_ci latin1_swedish_ci 2490execute abc; 2491Function sql_mode Create Function character_set_client collation_connection Database Collation 2492a STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11) 2493return 42+13 latin1 latin1_swedish_ci latin1_swedish_ci 2494execute abc; 2495Function sql_mode Create Function character_set_client collation_connection Database Collation 2496a STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11) 2497return 42+13 latin1 latin1_swedish_ci latin1_swedish_ci 2498deallocate prepare abc; 2499drop function a; 2500drop table if exists tab1; 2501create table tab1(a int, b char(1), primary key(a,b)); 2502create procedure proc_1() show create table tab1; 2503call proc_1(); 2504Table Create Table 2505tab1 CREATE TABLE `tab1` ( 2506 `a` int(11) NOT NULL, 2507 `b` char(1) NOT NULL, 2508 PRIMARY KEY (`a`,`b`) 2509) ENGINE=MyISAM DEFAULT CHARSET=latin1 2510call proc_1(); 2511Table Create Table 2512tab1 CREATE TABLE `tab1` ( 2513 `a` int(11) NOT NULL, 2514 `b` char(1) NOT NULL, 2515 PRIMARY KEY (`a`,`b`) 2516) ENGINE=MyISAM DEFAULT CHARSET=latin1 2517call proc_1(); 2518Table Create Table 2519tab1 CREATE TABLE `tab1` ( 2520 `a` int(11) NOT NULL, 2521 `b` char(1) NOT NULL, 2522 PRIMARY KEY (`a`,`b`) 2523) ENGINE=MyISAM DEFAULT CHARSET=latin1 2524drop procedure proc_1; 2525create function func_1() returns int begin show create table tab1; return 1; end| 2526ERROR 0A000: Not allowed to return a result set from a function 2527select func_1(), func_1(), func_1() from dual; 2528ERROR 42000: FUNCTION test.func_1 does not exist 2529drop function func_1; 2530ERROR 42000: FUNCTION test.func_1 does not exist 2531prepare abc from "show create table tab1"; 2532execute abc; 2533Table Create Table 2534tab1 CREATE TABLE `tab1` ( 2535 `a` int(11) NOT NULL, 2536 `b` char(1) NOT NULL, 2537 PRIMARY KEY (`a`,`b`) 2538) ENGINE=MyISAM DEFAULT CHARSET=latin1 2539execute abc; 2540Table Create Table 2541tab1 CREATE TABLE `tab1` ( 2542 `a` int(11) NOT NULL, 2543 `b` char(1) NOT NULL, 2544 PRIMARY KEY (`a`,`b`) 2545) ENGINE=MyISAM DEFAULT CHARSET=latin1 2546execute abc; 2547Table Create Table 2548tab1 CREATE TABLE `tab1` ( 2549 `a` int(11) NOT NULL, 2550 `b` char(1) NOT NULL, 2551 PRIMARY KEY (`a`,`b`) 2552) ENGINE=MyISAM DEFAULT CHARSET=latin1 2553deallocate prepare abc; 2554drop table tab1; 2555drop view if exists v1; 2556drop table if exists t1; 2557create table t1(a int, b char(5)); 2558insert into t1 values (1, "one"), (1, "edno"), (2, "two"), (2, "dve"); 2559create view v1 as 2560(select a, count(*) from t1 group by a) 2561union all 2562(select b, count(*) from t1 group by b); 2563create procedure proc_1() show create view v1; 2564call proc_1(); 2565View Create View character_set_client collation_connection 2566v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a`,count(0) AS `count(*)` from `t1` group by `t1`.`a`) union all (select `t1`.`b` AS `b`,count(0) AS `count(*)` from `t1` group by `t1`.`b`) latin1 latin1_swedish_ci 2567call proc_1(); 2568View Create View character_set_client collation_connection 2569v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a`,count(0) AS `count(*)` from `t1` group by `t1`.`a`) union all (select `t1`.`b` AS `b`,count(0) AS `count(*)` from `t1` group by `t1`.`b`) latin1 latin1_swedish_ci 2570call proc_1(); 2571View Create View character_set_client collation_connection 2572v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a`,count(0) AS `count(*)` from `t1` group by `t1`.`a`) union all (select `t1`.`b` AS `b`,count(0) AS `count(*)` from `t1` group by `t1`.`b`) latin1 latin1_swedish_ci 2573drop procedure proc_1; 2574create function func_1() returns int begin show create view v1; return 1; end| 2575ERROR 0A000: Not allowed to return a result set from a function 2576select func_1(), func_1(), func_1() from dual; 2577ERROR 42000: FUNCTION test.func_1 does not exist 2578drop function func_1; 2579ERROR 42000: FUNCTION test.func_1 does not exist 2580prepare abc from "show create view v1"; 2581execute abc; 2582View Create View character_set_client collation_connection 2583v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a`,count(0) AS `count(*)` from `t1` group by `t1`.`a`) union all (select `t1`.`b` AS `b`,count(0) AS `count(*)` from `t1` group by `t1`.`b`) latin1 latin1_swedish_ci 2584execute abc; 2585View Create View character_set_client collation_connection 2586v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a`,count(0) AS `count(*)` from `t1` group by `t1`.`a`) union all (select `t1`.`b` AS `b`,count(0) AS `count(*)` from `t1` group by `t1`.`b`) latin1 latin1_swedish_ci 2587execute abc; 2588View Create View character_set_client collation_connection 2589v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a`,count(0) AS `count(*)` from `t1` group by `t1`.`a`) union all (select `t1`.`b` AS `b`,count(0) AS `count(*)` from `t1` group by `t1`.`b`) latin1 latin1_swedish_ci 2590deallocate prepare abc; 2591drop view v1; 2592drop table t1; 2593create procedure proc_1() install plugin my_plug soname 'some_plugin.so'; 2594call proc_1(); 2595Got one of the listed errors 2596call proc_1(); 2597Got one of the listed errors 2598call proc_1(); 2599Got one of the listed errors 2600drop procedure proc_1; 2601create function func_1() returns int begin install plugin my_plug soname '/tmp/plugin'; return 1; end| 2602ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger 2603select func_1(), func_1(), func_1() from dual; 2604ERROR 42000: FUNCTION test.func_1 does not exist 2605drop function func_1; 2606ERROR 42000: FUNCTION test.func_1 does not exist 2607prepare abc from "install plugin my_plug soname 'some_plugin.so'"; 2608deallocate prepare abc; 2609create procedure proc_1() uninstall plugin my_plug; 2610call proc_1(); 2611ERROR 42000: PLUGIN my_plug does not exist 2612call proc_1(); 2613ERROR 42000: PLUGIN my_plug does not exist 2614call proc_1(); 2615ERROR 42000: PLUGIN my_plug does not exist 2616drop procedure proc_1; 2617create function func_1() returns int begin uninstall plugin my_plug; return 1; end| 2618ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger 2619select func_1(), func_1(), func_1() from dual; 2620ERROR 42000: FUNCTION test.func_1 does not exist 2621drop function func_1; 2622ERROR 42000: FUNCTION test.func_1 does not exist 2623prepare abc from "uninstall plugin my_plug"; 2624execute abc; 2625ERROR 42000: PLUGIN my_plug does not exist 2626execute abc; 2627ERROR 42000: PLUGIN my_plug does not exist 2628execute abc; 2629ERROR 42000: PLUGIN my_plug does not exist 2630deallocate prepare abc; 2631drop database if exists mysqltest_xyz; 2632create procedure proc_1() create database mysqltest_xyz; 2633call proc_1(); 2634drop database if exists mysqltest_xyz; 2635call proc_1(); 2636call proc_1(); 2637ERROR HY000: Can't create database 'mysqltest_xyz'; database exists 2638drop database if exists mysqltest_xyz; 2639call proc_1(); 2640drop database if exists mysqltest_xyz; 2641drop procedure proc_1; 2642create function func_1() returns int begin create database mysqltest_xyz; return 1; end| 2643ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger 2644select func_1(), func_1(), func_1() from dual; 2645ERROR 42000: FUNCTION test.func_1 does not exist 2646drop function func_1; 2647ERROR 42000: FUNCTION test.func_1 does not exist 2648prepare abc from "create database mysqltest_xyz"; 2649execute abc; 2650drop database if exists mysqltest_xyz; 2651execute abc; 2652execute abc; 2653ERROR HY000: Can't create database 'mysqltest_xyz'; database exists 2654drop database if exists mysqltest_xyz; 2655execute abc; 2656drop database if exists mysqltest_xyz; 2657deallocate prepare abc; 2658drop table if exists t1; 2659create table t1 (a int, b char(5)); 2660insert into t1 values (1, "one"), (2, "two"), (3, "three"); 2661create procedure proc_1() checksum table xyz; 2662call proc_1(); 2663Table Checksum 2664test.xyz NULL 2665Warnings: 2666Error 1146 Table 'test.xyz' doesn't exist 2667call proc_1(); 2668Table Checksum 2669test.xyz NULL 2670Warnings: 2671Error 1146 Table 'test.xyz' doesn't exist 2672call proc_1(); 2673Table Checksum 2674test.xyz NULL 2675Warnings: 2676Error 1146 Table 'test.xyz' doesn't exist 2677drop procedure proc_1; 2678create function func_1() returns int begin checksum table t1; return 1; end| 2679ERROR 0A000: Not allowed to return a result set from a function 2680select func_1(), func_1(), func_1() from dual; 2681ERROR 42000: FUNCTION test.func_1 does not exist 2682drop function func_1; 2683ERROR 42000: FUNCTION test.func_1 does not exist 2684prepare abc from "checksum table t1"; 2685execute abc; 2686Table Checksum 2687test.t1 645809265 2688execute abc; 2689Table Checksum 2690test.t1 645809265 2691execute abc; 2692Table Checksum 2693test.t1 645809265 2694deallocate prepare abc; 2695create procedure proc_1() create user pstest_xyz@localhost; 2696call proc_1(); 2697drop user pstest_xyz@localhost; 2698call proc_1(); 2699call proc_1(); 2700ERROR HY000: Operation CREATE USER failed for 'pstest_xyz'@'localhost' 2701drop user pstest_xyz@localhost; 2702call proc_1(); 2703drop user pstest_xyz@localhost; 2704drop procedure proc_1; 2705create function func_1() returns int begin create user pstest_xyz@localhost; return 1; end| 2706ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger 2707select func_1(), func_1(), func_1() from dual; 2708ERROR 42000: FUNCTION test.func_1 does not exist 2709drop function func_1; 2710ERROR 42000: FUNCTION test.func_1 does not exist 2711prepare abc from "create user pstest_xyz@localhost"; 2712execute abc; 2713drop user pstest_xyz@localhost; 2714execute abc; 2715execute abc; 2716ERROR HY000: Operation CREATE USER failed for 'pstest_xyz'@'localhost' 2717drop user pstest_xyz@localhost; 2718execute abc; 2719drop user pstest_xyz@localhost; 2720deallocate prepare abc; 2721drop event if exists xyz; 2722create function func_1() returns int begin create event xyz on schedule at now() do select 123; return 1; end| 2723ERROR HY000: Recursion of EVENT DDL statements is forbidden when body is present 2724select func_1(), func_1(), func_1() from dual; 2725ERROR 42000: FUNCTION test.func_1 does not exist 2726drop function func_1; 2727ERROR 42000: FUNCTION test.func_1 does not exist 2728prepare abc from "create event xyz on schedule at now() do select 123"; 2729ERROR HY000: This command is not supported in the prepared statement protocol yet 2730deallocate prepare abc; 2731ERROR HY000: Unknown prepared statement handler (abc) given to DEALLOCATE PREPARE 2732drop event if exists xyz; 2733create event xyz on schedule every 5 minute disable do select 123; 2734create procedure proc_1() alter event xyz comment 'xyz'; 2735call proc_1(); 2736drop event xyz; 2737create event xyz on schedule every 5 minute disable do select 123; 2738Warnings: 2739Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. 2740call proc_1(); 2741drop event xyz; 2742create event xyz on schedule every 5 minute disable do select 123; 2743Warnings: 2744Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. 2745call proc_1(); 2746drop event xyz; 2747drop procedure proc_1; 2748create function func_1() returns int begin alter event xyz comment 'xyz'; return 1; end| 2749ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger 2750prepare abc from "alter event xyz comment 'xyz'"; 2751ERROR HY000: This command is not supported in the prepared statement protocol yet 2752deallocate prepare abc; 2753ERROR HY000: Unknown prepared statement handler (abc) given to DEALLOCATE PREPARE 2754drop event if exists xyz; 2755create event xyz on schedule every 5 minute disable do select 123; 2756create procedure proc_1() drop event xyz; 2757call proc_1(); 2758create event xyz on schedule every 5 minute disable do select 123; 2759Warnings: 2760Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. 2761call proc_1(); 2762call proc_1(); 2763ERROR HY000: Unknown event 'xyz' 2764drop procedure proc_1; 2765create function func_1() returns int begin drop event xyz; return 1; end| 2766ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger 2767prepare abc from "drop event xyz"; 2768ERROR HY000: This command is not supported in the prepared statement protocol yet 2769deallocate prepare abc; 2770ERROR HY000: Unknown prepared statement handler (abc) given to DEALLOCATE PREPARE 2771drop table if exists t1; 2772create table t1 (a int, b char(5)) engine=myisam; 2773insert into t1 values (1, "one"), (2, "two"), (3, "three"); 2774SET GLOBAL new_cache.key_buffer_size=128*1024; 2775create procedure proc_1() cache index t1 in new_cache; 2776call proc_1(); 2777Table Op Msg_type Msg_text 2778test.t1 assign_to_keycache status OK 2779call proc_1(); 2780Table Op Msg_type Msg_text 2781test.t1 assign_to_keycache status OK 2782call proc_1(); 2783Table Op Msg_type Msg_text 2784test.t1 assign_to_keycache status OK 2785drop procedure proc_1; 2786SET GLOBAL second_cache.key_buffer_size=128*1024; 2787prepare abc from "cache index t1 in second_cache"; 2788execute abc; 2789Table Op Msg_type Msg_text 2790test.t1 assign_to_keycache status OK 2791execute abc; 2792Table Op Msg_type Msg_text 2793test.t1 assign_to_keycache status OK 2794execute abc; 2795Table Op Msg_type Msg_text 2796test.t1 assign_to_keycache status OK 2797deallocate prepare abc; 2798drop table t1; 2799drop table if exists t1; 2800drop table if exists t2; 2801create table t1 (a int, b char(5)) engine=myisam; 2802insert into t1 values (1, "one"), (2, "two"), (3, "three"); 2803create table t2 (a int, b char(5)) engine=myisam; 2804insert into t2 values (1, "one"), (2, "two"), (3, "three"); 2805create procedure proc_1() load index into cache t1 ignore leaves; 2806call proc_1(); 2807Table Op Msg_type Msg_text 2808test.t1 preload_keys status OK 2809call proc_1(); 2810Table Op Msg_type Msg_text 2811test.t1 preload_keys status OK 2812call proc_1(); 2813Table Op Msg_type Msg_text 2814test.t1 preload_keys status OK 2815drop procedure proc_1; 2816create function func_1() returns int begin load index into cache t1 ignore leaves; return 1; end| 2817ERROR 0A000: Not allowed to return a result set from a function 2818prepare abc from "load index into cache t2 ignore leaves"; 2819execute abc; 2820Table Op Msg_type Msg_text 2821test.t2 preload_keys status OK 2822execute abc; 2823Table Op Msg_type Msg_text 2824test.t2 preload_keys status OK 2825execute abc; 2826Table Op Msg_type Msg_text 2827test.t2 preload_keys status OK 2828deallocate prepare abc; 2829drop table t1, t2; 2830create procedure proc_1() show errors; 2831call proc_1(); 2832Level Code Message 2833call proc_1(); 2834Level Code Message 2835call proc_1(); 2836Level Code Message 2837drop procedure proc_1; 2838create function func_1() returns int begin show errors; return 1; end| 2839ERROR 0A000: Not allowed to return a result set from a function 2840prepare abc from "show errors"; 2841deallocate prepare abc; 2842drop table if exists t1; 2843drop table if exists t2; 2844create procedure proc_1() show warnings; 2845drop table if exists t1; 2846Warnings: 2847Note 1051 Unknown table 'test.t1' 2848call proc_1(); 2849Level Code Message 2850Note 1051 Unknown table 'test.t1' 2851drop table if exists t2; 2852Warnings: 2853Note 1051 Unknown table 'test.t2' 2854call proc_1(); 2855Level Code Message 2856Note 1051 Unknown table 'test.t2' 2857drop table if exists t1, t2; 2858Warnings: 2859Note 1051 Unknown table 'test.t1,test.t2' 2860call proc_1(); 2861Level Code Message 2862Note 1051 Unknown table 'test.t1,test.t2' 2863drop procedure proc_1; 2864create function func_1() returns int begin show warnings; return 1; end| 2865ERROR 0A000: Not allowed to return a result set from a function 2866prepare abc from "show warnings"; 2867drop table if exists t1; 2868Warnings: 2869Note 1051 Unknown table 'test.t1' 2870execute abc; 2871Level Code Message 2872Note 1051 Unknown table 'test.t1' 2873drop table if exists t2; 2874Warnings: 2875Note 1051 Unknown table 'test.t2' 2876execute abc; 2877Level Code Message 2878Note 1051 Unknown table 'test.t2' 2879drop table if exists t1, t2; 2880Warnings: 2881Note 1051 Unknown table 'test.t1,test.t2' 2882execute abc; 2883Level Code Message 2884Note 1051 Unknown table 'test.t1,test.t2' 2885deallocate prepare abc; 2886set @my_password="password"; 2887set @my_data="clear text to encode"; 2888prepare stmt1 from 'select decode(encode(?, ?), ?)'; 2889execute stmt1 using @my_data, @my_password, @my_password; 2890decode(encode(?, ?), ?) 2891clear text to encode 2892set @my_data="more text to encode"; 2893execute stmt1 using @my_data, @my_password, @my_password; 2894decode(encode(?, ?), ?) 2895more text to encode 2896set @my_password="new password"; 2897execute stmt1 using @my_data, @my_password, @my_password; 2898decode(encode(?, ?), ?) 2899more text to encode 2900deallocate prepare stmt1; 2901set @to_format="123456789.123456789"; 2902set @dec=0; 2903prepare stmt2 from 'select format(?, ?)'; 2904execute stmt2 using @to_format, @dec; 2905format(?, ?) 2906123,456,789 2907set @dec=4; 2908execute stmt2 using @to_format, @dec; 2909format(?, ?) 2910123,456,789.1235 2911set @dec=6; 2912execute stmt2 using @to_format, @dec; 2913format(?, ?) 2914123,456,789.123457 2915set @dec=2; 2916execute stmt2 using @to_format, @dec; 2917format(?, ?) 2918123,456,789.12 2919set @to_format="100"; 2920execute stmt2 using @to_format, @dec; 2921format(?, ?) 2922100.00 2923set @to_format="1000000"; 2924execute stmt2 using @to_format, @dec; 2925format(?, ?) 29261,000,000.00 2927set @to_format="10000"; 2928execute stmt2 using @to_format, @dec; 2929format(?, ?) 293010,000.00 2931deallocate prepare stmt2; 2932DROP TABLE IF EXISTS t1, t2; 2933CREATE TABLE t1 (i INT); 2934INSERT INTO t1 VALUES (1); 2935CREATE TABLE t2 (i INT); 2936INSERT INTO t2 VALUES (2); 2937LOCK TABLE t1 READ, t2 WRITE; 2938connect conn1, localhost, root, , ; 2939PREPARE stmt1 FROM "SELECT i FROM t1"; 2940PREPARE stmt2 FROM "INSERT INTO t2 (i) VALUES (3)"; 2941EXECUTE stmt1; 2942i 29431 2944EXECUTE stmt2; 2945connection default; 2946SELECT * FROM t2; 2947i 29482 2949UNLOCK TABLES; 2950SELECT * FROM t2; 2951i 29522 29533 2954ALTER TABLE t1 ADD COLUMN j INT; 2955ALTER TABLE t2 ADD COLUMN j INT; 2956INSERT INTO t1 VALUES (4, 5); 2957INSERT INTO t2 VALUES (4, 5); 2958connection conn1; 2959EXECUTE stmt1; 2960i 29611 29624 2963EXECUTE stmt2; 2964SELECT * FROM t2; 2965i j 29662 NULL 29673 NULL 29684 5 29693 NULL 2970disconnect conn1; 2971connection default; 2972DROP TABLE t1, t2; 2973drop table if exists t1; 2974Warnings: 2975Note 1051 Unknown table 'test.t1' 2976prepare stmt 2977from "create table t1 (c char(100) character set utf8, key (c(10)))"; 2978execute stmt; 2979show create table t1; 2980Table Create Table 2981t1 CREATE TABLE `t1` ( 2982 `c` char(100) CHARACTER SET utf8 DEFAULT NULL, 2983 KEY `c` (`c`(10)) 2984) ENGINE=MyISAM DEFAULT CHARSET=latin1 2985drop table t1; 2986execute stmt; 2987show create table t1; 2988Table Create Table 2989t1 CREATE TABLE `t1` ( 2990 `c` char(100) CHARACTER SET utf8 DEFAULT NULL, 2991 KEY `c` (`c`(10)) 2992) ENGINE=MyISAM DEFAULT CHARSET=latin1 2993drop table t1; 2994drop table if exists t1, t2; 2995create table t1 (a int, b int); 2996create table t2 like t1; 2997insert into t1 (a, b) values (1,1), (1,2), (1,3), (1,4), (1,5), 2998(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); 2999insert into t2 select a, max(b) from t1 group by a; 3000prepare stmt from "delete from t2 where (select (select max(b) from t1 group 3001by a having a < 2) x from t1) > 10000"; 3002delete from t2 where (select (select max(b) from t1 group 3003by a having a < 2) x from t1) > 10000; 3004ERROR 21000: Subquery returns more than 1 row 3005execute stmt; 3006ERROR 21000: Subquery returns more than 1 row 3007execute stmt; 3008ERROR 21000: Subquery returns more than 1 row 3009deallocate prepare stmt; 3010drop table t1, t2; 3011# 3012# Bug#27430 Crash in subquery code when in PS and table DDL changed 3013# after PREPARE 3014# 3015# This part of the test doesn't work in embedded server, this is 3016# why it's here. For the main test see ps_ddl*.test 3017 3018drop table if exists t1; 3019create table t1 (a int); 3020prepare stmt from "show events where (1) in (select * from t1)"; 3021execute stmt; 3022Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 3023drop table t1; 3024create table t1 (x int); 3025execute stmt; 3026Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 3027drop table t1; 3028deallocate prepare stmt; 3029# 3030# Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0 3031# 3032prepare encode from "select encode(?, ?) into @ciphertext"; 3033prepare decode from "select decode(?, ?) into @plaintext"; 3034set @str="abc", @key="cba"; 3035execute encode using @str, @key; 3036execute decode using @ciphertext, @key; 3037select @plaintext; 3038@plaintext 3039abc 3040set @str="bcd", @key="dcb"; 3041execute encode using @str, @key; 3042execute decode using @ciphertext, @key; 3043select @plaintext; 3044@plaintext 3045bcd 3046deallocate prepare encode; 3047deallocate prepare decode; 3048# 3049# Bug#52124 memory leaks like a sieve in datetime, timestamp, time, date fields + warnings 3050# 3051CREATE TABLE t1 (a DATETIME NOT NULL, b TINYINT); 3052INSERT INTO t1 VALUES (0, 0),(0, 0); 3053PREPARE stmt FROM "SELECT 1 FROM t1 WHERE 3054ROW(a, b) >= ROW('1', (SELECT 1 FROM t1 WHERE a > 1234))"; 3055EXECUTE stmt; 30561 3057EXECUTE stmt; 30581 3059DEALLOCATE PREPARE stmt; 3060DROP TABLE t1; 3061# 3062# Bug#54494 crash with explain extended and prepared statements 3063# 3064CREATE TABLE t1(a INT); 3065INSERT INTO t1 VALUES (1),(2); 3066SET @save_optimizer_switch=@@optimizer_switch; 3067SET optimizer_switch='outer_join_with_cache=off'; 3068PREPARE stmt FROM 'EXPLAIN EXTENDED SELECT 1 FROM t1 RIGHT JOIN t1 t2 ON 1'; 3069EXECUTE stmt; 3070id select_type table type possible_keys key key_len ref rows filtered Extra 30711 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 30721 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3073Warnings: 3074Note 1003 select 1 AS `1` from `test`.`t1` `t2` left join `test`.`t1` on(1) where 1 3075EXECUTE stmt; 3076id select_type table type possible_keys key key_len ref rows filtered Extra 30771 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 30781 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3079Warnings: 3080Note 1003 select 1 AS `1` from `test`.`t1` `t2` left join `test`.`t1` on(1) where 1 3081DEALLOCATE PREPARE stmt; 3082SET optimizer_switch=@save_optimizer_switch; 3083DROP TABLE t1; 3084# 3085# Bug#54488 crash when using explain and prepared statements with subqueries 3086# 3087CREATE TABLE t1(f1 INT); 3088INSERT INTO t1 VALUES (1),(1); 3089PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 WHERE (SELECT (SELECT 1 FROM t1 GROUP BY f1))'; 3090EXECUTE stmt; 3091id select_type table type possible_keys key key_len ref rows Extra 30921 PRIMARY t1 ALL NULL NULL NULL NULL 2 30932 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 30943 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 3095EXECUTE stmt; 3096id select_type table type possible_keys key key_len ref rows Extra 30971 PRIMARY t1 ALL NULL NULL NULL NULL 2 30982 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 30993 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 3100DEALLOCATE PREPARE stmt; 3101DROP TABLE t1; 3102 3103End of 5.1 tests. 3104# 3105# lp:1001500 Crash on the second execution of the PS for 3106# a query with degenerated conjunctive condition 3107# (see also mysql bug#12582849) 3108# 3109CREATE TABLE t1 ( 3110pk INTEGER AUTO_INCREMENT, 3111col_int_nokey INTEGER, 3112col_int_key INTEGER, 3113col_varchar_key VARCHAR(1), 3114col_varchar_nokey VARCHAR(1), 3115PRIMARY KEY (pk), 3116KEY (col_int_key), 3117KEY (col_varchar_key, col_int_key) 3118); 3119INSERT INTO t1 ( 3120col_int_key, col_int_nokey, 3121col_varchar_key, col_varchar_nokey 3122) VALUES 3123(4, 2, 'v', 'v'), 3124(62, 150, 'v', 'v'); 3125CREATE TABLE t2 ( 3126pk INTEGER AUTO_INCREMENT, 3127col_int_nokey INTEGER, 3128col_int_key INTEGER, 3129col_varchar_key VARCHAR(1), 3130col_varchar_nokey VARCHAR(1), 3131PRIMARY KEY (pk), 3132KEY (col_int_key), 3133KEY (col_varchar_key, col_int_key) 3134); 3135INSERT INTO t2 ( 3136col_int_key, col_int_nokey, 3137col_varchar_key, col_varchar_nokey 3138) VALUES 3139(8, NULL, 'x', 'x'), 3140(7, 8, 'd', 'd'); 3141PREPARE stmt FROM ' 3142SELECT 3143 ( SELECT MAX( SQ1_alias2 .col_int_nokey ) AS SQ1_field1 3144 FROM ( t2 AS SQ1_alias1 RIGHT JOIN t1 AS SQ1_alias2 3145 ON ( SQ1_alias2.col_varchar_key = SQ1_alias1.col_varchar_nokey ) 3146 ) 3147 WHERE SQ1_alias2.pk < alias1.col_int_nokey OR alias1.pk 3148 ) AS field1 3149FROM ( t1 AS alias1 JOIN t2 AS alias2 ON alias2.pk ) 3150GROUP BY field1 3151'; 3152EXECUTE stmt; 3153field1 3154150 3155EXECUTE stmt; 3156field1 3157150 3158DEALLOCATE PREPARE stmt; 3159DROP TABLE t1, t2; 3160 3161# 3162# WL#4435: Support OUT-parameters in prepared statements. 3163# 3164 3165DROP PROCEDURE IF EXISTS p_string; 3166DROP PROCEDURE IF EXISTS p_double; 3167DROP PROCEDURE IF EXISTS p_int; 3168DROP PROCEDURE IF EXISTS p_decimal; 3169 3170CREATE PROCEDURE p_string( 3171IN v0 INT, 3172OUT v1 CHAR(32), 3173IN v2 CHAR(32), 3174INOUT v3 CHAR(32)) 3175BEGIN 3176SET v0 = -1; 3177SET v1 = 'test_v1'; 3178SET v2 = 'n/a'; 3179SET v3 = 'test_v3'; 3180END| 3181 3182CREATE PROCEDURE p_double( 3183IN v0 INT, 3184OUT v1 DOUBLE(4, 2), 3185IN v2 DOUBLE(4, 2), 3186INOUT v3 DOUBLE(4, 2)) 3187BEGIN 3188SET v0 = -1; 3189SET v1 = 12.34; 3190SET v2 = 98.67; 3191SET v3 = 56.78; 3192END| 3193 3194CREATE PROCEDURE p_int( 3195IN v0 CHAR(10), 3196OUT v1 INT, 3197IN v2 INT, 3198INOUT v3 INT) 3199BEGIN 3200SET v0 = 'n/a'; 3201SET v1 = 1234; 3202SET v2 = 9876; 3203SET v3 = 5678; 3204END| 3205 3206CREATE PROCEDURE p_decimal( 3207IN v0 INT, 3208OUT v1 DECIMAL(4, 2), 3209IN v2 DECIMAL(4, 2), 3210INOUT v3 DECIMAL(4, 2)) 3211BEGIN 3212SET v0 = -1; 3213SET v1 = 12.34; 3214SET v2 = 98.67; 3215SET v3 = 56.78; 3216END| 3217 3218PREPARE stmt_str FROM 'CALL p_string(?, ?, ?, ?)'; 3219PREPARE stmt_dbl FROM 'CALL p_double(?, ?, ?, ?)'; 3220PREPARE stmt_int FROM 'CALL p_int(?, ?, ?, ?)'; 3221PREPARE stmt_dec FROM 'CALL p_decimal(?, ?, ?, ?)'; 3222 3223SET @x_str_1 = NULL; 3224SET @x_str_2 = NULL; 3225SET @x_str_3 = NULL; 3226SET @x_dbl_1 = NULL; 3227SET @x_dbl_2 = NULL; 3228SET @x_dbl_3 = NULL; 3229SET @x_int_1 = NULL; 3230SET @x_int_2 = NULL; 3231SET @x_int_3 = NULL; 3232SET @x_dec_1 = NULL; 3233SET @x_dec_2 = NULL; 3234SET @x_dec_3 = NULL; 3235 3236-- Testing strings... 3237 3238EXECUTE stmt_str USING @x_int_1, @x_str_1, @x_str_2, @x_str_3; 3239SELECT @x_int_1, @x_str_1, @x_str_2, @x_str_3; 3240@x_int_1 @x_str_1 @x_str_2 @x_str_3 3241NULL test_v1 NULL test_v3 3242 3243EXECUTE stmt_str USING @x_int_1, @x_str_1, @x_str_2, @x_str_3; 3244SELECT @x_int_1, @x_str_1, @x_str_2, @x_str_3; 3245@x_int_1 @x_str_1 @x_str_2 @x_str_3 3246NULL test_v1 NULL test_v3 3247 3248-- Testing doubles... 3249 3250EXECUTE stmt_dbl USING @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; 3251SELECT @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; 3252@x_int_1 @x_dbl_1 @x_dbl_2 @x_dbl_3 3253NULL 12.34 NULL 56.78 3254 3255EXECUTE stmt_dbl USING @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; 3256SELECT @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; 3257@x_int_1 @x_dbl_1 @x_dbl_2 @x_dbl_3 3258NULL 12.34 NULL 56.78 3259 3260-- Testing ints... 3261 3262EXECUTE stmt_int USING @x_str_1, @x_int_1, @x_int_2, @x_int_3; 3263SELECT @x_str_1, @x_int_1, @x_int_2, @x_int_3; 3264@x_str_1 @x_int_1 @x_int_2 @x_int_3 3265test_v1 1234 NULL 5678 3266 3267EXECUTE stmt_int USING @x_str_1, @x_int_1, @x_int_2, @x_int_3; 3268SELECT @x_str_1, @x_int_1, @x_int_2, @x_int_3; 3269@x_str_1 @x_int_1 @x_int_2 @x_int_3 3270test_v1 1234 NULL 5678 3271 3272-- Testing decs... 3273 3274EXECUTE stmt_dec USING @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; 3275SELECT @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; 3276@x_int_1 @x_dec_1 @x_dec_2 @x_dec_3 32771234 12.34 NULL 56.78 3278 3279EXECUTE stmt_dec USING @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; 3280SELECT @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; 3281@x_int_1 @x_dec_1 @x_dec_2 @x_dec_3 32821234 12.34 NULL 56.78 3283 3284DEALLOCATE PREPARE stmt_str; 3285DEALLOCATE PREPARE stmt_dbl; 3286DEALLOCATE PREPARE stmt_int; 3287DEALLOCATE PREPARE stmt_dec; 3288 3289DROP PROCEDURE p_string; 3290DROP PROCEDURE p_double; 3291DROP PROCEDURE p_int; 3292DROP PROCEDURE p_decimal; 3293 3294DROP PROCEDURE IF EXISTS p1; 3295DROP PROCEDURE IF EXISTS p2; 3296 3297CREATE PROCEDURE p1(OUT v1 CHAR(10)) 3298SET v1 = 'test1'; 3299 3300CREATE PROCEDURE p2(OUT v2 CHAR(10)) 3301BEGIN 3302SET @query = 'CALL p1(?)'; 3303PREPARE stmt1 FROM @query; 3304EXECUTE stmt1 USING @u1; 3305DEALLOCATE PREPARE stmt1; 3306SET v2 = @u1; 3307END| 3308 3309CALL p2(@a); 3310SELECT @a; 3311@a 3312test1 3313 3314DROP PROCEDURE p1; 3315DROP PROCEDURE p2; 3316 3317TINYINT 3318 3319CREATE PROCEDURE p1(OUT v TINYINT) 3320SET v = 127; 3321PREPARE stmt1 FROM 'CALL p1(?)'; 3322EXECUTE stmt1 USING @a; 3323CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3324SHOW CREATE TABLE tmp1; 3325Table Create Table 3326tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3327 `c1` bigint(20) DEFAULT NULL 3328) ENGINE=MyISAM DEFAULT CHARSET=latin1 3329SELECT @a, @a = 127; 3330@a @a = 127 3331127 1 3332DROP TEMPORARY TABLE tmp1; 3333DROP PROCEDURE p1; 3334 3335SMALLINT 3336 3337CREATE PROCEDURE p1(OUT v SMALLINT) 3338SET v = 32767; 3339PREPARE stmt1 FROM 'CALL p1(?)'; 3340EXECUTE stmt1 USING @a; 3341CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3342SHOW CREATE TABLE tmp1; 3343Table Create Table 3344tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3345 `c1` bigint(20) DEFAULT NULL 3346) ENGINE=MyISAM DEFAULT CHARSET=latin1 3347SELECT @a, @a = 32767; 3348@a @a = 32767 334932767 1 3350DROP TEMPORARY TABLE tmp1; 3351DROP PROCEDURE p1; 3352 3353MEDIUMINT 3354 3355CREATE PROCEDURE p1(OUT v MEDIUMINT) 3356SET v = 8388607; 3357PREPARE stmt1 FROM 'CALL p1(?)'; 3358EXECUTE stmt1 USING @a; 3359CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3360SHOW CREATE TABLE tmp1; 3361Table Create Table 3362tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3363 `c1` bigint(20) DEFAULT NULL 3364) ENGINE=MyISAM DEFAULT CHARSET=latin1 3365SELECT @a, @a = 8388607; 3366@a @a = 8388607 33678388607 1 3368DROP TEMPORARY TABLE tmp1; 3369DROP PROCEDURE p1; 3370 3371INT 3372 3373CREATE PROCEDURE p1(OUT v INT) 3374SET v = 2147483647; 3375PREPARE stmt1 FROM 'CALL p1(?)'; 3376EXECUTE stmt1 USING @a; 3377CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3378SHOW CREATE TABLE tmp1; 3379Table Create Table 3380tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3381 `c1` bigint(20) DEFAULT NULL 3382) ENGINE=MyISAM DEFAULT CHARSET=latin1 3383SELECT @a, @a = 2147483647; 3384@a @a = 2147483647 33852147483647 1 3386DROP TEMPORARY TABLE tmp1; 3387DROP PROCEDURE p1; 3388 3389BIGINT 3390 3391CREATE PROCEDURE p1(OUT v BIGINT) 3392SET v = 9223372036854775807; 3393PREPARE stmt1 FROM 'CALL p1(?)'; 3394EXECUTE stmt1 USING @a; 3395CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3396SHOW CREATE TABLE tmp1; 3397Table Create Table 3398tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3399 `c1` bigint(20) DEFAULT NULL 3400) ENGINE=MyISAM DEFAULT CHARSET=latin1 3401SELECT @a, @a = 9223372036854775807; 3402@a @a = 9223372036854775807 34039223372036854775807 1 3404DROP TEMPORARY TABLE tmp1; 3405DROP PROCEDURE p1; 3406 3407BIT(11) 3408 3409CREATE PROCEDURE p1(OUT v BIT(11)) 3410SET v = b'10100100101'; 3411PREPARE stmt1 FROM 'CALL p1(?)'; 3412EXECUTE stmt1 USING @a; 3413CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3414SHOW CREATE TABLE tmp1; 3415Table Create Table 3416tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3417 `c1` bigint(20) unsigned DEFAULT NULL 3418) ENGINE=MyISAM DEFAULT CHARSET=latin1 3419SELECT @a, @a = b'10100100101'; 3420@a @a = b'10100100101' 34211317 1 3422DROP TEMPORARY TABLE tmp1; 3423DROP PROCEDURE p1; 3424 3425TIMESTAMP 3426 3427CREATE PROCEDURE p1(OUT v TIMESTAMP) 3428SET v = '2007-11-18 15:01:02'; 3429PREPARE stmt1 FROM 'CALL p1(?)'; 3430EXECUTE stmt1 USING @a; 3431CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3432SHOW CREATE TABLE tmp1; 3433Table Create Table 3434tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3435 `c1` longblob DEFAULT NULL 3436) ENGINE=MyISAM DEFAULT CHARSET=latin1 3437SELECT @a, @a = '2007-11-18 15:01:02'; 3438@a @a = '2007-11-18 15:01:02' 34392007-11-18 15:01:02 1 3440DROP TEMPORARY TABLE tmp1; 3441DROP PROCEDURE p1; 3442 3443DATETIME 3444 3445CREATE PROCEDURE p1(OUT v DATETIME) 3446SET v = '1234-11-12 12:34:59'; 3447PREPARE stmt1 FROM 'CALL p1(?)'; 3448EXECUTE stmt1 USING @a; 3449CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3450SHOW CREATE TABLE tmp1; 3451Table Create Table 3452tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3453 `c1` longblob DEFAULT NULL 3454) ENGINE=MyISAM DEFAULT CHARSET=latin1 3455SELECT @a, @a = '1234-11-12 12:34:59'; 3456@a @a = '1234-11-12 12:34:59' 34571234-11-12 12:34:59 1 3458DROP TEMPORARY TABLE tmp1; 3459DROP PROCEDURE p1; 3460 3461TIME 3462 3463CREATE PROCEDURE p1(OUT v TIME) 3464SET v = '123:45:01'; 3465PREPARE stmt1 FROM 'CALL p1(?)'; 3466EXECUTE stmt1 USING @a; 3467CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3468SHOW CREATE TABLE tmp1; 3469Table Create Table 3470tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3471 `c1` longblob DEFAULT NULL 3472) ENGINE=MyISAM DEFAULT CHARSET=latin1 3473SELECT @a, @a = '123:45:01'; 3474@a @a = '123:45:01' 3475123:45:01 1 3476DROP TEMPORARY TABLE tmp1; 3477DROP PROCEDURE p1; 3478 3479DATE 3480 3481CREATE PROCEDURE p1(OUT v DATE) 3482SET v = '1234-11-12'; 3483PREPARE stmt1 FROM 'CALL p1(?)'; 3484EXECUTE stmt1 USING @a; 3485CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3486SHOW CREATE TABLE tmp1; 3487Table Create Table 3488tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3489 `c1` longblob DEFAULT NULL 3490) ENGINE=MyISAM DEFAULT CHARSET=latin1 3491SELECT @a, @a = '1234-11-12'; 3492@a @a = '1234-11-12' 34931234-11-12 1 3494DROP TEMPORARY TABLE tmp1; 3495DROP PROCEDURE p1; 3496 3497YEAR 3498 3499CREATE PROCEDURE p1(OUT v YEAR) 3500SET v = 2010; 3501PREPARE stmt1 FROM 'CALL p1(?)'; 3502EXECUTE stmt1 USING @a; 3503CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3504SHOW CREATE TABLE tmp1; 3505Table Create Table 3506tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3507 `c1` bigint(20) unsigned DEFAULT NULL 3508) ENGINE=MyISAM DEFAULT CHARSET=latin1 3509SELECT @a, @a = 2010; 3510@a @a = 2010 35112010 1 3512DROP TEMPORARY TABLE tmp1; 3513DROP PROCEDURE p1; 3514 3515FLOAT(7, 4) 3516 3517CREATE PROCEDURE p1(OUT v FLOAT(7, 4)) 3518SET v = 123.4567; 3519PREPARE stmt1 FROM 'CALL p1(?)'; 3520EXECUTE stmt1 USING @a; 3521CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3522SHOW CREATE TABLE tmp1; 3523Table Create Table 3524tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3525 `c1` double DEFAULT NULL 3526) ENGINE=MyISAM DEFAULT CHARSET=latin1 3527SELECT @a, @a - 123.4567 < 0.00001; 3528@a @a - 123.4567 < 0.00001 3529123.45670318603516 1 3530DROP TEMPORARY TABLE tmp1; 3531DROP PROCEDURE p1; 3532 3533DOUBLE(8, 5) 3534 3535CREATE PROCEDURE p1(OUT v DOUBLE(8, 5)) 3536SET v = 123.45678; 3537PREPARE stmt1 FROM 'CALL p1(?)'; 3538EXECUTE stmt1 USING @a; 3539CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3540SHOW CREATE TABLE tmp1; 3541Table Create Table 3542tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3543 `c1` double DEFAULT NULL 3544) ENGINE=MyISAM DEFAULT CHARSET=latin1 3545SELECT @a, @a - 123.45678 < 0.000001; 3546@a @a - 123.45678 < 0.000001 3547123.45678 1 3548DROP TEMPORARY TABLE tmp1; 3549DROP PROCEDURE p1; 3550 3551DECIMAL(9, 6) 3552 3553CREATE PROCEDURE p1(OUT v DECIMAL(9, 6)) 3554SET v = 123.456789; 3555PREPARE stmt1 FROM 'CALL p1(?)'; 3556EXECUTE stmt1 USING @a; 3557CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3558SHOW CREATE TABLE tmp1; 3559Table Create Table 3560tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3561 `c1` decimal(65,38) DEFAULT NULL 3562) ENGINE=MyISAM DEFAULT CHARSET=latin1 3563SELECT @a, @a = 123.456789; 3564@a @a = 123.456789 3565123.456789 1 3566DROP TEMPORARY TABLE tmp1; 3567DROP PROCEDURE p1; 3568 3569CHAR(32) 3570 3571CREATE PROCEDURE p1(OUT v CHAR(32)) 3572SET v = REPEAT('a', 16); 3573PREPARE stmt1 FROM 'CALL p1(?)'; 3574EXECUTE stmt1 USING @a; 3575CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3576SHOW CREATE TABLE tmp1; 3577Table Create Table 3578tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3579 `c1` longtext DEFAULT NULL 3580) ENGINE=MyISAM DEFAULT CHARSET=latin1 3581SELECT @a, @a = REPEAT('a', 16); 3582@a @a = REPEAT('a', 16) 3583aaaaaaaaaaaaaaaa 1 3584DROP TEMPORARY TABLE tmp1; 3585DROP PROCEDURE p1; 3586 3587VARCHAR(32) 3588 3589CREATE PROCEDURE p1(OUT v VARCHAR(32)) 3590SET v = REPEAT('b', 16); 3591PREPARE stmt1 FROM 'CALL p1(?)'; 3592EXECUTE stmt1 USING @a; 3593CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3594SHOW CREATE TABLE tmp1; 3595Table Create Table 3596tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3597 `c1` longtext DEFAULT NULL 3598) ENGINE=MyISAM DEFAULT CHARSET=latin1 3599SELECT @a, @a = REPEAT('b', 16); 3600@a @a = REPEAT('b', 16) 3601bbbbbbbbbbbbbbbb 1 3602DROP TEMPORARY TABLE tmp1; 3603DROP PROCEDURE p1; 3604 3605TINYTEXT 3606 3607CREATE PROCEDURE p1(OUT v TINYTEXT) 3608SET v = REPEAT('c', 16); 3609PREPARE stmt1 FROM 'CALL p1(?)'; 3610EXECUTE stmt1 USING @a; 3611CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3612SHOW CREATE TABLE tmp1; 3613Table Create Table 3614tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3615 `c1` longtext DEFAULT NULL 3616) ENGINE=MyISAM DEFAULT CHARSET=latin1 3617SELECT @a, @a = REPEAT('c', 16); 3618@a @a = REPEAT('c', 16) 3619cccccccccccccccc 1 3620DROP TEMPORARY TABLE tmp1; 3621DROP PROCEDURE p1; 3622 3623TEXT 3624 3625CREATE PROCEDURE p1(OUT v TEXT) 3626SET v = REPEAT('d', 16); 3627PREPARE stmt1 FROM 'CALL p1(?)'; 3628EXECUTE stmt1 USING @a; 3629CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3630SHOW CREATE TABLE tmp1; 3631Table Create Table 3632tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3633 `c1` longtext DEFAULT NULL 3634) ENGINE=MyISAM DEFAULT CHARSET=latin1 3635SELECT @a, @a = REPEAT('d', 16); 3636@a @a = REPEAT('d', 16) 3637dddddddddddddddd 1 3638DROP TEMPORARY TABLE tmp1; 3639DROP PROCEDURE p1; 3640 3641MEDIUMTEXT 3642 3643CREATE PROCEDURE p1(OUT v MEDIUMTEXT) 3644SET v = REPEAT('e', 16); 3645PREPARE stmt1 FROM 'CALL p1(?)'; 3646EXECUTE stmt1 USING @a; 3647CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3648SHOW CREATE TABLE tmp1; 3649Table Create Table 3650tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3651 `c1` longtext DEFAULT NULL 3652) ENGINE=MyISAM DEFAULT CHARSET=latin1 3653SELECT @a, @a = REPEAT('e', 16); 3654@a @a = REPEAT('e', 16) 3655eeeeeeeeeeeeeeee 1 3656DROP TEMPORARY TABLE tmp1; 3657DROP PROCEDURE p1; 3658 3659LONGTEXT 3660 3661CREATE PROCEDURE p1(OUT v LONGTEXT) 3662SET v = REPEAT('f', 16); 3663PREPARE stmt1 FROM 'CALL p1(?)'; 3664EXECUTE stmt1 USING @a; 3665CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3666SHOW CREATE TABLE tmp1; 3667Table Create Table 3668tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3669 `c1` longtext DEFAULT NULL 3670) ENGINE=MyISAM DEFAULT CHARSET=latin1 3671SELECT @a, @a = REPEAT('f', 16); 3672@a @a = REPEAT('f', 16) 3673ffffffffffffffff 1 3674DROP TEMPORARY TABLE tmp1; 3675DROP PROCEDURE p1; 3676 3677BINARY(32) 3678 3679CREATE PROCEDURE p1(OUT v BINARY(32)) 3680SET v = REPEAT('g', 32); 3681PREPARE stmt1 FROM 'CALL p1(?)'; 3682EXECUTE stmt1 USING @a; 3683CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3684SHOW CREATE TABLE tmp1; 3685Table Create Table 3686tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3687 `c1` longblob DEFAULT NULL 3688) ENGINE=MyISAM DEFAULT CHARSET=latin1 3689SELECT @a, @a = REPEAT('g', 32); 3690@a @a = REPEAT('g', 32) 3691gggggggggggggggggggggggggggggggg 1 3692DROP TEMPORARY TABLE tmp1; 3693DROP PROCEDURE p1; 3694 3695VARBINARY(32) 3696 3697CREATE PROCEDURE p1(OUT v VARBINARY(32)) 3698SET v = REPEAT('h', 16); 3699PREPARE stmt1 FROM 'CALL p1(?)'; 3700EXECUTE stmt1 USING @a; 3701CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3702SHOW CREATE TABLE tmp1; 3703Table Create Table 3704tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3705 `c1` longblob DEFAULT NULL 3706) ENGINE=MyISAM DEFAULT CHARSET=latin1 3707SELECT @a, @a = REPEAT('h', 16); 3708@a @a = REPEAT('h', 16) 3709hhhhhhhhhhhhhhhh 1 3710DROP TEMPORARY TABLE tmp1; 3711DROP PROCEDURE p1; 3712 3713TINYBLOB 3714 3715CREATE PROCEDURE p1(OUT v TINYBLOB) 3716SET v = REPEAT('i', 16); 3717PREPARE stmt1 FROM 'CALL p1(?)'; 3718EXECUTE stmt1 USING @a; 3719CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3720SHOW CREATE TABLE tmp1; 3721Table Create Table 3722tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3723 `c1` longblob DEFAULT NULL 3724) ENGINE=MyISAM DEFAULT CHARSET=latin1 3725SELECT @a, @a = REPEAT('i', 16); 3726@a @a = REPEAT('i', 16) 3727iiiiiiiiiiiiiiii 1 3728DROP TEMPORARY TABLE tmp1; 3729DROP PROCEDURE p1; 3730 3731BLOB 3732 3733CREATE PROCEDURE p1(OUT v BLOB) 3734SET v = REPEAT('j', 16); 3735PREPARE stmt1 FROM 'CALL p1(?)'; 3736EXECUTE stmt1 USING @a; 3737CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3738SHOW CREATE TABLE tmp1; 3739Table Create Table 3740tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3741 `c1` longblob DEFAULT NULL 3742) ENGINE=MyISAM DEFAULT CHARSET=latin1 3743SELECT @a, @a = REPEAT('j', 16); 3744@a @a = REPEAT('j', 16) 3745jjjjjjjjjjjjjjjj 1 3746DROP TEMPORARY TABLE tmp1; 3747DROP PROCEDURE p1; 3748 3749MEDIUMBLOB 3750 3751CREATE PROCEDURE p1(OUT v MEDIUMBLOB) 3752SET v = REPEAT('k', 16); 3753PREPARE stmt1 FROM 'CALL p1(?)'; 3754EXECUTE stmt1 USING @a; 3755CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3756SHOW CREATE TABLE tmp1; 3757Table Create Table 3758tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3759 `c1` longblob DEFAULT NULL 3760) ENGINE=MyISAM DEFAULT CHARSET=latin1 3761SELECT @a, @a = REPEAT('k', 16); 3762@a @a = REPEAT('k', 16) 3763kkkkkkkkkkkkkkkk 1 3764DROP TEMPORARY TABLE tmp1; 3765DROP PROCEDURE p1; 3766 3767LONGBLOB 3768 3769CREATE PROCEDURE p1(OUT v LONGBLOB) 3770SET v = REPEAT('l', 16); 3771PREPARE stmt1 FROM 'CALL p1(?)'; 3772EXECUTE stmt1 USING @a; 3773CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3774SHOW CREATE TABLE tmp1; 3775Table Create Table 3776tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3777 `c1` longblob DEFAULT NULL 3778) ENGINE=MyISAM DEFAULT CHARSET=latin1 3779SELECT @a, @a = REPEAT('l', 16); 3780@a @a = REPEAT('l', 16) 3781llllllllllllllll 1 3782DROP TEMPORARY TABLE tmp1; 3783DROP PROCEDURE p1; 3784 3785SET('aaa', 'bbb') 3786 3787CREATE PROCEDURE p1(OUT v SET('aaa', 'bbb')) 3788SET v = 'aaa'; 3789PREPARE stmt1 FROM 'CALL p1(?)'; 3790EXECUTE stmt1 USING @a; 3791CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3792SHOW CREATE TABLE tmp1; 3793Table Create Table 3794tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3795 `c1` longtext DEFAULT NULL 3796) ENGINE=MyISAM DEFAULT CHARSET=latin1 3797SELECT @a, @a = 'aaa'; 3798@a @a = 'aaa' 3799aaa 1 3800DROP TEMPORARY TABLE tmp1; 3801DROP PROCEDURE p1; 3802 3803ENUM('aaa', 'bbb') 3804 3805CREATE PROCEDURE p1(OUT v ENUM('aaa', 'bbb')) 3806SET v = 'aaa'; 3807PREPARE stmt1 FROM 'CALL p1(?)'; 3808EXECUTE stmt1 USING @a; 3809CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3810SHOW CREATE TABLE tmp1; 3811Table Create Table 3812tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3813 `c1` longtext DEFAULT NULL 3814) ENGINE=MyISAM DEFAULT CHARSET=latin1 3815SELECT @a, @a = 'aaa'; 3816@a @a = 'aaa' 3817aaa 1 3818DROP TEMPORARY TABLE tmp1; 3819DROP PROCEDURE p1; 3820 3821# End of WL#4435. 3822# 3823# WL#4284: Transactional DDL locking 3824# 3825DROP TABLE IF EXISTS t1; 3826CREATE TABLE t1 (a INT); 3827BEGIN; 3828SELECT * FROM t1; 3829a 3830# Test that preparing a CREATE TABLE does not take a exclusive metdata lock. 3831PREPARE stmt1 FROM "CREATE TABLE t1 AS SELECT 1"; 3832EXECUTE stmt1; 3833ERROR 42S01: Table 't1' already exists 3834DEALLOCATE PREPARE stmt1; 3835DROP TABLE t1; 3836# 3837# WL#4284: Transactional DDL locking 3838# 3839# Test that metadata locks taken during prepare are released. 3840# 3841connect con1,localhost,root,,; 3842connection default; 3843DROP TABLE IF EXISTS t1; 3844CREATE TABLE t1 (a INT); 3845connection con1; 3846BEGIN; 3847PREPARE stmt1 FROM "SELECT * FROM t1"; 3848connection default; 3849DROP TABLE t1; 3850disconnect con1; 3851 3852# 3853# Bug#56115: invalid memory reads when PS selecting from 3854# information_schema tables 3855# Bug#58701: crash in Field::make_field, cursor-protocol 3856# 3857# NOTE: MTR should be run both with --ps-protocol and --cursor-protocol. 3858# 3859 3860SELECT * 3861FROM (SELECT 1 UNION SELECT 2) t; 38621 38631 38642 3865 3866# Bug#13805127: Stored program cache produces wrong result in same THD 3867 3868PREPARE s1 FROM 3869" 3870SELECT c1, t2.c2, count(c3) 3871FROM 3872 ( 3873 SELECT 3 as c2 FROM dual WHERE @x = 1 3874 UNION 3875 SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 3876 ) AS t1, 3877 ( 3878 SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual 3879 UNION 3880 SELECT '2012-03-01 02:00:00', 3, 2 FROM dual 3881 UNION 3882 SELECT '2012-03-01 01:00:00', 2, 1 FROM dual 3883 ) AS t2 3884WHERE t2.c2 = t1.c2 3885GROUP BY c1, c2 3886"; 3887 3888SET @x = 1; 3889SELECT c1, t2.c2, count(c3) 3890FROM 3891( 3892SELECT 3 as c2 FROM dual WHERE @x = 1 3893UNION 3894SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 3895) AS t1, 3896( 3897SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual 3898UNION 3899SELECT '2012-03-01 02:00:00', 3, 2 FROM dual 3900UNION 3901SELECT '2012-03-01 01:00:00', 2, 1 FROM dual 3902) AS t2 3903WHERE t2.c2 = t1.c2 3904GROUP BY c1, c2; 3905c1 c2 count(c3) 39062012-03-01 01:00:00 2 1 39072012-03-01 01:00:00 3 1 39082012-03-01 02:00:00 3 1 3909 3910EXECUTE s1; 3911c1 c2 count(c3) 39122012-03-01 01:00:00 2 1 39132012-03-01 01:00:00 3 1 39142012-03-01 02:00:00 3 1 3915 3916SET @x = 2; 3917SELECT c1, t2.c2, count(c3) 3918FROM 3919( 3920SELECT 3 as c2 FROM dual WHERE @x = 1 3921UNION 3922SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 3923) AS t1, 3924( 3925SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual 3926UNION 3927SELECT '2012-03-01 02:00:00', 3, 2 FROM dual 3928UNION 3929SELECT '2012-03-01 01:00:00', 2, 1 FROM dual 3930) AS t2 3931WHERE t2.c2 = t1.c2 3932GROUP BY c1, c2; 3933c1 c2 count(c3) 39342012-03-01 01:00:00 2 1 3935 3936EXECUTE s1; 3937c1 c2 count(c3) 39382012-03-01 01:00:00 2 1 3939 3940SET @x = 1; 3941SELECT c1, t2.c2, count(c3) 3942FROM 3943( 3944SELECT 3 as c2 FROM dual WHERE @x = 1 3945UNION 3946SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 3947) AS t1, 3948( 3949SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual 3950UNION 3951SELECT '2012-03-01 02:00:00', 3, 2 FROM dual 3952UNION 3953SELECT '2012-03-01 01:00:00', 2, 1 FROM dual 3954) AS t2 3955WHERE t2.c2 = t1.c2 3956GROUP BY c1, c2; 3957c1 c2 count(c3) 39582012-03-01 01:00:00 2 1 39592012-03-01 01:00:00 3 1 39602012-03-01 02:00:00 3 1 3961 3962EXECUTE s1; 3963c1 c2 count(c3) 39642012-03-01 01:00:00 2 1 39652012-03-01 01:00:00 3 1 39662012-03-01 02:00:00 3 1 3967DEALLOCATE PREPARE s1; 3968prepare stmt from "select date('2010-10-10') between '2010-09-09' and ?"; 3969set @a='2010-11-11'; 3970execute stmt using @a; 3971date('2010-10-10') between '2010-09-09' and ? 39721 3973execute stmt using @a; 3974date('2010-10-10') between '2010-09-09' and ? 39751 3976set @a='2010-08-08'; 3977execute stmt using @a; 3978date('2010-10-10') between '2010-09-09' and ? 39790 3980execute stmt using @a; 3981date('2010-10-10') between '2010-09-09' and ? 39820 3983# 3984# Bug #892725: look-up is changed for a full scan when executing PS 3985# 3986create table t1 (a int primary key, b int); 3987insert into t1 values 3988(7,70), (3,40), (4,40), (8,70), (1,70), (9,50), (2,70); 3989prepare st from 'select * from t1 where a=8'; 3990flush status; 3991execute st; 3992a b 39938 70 3994show status like '%Handler_read%'; 3995Variable_name Value 3996Handler_read_first 0 3997Handler_read_key 1 3998Handler_read_last 0 3999Handler_read_next 0 4000Handler_read_prev 0 4001Handler_read_retry 0 4002Handler_read_rnd 0 4003Handler_read_rnd_deleted 0 4004Handler_read_rnd_next 0 4005flush status; 4006execute st; 4007a b 40088 70 4009show status like '%Handler_read%'; 4010Variable_name Value 4011Handler_read_first 0 4012Handler_read_key 1 4013Handler_read_last 0 4014Handler_read_next 0 4015Handler_read_prev 0 4016Handler_read_retry 0 4017Handler_read_rnd 0 4018Handler_read_rnd_deleted 0 4019Handler_read_rnd_next 0 4020flush status; 4021select * from t1 use index() where a=3; 4022a b 40233 40 4024show status like '%Handler_read%'; 4025Variable_name Value 4026Handler_read_first 0 4027Handler_read_key 0 4028Handler_read_last 0 4029Handler_read_next 0 4030Handler_read_prev 0 4031Handler_read_retry 0 4032Handler_read_rnd 0 4033Handler_read_rnd_deleted 0 4034Handler_read_rnd_next 8 4035flush status; 4036execute st; 4037a b 40388 70 4039show status like '%Handler_read%'; 4040Variable_name Value 4041Handler_read_first 0 4042Handler_read_key 1 4043Handler_read_last 0 4044Handler_read_next 0 4045Handler_read_prev 0 4046Handler_read_retry 0 4047Handler_read_rnd 0 4048Handler_read_rnd_deleted 0 4049Handler_read_rnd_next 0 4050deallocate prepare st; 4051drop table t1; 4052# 4053# Bug mdev-5410: crash at the execution of PS with subselect 4054# formed by UNION with global ORDER BY 4055# 4056CREATE TABLE t1 (a int DEFAULT NULL); 4057INSERT INTO t1 VALUES (2), (4); 4058CREATE TABLE t2 (b int DEFAULT NULL); 4059INSERT INTO t2 VALUES (1), (3); 4060PREPARE stmt FROM " 4061SELECT c1 FROM (SELECT (SELECT a FROM t1 WHERE t1.a <= t2.b 4062 UNION ALL 4063 SELECT a FROM t1 WHERE t1.a+3<= t2.b 4064 ORDER BY a DESC) AS c1 FROM t2) t3; 4065"; 4066EXECUTE stmt; 4067c1 4068NULL 40692 4070EXECUTE stmt; 4071c1 4072NULL 40732 4074DROP TABLE t1,t2; 4075# 4076# MDEV-5369: Wrong result (0 instead of NULL) on 2nd execution of 4077# PS with LEFT JOIN, TEMPTABLE view 4078# 4079CREATE TABLE t1 (a INT) ENGINE=MyISAM; 4080INSERT INTO t1 VALUES (0),(8); 4081CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=MyISAM; 4082CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; 4083SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk; 4084SUM(pk) 4085NULL 4086PREPARE stmt FROM "SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk"; 4087EXECUTE stmt; 4088SUM(pk) 4089NULL 4090EXECUTE stmt; 4091SUM(pk) 4092NULL 4093DEALLOCATE PREPARE stmt; 4094DROP VIEW v2; 4095DROP TABLE t1, t2; 4096# End of 5.3 tests 4097# 4098# MDEV-5505: Assertion `! is_set()' fails on PREPARE SELECT 4099# with out of range in GROUP BY 4100# 4101CREATE TABLE t1 (a INT); 4102PREPARE stmt FROM "SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1"; 4103ERROR 22003: BIGINT UNSIGNED value is out of range in '18446744073709551615 + 1' 4104SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1; 4105ERROR 22003: BIGINT UNSIGNED value is out of range in '18446744073709551615 + 1' 4106drop table t1; 4107# End of 5.3 tests 4108# 4109# MDEV-8756: MariaDB 10.0.21 crashes during PREPARE 4110# 4111CREATE TABLE t1 ( id INT(10), value INT(10) ); 4112CREATE TABLE t2 ( id INT(10) ); 4113SET @save_sql_mode= @@sql_mode; 4114SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY'; 4115PREPARE stmt FROM 'UPDATE t1 t1 SET value = (SELECT 1 FROM t2 WHERE id = t1.id)'; 4116execute stmt; 4117insert into t1 values (1,10),(2,10),(3,10); 4118insert into t2 values (1),(2); 4119execute stmt; 4120select * from t1; 4121id value 41221 1 41232 1 41243 NULL 4125deallocate prepare stmt; 4126SET SESSION sql_mode = @save_sql_mode; 4127DROP TABLE t1,t2; 4128# 4129# MDEV-8833: Crash of server on prepared statement with 4130# conversion to semi-join 4131# 4132CREATE TABLE t1 (column1 INT); 4133INSERT INTO t1 VALUES (3),(9); 4134CREATE TABLE t2 (column2 INT); 4135INSERT INTO t2 VALUES (1),(4); 4136CREATE TABLE t3 (column3 INT); 4137INSERT INTO t3 VALUES (6),(8); 4138CREATE TABLE t4 (column4 INT); 4139INSERT INTO t4 VALUES (2),(5); 4140PREPARE stmt FROM "SELECT ( SELECT MAX( table1.column1 ) AS field1 4141FROM t1 AS table1 4142WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) 4143) AS sq 4144FROM t3 AS table3, t4 AS table4"; 4145EXECUTE stmt; 4146sq 4147NULL 4148NULL 4149NULL 4150NULL 4151EXECUTE stmt; 4152sq 4153NULL 4154NULL 4155NULL 4156NULL 4157deallocate prepare stmt; 4158drop table t1,t2,t3,t4; 4159# 4160# MDEV-11859: the plans for the first and the second executions 4161# of PS are not the same 4162# 4163create table t1 (id int, c varchar(3), key idx(c))engine=myisam; 4164insert into t1 values (3,'bar'), (1,'xxx'), (2,'foo'), (5,'yyy'); 4165prepare stmt1 from 4166"explain extended 4167 select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'"; 4168execute stmt1; 4169id select_type table type possible_keys key key_len ref rows filtered Extra 41701 PRIMARY t1 ref idx idx 6 const 1 100.00 Using index condition 41712 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4172Warnings: 4173Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`c` = 'foo' 4174execute stmt1; 4175id select_type table type possible_keys key key_len ref rows filtered Extra 41761 PRIMARY t1 ref idx idx 6 const 1 100.00 Using index condition 41772 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4178Warnings: 4179Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`c` = 'foo' 4180deallocate prepare stmt1; 4181prepare stmt1 from 4182"select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'"; 4183flush status; 4184execute stmt1; 4185id c 41862 foo 4187show status like '%Handler_read%'; 4188Variable_name Value 4189Handler_read_first 0 4190Handler_read_key 1 4191Handler_read_last 0 4192Handler_read_next 1 4193Handler_read_prev 0 4194Handler_read_retry 0 4195Handler_read_rnd 0 4196Handler_read_rnd_deleted 0 4197Handler_read_rnd_next 0 4198flush status; 4199execute stmt1; 4200id c 42012 foo 4202show status like '%Handler_read%'; 4203Variable_name Value 4204Handler_read_first 0 4205Handler_read_key 1 4206Handler_read_last 0 4207Handler_read_next 1 4208Handler_read_prev 0 4209Handler_read_retry 0 4210Handler_read_rnd 0 4211Handler_read_rnd_deleted 0 4212Handler_read_rnd_next 0 4213deallocate prepare stmt1; 4214prepare stmt2 from 4215"explain extended 4216 select * from t1 where (1, 2) in ( select 3, 4 )"; 4217execute stmt2; 4218id select_type table type possible_keys key key_len ref rows filtered Extra 42191 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 42202 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4221Warnings: 4222Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where 0 4223execute stmt2; 4224id select_type table type possible_keys key key_len ref rows filtered Extra 42251 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 42262 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4227Warnings: 4228Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where 0 4229deallocate prepare stmt2; 4230drop table t1; 4231# 4232# MDEV-9208: Function->Function->View = Mysqld segfault 4233# (Server crashes in Dependency_marker::visit_field on 2nd 4234# execution with merged subquery) 4235# 4236CREATE TABLE t1 (i1 INT); 4237insert into t1 values(1),(2); 4238CREATE TABLE t2 (i2 INT); 4239insert into t2 values(1),(2); 4240prepare stmt from " 4241 select 1 from ( 4242 select 4243 if (i1<0, 0, 0) as f1, 4244 (select f1) as f2 4245 from t1, t2 4246 ) sq 4247"; 4248execute stmt; 42491 42501 42511 42521 42531 4254execute stmt; 42551 42561 42571 42581 42591 4260drop table t1,t2; 4261# 4262# MDEV-9619: Assertion `null_ref_table' failed in virtual 4263# table_map Item_direct_view_ref::used_tables() const on 2nd 4264# execution of PS 4265# 4266CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; 4267CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; 4268INSERT INTO t1 VALUES ('a'),('b'); 4269CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; 4270INSERT INTO t2 VALUES ('c'),('d'); 4271PREPARE stmt FROM "SELECT * FROM v1 WHERE f1 = SOME ( SELECT f2 FROM t2 )"; 4272EXECUTE stmt; 4273f1 4274EXECUTE stmt; 4275f1 4276insert into t1 values ('c'); 4277EXECUTE stmt; 4278f1 4279c 4280EXECUTE stmt; 4281f1 4282c 4283deallocate prepare stmt; 4284drop view v1; 4285drop table t1,t2; 4286CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; 4287CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; 4288INSERT INTO t1 VALUES ('a'),('b'); 4289CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; 4290INSERT INTO t2 VALUES ('c'),('d'); 4291PREPARE stmt FROM "SELECT * FROM v1 WHERE (f1,f1) = SOME ( SELECT f2,f2 FROM t2 )"; 4292EXECUTE stmt; 4293f1 4294EXECUTE stmt; 4295f1 4296insert into t1 values ('c'); 4297EXECUTE stmt; 4298f1 4299c 4300EXECUTE stmt; 4301f1 4302c 4303deallocate prepare stmt; 4304drop view v1; 4305drop table t1,t2; 4306CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; 4307INSERT INTO t1 VALUES (3),(9); 4308CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; 4309INSERT INTO t2 VALUES (1),(4); 4310CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; 4311INSERT INTO t3 VALUES (6),(8); 4312CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; 4313INSERT INTO t4 VALUES (2),(5); 4314PREPARE stmt FROM " 4315SELECT ( 4316 SELECT MAX( table1.column1 ) AS field1 4317 FROM t1 AS table1 4318 WHERE (111,table3.column3) IN ( SELECT 111,table2.column2 AS field2 FROM t2 AS table2 ) 4319) AS sq 4320FROM t3 AS table3, t4 AS table4 GROUP BY sq 4321"; 4322EXECUTE stmt; 4323sq 4324NULL 4325EXECUTE stmt; 4326sq 4327NULL 4328deallocate prepare stmt; 4329drop table t1,t2,t3,t4; 4330create table t1 (a int, b int, c int); 4331create table t2 (x int, y int, z int); 4332create table t3 as select * from t1; 4333insert into t1 values (1,2,3),(4,5,6),(100,200,300),(400,500,600); 4334insert into t2 values (1,2,3),(7,8,9),(100,200,300),(400,500,600); 4335insert into t3 values (1,2,3),(11,12,13),(100,0,0),(400,500,600); 4336set @optimizer_switch_save=@@optimizer_switch; 4337set @join_cache_level_save=@@join_cache_level; 4338set optimizer_switch='materialization=off'; 4339set join_cache_level=0; 4340select * from t1 where (select a,b from t3 where t3.c=t1.c) in (select x,y from t2 where t1.c= t2.z); 4341a b c 43421 2 3 4343400 500 600 4344prepare stmt from "select * from t1 where (select a,b from t3 where t3.c=t1.c) in (select x,y from t2 where t1.c= t2.z)"; 4345EXECUTE stmt; 4346a b c 43471 2 3 4348400 500 600 4349EXECUTE stmt; 4350a b c 43511 2 3 4352400 500 600 4353create view v1 as select * from t1; 4354create view v2 as select * from t2; 4355create view v3 as select * from t3; 4356select * from v1 where (select a,b from v3 where v3.c=v1.c) in (select x,y from v2 where v1.c= v2.z); 4357a b c 43581 2 3 4359400 500 600 4360prepare stmt from "select * from v1 where (select a,b from v3 where v3.c=v1.c) in (select x,y from v2 where v1.c= v2.z)"; 4361EXECUTE stmt; 4362a b c 43631 2 3 4364400 500 600 4365EXECUTE stmt; 4366a b c 43671 2 3 4368400 500 600 4369set optimizer_switch=@optimizer_switch_save; 4370set join_cache_level=@join_cache_level_save; 4371deallocate prepare stmt; 4372drop view v1,v2,v3; 4373drop table t1,t2,t3; 4374# 4375# MDEV-10657: incorrect result returned with binary protocol 4376# (prepared statements) 4377# 4378create table t1 (code varchar(10) primary key); 4379INSERT INTO t1(code) VALUES ('LINE1'), ('LINE2'), ('LINE3'); 4380SELECT X.* 4381FROM 4382(SELECT CODE, RN 4383FROM 4384(SELECT A.CODE, @cnt := @cnt + 1 AS RN 4385FROM t1 A, (SELECT @cnt := 0) C) T 4386) X; 4387CODE RN 4388LINE1 1 4389LINE2 2 4390LINE3 3 4391drop table t1; 4392# 4393# MDEV-17042: prepared statement does not return error with 4394# SQL_MODE STRICT_TRANS_TABLES. (Part 1) 4395# 4396set @save_sql_mode=@@sql_mode; 4397set sql_mode='STRICT_ALL_TABLES'; 4398CREATE TABLE t1 (id int, count int); 4399insert into t1 values (1,1),(0,2); 4400update t1 set count = count + 1 where id = '1bad'; 4401ERROR 22007: Truncated incorrect DOUBLE value: '1bad' 4402prepare stmt from "update t1 set count = count + 1 where id = '1bad'"; 4403execute stmt; 4404ERROR 22007: Truncated incorrect DOUBLE value: '1bad' 4405deallocate prepare stmt; 4406prepare stmt from 'update t1 set count = count + 1 where id = ?'; 4407set @a = '1bad'; 4408execute stmt using @a; 4409ERROR 22007: Truncated incorrect DOUBLE value: '1bad' 4410deallocate prepare stmt; 4411drop table t1; 4412CREATE TABLE t1 (id decimal(10,5), count int); 4413insert into t1 values (1,1),(0,2); 4414update t1 set count = count + 1 where id = '1bad'; 4415ERROR 22007: Truncated incorrect DECIMAL value: '1bad' 4416prepare stmt from "update t1 set count = count + 1 where id = '1bad'"; 4417execute stmt; 4418ERROR 22007: Truncated incorrect DECIMAL value: '1bad' 4419deallocate prepare stmt; 4420prepare stmt from 'update t1 set count = count + 1 where id = ?'; 4421set @a = '1bad'; 4422execute stmt using @a; 4423ERROR 22007: Truncated incorrect DECIMAL value: '1bad' 4424deallocate prepare stmt; 4425drop table t1; 4426CREATE TABLE t1 (id double, count int); 4427insert into t1 values (1,1),(0,2); 4428update t1 set count = count + 1 where id = '1bad'; 4429ERROR 22007: Truncated incorrect DOUBLE value: '1bad' 4430prepare stmt from "update t1 set count = count + 1 where id = '1bad'"; 4431execute stmt; 4432ERROR 22007: Truncated incorrect DOUBLE value: '1bad' 4433deallocate prepare stmt; 4434prepare stmt from 'update t1 set count = count + 1 where id = ?'; 4435set @a = '1bad'; 4436execute stmt using @a; 4437ERROR 22007: Truncated incorrect DOUBLE value: '1bad' 4438deallocate prepare stmt; 4439drop table t1; 4440CREATE TABLE t1 (id date, count int); 4441insert into t1 values ("2019-06-11",1),("2019-06-12",2); 4442update t1 set count = count + 1 where id = '1bad'; 4443ERROR 22007: Truncated incorrect datetime value: '1bad' 4444prepare stmt from "update t1 set count = count + 1 where id = '1bad'"; 4445execute stmt; 4446ERROR 22007: Truncated incorrect datetime value: '1bad' 4447deallocate prepare stmt; 4448prepare stmt from 'update t1 set count = count + 1 where id = ?'; 4449set @a = '1bad'; 4450execute stmt using @a; 4451ERROR 22007: Truncated incorrect datetime value: '1bad' 4452deallocate prepare stmt; 4453drop table t1; 4454set sql_mode=@save_sql_mode; 4455# End of 5.5 tests 4456# 4457# End of 10.0 tests 4458# 4459# 4460# MDEV-12060 Crash in EXECUTE IMMEDIATE with an expression returning a GRANT command 4461# (the 10.1 part) 4462# 4463CREATE PROCEDURE p2 () 4464BEGIN 4465SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR PREPARE stmt FROM 'SELECT 1'; 4466EXECUTE stmt; 4467DEALLOCATE PREPARE stmt; 4468END; 4469/ 4470CALL p2(); 44711 44721 4473DROP PROCEDURE p2; 4474BEGIN NOT ATOMIC 4475SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR PREPARE stmt FROM 'SELECT 1'; 4476EXECUTE stmt; 4477DEALLOCATE PREPARE stmt; 4478END; 4479/ 44801 44811 4482BEGIN NOT ATOMIC 4483SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR PREPARE stmt FROM 'SELECT 1'; 4484DEALLOCATE PREPARE stmt; 4485END; 4486/ 4487BEGIN NOT ATOMIC 4488PREPARE stmt FROM 'SELECT 1'; 4489SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR EXECUTE stmt; 4490DEALLOCATE PREPARE stmt; 4491END; 4492/ 44931 44941 4495# 4496# MDEV-14572: Assertion `! is_set()' failed in 4497# Diagnostics_area::set_eof_status upon EXPLAIN UPDATE in PS 4498# 4499CREATE TABLE t1 (a INT); 4500CREATE TABLE t2 (b INT); 4501PREPARE stmt FROM 'EXPLAIN UPDATE t1, t2 SET a = 1'; 4502EXECUTE stmt; 4503id select_type table type possible_keys key key_len ref rows Extra 45041 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found 45051 SIMPLE t2 system NULL NULL NULL NULL 0 Const row not found 4506EXECUTE stmt; 4507id select_type table type possible_keys key key_len ref rows Extra 45081 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found 45091 SIMPLE t2 system NULL NULL NULL NULL 0 Const row not found 4510deallocate prepare stmt; 4511DROP TABLE t1, t2; 4512# 4513# End of 10.1 tests 4514# 4515# 4516# MDEV-10709 Expressions as parameters to Dynamic SQL 4517# 4518# 4519# Using a simple expressions as an EXECUTE parameter 4520# 4521PREPARE stmt FROM 'SELECT ? FROM DUAL'; 4522EXECUTE stmt USING 10; 4523? 452410 4525DEALLOCATE PREPARE stmt; 4526PREPARE stmt FROM 'SELECT ? FROM DUAL'; 4527EXECUTE stmt USING TO_BASE64('xxx'); 4528? 4529eHh4 4530DEALLOCATE PREPARE stmt; 4531PREPARE stmt FROM 'SELECT ?+? FROM DUAL'; 4532EXECUTE stmt USING 10, 10 + 10; 4533?+? 453430 4535DEALLOCATE PREPARE stmt; 4536PREPARE stmt FROM 'SELECT CONCAT(?,?) FROM DUAL'; 4537EXECUTE stmt USING 'xxx', CONCAT('yyy','zzz'); 4538CONCAT(?,?) 4539xxxyyyzzz 4540DEALLOCATE PREPARE stmt; 4541# 4542# Testing disallowed expressions in USING 4543# 4544PREPARE stmt FROM 'SELECT ? FROM DUAL'; 4545EXECUTE stmt USING (SELECT 1); 4546ERROR 42000: EXECUTE..USING does not support subqueries or stored functions 4547DEALLOCATE PREPARE stmt; 4548CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test'; 4549PREPARE stmt FROM 'SELECT ? FROM DUAL'; 4550EXECUTE stmt USING f1(); 4551ERROR 42000: EXECUTE..USING does not support subqueries or stored functions 4552DEALLOCATE PREPARE stmt; 4553DROP FUNCTION f1; 4554# 4555# Testing erroneous expressions in USING 4556# 4557PREPARE stmt FROM 'SELECT ?'; 4558EXECUTE stmt USING _latin1'a'=_latin2'a'; 4559ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '=' 4560DEALLOCATE PREPARE stmt; 4561PREPARE stmt FROM 'SELECT ?'; 4562EXECUTE stmt USING ROW(1,2); 4563ERROR 21000: Operand should contain 1 column(s) 4564DEALLOCATE PREPARE stmt; 4565# 4566# Creating tables from EXECUTE parameters 4567# 4568PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT ? AS c1 FROM DUAL'; 4569EXECUTE stmt USING 10; 4570SHOW CREATE TABLE t1; 4571Table Create Table 4572t1 CREATE TABLE `t1` ( 4573 `c1` int(2) NOT NULL 4574) ENGINE=MyISAM DEFAULT CHARSET=latin1 4575DROP TABLE t1; 4576EXECUTE stmt USING 10.123; 4577SHOW CREATE TABLE t1; 4578Table Create Table 4579t1 CREATE TABLE `t1` ( 4580 `c1` decimal(5,3) NOT NULL 4581) ENGINE=MyISAM DEFAULT CHARSET=latin1 4582DROP TABLE t1; 4583EXECUTE stmt USING 10.123e0; 4584SHOW CREATE TABLE t1; 4585Table Create Table 4586t1 CREATE TABLE `t1` ( 4587 `c1` double NOT NULL 4588) ENGINE=MyISAM DEFAULT CHARSET=latin1 4589DROP TABLE t1; 4590EXECUTE stmt USING CURRENT_DATE; 4591SHOW CREATE TABLE t1; 4592Table Create Table 4593t1 CREATE TABLE `t1` ( 4594 `c1` date NOT NULL 4595) ENGINE=MyISAM DEFAULT CHARSET=latin1 4596DROP TABLE t1; 4597EXECUTE stmt USING CURRENT_TIMESTAMP; 4598SHOW CREATE TABLE t1; 4599Table Create Table 4600t1 CREATE TABLE `t1` ( 4601 `c1` datetime NOT NULL 4602) ENGINE=MyISAM DEFAULT CHARSET=latin1 4603DROP TABLE t1; 4604EXECUTE stmt USING CURRENT_TIMESTAMP(3); 4605SHOW CREATE TABLE t1; 4606Table Create Table 4607t1 CREATE TABLE `t1` ( 4608 `c1` datetime(3) NOT NULL 4609) ENGINE=MyISAM DEFAULT CHARSET=latin1 4610DROP TABLE t1; 4611EXECUTE stmt USING CURRENT_TIMESTAMP(6); 4612SHOW CREATE TABLE t1; 4613Table Create Table 4614t1 CREATE TABLE `t1` ( 4615 `c1` datetime(6) NOT NULL 4616) ENGINE=MyISAM DEFAULT CHARSET=latin1 4617DROP TABLE t1; 4618EXECUTE stmt USING CURRENT_TIME; 4619SHOW CREATE TABLE t1; 4620Table Create Table 4621t1 CREATE TABLE `t1` ( 4622 `c1` time NOT NULL 4623) ENGINE=MyISAM DEFAULT CHARSET=latin1 4624DROP TABLE t1; 4625EXECUTE stmt USING CURRENT_TIME(3); 4626SHOW CREATE TABLE t1; 4627Table Create Table 4628t1 CREATE TABLE `t1` ( 4629 `c1` time(3) NOT NULL 4630) ENGINE=MyISAM DEFAULT CHARSET=latin1 4631DROP TABLE t1; 4632EXECUTE stmt USING CURRENT_TIME(6); 4633SHOW CREATE TABLE t1; 4634Table Create Table 4635t1 CREATE TABLE `t1` ( 4636 `c1` time(6) NOT NULL 4637) ENGINE=MyISAM DEFAULT CHARSET=latin1 4638DROP TABLE t1; 4639DEALLOCATE PREPARE stmt; 4640# 4641# Using a user variable as an EXECUTE..USING out parameter 4642# 4643CREATE PROCEDURE p1(OUT a INT) 4644BEGIN 4645SET a:= 10; 4646END; 4647/ 4648SET @a=1; 4649CALL p1(@a); 4650SELECT @a; 4651@a 465210 4653SET @a=2; 4654PREPARE stmt FROM 'CALL p1(?)'; 4655EXECUTE stmt USING @a; 4656SELECT @a; 4657@a 465810 4659DROP PROCEDURE p1; 4660# 4661# Using an SP variable as an EXECUTE..USING out parameter 4662# 4663CREATE PROCEDURE p1 (OUT a INT) 4664BEGIN 4665SET a=10; 4666END; 4667/ 4668CREATE PROCEDURE p2 (OUT a INT) 4669BEGIN 4670PREPARE stmt FROM 'CALL p1(?)'; 4671EXECUTE stmt USING a; 4672END; 4673/ 4674SET @a= 1; 4675CALL p2(@a); 4676SELECT @a; 4677@a 467810 4679DROP PROCEDURE p2; 4680DROP PROCEDURE p1; 4681# 4682# Testing re-prepare on a table metadata update between PREPARE and EXECUTE 4683# 4684CREATE TABLE t1 (a INT); 4685CREATE PROCEDURE p1(a INT) 4686BEGIN 4687INSERT INTO t1 VALUES (a); 4688END; 4689/ 4690PREPARE stmt FROM 'CALL p1(?)'; 4691EXECUTE stmt USING 10; 4692SELECT * FROM t1; 4693a 469410 4695CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=NEW.a+1; 4696EXECUTE stmt USING 20; 4697SELECT * FROM t1; 4698a 469910 470021 4701DEALLOCATE PREPARE stmt; 4702DROP PROCEDURE p1; 4703DROP TABLE t1; 4704# 4705# End of MDEV-10709 Expressions as parameters to Dynamic SQL 4706# 4707# 4708# MDEV-10585 EXECUTE IMMEDIATE statement 4709# 4710EXECUTE IMMEDIATE 'SELECT 1 AS a'; 4711a 47121 4713SET @a=10; 4714EXECUTE IMMEDIATE 'SELECT ? AS a' USING @a; 4715a 471610 4717EXECUTE IMMEDIATE 'SELECT ? AS a' USING 20; 4718a 471920 4720# 4721# Erroneous queries 4722# 4723EXECUTE IMMEDIATE 'xxx'; 4724ERROR 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 'xxx' at line 1 4725EXECUTE IMMEDIATE 'SELECT 1' USING @a; 4726ERROR HY000: Incorrect arguments to EXECUTE 4727EXECUTE IMMEDIATE 'SELECT ?'; 4728ERROR HY000: Incorrect arguments to EXECUTE 4729EXECUTE IMMEDIATE 'EXECUTE IMMEDIATE "SELECT 1"'; 4730ERROR HY000: This command is not supported in the prepared statement protocol yet 4731EXECUTE IMMEDIATE 'PREPARE stmt FROM "SELECT 1"'; 4732ERROR HY000: This command is not supported in the prepared statement protocol yet 4733EXECUTE IMMEDIATE 'EXECUTE stmt'; 4734ERROR HY000: This command is not supported in the prepared statement protocol yet 4735EXECUTE IMMEDIATE 'DEALLOCATE PREPARE stmt'; 4736ERROR HY000: This command is not supported in the prepared statement protocol yet 4737EXECUTE IMMEDIATE 'SELECT ?' USING _latin1'a'=_latin2'a'; 4738ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '=' 4739EXECUTE IMMEDIATE 'SELECT ?' USING ROW(1,2); 4740ERROR 21000: Operand should contain 1 column(s) 4741# 4742# Testing disallowed expressions in USING 4743# 4744EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING (SELECT 1); 4745ERROR 42000: EXECUTE..USING does not support subqueries or stored functions 4746CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test'; 4747EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING f1(); 4748ERROR 42000: EXECUTE IMMEDIATE does not support subqueries or stored functions 4749DROP FUNCTION f1; 4750# 4751# DDL 4752# 4753EXECUTE IMMEDIATE 'CREATE TABLE t1 (a INT)'; 4754EXECUTE IMMEDIATE 'SHOW CREATE TABLE t1'; 4755Table Create Table 4756t1 CREATE TABLE `t1` ( 4757 `a` int(11) DEFAULT NULL 4758) ENGINE=MyISAM DEFAULT CHARSET=latin1 4759EXECUTE IMMEDIATE 'DROP TABLE t1'; 4760SET @stmt= 'CREATE TABLE t1 (a INT)'; 4761EXECUTE IMMEDIATE @stmt; 4762SET @stmt= 'SHOW CREATE TABLE t1'; 4763EXECUTE IMMEDIATE @stmt; 4764Table Create Table 4765t1 CREATE TABLE `t1` ( 4766 `a` int(11) DEFAULT NULL 4767) ENGINE=MyISAM DEFAULT CHARSET=latin1 4768SET @stmt= 'DROP TABLE t1'; 4769EXECUTE IMMEDIATE @stmt; 4770# 4771# DDL with parameters 4772# 4773SET @a= 10, @b= 10.1, @c= 10e0, @d='str'; 4774EXECUTE IMMEDIATE 4775'CREATE TABLE t1 AS SELECT ? AS a,? AS b,? AS c,? AS d' 4776 USING @a,@b,@c,@d; 4777SHOW CREATE TABLE t1; 4778Table Create Table 4779t1 CREATE TABLE `t1` ( 4780 `a` bigint(20) NOT NULL, 4781 `b` decimal(3,1) NOT NULL, 4782 `c` double NOT NULL, 4783 `d` tinytext NOT NULL 4784) ENGINE=MyISAM DEFAULT CHARSET=latin1 4785DROP TABLE t1; 4786EXECUTE IMMEDIATE 4787'CREATE TABLE t1 AS SELECT ? AS a,? AS b,? AS c,? AS d' 4788 USING 10, 10.1, 10e0, 'str'; 4789SHOW CREATE TABLE t1; 4790Table Create Table 4791t1 CREATE TABLE `t1` ( 4792 `a` int(2) NOT NULL, 4793 `b` decimal(3,1) NOT NULL, 4794 `c` double NOT NULL, 4795 `d` varchar(3) NOT NULL 4796) ENGINE=MyISAM DEFAULT CHARSET=latin1 4797DROP TABLE t1; 4798EXECUTE IMMEDIATE 4799'CREATE TABLE t1 AS SELECT ? AS t1,? AS t2, ? AS d1,? AS dt1, ? AS dt2' 4800 USING TIME'10:20:30', 4801TIME'10:20:30.123', 4802DATE'2001-01-01', 4803TIMESTAMP'2001-01-01 10:20:30', 4804TIMESTAMP'2001-01-01 10:20:30.123'; 4805SHOW CREATE TABLE t1; 4806Table Create Table 4807t1 CREATE TABLE `t1` ( 4808 `t1` time NOT NULL, 4809 `t2` time(3) NOT NULL, 4810 `d1` date NOT NULL, 4811 `dt1` datetime NOT NULL, 4812 `dt2` datetime(3) NOT NULL 4813) ENGINE=MyISAM DEFAULT CHARSET=latin1 4814DROP TABLE t1; 4815# 4816# Using a user variable as an EXECUTE IMMEDIATE..USING out parameter 4817# 4818CREATE PROCEDURE p1(OUT a INT) 4819BEGIN 4820SET a:= 10; 4821END; 4822/ 4823SET @a=1; 4824CALL p1(@a); 4825SELECT @a; 4826@a 482710 4828SET @a=2; 4829EXECUTE IMMEDIATE 'CALL p1(?)' USING @a; 4830SELECT @a; 4831@a 483210 4833DROP PROCEDURE p1; 4834# 4835# Using an SP variable as an EXECUTE IMMEDIATE..USING out parameter 4836# 4837CREATE PROCEDURE p1 (OUT a INT) 4838BEGIN 4839SET a=10; 4840END; 4841/ 4842CREATE PROCEDURE p2 (OUT a INT) 4843BEGIN 4844EXECUTE IMMEDIATE 'CALL p1(?)' USING a; 4845END; 4846/ 4847SET @a= 1; 4848CALL p2(@a); 4849SELECT @a; 4850@a 485110 4852DROP PROCEDURE p2; 4853DROP PROCEDURE p1; 4854# 4855# Changing user variables 4856# 4857SET @a=10; 4858EXECUTE IMMEDIATE 'SET @a=@a+1'; 4859SELECT @a; 4860@a 486111 4862# 4863# SET STATEMENT 4864# 4865SET @@max_sort_length=1024; 4866EXECUTE IMMEDIATE 'SET STATEMENT max_sort_length=1025 FOR SELECT @@max_sort_length'; 4867@@max_sort_length 48681025 4869SELECT @@max_sort_length; 4870@@max_sort_length 48711024 4872SET @@max_sort_length=DEFAULT; 4873# 4874# Similar to prepared EXECUTE, IMMEDIATE is not allowed in stored functions 4875# 4876CREATE FUNCTION f1() RETURNS INT 4877BEGIN 4878EXECUTE IMMEDIATE 'DO 1'; 4879RETURN 1; 4880END; 4881$$ 4882ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger 4883# 4884# Status variables 4885# 4886CREATE FUNCTION get_status_var(name TEXT) RETURNS INT 4887RETURN (SELECT CAST(VARIABLE_VALUE AS INT) 4888FROM INFORMATION_SCHEMA.SESSION_STATUS 4889WHERE VARIABLE_NAME=name); 4890CREATE PROCEDURE test_status_var(name TEXT) 4891BEGIN 4892SET @cnt0=get_status_var(name); 4893EXECUTE IMMEDIATE 'DO 1'; 4894SET @cnt1=get_status_var(name); 4895SELECT @cnt1-@cnt0 AS increment; 4896END; 4897$$ 4898# Note, EXECUTE IMMEDIATE does not increment COM_EXECUTE_SQL 4899# It increments COM_EXECUTE_IMMEDIATE instead. 4900CALL test_status_var('COM_EXECUTE_SQL'); 4901increment 49020 4903CALL test_status_var('COM_EXECUTE_IMMEDIATE'); 4904increment 49051 4906CALL test_status_var('COM_STMT_PREPARE'); 4907increment 49081 4909CALL test_status_var('COM_STMT_EXECUTE'); 4910increment 49111 4912CALL test_status_var('COM_STMT_CLOSE'); 4913increment 49141 4915DROP PROCEDURE test_status_var; 4916DROP FUNCTION get_status_var; 4917# 4918# End of MDEV-10585 EXECUTE IMMEDIATE statement 4919# 4920# 4921# MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions 4922# 4923# 4924# Testing erroneous and diallowed prepare source 4925# 4926EXECUTE IMMEDIATE CONCAT(_latin1'SELECT 1 AS c FROM ', _latin2 'DUAL'); 4927ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat' 4928PREPARE stmt FROM CONCAT(_latin1'SELECT 1 AS c FROM ', _latin2 'DUAL'); 4929ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat' 4930EXECUTE IMMEDIATE (SELECT 'SELECT 1'); 4931ERROR 42000: EXECUTE IMMEDIATE does not support subqueries or stored functions 4932PREPARE stmt FROM (SELECT 'SELECT 1'); 4933ERROR 42000: PREPARE..FROM does not support subqueries or stored functions 4934EXECUTE IMMEDIATE a; 4935ERROR 42S22: Unknown column 'a' in 'field list' 4936PREPARE stmt FROM a; 4937ERROR 42S22: Unknown column 'a' in 'field list' 4938EXECUTE IMMEDIATE NULL; 4939ERROR 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 'NULL' at line 1 4940PREPARE stmt FROM NULL; 4941ERROR 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 'NULL' at line 1 4942EXECUTE IMMEDIATE CONCAT(NULL); 4943ERROR 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 'NULL' at line 1 4944PREPARE stmt FROM CONCAT(NULL); 4945ERROR 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 'NULL' at line 1 4946EXECUTE IMMEDIATE ? USING 'SELECT 1'; 4947ERROR 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 '? USING 'SELECT 1'' at line 1 4948EXECUTE IMMEDIATE 10; 4949ERROR 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 '10' at line 1 4950EXECUTE IMMEDIATE TIME'10:20:30'; 4951ERROR 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 '10:20:30' at line 1 4952EXECUTE IMMEDIATE ROW('SELECT 1','SELECT 2'); 4953ERROR 21000: Operand should contain 1 column(s) 4954EXECUTE IMMEDIATE MAX('SELECT 1 AS c'); 4955ERROR HY000: Invalid use of group function 4956EXECUTE IMMEDIATE DEFAULT(a); 4957ERROR 42S22: Unknown column 'a' in 'field list' 4958EXECUTE IMMEDIATE VALUE(a); 4959ERROR 42S22: Unknown column 'a' in 'field list' 4960CREATE FUNCTION f1() RETURNS VARCHAR(64) RETURN 't1'; 4961EXECUTE IMMEDIATE f1(); 4962ERROR 42000: EXECUTE IMMEDIATE does not support subqueries or stored functions 4963PREPARE stmt FROM f1(); 4964ERROR 42000: PREPARE..FROM does not support subqueries or stored functions 4965DROP FUNCTION f1; 4966EXECUTE IMMEDIATE non_existent(); 4967ERROR 42000: EXECUTE IMMEDIATE does not support subqueries or stored functions 4968# 4969# Testing literals in prepare source 4970# 4971EXECUTE IMMEDIATE N'SELECT 1 AS c'; 4972c 49731 4974EXECUTE IMMEDIATE _latin1'SELECT 1 AS c'; 4975c 49761 4977EXECUTE IMMEDIATE 'SELECT ' '1' ' AS c' ' FROM ' 'DUAL'; 4978c 49791 4980EXECUTE IMMEDIATE 0x53454C4543542031 /*This is 'SELECT 1'*/; 49811 49821 4983# 4984# Testing user variables in prepare source 4985# 4986SET @stmt='SELECT 1 AS c FROM DUAL'; 4987EXECUTE IMMEDIATE @stmt; 4988c 49891 4990PREPARE stmt FROM @stmt; 4991EXECUTE stmt; 4992c 49931 4994DEALLOCATE PREPARE stmt; 4995SET @table_name='DUAL'; 4996EXECUTE IMMEDIATE CONCAT('SELECT 1 AS a FROM ', @table_name); 4997a 49981 4999PREPARE stmt FROM CONCAT('SELECT 1 AS a FROM ', @table_name); 5000EXECUTE stmt; 5001a 50021 5003DEALLOCATE PREPARE stmt; 5004# 5005# Testing SP parameters and variables in prepare source 5006# 5007CREATE PROCEDURE p1(table_name VARCHAR(64)) 5008BEGIN 5009EXECUTE IMMEDIATE CONCAT('SELECT 1 AS c FROM ', table_name); 5010END; 5011$$ 5012CALL p1('DUAL'); 5013c 50141 5015DROP PROCEDURE p1; 5016CREATE PROCEDURE p1() 5017BEGIN 5018DECLARE table_name VARCHAR(64) DEFAULT 'DUAL'; 5019EXECUTE IMMEDIATE CONCAT('SELECT 1 AS c FROM ', table_name); 5020END; 5021$$ 5022CALL p1(); 5023c 50241 5025DROP PROCEDURE p1; 5026# 5027# Testing complex expressions 5028# 5029EXECUTE IMMEDIATE CONVERT('SELECT 1 AS c' USING utf8); 5030c 50311 5032EXECUTE IMMEDIATE CAST('SELECT 1 AS c' AS CHAR); 5033c 50341 5035EXECUTE IMMEDIATE _latin1'SELECT 1 AS c' COLLATE latin1_bin; 5036c 50371 5038EXECUTE IMMEDIATE (((('SELECT 1 AS c')))); 5039c 50401 5041EXECUTE IMMEDIATE CASE WHEN 1>2 THEN 'SELECT 1 AS c' ELSE 'SELECT 2 AS c' END; 5042c 50432 5044EXECUTE IMMEDIATE TRIM('SELECT 1 AS c'); 5045c 50461 5047EXECUTE IMMEDIATE SUBSTRING('SELECT 1 AS c' FROM 1); 5048c 50491 5050EXECUTE IMMEDIATE COALESCE(NULL, 'SELECT 1 AS c'); 5051c 50521 5053# 5054# Testing SET STATEMENT and system variables 5055# 5056CREATE TABLE t1 (a INT); 5057SET STATEMENT max_sort_length=1025 FOR EXECUTE IMMEDIATE CONCAT('INSERT INTO t1 VALUES (', @@max_sort_length, ')'); 5058SELECT * FROM t1; 5059a 50601025 5061DROP TABLE t1; 5062# 5063# End of MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions 5064# 5065# 5066# MDEV-11360 Dynamic SQL: DEFAULT as a bind parameter 5067# 5068CREATE TABLE t1 (a INT DEFAULT 10, b INT DEFAULT NULL); 5069EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?,?)' USING DEFAULT, DEFAULT; 5070SELECT * FROM t1; 5071a b 507210 NULL 5073UPDATE t1 SET a=20, b=30; 5074SELECT * FROM t1; 5075a b 507620 30 5077EXECUTE IMMEDIATE 'UPDATE t1 SET a=?,b=?' USING DEFAULT, DEFAULT; 5078SELECT * FROM t1; 5079a b 508010 NULL 5081DROP TABLE t1; 5082CREATE TABLE t1 (a INT DEFAULT 10); 5083EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?+1)' USING DEFAULT; 5084ERROR HY000: Default/ignore value is not supported for such parameter usage 5085EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (CONCAT(?,?))' USING DEFAULT, 'test'; 5086ERROR HY000: Default/ignore value is not supported for such parameter usage 5087DROP TABLE t1; 5088CREATE TABLE t1 (a INT DEFAULT 10); 5089INSERT INTO t1 VALUES (20); 5090EXECUTE IMMEDIATE 'UPDATE t1 SET a=?+1' USING DEFAULT; 5091ERROR HY000: Default/ignore value is not supported for such parameter usage 5092EXECUTE IMMEDIATE 'UPDATE t1 SET a=CONCAT(?,?)' USING DEFAULT, 'test'; 5093ERROR HY000: Default/ignore value is not supported for such parameter usage 5094DROP TABLE t1; 5095EXECUTE IMMEDIATE 'SELECT CAST(? AS SIGNED)' USING DEFAULT; 5096ERROR HY000: Default/ignore value is not supported for such parameter usage 5097EXECUTE IMMEDIATE 'SELECT CAST(? AS DOUBLE)' USING DEFAULT; 5098ERROR HY000: Default/ignore value is not supported for such parameter usage 5099EXECUTE IMMEDIATE 'SELECT CAST(? AS CHAR)' USING DEFAULT; 5100ERROR HY000: Default/ignore value is not supported for such parameter usage 5101EXECUTE IMMEDIATE 'SELECT CAST(? AS DECIMAL(10,1))' USING DEFAULT; 5102ERROR HY000: Default/ignore value is not supported for such parameter usage 5103EXECUTE IMMEDIATE 'SELECT CAST(? AS TIME)' USING DEFAULT; 5104ERROR HY000: Default/ignore value is not supported for such parameter usage 5105EXECUTE IMMEDIATE 'SELECT CAST(? AS DATE)' USING DEFAULT; 5106ERROR HY000: Default/ignore value is not supported for such parameter usage 5107EXECUTE IMMEDIATE 'SELECT CAST(? AS DATETIME)' USING DEFAULT; 5108ERROR HY000: Default/ignore value is not supported for such parameter usage 5109EXECUTE IMMEDIATE 'SELECT ?+1' USING DEFAULT; 5110ERROR HY000: Default/ignore value is not supported for such parameter usage 5111EXECUTE IMMEDIATE 'SELECT CONCAT(?,?)' USING DEFAULT,'test'; 5112ERROR HY000: Default/ignore value is not supported for such parameter usage 5113EXECUTE IMMEDIATE 'SELECT 1 LIMIT ?' USING DEFAULT; 5114ERROR HY000: Default/ignore value is not supported for such parameter usage 5115CREATE TABLE t1 (a INT DEFAULT 10); 5116INSERT INTO t1 VALUES (1),(2),(3); 5117EXECUTE IMMEDIATE 'SELECT * FROM t1 LIMIT ?' USING DEFAULT; 5118ERROR HY000: Default/ignore value is not supported for such parameter usage 5119DROP TABLE t1; 5120# The output of this query in 'Note' is a syntactically incorrect query. 5121# But as it's never logged, it's ok. It should be human readable only. 5122EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING DEFAULT; 5123id select_type table type possible_keys key key_len ref rows filtered Extra 51241 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5125Warnings: 5126Note 1003 select default AS `?` 5127CREATE TABLE t1 (a INT); 5128INSERT INTO t1 VALUES (1),(2),(3); 5129EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING DEFAULT,DEFAULT; 5130ERROR HY000: Default/ignore value is not supported for such parameter usage 5131DROP TABLE t1; 5132# 5133# MDEV-11780 Crash with PREPARE + SP out parameter + literal 5134# 5135CREATE OR REPLACE PROCEDURE p1(OUT a INT) 5136BEGIN 5137SET a=10; 5138END; 5139$$ 5140PREPARE stmt FROM 'CALL p1(?)'; 5141EXECUTE stmt USING 10; 5142ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger 5143EXECUTE stmt USING DEFAULT; 5144ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger 5145EXECUTE stmt USING IGNORE; 5146ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger 5147DEALLOCATE PREPARE stmt; 5148EXECUTE IMMEDIATE 'CALL p1(?)' USING 10; 5149ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger 5150EXECUTE IMMEDIATE 'CALL p1(?)' USING DEFAULT; 5151ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger 5152EXECUTE IMMEDIATE 'CALL p1(?)' USING IGNORE; 5153ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger 5154DROP PROCEDURE p1; 5155# 5156# MDEV-14434 Wrong result for CHARSET(CONCAT(?,const)) 5157# 5158SET NAMES utf8; 5159EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(5,_latin1'a'))"; 5160CHARSET(CONCAT(5,_latin1'a')) 5161latin1 5162EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5; 5163CHARSET(CONCAT(?,_latin1'a')) 5164latin1 5165EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5.5; 5166CHARSET(CONCAT(?,_latin1'a')) 5167latin1 5168EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5.5e0; 5169CHARSET(CONCAT(?,_latin1'a')) 5170latin1 5171EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING TIME'10:20:30'; 5172CHARSET(CONCAT(?,_latin1'a')) 5173latin1 5174EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING TIMESTAMP'2001-01-01 10:20:30'; 5175CHARSET(CONCAT(?,_latin1'a')) 5176latin1 5177EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5; 5178COERCIBILITY(?) 51795 5180EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5.5; 5181COERCIBILITY(?) 51825 5183EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5.5e0; 5184COERCIBILITY(?) 51855 5186EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING TIME'10:20:30'; 5187COERCIBILITY(?) 51885 5189EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING TIMESTAMP'2001-01-01 10:20:30'; 5190COERCIBILITY(?) 51915 5192# 5193# MDEV-14435 Different UNSIGNED flag of out user variable for YEAR parameter for direct vs prepared CALL 5194# 5195CREATE PROCEDURE p1(OUT v INT UNSIGNED) SET v = 2010; 5196CALL p1(@a); 5197PREPARE stmt FROM 'CALL p1(?)'; 5198EXECUTE stmt USING @b; 5199DEALLOCATE PREPARE stmt; 5200CREATE TABLE t1 AS SELECT @a AS a, @b AS b; 5201SHOW CREATE TABLE t1; 5202Table Create Table 5203t1 CREATE TABLE `t1` ( 5204 `a` bigint(20) unsigned DEFAULT NULL, 5205 `b` bigint(20) unsigned DEFAULT NULL 5206) ENGINE=MyISAM DEFAULT CHARSET=latin1 5207DROP TABLE t1; 5208DROP PROCEDURE p1; 5209CREATE PROCEDURE p1(OUT v YEAR) SET v = 2010; 5210CALL p1(@a); 5211PREPARE stmt FROM 'CALL p1(?)'; 5212EXECUTE stmt USING @b; 5213DEALLOCATE PREPARE stmt; 5214CREATE TABLE t1 AS SELECT @a AS a, @b AS b; 5215SHOW CREATE TABLE t1; 5216Table Create Table 5217t1 CREATE TABLE `t1` ( 5218 `a` bigint(20) unsigned DEFAULT NULL, 5219 `b` bigint(20) unsigned DEFAULT NULL 5220) ENGINE=MyISAM DEFAULT CHARSET=latin1 5221DROP TABLE t1; 5222DROP PROCEDURE p1; 5223CREATE PROCEDURE p1(OUT v BIT(16)) SET v = 2010; 5224CALL p1(@a); 5225PREPARE stmt FROM 'CALL p1(?)'; 5226EXECUTE stmt USING @b; 5227DEALLOCATE PREPARE stmt; 5228CREATE TABLE t1 AS SELECT @a AS a, @b AS b; 5229SHOW CREATE TABLE t1; 5230Table Create Table 5231t1 CREATE TABLE `t1` ( 5232 `a` bigint(20) unsigned DEFAULT NULL, 5233 `b` bigint(20) unsigned DEFAULT NULL 5234) ENGINE=MyISAM DEFAULT CHARSET=latin1 5235DROP TABLE t1; 5236DROP PROCEDURE p1; 5237# 5238# MDEV-14426 Assertion in Diagnostics_area::set_error_status when using a bad datetime with PS and SP 5239# 5240CREATE PROCEDURE p1(OUT a VARCHAR(20)) 5241BEGIN 5242SET a=10; 5243END; 5244$$ 5245BEGIN NOT ATOMIC 5246DECLARE a DATETIME; 5247CALL p1(a); 5248END; 5249$$ 5250ERROR 22007: Incorrect datetime value: '10' for column ``.``.`a` at row 1 5251BEGIN NOT ATOMIC 5252DECLARE a DATETIME; 5253EXECUTE IMMEDIATE 'CALL p1(?)' USING a; 5254END; 5255$$ 5256ERROR 22007: Incorrect datetime value: '10' for column ``.``.`a` at row 1 5257BEGIN NOT ATOMIC 5258DECLARE a DATETIME; 5259PREPARE stmt FROM 'CALL p1(?)'; 5260EXECUTE stmt USING a; 5261DEALLOCATE PREPARE stmt; 5262END; 5263$$ 5264ERROR 22007: Incorrect datetime value: '10' for column ``.``.`a` at row 1 5265DROP PROCEDURE p1; 5266# 5267# MDEV-14454 Binary protocol returns wrong collation ID for SP OUT parameters 5268# 5269CREATE PROCEDURE p1(OUT v CHAR(32) CHARACTER SET utf8) SET v='aaa'; 5270PREPARE stmt1 FROM 'CALL p1(?)'; 5271EXECUTE stmt1 USING @a; 5272CREATE TABLE t1 AS SELECT @a AS c1; 5273SHOW CREATE TABLE t1; 5274Table Create Table 5275t1 CREATE TABLE `t1` ( 5276 `c1` longtext CHARACTER SET utf8 DEFAULT NULL 5277) ENGINE=MyISAM DEFAULT CHARSET=latin1 5278DROP TABLE t1; 5279DROP PROCEDURE p1; 5280# 5281# MDEV-14467 Item_param: replace {INT|DECIMAL|REAL|STRING|TIME}_VALUE with Type_handler 5282# 5283EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING 10; 5284id select_type table type possible_keys key key_len ref rows filtered Extra 52851 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5286Warnings: 5287Note 1003 select 1 AS `1` limit 10 5288EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING 10.1; 5289id select_type table type possible_keys key key_len ref rows filtered Extra 52901 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5291Warnings: 5292Note 1003 select 1 AS `1` limit 10 5293EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING 10.1e0; 5294id select_type table type possible_keys key key_len ref rows filtered Extra 52951 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5296Warnings: 5297Note 1003 select 1 AS `1` limit 10 5298EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING '10'; 5299id select_type table type possible_keys key key_len ref rows filtered Extra 53001 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5301Warnings: 5302Note 1003 select 1 AS `1` limit 10 5303EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING TIME'10:10:10'; 5304id select_type table type possible_keys key key_len ref rows filtered Extra 53051 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5306Warnings: 5307Note 1003 select 1 AS `1` limit 101010 5308EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 1 AS a,? AS b' USING 1; 5309SHOW CREATE TABLE t1; 5310Table Create Table 5311t1 CREATE TABLE `t1` ( 5312 `a` int(1) NOT NULL, 5313 `b` int(1) NOT NULL 5314) ENGINE=MyISAM DEFAULT CHARSET=latin1 5315DROP TABLE t1; 5316EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 10 AS a,? AS b' USING 10; 5317SHOW CREATE TABLE t1; 5318Table Create Table 5319t1 CREATE TABLE `t1` ( 5320 `a` int(2) NOT NULL, 5321 `b` int(2) NOT NULL 5322) ENGINE=MyISAM DEFAULT CHARSET=latin1 5323DROP TABLE t1; 5324EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 999999999 AS a,? AS b' USING 999999999; 5325SHOW CREATE TABLE t1; 5326Table Create Table 5327t1 CREATE TABLE `t1` ( 5328 `a` int(9) NOT NULL, 5329 `b` int(9) NOT NULL 5330) ENGINE=MyISAM DEFAULT CHARSET=latin1 5331DROP TABLE t1; 5332EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 2147483647 AS a,? AS b' USING 2147483647; 5333SHOW CREATE TABLE t1; 5334Table Create Table 5335t1 CREATE TABLE `t1` ( 5336 `a` bigint(10) NOT NULL, 5337 `b` bigint(10) NOT NULL 5338) ENGINE=MyISAM DEFAULT CHARSET=latin1 5339DROP TABLE t1; 5340# 5341# MDEV-14603 signal 11 with short stacktrace 5342# 5343SET NAMES utf8; 5344CREATE TABLE t1(i INT); 5345CREATE PROCEDURE p1(tn VARCHAR(32)) 5346EXECUTE IMMEDIATE CONCAT('ANALYZE TABLE ',tn); 5347CALL p1('t1'); 5348Table Op Msg_type Msg_text 5349test.t1 analyze status Engine-independent statistics collected 5350test.t1 analyze status Table is already up to date 5351DROP PROCEDURE p1; 5352DROP TABLE t1; 5353SET NAMES utf8; 5354CREATE PROCEDURE p1() 5355EXECUTE IMMEDIATE CONCAT('SELECT ',CONVERT(RAND() USING latin1)); 5356CALL p1(); 5357DROP PROCEDURE p1; 5358SET NAMES utf8; 5359CREATE PROCEDURE p1() 5360BEGIN 5361PREPARE stmt FROM CONCAT('SELECT ',CONVERT(RAND() USING latin1)); 5362EXECUTE stmt; 5363DEALLOCATE PREPARE stmt; 5364END; 5365$$ 5366CALL p1(); 5367DROP PROCEDURE p1; 5368SET NAMES utf8; 5369CREATE PROCEDURE p1(a VARCHAR(10) CHARACTER SET utf8) 5370EXECUTE IMMEDIATE 'SELECT ?' USING CONCAT(a, CONVERT(RAND() USING latin1)); 5371CALL p1('x'); 5372DROP PROCEDURE p1; 5373SET NAMES utf8; 5374CREATE PROCEDURE p1(a VARCHAR(10) CHARACTER SET utf8) 5375BEGIN 5376PREPARE stmt FROM 'SELECT ?'; 5377EXECUTE stmt USING CONCAT(a, CONVERT(RAND() USING latin1)); 5378DEALLOCATE PREPARE stmt; 5379END; 5380$$ 5381CALL p1('x'); 5382DROP PROCEDURE p1; 5383create table t1 (b blob default ''); 5384prepare stmt from "alter table t1 force"; 5385execute stmt; 5386execute stmt; 5387execute stmt; 5388set names latin1; 5389prepare stmt from "alter table t1 modify b text character set utf8 default 'a'"; 5390execute stmt; 5391execute stmt; 5392execute stmt; 5393drop table t1; 5394# 5395# MDEV-12060 Crash in EXECUTE IMMEDIATE with an expression returning a GRANT command 5396# 5397CREATE ROLE testrole; 5398CREATE OR REPLACE PROCEDURE p1() 5399BEGIN 5400END; 5401/ 5402CREATE PROCEDURE p2 (wgrp VARCHAR(10)) 5403BEGIN 5404EXECUTE IMMEDIATE concat('GRANT EXECUTE ON PROCEDURE p1 TO ',wgrp); 5405END; 5406/ 5407CALL p2('testrole'); 5408DROP PROCEDURE p2; 5409CREATE PROCEDURE p2 () 5410BEGIN 5411EXECUTE IMMEDIATE concat(_utf8'GRANT EXECUTE ON PROCEDURE p1 TO ',_latin1'testrole'); 5412END; 5413/ 5414CALL p2(); 5415DROP PROCEDURE p2; 5416CREATE PROCEDURE p2 () 5417BEGIN 5418PREPARE stmt FROM concat(_utf8'GRANT EXECUTE ON PROCEDURE p1 TO ',_latin1' testrole'); 5419EXECUTE stmt; 5420DEALLOCATE PREPARE stmt; 5421END; 5422/ 5423CALL p2(); 5424DROP PROCEDURE p2; 5425DROP PROCEDURE p1; 5426DROP ROLE testrole; 5427# 5428# MDEV-16992: prepare of CREATE TABLE, CREATE VIEW, DO, SET, CALL 5429# statements with CTE containing materialized derived 5430# (the bug is reproducible on 10.4) 5431# 5432prepare stmt from 5433"CREATE TABLE t1 AS 5434 WITH cte(a) AS (SELECT * FROM (SELECT 1) AS t) SELECT * FROM cte;"; 5435execute stmt; 5436select * from t1; 5437a 54381 5439prepare stmt from 5440"CREATE VIEW v1 AS 5441 WITH cte(a) AS (SELECT * FROM (SELECT 1) AS t) SELECT * FROM cte;"; 5442execute stmt; 5443select * from v1; 5444a 54451 5446prepare stmt from 5447"DO (SELECT 1 5448 FROM (WITH cte AS (SELECT * FROM (SELECT 1) AS t) 5449 SELECT * FROM cte) AS tt);"; 5450execute stmt; 5451prepare stmt from 5452"SET @a = (SELECT 1 5453 FROM (WITH cte AS (SELECT * FROM (SELECT 1) AS t) 5454 SELECT * FROM cte) AS t);"; 5455execute stmt; 5456create procedure p (i int) insert into t1 values(i); 5457prepare stmt from 5458"CALL p 5459 ((SELECT 1 5460 FROM (WITH cte AS (SELECT * FROM (SELECT 1) AS t) 5461 SELECT * FROM cte) AS tt));"; 5462execute stmt; 5463select * from t1; 5464a 54651 54661 5467drop procedure p; 5468drop view v1; 5469drop table t1; 5470# 5471# MDEV-22591 Debug build crashes on EXECUTE IMMEDIATE '... WHERE ?' USING IGNORE 5472# 5473CREATE TABLE t1 (a INT); 5474EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE ?' USING IGNORE; 5475ERROR HY000: Default/ignore value is not supported for such parameter usage 5476EXECUTE IMMEDIATE 'SELECT * FROM t1 HAVING ?' USING IGNORE; 5477ERROR HY000: Default/ignore value is not supported for such parameter usage 5478EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE ?' USING 0; 5479a 5480EXECUTE IMMEDIATE 'SELECT * FROM t1 HAVING ?' USING 0; 5481a 5482DROP TABLE t1; 5483EXECUTE IMMEDIATE 'SHOW DATABASES WHERE ?' USING DEFAULT; 5484ERROR HY000: Default/ignore value is not supported for such parameter usage 5485EXECUTE IMMEDIATE 'SHOW DATABASES WHERE ?' USING 0; 5486Database 5487# 5488# MDEV-24779: main.subselect fails in buildbot with --ps-protocol 5489# 5490CREATE TABLE t1(a INT); 5491PREPARE stmt FROM "SELECT EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1))"; 5492EXECUTE stmt; 5493EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1)) 54940 5495EXECUTE stmt; 5496EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1)) 54970 5498DROP TABLE t1; 5499# 5500# MDEV-25006: Failed assertion on executing EXPLAIN DELETE statement as a prepared statement 5501# 5502CREATE TABLE t1(c1 CHAR(255) PRIMARY KEY); 5503PREPARE stmt FROM 'EXPLAIN DELETE b FROM t1 AS a JOIN t1 AS b'; 5504EXECUTE stmt; 5505id select_type table type possible_keys key key_len ref rows Extra 55061 SIMPLE a system NULL NULL NULL NULL 0 Const row not found 55071 SIMPLE b system NULL NULL NULL NULL 0 Const row not found 5508DROP TABLE t1; 5509CREATE TABLE t1(a INT); 5510PREPARE stmt FROM 'EXPLAIN DELETE FROM t1.* USING t1'; 5511EXECUTE stmt; 5512id select_type table type possible_keys key key_len ref rows Extra 55131 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found 5514DEALLOCATE PREPARE stmt; 5515DROP TABLE t1; 5516# 5517# MDEV-25108: Running of the EXPLAIN EXTENDED statement produces extra warning 5518# in case it is executed in PS (prepared statement) mode 5519# 5520CREATE TABLE t1 (c int); 5521CREATE TABLE t2 (d int); 5522# EXPLAIN EXTENDED in regular way (not PS mode) 5523EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1; 5524id select_type table type possible_keys key key_len ref rows filtered Extra 55251 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 Const row not found 55262 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5527Warnings: 5528Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 5529Note 1003 /* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1` 5530SHOW WARNINGS; 5531Level Code Message 5532Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 5533Note 1003 /* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1` 5534# Now run the same EXPLAIN EXTENDED in PS mode. Number of warnings 5535# and their content must be the same as in case running the statement 5536# in regular way 5537PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1"; 5538Warnings: 5539Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 5540EXECUTE stmt; 5541id select_type table type possible_keys key key_len ref rows filtered Extra 55421 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 Const row not found 55432 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5544Warnings: 5545Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 5546Note 1003 /* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1` 5547SHOW WARNINGS; 5548Level Code Message 5549Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 5550Note 1003 /* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1` 5551DEALLOCATE PREPARE stmt; 5552DROP TABLE t1, t2; 5553# 5554# MDEV-25576: The statement EXPLAIN running as regular statement and 5555# as prepared statement produces different results for 5556# UPDATE with subquery 5557# 5558CREATE TABLE t1 (c1 INT KEY) ENGINE=MyISAM; 5559CREATE TABLE t2 (c2 INT) ENGINE=MyISAM; 5560CREATE TABLE t3 (c3 INT) ENGINE=MyISAM; 5561EXPLAIN EXTENDED UPDATE t3 SET c3 = ( SELECT COUNT(d1.c1) FROM ( SELECT a11.c1 FROM t1 AS a11 STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 JOIN t1 AS a12 ON a12.c1 = a11.c1 ) d1 ); 5562id select_type table type possible_keys key key_len ref rows filtered Extra 55631 PRIMARY t3 ALL NULL NULL NULL NULL 0 100.00 55642 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5565PREPARE stmt FROM "EXPLAIN EXTENDED UPDATE t3 SET c3 = ( SELECT COUNT(d1.c1) FROM ( SELECT a11.c1 FROM t1 AS a11 STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 JOIN t1 AS a12 ON a12.c1 = a11.c1 ) d1 )"; 5566EXECUTE stmt; 5567id select_type table type possible_keys key key_len ref rows filtered Extra 55681 PRIMARY t3 ALL NULL NULL NULL NULL 0 100.00 55692 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5570DEALLOCATE PREPARE stmt; 5571DROP TABLE t1, t2, t3; 5572# 5573# MDEV-21866: Assertion `!result' failed in convert_const_to_int upon 2nd execution of PS 5574# 5575CREATE TABLE t1 (a BIGINT DEFAULT -1); 5576CREATE VIEW v1 AS SELECT DISTINCT a FROM t1; 5577PREPARE stmt FROM 'SELECT * FROM v1 WHERE a <=> NULL'; 5578EXECUTE stmt; 5579a 5580EXECUTE stmt; 5581a 5582DEALLOCATE PREPARE stmt; 5583DROP VIEW v1; 5584DROP TABLE t1; 5585# End of 10.2 tests 5586# 5587# 5588# MDEV-26147: The test main.sp-row fails in case it is run in PS mode 5589# 5590CREATE PROCEDURE p1(a ROW(a INT,b INT)) 5591BEGIN 5592SELECT a.a, a.b; 5593END; 5594$$ 5595PREPARE stmt FROM 'CALL p1(ROW(10, 20))'; 5596EXECUTE stmt; 5597a.a a.b 559810 20 5599DEALLOCATE PREPARE stmt; 5600DROP PROCEDURE p1; 5601# 5602# MDEV-19263: Server crashes in mysql_handle_single_derived 5603# upon 2nd execution of PS 5604# 5605CREATE TABLE t1 (f INT); 5606CREATE VIEW v1 AS SELECT * FROM t1; 5607CREATE TRIGGER tr BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO v1 SELECT * FROM x; 5608PREPARE stmt FROM "INSERT INTO v1 VALUES (1)"; 5609EXECUTE stmt; 5610ERROR 42S02: Table 'test.x' doesn't exist 5611EXECUTE stmt; 5612ERROR 42S02: Table 'test.x' doesn't exist 5613DEALLOCATE PREPARE stmt; 5614DROP VIEW v1; 5615DROP TABLE t1; 5616# 5617# MDEV-25197: The statement set password=password('') executed in PS mode 5618# fails in case it is run by a user with expired password 5619# 5620CREATE USER user1@localhost PASSWORD EXPIRE; 5621SET @disconnect_on_expired_password_save=@@global.disconnect_on_expired_password; 5622SET GLOBAL disconnect_on_expired_password=OFF; 5623connect con1,localhost,user1; 5624connection con1; 5625# Check that no regular statement like SELECT can be prepared 5626# by a user with an expired password 5627PREPARE stmt FROM "SELECT 1"; 5628ERROR HY000: You must SET PASSWORD before executing this statement 5629# Check that the DEALLOCATE PREPARE statement can be run by a user 5630# with an expired password 5631PREPARE stmt FROM "SET password=password('')"; 5632DEALLOCATE PREPARE stmt; 5633# Check that the SET PASSWORD statement can be executed in PS mode by 5634# a user with an expired password 5635PREPARE stmt FROM "SET password=password('')"; 5636EXECUTE stmt; 5637PREPARE stmt FROM "SELECT 1"; 5638# Check that user's password is not expired anymore 5639EXECUTE stmt; 56401 56411 5642DEALLOCATE PREPARE stmt; 5643# Clean up 5644disconnect con1; 5645connection default; 5646SET GLOBAL disconnect_on_expired_password=@disconnect_on_expired_password_save; 5647DROP USER user1@localhost; 5648# 5649# End of 10.4 tests 5650# 5651