1
call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.');
2
drop table if exists t1,t2,t3,t4;
3
drop database if exists client_test_db;
9
insert into t1 values (1,'one');
10
insert into t1 values (2,'two');
11
insert into t1 values (3,'three');
12
insert into t1 values (4,'four');
14
prepare stmt1 from 'select * from t1 where a <= ?';
15
execute stmt1 using @a;
20
execute stmt1 using @a;
25
deallocate prepare no_such_statement;
26
ERROR HY000: Unknown prepared statement handler (no_such_statement) given to DEALLOCATE PREPARE
28
ERROR HY000: Incorrect arguments to EXECUTE
29
prepare stmt2 from 'prepare nested_stmt from "select 1"';
30
ERROR HY000: This command is not supported in the prepared statement protocol yet
31
prepare stmt2 from 'execute stmt1';
32
ERROR HY000: This command is not supported in the prepared statement protocol yet
33
prepare stmt2 from 'deallocate prepare z';
34
ERROR HY000: This command is not supported in the prepared statement protocol yet
35
prepare stmt3 from 'insert into t1 values (?,?)';
36
set @arg1=5, @arg2='five';
37
execute stmt3 using @arg1, @arg2;
38
select * from t1 where a>3;
42
prepare stmt4 from 'update t1 set a=? where b=?';
43
set @arg1=55, @arg2='five';
44
execute stmt4 using @arg1, @arg2;
45
select * from t1 where a>3;
49
prepare stmt4 from 'create table t2 (a int)';
51
prepare stmt4 from 'drop table t2';
54
ERROR 42S02: Unknown table 't2'
55
prepare stmt5 from 'select ? + a from t1';
57
execute stmt5 using @a;
64
execute stmt5 using @no_such_var;
73
execute stmt5 using @nullvar;
81
execute stmt5 using @nullvar2;
88
prepare stmt6 from 'select 1; select2';
89
ERROR 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
90
prepare stmt6 from 'insert into t1 values (5,"five"); select2';
91
ERROR 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
92
explain prepare stmt6 from 'insert into t1 values (5,"five"); select2';
93
ERROR 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
98
insert into t2 values (0);
100
prepare stmt1 from 'select 1 FROM t2 where a=?' ;
101
execute stmt1 using @arg00 ;
103
prepare stmt1 from @nosuchvar;
104
ERROR 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
106
prepare stmt1 from @ivar;
107
ERROR 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
109
prepare stmt1 from @fvar;
110
ERROR 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
112
deallocate prepare stmt3;
113
deallocate prepare stmt4;
114
deallocate prepare stmt5;
115
PREPARE stmt1 FROM "select _utf8 'A' collate utf8_bin = ?";
117
EXECUTE stmt1 USING @var;
118
_utf8 'A' collate utf8_bin = ?
120
DEALLOCATE PREPARE stmt1;
121
create table t1 (id int);
122
prepare stmt1 from "select FOUND_ROWS()";
123
select SQL_CALC_FOUND_ROWS * from t1;
128
insert into t1 values (1);
129
select SQL_CALC_FOUND_ROWS * from t1;
138
deallocate prepare stmt1;
142
c1 tinyint, c2 smallint, c3 mediumint, c4 int,
143
c5 integer, c6 bigint, c7 float, c8 double,
144
c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
145
c13 date, c14 datetime, c15 timestamp, c16 time,
146
c17 year, c18 bit, c19 bool, c20 char,
147
c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
148
c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
149
c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'),
150
c32 set('monday', 'tuesday', 'wednesday')
152
create table t2 like t1;
153
set @save_optimizer_switch=@@optimizer_switch;
154
set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off";
155
set @tmp_optimizer_switch=@@optimizer_switch;
156
set optimizer_switch='derived_merge=off,derived_with_keys=off';
157
set @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 ' ;
158
prepare stmt1 from @stmt ;
160
id select_type table type possible_keys key key_len ref rows Extra
161
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
162
6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
163
5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
164
4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
165
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
166
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
168
id select_type table type possible_keys key key_len ref rows Extra
169
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
170
6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
171
5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
172
4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
173
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
174
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
175
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;
176
id select_type table type possible_keys key key_len ref rows Extra
177
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
178
6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
179
5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
180
4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
181
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
182
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
183
deallocate prepare stmt1;
184
set optimizer_switch=@tmp_optimizer_switch;
186
set @@optimizer_switch=@save_optimizer_switch;
188
prepare stmt1 from ' create table t1 (m int) as select 1 as m ' ;
194
prepare stmt1 from ' create table t1 (m int) as select ? as m ' ;
195
execute stmt1 using @arg00;
199
deallocate prepare stmt1;
201
create table t1 (id int(10) unsigned NOT NULL default '0',
202
name varchar(64) NOT NULL default '',
203
PRIMARY KEY (id), UNIQUE KEY `name` (`name`));
204
insert into t1 values (1,'1'),(2,'2'),(3,'3'),(4,'4'),(5,'5'),(6,'6'),(7,'7');
205
prepare stmt1 from 'select name from t1 where id=? or id=?';
207
execute stmt1 using @id1, @id2;
211
select name from t1 where id=1 or id=6;
215
deallocate prepare stmt1;
217
create table t1 ( a int primary key, b varchar(30)) engine = MYISAM ;
218
prepare stmt1 from ' show table status from test like ''t1%'' ';
220
Name 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
221
t1 MyISAM 10 Dynamic 0 0 0 4294967295 1024 0 NULL # # # latin1_swedish_ci NULL
222
show table status from test like 't1%' ;
223
Name 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
224
t1 MyISAM 10 Dynamic 0 0 0 4294967295 1024 0 NULL # # # latin1_swedish_ci NULL
225
deallocate prepare stmt1 ;
227
create table t1(a varchar(2), b varchar(3));
228
prepare stmt1 from "select a, b from t1 where (not (a='aa' and b < 'zzz'))";
233
deallocate prepare stmt1;
235
prepare stmt1 from "select 1 into @var";
238
prepare stmt1 from "create table t1 select 1 as i";
242
prepare stmt1 from "insert into t1 select i from t1";
245
prepare stmt1 from "select * from t1 into outfile '<MYSQLTEST_VARDIR>/tmp/f1.txt'";
247
deallocate prepare stmt1;
249
prepare stmt1 from 'select 1';
250
prepare STMT1 from 'select 2';
254
deallocate prepare StMt1;
255
deallocate prepare Stmt1;
256
ERROR HY000: Unknown prepared statement handler (Stmt1) given to DEALLOCATE PREPARE
258
prepare `ü` from 'select 1234';
266
deallocate prepare `ü`;
268
create table t1 (a varchar(10)) charset=utf8;
269
insert into t1 (a) values ('yahoo');
270
set character_set_connection=latin1;
271
prepare stmt from 'select a from t1 where a like ?';
273
execute stmt using @var;
275
execute stmt using @var;
277
deallocate prepare stmt;
279
create table t1 (a bigint(20) not null primary key auto_increment);
280
insert into t1 (a) values (null);
284
prepare stmt from "insert into t1 (a) values (?)";
286
execute stmt using @var;
292
create table t1 (a timestamp not null);
293
prepare stmt from "insert into t1 (a) values (?)";
294
execute stmt using @var;
296
deallocate prepare stmt;
298
prepare stmt from "select 'abc' like convert('abc' using utf8)";
300
'abc' like convert('abc' using utf8)
303
'abc' like convert('abc' using utf8)
305
deallocate prepare stmt;
306
create table t1 ( a bigint );
307
prepare stmt from 'select a from t1 where a between ? and ?';
309
execute stmt using @a, @a;
311
execute stmt using @a, @a;
313
execute stmt using @a, @a;
316
deallocate prepare stmt;
317
create table t1 (a int);
318
prepare stmt from "select * from t1 where 1 > (1 in (SELECT * FROM t1))";
326
deallocate prepare stmt;
327
create table t1 (a int, b int);
328
insert into t1 (a, b) values (1,1), (1,2), (2,1), (2,2);
330
"explain select * from t1 where t1.a=2 and t1.a=t1.b and t1.b > 1 + ?";
332
execute stmt using @v;
333
id select_type table type possible_keys key key_len ref rows Extra
334
- - - - - - - - NULL Impossible WHERE
336
execute stmt using @v;
337
id select_type table type possible_keys key key_len ref rows Extra
338
- - - - - - - - 4 Using where
340
execute stmt using @v;
341
id select_type table type possible_keys key key_len ref rows Extra
342
- - - - - - - - NULL Impossible WHERE
344
deallocate prepare stmt;
345
create table t1 (a int);
346
insert into t1 (a) values (1), (2), (3), (4);
347
set @precision=10000000000;
349
cast(rand(10)*@precision as unsigned integer) from t1;
350
rand() cast(rand(10)*@precision as unsigned integer)
357
cast(rand(10)*@precision as unsigned integer),
358
cast(rand(?)*@precision as unsigned integer) from t1";
360
execute stmt using @var;
361
rand() cast(rand(10)*@precision as unsigned integer) cast(rand(?)*@precision as unsigned integer)
367
execute stmt using @var;
368
rand() cast(rand(10)*@precision as unsigned integer) cast(rand(?)*@precision as unsigned integer)
369
- 6570515220 6555866465
370
- 1282061302 1223466193
371
- 6698761160 6449731874
372
- 9647622201 8578261098
374
execute stmt using @var;
375
rand() cast(rand(10)*@precision as unsigned integer) cast(rand(?)*@precision as unsigned integer)
376
- 6570515220 9057697560
377
- 1282061302 3730790581
378
- 6698761160 1480860535
379
- 9647622201 6211931236
381
deallocate prepare stmt;
382
create database mysqltest1;
383
create table t1 (a int);
384
create table mysqltest1.t1 (a int);
385
select * from t1, mysqltest1.t1;
387
prepare stmt from "select * from t1, mysqltest1.t1";
395
drop table mysqltest1.t1;
396
drop database mysqltest1;
397
deallocate prepare stmt;
398
select '1.1' as a, '1.2' as a UNION SELECT '2.1', '2.2';
403
"select '1.1' as a, '1.2' as a UNION SELECT '2.1', '2.2'";
416
deallocate prepare stmt;
417
create table t1 (a int);
418
insert into t1 values (1),(2),(3);
419
create table t2 select * from t1;
420
prepare stmt FROM 'create table t2 select * from t1';
426
ERROR 42S01: Table 't2' already exists
430
deallocate prepare stmt;
431
create table t1 (a int);
432
insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
433
prepare stmt from "select sql_calc_found_rows * from t1 limit 2";
455
deallocate prepare stmt;
457
CREATE TABLE t1 (N int, M tinyint);
458
INSERT INTO t1 VALUES (1,0),(1,0),(2,0),(2,0),(3,0);
459
PREPARE 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';
461
DEALLOCATE PREPARE stmt;
463
prepare stmt from "select ? is null, ? is not null, ?";
464
select @no_such_var is null, @no_such_var is not null, @no_such_var;
465
@no_such_var is null @no_such_var is not null @no_such_var
467
execute stmt using @no_such_var, @no_such_var, @no_such_var;
468
? is null ? is not null ?
471
select @var is null, @var is not null, @var;
472
@var is null @var is not null @var
474
execute stmt using @var, @var, @var;
475
? is null ? is not null ?
478
select @var is null, @var is not null, @var;
479
@var is null @var is not null @var
481
execute stmt using @var, @var, @var;
482
? is null ? is not null ?
484
create table t1 (pnum char(3));
485
create table t2 (pnum char(3));
486
prepare stmt from "select pnum from t2 having pnum in (select 'p1' from t1)";
493
deallocate prepare stmt;
495
drop table if exists t1;
496
create temporary table if not exists t1 (a1 int);
497
prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1";
498
drop temporary table t1;
499
create temporary table if not exists t1 (a1 int);
501
drop temporary table t1;
502
create temporary table if not exists t1 (a1 int);
504
drop temporary table t1;
505
create temporary table if not exists t1 (a1 int);
507
drop temporary table t1;
508
deallocate prepare stmt;
509
create table t1 (a varchar(20));
510
insert into t1 values ('foo');
511
prepare stmt FROM 'SELECT char_length (a) FROM t1';
512
prepare stmt2 FROM 'SELECT not_a_function (a) FROM t1';
513
ERROR 42000: FUNCTION test.not_a_function does not exist
515
prepare stmt from "SELECT SQL_CALC_FOUND_ROWS 'foo' UNION SELECT 'bar' LIMIT 0";
526
deallocate prepare stmt;
527
drop table if exists t1;
529
Note 1051 Unknown table 't1'
530
create table t1 (c1 int(11) not null, c2 int(11) not null,
531
primary key (c1,c2), key c2 (c2), key c1 (c1));
532
insert into t1 values (200887, 860);
533
insert into t1 values (200887, 200887);
534
select * from t1 where (c1=200887 and c2=200887) or c2=860;
539
"select * from t1 where (c1=200887 and c2=200887) or c2=860";
545
"select * from t1 where (c1=200887 and c2=?) or c2=?";
546
set @a=200887, @b=860;
547
execute stmt using @a, @b;
551
deallocate prepare stmt;
554
id bigint(20) not null auto_increment,
555
code varchar(20) character set utf8 collate utf8_bin not null default '',
556
company_name varchar(250) character set utf8 collate utf8_bin default null,
557
setup_mode tinyint(4) default null,
558
start_date datetime default null,
559
primary key (id), unique key code (code)
562
id bigint(20) not null auto_increment,
563
email varchar(250) character set utf8 collate utf8_bin default null,
564
name varchar(250) character set utf8 collate utf8_bin default null,
565
t1_id bigint(20) default null,
566
password varchar(250) character set utf8 collate utf8_bin default null,
567
primary_contact tinyint(4) not null default '0',
568
email_opt_in tinyint(4) not null default '1',
569
primary key (id), unique key email (email), key t1_id (t1_id),
570
constraint t2_fk1 foreign key (t1_id) references t1 (id)
572
insert into t1 values
573
(1, 'demo', 'demo s', 0, current_date()),
574
(2, 'code2', 'name 2', 0, current_date()),
575
(3, 'code3', 'name 3', 0, current_date());
576
insert into t2 values
577
(2, 'email1', 'name1', 3, 'password1', 0, 0),
578
(3, 'email2', 'name1', 1, 'password2', 1, 0),
579
(5, 'email3', 'name3', 2, 'password3', 0, 0);
580
prepare stmt from 'select t2.id from t2, t1 where (t1.id=? and t2.t1_id=t1.id)';
582
execute stmt using @a;
585
select t2.id from t2, t1 where (t1.id=1 and t2.t1_id=t1.id);
588
deallocate prepare stmt;
590
create table t1 (id int);
591
prepare stmt from "insert into t1 (id) select id from t1 union select id from t1";
594
deallocate prepare stmt;
597
id int(11) unsigned not null primary key auto_increment,
598
partner_id varchar(35) not null,
599
t1_status_id int(10) unsigned
601
insert into t1 values ("1", "partner1", "10"), ("2", "partner2", "10"),
602
("3", "partner3", "10"), ("4", "partner4", "10");
604
id int(11) unsigned not null default '0',
605
t1_line_id int(11) unsigned not null default '0',
606
article_id varchar(20),
607
sequence int(11) not null default '0',
608
primary key (id,t1_line_id)
610
insert into t2 values ("1", "1", "sup", "0"), ("2", "1", "sup", "1"),
611
("2", "2", "sup", "2"), ("2", "3", "sup", "3"),
612
("2", "4", "imp", "4"), ("3", "1", "sup", "0"),
613
("4", "1", "sup", "0");
615
id int(11) not null default '0',
616
preceeding_id int(11) not null default '0',
617
primary key (id,preceeding_id)
620
user_id varchar(50) not null,
621
article_id varchar(20) not null,
622
primary key (user_id,article_id)
624
insert into t4 values("nicke", "imp");
626
'select distinct t1.partner_id
627
from t1 left join t3 on t1.id = t3.id
628
left join t1 pp on pp.id = t3.preceeding_id
633
where pl_inner.id = t1.id
634
and pl_inner.sequence <= (
635
select min(sequence) from t2 pl_seqnr
636
where pl_seqnr.id = t1.id
640
where t4.article_id = pl_inner.article_id
646
having count(pp.id) = 0';
647
set @user_id = 'nicke';
649
execute stmt using @user_id, @id;
651
execute stmt using @user_id, @id;
653
deallocate prepare stmt;
654
drop table t1, t2, t3, t4;
655
prepare stmt from 'select ?=?';
658
execute stmt using @a, @b;
661
execute stmt using @a, @b;
665
execute stmt using @a, @b;
670
execute stmt using @a, @b;
673
deallocate prepare stmt;
674
create table t1 (a int);
675
prepare stmt from "select ??";
676
ERROR 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
677
prepare stmt from "select ?FROM t1";
678
ERROR 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
679
prepare stmt from "select FROM t1 WHERE?=1";
680
ERROR 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
681
prepare stmt from "update t1 set a=a+?WHERE 1";
682
ERROR 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
684
ERROR 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
686
ERROR 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
688
ERROR 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
690
prepare stmt from "select @@time_zone";
694
set @@time_zone:='Japan';
698
prepare stmt from "select @@tx_isolation";
702
set transaction isolation level read committed;
706
set transaction isolation level serializable;
710
set @@tx_isolation=default;
714
deallocate prepare stmt;
715
prepare stmt from "create temporary table t1 (letter enum('','a','b','c')
724
prepare stmt from "create table t1 (a enum('test') default 'test')
733
deallocate prepare stmt;
735
word_id mediumint(8) unsigned not null default '0',
736
formatted varchar(20) not null default ''
738
insert into t1 values
739
(80,'pendant'), (475,'pretendants'), (989,'tendances'),
740
(1019,'cependant'),(1022,'abondance'),(1205,'independants'),
741
(13,'lessiver'),(25,'lambiner'),(46,'situer'),(71,'terminer'),
743
select count(*) from t1 where formatted like '%NDAN%';
746
select count(*) from t1 where formatted like '%ER';
749
prepare stmt from "select count(*) from t1 where formatted like ?";
751
execute stmt using @like;
755
execute stmt using @like;
759
execute stmt using @like;
763
execute stmt using @like;
766
deallocate prepare stmt;
768
prepare stmt from 'create table t1 (a varchar(10) character set utf8)';
770
insert into t1 (a) values (repeat('a', 20));
771
select length(a) from t1;
776
insert into t1 (a) values (repeat('a', 20));
777
select length(a) from t1;
781
deallocate prepare stmt;
782
create table t1 (col1 integer, col2 integer);
783
insert into t1 values(100,100),(101,101),(102,102),(103,103);
784
prepare stmt from 'select col1, col2 from t1 where (col1, col2) in ((?,?))';
786
execute stmt using @a,@b;
790
execute stmt using @a,@b;
794
execute stmt using @a,@b;
798
execute stmt using @a,@b;
800
deallocate prepare stmt;
802
set @old_max_prepared_stmt_count= @@max_prepared_stmt_count;
803
show variables like 'max_prepared_stmt_count';
805
max_prepared_stmt_count 16382
806
show status like 'prepared_stmt_count';
808
Prepared_stmt_count 0
809
select @@max_prepared_stmt_count;
810
@@max_prepared_stmt_count
812
set global max_prepared_stmt_count=-1;
814
Warning 1292 Truncated incorrect max_prepared_stmt_count value: '-1'
815
select @@max_prepared_stmt_count;
816
@@max_prepared_stmt_count
818
set global max_prepared_stmt_count=10000000000000000;
820
Warning 1292 Truncated incorrect max_prepared_stmt_count value: '10000000000000000'
821
select @@max_prepared_stmt_count;
822
@@max_prepared_stmt_count
824
set global max_prepared_stmt_count=default;
825
select @@max_prepared_stmt_count;
826
@@max_prepared_stmt_count
828
set @@max_prepared_stmt_count=1;
829
ERROR HY000: Variable 'max_prepared_stmt_count' is a GLOBAL variable and should be set with SET GLOBAL
830
set max_prepared_stmt_count=1;
831
ERROR HY000: Variable 'max_prepared_stmt_count' is a GLOBAL variable and should be set with SET GLOBAL
832
set local max_prepared_stmt_count=1;
833
ERROR HY000: Variable 'max_prepared_stmt_count' is a GLOBAL variable and should be set with SET GLOBAL
834
set global max_prepared_stmt_count=1;
835
select @@max_prepared_stmt_count;
836
@@max_prepared_stmt_count
838
set global max_prepared_stmt_count=0;
839
select @@max_prepared_stmt_count;
840
@@max_prepared_stmt_count
842
show status like 'prepared_stmt_count';
844
Prepared_stmt_count 0
845
prepare stmt from "select 1";
846
ERROR 42000: Can't create more than max_prepared_stmt_count statements (current value: 0)
847
show status like 'prepared_stmt_count';
849
Prepared_stmt_count 0
850
set global max_prepared_stmt_count=1;
851
prepare stmt from "select 1";
852
show status like 'prepared_stmt_count';
854
Prepared_stmt_count 1
855
prepare stmt1 from "select 1";
856
ERROR 42000: Can't create more than max_prepared_stmt_count statements (current value: 1)
857
show status like 'prepared_stmt_count';
859
Prepared_stmt_count 1
860
deallocate prepare stmt;
861
show status like 'prepared_stmt_count';
863
Prepared_stmt_count 0
864
prepare stmt from "select 1";
865
show status like 'prepared_stmt_count';
867
Prepared_stmt_count 1
868
prepare stmt from "select 2";
869
show status like 'prepared_stmt_count';
871
Prepared_stmt_count 1
872
show status like 'prepared_stmt_count';
874
Prepared_stmt_count 1
875
select @@max_prepared_stmt_count;
876
@@max_prepared_stmt_count
878
set global max_prepared_stmt_count=0;
879
prepare stmt from "select 1";
880
ERROR 42000: Can't create more than max_prepared_stmt_count statements (current value: 0)
882
ERROR HY000: Unknown prepared statement handler (stmt) given to EXECUTE
883
show status like 'prepared_stmt_count';
885
Prepared_stmt_count 0
886
prepare stmt from "select 1";
887
ERROR 42000: Can't create more than max_prepared_stmt_count statements (current value: 0)
888
show status like 'prepared_stmt_count';
890
Prepared_stmt_count 0
891
set global max_prepared_stmt_count=3;
892
select @@max_prepared_stmt_count;
893
@@max_prepared_stmt_count
895
show status like 'prepared_stmt_count';
897
Prepared_stmt_count 0
898
prepare stmt from "select 1";
899
prepare stmt from "select 2";
900
prepare stmt1 from "select 3";
901
prepare stmt2 from "select 4";
902
ERROR 42000: Can't create more than max_prepared_stmt_count statements (current value: 3)
903
prepare stmt2 from "select 4";
904
ERROR 42000: Can't create more than max_prepared_stmt_count statements (current value: 3)
905
select @@max_prepared_stmt_count;
906
@@max_prepared_stmt_count
908
show status like 'prepared_stmt_count';
910
Prepared_stmt_count 3
911
deallocate prepare stmt;
912
select @@max_prepared_stmt_count;
913
@@max_prepared_stmt_count
915
show status like 'prepared_stmt_count';
917
Prepared_stmt_count 0
918
set global max_prepared_stmt_count= @old_max_prepared_stmt_count;
919
drop table if exists t1;
920
create temporary table if not exists t1 (a1 int);
921
prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1";
922
drop temporary table t1;
923
create temporary table if not exists t1 (a1 int);
925
drop temporary table t1;
926
create temporary table if not exists t1 (a1 int);
928
drop temporary table t1;
929
create temporary table if not exists t1 (a1 int);
931
drop temporary table t1;
932
deallocate prepare stmt;
934
ID int(10) unsigned NOT NULL auto_increment,
935
Member_ID varchar(15) NOT NULL default '',
936
Action varchar(12) NOT NULL,
937
Action_Date datetime NOT NULL,
938
Track varchar(15) default NULL,
939
User varchar(12) default NULL,
940
Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update
944
KEY Action_Date (Action_Date)
946
INSERT INTO t1(Member_ID, Action, Action_Date, Track) VALUES
947
('111111', 'Disenrolled', '2006-03-01', 'CAD' ),
948
('111111', 'Enrolled', '2006-03-01', 'CAD' ),
949
('111111', 'Disenrolled', '2006-07-03', 'CAD' ),
950
('222222', 'Enrolled', '2006-03-07', 'CAD' ),
951
('222222', 'Enrolled', '2006-03-07', 'CHF' ),
952
('222222', 'Disenrolled', '2006-08-02', 'CHF' ),
953
('333333', 'Enrolled', '2006-03-01', 'CAD' ),
954
('333333', 'Disenrolled', '2006-03-01', 'CAD' ),
955
('444444', 'Enrolled', '2006-03-01', 'CAD' ),
956
('555555', 'Disenrolled', '2006-03-01', 'CAD' ),
957
('555555', 'Enrolled', '2006-07-21', 'CAD' ),
958
('555555', 'Disenrolled', '2006-03-01', 'CHF' ),
959
('666666', 'Enrolled', '2006-02-09', 'CAD' ),
960
('666666', 'Enrolled', '2006-05-12', 'CHF' ),
961
('666666', 'Disenrolled', '2006-06-01', 'CAD' );
963
"SELECT GROUP_CONCAT(Track SEPARATOR ', ') FROM t1
964
WHERE Member_ID=? AND Action='Enrolled' AND
965
(Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t1
968
HAVING Track>='CAD' AND
969
MAX(Action_Date)>'2006-03-01')";
971
EXECUTE STMT USING @id,@id;
972
GROUP_CONCAT(Track SEPARATOR ', ')
975
EXECUTE STMT USING @id,@id;
976
GROUP_CONCAT(Track SEPARATOR ', ')
978
DEALLOCATE PREPARE STMT;
980
DROP TABLE IF EXISTS t1;
981
CREATE TABLE t1 (i INT, INDEX(i));
982
INSERT INTO t1 VALUES (1);
983
PREPARE stmt FROM "SELECT (COUNT(i) = 1), COUNT(i) FROM t1 WHERE i = ?";
985
EXECUTE stmt USING @a;
986
(COUNT(i) = 1) COUNT(i)
989
EXECUTE stmt USING @a;
990
(COUNT(i) = 1) COUNT(i)
993
EXECUTE stmt USING @a;
994
(COUNT(i) = 1) COUNT(i)
996
PREPARE stmt FROM "SELECT (AVG(i) = 1), AVG(i) FROM t1 WHERE i = ?";
998
EXECUTE stmt USING @a;
1002
EXECUTE stmt USING @a;
1006
EXECUTE stmt USING @a;
1009
PREPARE stmt FROM "SELECT (VARIANCE(i) = 1), VARIANCE(i) FROM t1 WHERE i = ?";
1011
EXECUTE stmt USING @a;
1012
(VARIANCE(i) = 1) VARIANCE(i)
1015
EXECUTE stmt USING @a;
1016
(VARIANCE(i) = 1) VARIANCE(i)
1019
EXECUTE stmt USING @a;
1020
(VARIANCE(i) = 1) VARIANCE(i)
1022
PREPARE stmt FROM "SELECT (STDDEV(i) = 1), STDDEV(i) FROM t1 WHERE i = ?";
1024
EXECUTE stmt USING @a;
1025
(STDDEV(i) = 1) STDDEV(i)
1028
EXECUTE stmt USING @a;
1029
(STDDEV(i) = 1) STDDEV(i)
1032
EXECUTE stmt USING @a;
1033
(STDDEV(i) = 1) STDDEV(i)
1035
PREPARE stmt FROM "SELECT (BIT_OR(i) = 1), BIT_OR(i) FROM t1 WHERE i = ?";
1037
EXECUTE stmt USING @a;
1038
(BIT_OR(i) = 1) BIT_OR(i)
1041
EXECUTE stmt USING @a;
1042
(BIT_OR(i) = 1) BIT_OR(i)
1045
EXECUTE stmt USING @a;
1046
(BIT_OR(i) = 1) BIT_OR(i)
1048
PREPARE stmt FROM "SELECT (BIT_AND(i) = 1), BIT_AND(i) FROM t1 WHERE i = ?";
1050
EXECUTE stmt USING @a;
1051
(BIT_AND(i) = 1) BIT_AND(i)
1052
0 18446744073709551615
1054
EXECUTE stmt USING @a;
1055
(BIT_AND(i) = 1) BIT_AND(i)
1058
EXECUTE stmt USING @a;
1059
(BIT_AND(i) = 1) BIT_AND(i)
1060
0 18446744073709551615
1061
PREPARE stmt FROM "SELECT (BIT_XOR(i) = 1), BIT_XOR(i) FROM t1 WHERE i = ?";
1063
EXECUTE stmt USING @a;
1064
(BIT_XOR(i) = 1) BIT_XOR(i)
1067
EXECUTE stmt USING @a;
1068
(BIT_XOR(i) = 1) BIT_XOR(i)
1071
EXECUTE stmt USING @a;
1072
(BIT_XOR(i) = 1) BIT_XOR(i)
1074
DEALLOCATE PREPARE stmt;
1076
DROP TABLE IF EXISTS t1, t2;
1077
CREATE TABLE t1 (i INT);
1079
FROM "CREATE TABLE t2 (i INT, j INT, KEY (i), KEY(j)) SELECT i FROM t1";
1082
Field Type Null Key Default Extra
1083
j int(11) YES MUL NULL
1084
i int(11) YES MUL NULL
1088
Field Type Null Key Default Extra
1089
j int(11) YES MUL NULL
1090
i int(11) YES MUL NULL
1091
DEALLOCATE PREPARE st_19182;
1093
drop database if exists mysqltest;
1094
drop table if exists t1, t2;
1095
create database mysqltest character set utf8;
1096
prepare stmt1 from "create table mysqltest.t1 (c char(10))";
1097
prepare stmt2 from "create table mysqltest.t2 select 'test'";
1100
show create table mysqltest.t1;
1102
t1 CREATE TABLE `t1` (
1103
`c` char(10) DEFAULT NULL
1104
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1105
show create table mysqltest.t2;
1107
t2 CREATE TABLE `t2` (
1108
`test` varchar(4) CHARACTER SET latin1 NOT NULL DEFAULT ''
1109
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1110
drop table mysqltest.t1;
1111
drop table mysqltest.t2;
1112
alter database mysqltest character set latin1;
1115
show create table mysqltest.t1;
1117
t1 CREATE TABLE `t1` (
1118
`c` char(10) DEFAULT NULL
1119
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1120
show create table mysqltest.t2;
1122
t2 CREATE TABLE `t2` (
1123
`test` varchar(4) NOT NULL DEFAULT ''
1124
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1125
drop database mysqltest;
1126
deallocate prepare stmt1;
1127
deallocate prepare stmt2;
1129
show create table t1;
1132
show create table t1;
1134
deallocate prepare stmt;
1135
CREATE TABLE t1(a int);
1136
INSERT INTO t1 VALUES (2), (3), (1);
1138
'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a';
1155
DEALLOCATE PREPARE st1;
1157
create table t1 (a int, b tinyint);
1158
prepare st1 from 'update t1 set b= (str_to_date(a, a))';
1160
deallocate prepare st1;
1163
create table t1 (a varchar(20));
1164
insert into t1 values ('foo');
1165
prepare stmt FROM 'SELECT char_length (a) FROM t1';
1166
prepare stmt2 FROM 'SELECT not_a_function (a) FROM t1';
1167
ERROR 42000: FUNCTION test.not_a_function does not exist
1169
create table t1 (a char(3) not null, b char(3) not null,
1170
c char(3) not null, primary key (a, b, c));
1171
create table t2 like t1;
1173
"select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b)
1182
"select t1.a, t1.b, t1.c, t2.a, t2.b, t2.c from
1183
(t1 left outer join t2 on t2.a=? and t1.b=t2.b)
1184
left outer join t2 t3 on t3.a=? where t1.a=?";
1185
set @a:=1, @b:=1, @c:=1;
1186
execute stmt using @a, @b, @c;
1188
execute stmt using @a, @b, @c;
1190
execute stmt using @a, @b, @c;
1192
deallocate prepare stmt;
1194
SET @aux= "SELECT COUNT(*)
1195
FROM INFORMATION_SCHEMA.COLUMNS A,
1196
INFORMATION_SCHEMA.COLUMNS B
1197
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
1198
AND A.TABLE_NAME = B.TABLE_NAME
1199
AND A.COLUMN_NAME = B.COLUMN_NAME AND
1200
A.TABLE_NAME = 'user'";
1201
prepare my_stmt from @aux;
1211
deallocate prepare my_stmt;
1212
drop procedure if exists p1|
1213
drop table if exists t1|
1214
create table t1 (id int)|
1215
insert into t1 values(1)|
1216
create procedure p1(a int, b int)
1219
select max(id)+1 into c from t1;
1220
insert into t1 select a+b;
1221
insert into t1 select a-b;
1222
insert into t1 select a-c;
1225
prepare stmt from "call p1(?, ?)"|
1226
execute stmt using @a, @b|
1227
execute stmt using @a, @b|
1237
deallocate prepare stmt|
1240
create table t1 (a int);
1241
insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
1242
prepare stmt from "select * from t1 limit ?, ?";
1243
set @offset=0, @limit=1;
1244
execute stmt using @offset, @limit;
1247
select * from t1 limit 0, 1;
1250
set @offset=3, @limit=2;
1251
execute stmt using @offset, @limit;
1255
select * from t1 limit 3, 2;
1259
prepare stmt from "select * from t1 limit ?";
1260
execute stmt using @limit;
1264
prepare stmt from "select * from t1 where a in (select a from t1 limit ?)";
1265
ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
1266
prepare stmt from "select * from t1 union all select * from t1 limit ?, ?";
1269
execute stmt using @offset, @limit;
1273
prepare stmt from "(select * from t1 limit ?, ?) union all
1274
(select * from t1 limit ?, ?) order by a limit ?";
1275
execute stmt using @offset, @limit, @offset, @limit, @limit;
1280
deallocate prepare stmt;
1281
CREATE TABLE b12651_T1(a int) ENGINE=MYISAM;
1282
CREATE TABLE b12651_T2(b int) ENGINE=MYISAM;
1283
CREATE VIEW b12651_V1 as SELECT b FROM b12651_T2;
1284
PREPARE b12651 FROM 'SELECT 1 FROM b12651_T1 WHERE a IN (SELECT b FROM b12651_V1)';
1287
DROP VIEW b12651_V1;
1288
DROP TABLE b12651_T1, b12651_T2;
1289
DEALLOCATE PREPARE b12651;
1290
create table t1 (id int);
1291
prepare ins_call from "insert into t1 (id) values (1)";
1297
create table t1 (a int, b int);
1298
insert into t1 (a,b) values (2,8),(1,9),(3,7);
1299
prepare stmt from "select * from t1 order by ?";
1301
execute stmt using @a;
1307
execute stmt using @a;
1313
execute stmt using @a;
1318
deallocate prepare stmt;
1319
select * from t1 order by 1;
1324
prepare stmt from "select * from t1 order by ?+1";
1326
execute stmt using @a;
1332
execute stmt using @a;
1337
deallocate prepare stmt;
1338
select * from t1 order by 1+1;
1344
create table t1 (a int);
1345
create table t2 like t1;
1346
create table t3 like t2;
1347
prepare stmt from "repair table t1";
1349
Table Op Msg_type Msg_text
1350
test.t1 repair status OK
1352
Table Op Msg_type Msg_text
1353
test.t1 repair status OK
1354
prepare stmt from "optimize table t1";
1356
Table Op Msg_type Msg_text
1357
test.t1 optimize status OK
1359
Table Op Msg_type Msg_text
1360
test.t1 optimize status Table is already up to date
1361
prepare stmt from "analyze table t1";
1363
Table Op Msg_type Msg_text
1364
test.t1 analyze status Table is already up to date
1366
Table Op Msg_type Msg_text
1367
test.t1 analyze status Table is already up to date
1368
prepare stmt from "repair table t1, t2, t3";
1370
Table Op Msg_type Msg_text
1371
test.t1 repair status OK
1372
test.t2 repair status OK
1373
test.t3 repair status OK
1375
Table Op Msg_type Msg_text
1376
test.t1 repair status OK
1377
test.t2 repair status OK
1378
test.t3 repair status OK
1379
prepare stmt from "optimize table t1, t2, t3";
1381
Table Op Msg_type Msg_text
1382
test.t1 optimize status OK
1383
test.t2 optimize status OK
1384
test.t3 optimize status OK
1386
Table Op Msg_type Msg_text
1387
test.t1 optimize status Table is already up to date
1388
test.t2 optimize status Table is already up to date
1389
test.t3 optimize status Table is already up to date
1390
prepare stmt from "analyze table t1, t2, t3";
1392
Table Op Msg_type Msg_text
1393
test.t1 analyze status Table is already up to date
1394
test.t2 analyze status Table is already up to date
1395
test.t3 analyze status Table is already up to date
1397
Table Op Msg_type Msg_text
1398
test.t1 analyze status Table is already up to date
1399
test.t2 analyze status Table is already up to date
1400
test.t3 analyze status Table is already up to date
1401
prepare stmt from "repair table t1, t4, t3";
1403
Table Op Msg_type Msg_text
1404
test.t1 repair status OK
1405
test.t4 repair Error Table 'test.t4' doesn't exist
1406
test.t4 repair status Operation failed
1407
test.t3 repair status OK
1409
Table Op Msg_type Msg_text
1410
test.t1 repair status OK
1411
test.t4 repair Error Table 'test.t4' doesn't exist
1412
test.t4 repair status Operation failed
1413
test.t3 repair status OK
1414
prepare stmt from "optimize table t1, t3, t4";
1416
Table Op Msg_type Msg_text
1417
test.t1 optimize status OK
1418
test.t3 optimize status OK
1419
test.t4 optimize Error Table 'test.t4' doesn't exist
1420
test.t4 optimize status Operation failed
1422
Table Op Msg_type Msg_text
1423
test.t1 optimize status Table is already up to date
1424
test.t3 optimize status Table is already up to date
1425
test.t4 optimize Error Table 'test.t4' doesn't exist
1426
test.t4 optimize status Operation failed
1427
prepare stmt from "analyze table t4, t1";
1429
Table Op Msg_type Msg_text
1430
test.t4 analyze Error Table 'test.t4' doesn't exist
1431
test.t4 analyze status Operation failed
1432
test.t1 analyze status Table is already up to date
1434
Table Op Msg_type Msg_text
1435
test.t4 analyze Error Table 'test.t4' doesn't exist
1436
test.t4 analyze status Operation failed
1437
test.t1 analyze status Table is already up to date
1438
deallocate prepare stmt;
1439
drop table t1, t2, t3;
1440
create database mysqltest_long_database_name_to_thrash_heap;
1442
create table t1 (i int);
1443
prepare stmt from "alter table test.t1 rename t1";
1444
use mysqltest_long_database_name_to_thrash_heap;
1446
show tables like 't1';
1447
Tables_in_mysqltest_long_database_name_to_thrash_heap (t1)
1448
prepare stmt from "alter table test.t1 rename t1";
1451
show tables like 't1';
1453
use mysqltest_long_database_name_to_thrash_heap;
1454
show tables like 't1';
1455
Tables_in_mysqltest_long_database_name_to_thrash_heap (t1)
1457
deallocate prepare stmt;
1458
use mysqltest_long_database_name_to_thrash_heap;
1459
prepare stmt_create from "create table t1 (i int)";
1460
prepare stmt_insert from "insert into t1 (i) values (1)";
1461
prepare stmt_update from "update t1 set i=2";
1462
prepare stmt_delete from "delete from t1 where i=2";
1463
prepare stmt_select from "select * from t1";
1464
prepare stmt_alter from "alter table t1 add column (b int)";
1465
prepare stmt_alter1 from "alter table t1 drop column b";
1466
prepare stmt_analyze from "analyze table t1";
1467
prepare stmt_optimize from "optimize table t1";
1468
prepare stmt_show from "show tables like 't1'";
1469
prepare stmt_truncate from "truncate table t1";
1470
prepare stmt_drop from "drop table t1";
1473
execute stmt_create;
1474
show tables like 't1';
1476
use mysqltest_long_database_name_to_thrash_heap;
1477
show tables like 't1';
1478
Tables_in_mysqltest_long_database_name_to_thrash_heap (t1)
1481
execute stmt_insert;
1482
select * from mysqltest_long_database_name_to_thrash_heap.t1;
1485
execute stmt_update;
1486
select * from mysqltest_long_database_name_to_thrash_heap.t1;
1489
execute stmt_delete;
1490
execute stmt_select;
1493
show columns from mysqltest_long_database_name_to_thrash_heap.t1;
1494
Field Type Null Key Default Extra
1497
execute stmt_alter1;
1498
show columns from mysqltest_long_database_name_to_thrash_heap.t1;
1499
Field Type Null Key Default Extra
1501
execute stmt_analyze;
1502
Table Op Msg_type Msg_text
1503
mysqltest_long_database_name_to_thrash_heap.t1 analyze status Table is already up to date
1504
execute stmt_optimize;
1505
Table Op Msg_type Msg_text
1506
mysqltest_long_database_name_to_thrash_heap.t1 optimize status Table is already up to date
1508
Tables_in_mysqltest_long_database_name_to_thrash_heap (t1)
1510
execute stmt_truncate;
1512
show tables like 't1';
1514
use mysqltest_long_database_name_to_thrash_heap;
1515
show tables like 't1';
1516
Tables_in_mysqltest_long_database_name_to_thrash_heap (t1)
1517
drop database mysqltest_long_database_name_to_thrash_heap;
1518
prepare stmt_create from "create table t1 (i int)";
1519
ERROR 3D000: No database selected
1520
prepare stmt_insert from "insert into t1 (i) values (1)";
1521
ERROR 3D000: No database selected
1522
prepare stmt_update from "update t1 set i=2";
1523
ERROR 3D000: No database selected
1524
prepare stmt_delete from "delete from t1 where i=2";
1525
ERROR 3D000: No database selected
1526
prepare stmt_select from "select * from t1";
1527
ERROR 3D000: No database selected
1528
prepare stmt_alter from "alter table t1 add column (b int)";
1529
ERROR 3D000: No database selected
1530
prepare stmt_alter1 from "alter table t1 drop column b";
1531
ERROR 3D000: No database selected
1532
prepare stmt_analyze from "analyze table t1";
1533
ERROR 3D000: No database selected
1534
prepare stmt_optimize from "optimize table t1";
1535
ERROR 3D000: No database selected
1536
prepare stmt_show from "show tables like 't1'";
1537
ERROR 3D000: No database selected
1538
prepare stmt_truncate from "truncate table t1";
1539
ERROR 3D000: No database selected
1540
prepare stmt_drop from "drop table t1";
1541
ERROR 3D000: No database selected
1542
create temporary table t1 (i int);
1543
ERROR 3D000: No database selected
1545
DROP TABLE IF EXISTS t1, t2, t3;
1546
CREATE TABLE t1 (i BIGINT, j BIGINT);
1547
CREATE TABLE t2 (i BIGINT);
1548
CREATE TABLE t3 (i BIGINT, j BIGINT);
1549
PREPARE stmt FROM "SELECT * FROM t1 JOIN t2 ON (t2.i = t1.i)
1550
LEFT JOIN t3 ON ((t3.i, t3.j) = (t1.i, t1.j))
1553
EXECUTE stmt USING @a;
1555
EXECUTE stmt USING @a;
1557
DEALLOCATE PREPARE stmt;
1558
DROP TABLE IF EXISTS t1, t2, t3;
1559
DROP TABLE IF EXISTS t1, t2;
1560
CREATE TABLE t1 (i INT KEY);
1561
CREATE TABLE t2 (i INT);
1562
INSERT INTO t1 VALUES (1), (2);
1563
INSERT INTO t2 VALUES (1);
1564
PREPARE stmt FROM "SELECT t2.i FROM t1 LEFT JOIN t2 ON t2.i = t1.i
1567
EXECUTE stmt USING @arg;
1571
EXECUTE stmt USING @arg;
1575
EXECUTE stmt USING @arg;
1578
DEALLOCATE PREPARE stmt;
1580
CREATE TABLE t1 (i INT);
1581
CREATE VIEW v1 AS SELECT * FROM t1;
1582
INSERT INTO t1 VALUES (1), (2);
1583
SELECT t1.i FROM t1 JOIN v1 ON t1.i = v1.i
1584
WHERE EXISTS (SELECT * FROM t1 WHERE v1.i = 1);
1587
PREPARE stmt FROM "SELECT t1.i FROM t1 JOIN v1 ON t1.i = v1.i
1588
WHERE EXISTS (SELECT * FROM t1 WHERE v1.i = 1)";
1595
DEALLOCATE PREPARE stmt;
1598
DROP PROCEDURE IF EXISTS p1;
1600
prepare sq from 'show status like "slow_queries"';
1604
prepare no_index from 'select 1 from information_schema.tables limit 1';
1614
deallocate prepare no_index;
1615
deallocate prepare sq;
1616
CREATE TABLE t1 (a int);
1617
INSERT INTO t1 VALUES (1), (2);
1618
CREATE TABLE t2 (b int);
1619
INSERT INTO t2 VALUES (NULL);
1620
SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL;
1624
PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL';
1629
DEALLOCATE PREPARE stmt;
1630
PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2 limit ?) IS NULL';
1632
EXECUTE stmt USING @arg;
1636
DEALLOCATE PREPARE stmt;
1638
drop table if exists t1;
1639
create table t1 (s1 char(20));
1640
prepare stmt from "alter table t1 modify s1 int";
1644
deallocate prepare stmt;
1645
drop table if exists t1;
1646
create table t1 (a int, b int);
1647
prepare s_6895 from "alter table t1 drop column b";
1649
show columns from t1;
1650
Field Type Null Key Default Extra
1653
create table t1 (a int, b int);
1655
show columns from t1;
1656
Field Type Null Key Default Extra
1659
create table t1 (a int, b int);
1661
show columns from t1;
1662
Field Type Null Key Default Extra
1664
deallocate prepare s_6895;
1666
create table t1 (i int primary key auto_increment) comment='comment for table t1';
1667
create table t2 (i int, j int, k int);
1668
prepare stmt from "alter table t1 auto_increment=100";
1670
show create table t1;
1672
t1 CREATE TABLE `t1` (
1673
`i` int(11) NOT NULL AUTO_INCREMENT,
1675
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 COMMENT='comment for table t1'
1680
show create table t1;
1682
t1 CREATE TABLE `t1` (
1683
`i` int(11) NOT NULL AUTO_INCREMENT,
1685
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 COMMENT='comment for table t1'
1686
deallocate prepare stmt;
1688
set @old_character_set_server= @@character_set_server;
1689
set @@character_set_server= latin1;
1690
prepare stmt from "create database mysqltest_1";
1692
show create database mysqltest_1;
1693
Database Create Database
1694
mysqltest_1 CREATE DATABASE `mysqltest_1` /*!40100 DEFAULT CHARACTER SET latin1 */
1695
drop database mysqltest_1;
1696
set @@character_set_server= utf8;
1698
show create database mysqltest_1;
1699
Database Create Database
1700
mysqltest_1 CREATE DATABASE `mysqltest_1` /*!40100 DEFAULT CHARACTER SET utf8 */
1701
drop database mysqltest_1;
1702
deallocate prepare stmt;
1703
set @@character_set_server= @old_character_set_server;
1704
drop tables if exists t1;
1705
create table t1 (id int primary key auto_increment, value varchar(10));
1706
insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD');
1707
prepare stmt from "insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as v) as y on duplicate key update v = 'DUP'";
1709
ERROR 42S22: Unknown column 'v' in 'field list'
1711
ERROR 42S22: Unknown column 'v' in 'field list'
1712
deallocate prepare stmt;
1713
prepare 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'";
1715
ERROR 42S22: Unknown column 'y.value' in 'field list'
1717
ERROR 42S22: Unknown column 'y.value' in 'field list'
1718
deallocate prepare stmt;
1720
prepare stmt from "create table t1 select ?";
1722
execute stmt using @a;
1723
show create table t1;
1725
t1 CREATE TABLE `t1` (
1726
`?` decimal(2,1) DEFAULT NULL
1727
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1729
drop table if exists t1;
1730
create table t1 (a bigint unsigned, b bigint(20) unsigned);
1731
prepare stmt from "insert into t1 values (?,?)";
1732
set @a= 9999999999999999;
1733
set @b= 14632475938453979136;
1734
insert into t1 values (@a, @b);
1735
select * from t1 where a = @a and b = @b;
1737
9999999999999999 14632475938453979136
1738
execute stmt using @a, @b;
1739
select * from t1 where a = @a and b = @b;
1741
9999999999999999 14632475938453979136
1742
9999999999999999 14632475938453979136
1743
deallocate prepare stmt;
1745
drop view if exists v1;
1746
drop table if exists t1;
1747
create table t1 (a int, b int);
1748
insert into t1 values (1,1), (2,2), (3,3);
1749
insert into t1 values (3,1), (1,2), (2,3);
1750
prepare stmt from "create view v1 as select * from t1";
1753
create table t1 (a int, b int);
1756
show create view v1;
1757
View Create View character_set_client collation_connection
1758
v1 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
1760
prepare stmt from "create view v1 (c,d) as select a,b from t1";
1762
show create view v1;
1763
View Create View character_set_client collation_connection
1764
v1 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
1769
deallocate prepare stmt;
1770
show create view v1;
1771
View Create View character_set_client collation_connection
1772
v1 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
1776
prepare stmt from "create view v1 (c) as select b+1 from t1";
1778
show create view v1;
1779
View Create View character_set_client collation_connection
1780
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`b` + 1) AS `c` from `t1` latin1 latin1_swedish_ci
1785
deallocate prepare stmt;
1786
show create view v1;
1787
View Create View character_set_client collation_connection
1788
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`b` + 1) AS `c` from `t1` latin1 latin1_swedish_ci
1792
prepare 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";
1794
show create view v1;
1795
View Create View character_set_client collation_connection
1796
v1 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
1801
deallocate prepare stmt;
1802
show create view v1;
1803
View Create View character_set_client collation_connection
1804
v1 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
1808
prepare stmt from "create or replace view v1 as select 1";
1810
show create view v1;
1811
View Create View character_set_client collation_connection
1812
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci
1817
show create view v1;
1818
View Create View character_set_client collation_connection
1819
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci
1820
deallocate prepare stmt;
1821
show create view v1;
1822
View Create View character_set_client collation_connection
1823
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci
1828
prepare stmt from "create view v1 as select 1, 1";
1830
show create view v1;
1831
View Create View character_set_client collation_connection
1832
v1 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
1838
deallocate prepare stmt;
1839
show create view v1;
1840
View Create View character_set_client collation_connection
1841
v1 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
1846
prepare stmt from "create view v1 (x) as select a from t1 where a > 1";
1848
show create view v1;
1849
View Create View character_set_client collation_connection
1850
v1 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
1855
deallocate prepare stmt;
1856
show create view v1;
1857
View Create View character_set_client collation_connection
1858
v1 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
1862
prepare stmt from "create view v1 as select * from `t1` `b`";
1864
show create view v1;
1865
View Create View character_set_client collation_connection
1866
v1 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
1871
deallocate prepare stmt;
1872
show create view v1;
1873
View Create View character_set_client collation_connection
1874
v1 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
1878
prepare stmt from "create view v1 (a,b,c) as select * from t1";
1880
ERROR HY000: View's SELECT and view's field list have different column counts
1882
ERROR HY000: View's SELECT and view's field list have different column counts
1883
deallocate prepare stmt;
1885
create temporary table t1 (a int, b int);
1886
prepare stmt from "create view v1 as select * from t1";
1888
ERROR HY000: View's SELECT refers to a temporary table 't1'
1890
ERROR HY000: View's SELECT refers to a temporary table 't1'
1891
deallocate prepare stmt;
1893
prepare stmt from "create view v1 as select * from t1";
1894
ERROR 42S02: Table 'test.t1' doesn't exist
1895
prepare stmt from "create view v1 as select * from `t1` `b`";
1896
ERROR 42S02: Table 'test.t1' doesn't exist
1897
prepare stmt from "select ?";
1898
set @arg= 123456789.987654321;
1902
execute stmt using @arg;
1909
execute stmt using @arg;
1916
execute stmt using @arg;
1919
set @arg= cast(-12345.54321 as decimal(20, 10));
1923
execute stmt using @arg;
1926
deallocate prepare stmt;
1928
# Bug#48508: Crash on prepared statement re-execution.
1930
create table t1(b int);
1931
insert into t1 values (0);
1932
create view v1 AS select 1 as a from t1 where b;
1933
prepare stmt from "select * from v1 where a";
1938
deallocate prepare stmt;
1941
create table t1(a bigint);
1942
create table t2(b tinyint);
1943
insert into t2 values (null);
1944
prepare stmt from "select 1 from t1 join t2 on a xor b where b > 1 and a =1";
1949
deallocate prepare stmt;
1953
# Bug #49570: Assertion failed: !(order->used & map)
1954
# on re-execution of prepared statement
1956
CREATE TABLE t1(a INT PRIMARY KEY);
1957
INSERT INTO t1 VALUES(0), (1);
1959
"SELECT 1 FROM t1 JOIN t1 t2 USING(a) GROUP BY t2.a, t1.a";
1972
DEALLOCATE PREPARE stmt;
1975
create procedure proc_1() reset query cache;
1979
create function func_1() returns int deterministic begin reset query cache; return 1; end|
1980
ERROR 0A000: RESET is not allowed in stored function or trigger
1981
create function func_1() returns int deterministic begin call proc_1(); return 1; end|
1982
select func_1(), func_1(), func_1() from dual;
1983
ERROR 0A000: RESET is not allowed in stored function or trigger
1984
drop function func_1;
1985
drop procedure proc_1;
1986
prepare abc from "reset query cache";
1990
deallocate prepare abc;
1991
create procedure proc_1() reset master;
1992
create function func_1() returns int begin reset master; return 1; end|
1993
ERROR 0A000: RESET is not allowed in stored function or trigger
1994
create function func_1() returns int begin call proc_1(); return 1; end|
1995
select func_1(), func_1(), func_1() from dual;
1996
ERROR 0A000: RESET is not allowed in stored function or trigger
1997
drop function func_1;
1998
drop procedure proc_1;
1999
prepare abc from "reset master";
2003
deallocate prepare abc;
2004
create procedure proc_1() reset slave;
2008
create function func_1() returns int begin reset slave; return 1; end|
2009
ERROR 0A000: RESET is not allowed in stored function or trigger
2010
create function func_1() returns int begin call proc_1(); return 1; end|
2011
select func_1(), func_1(), func_1() from dual;
2012
ERROR 0A000: RESET is not allowed in stored function or trigger
2013
drop function func_1;
2014
drop procedure proc_1;
2015
prepare abc from "reset slave";
2019
deallocate prepare abc;
2020
create procedure proc_1(a integer) kill a;
2022
ERROR HY000: Unknown thread id: 0
2024
ERROR HY000: Unknown thread id: 0
2026
ERROR HY000: Unknown thread id: 0
2027
drop procedure proc_1;
2028
create function func_1() returns int begin kill 0; return 1; end|
2029
select func_1() from dual;
2030
ERROR HY000: Unknown thread id: 0
2031
select func_1() from dual;
2032
ERROR HY000: Unknown thread id: 0
2033
select func_1() from dual;
2034
ERROR HY000: Unknown thread id: 0
2035
drop function func_1;
2036
prepare abc from "kill 0";
2038
ERROR HY000: Unknown thread id: 0
2040
ERROR HY000: Unknown thread id: 0
2042
ERROR HY000: Unknown thread id: 0
2043
deallocate prepare abc;
2044
create procedure proc_1() flush hosts;
2049
create function func_1() returns int begin flush hosts; return 1; end|
2050
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2051
create function func_1() returns int begin call proc_1(); return 1; end|
2052
select func_1(), func_1(), func_1() from dual;
2053
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2054
drop function func_1;
2055
drop procedure proc_1;
2056
prepare abc from "flush hosts";
2060
deallocate prepare abc;
2061
create procedure proc_1() flush privileges;
2065
create function func_1() returns int begin flush privileges; return 1; end|
2066
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2067
create function func_1() returns int begin call proc_1(); return 1; end|
2068
select func_1(), func_1(), func_1() from dual;
2069
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2070
drop function func_1;
2071
drop procedure proc_1;
2072
prepare abc from "flush privileges";
2073
deallocate prepare abc;
2074
create procedure proc_1() flush tables with read lock;
2081
create function func_1() returns int begin flush tables with read lock; return 1; end|
2082
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2083
create function func_1() returns int begin call proc_1(); return 1; end|
2084
select func_1(), func_1(), func_1() from dual;
2085
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2086
drop function func_1;
2087
drop procedure proc_1;
2088
prepare abc from "flush tables with read lock";
2092
deallocate prepare abc;
2094
create procedure proc_1() flush tables;
2098
create function func_1() returns int begin flush tables; return 1; end|
2099
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2100
create function func_1() returns int begin call proc_1(); return 1; end|
2101
select func_1(), func_1(), func_1() from dual;
2102
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2103
drop function func_1;
2104
drop procedure proc_1;
2105
prepare abc from "flush tables";
2109
deallocate prepare abc;
2110
create procedure proc_1() flush tables;
2112
show open tables from mysql;
2113
Database Table In_use Name_locked
2114
mysql general_log 0 0
2115
select Host, User from mysql.user limit 0;
2117
select Host, Db from mysql.host limit 0;
2119
show open tables from mysql;
2120
Database Table In_use Name_locked
2122
mysql general_log 0 0
2125
show open tables from mysql;
2126
Database Table In_use Name_locked
2127
mysql general_log 0 0
2128
select Host, User from mysql.user limit 0;
2130
select Host, Db from mysql.host limit 0;
2132
show open tables from mysql;
2133
Database Table In_use Name_locked
2135
mysql general_log 0 0
2138
show open tables from mysql;
2139
Database Table In_use Name_locked
2140
mysql general_log 0 0
2141
select Host, User from mysql.user limit 0;
2143
select Host, Db from mysql.host limit 0;
2145
show open tables from mysql;
2146
Database Table In_use Name_locked
2148
mysql general_log 0 0
2151
show open tables from mysql;
2152
Database Table In_use Name_locked
2153
mysql general_log 0 0
2154
select Host, User from mysql.user limit 0;
2156
select Host, Db from mysql.host limit 0;
2158
show open tables from mysql;
2159
Database Table In_use Name_locked
2161
mysql general_log 0 0
2164
create function func_1() returns int begin flush tables; return 1; end|
2165
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2166
create function func_1() returns int begin call proc_1(); return 1; end|
2167
select func_1(), func_1(), func_1() from dual;
2168
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2169
drop function func_1;
2170
drop procedure proc_1;
2172
select Host, User from mysql.user limit 0;
2174
select Host, Db from mysql.host limit 0;
2176
show open tables from mysql;
2177
Database Table In_use Name_locked
2179
mysql general_log 0 0
2181
prepare abc from "flush tables";
2183
show open tables from mysql;
2184
Database Table In_use Name_locked
2185
mysql general_log 0 0
2186
select Host, User from mysql.user limit 0;
2188
select Host, Db from mysql.host limit 0;
2190
show open tables from mysql;
2191
Database Table In_use Name_locked
2193
mysql general_log 0 0
2196
show open tables from mysql;
2197
Database Table In_use Name_locked
2198
mysql general_log 0 0
2199
select Host, User from mysql.user limit 0;
2201
select Host, Db from mysql.host limit 0;
2203
show open tables from mysql;
2204
Database Table In_use Name_locked
2206
mysql general_log 0 0
2209
show open tables from mysql;
2210
Database Table In_use Name_locked
2211
mysql general_log 0 0
2212
select Host, User from mysql.user limit 0;
2214
select Host, Db from mysql.host limit 0;
2216
show open tables from mysql;
2217
Database Table In_use Name_locked
2219
mysql general_log 0 0
2222
deallocate prepare abc;
2223
create procedure proc_1() flush logs;
2227
create function func_1() returns int begin flush logs; return 1; end|
2228
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2229
create function func_1() returns int begin call proc_1(); return 1; end|
2230
select func_1(), func_1(), func_1() from dual;
2231
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2232
drop function func_1;
2233
drop procedure proc_1;
2234
prepare abc from "flush logs";
2238
deallocate prepare abc;
2239
create procedure proc_1() flush status;
2243
create function func_1() returns int begin flush status; return 1; end|
2244
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2245
create function func_1() returns int begin call proc_1(); return 1; end|
2246
select func_1(), func_1(), func_1() from dual;
2247
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2248
drop function func_1;
2249
drop procedure proc_1;
2250
prepare abc from "flush status";
2254
deallocate prepare abc;
2255
create procedure proc_1() flush slave;
2259
create function func_1() returns int begin flush slave; return 1; end|
2260
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2261
create function func_1() returns int begin call proc_1(); return 1; end|
2262
select func_1(), func_1(), func_1() from dual;
2263
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2264
drop function func_1;
2265
drop procedure proc_1;
2266
prepare abc from "flush slave";
2270
deallocate prepare abc;
2271
create procedure proc_1() flush master;
2272
create function func_1() returns int begin flush master; return 1; end|
2273
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2274
create function func_1() returns int begin call proc_1(); return 1; end|
2275
select func_1(), func_1(), func_1() from dual;
2276
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2277
drop function func_1;
2278
drop procedure proc_1;
2279
prepare abc from "flush master";
2280
deallocate prepare abc;
2281
create procedure proc_1() flush des_key_file;
2285
create function func_1() returns int begin flush des_key_file; return 1; end|
2286
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2287
create function func_1() returns int begin call proc_1(); return 1; end|
2288
select func_1(), func_1(), func_1() from dual;
2289
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2290
drop function func_1;
2291
drop procedure proc_1;
2292
prepare abc from "flush des_key_file";
2296
deallocate prepare abc;
2297
create procedure proc_1() flush user_resources;
2301
create function func_1() returns int begin flush user_resources; return 1; end|
2302
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2303
create function func_1() returns int begin call proc_1(); return 1; end|
2304
select func_1(), func_1(), func_1() from dual;
2305
ERROR 0A000: FLUSH is not allowed in stored function or trigger
2306
drop function func_1;
2307
drop procedure proc_1;
2308
prepare abc from "flush user_resources";
2312
deallocate prepare abc;
2313
create procedure proc_1() start slave;
2314
drop procedure proc_1;
2315
create function func_1() returns int begin start slave; return 1; end|
2316
drop function func_1;
2317
prepare abc from "start slave";
2318
deallocate prepare abc;
2319
create procedure proc_1() stop slave;
2320
drop procedure proc_1;
2321
create function func_1() returns int begin stop slave; return 1; end|
2322
drop function func_1;
2323
prepare abc from "stop slave";
2324
deallocate prepare abc;
2325
create procedure proc_1() show binlog events;
2326
drop procedure proc_1;
2327
create function func_1() returns int begin show binlog events; return 1; end|
2328
ERROR 0A000: Not allowed to return a result set from a function
2329
select func_1(), func_1(), func_1() from dual;
2330
ERROR 42000: FUNCTION test.func_1 does not exist
2331
drop function func_1;
2332
ERROR 42000: FUNCTION test.func_1 does not exist
2333
prepare abc from "show binlog events";
2334
deallocate prepare abc;
2335
create procedure proc_1() show slave status;
2336
drop procedure proc_1;
2337
create function func_1() returns int begin show slave status; return 1; end|
2338
ERROR 0A000: Not allowed to return a result set from a function
2339
select func_1(), func_1(), func_1() from dual;
2340
ERROR 42000: FUNCTION test.func_1 does not exist
2341
drop function func_1;
2342
ERROR 42000: FUNCTION test.func_1 does not exist
2343
prepare abc from "show slave status";
2344
deallocate prepare abc;
2345
create procedure proc_1() show master status;
2346
drop procedure proc_1;
2347
create function func_1() returns int begin show master status; return 1; end|
2348
ERROR 0A000: Not allowed to return a result set from a function
2349
select func_1(), func_1(), func_1() from dual;
2350
ERROR 42000: FUNCTION test.func_1 does not exist
2351
drop function func_1;
2352
ERROR 42000: FUNCTION test.func_1 does not exist
2353
prepare abc from "show master status";
2354
deallocate prepare abc;
2355
create procedure proc_1() show master logs;
2356
drop procedure proc_1;
2357
create function func_1() returns int begin show master logs; return 1; end|
2358
ERROR 0A000: Not allowed to return a result set from a function
2359
select func_1(), func_1(), func_1() from dual;
2360
ERROR 42000: FUNCTION test.func_1 does not exist
2361
drop function func_1;
2362
ERROR 42000: FUNCTION test.func_1 does not exist
2363
prepare abc from "show master logs";
2364
deallocate prepare abc;
2365
create procedure proc_1() show events;
2367
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
2369
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
2371
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
2372
drop procedure proc_1;
2373
create function func_1() returns int begin show events; return 1; end|
2374
ERROR 0A000: Not allowed to return a result set from a function
2375
select func_1(), func_1(), func_1() from dual;
2376
ERROR 42000: FUNCTION test.func_1 does not exist
2377
drop function func_1;
2378
ERROR 42000: FUNCTION test.func_1 does not exist
2379
prepare abc from "show events";
2381
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
2383
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
2385
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
2386
deallocate prepare abc;
2387
drop procedure if exists a;
2388
create procedure a() select 42;
2389
create procedure proc_1(a char(2)) show create procedure a;
2391
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
2392
a CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
2393
select 42 latin1 latin1_swedish_ci latin1_swedish_ci
2395
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
2396
a CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
2397
select 42 latin1 latin1_swedish_ci latin1_swedish_ci
2399
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
2400
a CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
2401
select 42 latin1 latin1_swedish_ci latin1_swedish_ci
2402
drop procedure proc_1;
2403
create function func_1() returns int begin show create procedure a; return 1; end|
2404
ERROR 0A000: Not allowed to return a result set from a function
2405
select func_1(), func_1(), func_1() from dual;
2406
ERROR 42000: FUNCTION test.func_1 does not exist
2407
drop function func_1;
2408
ERROR 42000: FUNCTION test.func_1 does not exist
2409
prepare abc from "show create procedure a";
2411
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
2412
a CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
2413
select 42 latin1 latin1_swedish_ci latin1_swedish_ci
2415
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
2416
a CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
2417
select 42 latin1 latin1_swedish_ci latin1_swedish_ci
2419
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
2420
a CREATE DEFINER=`root`@`localhost` PROCEDURE `a`()
2421
select 42 latin1 latin1_swedish_ci latin1_swedish_ci
2422
deallocate prepare abc;
2424
drop function if exists a;
2425
create function a() returns int return 42+13;
2426
create procedure proc_1(a char(2)) show create function a;
2428
Function sql_mode Create Function character_set_client collation_connection Database Collation
2429
a CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
2430
return 42+13 latin1 latin1_swedish_ci latin1_swedish_ci
2432
Function sql_mode Create Function character_set_client collation_connection Database Collation
2433
a CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
2434
return 42+13 latin1 latin1_swedish_ci latin1_swedish_ci
2436
Function sql_mode Create Function character_set_client collation_connection Database Collation
2437
a CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
2438
return 42+13 latin1 latin1_swedish_ci latin1_swedish_ci
2439
drop procedure proc_1;
2440
create function func_1() returns int begin show create function a; return 1; end|
2441
ERROR 0A000: Not allowed to return a result set from a function
2442
select func_1(), func_1(), func_1() from dual;
2443
ERROR 42000: FUNCTION test.func_1 does not exist
2444
drop function func_1;
2445
ERROR 42000: FUNCTION test.func_1 does not exist
2446
prepare abc from "show create function a";
2448
Function sql_mode Create Function character_set_client collation_connection Database Collation
2449
a CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
2450
return 42+13 latin1 latin1_swedish_ci latin1_swedish_ci
2452
Function sql_mode Create Function character_set_client collation_connection Database Collation
2453
a CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
2454
return 42+13 latin1 latin1_swedish_ci latin1_swedish_ci
2456
Function sql_mode Create Function character_set_client collation_connection Database Collation
2457
a CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11)
2458
return 42+13 latin1 latin1_swedish_ci latin1_swedish_ci
2459
deallocate prepare abc;
2461
drop table if exists tab1;
2462
create table tab1(a int, b char(1), primary key(a,b));
2463
create procedure proc_1() show create table tab1;
2466
tab1 CREATE TABLE `tab1` (
2467
`a` int(11) NOT NULL DEFAULT '0',
2468
`b` char(1) NOT NULL DEFAULT '',
2469
PRIMARY KEY (`a`,`b`)
2470
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2473
tab1 CREATE TABLE `tab1` (
2474
`a` int(11) NOT NULL DEFAULT '0',
2475
`b` char(1) NOT NULL DEFAULT '',
2476
PRIMARY KEY (`a`,`b`)
2477
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2480
tab1 CREATE TABLE `tab1` (
2481
`a` int(11) NOT NULL DEFAULT '0',
2482
`b` char(1) NOT NULL DEFAULT '',
2483
PRIMARY KEY (`a`,`b`)
2484
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2485
drop procedure proc_1;
2486
create function func_1() returns int begin show create table tab1; return 1; end|
2487
ERROR 0A000: Not allowed to return a result set from a function
2488
select func_1(), func_1(), func_1() from dual;
2489
ERROR 42000: FUNCTION test.func_1 does not exist
2490
drop function func_1;
2491
ERROR 42000: FUNCTION test.func_1 does not exist
2492
prepare abc from "show create table tab1";
2495
tab1 CREATE TABLE `tab1` (
2496
`a` int(11) NOT NULL DEFAULT '0',
2497
`b` char(1) NOT NULL DEFAULT '',
2498
PRIMARY KEY (`a`,`b`)
2499
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2502
tab1 CREATE TABLE `tab1` (
2503
`a` int(11) NOT NULL DEFAULT '0',
2504
`b` char(1) NOT NULL DEFAULT '',
2505
PRIMARY KEY (`a`,`b`)
2506
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2509
tab1 CREATE TABLE `tab1` (
2510
`a` int(11) NOT NULL DEFAULT '0',
2511
`b` char(1) NOT NULL DEFAULT '',
2512
PRIMARY KEY (`a`,`b`)
2513
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2514
deallocate prepare abc;
2516
drop view if exists v1;
2517
drop table if exists t1;
2518
create table t1(a int, b char(5));
2519
insert into t1 values (1, "one"), (1, "edno"), (2, "two"), (2, "dve");
2521
(select a, count(*) from t1 group by a)
2523
(select b, count(*) from t1 group by b);
2524
create procedure proc_1() show create view v1;
2526
View Create View character_set_client collation_connection
2527
v1 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
2529
View Create View character_set_client collation_connection
2530
v1 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
2532
View Create View character_set_client collation_connection
2533
v1 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
2534
drop procedure proc_1;
2535
create function func_1() returns int begin show create view v1; return 1; end|
2536
ERROR 0A000: Not allowed to return a result set from a function
2537
select func_1(), func_1(), func_1() from dual;
2538
ERROR 42000: FUNCTION test.func_1 does not exist
2539
drop function func_1;
2540
ERROR 42000: FUNCTION test.func_1 does not exist
2541
prepare abc from "show create view v1";
2543
View Create View character_set_client collation_connection
2544
v1 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
2546
View Create View character_set_client collation_connection
2547
v1 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
2549
View Create View character_set_client collation_connection
2550
v1 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
2551
deallocate prepare abc;
2554
create procedure proc_1() install plugin my_plug soname 'some_plugin.so';
2556
Got one of the listed errors
2558
Got one of the listed errors
2560
Got one of the listed errors
2561
drop procedure proc_1;
2562
create function func_1() returns int begin install plugin my_plug soname '/tmp/plugin'; return 1; end|
2563
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
2564
select func_1(), func_1(), func_1() from dual;
2565
ERROR 42000: FUNCTION test.func_1 does not exist
2566
drop function func_1;
2567
ERROR 42000: FUNCTION test.func_1 does not exist
2568
prepare abc from "install plugin my_plug soname 'some_plugin.so'";
2569
deallocate prepare abc;
2570
create procedure proc_1() uninstall plugin my_plug;
2572
ERROR 42000: PLUGIN my_plug does not exist
2574
ERROR 42000: PLUGIN my_plug does not exist
2576
ERROR 42000: PLUGIN my_plug does not exist
2577
drop procedure proc_1;
2578
create function func_1() returns int begin uninstall plugin my_plug; return 1; end|
2579
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
2580
select func_1(), func_1(), func_1() from dual;
2581
ERROR 42000: FUNCTION test.func_1 does not exist
2582
drop function func_1;
2583
ERROR 42000: FUNCTION test.func_1 does not exist
2584
prepare abc from "uninstall plugin my_plug";
2586
ERROR 42000: PLUGIN my_plug does not exist
2588
ERROR 42000: PLUGIN my_plug does not exist
2590
ERROR 42000: PLUGIN my_plug does not exist
2591
deallocate prepare abc;
2592
drop database if exists mysqltest_xyz;
2593
create procedure proc_1() create database mysqltest_xyz;
2595
drop database if exists mysqltest_xyz;
2598
ERROR HY000: Can't create database 'mysqltest_xyz'; database exists
2599
drop database if exists mysqltest_xyz;
2601
drop database if exists mysqltest_xyz;
2602
drop procedure proc_1;
2603
create function func_1() returns int begin create database mysqltest_xyz; return 1; end|
2604
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
2605
select func_1(), func_1(), func_1() from dual;
2606
ERROR 42000: FUNCTION test.func_1 does not exist
2607
drop function func_1;
2608
ERROR 42000: FUNCTION test.func_1 does not exist
2609
prepare abc from "create database mysqltest_xyz";
2611
drop database if exists mysqltest_xyz;
2614
ERROR HY000: Can't create database 'mysqltest_xyz'; database exists
2615
drop database if exists mysqltest_xyz;
2617
drop database if exists mysqltest_xyz;
2618
deallocate prepare abc;
2619
drop table if exists t1;
2620
create table t1 (a int, b char(5));
2621
insert into t1 values (1, "one"), (2, "two"), (3, "three");
2622
create procedure proc_1() checksum table xyz;
2627
Error 1146 Table 'test.xyz' doesn't exist
2632
Error 1146 Table 'test.xyz' doesn't exist
2637
Error 1146 Table 'test.xyz' doesn't exist
2638
drop procedure proc_1;
2639
create function func_1() returns int begin checksum table t1; return 1; end|
2640
ERROR 0A000: Not allowed to return a result set from a function
2641
select func_1(), func_1(), func_1() from dual;
2642
ERROR 42000: FUNCTION test.func_1 does not exist
2643
drop function func_1;
2644
ERROR 42000: FUNCTION test.func_1 does not exist
2645
prepare abc from "checksum table t1";
2655
deallocate prepare abc;
2656
create procedure proc_1() create user pstest_xyz@localhost;
2658
drop user pstest_xyz@localhost;
2661
ERROR HY000: Operation CREATE USER failed for 'pstest_xyz'@'localhost'
2662
drop user pstest_xyz@localhost;
2664
drop user pstest_xyz@localhost;
2665
drop procedure proc_1;
2666
create function func_1() returns int begin create user pstest_xyz@localhost; return 1; end|
2667
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
2668
select func_1(), func_1(), func_1() from dual;
2669
ERROR 42000: FUNCTION test.func_1 does not exist
2670
drop function func_1;
2671
ERROR 42000: FUNCTION test.func_1 does not exist
2672
prepare abc from "create user pstest_xyz@localhost";
2674
drop user pstest_xyz@localhost;
2677
ERROR HY000: Operation CREATE USER failed for 'pstest_xyz'@'localhost'
2678
drop user pstest_xyz@localhost;
2680
drop user pstest_xyz@localhost;
2681
deallocate prepare abc;
2682
drop event if exists xyz;
2683
create function func_1() returns int begin create event xyz on schedule at now() do select 123; return 1; end|
2684
ERROR HY000: Recursion of EVENT DDL statements is forbidden when body is present
2685
select func_1(), func_1(), func_1() from dual;
2686
ERROR 42000: FUNCTION test.func_1 does not exist
2687
drop function func_1;
2688
ERROR 42000: FUNCTION test.func_1 does not exist
2689
prepare abc from "create event xyz on schedule at now() do select 123";
2690
ERROR HY000: This command is not supported in the prepared statement protocol yet
2691
deallocate prepare abc;
2692
ERROR HY000: Unknown prepared statement handler (abc) given to DEALLOCATE PREPARE
2693
drop event if exists xyz;
2694
create event xyz on schedule every 5 minute disable do select 123;
2695
create procedure proc_1() alter event xyz comment 'xyz';
2698
create event xyz on schedule every 5 minute disable do select 123;
2701
create event xyz on schedule every 5 minute disable do select 123;
2704
drop procedure proc_1;
2705
create function func_1() returns int begin alter event xyz comment 'xyz'; return 1; end|
2706
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
2707
prepare abc from "alter event xyz comment 'xyz'";
2708
ERROR HY000: This command is not supported in the prepared statement protocol yet
2709
deallocate prepare abc;
2710
ERROR HY000: Unknown prepared statement handler (abc) given to DEALLOCATE PREPARE
2711
drop event if exists xyz;
2712
create event xyz on schedule every 5 minute disable do select 123;
2713
create procedure proc_1() drop event xyz;
2715
create event xyz on schedule every 5 minute disable do select 123;
2718
ERROR HY000: Unknown event 'xyz'
2719
drop procedure proc_1;
2720
create function func_1() returns int begin drop event xyz; return 1; end|
2721
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
2722
prepare abc from "drop event xyz";
2723
ERROR HY000: This command is not supported in the prepared statement protocol yet
2724
deallocate prepare abc;
2725
ERROR HY000: Unknown prepared statement handler (abc) given to DEALLOCATE PREPARE
2726
drop table if exists t1;
2727
create table t1 (a int, b char(5)) engine=myisam;
2728
insert into t1 values (1, "one"), (2, "two"), (3, "three");
2729
SET GLOBAL new_cache.key_buffer_size=128*1024;
2730
create procedure proc_1() cache index t1 in new_cache;
2732
Table Op Msg_type Msg_text
2733
test.t1 assign_to_keycache status OK
2735
Table Op Msg_type Msg_text
2736
test.t1 assign_to_keycache status OK
2738
Table Op Msg_type Msg_text
2739
test.t1 assign_to_keycache status OK
2740
drop procedure proc_1;
2741
SET GLOBAL second_cache.key_buffer_size=128*1024;
2742
prepare abc from "cache index t1 in second_cache";
2744
Table Op Msg_type Msg_text
2745
test.t1 assign_to_keycache status OK
2747
Table Op Msg_type Msg_text
2748
test.t1 assign_to_keycache status OK
2750
Table Op Msg_type Msg_text
2751
test.t1 assign_to_keycache status OK
2752
deallocate prepare abc;
2754
drop table if exists t1;
2755
drop table if exists t2;
2756
create table t1 (a int, b char(5)) engine=myisam;
2757
insert into t1 values (1, "one"), (2, "two"), (3, "three");
2758
create table t2 (a int, b char(5)) engine=myisam;
2759
insert into t2 values (1, "one"), (2, "two"), (3, "three");
2760
create procedure proc_1() load index into cache t1 ignore leaves;
2762
Table Op Msg_type Msg_text
2763
test.t1 preload_keys status OK
2765
Table Op Msg_type Msg_text
2766
test.t1 preload_keys status OK
2768
Table Op Msg_type Msg_text
2769
test.t1 preload_keys status OK
2770
drop procedure proc_1;
2771
create function func_1() returns int begin load index into cache t1 ignore leaves; return 1; end|
2772
ERROR 0A000: Not allowed to return a result set from a function
2773
prepare abc from "load index into cache t2 ignore leaves";
2775
Table Op Msg_type Msg_text
2776
test.t2 preload_keys status OK
2778
Table Op Msg_type Msg_text
2779
test.t2 preload_keys status OK
2781
Table Op Msg_type Msg_text
2782
test.t2 preload_keys status OK
2783
deallocate prepare abc;
2785
create procedure proc_1() show errors;
2792
drop procedure proc_1;
2793
create function func_1() returns int begin show errors; return 1; end|
2794
ERROR 0A000: Not allowed to return a result set from a function
2795
prepare abc from "show errors";
2796
deallocate prepare abc;
2797
drop table if exists t1;
2798
drop table if exists t2;
2799
create procedure proc_1() show warnings;
2800
drop table if exists t1;
2802
Note 1051 Unknown table 't1'
2805
Note 1051 Unknown table 't1'
2806
drop table if exists t2;
2808
Note 1051 Unknown table 't2'
2811
Note 1051 Unknown table 't2'
2812
drop table if exists t1, t2;
2814
Note 1051 Unknown table 't1'
2815
Note 1051 Unknown table 't2'
2818
Note 1051 Unknown table 't1'
2819
Note 1051 Unknown table 't2'
2820
drop procedure proc_1;
2821
create function func_1() returns int begin show warnings; return 1; end|
2822
ERROR 0A000: Not allowed to return a result set from a function
2823
prepare abc from "show warnings";
2824
drop table if exists t1;
2826
Note 1051 Unknown table 't1'
2829
Note 1051 Unknown table 't1'
2830
drop table if exists t2;
2832
Note 1051 Unknown table 't2'
2835
Note 1051 Unknown table 't2'
2836
drop table if exists t1, t2;
2838
Note 1051 Unknown table 't1'
2839
Note 1051 Unknown table 't2'
2842
Note 1051 Unknown table 't1'
2843
Note 1051 Unknown table 't2'
2844
deallocate prepare abc;
2845
set @my_password="password";
2846
set @my_data="clear text to encode";
2847
prepare stmt1 from 'select decode(encode(?, ?), ?)';
2848
execute stmt1 using @my_data, @my_password, @my_password;
2849
decode(encode(?, ?), ?)
2850
clear text to encode
2851
set @my_data="more text to encode";
2852
execute stmt1 using @my_data, @my_password, @my_password;
2853
decode(encode(?, ?), ?)
2855
set @my_password="new password";
2856
execute stmt1 using @my_data, @my_password, @my_password;
2857
decode(encode(?, ?), ?)
2859
deallocate prepare stmt1;
2860
set @to_format="123456789.123456789";
2862
prepare stmt2 from 'select format(?, ?)';
2863
execute stmt2 using @to_format, @dec;
2867
execute stmt2 using @to_format, @dec;
2871
execute stmt2 using @to_format, @dec;
2875
execute stmt2 using @to_format, @dec;
2878
set @to_format="100";
2879
execute stmt2 using @to_format, @dec;
2882
set @to_format="1000000";
2883
execute stmt2 using @to_format, @dec;
2886
set @to_format="10000";
2887
execute stmt2 using @to_format, @dec;
2890
deallocate prepare stmt2;
2891
DROP TABLE IF EXISTS t1, t2;
2892
CREATE TABLE t1 (i INT);
2893
INSERT INTO t1 VALUES (1);
2894
CREATE TABLE t2 (i INT);
2895
INSERT INTO t2 VALUES (2);
2896
LOCK TABLE t1 READ, t2 WRITE;
2897
PREPARE stmt1 FROM "SELECT i FROM t1";
2898
PREPARE stmt2 FROM "INSERT INTO t2 (i) VALUES (3)";
2911
ALTER TABLE t1 ADD COLUMN j INT;
2912
ALTER TABLE t2 ADD COLUMN j INT;
2913
INSERT INTO t1 VALUES (4, 5);
2914
INSERT INTO t2 VALUES (4, 5);
2927
drop table if exists t1;
2929
Note 1051 Unknown table 't1'
2931
from "create table t1 (c char(100) character set utf8, key (c(10)))";
2933
show create table t1;
2935
t1 CREATE TABLE `t1` (
2936
`c` char(100) CHARACTER SET utf8 DEFAULT NULL,
2938
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2941
show create table t1;
2943
t1 CREATE TABLE `t1` (
2944
`c` char(100) CHARACTER SET utf8 DEFAULT NULL,
2946
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2948
drop table if exists t1, t2;
2949
create table t1 (a int, b int);
2950
create table t2 like t1;
2951
insert into t1 (a, b) values (1,1), (1,2), (1,3), (1,4), (1,5),
2952
(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
2953
insert into t2 select a, max(b) from t1 group by a;
2954
prepare stmt from "delete from t2 where (select (select max(b) from t1 group
2955
by a having a < 2) x from t1) > 10000";
2956
delete from t2 where (select (select max(b) from t1 group
2957
by a having a < 2) x from t1) > 10000;
2958
ERROR 21000: Subquery returns more than 1 row
2960
ERROR 21000: Subquery returns more than 1 row
2962
ERROR 21000: Subquery returns more than 1 row
2963
deallocate prepare stmt;
2966
# Bug#27430 Crash in subquery code when in PS and table DDL changed
2969
# This part of the test doesn't work in embedded server, this is
2970
# why it's here. For the main test see ps_ddl*.test
2972
drop table if exists t1;
2973
create table t1 (a int);
2974
prepare stmt from "show events where (1) in (select * from t1)";
2976
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
2978
create table t1 (x int);
2980
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
2982
deallocate prepare stmt;
2984
# Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0
2986
prepare encode from "select encode(?, ?) into @ciphertext";
2987
prepare decode from "select decode(?, ?) into @plaintext";
2988
set @str="abc", @key="cba";
2989
execute encode using @str, @key;
2990
execute decode using @ciphertext, @key;
2994
set @str="bcd", @key="dcb";
2995
execute encode using @str, @key;
2996
execute decode using @ciphertext, @key;
3000
deallocate prepare encode;
3001
deallocate prepare decode;
3003
# Bug#52124 memory leaks like a sieve in datetime, timestamp, time, date fields + warnings
3005
CREATE TABLE t1 (a DATETIME NOT NULL, b TINYINT);
3006
INSERT INTO t1 VALUES (0, 0),(0, 0);
3007
PREPARE stmt FROM "SELECT 1 FROM t1 WHERE
3008
ROW(a, b) >= ROW('1', (SELECT 1 FROM t1 WHERE a > 1234))";
3013
DEALLOCATE PREPARE stmt;
3016
# Bug#54494 crash with explain extended and prepared statements
3018
CREATE TABLE t1(a INT);
3019
INSERT INTO t1 VALUES (1),(2);
3020
SET @save_optimizer_switch=@@optimizer_switch;
3021
SET optimizer_switch='outer_join_with_cache=off';
3022
PREPARE stmt FROM 'EXPLAIN EXTENDED SELECT 1 FROM t1 RIGHT JOIN t1 t2 ON 1';
3024
id select_type table type possible_keys key key_len ref rows filtered Extra
3025
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00
3026
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
3028
Note 1003 select 1 AS `1` from `test`.`t1` `t2` left join `test`.`t1` on(1) where 1
3030
id select_type table type possible_keys key key_len ref rows filtered Extra
3031
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00
3032
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
3034
Note 1003 select 1 AS `1` from `test`.`t1` `t2` left join `test`.`t1` on(1) where 1
3035
DEALLOCATE PREPARE stmt;
3036
SET optimizer_switch=@save_optimizer_switch;
3039
# Bug#54488 crash when using explain and prepared statements with subqueries
3041
CREATE TABLE t1(f1 INT);
3042
INSERT INTO t1 VALUES (1),(1);
3043
PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 WHERE (SELECT (SELECT 1 FROM t1 GROUP BY f1))';
3045
id select_type table type possible_keys key key_len ref rows Extra
3046
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
3047
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
3048
3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
3050
id select_type table type possible_keys key key_len ref rows Extra
3051
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
3052
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
3053
3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
3054
DEALLOCATE PREPARE stmt;
3059
# lp:1001500 Crash on the second execution of the PS for
3060
# a query with degenerated conjunctive condition
3061
# (see also mysql bug#12582849)
3064
pk INTEGER AUTO_INCREMENT,
3065
col_int_nokey INTEGER,
3066
col_int_key INTEGER,
3067
col_varchar_key VARCHAR(1),
3068
col_varchar_nokey VARCHAR(1),
3071
KEY (col_varchar_key, col_int_key)
3074
col_int_key, col_int_nokey,
3075
col_varchar_key, col_varchar_nokey
3078
(62, 150, 'v', 'v');
3080
pk INTEGER AUTO_INCREMENT,
3081
col_int_nokey INTEGER,
3082
col_int_key INTEGER,
3083
col_varchar_key VARCHAR(1),
3084
col_varchar_nokey VARCHAR(1),
3087
KEY (col_varchar_key, col_int_key)
3090
col_int_key, col_int_nokey,
3091
col_varchar_key, col_varchar_nokey
3093
(8, NULL, 'x', 'x'),
3097
( SELECT MAX( SQ1_alias2 .col_int_nokey ) AS SQ1_field1
3098
FROM ( t2 AS SQ1_alias1 RIGHT JOIN t1 AS SQ1_alias2
3099
ON ( SQ1_alias2.col_varchar_key = SQ1_alias1.col_varchar_nokey )
3101
WHERE SQ1_alias2.pk < alias1.col_int_nokey OR alias1.pk
3103
FROM ( t1 AS alias1 JOIN t2 AS alias2 ON alias2.pk )
3112
DEALLOCATE PREPARE stmt;
3116
# WL#4435: Support OUT-parameters in prepared statements.
3119
DROP PROCEDURE IF EXISTS p_string;
3120
DROP PROCEDURE IF EXISTS p_double;
3121
DROP PROCEDURE IF EXISTS p_int;
3122
DROP PROCEDURE IF EXISTS p_decimal;
3124
CREATE PROCEDURE p_string(
3136
CREATE PROCEDURE p_double(
3138
OUT v1 DOUBLE(4, 2),
3140
INOUT v3 DOUBLE(4, 2))
3148
CREATE PROCEDURE p_int(
3160
CREATE PROCEDURE p_decimal(
3162
OUT v1 DECIMAL(4, 2),
3163
IN v2 DECIMAL(4, 2),
3164
INOUT v3 DECIMAL(4, 2))
3172
PREPARE stmt_str FROM 'CALL p_string(?, ?, ?, ?)';
3173
PREPARE stmt_dbl FROM 'CALL p_double(?, ?, ?, ?)';
3174
PREPARE stmt_int FROM 'CALL p_int(?, ?, ?, ?)';
3175
PREPARE stmt_dec FROM 'CALL p_decimal(?, ?, ?, ?)';
3177
SET @x_str_1 = NULL;
3178
SET @x_str_2 = NULL;
3179
SET @x_str_3 = NULL;
3180
SET @x_dbl_1 = NULL;
3181
SET @x_dbl_2 = NULL;
3182
SET @x_dbl_3 = NULL;
3183
SET @x_int_1 = NULL;
3184
SET @x_int_2 = NULL;
3185
SET @x_int_3 = NULL;
3186
SET @x_dec_1 = NULL;
3187
SET @x_dec_2 = NULL;
3188
SET @x_dec_3 = NULL;
3190
-- Testing strings...
3192
EXECUTE stmt_str USING @x_int_1, @x_str_1, @x_str_2, @x_str_3;
3193
SELECT @x_int_1, @x_str_1, @x_str_2, @x_str_3;
3194
@x_int_1 @x_str_1 @x_str_2 @x_str_3
3195
NULL test_v1 NULL test_v3
3197
EXECUTE stmt_str USING @x_int_1, @x_str_1, @x_str_2, @x_str_3;
3198
SELECT @x_int_1, @x_str_1, @x_str_2, @x_str_3;
3199
@x_int_1 @x_str_1 @x_str_2 @x_str_3
3200
NULL test_v1 NULL test_v3
3202
-- Testing doubles...
3204
EXECUTE stmt_dbl USING @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3;
3205
SELECT @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3;
3206
@x_int_1 @x_dbl_1 @x_dbl_2 @x_dbl_3
3207
NULL 12.34 NULL 56.78
3209
EXECUTE stmt_dbl USING @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3;
3210
SELECT @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3;
3211
@x_int_1 @x_dbl_1 @x_dbl_2 @x_dbl_3
3212
NULL 12.34 NULL 56.78
3216
EXECUTE stmt_int USING @x_str_1, @x_int_1, @x_int_2, @x_int_3;
3217
SELECT @x_str_1, @x_int_1, @x_int_2, @x_int_3;
3218
@x_str_1 @x_int_1 @x_int_2 @x_int_3
3219
test_v1 1234 NULL 5678
3221
EXECUTE stmt_int USING @x_str_1, @x_int_1, @x_int_2, @x_int_3;
3222
SELECT @x_str_1, @x_int_1, @x_int_2, @x_int_3;
3223
@x_str_1 @x_int_1 @x_int_2 @x_int_3
3224
test_v1 1234 NULL 5678
3228
EXECUTE stmt_dec USING @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3;
3229
SELECT @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3;
3230
@x_int_1 @x_dec_1 @x_dec_2 @x_dec_3
3231
1234 12.34 NULL 56.78
3233
EXECUTE stmt_dec USING @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3;
3234
SELECT @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3;
3235
@x_int_1 @x_dec_1 @x_dec_2 @x_dec_3
3236
1234 12.34 NULL 56.78
3238
DEALLOCATE PREPARE stmt_str;
3239
DEALLOCATE PREPARE stmt_dbl;
3240
DEALLOCATE PREPARE stmt_int;
3241
DEALLOCATE PREPARE stmt_dec;
3243
DROP PROCEDURE p_string;
3244
DROP PROCEDURE p_double;
3245
DROP PROCEDURE p_int;
3246
DROP PROCEDURE p_decimal;
3248
DROP PROCEDURE IF EXISTS p1;
3249
DROP PROCEDURE IF EXISTS p2;
3251
CREATE PROCEDURE p1(OUT v1 CHAR(10))
3254
CREATE PROCEDURE p2(OUT v2 CHAR(10))
3256
SET @query = 'CALL p1(?)';
3257
PREPARE stmt1 FROM @query;
3258
EXECUTE stmt1 USING @u1;
3259
DEALLOCATE PREPARE stmt1;
3273
CREATE PROCEDURE p1(OUT v TINYINT)
3275
PREPARE stmt1 FROM 'CALL p1(?)';
3276
EXECUTE stmt1 USING @a;
3277
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3278
SHOW CREATE TABLE tmp1;
3280
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3281
`c1` bigint(20) DEFAULT NULL
3282
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3283
SELECT @a, @a = 127;
3286
DROP TEMPORARY TABLE tmp1;
3291
CREATE PROCEDURE p1(OUT v SMALLINT)
3293
PREPARE stmt1 FROM 'CALL p1(?)';
3294
EXECUTE stmt1 USING @a;
3295
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3296
SHOW CREATE TABLE tmp1;
3298
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3299
`c1` bigint(20) DEFAULT NULL
3300
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3301
SELECT @a, @a = 32767;
3304
DROP TEMPORARY TABLE tmp1;
3309
CREATE PROCEDURE p1(OUT v MEDIUMINT)
3311
PREPARE stmt1 FROM 'CALL p1(?)';
3312
EXECUTE stmt1 USING @a;
3313
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3314
SHOW CREATE TABLE tmp1;
3316
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3317
`c1` bigint(20) DEFAULT NULL
3318
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3319
SELECT @a, @a = 8388607;
3322
DROP TEMPORARY TABLE tmp1;
3327
CREATE PROCEDURE p1(OUT v INT)
3329
PREPARE stmt1 FROM 'CALL p1(?)';
3330
EXECUTE stmt1 USING @a;
3331
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3332
SHOW CREATE TABLE tmp1;
3334
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3335
`c1` bigint(20) DEFAULT NULL
3336
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3337
SELECT @a, @a = 2147483647;
3340
DROP TEMPORARY TABLE tmp1;
3345
CREATE PROCEDURE p1(OUT v BIGINT)
3346
SET v = 9223372036854775807;
3347
PREPARE stmt1 FROM 'CALL p1(?)';
3348
EXECUTE stmt1 USING @a;
3349
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3350
SHOW CREATE TABLE tmp1;
3352
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3353
`c1` bigint(20) DEFAULT NULL
3354
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3355
SELECT @a, @a = 9223372036854775807;
3356
@a @a = 9223372036854775807
3357
9223372036854775807 1
3358
DROP TEMPORARY TABLE tmp1;
3363
CREATE PROCEDURE p1(OUT v BIT(11))
3364
SET v = b'10100100101';
3365
PREPARE stmt1 FROM 'CALL p1(?)';
3366
EXECUTE stmt1 USING @a;
3367
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3368
SHOW CREATE TABLE tmp1;
3370
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3371
`c1` bigint(20) DEFAULT NULL
3372
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3373
SELECT @a, @a = b'10100100101';
3374
@a @a = b'10100100101'
3376
DROP TEMPORARY TABLE tmp1;
3381
CREATE PROCEDURE p1(OUT v TIMESTAMP)
3382
SET v = '2007-11-18 15:01:02';
3383
PREPARE stmt1 FROM 'CALL p1(?)';
3384
EXECUTE stmt1 USING @a;
3385
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3386
SHOW CREATE TABLE tmp1;
3388
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3390
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3391
SELECT @a, @a = '2007-11-18 15:01:02';
3392
@a @a = '2007-11-18 15:01:02'
3393
2007-11-18 15:01:02 1
3394
DROP TEMPORARY TABLE tmp1;
3399
CREATE PROCEDURE p1(OUT v DATETIME)
3400
SET v = '1234-11-12 12:34:59';
3401
PREPARE stmt1 FROM 'CALL p1(?)';
3402
EXECUTE stmt1 USING @a;
3403
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3404
SHOW CREATE TABLE tmp1;
3406
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3408
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3409
SELECT @a, @a = '1234-11-12 12:34:59';
3410
@a @a = '1234-11-12 12:34:59'
3411
1234-11-12 12:34:59 1
3412
DROP TEMPORARY TABLE tmp1;
3417
CREATE PROCEDURE p1(OUT v TIME)
3418
SET v = '123:45:01';
3419
PREPARE stmt1 FROM 'CALL p1(?)';
3420
EXECUTE stmt1 USING @a;
3421
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3422
SHOW CREATE TABLE tmp1;
3424
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3426
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3427
SELECT @a, @a = '123:45:01';
3430
DROP TEMPORARY TABLE tmp1;
3435
CREATE PROCEDURE p1(OUT v DATE)
3436
SET v = '1234-11-12';
3437
PREPARE stmt1 FROM 'CALL p1(?)';
3438
EXECUTE stmt1 USING @a;
3439
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3440
SHOW CREATE TABLE tmp1;
3442
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3444
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3445
SELECT @a, @a = '1234-11-12';
3446
@a @a = '1234-11-12'
3448
DROP TEMPORARY TABLE tmp1;
3453
CREATE PROCEDURE p1(OUT v YEAR)
3455
PREPARE stmt1 FROM 'CALL p1(?)';
3456
EXECUTE stmt1 USING @a;
3457
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3458
SHOW CREATE TABLE tmp1;
3460
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3461
`c1` bigint(20) DEFAULT NULL
3462
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3463
SELECT @a, @a = 2010;
3466
DROP TEMPORARY TABLE tmp1;
3471
CREATE PROCEDURE p1(OUT v FLOAT(7, 4))
3473
PREPARE stmt1 FROM 'CALL p1(?)';
3474
EXECUTE stmt1 USING @a;
3475
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3476
SHOW CREATE TABLE tmp1;
3478
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3479
`c1` double DEFAULT NULL
3480
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3481
SELECT @a, @a - 123.4567 < 0.00001;
3482
@a @a - 123.4567 < 0.00001
3483
123.45670318603516 1
3484
DROP TEMPORARY TABLE tmp1;
3489
CREATE PROCEDURE p1(OUT v DOUBLE(8, 5))
3491
PREPARE stmt1 FROM 'CALL p1(?)';
3492
EXECUTE stmt1 USING @a;
3493
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3494
SHOW CREATE TABLE tmp1;
3496
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3497
`c1` double DEFAULT NULL
3498
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3499
SELECT @a, @a - 123.45678 < 0.000001;
3500
@a @a - 123.45678 < 0.000001
3502
DROP TEMPORARY TABLE tmp1;
3507
CREATE PROCEDURE p1(OUT v DECIMAL(9, 6))
3509
PREPARE stmt1 FROM 'CALL p1(?)';
3510
EXECUTE stmt1 USING @a;
3511
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3512
SHOW CREATE TABLE tmp1;
3514
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3515
`c1` decimal(65,30) DEFAULT NULL
3516
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3517
SELECT @a, @a = 123.456789;
3520
DROP TEMPORARY TABLE tmp1;
3525
CREATE PROCEDURE p1(OUT v CHAR(32))
3526
SET v = REPEAT('a', 16);
3527
PREPARE stmt1 FROM 'CALL p1(?)';
3528
EXECUTE stmt1 USING @a;
3529
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3530
SHOW CREATE TABLE tmp1;
3532
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3534
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3535
SELECT @a, @a = REPEAT('a', 16);
3536
@a @a = REPEAT('a', 16)
3538
DROP TEMPORARY TABLE tmp1;
3543
CREATE PROCEDURE p1(OUT v VARCHAR(32))
3544
SET v = REPEAT('b', 16);
3545
PREPARE stmt1 FROM 'CALL p1(?)';
3546
EXECUTE stmt1 USING @a;
3547
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3548
SHOW CREATE TABLE tmp1;
3550
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3552
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3553
SELECT @a, @a = REPEAT('b', 16);
3554
@a @a = REPEAT('b', 16)
3556
DROP TEMPORARY TABLE tmp1;
3561
CREATE PROCEDURE p1(OUT v TINYTEXT)
3562
SET v = REPEAT('c', 16);
3563
PREPARE stmt1 FROM 'CALL p1(?)';
3564
EXECUTE stmt1 USING @a;
3565
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3566
SHOW CREATE TABLE tmp1;
3568
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3570
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3571
SELECT @a, @a = REPEAT('c', 16);
3572
@a @a = REPEAT('c', 16)
3574
DROP TEMPORARY TABLE tmp1;
3579
CREATE PROCEDURE p1(OUT v TEXT)
3580
SET v = REPEAT('d', 16);
3581
PREPARE stmt1 FROM 'CALL p1(?)';
3582
EXECUTE stmt1 USING @a;
3583
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3584
SHOW CREATE TABLE tmp1;
3586
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3588
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3589
SELECT @a, @a = REPEAT('d', 16);
3590
@a @a = REPEAT('d', 16)
3592
DROP TEMPORARY TABLE tmp1;
3597
CREATE PROCEDURE p1(OUT v MEDIUMTEXT)
3598
SET v = REPEAT('e', 16);
3599
PREPARE stmt1 FROM 'CALL p1(?)';
3600
EXECUTE stmt1 USING @a;
3601
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3602
SHOW CREATE TABLE tmp1;
3604
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3606
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3607
SELECT @a, @a = REPEAT('e', 16);
3608
@a @a = REPEAT('e', 16)
3610
DROP TEMPORARY TABLE tmp1;
3615
CREATE PROCEDURE p1(OUT v LONGTEXT)
3616
SET v = REPEAT('f', 16);
3617
PREPARE stmt1 FROM 'CALL p1(?)';
3618
EXECUTE stmt1 USING @a;
3619
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3620
SHOW CREATE TABLE tmp1;
3622
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3624
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3625
SELECT @a, @a = REPEAT('f', 16);
3626
@a @a = REPEAT('f', 16)
3628
DROP TEMPORARY TABLE tmp1;
3633
CREATE PROCEDURE p1(OUT v BINARY(32))
3634
SET v = REPEAT('g', 32);
3635
PREPARE stmt1 FROM 'CALL p1(?)';
3636
EXECUTE stmt1 USING @a;
3637
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3638
SHOW CREATE TABLE tmp1;
3640
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3642
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3643
SELECT @a, @a = REPEAT('g', 32);
3644
@a @a = REPEAT('g', 32)
3645
gggggggggggggggggggggggggggggggg 1
3646
DROP TEMPORARY TABLE tmp1;
3651
CREATE PROCEDURE p1(OUT v VARBINARY(32))
3652
SET v = REPEAT('h', 16);
3653
PREPARE stmt1 FROM 'CALL p1(?)';
3654
EXECUTE stmt1 USING @a;
3655
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3656
SHOW CREATE TABLE tmp1;
3658
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3660
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3661
SELECT @a, @a = REPEAT('h', 16);
3662
@a @a = REPEAT('h', 16)
3664
DROP TEMPORARY TABLE tmp1;
3669
CREATE PROCEDURE p1(OUT v TINYBLOB)
3670
SET v = REPEAT('i', 16);
3671
PREPARE stmt1 FROM 'CALL p1(?)';
3672
EXECUTE stmt1 USING @a;
3673
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3674
SHOW CREATE TABLE tmp1;
3676
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3678
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3679
SELECT @a, @a = REPEAT('i', 16);
3680
@a @a = REPEAT('i', 16)
3682
DROP TEMPORARY TABLE tmp1;
3687
CREATE PROCEDURE p1(OUT v BLOB)
3688
SET v = REPEAT('j', 16);
3689
PREPARE stmt1 FROM 'CALL p1(?)';
3690
EXECUTE stmt1 USING @a;
3691
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3692
SHOW CREATE TABLE tmp1;
3694
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3696
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3697
SELECT @a, @a = REPEAT('j', 16);
3698
@a @a = REPEAT('j', 16)
3700
DROP TEMPORARY TABLE tmp1;
3705
CREATE PROCEDURE p1(OUT v MEDIUMBLOB)
3706
SET v = REPEAT('k', 16);
3707
PREPARE stmt1 FROM 'CALL p1(?)';
3708
EXECUTE stmt1 USING @a;
3709
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3710
SHOW CREATE TABLE tmp1;
3712
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3714
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3715
SELECT @a, @a = REPEAT('k', 16);
3716
@a @a = REPEAT('k', 16)
3718
DROP TEMPORARY TABLE tmp1;
3723
CREATE PROCEDURE p1(OUT v LONGBLOB)
3724
SET v = REPEAT('l', 16);
3725
PREPARE stmt1 FROM 'CALL p1(?)';
3726
EXECUTE stmt1 USING @a;
3727
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3728
SHOW CREATE TABLE tmp1;
3730
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3732
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3733
SELECT @a, @a = REPEAT('l', 16);
3734
@a @a = REPEAT('l', 16)
3736
DROP TEMPORARY TABLE tmp1;
3741
CREATE PROCEDURE p1(OUT v SET('aaa', 'bbb'))
3743
PREPARE stmt1 FROM 'CALL p1(?)';
3744
EXECUTE stmt1 USING @a;
3745
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3746
SHOW CREATE TABLE tmp1;
3748
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3750
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3751
SELECT @a, @a = 'aaa';
3754
DROP TEMPORARY TABLE tmp1;
3759
CREATE PROCEDURE p1(OUT v ENUM('aaa', 'bbb'))
3761
PREPARE stmt1 FROM 'CALL p1(?)';
3762
EXECUTE stmt1 USING @a;
3763
CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
3764
SHOW CREATE TABLE tmp1;
3766
tmp1 CREATE TEMPORARY TABLE `tmp1` (
3768
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3769
SELECT @a, @a = 'aaa';
3772
DROP TEMPORARY TABLE tmp1;
3777
# WL#4284: Transactional DDL locking
3779
DROP TABLE IF EXISTS t1;
3780
CREATE TABLE t1 (a INT);
3784
# Test that preparing a CREATE TABLE does not take a exclusive metdata lock.
3785
PREPARE stmt1 FROM "CREATE TABLE t1 AS SELECT 1";
3787
ERROR 42S01: Table 't1' already exists
3788
DEALLOCATE PREPARE stmt1;
3791
# WL#4284: Transactional DDL locking
3793
# Test that metadata locks taken during prepare are released.
3795
DROP TABLE IF EXISTS t1;
3796
CREATE TABLE t1 (a INT);
3798
PREPARE stmt1 FROM "SELECT * FROM t1";
3802
# Bug#56115: invalid memory reads when PS selecting from
3803
# information_schema tables
3804
# Bug#58701: crash in Field::make_field, cursor-protocol
3806
# NOTE: MTR should be run both with --ps-protocol and --cursor-protocol.
3810
FROM (SELECT 1 UNION SELECT 2) t;
3815
# Bug#13805127: Stored program cache produces wrong result in same THD
3819
SELECT c1, t2.c2, count(c3)
3822
SELECT 3 as c2 FROM dual WHERE @x = 1
3824
SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
3827
SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
3829
SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
3831
SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
3838
SELECT c1, t2.c2, count(c3)
3841
SELECT 3 as c2 FROM dual WHERE @x = 1
3843
SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
3846
SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
3848
SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
3850
SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
3855
2012-03-01 01:00:00 2 1
3856
2012-03-01 01:00:00 3 1
3857
2012-03-01 02:00:00 3 1
3861
2012-03-01 01:00:00 2 1
3862
2012-03-01 01:00:00 3 1
3863
2012-03-01 02:00:00 3 1
3866
SELECT c1, t2.c2, count(c3)
3869
SELECT 3 as c2 FROM dual WHERE @x = 1
3871
SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
3874
SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
3876
SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
3878
SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
3883
2012-03-01 01:00:00 2 1
3887
2012-03-01 01:00:00 2 1
3890
SELECT c1, t2.c2, count(c3)
3893
SELECT 3 as c2 FROM dual WHERE @x = 1
3895
SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
3898
SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
3900
SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
3902
SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
3907
2012-03-01 01:00:00 2 1
3908
2012-03-01 01:00:00 3 1
3909
2012-03-01 02:00:00 3 1
3913
2012-03-01 01:00:00 2 1
3914
2012-03-01 01:00:00 3 1
3915
2012-03-01 02:00:00 3 1
3916
DEALLOCATE PREPARE s1;
3919
prepare stmt from "select date('2010-10-10') between '2010-09-09' and ?";
3920
set @a='2010-11-11';
3921
execute stmt using @a;
3922
date('2010-10-10') between '2010-09-09' and ?
3924
execute stmt using @a;
3925
date('2010-10-10') between '2010-09-09' and ?
3927
set @a='2010-08-08';
3928
execute stmt using @a;
3929
date('2010-10-10') between '2010-09-09' and ?
3931
execute stmt using @a;
3932
date('2010-10-10') between '2010-09-09' and ?
3935
# Bug #892725: look-up is changed for a full scan when executing PS
3937
create table t1 (a int primary key, b int);
3938
insert into t1 values
3939
(7,70), (3,40), (4,40), (8,70), (1,70), (9,50), (2,70);
3940
prepare st from 'select * from t1 where a=8';
3945
show status like '%Handler_read%';
3947
Handler_read_first 0
3953
Handler_read_rnd_deleted 0
3954
Handler_read_rnd_next 0
3959
show status like '%Handler_read%';
3961
Handler_read_first 0
3967
Handler_read_rnd_deleted 0
3968
Handler_read_rnd_next 0
3970
select * from t1 use index() where a=3;
3973
show status like '%Handler_read%';
3975
Handler_read_first 0
3981
Handler_read_rnd_deleted 0
3982
Handler_read_rnd_next 8
3987
show status like '%Handler_read%';
3989
Handler_read_first 0
3995
Handler_read_rnd_deleted 0
3996
Handler_read_rnd_next 0
3997
deallocate prepare st;
4000
# Bug mdev-5410: crash at the execution of PS with subselect
4001
# formed by UNION with global ORDER BY
4003
CREATE TABLE t1 (a int DEFAULT NULL);
4004
INSERT INTO t1 VALUES (2), (4);
4005
CREATE TABLE t2 (b int DEFAULT NULL);
4006
INSERT INTO t2 VALUES (1), (3);
4008
SELECT c1 FROM (SELECT (SELECT a FROM t1 WHERE t1.a <= t2.b
4010
SELECT a FROM t1 WHERE t1.a+3<= t2.b
4011
ORDER BY a DESC) AS c1 FROM t2) t3;
4023
# MDEV-5369: Wrong result (0 instead of NULL) on 2nd execution of
4024
# PS with LEFT JOIN, TEMPTABLE view
4026
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
4027
INSERT INTO t1 VALUES (0),(8);
4028
CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=MyISAM;
4029
CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
4030
SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk;
4033
PREPARE stmt FROM "SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk";
4040
DEALLOCATE PREPARE stmt;
4045
# MDEV-5505: Assertion `! is_set()' fails on PREPARE SELECT
4046
# with out of range in GROUP BY
4048
CREATE TABLE t1 (a INT);
4049
PREPARE stmt FROM "SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1";
4050
ERROR 22003: BIGINT UNSIGNED value is out of range in '(18446744073709551615 + 1)'
4051
SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1;
4052
ERROR 22003: BIGINT UNSIGNED value is out of range in '(18446744073709551615 + 1)'