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' 2860Note 1051 Unknown table 'test.t2' 2861call proc_1(); 2862Level Code Message 2863Note 1051 Unknown table 'test.t1' 2864Note 1051 Unknown table 'test.t2' 2865drop procedure proc_1; 2866create function func_1() returns int begin show warnings; return 1; end| 2867ERROR 0A000: Not allowed to return a result set from a function 2868prepare abc from "show warnings"; 2869drop table if exists t1; 2870Warnings: 2871Note 1051 Unknown table 'test.t1' 2872execute abc; 2873Level Code Message 2874Note 1051 Unknown table 'test.t1' 2875drop table if exists t2; 2876Warnings: 2877Note 1051 Unknown table 'test.t2' 2878execute abc; 2879Level Code Message 2880Note 1051 Unknown table 'test.t2' 2881drop table if exists t1, t2; 2882Warnings: 2883Note 1051 Unknown table 'test.t1' 2884Note 1051 Unknown table 'test.t2' 2885execute abc; 2886Level Code Message 2887Note 1051 Unknown table 'test.t1' 2888Note 1051 Unknown table 'test.t2' 2889deallocate prepare abc; 2890set @my_password="password"; 2891set @my_data="clear text to encode"; 2892prepare stmt1 from 'select decode(encode(?, ?), ?)'; 2893execute stmt1 using @my_data, @my_password, @my_password; 2894decode(encode(?, ?), ?) 2895clear text to encode 2896set @my_data="more text to encode"; 2897execute stmt1 using @my_data, @my_password, @my_password; 2898decode(encode(?, ?), ?) 2899more text to encode 2900set @my_password="new password"; 2901execute stmt1 using @my_data, @my_password, @my_password; 2902decode(encode(?, ?), ?) 2903more text to encode 2904deallocate prepare stmt1; 2905set @to_format="123456789.123456789"; 2906set @dec=0; 2907prepare stmt2 from 'select format(?, ?)'; 2908execute stmt2 using @to_format, @dec; 2909format(?, ?) 2910123,456,789 2911set @dec=4; 2912execute stmt2 using @to_format, @dec; 2913format(?, ?) 2914123,456,789.1235 2915set @dec=6; 2916execute stmt2 using @to_format, @dec; 2917format(?, ?) 2918123,456,789.123457 2919set @dec=2; 2920execute stmt2 using @to_format, @dec; 2921format(?, ?) 2922123,456,789.12 2923set @to_format="100"; 2924execute stmt2 using @to_format, @dec; 2925format(?, ?) 2926100.00 2927set @to_format="1000000"; 2928execute stmt2 using @to_format, @dec; 2929format(?, ?) 29301,000,000.00 2931set @to_format="10000"; 2932execute stmt2 using @to_format, @dec; 2933format(?, ?) 293410,000.00 2935deallocate prepare stmt2; 2936DROP TABLE IF EXISTS t1, t2; 2937CREATE TABLE t1 (i INT); 2938INSERT INTO t1 VALUES (1); 2939CREATE TABLE t2 (i INT); 2940INSERT INTO t2 VALUES (2); 2941LOCK TABLE t1 READ, t2 WRITE; 2942connect conn1, localhost, root, , ; 2943PREPARE stmt1 FROM "SELECT i FROM t1"; 2944PREPARE stmt2 FROM "INSERT INTO t2 (i) VALUES (3)"; 2945EXECUTE stmt1; 2946i 29471 2948EXECUTE stmt2; 2949connection default; 2950SELECT * FROM t2; 2951i 29522 2953UNLOCK TABLES; 2954SELECT * FROM t2; 2955i 29562 29573 2958ALTER TABLE t1 ADD COLUMN j INT; 2959ALTER TABLE t2 ADD COLUMN j INT; 2960INSERT INTO t1 VALUES (4, 5); 2961INSERT INTO t2 VALUES (4, 5); 2962connection conn1; 2963EXECUTE stmt1; 2964i 29651 29664 2967EXECUTE stmt2; 2968SELECT * FROM t2; 2969i j 29702 NULL 29713 NULL 29724 5 29733 NULL 2974disconnect conn1; 2975connection default; 2976DROP TABLE t1, t2; 2977drop table if exists t1; 2978Warnings: 2979Note 1051 Unknown table 'test.t1' 2980prepare stmt 2981from "create table t1 (c char(100) character set utf8, key (c(10)))"; 2982execute stmt; 2983show create table t1; 2984Table Create Table 2985t1 CREATE TABLE `t1` ( 2986 `c` char(100) CHARACTER SET utf8 DEFAULT NULL, 2987 KEY `c` (`c`(10)) 2988) ENGINE=MyISAM DEFAULT CHARSET=latin1 2989drop table t1; 2990execute stmt; 2991show create table t1; 2992Table Create Table 2993t1 CREATE TABLE `t1` ( 2994 `c` char(100) CHARACTER SET utf8 DEFAULT NULL, 2995 KEY `c` (`c`(10)) 2996) ENGINE=MyISAM DEFAULT CHARSET=latin1 2997drop table t1; 2998drop table if exists t1, t2; 2999create table t1 (a int, b int); 3000create table t2 like t1; 3001insert into t1 (a, b) values (1,1), (1,2), (1,3), (1,4), (1,5), 3002(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); 3003insert into t2 select a, max(b) from t1 group by a; 3004prepare stmt from "delete from t2 where (select (select max(b) from t1 group 3005by a having a < 2) x from t1) > 10000"; 3006delete from t2 where (select (select max(b) from t1 group 3007by a having a < 2) x from t1) > 10000; 3008ERROR 21000: Subquery returns more than 1 row 3009execute stmt; 3010ERROR 21000: Subquery returns more than 1 row 3011execute stmt; 3012ERROR 21000: Subquery returns more than 1 row 3013deallocate prepare stmt; 3014drop table t1, t2; 3015# 3016# Bug#27430 Crash in subquery code when in PS and table DDL changed 3017# after PREPARE 3018# 3019# This part of the test doesn't work in embedded server, this is 3020# why it's here. For the main test see ps_ddl*.test 3021 3022drop table if exists t1; 3023create table t1 (a int); 3024prepare stmt from "show events where (1) in (select * from t1)"; 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; 3028create table t1 (x int); 3029execute stmt; 3030Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 3031drop table t1; 3032deallocate prepare stmt; 3033# 3034# Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0 3035# 3036prepare encode from "select encode(?, ?) into @ciphertext"; 3037prepare decode from "select decode(?, ?) into @plaintext"; 3038set @str="abc", @key="cba"; 3039execute encode using @str, @key; 3040execute decode using @ciphertext, @key; 3041select @plaintext; 3042@plaintext 3043abc 3044set @str="bcd", @key="dcb"; 3045execute encode using @str, @key; 3046execute decode using @ciphertext, @key; 3047select @plaintext; 3048@plaintext 3049bcd 3050deallocate prepare encode; 3051deallocate prepare decode; 3052# 3053# Bug#52124 memory leaks like a sieve in datetime, timestamp, time, date fields + warnings 3054# 3055CREATE TABLE t1 (a DATETIME NOT NULL, b TINYINT); 3056INSERT INTO t1 VALUES (0, 0),(0, 0); 3057PREPARE stmt FROM "SELECT 1 FROM t1 WHERE 3058ROW(a, b) >= ROW('1', (SELECT 1 FROM t1 WHERE a > 1234))"; 3059EXECUTE stmt; 30601 3061EXECUTE stmt; 30621 3063DEALLOCATE PREPARE stmt; 3064DROP TABLE t1; 3065# 3066# Bug#54494 crash with explain extended and prepared statements 3067# 3068CREATE TABLE t1(a INT); 3069INSERT INTO t1 VALUES (1),(2); 3070SET @save_optimizer_switch=@@optimizer_switch; 3071SET optimizer_switch='outer_join_with_cache=off'; 3072PREPARE stmt FROM 'EXPLAIN EXTENDED SELECT 1 FROM t1 RIGHT JOIN t1 t2 ON 1'; 3073EXECUTE stmt; 3074id select_type table type possible_keys key key_len ref rows filtered Extra 30751 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 30761 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3077Warnings: 3078Note 1003 select 1 AS `1` from `test`.`t1` `t2` left join `test`.`t1` on(1) where 1 3079EXECUTE stmt; 3080id select_type table type possible_keys key key_len ref rows filtered Extra 30811 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 30821 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3083Warnings: 3084Note 1003 select 1 AS `1` from `test`.`t1` `t2` left join `test`.`t1` on(1) where 1 3085DEALLOCATE PREPARE stmt; 3086SET optimizer_switch=@save_optimizer_switch; 3087DROP TABLE t1; 3088# 3089# Bug#54488 crash when using explain and prepared statements with subqueries 3090# 3091CREATE TABLE t1(f1 INT); 3092INSERT INTO t1 VALUES (1),(1); 3093PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 WHERE (SELECT (SELECT 1 FROM t1 GROUP BY f1))'; 3094EXECUTE stmt; 3095id select_type table type possible_keys key key_len ref rows Extra 30961 PRIMARY t1 ALL NULL NULL NULL NULL 2 30972 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 30983 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 3099EXECUTE stmt; 3100id select_type table type possible_keys key key_len ref rows Extra 31011 PRIMARY t1 ALL NULL NULL NULL NULL 2 31022 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 31033 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 3104DEALLOCATE PREPARE stmt; 3105DROP TABLE t1; 3106 3107End of 5.1 tests. 3108# 3109# lp:1001500 Crash on the second execution of the PS for 3110# a query with degenerated conjunctive condition 3111# (see also mysql bug#12582849) 3112# 3113CREATE TABLE t1 ( 3114pk INTEGER AUTO_INCREMENT, 3115col_int_nokey INTEGER, 3116col_int_key INTEGER, 3117col_varchar_key VARCHAR(1), 3118col_varchar_nokey VARCHAR(1), 3119PRIMARY KEY (pk), 3120KEY (col_int_key), 3121KEY (col_varchar_key, col_int_key) 3122); 3123INSERT INTO t1 ( 3124col_int_key, col_int_nokey, 3125col_varchar_key, col_varchar_nokey 3126) VALUES 3127(4, 2, 'v', 'v'), 3128(62, 150, 'v', 'v'); 3129CREATE TABLE t2 ( 3130pk INTEGER AUTO_INCREMENT, 3131col_int_nokey INTEGER, 3132col_int_key INTEGER, 3133col_varchar_key VARCHAR(1), 3134col_varchar_nokey VARCHAR(1), 3135PRIMARY KEY (pk), 3136KEY (col_int_key), 3137KEY (col_varchar_key, col_int_key) 3138); 3139INSERT INTO t2 ( 3140col_int_key, col_int_nokey, 3141col_varchar_key, col_varchar_nokey 3142) VALUES 3143(8, NULL, 'x', 'x'), 3144(7, 8, 'd', 'd'); 3145PREPARE stmt FROM ' 3146SELECT 3147 ( SELECT MAX( SQ1_alias2 .col_int_nokey ) AS SQ1_field1 3148 FROM ( t2 AS SQ1_alias1 RIGHT JOIN t1 AS SQ1_alias2 3149 ON ( SQ1_alias2.col_varchar_key = SQ1_alias1.col_varchar_nokey ) 3150 ) 3151 WHERE SQ1_alias2.pk < alias1.col_int_nokey OR alias1.pk 3152 ) AS field1 3153FROM ( t1 AS alias1 JOIN t2 AS alias2 ON alias2.pk ) 3154GROUP BY field1 3155'; 3156EXECUTE stmt; 3157field1 3158150 3159EXECUTE stmt; 3160field1 3161150 3162DEALLOCATE PREPARE stmt; 3163DROP TABLE t1, t2; 3164 3165# 3166# WL#4435: Support OUT-parameters in prepared statements. 3167# 3168 3169DROP PROCEDURE IF EXISTS p_string; 3170DROP PROCEDURE IF EXISTS p_double; 3171DROP PROCEDURE IF EXISTS p_int; 3172DROP PROCEDURE IF EXISTS p_decimal; 3173 3174CREATE PROCEDURE p_string( 3175IN v0 INT, 3176OUT v1 CHAR(32), 3177IN v2 CHAR(32), 3178INOUT v3 CHAR(32)) 3179BEGIN 3180SET v0 = -1; 3181SET v1 = 'test_v1'; 3182SET v2 = 'n/a'; 3183SET v3 = 'test_v3'; 3184END| 3185 3186CREATE PROCEDURE p_double( 3187IN v0 INT, 3188OUT v1 DOUBLE(4, 2), 3189IN v2 DOUBLE(4, 2), 3190INOUT v3 DOUBLE(4, 2)) 3191BEGIN 3192SET v0 = -1; 3193SET v1 = 12.34; 3194SET v2 = 98.67; 3195SET v3 = 56.78; 3196END| 3197 3198CREATE PROCEDURE p_int( 3199IN v0 CHAR(10), 3200OUT v1 INT, 3201IN v2 INT, 3202INOUT v3 INT) 3203BEGIN 3204SET v0 = 'n/a'; 3205SET v1 = 1234; 3206SET v2 = 9876; 3207SET v3 = 5678; 3208END| 3209 3210CREATE PROCEDURE p_decimal( 3211IN v0 INT, 3212OUT v1 DECIMAL(4, 2), 3213IN v2 DECIMAL(4, 2), 3214INOUT v3 DECIMAL(4, 2)) 3215BEGIN 3216SET v0 = -1; 3217SET v1 = 12.34; 3218SET v2 = 98.67; 3219SET v3 = 56.78; 3220END| 3221 3222PREPARE stmt_str FROM 'CALL p_string(?, ?, ?, ?)'; 3223PREPARE stmt_dbl FROM 'CALL p_double(?, ?, ?, ?)'; 3224PREPARE stmt_int FROM 'CALL p_int(?, ?, ?, ?)'; 3225PREPARE stmt_dec FROM 'CALL p_decimal(?, ?, ?, ?)'; 3226 3227SET @x_str_1 = NULL; 3228SET @x_str_2 = NULL; 3229SET @x_str_3 = NULL; 3230SET @x_dbl_1 = NULL; 3231SET @x_dbl_2 = NULL; 3232SET @x_dbl_3 = NULL; 3233SET @x_int_1 = NULL; 3234SET @x_int_2 = NULL; 3235SET @x_int_3 = NULL; 3236SET @x_dec_1 = NULL; 3237SET @x_dec_2 = NULL; 3238SET @x_dec_3 = NULL; 3239 3240-- Testing strings... 3241 3242EXECUTE stmt_str USING @x_int_1, @x_str_1, @x_str_2, @x_str_3; 3243SELECT @x_int_1, @x_str_1, @x_str_2, @x_str_3; 3244@x_int_1 @x_str_1 @x_str_2 @x_str_3 3245NULL test_v1 NULL test_v3 3246 3247EXECUTE stmt_str USING @x_int_1, @x_str_1, @x_str_2, @x_str_3; 3248SELECT @x_int_1, @x_str_1, @x_str_2, @x_str_3; 3249@x_int_1 @x_str_1 @x_str_2 @x_str_3 3250NULL test_v1 NULL test_v3 3251 3252-- Testing doubles... 3253 3254EXECUTE stmt_dbl USING @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; 3255SELECT @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; 3256@x_int_1 @x_dbl_1 @x_dbl_2 @x_dbl_3 3257NULL 12.34 NULL 56.78 3258 3259EXECUTE stmt_dbl USING @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; 3260SELECT @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; 3261@x_int_1 @x_dbl_1 @x_dbl_2 @x_dbl_3 3262NULL 12.34 NULL 56.78 3263 3264-- Testing ints... 3265 3266EXECUTE stmt_int USING @x_str_1, @x_int_1, @x_int_2, @x_int_3; 3267SELECT @x_str_1, @x_int_1, @x_int_2, @x_int_3; 3268@x_str_1 @x_int_1 @x_int_2 @x_int_3 3269test_v1 1234 NULL 5678 3270 3271EXECUTE stmt_int USING @x_str_1, @x_int_1, @x_int_2, @x_int_3; 3272SELECT @x_str_1, @x_int_1, @x_int_2, @x_int_3; 3273@x_str_1 @x_int_1 @x_int_2 @x_int_3 3274test_v1 1234 NULL 5678 3275 3276-- Testing decs... 3277 3278EXECUTE stmt_dec USING @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; 3279SELECT @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; 3280@x_int_1 @x_dec_1 @x_dec_2 @x_dec_3 32811234 12.34 NULL 56.78 3282 3283EXECUTE stmt_dec USING @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; 3284SELECT @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; 3285@x_int_1 @x_dec_1 @x_dec_2 @x_dec_3 32861234 12.34 NULL 56.78 3287 3288DEALLOCATE PREPARE stmt_str; 3289DEALLOCATE PREPARE stmt_dbl; 3290DEALLOCATE PREPARE stmt_int; 3291DEALLOCATE PREPARE stmt_dec; 3292 3293DROP PROCEDURE p_string; 3294DROP PROCEDURE p_double; 3295DROP PROCEDURE p_int; 3296DROP PROCEDURE p_decimal; 3297 3298DROP PROCEDURE IF EXISTS p1; 3299DROP PROCEDURE IF EXISTS p2; 3300 3301CREATE PROCEDURE p1(OUT v1 CHAR(10)) 3302SET v1 = 'test1'; 3303 3304CREATE PROCEDURE p2(OUT v2 CHAR(10)) 3305BEGIN 3306SET @query = 'CALL p1(?)'; 3307PREPARE stmt1 FROM @query; 3308EXECUTE stmt1 USING @u1; 3309DEALLOCATE PREPARE stmt1; 3310SET v2 = @u1; 3311END| 3312 3313CALL p2(@a); 3314SELECT @a; 3315@a 3316test1 3317 3318DROP PROCEDURE p1; 3319DROP PROCEDURE p2; 3320 3321TINYINT 3322 3323CREATE PROCEDURE p1(OUT v TINYINT) 3324SET v = 127; 3325PREPARE stmt1 FROM 'CALL p1(?)'; 3326EXECUTE stmt1 USING @a; 3327CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3328SHOW CREATE TABLE tmp1; 3329Table Create Table 3330tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3331 `c1` bigint(20) DEFAULT NULL 3332) ENGINE=MyISAM DEFAULT CHARSET=latin1 3333SELECT @a, @a = 127; 3334@a @a = 127 3335127 1 3336DROP TEMPORARY TABLE tmp1; 3337DROP PROCEDURE p1; 3338 3339SMALLINT 3340 3341CREATE PROCEDURE p1(OUT v SMALLINT) 3342SET v = 32767; 3343PREPARE stmt1 FROM 'CALL p1(?)'; 3344EXECUTE stmt1 USING @a; 3345CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3346SHOW CREATE TABLE tmp1; 3347Table Create Table 3348tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3349 `c1` bigint(20) DEFAULT NULL 3350) ENGINE=MyISAM DEFAULT CHARSET=latin1 3351SELECT @a, @a = 32767; 3352@a @a = 32767 335332767 1 3354DROP TEMPORARY TABLE tmp1; 3355DROP PROCEDURE p1; 3356 3357MEDIUMINT 3358 3359CREATE PROCEDURE p1(OUT v MEDIUMINT) 3360SET v = 8388607; 3361PREPARE stmt1 FROM 'CALL p1(?)'; 3362EXECUTE stmt1 USING @a; 3363CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3364SHOW CREATE TABLE tmp1; 3365Table Create Table 3366tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3367 `c1` bigint(20) DEFAULT NULL 3368) ENGINE=MyISAM DEFAULT CHARSET=latin1 3369SELECT @a, @a = 8388607; 3370@a @a = 8388607 33718388607 1 3372DROP TEMPORARY TABLE tmp1; 3373DROP PROCEDURE p1; 3374 3375INT 3376 3377CREATE PROCEDURE p1(OUT v INT) 3378SET v = 2147483647; 3379PREPARE stmt1 FROM 'CALL p1(?)'; 3380EXECUTE stmt1 USING @a; 3381CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3382SHOW CREATE TABLE tmp1; 3383Table Create Table 3384tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3385 `c1` bigint(20) DEFAULT NULL 3386) ENGINE=MyISAM DEFAULT CHARSET=latin1 3387SELECT @a, @a = 2147483647; 3388@a @a = 2147483647 33892147483647 1 3390DROP TEMPORARY TABLE tmp1; 3391DROP PROCEDURE p1; 3392 3393BIGINT 3394 3395CREATE PROCEDURE p1(OUT v BIGINT) 3396SET v = 9223372036854775807; 3397PREPARE stmt1 FROM 'CALL p1(?)'; 3398EXECUTE stmt1 USING @a; 3399CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3400SHOW CREATE TABLE tmp1; 3401Table Create Table 3402tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3403 `c1` bigint(20) DEFAULT NULL 3404) ENGINE=MyISAM DEFAULT CHARSET=latin1 3405SELECT @a, @a = 9223372036854775807; 3406@a @a = 9223372036854775807 34079223372036854775807 1 3408DROP TEMPORARY TABLE tmp1; 3409DROP PROCEDURE p1; 3410 3411BIT(11) 3412 3413CREATE PROCEDURE p1(OUT v BIT(11)) 3414SET v = b'10100100101'; 3415PREPARE stmt1 FROM 'CALL p1(?)'; 3416EXECUTE stmt1 USING @a; 3417CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3418SHOW CREATE TABLE tmp1; 3419Table Create Table 3420tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3421 `c1` bigint(20) unsigned DEFAULT NULL 3422) ENGINE=MyISAM DEFAULT CHARSET=latin1 3423SELECT @a, @a = b'10100100101'; 3424@a @a = b'10100100101' 34251317 1 3426DROP TEMPORARY TABLE tmp1; 3427DROP PROCEDURE p1; 3428 3429TIMESTAMP 3430 3431CREATE PROCEDURE p1(OUT v TIMESTAMP) 3432SET v = '2007-11-18 15:01:02'; 3433PREPARE stmt1 FROM 'CALL p1(?)'; 3434EXECUTE stmt1 USING @a; 3435CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3436SHOW CREATE TABLE tmp1; 3437Table Create Table 3438tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3439 `c1` longblob DEFAULT NULL 3440) ENGINE=MyISAM DEFAULT CHARSET=latin1 3441SELECT @a, @a = '2007-11-18 15:01:02'; 3442@a @a = '2007-11-18 15:01:02' 34432007-11-18 15:01:02 1 3444DROP TEMPORARY TABLE tmp1; 3445DROP PROCEDURE p1; 3446 3447DATETIME 3448 3449CREATE PROCEDURE p1(OUT v DATETIME) 3450SET v = '1234-11-12 12:34:59'; 3451PREPARE stmt1 FROM 'CALL p1(?)'; 3452EXECUTE stmt1 USING @a; 3453CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3454SHOW CREATE TABLE tmp1; 3455Table Create Table 3456tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3457 `c1` longblob DEFAULT NULL 3458) ENGINE=MyISAM DEFAULT CHARSET=latin1 3459SELECT @a, @a = '1234-11-12 12:34:59'; 3460@a @a = '1234-11-12 12:34:59' 34611234-11-12 12:34:59 1 3462DROP TEMPORARY TABLE tmp1; 3463DROP PROCEDURE p1; 3464 3465TIME 3466 3467CREATE PROCEDURE p1(OUT v TIME) 3468SET v = '123:45:01'; 3469PREPARE stmt1 FROM 'CALL p1(?)'; 3470EXECUTE stmt1 USING @a; 3471CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3472SHOW CREATE TABLE tmp1; 3473Table Create Table 3474tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3475 `c1` longblob DEFAULT NULL 3476) ENGINE=MyISAM DEFAULT CHARSET=latin1 3477SELECT @a, @a = '123:45:01'; 3478@a @a = '123:45:01' 3479123:45:01 1 3480DROP TEMPORARY TABLE tmp1; 3481DROP PROCEDURE p1; 3482 3483DATE 3484 3485CREATE PROCEDURE p1(OUT v DATE) 3486SET v = '1234-11-12'; 3487PREPARE stmt1 FROM 'CALL p1(?)'; 3488EXECUTE stmt1 USING @a; 3489CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3490SHOW CREATE TABLE tmp1; 3491Table Create Table 3492tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3493 `c1` longblob DEFAULT NULL 3494) ENGINE=MyISAM DEFAULT CHARSET=latin1 3495SELECT @a, @a = '1234-11-12'; 3496@a @a = '1234-11-12' 34971234-11-12 1 3498DROP TEMPORARY TABLE tmp1; 3499DROP PROCEDURE p1; 3500 3501YEAR 3502 3503CREATE PROCEDURE p1(OUT v YEAR) 3504SET v = 2010; 3505PREPARE stmt1 FROM 'CALL p1(?)'; 3506EXECUTE stmt1 USING @a; 3507CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3508SHOW CREATE TABLE tmp1; 3509Table Create Table 3510tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3511 `c1` bigint(20) unsigned DEFAULT NULL 3512) ENGINE=MyISAM DEFAULT CHARSET=latin1 3513SELECT @a, @a = 2010; 3514@a @a = 2010 35152010 1 3516DROP TEMPORARY TABLE tmp1; 3517DROP PROCEDURE p1; 3518 3519FLOAT(7, 4) 3520 3521CREATE PROCEDURE p1(OUT v FLOAT(7, 4)) 3522SET v = 123.4567; 3523PREPARE stmt1 FROM 'CALL p1(?)'; 3524EXECUTE stmt1 USING @a; 3525CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3526SHOW CREATE TABLE tmp1; 3527Table Create Table 3528tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3529 `c1` double DEFAULT NULL 3530) ENGINE=MyISAM DEFAULT CHARSET=latin1 3531SELECT @a, @a - 123.4567 < 0.00001; 3532@a @a - 123.4567 < 0.00001 3533123.45670318603516 1 3534DROP TEMPORARY TABLE tmp1; 3535DROP PROCEDURE p1; 3536 3537DOUBLE(8, 5) 3538 3539CREATE PROCEDURE p1(OUT v DOUBLE(8, 5)) 3540SET v = 123.45678; 3541PREPARE stmt1 FROM 'CALL p1(?)'; 3542EXECUTE stmt1 USING @a; 3543CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3544SHOW CREATE TABLE tmp1; 3545Table Create Table 3546tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3547 `c1` double DEFAULT NULL 3548) ENGINE=MyISAM DEFAULT CHARSET=latin1 3549SELECT @a, @a - 123.45678 < 0.000001; 3550@a @a - 123.45678 < 0.000001 3551123.45678 1 3552DROP TEMPORARY TABLE tmp1; 3553DROP PROCEDURE p1; 3554 3555DECIMAL(9, 6) 3556 3557CREATE PROCEDURE p1(OUT v DECIMAL(9, 6)) 3558SET v = 123.456789; 3559PREPARE stmt1 FROM 'CALL p1(?)'; 3560EXECUTE stmt1 USING @a; 3561CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3562SHOW CREATE TABLE tmp1; 3563Table Create Table 3564tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3565 `c1` decimal(65,38) DEFAULT NULL 3566) ENGINE=MyISAM DEFAULT CHARSET=latin1 3567SELECT @a, @a = 123.456789; 3568@a @a = 123.456789 3569123.456789 1 3570DROP TEMPORARY TABLE tmp1; 3571DROP PROCEDURE p1; 3572 3573CHAR(32) 3574 3575CREATE PROCEDURE p1(OUT v CHAR(32)) 3576SET v = REPEAT('a', 16); 3577PREPARE stmt1 FROM 'CALL p1(?)'; 3578EXECUTE stmt1 USING @a; 3579CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3580SHOW CREATE TABLE tmp1; 3581Table Create Table 3582tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3583 `c1` longtext DEFAULT NULL 3584) ENGINE=MyISAM DEFAULT CHARSET=latin1 3585SELECT @a, @a = REPEAT('a', 16); 3586@a @a = REPEAT('a', 16) 3587aaaaaaaaaaaaaaaa 1 3588DROP TEMPORARY TABLE tmp1; 3589DROP PROCEDURE p1; 3590 3591VARCHAR(32) 3592 3593CREATE PROCEDURE p1(OUT v VARCHAR(32)) 3594SET v = REPEAT('b', 16); 3595PREPARE stmt1 FROM 'CALL p1(?)'; 3596EXECUTE stmt1 USING @a; 3597CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3598SHOW CREATE TABLE tmp1; 3599Table Create Table 3600tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3601 `c1` longtext DEFAULT NULL 3602) ENGINE=MyISAM DEFAULT CHARSET=latin1 3603SELECT @a, @a = REPEAT('b', 16); 3604@a @a = REPEAT('b', 16) 3605bbbbbbbbbbbbbbbb 1 3606DROP TEMPORARY TABLE tmp1; 3607DROP PROCEDURE p1; 3608 3609TINYTEXT 3610 3611CREATE PROCEDURE p1(OUT v TINYTEXT) 3612SET v = REPEAT('c', 16); 3613PREPARE stmt1 FROM 'CALL p1(?)'; 3614EXECUTE stmt1 USING @a; 3615CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3616SHOW CREATE TABLE tmp1; 3617Table Create Table 3618tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3619 `c1` longtext DEFAULT NULL 3620) ENGINE=MyISAM DEFAULT CHARSET=latin1 3621SELECT @a, @a = REPEAT('c', 16); 3622@a @a = REPEAT('c', 16) 3623cccccccccccccccc 1 3624DROP TEMPORARY TABLE tmp1; 3625DROP PROCEDURE p1; 3626 3627TEXT 3628 3629CREATE PROCEDURE p1(OUT v TEXT) 3630SET v = REPEAT('d', 16); 3631PREPARE stmt1 FROM 'CALL p1(?)'; 3632EXECUTE stmt1 USING @a; 3633CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3634SHOW CREATE TABLE tmp1; 3635Table Create Table 3636tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3637 `c1` longtext DEFAULT NULL 3638) ENGINE=MyISAM DEFAULT CHARSET=latin1 3639SELECT @a, @a = REPEAT('d', 16); 3640@a @a = REPEAT('d', 16) 3641dddddddddddddddd 1 3642DROP TEMPORARY TABLE tmp1; 3643DROP PROCEDURE p1; 3644 3645MEDIUMTEXT 3646 3647CREATE PROCEDURE p1(OUT v MEDIUMTEXT) 3648SET v = REPEAT('e', 16); 3649PREPARE stmt1 FROM 'CALL p1(?)'; 3650EXECUTE stmt1 USING @a; 3651CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3652SHOW CREATE TABLE tmp1; 3653Table Create Table 3654tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3655 `c1` longtext DEFAULT NULL 3656) ENGINE=MyISAM DEFAULT CHARSET=latin1 3657SELECT @a, @a = REPEAT('e', 16); 3658@a @a = REPEAT('e', 16) 3659eeeeeeeeeeeeeeee 1 3660DROP TEMPORARY TABLE tmp1; 3661DROP PROCEDURE p1; 3662 3663LONGTEXT 3664 3665CREATE PROCEDURE p1(OUT v LONGTEXT) 3666SET v = REPEAT('f', 16); 3667PREPARE stmt1 FROM 'CALL p1(?)'; 3668EXECUTE stmt1 USING @a; 3669CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3670SHOW CREATE TABLE tmp1; 3671Table Create Table 3672tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3673 `c1` longtext DEFAULT NULL 3674) ENGINE=MyISAM DEFAULT CHARSET=latin1 3675SELECT @a, @a = REPEAT('f', 16); 3676@a @a = REPEAT('f', 16) 3677ffffffffffffffff 1 3678DROP TEMPORARY TABLE tmp1; 3679DROP PROCEDURE p1; 3680 3681BINARY(32) 3682 3683CREATE PROCEDURE p1(OUT v BINARY(32)) 3684SET v = REPEAT('g', 32); 3685PREPARE stmt1 FROM 'CALL p1(?)'; 3686EXECUTE stmt1 USING @a; 3687CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3688SHOW CREATE TABLE tmp1; 3689Table Create Table 3690tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3691 `c1` longblob DEFAULT NULL 3692) ENGINE=MyISAM DEFAULT CHARSET=latin1 3693SELECT @a, @a = REPEAT('g', 32); 3694@a @a = REPEAT('g', 32) 3695gggggggggggggggggggggggggggggggg 1 3696DROP TEMPORARY TABLE tmp1; 3697DROP PROCEDURE p1; 3698 3699VARBINARY(32) 3700 3701CREATE PROCEDURE p1(OUT v VARBINARY(32)) 3702SET v = REPEAT('h', 16); 3703PREPARE stmt1 FROM 'CALL p1(?)'; 3704EXECUTE stmt1 USING @a; 3705CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3706SHOW CREATE TABLE tmp1; 3707Table Create Table 3708tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3709 `c1` longblob DEFAULT NULL 3710) ENGINE=MyISAM DEFAULT CHARSET=latin1 3711SELECT @a, @a = REPEAT('h', 16); 3712@a @a = REPEAT('h', 16) 3713hhhhhhhhhhhhhhhh 1 3714DROP TEMPORARY TABLE tmp1; 3715DROP PROCEDURE p1; 3716 3717TINYBLOB 3718 3719CREATE PROCEDURE p1(OUT v TINYBLOB) 3720SET v = REPEAT('i', 16); 3721PREPARE stmt1 FROM 'CALL p1(?)'; 3722EXECUTE stmt1 USING @a; 3723CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3724SHOW CREATE TABLE tmp1; 3725Table Create Table 3726tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3727 `c1` longblob DEFAULT NULL 3728) ENGINE=MyISAM DEFAULT CHARSET=latin1 3729SELECT @a, @a = REPEAT('i', 16); 3730@a @a = REPEAT('i', 16) 3731iiiiiiiiiiiiiiii 1 3732DROP TEMPORARY TABLE tmp1; 3733DROP PROCEDURE p1; 3734 3735BLOB 3736 3737CREATE PROCEDURE p1(OUT v BLOB) 3738SET v = REPEAT('j', 16); 3739PREPARE stmt1 FROM 'CALL p1(?)'; 3740EXECUTE stmt1 USING @a; 3741CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3742SHOW CREATE TABLE tmp1; 3743Table Create Table 3744tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3745 `c1` longblob DEFAULT NULL 3746) ENGINE=MyISAM DEFAULT CHARSET=latin1 3747SELECT @a, @a = REPEAT('j', 16); 3748@a @a = REPEAT('j', 16) 3749jjjjjjjjjjjjjjjj 1 3750DROP TEMPORARY TABLE tmp1; 3751DROP PROCEDURE p1; 3752 3753MEDIUMBLOB 3754 3755CREATE PROCEDURE p1(OUT v MEDIUMBLOB) 3756SET v = REPEAT('k', 16); 3757PREPARE stmt1 FROM 'CALL p1(?)'; 3758EXECUTE stmt1 USING @a; 3759CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3760SHOW CREATE TABLE tmp1; 3761Table Create Table 3762tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3763 `c1` longblob DEFAULT NULL 3764) ENGINE=MyISAM DEFAULT CHARSET=latin1 3765SELECT @a, @a = REPEAT('k', 16); 3766@a @a = REPEAT('k', 16) 3767kkkkkkkkkkkkkkkk 1 3768DROP TEMPORARY TABLE tmp1; 3769DROP PROCEDURE p1; 3770 3771LONGBLOB 3772 3773CREATE PROCEDURE p1(OUT v LONGBLOB) 3774SET v = REPEAT('l', 16); 3775PREPARE stmt1 FROM 'CALL p1(?)'; 3776EXECUTE stmt1 USING @a; 3777CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3778SHOW CREATE TABLE tmp1; 3779Table Create Table 3780tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3781 `c1` longblob DEFAULT NULL 3782) ENGINE=MyISAM DEFAULT CHARSET=latin1 3783SELECT @a, @a = REPEAT('l', 16); 3784@a @a = REPEAT('l', 16) 3785llllllllllllllll 1 3786DROP TEMPORARY TABLE tmp1; 3787DROP PROCEDURE p1; 3788 3789SET('aaa', 'bbb') 3790 3791CREATE PROCEDURE p1(OUT v SET('aaa', 'bbb')) 3792SET v = 'aaa'; 3793PREPARE stmt1 FROM 'CALL p1(?)'; 3794EXECUTE stmt1 USING @a; 3795CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3796SHOW CREATE TABLE tmp1; 3797Table Create Table 3798tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3799 `c1` longtext DEFAULT NULL 3800) ENGINE=MyISAM DEFAULT CHARSET=latin1 3801SELECT @a, @a = 'aaa'; 3802@a @a = 'aaa' 3803aaa 1 3804DROP TEMPORARY TABLE tmp1; 3805DROP PROCEDURE p1; 3806 3807ENUM('aaa', 'bbb') 3808 3809CREATE PROCEDURE p1(OUT v ENUM('aaa', 'bbb')) 3810SET v = 'aaa'; 3811PREPARE stmt1 FROM 'CALL p1(?)'; 3812EXECUTE stmt1 USING @a; 3813CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; 3814SHOW CREATE TABLE tmp1; 3815Table Create Table 3816tmp1 CREATE TEMPORARY TABLE `tmp1` ( 3817 `c1` longtext DEFAULT NULL 3818) ENGINE=MyISAM DEFAULT CHARSET=latin1 3819SELECT @a, @a = 'aaa'; 3820@a @a = 'aaa' 3821aaa 1 3822DROP TEMPORARY TABLE tmp1; 3823DROP PROCEDURE p1; 3824 3825# End of WL#4435. 3826# 3827# WL#4284: Transactional DDL locking 3828# 3829DROP TABLE IF EXISTS t1; 3830CREATE TABLE t1 (a INT); 3831BEGIN; 3832SELECT * FROM t1; 3833a 3834# Test that preparing a CREATE TABLE does not take a exclusive metdata lock. 3835PREPARE stmt1 FROM "CREATE TABLE t1 AS SELECT 1"; 3836EXECUTE stmt1; 3837ERROR 42S01: Table 't1' already exists 3838DEALLOCATE PREPARE stmt1; 3839DROP TABLE t1; 3840# 3841# WL#4284: Transactional DDL locking 3842# 3843# Test that metadata locks taken during prepare are released. 3844# 3845connect con1,localhost,root,,; 3846connection default; 3847DROP TABLE IF EXISTS t1; 3848CREATE TABLE t1 (a INT); 3849connection con1; 3850BEGIN; 3851PREPARE stmt1 FROM "SELECT * FROM t1"; 3852connection default; 3853DROP TABLE t1; 3854disconnect con1; 3855 3856# 3857# Bug#56115: invalid memory reads when PS selecting from 3858# information_schema tables 3859# Bug#58701: crash in Field::make_field, cursor-protocol 3860# 3861# NOTE: MTR should be run both with --ps-protocol and --cursor-protocol. 3862# 3863 3864SELECT * 3865FROM (SELECT 1 UNION SELECT 2) t; 38661 38671 38682 3869 3870# Bug#13805127: Stored program cache produces wrong result in same THD 3871 3872PREPARE s1 FROM 3873" 3874SELECT c1, t2.c2, count(c3) 3875FROM 3876 ( 3877 SELECT 3 as c2 FROM dual WHERE @x = 1 3878 UNION 3879 SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 3880 ) AS t1, 3881 ( 3882 SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual 3883 UNION 3884 SELECT '2012-03-01 02:00:00', 3, 2 FROM dual 3885 UNION 3886 SELECT '2012-03-01 01:00:00', 2, 1 FROM dual 3887 ) AS t2 3888WHERE t2.c2 = t1.c2 3889GROUP BY c1, c2 3890"; 3891 3892SET @x = 1; 3893SELECT c1, t2.c2, count(c3) 3894FROM 3895( 3896SELECT 3 as c2 FROM dual WHERE @x = 1 3897UNION 3898SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 3899) AS t1, 3900( 3901SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual 3902UNION 3903SELECT '2012-03-01 02:00:00', 3, 2 FROM dual 3904UNION 3905SELECT '2012-03-01 01:00:00', 2, 1 FROM dual 3906) AS t2 3907WHERE t2.c2 = t1.c2 3908GROUP BY c1, c2; 3909c1 c2 count(c3) 39102012-03-01 01:00:00 2 1 39112012-03-01 01:00:00 3 1 39122012-03-01 02:00:00 3 1 3913 3914EXECUTE s1; 3915c1 c2 count(c3) 39162012-03-01 01:00:00 2 1 39172012-03-01 01:00:00 3 1 39182012-03-01 02:00:00 3 1 3919 3920SET @x = 2; 3921SELECT c1, t2.c2, count(c3) 3922FROM 3923( 3924SELECT 3 as c2 FROM dual WHERE @x = 1 3925UNION 3926SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 3927) AS t1, 3928( 3929SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual 3930UNION 3931SELECT '2012-03-01 02:00:00', 3, 2 FROM dual 3932UNION 3933SELECT '2012-03-01 01:00:00', 2, 1 FROM dual 3934) AS t2 3935WHERE t2.c2 = t1.c2 3936GROUP BY c1, c2; 3937c1 c2 count(c3) 39382012-03-01 01:00:00 2 1 3939 3940EXECUTE s1; 3941c1 c2 count(c3) 39422012-03-01 01:00:00 2 1 3943 3944SET @x = 1; 3945SELECT c1, t2.c2, count(c3) 3946FROM 3947( 3948SELECT 3 as c2 FROM dual WHERE @x = 1 3949UNION 3950SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 3951) AS t1, 3952( 3953SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual 3954UNION 3955SELECT '2012-03-01 02:00:00', 3, 2 FROM dual 3956UNION 3957SELECT '2012-03-01 01:00:00', 2, 1 FROM dual 3958) AS t2 3959WHERE t2.c2 = t1.c2 3960GROUP BY c1, c2; 3961c1 c2 count(c3) 39622012-03-01 01:00:00 2 1 39632012-03-01 01:00:00 3 1 39642012-03-01 02:00:00 3 1 3965 3966EXECUTE s1; 3967c1 c2 count(c3) 39682012-03-01 01:00:00 2 1 39692012-03-01 01:00:00 3 1 39702012-03-01 02:00:00 3 1 3971DEALLOCATE PREPARE s1; 3972prepare stmt from "select date('2010-10-10') between '2010-09-09' and ?"; 3973set @a='2010-11-11'; 3974execute stmt using @a; 3975date('2010-10-10') between '2010-09-09' and ? 39761 3977execute stmt using @a; 3978date('2010-10-10') between '2010-09-09' and ? 39791 3980set @a='2010-08-08'; 3981execute stmt using @a; 3982date('2010-10-10') between '2010-09-09' and ? 39830 3984execute stmt using @a; 3985date('2010-10-10') between '2010-09-09' and ? 39860 3987# 3988# Bug #892725: look-up is changed for a full scan when executing PS 3989# 3990create table t1 (a int primary key, b int); 3991insert into t1 values 3992(7,70), (3,40), (4,40), (8,70), (1,70), (9,50), (2,70); 3993prepare st from 'select * from t1 where a=8'; 3994flush status; 3995execute st; 3996a b 39978 70 3998show status like '%Handler_read%'; 3999Variable_name Value 4000Handler_read_first 0 4001Handler_read_key 1 4002Handler_read_last 0 4003Handler_read_next 0 4004Handler_read_prev 0 4005Handler_read_retry 0 4006Handler_read_rnd 0 4007Handler_read_rnd_deleted 0 4008Handler_read_rnd_next 0 4009flush status; 4010execute st; 4011a b 40128 70 4013show status like '%Handler_read%'; 4014Variable_name Value 4015Handler_read_first 0 4016Handler_read_key 1 4017Handler_read_last 0 4018Handler_read_next 0 4019Handler_read_prev 0 4020Handler_read_retry 0 4021Handler_read_rnd 0 4022Handler_read_rnd_deleted 0 4023Handler_read_rnd_next 0 4024flush status; 4025select * from t1 use index() where a=3; 4026a b 40273 40 4028show status like '%Handler_read%'; 4029Variable_name Value 4030Handler_read_first 0 4031Handler_read_key 0 4032Handler_read_last 0 4033Handler_read_next 0 4034Handler_read_prev 0 4035Handler_read_retry 0 4036Handler_read_rnd 0 4037Handler_read_rnd_deleted 0 4038Handler_read_rnd_next 8 4039flush status; 4040execute st; 4041a b 40428 70 4043show status like '%Handler_read%'; 4044Variable_name Value 4045Handler_read_first 0 4046Handler_read_key 1 4047Handler_read_last 0 4048Handler_read_next 0 4049Handler_read_prev 0 4050Handler_read_retry 0 4051Handler_read_rnd 0 4052Handler_read_rnd_deleted 0 4053Handler_read_rnd_next 0 4054deallocate prepare st; 4055drop table t1; 4056# 4057# Bug mdev-5410: crash at the execution of PS with subselect 4058# formed by UNION with global ORDER BY 4059# 4060CREATE TABLE t1 (a int DEFAULT NULL); 4061INSERT INTO t1 VALUES (2), (4); 4062CREATE TABLE t2 (b int DEFAULT NULL); 4063INSERT INTO t2 VALUES (1), (3); 4064PREPARE stmt FROM " 4065SELECT c1 FROM (SELECT (SELECT a FROM t1 WHERE t1.a <= t2.b 4066 UNION ALL 4067 SELECT a FROM t1 WHERE t1.a+3<= t2.b 4068 ORDER BY a DESC) AS c1 FROM t2) t3; 4069"; 4070EXECUTE stmt; 4071c1 4072NULL 40732 4074EXECUTE stmt; 4075c1 4076NULL 40772 4078DROP TABLE t1,t2; 4079# 4080# MDEV-5369: Wrong result (0 instead of NULL) on 2nd execution of 4081# PS with LEFT JOIN, TEMPTABLE view 4082# 4083CREATE TABLE t1 (a INT) ENGINE=MyISAM; 4084INSERT INTO t1 VALUES (0),(8); 4085CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=MyISAM; 4086CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; 4087SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk; 4088SUM(pk) 4089NULL 4090PREPARE stmt FROM "SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk"; 4091EXECUTE stmt; 4092SUM(pk) 4093NULL 4094EXECUTE stmt; 4095SUM(pk) 4096NULL 4097DEALLOCATE PREPARE stmt; 4098DROP VIEW v2; 4099DROP TABLE t1, t2; 4100# End of 5.3 tests 4101# 4102# MDEV-5505: Assertion `! is_set()' fails on PREPARE SELECT 4103# with out of range in GROUP BY 4104# 4105CREATE TABLE t1 (a INT); 4106PREPARE stmt FROM "SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1"; 4107ERROR 22003: BIGINT UNSIGNED value is out of range in '18446744073709551615 + 1' 4108SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1; 4109ERROR 22003: BIGINT UNSIGNED value is out of range in '18446744073709551615 + 1' 4110drop table t1; 4111# End of 5.3 tests 4112# 4113# MDEV-8756: MariaDB 10.0.21 crashes during PREPARE 4114# 4115CREATE TABLE t1 ( id INT(10), value INT(10) ); 4116CREATE TABLE t2 ( id INT(10) ); 4117SET @save_sql_mode= @@sql_mode; 4118SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY'; 4119PREPARE stmt FROM 'UPDATE t1 t1 SET value = (SELECT 1 FROM t2 WHERE id = t1.id)'; 4120execute stmt; 4121insert into t1 values (1,10),(2,10),(3,10); 4122insert into t2 values (1),(2); 4123execute stmt; 4124select * from t1; 4125id value 41261 1 41272 1 41283 NULL 4129deallocate prepare stmt; 4130SET SESSION sql_mode = @save_sql_mode; 4131DROP TABLE t1,t2; 4132# 4133# MDEV-8833: Crash of server on prepared statement with 4134# conversion to semi-join 4135# 4136CREATE TABLE t1 (column1 INT); 4137INSERT INTO t1 VALUES (3),(9); 4138CREATE TABLE t2 (column2 INT); 4139INSERT INTO t2 VALUES (1),(4); 4140CREATE TABLE t3 (column3 INT); 4141INSERT INTO t3 VALUES (6),(8); 4142CREATE TABLE t4 (column4 INT); 4143INSERT INTO t4 VALUES (2),(5); 4144PREPARE stmt FROM "SELECT ( SELECT MAX( table1.column1 ) AS field1 4145FROM t1 AS table1 4146WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) 4147) AS sq 4148FROM t3 AS table3, t4 AS table4"; 4149EXECUTE stmt; 4150sq 4151NULL 4152NULL 4153NULL 4154NULL 4155EXECUTE stmt; 4156sq 4157NULL 4158NULL 4159NULL 4160NULL 4161deallocate prepare stmt; 4162drop table t1,t2,t3,t4; 4163# 4164# MDEV-11859: the plans for the first and the second executions 4165# of PS are not the same 4166# 4167create table t1 (id int, c varchar(3), key idx(c))engine=myisam; 4168insert into t1 values (3,'bar'), (1,'xxx'), (2,'foo'), (5,'yyy'); 4169prepare stmt1 from 4170"explain extended 4171 select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'"; 4172execute stmt1; 4173id select_type table type possible_keys key key_len ref rows filtered Extra 41741 PRIMARY t1 ref idx idx 6 const 1 100.00 Using index condition 41752 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4176Warnings: 4177Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`c` = 'foo' 4178execute stmt1; 4179id select_type table type possible_keys key key_len ref rows filtered Extra 41801 PRIMARY t1 ref idx idx 6 const 1 100.00 Using index condition 41812 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4182Warnings: 4183Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`c` = 'foo' 4184deallocate prepare stmt1; 4185prepare stmt1 from 4186"select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'"; 4187flush status; 4188execute stmt1; 4189id c 41902 foo 4191show status like '%Handler_read%'; 4192Variable_name Value 4193Handler_read_first 0 4194Handler_read_key 1 4195Handler_read_last 0 4196Handler_read_next 1 4197Handler_read_prev 0 4198Handler_read_retry 0 4199Handler_read_rnd 0 4200Handler_read_rnd_deleted 0 4201Handler_read_rnd_next 0 4202flush status; 4203execute stmt1; 4204id c 42052 foo 4206show status like '%Handler_read%'; 4207Variable_name Value 4208Handler_read_first 0 4209Handler_read_key 1 4210Handler_read_last 0 4211Handler_read_next 1 4212Handler_read_prev 0 4213Handler_read_retry 0 4214Handler_read_rnd 0 4215Handler_read_rnd_deleted 0 4216Handler_read_rnd_next 0 4217deallocate prepare stmt1; 4218prepare stmt2 from 4219"explain extended 4220 select * from t1 where (1, 2) in ( select 3, 4 )"; 4221execute stmt2; 4222id select_type table type possible_keys key key_len ref rows filtered Extra 42231 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 42242 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4225Warnings: 4226Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where 0 4227execute stmt2; 4228id select_type table type possible_keys key key_len ref rows filtered Extra 42291 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 42302 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4231Warnings: 4232Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where 0 4233deallocate prepare stmt2; 4234drop table t1; 4235# 4236# MDEV-9208: Function->Function->View = Mysqld segfault 4237# (Server crashes in Dependency_marker::visit_field on 2nd 4238# execution with merged subquery) 4239# 4240CREATE TABLE t1 (i1 INT); 4241insert into t1 values(1),(2); 4242CREATE TABLE t2 (i2 INT); 4243insert into t2 values(1),(2); 4244prepare stmt from " 4245 select 1 from ( 4246 select 4247 if (i1<0, 0, 0) as f1, 4248 (select f1) as f2 4249 from t1, t2 4250 ) sq 4251"; 4252execute stmt; 42531 42541 42551 42561 42571 4258execute stmt; 42591 42601 42611 42621 42631 4264drop table t1,t2; 4265# 4266# MDEV-9619: Assertion `null_ref_table' failed in virtual 4267# table_map Item_direct_view_ref::used_tables() const on 2nd 4268# execution of PS 4269# 4270CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; 4271CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; 4272INSERT INTO t1 VALUES ('a'),('b'); 4273CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; 4274INSERT INTO t2 VALUES ('c'),('d'); 4275PREPARE stmt FROM "SELECT * FROM v1 WHERE f1 = SOME ( SELECT f2 FROM t2 )"; 4276EXECUTE stmt; 4277f1 4278EXECUTE stmt; 4279f1 4280insert into t1 values ('c'); 4281EXECUTE stmt; 4282f1 4283c 4284EXECUTE stmt; 4285f1 4286c 4287deallocate prepare stmt; 4288drop view v1; 4289drop table t1,t2; 4290CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; 4291CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; 4292INSERT INTO t1 VALUES ('a'),('b'); 4293CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; 4294INSERT INTO t2 VALUES ('c'),('d'); 4295PREPARE stmt FROM "SELECT * FROM v1 WHERE (f1,f1) = SOME ( SELECT f2,f2 FROM t2 )"; 4296EXECUTE stmt; 4297f1 4298EXECUTE stmt; 4299f1 4300insert into t1 values ('c'); 4301EXECUTE stmt; 4302f1 4303c 4304EXECUTE stmt; 4305f1 4306c 4307deallocate prepare stmt; 4308drop view v1; 4309drop table t1,t2; 4310CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; 4311INSERT INTO t1 VALUES (3),(9); 4312CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; 4313INSERT INTO t2 VALUES (1),(4); 4314CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; 4315INSERT INTO t3 VALUES (6),(8); 4316CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; 4317INSERT INTO t4 VALUES (2),(5); 4318PREPARE stmt FROM " 4319SELECT ( 4320 SELECT MAX( table1.column1 ) AS field1 4321 FROM t1 AS table1 4322 WHERE (111,table3.column3) IN ( SELECT 111,table2.column2 AS field2 FROM t2 AS table2 ) 4323) AS sq 4324FROM t3 AS table3, t4 AS table4 GROUP BY sq 4325"; 4326EXECUTE stmt; 4327sq 4328NULL 4329EXECUTE stmt; 4330sq 4331NULL 4332deallocate prepare stmt; 4333drop table t1,t2,t3,t4; 4334create table t1 (a int, b int, c int); 4335create table t2 (x int, y int, z int); 4336create table t3 as select * from t1; 4337insert into t1 values (1,2,3),(4,5,6),(100,200,300),(400,500,600); 4338insert into t2 values (1,2,3),(7,8,9),(100,200,300),(400,500,600); 4339insert into t3 values (1,2,3),(11,12,13),(100,0,0),(400,500,600); 4340set @optimizer_switch_save=@@optimizer_switch; 4341set @join_cache_level_save=@@join_cache_level; 4342set optimizer_switch='materialization=off'; 4343set join_cache_level=0; 4344select * from t1 where (select a,b from t3 where t3.c=t1.c) in (select x,y from t2 where t1.c= t2.z); 4345a b c 43461 2 3 4347400 500 600 4348prepare 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)"; 4349EXECUTE stmt; 4350a b c 43511 2 3 4352400 500 600 4353EXECUTE stmt; 4354a b c 43551 2 3 4356400 500 600 4357create view v1 as select * from t1; 4358create view v2 as select * from t2; 4359create view v3 as select * from t3; 4360select * from v1 where (select a,b from v3 where v3.c=v1.c) in (select x,y from v2 where v1.c= v2.z); 4361a b c 43621 2 3 4363400 500 600 4364prepare 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)"; 4365EXECUTE stmt; 4366a b c 43671 2 3 4368400 500 600 4369EXECUTE stmt; 4370a b c 43711 2 3 4372400 500 600 4373set optimizer_switch=@optimizer_switch_save; 4374set join_cache_level=@join_cache_level_save; 4375deallocate prepare stmt; 4376drop view v1,v2,v3; 4377drop table t1,t2,t3; 4378# 4379# MDEV-10657: incorrect result returned with binary protocol 4380# (prepared statements) 4381# 4382create table t1 (code varchar(10) primary key); 4383INSERT INTO t1(code) VALUES ('LINE1'), ('LINE2'), ('LINE3'); 4384SELECT X.* 4385FROM 4386(SELECT CODE, RN 4387FROM 4388(SELECT A.CODE, @cnt := @cnt + 1 AS RN 4389FROM t1 A, (SELECT @cnt := 0) C) T 4390) X; 4391CODE RN 4392LINE1 1 4393LINE2 2 4394LINE3 3 4395drop table t1; 4396# 4397# MDEV-17042: prepared statement does not return error with 4398# SQL_MODE STRICT_TRANS_TABLES. (Part 1) 4399# 4400set @save_sql_mode=@@sql_mode; 4401set sql_mode='STRICT_ALL_TABLES'; 4402CREATE TABLE t1 (id int, count int); 4403insert into t1 values (1,1),(0,2); 4404update t1 set count = count + 1 where id = '1bad'; 4405ERROR 22007: Truncated incorrect DOUBLE value: '1bad' 4406prepare stmt from "update t1 set count = count + 1 where id = '1bad'"; 4407execute stmt; 4408ERROR 22007: Truncated incorrect DOUBLE value: '1bad' 4409deallocate prepare stmt; 4410prepare stmt from 'update t1 set count = count + 1 where id = ?'; 4411set @a = '1bad'; 4412execute stmt using @a; 4413ERROR 22007: Truncated incorrect DOUBLE value: '1bad' 4414deallocate prepare stmt; 4415drop table t1; 4416CREATE TABLE t1 (id decimal(10,5), count int); 4417insert into t1 values (1,1),(0,2); 4418update t1 set count = count + 1 where id = '1bad'; 4419ERROR 22007: Truncated incorrect DECIMAL value: '1bad' 4420prepare stmt from "update t1 set count = count + 1 where id = '1bad'"; 4421execute stmt; 4422ERROR 22007: Truncated incorrect DECIMAL value: '1bad' 4423deallocate prepare stmt; 4424prepare stmt from 'update t1 set count = count + 1 where id = ?'; 4425set @a = '1bad'; 4426execute stmt using @a; 4427ERROR 22007: Truncated incorrect DECIMAL value: '1bad' 4428deallocate prepare stmt; 4429drop table t1; 4430CREATE TABLE t1 (id double, count int); 4431insert into t1 values (1,1),(0,2); 4432update t1 set count = count + 1 where id = '1bad'; 4433ERROR 22007: Truncated incorrect DOUBLE value: '1bad' 4434prepare stmt from "update t1 set count = count + 1 where id = '1bad'"; 4435execute stmt; 4436ERROR 22007: Truncated incorrect DOUBLE value: '1bad' 4437deallocate prepare stmt; 4438prepare stmt from 'update t1 set count = count + 1 where id = ?'; 4439set @a = '1bad'; 4440execute stmt using @a; 4441ERROR 22007: Truncated incorrect DOUBLE value: '1bad' 4442deallocate prepare stmt; 4443drop table t1; 4444CREATE TABLE t1 (id date, count int); 4445insert into t1 values ("2019-06-11",1),("2019-06-12",2); 4446update t1 set count = count + 1 where id = '1bad'; 4447ERROR 22007: Truncated incorrect datetime value: '1bad' 4448prepare stmt from "update t1 set count = count + 1 where id = '1bad'"; 4449execute stmt; 4450ERROR 22007: Truncated incorrect datetime value: '1bad' 4451deallocate prepare stmt; 4452prepare stmt from 'update t1 set count = count + 1 where id = ?'; 4453set @a = '1bad'; 4454execute stmt using @a; 4455ERROR 22007: Truncated incorrect datetime value: '1bad' 4456deallocate prepare stmt; 4457drop table t1; 4458set sql_mode=@save_sql_mode; 4459# End of 5.5 tests 4460# 4461# End of 10.0 tests 4462# 4463# 4464# MDEV-12060 Crash in EXECUTE IMMEDIATE with an expression returning a GRANT command 4465# (the 10.1 part) 4466# 4467CREATE PROCEDURE p2 () 4468BEGIN 4469SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR PREPARE stmt FROM 'SELECT 1'; 4470EXECUTE stmt; 4471DEALLOCATE PREPARE stmt; 4472END; 4473/ 4474CALL p2(); 44751 44761 4477DROP PROCEDURE p2; 4478BEGIN NOT ATOMIC 4479SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR PREPARE stmt FROM 'SELECT 1'; 4480EXECUTE stmt; 4481DEALLOCATE PREPARE stmt; 4482END; 4483/ 44841 44851 4486BEGIN NOT ATOMIC 4487SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR PREPARE stmt FROM 'SELECT 1'; 4488DEALLOCATE PREPARE stmt; 4489END; 4490/ 4491BEGIN NOT ATOMIC 4492PREPARE stmt FROM 'SELECT 1'; 4493SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR EXECUTE stmt; 4494DEALLOCATE PREPARE stmt; 4495END; 4496/ 44971 44981 4499# 4500# MDEV-14572: Assertion `! is_set()' failed in 4501# Diagnostics_area::set_eof_status upon EXPLAIN UPDATE in PS 4502# 4503CREATE TABLE t1 (a INT); 4504CREATE TABLE t2 (b INT); 4505PREPARE stmt FROM 'EXPLAIN UPDATE t1, t2 SET a = 1'; 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 4510EXECUTE stmt; 4511id select_type table type possible_keys key key_len ref rows Extra 45121 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found 45131 SIMPLE t2 system NULL NULL NULL NULL 0 Const row not found 4514deallocate prepare stmt; 4515DROP TABLE t1, t2; 4516# 4517# End of 10.1 tests 4518# 4519# 4520# MDEV-10709 Expressions as parameters to Dynamic SQL 4521# 4522# 4523# Using a simple expressions as an EXECUTE parameter 4524# 4525PREPARE stmt FROM 'SELECT ? FROM DUAL'; 4526EXECUTE stmt USING 10; 4527? 452810 4529DEALLOCATE PREPARE stmt; 4530PREPARE stmt FROM 'SELECT ? FROM DUAL'; 4531EXECUTE stmt USING TO_BASE64('xxx'); 4532? 4533eHh4 4534DEALLOCATE PREPARE stmt; 4535PREPARE stmt FROM 'SELECT ?+? FROM DUAL'; 4536EXECUTE stmt USING 10, 10 + 10; 4537?+? 453830 4539DEALLOCATE PREPARE stmt; 4540PREPARE stmt FROM 'SELECT CONCAT(?,?) FROM DUAL'; 4541EXECUTE stmt USING 'xxx', CONCAT('yyy','zzz'); 4542CONCAT(?,?) 4543xxxyyyzzz 4544DEALLOCATE PREPARE stmt; 4545# 4546# Testing disallowed expressions in USING 4547# 4548PREPARE stmt FROM 'SELECT ? FROM DUAL'; 4549EXECUTE stmt USING (SELECT 1); 4550ERROR 42000: EXECUTE..USING does not support subqueries or stored functions 4551DEALLOCATE PREPARE stmt; 4552CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test'; 4553PREPARE stmt FROM 'SELECT ? FROM DUAL'; 4554EXECUTE stmt USING f1(); 4555ERROR 42000: EXECUTE..USING does not support subqueries or stored functions 4556DEALLOCATE PREPARE stmt; 4557DROP FUNCTION f1; 4558# 4559# Testing erroneous expressions in USING 4560# 4561PREPARE stmt FROM 'SELECT ?'; 4562EXECUTE stmt USING _latin1'a'=_latin2'a'; 4563ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '=' 4564DEALLOCATE PREPARE stmt; 4565PREPARE stmt FROM 'SELECT ?'; 4566EXECUTE stmt USING ROW(1,2); 4567ERROR 21000: Operand should contain 1 column(s) 4568DEALLOCATE PREPARE stmt; 4569# 4570# Creating tables from EXECUTE parameters 4571# 4572PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT ? AS c1 FROM DUAL'; 4573EXECUTE stmt USING 10; 4574SHOW CREATE TABLE t1; 4575Table Create Table 4576t1 CREATE TABLE `t1` ( 4577 `c1` int(2) NOT NULL 4578) ENGINE=MyISAM DEFAULT CHARSET=latin1 4579DROP TABLE t1; 4580EXECUTE stmt USING 10.123; 4581SHOW CREATE TABLE t1; 4582Table Create Table 4583t1 CREATE TABLE `t1` ( 4584 `c1` decimal(5,3) NOT NULL 4585) ENGINE=MyISAM DEFAULT CHARSET=latin1 4586DROP TABLE t1; 4587EXECUTE stmt USING 10.123e0; 4588SHOW CREATE TABLE t1; 4589Table Create Table 4590t1 CREATE TABLE `t1` ( 4591 `c1` double NOT NULL 4592) ENGINE=MyISAM DEFAULT CHARSET=latin1 4593DROP TABLE t1; 4594EXECUTE stmt USING CURRENT_DATE; 4595SHOW CREATE TABLE t1; 4596Table Create Table 4597t1 CREATE TABLE `t1` ( 4598 `c1` date NOT NULL 4599) ENGINE=MyISAM DEFAULT CHARSET=latin1 4600DROP TABLE t1; 4601EXECUTE stmt USING CURRENT_TIMESTAMP; 4602SHOW CREATE TABLE t1; 4603Table Create Table 4604t1 CREATE TABLE `t1` ( 4605 `c1` datetime NOT NULL 4606) ENGINE=MyISAM DEFAULT CHARSET=latin1 4607DROP TABLE t1; 4608EXECUTE stmt USING CURRENT_TIMESTAMP(3); 4609SHOW CREATE TABLE t1; 4610Table Create Table 4611t1 CREATE TABLE `t1` ( 4612 `c1` datetime(3) NOT NULL 4613) ENGINE=MyISAM DEFAULT CHARSET=latin1 4614DROP TABLE t1; 4615EXECUTE stmt USING CURRENT_TIMESTAMP(6); 4616SHOW CREATE TABLE t1; 4617Table Create Table 4618t1 CREATE TABLE `t1` ( 4619 `c1` datetime(6) NOT NULL 4620) ENGINE=MyISAM DEFAULT CHARSET=latin1 4621DROP TABLE t1; 4622EXECUTE stmt USING CURRENT_TIME; 4623SHOW CREATE TABLE t1; 4624Table Create Table 4625t1 CREATE TABLE `t1` ( 4626 `c1` time NOT NULL 4627) ENGINE=MyISAM DEFAULT CHARSET=latin1 4628DROP TABLE t1; 4629EXECUTE stmt USING CURRENT_TIME(3); 4630SHOW CREATE TABLE t1; 4631Table Create Table 4632t1 CREATE TABLE `t1` ( 4633 `c1` time(3) NOT NULL 4634) ENGINE=MyISAM DEFAULT CHARSET=latin1 4635DROP TABLE t1; 4636EXECUTE stmt USING CURRENT_TIME(6); 4637SHOW CREATE TABLE t1; 4638Table Create Table 4639t1 CREATE TABLE `t1` ( 4640 `c1` time(6) NOT NULL 4641) ENGINE=MyISAM DEFAULT CHARSET=latin1 4642DROP TABLE t1; 4643DEALLOCATE PREPARE stmt; 4644# 4645# Using a user variable as an EXECUTE..USING out parameter 4646# 4647CREATE PROCEDURE p1(OUT a INT) 4648BEGIN 4649SET a:= 10; 4650END; 4651/ 4652SET @a=1; 4653CALL p1(@a); 4654SELECT @a; 4655@a 465610 4657SET @a=2; 4658PREPARE stmt FROM 'CALL p1(?)'; 4659EXECUTE stmt USING @a; 4660SELECT @a; 4661@a 466210 4663DROP PROCEDURE p1; 4664# 4665# Using an SP variable as an EXECUTE..USING out parameter 4666# 4667CREATE PROCEDURE p1 (OUT a INT) 4668BEGIN 4669SET a=10; 4670END; 4671/ 4672CREATE PROCEDURE p2 (OUT a INT) 4673BEGIN 4674PREPARE stmt FROM 'CALL p1(?)'; 4675EXECUTE stmt USING a; 4676END; 4677/ 4678SET @a= 1; 4679CALL p2(@a); 4680SELECT @a; 4681@a 468210 4683DROP PROCEDURE p2; 4684DROP PROCEDURE p1; 4685# 4686# Testing re-prepare on a table metadata update between PREPARE and EXECUTE 4687# 4688CREATE TABLE t1 (a INT); 4689CREATE PROCEDURE p1(a INT) 4690BEGIN 4691INSERT INTO t1 VALUES (a); 4692END; 4693/ 4694PREPARE stmt FROM 'CALL p1(?)'; 4695EXECUTE stmt USING 10; 4696SELECT * FROM t1; 4697a 469810 4699CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=NEW.a+1; 4700EXECUTE stmt USING 20; 4701SELECT * FROM t1; 4702a 470310 470421 4705DEALLOCATE PREPARE stmt; 4706DROP PROCEDURE p1; 4707DROP TABLE t1; 4708# 4709# End of MDEV-10709 Expressions as parameters to Dynamic SQL 4710# 4711# 4712# MDEV-10585 EXECUTE IMMEDIATE statement 4713# 4714EXECUTE IMMEDIATE 'SELECT 1 AS a'; 4715a 47161 4717SET @a=10; 4718EXECUTE IMMEDIATE 'SELECT ? AS a' USING @a; 4719a 472010 4721EXECUTE IMMEDIATE 'SELECT ? AS a' USING 20; 4722a 472320 4724# 4725# Erroneous queries 4726# 4727EXECUTE IMMEDIATE 'xxx'; 4728ERROR 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 4729EXECUTE IMMEDIATE 'SELECT 1' USING @a; 4730ERROR HY000: Incorrect arguments to EXECUTE 4731EXECUTE IMMEDIATE 'SELECT ?'; 4732ERROR HY000: Incorrect arguments to EXECUTE 4733EXECUTE IMMEDIATE 'EXECUTE IMMEDIATE "SELECT 1"'; 4734ERROR HY000: This command is not supported in the prepared statement protocol yet 4735EXECUTE IMMEDIATE 'PREPARE stmt FROM "SELECT 1"'; 4736ERROR HY000: This command is not supported in the prepared statement protocol yet 4737EXECUTE IMMEDIATE 'EXECUTE stmt'; 4738ERROR HY000: This command is not supported in the prepared statement protocol yet 4739EXECUTE IMMEDIATE 'DEALLOCATE PREPARE stmt'; 4740ERROR HY000: This command is not supported in the prepared statement protocol yet 4741EXECUTE IMMEDIATE 'SELECT ?' USING _latin1'a'=_latin2'a'; 4742ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '=' 4743EXECUTE IMMEDIATE 'SELECT ?' USING ROW(1,2); 4744ERROR 21000: Operand should contain 1 column(s) 4745# 4746# Testing disallowed expressions in USING 4747# 4748EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING (SELECT 1); 4749ERROR 42000: EXECUTE..USING does not support subqueries or stored functions 4750CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test'; 4751EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING f1(); 4752ERROR 42000: EXECUTE IMMEDIATE does not support subqueries or stored functions 4753DROP FUNCTION f1; 4754# 4755# DDL 4756# 4757EXECUTE IMMEDIATE 'CREATE TABLE t1 (a INT)'; 4758EXECUTE IMMEDIATE 'SHOW CREATE TABLE t1'; 4759Table Create Table 4760t1 CREATE TABLE `t1` ( 4761 `a` int(11) DEFAULT NULL 4762) ENGINE=MyISAM DEFAULT CHARSET=latin1 4763EXECUTE IMMEDIATE 'DROP TABLE t1'; 4764SET @stmt= 'CREATE TABLE t1 (a INT)'; 4765EXECUTE IMMEDIATE @stmt; 4766SET @stmt= 'SHOW CREATE TABLE t1'; 4767EXECUTE IMMEDIATE @stmt; 4768Table Create Table 4769t1 CREATE TABLE `t1` ( 4770 `a` int(11) DEFAULT NULL 4771) ENGINE=MyISAM DEFAULT CHARSET=latin1 4772SET @stmt= 'DROP TABLE t1'; 4773EXECUTE IMMEDIATE @stmt; 4774# 4775# DDL with parameters 4776# 4777SET @a= 10, @b= 10.1, @c= 10e0, @d='str'; 4778EXECUTE IMMEDIATE 4779'CREATE TABLE t1 AS SELECT ? AS a,? AS b,? AS c,? AS d' 4780 USING @a,@b,@c,@d; 4781SHOW CREATE TABLE t1; 4782Table Create Table 4783t1 CREATE TABLE `t1` ( 4784 `a` bigint(20) NOT NULL, 4785 `b` decimal(3,1) NOT NULL, 4786 `c` double NOT NULL, 4787 `d` tinytext NOT NULL 4788) ENGINE=MyISAM DEFAULT CHARSET=latin1 4789DROP TABLE t1; 4790EXECUTE IMMEDIATE 4791'CREATE TABLE t1 AS SELECT ? AS a,? AS b,? AS c,? AS d' 4792 USING 10, 10.1, 10e0, 'str'; 4793SHOW CREATE TABLE t1; 4794Table Create Table 4795t1 CREATE TABLE `t1` ( 4796 `a` int(2) NOT NULL, 4797 `b` decimal(3,1) NOT NULL, 4798 `c` double NOT NULL, 4799 `d` varchar(3) NOT NULL 4800) ENGINE=MyISAM DEFAULT CHARSET=latin1 4801DROP TABLE t1; 4802EXECUTE IMMEDIATE 4803'CREATE TABLE t1 AS SELECT ? AS t1,? AS t2, ? AS d1,? AS dt1, ? AS dt2' 4804 USING TIME'10:20:30', 4805TIME'10:20:30.123', 4806DATE'2001-01-01', 4807TIMESTAMP'2001-01-01 10:20:30', 4808TIMESTAMP'2001-01-01 10:20:30.123'; 4809SHOW CREATE TABLE t1; 4810Table Create Table 4811t1 CREATE TABLE `t1` ( 4812 `t1` time NOT NULL, 4813 `t2` time(3) NOT NULL, 4814 `d1` date NOT NULL, 4815 `dt1` datetime NOT NULL, 4816 `dt2` datetime(3) NOT NULL 4817) ENGINE=MyISAM DEFAULT CHARSET=latin1 4818DROP TABLE t1; 4819# 4820# Using a user variable as an EXECUTE IMMEDIATE..USING out parameter 4821# 4822CREATE PROCEDURE p1(OUT a INT) 4823BEGIN 4824SET a:= 10; 4825END; 4826/ 4827SET @a=1; 4828CALL p1(@a); 4829SELECT @a; 4830@a 483110 4832SET @a=2; 4833EXECUTE IMMEDIATE 'CALL p1(?)' USING @a; 4834SELECT @a; 4835@a 483610 4837DROP PROCEDURE p1; 4838# 4839# Using an SP variable as an EXECUTE IMMEDIATE..USING out parameter 4840# 4841CREATE PROCEDURE p1 (OUT a INT) 4842BEGIN 4843SET a=10; 4844END; 4845/ 4846CREATE PROCEDURE p2 (OUT a INT) 4847BEGIN 4848EXECUTE IMMEDIATE 'CALL p1(?)' USING a; 4849END; 4850/ 4851SET @a= 1; 4852CALL p2(@a); 4853SELECT @a; 4854@a 485510 4856DROP PROCEDURE p2; 4857DROP PROCEDURE p1; 4858# 4859# Changing user variables 4860# 4861SET @a=10; 4862EXECUTE IMMEDIATE 'SET @a=@a+1'; 4863SELECT @a; 4864@a 486511 4866# 4867# SET STATEMENT 4868# 4869SET @@max_sort_length=1024; 4870EXECUTE IMMEDIATE 'SET STATEMENT max_sort_length=1025 FOR SELECT @@max_sort_length'; 4871@@max_sort_length 48721025 4873SELECT @@max_sort_length; 4874@@max_sort_length 48751024 4876SET @@max_sort_length=DEFAULT; 4877# 4878# Similar to prepared EXECUTE, IMMEDIATE is not allowed in stored functions 4879# 4880CREATE FUNCTION f1() RETURNS INT 4881BEGIN 4882EXECUTE IMMEDIATE 'DO 1'; 4883RETURN 1; 4884END; 4885$$ 4886ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger 4887# 4888# Status variables 4889# 4890CREATE FUNCTION get_status_var(name TEXT) RETURNS INT 4891RETURN (SELECT CAST(VARIABLE_VALUE AS INT) 4892FROM INFORMATION_SCHEMA.SESSION_STATUS 4893WHERE VARIABLE_NAME=name); 4894CREATE PROCEDURE test_status_var(name TEXT) 4895BEGIN 4896SET @cnt0=get_status_var(name); 4897EXECUTE IMMEDIATE 'DO 1'; 4898SET @cnt1=get_status_var(name); 4899SELECT @cnt1-@cnt0 AS increment; 4900END; 4901$$ 4902# Note, EXECUTE IMMEDIATE does not increment COM_EXECUTE_SQL 4903# It increments COM_EXECUTE_IMMEDIATE instead. 4904CALL test_status_var('COM_EXECUTE_SQL'); 4905increment 49060 4907CALL test_status_var('COM_EXECUTE_IMMEDIATE'); 4908increment 49091 4910CALL test_status_var('COM_STMT_PREPARE'); 4911increment 49121 4913CALL test_status_var('COM_STMT_EXECUTE'); 4914increment 49151 4916CALL test_status_var('COM_STMT_CLOSE'); 4917increment 49181 4919DROP PROCEDURE test_status_var; 4920DROP FUNCTION get_status_var; 4921# 4922# End of MDEV-10585 EXECUTE IMMEDIATE statement 4923# 4924# 4925# MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions 4926# 4927# 4928# Testing erroneous and diallowed prepare source 4929# 4930EXECUTE IMMEDIATE CONCAT(_latin1'SELECT 1 AS c FROM ', _latin2 'DUAL'); 4931ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat' 4932PREPARE stmt FROM CONCAT(_latin1'SELECT 1 AS c FROM ', _latin2 'DUAL'); 4933ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat' 4934EXECUTE IMMEDIATE (SELECT 'SELECT 1'); 4935ERROR 42000: EXECUTE IMMEDIATE does not support subqueries or stored functions 4936PREPARE stmt FROM (SELECT 'SELECT 1'); 4937ERROR 42000: PREPARE..FROM does not support subqueries or stored functions 4938EXECUTE IMMEDIATE a; 4939ERROR 42S22: Unknown column 'a' in 'field list' 4940PREPARE stmt FROM a; 4941ERROR 42S22: Unknown column 'a' in 'field list' 4942EXECUTE IMMEDIATE 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 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 CONCAT(NULL); 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 'NULL' at line 1 4948PREPARE stmt FROM CONCAT(NULL); 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 'NULL' at line 1 4950EXECUTE IMMEDIATE ? USING 'SELECT 1'; 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 '? USING 'SELECT 1'' at line 1 4952EXECUTE IMMEDIATE 10; 4953ERROR 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 4954EXECUTE IMMEDIATE TIME'10:20:30'; 4955ERROR 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 4956EXECUTE IMMEDIATE ROW('SELECT 1','SELECT 2'); 4957ERROR 21000: Operand should contain 1 column(s) 4958EXECUTE IMMEDIATE MAX('SELECT 1 AS c'); 4959ERROR HY000: Invalid use of group function 4960EXECUTE IMMEDIATE DEFAULT(a); 4961ERROR 42S22: Unknown column 'a' in 'field list' 4962EXECUTE IMMEDIATE VALUE(a); 4963ERROR 42S22: Unknown column 'a' in 'field list' 4964CREATE FUNCTION f1() RETURNS VARCHAR(64) RETURN 't1'; 4965EXECUTE IMMEDIATE f1(); 4966ERROR 42000: EXECUTE IMMEDIATE does not support subqueries or stored functions 4967PREPARE stmt FROM f1(); 4968ERROR 42000: PREPARE..FROM does not support subqueries or stored functions 4969DROP FUNCTION f1; 4970EXECUTE IMMEDIATE non_existent(); 4971ERROR 42000: EXECUTE IMMEDIATE does not support subqueries or stored functions 4972# 4973# Testing literals in prepare source 4974# 4975EXECUTE IMMEDIATE N'SELECT 1 AS c'; 4976c 49771 4978EXECUTE IMMEDIATE _latin1'SELECT 1 AS c'; 4979c 49801 4981EXECUTE IMMEDIATE 'SELECT ' '1' ' AS c' ' FROM ' 'DUAL'; 4982c 49831 4984EXECUTE IMMEDIATE 0x53454C4543542031 /*This is 'SELECT 1'*/; 49851 49861 4987# 4988# Testing user variables in prepare source 4989# 4990SET @stmt='SELECT 1 AS c FROM DUAL'; 4991EXECUTE IMMEDIATE @stmt; 4992c 49931 4994PREPARE stmt FROM @stmt; 4995EXECUTE stmt; 4996c 49971 4998DEALLOCATE PREPARE stmt; 4999SET @table_name='DUAL'; 5000EXECUTE IMMEDIATE CONCAT('SELECT 1 AS a FROM ', @table_name); 5001a 50021 5003PREPARE stmt FROM CONCAT('SELECT 1 AS a FROM ', @table_name); 5004EXECUTE stmt; 5005a 50061 5007DEALLOCATE PREPARE stmt; 5008# 5009# Testing SP parameters and variables in prepare source 5010# 5011CREATE PROCEDURE p1(table_name VARCHAR(64)) 5012BEGIN 5013EXECUTE IMMEDIATE CONCAT('SELECT 1 AS c FROM ', table_name); 5014END; 5015$$ 5016CALL p1('DUAL'); 5017c 50181 5019DROP PROCEDURE p1; 5020CREATE PROCEDURE p1() 5021BEGIN 5022DECLARE table_name VARCHAR(64) DEFAULT 'DUAL'; 5023EXECUTE IMMEDIATE CONCAT('SELECT 1 AS c FROM ', table_name); 5024END; 5025$$ 5026CALL p1(); 5027c 50281 5029DROP PROCEDURE p1; 5030# 5031# Testing complex expressions 5032# 5033EXECUTE IMMEDIATE CONVERT('SELECT 1 AS c' USING utf8); 5034c 50351 5036EXECUTE IMMEDIATE CAST('SELECT 1 AS c' AS CHAR); 5037c 50381 5039EXECUTE IMMEDIATE _latin1'SELECT 1 AS c' COLLATE latin1_bin; 5040c 50411 5042EXECUTE IMMEDIATE (((('SELECT 1 AS c')))); 5043c 50441 5045EXECUTE IMMEDIATE CASE WHEN 1>2 THEN 'SELECT 1 AS c' ELSE 'SELECT 2 AS c' END; 5046c 50472 5048EXECUTE IMMEDIATE TRIM('SELECT 1 AS c'); 5049c 50501 5051EXECUTE IMMEDIATE SUBSTRING('SELECT 1 AS c' FROM 1); 5052c 50531 5054EXECUTE IMMEDIATE COALESCE(NULL, 'SELECT 1 AS c'); 5055c 50561 5057# 5058# Testing SET STATEMENT and system variables 5059# 5060CREATE TABLE t1 (a INT); 5061SET STATEMENT max_sort_length=1025 FOR EXECUTE IMMEDIATE CONCAT('INSERT INTO t1 VALUES (', @@max_sort_length, ')'); 5062SELECT * FROM t1; 5063a 50641025 5065DROP TABLE t1; 5066# 5067# End of MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions 5068# 5069# 5070# MDEV-11360 Dynamic SQL: DEFAULT as a bind parameter 5071# 5072CREATE TABLE t1 (a INT DEFAULT 10, b INT DEFAULT NULL); 5073EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?,?)' USING DEFAULT, DEFAULT; 5074SELECT * FROM t1; 5075a b 507610 NULL 5077UPDATE t1 SET a=20, b=30; 5078SELECT * FROM t1; 5079a b 508020 30 5081EXECUTE IMMEDIATE 'UPDATE t1 SET a=?,b=?' USING DEFAULT, DEFAULT; 5082SELECT * FROM t1; 5083a b 508410 NULL 5085DROP TABLE t1; 5086CREATE TABLE t1 (a INT DEFAULT 10); 5087EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?+1)' USING DEFAULT; 5088ERROR HY000: Default/ignore value is not supported for such parameter usage 5089EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (CONCAT(?,?))' USING DEFAULT, 'test'; 5090ERROR HY000: Default/ignore value is not supported for such parameter usage 5091DROP TABLE t1; 5092CREATE TABLE t1 (a INT DEFAULT 10); 5093INSERT INTO t1 VALUES (20); 5094EXECUTE IMMEDIATE 'UPDATE t1 SET a=?+1' USING DEFAULT; 5095ERROR HY000: Default/ignore value is not supported for such parameter usage 5096EXECUTE IMMEDIATE 'UPDATE t1 SET a=CONCAT(?,?)' USING DEFAULT, 'test'; 5097ERROR HY000: Default/ignore value is not supported for such parameter usage 5098DROP TABLE t1; 5099EXECUTE IMMEDIATE 'SELECT CAST(? AS SIGNED)' USING DEFAULT; 5100ERROR HY000: Default/ignore value is not supported for such parameter usage 5101EXECUTE IMMEDIATE 'SELECT CAST(? AS DOUBLE)' USING DEFAULT; 5102ERROR HY000: Default/ignore value is not supported for such parameter usage 5103EXECUTE IMMEDIATE 'SELECT CAST(? AS CHAR)' USING DEFAULT; 5104ERROR HY000: Default/ignore value is not supported for such parameter usage 5105EXECUTE IMMEDIATE 'SELECT CAST(? AS DECIMAL(10,1))' USING DEFAULT; 5106ERROR HY000: Default/ignore value is not supported for such parameter usage 5107EXECUTE IMMEDIATE 'SELECT CAST(? AS TIME)' USING DEFAULT; 5108ERROR HY000: Default/ignore value is not supported for such parameter usage 5109EXECUTE IMMEDIATE 'SELECT CAST(? AS DATE)' USING DEFAULT; 5110ERROR HY000: Default/ignore value is not supported for such parameter usage 5111EXECUTE IMMEDIATE 'SELECT CAST(? AS DATETIME)' USING DEFAULT; 5112ERROR HY000: Default/ignore value is not supported for such parameter usage 5113EXECUTE IMMEDIATE 'SELECT ?+1' USING DEFAULT; 5114ERROR HY000: Default/ignore value is not supported for such parameter usage 5115EXECUTE IMMEDIATE 'SELECT CONCAT(?,?)' USING DEFAULT,'test'; 5116ERROR HY000: Default/ignore value is not supported for such parameter usage 5117EXECUTE IMMEDIATE 'SELECT 1 LIMIT ?' USING DEFAULT; 5118ERROR HY000: Default/ignore value is not supported for such parameter usage 5119CREATE TABLE t1 (a INT DEFAULT 10); 5120INSERT INTO t1 VALUES (1),(2),(3); 5121EXECUTE IMMEDIATE 'SELECT * FROM t1 LIMIT ?' USING DEFAULT; 5122ERROR HY000: Default/ignore value is not supported for such parameter usage 5123DROP TABLE t1; 5124# The output of this query in 'Note' is a syntactically incorrect query. 5125# But as it's never logged, it's ok. It should be human readable only. 5126EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING DEFAULT; 5127id select_type table type possible_keys key key_len ref rows filtered Extra 51281 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5129Warnings: 5130Note 1003 select default AS `?` 5131CREATE TABLE t1 (a INT); 5132INSERT INTO t1 VALUES (1),(2),(3); 5133EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING DEFAULT,DEFAULT; 5134ERROR HY000: Default/ignore value is not supported for such parameter usage 5135DROP TABLE t1; 5136# 5137# MDEV-11780 Crash with PREPARE + SP out parameter + literal 5138# 5139CREATE OR REPLACE PROCEDURE p1(OUT a INT) 5140BEGIN 5141SET a=10; 5142END; 5143$$ 5144PREPARE stmt FROM 'CALL p1(?)'; 5145EXECUTE stmt USING 10; 5146ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger 5147EXECUTE stmt USING DEFAULT; 5148ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger 5149EXECUTE stmt USING IGNORE; 5150ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger 5151DEALLOCATE PREPARE stmt; 5152EXECUTE IMMEDIATE 'CALL p1(?)' USING 10; 5153ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger 5154EXECUTE IMMEDIATE 'CALL p1(?)' USING DEFAULT; 5155ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger 5156EXECUTE IMMEDIATE 'CALL p1(?)' USING IGNORE; 5157ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger 5158DROP PROCEDURE p1; 5159# 5160# MDEV-14434 Wrong result for CHARSET(CONCAT(?,const)) 5161# 5162SET NAMES utf8; 5163EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(5,_latin1'a'))"; 5164CHARSET(CONCAT(5,_latin1'a')) 5165latin1 5166EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5; 5167CHARSET(CONCAT(?,_latin1'a')) 5168latin1 5169EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5.5; 5170CHARSET(CONCAT(?,_latin1'a')) 5171latin1 5172EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5.5e0; 5173CHARSET(CONCAT(?,_latin1'a')) 5174latin1 5175EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING TIME'10:20:30'; 5176CHARSET(CONCAT(?,_latin1'a')) 5177latin1 5178EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING TIMESTAMP'2001-01-01 10:20:30'; 5179CHARSET(CONCAT(?,_latin1'a')) 5180latin1 5181EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5; 5182COERCIBILITY(?) 51835 5184EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5.5; 5185COERCIBILITY(?) 51865 5187EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5.5e0; 5188COERCIBILITY(?) 51895 5190EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING TIME'10:20:30'; 5191COERCIBILITY(?) 51925 5193EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING TIMESTAMP'2001-01-01 10:20:30'; 5194COERCIBILITY(?) 51955 5196# 5197# MDEV-14435 Different UNSIGNED flag of out user variable for YEAR parameter for direct vs prepared CALL 5198# 5199CREATE PROCEDURE p1(OUT v INT UNSIGNED) SET v = 2010; 5200CALL p1(@a); 5201PREPARE stmt FROM 'CALL p1(?)'; 5202EXECUTE stmt USING @b; 5203DEALLOCATE PREPARE stmt; 5204CREATE TABLE t1 AS SELECT @a AS a, @b AS b; 5205SHOW CREATE TABLE t1; 5206Table Create Table 5207t1 CREATE TABLE `t1` ( 5208 `a` bigint(20) unsigned DEFAULT NULL, 5209 `b` bigint(20) unsigned DEFAULT NULL 5210) ENGINE=MyISAM DEFAULT CHARSET=latin1 5211DROP TABLE t1; 5212DROP PROCEDURE p1; 5213CREATE PROCEDURE p1(OUT v YEAR) SET v = 2010; 5214CALL p1(@a); 5215PREPARE stmt FROM 'CALL p1(?)'; 5216EXECUTE stmt USING @b; 5217DEALLOCATE PREPARE stmt; 5218CREATE TABLE t1 AS SELECT @a AS a, @b AS b; 5219SHOW CREATE TABLE t1; 5220Table Create Table 5221t1 CREATE TABLE `t1` ( 5222 `a` bigint(20) unsigned DEFAULT NULL, 5223 `b` bigint(20) unsigned DEFAULT NULL 5224) ENGINE=MyISAM DEFAULT CHARSET=latin1 5225DROP TABLE t1; 5226DROP PROCEDURE p1; 5227CREATE PROCEDURE p1(OUT v BIT(16)) SET v = 2010; 5228CALL p1(@a); 5229PREPARE stmt FROM 'CALL p1(?)'; 5230EXECUTE stmt USING @b; 5231DEALLOCATE PREPARE stmt; 5232CREATE TABLE t1 AS SELECT @a AS a, @b AS b; 5233SHOW CREATE TABLE t1; 5234Table Create Table 5235t1 CREATE TABLE `t1` ( 5236 `a` bigint(20) unsigned DEFAULT NULL, 5237 `b` bigint(20) unsigned DEFAULT NULL 5238) ENGINE=MyISAM DEFAULT CHARSET=latin1 5239DROP TABLE t1; 5240DROP PROCEDURE p1; 5241# 5242# MDEV-14426 Assertion in Diagnostics_area::set_error_status when using a bad datetime with PS and SP 5243# 5244CREATE PROCEDURE p1(OUT a VARCHAR(20)) 5245BEGIN 5246SET a=10; 5247END; 5248$$ 5249BEGIN NOT ATOMIC 5250DECLARE a DATETIME; 5251CALL p1(a); 5252END; 5253$$ 5254ERROR 22007: Incorrect datetime value: '10' for column ``.``.`a` at row 1 5255BEGIN NOT ATOMIC 5256DECLARE a DATETIME; 5257EXECUTE IMMEDIATE 'CALL p1(?)' USING a; 5258END; 5259$$ 5260ERROR 22007: Incorrect datetime value: '10' for column ``.``.`a` at row 1 5261BEGIN NOT ATOMIC 5262DECLARE a DATETIME; 5263PREPARE stmt FROM 'CALL p1(?)'; 5264EXECUTE stmt USING a; 5265DEALLOCATE PREPARE stmt; 5266END; 5267$$ 5268ERROR 22007: Incorrect datetime value: '10' for column ``.``.`a` at row 1 5269DROP PROCEDURE p1; 5270# 5271# MDEV-14454 Binary protocol returns wrong collation ID for SP OUT parameters 5272# 5273CREATE PROCEDURE p1(OUT v CHAR(32) CHARACTER SET utf8) SET v='aaa'; 5274PREPARE stmt1 FROM 'CALL p1(?)'; 5275EXECUTE stmt1 USING @a; 5276CREATE TABLE t1 AS SELECT @a AS c1; 5277SHOW CREATE TABLE t1; 5278Table Create Table 5279t1 CREATE TABLE `t1` ( 5280 `c1` longtext CHARACTER SET utf8 DEFAULT NULL 5281) ENGINE=MyISAM DEFAULT CHARSET=latin1 5282DROP TABLE t1; 5283DROP PROCEDURE p1; 5284# 5285# MDEV-14467 Item_param: replace {INT|DECIMAL|REAL|STRING|TIME}_VALUE with Type_handler 5286# 5287EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING 10; 5288id select_type table type possible_keys key key_len ref rows filtered Extra 52891 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5290Warnings: 5291Note 1003 select 1 AS `1` limit 10 5292EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING 10.1; 5293id select_type table type possible_keys key key_len ref rows filtered Extra 52941 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5295Warnings: 5296Note 1003 select 1 AS `1` limit 10 5297EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING 10.1e0; 5298id select_type table type possible_keys key key_len ref rows filtered Extra 52991 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5300Warnings: 5301Note 1003 select 1 AS `1` limit 10 5302EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING '10'; 5303id select_type table type possible_keys key key_len ref rows filtered Extra 53041 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5305Warnings: 5306Note 1003 select 1 AS `1` limit 10 5307EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING TIME'10:10:10'; 5308id select_type table type possible_keys key key_len ref rows filtered Extra 53091 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5310Warnings: 5311Note 1003 select 1 AS `1` limit 101010 5312EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 1 AS a,? AS b' USING 1; 5313SHOW CREATE TABLE t1; 5314Table Create Table 5315t1 CREATE TABLE `t1` ( 5316 `a` int(1) NOT NULL, 5317 `b` int(1) NOT NULL 5318) ENGINE=MyISAM DEFAULT CHARSET=latin1 5319DROP TABLE t1; 5320EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 10 AS a,? AS b' USING 10; 5321SHOW CREATE TABLE t1; 5322Table Create Table 5323t1 CREATE TABLE `t1` ( 5324 `a` int(2) NOT NULL, 5325 `b` int(2) NOT NULL 5326) ENGINE=MyISAM DEFAULT CHARSET=latin1 5327DROP TABLE t1; 5328EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 999999999 AS a,? AS b' USING 999999999; 5329SHOW CREATE TABLE t1; 5330Table Create Table 5331t1 CREATE TABLE `t1` ( 5332 `a` int(9) NOT NULL, 5333 `b` int(9) NOT NULL 5334) ENGINE=MyISAM DEFAULT CHARSET=latin1 5335DROP TABLE t1; 5336EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 2147483647 AS a,? AS b' USING 2147483647; 5337SHOW CREATE TABLE t1; 5338Table Create Table 5339t1 CREATE TABLE `t1` ( 5340 `a` bigint(10) NOT NULL, 5341 `b` bigint(10) NOT NULL 5342) ENGINE=MyISAM DEFAULT CHARSET=latin1 5343DROP TABLE t1; 5344# 5345# MDEV-14603 signal 11 with short stacktrace 5346# 5347SET NAMES utf8; 5348CREATE TABLE t1(i INT); 5349CREATE PROCEDURE p1(tn VARCHAR(32)) 5350EXECUTE IMMEDIATE CONCAT('ANALYZE TABLE ',tn); 5351CALL p1('t1'); 5352Table Op Msg_type Msg_text 5353test.t1 analyze status Engine-independent statistics collected 5354test.t1 analyze status Table is already up to date 5355DROP PROCEDURE p1; 5356DROP TABLE t1; 5357SET NAMES utf8; 5358CREATE PROCEDURE p1() 5359EXECUTE IMMEDIATE CONCAT('SELECT ',CONVERT(RAND() USING latin1)); 5360CALL p1(); 5361DROP PROCEDURE p1; 5362SET NAMES utf8; 5363CREATE PROCEDURE p1() 5364BEGIN 5365PREPARE stmt FROM CONCAT('SELECT ',CONVERT(RAND() USING latin1)); 5366EXECUTE stmt; 5367DEALLOCATE PREPARE stmt; 5368END; 5369$$ 5370CALL p1(); 5371DROP PROCEDURE p1; 5372SET NAMES utf8; 5373CREATE PROCEDURE p1(a VARCHAR(10) CHARACTER SET utf8) 5374EXECUTE IMMEDIATE 'SELECT ?' USING CONCAT(a, CONVERT(RAND() USING latin1)); 5375CALL p1('x'); 5376DROP PROCEDURE p1; 5377SET NAMES utf8; 5378CREATE PROCEDURE p1(a VARCHAR(10) CHARACTER SET utf8) 5379BEGIN 5380PREPARE stmt FROM 'SELECT ?'; 5381EXECUTE stmt USING CONCAT(a, CONVERT(RAND() USING latin1)); 5382DEALLOCATE PREPARE stmt; 5383END; 5384$$ 5385CALL p1('x'); 5386DROP PROCEDURE p1; 5387create table t1 (b blob default ''); 5388prepare stmt from "alter table t1 force"; 5389execute stmt; 5390execute stmt; 5391execute stmt; 5392set names latin1; 5393prepare stmt from "alter table t1 modify b text character set utf8 default 'a'"; 5394execute stmt; 5395execute stmt; 5396execute stmt; 5397drop table t1; 5398# 5399# MDEV-12060 Crash in EXECUTE IMMEDIATE with an expression returning a GRANT command 5400# 5401CREATE ROLE testrole; 5402CREATE OR REPLACE PROCEDURE p1() 5403BEGIN 5404END; 5405/ 5406CREATE PROCEDURE p2 (wgrp VARCHAR(10)) 5407BEGIN 5408EXECUTE IMMEDIATE concat('GRANT EXECUTE ON PROCEDURE p1 TO ',wgrp); 5409END; 5410/ 5411CALL p2('testrole'); 5412DROP PROCEDURE p2; 5413CREATE PROCEDURE p2 () 5414BEGIN 5415EXECUTE IMMEDIATE concat(_utf8'GRANT EXECUTE ON PROCEDURE p1 TO ',_latin1'testrole'); 5416END; 5417/ 5418CALL p2(); 5419DROP PROCEDURE p2; 5420CREATE PROCEDURE p2 () 5421BEGIN 5422PREPARE stmt FROM concat(_utf8'GRANT EXECUTE ON PROCEDURE p1 TO ',_latin1' testrole'); 5423EXECUTE stmt; 5424DEALLOCATE PREPARE stmt; 5425END; 5426/ 5427CALL p2(); 5428DROP PROCEDURE p2; 5429DROP PROCEDURE p1; 5430DROP ROLE testrole; 5431# 5432# MDEV-16992: prepare of CREATE TABLE, CREATE VIEW, DO, SET, CALL 5433# statements with CTE containing materialized derived 5434# (the bug is reproducible on 10.4) 5435# 5436prepare stmt from 5437"CREATE TABLE t1 AS 5438 WITH cte(a) AS (SELECT * FROM (SELECT 1) AS t) SELECT * FROM cte;"; 5439execute stmt; 5440select * from t1; 5441a 54421 5443prepare stmt from 5444"CREATE VIEW v1 AS 5445 WITH cte(a) AS (SELECT * FROM (SELECT 1) AS t) SELECT * FROM cte;"; 5446execute stmt; 5447select * from v1; 5448a 54491 5450prepare stmt from 5451"DO (SELECT 1 5452 FROM (WITH cte AS (SELECT * FROM (SELECT 1) AS t) 5453 SELECT * FROM cte) AS tt);"; 5454execute stmt; 5455prepare stmt from 5456"SET @a = (SELECT 1 5457 FROM (WITH cte AS (SELECT * FROM (SELECT 1) AS t) 5458 SELECT * FROM cte) AS t);"; 5459execute stmt; 5460create procedure p (i int) insert into t1 values(i); 5461prepare stmt from 5462"CALL p 5463 ((SELECT 1 5464 FROM (WITH cte AS (SELECT * FROM (SELECT 1) AS t) 5465 SELECT * FROM cte) AS tt));"; 5466execute stmt; 5467select * from t1; 5468a 54691 54701 5471drop procedure p; 5472drop view v1; 5473drop table t1; 5474# 5475# MDEV-22591 Debug build crashes on EXECUTE IMMEDIATE '... WHERE ?' USING IGNORE 5476# 5477CREATE TABLE t1 (a INT); 5478EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE ?' USING IGNORE; 5479ERROR HY000: Default/ignore value is not supported for such parameter usage 5480EXECUTE IMMEDIATE 'SELECT * FROM t1 HAVING ?' USING IGNORE; 5481ERROR HY000: Default/ignore value is not supported for such parameter usage 5482EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE ?' USING 0; 5483a 5484EXECUTE IMMEDIATE 'SELECT * FROM t1 HAVING ?' USING 0; 5485a 5486DROP TABLE t1; 5487EXECUTE IMMEDIATE 'SHOW DATABASES WHERE ?' USING DEFAULT; 5488ERROR HY000: Default/ignore value is not supported for such parameter usage 5489EXECUTE IMMEDIATE 'SHOW DATABASES WHERE ?' USING 0; 5490Database 5491# 5492# MDEV-24779: main.subselect fails in buildbot with --ps-protocol 5493# 5494CREATE TABLE t1(a INT); 5495PREPARE stmt FROM "SELECT EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1))"; 5496EXECUTE stmt; 5497EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1)) 54980 5499EXECUTE stmt; 5500EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1)) 55010 5502DROP TABLE t1; 5503# 5504# MDEV-25006: Failed assertion on executing EXPLAIN DELETE statement as a prepared statement 5505# 5506CREATE TABLE t1(c1 CHAR(255) PRIMARY KEY); 5507PREPARE stmt FROM 'EXPLAIN DELETE b FROM t1 AS a JOIN t1 AS b'; 5508EXECUTE stmt; 5509id select_type table type possible_keys key key_len ref rows Extra 55101 SIMPLE a system NULL NULL NULL NULL 0 Const row not found 55111 SIMPLE b system NULL NULL NULL NULL 0 Const row not found 5512DROP TABLE t1; 5513CREATE TABLE t1(a INT); 5514PREPARE stmt FROM 'EXPLAIN DELETE FROM t1.* USING t1'; 5515EXECUTE stmt; 5516id select_type table type possible_keys key key_len ref rows Extra 55171 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found 5518DEALLOCATE PREPARE stmt; 5519DROP TABLE t1; 5520# 5521# MDEV-25108: Running of the EXPLAIN EXTENDED statement produces extra warning 5522# in case it is executed in PS (prepared statement) mode 5523# 5524CREATE TABLE t1 (c int); 5525CREATE TABLE t2 (d int); 5526# EXPLAIN EXTENDED in regular way (not PS mode) 5527EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1; 5528id select_type table type possible_keys key key_len ref rows filtered Extra 55291 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 Const row not found 55302 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5531Warnings: 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` 5534SHOW WARNINGS; 5535Level Code Message 5536Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 5537Note 1003 /* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1` 5538# Now run the same EXPLAIN EXTENDED in PS mode. Number of warnings 5539# and their content must be the same as in case running the statement 5540# in regular way 5541PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1"; 5542Warnings: 5543Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 5544EXECUTE stmt; 5545id select_type table type possible_keys key key_len ref rows filtered Extra 55461 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 Const row not found 55472 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5548Warnings: 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` 5551SHOW WARNINGS; 5552Level Code Message 5553Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 5554Note 1003 /* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1` 5555DEALLOCATE PREPARE stmt; 5556DROP TABLE t1, t2; 5557# 5558# MDEV-25576: The statement EXPLAIN running as regular statement and 5559# as prepared statement produces different results for 5560# UPDATE with subquery 5561# 5562CREATE TABLE t1 (c1 INT KEY) ENGINE=MyISAM; 5563CREATE TABLE t2 (c2 INT) ENGINE=MyISAM; 5564CREATE TABLE t3 (c3 INT) ENGINE=MyISAM; 5565EXPLAIN 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 ); 5566id select_type table type possible_keys key key_len ref rows filtered Extra 55671 PRIMARY t3 ALL NULL NULL NULL NULL 0 100.00 55682 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5569PREPARE 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 )"; 5570EXECUTE stmt; 5571id select_type table type possible_keys key key_len ref rows filtered Extra 55721 PRIMARY t3 ALL NULL NULL NULL NULL 0 100.00 55732 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5574DEALLOCATE PREPARE stmt; 5575DROP TABLE t1, t2, t3; 5576# 5577# MDEV-21866: Assertion `!result' failed in convert_const_to_int upon 2nd execution of PS 5578# 5579CREATE TABLE t1 (a BIGINT DEFAULT -1); 5580CREATE VIEW v1 AS SELECT DISTINCT a FROM t1; 5581PREPARE stmt FROM 'SELECT * FROM v1 WHERE a <=> NULL'; 5582EXECUTE stmt; 5583a 5584EXECUTE stmt; 5585a 5586DEALLOCATE PREPARE stmt; 5587DROP VIEW v1; 5588DROP TABLE t1; 5589# End of 10.2 tests 5590# 5591# 5592# MDEV-26147: The test main.sp-row fails in case it is run in PS mode 5593# 5594CREATE PROCEDURE p1(a ROW(a INT,b INT)) 5595BEGIN 5596SELECT a.a, a.b; 5597END; 5598$$ 5599PREPARE stmt FROM 'CALL p1(ROW(10, 20))'; 5600EXECUTE stmt; 5601a.a a.b 560210 20 5603DEALLOCATE PREPARE stmt; 5604DROP PROCEDURE p1; 5605# 5606# MDEV-19263: Server crashes in mysql_handle_single_derived 5607# upon 2nd execution of PS 5608# 5609CREATE TABLE t1 (f INT); 5610CREATE VIEW v1 AS SELECT * FROM t1; 5611CREATE TRIGGER tr BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO v1 SELECT * FROM x; 5612PREPARE stmt FROM "INSERT INTO v1 VALUES (1)"; 5613EXECUTE stmt; 5614ERROR 42S02: Table 'test.x' doesn't exist 5615EXECUTE stmt; 5616ERROR 42S02: Table 'test.x' doesn't exist 5617DEALLOCATE PREPARE stmt; 5618DROP VIEW v1; 5619DROP TABLE t1; 5620# 5621# MDEV-25197: The statement set password=password('') executed in PS mode 5622# fails in case it is run by a user with expired password 5623# 5624CREATE USER user1@localhost PASSWORD EXPIRE; 5625SET @disconnect_on_expired_password_save=@@global.disconnect_on_expired_password; 5626SET GLOBAL disconnect_on_expired_password=OFF; 5627connect con1,localhost,user1; 5628connection con1; 5629# Check that no regular statement like SELECT can be prepared 5630# by a user with an expired password 5631PREPARE stmt FROM "SELECT 1"; 5632ERROR HY000: You must SET PASSWORD before executing this statement 5633# Check that the DEALLOCATE PREPARE statement can be run by a user 5634# with an expired password 5635PREPARE stmt FROM "SET password=password('')"; 5636DEALLOCATE PREPARE stmt; 5637# Check that the SET PASSWORD statement can be executed in PS mode by 5638# a user with an expired password 5639PREPARE stmt FROM "SET password=password('')"; 5640EXECUTE stmt; 5641PREPARE stmt FROM "SELECT 1"; 5642# Check that user's password is not expired anymore 5643EXECUTE stmt; 56441 56451 5646DEALLOCATE PREPARE stmt; 5647# Clean up 5648disconnect con1; 5649connection default; 5650SET GLOBAL disconnect_on_expired_password=@disconnect_on_expired_password_save; 5651DROP USER user1@localhost; 5652# 5653# End of 10.4 tests 5654# 5655