2 drop table if exists t1, t9 ;
10 c1 tinyint, c2 smallint, c3 mediumint, c4 int,
11 c5 integer, c6 bigint, c7 float, c8 double,
12 c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
13 c13 date, c14 datetime, c15 timestamp, c16 time,
14 c17 year, c18 tinyint, c19 bool, c20 char,
15 c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
16 c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
17 c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'),
18 c32 set('monday', 'tuesday', 'wednesday'),
22 insert into t1 values (1,'one');
23 insert into t1 values (2,'two');
24 insert into t1 values (3,'three');
25 insert into t1 values (4,'four');
29 set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
30 c10= 1, c11= 1, c12 = 1,
31 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
32 c16= '11:11:11', c17= '2004',
33 c18= 1, c19=true, c20= 'a', c21= '123456789a',
34 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
35 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
36 c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
38 set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
39 c10= 9, c11= 9, c12 = 9,
40 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
41 c16= '11:11:11', c17= '2004',
42 c18= 1, c19=false, c20= 'a', c21= '123456789a',
43 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
44 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
45 c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
48 ------ simple select tests ------
49 prepare stmt1 from ' select * from t9 order by c1 ' ;
51 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
52 def test t9 t9 c1 c1 1 4 1 N 49155 0 63
53 def test t9 t9 c2 c2 2 6 1 Y 32768 0 63
54 def test t9 t9 c3 c3 9 9 1 Y 32768 0 63
55 def test t9 t9 c4 c4 3 11 1 Y 32768 0 63
56 def test t9 t9 c5 c5 3 11 1 Y 32768 0 63
57 def test t9 t9 c6 c6 8 20 1 Y 32768 0 63
58 def test t9 t9 c7 c7 4 12 1 Y 32768 31 63
59 def test t9 t9 c8 c8 5 22 1 Y 32768 31 63
60 def test t9 t9 c9 c9 5 22 1 Y 32768 31 63
61 def test t9 t9 c10 c10 5 22 1 Y 32768 31 63
62 def test t9 t9 c11 c11 246 9 6 Y 0 4 63
63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63
64 def test t9 t9 c13 c13 10 10 10 Y 128 0 63
65 def test t9 t9 c14 c14 12 19 19 Y 128 0 63
66 def test t9 t9 c15 c15 7 19 19 N 9441 0 63
67 def test t9 t9 c16 c16 11 8 8 Y 128 0 63
68 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63
69 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63
70 def test t9 t9 c19 c19 1 1 1 Y 32768 0 63
71 def test t9 t9 c20 c20 254 1 1 Y 0 0 8
72 def test t9 t9 c21 c21 254 10 10 Y 0 0 8
73 def test t9 t9 c22 c22 253 30 30 Y 0 0 8
74 def test t9 t9 c23 c23 252 255 8 Y 144 0 63
75 def test t9 t9 c24 c24 252 255 8 Y 16 0 8
76 def test t9 t9 c25 c25 252 65535 4 Y 144 0 63
77 def test t9 t9 c26 c26 252 65535 4 Y 16 0 8
78 def test t9 t9 c27 c27 252 16777215 10 Y 144 0 63
79 def test t9 t9 c28 c28 252 16777215 10 Y 16 0 8
80 def test t9 t9 c29 c29 252 4294967295 8 Y 144 0 63
81 def test t9 t9 c30 c30 252 4294967295 8 Y 16 0 8
82 def test t9 t9 c31 c31 254 5 3 Y 256 0 8
83 def test t9 t9 c32 c32 254 24 7 Y 2048 0 8
84 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
85 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
86 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
88 @arg00 a from t1 where a=1;
89 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
90 prepare stmt1 from ' ? a from t1 where a=1 ';
91 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
93 select @arg00, b from t1 where a=1 ;
96 prepare stmt1 from ' select ?, b from t1 where a=1 ' ;
97 execute stmt1 using @arg00 ;
101 select @arg00, b from t1 where a=1 ;
104 prepare stmt1 from ' select ?, b from t1 where a=1 ' ;
105 execute stmt1 using @arg00 ;
109 select @arg00, b from t1 where a=1 ;
112 prepare stmt1 from ' select ?, b from t1 where a=1 ' ;
113 execute stmt1 using @arg00 ;
117 select b, a - @arg00 from t1 where a=1 ;
120 prepare stmt1 from ' select b, a - ? from t1 where a=1 ' ;
121 execute stmt1 using @arg00 ;
125 select @arg00 as my_col ;
128 prepare stmt1 from ' select ? as my_col';
129 execute stmt1 using @arg00 ;
132 select @arg00 + 1 as my_col ;
135 prepare stmt1 from ' select ? + 1 as my_col';
136 execute stmt1 using @arg00 ;
139 select 1 + @arg00 as my_col ;
142 prepare stmt1 from ' select 1 + ? as my_col';
143 execute stmt1 using @arg00 ;
147 select substr(@arg00,1,2) from t1 where a=1 ;
150 prepare stmt1 from ' select substr(?,1,2) from t1 where a=1 ' ;
151 execute stmt1 using @arg00 ;
155 select substr('MySQL',@arg00,5) from t1 where a=1 ;
156 substr('MySQL',@arg00,5)
158 prepare stmt1 from ' select substr(''MySQL'',?,5) from t1 where a=1 ' ;
159 execute stmt1 using @arg00 ;
162 select substr('MySQL',1,@arg00) from t1 where a=1 ;
163 substr('MySQL',1,@arg00)
165 prepare stmt1 from ' select substr(''MySQL'',1,?) from t1 where a=1 ' ;
166 execute stmt1 using @arg00 ;
170 select a , concat(@arg00,b) from t1 order by a;
176 prepare stmt1 from ' select a , concat(?,b) from t1 order by a ' ;
177 execute stmt1 using @arg00;
183 select a , concat(b,@arg00) from t1 order by a ;
189 prepare stmt1 from ' select a , concat(b,?) from t1 order by a ' ;
190 execute stmt1 using @arg00;
197 select group_concat(@arg00,b order by a) from t1
199 group_concat(@arg00,b order by a)
200 MySQLone,MySQLtwo,MySQLthree,MySQLfour
201 prepare stmt1 from ' select group_concat(?,b order by a) from t1
203 execute stmt1 using @arg00;
204 group_concat(?,b order by a)
205 MySQLone,MySQLtwo,MySQLthree,MySQLfour
206 select group_concat(b,@arg00 order by a) from t1
208 group_concat(b,@arg00 order by a)
209 oneMySQL,twoMySQL,threeMySQL,fourMySQL
210 prepare stmt1 from ' select group_concat(b,? order by a) from t1
212 execute stmt1 using @arg00;
213 group_concat(b,? order by a)
214 oneMySQL,twoMySQL,threeMySQL,fourMySQL
216 set @arg01='second' ;
218 select @arg00, @arg01 from t1 where a=1 ;
221 prepare stmt1 from ' select ?, ? from t1 where a=1 ' ;
222 execute stmt1 using @arg00, @arg01 ;
225 execute stmt1 using @arg02, @arg01 ;
228 execute stmt1 using @arg00, @arg02 ;
231 execute stmt1 using @arg02, @arg02 ;
234 drop table if exists t5 ;
235 create table t5 (id1 int(11) not null default '0',
236 value2 varchar(100), value1 varchar(100)) ;
237 insert into t5 values (1,'hh','hh'),(2,'hh','hh'),
238 (1,'ii','ii'),(2,'ii','ii') ;
239 prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? order by id1,value1 ' ;
242 execute stmt1 using @arg00, @arg01 ;
248 drop table if exists t5 ;
249 create table t5(session_id char(9) not null) ;
250 insert into t5 values ('abc') ;
251 prepare stmt1 from ' select * from t5
252 where ?=''1111'' and session_id = ''abc'' ' ;
254 execute stmt1 using @arg00 ;
257 execute stmt1 using @arg00 ;
261 execute stmt1 using @arg00 ;
265 select a @arg00 t1 where a=1 ;
266 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
267 prepare stmt1 from ' select a ? t1 where a=1 ' ;
268 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
270 select a from @arg00 where a=1 ;
271 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
272 prepare stmt1 from ' select a from ? where a=1 ' ;
273 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
275 select a from t1 @arg00 a=1 ;
276 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
277 prepare stmt1 from ' select a from t1 ? a=1 ' ;
278 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
280 select a FROM t1 where a=@arg00 ;
283 prepare stmt1 from ' select a FROM t1 where a=? ' ;
284 execute stmt1 using @arg00 ;
288 execute stmt1 using @arg00 ;
291 select a FROM t1 where a=@arg00 ;
293 prepare stmt1 from ' select a FROM t1 where a=? ' ;
294 execute stmt1 using @arg00 ;
297 select a FROM t1 where a=sqrt(@arg00) ;
300 prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ;
301 execute stmt1 using @arg00 ;
305 select a FROM t1 where a=sqrt(@arg00) ;
307 prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ;
308 execute stmt1 using @arg00 ;
312 select a FROM t1 where a in (@arg00,@arg01) order by a;
316 prepare stmt1 from ' select a FROM t1 where a in (?,?) order by a ';
317 execute stmt1 using @arg00, @arg01;
324 prepare stmt1 from ' select b FROM t1 where b in (?,?,?) order by b ' ;
325 execute stmt1 using @arg00, @arg01, @arg02 ;
329 prepare stmt1 from ' select b FROM t1 where b like ? ';
331 execute stmt1 using @arg00 ;
335 execute stmt1 using @arg00 ;
339 execute stmt1 using @arg00 ;
343 insert into t9 set c1= 0, c5 = NULL ;
344 select c5 from t9 where c5 > NULL ;
346 prepare stmt1 from ' select c5 from t9 where c5 > ? ';
347 execute stmt1 using @arg00 ;
349 select c5 from t9 where c5 < NULL ;
351 prepare stmt1 from ' select c5 from t9 where c5 < ? ';
352 execute stmt1 using @arg00 ;
354 select c5 from t9 where c5 = NULL ;
356 prepare stmt1 from ' select c5 from t9 where c5 = ? ';
357 execute stmt1 using @arg00 ;
359 select c5 from t9 where c5 <=> NULL ;
362 prepare stmt1 from ' select c5 from t9 where c5 <=> ? ';
363 execute stmt1 using @arg00 ;
366 delete from t9 where c1= 0 ;
368 select a FROM t1 where a @arg00 1 ;
369 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
370 prepare stmt1 from ' select a FROM t1 where a ? 1 ' ;
371 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
373 select a,b FROM t1 where a is not NULL
374 AND b is not NULL group by a - @arg00 ;
380 prepare stmt1 from ' select a,b FROM t1 where a is not NULL
381 AND b is not NULL group by a - ? ' ;
382 execute stmt1 using @arg00 ;
389 select a,b FROM t1 where a is not NULL
390 AND b is not NULL having b <> @arg00 order by a ;
395 prepare stmt1 from ' select a,b FROM t1 where a is not NULL
396 AND b is not NULL having b <> ? order by a ' ;
397 execute stmt1 using @arg00 ;
403 select a,b FROM t1 where a is not NULL
404 AND b is not NULL order by a - @arg00 ;
410 prepare stmt1 from ' select a,b FROM t1 where a is not NULL
411 AND b is not NULL order by a - ? ' ;
412 execute stmt1 using @arg00 ;
419 select a,b from t1 order by 2 ;
425 prepare stmt1 from ' select a,b from t1
427 execute stmt1 using @arg00;
434 execute stmt1 using @arg00;
441 execute stmt1 using @arg00;
442 ERROR 42S22: Unknown column '?' in 'order clause'
444 prepare stmt1 from ' select a,b from t1 order by a
449 prepare stmt1 from ' select a,b from t1 order by a limit ? ';
450 execute stmt1 using @arg00;
457 select sum(a), @arg00 from t1 where a > @arg01
458 and b is not null group by substr(b,@arg02)
459 having sum(a) <> @arg03 ;
464 prepare stmt1 from ' select sum(a), ? from t1 where a > ?
465 and b is not null group by substr(b,?)
466 having sum(a) <> ? ';
467 execute stmt1 using @arg00, @arg01, @arg02, @arg03;
473 ------ join tests ------
474 select first.a as a1, second.a as a2
475 from t1 first, t1 second
476 where first.a = second.a order by a1 ;
482 prepare stmt1 from ' select first.a as a1, second.a as a2
483 from t1 first, t1 second
484 where first.a = second.a order by a1 ';
494 select first.a, @arg00, second.a FROM t1 first, t1 second
495 where @arg01 = first.b or first.a = second.a or second.b = @arg02
496 order by second.a, first.a;
507 prepare stmt1 from ' select first.a, ?, second.a FROM t1 first, t1 second
508 where ? = first.b or first.a = second.a or second.b = ?
509 order by second.a, first.a';
510 execute stmt1 using @arg00, @arg01, @arg02;
521 drop table if exists t2 ;
522 create table t2 as select * from t1 ;
523 set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
524 set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
525 set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
526 set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
527 set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
528 set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
529 set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
530 set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
531 set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
532 the join statement is:
533 SELECT * FROM t2 right join t1 using(a) order by t2.a
534 prepare stmt1 from @query9 ;
553 the join statement is:
554 SELECT * FROM t2 natural right join t1 order by t2.a
555 prepare stmt1 from @query8 ;
574 the join statement is:
575 SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a
576 prepare stmt1 from @query7 ;
595 the join statement is:
596 SELECT * FROM t2 left join t1 using(a) order by t2.a
597 prepare stmt1 from @query6 ;
616 the join statement is:
617 SELECT * FROM t2 natural left join t1 order by t2.a
618 prepare stmt1 from @query5 ;
637 the join statement is:
638 SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a
639 prepare stmt1 from @query4 ;
658 the join statement is:
659 SELECT * FROM t2 join t1 using(a) order by t2.a
660 prepare stmt1 from @query3 ;
679 the join statement is:
680 SELECT * FROM t2 natural join t1 order by t2.a
681 prepare stmt1 from @query2 ;
700 the join statement is:
701 SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a
702 prepare stmt1 from @query1 ;
723 ------ subquery tests ------
724 prepare stmt1 from ' select a, b FROM t1 outer_table where
725 a = (select a from t1 where b = ''two'') ';
730 select a, b FROM t1 outer_table where
731 a = (select a from t1 where b = 'two' ) and b=@arg00 ;
734 prepare stmt1 from ' select a, b FROM t1 outer_table where
735 a = (select a from t1 where b = ''two'') and b=? ';
736 execute stmt1 using @arg00;
740 select a, b FROM t1 outer_table where
741 a = (select a from t1 where b = @arg00 ) and b='two' ;
744 prepare stmt1 from ' select a, b FROM t1 outer_table where
745 a = (select a from t1 where b = ? ) and b=''two'' ' ;
746 execute stmt1 using @arg00;
751 select a,b FROM t1 where (a,b) in (select 3, 'three');
754 select a FROM t1 where (a,b) in (select @arg00,@arg01);
757 prepare stmt1 from ' select a FROM t1 where (a,b) in (select ?, ?) ';
758 execute stmt1 using @arg00, @arg01;
765 select a, @arg00, b FROM t1 outer_table where
766 b=@arg01 and a = (select @arg02 from t1 where b = @arg03 ) ;
769 prepare stmt1 from ' select a, ?, b FROM t1 outer_table where
770 b=? and a = (select ? from t1 where b = ? ) ' ;
771 execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
774 prepare stmt1 from 'select c4 FROM t9 where
775 c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ;
776 execute stmt1 using @arg01, @arg02;
778 prepare stmt1 from ' select a, b FROM t1 outer_table where
779 a = (select a from t1 where b = outer_table.b ) order by a ';
786 prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
787 (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
791 deallocate prepare stmt1 ;
792 prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
793 (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
797 deallocate prepare stmt1 ;
798 prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
799 (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
803 deallocate prepare stmt1 ;
805 select a, b FROM t1 outer_table where
806 a = (select a from t1 where b = outer_table.b ) and b=@arg00 ;
809 prepare stmt1 from ' select a, b FROM t1 outer_table where
810 a = (select a from t1 where b = outer_table.b) and b=? ';
811 execute stmt1 using @arg00;
815 select a, b FROM t1 outer_table where
816 a = (select a from t1 where a = @arg00 and b = outer_table.b) and b='two' ;
819 prepare stmt1 from ' select a, b FROM t1 outer_table where
820 a = (select a from t1 where a = ? and b = outer_table.b) and b=''two'' ' ;
821 execute stmt1 using @arg00;
825 select a, b FROM t1 outer_table where
826 a = (select a from t1 where outer_table.a = @arg00 and a=2) and b='two' ;
829 prepare stmt1 from ' select a, b FROM t1 outer_table where
830 a = (select a from t1 where outer_table.a = ? and a=2) and b=''two'' ' ;
831 execute stmt1 using @arg00;
838 select a, @arg00, b FROM t1 outer_table where
839 b=@arg01 and a = (select @arg02 from t1 where outer_table.b = @arg03
840 and outer_table.a=a ) ;
843 prepare stmt1 from ' select a, ?, b FROM t1 outer_table where
844 b=? and a = (select ? from t1 where outer_table.b = ?
845 and outer_table.a=a ) ' ;
846 execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
852 from ( select a - @arg00 as a from t1 where a=@arg00 ) as t2
856 prepare stmt1 from ' select a, ?
857 from ( select a - ? as a from t1 where a=? ) as t2
859 execute stmt1 using @arg00, @arg00, @arg00, @arg01 ;
862 drop table if exists t2 ;
863 create table t2 as select * from t1;
864 prepare stmt1 from ' select a in (select a from t2) from t1 ' ;
866 a in (select a from t2)
871 drop table if exists t5, t6, t7 ;
872 create table t5 (a int , b int) ;
873 create table t6 like t5 ;
874 create table t7 like t5 ;
875 insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7),
877 insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1) ;
878 insert into t7 values (3, 3), (2, 2), (1, 1) ;
879 prepare stmt1 from ' select a, (select count(distinct t5.b) as sum from t5, t6
880 where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
881 group by t5.a order by sum limit 1) from t7 ' ;
883 a (select count(distinct t5.b) as sum from t5, t6
884 where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
885 group by t5.a order by sum limit 1)
890 a (select count(distinct t5.b) as sum from t5, t6
891 where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
892 group by t5.a order by sum limit 1)
897 a (select count(distinct t5.b) as sum from t5, t6
898 where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
899 group by t5.a order by sum limit 1)
903 drop table t5, t6, t7 ;
904 drop table if exists t2 ;
905 create table t2 as select * from t9;
907 (SELECT SUM(c1 + c12 + 0.0) FROM t2
908 where (t9.c2 - 0e-3) = t2.c2
909 GROUP BY t9.c15 LIMIT 1) as scalar_s,
910 exists (select 1.0e+0 from t2
911 where t2.c3 * 9.0000000000 = t9.c4) as exists_s,
912 c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s,
913 (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s
915 (select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
916 prepare stmt1 from @stmt ;
919 set @stmt= concat('explain ',@stmt);
920 prepare stmt1 from @stmt ;
924 (SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2
925 GROUP BY t9.c15 LIMIT 1) as scalar_s,
926 exists (select ? from t2
927 where t2.c3*?=t9.c4) as exists_s,
928 c5*? in (select c6+? from t2) as in_s,
929 (c7-?, c8-?) in (select c9+?, c10+? from t2) as in_row_s
931 (select c25 x, c32 y from t2) tt WHERE x =c25 ' ;
935 set @arg03= 9.0000000000 ;
942 prepare stmt1 from @stmt ;
943 execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
944 @arg07, @arg08, @arg09 ;
945 execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
946 @arg07, @arg08, @arg09 ;
947 set @stmt= concat('explain ',@stmt);
948 prepare stmt1 from @stmt ;
949 execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
950 @arg07, @arg08, @arg09 ;
951 execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
952 @arg07, @arg08, @arg09 ;
954 select 1 < (select a from t1) ;
955 ERROR 21000: Subquery returns more than 1 row
956 prepare stmt1 from ' select 1 < (select a from t1) ' ;
958 ERROR 21000: Subquery returns more than 1 row
963 ------ union tests ------
964 prepare stmt1 from ' select a FROM t1 where a=1
966 select a FROM t1 where a=1 ';
973 prepare stmt1 from ' select a FROM t1 where a=1
975 select a FROM t1 where a=1 ';
980 prepare stmt1 from ' SELECT 1, 2 union SELECT 1 ' ;
981 ERROR 21000: The used SELECT statements have a different number of columns
982 prepare stmt1 from ' SELECT 1 union SELECT 1, 2 ' ;
983 ERROR 21000: The used SELECT statements have a different number of columns
984 prepare stmt1 from ' SELECT * from t1 union SELECT 1 ' ;
985 ERROR 21000: The used SELECT statements have a different number of columns
986 prepare stmt1 from ' SELECT 1 union SELECT * from t1 ' ;
987 ERROR 21000: The used SELECT statements have a different number of columns
989 select @arg00 FROM t1 where a=1
991 select 1 FROM t1 where a=1;
994 prepare stmt1 from ' select ? FROM t1 where a=1
996 select 1 FROM t1 where a=1 ' ;
997 execute stmt1 using @arg00;
1001 select 1 FROM t1 where a=1
1003 select @arg00 FROM t1 where a=1;
1006 prepare stmt1 from ' select 1 FROM t1 where a=1
1008 select ? FROM t1 where a=1 ' ;
1009 execute stmt1 using @arg00;
1013 select @arg00 FROM t1 where a=1
1015 select @arg00 FROM t1 where a=1;
1018 prepare stmt1 from ' select ? FROM t1 where a=1
1020 select ? FROM t1 where a=1 ';
1021 execute stmt1 using @arg00, @arg00;
1024 prepare stmt1 from ' select ?
1027 execute stmt1 using @arg00, @arg00;
1034 select @arg00 FROM t1 where a=@arg01
1036 select @arg02 FROM t1 where a=@arg03;
1039 prepare stmt1 from ' select ? FROM t1 where a=?
1041 select ? FROM t1 where a=? ' ;
1042 execute stmt1 using @arg00, @arg01, @arg02, @arg03;
1046 prepare stmt1 from ' select sum(a) + 200, ? from t1
1048 select sum(a) + 200, 1 from t1
1050 execute stmt1 using @arg00;
1057 set @Oporto='Oporto' ;
1058 set @Lisboa='Lisboa' ;
1064 select @Oporto,@Lisboa,@0,@1,@2,@3,@4 ;
1065 @Oporto @Lisboa @0 @1 @2 @3 @4
1066 Oporto Lisboa 0 1 2 3 4
1067 select sum(a) + 200 as the_sum, @Oporto as the_town from t1
1070 select sum(a) + 200, @Lisboa from t1
1081 prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
1084 select sum(a) + 200, ? from t1
1086 execute stmt1 using @Oporto, @Lisboa;
1096 select sum(a) + 200 as the_sum, @Oporto as the_town from t1
1100 select sum(a) + 200, @Lisboa from t1
1109 prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
1113 select sum(a) + 200, ? from t1
1116 execute stmt1 using @Oporto, @1, @Lisboa, @2;
1123 select sum(a) + 200 as the_sum, @Oporto as the_town from t1
1128 select sum(a) + 200, @Lisboa from t1
1136 prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
1141 select sum(a) + 200, ? from t1
1144 having avg(a) > ? ';
1145 execute stmt1 using @Oporto, @1, @2, @Lisboa, @2, @3;
1151 ------ explain select tests ------
1152 prepare stmt1 from ' explain select * from t9 ' ;
1154 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1155 def id 8 3 1 N 32929 0 63
1156 def select_type 253 19 6 N 1 31 8
1157 def table 253 64 2 Y 0 31 8
1158 def type 253 10 3 Y 0 31 8
1159 def possible_keys 253 4096 0 Y 0 31 8
1160 def key 253 64 0 Y 0 31 8
1161 def key_len 253 4096 0 Y 0 31 8
1162 def ref 253 1024 0 Y 0 31 8
1163 def rows 8 10 1 Y 32928 0 63
1164 def Extra 253 255 0 N 1 31 8
1165 id select_type table type possible_keys key key_len ref rows Extra
1166 1 SIMPLE t9 ALL NULL NULL NULL NULL 2
1167 drop table if exists t2 ;
1168 create table t2 (s varchar(25), fulltext(s))
1170 insert into t2 values ('Gravedigger'), ('Greed'),('Hollow Dogs') ;
1172 prepare stmt1 from ' select s from t2 where match (s) against (?) ' ;
1174 execute stmt1 using @arg00 ;
1177 prepare stmt1 from ' SELECT s FROM t2
1178 where match (s) against (concat(?,''digger'')) ';
1179 set @arg00='Grave' ;
1180 execute stmt1 using @arg00 ;
1185 ------ delete tests ------
1187 insert into t1 values (1,'one');
1188 insert into t1 values (2,'two');
1189 insert into t1 values (3,'three');
1190 insert into t1 values (4,'four');
1194 set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1195 c10= 1, c11= 1, c12 = 1,
1196 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1197 c16= '11:11:11', c17= '2004',
1198 c18= 1, c19=true, c20= 'a', c21= '123456789a',
1199 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1200 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1201 c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1203 set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1204 c10= 9, c11= 9, c12 = 9,
1205 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1206 c16= '11:11:11', c17= '2004',
1207 c18= 1, c19=false, c20= 'a', c21= '123456789a',
1208 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1209 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1210 c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1212 prepare stmt1 from 'delete from t1 where a=2' ;
1214 select a,b from t1 where a=2;
1217 insert into t1 values(0,NULL);
1219 prepare stmt1 from 'delete from t1 where b=?' ;
1220 execute stmt1 using @arg00;
1221 select a,b from t1 where b is NULL ;
1225 execute stmt1 using @arg00;
1226 select a,b from t1 where b=@arg00;
1228 prepare stmt1 from 'truncate table t1' ;
1230 ------ update tests ------
1232 insert into t1 values (1,'one');
1233 insert into t1 values (2,'two');
1234 insert into t1 values (3,'three');
1235 insert into t1 values (4,'four');
1239 set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1240 c10= 1, c11= 1, c12 = 1,
1241 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1242 c16= '11:11:11', c17= '2004',
1243 c18= 1, c19=true, c20= 'a', c21= '123456789a',
1244 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1245 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1246 c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1248 set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1249 c10= 9, c11= 9, c12 = 9,
1250 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1251 c16= '11:11:11', c17= '2004',
1252 c18= 1, c19=false, c20= 'a', c21= '123456789a',
1253 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1254 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1255 c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1257 prepare stmt1 from 'update t1 set b=''a=two'' where a=2' ;
1259 select a,b from t1 where a=2;
1263 select a,b from t1 where a=2;
1267 prepare stmt1 from 'update t1 set b=? where a=2' ;
1268 execute stmt1 using @arg00;
1269 select a,b from t1 where a=2;
1273 execute stmt1 using @arg00;
1274 select a,b from t1 where a=2;
1278 prepare stmt1 from 'update t1 set b=NULL where a=?' ;
1279 execute stmt1 using @arg00;
1280 select a,b from t1 where a=@arg00;
1283 update t1 set b='two' where a=@arg00;
1285 execute stmt1 using @arg00;
1286 select a,b from t1 where a=@arg00;
1290 prepare stmt1 from 'update t1 set a=? where a=?' ;
1291 execute stmt1 using @arg00, @arg00;
1292 select a,b from t1 where a=@arg00;
1295 execute stmt1 using @arg01, @arg00;
1296 select a,b from t1 where a=@arg01;
1299 execute stmt1 using @arg00, @arg01;
1300 select a,b from t1 where a=@arg00;
1305 execute stmt1 using @arg00, @arg01;
1307 Warning 1048 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 = 'MYISAM' ;
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 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 250 16777215 10 Y 128 31 63
1933 def @arg14 250 16777215 19 Y 128 31 63
1934 def @arg15 250 16777215 19 Y 128 31 63
1935 def @arg16 250 16777215 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 250 16777215 1 Y 0 31 8
1940 def @arg21 250 16777215 10 Y 0 31 8
1941 def @arg22 250 16777215 30 Y 0 31 8
1942 def @arg23 250 16777215 8 Y 128 31 63
1943 def @arg24 250 16777215 8 Y 0 31 8
1944 def @arg25 250 16777215 4 Y 128 31 63
1945 def @arg26 250 16777215 4 Y 0 31 8
1946 def @arg27 250 16777215 10 Y 128 31 63
1947 def @arg28 250 16777215 10 Y 0 31 8
1948 def @arg29 250 16777215 8 Y 128 31 63
1949 def @arg30 250 16777215 8 Y 0 31 8
1950 def @arg31 250 16777215 3 Y 0 31 8
1951 def @arg32 250 16777215 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 250 16777215 0 Y 128 31 63
1980 def @arg14 250 16777215 0 Y 128 31 63
1981 def @arg15 250 16777215 19 Y 128 31 63
1982 def @arg16 250 16777215 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 250 16777215 0 Y 0 31 8
1987 def @arg21 250 16777215 0 Y 0 31 8
1988 def @arg22 250 16777215 0 Y 0 31 8
1989 def @arg23 250 16777215 0 Y 128 31 63
1990 def @arg24 250 16777215 0 Y 0 31 8
1991 def @arg25 250 16777215 0 Y 128 31 63
1992 def @arg26 250 16777215 0 Y 0 31 8
1993 def @arg27 250 16777215 0 Y 128 31 63
1994 def @arg28 250 16777215 0 Y 0 31 8
1995 def @arg29 250 16777215 0 Y 128 31 63
1996 def @arg30 250 16777215 0 Y 0 31 8
1997 def @arg31 250 16777215 0 Y 0 31 8
1998 def @arg32 250 16777215 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 250 16777215 10 Y 128 31 63
2030 def @arg14 250 16777215 19 Y 128 31 63
2031 def @arg15 250 16777215 19 Y 128 31 63
2032 def @arg16 250 16777215 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 250 16777215 1 Y 0 31 8
2037 def @arg21 250 16777215 10 Y 0 31 8
2038 def @arg22 250 16777215 30 Y 0 31 8
2039 def @arg23 250 16777215 8 Y 128 31 63
2040 def @arg24 250 16777215 8 Y 0 31 8
2041 def @arg25 250 16777215 4 Y 128 31 63
2042 def @arg26 250 16777215 4 Y 0 31 8
2043 def @arg27 250 16777215 10 Y 128 31 63
2044 def @arg28 250 16777215 10 Y 0 31 8
2045 def @arg29 250 16777215 8 Y 128 31 63
2046 def @arg30 250 16777215 8 Y 0 31 8
2047 def @arg31 250 16777215 3 Y 0 31 8
2048 def @arg32 250 16777215 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 250 16777215 0 Y 128 31 63
2070 def @arg14 250 16777215 0 Y 128 31 63
2071 def @arg15 250 16777215 19 Y 128 31 63
2072 def @arg16 250 16777215 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 250 16777215 0 Y 0 31 8
2077 def @arg21 250 16777215 0 Y 0 31 8
2078 def @arg22 250 16777215 0 Y 0 31 8
2079 def @arg23 250 16777215 0 Y 128 31 63
2080 def @arg24 250 16777215 0 Y 0 31 8
2081 def @arg25 250 16777215 0 Y 128 31 63
2082 def @arg26 250 16777215 0 Y 0 31 8
2083 def @arg27 250 16777215 0 Y 128 31 63
2084 def @arg28 250 16777215 0 Y 0 31 8
2085 def @arg29 250 16777215 0 Y 128 31 63
2086 def @arg30 250 16777215 0 Y 0 31 8
2087 def @arg31 250 16777215 0 Y 0 31 8
2088 def @arg32 250 16777215 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 250 16777215 10 Y 128 31 63
2118 def @arg14 250 16777215 19 Y 128 31 63
2119 def @arg15 250 16777215 19 Y 128 31 63
2120 def @arg16 250 16777215 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 250 16777215 1 Y 0 31 8
2125 def @arg21 250 16777215 10 Y 0 31 8
2126 def @arg22 250 16777215 30 Y 0 31 8
2127 def @arg23 250 16777215 8 Y 128 31 63
2128 def @arg24 250 16777215 8 Y 0 31 8
2129 def @arg25 250 16777215 4 Y 128 31 63
2130 def @arg26 250 16777215 4 Y 0 31 8
2131 def @arg27 250 16777215 10 Y 128 31 63
2132 def @arg28 250 16777215 10 Y 0 31 8
2133 def @arg29 250 16777215 8 Y 128 31 63
2134 def @arg30 250 16777215 8 Y 0 31 8
2135 def @arg31 250 16777215 3 Y 0 31 8
2136 def @arg32 250 16777215 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 250 16777215 0 Y 128 31 63
2162 def @arg14 250 16777215 0 Y 128 31 63
2163 def @arg15 250 16777215 19 Y 128 31 63
2164 def @arg16 250 16777215 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 250 16777215 0 Y 0 31 8
2169 def @arg21 250 16777215 0 Y 0 31 8
2170 def @arg22 250 16777215 0 Y 0 31 8
2171 def @arg23 250 16777215 0 Y 128 31 63
2172 def @arg24 250 16777215 0 Y 0 31 8
2173 def @arg25 250 16777215 0 Y 128 31 63
2174 def @arg26 250 16777215 0 Y 0 31 8
2175 def @arg27 250 16777215 0 Y 128 31 63
2176 def @arg28 250 16777215 0 Y 0 31 8
2177 def @arg29 250 16777215 0 Y 128 31 63
2178 def @arg30 250 16777215 0 Y 0 31 8
2179 def @arg31 250 16777215 0 Y 0 31 8
2180 def @arg32 250 16777215 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 250 16777215 10 Y 128 31 63
2208 def @arg14 250 16777215 19 Y 128 31 63
2209 def @arg15 250 16777215 19 Y 128 31 63
2210 def @arg16 250 16777215 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 250 16777215 1 Y 0 31 8
2215 def @arg21 250 16777215 10 Y 0 31 8
2216 def @arg22 250 16777215 30 Y 0 31 8
2217 def @arg23 250 16777215 8 Y 128 31 63
2218 def @arg24 250 16777215 8 Y 0 31 8
2219 def @arg25 250 16777215 4 Y 128 31 63
2220 def @arg26 250 16777215 4 Y 0 31 8
2221 def @arg27 250 16777215 10 Y 128 31 63
2222 def @arg28 250 16777215 10 Y 0 31 8
2223 def @arg29 250 16777215 8 Y 128 31 63
2224 def @arg30 250 16777215 8 Y 0 31 8
2225 def @arg31 250 16777215 3 Y 0 31 8
2226 def @arg32 250 16777215 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 250 16777215 0 Y 128 31 63
2246 def @arg14 250 16777215 0 Y 128 31 63
2247 def @arg15 250 16777215 19 Y 128 31 63
2248 def @arg16 250 16777215 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 250 16777215 0 Y 0 31 8
2253 def @arg21 250 16777215 0 Y 0 31 8
2254 def @arg22 250 16777215 0 Y 0 31 8
2255 def @arg23 250 16777215 0 Y 128 31 63
2256 def @arg24 250 16777215 0 Y 0 31 8
2257 def @arg25 250 16777215 0 Y 128 31 63
2258 def @arg26 250 16777215 0 Y 0 31 8
2259 def @arg27 250 16777215 0 Y 128 31 63
2260 def @arg28 250 16777215 0 Y 0 31 8
2261 def @arg29 250 16777215 0 Y 128 31 63
2262 def @arg30 250 16777215 0 Y 0 31 8
2263 def @arg31 250 16777215 0 Y 0 31 8
2264 def @arg32 250 16777215 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.22337203685478e+18
2590 c9 9.22337203685478e+18
2591 c10 9.22337203685478e+18
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.22337203685478e+18
2613 c9 9.22337203685478e+18
2614 c10 9.22337203685478e+18
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.22337203685478e+18
2636 c9 -9.22337203685478e+18
2637 c10 -9.22337203685478e+18
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.22337203685478e+18
2659 c9 -9.22337203685478e+18
2660 c10 -9.22337203685478e+18
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.11111111111111e+50
2684 c9 1.11111111111111e+50
2685 c10 1.11111111111111e+50
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.11111111111111e+50
2709 c9 1.11111111111111e+50
2710 c10 1.11111111111111e+50
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.11111111111111e+50
2734 c9 -1.11111111111111e+50
2735 c10 -1.11111111111111e+50
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.11111111111111e+50
2759 c9 -1.11111111111111e+50
2760 c10 -1.11111111111111e+50
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.00 54.00 54.00 54.00 54.00 54.00 54.00 54.00
2826 55 5 55 55 55 55 55 55 55 55 55 55
2827 56 6 56 56 56.00 56.00 56.00 56.00 56.00 56.00 56.00 56.00
2828 57 6 57 57 57.00 57.00 57.00 57.00 57.00 57.00 57.00 57.00
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 2010-00-00 2010-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 ;