1
set global maria_log_file_size=4294967295;
3
drop table if exists t1, t9 ;
11
c1 tinyint, c2 smallint, c3 mediumint, c4 int,
12
c5 integer, c6 bigint, c7 float, c8 double,
13
c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
14
c13 date, c14 datetime, c15 timestamp, c16 time,
15
c17 year, c18 tinyint, c19 bool, c20 char,
16
c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
17
c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
18
c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'),
19
c32 set('monday', 'tuesday', 'wednesday'),
23
insert into t1 values (1,'one');
24
insert into t1 values (2,'two');
25
insert into t1 values (3,'three');
26
insert into t1 values (4,'four');
30
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
31
c10= 1, c11= 1, c12 = 1,
32
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
33
c16= '11:11:11', c17= '2004',
34
c18= 1, c19=true, c20= 'a', c21= '123456789a',
35
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
36
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
37
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
39
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
40
c10= 9, c11= 9, c12 = 9,
41
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
42
c16= '11:11:11', c17= '2004',
43
c18= 1, c19=false, c20= 'a', c21= '123456789a',
44
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
45
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
46
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
49
------ simple select tests ------
50
prepare stmt1 from ' select * from t9 order by c1 ' ;
52
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
53
def test t9 t9 c1 c1 1 4 1 N 49155 0 63
54
def test t9 t9 c2 c2 2 6 1 Y 32768 0 63
55
def test t9 t9 c3 c3 9 9 1 Y 32768 0 63
56
def test t9 t9 c4 c4 3 11 1 Y 32768 0 63
57
def test t9 t9 c5 c5 3 11 1 Y 32768 0 63
58
def test t9 t9 c6 c6 8 20 1 Y 32768 0 63
59
def test t9 t9 c7 c7 4 12 1 Y 32768 31 63
60
def test t9 t9 c8 c8 5 22 1 Y 32768 31 63
61
def test t9 t9 c9 c9 5 22 1 Y 32768 31 63
62
def test t9 t9 c10 c10 5 22 1 Y 32768 31 63
63
def test t9 t9 c11 c11 246 9 6 Y 0 4 63
64
def test t9 t9 c12 c12 246 10 6 Y 0 4 63
65
def test t9 t9 c13 c13 10 10 10 Y 128 0 63
66
def test t9 t9 c14 c14 12 19 19 Y 128 0 63
67
def test t9 t9 c15 c15 7 19 19 N 9441 0 63
68
def test t9 t9 c16 c16 11 8 8 Y 128 0 63
69
def test t9 t9 c17 c17 13 4 4 Y 32864 0 63
70
def test t9 t9 c18 c18 1 4 1 Y 32768 0 63
71
def test t9 t9 c19 c19 1 1 1 Y 32768 0 63
72
def test t9 t9 c20 c20 254 1 1 Y 0 0 8
73
def test t9 t9 c21 c21 254 10 10 Y 0 0 8
74
def test t9 t9 c22 c22 253 30 30 Y 0 0 8
75
def test t9 t9 c23 c23 252 255 8 Y 144 0 63
76
def test t9 t9 c24 c24 252 255 8 Y 16 0 8
77
def test t9 t9 c25 c25 252 65535 4 Y 144 0 63
78
def test t9 t9 c26 c26 252 65535 4 Y 16 0 8
79
def test t9 t9 c27 c27 252 16777215 10 Y 144 0 63
80
def test t9 t9 c28 c28 252 16777215 10 Y 16 0 8
81
def test t9 t9 c29 c29 252 4294967295 8 Y 144 0 63
82
def test t9 t9 c30 c30 252 4294967295 8 Y 16 0 8
83
def test t9 t9 c31 c31 254 5 3 Y 256 0 8
84
def test t9 t9 c32 c32 254 24 7 Y 2048 0 8
85
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
86
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
87
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
89
@arg00 a from t1 where a=1;
90
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
91
prepare stmt1 from ' ? a from t1 where a=1 ';
92
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
94
select @arg00, b from t1 where a=1 ;
97
prepare stmt1 from ' select ?, b from t1 where a=1 ' ;
98
execute stmt1 using @arg00 ;
102
select @arg00, b from t1 where a=1 ;
105
prepare stmt1 from ' select ?, b from t1 where a=1 ' ;
106
execute stmt1 using @arg00 ;
110
select @arg00, b from t1 where a=1 ;
113
prepare stmt1 from ' select ?, b from t1 where a=1 ' ;
114
execute stmt1 using @arg00 ;
118
select b, a - @arg00 from t1 where a=1 ;
121
prepare stmt1 from ' select b, a - ? from t1 where a=1 ' ;
122
execute stmt1 using @arg00 ;
126
select @arg00 as my_col ;
129
prepare stmt1 from ' select ? as my_col';
130
execute stmt1 using @arg00 ;
133
select @arg00 + 1 as my_col ;
136
prepare stmt1 from ' select ? + 1 as my_col';
137
execute stmt1 using @arg00 ;
140
select 1 + @arg00 as my_col ;
143
prepare stmt1 from ' select 1 + ? as my_col';
144
execute stmt1 using @arg00 ;
148
select substr(@arg00,1,2) from t1 where a=1 ;
151
prepare stmt1 from ' select substr(?,1,2) from t1 where a=1 ' ;
152
execute stmt1 using @arg00 ;
156
select substr('MySQL',@arg00,5) from t1 where a=1 ;
157
substr('MySQL',@arg00,5)
159
prepare stmt1 from ' select substr(''MySQL'',?,5) from t1 where a=1 ' ;
160
execute stmt1 using @arg00 ;
163
select substr('MySQL',1,@arg00) from t1 where a=1 ;
164
substr('MySQL',1,@arg00)
166
prepare stmt1 from ' select substr(''MySQL'',1,?) from t1 where a=1 ' ;
167
execute stmt1 using @arg00 ;
171
select a , concat(@arg00,b) from t1 order by a;
177
prepare stmt1 from ' select a , concat(?,b) from t1 order by a ' ;
178
execute stmt1 using @arg00;
184
select a , concat(b,@arg00) from t1 order by a ;
190
prepare stmt1 from ' select a , concat(b,?) from t1 order by a ' ;
191
execute stmt1 using @arg00;
198
select group_concat(@arg00,b order by a) from t1
200
group_concat(@arg00,b order by a)
201
MySQLone,MySQLtwo,MySQLthree,MySQLfour
202
prepare stmt1 from ' select group_concat(?,b order by a) from t1
204
execute stmt1 using @arg00;
205
group_concat(?,b order by a)
206
MySQLone,MySQLtwo,MySQLthree,MySQLfour
207
select group_concat(b,@arg00 order by a) from t1
209
group_concat(b,@arg00 order by a)
210
oneMySQL,twoMySQL,threeMySQL,fourMySQL
211
prepare stmt1 from ' select group_concat(b,? order by a) from t1
213
execute stmt1 using @arg00;
214
group_concat(b,? order by a)
215
oneMySQL,twoMySQL,threeMySQL,fourMySQL
217
set @arg01='second' ;
219
select @arg00, @arg01 from t1 where a=1 ;
222
prepare stmt1 from ' select ?, ? from t1 where a=1 ' ;
223
execute stmt1 using @arg00, @arg01 ;
226
execute stmt1 using @arg02, @arg01 ;
229
execute stmt1 using @arg00, @arg02 ;
232
execute stmt1 using @arg02, @arg02 ;
235
drop table if exists t5 ;
236
create table t5 (id1 int(11) not null default '0',
237
value2 varchar(100), value1 varchar(100)) ;
238
insert into t5 values (1,'hh','hh'),(2,'hh','hh'),
239
(1,'ii','ii'),(2,'ii','ii') ;
240
prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? order by id1,value1 ' ;
243
execute stmt1 using @arg00, @arg01 ;
249
drop table if exists t5 ;
250
create table t5(session_id char(9) not null) ;
251
insert into t5 values ('abc') ;
252
prepare stmt1 from ' select * from t5
253
where ?=''1111'' and session_id = ''abc'' ' ;
255
execute stmt1 using @arg00 ;
258
execute stmt1 using @arg00 ;
262
execute stmt1 using @arg00 ;
266
select a @arg00 t1 where a=1 ;
267
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
268
prepare stmt1 from ' select a ? t1 where a=1 ' ;
269
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
271
select a from @arg00 where a=1 ;
272
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
273
prepare stmt1 from ' select a from ? where a=1 ' ;
274
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
276
select a from t1 @arg00 a=1 ;
277
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
278
prepare stmt1 from ' select a from t1 ? a=1 ' ;
279
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
281
select a FROM t1 where a=@arg00 ;
284
prepare stmt1 from ' select a FROM t1 where a=? ' ;
285
execute stmt1 using @arg00 ;
289
execute stmt1 using @arg00 ;
292
select a FROM t1 where a=@arg00 ;
294
prepare stmt1 from ' select a FROM t1 where a=? ' ;
295
execute stmt1 using @arg00 ;
298
select a FROM t1 where a=sqrt(@arg00) ;
301
prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ;
302
execute stmt1 using @arg00 ;
306
select a FROM t1 where a=sqrt(@arg00) ;
308
prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ;
309
execute stmt1 using @arg00 ;
313
select a FROM t1 where a in (@arg00,@arg01) order by a;
317
prepare stmt1 from ' select a FROM t1 where a in (?,?) order by a ';
318
execute stmt1 using @arg00, @arg01;
325
prepare stmt1 from ' select b FROM t1 where b in (?,?,?) order by b ' ;
326
execute stmt1 using @arg00, @arg01, @arg02 ;
330
prepare stmt1 from ' select b FROM t1 where b like ? ';
332
execute stmt1 using @arg00 ;
336
execute stmt1 using @arg00 ;
340
execute stmt1 using @arg00 ;
344
insert into t9 set c1= 0, c5 = NULL ;
345
select c5 from t9 where c5 > NULL ;
347
prepare stmt1 from ' select c5 from t9 where c5 > ? ';
348
execute stmt1 using @arg00 ;
350
select c5 from t9 where c5 < NULL ;
352
prepare stmt1 from ' select c5 from t9 where c5 < ? ';
353
execute stmt1 using @arg00 ;
355
select c5 from t9 where c5 = NULL ;
357
prepare stmt1 from ' select c5 from t9 where c5 = ? ';
358
execute stmt1 using @arg00 ;
360
select c5 from t9 where c5 <=> NULL ;
363
prepare stmt1 from ' select c5 from t9 where c5 <=> ? ';
364
execute stmt1 using @arg00 ;
367
delete from t9 where c1= 0 ;
369
select a FROM t1 where a @arg00 1 ;
370
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
371
prepare stmt1 from ' select a FROM t1 where a ? 1 ' ;
372
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
374
select a,b FROM t1 where a is not NULL
375
AND b is not NULL group by a - @arg00 ;
381
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
382
AND b is not NULL group by a - ? ' ;
383
execute stmt1 using @arg00 ;
390
select a,b FROM t1 where a is not NULL
391
AND b is not NULL having b <> @arg00 order by a ;
396
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
397
AND b is not NULL having b <> ? order by a ' ;
398
execute stmt1 using @arg00 ;
404
select a,b FROM t1 where a is not NULL
405
AND b is not NULL order by a - @arg00 ;
411
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
412
AND b is not NULL order by a - ? ' ;
413
execute stmt1 using @arg00 ;
420
select a,b from t1 order by 2 ;
426
prepare stmt1 from ' select a,b from t1
428
execute stmt1 using @arg00;
435
execute stmt1 using @arg00;
442
execute stmt1 using @arg00;
443
ERROR 42S22: Unknown column '?' in 'order clause'
445
prepare stmt1 from ' select a,b from t1 order by a
450
prepare stmt1 from ' select a,b from t1 order by a limit ? ';
451
execute stmt1 using @arg00;
458
select sum(a), @arg00 from t1 where a > @arg01
459
and b is not null group by substr(b,@arg02)
460
having sum(a) <> @arg03 ;
465
prepare stmt1 from ' select sum(a), ? from t1 where a > ?
466
and b is not null group by substr(b,?)
467
having sum(a) <> ? ';
468
execute stmt1 using @arg00, @arg01, @arg02, @arg03;
474
------ join tests ------
475
select first.a as a1, second.a as a2
476
from t1 first, t1 second
477
where first.a = second.a order by a1 ;
483
prepare stmt1 from ' select first.a as a1, second.a as a2
484
from t1 first, t1 second
485
where first.a = second.a order by a1 ';
495
select first.a, @arg00, second.a FROM t1 first, t1 second
496
where @arg01 = first.b or first.a = second.a or second.b = @arg02
497
order by second.a, first.a;
508
prepare stmt1 from ' select first.a, ?, second.a FROM t1 first, t1 second
509
where ? = first.b or first.a = second.a or second.b = ?
510
order by second.a, first.a';
511
execute stmt1 using @arg00, @arg01, @arg02;
522
drop table if exists t2 ;
523
create table t2 as select * from t1 ;
524
set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
525
set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
526
set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
527
set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
528
set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
529
set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
530
set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
531
set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
532
set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
533
the join statement is:
534
SELECT * FROM t2 right join t1 using(a) order by t2.a
535
prepare stmt1 from @query9 ;
554
the join statement is:
555
SELECT * FROM t2 natural right join t1 order by t2.a
556
prepare stmt1 from @query8 ;
575
the join statement is:
576
SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a
577
prepare stmt1 from @query7 ;
596
the join statement is:
597
SELECT * FROM t2 left join t1 using(a) order by t2.a
598
prepare stmt1 from @query6 ;
617
the join statement is:
618
SELECT * FROM t2 natural left join t1 order by t2.a
619
prepare stmt1 from @query5 ;
638
the join statement is:
639
SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a
640
prepare stmt1 from @query4 ;
659
the join statement is:
660
SELECT * FROM t2 join t1 using(a) order by t2.a
661
prepare stmt1 from @query3 ;
680
the join statement is:
681
SELECT * FROM t2 natural join t1 order by t2.a
682
prepare stmt1 from @query2 ;
701
the join statement is:
702
SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a
703
prepare stmt1 from @query1 ;
724
------ subquery tests ------
725
prepare stmt1 from ' select a, b FROM t1 outer_table where
726
a = (select a from t1 where b = ''two'') ';
731
select a, b FROM t1 outer_table where
732
a = (select a from t1 where b = 'two' ) and b=@arg00 ;
735
prepare stmt1 from ' select a, b FROM t1 outer_table where
736
a = (select a from t1 where b = ''two'') and b=? ';
737
execute stmt1 using @arg00;
741
select a, b FROM t1 outer_table where
742
a = (select a from t1 where b = @arg00 ) and b='two' ;
745
prepare stmt1 from ' select a, b FROM t1 outer_table where
746
a = (select a from t1 where b = ? ) and b=''two'' ' ;
747
execute stmt1 using @arg00;
752
select a,b FROM t1 where (a,b) in (select 3, 'three');
755
select a FROM t1 where (a,b) in (select @arg00,@arg01);
758
prepare stmt1 from ' select a FROM t1 where (a,b) in (select ?, ?) ';
759
execute stmt1 using @arg00, @arg01;
766
select a, @arg00, b FROM t1 outer_table where
767
b=@arg01 and a = (select @arg02 from t1 where b = @arg03 ) ;
770
prepare stmt1 from ' select a, ?, b FROM t1 outer_table where
771
b=? and a = (select ? from t1 where b = ? ) ' ;
772
execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
775
prepare stmt1 from 'select c4 FROM t9 where
776
c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ;
777
execute stmt1 using @arg01, @arg02;
779
prepare stmt1 from ' select a, b FROM t1 outer_table where
780
a = (select a from t1 where b = outer_table.b ) order by a ';
787
prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
788
(SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
792
deallocate prepare stmt1 ;
793
prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
794
(SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
798
deallocate prepare stmt1 ;
799
prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
800
(SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
804
deallocate prepare stmt1 ;
806
select a, b FROM t1 outer_table where
807
a = (select a from t1 where b = outer_table.b ) and b=@arg00 ;
810
prepare stmt1 from ' select a, b FROM t1 outer_table where
811
a = (select a from t1 where b = outer_table.b) and b=? ';
812
execute stmt1 using @arg00;
816
select a, b FROM t1 outer_table where
817
a = (select a from t1 where a = @arg00 and b = outer_table.b) and b='two' ;
820
prepare stmt1 from ' select a, b FROM t1 outer_table where
821
a = (select a from t1 where a = ? and b = outer_table.b) and b=''two'' ' ;
822
execute stmt1 using @arg00;
826
select a, b FROM t1 outer_table where
827
a = (select a from t1 where outer_table.a = @arg00 and a=2) and b='two' ;
830
prepare stmt1 from ' select a, b FROM t1 outer_table where
831
a = (select a from t1 where outer_table.a = ? and a=2) and b=''two'' ' ;
832
execute stmt1 using @arg00;
839
select a, @arg00, b FROM t1 outer_table where
840
b=@arg01 and a = (select @arg02 from t1 where outer_table.b = @arg03
841
and outer_table.a=a ) ;
844
prepare stmt1 from ' select a, ?, b FROM t1 outer_table where
845
b=? and a = (select ? from t1 where outer_table.b = ?
846
and outer_table.a=a ) ' ;
847
execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
853
from ( select a - @arg00 as a from t1 where a=@arg00 ) as t2
857
prepare stmt1 from ' select a, ?
858
from ( select a - ? as a from t1 where a=? ) as t2
860
execute stmt1 using @arg00, @arg00, @arg00, @arg01 ;
863
drop table if exists t2 ;
864
create table t2 as select * from t1;
865
prepare stmt1 from ' select a in (select a from t2) from t1 ' ;
867
a in (select a from t2)
872
drop table if exists t5, t6, t7 ;
873
create table t5 (a int , b int) ;
874
create table t6 like t5 ;
875
create table t7 like t5 ;
876
insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7),
878
insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1) ;
879
insert into t7 values (3, 3), (2, 2), (1, 1) ;
880
prepare stmt1 from ' select a, (select count(distinct t5.b) as sum from t5, t6
881
where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
882
group by t5.a order by sum limit 1) from t7 ' ;
884
a (select count(distinct t5.b) as sum from t5, t6
885
where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
886
group by t5.a order by sum limit 1)
891
a (select count(distinct t5.b) as sum from t5, t6
892
where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
893
group by t5.a order by sum limit 1)
898
a (select count(distinct t5.b) as sum from t5, t6
899
where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
900
group by t5.a order by sum limit 1)
904
drop table t5, t6, t7 ;
905
drop table if exists t2 ;
906
create table t2 as select * from t9;
908
(SELECT SUM(c1 + c12 + 0.0) FROM t2
909
where (t9.c2 - 0e-3) = t2.c2
910
GROUP BY t9.c15 LIMIT 1) as scalar_s,
911
exists (select 1.0e+0 from t2
912
where t2.c3 * 9.0000000000 = t9.c4) as exists_s,
913
c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s,
914
(c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s
916
(select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
917
prepare stmt1 from @stmt ;
920
set @stmt= concat('explain ',@stmt);
921
prepare stmt1 from @stmt ;
925
(SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2
926
GROUP BY t9.c15 LIMIT 1) as scalar_s,
927
exists (select ? from t2
928
where t2.c3*?=t9.c4) as exists_s,
929
c5*? in (select c6+? from t2) as in_s,
930
(c7-?, c8-?) in (select c9+?, c10+? from t2) as in_row_s
932
(select c25 x, c32 y from t2) tt WHERE x =c25 ' ;
936
set @arg03= 9.0000000000 ;
943
prepare stmt1 from @stmt ;
944
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
945
@arg07, @arg08, @arg09 ;
946
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
947
@arg07, @arg08, @arg09 ;
948
set @stmt= concat('explain ',@stmt);
949
prepare stmt1 from @stmt ;
950
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
951
@arg07, @arg08, @arg09 ;
952
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
953
@arg07, @arg08, @arg09 ;
955
select 1 < (select a from t1) ;
956
ERROR 21000: Subquery returns more than 1 row
957
prepare stmt1 from ' select 1 < (select a from t1) ' ;
959
ERROR 21000: Subquery returns more than 1 row
964
------ union tests ------
965
prepare stmt1 from ' select a FROM t1 where a=1
967
select a FROM t1 where a=1 ';
974
prepare stmt1 from ' select a FROM t1 where a=1
976
select a FROM t1 where a=1 ';
981
prepare stmt1 from ' SELECT 1, 2 union SELECT 1 ' ;
982
ERROR 21000: The used SELECT statements have a different number of columns
983
prepare stmt1 from ' SELECT 1 union SELECT 1, 2 ' ;
984
ERROR 21000: The used SELECT statements have a different number of columns
985
prepare stmt1 from ' SELECT * from t1 union SELECT 1 ' ;
986
ERROR 21000: The used SELECT statements have a different number of columns
987
prepare stmt1 from ' SELECT 1 union SELECT * from t1 ' ;
988
ERROR 21000: The used SELECT statements have a different number of columns
990
select @arg00 FROM t1 where a=1
992
select 1 FROM t1 where a=1;
995
prepare stmt1 from ' select ? FROM t1 where a=1
997
select 1 FROM t1 where a=1 ' ;
998
execute stmt1 using @arg00;
1002
select 1 FROM t1 where a=1
1004
select @arg00 FROM t1 where a=1;
1007
prepare stmt1 from ' select 1 FROM t1 where a=1
1009
select ? FROM t1 where a=1 ' ;
1010
execute stmt1 using @arg00;
1014
select @arg00 FROM t1 where a=1
1016
select @arg00 FROM t1 where a=1;
1019
prepare stmt1 from ' select ? FROM t1 where a=1
1021
select ? FROM t1 where a=1 ';
1022
execute stmt1 using @arg00, @arg00;
1025
prepare stmt1 from ' select ?
1028
execute stmt1 using @arg00, @arg00;
1035
select @arg00 FROM t1 where a=@arg01
1037
select @arg02 FROM t1 where a=@arg03;
1040
prepare stmt1 from ' select ? FROM t1 where a=?
1042
select ? FROM t1 where a=? ' ;
1043
execute stmt1 using @arg00, @arg01, @arg02, @arg03;
1047
prepare stmt1 from ' select sum(a) + 200, ? from t1
1049
select sum(a) + 200, 1 from t1
1051
execute stmt1 using @arg00;
1058
set @Oporto='Oporto' ;
1059
set @Lisboa='Lisboa' ;
1065
select @Oporto,@Lisboa,@0,@1,@2,@3,@4 ;
1066
@Oporto @Lisboa @0 @1 @2 @3 @4
1067
Oporto Lisboa 0 1 2 3 4
1068
select sum(a) + 200 as the_sum, @Oporto as the_town from t1
1071
select sum(a) + 200, @Lisboa from t1
1082
prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
1085
select sum(a) + 200, ? from t1
1087
execute stmt1 using @Oporto, @Lisboa;
1097
select sum(a) + 200 as the_sum, @Oporto as the_town from t1
1101
select sum(a) + 200, @Lisboa from t1
1110
prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
1114
select sum(a) + 200, ? from t1
1117
execute stmt1 using @Oporto, @1, @Lisboa, @2;
1124
select sum(a) + 200 as the_sum, @Oporto as the_town from t1
1129
select sum(a) + 200, @Lisboa from t1
1137
prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
1142
select sum(a) + 200, ? from t1
1145
having avg(a) > ? ';
1146
execute stmt1 using @Oporto, @1, @2, @Lisboa, @2, @3;
1152
------ explain select tests ------
1153
prepare stmt1 from ' explain select * from t9 ' ;
1155
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1156
def id 8 3 1 N 32929 0 63
1157
def select_type 253 19 6 N 1 31 8
1158
def table 253 64 2 Y 0 31 8
1159
def type 253 10 3 Y 0 31 8
1160
def possible_keys 253 4096 0 Y 0 31 8
1161
def key 253 64 0 Y 0 31 8
1162
def key_len 253 320 0 Y 0 31 8
1163
def ref 253 1024 0 Y 0 31 8
1164
def rows 8 10 1 Y 32928 0 63
1165
def Extra 253 255 0 N 1 31 8
1166
id select_type table type possible_keys key key_len ref rows Extra
1167
1 SIMPLE t9 ALL NULL NULL NULL NULL 2
1168
drop table if exists t2 ;
1169
create table t2 (s varchar(25), fulltext(s)) TRANSACTIONAL= 0
1171
insert into t2 values ('Gravedigger'), ('Greed'),('Hollow Dogs') ;
1173
prepare stmt1 from ' select s from t2 where match (s) against (?) ' ;
1175
execute stmt1 using @arg00 ;
1178
prepare stmt1 from ' SELECT s FROM t2
1179
where match (s) against (concat(?,''digger'')) ';
1180
set @arg00='Grave' ;
1181
execute stmt1 using @arg00 ;
1186
------ delete tests ------
1188
insert into t1 values (1,'one');
1189
insert into t1 values (2,'two');
1190
insert into t1 values (3,'three');
1191
insert into t1 values (4,'four');
1195
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1196
c10= 1, c11= 1, c12 = 1,
1197
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1198
c16= '11:11:11', c17= '2004',
1199
c18= 1, c19=true, c20= 'a', c21= '123456789a',
1200
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1201
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1202
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1204
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1205
c10= 9, c11= 9, c12 = 9,
1206
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1207
c16= '11:11:11', c17= '2004',
1208
c18= 1, c19=false, c20= 'a', c21= '123456789a',
1209
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1210
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1211
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1213
prepare stmt1 from 'delete from t1 where a=2' ;
1215
select a,b from t1 where a=2;
1218
insert into t1 values(0,NULL);
1220
prepare stmt1 from 'delete from t1 where b=?' ;
1221
execute stmt1 using @arg00;
1222
select a,b from t1 where b is NULL ;
1226
execute stmt1 using @arg00;
1227
select a,b from t1 where b=@arg00;
1229
prepare stmt1 from 'truncate table t1' ;
1231
------ update tests ------
1233
insert into t1 values (1,'one');
1234
insert into t1 values (2,'two');
1235
insert into t1 values (3,'three');
1236
insert into t1 values (4,'four');
1240
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1241
c10= 1, c11= 1, c12 = 1,
1242
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1243
c16= '11:11:11', c17= '2004',
1244
c18= 1, c19=true, c20= 'a', c21= '123456789a',
1245
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1246
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1247
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1249
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1250
c10= 9, c11= 9, c12 = 9,
1251
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1252
c16= '11:11:11', c17= '2004',
1253
c18= 1, c19=false, c20= 'a', c21= '123456789a',
1254
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1255
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1256
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1258
prepare stmt1 from 'update t1 set b=''a=two'' where a=2' ;
1260
select a,b from t1 where a=2;
1264
select a,b from t1 where a=2;
1268
prepare stmt1 from 'update t1 set b=? where a=2' ;
1269
execute stmt1 using @arg00;
1270
select a,b from t1 where a=2;
1274
execute stmt1 using @arg00;
1275
select a,b from t1 where a=2;
1279
prepare stmt1 from 'update t1 set b=NULL where a=?' ;
1280
execute stmt1 using @arg00;
1281
select a,b from t1 where a=@arg00;
1284
update t1 set b='two' where a=@arg00;
1286
execute stmt1 using @arg00;
1287
select a,b from t1 where a=@arg00;
1291
prepare stmt1 from 'update t1 set a=? where a=?' ;
1292
execute stmt1 using @arg00, @arg00;
1293
select a,b from t1 where a=@arg00;
1296
execute stmt1 using @arg01, @arg00;
1297
select a,b from t1 where a=@arg01;
1300
execute stmt1 using @arg00, @arg01;
1301
select a,b from t1 where a=@arg00;
1306
execute stmt1 using @arg00, @arg01;
1307
ERROR 23000: Column 'a' cannot be null
1308
select a,b from t1 order by a;
1315
execute stmt1 using @arg01, @arg00;
1316
select a,b from t1 order by a;
1327
drop table if exists t2;
1328
create table t2 as select a,b from t1 ;
1329
prepare stmt1 from 'update t1 set a=? where b=?
1330
and a in (select ? from t2
1331
where b = ? or a = ?)';
1332
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;
1334
info: Rows matched: 1 Changed: 1 Warnings: 0
1335
select a,b from t1 where a = @arg00 ;
1338
prepare stmt1 from 'update t1 set a=? where b=?
1339
and a not in (select ? from t2
1340
where b = ? or a = ?)';
1341
execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;
1343
info: Rows matched: 1 Changed: 1 Warnings: 0
1344
select a,b from t1 order by a ;
1353
a int, b varchar(30),
1355
) engine = 'MARIA' ;
1356
insert into t2(a,b) select a, b from t1 ;
1357
prepare stmt1 from 'update t1 set a=? where b=?
1358
and a in (select ? from t2
1359
where b = ? or a = ?)';
1360
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;
1362
info: Rows matched: 1 Changed: 1 Warnings: 0
1363
select a,b from t1 where a = @arg00 ;
1366
prepare stmt1 from 'update t1 set a=? where b=?
1367
and a not in (select ? from t2
1368
where b = ? or a = ?)';
1369
execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;
1371
info: Rows matched: 1 Changed: 1 Warnings: 0
1372
select a,b from t1 order by a ;
1380
prepare stmt1 from 'update t1 set b=''bla''
1384
select a,b from t1 where b = 'bla' ;
1387
prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';
1388
execute stmt1 using @arg00;
1390
------ insert tests ------
1392
insert into t1 values (1,'one');
1393
insert into t1 values (2,'two');
1394
insert into t1 values (3,'three');
1395
insert into t1 values (4,'four');
1399
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1400
c10= 1, c11= 1, c12 = 1,
1401
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1402
c16= '11:11:11', c17= '2004',
1403
c18= 1, c19=true, c20= 'a', c21= '123456789a',
1404
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1405
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1406
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1408
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1409
c10= 9, c11= 9, c12 = 9,
1410
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1411
c16= '11:11:11', c17= '2004',
1412
c18= 1, c19=false, c20= 'a', c21= '123456789a',
1413
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1414
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1415
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1417
prepare stmt1 from 'insert into t1 values(5, ''five'' )';
1419
select a,b from t1 where a = 5;
1423
prepare stmt1 from 'insert into t1 values(6, ? )';
1424
execute stmt1 using @arg00;
1425
select a,b from t1 where b = @arg00;
1428
execute stmt1 using @arg00;
1429
ERROR 23000: Duplicate entry '6' for key 'PRIMARY'
1431
prepare stmt1 from 'insert into t1 values(0, ? )';
1432
execute stmt1 using @arg00;
1433
select a,b from t1 where b is NULL;
1437
set @arg01='eight' ;
1438
prepare stmt1 from 'insert into t1 values(?, ? )';
1439
execute stmt1 using @arg00, @arg01 ;
1440
select a,b from t1 where b = @arg01;
1445
execute stmt1 using @NULL, @NULL ;
1446
ERROR 23000: Column 'a' cannot be null
1447
execute stmt1 using @NULL, @NULL ;
1448
ERROR 23000: Column 'a' cannot be null
1449
execute stmt1 using @NULL, @arg00 ;
1450
ERROR 23000: Column 'a' cannot be null
1451
execute stmt1 using @NULL, @arg00 ;
1452
ERROR 23000: Column 'a' cannot be null
1453
set @arg01= 10000 + 2 ;
1454
execute stmt1 using @arg01, @arg00 ;
1455
set @arg01= 10000 + 1 ;
1456
execute stmt1 using @arg01, @arg00 ;
1457
select * from t1 where a > 10000 order by a ;
1461
delete from t1 where a > 10000 ;
1462
set @arg01= 10000 + 2 ;
1463
execute stmt1 using @arg01, @NULL ;
1464
set @arg01= 10000 + 1 ;
1465
execute stmt1 using @arg01, @NULL ;
1466
select * from t1 where a > 10000 order by a ;
1470
delete from t1 where a > 10000 ;
1471
set @arg01= 10000 + 10 ;
1472
execute stmt1 using @arg01, @arg01 ;
1473
set @arg01= 10000 + 9 ;
1474
execute stmt1 using @arg01, @arg01 ;
1475
set @arg01= 10000 + 8 ;
1476
execute stmt1 using @arg01, @arg01 ;
1477
set @arg01= 10000 + 7 ;
1478
execute stmt1 using @arg01, @arg01 ;
1479
set @arg01= 10000 + 6 ;
1480
execute stmt1 using @arg01, @arg01 ;
1481
set @arg01= 10000 + 5 ;
1482
execute stmt1 using @arg01, @arg01 ;
1483
set @arg01= 10000 + 4 ;
1484
execute stmt1 using @arg01, @arg01 ;
1485
set @arg01= 10000 + 3 ;
1486
execute stmt1 using @arg01, @arg01 ;
1487
set @arg01= 10000 + 2 ;
1488
execute stmt1 using @arg01, @arg01 ;
1489
set @arg01= 10000 + 1 ;
1490
execute stmt1 using @arg01, @arg01 ;
1491
select * from t1 where a > 10000 order by a ;
1503
delete from t1 where a > 10000 ;
1508
prepare stmt1 from 'insert into t1 values(?,?),(?,?)';
1509
execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
1510
select a,b from t1 where a in (@arg00,@arg02) ;
1516
prepare stmt1 from 'insert into t1 set a=?, b=? ';
1517
execute stmt1 using @arg00, @arg01 ;
1518
select a,b from t1 where a = @arg00 ;
1523
prepare stmt1 from 'insert into t1 set a=?, b=''sechs''
1524
on duplicate key update a=a + ?, b=concat(b,''modified'') ';
1525
execute stmt1 using @arg00, @arg01;
1526
select * from t1 order by a;
1541
execute stmt1 using @arg00, @arg01;
1542
ERROR 23000: Duplicate entry '82' for key 'PRIMARY'
1543
drop table if exists t2 ;
1544
create table t2 (id int auto_increment primary key)
1546
prepare stmt1 from ' select last_insert_id() ' ;
1547
insert into t2 values (NULL) ;
1551
insert into t2 values (NULL) ;
1557
set @x1000_2="x1000_2" ;
1558
set @x1000_3="x1000_3" ;
1559
set @x1000="x1000" ;
1561
set @x1100="x1100" ;
1563
set @updated="updated" ;
1564
insert into t1 values(1000,'x1000_1') ;
1565
insert into t1 values(@1000,@x1000_2),(@1000,@x1000_3)
1566
on duplicate key update a = a + @100, b = concat(b,@updated) ;
1567
select a,b from t1 where a >= 1000 order by a ;
1571
delete from t1 where a >= 1000 ;
1572
insert into t1 values(1000,'x1000_1') ;
1573
prepare stmt1 from ' insert into t1 values(?,?),(?,?)
1574
on duplicate key update a = a + ?, b = concat(b,?) ';
1575
execute stmt1 using @1000, @x1000_2, @1000, @x1000_3, @100, @updated ;
1576
select a,b from t1 where a >= 1000 order by a ;
1580
delete from t1 where a >= 1000 ;
1581
insert into t1 values(1000,'x1000_1') ;
1582
execute stmt1 using @1000, @x1000_2, @1100, @x1000_3, @100, @updated ;
1583
select a,b from t1 where a >= 1000 order by a ;
1585
1200 x1000_1updatedupdated
1586
delete from t1 where a >= 1000 ;
1587
prepare stmt1 from ' replace into t1 (a,b) select 100, ''hundred'' ';
1592
------ multi table tests ------
1595
insert into t1(a,b) values (1, 'one'), (2, 'two'), (3, 'three') ;
1596
insert into t9 (c1,c21)
1597
values (1, 'one'), (2, 'two'), (3, 'three') ;
1598
prepare stmt_delete from " delete t1, t9
1599
from t1, t9 where t1.a=t9.c1 and t1.b='updated' ";
1600
prepare stmt_update from " update t1, t9
1601
set t1.b='updated', t9.c21='updated'
1602
where t1.a=t9.c1 and t1.a=? ";
1603
prepare stmt_select1 from " select a, b from t1 order by a" ;
1604
prepare stmt_select2 from " select c1, c21 from t9 order by c1" ;
1606
execute stmt_update using @arg00 ;
1607
execute stmt_delete ;
1608
execute stmt_select1 ;
1612
execute stmt_select2 ;
1616
set @arg00= @arg00 + 1 ;
1617
execute stmt_update using @arg00 ;
1618
execute stmt_delete ;
1619
execute stmt_select1 ;
1622
execute stmt_select2 ;
1625
set @arg00= @arg00 + 1 ;
1626
execute stmt_update using @arg00 ;
1627
execute stmt_delete ;
1628
execute stmt_select1 ;
1630
execute stmt_select2 ;
1632
set @arg00= @arg00 + 1 ;
1634
insert into t1 values (1,'one');
1635
insert into t1 values (2,'two');
1636
insert into t1 values (3,'three');
1637
insert into t1 values (4,'four');
1641
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1642
c10= 1, c11= 1, c12 = 1,
1643
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1644
c16= '11:11:11', c17= '2004',
1645
c18= 1, c19=true, c20= 'a', c21= '123456789a',
1646
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1647
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1648
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1650
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1651
c10= 9, c11= 9, c12 = 9,
1652
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1653
c16= '11:11:11', c17= '2004',
1654
c18= 1, c19=false, c20= 'a', c21= '123456789a',
1655
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1656
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1657
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1659
insert into t1 values(0,NULL) ;
1660
set @duplicate='duplicate ' ;
1663
select a,b from t1 where a < 5 order by a ;
1670
insert into t1 select a + @1000, concat(@duplicate,b) from t1
1673
info: Records: 5 Duplicates: 0 Warnings: 0
1674
select a,b from t1 where a >= 1000 order by a ;
1679
1003 duplicate three
1681
delete from t1 where a >= 1000 ;
1682
prepare stmt1 from ' insert into t1 select a + ?, concat(?,b) from t1
1684
execute stmt1 using @1000, @duplicate, @5;
1686
info: Records: 5 Duplicates: 0 Warnings: 0
1687
select a,b from t1 where a >= 1000 order by a ;
1692
1003 duplicate three
1694
delete from t1 where a >= 1000 ;
1700
drop table if exists t2;
1701
create table t2 like t1 ;
1702
insert into t2 (b,a)
1703
select @duplicate, sum(first.a) from t1 first, t1 second
1704
where first.a <> @5 and second.b = first.b
1705
and second.b <> @five
1707
having sum(second.a) > @2
1709
select b, a + @100 from t1
1710
where (a,b) in ( select sqrt(a+@1)+CAST(@float AS signed),b
1713
info: Records: 3 Duplicates: 0 Warnings: 0
1714
select a,b from t2 order by a ;
1720
prepare stmt1 from ' insert into t2 (b,a)
1721
select ?, sum(first.a)
1722
from t1 first, t1 second
1723
where first.a <> ? and second.b = first.b and second.b <> ?
1725
having sum(second.a) > ?
1727
select b, a + ? from t1
1728
where (a,b) in ( select sqrt(a+?)+CAST(? AS signed),b
1730
execute stmt1 using @duplicate, @5, @five, @2, @100, @1, @float ;
1732
info: Records: 3 Duplicates: 0 Warnings: 0
1733
select a,b from t2 order by a ;
1739
drop table if exists t5 ;
1742
set @arg03= 80.00000000000e-1;
1744
set @arg05= CAST('abc' as binary) ;
1745
set @arg06= '1991-08-05' ;
1746
set @arg07= CAST('1991-08-05' as date);
1747
set @arg08= '1991-08-05 01:01:01' ;
1748
set @arg09= CAST('1991-08-05 01:01:01' as datetime) ;
1749
set @arg10= unix_timestamp('1991-01-01 01:01:01');
1750
set @arg11= YEAR('1991-01-01 01:01:01');
1757
set @arg15= CAST('abc' as binary) ;
1759
create table t5 engine = MyISAM as select
1760
8 as const01, @arg01 as param01,
1761
8.0 as const02, @arg02 as param02,
1762
80.00000000000e-1 as const03, @arg03 as param03,
1763
'abc' as const04, @arg04 as param04,
1764
CAST('abc' as binary) as const05, @arg05 as param05,
1765
'1991-08-05' as const06, @arg06 as param06,
1766
CAST('1991-08-05' as date) as const07, @arg07 as param07,
1767
'1991-08-05 01:01:01' as const08, @arg08 as param08,
1768
CAST('1991-08-05 01:01:01' as datetime) as const09, @arg09 as param09,
1769
unix_timestamp('1991-01-01 01:01:01') as const10, @arg10 as param10,
1770
YEAR('1991-01-01 01:01:01') as const11, @arg11 as param11,
1771
NULL as const12, @arg12 as param12,
1775
show create table t5 ;
1777
t5 CREATE TABLE `t5` (
1778
`const01` int(1) NOT NULL DEFAULT '0',
1779
`param01` bigint(20) DEFAULT NULL,
1780
`const02` decimal(2,1) NOT NULL DEFAULT '0.0',
1781
`param02` decimal(65,30) DEFAULT NULL,
1782
`const03` double NOT NULL DEFAULT '0',
1783
`param03` double DEFAULT NULL,
1784
`const04` varchar(3) NOT NULL DEFAULT '',
1786
`const05` varbinary(3) NOT NULL DEFAULT '',
1788
`const06` varchar(10) NOT NULL DEFAULT '',
1790
`const07` date DEFAULT NULL,
1792
`const08` varchar(19) NOT NULL DEFAULT '',
1794
`const09` datetime DEFAULT NULL,
1796
`const10` int(10) NOT NULL DEFAULT '0',
1797
`param10` bigint(20) DEFAULT NULL,
1798
`const11` int(4) DEFAULT NULL,
1799
`param11` bigint(20) DEFAULT NULL,
1800
`const12` binary(0) DEFAULT NULL,
1801
`param12` bigint(20) DEFAULT NULL,
1802
`param13` decimal(65,30) DEFAULT NULL,
1805
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1807
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1808
def test t5 t5 const01 const01 3 1 1 N 32769 0 63
1809
def test t5 t5 param01 param01 8 20 1 Y 32768 0 63
1810
def test t5 t5 const02 const02 246 4 3 N 1 1 63
1811
def test t5 t5 param02 param02 246 67 32 Y 0 30 63
1812
def test t5 t5 const03 const03 5 17 1 N 32769 31 63
1813
def test t5 t5 param03 param03 5 23 1 Y 32768 31 63
1814
def test t5 t5 const04 const04 253 3 3 N 1 0 8
1815
def test t5 t5 param04 param04 252 4294967295 3 Y 16 0 8
1816
def test t5 t5 const05 const05 253 3 3 N 129 0 63
1817
def test t5 t5 param05 param05 252 4294967295 3 Y 144 0 63
1818
def test t5 t5 const06 const06 253 10 10 N 1 0 8
1819
def test t5 t5 param06 param06 252 4294967295 10 Y 16 0 8
1820
def test t5 t5 const07 const07 10 10 10 Y 128 0 63
1821
def test t5 t5 param07 param07 252 4294967295 10 Y 144 0 63
1822
def test t5 t5 const08 const08 253 19 19 N 1 0 8
1823
def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8
1824
def test t5 t5 const09 const09 12 19 19 Y 128 0 63
1825
def test t5 t5 param09 param09 252 4294967295 19 Y 144 0 63
1826
def test t5 t5 const10 const10 3 10 9 N 32769 0 63
1827
def test t5 t5 param10 param10 8 20 9 Y 32768 0 63
1828
def test t5 t5 const11 const11 3 4 4 Y 32768 0 63
1829
def test t5 t5 param11 param11 8 20 4 Y 32768 0 63
1830
def test t5 t5 const12 const12 254 0 0 Y 128 0 63
1831
def test t5 t5 param12 param12 8 20 0 Y 32768 0 63
1832
def test t5 t5 param13 param13 246 67 0 Y 0 30 63
1833
def test t5 t5 param14 param14 252 4294967295 0 Y 16 0 8
1834
def test t5 t5 param15 param15 252 4294967295 0 Y 144 0 63
1838
param02 8.000000000000000000000000000000
1849
const08 1991-08-05 01:01:01
1850
param08 1991-08-05 01:01:01
1851
const09 1991-08-05 01:01:01
1852
param09 1991-08-05 01:01:01
1864
------ data type conversion tests ------
1866
insert into t1 values (1,'one');
1867
insert into t1 values (2,'two');
1868
insert into t1 values (3,'three');
1869
insert into t1 values (4,'four');
1873
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1874
c10= 1, c11= 1, c12 = 1,
1875
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1876
c16= '11:11:11', c17= '2004',
1877
c18= 1, c19=true, c20= 'a', c21= '123456789a',
1878
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1879
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1880
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1882
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1883
c10= 9, c11= 9, c12 = 9,
1884
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1885
c16= '11:11:11', c17= '2004',
1886
c18= 1, c19=false, c20= 'a', c21= '123456789a',
1887
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1888
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1889
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1891
insert into t9 set c1= 0, c15= '1991-01-01 01:01:01' ;
1892
select * from t9 order by c1 ;
1893
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
1894
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
1895
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
1896
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
1898
------ select @parameter:= column ------
1899
prepare full_info from "select @arg01, @arg02, @arg03, @arg04,
1900
@arg05, @arg06, @arg07, @arg08,
1901
@arg09, @arg10, @arg11, @arg12,
1902
@arg13, @arg14, @arg15, @arg16,
1903
@arg17, @arg18, @arg19, @arg20,
1904
@arg21, @arg22, @arg23, @arg24,
1905
@arg25, @arg26, @arg27, @arg28,
1906
@arg29, @arg30, @arg31, @arg32" ;
1907
select @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,
1908
@arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,
1909
@arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
1910
@arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
1911
@arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
1912
@arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
1913
@arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
1914
@arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
1915
from t9 where c1= 1 ;
1916
@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
1917
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
1919
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1920
def @arg01 8 20 1 Y 32896 0 63
1921
def @arg02 8 20 1 Y 32896 0 63
1922
def @arg03 8 20 1 Y 32896 0 63
1923
def @arg04 8 20 1 Y 32896 0 63
1924
def @arg05 8 20 1 Y 32896 0 63
1925
def @arg06 8 20 1 Y 32896 0 63
1926
def @arg07 5 23 1 Y 32896 31 63
1927
def @arg08 5 23 1 Y 32896 31 63
1928
def @arg09 5 23 1 Y 32896 31 63
1929
def @arg10 5 23 1 Y 32896 31 63
1930
def @arg11 246 83 6 Y 128 30 63
1931
def @arg12 246 83 6 Y 128 30 63
1932
def @arg13 251 16777216 10 Y 128 31 63
1933
def @arg14 251 16777216 19 Y 128 31 63
1934
def @arg15 251 16777216 19 Y 128 31 63
1935
def @arg16 251 16777216 8 Y 128 31 63
1936
def @arg17 8 20 4 Y 32928 0 63
1937
def @arg18 8 20 1 Y 32896 0 63
1938
def @arg19 8 20 1 Y 32896 0 63
1939
def @arg20 251 16777216 1 Y 0 31 8
1940
def @arg21 251 16777216 10 Y 0 31 8
1941
def @arg22 251 16777216 30 Y 0 31 8
1942
def @arg23 251 16777216 8 Y 128 31 63
1943
def @arg24 251 16777216 8 Y 0 31 8
1944
def @arg25 251 16777216 4 Y 128 31 63
1945
def @arg26 251 16777216 4 Y 0 31 8
1946
def @arg27 251 16777216 10 Y 128 31 63
1947
def @arg28 251 16777216 10 Y 0 31 8
1948
def @arg29 251 16777216 8 Y 128 31 63
1949
def @arg30 251 16777216 8 Y 0 31 8
1950
def @arg31 251 16777216 3 Y 0 31 8
1951
def @arg32 251 16777216 6 Y 0 31 8
1952
@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
1953
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
1954
select @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,
1955
@arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,
1956
@arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
1957
@arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
1958
@arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
1959
@arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
1960
@arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
1961
@arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
1962
from t9 where c1= 0 ;
1963
@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
1964
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
1966
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1967
def @arg01 8 20 1 Y 32896 0 63
1968
def @arg02 8 20 0 Y 32896 0 63
1969
def @arg03 8 20 0 Y 32896 0 63
1970
def @arg04 8 20 0 Y 32896 0 63
1971
def @arg05 8 20 0 Y 32896 0 63
1972
def @arg06 8 20 0 Y 32896 0 63
1973
def @arg07 5 23 0 Y 32896 31 63
1974
def @arg08 5 23 0 Y 32896 31 63
1975
def @arg09 5 23 0 Y 32896 31 63
1976
def @arg10 5 23 0 Y 32896 31 63
1977
def @arg11 246 83 0 Y 128 30 63
1978
def @arg12 246 83 0 Y 128 30 63
1979
def @arg13 251 16777216 0 Y 128 31 63
1980
def @arg14 251 16777216 0 Y 128 31 63
1981
def @arg15 251 16777216 19 Y 128 31 63
1982
def @arg16 251 16777216 0 Y 128 31 63
1983
def @arg17 8 20 0 Y 32928 0 63
1984
def @arg18 8 20 0 Y 32896 0 63
1985
def @arg19 8 20 0 Y 32896 0 63
1986
def @arg20 251 16777216 0 Y 0 31 8
1987
def @arg21 251 16777216 0 Y 0 31 8
1988
def @arg22 251 16777216 0 Y 0 31 8
1989
def @arg23 251 16777216 0 Y 128 31 63
1990
def @arg24 251 16777216 0 Y 0 31 8
1991
def @arg25 251 16777216 0 Y 128 31 63
1992
def @arg26 251 16777216 0 Y 0 31 8
1993
def @arg27 251 16777216 0 Y 128 31 63
1994
def @arg28 251 16777216 0 Y 0 31 8
1995
def @arg29 251 16777216 0 Y 128 31 63
1996
def @arg30 251 16777216 0 Y 0 31 8
1997
def @arg31 251 16777216 0 Y 0 31 8
1998
def @arg32 251 16777216 0 Y 0 31 8
1999
@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
2000
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
2001
prepare stmt1 from "select
2002
@arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,
2003
@arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,
2004
@arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
2005
@arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
2006
@arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
2007
@arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
2008
@arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
2009
@arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
2010
from t9 where c1= ?" ;
2012
execute stmt1 using @my_key ;
2013
@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
2014
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
2016
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2017
def @arg01 8 20 1 Y 32896 0 63
2018
def @arg02 8 20 1 Y 32896 0 63
2019
def @arg03 8 20 1 Y 32896 0 63
2020
def @arg04 8 20 1 Y 32896 0 63
2021
def @arg05 8 20 1 Y 32896 0 63
2022
def @arg06 8 20 1 Y 32896 0 63
2023
def @arg07 5 23 1 Y 32896 31 63
2024
def @arg08 5 23 1 Y 32896 31 63
2025
def @arg09 5 23 1 Y 32896 31 63
2026
def @arg10 5 23 1 Y 32896 31 63
2027
def @arg11 246 83 6 Y 128 30 63
2028
def @arg12 246 83 6 Y 128 30 63
2029
def @arg13 251 16777216 10 Y 128 31 63
2030
def @arg14 251 16777216 19 Y 128 31 63
2031
def @arg15 251 16777216 19 Y 128 31 63
2032
def @arg16 251 16777216 8 Y 128 31 63
2033
def @arg17 8 20 4 Y 32928 0 63
2034
def @arg18 8 20 1 Y 32896 0 63
2035
def @arg19 8 20 1 Y 32896 0 63
2036
def @arg20 251 16777216 1 Y 0 31 8
2037
def @arg21 251 16777216 10 Y 0 31 8
2038
def @arg22 251 16777216 30 Y 0 31 8
2039
def @arg23 251 16777216 8 Y 128 31 63
2040
def @arg24 251 16777216 8 Y 0 31 8
2041
def @arg25 251 16777216 4 Y 128 31 63
2042
def @arg26 251 16777216 4 Y 0 31 8
2043
def @arg27 251 16777216 10 Y 128 31 63
2044
def @arg28 251 16777216 10 Y 0 31 8
2045
def @arg29 251 16777216 8 Y 128 31 63
2046
def @arg30 251 16777216 8 Y 0 31 8
2047
def @arg31 251 16777216 3 Y 0 31 8
2048
def @arg32 251 16777216 6 Y 0 31 8
2049
@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
2050
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
2052
execute stmt1 using @my_key ;
2053
@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
2054
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
2056
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2057
def @arg01 8 20 1 Y 32896 0 63
2058
def @arg02 8 20 0 Y 32896 0 63
2059
def @arg03 8 20 0 Y 32896 0 63
2060
def @arg04 8 20 0 Y 32896 0 63
2061
def @arg05 8 20 0 Y 32896 0 63
2062
def @arg06 8 20 0 Y 32896 0 63
2063
def @arg07 5 23 0 Y 32896 31 63
2064
def @arg08 5 23 0 Y 32896 31 63
2065
def @arg09 5 23 0 Y 32896 31 63
2066
def @arg10 5 23 0 Y 32896 31 63
2067
def @arg11 246 83 0 Y 128 30 63
2068
def @arg12 246 83 0 Y 128 30 63
2069
def @arg13 251 16777216 0 Y 128 31 63
2070
def @arg14 251 16777216 0 Y 128 31 63
2071
def @arg15 251 16777216 19 Y 128 31 63
2072
def @arg16 251 16777216 0 Y 128 31 63
2073
def @arg17 8 20 0 Y 32928 0 63
2074
def @arg18 8 20 0 Y 32896 0 63
2075
def @arg19 8 20 0 Y 32896 0 63
2076
def @arg20 251 16777216 0 Y 0 31 8
2077
def @arg21 251 16777216 0 Y 0 31 8
2078
def @arg22 251 16777216 0 Y 0 31 8
2079
def @arg23 251 16777216 0 Y 128 31 63
2080
def @arg24 251 16777216 0 Y 0 31 8
2081
def @arg25 251 16777216 0 Y 128 31 63
2082
def @arg26 251 16777216 0 Y 0 31 8
2083
def @arg27 251 16777216 0 Y 128 31 63
2084
def @arg28 251 16777216 0 Y 0 31 8
2085
def @arg29 251 16777216 0 Y 128 31 63
2086
def @arg30 251 16777216 0 Y 0 31 8
2087
def @arg31 251 16777216 0 Y 0 31 8
2088
def @arg32 251 16777216 0 Y 0 31 8
2089
@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
2090
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
2091
prepare stmt1 from "select ? := c1 from t9 where c1= 1" ;
2092
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
2094
------ select column, .. into @parm,.. ------
2095
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
2096
c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
2097
c25, c26, c27, c28, c29, c30, c31, c32
2098
into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
2099
@arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
2100
@arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
2101
@arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
2102
from t9 where c1= 1 ;
2104
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2105
def @arg01 8 20 1 Y 32896 0 63
2106
def @arg02 8 20 1 Y 32896 0 63
2107
def @arg03 8 20 1 Y 32896 0 63
2108
def @arg04 8 20 1 Y 32896 0 63
2109
def @arg05 8 20 1 Y 32896 0 63
2110
def @arg06 8 20 1 Y 32896 0 63
2111
def @arg07 5 23 1 Y 32896 31 63
2112
def @arg08 5 23 1 Y 32896 31 63
2113
def @arg09 5 23 1 Y 32896 31 63
2114
def @arg10 5 23 1 Y 32896 31 63
2115
def @arg11 246 83 6 Y 128 30 63
2116
def @arg12 246 83 6 Y 128 30 63
2117
def @arg13 251 16777216 10 Y 128 31 63
2118
def @arg14 251 16777216 19 Y 128 31 63
2119
def @arg15 251 16777216 19 Y 128 31 63
2120
def @arg16 251 16777216 8 Y 128 31 63
2121
def @arg17 8 20 4 Y 32928 0 63
2122
def @arg18 8 20 1 Y 32896 0 63
2123
def @arg19 8 20 1 Y 32896 0 63
2124
def @arg20 251 16777216 1 Y 0 31 8
2125
def @arg21 251 16777216 10 Y 0 31 8
2126
def @arg22 251 16777216 30 Y 0 31 8
2127
def @arg23 251 16777216 8 Y 128 31 63
2128
def @arg24 251 16777216 8 Y 0 31 8
2129
def @arg25 251 16777216 4 Y 128 31 63
2130
def @arg26 251 16777216 4 Y 0 31 8
2131
def @arg27 251 16777216 10 Y 128 31 63
2132
def @arg28 251 16777216 10 Y 0 31 8
2133
def @arg29 251 16777216 8 Y 128 31 63
2134
def @arg30 251 16777216 8 Y 0 31 8
2135
def @arg31 251 16777216 3 Y 0 31 8
2136
def @arg32 251 16777216 6 Y 0 31 8
2137
@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
2138
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
2139
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
2140
c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
2141
c25, c26, c27, c28, c29, c30, c31, c32
2142
into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
2143
@arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
2144
@arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
2145
@arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
2146
from t9 where c1= 0 ;
2148
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2149
def @arg01 8 20 1 Y 32896 0 63
2150
def @arg02 8 20 0 Y 32896 0 63
2151
def @arg03 8 20 0 Y 32896 0 63
2152
def @arg04 8 20 0 Y 32896 0 63
2153
def @arg05 8 20 0 Y 32896 0 63
2154
def @arg06 8 20 0 Y 32896 0 63
2155
def @arg07 5 23 0 Y 32896 31 63
2156
def @arg08 5 23 0 Y 32896 31 63
2157
def @arg09 5 23 0 Y 32896 31 63
2158
def @arg10 5 23 0 Y 32896 31 63
2159
def @arg11 246 83 0 Y 128 30 63
2160
def @arg12 246 83 0 Y 128 30 63
2161
def @arg13 251 16777216 0 Y 128 31 63
2162
def @arg14 251 16777216 0 Y 128 31 63
2163
def @arg15 251 16777216 19 Y 128 31 63
2164
def @arg16 251 16777216 0 Y 128 31 63
2165
def @arg17 8 20 0 Y 32928 0 63
2166
def @arg18 8 20 0 Y 32896 0 63
2167
def @arg19 8 20 0 Y 32896 0 63
2168
def @arg20 251 16777216 0 Y 0 31 8
2169
def @arg21 251 16777216 0 Y 0 31 8
2170
def @arg22 251 16777216 0 Y 0 31 8
2171
def @arg23 251 16777216 0 Y 128 31 63
2172
def @arg24 251 16777216 0 Y 0 31 8
2173
def @arg25 251 16777216 0 Y 128 31 63
2174
def @arg26 251 16777216 0 Y 0 31 8
2175
def @arg27 251 16777216 0 Y 128 31 63
2176
def @arg28 251 16777216 0 Y 0 31 8
2177
def @arg29 251 16777216 0 Y 128 31 63
2178
def @arg30 251 16777216 0 Y 0 31 8
2179
def @arg31 251 16777216 0 Y 0 31 8
2180
def @arg32 251 16777216 0 Y 0 31 8
2181
@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
2182
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
2183
prepare stmt1 from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
2184
c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
2185
c25, c26, c27, c28, c29, c30, c31, c32
2186
into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
2187
@arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
2188
@arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
2189
@arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
2190
from t9 where c1= ?" ;
2192
execute stmt1 using @my_key ;
2194
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2195
def @arg01 8 20 1 Y 32896 0 63
2196
def @arg02 8 20 1 Y 32896 0 63
2197
def @arg03 8 20 1 Y 32896 0 63
2198
def @arg04 8 20 1 Y 32896 0 63
2199
def @arg05 8 20 1 Y 32896 0 63
2200
def @arg06 8 20 1 Y 32896 0 63
2201
def @arg07 5 23 1 Y 32896 31 63
2202
def @arg08 5 23 1 Y 32896 31 63
2203
def @arg09 5 23 1 Y 32896 31 63
2204
def @arg10 5 23 1 Y 32896 31 63
2205
def @arg11 246 83 6 Y 128 30 63
2206
def @arg12 246 83 6 Y 128 30 63
2207
def @arg13 251 16777216 10 Y 128 31 63
2208
def @arg14 251 16777216 19 Y 128 31 63
2209
def @arg15 251 16777216 19 Y 128 31 63
2210
def @arg16 251 16777216 8 Y 128 31 63
2211
def @arg17 8 20 4 Y 32928 0 63
2212
def @arg18 8 20 1 Y 32896 0 63
2213
def @arg19 8 20 1 Y 32896 0 63
2214
def @arg20 251 16777216 1 Y 0 31 8
2215
def @arg21 251 16777216 10 Y 0 31 8
2216
def @arg22 251 16777216 30 Y 0 31 8
2217
def @arg23 251 16777216 8 Y 128 31 63
2218
def @arg24 251 16777216 8 Y 0 31 8
2219
def @arg25 251 16777216 4 Y 128 31 63
2220
def @arg26 251 16777216 4 Y 0 31 8
2221
def @arg27 251 16777216 10 Y 128 31 63
2222
def @arg28 251 16777216 10 Y 0 31 8
2223
def @arg29 251 16777216 8 Y 128 31 63
2224
def @arg30 251 16777216 8 Y 0 31 8
2225
def @arg31 251 16777216 3 Y 0 31 8
2226
def @arg32 251 16777216 6 Y 0 31 8
2227
@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
2228
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
2230
execute stmt1 using @my_key ;
2232
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2233
def @arg01 8 20 1 Y 32896 0 63
2234
def @arg02 8 20 0 Y 32896 0 63
2235
def @arg03 8 20 0 Y 32896 0 63
2236
def @arg04 8 20 0 Y 32896 0 63
2237
def @arg05 8 20 0 Y 32896 0 63
2238
def @arg06 8 20 0 Y 32896 0 63
2239
def @arg07 5 23 0 Y 32896 31 63
2240
def @arg08 5 23 0 Y 32896 31 63
2241
def @arg09 5 23 0 Y 32896 31 63
2242
def @arg10 5 23 0 Y 32896 31 63
2243
def @arg11 246 83 0 Y 128 30 63
2244
def @arg12 246 83 0 Y 128 30 63
2245
def @arg13 251 16777216 0 Y 128 31 63
2246
def @arg14 251 16777216 0 Y 128 31 63
2247
def @arg15 251 16777216 19 Y 128 31 63
2248
def @arg16 251 16777216 0 Y 128 31 63
2249
def @arg17 8 20 0 Y 32928 0 63
2250
def @arg18 8 20 0 Y 32896 0 63
2251
def @arg19 8 20 0 Y 32896 0 63
2252
def @arg20 251 16777216 0 Y 0 31 8
2253
def @arg21 251 16777216 0 Y 0 31 8
2254
def @arg22 251 16777216 0 Y 0 31 8
2255
def @arg23 251 16777216 0 Y 128 31 63
2256
def @arg24 251 16777216 0 Y 0 31 8
2257
def @arg25 251 16777216 0 Y 128 31 63
2258
def @arg26 251 16777216 0 Y 0 31 8
2259
def @arg27 251 16777216 0 Y 128 31 63
2260
def @arg28 251 16777216 0 Y 0 31 8
2261
def @arg29 251 16777216 0 Y 128 31 63
2262
def @arg30 251 16777216 0 Y 0 31 8
2263
def @arg31 251 16777216 0 Y 0 31 8
2264
def @arg32 251 16777216 0 Y 0 31 8
2265
@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
2266
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
2267
prepare stmt1 from "select c1 into ? from t9 where c1= 1" ;
2268
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
2270
-- insert into numeric columns --
2272
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2274
( 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20 ) ;
2277
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2279
( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2280
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2281
prepare stmt1 from "insert into t9
2282
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2284
( 22, 22, 22, 22, 22, 22, 22, 22, 22, 22, 22 )" ;
2287
prepare stmt2 from "insert into t9
2288
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2290
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2291
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2292
@arg00, @arg00, @arg00, @arg00 ;
2294
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2296
( 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0,
2297
30.0, 30.0, 30.0 ) ;
2300
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2302
( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2303
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2304
prepare stmt1 from "insert into t9
2305
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2307
( 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0,
2308
32.0, 32.0, 32.0 )" ;
2311
prepare stmt2 from "insert into t9
2312
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2314
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2315
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2316
@arg00, @arg00, @arg00, @arg00 ;
2318
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2320
( '40', '40', '40', '40', '40', '40', '40', '40',
2321
'40', '40', '40' ) ;
2324
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2326
( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2327
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2328
prepare stmt1 from "insert into t9
2329
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2331
( '42', '42', '42', '42', '42', '42', '42', '42',
2332
'42', '42', '42' )" ;
2335
prepare stmt2 from "insert into t9
2336
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2338
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2339
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2340
@arg00, @arg00, @arg00, @arg00 ;
2342
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2344
( CAST('50' as binary), CAST('50' as binary),
2345
CAST('50' as binary), CAST('50' as binary), CAST('50' as binary),
2346
CAST('50' as binary), CAST('50' as binary), CAST('50' as binary),
2347
CAST('50' as binary), CAST('50' as binary), CAST('50' as binary) ) ;
2348
set @arg00= CAST('51' as binary) ;
2350
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2352
( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2353
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2354
prepare stmt1 from "insert into t9
2355
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2357
( CAST('52' as binary), CAST('52' as binary),
2358
CAST('52' as binary), CAST('52' as binary), CAST('52' as binary),
2359
CAST('52' as binary), CAST('52' as binary), CAST('52' as binary),
2360
CAST('52' as binary), CAST('52' as binary), CAST('52' as binary) )" ;
2362
set @arg00= CAST('53' as binary) ;
2363
prepare stmt2 from "insert into t9
2364
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2366
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2367
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2368
@arg00, @arg00, @arg00, @arg00 ;
2372
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2374
( 60, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2375
NULL, NULL, NULL ) ;
2377
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2379
( 61, @arg00, @arg00, @arg00, @arg00, @arg00,
2380
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2381
prepare stmt1 from "insert into t9
2382
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2384
( 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2385
NULL, NULL, NULL )" ;
2387
prepare stmt2 from "insert into t9
2388
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2390
( 63, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2391
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2392
@arg00, @arg00, @arg00, @arg00 ;
2396
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2398
( 71, @arg00, @arg00, @arg00, @arg00, @arg00,
2399
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2400
prepare stmt2 from "insert into t9
2401
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2403
( 73, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2404
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2405
@arg00, @arg00, @arg00, @arg00 ;
2409
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2411
( 81, @arg00, @arg00, @arg00, @arg00, @arg00,
2412
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2413
prepare stmt2 from "insert into t9
2414
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2416
( 83, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2417
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2418
@arg00, @arg00, @arg00, @arg00 ;
2419
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12
2420
from t9 where c1 >= 20
2422
c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c12
2423
20 20 20 20 20 20 20 20 20 20 20.0000
2424
21 21 21 21 21 21 21 21 21 21 21.0000
2425
22 22 22 22 22 22 22 22 22 22 22.0000
2426
23 23 23 23 23 23 23 23 23 23 23.0000
2427
30 30 30 30 30 30 30 30 30 30 30.0000
2428
31 31 31 31 31 31 31 31 31 31 31.0000
2429
32 32 32 32 32 32 32 32 32 32 32.0000
2430
33 33 33 33 33 33 33 33 33 33 33.0000
2431
40 40 40 40 40 40 40 40 40 40 40.0000
2432
41 41 41 41 41 41 41 41 41 41 41.0000
2433
42 42 42 42 42 42 42 42 42 42 42.0000
2434
43 43 43 43 43 43 43 43 43 43 43.0000
2435
50 50 50 50 50 50 50 50 50 50 50.0000
2436
51 51 51 51 51 51 51 51 51 51 51.0000
2437
52 52 52 52 52 52 52 52 52 52 52.0000
2438
53 53 53 53 53 53 53 53 53 53 53.0000
2439
60 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2440
61 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2441
62 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2442
63 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2443
71 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2444
73 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2445
81 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2446
83 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2448
-- select .. where numeric column = .. --
2450
select 'true' as found from t9
2451
where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20
2452
and c8= 20 and c9= 20 and c10= 20 and c12= 20;
2455
select 'true' as found from t9
2456
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
2457
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
2461
prepare stmt1 from "select 'true' as found from t9
2462
where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20
2463
and c8= 20 and c9= 20 and c10= 20 and c12= 20 ";
2467
prepare stmt1 from "select 'true' as found from t9
2468
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
2469
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
2471
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2472
@arg00, @arg00, @arg00, @arg00 ;
2476
select 'true' as found from t9
2477
where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0
2478
and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0;
2481
select 'true' as found from t9
2482
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
2483
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
2487
prepare stmt1 from "select 'true' as found from t9
2488
where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0
2489
and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0 ";
2493
prepare stmt1 from "select 'true' as found from t9
2494
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
2495
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
2497
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2498
@arg00, @arg00, @arg00, @arg00 ;
2501
select 'true' as found from t9
2502
where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'
2503
and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20';
2506
prepare stmt1 from "select 'true' as found from t9
2507
where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'
2508
and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20' ";
2513
select 'true' as found from t9
2514
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
2515
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
2519
prepare stmt1 from "select 'true' as found from t9
2520
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
2521
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
2523
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2524
@arg00, @arg00, @arg00, @arg00 ;
2527
select 'true' as found from t9
2528
where c1= CAST('20' as binary) and c2= CAST('20' as binary) and
2529
c3= CAST('20' as binary) and c4= CAST('20' as binary) and
2530
c5= CAST('20' as binary) and c6= CAST('20' as binary) and
2531
c7= CAST('20' as binary) and c8= CAST('20' as binary) and
2532
c9= CAST('20' as binary) and c10= CAST('20' as binary) and
2533
c12= CAST('20' as binary);
2536
prepare stmt1 from "select 'true' as found from t9
2537
where c1= CAST('20' as binary) and c2= CAST('20' as binary) and
2538
c3= CAST('20' as binary) and c4= CAST('20' as binary) and
2539
c5= CAST('20' as binary) and c6= CAST('20' as binary) and
2540
c7= CAST('20' as binary) and c8= CAST('20' as binary) and
2541
c9= CAST('20' as binary) and c10= CAST('20' as binary) and
2542
c12= CAST('20' as binary) ";
2546
set @arg00= CAST('20' as binary) ;
2547
select 'true' as found from t9
2548
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
2549
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
2553
prepare stmt1 from "select 'true' as found from t9
2554
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
2555
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
2557
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2558
@arg00, @arg00, @arg00, @arg00 ;
2563
-- some numeric overflow experiments --
2564
prepare my_insert from "insert into t9
2565
( c21, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2567
( 'O', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2568
prepare my_select from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12
2569
from t9 where c21 = 'O' ";
2570
prepare my_delete from "delete from t9 where c21 = 'O' ";
2571
set @arg00= 9223372036854775807 ;
2572
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2573
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2575
Warning 1264 Out of range value for column 'c1' at row 1
2576
Warning 1264 Out of range value for column 'c2' at row 1
2577
Warning 1264 Out of range value for column 'c3' at row 1
2578
Warning 1264 Out of range value for column 'c4' at row 1
2579
Warning 1264 Out of range value for column 'c5' at row 1
2580
Warning 1264 Out of range value for column 'c12' at row 1
2587
c6 9223372036854775807
2589
c8 9.223372036854776e18
2590
c9 9.223372036854776e18
2591
c10 9.223372036854776e18
2594
set @arg00= '9223372036854775807' ;
2595
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2596
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2598
Warning 1264 Out of range value for column 'c1' at row 1
2599
Warning 1264 Out of range value for column 'c2' at row 1
2600
Warning 1264 Out of range value for column 'c3' at row 1
2601
Warning 1264 Out of range value for column 'c4' at row 1
2602
Warning 1264 Out of range value for column 'c5' at row 1
2603
Warning 1264 Out of range value for column 'c12' at row 1
2610
c6 9223372036854775807
2612
c8 9.223372036854776e18
2613
c9 9.223372036854776e18
2614
c10 9.223372036854776e18
2617
set @arg00= -9223372036854775808 ;
2618
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2619
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2621
Warning 1264 Out of range value for column 'c1' at row 1
2622
Warning 1264 Out of range value for column 'c2' at row 1
2623
Warning 1264 Out of range value for column 'c3' at row 1
2624
Warning 1264 Out of range value for column 'c4' at row 1
2625
Warning 1264 Out of range value for column 'c5' at row 1
2626
Warning 1264 Out of range value for column 'c12' at row 1
2633
c6 -9223372036854775808
2635
c8 -9.223372036854776e18
2636
c9 -9.223372036854776e18
2637
c10 -9.223372036854776e18
2640
set @arg00= '-9223372036854775808' ;
2641
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2642
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2644
Warning 1264 Out of range value for column 'c1' at row 1
2645
Warning 1264 Out of range value for column 'c2' at row 1
2646
Warning 1264 Out of range value for column 'c3' at row 1
2647
Warning 1264 Out of range value for column 'c4' at row 1
2648
Warning 1264 Out of range value for column 'c5' at row 1
2649
Warning 1264 Out of range value for column 'c12' at row 1
2656
c6 -9223372036854775808
2658
c8 -9.223372036854776e18
2659
c9 -9.223372036854776e18
2660
c10 -9.223372036854776e18
2663
set @arg00= 1.11111111111111111111e+50 ;
2664
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2665
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2667
Warning 1264 Out of range value for column 'c1' at row 1
2668
Warning 1264 Out of range value for column 'c2' at row 1
2669
Warning 1264 Out of range value for column 'c3' at row 1
2670
Warning 1264 Out of range value for column 'c4' at row 1
2671
Warning 1264 Out of range value for column 'c5' at row 1
2672
Warning 1264 Out of range value for column 'c6' at row 1
2673
Warning 1264 Out of range value for column 'c7' at row 1
2674
Warning 1264 Out of range value for column 'c12' at row 1
2681
c6 9223372036854775807
2683
c8 1.111111111111111e50
2684
c9 1.111111111111111e50
2685
c10 1.111111111111111e50
2688
set @arg00= '1.11111111111111111111e+50' ;
2689
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2690
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2692
Warning 1264 Out of range value for column 'c1' at row 1
2693
Warning 1264 Out of range value for column 'c2' at row 1
2694
Warning 1264 Out of range value for column 'c3' at row 1
2695
Warning 1264 Out of range value for column 'c4' at row 1
2696
Warning 1264 Out of range value for column 'c5' at row 1
2697
Warning 1264 Out of range value for column 'c6' at row 1
2698
Warning 1264 Out of range value for column 'c7' at row 1
2699
Warning 1264 Out of range value for column 'c12' at row 1
2706
c6 9223372036854775807
2708
c8 1.111111111111111e50
2709
c9 1.111111111111111e50
2710
c10 1.111111111111111e50
2713
set @arg00= -1.11111111111111111111e+50 ;
2714
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2715
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2717
Warning 1264 Out of range value for column 'c1' at row 1
2718
Warning 1264 Out of range value for column 'c2' at row 1
2719
Warning 1264 Out of range value for column 'c3' at row 1
2720
Warning 1264 Out of range value for column 'c4' at row 1
2721
Warning 1264 Out of range value for column 'c5' at row 1
2722
Warning 1264 Out of range value for column 'c6' at row 1
2723
Warning 1264 Out of range value for column 'c7' at row 1
2724
Warning 1264 Out of range value for column 'c12' at row 1
2731
c6 -9223372036854775808
2733
c8 -1.111111111111111e50
2734
c9 -1.111111111111111e50
2735
c10 -1.111111111111111e50
2738
set @arg00= '-1.11111111111111111111e+50' ;
2739
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2740
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2742
Warning 1264 Out of range value for column 'c1' at row 1
2743
Warning 1264 Out of range value for column 'c2' at row 1
2744
Warning 1264 Out of range value for column 'c3' at row 1
2745
Warning 1264 Out of range value for column 'c4' at row 1
2746
Warning 1264 Out of range value for column 'c5' at row 1
2747
Warning 1264 Out of range value for column 'c6' at row 1
2748
Warning 1264 Out of range value for column 'c7' at row 1
2749
Warning 1264 Out of range value for column 'c12' at row 1
2756
c6 -9223372036854775808
2758
c8 -1.111111111111111e50
2759
c9 -1.111111111111111e50
2760
c10 -1.111111111111111e50
2764
-- insert into string columns --
2766
Warning 1265 Data truncated for column 'c20' at row 1
2768
Warning 1265 Data truncated for column 'c20' at row 1
2770
Warning 1265 Data truncated for column 'c20' at row 1
2772
Warning 1265 Data truncated for column 'c20' at row 1
2774
Warning 1265 Data truncated for column 'c20' at row 1
2776
Warning 1265 Data truncated for column 'c20' at row 1
2778
Warning 1265 Data truncated for column 'c20' at row 1
2780
Warning 1265 Data truncated for column 'c20' at row 1
2782
Warning 1265 Data truncated for column 'c20' at row 1
2784
Warning 1265 Data truncated for column 'c20' at row 1
2786
Warning 1265 Data truncated for column 'c20' at row 1
2788
Warning 1265 Data truncated for column 'c20' at row 1
2790
Warning 1265 Data truncated for column 'c20' at row 1
2792
Warning 1265 Data truncated for column 'c20' at row 1
2794
Warning 1265 Data truncated for column 'c20' at row 1
2796
Warning 1265 Data truncated for column 'c20' at row 1
2798
Warning 1265 Data truncated for column 'c20' at row 1
2800
Warning 1265 Data truncated for column 'c20' at row 1
2802
Warning 1265 Data truncated for column 'c20' at row 1
2804
Warning 1265 Data truncated for column 'c20' at row 1
2805
select c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30
2806
from t9 where c1 >= 20
2808
c1 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30
2809
20 2 20 20 20 20 20 20 20 20 20 20
2810
21 2 21 21 21 21 21 21 21 21 21 21
2811
22 2 22 22 22 22 22 22 22 22 22 22
2812
23 2 23 23 23 23 23 23 23 23 23 23
2813
30 3 30 30 30 30 30 30 30 30 30 30
2814
31 3 31 31 31 31 31 31 31 31 31 31
2815
32 3 32 32 32 32 32 32 32 32 32 32
2816
33 3 33 33 33 33 33 33 33 33 33 33
2817
40 4 40 40 40 40 40 40 40 40 40 40
2818
41 4 41 41 41 41 41 41 41 41 41 41
2819
42 4 42 42 42 42 42 42 42 42 42 42
2820
43 4 43 43 43 43 43 43 43 43 43 43
2821
50 5 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0
2822
51 5 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0
2823
52 5 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0
2824
53 5 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0
2825
54 5 54 54 54 54 54 54 54 54 54 54
2826
55 6 55 55 55 55 55 55 55 55 55 55
2827
56 6 56 56 56 56 56 56 56 56 56 56
2828
57 6 57 57 57 57 57 57 57 57 57 57
2829
60 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2830
61 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2831
62 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2832
63 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2833
71 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2834
73 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2835
81 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2836
83 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2838
-- select .. where string column = .. --
2840
select 'true' as found from t9
2841
where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and
2842
c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and
2843
c27= '20' and c28= '20' and c29= '20' and c30= '20' ;
2846
select 'true' as found from t9
2847
where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
2848
c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
2849
c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
2852
prepare stmt1 from "select 'true' as found from t9
2853
where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and
2854
c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and
2855
c27= '20' and c28= '20' and c29= '20' and c30= '20'" ;
2859
prepare stmt1 from "select 'true' as found from t9
2860
where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
2861
c21= ? and c22= ? and c23= ? and c25= ? and
2862
c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
2863
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2864
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2867
set @arg00= CAST('20' as binary);
2868
select 'true' as found from t9
2869
where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))
2870
= CAST('20' as binary) and c21= CAST('20' as binary)
2871
and c22= CAST('20' as binary) and c23= CAST('20' as binary) and
2872
c24= CAST('20' as binary) and c25= CAST('20' as binary) and
2873
c26= CAST('20' as binary) and c27= CAST('20' as binary) and
2874
c28= CAST('20' as binary) and c29= CAST('20' as binary) and
2875
c30= CAST('20' as binary) ;
2878
select 'true' as found from t9
2879
where c1= 20 and concat(c20,substr(@arg00,1+length(c20))) = @arg00 and
2880
c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
2881
c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and
2885
prepare stmt1 from "select 'true' as found from t9
2886
where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))
2887
= CAST('20' as binary) and c21= CAST('20' as binary)
2888
and c22= CAST('20' as binary) and c23= CAST('20' as binary) and
2889
c24= CAST('20' as binary) and c25= CAST('20' as binary) and
2890
c26= CAST('20' as binary) and c27= CAST('20' as binary) and
2891
c28= CAST('20' as binary) and c29= CAST('20' as binary) and
2892
c30= CAST('20' as binary)" ;
2896
prepare stmt1 from "select 'true' as found from t9
2897
where c1= 20 and concat(c20,substr(?,1+length(c20))) = ? and c21= ? and
2898
c22= ? and c23= ? and c25= ? and c26= ? and c27= ? and c28= ? and
2900
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2901
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2905
select 'true' as found from t9
2906
where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and
2907
c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and
2908
c27= 20 and c28= 20 and c29= 20 and c30= 20 ;
2911
select 'true' as found from t9
2912
where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
2913
c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
2914
c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
2917
prepare stmt1 from "select 'true' as found from t9
2918
where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and
2919
c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and
2920
c27= 20 and c28= 20 and c29= 20 and c30= 20" ;
2924
prepare stmt1 from "select 'true' as found from t9
2925
where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
2926
c21= ? and c22= ? and c23= ? and c25= ? and
2927
c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
2928
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2929
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2933
select 'true' as found from t9
2934
where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and
2935
c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and
2936
c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0 ;
2939
select 'true' as found from t9
2940
where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
2941
c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
2942
c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
2945
prepare stmt1 from "select 'true' as found from t9
2946
where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and
2947
c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and
2948
c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0" ;
2952
prepare stmt1 from "select 'true' as found from t9
2953
where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
2954
c21= ? and c22= ? and c23= ? and c25= ? and
2955
c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
2956
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2957
@arg00, @arg00, @arg00, @arg00, @arg00 ;
2962
-- insert into date/time columns --
2964
Note 1265 Data truncated for column 'c13' at row 1
2965
Warning 1265 Data truncated for column 'c17' at row 1
2967
Note 1265 Data truncated for column 'c13' at row 1
2968
Warning 1265 Data truncated for column 'c17' at row 1
2970
Note 1265 Data truncated for column 'c13' at row 1
2971
Warning 1265 Data truncated for column 'c17' at row 1
2973
Note 1265 Data truncated for column 'c13' at row 1
2974
Warning 1265 Data truncated for column 'c17' at row 1
2976
Note 1265 Data truncated for column 'c13' at row 1
2977
Warning 1265 Data truncated for column 'c17' at row 1
2979
Note 1265 Data truncated for column 'c13' at row 1
2980
Warning 1265 Data truncated for column 'c17' at row 1
2982
Note 1265 Data truncated for column 'c13' at row 1
2983
Warning 1265 Data truncated for column 'c17' at row 1
2985
Note 1265 Data truncated for column 'c13' at row 1
2986
Warning 1265 Data truncated for column 'c17' at row 1
2988
Warning 1264 Out of range value for column 'c13' at row 1
2989
Warning 1264 Out of range value for column 'c14' at row 1
2990
Warning 1265 Data truncated for column 'c15' at row 1
2991
Warning 1264 Out of range value for column 'c16' at row 1
2992
Warning 1264 Out of range value for column 'c17' at row 1
2994
Warning 1264 Out of range value for column 'c13' at row 1
2995
Warning 1264 Out of range value for column 'c14' at row 1
2996
Warning 1265 Data truncated for column 'c15' at row 1
2997
Warning 1264 Out of range value for column 'c16' at row 1
2998
Warning 1264 Out of range value for column 'c17' at row 1
3000
Warning 1264 Out of range value for column 'c13' at row 1
3001
Warning 1264 Out of range value for column 'c14' at row 1
3002
Warning 1265 Data truncated for column 'c15' at row 1
3003
Warning 1264 Out of range value for column 'c16' at row 1
3004
Warning 1264 Out of range value for column 'c17' at row 1
3006
Warning 1264 Out of range value for column 'c13' at row 1
3007
Warning 1264 Out of range value for column 'c14' at row 1
3008
Warning 1265 Data truncated for column 'c15' at row 1
3009
Warning 1264 Out of range value for column 'c16' at row 1
3010
Warning 1264 Out of range value for column 'c17' at row 1
3012
Warning 1265 Data truncated for column 'c15' at row 1
3013
Warning 1264 Out of range value for column 'c16' at row 1
3014
Warning 1264 Out of range value for column 'c17' at row 1
3016
Warning 1265 Data truncated for column 'c15' at row 1
3017
Warning 1264 Out of range value for column 'c16' at row 1
3018
Warning 1264 Out of range value for column 'c17' at row 1
3020
Warning 1265 Data truncated for column 'c15' at row 1
3021
Warning 1264 Out of range value for column 'c16' at row 1
3022
Warning 1264 Out of range value for column 'c17' at row 1
3024
Warning 1265 Data truncated for column 'c15' at row 1
3025
Warning 1264 Out of range value for column 'c16' at row 1
3026
Warning 1264 Out of range value for column 'c17' at row 1
3027
select c1, c13, c14, c15, c16, c17 from t9 order by c1 ;
3028
c1 c13 c14 c15 c16 c17
3029
20 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3030
21 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3031
22 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3032
23 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3033
30 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3034
31 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3035
32 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3036
33 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3037
40 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
3038
41 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
3039
42 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
3040
43 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
3041
50 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
3042
51 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
3043
52 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
3044
53 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
3045
60 NULL NULL 1991-01-01 01:01:01 NULL NULL
3046
61 NULL NULL 1991-01-01 01:01:01 NULL NULL
3047
62 NULL NULL 1991-01-01 01:01:01 NULL NULL
3048
63 NULL NULL 1991-01-01 01:01:01 NULL NULL
3049
71 NULL NULL 1991-01-01 01:01:01 NULL NULL
3050
73 NULL NULL 1991-01-01 01:01:01 NULL NULL
3051
81 NULL NULL 1991-01-01 01:01:01 NULL NULL
3052
83 NULL NULL 1991-01-01 01:01:01 NULL NULL
3054
-- select .. where date/time column = .. --
3055
set @arg00= '1991-01-01 01:01:01' ;
3056
select 'true' as found from t9
3057
where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
3058
c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
3059
c17= '1991-01-01 01:01:01' ;
3062
select 'true' as found from t9
3063
where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
3067
prepare stmt1 from "select 'true' as found from t9
3068
where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
3069
c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
3070
c17= '1991-01-01 01:01:01'" ;
3074
prepare stmt1 from "select 'true' as found from t9
3075
where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
3076
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
3079
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
3080
select 'true' as found from t9
3081
where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
3082
c14= CAST('1991-01-01 01:01:01' as datetime) and
3083
c15= CAST('1991-01-01 01:01:01' as datetime) and
3084
c16= CAST('1991-01-01 01:01:01' as datetime) and
3085
c17= CAST('1991-01-01 01:01:01' as datetime) ;
3088
select 'true' as found from t9
3089
where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
3093
prepare stmt1 from "select 'true' as found from t9
3094
where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
3095
c14= CAST('1991-01-01 01:01:01' as datetime) and
3096
c15= CAST('1991-01-01 01:01:01' as datetime) and
3097
c16= CAST('1991-01-01 01:01:01' as datetime) and
3098
c17= CAST('1991-01-01 01:01:01' as datetime)" ;
3102
prepare stmt1 from "select 'true' as found from t9
3103
where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
3104
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
3108
select 'true' as found from t9
3109
where c1= 20 and c17= 1991 ;
3112
select 'true' as found from t9
3113
where c1= 20 and c17= @arg00 ;
3116
prepare stmt1 from "select 'true' as found from t9
3117
where c1= 20 and c17= 1991" ;
3121
prepare stmt1 from "select 'true' as found from t9
3122
where c1= 20 and c17= ?" ;
3123
execute stmt1 using @arg00 ;
3126
set @arg00= 1.991e+3 ;
3127
select 'true' as found from t9
3128
where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;
3131
select 'true' as found from t9
3132
where c1= 20 and abs(c17 - @arg00) < 0.01 ;
3135
prepare stmt1 from "select 'true' as found from t9
3136
where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;
3140
prepare stmt1 from "select 'true' as found from t9
3141
where c1= 20 and abs(c17 - ?) < 0.01" ;
3142
execute stmt1 using @arg00 ;
added
removed
mysql-test/r/ps_maria.result
