2
drop table if exists t1, t1_1, t1_2,
4
drop table if exists t1, t9 ;
12
c1 tinyint, c2 smallint, c3 mediumint, c4 int,
13
c5 integer, c6 bigint, c7 float, c8 double,
14
c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
15
c13 date, c14 datetime, c15 timestamp, c16 time,
16
c17 year, c18 tinyint, c19 bool, c20 char,
17
c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
18
c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
19
c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'),
20
c32 set('monday', 'tuesday', 'wednesday'),
23
rename table t1 to t1_1, t9 to t9_1 ;
24
drop table if exists t1, t9 ;
32
c1 tinyint, c2 smallint, c3 mediumint, c4 int,
33
c5 integer, c6 bigint, c7 float, c8 double,
34
c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
35
c13 date, c14 datetime, c15 timestamp, c16 time,
36
c17 year, c18 tinyint, c19 bool, c20 char,
37
c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
38
c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
39
c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'),
40
c32 set('monday', 'tuesday', 'wednesday'),
43
rename table t1 to t1_2, t9 to t9_2 ;
48
) ENGINE = MERGE UNION=(t1_1,t1_2)
52
c1 tinyint, c2 smallint, c3 mediumint, c4 int,
53
c5 integer, c6 bigint, c7 float, c8 double,
54
c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
55
c13 date, c14 datetime, c15 timestamp, c16 time,
56
c17 year, c18 tinyint, c19 bool, c20 char,
57
c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
58
c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
59
c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'),
60
c32 set('monday', 'tuesday', 'wednesday'),
62
) ENGINE = MERGE UNION=(t9_1,t9_2)
65
insert into t1 values (1,'one');
66
insert into t1 values (2,'two');
67
insert into t1 values (3,'three');
68
insert into t1 values (4,'four');
72
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
73
c10= 1, c11= 1, c12 = 1,
74
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
75
c16= '11:11:11', c17= '2004',
76
c18= 1, c19=true, c20= 'a', c21= '123456789a',
77
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
78
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
79
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
81
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
82
c10= 9, c11= 9, c12 = 9,
83
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
84
c16= '11:11:11', c17= '2004',
85
c18= 1, c19=false, c20= 'a', c21= '123456789a',
86
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
87
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
88
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
91
------ simple select tests ------
92
prepare stmt1 from ' select * from t9 order by c1 ' ;
94
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
95
def test t9 t9 c1 c1 1 4 1 N 49155 0 63
96
def test t9 t9 c2 c2 2 6 1 Y 32768 0 63
97
def test t9 t9 c3 c3 9 9 1 Y 32768 0 63
98
def test t9 t9 c4 c4 3 11 1 Y 32768 0 63
99
def test t9 t9 c5 c5 3 11 1 Y 32768 0 63
100
def test t9 t9 c6 c6 8 20 1 Y 32768 0 63
101
def test t9 t9 c7 c7 4 12 1 Y 32768 31 63
102
def test t9 t9 c8 c8 5 22 1 Y 32768 31 63
103
def test t9 t9 c9 c9 5 22 1 Y 32768 31 63
104
def test t9 t9 c10 c10 5 22 1 Y 32768 31 63
105
def test t9 t9 c11 c11 246 9 6 Y 0 4 63
106
def test t9 t9 c12 c12 246 10 6 Y 0 4 63
107
def test t9 t9 c13 c13 10 10 10 Y 128 0 63
108
def test t9 t9 c14 c14 12 19 19 Y 128 0 63
109
def test t9 t9 c15 c15 7 19 19 N 9441 0 63
110
def test t9 t9 c16 c16 11 8 8 Y 128 0 63
111
def test t9 t9 c17 c17 13 4 4 Y 32864 0 63
112
def test t9 t9 c18 c18 1 4 1 Y 32768 0 63
113
def test t9 t9 c19 c19 1 1 1 Y 32768 0 63
114
def test t9 t9 c20 c20 254 1 1 Y 0 0 8
115
def test t9 t9 c21 c21 254 10 10 Y 0 0 8
116
def test t9 t9 c22 c22 253 30 30 Y 0 0 8
117
def test t9 t9 c23 c23 252 255 8 Y 144 0 63
118
def test t9 t9 c24 c24 252 255 8 Y 16 0 8
119
def test t9 t9 c25 c25 252 65535 4 Y 144 0 63
120
def test t9 t9 c26 c26 252 65535 4 Y 16 0 8
121
def test t9 t9 c27 c27 252 16777215 10 Y 144 0 63
122
def test t9 t9 c28 c28 252 16777215 10 Y 16 0 8
123
def test t9 t9 c29 c29 252 4294967295 8 Y 144 0 63
124
def test t9 t9 c30 c30 252 4294967295 8 Y 16 0 8
125
def test t9 t9 c31 c31 254 5 3 Y 256 0 8
126
def test t9 t9 c32 c32 254 24 7 Y 2048 0 8
127
c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32
128
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
129
9 9 9 9 9 9 9 9 9 9 9.0000 9.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 0 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext two tuesday
130
set @arg00='SELECT' ;
131
@arg00 a from t1 where a=1;
132
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@arg00 a from t1 where a=1' at line 1
133
prepare stmt1 from ' ? a from t1 where a=1 ';
134
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? a from t1 where a=1' at line 1
136
select @arg00, b from t1 where a=1 ;
139
prepare stmt1 from ' select ?, b from t1 where a=1 ' ;
140
execute stmt1 using @arg00 ;
144
select @arg00, b from t1 where a=1 ;
147
prepare stmt1 from ' select ?, b from t1 where a=1 ' ;
148
execute stmt1 using @arg00 ;
152
select @arg00, b from t1 where a=1 ;
155
prepare stmt1 from ' select ?, b from t1 where a=1 ' ;
156
execute stmt1 using @arg00 ;
160
select b, a - @arg00 from t1 where a=1 ;
163
prepare stmt1 from ' select b, a - ? from t1 where a=1 ' ;
164
execute stmt1 using @arg00 ;
168
select @arg00 as my_col ;
171
prepare stmt1 from ' select ? as my_col';
172
execute stmt1 using @arg00 ;
175
select @arg00 + 1 as my_col ;
178
prepare stmt1 from ' select ? + 1 as my_col';
179
execute stmt1 using @arg00 ;
182
select 1 + @arg00 as my_col ;
185
prepare stmt1 from ' select 1 + ? as my_col';
186
execute stmt1 using @arg00 ;
190
select substr(@arg00,1,2) from t1 where a=1 ;
193
prepare stmt1 from ' select substr(?,1,2) from t1 where a=1 ' ;
194
execute stmt1 using @arg00 ;
198
select substr('MySQL',@arg00,5) from t1 where a=1 ;
199
substr('MySQL',@arg00,5)
201
prepare stmt1 from ' select substr(''MySQL'',?,5) from t1 where a=1 ' ;
202
execute stmt1 using @arg00 ;
205
select substr('MySQL',1,@arg00) from t1 where a=1 ;
206
substr('MySQL',1,@arg00)
208
prepare stmt1 from ' select substr(''MySQL'',1,?) from t1 where a=1 ' ;
209
execute stmt1 using @arg00 ;
213
select a , concat(@arg00,b) from t1 order by a;
219
prepare stmt1 from ' select a , concat(?,b) from t1 order by a ' ;
220
execute stmt1 using @arg00;
226
select a , concat(b,@arg00) from t1 order by a ;
232
prepare stmt1 from ' select a , concat(b,?) from t1 order by a ' ;
233
execute stmt1 using @arg00;
240
select group_concat(@arg00,b order by a) from t1
242
group_concat(@arg00,b order by a)
243
MySQLone,MySQLtwo,MySQLthree,MySQLfour
244
prepare stmt1 from ' select group_concat(?,b order by a) from t1
246
execute stmt1 using @arg00;
247
group_concat(?,b order by a)
248
MySQLone,MySQLtwo,MySQLthree,MySQLfour
249
select group_concat(b,@arg00 order by a) from t1
251
group_concat(b,@arg00 order by a)
252
oneMySQL,twoMySQL,threeMySQL,fourMySQL
253
prepare stmt1 from ' select group_concat(b,? order by a) from t1
255
execute stmt1 using @arg00;
256
group_concat(b,? order by a)
257
oneMySQL,twoMySQL,threeMySQL,fourMySQL
259
set @arg01='second' ;
261
select @arg00, @arg01 from t1 where a=1 ;
264
prepare stmt1 from ' select ?, ? from t1 where a=1 ' ;
265
execute stmt1 using @arg00, @arg01 ;
268
execute stmt1 using @arg02, @arg01 ;
271
execute stmt1 using @arg00, @arg02 ;
274
execute stmt1 using @arg02, @arg02 ;
277
drop table if exists t5 ;
278
create table t5 (id1 int(11) not null default '0',
279
value2 varchar(100), value1 varchar(100)) ;
280
insert into t5 values (1,'hh','hh'),(2,'hh','hh'),
281
(1,'ii','ii'),(2,'ii','ii') ;
282
prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? order by id1,value1 ' ;
285
execute stmt1 using @arg00, @arg01 ;
291
drop table if exists t5 ;
292
create table t5(session_id char(9) not null) ;
293
insert into t5 values ('abc') ;
294
prepare stmt1 from ' select * from t5
295
where ?=''1111'' and session_id = ''abc'' ' ;
297
execute stmt1 using @arg00 ;
300
execute stmt1 using @arg00 ;
304
execute stmt1 using @arg00 ;
308
select a @arg00 t1 where a=1 ;
309
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@arg00 t1 where a=1' at line 1
310
prepare stmt1 from ' select a ? t1 where a=1 ' ;
311
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? t1 where a=1' at line 1
313
select a from @arg00 where a=1 ;
314
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@arg00 where a=1' at line 1
315
prepare stmt1 from ' select a from ? where a=1 ' ;
316
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? where a=1' at line 1
318
select a from t1 @arg00 a=1 ;
319
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@arg00 a=1' at line 1
320
prepare stmt1 from ' select a from t1 ? a=1 ' ;
321
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? a=1' at line 1
323
select a FROM t1 where a=@arg00 ;
326
prepare stmt1 from ' select a FROM t1 where a=? ' ;
327
execute stmt1 using @arg00 ;
331
execute stmt1 using @arg00 ;
334
select a FROM t1 where a=@arg00 ;
336
prepare stmt1 from ' select a FROM t1 where a=? ' ;
337
execute stmt1 using @arg00 ;
340
select a FROM t1 where a=sqrt(@arg00) ;
343
prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ;
344
execute stmt1 using @arg00 ;
348
select a FROM t1 where a=sqrt(@arg00) ;
350
prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ;
351
execute stmt1 using @arg00 ;
355
select a FROM t1 where a in (@arg00,@arg01) order by a;
359
prepare stmt1 from ' select a FROM t1 where a in (?,?) order by a ';
360
execute stmt1 using @arg00, @arg01;
367
prepare stmt1 from ' select b FROM t1 where b in (?,?,?) order by b ' ;
368
execute stmt1 using @arg00, @arg01, @arg02 ;
372
prepare stmt1 from ' select b FROM t1 where b like ? ';
374
execute stmt1 using @arg00 ;
378
execute stmt1 using @arg00 ;
382
execute stmt1 using @arg00 ;
386
insert into t9 set c1= 0, c5 = NULL ;
387
select c5 from t9 where c5 > NULL ;
389
prepare stmt1 from ' select c5 from t9 where c5 > ? ';
390
execute stmt1 using @arg00 ;
392
select c5 from t9 where c5 < NULL ;
394
prepare stmt1 from ' select c5 from t9 where c5 < ? ';
395
execute stmt1 using @arg00 ;
397
select c5 from t9 where c5 = NULL ;
399
prepare stmt1 from ' select c5 from t9 where c5 = ? ';
400
execute stmt1 using @arg00 ;
402
select c5 from t9 where c5 <=> NULL ;
405
prepare stmt1 from ' select c5 from t9 where c5 <=> ? ';
406
execute stmt1 using @arg00 ;
409
delete from t9 where c1= 0 ;
411
select a FROM t1 where a @arg00 1 ;
412
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@arg00 1' at line 1
413
prepare stmt1 from ' select a FROM t1 where a ? 1 ' ;
414
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? 1' at line 1
416
select a,b FROM t1 where a is not NULL
417
AND b is not NULL group by a - @arg00 ;
423
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
424
AND b is not NULL group by a - ? ' ;
425
execute stmt1 using @arg00 ;
432
select a,b FROM t1 where a is not NULL
433
AND b is not NULL having b <> @arg00 order by a ;
438
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
439
AND b is not NULL having b <> ? order by a ' ;
440
execute stmt1 using @arg00 ;
446
select a,b FROM t1 where a is not NULL
447
AND b is not NULL order by a - @arg00 ;
453
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
454
AND b is not NULL order by a - ? ' ;
455
execute stmt1 using @arg00 ;
462
select a,b from t1 order by 2 ;
468
prepare stmt1 from ' select a,b from t1
470
execute stmt1 using @arg00;
477
execute stmt1 using @arg00;
484
execute stmt1 using @arg00;
485
ERROR 42S22: Unknown column '?' in 'order clause'
487
prepare stmt1 from ' select a,b from t1 order by a
492
prepare stmt1 from ' select a,b from t1 order by a limit ? ';
493
execute stmt1 using @arg00;
500
select sum(a), @arg00 from t1 where a > @arg01
501
and b is not null group by substr(b,@arg02)
502
having sum(a) <> @arg03 ;
507
prepare stmt1 from ' select sum(a), ? from t1 where a > ?
508
and b is not null group by substr(b,?)
509
having sum(a) <> ? ';
510
execute stmt1 using @arg00, @arg01, @arg02, @arg03;
516
------ join tests ------
517
select first.a as a1, second.a as a2
518
from t1 first, t1 second
519
where first.a = second.a order by a1 ;
525
prepare stmt1 from ' select first.a as a1, second.a as a2
526
from t1 first, t1 second
527
where first.a = second.a order by a1 ';
537
select first.a, @arg00, second.a FROM t1 first, t1 second
538
where @arg01 = first.b or first.a = second.a or second.b = @arg02
539
order by second.a, first.a;
550
prepare stmt1 from ' select first.a, ?, second.a FROM t1 first, t1 second
551
where ? = first.b or first.a = second.a or second.b = ?
552
order by second.a, first.a';
553
execute stmt1 using @arg00, @arg01, @arg02;
564
drop table if exists t2 ;
565
create table t2 as select * from t1 ;
566
set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
567
set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
568
set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
569
set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
570
set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
571
set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
572
set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
573
set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
574
set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
575
the join statement is:
576
SELECT * FROM t2 right join t1 using(a) order by t2.a
577
prepare stmt1 from @query9 ;
596
the join statement is:
597
SELECT * FROM t2 natural right join t1 order by t2.a
598
prepare stmt1 from @query8 ;
617
the join statement is:
618
SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a
619
prepare stmt1 from @query7 ;
638
the join statement is:
639
SELECT * FROM t2 left join t1 using(a) order by t2.a
640
prepare stmt1 from @query6 ;
659
the join statement is:
660
SELECT * FROM t2 natural left join t1 order by t2.a
661
prepare stmt1 from @query5 ;
680
the join statement is:
681
SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a
682
prepare stmt1 from @query4 ;
701
the join statement is:
702
SELECT * FROM t2 join t1 using(a) order by t2.a
703
prepare stmt1 from @query3 ;
722
the join statement is:
723
SELECT * FROM t2 natural join t1 order by t2.a
724
prepare stmt1 from @query2 ;
743
the join statement is:
744
SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a
745
prepare stmt1 from @query1 ;
766
------ subquery tests ------
767
prepare stmt1 from ' select a, b FROM t1 outer_table where
768
a = (select a from t1 where b = ''two'') ';
773
select a, b FROM t1 outer_table where
774
a = (select a from t1 where b = 'two' ) and b=@arg00 ;
777
prepare stmt1 from ' select a, b FROM t1 outer_table where
778
a = (select a from t1 where b = ''two'') and b=? ';
779
execute stmt1 using @arg00;
783
select a, b FROM t1 outer_table where
784
a = (select a from t1 where b = @arg00 ) and b='two' ;
787
prepare stmt1 from ' select a, b FROM t1 outer_table where
788
a = (select a from t1 where b = ? ) and b=''two'' ' ;
789
execute stmt1 using @arg00;
794
select a,b FROM t1 where (a,b) in (select 3, 'three');
797
select a FROM t1 where (a,b) in (select @arg00,@arg01);
800
prepare stmt1 from ' select a FROM t1 where (a,b) in (select ?, ?) ';
801
execute stmt1 using @arg00, @arg01;
808
select a, @arg00, b FROM t1 outer_table where
809
b=@arg01 and a = (select @arg02 from t1 where b = @arg03 ) ;
812
prepare stmt1 from ' select a, ?, b FROM t1 outer_table where
813
b=? and a = (select ? from t1 where b = ? ) ' ;
814
execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
817
prepare stmt1 from 'select c4 FROM t9 where
818
c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ;
819
execute stmt1 using @arg01, @arg02;
821
prepare stmt1 from ' select a, b FROM t1 outer_table where
822
a = (select a from t1 where b = outer_table.b ) order by a ';
829
prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
830
(SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
834
deallocate prepare stmt1 ;
835
prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
836
(SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
840
deallocate prepare stmt1 ;
841
prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
842
(SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
846
deallocate prepare stmt1 ;
848
select a, b FROM t1 outer_table where
849
a = (select a from t1 where b = outer_table.b ) and b=@arg00 ;
852
prepare stmt1 from ' select a, b FROM t1 outer_table where
853
a = (select a from t1 where b = outer_table.b) and b=? ';
854
execute stmt1 using @arg00;
858
select a, b FROM t1 outer_table where
859
a = (select a from t1 where a = @arg00 and b = outer_table.b) and b='two' ;
862
prepare stmt1 from ' select a, b FROM t1 outer_table where
863
a = (select a from t1 where a = ? and b = outer_table.b) and b=''two'' ' ;
864
execute stmt1 using @arg00;
868
select a, b FROM t1 outer_table where
869
a = (select a from t1 where outer_table.a = @arg00 and a=2) and b='two' ;
872
prepare stmt1 from ' select a, b FROM t1 outer_table where
873
a = (select a from t1 where outer_table.a = ? and a=2) and b=''two'' ' ;
874
execute stmt1 using @arg00;
881
select a, @arg00, b FROM t1 outer_table where
882
b=@arg01 and a = (select @arg02 from t1 where outer_table.b = @arg03
883
and outer_table.a=a ) ;
886
prepare stmt1 from ' select a, ?, b FROM t1 outer_table where
887
b=? and a = (select ? from t1 where outer_table.b = ?
888
and outer_table.a=a ) ' ;
889
execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
895
from ( select a - @arg00 as a from t1 where a=@arg00 ) as t2
899
prepare stmt1 from ' select a, ?
900
from ( select a - ? as a from t1 where a=? ) as t2
902
execute stmt1 using @arg00, @arg00, @arg00, @arg01 ;
905
drop table if exists t2 ;
906
create table t2 as select * from t1;
907
prepare stmt1 from ' select a in (select a from t2) from t1 ' ;
909
a in (select a from t2)
914
drop table if exists t5, t6, t7 ;
915
create table t5 (a int , b int) ;
916
create table t6 like t5 ;
917
create table t7 like t5 ;
918
insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7),
920
insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1) ;
921
insert into t7 values (3, 3), (2, 2), (1, 1) ;
922
prepare stmt1 from ' select a, (select count(distinct t5.b) as sum from t5, t6
923
where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
924
group by t5.a order by sum limit 1) from t7 ' ;
926
a (select count(distinct t5.b) as sum from t5, t6
927
where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
928
group by t5.a order by sum limit 1)
933
a (select count(distinct t5.b) as sum from t5, t6
934
where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
935
group by t5.a order by sum limit 1)
940
a (select count(distinct t5.b) as sum from t5, t6
941
where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
942
group by t5.a order by sum limit 1)
946
drop table t5, t6, t7 ;
947
drop table if exists t2 ;
948
create table t2 as select * from t9;
950
(SELECT SUM(c1 + c12 + 0.0) FROM t2
951
where (t9.c2 - 0e-3) = t2.c2
952
GROUP BY t9.c15 LIMIT 1) as scalar_s,
953
exists (select 1.0e+0 from t2
954
where t2.c3 * 9.0000000000 = t9.c4) as exists_s,
955
c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s,
956
(c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s
958
(select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
959
prepare stmt1 from @stmt ;
962
set @stmt= concat('explain ',@stmt);
963
prepare stmt1 from @stmt ;
967
(SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2
968
GROUP BY t9.c15 LIMIT 1) as scalar_s,
969
exists (select ? from t2
970
where t2.c3*?=t9.c4) as exists_s,
971
c5*? in (select c6+? from t2) as in_s,
972
(c7-?, c8-?) in (select c9+?, c10+? from t2) as in_row_s
974
(select c25 x, c32 y from t2) tt WHERE x =c25 ' ;
978
set @arg03= 9.0000000000 ;
985
prepare stmt1 from @stmt ;
986
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
987
@arg07, @arg08, @arg09 ;
988
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
989
@arg07, @arg08, @arg09 ;
990
set @stmt= concat('explain ',@stmt);
991
prepare stmt1 from @stmt ;
992
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
993
@arg07, @arg08, @arg09 ;
994
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
995
@arg07, @arg08, @arg09 ;
997
select 1 < (select a from t1) ;
998
ERROR 21000: Subquery returns more than 1 row
999
prepare stmt1 from ' select 1 < (select a from t1) ' ;
1001
ERROR 21000: Subquery returns more than 1 row
1002
select 1 as my_col ;
1006
------ union tests ------
1007
prepare stmt1 from ' select a FROM t1 where a=1
1009
select a FROM t1 where a=1 ';
1016
prepare stmt1 from ' select a FROM t1 where a=1
1018
select a FROM t1 where a=1 ';
1023
prepare stmt1 from ' SELECT 1, 2 union SELECT 1 ' ;
1024
ERROR 21000: The used SELECT statements have a different number of columns
1025
prepare stmt1 from ' SELECT 1 union SELECT 1, 2 ' ;
1026
ERROR 21000: The used SELECT statements have a different number of columns
1027
prepare stmt1 from ' SELECT * from t1 union SELECT 1 ' ;
1028
ERROR 21000: The used SELECT statements have a different number of columns
1029
prepare stmt1 from ' SELECT 1 union SELECT * from t1 ' ;
1030
ERROR 21000: The used SELECT statements have a different number of columns
1032
select @arg00 FROM t1 where a=1
1034
select 1 FROM t1 where a=1;
1037
prepare stmt1 from ' select ? FROM t1 where a=1
1039
select 1 FROM t1 where a=1 ' ;
1040
execute stmt1 using @arg00;
1044
select 1 FROM t1 where a=1
1046
select @arg00 FROM t1 where a=1;
1049
prepare stmt1 from ' select 1 FROM t1 where a=1
1051
select ? FROM t1 where a=1 ' ;
1052
execute stmt1 using @arg00;
1056
select @arg00 FROM t1 where a=1
1058
select @arg00 FROM t1 where a=1;
1061
prepare stmt1 from ' select ? FROM t1 where a=1
1063
select ? FROM t1 where a=1 ';
1064
execute stmt1 using @arg00, @arg00;
1067
prepare stmt1 from ' select ?
1070
execute stmt1 using @arg00, @arg00;
1077
select @arg00 FROM t1 where a=@arg01
1079
select @arg02 FROM t1 where a=@arg03;
1082
prepare stmt1 from ' select ? FROM t1 where a=?
1084
select ? FROM t1 where a=? ' ;
1085
execute stmt1 using @arg00, @arg01, @arg02, @arg03;
1089
prepare stmt1 from ' select sum(a) + 200, ? from t1
1091
select sum(a) + 200, 1 from t1
1093
execute stmt1 using @arg00;
1100
set @Oporto='Oporto' ;
1101
set @Lisboa='Lisboa' ;
1107
select @Oporto,@Lisboa,@0,@1,@2,@3,@4 ;
1108
@Oporto @Lisboa @0 @1 @2 @3 @4
1109
Oporto Lisboa 0 1 2 3 4
1110
select sum(a) + 200 as the_sum, @Oporto as the_town from t1
1113
select sum(a) + 200, @Lisboa from t1
1124
prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
1127
select sum(a) + 200, ? from t1
1129
execute stmt1 using @Oporto, @Lisboa;
1139
select sum(a) + 200 as the_sum, @Oporto as the_town from t1
1143
select sum(a) + 200, @Lisboa from t1
1152
prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
1156
select sum(a) + 200, ? from t1
1159
execute stmt1 using @Oporto, @1, @Lisboa, @2;
1166
select sum(a) + 200 as the_sum, @Oporto as the_town from t1
1171
select sum(a) + 200, @Lisboa from t1
1179
prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
1184
select sum(a) + 200, ? from t1
1187
having avg(a) > ? ';
1188
execute stmt1 using @Oporto, @1, @2, @Lisboa, @2, @3;
1194
------ explain select tests ------
1195
prepare stmt1 from ' explain select * from t9 ' ;
1197
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1198
def id 8 3 1 N 32929 0 63
1199
def select_type 253 19 6 N 1 31 8
1200
def table 253 64 2 Y 0 31 8
1201
def type 253 10 3 Y 0 31 8
1202
def possible_keys 253 4096 0 Y 0 31 8
1203
def key 253 64 0 Y 0 31 8
1204
def key_len 253 4096 0 Y 0 31 8
1205
def ref 253 1024 0 Y 0 31 8
1206
def rows 8 10 1 Y 32928 0 63
1207
def Extra 253 255 0 N 1 31 8
1208
id select_type table type possible_keys key key_len ref rows Extra
1209
1 SIMPLE t9 ALL NULL NULL NULL NULL 2
1211
------ delete tests ------
1213
insert into t1 values (1,'one');
1214
insert into t1 values (2,'two');
1215
insert into t1 values (3,'three');
1216
insert into t1 values (4,'four');
1220
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1221
c10= 1, c11= 1, c12 = 1,
1222
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1223
c16= '11:11:11', c17= '2004',
1224
c18= 1, c19=true, c20= 'a', c21= '123456789a',
1225
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1226
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1227
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1229
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1230
c10= 9, c11= 9, c12 = 9,
1231
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1232
c16= '11:11:11', c17= '2004',
1233
c18= 1, c19=false, c20= 'a', c21= '123456789a',
1234
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1235
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1236
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1238
prepare stmt1 from 'delete from t1 where a=2' ;
1240
select a,b from t1 where a=2;
1243
insert into t1 values(0,NULL);
1245
prepare stmt1 from 'delete from t1 where b=?' ;
1246
execute stmt1 using @arg00;
1247
select a,b from t1 where b is NULL ;
1251
execute stmt1 using @arg00;
1252
select a,b from t1 where b=@arg00;
1254
prepare stmt1 from 'truncate table t1' ;
1256
------ update tests ------
1258
insert into t1 values (1,'one');
1259
insert into t1 values (2,'two');
1260
insert into t1 values (3,'three');
1261
insert into t1 values (4,'four');
1265
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1266
c10= 1, c11= 1, c12 = 1,
1267
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1268
c16= '11:11:11', c17= '2004',
1269
c18= 1, c19=true, c20= 'a', c21= '123456789a',
1270
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1271
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1272
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1274
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1275
c10= 9, c11= 9, c12 = 9,
1276
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1277
c16= '11:11:11', c17= '2004',
1278
c18= 1, c19=false, c20= 'a', c21= '123456789a',
1279
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1280
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1281
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1283
prepare stmt1 from 'update t1 set b=''a=two'' where a=2' ;
1285
select a,b from t1 where a=2;
1289
select a,b from t1 where a=2;
1293
prepare stmt1 from 'update t1 set b=? where a=2' ;
1294
execute stmt1 using @arg00;
1295
select a,b from t1 where a=2;
1299
execute stmt1 using @arg00;
1300
select a,b from t1 where a=2;
1304
prepare stmt1 from 'update t1 set b=NULL where a=?' ;
1305
execute stmt1 using @arg00;
1306
select a,b from t1 where a=@arg00;
1309
update t1 set b='two' where a=@arg00;
1311
execute stmt1 using @arg00;
1312
select a,b from t1 where a=@arg00;
1316
prepare stmt1 from 'update t1 set a=? where a=?' ;
1317
execute stmt1 using @arg00, @arg00;
1318
select a,b from t1 where a=@arg00;
1321
execute stmt1 using @arg01, @arg00;
1322
select a,b from t1 where a=@arg01;
1325
execute stmt1 using @arg00, @arg01;
1326
select a,b from t1 where a=@arg00;
1331
execute stmt1 using @arg00, @arg01;
1333
Warning 1048 Column 'a' cannot be null
1334
select a,b from t1 order by a;
1341
execute stmt1 using @arg01, @arg00;
1342
select a,b from t1 order by a;
1353
drop table if exists t2;
1354
create table t2 as select a,b from t1 ;
1355
prepare stmt1 from 'update t1 set a=? where b=?
1356
and a in (select ? from t2
1357
where b = ? or a = ?)';
1358
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;
1360
info: Rows matched: 1 Changed: 1 Warnings: 0
1361
select a,b from t1 where a = @arg00 ;
1364
prepare stmt1 from 'update t1 set a=? where b=?
1365
and a not in (select ? from t2
1366
where b = ? or a = ?)';
1367
execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;
1369
info: Rows matched: 1 Changed: 1 Warnings: 0
1370
select a,b from t1 order by a ;
1379
a int, b varchar(30),
1381
) engine = 'MYISAM' ;
1382
insert into t2(a,b) select a, b from t1 ;
1383
prepare stmt1 from 'update t1 set a=? where b=?
1384
and a in (select ? from t2
1385
where b = ? or a = ?)';
1386
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;
1388
info: Rows matched: 1 Changed: 1 Warnings: 0
1389
select a,b from t1 where a = @arg00 ;
1392
prepare stmt1 from 'update t1 set a=? where b=?
1393
and a not in (select ? from t2
1394
where b = ? or a = ?)';
1395
execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;
1397
info: Rows matched: 1 Changed: 1 Warnings: 0
1398
select a,b from t1 order by a ;
1406
prepare stmt1 from 'update t1 set b=''bla''
1410
select a,b from t1 where b = 'bla' ;
1413
prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';
1414
execute stmt1 using @arg00;
1416
------ insert tests ------
1418
insert into t1 values (1,'one');
1419
insert into t1 values (2,'two');
1420
insert into t1 values (3,'three');
1421
insert into t1 values (4,'four');
1425
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1426
c10= 1, c11= 1, c12 = 1,
1427
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1428
c16= '11:11:11', c17= '2004',
1429
c18= 1, c19=true, c20= 'a', c21= '123456789a',
1430
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1431
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1432
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1434
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1435
c10= 9, c11= 9, c12 = 9,
1436
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1437
c16= '11:11:11', c17= '2004',
1438
c18= 1, c19=false, c20= 'a', c21= '123456789a',
1439
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1440
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1441
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1443
prepare stmt1 from 'insert into t1 values(5, ''five'' )';
1445
select a,b from t1 where a = 5;
1449
prepare stmt1 from 'insert into t1 values(6, ? )';
1450
execute stmt1 using @arg00;
1451
select a,b from t1 where b = @arg00;
1454
execute stmt1 using @arg00;
1455
ERROR 23000: Duplicate entry '6' for key 'PRIMARY'
1457
prepare stmt1 from 'insert into t1 values(0, ? )';
1458
execute stmt1 using @arg00;
1459
select a,b from t1 where b is NULL;
1463
set @arg01='eight' ;
1464
prepare stmt1 from 'insert into t1 values(?, ? )';
1465
execute stmt1 using @arg00, @arg01 ;
1466
select a,b from t1 where b = @arg01;
1471
execute stmt1 using @NULL, @NULL ;
1472
ERROR 23000: Column 'a' cannot be null
1473
execute stmt1 using @NULL, @NULL ;
1474
ERROR 23000: Column 'a' cannot be null
1475
execute stmt1 using @NULL, @arg00 ;
1476
ERROR 23000: Column 'a' cannot be null
1477
execute stmt1 using @NULL, @arg00 ;
1478
ERROR 23000: Column 'a' cannot be null
1479
set @arg01= 10000 + 2 ;
1480
execute stmt1 using @arg01, @arg00 ;
1481
set @arg01= 10000 + 1 ;
1482
execute stmt1 using @arg01, @arg00 ;
1483
select * from t1 where a > 10000 order by a ;
1487
delete from t1 where a > 10000 ;
1488
set @arg01= 10000 + 2 ;
1489
execute stmt1 using @arg01, @NULL ;
1490
set @arg01= 10000 + 1 ;
1491
execute stmt1 using @arg01, @NULL ;
1492
select * from t1 where a > 10000 order by a ;
1496
delete from t1 where a > 10000 ;
1497
set @arg01= 10000 + 10 ;
1498
execute stmt1 using @arg01, @arg01 ;
1499
set @arg01= 10000 + 9 ;
1500
execute stmt1 using @arg01, @arg01 ;
1501
set @arg01= 10000 + 8 ;
1502
execute stmt1 using @arg01, @arg01 ;
1503
set @arg01= 10000 + 7 ;
1504
execute stmt1 using @arg01, @arg01 ;
1505
set @arg01= 10000 + 6 ;
1506
execute stmt1 using @arg01, @arg01 ;
1507
set @arg01= 10000 + 5 ;
1508
execute stmt1 using @arg01, @arg01 ;
1509
set @arg01= 10000 + 4 ;
1510
execute stmt1 using @arg01, @arg01 ;
1511
set @arg01= 10000 + 3 ;
1512
execute stmt1 using @arg01, @arg01 ;
1513
set @arg01= 10000 + 2 ;
1514
execute stmt1 using @arg01, @arg01 ;
1515
set @arg01= 10000 + 1 ;
1516
execute stmt1 using @arg01, @arg01 ;
1517
select * from t1 where a > 10000 order by a ;
1529
delete from t1 where a > 10000 ;
1534
prepare stmt1 from 'insert into t1 values(?,?),(?,?)';
1535
execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
1536
select a,b from t1 where a in (@arg00,@arg02) ;
1542
prepare stmt1 from 'insert into t1 set a=?, b=? ';
1543
execute stmt1 using @arg00, @arg01 ;
1544
select a,b from t1 where a = @arg00 ;
1549
prepare stmt1 from 'insert into t1 set a=?, b=''sechs''
1550
on duplicate key update a=a + ?, b=concat(b,''modified'') ';
1551
execute stmt1 using @arg00, @arg01;
1552
select * from t1 order by a;
1567
execute stmt1 using @arg00, @arg01;
1568
ERROR 23000: Duplicate entry '82' for key 'PRIMARY'
1569
drop table if exists t2 ;
1570
create table t2 (id int auto_increment primary key)
1572
prepare stmt1 from ' select last_insert_id() ' ;
1573
insert into t2 values (NULL) ;
1577
insert into t2 values (NULL) ;
1583
set @x1000_2="x1000_2" ;
1584
set @x1000_3="x1000_3" ;
1585
set @x1000="x1000" ;
1587
set @x1100="x1100" ;
1589
set @updated="updated" ;
1590
insert into t1 values(1000,'x1000_1') ;
1591
insert into t1 values(@1000,@x1000_2),(@1000,@x1000_3)
1592
on duplicate key update a = a + @100, b = concat(b,@updated) ;
1593
select a,b from t1 where a >= 1000 order by a ;
1597
delete from t1 where a >= 1000 ;
1598
insert into t1 values(1000,'x1000_1') ;
1599
prepare stmt1 from ' insert into t1 values(?,?),(?,?)
1600
on duplicate key update a = a + ?, b = concat(b,?) ';
1601
execute stmt1 using @1000, @x1000_2, @1000, @x1000_3, @100, @updated ;
1602
select a,b from t1 where a >= 1000 order by a ;
1606
delete from t1 where a >= 1000 ;
1607
insert into t1 values(1000,'x1000_1') ;
1608
execute stmt1 using @1000, @x1000_2, @1100, @x1000_3, @100, @updated ;
1609
select a,b from t1 where a >= 1000 order by a ;
1611
1200 x1000_1updatedupdated
1612
delete from t1 where a >= 1000 ;
1613
prepare stmt1 from ' replace into t1 (a,b) select 100, ''hundred'' ';
1618
------ multi table tests ------
1621
insert into t1(a,b) values (1, 'one'), (2, 'two'), (3, 'three') ;
1622
insert into t9 (c1,c21)
1623
values (1, 'one'), (2, 'two'), (3, 'three') ;
1624
prepare stmt_delete from " delete t1, t9
1625
from t1, t9 where t1.a=t9.c1 and t1.b='updated' ";
1626
prepare stmt_update from " update t1, t9
1627
set t1.b='updated', t9.c21='updated'
1628
where t1.a=t9.c1 and t1.a=? ";
1629
prepare stmt_select1 from " select a, b from t1 order by a" ;
1630
prepare stmt_select2 from " select c1, c21 from t9 order by c1" ;
1632
execute stmt_update using @arg00 ;
1633
execute stmt_delete ;
1634
execute stmt_select1 ;
1638
execute stmt_select2 ;
1642
set @arg00= @arg00 + 1 ;
1643
execute stmt_update using @arg00 ;
1644
execute stmt_delete ;
1645
execute stmt_select1 ;
1648
execute stmt_select2 ;
1651
set @arg00= @arg00 + 1 ;
1652
execute stmt_update using @arg00 ;
1653
execute stmt_delete ;
1654
execute stmt_select1 ;
1656
execute stmt_select2 ;
1658
set @arg00= @arg00 + 1 ;
1659
drop table if exists t5 ;
1662
set @arg03= 80.00000000000e-1;
1664
set @arg05= CAST('abc' as binary) ;
1665
set @arg06= '1991-08-05' ;
1666
set @arg07= CAST('1991-08-05' as date);
1667
set @arg08= '1991-08-05 01:01:01' ;
1668
set @arg09= CAST('1991-08-05 01:01:01' as datetime) ;
1669
set @arg10= unix_timestamp('1991-01-01 01:01:01');
1670
set @arg11= YEAR('1991-01-01 01:01:01');
1677
set @arg15= CAST('abc' as binary) ;
1679
create table t5 as select
1680
8 as const01, @arg01 as param01,
1681
8.0 as const02, @arg02 as param02,
1682
80.00000000000e-1 as const03, @arg03 as param03,
1683
'abc' as const04, @arg04 as param04,
1684
CAST('abc' as binary) as const05, @arg05 as param05,
1685
'1991-08-05' as const06, @arg06 as param06,
1686
CAST('1991-08-05' as date) as const07, @arg07 as param07,
1687
'1991-08-05 01:01:01' as const08, @arg08 as param08,
1688
CAST('1991-08-05 01:01:01' as datetime) as const09, @arg09 as param09,
1689
unix_timestamp('1991-01-01 01:01:01') as const10, @arg10 as param10,
1690
YEAR('1991-01-01 01:01:01') as const11, @arg11 as param11,
1691
NULL as const12, @arg12 as param12,
1695
show create table t5 ;
1697
t5 CREATE TABLE `t5` (
1698
`const01` int(1) NOT NULL DEFAULT '0',
1699
`param01` bigint(20) DEFAULT NULL,
1700
`const02` decimal(2,1) NOT NULL DEFAULT '0.0',
1701
`param02` decimal(65,30) DEFAULT NULL,
1702
`const03` double NOT NULL DEFAULT '0',
1703
`param03` double DEFAULT NULL,
1704
`const04` varchar(3) NOT NULL DEFAULT '',
1706
`const05` varbinary(3) NOT NULL DEFAULT '',
1708
`const06` varchar(10) NOT NULL DEFAULT '',
1710
`const07` date DEFAULT NULL,
1712
`const08` varchar(19) NOT NULL DEFAULT '',
1714
`const09` datetime DEFAULT NULL,
1716
`const10` int(10) NOT NULL DEFAULT '0',
1717
`param10` bigint(20) DEFAULT NULL,
1718
`const11` int(4) DEFAULT NULL,
1719
`param11` bigint(20) DEFAULT NULL,
1720
`const12` binary(0) DEFAULT NULL,
1721
`param12` bigint(20) DEFAULT NULL,
1722
`param13` decimal(65,30) DEFAULT NULL,
1725
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1727
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1728
def test t5 t5 const01 const01 3 1 1 N 32769 0 63
1729
def test t5 t5 param01 param01 8 20 1 Y 32768 0 63
1730
def test t5 t5 const02 const02 246 4 3 N 1 1 63
1731
def test t5 t5 param02 param02 246 67 32 Y 0 30 63
1732
def test t5 t5 const03 const03 5 17 1 N 32769 31 63
1733
def test t5 t5 param03 param03 5 23 1 Y 32768 31 63
1734
def test t5 t5 const04 const04 253 3 3 N 1 0 8
1735
def test t5 t5 param04 param04 252 4294967295 3 Y 16 0 8
1736
def test t5 t5 const05 const05 253 3 3 N 129 0 63
1737
def test t5 t5 param05 param05 252 4294967295 3 Y 144 0 63
1738
def test t5 t5 const06 const06 253 10 10 N 1 0 8
1739
def test t5 t5 param06 param06 252 4294967295 10 Y 16 0 8
1740
def test t5 t5 const07 const07 10 10 10 Y 128 0 63
1741
def test t5 t5 param07 param07 252 4294967295 10 Y 144 0 63
1742
def test t5 t5 const08 const08 253 19 19 N 1 0 8
1743
def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8
1744
def test t5 t5 const09 const09 12 19 19 Y 128 0 63
1745
def test t5 t5 param09 param09 252 4294967295 19 Y 144 0 63
1746
def test t5 t5 const10 const10 3 10 9 N 32769 0 63
1747
def test t5 t5 param10 param10 8 20 9 Y 32768 0 63
1748
def test t5 t5 const11 const11 3 4 4 Y 32768 0 63
1749
def test t5 t5 param11 param11 8 20 4 Y 32768 0 63
1750
def test t5 t5 const12 const12 254 0 0 Y 128 0 63
1751
def test t5 t5 param12 param12 8 20 0 Y 32768 0 63
1752
def test t5 t5 param13 param13 246 67 0 Y 0 30 63
1753
def test t5 t5 param14 param14 252 4294967295 0 Y 16 0 8
1754
def test t5 t5 param15 param15 252 4294967295 0 Y 144 0 63
1758
param02 8.000000000000000000000000000000
1769
const08 1991-08-05 01:01:01
1770
param08 1991-08-05 01:01:01
1771
const09 1991-08-05 01:01:01
1772
param09 1991-08-05 01:01:01
1784
------ data type conversion tests ------
1786
insert into t1 values (1,'one');
1787
insert into t1 values (2,'two');
1788
insert into t1 values (3,'three');
1789
insert into t1 values (4,'four');
1793
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1794
c10= 1, c11= 1, c12 = 1,
1795
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1796
c16= '11:11:11', c17= '2004',
1797
c18= 1, c19=true, c20= 'a', c21= '123456789a',
1798
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1799
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1800
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1802
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1803
c10= 9, c11= 9, c12 = 9,
1804
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1805
c16= '11:11:11', c17= '2004',
1806
c18= 1, c19=false, c20= 'a', c21= '123456789a',
1807
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1808
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1809
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1811
insert into t9 set c1= 0, c15= '1991-01-01 01:01:01' ;
1812
select * from t9 order by c1 ;
1813
c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32
1814
0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1815
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
1816
9 9 9 9 9 9 9 9 9 9 9.0000 9.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 0 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext two tuesday
1818
------ select @parameter:= column ------
1819
prepare full_info from "select @arg01, @arg02, @arg03, @arg04,
1820
@arg05, @arg06, @arg07, @arg08,
1821
@arg09, @arg10, @arg11, @arg12,
1822
@arg13, @arg14, @arg15, @arg16,
1823
@arg17, @arg18, @arg19, @arg20,
1824
@arg21, @arg22, @arg23, @arg24,
1825
@arg25, @arg26, @arg27, @arg28,
1826
@arg29, @arg30, @arg31, @arg32" ;
1827
select @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,
1828
@arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,
1829
@arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
1830
@arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
1831
@arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
1832
@arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
1833
@arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
1834
@arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
1835
from t9 where c1= 1 ;
1836
@arg01:= c1 @arg02:= c2 @arg03:= c3 @arg04:= c4 @arg05:= c5 @arg06:= c6 @arg07:= c7 @arg08:= c8 @arg09:= c9 @arg10:= c10 @arg11:= c11 @arg12:= c12 @arg13:= c13 @arg14:= c14 @arg15:= c15 @arg16:= c16 @arg17:= c17 @arg18:= c18 @arg19:= c19 @arg20:= c20 @arg21:= c21 @arg22:= c22 @arg23:= c23 @arg24:= c24 @arg25:= c25 @arg26:= c26 @arg27:= c27 @arg28:= c28 @arg29:= c29 @arg30:= c30 @arg31:= c31 @arg32:= c32
1837
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
1839
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1840
def @arg01 8 20 1 Y 32896 0 63
1841
def @arg02 8 20 1 Y 32896 0 63
1842
def @arg03 8 20 1 Y 32896 0 63
1843
def @arg04 8 20 1 Y 32896 0 63
1844
def @arg05 8 20 1 Y 32896 0 63
1845
def @arg06 8 20 1 Y 32896 0 63
1846
def @arg07 5 23 1 Y 32896 31 63
1847
def @arg08 5 23 1 Y 32896 31 63
1848
def @arg09 5 23 1 Y 32896 31 63
1849
def @arg10 5 23 1 Y 32896 31 63
1850
def @arg11 246 83 6 Y 128 30 63
1851
def @arg12 246 83 6 Y 128 30 63
1852
def @arg13 251 16777216 10 Y 128 31 63
1853
def @arg14 251 16777216 19 Y 128 31 63
1854
def @arg15 251 16777216 19 Y 128 31 63
1855
def @arg16 251 16777216 8 Y 128 31 63
1856
def @arg17 8 20 4 Y 32928 0 63
1857
def @arg18 8 20 1 Y 32896 0 63
1858
def @arg19 8 20 1 Y 32896 0 63
1859
def @arg20 251 16777216 1 Y 0 31 8
1860
def @arg21 251 16777216 10 Y 0 31 8
1861
def @arg22 251 16777216 30 Y 0 31 8
1862
def @arg23 251 16777216 8 Y 128 31 63
1863
def @arg24 251 16777216 8 Y 0 31 8
1864
def @arg25 251 16777216 4 Y 128 31 63
1865
def @arg26 251 16777216 4 Y 0 31 8
1866
def @arg27 251 16777216 10 Y 128 31 63
1867
def @arg28 251 16777216 10 Y 0 31 8
1868
def @arg29 251 16777216 8 Y 128 31 63
1869
def @arg30 251 16777216 8 Y 0 31 8
1870
def @arg31 251 16777216 3 Y 0 31 8
1871
def @arg32 251 16777216 6 Y 0 31 8
1872
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
1873
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
1874
select @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,
1875
@arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,
1876
@arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
1877
@arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
1878
@arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
1879
@arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
1880
@arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
1881
@arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
1882
from t9 where c1= 0 ;
1883
@arg01:= c1 @arg02:= c2 @arg03:= c3 @arg04:= c4 @arg05:= c5 @arg06:= c6 @arg07:= c7 @arg08:= c8 @arg09:= c9 @arg10:= c10 @arg11:= c11 @arg12:= c12 @arg13:= c13 @arg14:= c14 @arg15:= c15 @arg16:= c16 @arg17:= c17 @arg18:= c18 @arg19:= c19 @arg20:= c20 @arg21:= c21 @arg22:= c22 @arg23:= c23 @arg24:= c24 @arg25:= c25 @arg26:= c26 @arg27:= c27 @arg28:= c28 @arg29:= c29 @arg30:= c30 @arg31:= c31 @arg32:= c32
1884
0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1886
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1887
def @arg01 8 20 1 Y 32896 0 63
1888
def @arg02 8 20 0 Y 32896 0 63
1889
def @arg03 8 20 0 Y 32896 0 63
1890
def @arg04 8 20 0 Y 32896 0 63
1891
def @arg05 8 20 0 Y 32896 0 63
1892
def @arg06 8 20 0 Y 32896 0 63
1893
def @arg07 5 23 0 Y 32896 31 63
1894
def @arg08 5 23 0 Y 32896 31 63
1895
def @arg09 5 23 0 Y 32896 31 63
1896
def @arg10 5 23 0 Y 32896 31 63
1897
def @arg11 246 83 0 Y 128 30 63
1898
def @arg12 246 83 0 Y 128 30 63
1899
def @arg13 251 16777216 0 Y 128 31 63
1900
def @arg14 251 16777216 0 Y 128 31 63
1901
def @arg15 251 16777216 19 Y 128 31 63
1902
def @arg16 251 16777216 0 Y 128 31 63
1903
def @arg17 8 20 0 Y 32928 0 63
1904
def @arg18 8 20 0 Y 32896 0 63
1905
def @arg19 8 20 0 Y 32896 0 63
1906
def @arg20 251 16777216 0 Y 0 31 8
1907
def @arg21 251 16777216 0 Y 0 31 8
1908
def @arg22 251 16777216 0 Y 0 31 8
1909
def @arg23 251 16777216 0 Y 128 31 63
1910
def @arg24 251 16777216 0 Y 0 31 8
1911
def @arg25 251 16777216 0 Y 128 31 63
1912
def @arg26 251 16777216 0 Y 0 31 8
1913
def @arg27 251 16777216 0 Y 128 31 63
1914
def @arg28 251 16777216 0 Y 0 31 8
1915
def @arg29 251 16777216 0 Y 128 31 63
1916
def @arg30 251 16777216 0 Y 0 31 8
1917
def @arg31 251 16777216 0 Y 0 31 8
1918
def @arg32 251 16777216 0 Y 0 31 8
1919
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
1920
0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1921
prepare stmt1 from "select
1922
@arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,
1923
@arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,
1924
@arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
1925
@arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
1926
@arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
1927
@arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
1928
@arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
1929
@arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
1930
from t9 where c1= ?" ;
1932
execute stmt1 using @my_key ;
1933
@arg01:= c1 @arg02:= c2 @arg03:= c3 @arg04:= c4 @arg05:= c5 @arg06:= c6 @arg07:= c7 @arg08:= c8 @arg09:= c9 @arg10:= c10 @arg11:= c11 @arg12:= c12 @arg13:= c13 @arg14:= c14 @arg15:= c15 @arg16:= c16 @arg17:= c17 @arg18:= c18 @arg19:= c19 @arg20:= c20 @arg21:= c21 @arg22:= c22 @arg23:= c23 @arg24:= c24 @arg25:= c25 @arg26:= c26 @arg27:= c27 @arg28:= c28 @arg29:= c29 @arg30:= c30 @arg31:= c31 @arg32:= c32
1934
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
1936
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1937
def @arg01 8 20 1 Y 32896 0 63
1938
def @arg02 8 20 1 Y 32896 0 63
1939
def @arg03 8 20 1 Y 32896 0 63
1940
def @arg04 8 20 1 Y 32896 0 63
1941
def @arg05 8 20 1 Y 32896 0 63
1942
def @arg06 8 20 1 Y 32896 0 63
1943
def @arg07 5 23 1 Y 32896 31 63
1944
def @arg08 5 23 1 Y 32896 31 63
1945
def @arg09 5 23 1 Y 32896 31 63
1946
def @arg10 5 23 1 Y 32896 31 63
1947
def @arg11 246 83 6 Y 128 30 63
1948
def @arg12 246 83 6 Y 128 30 63
1949
def @arg13 251 16777216 10 Y 128 31 63
1950
def @arg14 251 16777216 19 Y 128 31 63
1951
def @arg15 251 16777216 19 Y 128 31 63
1952
def @arg16 251 16777216 8 Y 128 31 63
1953
def @arg17 8 20 4 Y 32928 0 63
1954
def @arg18 8 20 1 Y 32896 0 63
1955
def @arg19 8 20 1 Y 32896 0 63
1956
def @arg20 251 16777216 1 Y 0 31 8
1957
def @arg21 251 16777216 10 Y 0 31 8
1958
def @arg22 251 16777216 30 Y 0 31 8
1959
def @arg23 251 16777216 8 Y 128 31 63
1960
def @arg24 251 16777216 8 Y 0 31 8
1961
def @arg25 251 16777216 4 Y 128 31 63
1962
def @arg26 251 16777216 4 Y 0 31 8
1963
def @arg27 251 16777216 10 Y 128 31 63
1964
def @arg28 251 16777216 10 Y 0 31 8
1965
def @arg29 251 16777216 8 Y 128 31 63
1966
def @arg30 251 16777216 8 Y 0 31 8
1967
def @arg31 251 16777216 3 Y 0 31 8
1968
def @arg32 251 16777216 6 Y 0 31 8
1969
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
1970
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
1972
execute stmt1 using @my_key ;
1973
@arg01:= c1 @arg02:= c2 @arg03:= c3 @arg04:= c4 @arg05:= c5 @arg06:= c6 @arg07:= c7 @arg08:= c8 @arg09:= c9 @arg10:= c10 @arg11:= c11 @arg12:= c12 @arg13:= c13 @arg14:= c14 @arg15:= c15 @arg16:= c16 @arg17:= c17 @arg18:= c18 @arg19:= c19 @arg20:= c20 @arg21:= c21 @arg22:= c22 @arg23:= c23 @arg24:= c24 @arg25:= c25 @arg26:= c26 @arg27:= c27 @arg28:= c28 @arg29:= c29 @arg30:= c30 @arg31:= c31 @arg32:= c32
1974
0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1976
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1977
def @arg01 8 20 1 Y 32896 0 63
1978
def @arg02 8 20 0 Y 32896 0 63
1979
def @arg03 8 20 0 Y 32896 0 63
1980
def @arg04 8 20 0 Y 32896 0 63
1981
def @arg05 8 20 0 Y 32896 0 63
1982
def @arg06 8 20 0 Y 32896 0 63
1983
def @arg07 5 23 0 Y 32896 31 63
1984
def @arg08 5 23 0 Y 32896 31 63
1985
def @arg09 5 23 0 Y 32896 31 63
1986
def @arg10 5 23 0 Y 32896 31 63
1987
def @arg11 246 83 0 Y 128 30 63
1988
def @arg12 246 83 0 Y 128 30 63
1989
def @arg13 251 16777216 0 Y 128 31 63
1990
def @arg14 251 16777216 0 Y 128 31 63
1991
def @arg15 251 16777216 19 Y 128 31 63
1992
def @arg16 251 16777216 0 Y 128 31 63
1993
def @arg17 8 20 0 Y 32928 0 63
1994
def @arg18 8 20 0 Y 32896 0 63
1995
def @arg19 8 20 0 Y 32896 0 63
1996
def @arg20 251 16777216 0 Y 0 31 8
1997
def @arg21 251 16777216 0 Y 0 31 8
1998
def @arg22 251 16777216 0 Y 0 31 8
1999
def @arg23 251 16777216 0 Y 128 31 63
2000
def @arg24 251 16777216 0 Y 0 31 8
2001
def @arg25 251 16777216 0 Y 128 31 63
2002
def @arg26 251 16777216 0 Y 0 31 8
2003
def @arg27 251 16777216 0 Y 128 31 63
2004
def @arg28 251 16777216 0 Y 0 31 8
2005
def @arg29 251 16777216 0 Y 128 31 63
2006
def @arg30 251 16777216 0 Y 0 31 8
2007
def @arg31 251 16777216 0 Y 0 31 8
2008
def @arg32 251 16777216 0 Y 0 31 8
2009
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
2010
0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2011
prepare stmt1 from "select ? := c1 from t9 where c1= 1" ;
2012
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= c1 from t9 where c1= 1' at line 1
2014
------ select column, .. into @parm,.. ------
2015
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
2016
c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
2017
c25, c26, c27, c28, c29, c30, c31, c32
2018
into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
2019
@arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
2020
@arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
2021
@arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
2022
from t9 where c1= 1 ;
2024
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2025
def @arg01 8 20 1 Y 32896 0 63
2026
def @arg02 8 20 1 Y 32896 0 63
2027
def @arg03 8 20 1 Y 32896 0 63
2028
def @arg04 8 20 1 Y 32896 0 63
2029
def @arg05 8 20 1 Y 32896 0 63
2030
def @arg06 8 20 1 Y 32896 0 63
2031
def @arg07 5 23 1 Y 32896 31 63
2032
def @arg08 5 23 1 Y 32896 31 63
2033
def @arg09 5 23 1 Y 32896 31 63
2034
def @arg10 5 23 1 Y 32896 31 63
2035
def @arg11 246 83 6 Y 128 30 63
2036
def @arg12 246 83 6 Y 128 30 63
2037
def @arg13 251 16777216 10 Y 128 31 63
2038
def @arg14 251 16777216 19 Y 128 31 63
2039
def @arg15 251 16777216 19 Y 128 31 63
2040
def @arg16 251 16777216 8 Y 128 31 63
2041
def @arg17 8 20 4 Y 32928 0 63
2042
def @arg18 8 20 1 Y 32896 0 63
2043
def @arg19 8 20 1 Y 32896 0 63
2044
def @arg20 251 16777216 1 Y 0 31 8
2045
def @arg21 251 16777216 10 Y 0 31 8
2046
def @arg22 251 16777216 30 Y 0 31 8
2047
def @arg23 251 16777216 8 Y 128 31 63
2048
def @arg24 251 16777216 8 Y 0 31 8
2049
def @arg25 251 16777216 4 Y 128 31 63
2050
def @arg26 251 16777216 4 Y 0 31 8
2051
def @arg27 251 16777216 10 Y 128 31 63
2052
def @arg28 251 16777216 10 Y 0 31 8
2053
def @arg29 251 16777216 8 Y 128 31 63
2054
def @arg30 251 16777216 8 Y 0 31 8
2055
def @arg31 251 16777216 3 Y 0 31 8
2056
def @arg32 251 16777216 6 Y 0 31 8
2057
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
2058
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
2059
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
2060
c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
2061
c25, c26, c27, c28, c29, c30, c31, c32
2062
into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
2063
@arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
2064
@arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
2065
@arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
2066
from t9 where c1= 0 ;
2068
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2069
def @arg01 8 20 1 Y 32896 0 63
2070
def @arg02 8 20 0 Y 32896 0 63
2071
def @arg03 8 20 0 Y 32896 0 63
2072
def @arg04 8 20 0 Y 32896 0 63
2073
def @arg05 8 20 0 Y 32896 0 63
2074
def @arg06 8 20 0 Y 32896 0 63
2075
def @arg07 5 23 0 Y 32896 31 63
2076
def @arg08 5 23 0 Y 32896 31 63
2077
def @arg09 5 23 0 Y 32896 31 63
2078
def @arg10 5 23 0 Y 32896 31 63
2079
def @arg11 246 83 0 Y 128 30 63
2080
def @arg12 246 83 0 Y 128 30 63
2081
def @arg13 251 16777216 0 Y 128 31 63
2082
def @arg14 251 16777216 0 Y 128 31 63
2083
def @arg15 251 16777216 19 Y 128 31 63
2084
def @arg16 251 16777216 0 Y 128 31 63
2085
def @arg17 8 20 0 Y 32928 0 63
2086
def @arg18 8 20 0 Y 32896 0 63
2087
def @arg19 8 20 0 Y 32896 0 63
2088
def @arg20 251 16777216 0 Y 0 31 8
2089
def @arg21 251 16777216 0 Y 0 31 8
2090
def @arg22 251 16777216 0 Y 0 31 8
2091
def @arg23 251 16777216 0 Y 128 31 63
2092
def @arg24 251 16777216 0 Y 0 31 8
2093
def @arg25 251 16777216 0 Y 128 31 63
2094
def @arg26 251 16777216 0 Y 0 31 8
2095
def @arg27 251 16777216 0 Y 128 31 63
2096
def @arg28 251 16777216 0 Y 0 31 8
2097
def @arg29 251 16777216 0 Y 128 31 63
2098
def @arg30 251 16777216 0 Y 0 31 8
2099
def @arg31 251 16777216 0 Y 0 31 8
2100
def @arg32 251 16777216 0 Y 0 31 8
2101
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
2102
0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2103
prepare stmt1 from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
2104
c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
2105
c25, c26, c27, c28, c29, c30, c31, c32
2106
into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
2107
@arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
2108
@arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
2109
@arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
2110
from t9 where c1= ?" ;
2112
execute stmt1 using @my_key ;
2114
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2115
def @arg01 8 20 1 Y 32896 0 63
2116
def @arg02 8 20 1 Y 32896 0 63
2117
def @arg03 8 20 1 Y 32896 0 63
2118
def @arg04 8 20 1 Y 32896 0 63
2119
def @arg05 8 20 1 Y 32896 0 63
2120
def @arg06 8 20 1 Y 32896 0 63
2121
def @arg07 5 23 1 Y 32896 31 63
2122
def @arg08 5 23 1 Y 32896 31 63
2123
def @arg09 5 23 1 Y 32896 31 63
2124
def @arg10 5 23 1 Y 32896 31 63
2125
def @arg11 246 83 6 Y 128 30 63
2126
def @arg12 246 83 6 Y 128 30 63
2127
def @arg13 251 16777216 10 Y 128 31 63
2128
def @arg14 251 16777216 19 Y 128 31 63
2129
def @arg15 251 16777216 19 Y 128 31 63
2130
def @arg16 251 16777216 8 Y 128 31 63
2131
def @arg17 8 20 4 Y 32928 0 63
2132
def @arg18 8 20 1 Y 32896 0 63
2133
def @arg19 8 20 1 Y 32896 0 63
2134
def @arg20 251 16777216 1 Y 0 31 8
2135
def @arg21 251 16777216 10 Y 0 31 8
2136
def @arg22 251 16777216 30 Y 0 31 8
2137
def @arg23 251 16777216 8 Y 128 31 63
2138
def @arg24 251 16777216 8 Y 0 31 8
2139
def @arg25 251 16777216 4 Y 128 31 63
2140
def @arg26 251 16777216 4 Y 0 31 8
2141
def @arg27 251 16777216 10 Y 128 31 63
2142
def @arg28 251 16777216 10 Y 0 31 8
2143
def @arg29 251 16777216 8 Y 128 31 63
2144
def @arg30 251 16777216 8 Y 0 31 8
2145
def @arg31 251 16777216 3 Y 0 31 8
2146
def @arg32 251 16777216 6 Y 0 31 8
2147
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
2148
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
2150
execute stmt1 using @my_key ;
2152
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2153
def @arg01 8 20 1 Y 32896 0 63
2154
def @arg02 8 20 0 Y 32896 0 63
2155
def @arg03 8 20 0 Y 32896 0 63
2156
def @arg04 8 20 0 Y 32896 0 63
2157
def @arg05 8 20 0 Y 32896 0 63
2158
def @arg06 8 20 0 Y 32896 0 63
2159
def @arg07 5 23 0 Y 32896 31 63
2160
def @arg08 5 23 0 Y 32896 31 63
2161
def @arg09 5 23 0 Y 32896 31 63
2162
def @arg10 5 23 0 Y 32896 31 63
2163
def @arg11 246 83 0 Y 128 30 63
2164
def @arg12 246 83 0 Y 128 30 63
2165
def @arg13 251 16777216 0 Y 128 31 63
2166
def @arg14 251 16777216 0 Y 128 31 63
2167
def @arg15 251 16777216 19 Y 128 31 63
2168
def @arg16 251 16777216 0 Y 128 31 63
2169
def @arg17 8 20 0 Y 32928 0 63
2170
def @arg18 8 20 0 Y 32896 0 63
2171
def @arg19 8 20 0 Y 32896 0 63
2172
def @arg20 251 16777216 0 Y 0 31 8
2173
def @arg21 251 16777216 0 Y 0 31 8
2174
def @arg22 251 16777216 0 Y 0 31 8
2175
def @arg23 251 16777216 0 Y 128 31 63
2176
def @arg24 251 16777216 0 Y 0 31 8
2177
def @arg25 251 16777216 0 Y 128 31 63
2178
def @arg26 251 16777216 0 Y 0 31 8
2179
def @arg27 251 16777216 0 Y 128 31 63
2180
def @arg28 251 16777216 0 Y 0 31 8
2181
def @arg29 251 16777216 0 Y 128 31 63
2182
def @arg30 251 16777216 0 Y 0 31 8
2183
def @arg31 251 16777216 0 Y 0 31 8
2184
def @arg32 251 16777216 0 Y 0 31 8
2185
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
2186
0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2187
prepare stmt1 from "select c1 into ? from t9 where c1= 1" ;
2188
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? from t9 where c1= 1' at line 1
2190
-- insert into numeric columns --
2192
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2194
( 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20 ) ;
2197
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2199
( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2200
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2201
prepare stmt1 from "insert into t9
2202
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2204
( 22, 22, 22, 22, 22, 22, 22, 22, 22, 22, 22 )" ;
2207
prepare stmt2 from "insert into t9
2208
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2210
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2211
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2212
@arg00, @arg00, @arg00, @arg00 ;
2214
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2216
( 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0,
2217
30.0, 30.0, 30.0 ) ;
2220
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2222
( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2223
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2224
prepare stmt1 from "insert into t9
2225
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2227
( 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0,
2228
32.0, 32.0, 32.0 )" ;
2231
prepare stmt2 from "insert into t9
2232
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2234
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2235
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2236
@arg00, @arg00, @arg00, @arg00 ;
2238
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2240
( '40', '40', '40', '40', '40', '40', '40', '40',
2241
'40', '40', '40' ) ;
2244
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2246
( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2247
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2248
prepare stmt1 from "insert into t9
2249
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2251
( '42', '42', '42', '42', '42', '42', '42', '42',
2252
'42', '42', '42' )" ;
2255
prepare stmt2 from "insert into t9
2256
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2258
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2259
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2260
@arg00, @arg00, @arg00, @arg00 ;
2262
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2264
( CAST('50' as binary), CAST('50' as binary),
2265
CAST('50' as binary), CAST('50' as binary), CAST('50' as binary),
2266
CAST('50' as binary), CAST('50' as binary), CAST('50' as binary),
2267
CAST('50' as binary), CAST('50' as binary), CAST('50' as binary) ) ;
2268
set @arg00= CAST('51' as binary) ;
2270
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2272
( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2273
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2274
prepare stmt1 from "insert into t9
2275
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2277
( CAST('52' as binary), CAST('52' as binary),
2278
CAST('52' as binary), CAST('52' as binary), CAST('52' as binary),
2279
CAST('52' as binary), CAST('52' as binary), CAST('52' as binary),
2280
CAST('52' as binary), CAST('52' as binary), CAST('52' as binary) )" ;
2282
set @arg00= CAST('53' as binary) ;
2283
prepare stmt2 from "insert into t9
2284
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2286
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2287
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2288
@arg00, @arg00, @arg00, @arg00 ;
2292
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2294
( 60, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2295
NULL, NULL, NULL ) ;
2297
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2299
( 61, @arg00, @arg00, @arg00, @arg00, @arg00,
2300
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2301
prepare stmt1 from "insert into t9
2302
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2304
( 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2305
NULL, NULL, NULL )" ;
2307
prepare stmt2 from "insert into t9
2308
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2310
( 63, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2311
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2312
@arg00, @arg00, @arg00, @arg00 ;
2316
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2318
( 71, @arg00, @arg00, @arg00, @arg00, @arg00,
2319
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2320
prepare stmt2 from "insert into t9
2321
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2323
( 73, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2324
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2325
@arg00, @arg00, @arg00, @arg00 ;
2329
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2331
( 81, @arg00, @arg00, @arg00, @arg00, @arg00,
2332
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2333
prepare stmt2 from "insert into t9
2334
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2336
( 83, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2337
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2338
@arg00, @arg00, @arg00, @arg00 ;
2339
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12
2340
from t9 where c1 >= 20
2342
c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c12
2343
20 20 20 20 20 20 20 20 20 20 20.0000
2344
21 21 21 21 21 21 21 21 21 21 21.0000
2345
22 22 22 22 22 22 22 22 22 22 22.0000
2346
23 23 23 23 23 23 23 23 23 23 23.0000
2347
30 30 30 30 30 30 30 30 30 30 30.0000
2348
31 31 31 31 31 31 31 31 31 31 31.0000
2349
32 32 32 32 32 32 32 32 32 32 32.0000
2350
33 33 33 33 33 33 33 33 33 33 33.0000
2351
40 40 40 40 40 40 40 40 40 40 40.0000
2352
41 41 41 41 41 41 41 41 41 41 41.0000
2353
42 42 42 42 42 42 42 42 42 42 42.0000
2354
43 43 43 43 43 43 43 43 43 43 43.0000
2355
50 50 50 50 50 50 50 50 50 50 50.0000
2356
51 51 51 51 51 51 51 51 51 51 51.0000
2357
52 52 52 52 52 52 52 52 52 52 52.0000
2358
53 53 53 53 53 53 53 53 53 53 53.0000
2359
60 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2360
61 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2361
62 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2362
63 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2363
71 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2364
73 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2365
81 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2366
83 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2368
-- select .. where numeric column = .. --
2370
select 'true' as found from t9
2371
where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20
2372
and c8= 20 and c9= 20 and c10= 20 and c12= 20;
2375
select 'true' as found from t9
2376
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
2377
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
2381
prepare stmt1 from "select 'true' as found from t9
2382
where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20
2383
and c8= 20 and c9= 20 and c10= 20 and c12= 20 ";
2387
prepare stmt1 from "select 'true' as found from t9
2388
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
2389
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
2391
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2392
@arg00, @arg00, @arg00, @arg00 ;
2396
select 'true' as found from t9
2397
where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0
2398
and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0;
2401
select 'true' as found from t9
2402
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
2403
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
2407
prepare stmt1 from "select 'true' as found from t9
2408
where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0
2409
and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0 ";
2413
prepare stmt1 from "select 'true' as found from t9
2414
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
2415
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
2417
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2418
@arg00, @arg00, @arg00, @arg00 ;
2421
select 'true' as found from t9
2422
where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'
2423
and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20';
2426
prepare stmt1 from "select 'true' as found from t9
2427
where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'
2428
and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20' ";
2433
select 'true' as found from t9
2434
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
2435
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
2439
prepare stmt1 from "select 'true' as found from t9
2440
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
2441
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
2443
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2444
@arg00, @arg00, @arg00, @arg00 ;
2447
select 'true' as found from t9
2448
where c1= CAST('20' as binary) and c2= CAST('20' as binary) and
2449
c3= CAST('20' as binary) and c4= CAST('20' as binary) and
2450
c5= CAST('20' as binary) and c6= CAST('20' as binary) and
2451
c7= CAST('20' as binary) and c8= CAST('20' as binary) and
2452
c9= CAST('20' as binary) and c10= CAST('20' as binary) and
2453
c12= CAST('20' as binary);
2456
prepare stmt1 from "select 'true' as found from t9
2457
where c1= CAST('20' as binary) and c2= CAST('20' as binary) and
2458
c3= CAST('20' as binary) and c4= CAST('20' as binary) and
2459
c5= CAST('20' as binary) and c6= CAST('20' as binary) and
2460
c7= CAST('20' as binary) and c8= CAST('20' as binary) and
2461
c9= CAST('20' as binary) and c10= CAST('20' as binary) and
2462
c12= CAST('20' as binary) ";
2466
set @arg00= CAST('20' as binary) ;
2467
select 'true' as found from t9
2468
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
2469
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
2473
prepare stmt1 from "select 'true' as found from t9
2474
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
2475
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
2477
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2478
@arg00, @arg00, @arg00, @arg00 ;
2483
-- some numeric overflow experiments --
2484
prepare my_insert from "insert into t9
2485
( c21, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2487
( 'O', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2488
prepare my_select from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12
2489
from t9 where c21 = 'O' ";
2490
prepare my_delete from "delete from t9 where c21 = 'O' ";
2491
set @arg00= 9223372036854775807 ;
2492
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2493
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2495
Warning 1264 Out of range value for column 'c1' at row 1
2496
Warning 1264 Out of range value for column 'c2' at row 1
2497
Warning 1264 Out of range value for column 'c3' at row 1
2498
Warning 1264 Out of range value for column 'c4' at row 1
2499
Warning 1264 Out of range value for column 'c5' at row 1
2500
Warning 1264 Out of range value for column 'c12' at row 1
2507
c6 9223372036854775807
2509
c8 9.22337203685478e+18
2510
c9 9.22337203685478e+18
2511
c10 9.22337203685478e+18
2514
set @arg00= '9223372036854775807' ;
2515
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2516
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2518
Warning 1264 Out of range value for column 'c1' at row 1
2519
Warning 1264 Out of range value for column 'c2' at row 1
2520
Warning 1264 Out of range value for column 'c3' at row 1
2521
Warning 1264 Out of range value for column 'c4' at row 1
2522
Warning 1264 Out of range value for column 'c5' at row 1
2523
Warning 1264 Out of range value for column 'c12' at row 1
2530
c6 9223372036854775807
2532
c8 9.22337203685478e+18
2533
c9 9.22337203685478e+18
2534
c10 9.22337203685478e+18
2537
set @arg00= -9223372036854775808 ;
2538
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2539
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2541
Warning 1264 Out of range value for column 'c1' at row 1
2542
Warning 1264 Out of range value for column 'c2' at row 1
2543
Warning 1264 Out of range value for column 'c3' at row 1
2544
Warning 1264 Out of range value for column 'c4' at row 1
2545
Warning 1264 Out of range value for column 'c5' at row 1
2546
Warning 1264 Out of range value for column 'c12' at row 1
2553
c6 -9223372036854775808
2555
c8 -9.22337203685478e+18
2556
c9 -9.22337203685478e+18
2557
c10 -9.22337203685478e+18
2560
set @arg00= '-9223372036854775808' ;
2561
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2562
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2564
Warning 1264 Out of range value for column 'c1' at row 1
2565
Warning 1264 Out of range value for column 'c2' at row 1
2566
Warning 1264 Out of range value for column 'c3' at row 1
2567
Warning 1264 Out of range value for column 'c4' at row 1
2568
Warning 1264 Out of range value for column 'c5' at row 1
2569
Warning 1264 Out of range value for column 'c12' at row 1
2576
c6 -9223372036854775808
2578
c8 -9.22337203685478e+18
2579
c9 -9.22337203685478e+18
2580
c10 -9.22337203685478e+18
2583
set @arg00= 1.11111111111111111111e+50 ;
2584
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2585
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2587
Warning 1264 Out of range value for column 'c1' at row 1
2588
Warning 1264 Out of range value for column 'c2' at row 1
2589
Warning 1264 Out of range value for column 'c3' at row 1
2590
Warning 1264 Out of range value for column 'c4' at row 1
2591
Warning 1264 Out of range value for column 'c5' at row 1
2592
Warning 1264 Out of range value for column 'c6' at row 1
2593
Warning 1264 Out of range value for column 'c7' at row 1
2594
Warning 1264 Out of range value for column 'c12' at row 1
2601
c6 9223372036854775807
2603
c8 1.11111111111111e+50
2604
c9 1.11111111111111e+50
2605
c10 1.11111111111111e+50
2608
set @arg00= '1.11111111111111111111e+50' ;
2609
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2610
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2612
Warning 1264 Out of range value for column 'c1' at row 1
2613
Warning 1264 Out of range value for column 'c2' at row 1
2614
Warning 1264 Out of range value for column 'c3' at row 1
2615
Warning 1264 Out of range value for column 'c4' at row 1
2616
Warning 1264 Out of range value for column 'c5' at row 1
2617
Warning 1264 Out of range value for column 'c6' at row 1
2618
Warning 1264 Out of range value for column 'c7' at row 1
2619
Warning 1264 Out of range value for column 'c12' at row 1
2626
c6 9223372036854775807
2628
c8 1.11111111111111e+50
2629
c9 1.11111111111111e+50
2630
c10 1.11111111111111e+50
2633
set @arg00= -1.11111111111111111111e+50 ;
2634
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2635
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2637
Warning 1264 Out of range value for column 'c1' at row 1
2638
Warning 1264 Out of range value for column 'c2' at row 1
2639
Warning 1264 Out of range value for column 'c3' at row 1
2640
Warning 1264 Out of range value for column 'c4' at row 1
2641
Warning 1264 Out of range value for column 'c5' at row 1
2642
Warning 1264 Out of range value for column 'c6' at row 1
2643
Warning 1264 Out of range value for column 'c7' at row 1
2644
Warning 1264 Out of range value for column 'c12' at row 1
2651
c6 -9223372036854775808
2653
c8 -1.11111111111111e+50
2654
c9 -1.11111111111111e+50
2655
c10 -1.11111111111111e+50
2658
set @arg00= '-1.11111111111111111111e+50' ;
2659
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2660
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2662
Warning 1264 Out of range value for column 'c1' at row 1
2663
Warning 1264 Out of range value for column 'c2' at row 1
2664
Warning 1264 Out of range value for column 'c3' at row 1
2665
Warning 1264 Out of range value for column 'c4' at row 1
2666
Warning 1264 Out of range value for column 'c5' at row 1
2667
Warning 1264 Out of range value for column 'c6' at row 1
2668
Warning 1264 Out of range value for column 'c7' at row 1
2669
Warning 1264 Out of range value for column 'c12' at row 1
2676
c6 -9223372036854775808
2678
c8 -1.11111111111111e+50
2679
c9 -1.11111111111111e+50
2680
c10 -1.11111111111111e+50
2684
-- insert into string columns --
2686
Warning 1265 Data truncated for column 'c20' at row 1
2688
Warning 1265 Data truncated for column 'c20' at row 1
2690
Warning 1265 Data truncated for column 'c20' at row 1
2692
Warning 1265 Data truncated for column 'c20' at row 1
2694
Warning 1265 Data truncated for column 'c20' at row 1
2696
Warning 1265 Data truncated for column 'c20' at row 1
2698
Warning 1265 Data truncated for column 'c20' at row 1
2700
Warning 1265 Data truncated for column 'c20' at row 1
2702
Warning 1265 Data truncated for column 'c20' at row 1
2704
Warning 1265 Data truncated for column 'c20' at row 1
2706
Warning 1265 Data truncated for column 'c20' at row 1
2708
Warning 1265 Data truncated for column 'c20' at row 1
2710
Warning 1265 Data truncated for column 'c20' at row 1
2712
Warning 1265 Data truncated for column 'c20' at row 1
2714
Warning 1265 Data truncated for column 'c20' at row 1
2716
Warning 1265 Data truncated for column 'c20' at row 1
2718
Warning 1265 Data truncated for column 'c20' at row 1
2720
Warning 1265 Data truncated for column 'c20' at row 1
2722
Warning 1265 Data truncated for column 'c20' at row 1
2724
Warning 1265 Data truncated for column 'c20' at row 1
2725
select c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30
2726
from t9 where c1 >= 20
2728
c1 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30
2729
20 2 20 20 20 20 20 20 20 20 20 20
2730
21 2 21 21 21 21 21 21 21 21 21 21
2731
22 2 22 22 22 22 22 22 22 22 22 22
2732
23 2 23 23 23 23 23 23 23 23 23 23
2733
30 3 30 30 30 30 30 30 30 30 30 30
2734
31 3 31 31 31 31 31 31 31 31 31 31
2735
32 3 32 32 32 32 32 32 32 32 32 32
2736
33 3 33 33 33 33 33 33 33 33 33 33
2737
40 4 40 40 40 40 40 40 40 40 40 40
2738
41 4 41 41 41 41 41 41 41 41 41 41
2739
42 4 42 42 42 42 42 42 42 42 42 42
2740
43 4 43 43 43 43 43 43 43 43 43 43
2741
50 5 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0
2742
51 5 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0
2743
52 5 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0
2744
53 5 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0
2745
54 5 54 54 54.00 54.00 54.00 54.00 54.00 54.00 54.00 54.00
2746
55 5 55 55 55 55 55 55 55 55 55 55
2747
56 6 56 56 56.00 56.00 56.00 56.00 56.00 56.00 56.00 56.00
2748
57 6 57 57 57.00 57.00 57.00 57.00 57.00 57.00 57.00 57.00
2749
60 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2750
61 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2751
62 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2752
63 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2753
71 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2754
73 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2755
81 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2756
83 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2758
-- select .. where string column = .. --
2760
select 'true' as found from t9
2761
where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and
2762
c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and
2763
c27= '20' and c28= '20' and c29= '20' and c30= '20' ;
2766
select 'true' as found from t9
2767
where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
2768
c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
2769
c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
2772
prepare stmt1 from "select 'true' as found from t9
2773
where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and
2774
c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and
2775
c27= '20' and c28= '20' and c29= '20' and c30= '20'" ;
2779
prepare stmt1 from "select 'true' as found from t9
2780
where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
2781
c21= ? and c22= ? and c23= ? and c25= ? and
2782
c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
2783
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2784
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2787
set @arg00= CAST('20' as binary);
2788
select 'true' as found from t9
2789
where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))
2790
= CAST('20' as binary) and c21= CAST('20' as binary)
2791
and c22= CAST('20' as binary) and c23= CAST('20' as binary) and
2792
c24= CAST('20' as binary) and c25= CAST('20' as binary) and
2793
c26= CAST('20' as binary) and c27= CAST('20' as binary) and
2794
c28= CAST('20' as binary) and c29= CAST('20' as binary) and
2795
c30= CAST('20' as binary) ;
2798
select 'true' as found from t9
2799
where c1= 20 and concat(c20,substr(@arg00,1+length(c20))) = @arg00 and
2800
c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
2801
c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and
2805
prepare stmt1 from "select 'true' as found from t9
2806
where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))
2807
= CAST('20' as binary) and c21= CAST('20' as binary)
2808
and c22= CAST('20' as binary) and c23= CAST('20' as binary) and
2809
c24= CAST('20' as binary) and c25= CAST('20' as binary) and
2810
c26= CAST('20' as binary) and c27= CAST('20' as binary) and
2811
c28= CAST('20' as binary) and c29= CAST('20' as binary) and
2812
c30= CAST('20' as binary)" ;
2816
prepare stmt1 from "select 'true' as found from t9
2817
where c1= 20 and concat(c20,substr(?,1+length(c20))) = ? and c21= ? and
2818
c22= ? and c23= ? and c25= ? and c26= ? and c27= ? and c28= ? and
2820
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2821
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2825
select 'true' as found from t9
2826
where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and
2827
c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and
2828
c27= 20 and c28= 20 and c29= 20 and c30= 20 ;
2831
select 'true' as found from t9
2832
where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
2833
c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
2834
c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
2837
prepare stmt1 from "select 'true' as found from t9
2838
where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and
2839
c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and
2840
c27= 20 and c28= 20 and c29= 20 and c30= 20" ;
2844
prepare stmt1 from "select 'true' as found from t9
2845
where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
2846
c21= ? and c22= ? and c23= ? and c25= ? and
2847
c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
2848
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2849
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2853
select 'true' as found from t9
2854
where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and
2855
c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and
2856
c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0 ;
2859
select 'true' as found from t9
2860
where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
2861
c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
2862
c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
2865
prepare stmt1 from "select 'true' as found from t9
2866
where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and
2867
c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and
2868
c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0" ;
2872
prepare stmt1 from "select 'true' as found from t9
2873
where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
2874
c21= ? and c22= ? and c23= ? and c25= ? and
2875
c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
2876
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2877
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2882
-- insert into date/time columns --
2884
Note 1265 Data truncated for column 'c13' at row 1
2885
Warning 1265 Data truncated for column 'c17' at row 1
2887
Note 1265 Data truncated for column 'c13' at row 1
2888
Warning 1265 Data truncated for column 'c17' at row 1
2890
Note 1265 Data truncated for column 'c13' at row 1
2891
Warning 1265 Data truncated for column 'c17' at row 1
2893
Note 1265 Data truncated for column 'c13' at row 1
2894
Warning 1265 Data truncated for column 'c17' at row 1
2896
Note 1265 Data truncated for column 'c13' at row 1
2897
Warning 1265 Data truncated for column 'c17' at row 1
2899
Note 1265 Data truncated for column 'c13' at row 1
2900
Warning 1265 Data truncated for column 'c17' at row 1
2902
Note 1265 Data truncated for column 'c13' at row 1
2903
Warning 1265 Data truncated for column 'c17' at row 1
2905
Note 1265 Data truncated for column 'c13' at row 1
2906
Warning 1265 Data truncated for column 'c17' at row 1
2908
Warning 1264 Out of range value for column 'c13' at row 1
2909
Warning 1264 Out of range value for column 'c14' at row 1
2910
Warning 1265 Data truncated for column 'c15' at row 1
2911
Warning 1264 Out of range value for column 'c16' at row 1
2912
Warning 1264 Out of range value for column 'c17' at row 1
2914
Warning 1264 Out of range value for column 'c13' at row 1
2915
Warning 1264 Out of range value for column 'c14' at row 1
2916
Warning 1265 Data truncated for column 'c15' at row 1
2917
Warning 1264 Out of range value for column 'c16' at row 1
2918
Warning 1264 Out of range value for column 'c17' at row 1
2920
Warning 1264 Out of range value for column 'c13' at row 1
2921
Warning 1264 Out of range value for column 'c14' at row 1
2922
Warning 1265 Data truncated for column 'c15' at row 1
2923
Warning 1264 Out of range value for column 'c16' at row 1
2924
Warning 1264 Out of range value for column 'c17' at row 1
2926
Warning 1264 Out of range value for column 'c13' at row 1
2927
Warning 1264 Out of range value for column 'c14' at row 1
2928
Warning 1265 Data truncated for column 'c15' at row 1
2929
Warning 1264 Out of range value for column 'c16' at row 1
2930
Warning 1264 Out of range value for column 'c17' at row 1
2932
Warning 1265 Data truncated for column 'c15' at row 1
2933
Warning 1264 Out of range value for column 'c16' at row 1
2934
Warning 1264 Out of range value for column 'c17' at row 1
2936
Warning 1265 Data truncated for column 'c15' at row 1
2937
Warning 1264 Out of range value for column 'c16' at row 1
2938
Warning 1264 Out of range value for column 'c17' at row 1
2940
Warning 1265 Data truncated for column 'c15' at row 1
2941
Warning 1264 Out of range value for column 'c16' at row 1
2942
Warning 1264 Out of range value for column 'c17' at row 1
2944
Warning 1265 Data truncated for column 'c15' at row 1
2945
Warning 1264 Out of range value for column 'c16' at row 1
2946
Warning 1264 Out of range value for column 'c17' at row 1
2947
select c1, c13, c14, c15, c16, c17 from t9 order by c1 ;
2948
c1 c13 c14 c15 c16 c17
2949
20 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
2950
21 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
2951
22 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
2952
23 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
2953
30 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
2954
31 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
2955
32 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
2956
33 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
2957
40 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
2958
41 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
2959
42 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
2960
43 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
2961
50 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
2962
51 2010-00-00 2010-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
2963
52 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
2964
53 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
2965
60 NULL NULL 1991-01-01 01:01:01 NULL NULL
2966
61 NULL NULL 1991-01-01 01:01:01 NULL NULL
2967
62 NULL NULL 1991-01-01 01:01:01 NULL NULL
2968
63 NULL NULL 1991-01-01 01:01:01 NULL NULL
2969
71 NULL NULL 1991-01-01 01:01:01 NULL NULL
2970
73 NULL NULL 1991-01-01 01:01:01 NULL NULL
2971
81 NULL NULL 1991-01-01 01:01:01 NULL NULL
2972
83 NULL NULL 1991-01-01 01:01:01 NULL NULL
2974
-- select .. where date/time column = .. --
2975
set @arg00= '1991-01-01 01:01:01' ;
2976
select 'true' as found from t9
2977
where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
2978
c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
2979
c17= '1991-01-01 01:01:01' ;
2982
select 'true' as found from t9
2983
where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
2987
prepare stmt1 from "select 'true' as found from t9
2988
where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
2989
c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
2990
c17= '1991-01-01 01:01:01'" ;
2994
prepare stmt1 from "select 'true' as found from t9
2995
where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
2996
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
2999
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
3000
select 'true' as found from t9
3001
where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
3002
c14= CAST('1991-01-01 01:01:01' as datetime) and
3003
c15= CAST('1991-01-01 01:01:01' as datetime) and
3004
c16= CAST('1991-01-01 01:01:01' as datetime) and
3005
c17= CAST('1991-01-01 01:01:01' as datetime) ;
3008
select 'true' as found from t9
3009
where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
3013
prepare stmt1 from "select 'true' as found from t9
3014
where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
3015
c14= CAST('1991-01-01 01:01:01' as datetime) and
3016
c15= CAST('1991-01-01 01:01:01' as datetime) and
3017
c16= CAST('1991-01-01 01:01:01' as datetime) and
3018
c17= CAST('1991-01-01 01:01:01' as datetime)" ;
3022
prepare stmt1 from "select 'true' as found from t9
3023
where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
3024
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
3028
select 'true' as found from t9
3029
where c1= 20 and c17= 1991 ;
3032
select 'true' as found from t9
3033
where c1= 20 and c17= @arg00 ;
3036
prepare stmt1 from "select 'true' as found from t9
3037
where c1= 20 and c17= 1991" ;
3041
prepare stmt1 from "select 'true' as found from t9
3042
where c1= 20 and c17= ?" ;
3043
execute stmt1 using @arg00 ;
3046
set @arg00= 1.991e+3 ;
3047
select 'true' as found from t9
3048
where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;
3051
select 'true' as found from t9
3052
where c1= 20 and abs(c17 - @arg00) < 0.01 ;
3055
prepare stmt1 from "select 'true' as found from t9
3056
where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;
3060
prepare stmt1 from "select 'true' as found from t9
3061
where c1= 20 and abs(c17 - ?) < 0.01" ;
3062
execute stmt1 using @arg00 ;
3068
a int, b varchar(30),
3070
) ENGINE = MERGE UNION=(t1_1,t1_2)
3074
c1 tinyint, c2 smallint, c3 mediumint, c4 int,
3075
c5 integer, c6 bigint, c7 float, c8 double,
3076
c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
3077
c13 date, c14 datetime, c15 timestamp, c16 time,
3078
c17 year, c18 tinyint, c19 bool, c20 char,
3079
c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
3080
c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
3081
c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'),
3082
c32 set('monday', 'tuesday', 'wednesday'),
3084
) ENGINE = MERGE UNION=(t9_1,t9_2)
3087
insert into t1 values (1,'one');
3088
insert into t1 values (2,'two');
3089
insert into t1 values (3,'three');
3090
insert into t1 values (4,'four');
3094
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
3095
c10= 1, c11= 1, c12 = 1,
3096
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
3097
c16= '11:11:11', c17= '2004',
3098
c18= 1, c19=true, c20= 'a', c21= '123456789a',
3099
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
3100
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
3101
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
3103
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
3104
c10= 9, c11= 9, c12 = 9,
3105
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
3106
c16= '11:11:11', c17= '2004',
3107
c18= 1, c19=false, c20= 'a', c21= '123456789a',
3108
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
3109
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
3110
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
3113
------ simple select tests ------
3114
prepare stmt1 from ' select * from t9 order by c1 ' ;
3116
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
3117
def test t9 t9 c1 c1 1 4 1 N 49155 0 63
3118
def test t9 t9 c2 c2 2 6 1 Y 32768 0 63
3119
def test t9 t9 c3 c3 9 9 1 Y 32768 0 63
3120
def test t9 t9 c4 c4 3 11 1 Y 32768 0 63
3121
def test t9 t9 c5 c5 3 11 1 Y 32768 0 63
3122
def test t9 t9 c6 c6 8 20 1 Y 32768 0 63
3123
def test t9 t9 c7 c7 4 12 1 Y 32768 31 63
3124
def test t9 t9 c8 c8 5 22 1 Y 32768 31 63
3125
def test t9 t9 c9 c9 5 22 1 Y 32768 31 63
3126
def test t9 t9 c10 c10 5 22 1 Y 32768 31 63
3127
def test t9 t9 c11 c11 246 9 6 Y 0 4 63
3128
def test t9 t9 c12 c12 246 10 6 Y 0 4 63
3129
def test t9 t9 c13 c13 10 10 10 Y 128 0 63
3130
def test t9 t9 c14 c14 12 19 19 Y 128 0 63
3131
def test t9 t9 c15 c15 7 19 19 N 9441 0 63
3132
def test t9 t9 c16 c16 11 8 8 Y 128 0 63
3133
def test t9 t9 c17 c17 13 4 4 Y 32864 0 63
3134
def test t9 t9 c18 c18 1 4 1 Y 32768 0 63
3135
def test t9 t9 c19 c19 1 1 1 Y 32768 0 63
3136
def test t9 t9 c20 c20 254 1 1 Y 0 0 8
3137
def test t9 t9 c21 c21 254 10 10 Y 0 0 8
3138
def test t9 t9 c22 c22 253 30 30 Y 0 0 8
3139
def test t9 t9 c23 c23 252 255 8 Y 144 0 63
3140
def test t9 t9 c24 c24 252 255 8 Y 16 0 8
3141
def test t9 t9 c25 c25 252 65535 4 Y 144 0 63
3142
def test t9 t9 c26 c26 252 65535 4 Y 16 0 8
3143
def test t9 t9 c27 c27 252 16777215 10 Y 144 0 63
3144
def test t9 t9 c28 c28 252 16777215 10 Y 16 0 8
3145
def test t9 t9 c29 c29 252 4294967295 8 Y 144 0 63
3146
def test t9 t9 c30 c30 252 4294967295 8 Y 16 0 8
3147
def test t9 t9 c31 c31 254 5 3 Y 256 0 8
3148
def test t9 t9 c32 c32 254 24 7 Y 2048 0 8
3149
c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32
3150
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
3151
9 9 9 9 9 9 9 9 9 9 9.0000 9.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 0 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext two tuesday
3152
set @arg00='SELECT' ;
3153
@arg00 a from t1 where a=1;
3154
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@arg00 a from t1 where a=1' at line 1
3155
prepare stmt1 from ' ? a from t1 where a=1 ';
3156
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? a from t1 where a=1' at line 1
3158
select @arg00, b from t1 where a=1 ;
3161
prepare stmt1 from ' select ?, b from t1 where a=1 ' ;
3162
execute stmt1 using @arg00 ;
3166
select @arg00, b from t1 where a=1 ;
3169
prepare stmt1 from ' select ?, b from t1 where a=1 ' ;
3170
execute stmt1 using @arg00 ;
3174
select @arg00, b from t1 where a=1 ;
3177
prepare stmt1 from ' select ?, b from t1 where a=1 ' ;
3178
execute stmt1 using @arg00 ;
3182
select b, a - @arg00 from t1 where a=1 ;
3185
prepare stmt1 from ' select b, a - ? from t1 where a=1 ' ;
3186
execute stmt1 using @arg00 ;
3190
select @arg00 as my_col ;
3193
prepare stmt1 from ' select ? as my_col';
3194
execute stmt1 using @arg00 ;
3197
select @arg00 + 1 as my_col ;
3200
prepare stmt1 from ' select ? + 1 as my_col';
3201
execute stmt1 using @arg00 ;
3204
select 1 + @arg00 as my_col ;
3207
prepare stmt1 from ' select 1 + ? as my_col';
3208
execute stmt1 using @arg00 ;
3211
set @arg00='MySQL' ;
3212
select substr(@arg00,1,2) from t1 where a=1 ;
3215
prepare stmt1 from ' select substr(?,1,2) from t1 where a=1 ' ;
3216
execute stmt1 using @arg00 ;
3220
select substr('MySQL',@arg00,5) from t1 where a=1 ;
3221
substr('MySQL',@arg00,5)
3223
prepare stmt1 from ' select substr(''MySQL'',?,5) from t1 where a=1 ' ;
3224
execute stmt1 using @arg00 ;
3227
select substr('MySQL',1,@arg00) from t1 where a=1 ;
3228
substr('MySQL',1,@arg00)
3230
prepare stmt1 from ' select substr(''MySQL'',1,?) from t1 where a=1 ' ;
3231
execute stmt1 using @arg00 ;
3234
set @arg00='MySQL' ;
3235
select a , concat(@arg00,b) from t1 order by a;
3241
prepare stmt1 from ' select a , concat(?,b) from t1 order by a ' ;
3242
execute stmt1 using @arg00;
3248
select a , concat(b,@arg00) from t1 order by a ;
3254
prepare stmt1 from ' select a , concat(b,?) from t1 order by a ' ;
3255
execute stmt1 using @arg00;
3261
set @arg00='MySQL' ;
3262
select group_concat(@arg00,b order by a) from t1
3264
group_concat(@arg00,b order by a)
3265
MySQLone,MySQLtwo,MySQLthree,MySQLfour
3266
prepare stmt1 from ' select group_concat(?,b order by a) from t1
3268
execute stmt1 using @arg00;
3269
group_concat(?,b order by a)
3270
MySQLone,MySQLtwo,MySQLthree,MySQLfour
3271
select group_concat(b,@arg00 order by a) from t1
3273
group_concat(b,@arg00 order by a)
3274
oneMySQL,twoMySQL,threeMySQL,fourMySQL
3275
prepare stmt1 from ' select group_concat(b,? order by a) from t1
3277
execute stmt1 using @arg00;
3278
group_concat(b,? order by a)
3279
oneMySQL,twoMySQL,threeMySQL,fourMySQL
3280
set @arg00='first' ;
3281
set @arg01='second' ;
3283
select @arg00, @arg01 from t1 where a=1 ;
3286
prepare stmt1 from ' select ?, ? from t1 where a=1 ' ;
3287
execute stmt1 using @arg00, @arg01 ;
3290
execute stmt1 using @arg02, @arg01 ;
3293
execute stmt1 using @arg00, @arg02 ;
3296
execute stmt1 using @arg02, @arg02 ;
3299
drop table if exists t5 ;
3300
create table t5 (id1 int(11) not null default '0',
3301
value2 varchar(100), value1 varchar(100)) ;
3302
insert into t5 values (1,'hh','hh'),(2,'hh','hh'),
3303
(1,'ii','ii'),(2,'ii','ii') ;
3304
prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? order by id1,value1 ' ;
3307
execute stmt1 using @arg00, @arg01 ;
3313
drop table if exists t5 ;
3314
create table t5(session_id char(9) not null) ;
3315
insert into t5 values ('abc') ;
3316
prepare stmt1 from ' select * from t5
3317
where ?=''1111'' and session_id = ''abc'' ' ;
3319
execute stmt1 using @arg00 ;
3322
execute stmt1 using @arg00 ;
3326
execute stmt1 using @arg00 ;
3330
select a @arg00 t1 where a=1 ;
3331
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@arg00 t1 where a=1' at line 1
3332
prepare stmt1 from ' select a ? t1 where a=1 ' ;
3333
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? t1 where a=1' at line 1
3335
select a from @arg00 where a=1 ;
3336
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@arg00 where a=1' at line 1
3337
prepare stmt1 from ' select a from ? where a=1 ' ;
3338
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? where a=1' at line 1
3339
set @arg00='WHERE' ;
3340
select a from t1 @arg00 a=1 ;
3341
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@arg00 a=1' at line 1
3342
prepare stmt1 from ' select a from t1 ? a=1 ' ;
3343
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? a=1' at line 1
3345
select a FROM t1 where a=@arg00 ;
3348
prepare stmt1 from ' select a FROM t1 where a=? ' ;
3349
execute stmt1 using @arg00 ;
3353
execute stmt1 using @arg00 ;
3356
select a FROM t1 where a=@arg00 ;
3358
prepare stmt1 from ' select a FROM t1 where a=? ' ;
3359
execute stmt1 using @arg00 ;
3362
select a FROM t1 where a=sqrt(@arg00) ;
3365
prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ;
3366
execute stmt1 using @arg00 ;
3370
select a FROM t1 where a=sqrt(@arg00) ;
3372
prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ;
3373
execute stmt1 using @arg00 ;
3377
select a FROM t1 where a in (@arg00,@arg01) order by a;
3381
prepare stmt1 from ' select a FROM t1 where a in (?,?) order by a ';
3382
execute stmt1 using @arg00, @arg01;
3388
set @arg02= 'five' ;
3389
prepare stmt1 from ' select b FROM t1 where b in (?,?,?) order by b ' ;
3390
execute stmt1 using @arg00, @arg01, @arg02 ;
3394
prepare stmt1 from ' select b FROM t1 where b like ? ';
3396
execute stmt1 using @arg00 ;
3400
execute stmt1 using @arg00 ;
3404
execute stmt1 using @arg00 ;
3408
insert into t9 set c1= 0, c5 = NULL ;
3409
select c5 from t9 where c5 > NULL ;
3411
prepare stmt1 from ' select c5 from t9 where c5 > ? ';
3412
execute stmt1 using @arg00 ;
3414
select c5 from t9 where c5 < NULL ;
3416
prepare stmt1 from ' select c5 from t9 where c5 < ? ';
3417
execute stmt1 using @arg00 ;
3419
select c5 from t9 where c5 = NULL ;
3421
prepare stmt1 from ' select c5 from t9 where c5 = ? ';
3422
execute stmt1 using @arg00 ;
3424
select c5 from t9 where c5 <=> NULL ;
3427
prepare stmt1 from ' select c5 from t9 where c5 <=> ? ';
3428
execute stmt1 using @arg00 ;
3431
delete from t9 where c1= 0 ;
3433
select a FROM t1 where a @arg00 1 ;
3434
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@arg00 1' at line 1
3435
prepare stmt1 from ' select a FROM t1 where a ? 1 ' ;
3436
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? 1' at line 1
3438
select a,b FROM t1 where a is not NULL
3439
AND b is not NULL group by a - @arg00 ;
3445
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
3446
AND b is not NULL group by a - ? ' ;
3447
execute stmt1 using @arg00 ;
3454
select a,b FROM t1 where a is not NULL
3455
AND b is not NULL having b <> @arg00 order by a ;
3460
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
3461
AND b is not NULL having b <> ? order by a ' ;
3462
execute stmt1 using @arg00 ;
3468
select a,b FROM t1 where a is not NULL
3469
AND b is not NULL order by a - @arg00 ;
3475
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
3476
AND b is not NULL order by a - ? ' ;
3477
execute stmt1 using @arg00 ;
3484
select a,b from t1 order by 2 ;
3490
prepare stmt1 from ' select a,b from t1
3492
execute stmt1 using @arg00;
3499
execute stmt1 using @arg00;
3506
execute stmt1 using @arg00;
3507
ERROR 42S22: Unknown column '?' in 'order clause'
3509
prepare stmt1 from ' select a,b from t1 order by a
3514
prepare stmt1 from ' select a,b from t1 order by a limit ? ';
3515
execute stmt1 using @arg00;
3522
select sum(a), @arg00 from t1 where a > @arg01
3523
and b is not null group by substr(b,@arg02)
3524
having sum(a) <> @arg03 ;
3529
prepare stmt1 from ' select sum(a), ? from t1 where a > ?
3530
and b is not null group by substr(b,?)
3531
having sum(a) <> ? ';
3532
execute stmt1 using @arg00, @arg01, @arg02, @arg03;
3538
------ join tests ------
3539
select first.a as a1, second.a as a2
3540
from t1 first, t1 second
3541
where first.a = second.a order by a1 ;
3547
prepare stmt1 from ' select first.a as a1, second.a as a2
3548
from t1 first, t1 second
3549
where first.a = second.a order by a1 ';
3559
select first.a, @arg00, second.a FROM t1 first, t1 second
3560
where @arg01 = first.b or first.a = second.a or second.b = @arg02
3561
order by second.a, first.a;
3572
prepare stmt1 from ' select first.a, ?, second.a FROM t1 first, t1 second
3573
where ? = first.b or first.a = second.a or second.b = ?
3574
order by second.a, first.a';
3575
execute stmt1 using @arg00, @arg01, @arg02;
3586
drop table if exists t2 ;
3587
create table t2 as select * from t1 ;
3588
set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
3589
set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
3590
set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
3591
set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
3592
set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
3593
set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
3594
set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
3595
set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
3596
set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
3597
the join statement is:
3598
SELECT * FROM t2 right join t1 using(a) order by t2.a
3599
prepare stmt1 from @query9 ;
3618
the join statement is:
3619
SELECT * FROM t2 natural right join t1 order by t2.a
3620
prepare stmt1 from @query8 ;
3639
the join statement is:
3640
SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a
3641
prepare stmt1 from @query7 ;
3660
the join statement is:
3661
SELECT * FROM t2 left join t1 using(a) order by t2.a
3662
prepare stmt1 from @query6 ;
3681
the join statement is:
3682
SELECT * FROM t2 natural left join t1 order by t2.a
3683
prepare stmt1 from @query5 ;
3702
the join statement is:
3703
SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a
3704
prepare stmt1 from @query4 ;
3723
the join statement is:
3724
SELECT * FROM t2 join t1 using(a) order by t2.a
3725
prepare stmt1 from @query3 ;
3744
the join statement is:
3745
SELECT * FROM t2 natural join t1 order by t2.a
3746
prepare stmt1 from @query2 ;
3765
the join statement is:
3766
SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a
3767
prepare stmt1 from @query1 ;
3788
------ subquery tests ------
3789
prepare stmt1 from ' select a, b FROM t1 outer_table where
3790
a = (select a from t1 where b = ''two'') ';
3795
select a, b FROM t1 outer_table where
3796
a = (select a from t1 where b = 'two' ) and b=@arg00 ;
3799
prepare stmt1 from ' select a, b FROM t1 outer_table where
3800
a = (select a from t1 where b = ''two'') and b=? ';
3801
execute stmt1 using @arg00;
3805
select a, b FROM t1 outer_table where
3806
a = (select a from t1 where b = @arg00 ) and b='two' ;
3809
prepare stmt1 from ' select a, b FROM t1 outer_table where
3810
a = (select a from t1 where b = ? ) and b=''two'' ' ;
3811
execute stmt1 using @arg00;
3815
set @arg01='three' ;
3816
select a,b FROM t1 where (a,b) in (select 3, 'three');
3819
select a FROM t1 where (a,b) in (select @arg00,@arg01);
3822
prepare stmt1 from ' select a FROM t1 where (a,b) in (select ?, ?) ';
3823
execute stmt1 using @arg00, @arg01;
3830
select a, @arg00, b FROM t1 outer_table where
3831
b=@arg01 and a = (select @arg02 from t1 where b = @arg03 ) ;
3834
prepare stmt1 from ' select a, ?, b FROM t1 outer_table where
3835
b=? and a = (select ? from t1 where b = ? ) ' ;
3836
execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
3839
prepare stmt1 from 'select c4 FROM t9 where
3840
c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ;
3841
execute stmt1 using @arg01, @arg02;
3843
prepare stmt1 from ' select a, b FROM t1 outer_table where
3844
a = (select a from t1 where b = outer_table.b ) order by a ';
3851
prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
3852
(SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
3856
deallocate prepare stmt1 ;
3857
prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
3858
(SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
3862
deallocate prepare stmt1 ;
3863
prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
3864
(SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
3868
deallocate prepare stmt1 ;
3870
select a, b FROM t1 outer_table where
3871
a = (select a from t1 where b = outer_table.b ) and b=@arg00 ;
3874
prepare stmt1 from ' select a, b FROM t1 outer_table where
3875
a = (select a from t1 where b = outer_table.b) and b=? ';
3876
execute stmt1 using @arg00;
3880
select a, b FROM t1 outer_table where
3881
a = (select a from t1 where a = @arg00 and b = outer_table.b) and b='two' ;
3884
prepare stmt1 from ' select a, b FROM t1 outer_table where
3885
a = (select a from t1 where a = ? and b = outer_table.b) and b=''two'' ' ;
3886
execute stmt1 using @arg00;
3890
select a, b FROM t1 outer_table where
3891
a = (select a from t1 where outer_table.a = @arg00 and a=2) and b='two' ;
3894
prepare stmt1 from ' select a, b FROM t1 outer_table where
3895
a = (select a from t1 where outer_table.a = ? and a=2) and b=''two'' ' ;
3896
execute stmt1 using @arg00;
3903
select a, @arg00, b FROM t1 outer_table where
3904
b=@arg01 and a = (select @arg02 from t1 where outer_table.b = @arg03
3905
and outer_table.a=a ) ;
3908
prepare stmt1 from ' select a, ?, b FROM t1 outer_table where
3909
b=? and a = (select ? from t1 where outer_table.b = ?
3910
and outer_table.a=a ) ' ;
3911
execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
3917
from ( select a - @arg00 as a from t1 where a=@arg00 ) as t2
3921
prepare stmt1 from ' select a, ?
3922
from ( select a - ? as a from t1 where a=? ) as t2
3924
execute stmt1 using @arg00, @arg00, @arg00, @arg01 ;
3927
drop table if exists t2 ;
3928
create table t2 as select * from t1;
3929
prepare stmt1 from ' select a in (select a from t2) from t1 ' ;
3931
a in (select a from t2)
3936
drop table if exists t5, t6, t7 ;
3937
create table t5 (a int , b int) ;
3938
create table t6 like t5 ;
3939
create table t7 like t5 ;
3940
insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7),
3942
insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1) ;
3943
insert into t7 values (3, 3), (2, 2), (1, 1) ;
3944
prepare stmt1 from ' select a, (select count(distinct t5.b) as sum from t5, t6
3945
where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
3946
group by t5.a order by sum limit 1) from t7 ' ;
3948
a (select count(distinct t5.b) as sum from t5, t6
3949
where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
3950
group by t5.a order by sum limit 1)
3955
a (select count(distinct t5.b) as sum from t5, t6
3956
where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
3957
group by t5.a order by sum limit 1)
3962
a (select count(distinct t5.b) as sum from t5, t6
3963
where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
3964
group by t5.a order by sum limit 1)
3968
drop table t5, t6, t7 ;
3969
drop table if exists t2 ;
3970
create table t2 as select * from t9;
3972
(SELECT SUM(c1 + c12 + 0.0) FROM t2
3973
where (t9.c2 - 0e-3) = t2.c2
3974
GROUP BY t9.c15 LIMIT 1) as scalar_s,
3975
exists (select 1.0e+0 from t2
3976
where t2.c3 * 9.0000000000 = t9.c4) as exists_s,
3977
c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s,
3978
(c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s
3980
(select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
3981
prepare stmt1 from @stmt ;
3984
set @stmt= concat('explain ',@stmt);
3985
prepare stmt1 from @stmt ;
3989
(SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2
3990
GROUP BY t9.c15 LIMIT 1) as scalar_s,
3991
exists (select ? from t2
3992
where t2.c3*?=t9.c4) as exists_s,
3993
c5*? in (select c6+? from t2) as in_s,
3994
(c7-?, c8-?) in (select c9+?, c10+? from t2) as in_row_s
3996
(select c25 x, c32 y from t2) tt WHERE x =c25 ' ;
3999
set @arg02= 1.0e+0 ;
4000
set @arg03= 9.0000000000 ;
4002
set @arg05= 0.3e+1 ;
4007
prepare stmt1 from @stmt ;
4008
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
4009
@arg07, @arg08, @arg09 ;
4010
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
4011
@arg07, @arg08, @arg09 ;
4012
set @stmt= concat('explain ',@stmt);
4013
prepare stmt1 from @stmt ;
4014
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
4015
@arg07, @arg08, @arg09 ;
4016
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
4017
@arg07, @arg08, @arg09 ;
4019
select 1 < (select a from t1) ;
4020
ERROR 21000: Subquery returns more than 1 row
4021
prepare stmt1 from ' select 1 < (select a from t1) ' ;
4023
ERROR 21000: Subquery returns more than 1 row
4024
select 1 as my_col ;
4028
------ union tests ------
4029
prepare stmt1 from ' select a FROM t1 where a=1
4031
select a FROM t1 where a=1 ';
4038
prepare stmt1 from ' select a FROM t1 where a=1
4040
select a FROM t1 where a=1 ';
4045
prepare stmt1 from ' SELECT 1, 2 union SELECT 1 ' ;
4046
ERROR 21000: The used SELECT statements have a different number of columns
4047
prepare stmt1 from ' SELECT 1 union SELECT 1, 2 ' ;
4048
ERROR 21000: The used SELECT statements have a different number of columns
4049
prepare stmt1 from ' SELECT * from t1 union SELECT 1 ' ;
4050
ERROR 21000: The used SELECT statements have a different number of columns
4051
prepare stmt1 from ' SELECT 1 union SELECT * from t1 ' ;
4052
ERROR 21000: The used SELECT statements have a different number of columns
4054
select @arg00 FROM t1 where a=1
4056
select 1 FROM t1 where a=1;
4059
prepare stmt1 from ' select ? FROM t1 where a=1
4061
select 1 FROM t1 where a=1 ' ;
4062
execute stmt1 using @arg00;
4066
select 1 FROM t1 where a=1
4068
select @arg00 FROM t1 where a=1;
4071
prepare stmt1 from ' select 1 FROM t1 where a=1
4073
select ? FROM t1 where a=1 ' ;
4074
execute stmt1 using @arg00;
4078
select @arg00 FROM t1 where a=1
4080
select @arg00 FROM t1 where a=1;
4083
prepare stmt1 from ' select ? FROM t1 where a=1
4085
select ? FROM t1 where a=1 ';
4086
execute stmt1 using @arg00, @arg00;
4089
prepare stmt1 from ' select ?
4092
execute stmt1 using @arg00, @arg00;
4099
select @arg00 FROM t1 where a=@arg01
4101
select @arg02 FROM t1 where a=@arg03;
4104
prepare stmt1 from ' select ? FROM t1 where a=?
4106
select ? FROM t1 where a=? ' ;
4107
execute stmt1 using @arg00, @arg01, @arg02, @arg03;
4111
prepare stmt1 from ' select sum(a) + 200, ? from t1
4113
select sum(a) + 200, 1 from t1
4115
execute stmt1 using @arg00;
4122
set @Oporto='Oporto' ;
4123
set @Lisboa='Lisboa' ;
4129
select @Oporto,@Lisboa,@0,@1,@2,@3,@4 ;
4130
@Oporto @Lisboa @0 @1 @2 @3 @4
4131
Oporto Lisboa 0 1 2 3 4
4132
select sum(a) + 200 as the_sum, @Oporto as the_town from t1
4135
select sum(a) + 200, @Lisboa from t1
4146
prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
4149
select sum(a) + 200, ? from t1
4151
execute stmt1 using @Oporto, @Lisboa;
4161
select sum(a) + 200 as the_sum, @Oporto as the_town from t1
4165
select sum(a) + 200, @Lisboa from t1
4174
prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
4178
select sum(a) + 200, ? from t1
4181
execute stmt1 using @Oporto, @1, @Lisboa, @2;
4188
select sum(a) + 200 as the_sum, @Oporto as the_town from t1
4193
select sum(a) + 200, @Lisboa from t1
4201
prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
4206
select sum(a) + 200, ? from t1
4209
having avg(a) > ? ';
4210
execute stmt1 using @Oporto, @1, @2, @Lisboa, @2, @3;
4216
------ explain select tests ------
4217
prepare stmt1 from ' explain select * from t9 ' ;
4219
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
4220
def id 8 3 1 N 32929 0 63
4221
def select_type 253 19 6 N 1 31 8
4222
def table 253 64 2 Y 0 31 8
4223
def type 253 10 3 Y 0 31 8
4224
def possible_keys 253 4096 0 Y 0 31 8
4225
def key 253 64 0 Y 0 31 8
4226
def key_len 253 4096 0 Y 0 31 8
4227
def ref 253 1024 0 Y 0 31 8
4228
def rows 8 10 1 Y 32928 0 63
4229
def Extra 253 255 0 N 1 31 8
4230
id select_type table type possible_keys key key_len ref rows Extra
4231
1 SIMPLE t9 ALL NULL NULL NULL NULL 2
4233
------ delete tests ------
4235
insert into t1 values (1,'one');
4236
insert into t1 values (2,'two');
4237
insert into t1 values (3,'three');
4238
insert into t1 values (4,'four');
4242
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
4243
c10= 1, c11= 1, c12 = 1,
4244
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
4245
c16= '11:11:11', c17= '2004',
4246
c18= 1, c19=true, c20= 'a', c21= '123456789a',
4247
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
4248
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
4249
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
4251
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
4252
c10= 9, c11= 9, c12 = 9,
4253
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
4254
c16= '11:11:11', c17= '2004',
4255
c18= 1, c19=false, c20= 'a', c21= '123456789a',
4256
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
4257
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
4258
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
4260
prepare stmt1 from 'delete from t1 where a=2' ;
4262
select a,b from t1 where a=2;
4265
insert into t1 values(0,NULL);
4267
prepare stmt1 from 'delete from t1 where b=?' ;
4268
execute stmt1 using @arg00;
4269
select a,b from t1 where b is NULL ;
4273
execute stmt1 using @arg00;
4274
select a,b from t1 where b=@arg00;
4276
prepare stmt1 from 'truncate table t1' ;
4278
------ update tests ------
4280
insert into t1 values (1,'one');
4281
insert into t1 values (2,'two');
4282
insert into t1 values (3,'three');
4283
insert into t1 values (4,'four');
4287
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
4288
c10= 1, c11= 1, c12 = 1,
4289
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
4290
c16= '11:11:11', c17= '2004',
4291
c18= 1, c19=true, c20= 'a', c21= '123456789a',
4292
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
4293
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
4294
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
4296
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
4297
c10= 9, c11= 9, c12 = 9,
4298
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
4299
c16= '11:11:11', c17= '2004',
4300
c18= 1, c19=false, c20= 'a', c21= '123456789a',
4301
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
4302
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
4303
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
4305
prepare stmt1 from 'update t1 set b=''a=two'' where a=2' ;
4307
select a,b from t1 where a=2;
4311
select a,b from t1 where a=2;
4315
prepare stmt1 from 'update t1 set b=? where a=2' ;
4316
execute stmt1 using @arg00;
4317
select a,b from t1 where a=2;
4321
execute stmt1 using @arg00;
4322
select a,b from t1 where a=2;
4326
prepare stmt1 from 'update t1 set b=NULL where a=?' ;
4327
execute stmt1 using @arg00;
4328
select a,b from t1 where a=@arg00;
4331
update t1 set b='two' where a=@arg00;
4333
execute stmt1 using @arg00;
4334
select a,b from t1 where a=@arg00;
4338
prepare stmt1 from 'update t1 set a=? where a=?' ;
4339
execute stmt1 using @arg00, @arg00;
4340
select a,b from t1 where a=@arg00;
4343
execute stmt1 using @arg01, @arg00;
4344
select a,b from t1 where a=@arg01;
4347
execute stmt1 using @arg00, @arg01;
4348
select a,b from t1 where a=@arg00;
4353
execute stmt1 using @arg00, @arg01;
4355
Warning 1048 Column 'a' cannot be null
4356
select a,b from t1 order by a;
4363
execute stmt1 using @arg01, @arg00;
4364
select a,b from t1 order by a;
4375
drop table if exists t2;
4376
create table t2 as select a,b from t1 ;
4377
prepare stmt1 from 'update t1 set a=? where b=?
4378
and a in (select ? from t2
4379
where b = ? or a = ?)';
4380
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;
4382
info: Rows matched: 1 Changed: 1 Warnings: 0
4383
select a,b from t1 where a = @arg00 ;
4386
prepare stmt1 from 'update t1 set a=? where b=?
4387
and a not in (select ? from t2
4388
where b = ? or a = ?)';
4389
execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;
4391
info: Rows matched: 1 Changed: 1 Warnings: 0
4392
select a,b from t1 order by a ;
4401
a int, b varchar(30),
4403
) engine = 'MYISAM' ;
4404
insert into t2(a,b) select a, b from t1 ;
4405
prepare stmt1 from 'update t1 set a=? where b=?
4406
and a in (select ? from t2
4407
where b = ? or a = ?)';
4408
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;
4410
info: Rows matched: 1 Changed: 1 Warnings: 0
4411
select a,b from t1 where a = @arg00 ;
4414
prepare stmt1 from 'update t1 set a=? where b=?
4415
and a not in (select ? from t2
4416
where b = ? or a = ?)';
4417
execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;
4419
info: Rows matched: 1 Changed: 1 Warnings: 0
4420
select a,b from t1 order by a ;
4428
prepare stmt1 from 'update t1 set b=''bla''
4432
select a,b from t1 where b = 'bla' ;
4435
prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';
4436
execute stmt1 using @arg00;
4438
------ insert tests ------
4440
insert into t1 values (1,'one');
4441
insert into t1 values (2,'two');
4442
insert into t1 values (3,'three');
4443
insert into t1 values (4,'four');
4447
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
4448
c10= 1, c11= 1, c12 = 1,
4449
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
4450
c16= '11:11:11', c17= '2004',
4451
c18= 1, c19=true, c20= 'a', c21= '123456789a',
4452
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
4453
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
4454
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
4456
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
4457
c10= 9, c11= 9, c12 = 9,
4458
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
4459
c16= '11:11:11', c17= '2004',
4460
c18= 1, c19=false, c20= 'a', c21= '123456789a',
4461
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
4462
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
4463
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
4465
prepare stmt1 from 'insert into t1 values(5, ''five'' )';
4467
select a,b from t1 where a = 5;
4471
prepare stmt1 from 'insert into t1 values(6, ? )';
4472
execute stmt1 using @arg00;
4473
select a,b from t1 where b = @arg00;
4476
execute stmt1 using @arg00;
4477
ERROR 23000: Duplicate entry '6' for key 'PRIMARY'
4479
prepare stmt1 from 'insert into t1 values(0, ? )';
4480
execute stmt1 using @arg00;
4481
select a,b from t1 where b is NULL;
4485
set @arg01='eight' ;
4486
prepare stmt1 from 'insert into t1 values(?, ? )';
4487
execute stmt1 using @arg00, @arg01 ;
4488
select a,b from t1 where b = @arg01;
4493
execute stmt1 using @NULL, @NULL ;
4494
ERROR 23000: Column 'a' cannot be null
4495
execute stmt1 using @NULL, @NULL ;
4496
ERROR 23000: Column 'a' cannot be null
4497
execute stmt1 using @NULL, @arg00 ;
4498
ERROR 23000: Column 'a' cannot be null
4499
execute stmt1 using @NULL, @arg00 ;
4500
ERROR 23000: Column 'a' cannot be null
4501
set @arg01= 10000 + 2 ;
4502
execute stmt1 using @arg01, @arg00 ;
4503
set @arg01= 10000 + 1 ;
4504
execute stmt1 using @arg01, @arg00 ;
4505
select * from t1 where a > 10000 order by a ;
4509
delete from t1 where a > 10000 ;
4510
set @arg01= 10000 + 2 ;
4511
execute stmt1 using @arg01, @NULL ;
4512
set @arg01= 10000 + 1 ;
4513
execute stmt1 using @arg01, @NULL ;
4514
select * from t1 where a > 10000 order by a ;
4518
delete from t1 where a > 10000 ;
4519
set @arg01= 10000 + 10 ;
4520
execute stmt1 using @arg01, @arg01 ;
4521
set @arg01= 10000 + 9 ;
4522
execute stmt1 using @arg01, @arg01 ;
4523
set @arg01= 10000 + 8 ;
4524
execute stmt1 using @arg01, @arg01 ;
4525
set @arg01= 10000 + 7 ;
4526
execute stmt1 using @arg01, @arg01 ;
4527
set @arg01= 10000 + 6 ;
4528
execute stmt1 using @arg01, @arg01 ;
4529
set @arg01= 10000 + 5 ;
4530
execute stmt1 using @arg01, @arg01 ;
4531
set @arg01= 10000 + 4 ;
4532
execute stmt1 using @arg01, @arg01 ;
4533
set @arg01= 10000 + 3 ;
4534
execute stmt1 using @arg01, @arg01 ;
4535
set @arg01= 10000 + 2 ;
4536
execute stmt1 using @arg01, @arg01 ;
4537
set @arg01= 10000 + 1 ;
4538
execute stmt1 using @arg01, @arg01 ;
4539
select * from t1 where a > 10000 order by a ;
4551
delete from t1 where a > 10000 ;
4556
prepare stmt1 from 'insert into t1 values(?,?),(?,?)';
4557
execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
4558
select a,b from t1 where a in (@arg00,@arg02) ;
4564
prepare stmt1 from 'insert into t1 set a=?, b=? ';
4565
execute stmt1 using @arg00, @arg01 ;
4566
select a,b from t1 where a = @arg00 ;
4571
prepare stmt1 from 'insert into t1 set a=?, b=''sechs''
4572
on duplicate key update a=a + ?, b=concat(b,''modified'') ';
4573
execute stmt1 using @arg00, @arg01;
4574
select * from t1 order by a;
4589
execute stmt1 using @arg00, @arg01;
4590
ERROR 23000: Duplicate entry '82' for key 'PRIMARY'
4591
drop table if exists t2 ;
4592
create table t2 (id int auto_increment primary key)
4594
prepare stmt1 from ' select last_insert_id() ' ;
4595
insert into t2 values (NULL) ;
4599
insert into t2 values (NULL) ;
4605
set @x1000_2="x1000_2" ;
4606
set @x1000_3="x1000_3" ;
4607
set @x1000="x1000" ;
4609
set @x1100="x1100" ;
4611
set @updated="updated" ;
4612
insert into t1 values(1000,'x1000_1') ;
4613
insert into t1 values(@1000,@x1000_2),(@1000,@x1000_3)
4614
on duplicate key update a = a + @100, b = concat(b,@updated) ;
4615
select a,b from t1 where a >= 1000 order by a ;
4619
delete from t1 where a >= 1000 ;
4620
insert into t1 values(1000,'x1000_1') ;
4621
prepare stmt1 from ' insert into t1 values(?,?),(?,?)
4622
on duplicate key update a = a + ?, b = concat(b,?) ';
4623
execute stmt1 using @1000, @x1000_2, @1000, @x1000_3, @100, @updated ;
4624
select a,b from t1 where a >= 1000 order by a ;
4628
delete from t1 where a >= 1000 ;
4629
insert into t1 values(1000,'x1000_1') ;
4630
execute stmt1 using @1000, @x1000_2, @1100, @x1000_3, @100, @updated ;
4631
select a,b from t1 where a >= 1000 order by a ;
4633
1200 x1000_1updatedupdated
4634
delete from t1 where a >= 1000 ;
4635
prepare stmt1 from ' replace into t1 (a,b) select 100, ''hundred'' ';
4640
------ multi table tests ------
4643
insert into t1(a,b) values (1, 'one'), (2, 'two'), (3, 'three') ;
4644
insert into t9 (c1,c21)
4645
values (1, 'one'), (2, 'two'), (3, 'three') ;
4646
prepare stmt_delete from " delete t1, t9
4647
from t1, t9 where t1.a=t9.c1 and t1.b='updated' ";
4648
prepare stmt_update from " update t1, t9
4649
set t1.b='updated', t9.c21='updated'
4650
where t1.a=t9.c1 and t1.a=? ";
4651
prepare stmt_select1 from " select a, b from t1 order by a" ;
4652
prepare stmt_select2 from " select c1, c21 from t9 order by c1" ;
4654
execute stmt_update using @arg00 ;
4655
execute stmt_delete ;
4656
execute stmt_select1 ;
4660
execute stmt_select2 ;
4664
set @arg00= @arg00 + 1 ;
4665
execute stmt_update using @arg00 ;
4666
execute stmt_delete ;
4667
execute stmt_select1 ;
4670
execute stmt_select2 ;
4673
set @arg00= @arg00 + 1 ;
4674
execute stmt_update using @arg00 ;
4675
execute stmt_delete ;
4676
execute stmt_select1 ;
4678
execute stmt_select2 ;
4680
set @arg00= @arg00 + 1 ;
4681
drop table if exists t5 ;
4684
set @arg03= 80.00000000000e-1;
4686
set @arg05= CAST('abc' as binary) ;
4687
set @arg06= '1991-08-05' ;
4688
set @arg07= CAST('1991-08-05' as date);
4689
set @arg08= '1991-08-05 01:01:01' ;
4690
set @arg09= CAST('1991-08-05 01:01:01' as datetime) ;
4691
set @arg10= unix_timestamp('1991-01-01 01:01:01');
4692
set @arg11= YEAR('1991-01-01 01:01:01');
4699
set @arg15= CAST('abc' as binary) ;
4701
create table t5 as select
4702
8 as const01, @arg01 as param01,
4703
8.0 as const02, @arg02 as param02,
4704
80.00000000000e-1 as const03, @arg03 as param03,
4705
'abc' as const04, @arg04 as param04,
4706
CAST('abc' as binary) as const05, @arg05 as param05,
4707
'1991-08-05' as const06, @arg06 as param06,
4708
CAST('1991-08-05' as date) as const07, @arg07 as param07,
4709
'1991-08-05 01:01:01' as const08, @arg08 as param08,
4710
CAST('1991-08-05 01:01:01' as datetime) as const09, @arg09 as param09,
4711
unix_timestamp('1991-01-01 01:01:01') as const10, @arg10 as param10,
4712
YEAR('1991-01-01 01:01:01') as const11, @arg11 as param11,
4713
NULL as const12, @arg12 as param12,
4717
show create table t5 ;
4719
t5 CREATE TABLE `t5` (
4720
`const01` int(1) NOT NULL DEFAULT '0',
4721
`param01` bigint(20) DEFAULT NULL,
4722
`const02` decimal(2,1) NOT NULL DEFAULT '0.0',
4723
`param02` decimal(65,30) DEFAULT NULL,
4724
`const03` double NOT NULL DEFAULT '0',
4725
`param03` double DEFAULT NULL,
4726
`const04` varchar(3) NOT NULL DEFAULT '',
4728
`const05` varbinary(3) NOT NULL DEFAULT '',
4730
`const06` varchar(10) NOT NULL DEFAULT '',
4732
`const07` date DEFAULT NULL,
4734
`const08` varchar(19) NOT NULL DEFAULT '',
4736
`const09` datetime DEFAULT NULL,
4738
`const10` int(10) NOT NULL DEFAULT '0',
4739
`param10` bigint(20) DEFAULT NULL,
4740
`const11` int(4) DEFAULT NULL,
4741
`param11` bigint(20) DEFAULT NULL,
4742
`const12` binary(0) DEFAULT NULL,
4743
`param12` bigint(20) DEFAULT NULL,
4744
`param13` decimal(65,30) DEFAULT NULL,
4747
) ENGINE=MyISAM DEFAULT CHARSET=latin1
4749
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
4750
def test t5 t5 const01 const01 3 1 1 N 32769 0 63
4751
def test t5 t5 param01 param01 8 20 1 Y 32768 0 63
4752
def test t5 t5 const02 const02 246 4 3 N 1 1 63
4753
def test t5 t5 param02 param02 246 67 32 Y 0 30 63
4754
def test t5 t5 const03 const03 5 17 1 N 32769 31 63
4755
def test t5 t5 param03 param03 5 23 1 Y 32768 31 63
4756
def test t5 t5 const04 const04 253 3 3 N 1 0 8
4757
def test t5 t5 param04 param04 252 4294967295 3 Y 16 0 8
4758
def test t5 t5 const05 const05 253 3 3 N 129 0 63
4759
def test t5 t5 param05 param05 252 4294967295 3 Y 144 0 63
4760
def test t5 t5 const06 const06 253 10 10 N 1 0 8
4761
def test t5 t5 param06 param06 252 4294967295 10 Y 16 0 8
4762
def test t5 t5 const07 const07 10 10 10 Y 128 0 63
4763
def test t5 t5 param07 param07 252 4294967295 10 Y 144 0 63
4764
def test t5 t5 const08 const08 253 19 19 N 1 0 8
4765
def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8
4766
def test t5 t5 const09 const09 12 19 19 Y 128 0 63
4767
def test t5 t5 param09 param09 252 4294967295 19 Y 144 0 63
4768
def test t5 t5 const10 const10 3 10 9 N 32769 0 63
4769
def test t5 t5 param10 param10 8 20 9 Y 32768 0 63
4770
def test t5 t5 const11 const11 3 4 4 Y 32768 0 63
4771
def test t5 t5 param11 param11 8 20 4 Y 32768 0 63
4772
def test t5 t5 const12 const12 254 0 0 Y 128 0 63
4773
def test t5 t5 param12 param12 8 20 0 Y 32768 0 63
4774
def test t5 t5 param13 param13 246 67 0 Y 0 30 63
4775
def test t5 t5 param14 param14 252 4294967295 0 Y 16 0 8
4776
def test t5 t5 param15 param15 252 4294967295 0 Y 144 0 63
4780
param02 8.000000000000000000000000000000
4791
const08 1991-08-05 01:01:01
4792
param08 1991-08-05 01:01:01
4793
const09 1991-08-05 01:01:01
4794
param09 1991-08-05 01:01:01
4806
------ data type conversion tests ------
4808
insert into t1 values (1,'one');
4809
insert into t1 values (2,'two');
4810
insert into t1 values (3,'three');
4811
insert into t1 values (4,'four');
4815
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
4816
c10= 1, c11= 1, c12 = 1,
4817
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
4818
c16= '11:11:11', c17= '2004',
4819
c18= 1, c19=true, c20= 'a', c21= '123456789a',
4820
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
4821
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
4822
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
4824
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
4825
c10= 9, c11= 9, c12 = 9,
4826
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
4827
c16= '11:11:11', c17= '2004',
4828
c18= 1, c19=false, c20= 'a', c21= '123456789a',
4829
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
4830
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
4831
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
4833
insert into t9 set c1= 0, c15= '1991-01-01 01:01:01' ;
4834
select * from t9 order by c1 ;
4835
c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32
4836
0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
4837
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
4838
9 9 9 9 9 9 9 9 9 9 9.0000 9.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 0 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext two tuesday
4840
------ select @parameter:= column ------
4841
prepare full_info from "select @arg01, @arg02, @arg03, @arg04,
4842
@arg05, @arg06, @arg07, @arg08,
4843
@arg09, @arg10, @arg11, @arg12,
4844
@arg13, @arg14, @arg15, @arg16,
4845
@arg17, @arg18, @arg19, @arg20,
4846
@arg21, @arg22, @arg23, @arg24,
4847
@arg25, @arg26, @arg27, @arg28,
4848
@arg29, @arg30, @arg31, @arg32" ;
4849
select @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,
4850
@arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,
4851
@arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
4852
@arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
4853
@arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
4854
@arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
4855
@arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
4856
@arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
4857
from t9 where c1= 1 ;
4858
@arg01:= c1 @arg02:= c2 @arg03:= c3 @arg04:= c4 @arg05:= c5 @arg06:= c6 @arg07:= c7 @arg08:= c8 @arg09:= c9 @arg10:= c10 @arg11:= c11 @arg12:= c12 @arg13:= c13 @arg14:= c14 @arg15:= c15 @arg16:= c16 @arg17:= c17 @arg18:= c18 @arg19:= c19 @arg20:= c20 @arg21:= c21 @arg22:= c22 @arg23:= c23 @arg24:= c24 @arg25:= c25 @arg26:= c26 @arg27:= c27 @arg28:= c28 @arg29:= c29 @arg30:= c30 @arg31:= c31 @arg32:= c32
4859
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
4861
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
4862
def @arg01 8 20 1 Y 32896 0 63
4863
def @arg02 8 20 1 Y 32896 0 63
4864
def @arg03 8 20 1 Y 32896 0 63
4865
def @arg04 8 20 1 Y 32896 0 63
4866
def @arg05 8 20 1 Y 32896 0 63
4867
def @arg06 8 20 1 Y 32896 0 63
4868
def @arg07 5 23 1 Y 32896 31 63
4869
def @arg08 5 23 1 Y 32896 31 63
4870
def @arg09 5 23 1 Y 32896 31 63
4871
def @arg10 5 23 1 Y 32896 31 63
4872
def @arg11 246 83 6 Y 128 30 63
4873
def @arg12 246 83 6 Y 128 30 63
4874
def @arg13 251 16777216 10 Y 128 31 63
4875
def @arg14 251 16777216 19 Y 128 31 63
4876
def @arg15 251 16777216 19 Y 128 31 63
4877
def @arg16 251 16777216 8 Y 128 31 63
4878
def @arg17 8 20 4 Y 32928 0 63
4879
def @arg18 8 20 1 Y 32896 0 63
4880
def @arg19 8 20 1 Y 32896 0 63
4881
def @arg20 251 16777216 1 Y 0 31 8
4882
def @arg21 251 16777216 10 Y 0 31 8
4883
def @arg22 251 16777216 30 Y 0 31 8
4884
def @arg23 251 16777216 8 Y 128 31 63
4885
def @arg24 251 16777216 8 Y 0 31 8
4886
def @arg25 251 16777216 4 Y 128 31 63
4887
def @arg26 251 16777216 4 Y 0 31 8
4888
def @arg27 251 16777216 10 Y 128 31 63
4889
def @arg28 251 16777216 10 Y 0 31 8
4890
def @arg29 251 16777216 8 Y 128 31 63
4891
def @arg30 251 16777216 8 Y 0 31 8
4892
def @arg31 251 16777216 3 Y 0 31 8
4893
def @arg32 251 16777216 6 Y 0 31 8
4894
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
4895
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
4896
select @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,
4897
@arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,
4898
@arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
4899
@arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
4900
@arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
4901
@arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
4902
@arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
4903
@arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
4904
from t9 where c1= 0 ;
4905
@arg01:= c1 @arg02:= c2 @arg03:= c3 @arg04:= c4 @arg05:= c5 @arg06:= c6 @arg07:= c7 @arg08:= c8 @arg09:= c9 @arg10:= c10 @arg11:= c11 @arg12:= c12 @arg13:= c13 @arg14:= c14 @arg15:= c15 @arg16:= c16 @arg17:= c17 @arg18:= c18 @arg19:= c19 @arg20:= c20 @arg21:= c21 @arg22:= c22 @arg23:= c23 @arg24:= c24 @arg25:= c25 @arg26:= c26 @arg27:= c27 @arg28:= c28 @arg29:= c29 @arg30:= c30 @arg31:= c31 @arg32:= c32
4906
0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
4908
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
4909
def @arg01 8 20 1 Y 32896 0 63
4910
def @arg02 8 20 0 Y 32896 0 63
4911
def @arg03 8 20 0 Y 32896 0 63
4912
def @arg04 8 20 0 Y 32896 0 63
4913
def @arg05 8 20 0 Y 32896 0 63
4914
def @arg06 8 20 0 Y 32896 0 63
4915
def @arg07 5 23 0 Y 32896 31 63
4916
def @arg08 5 23 0 Y 32896 31 63
4917
def @arg09 5 23 0 Y 32896 31 63
4918
def @arg10 5 23 0 Y 32896 31 63
4919
def @arg11 246 83 0 Y 128 30 63
4920
def @arg12 246 83 0 Y 128 30 63
4921
def @arg13 251 16777216 0 Y 128 31 63
4922
def @arg14 251 16777216 0 Y 128 31 63
4923
def @arg15 251 16777216 19 Y 128 31 63
4924
def @arg16 251 16777216 0 Y 128 31 63
4925
def @arg17 8 20 0 Y 32928 0 63
4926
def @arg18 8 20 0 Y 32896 0 63
4927
def @arg19 8 20 0 Y 32896 0 63
4928
def @arg20 251 16777216 0 Y 0 31 8
4929
def @arg21 251 16777216 0 Y 0 31 8
4930
def @arg22 251 16777216 0 Y 0 31 8
4931
def @arg23 251 16777216 0 Y 128 31 63
4932
def @arg24 251 16777216 0 Y 0 31 8
4933
def @arg25 251 16777216 0 Y 128 31 63
4934
def @arg26 251 16777216 0 Y 0 31 8
4935
def @arg27 251 16777216 0 Y 128 31 63
4936
def @arg28 251 16777216 0 Y 0 31 8
4937
def @arg29 251 16777216 0 Y 128 31 63
4938
def @arg30 251 16777216 0 Y 0 31 8
4939
def @arg31 251 16777216 0 Y 0 31 8
4940
def @arg32 251 16777216 0 Y 0 31 8
4941
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
4942
0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
4943
prepare stmt1 from "select
4944
@arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,
4945
@arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,
4946
@arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
4947
@arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
4948
@arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
4949
@arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
4950
@arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
4951
@arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
4952
from t9 where c1= ?" ;
4954
execute stmt1 using @my_key ;
4955
@arg01:= c1 @arg02:= c2 @arg03:= c3 @arg04:= c4 @arg05:= c5 @arg06:= c6 @arg07:= c7 @arg08:= c8 @arg09:= c9 @arg10:= c10 @arg11:= c11 @arg12:= c12 @arg13:= c13 @arg14:= c14 @arg15:= c15 @arg16:= c16 @arg17:= c17 @arg18:= c18 @arg19:= c19 @arg20:= c20 @arg21:= c21 @arg22:= c22 @arg23:= c23 @arg24:= c24 @arg25:= c25 @arg26:= c26 @arg27:= c27 @arg28:= c28 @arg29:= c29 @arg30:= c30 @arg31:= c31 @arg32:= c32
4956
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
4958
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
4959
def @arg01 8 20 1 Y 32896 0 63
4960
def @arg02 8 20 1 Y 32896 0 63
4961
def @arg03 8 20 1 Y 32896 0 63
4962
def @arg04 8 20 1 Y 32896 0 63
4963
def @arg05 8 20 1 Y 32896 0 63
4964
def @arg06 8 20 1 Y 32896 0 63
4965
def @arg07 5 23 1 Y 32896 31 63
4966
def @arg08 5 23 1 Y 32896 31 63
4967
def @arg09 5 23 1 Y 32896 31 63
4968
def @arg10 5 23 1 Y 32896 31 63
4969
def @arg11 246 83 6 Y 128 30 63
4970
def @arg12 246 83 6 Y 128 30 63
4971
def @arg13 251 16777216 10 Y 128 31 63
4972
def @arg14 251 16777216 19 Y 128 31 63
4973
def @arg15 251 16777216 19 Y 128 31 63
4974
def @arg16 251 16777216 8 Y 128 31 63
4975
def @arg17 8 20 4 Y 32928 0 63
4976
def @arg18 8 20 1 Y 32896 0 63
4977
def @arg19 8 20 1 Y 32896 0 63
4978
def @arg20 251 16777216 1 Y 0 31 8
4979
def @arg21 251 16777216 10 Y 0 31 8
4980
def @arg22 251 16777216 30 Y 0 31 8
4981
def @arg23 251 16777216 8 Y 128 31 63
4982
def @arg24 251 16777216 8 Y 0 31 8
4983
def @arg25 251 16777216 4 Y 128 31 63
4984
def @arg26 251 16777216 4 Y 0 31 8
4985
def @arg27 251 16777216 10 Y 128 31 63
4986
def @arg28 251 16777216 10 Y 0 31 8
4987
def @arg29 251 16777216 8 Y 128 31 63
4988
def @arg30 251 16777216 8 Y 0 31 8
4989
def @arg31 251 16777216 3 Y 0 31 8
4990
def @arg32 251 16777216 6 Y 0 31 8
4991
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
4992
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
4994
execute stmt1 using @my_key ;
4995
@arg01:= c1 @arg02:= c2 @arg03:= c3 @arg04:= c4 @arg05:= c5 @arg06:= c6 @arg07:= c7 @arg08:= c8 @arg09:= c9 @arg10:= c10 @arg11:= c11 @arg12:= c12 @arg13:= c13 @arg14:= c14 @arg15:= c15 @arg16:= c16 @arg17:= c17 @arg18:= c18 @arg19:= c19 @arg20:= c20 @arg21:= c21 @arg22:= c22 @arg23:= c23 @arg24:= c24 @arg25:= c25 @arg26:= c26 @arg27:= c27 @arg28:= c28 @arg29:= c29 @arg30:= c30 @arg31:= c31 @arg32:= c32
4996
0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
4998
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
4999
def @arg01 8 20 1 Y 32896 0 63
5000
def @arg02 8 20 0 Y 32896 0 63
5001
def @arg03 8 20 0 Y 32896 0 63
5002
def @arg04 8 20 0 Y 32896 0 63
5003
def @arg05 8 20 0 Y 32896 0 63
5004
def @arg06 8 20 0 Y 32896 0 63
5005
def @arg07 5 23 0 Y 32896 31 63
5006
def @arg08 5 23 0 Y 32896 31 63
5007
def @arg09 5 23 0 Y 32896 31 63
5008
def @arg10 5 23 0 Y 32896 31 63
5009
def @arg11 246 83 0 Y 128 30 63
5010
def @arg12 246 83 0 Y 128 30 63
5011
def @arg13 251 16777216 0 Y 128 31 63
5012
def @arg14 251 16777216 0 Y 128 31 63
5013
def @arg15 251 16777216 19 Y 128 31 63
5014
def @arg16 251 16777216 0 Y 128 31 63
5015
def @arg17 8 20 0 Y 32928 0 63
5016
def @arg18 8 20 0 Y 32896 0 63
5017
def @arg19 8 20 0 Y 32896 0 63
5018
def @arg20 251 16777216 0 Y 0 31 8
5019
def @arg21 251 16777216 0 Y 0 31 8
5020
def @arg22 251 16777216 0 Y 0 31 8
5021
def @arg23 251 16777216 0 Y 128 31 63
5022
def @arg24 251 16777216 0 Y 0 31 8
5023
def @arg25 251 16777216 0 Y 128 31 63
5024
def @arg26 251 16777216 0 Y 0 31 8
5025
def @arg27 251 16777216 0 Y 128 31 63
5026
def @arg28 251 16777216 0 Y 0 31 8
5027
def @arg29 251 16777216 0 Y 128 31 63
5028
def @arg30 251 16777216 0 Y 0 31 8
5029
def @arg31 251 16777216 0 Y 0 31 8
5030
def @arg32 251 16777216 0 Y 0 31 8
5031
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
5032
0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5033
prepare stmt1 from "select ? := c1 from t9 where c1= 1" ;
5034
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= c1 from t9 where c1= 1' at line 1
5036
------ select column, .. into @parm,.. ------
5037
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
5038
c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
5039
c25, c26, c27, c28, c29, c30, c31, c32
5040
into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
5041
@arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
5042
@arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
5043
@arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
5044
from t9 where c1= 1 ;
5046
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
5047
def @arg01 8 20 1 Y 32896 0 63
5048
def @arg02 8 20 1 Y 32896 0 63
5049
def @arg03 8 20 1 Y 32896 0 63
5050
def @arg04 8 20 1 Y 32896 0 63
5051
def @arg05 8 20 1 Y 32896 0 63
5052
def @arg06 8 20 1 Y 32896 0 63
5053
def @arg07 5 23 1 Y 32896 31 63
5054
def @arg08 5 23 1 Y 32896 31 63
5055
def @arg09 5 23 1 Y 32896 31 63
5056
def @arg10 5 23 1 Y 32896 31 63
5057
def @arg11 246 83 6 Y 128 30 63
5058
def @arg12 246 83 6 Y 128 30 63
5059
def @arg13 251 16777216 10 Y 128 31 63
5060
def @arg14 251 16777216 19 Y 128 31 63
5061
def @arg15 251 16777216 19 Y 128 31 63
5062
def @arg16 251 16777216 8 Y 128 31 63
5063
def @arg17 8 20 4 Y 32928 0 63
5064
def @arg18 8 20 1 Y 32896 0 63
5065
def @arg19 8 20 1 Y 32896 0 63
5066
def @arg20 251 16777216 1 Y 0 31 8
5067
def @arg21 251 16777216 10 Y 0 31 8
5068
def @arg22 251 16777216 30 Y 0 31 8
5069
def @arg23 251 16777216 8 Y 128 31 63
5070
def @arg24 251 16777216 8 Y 0 31 8
5071
def @arg25 251 16777216 4 Y 128 31 63
5072
def @arg26 251 16777216 4 Y 0 31 8
5073
def @arg27 251 16777216 10 Y 128 31 63
5074
def @arg28 251 16777216 10 Y 0 31 8
5075
def @arg29 251 16777216 8 Y 128 31 63
5076
def @arg30 251 16777216 8 Y 0 31 8
5077
def @arg31 251 16777216 3 Y 0 31 8
5078
def @arg32 251 16777216 6 Y 0 31 8
5079
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
5080
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
5081
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
5082
c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
5083
c25, c26, c27, c28, c29, c30, c31, c32
5084
into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
5085
@arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
5086
@arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
5087
@arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
5088
from t9 where c1= 0 ;
5090
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
5091
def @arg01 8 20 1 Y 32896 0 63
5092
def @arg02 8 20 0 Y 32896 0 63
5093
def @arg03 8 20 0 Y 32896 0 63
5094
def @arg04 8 20 0 Y 32896 0 63
5095
def @arg05 8 20 0 Y 32896 0 63
5096
def @arg06 8 20 0 Y 32896 0 63
5097
def @arg07 5 23 0 Y 32896 31 63
5098
def @arg08 5 23 0 Y 32896 31 63
5099
def @arg09 5 23 0 Y 32896 31 63
5100
def @arg10 5 23 0 Y 32896 31 63
5101
def @arg11 246 83 0 Y 128 30 63
5102
def @arg12 246 83 0 Y 128 30 63
5103
def @arg13 251 16777216 0 Y 128 31 63
5104
def @arg14 251 16777216 0 Y 128 31 63
5105
def @arg15 251 16777216 19 Y 128 31 63
5106
def @arg16 251 16777216 0 Y 128 31 63
5107
def @arg17 8 20 0 Y 32928 0 63
5108
def @arg18 8 20 0 Y 32896 0 63
5109
def @arg19 8 20 0 Y 32896 0 63
5110
def @arg20 251 16777216 0 Y 0 31 8
5111
def @arg21 251 16777216 0 Y 0 31 8
5112
def @arg22 251 16777216 0 Y 0 31 8
5113
def @arg23 251 16777216 0 Y 128 31 63
5114
def @arg24 251 16777216 0 Y 0 31 8
5115
def @arg25 251 16777216 0 Y 128 31 63
5116
def @arg26 251 16777216 0 Y 0 31 8
5117
def @arg27 251 16777216 0 Y 128 31 63
5118
def @arg28 251 16777216 0 Y 0 31 8
5119
def @arg29 251 16777216 0 Y 128 31 63
5120
def @arg30 251 16777216 0 Y 0 31 8
5121
def @arg31 251 16777216 0 Y 0 31 8
5122
def @arg32 251 16777216 0 Y 0 31 8
5123
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
5124
0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5125
prepare stmt1 from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
5126
c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
5127
c25, c26, c27, c28, c29, c30, c31, c32
5128
into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
5129
@arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
5130
@arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
5131
@arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
5132
from t9 where c1= ?" ;
5134
execute stmt1 using @my_key ;
5136
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
5137
def @arg01 8 20 1 Y 32896 0 63
5138
def @arg02 8 20 1 Y 32896 0 63
5139
def @arg03 8 20 1 Y 32896 0 63
5140
def @arg04 8 20 1 Y 32896 0 63
5141
def @arg05 8 20 1 Y 32896 0 63
5142
def @arg06 8 20 1 Y 32896 0 63
5143
def @arg07 5 23 1 Y 32896 31 63
5144
def @arg08 5 23 1 Y 32896 31 63
5145
def @arg09 5 23 1 Y 32896 31 63
5146
def @arg10 5 23 1 Y 32896 31 63
5147
def @arg11 246 83 6 Y 128 30 63
5148
def @arg12 246 83 6 Y 128 30 63
5149
def @arg13 251 16777216 10 Y 128 31 63
5150
def @arg14 251 16777216 19 Y 128 31 63
5151
def @arg15 251 16777216 19 Y 128 31 63
5152
def @arg16 251 16777216 8 Y 128 31 63
5153
def @arg17 8 20 4 Y 32928 0 63
5154
def @arg18 8 20 1 Y 32896 0 63
5155
def @arg19 8 20 1 Y 32896 0 63
5156
def @arg20 251 16777216 1 Y 0 31 8
5157
def @arg21 251 16777216 10 Y 0 31 8
5158
def @arg22 251 16777216 30 Y 0 31 8
5159
def @arg23 251 16777216 8 Y 128 31 63
5160
def @arg24 251 16777216 8 Y 0 31 8
5161
def @arg25 251 16777216 4 Y 128 31 63
5162
def @arg26 251 16777216 4 Y 0 31 8
5163
def @arg27 251 16777216 10 Y 128 31 63
5164
def @arg28 251 16777216 10 Y 0 31 8
5165
def @arg29 251 16777216 8 Y 128 31 63
5166
def @arg30 251 16777216 8 Y 0 31 8
5167
def @arg31 251 16777216 3 Y 0 31 8
5168
def @arg32 251 16777216 6 Y 0 31 8
5169
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
5170
1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
5172
execute stmt1 using @my_key ;
5174
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
5175
def @arg01 8 20 1 Y 32896 0 63
5176
def @arg02 8 20 0 Y 32896 0 63
5177
def @arg03 8 20 0 Y 32896 0 63
5178
def @arg04 8 20 0 Y 32896 0 63
5179
def @arg05 8 20 0 Y 32896 0 63
5180
def @arg06 8 20 0 Y 32896 0 63
5181
def @arg07 5 23 0 Y 32896 31 63
5182
def @arg08 5 23 0 Y 32896 31 63
5183
def @arg09 5 23 0 Y 32896 31 63
5184
def @arg10 5 23 0 Y 32896 31 63
5185
def @arg11 246 83 0 Y 128 30 63
5186
def @arg12 246 83 0 Y 128 30 63
5187
def @arg13 251 16777216 0 Y 128 31 63
5188
def @arg14 251 16777216 0 Y 128 31 63
5189
def @arg15 251 16777216 19 Y 128 31 63
5190
def @arg16 251 16777216 0 Y 128 31 63
5191
def @arg17 8 20 0 Y 32928 0 63
5192
def @arg18 8 20 0 Y 32896 0 63
5193
def @arg19 8 20 0 Y 32896 0 63
5194
def @arg20 251 16777216 0 Y 0 31 8
5195
def @arg21 251 16777216 0 Y 0 31 8
5196
def @arg22 251 16777216 0 Y 0 31 8
5197
def @arg23 251 16777216 0 Y 128 31 63
5198
def @arg24 251 16777216 0 Y 0 31 8
5199
def @arg25 251 16777216 0 Y 128 31 63
5200
def @arg26 251 16777216 0 Y 0 31 8
5201
def @arg27 251 16777216 0 Y 128 31 63
5202
def @arg28 251 16777216 0 Y 0 31 8
5203
def @arg29 251 16777216 0 Y 128 31 63
5204
def @arg30 251 16777216 0 Y 0 31 8
5205
def @arg31 251 16777216 0 Y 0 31 8
5206
def @arg32 251 16777216 0 Y 0 31 8
5207
@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32
5208
0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5209
prepare stmt1 from "select c1 into ? from t9 where c1= 1" ;
5210
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? from t9 where c1= 1' at line 1
5212
-- insert into numeric columns --
5214
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5216
( 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20 ) ;
5219
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5221
( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5222
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
5223
prepare stmt1 from "insert into t9
5224
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5226
( 22, 22, 22, 22, 22, 22, 22, 22, 22, 22, 22 )" ;
5229
prepare stmt2 from "insert into t9
5230
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5232
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
5233
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5234
@arg00, @arg00, @arg00, @arg00 ;
5236
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5238
( 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0,
5239
30.0, 30.0, 30.0 ) ;
5242
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5244
( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5245
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
5246
prepare stmt1 from "insert into t9
5247
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5249
( 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0,
5250
32.0, 32.0, 32.0 )" ;
5253
prepare stmt2 from "insert into t9
5254
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5256
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
5257
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5258
@arg00, @arg00, @arg00, @arg00 ;
5260
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5262
( '40', '40', '40', '40', '40', '40', '40', '40',
5263
'40', '40', '40' ) ;
5266
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5268
( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5269
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
5270
prepare stmt1 from "insert into t9
5271
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5273
( '42', '42', '42', '42', '42', '42', '42', '42',
5274
'42', '42', '42' )" ;
5277
prepare stmt2 from "insert into t9
5278
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5280
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
5281
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5282
@arg00, @arg00, @arg00, @arg00 ;
5284
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5286
( CAST('50' as binary), CAST('50' as binary),
5287
CAST('50' as binary), CAST('50' as binary), CAST('50' as binary),
5288
CAST('50' as binary), CAST('50' as binary), CAST('50' as binary),
5289
CAST('50' as binary), CAST('50' as binary), CAST('50' as binary) ) ;
5290
set @arg00= CAST('51' as binary) ;
5292
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5294
( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5295
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
5296
prepare stmt1 from "insert into t9
5297
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5299
( CAST('52' as binary), CAST('52' as binary),
5300
CAST('52' as binary), CAST('52' as binary), CAST('52' as binary),
5301
CAST('52' as binary), CAST('52' as binary), CAST('52' as binary),
5302
CAST('52' as binary), CAST('52' as binary), CAST('52' as binary) )" ;
5304
set @arg00= CAST('53' as binary) ;
5305
prepare stmt2 from "insert into t9
5306
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5308
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
5309
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5310
@arg00, @arg00, @arg00, @arg00 ;
5314
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5316
( 60, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
5317
NULL, NULL, NULL ) ;
5319
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5321
( 61, @arg00, @arg00, @arg00, @arg00, @arg00,
5322
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
5323
prepare stmt1 from "insert into t9
5324
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5326
( 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
5327
NULL, NULL, NULL )" ;
5329
prepare stmt2 from "insert into t9
5330
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5332
( 63, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
5333
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5334
@arg00, @arg00, @arg00, @arg00 ;
5338
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5340
( 71, @arg00, @arg00, @arg00, @arg00, @arg00,
5341
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
5342
prepare stmt2 from "insert into t9
5343
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5345
( 73, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
5346
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5347
@arg00, @arg00, @arg00, @arg00 ;
5351
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5353
( 81, @arg00, @arg00, @arg00, @arg00, @arg00,
5354
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
5355
prepare stmt2 from "insert into t9
5356
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5358
( 83, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
5359
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5360
@arg00, @arg00, @arg00, @arg00 ;
5361
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12
5362
from t9 where c1 >= 20
5364
c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c12
5365
20 20 20 20 20 20 20 20 20 20 20.0000
5366
21 21 21 21 21 21 21 21 21 21 21.0000
5367
22 22 22 22 22 22 22 22 22 22 22.0000
5368
23 23 23 23 23 23 23 23 23 23 23.0000
5369
30 30 30 30 30 30 30 30 30 30 30.0000
5370
31 31 31 31 31 31 31 31 31 31 31.0000
5371
32 32 32 32 32 32 32 32 32 32 32.0000
5372
33 33 33 33 33 33 33 33 33 33 33.0000
5373
40 40 40 40 40 40 40 40 40 40 40.0000
5374
41 41 41 41 41 41 41 41 41 41 41.0000
5375
42 42 42 42 42 42 42 42 42 42 42.0000
5376
43 43 43 43 43 43 43 43 43 43 43.0000
5377
50 50 50 50 50 50 50 50 50 50 50.0000
5378
51 51 51 51 51 51 51 51 51 51 51.0000
5379
52 52 52 52 52 52 52 52 52 52 52.0000
5380
53 53 53 53 53 53 53 53 53 53 53.0000
5381
60 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5382
61 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5383
62 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5384
63 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5385
71 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5386
73 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5387
81 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5388
83 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5390
-- select .. where numeric column = .. --
5392
select 'true' as found from t9
5393
where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20
5394
and c8= 20 and c9= 20 and c10= 20 and c12= 20;
5397
select 'true' as found from t9
5398
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
5399
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
5403
prepare stmt1 from "select 'true' as found from t9
5404
where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20
5405
and c8= 20 and c9= 20 and c10= 20 and c12= 20 ";
5409
prepare stmt1 from "select 'true' as found from t9
5410
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
5411
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
5413
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5414
@arg00, @arg00, @arg00, @arg00 ;
5418
select 'true' as found from t9
5419
where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0
5420
and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0;
5423
select 'true' as found from t9
5424
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
5425
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
5429
prepare stmt1 from "select 'true' as found from t9
5430
where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0
5431
and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0 ";
5435
prepare stmt1 from "select 'true' as found from t9
5436
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
5437
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
5439
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5440
@arg00, @arg00, @arg00, @arg00 ;
5443
select 'true' as found from t9
5444
where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'
5445
and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20';
5448
prepare stmt1 from "select 'true' as found from t9
5449
where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'
5450
and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20' ";
5455
select 'true' as found from t9
5456
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
5457
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
5461
prepare stmt1 from "select 'true' as found from t9
5462
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
5463
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
5465
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5466
@arg00, @arg00, @arg00, @arg00 ;
5469
select 'true' as found from t9
5470
where c1= CAST('20' as binary) and c2= CAST('20' as binary) and
5471
c3= CAST('20' as binary) and c4= CAST('20' as binary) and
5472
c5= CAST('20' as binary) and c6= CAST('20' as binary) and
5473
c7= CAST('20' as binary) and c8= CAST('20' as binary) and
5474
c9= CAST('20' as binary) and c10= CAST('20' as binary) and
5475
c12= CAST('20' as binary);
5478
prepare stmt1 from "select 'true' as found from t9
5479
where c1= CAST('20' as binary) and c2= CAST('20' as binary) and
5480
c3= CAST('20' as binary) and c4= CAST('20' as binary) and
5481
c5= CAST('20' as binary) and c6= CAST('20' as binary) and
5482
c7= CAST('20' as binary) and c8= CAST('20' as binary) and
5483
c9= CAST('20' as binary) and c10= CAST('20' as binary) and
5484
c12= CAST('20' as binary) ";
5488
set @arg00= CAST('20' as binary) ;
5489
select 'true' as found from t9
5490
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
5491
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
5495
prepare stmt1 from "select 'true' as found from t9
5496
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
5497
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
5499
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5500
@arg00, @arg00, @arg00, @arg00 ;
5505
-- some numeric overflow experiments --
5506
prepare my_insert from "insert into t9
5507
( c21, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
5509
( 'O', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
5510
prepare my_select from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12
5511
from t9 where c21 = 'O' ";
5512
prepare my_delete from "delete from t9 where c21 = 'O' ";
5513
set @arg00= 9223372036854775807 ;
5514
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5515
@arg00, @arg00, @arg00, @arg00, @arg00 ;
5517
Warning 1264 Out of range value for column 'c1' at row 1
5518
Warning 1264 Out of range value for column 'c2' at row 1
5519
Warning 1264 Out of range value for column 'c3' at row 1
5520
Warning 1264 Out of range value for column 'c4' at row 1
5521
Warning 1264 Out of range value for column 'c5' at row 1
5522
Warning 1264 Out of range value for column 'c12' at row 1
5529
c6 9223372036854775807
5531
c8 9.22337203685478e+18
5532
c9 9.22337203685478e+18
5533
c10 9.22337203685478e+18
5536
set @arg00= '9223372036854775807' ;
5537
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5538
@arg00, @arg00, @arg00, @arg00, @arg00 ;
5540
Warning 1264 Out of range value for column 'c1' at row 1
5541
Warning 1264 Out of range value for column 'c2' at row 1
5542
Warning 1264 Out of range value for column 'c3' at row 1
5543
Warning 1264 Out of range value for column 'c4' at row 1
5544
Warning 1264 Out of range value for column 'c5' at row 1
5545
Warning 1264 Out of range value for column 'c12' at row 1
5552
c6 9223372036854775807
5554
c8 9.22337203685478e+18
5555
c9 9.22337203685478e+18
5556
c10 9.22337203685478e+18
5559
set @arg00= -9223372036854775808 ;
5560
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5561
@arg00, @arg00, @arg00, @arg00, @arg00 ;
5563
Warning 1264 Out of range value for column 'c1' at row 1
5564
Warning 1264 Out of range value for column 'c2' at row 1
5565
Warning 1264 Out of range value for column 'c3' at row 1
5566
Warning 1264 Out of range value for column 'c4' at row 1
5567
Warning 1264 Out of range value for column 'c5' at row 1
5568
Warning 1264 Out of range value for column 'c12' at row 1
5575
c6 -9223372036854775808
5577
c8 -9.22337203685478e+18
5578
c9 -9.22337203685478e+18
5579
c10 -9.22337203685478e+18
5582
set @arg00= '-9223372036854775808' ;
5583
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5584
@arg00, @arg00, @arg00, @arg00, @arg00 ;
5586
Warning 1264 Out of range value for column 'c1' at row 1
5587
Warning 1264 Out of range value for column 'c2' at row 1
5588
Warning 1264 Out of range value for column 'c3' at row 1
5589
Warning 1264 Out of range value for column 'c4' at row 1
5590
Warning 1264 Out of range value for column 'c5' at row 1
5591
Warning 1264 Out of range value for column 'c12' at row 1
5598
c6 -9223372036854775808
5600
c8 -9.22337203685478e+18
5601
c9 -9.22337203685478e+18
5602
c10 -9.22337203685478e+18
5605
set @arg00= 1.11111111111111111111e+50 ;
5606
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5607
@arg00, @arg00, @arg00, @arg00, @arg00 ;
5609
Warning 1264 Out of range value for column 'c1' at row 1
5610
Warning 1264 Out of range value for column 'c2' at row 1
5611
Warning 1264 Out of range value for column 'c3' at row 1
5612
Warning 1264 Out of range value for column 'c4' at row 1
5613
Warning 1264 Out of range value for column 'c5' at row 1
5614
Warning 1264 Out of range value for column 'c6' at row 1
5615
Warning 1264 Out of range value for column 'c7' at row 1
5616
Warning 1264 Out of range value for column 'c12' at row 1
5623
c6 9223372036854775807
5625
c8 1.11111111111111e+50
5626
c9 1.11111111111111e+50
5627
c10 1.11111111111111e+50
5630
set @arg00= '1.11111111111111111111e+50' ;
5631
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5632
@arg00, @arg00, @arg00, @arg00, @arg00 ;
5634
Warning 1264 Out of range value for column 'c1' at row 1
5635
Warning 1264 Out of range value for column 'c2' at row 1
5636
Warning 1264 Out of range value for column 'c3' at row 1
5637
Warning 1264 Out of range value for column 'c4' at row 1
5638
Warning 1264 Out of range value for column 'c5' at row 1
5639
Warning 1264 Out of range value for column 'c6' at row 1
5640
Warning 1264 Out of range value for column 'c7' at row 1
5641
Warning 1264 Out of range value for column 'c12' at row 1
5648
c6 9223372036854775807
5650
c8 1.11111111111111e+50
5651
c9 1.11111111111111e+50
5652
c10 1.11111111111111e+50
5655
set @arg00= -1.11111111111111111111e+50 ;
5656
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5657
@arg00, @arg00, @arg00, @arg00, @arg00 ;
5659
Warning 1264 Out of range value for column 'c1' at row 1
5660
Warning 1264 Out of range value for column 'c2' at row 1
5661
Warning 1264 Out of range value for column 'c3' at row 1
5662
Warning 1264 Out of range value for column 'c4' at row 1
5663
Warning 1264 Out of range value for column 'c5' at row 1
5664
Warning 1264 Out of range value for column 'c6' at row 1
5665
Warning 1264 Out of range value for column 'c7' at row 1
5666
Warning 1264 Out of range value for column 'c12' at row 1
5673
c6 -9223372036854775808
5675
c8 -1.11111111111111e+50
5676
c9 -1.11111111111111e+50
5677
c10 -1.11111111111111e+50
5680
set @arg00= '-1.11111111111111111111e+50' ;
5681
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5682
@arg00, @arg00, @arg00, @arg00, @arg00 ;
5684
Warning 1264 Out of range value for column 'c1' at row 1
5685
Warning 1264 Out of range value for column 'c2' at row 1
5686
Warning 1264 Out of range value for column 'c3' at row 1
5687
Warning 1264 Out of range value for column 'c4' at row 1
5688
Warning 1264 Out of range value for column 'c5' at row 1
5689
Warning 1264 Out of range value for column 'c6' at row 1
5690
Warning 1264 Out of range value for column 'c7' at row 1
5691
Warning 1264 Out of range value for column 'c12' at row 1
5698
c6 -9223372036854775808
5700
c8 -1.11111111111111e+50
5701
c9 -1.11111111111111e+50
5702
c10 -1.11111111111111e+50
5706
-- insert into string columns --
5708
Warning 1265 Data truncated for column 'c20' at row 1
5710
Warning 1265 Data truncated for column 'c20' at row 1
5712
Warning 1265 Data truncated for column 'c20' at row 1
5714
Warning 1265 Data truncated for column 'c20' at row 1
5716
Warning 1265 Data truncated for column 'c20' at row 1
5718
Warning 1265 Data truncated for column 'c20' at row 1
5720
Warning 1265 Data truncated for column 'c20' at row 1
5722
Warning 1265 Data truncated for column 'c20' at row 1
5724
Warning 1265 Data truncated for column 'c20' at row 1
5726
Warning 1265 Data truncated for column 'c20' at row 1
5728
Warning 1265 Data truncated for column 'c20' at row 1
5730
Warning 1265 Data truncated for column 'c20' at row 1
5732
Warning 1265 Data truncated for column 'c20' at row 1
5734
Warning 1265 Data truncated for column 'c20' at row 1
5736
Warning 1265 Data truncated for column 'c20' at row 1
5738
Warning 1265 Data truncated for column 'c20' at row 1
5740
Warning 1265 Data truncated for column 'c20' at row 1
5742
Warning 1265 Data truncated for column 'c20' at row 1
5744
Warning 1265 Data truncated for column 'c20' at row 1
5746
Warning 1265 Data truncated for column 'c20' at row 1
5747
select c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30
5748
from t9 where c1 >= 20
5750
c1 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30
5751
20 2 20 20 20 20 20 20 20 20 20 20
5752
21 2 21 21 21 21 21 21 21 21 21 21
5753
22 2 22 22 22 22 22 22 22 22 22 22
5754
23 2 23 23 23 23 23 23 23 23 23 23
5755
30 3 30 30 30 30 30 30 30 30 30 30
5756
31 3 31 31 31 31 31 31 31 31 31 31
5757
32 3 32 32 32 32 32 32 32 32 32 32
5758
33 3 33 33 33 33 33 33 33 33 33 33
5759
40 4 40 40 40 40 40 40 40 40 40 40
5760
41 4 41 41 41 41 41 41 41 41 41 41
5761
42 4 42 42 42 42 42 42 42 42 42 42
5762
43 4 43 43 43 43 43 43 43 43 43 43
5763
50 5 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0
5764
51 5 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0
5765
52 5 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0
5766
53 5 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0
5767
54 5 54 54 54.00 54.00 54.00 54.00 54.00 54.00 54.00 54.00
5768
55 5 55 55 55 55 55 55 55 55 55 55
5769
56 6 56 56 56.00 56.00 56.00 56.00 56.00 56.00 56.00 56.00
5770
57 6 57 57 57.00 57.00 57.00 57.00 57.00 57.00 57.00 57.00
5771
60 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5772
61 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5773
62 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5774
63 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5775
71 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5776
73 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5777
81 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5778
83 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5780
-- select .. where string column = .. --
5782
select 'true' as found from t9
5783
where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and
5784
c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and
5785
c27= '20' and c28= '20' and c29= '20' and c30= '20' ;
5788
select 'true' as found from t9
5789
where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
5790
c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
5791
c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
5794
prepare stmt1 from "select 'true' as found from t9
5795
where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and
5796
c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and
5797
c27= '20' and c28= '20' and c29= '20' and c30= '20'" ;
5801
prepare stmt1 from "select 'true' as found from t9
5802
where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
5803
c21= ? and c22= ? and c23= ? and c25= ? and
5804
c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
5805
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5806
@arg00, @arg00, @arg00, @arg00, @arg00 ;
5809
set @arg00= CAST('20' as binary);
5810
select 'true' as found from t9
5811
where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))
5812
= CAST('20' as binary) and c21= CAST('20' as binary)
5813
and c22= CAST('20' as binary) and c23= CAST('20' as binary) and
5814
c24= CAST('20' as binary) and c25= CAST('20' as binary) and
5815
c26= CAST('20' as binary) and c27= CAST('20' as binary) and
5816
c28= CAST('20' as binary) and c29= CAST('20' as binary) and
5817
c30= CAST('20' as binary) ;
5820
select 'true' as found from t9
5821
where c1= 20 and concat(c20,substr(@arg00,1+length(c20))) = @arg00 and
5822
c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
5823
c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and
5827
prepare stmt1 from "select 'true' as found from t9
5828
where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))
5829
= CAST('20' as binary) and c21= CAST('20' as binary)
5830
and c22= CAST('20' as binary) and c23= CAST('20' as binary) and
5831
c24= CAST('20' as binary) and c25= CAST('20' as binary) and
5832
c26= CAST('20' as binary) and c27= CAST('20' as binary) and
5833
c28= CAST('20' as binary) and c29= CAST('20' as binary) and
5834
c30= CAST('20' as binary)" ;
5838
prepare stmt1 from "select 'true' as found from t9
5839
where c1= 20 and concat(c20,substr(?,1+length(c20))) = ? and c21= ? and
5840
c22= ? and c23= ? and c25= ? and c26= ? and c27= ? and c28= ? and
5842
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5843
@arg00, @arg00, @arg00, @arg00, @arg00 ;
5847
select 'true' as found from t9
5848
where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and
5849
c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and
5850
c27= 20 and c28= 20 and c29= 20 and c30= 20 ;
5853
select 'true' as found from t9
5854
where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
5855
c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
5856
c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
5859
prepare stmt1 from "select 'true' as found from t9
5860
where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and
5861
c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and
5862
c27= 20 and c28= 20 and c29= 20 and c30= 20" ;
5866
prepare stmt1 from "select 'true' as found from t9
5867
where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
5868
c21= ? and c22= ? and c23= ? and c25= ? and
5869
c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
5870
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5871
@arg00, @arg00, @arg00, @arg00, @arg00 ;
5875
select 'true' as found from t9
5876
where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and
5877
c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and
5878
c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0 ;
5881
select 'true' as found from t9
5882
where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
5883
c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
5884
c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
5887
prepare stmt1 from "select 'true' as found from t9
5888
where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and
5889
c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and
5890
c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0" ;
5894
prepare stmt1 from "select 'true' as found from t9
5895
where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
5896
c21= ? and c22= ? and c23= ? and c25= ? and
5897
c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
5898
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
5899
@arg00, @arg00, @arg00, @arg00, @arg00 ;
5904
-- insert into date/time columns --
5906
Note 1265 Data truncated for column 'c13' at row 1
5907
Warning 1265 Data truncated for column 'c17' at row 1
5909
Note 1265 Data truncated for column 'c13' at row 1
5910
Warning 1265 Data truncated for column 'c17' at row 1
5912
Note 1265 Data truncated for column 'c13' at row 1
5913
Warning 1265 Data truncated for column 'c17' at row 1
5915
Note 1265 Data truncated for column 'c13' at row 1
5916
Warning 1265 Data truncated for column 'c17' at row 1
5918
Note 1265 Data truncated for column 'c13' at row 1
5919
Warning 1265 Data truncated for column 'c17' at row 1
5921
Note 1265 Data truncated for column 'c13' at row 1
5922
Warning 1265 Data truncated for column 'c17' at row 1
5924
Note 1265 Data truncated for column 'c13' at row 1
5925
Warning 1265 Data truncated for column 'c17' at row 1
5927
Note 1265 Data truncated for column 'c13' at row 1
5928
Warning 1265 Data truncated for column 'c17' at row 1
5930
Warning 1264 Out of range value for column 'c13' at row 1
5931
Warning 1264 Out of range value for column 'c14' at row 1
5932
Warning 1265 Data truncated for column 'c15' at row 1
5933
Warning 1264 Out of range value for column 'c16' at row 1
5934
Warning 1264 Out of range value for column 'c17' at row 1
5936
Warning 1264 Out of range value for column 'c13' at row 1
5937
Warning 1264 Out of range value for column 'c14' at row 1
5938
Warning 1265 Data truncated for column 'c15' at row 1
5939
Warning 1264 Out of range value for column 'c16' at row 1
5940
Warning 1264 Out of range value for column 'c17' at row 1
5942
Warning 1264 Out of range value for column 'c13' at row 1
5943
Warning 1264 Out of range value for column 'c14' at row 1
5944
Warning 1265 Data truncated for column 'c15' at row 1
5945
Warning 1264 Out of range value for column 'c16' at row 1
5946
Warning 1264 Out of range value for column 'c17' at row 1
5948
Warning 1264 Out of range value for column 'c13' at row 1
5949
Warning 1264 Out of range value for column 'c14' at row 1
5950
Warning 1265 Data truncated for column 'c15' at row 1
5951
Warning 1264 Out of range value for column 'c16' at row 1
5952
Warning 1264 Out of range value for column 'c17' at row 1
5954
Warning 1265 Data truncated for column 'c15' at row 1
5955
Warning 1264 Out of range value for column 'c16' at row 1
5956
Warning 1264 Out of range value for column 'c17' at row 1
5958
Warning 1265 Data truncated for column 'c15' at row 1
5959
Warning 1264 Out of range value for column 'c16' at row 1
5960
Warning 1264 Out of range value for column 'c17' at row 1
5962
Warning 1265 Data truncated for column 'c15' at row 1
5963
Warning 1264 Out of range value for column 'c16' at row 1
5964
Warning 1264 Out of range value for column 'c17' at row 1
5966
Warning 1265 Data truncated for column 'c15' at row 1
5967
Warning 1264 Out of range value for column 'c16' at row 1
5968
Warning 1264 Out of range value for column 'c17' at row 1
5969
select c1, c13, c14, c15, c16, c17 from t9 order by c1 ;
5970
c1 c13 c14 c15 c16 c17
5971
20 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
5972
21 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
5973
22 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
5974
23 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
5975
30 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
5976
31 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
5977
32 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
5978
33 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
5979
40 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
5980
41 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
5981
42 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
5982
43 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
5983
50 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
5984
51 2010-00-00 2010-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
5985
52 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
5986
53 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
5987
60 NULL NULL 1991-01-01 01:01:01 NULL NULL
5988
61 NULL NULL 1991-01-01 01:01:01 NULL NULL
5989
62 NULL NULL 1991-01-01 01:01:01 NULL NULL
5990
63 NULL NULL 1991-01-01 01:01:01 NULL NULL
5991
71 NULL NULL 1991-01-01 01:01:01 NULL NULL
5992
73 NULL NULL 1991-01-01 01:01:01 NULL NULL
5993
81 NULL NULL 1991-01-01 01:01:01 NULL NULL
5994
83 NULL NULL 1991-01-01 01:01:01 NULL NULL
5996
-- select .. where date/time column = .. --
5997
set @arg00= '1991-01-01 01:01:01' ;
5998
select 'true' as found from t9
5999
where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
6000
c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
6001
c17= '1991-01-01 01:01:01' ;
6004
select 'true' as found from t9
6005
where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
6009
prepare stmt1 from "select 'true' as found from t9
6010
where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
6011
c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
6012
c17= '1991-01-01 01:01:01'" ;
6016
prepare stmt1 from "select 'true' as found from t9
6017
where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
6018
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
6021
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
6022
select 'true' as found from t9
6023
where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
6024
c14= CAST('1991-01-01 01:01:01' as datetime) and
6025
c15= CAST('1991-01-01 01:01:01' as datetime) and
6026
c16= CAST('1991-01-01 01:01:01' as datetime) and
6027
c17= CAST('1991-01-01 01:01:01' as datetime) ;
6030
select 'true' as found from t9
6031
where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
6035
prepare stmt1 from "select 'true' as found from t9
6036
where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
6037
c14= CAST('1991-01-01 01:01:01' as datetime) and
6038
c15= CAST('1991-01-01 01:01:01' as datetime) and
6039
c16= CAST('1991-01-01 01:01:01' as datetime) and
6040
c17= CAST('1991-01-01 01:01:01' as datetime)" ;
6044
prepare stmt1 from "select 'true' as found from t9
6045
where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
6046
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
6050
select 'true' as found from t9
6051
where c1= 20 and c17= 1991 ;
6054
select 'true' as found from t9
6055
where c1= 20 and c17= @arg00 ;
6058
prepare stmt1 from "select 'true' as found from t9
6059
where c1= 20 and c17= 1991" ;
6063
prepare stmt1 from "select 'true' as found from t9
6064
where c1= 20 and c17= ?" ;
6065
execute stmt1 using @arg00 ;
6068
set @arg00= 1.991e+3 ;
6069
select 'true' as found from t9
6070
where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;
6073
select 'true' as found from t9
6074
where c1= 20 and abs(c17 - @arg00) < 0.01 ;
6077
prepare stmt1 from "select 'true' as found from t9
6078
where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;
6082
prepare stmt1 from "select 'true' as found from t9
6083
where c1= 20 and abs(c17 - ?) < 0.01" ;
6084
execute stmt1 using @arg00 ;
6087
drop table t1, t1_1, t1_2,