Debian Codegraph @

mysql-test/suite/maria/ps_maria.result

raw
1 use test;
2 drop table if exists t1, t9 ;
3 create table t1
4 (
5 a int, b varchar(30),
6 primary key(a)
7 ) engine = 'ARIA' ;
8 create table t9
9 (
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 NOT NULL DEFAULT CURRENT_TIMESTAMP
14 ON UPDATE CURRENT_TIMESTAMP, c16 time,
15 c17 year, c18 tinyint, c19 bool, c20 char,
16 c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
17 c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
18 c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'),
19 c32 set('monday', 'tuesday', 'wednesday'),
20 primary key(c1)
21 ) engine = 'ARIA' ;
22 delete from t1 ;
23 insert into t1 values (1,'one');
24 insert into t1 values (2,'two');
25 insert into t1 values (3,'three');
26 insert into t1 values (4,'four');
27 commit ;
28 delete from t9 ;
29 insert into t9
30 set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
31 c10= 1, c11= 1, c12 = 1,
32 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
33 c16= '11:11:11', c17= '2004',
34 c18= 1, c19=true, c20= 'a', c21= '123456789a',
35 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
36 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
37 c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
38 insert into t9
39 set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
40 c10= 9, c11= 9, c12 = 9,
41 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
42 c16= '11:11:11', c17= '2004',
43 c18= 1, c19=false, c20= 'a', c21= '123456789a',
44 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
45 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
46 c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
47 commit ;
48 test_sequence
49 ------ simple select tests ------
50 prepare stmt1 from ' select * from t9 order by c1 ' ;
51 execute stmt1;
52 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
53 def test t9 t9 c1 c1 1 4 1 N 53251 0 63
54 def test t9 t9 c2 c2 2 6 1 Y 32768 0 63
55 def test t9 t9 c3 c3 9 9 1 Y 32768 0 63
56 def test t9 t9 c4 c4 3 11 1 Y 32768 0 63
57 def test t9 t9 c5 c5 3 11 1 Y 32768 0 63
58 def test t9 t9 c6 c6 8 20 1 Y 32768 0 63
59 def test t9 t9 c7 c7 4 12 1 Y 32768 31 63
60 def test t9 t9 c8 c8 5 22 1 Y 32768 31 63
61 def test t9 t9 c9 c9 5 22 1 Y 32768 31 63
62 def test t9 t9 c10 c10 5 22 1 Y 32768 31 63
63 def test t9 t9 c11 c11 246 9 6 Y 32768 4 63
64 def test t9 t9 c12 c12 246 10 6 Y 32768 4 63
65 def test t9 t9 c13 c13 10 10 10 Y 128 0 63
66 def test t9 t9 c14 c14 12 19 19 Y 128 0 63
67 def test t9 t9 c15 c15 7 19 19 N 9377 0 63
68 def test t9 t9 c16 c16 11 10 8 Y 128 0 63
69 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63
70 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63
71 def test t9 t9 c19 c19 1 1 1 Y 32768 0 63
72 def test t9 t9 c20 c20 254 1 1 Y 0 0 8
73 def test t9 t9 c21 c21 254 10 10 Y 0 0 8
74 def test t9 t9 c22 c22 253 30 30 Y 0 0 8
75 def test t9 t9 c23 c23 252 255 8 Y 144 0 63
76 def test t9 t9 c24 c24 252 255 8 Y 16 0 8
77 def test t9 t9 c25 c25 252 65535 4 Y 144 0 63
78 def test t9 t9 c26 c26 252 65535 4 Y 16 0 8
79 def test t9 t9 c27 c27 252 16777215 10 Y 144 0 63
80 def test t9 t9 c28 c28 252 16777215 10 Y 16 0 8
81 def test t9 t9 c29 c29 252 4294967295 8 Y 144 0 63
82 def test t9 t9 c30 c30 252 4294967295 8 Y 16 0 8
83 def test t9 t9 c31 c31 254 5 3 Y 256 0 8
84 def test t9 t9 c32 c32 254 24 7 Y 2048 0 8
85 c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32
86 1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday
87 9 9 9 9 9 9 9 9 9 9 9.0000 9.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 0 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext two tuesday
88 set @arg00='SELECT' ;
89 @arg00 a from t1 where a=1;
90 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@arg00 a from t1 where a=1' at line 1
91 prepare stmt1 from ' ? a from t1 where a=1 ';
92 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? a from t1 where a=1' at line 1
93 set @arg00=1 ;
94 select @arg00, b from t1 where a=1 ;
95 @arg00 b
96 1 one
97 prepare stmt1 from ' select ?, b from t1 where a=1 ' ;
98 execute stmt1 using @arg00 ;
99 ? b
100 1 one
101 set @arg00='lion' ;
102 select @arg00, b from t1 where a=1 ;
103 @arg00 b
104 lion one
105 prepare stmt1 from ' select ?, b from t1 where a=1 ' ;
106 execute stmt1 using @arg00 ;
107 ? b
108 lion one
109 set @arg00=NULL ;
110 select @arg00, b from t1 where a=1 ;
111 @arg00 b
112 NULL one
113 prepare stmt1 from ' select ?, b from t1 where a=1 ' ;
114 execute stmt1 using @arg00 ;
115 ? b
116 NULL one
117 set @arg00=1 ;
118 select b, a - @arg00 from t1 where a=1 ;
119 b a - @arg00
120 one 0
121 prepare stmt1 from ' select b, a - ? from t1 where a=1 ' ;
122 execute stmt1 using @arg00 ;
123 b a - ?
124 one 0
125 set @arg00=null ;
126 select @arg00 as my_col ;
127 my_col
128 NULL
129 prepare stmt1 from ' select ? as my_col';
130 execute stmt1 using @arg00 ;
131 my_col
132 NULL
133 select @arg00 + 1 as my_col ;
134 my_col
135 NULL
136 prepare stmt1 from ' select ? + 1 as my_col';
137 execute stmt1 using @arg00 ;
138 my_col
139 NULL
140 select 1 + @arg00 as my_col ;
141 my_col
142 NULL
143 prepare stmt1 from ' select 1 + ? as my_col';
144 execute stmt1 using @arg00 ;
145 my_col
146 NULL
147 set @arg00='MySQL' ;
148 select substr(@arg00,1,2) from t1 where a=1 ;
149 substr(@arg00,1,2)
150 My
151 prepare stmt1 from ' select substr(?,1,2) from t1 where a=1 ' ;
152 execute stmt1 using @arg00 ;
153 substr(?,1,2)
154 My
155 set @arg00=3 ;
156 select substr('MySQL',@arg00,5) from t1 where a=1 ;
157 substr('MySQL',@arg00,5)
158 SQL
159 prepare stmt1 from ' select substr(''MySQL'',?,5) from t1 where a=1 ' ;
160 execute stmt1 using @arg00 ;
161 substr('MySQL',?,5)
162 SQL
163 select substr('MySQL',1,@arg00) from t1 where a=1 ;
164 substr('MySQL',1,@arg00)
165 MyS
166 prepare stmt1 from ' select substr(''MySQL'',1,?) from t1 where a=1 ' ;
167 execute stmt1 using @arg00 ;
168 substr('MySQL',1,?)
169 MyS
170 set @arg00='MySQL' ;
171 select a , concat(@arg00,b) from t1 order by a;
172 a concat(@arg00,b)
173 1 MySQLone
174 2 MySQLtwo
175 3 MySQLthree
176 4 MySQLfour
177 prepare stmt1 from ' select a , concat(?,b) from t1 order by a ' ;
178 execute stmt1 using @arg00;
179 a concat(?,b)
180 1 MySQLone
181 2 MySQLtwo
182 3 MySQLthree
183 4 MySQLfour
184 select a , concat(b,@arg00) from t1 order by a ;
185 a concat(b,@arg00)
186 1 oneMySQL
187 2 twoMySQL
188 3 threeMySQL
189 4 fourMySQL
190 prepare stmt1 from ' select a , concat(b,?) from t1 order by a ' ;
191 execute stmt1 using @arg00;
192 a concat(b,?)
193 1 oneMySQL
194 2 twoMySQL
195 3 threeMySQL
196 4 fourMySQL
197 set @arg00='MySQL' ;
198 select group_concat(@arg00,b order by a) from t1
199 group by 'a' ;
200 group_concat(@arg00,b order by a)
201 MySQLone,MySQLtwo,MySQLthree,MySQLfour
202 prepare stmt1 from ' select group_concat(?,b order by a) from t1
203 group by ''a'' ' ;
204 execute stmt1 using @arg00;
205 group_concat(?,b order by a)
206 MySQLone,MySQLtwo,MySQLthree,MySQLfour
207 select group_concat(b,@arg00 order by a) from t1
208 group by 'a' ;
209 group_concat(b,@arg00 order by a)
210 oneMySQL,twoMySQL,threeMySQL,fourMySQL
211 prepare stmt1 from ' select group_concat(b,? order by a) from t1
212 group by ''a'' ' ;
213 execute stmt1 using @arg00;
214 group_concat(b,? order by a)
215 oneMySQL,twoMySQL,threeMySQL,fourMySQL
216 set @arg00='first' ;
217 set @arg01='second' ;
218 set @arg02=NULL;
219 select @arg00, @arg01 from t1 where a=1 ;
220 @arg00 @arg01
221 first second
222 prepare stmt1 from ' select ?, ? from t1 where a=1 ' ;
223 execute stmt1 using @arg00, @arg01 ;
224 ? ?
225 first second
226 execute stmt1 using @arg02, @arg01 ;
227 ? ?
228 NULL second
229 execute stmt1 using @arg00, @arg02 ;
230 ? ?
231 first NULL
232 execute stmt1 using @arg02, @arg02 ;
233 ? ?
234 NULL NULL
235 drop table if exists t5 ;
236 create table t5 (id1 int(11) not null default '0',
237 value2 varchar(100), value1 varchar(100)) ;
238 insert into t5 values (1,'hh','hh'),(2,'hh','hh'),
239 (1,'ii','ii'),(2,'ii','ii') ;
240 prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? order by id1,value1 ' ;
241 set @arg00=1 ;
242 set @arg01='hh' ;
243 execute stmt1 using @arg00, @arg01 ;
244 id1 value1
245 1 hh
246 1 ii
247 2 hh
248 drop table t5 ;
249 drop table if exists t5 ;
250 create table t5(session_id char(9) not null) ;
251 insert into t5 values ('abc') ;
252 prepare stmt1 from ' select * from t5
253 where ?=''1111'' and session_id = ''abc'' ' ;
254 set @arg00='abc' ;
255 execute stmt1 using @arg00 ;
256 session_id
257 set @arg00='1111' ;
258 execute stmt1 using @arg00 ;
259 session_id
260 abc
261 set @arg00='abc' ;
262 execute stmt1 using @arg00 ;
263 session_id
264 drop table t5 ;
265 set @arg00='FROM' ;
266 select a @arg00 t1 where a=1 ;
267 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@arg00 t1 where a=1' at line 1
268 prepare stmt1 from ' select a ? t1 where a=1 ' ;
269 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? t1 where a=1' at line 1
270 set @arg00='t1' ;
271 select a from @arg00 where a=1 ;
272 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@arg00 where a=1' at line 1
273 prepare stmt1 from ' select a from ? where a=1 ' ;
274 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? where a=1' at line 1
275 set @arg00='WHERE' ;
276 select a from t1 @arg00 a=1 ;
277 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@arg00 a=1' at line 1
278 prepare stmt1 from ' select a from t1 ? a=1 ' ;
279 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? a=1' at line 1
280 set @arg00=1 ;
281 select a FROM t1 where a=@arg00 ;
282 a
283 1
284 prepare stmt1 from ' select a FROM t1 where a=? ' ;
285 execute stmt1 using @arg00 ;
286 a
287 1
288 set @arg00=1000 ;
289 execute stmt1 using @arg00 ;
290 a
291 set @arg00=NULL ;
292 select a FROM t1 where a=@arg00 ;
293 a
294 prepare stmt1 from ' select a FROM t1 where a=? ' ;
295 execute stmt1 using @arg00 ;
296 a
297 set @arg00=4 ;
298 select a FROM t1 where a=sqrt(@arg00) ;
299 a
300 2
301 prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ;
302 execute stmt1 using @arg00 ;
303 a
304 2
305 set @arg00=NULL ;
306 select a FROM t1 where a=sqrt(@arg00) ;
307 a
308 prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ;
309 execute stmt1 using @arg00 ;
310 a
311 set @arg00=2 ;
312 set @arg01=3 ;
313 select a FROM t1 where a in (@arg00,@arg01) order by a;
314 a
315 2
316 3
317 prepare stmt1 from ' select a FROM t1 where a in (?,?) order by a ';
318 execute stmt1 using @arg00, @arg01;
319 a
320 2
321 3
322 set @arg00= 'one' ;
323 set @arg01= 'two' ;
324 set @arg02= 'five' ;
325 prepare stmt1 from ' select b FROM t1 where b in (?,?,?) order by b ' ;
326 execute stmt1 using @arg00, @arg01, @arg02 ;
327 b
328 one
329 two
330 prepare stmt1 from ' select b FROM t1 where b like ? ';
331 set @arg00='two' ;
332 execute stmt1 using @arg00 ;
333 b
334 two
335 set @arg00='tw%' ;
336 execute stmt1 using @arg00 ;
337 b
338 two
339 set @arg00='%wo' ;
340 execute stmt1 using @arg00 ;
341 b
342 two
343 set @arg00=null ;
344 insert into t9 set c1= 0, c5 = NULL ;
345 select c5 from t9 where c5 > NULL ;
346 c5
347 prepare stmt1 from ' select c5 from t9 where c5 > ? ';
348 execute stmt1 using @arg00 ;
349 c5
350 select c5 from t9 where c5 < NULL ;
351 c5
352 prepare stmt1 from ' select c5 from t9 where c5 < ? ';
353 execute stmt1 using @arg00 ;
354 c5
355 select c5 from t9 where c5 = NULL ;
356 c5
357 prepare stmt1 from ' select c5 from t9 where c5 = ? ';
358 execute stmt1 using @arg00 ;
359 c5
360 select c5 from t9 where c5 <=> NULL ;
361 c5
362 NULL
363 prepare stmt1 from ' select c5 from t9 where c5 <=> ? ';
364 execute stmt1 using @arg00 ;
365 c5
366 NULL
367 delete from t9 where c1= 0 ;
368 set @arg00='>' ;
369 select a FROM t1 where a @arg00 1 ;
370 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@arg00 1' at line 1
371 prepare stmt1 from ' select a FROM t1 where a ? 1 ' ;
372 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? 1' at line 1
373 set @arg00=1 ;
374 select a,b FROM t1 where a is not NULL
375 AND b is not NULL group by a - @arg00 ;
376 a b
377 1 one
378 2 two
379 3 three
380 4 four
381 prepare stmt1 from ' select a,b FROM t1 where a is not NULL
382 AND b is not NULL group by a - ? ' ;
383 execute stmt1 using @arg00 ;
384 a b
385 1 one
386 2 two
387 3 three
388 4 four
389 set @arg00='two' ;
390 select a,b FROM t1 where a is not NULL
391 AND b is not NULL having b <> @arg00 order by a ;
392 a b
393 1 one
394 3 three
395 4 four
396 prepare stmt1 from ' select a,b FROM t1 where a is not NULL
397 AND b is not NULL having b <> ? order by a ' ;
398 execute stmt1 using @arg00 ;
399 a b
400 1 one
401 3 three
402 4 four
403 set @arg00=1 ;
404 select a,b FROM t1 where a is not NULL
405 AND b is not NULL order by a - @arg00 ;
406 a b
407 1 one
408 2 two
409 3 three
410 4 four
411 prepare stmt1 from ' select a,b FROM t1 where a is not NULL
412 AND b is not NULL order by a - ? ' ;
413 execute stmt1 using @arg00 ;
414 a b
415 1 one
416 2 two
417 3 three
418 4 four
419 set @arg00=2 ;
420 select a,b from t1 order by 2 ;
421 a b
422 4 four
423 1 one
424 3 three
425 2 two
426 prepare stmt1 from ' select a,b from t1
427 order by ? ';
428 execute stmt1 using @arg00;
429 a b
430 4 four
431 1 one
432 3 three
433 2 two
434 set @arg00=1 ;
435 execute stmt1 using @arg00;
436 a b
437 1 one
438 2 two
439 3 three
440 4 four
441 set @arg00=0 ;
442 execute stmt1 using @arg00;
443 ERROR 42S22: Unknown column '0' in 'ORDER BY'
444 set @arg00=1;
445 prepare stmt1 from ' select a,b from t1 order by a
446 limit 1 ';
447 execute stmt1 ;
448 a b
449 1 one
450 prepare stmt1 from ' select a,b from t1 order by a limit ? ';
451 execute stmt1 using @arg00;
452 a b
453 1 one
454 set @arg00='b' ;
455 set @arg01=0 ;
456 set @arg02=2 ;
457 set @arg03=2 ;
458 select sum(a), @arg00 from t1 where a > @arg01
459 and b is not null group by substr(b,@arg02)
460 having sum(a) <> @arg03 ;
461 sum(a) @arg00
462 3 b
463 1 b
464 4 b
465 prepare stmt1 from ' select sum(a), ? from t1 where a > ?
466 and b is not null group by substr(b,?)
467 having sum(a) <> ? ';
468 execute stmt1 using @arg00, @arg01, @arg02, @arg03;
469 sum(a) ?
470 3 b
471 1 b
472 4 b
473 test_sequence
474 ------ join tests ------
475 select first.a as a1, second.a as a2
476 from t1 first, t1 second
477 where first.a = second.a order by a1 ;
478 a1 a2
479 1 1
480 2 2
481 3 3
482 4 4
483 prepare stmt1 from ' select first.a as a1, second.a as a2
484 from t1 first, t1 second
485 where first.a = second.a order by a1 ';
486 execute stmt1 ;
487 a1 a2
488 1 1
489 2 2
490 3 3
491 4 4
492 set @arg00='ABC';
493 set @arg01='two';
494 set @arg02='one';
495 select first.a, @arg00, second.a FROM t1 first, t1 second
496 where @arg01 = first.b or first.a = second.a or second.b = @arg02
497 order by second.a, first.a;
498 a @arg00 a
499 1 ABC 1
500 2 ABC 1
501 3 ABC 1
502 4 ABC 1
503 2 ABC 2
504 2 ABC 3
505 3 ABC 3
506 2 ABC 4
507 4 ABC 4
508 prepare stmt1 from ' select first.a, ?, second.a FROM t1 first, t1 second
509 where ? = first.b or first.a = second.a or second.b = ?
510 order by second.a, first.a';
511 execute stmt1 using @arg00, @arg01, @arg02;
512 a ? a
513 1 ABC 1
514 2 ABC 1
515 3 ABC 1
516 4 ABC 1
517 2 ABC 2
518 2 ABC 3
519 3 ABC 3
520 2 ABC 4
521 4 ABC 4
522 drop table if exists t2 ;
523 create table t2 as select * from t1 ;
524 set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
525 set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
526 set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
527 set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
528 set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
529 set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
530 set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
531 set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
532 set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
533 the join statement is:
534 SELECT * FROM t2 right join t1 using(a) order by t2.a
535 prepare stmt1 from @query9 ;
536 execute stmt1 ;
537 a b b
538 1 one one
539 2 two two
540 3 three three
541 4 four four
542 execute stmt1 ;
543 a b b
544 1 one one
545 2 two two
546 3 three three
547 4 four four
548 execute stmt1 ;
549 a b b
550 1 one one
551 2 two two
552 3 three three
553 4 four four
554 the join statement is:
555 SELECT * FROM t2 natural right join t1 order by t2.a
556 prepare stmt1 from @query8 ;
557 execute stmt1 ;
558 a b
559 1 one
560 2 two
561 3 three
562 4 four
563 execute stmt1 ;
564 a b
565 1 one
566 2 two
567 3 three
568 4 four
569 execute stmt1 ;
570 a b
571 1 one
572 2 two
573 3 three
574 4 four
575 the join statement is:
576 SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a
577 prepare stmt1 from @query7 ;
578 execute stmt1 ;
579 a b a b
580 1 one 1 one
581 2 two 2 two
582 3 three 3 three
583 4 four 4 four
584 execute stmt1 ;
585 a b a b
586 1 one 1 one
587 2 two 2 two
588 3 three 3 three
589 4 four 4 four
590 execute stmt1 ;
591 a b a b
592 1 one 1 one
593 2 two 2 two
594 3 three 3 three
595 4 four 4 four
596 the join statement is:
597 SELECT * FROM t2 left join t1 using(a) order by t2.a
598 prepare stmt1 from @query6 ;
599 execute stmt1 ;
600 a b b
601 1 one one
602 2 two two
603 3 three three
604 4 four four
605 execute stmt1 ;
606 a b b
607 1 one one
608 2 two two
609 3 three three
610 4 four four
611 execute stmt1 ;
612 a b b
613 1 one one
614 2 two two
615 3 three three
616 4 four four
617 the join statement is:
618 SELECT * FROM t2 natural left join t1 order by t2.a
619 prepare stmt1 from @query5 ;
620 execute stmt1 ;
621 a b
622 1 one
623 2 two
624 3 three
625 4 four
626 execute stmt1 ;
627 a b
628 1 one
629 2 two
630 3 three
631 4 four
632 execute stmt1 ;
633 a b
634 1 one
635 2 two
636 3 three
637 4 four
638 the join statement is:
639 SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a
640 prepare stmt1 from @query4 ;
641 execute stmt1 ;
642 a b a b
643 1 one 1 one
644 2 two 2 two
645 3 three 3 three
646 4 four 4 four
647 execute stmt1 ;
648 a b a b
649 1 one 1 one
650 2 two 2 two
651 3 three 3 three
652 4 four 4 four
653 execute stmt1 ;
654 a b a b
655 1 one 1 one
656 2 two 2 two
657 3 three 3 three
658 4 four 4 four
659 the join statement is:
660 SELECT * FROM t2 join t1 using(a) order by t2.a
661 prepare stmt1 from @query3 ;
662 execute stmt1 ;
663 a b b
664 1 one one
665 2 two two
666 3 three three
667 4 four four
668 execute stmt1 ;
669 a b b
670 1 one one
671 2 two two
672 3 three three
673 4 four four
674 execute stmt1 ;
675 a b b
676 1 one one
677 2 two two
678 3 three three
679 4 four four
680 the join statement is:
681 SELECT * FROM t2 natural join t1 order by t2.a
682 prepare stmt1 from @query2 ;
683 execute stmt1 ;
684 a b
685 1 one
686 2 two
687 3 three
688 4 four
689 execute stmt1 ;
690 a b
691 1 one
692 2 two
693 3 three
694 4 four
695 execute stmt1 ;
696 a b
697 1 one
698 2 two
699 3 three
700 4 four
701 the join statement is:
702 SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a
703 prepare stmt1 from @query1 ;
704 execute stmt1 ;
705 a b a b
706 1 one 1 one
707 2 two 2 two
708 3 three 3 three
709 4 four 4 four
710 execute stmt1 ;
711 a b a b
712 1 one 1 one
713 2 two 2 two
714 3 three 3 three
715 4 four 4 four
716 execute stmt1 ;
717 a b a b
718 1 one 1 one
719 2 two 2 two
720 3 three 3 three
721 4 four 4 four
722 drop table t2 ;
723 test_sequence
724 ------ subquery tests ------
725 prepare stmt1 from ' select a, b FROM t1 outer_table where
726 a = (select a from t1 where b = ''two'') ';
727 execute stmt1 ;
728 a b
729 2 two
730 set @arg00='two' ;
731 select a, b FROM t1 outer_table where
732 a = (select a from t1 where b = 'two' ) and b=@arg00 ;
733 a b
734 2 two
735 prepare stmt1 from ' select a, b FROM t1 outer_table where
736 a = (select a from t1 where b = ''two'') and b=? ';
737 execute stmt1 using @arg00;
738 a b
739 2 two
740 set @arg00='two' ;
741 select a, b FROM t1 outer_table where
742 a = (select a from t1 where b = @arg00 ) and b='two' ;
743 a b
744 2 two
745 prepare stmt1 from ' select a, b FROM t1 outer_table where
746 a = (select a from t1 where b = ? ) and b=''two'' ' ;
747 execute stmt1 using @arg00;
748 a b
749 2 two
750 set @arg00=3 ;
751 set @arg01='three' ;
752 select a,b FROM t1 where (a,b) in (select 3, 'three');
753 a b
754 3 three
755 select a FROM t1 where (a,b) in (select @arg00,@arg01);
756 a
757 3
758 prepare stmt1 from ' select a FROM t1 where (a,b) in (select ?, ?) ';
759 execute stmt1 using @arg00, @arg01;
760 a
761 3
762 set @arg00=1 ;
763 set @arg01='two' ;
764 set @arg02=2 ;
765 set @arg03='two' ;
766 select a, @arg00, b FROM t1 outer_table where
767 b=@arg01 and a = (select @arg02 from t1 where b = @arg03 ) ;
768 a @arg00 b
769 2 1 two
770 prepare stmt1 from ' select a, ?, b FROM t1 outer_table where
771 b=? and a = (select ? from t1 where b = ? ) ' ;
772 execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
773 a ? b
774 2 1 two
775 prepare stmt1 from 'select c4 FROM t9 where
776 c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ;
777 execute stmt1 using @arg01, @arg02;
778 c4
779 prepare stmt1 from ' select a, b FROM t1 outer_table where
780 a = (select a from t1 where b = outer_table.b ) order by a ';
781 execute stmt1 ;
782 a b
783 1 one
784 2 two
785 3 three
786 4 four
787 prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
788 (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
789 execute stmt1 ;
790 ccc
791 1
792 deallocate prepare stmt1 ;
793 prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
794 (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
795 execute stmt1 ;
796 ccc
797 1
798 deallocate prepare stmt1 ;
799 prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=
800 (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';
801 execute stmt1 ;
802 ccc
803 1
804 deallocate prepare stmt1 ;
805 set @arg00='two' ;
806 select a, b FROM t1 outer_table where
807 a = (select a from t1 where b = outer_table.b ) and b=@arg00 ;
808 a b
809 2 two
810 prepare stmt1 from ' select a, b FROM t1 outer_table where
811 a = (select a from t1 where b = outer_table.b) and b=? ';
812 execute stmt1 using @arg00;
813 a b
814 2 two
815 set @arg00=2 ;
816 select a, b FROM t1 outer_table where
817 a = (select a from t1 where a = @arg00 and b = outer_table.b) and b='two' ;
818 a b
819 2 two
820 prepare stmt1 from ' select a, b FROM t1 outer_table where
821 a = (select a from t1 where a = ? and b = outer_table.b) and b=''two'' ' ;
822 execute stmt1 using @arg00;
823 a b
824 2 two
825 set @arg00=2 ;
826 select a, b FROM t1 outer_table where
827 a = (select a from t1 where outer_table.a = @arg00 and a=2) and b='two' ;
828 a b
829 2 two
830 prepare stmt1 from ' select a, b FROM t1 outer_table where
831 a = (select a from t1 where outer_table.a = ? and a=2) and b=''two'' ' ;
832 execute stmt1 using @arg00;
833 a b
834 2 two
835 set @arg00=1 ;
836 set @arg01='two' ;
837 set @arg02=2 ;
838 set @arg03='two' ;
839 select a, @arg00, b FROM t1 outer_table where
840 b=@arg01 and a = (select @arg02 from t1 where outer_table.b = @arg03
841 and outer_table.a=a ) ;
842 a @arg00 b
843 2 1 two
844 prepare stmt1 from ' select a, ?, b FROM t1 outer_table where
845 b=? and a = (select ? from t1 where outer_table.b = ?
846 and outer_table.a=a ) ' ;
847 execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
848 a ? b
849 2 1 two
850 set @arg00=1 ;
851 set @arg01=0 ;
852 select a, @arg00
853 from ( select a - @arg00 as a from t1 where a=@arg00 ) as t2
854 where a=@arg01;
855 a @arg00
856 0 1
857 prepare stmt1 from ' select a, ?
858 from ( select a - ? as a from t1 where a=? ) as t2
859 where a=? ';
860 execute stmt1 using @arg00, @arg00, @arg00, @arg01 ;
861 a ?
862 0 1
863 drop table if exists t2 ;
864 create table t2 as select * from t1;
865 prepare stmt1 from ' select a in (select a from t2) from t1 ' ;
866 execute stmt1 ;
867 a in (select a from t2)
868 1
869 1
870 1
871 1
872 drop table if exists t5, t6, t7 ;
873 create table t5 (a int , b int) ;
874 create table t6 like t5 ;
875 create table t7 like t5 ;
876 insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7),
877 (2, -1), (3, 10) ;
878 insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1) ;
879 insert into t7 values (3, 3), (2, 2), (1, 1) ;
880 prepare stmt1 from ' select a, (select count(distinct t5.b) as sum from t5, t6
881 where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
882 group by t5.a order by sum limit 1) from t7 ' ;
883 execute stmt1 ;
884 a (select count(distinct t5.b) as sum from t5, t6
885 where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
886 group by t5.a order by sum limit 1)
887 3 1
888 2 2
889 1 2
890 execute stmt1 ;
891 a (select count(distinct t5.b) as sum from t5, t6
892 where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
893 group by t5.a order by sum limit 1)
894 3 1
895 2 2
896 1 2
897 execute stmt1 ;
898 a (select count(distinct t5.b) as sum from t5, t6
899 where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
900 group by t5.a order by sum limit 1)
901 3 1
902 2 2
903 1 2
904 drop table t5, t6, t7 ;
905 drop table if exists t2 ;
906 create table t2 as select * from t9;
907 set @stmt= ' SELECT
908 (SELECT SUM(c1 + c12 + 0.0) FROM t2
909 where (t9.c2 - 0e-3) = t2.c2
910 GROUP BY t9.c15 LIMIT 1) as scalar_s,
911 exists (select 1.0e+0 from t2
912 where t2.c3 * 9.0000000000 = t9.c4) as exists_s,
913 c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s,
914 (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s
915 FROM t9,
916 (select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
917 prepare stmt1 from @stmt ;
918 execute stmt1 ;
919 execute stmt1 ;
920 set @stmt= concat('explain ',@stmt);
921 prepare stmt1 from @stmt ;
922 execute stmt1 ;
923 execute stmt1 ;
924 set @stmt= ' SELECT
925 (SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2
926 GROUP BY t9.c15 LIMIT 1) as scalar_s,
927 exists (select ? from t2
928 where t2.c3*?=t9.c4) as exists_s,
929 c5*? in (select c6+? from t2) as in_s,
930 (c7-?, c8-?) in (select c9+?, c10+? from t2) as in_row_s
931 FROM t9,
932 (select c25 x, c32 y from t2) tt WHERE x =c25 ' ;
933 set @arg00= 0.0 ;
934 set @arg01= 0e-3 ;
935 set @arg02= 1.0e+0 ;
936 set @arg03= 9.0000000000 ;
937 set @arg04= 4 ;
938 set @arg05= 0.3e+1 ;
939 set @arg06= 4 ;
940 set @arg07= 4 ;
941 set @arg08= 4.0 ;
942 set @arg09= 40e-1 ;
943 prepare stmt1 from @stmt ;
944 execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
945 @arg07, @arg08, @arg09 ;
946 execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
947 @arg07, @arg08, @arg09 ;
948 set @stmt= concat('explain ',@stmt);
949 prepare stmt1 from @stmt ;
950 execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
951 @arg07, @arg08, @arg09 ;
952 execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
953 @arg07, @arg08, @arg09 ;
954 drop table t2 ;
955 select 1 < (select a from t1) ;
956 ERROR 21000: Subquery returns more than 1 row
957 prepare stmt1 from ' select 1 < (select a from t1) ' ;
958 execute stmt1 ;
959 ERROR 21000: Subquery returns more than 1 row
960 select 1 as my_col ;
961 my_col
962 1
963 test_sequence
964 ------ union tests ------
965 prepare stmt1 from ' select a FROM t1 where a=1
966 union distinct
967 select a FROM t1 where a=1 ';
968 execute stmt1 ;
969 a
970 1
971 execute stmt1 ;
972 a
973 1
974 prepare stmt1 from ' select a FROM t1 where a=1
975 union all
976 select a FROM t1 where a=1 ';
977 execute stmt1 ;
978 a
979 1
980 1
981 prepare stmt1 from ' SELECT 1, 2 union SELECT 1 ' ;
982 ERROR 21000: The used SELECT statements have a different number of columns
983 prepare stmt1 from ' SELECT 1 union SELECT 1, 2 ' ;
984 ERROR 21000: The used SELECT statements have a different number of columns
985 prepare stmt1 from ' SELECT * from t1 union SELECT 1 ' ;
986 ERROR 21000: The used SELECT statements have a different number of columns
987 prepare stmt1 from ' SELECT 1 union SELECT * from t1 ' ;
988 ERROR 21000: The used SELECT statements have a different number of columns
989 set @arg00=1 ;
990 select @arg00 FROM t1 where a=1
991 union distinct
992 select 1 FROM t1 where a=1;
993 @arg00
994 1
995 prepare stmt1 from ' select ? FROM t1 where a=1
996 union distinct
997 select 1 FROM t1 where a=1 ' ;
998 execute stmt1 using @arg00;
999 ?
1000 1
1001 set @arg00=1 ;
1002 select 1 FROM t1 where a=1
1003 union distinct
1004 select @arg00 FROM t1 where a=1;
1005 1
1006 1
1007 prepare stmt1 from ' select 1 FROM t1 where a=1
1008 union distinct
1009 select ? FROM t1 where a=1 ' ;
1010 execute stmt1 using @arg00;
1011 1
1012 1
1013 set @arg00='a' ;
1014 select @arg00 FROM t1 where a=1
1015 union distinct
1016 select @arg00 FROM t1 where a=1;
1017 @arg00
1018 a
1019 prepare stmt1 from ' select ? FROM t1 where a=1
1020 union distinct
1021 select ? FROM t1 where a=1 ';
1022 execute stmt1 using @arg00, @arg00;
1023 ?
1024 a
1025 prepare stmt1 from ' select ?
1026 union distinct
1027 select ? ';
1028 execute stmt1 using @arg00, @arg00;
1029 ?
1030 a
1031 set @arg00='a' ;
1032 set @arg01=1 ;
1033 set @arg02='a' ;
1034 set @arg03=2 ;
1035 select @arg00 FROM t1 where a=@arg01
1036 union distinct
1037 select @arg02 FROM t1 where a=@arg03;
1038 @arg00
1039 a
1040 prepare stmt1 from ' select ? FROM t1 where a=?
1041 union distinct
1042 select ? FROM t1 where a=? ' ;
1043 execute stmt1 using @arg00, @arg01, @arg02, @arg03;
1044 ?
1045 a
1046 set @arg00=1 ;
1047 prepare stmt1 from ' select sum(a) + 200, ? from t1
1048 union distinct
1049 select sum(a) + 200, 1 from t1
1050 group by b ' ;
1051 execute stmt1 using @arg00;
1052 sum(a) + 200 ?
1053 210 1
1054 204 1
1055 201 1
1056 203 1
1057 202 1
1058 set @Oporto='Oporto' ;
1059 set @Lisboa='Lisboa' ;
1060 set @0=0 ;
1061 set @1=1 ;
1062 set @2=2 ;
1063 set @3=3 ;
1064 set @4=4 ;
1065 select @Oporto,@Lisboa,@0,@1,@2,@3,@4 ;
1066 @Oporto @Lisboa @0 @1 @2 @3 @4
1067 Oporto Lisboa 0 1 2 3 4
1068 select sum(a) + 200 as the_sum, @Oporto as the_town from t1
1069 group by b
1070 union distinct
1071 select sum(a) + 200, @Lisboa from t1
1072 group by b ;
1073 the_sum the_town
1074 204 Oporto
1075 201 Oporto
1076 203 Oporto
1077 202 Oporto
1078 204 Lisboa
1079 201 Lisboa
1080 203 Lisboa
1081 202 Lisboa
1082 prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
1083 group by b
1084 union distinct
1085 select sum(a) + 200, ? from t1
1086 group by b ' ;
1087 execute stmt1 using @Oporto, @Lisboa;
1088 the_sum the_town
1089 204 Oporto
1090 201 Oporto
1091 203 Oporto
1092 202 Oporto
1093 204 Lisboa
1094 201 Lisboa
1095 203 Lisboa
1096 202 Lisboa
1097 select sum(a) + 200 as the_sum, @Oporto as the_town from t1
1098 where a > @1
1099 group by b
1100 union distinct
1101 select sum(a) + 200, @Lisboa from t1
1102 where a > @2
1103 group by b ;
1104 the_sum the_town
1105 204 Oporto
1106 203 Oporto
1107 202 Oporto
1108 204 Lisboa
1109 203 Lisboa
1110 prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
1111 where a > ?
1112 group by b
1113 union distinct
1114 select sum(a) + 200, ? from t1
1115 where a > ?
1116 group by b ' ;
1117 execute stmt1 using @Oporto, @1, @Lisboa, @2;
1118 the_sum the_town
1119 204 Oporto
1120 203 Oporto
1121 202 Oporto
1122 204 Lisboa
1123 203 Lisboa
1124 select sum(a) + 200 as the_sum, @Oporto as the_town from t1
1125 where a > @1
1126 group by b
1127 having avg(a) > @2
1128 union distinct
1129 select sum(a) + 200, @Lisboa from t1
1130 where a > @2
1131 group by b
1132 having avg(a) > @3;
1133 the_sum the_town
1134 204 Oporto
1135 203 Oporto
1136 204 Lisboa
1137 prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
1138 where a > ?
1139 group by b
1140 having avg(a) > ?
1141 union distinct
1142 select sum(a) + 200, ? from t1
1143 where a > ?
1144 group by b
1145 having avg(a) > ? ';
1146 execute stmt1 using @Oporto, @1, @2, @Lisboa, @2, @3;
1147 the_sum the_town
1148 204 Oporto
1149 203 Oporto
1150 204 Lisboa
1151 test_sequence
1152 ------ explain select tests ------
1153 prepare stmt1 from ' explain select * from t9 ' ;
1154 execute stmt1;
1155 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1156 def id 8 3 1 Y 32928 0 63
1157 def select_type 253 19 6 N 1 39 8
1158 def table 253 64 2 Y 0 39 8
1159 def type 253 10 3 Y 0 39 8
1160 def possible_keys 253 4_OR_8_K 0 Y 0 39 8
1161 def key 253 64 0 Y 0 39 8
1162 def key_len 253 4_OR_8_K 0 Y 0 39 8
1163 def ref 253 2048 0 Y 0 39 8
1164 def rows 253 64 1 Y 0 39 8
1165 def Extra 253 255 0 N 1 39 8
1166 id select_type table type possible_keys key key_len ref rows Extra
1167 1 SIMPLE t9 ALL NULL NULL NULL NULL 2
1168 drop table if exists t2 ;
1169 create table t2 (s varchar(25), fulltext(s)) TRANSACTIONAL= 0
1170 ENGINE = 'ARIA' ;
1171 insert into t2 values ('Gravedigger'), ('Greed'),('Hollow Dogs') ;
1172 commit ;
1173 prepare stmt1 from ' select s from t2 where match (s) against (?) ' ;
1174 set @arg00='Dogs' ;
1175 execute stmt1 using @arg00 ;
1176 s
1177 Hollow Dogs
1178 prepare stmt1 from ' SELECT s FROM t2
1179 where match (s) against (concat(?,''digger'')) ';
1180 set @arg00='Grave' ;
1181 execute stmt1 using @arg00 ;
1182 s
1183 Gravedigger
1184 drop table t2 ;
1185 test_sequence
1186 ------ delete tests ------
1187 delete from t1 ;
1188 insert into t1 values (1,'one');
1189 insert into t1 values (2,'two');
1190 insert into t1 values (3,'three');
1191 insert into t1 values (4,'four');
1192 commit ;
1193 delete from t9 ;
1194 insert into t9
1195 set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1196 c10= 1, c11= 1, c12 = 1,
1197 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1198 c16= '11:11:11', c17= '2004',
1199 c18= 1, c19=true, c20= 'a', c21= '123456789a',
1200 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1201 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1202 c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1203 insert into t9
1204 set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1205 c10= 9, c11= 9, c12 = 9,
1206 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1207 c16= '11:11:11', c17= '2004',
1208 c18= 1, c19=false, c20= 'a', c21= '123456789a',
1209 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1210 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1211 c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1212 commit ;
1213 prepare stmt1 from 'delete from t1 where a=2' ;
1214 execute stmt1;
1215 select a,b from t1 where a=2;
1216 a b
1217 execute stmt1;
1218 insert into t1 values(0,NULL);
1219 set @arg00=NULL;
1220 prepare stmt1 from 'delete from t1 where b=?' ;
1221 execute stmt1 using @arg00;
1222 select a,b from t1 where b is NULL ;
1223 a b
1224 0 NULL
1225 set @arg00='one';
1226 execute stmt1 using @arg00;
1227 select a,b from t1 where b=@arg00;
1228 a b
1229 prepare stmt1 from 'truncate table t1' ;
1230 test_sequence
1231 ------ update tests ------
1232 delete from t1 ;
1233 insert into t1 values (1,'one');
1234 insert into t1 values (2,'two');
1235 insert into t1 values (3,'three');
1236 insert into t1 values (4,'four');
1237 commit ;
1238 delete from t9 ;
1239 insert into t9
1240 set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1241 c10= 1, c11= 1, c12 = 1,
1242 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1243 c16= '11:11:11', c17= '2004',
1244 c18= 1, c19=true, c20= 'a', c21= '123456789a',
1245 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1246 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1247 c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1248 insert into t9
1249 set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1250 c10= 9, c11= 9, c12 = 9,
1251 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1252 c16= '11:11:11', c17= '2004',
1253 c18= 1, c19=false, c20= 'a', c21= '123456789a',
1254 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1255 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1256 c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1257 commit ;
1258 prepare stmt1 from 'update t1 set b=''a=two'' where a=2' ;
1259 execute stmt1;
1260 select a,b from t1 where a=2;
1261 a b
1262 2 a=two
1263 execute stmt1;
1264 select a,b from t1 where a=2;
1265 a b
1266 2 a=two
1267 set @arg00=NULL;
1268 prepare stmt1 from 'update t1 set b=? where a=2' ;
1269 execute stmt1 using @arg00;
1270 select a,b from t1 where a=2;
1271 a b
1272 2 NULL
1273 set @arg00='two';
1274 execute stmt1 using @arg00;
1275 select a,b from t1 where a=2;
1276 a b
1277 2 two
1278 set @arg00=2;
1279 prepare stmt1 from 'update t1 set b=NULL where a=?' ;
1280 execute stmt1 using @arg00;
1281 select a,b from t1 where a=@arg00;
1282 a b
1283 2 NULL
1284 update t1 set b='two' where a=@arg00;
1285 set @arg00=2000;
1286 execute stmt1 using @arg00;
1287 select a,b from t1 where a=@arg00;
1288 a b
1289 set @arg00=2;
1290 set @arg01=22;
1291 prepare stmt1 from 'update t1 set a=? where a=?' ;
1292 execute stmt1 using @arg00, @arg00;
1293 select a,b from t1 where a=@arg00;
1294 a b
1295 2 two
1296 execute stmt1 using @arg01, @arg00;
1297 select a,b from t1 where a=@arg01;
1298 a b
1299 22 two
1300 execute stmt1 using @arg00, @arg01;
1301 select a,b from t1 where a=@arg00;
1302 a b
1303 2 two
1304 set @arg00=NULL;
1305 set @arg01=2;
1306 set sql_mode = '';
1307 execute stmt1 using @arg00, @arg01;
1308 Warnings:
1309 Warning 1048 Column 'a' cannot be null
1310 set sql_mode = default;
1311 select a,b from t1 order by a;
1312 a b
1313 0 two
1314 1 one
1315 3 three
1316 4 four
1317 set @arg00=0;
1318 execute stmt1 using @arg01, @arg00;
1319 select a,b from t1 order by a;
1320 a b
1321 1 one
1322 2 two
1323 3 three
1324 4 four
1325 set @arg00=23;
1326 set @arg01='two';
1327 set @arg02=2;
1328 set @arg03='two';
1329 set @arg04=2;
1330 drop table if exists t2;
1331 create table t2 as select a,b from t1 ;
1332 prepare stmt1 from 'update t1 set a=? where b=?
1333 and a in (select ? from t2
1334 where b = ? or a = ?)';
1335 execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;
1336 affected rows: 1
1337 info: Rows matched: 1 Changed: 1 Warnings: 0
1338 select a,b from t1 where a = @arg00 ;
1339 a b
1340 23 two
1341 prepare stmt1 from 'update t1 set a=? where b=?
1342 and a not in (select ? from t2
1343 where b = ? or a = ?)';
1344 execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;
1345 affected rows: 1
1346 info: Rows matched: 1 Changed: 1 Warnings: 0
1347 select a,b from t1 order by a ;
1348 a b
1349 1 one
1350 2 two
1351 3 three
1352 4 four
1353 drop table t2 ;
1354 create table t2
1355 (
1356 a int, b varchar(30),
1357 primary key(a)
1358 ) engine = 'ARIA' ;
1359 insert into t2(a,b) select a, b from t1 ;
1360 prepare stmt1 from 'update t1 set a=? where b=?
1361 and a in (select ? from t2
1362 where b = ? or a = ?)';
1363 execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;
1364 affected rows: 1
1365 info: Rows matched: 1 Changed: 1 Warnings: 0
1366 select a,b from t1 where a = @arg00 ;
1367 a b
1368 23 two
1369 prepare stmt1 from 'update t1 set a=? where b=?
1370 and a not in (select ? from t2
1371 where b = ? or a = ?)';
1372 execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;
1373 affected rows: 1
1374 info: Rows matched: 1 Changed: 1 Warnings: 0
1375 select a,b from t1 order by a ;
1376 a b
1377 1 one
1378 2 two
1379 3 three
1380 4 four
1381 drop table t2 ;
1382 set @arg00=1;
1383 prepare stmt1 from 'update t1 set b=''bla''
1384 where a=2
1385 limit 1';
1386 execute stmt1 ;
1387 select a,b from t1 where b = 'bla' ;
1388 a b
1389 2 bla
1390 prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';
1391 execute stmt1 using @arg00;
1392 test_sequence
1393 ------ insert tests ------
1394 delete from t1 ;
1395 insert into t1 values (1,'one');
1396 insert into t1 values (2,'two');
1397 insert into t1 values (3,'three');
1398 insert into t1 values (4,'four');
1399 commit ;
1400 delete from t9 ;
1401 insert into t9
1402 set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1403 c10= 1, c11= 1, c12 = 1,
1404 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1405 c16= '11:11:11', c17= '2004',
1406 c18= 1, c19=true, c20= 'a', c21= '123456789a',
1407 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1408 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1409 c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1410 insert into t9
1411 set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1412 c10= 9, c11= 9, c12 = 9,
1413 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1414 c16= '11:11:11', c17= '2004',
1415 c18= 1, c19=false, c20= 'a', c21= '123456789a',
1416 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1417 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1418 c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1419 commit ;
1420 prepare stmt1 from 'insert into t1 values(5, ''five'' )';
1421 execute stmt1;
1422 select a,b from t1 where a = 5;
1423 a b
1424 5 five
1425 set @arg00='six' ;
1426 prepare stmt1 from 'insert into t1 values(6, ? )';
1427 execute stmt1 using @arg00;
1428 select a,b from t1 where b = @arg00;
1429 a b
1430 6 six
1431 execute stmt1 using @arg00;
1432 ERROR 23000: Duplicate entry '6' for key 'PRIMARY'
1433 set @arg00=NULL ;
1434 prepare stmt1 from 'insert into t1 values(0, ? )';
1435 execute stmt1 using @arg00;
1436 select a,b from t1 where b is NULL;
1437 a b
1438 0 NULL
1439 set @arg00=8 ;
1440 set @arg01='eight' ;
1441 prepare stmt1 from 'insert into t1 values(?, ? )';
1442 execute stmt1 using @arg00, @arg01 ;
1443 select a,b from t1 where b = @arg01;
1444 a b
1445 8 eight
1446 set @NULL= null ;
1447 set @arg00= 'abc' ;
1448 execute stmt1 using @NULL, @NULL ;
1449 ERROR 23000: Column 'a' cannot be null
1450 execute stmt1 using @NULL, @NULL ;
1451 ERROR 23000: Column 'a' cannot be null
1452 execute stmt1 using @NULL, @arg00 ;
1453 ERROR 23000: Column 'a' cannot be null
1454 execute stmt1 using @NULL, @arg00 ;
1455 ERROR 23000: Column 'a' cannot be null
1456 set @arg01= 10000 + 2 ;
1457 execute stmt1 using @arg01, @arg00 ;
1458 set @arg01= 10000 + 1 ;
1459 execute stmt1 using @arg01, @arg00 ;
1460 select * from t1 where a > 10000 order by a ;
1461 a b
1462 10001 abc
1463 10002 abc
1464 delete from t1 where a > 10000 ;
1465 set @arg01= 10000 + 2 ;
1466 execute stmt1 using @arg01, @NULL ;
1467 set @arg01= 10000 + 1 ;
1468 execute stmt1 using @arg01, @NULL ;
1469 select * from t1 where a > 10000 order by a ;
1470 a b
1471 10001 NULL
1472 10002 NULL
1473 delete from t1 where a > 10000 ;
1474 set @arg01= 10000 + 10 ;
1475 execute stmt1 using @arg01, @arg01 ;
1476 set @arg01= 10000 + 9 ;
1477 execute stmt1 using @arg01, @arg01 ;
1478 set @arg01= 10000 + 8 ;
1479 execute stmt1 using @arg01, @arg01 ;
1480 set @arg01= 10000 + 7 ;
1481 execute stmt1 using @arg01, @arg01 ;
1482 set @arg01= 10000 + 6 ;
1483 execute stmt1 using @arg01, @arg01 ;
1484 set @arg01= 10000 + 5 ;
1485 execute stmt1 using @arg01, @arg01 ;
1486 set @arg01= 10000 + 4 ;
1487 execute stmt1 using @arg01, @arg01 ;
1488 set @arg01= 10000 + 3 ;
1489 execute stmt1 using @arg01, @arg01 ;
1490 set @arg01= 10000 + 2 ;
1491 execute stmt1 using @arg01, @arg01 ;
1492 set @arg01= 10000 + 1 ;
1493 execute stmt1 using @arg01, @arg01 ;
1494 select * from t1 where a > 10000 order by a ;
1495 a b
1496 10001 10001
1497 10002 10002
1498 10003 10003
1499 10004 10004
1500 10005 10005
1501 10006 10006
1502 10007 10007
1503 10008 10008
1504 10009 10009
1505 10010 10010
1506 delete from t1 where a > 10000 ;
1507 set @arg00=81 ;
1508 set @arg01='8-1' ;
1509 set @arg02=82 ;
1510 set @arg03='8-2' ;
1511 prepare stmt1 from 'insert into t1 values(?,?),(?,?)';
1512 execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
1513 select a,b from t1 where a in (@arg00,@arg02) ;
1514 a b
1515 81 8-1
1516 82 8-2
1517 set @arg00=9 ;
1518 set @arg01='nine' ;
1519 prepare stmt1 from 'insert into t1 set a=?, b=? ';
1520 execute stmt1 using @arg00, @arg01 ;
1521 select a,b from t1 where a = @arg00 ;
1522 a b
1523 9 nine
1524 set @arg00=6 ;
1525 set @arg01=1 ;
1526 prepare stmt1 from 'insert into t1 set a=?, b=''sechs''
1527 on duplicate key update a=a + ?, b=concat(b,''modified'') ';
1528 execute stmt1 using @arg00, @arg01;
1529 select * from t1 order by a;
1530 a b
1531 0 NULL
1532 1 one
1533 2 two
1534 3 three
1535 4 four
1536 5 five
1537 7 sixmodified
1538 8 eight
1539 9 nine
1540 81 8-1
1541 82 8-2
1542 set @arg00=81 ;
1543 set @arg01=1 ;
1544 execute stmt1 using @arg00, @arg01;
1545 ERROR 23000: Duplicate entry '82' for key 'PRIMARY'
1546 drop table if exists t2 ;
1547 create table t2 (id int auto_increment primary key)
1548 ENGINE= 'ARIA' ;
1549 prepare stmt1 from ' select last_insert_id() ' ;
1550 insert into t2 values (NULL) ;
1551 execute stmt1 ;
1552 last_insert_id()
1553 1
1554 insert into t2 values (NULL) ;
1555 execute stmt1 ;
1556 last_insert_id()
1557 2
1558 drop table t2 ;
1559 set @1000=1000 ;
1560 set @x1000_2="x1000_2" ;
1561 set @x1000_3="x1000_3" ;
1562 set @x1000="x1000" ;
1563 set @1100=1100 ;
1564 set @x1100="x1100" ;
1565 set @100=100 ;
1566 set @updated="updated" ;
1567 insert into t1 values(1000,'x1000_1') ;
1568 insert into t1 values(@1000,@x1000_2),(@1000,@x1000_3)
1569 on duplicate key update a = a + @100, b = concat(b,@updated) ;
1570 select a,b from t1 where a >= 1000 order by a ;
1571 a b
1572 1000 x1000_3
1573 1100 x1000_1updated
1574 delete from t1 where a >= 1000 ;
1575 insert into t1 values(1000,'x1000_1') ;
1576 prepare stmt1 from ' insert into t1 values(?,?),(?,?)
1577 on duplicate key update a = a + ?, b = concat(b,?) ';
1578 execute stmt1 using @1000, @x1000_2, @1000, @x1000_3, @100, @updated ;
1579 select a,b from t1 where a >= 1000 order by a ;
1580 a b
1581 1000 x1000_3
1582 1100 x1000_1updated
1583 delete from t1 where a >= 1000 ;
1584 insert into t1 values(1000,'x1000_1') ;
1585 execute stmt1 using @1000, @x1000_2, @1100, @x1000_3, @100, @updated ;
1586 select a,b from t1 where a >= 1000 order by a ;
1587 a b
1588 1200 x1000_1updatedupdated
1589 delete from t1 where a >= 1000 ;
1590 prepare stmt1 from ' replace into t1 (a,b) select 100, ''hundred'' ';
1591 execute stmt1;
1592 execute stmt1;
1593 execute stmt1;
1594 test_sequence
1595 ------ multi table tests ------
1596 delete from t1 ;
1597 delete from t9 ;
1598 insert into t1(a,b) values (1, 'one'), (2, 'two'), (3, 'three') ;
1599 insert into t9 (c1,c21)
1600 values (1, 'one'), (2, 'two'), (3, 'three') ;
1601 prepare stmt_delete from " delete t1, t9
1602 from t1, t9 where t1.a=t9.c1 and t1.b='updated' ";
1603 prepare stmt_update from " update t1, t9
1604 set t1.b='updated', t9.c21='updated'
1605 where t1.a=t9.c1 and t1.a=? ";
1606 prepare stmt_select1 from " select a, b from t1 order by a" ;
1607 prepare stmt_select2 from " select c1, c21 from t9 order by c1" ;
1608 set @arg00= 1 ;
1609 execute stmt_update using @arg00 ;
1610 execute stmt_delete ;
1611 execute stmt_select1 ;
1612 a b
1613 2 two
1614 3 three
1615 execute stmt_select2 ;
1616 c1 c21
1617 2 two
1618 3 three
1619 set @arg00= @arg00 + 1 ;
1620 execute stmt_update using @arg00 ;
1621 execute stmt_delete ;
1622 execute stmt_select1 ;
1623 a b
1624 3 three
1625 execute stmt_select2 ;
1626 c1 c21
1627 3 three
1628 set @arg00= @arg00 + 1 ;
1629 execute stmt_update using @arg00 ;
1630 execute stmt_delete ;
1631 execute stmt_select1 ;
1632 a b
1633 execute stmt_select2 ;
1634 c1 c21
1635 set @arg00= @arg00 + 1 ;
1636 delete from t1 ;
1637 insert into t1 values (1,'one');
1638 insert into t1 values (2,'two');
1639 insert into t1 values (3,'three');
1640 insert into t1 values (4,'four');
1641 commit ;
1642 delete from t9 ;
1643 insert into t9
1644 set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1645 c10= 1, c11= 1, c12 = 1,
1646 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1647 c16= '11:11:11', c17= '2004',
1648 c18= 1, c19=true, c20= 'a', c21= '123456789a',
1649 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1650 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1651 c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1652 insert into t9
1653 set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1654 c10= 9, c11= 9, c12 = 9,
1655 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1656 c16= '11:11:11', c17= '2004',
1657 c18= 1, c19=false, c20= 'a', c21= '123456789a',
1658 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1659 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1660 c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1661 commit ;
1662 insert into t1 values(0,NULL) ;
1663 set @duplicate='duplicate ' ;
1664 set @1000=1000 ;
1665 set @5=5 ;
1666 select a,b from t1 where a < 5 order by a ;
1667 a b
1668 0 NULL
1669 1 one
1670 2 two
1671 3 three
1672 4 four
1673 insert into t1 select a + @1000, concat(@duplicate,b) from t1
1674 where a < @5 ;
1675 affected rows: 5
1676 info: Records: 5 Duplicates: 0 Warnings: 0
1677 select a,b from t1 where a >= 1000 order by a ;
1678 a b
1679 1000 NULL
1680 1001 duplicate one
1681 1002 duplicate two
1682 1003 duplicate three
1683 1004 duplicate four
1684 delete from t1 where a >= 1000 ;
1685 prepare stmt1 from ' insert into t1 select a + ?, concat(?,b) from t1
1686 where a < ? ' ;
1687 execute stmt1 using @1000, @duplicate, @5;
1688 affected rows: 5
1689 info: Records: 5 Duplicates: 0 Warnings: 0
1690 select a,b from t1 where a >= 1000 order by a ;
1691 a b
1692 1000 NULL
1693 1001 duplicate one
1694 1002 duplicate two
1695 1003 duplicate three
1696 1004 duplicate four
1697 delete from t1 where a >= 1000 ;
1698 set @1=1 ;
1699 set @2=2 ;
1700 set @100=100 ;
1701 set @float=1.00;
1702 set @five='five' ;
1703 drop table if exists t2;
1704 create table t2 like t1 ;
1705 insert into t2 (b,a)
1706 select @duplicate, sum(first.a) from t1 first, t1 second
1707 where first.a <> @5 and second.b = first.b
1708 and second.b <> @five
1709 group by second.b
1710 having sum(second.a) > @2
1711 union
1712 select b, a + @100 from t1
1713 where (a,b) in ( select sqrt(a+@1)+CAST(@float AS signed),b
1714 from t1);
1715 affected rows: 3
1716 info: Records: 3 Duplicates: 0 Warnings: 0
1717 select a,b from t2 order by a ;
1718 a b
1719 3 duplicate
1720 4 duplicate
1721 103 three
1722 delete from t2 ;
1723 prepare stmt1 from ' insert into t2 (b,a)
1724 select ?, sum(first.a)
1725 from t1 first, t1 second
1726 where first.a <> ? and second.b = first.b and second.b <> ?
1727 group by second.b
1728 having sum(second.a) > ?
1729 union
1730 select b, a + ? from t1
1731 where (a,b) in ( select sqrt(a+?)+CAST(? AS signed),b
1732 from t1 ) ' ;
1733 execute stmt1 using @duplicate, @5, @five, @2, @100, @1, @float ;
1734 affected rows: 3
1735 info: Records: 3 Duplicates: 0 Warnings: 0
1736 select a,b from t2 order by a ;
1737 a b
1738 3 duplicate
1739 4 duplicate
1740 103 three
1741 drop table t2;
1742 drop table if exists t5 ;
1743 set @arg01= 8;
1744 set @arg02= 8.0;
1745 set @arg03= 80.00000000000e-1;
1746 set @arg04= 'abc' ;
1747 set @arg05= CAST('abc' as binary) ;
1748 set @arg06= '1991-08-05' ;
1749 set @arg07= CAST('1991-08-05' as date);
1750 set @arg08= '1991-08-05 01:01:01' ;
1751 set @arg09= CAST('1991-08-05 01:01:01' as datetime) ;
1752 set @arg10= unix_timestamp('1991-01-01 01:01:01');
1753 set @arg11= YEAR('1991-01-01 01:01:01');
1754 set @arg12= 8 ;
1755 set @arg12= NULL ;
1756 set @arg13= 8.0 ;
1757 set @arg13= NULL ;
1758 set @arg14= 'abc';
1759 set @arg14= NULL ;
1760 set @arg15= CAST('abc' as binary) ;
1761 set @arg15= NULL ;
1762 create table t5 engine = MyISAM as select
1763 8 as const01, @arg01 as param01,
1764 8.0 as const02, @arg02 as param02,
1765 80.00000000000e-1 as const03, @arg03 as param03,
1766 'abc' as const04, @arg04 as param04,
1767 CAST('abc' as binary) as const05, @arg05 as param05,
1768 '1991-08-05' as const06, @arg06 as param06,
1769 CAST('1991-08-05' as date) as const07, @arg07 as param07,
1770 '1991-08-05 01:01:01' as const08, @arg08 as param08,
1771 CAST('1991-08-05 01:01:01' as datetime) as const09, @arg09 as param09,
1772 unix_timestamp('1991-01-01 01:01:01') as const10, @arg10 as param10,
1773 YEAR('1991-01-01 01:01:01') as const11, @arg11 as param11,
1774 NULL as const12, @arg12 as param12,
1775 @arg13 as param13,
1776 @arg14 as param14,
1777 @arg15 as param15;
1778 show create table t5 ;
1779 Table Create Table
1780 t5 CREATE TABLE `t5` (
1781 `const01` int(1) NOT NULL,
1782 `param01` bigint(20) DEFAULT NULL,
1783 `const02` decimal(2,1) NOT NULL,
1784 `param02` decimal(65,38) DEFAULT NULL,
1785 `const03` double NOT NULL,
1786 `param03` double DEFAULT NULL,
1787 `const04` varchar(3) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
1788 `param04` longtext CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
1789 `const05` varbinary(3) DEFAULT NULL,
1790 `param05` longblob DEFAULT NULL,
1791 `const06` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
1792 `param06` longtext CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
1793 `const07` date DEFAULT NULL,
1794 `param07` longtext CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
1795 `const08` varchar(19) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
1796 `param08` longtext CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
1797 `const09` datetime DEFAULT NULL,
1798 `param09` longtext CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
1799 `const10` bigint(17) DEFAULT NULL,
1800 `param10` bigint(20) DEFAULT NULL,
1801 `const11` int(5) DEFAULT NULL,
1802 `param11` bigint(20) DEFAULT NULL,
1803 `const12` binary(0) DEFAULT NULL,
1804 `param12` bigint(20) DEFAULT NULL,
1805 `param13` decimal(65,38) DEFAULT NULL,
1806 `param14` longtext CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
1807 `param15` longblob DEFAULT NULL
1808 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1809 select * from t5 ;
1810 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1811 def test t5 t5 const01 const01 3 1 1 N 36865 0 63
1812 def test t5 t5 param01 param01 8 20 1 Y 32768 0 63
1813 def test t5 t5 const02 const02 246 4 3 N 36865 1 63
1814 def test t5 t5 param02 param02 246 67 40 Y 32768 38 63
1815 def test t5 t5 const03 const03 5 17 1 N 36865 31 63
1816 def test t5 t5 param03 param03 5 23 1 Y 32768 31 63
1817 def test t5 t5 const04 const04 253 3 3 N 4097 0 8
1818 def test t5 t5 param04 param04 252 4294967295 3 Y 16 0 8
1819 def test t5 t5 const05 const05 253 3 3 Y 128 0 63
1820 def test t5 t5 param05 param05 252 4294967295 3 Y 144 0 63
1821 def test t5 t5 const06 const06 253 10 10 N 4097 0 8
1822 def test t5 t5 param06 param06 252 4294967295 10 Y 16 0 8
1823 def test t5 t5 const07 const07 10 10 10 Y 128 0 63
1824 def test t5 t5 param07 param07 252 4294967295 10 Y 16 0 8
1825 def test t5 t5 const08 const08 253 19 19 N 4097 0 8
1826 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8
1827 def test t5 t5 const09 const09 12 19 19 Y 128 0 63
1828 def test t5 t5 param09 param09 252 4294967295 19 Y 16 0 8
1829 def test t5 t5 const10 const10 8 17 9 Y 32768 0 63
1830 def test t5 t5 param10 param10 8 20 9 Y 32768 0 63
1831 def test t5 t5 const11 const11 3 5 4 Y 32768 0 63
1832 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63
1833 def test t5 t5 const12 const12 254 0 0 Y 128 0 63
1834 def test t5 t5 param12 param12 8 20 0 Y 32768 0 63
1835 def test t5 t5 param13 param13 246 67 0 Y 32768 38 63
1836 def test t5 t5 param14 param14 252 4294967295 0 Y 16 0 8
1837 def test t5 t5 param15 param15 252 4294967295 0 Y 144 0 63
1838 const01 8
1839 param01 8
1840 const02 8.0
1841 param02 8.00000000000000000000000000000000000000
1842 const03 8
1843 param03 8
1844 const04 abc
1845 param04 abc
1846 const05 abc
1847 param05 abc
1848 const06 1991-08-05
1849 param06 1991-08-05
1850 const07 1991-08-05
1851 param07 1991-08-05
1852 const08 1991-08-05 01:01:01
1853 param08 1991-08-05 01:01:01
1854 const09 1991-08-05 01:01:01
1855 param09 1991-08-05 01:01:01
1856 const10 662680861
1857 param10 662680861
1858 const11 1991
1859 param11 1991
1860 const12 NULL
1861 param12 NULL
1862 param13 NULL
1863 param14 NULL
1864 param15 NULL
1865 drop table t5 ;
1866 test_sequence
1867 ------ data type conversion tests ------
1868 delete from t1 ;
1869 insert into t1 values (1,'one');
1870 insert into t1 values (2,'two');
1871 insert into t1 values (3,'three');
1872 insert into t1 values (4,'four');
1873 commit ;
1874 delete from t9 ;
1875 insert into t9
1876 set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
1877 c10= 1, c11= 1, c12 = 1,
1878 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1879 c16= '11:11:11', c17= '2004',
1880 c18= 1, c19=true, c20= 'a', c21= '123456789a',
1881 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1882 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1883 c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
1884 insert into t9
1885 set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
1886 c10= 9, c11= 9, c12 = 9,
1887 c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
1888 c16= '11:11:11', c17= '2004',
1889 c18= 1, c19=false, c20= 'a', c21= '123456789a',
1890 c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
1891 c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
1892 c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
1893 commit ;
1894 insert into t9 set c1= 0, c15= '1991-01-01 01:01:01' ;
1895 select * from t9 order by c1 ;
1896 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
1897 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
1898 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
1899 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
1900 test_sequence
1901 ------ select @parameter:= column ------
1902 prepare full_info from "select @arg01, @arg02, @arg03, @arg04,
1903 @arg05, @arg06, @arg07, @arg08,
1904 @arg09, @arg10, @arg11, @arg12,
1905 @arg13, @arg14, @arg15, @arg16,
1906 @arg17, @arg18, @arg19, @arg20,
1907 @arg21, @arg22, @arg23, @arg24,
1908 @arg25, @arg26, @arg27, @arg28,
1909 @arg29, @arg30, @arg31, @arg32" ;
1910 select @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,
1911 @arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,
1912 @arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
1913 @arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
1914 @arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
1915 @arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
1916 @arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
1917 @arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
1918 from t9 where c1= 1 ;
1919 @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
1920 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
1921 execute full_info ;
1922 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1923 def @arg01 8 20 1 Y 32896 0 63
1924 def @arg02 8 20 1 Y 32896 0 63
1925 def @arg03 8 20 1 Y 32896 0 63
1926 def @arg04 8 20 1 Y 32896 0 63
1927 def @arg05 8 20 1 Y 32896 0 63
1928 def @arg06 8 20 1 Y 32896 0 63
1929 def @arg07 5 23 1 Y 32896 31 63
1930 def @arg08 5 23 1 Y 32896 31 63
1931 def @arg09 5 23 1 Y 32896 31 63
1932 def @arg10 5 23 1 Y 32896 31 63
1933 def @arg11 246 83 6 Y 32896 38 63
1934 def @arg12 246 83 6 Y 32896 38 63
1935 def @arg13 251 16777215 10 Y 0 39 8
1936 def @arg14 251 16777215 19 Y 0 39 8
1937 def @arg15 251 16777215 19 Y 0 39 8
1938 def @arg16 251 16777215 8 Y 0 39 8
1939 def @arg17 8 20 4 Y 32928 0 63
1940 def @arg18 8 20 1 Y 32896 0 63
1941 def @arg19 8 20 1 Y 32896 0 63
1942 def @arg20 251 16777215 1 Y 0 39 8
1943 def @arg21 251 16777215 10 Y 0 39 8
1944 def @arg22 251 16777215 30 Y 0 39 8
1945 def @arg23 251 16777215 8 Y 128 39 63
1946 def @arg24 251 16777215 8 Y 0 39 8
1947 def @arg25 251 16777215 4 Y 128 39 63
1948 def @arg26 251 16777215 4 Y 0 39 8
1949 def @arg27 251 16777215 10 Y 128 39 63
1950 def @arg28 251 16777215 10 Y 0 39 8
1951 def @arg29 251 16777215 8 Y 128 39 63
1952 def @arg30 251 16777215 8 Y 0 39 8
1953 def @arg31 251 16777215 3 Y 0 39 8
1954 def @arg32 251 16777215 6 Y 0 39 8
1955 @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
1956 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
1957 select @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,
1958 @arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,
1959 @arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
1960 @arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
1961 @arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
1962 @arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
1963 @arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
1964 @arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
1965 from t9 where c1= 0 ;
1966 @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
1967 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
1968 execute full_info ;
1969 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1970 def @arg01 8 20 1 Y 32896 0 63
1971 def @arg02 8 20 0 Y 32896 0 63
1972 def @arg03 8 20 0 Y 32896 0 63
1973 def @arg04 8 20 0 Y 32896 0 63
1974 def @arg05 8 20 0 Y 32896 0 63
1975 def @arg06 8 20 0 Y 32896 0 63
1976 def @arg07 5 23 0 Y 32896 31 63
1977 def @arg08 5 23 0 Y 32896 31 63
1978 def @arg09 5 23 0 Y 32896 31 63
1979 def @arg10 5 23 0 Y 32896 31 63
1980 def @arg11 246 83 0 Y 32896 38 63
1981 def @arg12 246 83 0 Y 32896 38 63
1982 def @arg13 251 16777215 0 Y 0 39 8
1983 def @arg14 251 16777215 0 Y 0 39 8
1984 def @arg15 251 16777215 19 Y 0 39 8
1985 def @arg16 251 16777215 0 Y 0 39 8
1986 def @arg17 8 20 0 Y 32928 0 63
1987 def @arg18 8 20 0 Y 32896 0 63
1988 def @arg19 8 20 0 Y 32896 0 63
1989 def @arg20 251 16777215 0 Y 0 39 8
1990 def @arg21 251 16777215 0 Y 0 39 8
1991 def @arg22 251 16777215 0 Y 0 39 8
1992 def @arg23 251 16777215 0 Y 128 39 63
1993 def @arg24 251 16777215 0 Y 0 39 8
1994 def @arg25 251 16777215 0 Y 128 39 63
1995 def @arg26 251 16777215 0 Y 0 39 8
1996 def @arg27 251 16777215 0 Y 128 39 63
1997 def @arg28 251 16777215 0 Y 0 39 8
1998 def @arg29 251 16777215 0 Y 128 39 63
1999 def @arg30 251 16777215 0 Y 0 39 8
2000 def @arg31 251 16777215 0 Y 0 39 8
2001 def @arg32 251 16777215 0 Y 0 39 8
2002 @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
2003 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
2004 prepare stmt1 from "select
2005 @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,
2006 @arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,
2007 @arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
2008 @arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
2009 @arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
2010 @arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
2011 @arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
2012 @arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
2013 from t9 where c1= ?" ;
2014 set @my_key= 1 ;
2015 execute stmt1 using @my_key ;
2016 @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
2017 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
2018 execute full_info ;
2019 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2020 def @arg01 8 20 1 Y 32896 0 63
2021 def @arg02 8 20 1 Y 32896 0 63
2022 def @arg03 8 20 1 Y 32896 0 63
2023 def @arg04 8 20 1 Y 32896 0 63
2024 def @arg05 8 20 1 Y 32896 0 63
2025 def @arg06 8 20 1 Y 32896 0 63
2026 def @arg07 5 23 1 Y 32896 31 63
2027 def @arg08 5 23 1 Y 32896 31 63
2028 def @arg09 5 23 1 Y 32896 31 63
2029 def @arg10 5 23 1 Y 32896 31 63
2030 def @arg11 246 83 6 Y 32896 38 63
2031 def @arg12 246 83 6 Y 32896 38 63
2032 def @arg13 251 16777215 10 Y 0 39 8
2033 def @arg14 251 16777215 19 Y 0 39 8
2034 def @arg15 251 16777215 19 Y 0 39 8
2035 def @arg16 251 16777215 8 Y 0 39 8
2036 def @arg17 8 20 4 Y 32928 0 63
2037 def @arg18 8 20 1 Y 32896 0 63
2038 def @arg19 8 20 1 Y 32896 0 63
2039 def @arg20 251 16777215 1 Y 0 39 8
2040 def @arg21 251 16777215 10 Y 0 39 8
2041 def @arg22 251 16777215 30 Y 0 39 8
2042 def @arg23 251 16777215 8 Y 128 39 63
2043 def @arg24 251 16777215 8 Y 0 39 8
2044 def @arg25 251 16777215 4 Y 128 39 63
2045 def @arg26 251 16777215 4 Y 0 39 8
2046 def @arg27 251 16777215 10 Y 128 39 63
2047 def @arg28 251 16777215 10 Y 0 39 8
2048 def @arg29 251 16777215 8 Y 128 39 63
2049 def @arg30 251 16777215 8 Y 0 39 8
2050 def @arg31 251 16777215 3 Y 0 39 8
2051 def @arg32 251 16777215 6 Y 0 39 8
2052 @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
2053 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
2054 set @my_key= 0 ;
2055 execute stmt1 using @my_key ;
2056 @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
2057 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
2058 execute full_info ;
2059 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2060 def @arg01 8 20 1 Y 32896 0 63
2061 def @arg02 8 20 0 Y 32896 0 63
2062 def @arg03 8 20 0 Y 32896 0 63
2063 def @arg04 8 20 0 Y 32896 0 63
2064 def @arg05 8 20 0 Y 32896 0 63
2065 def @arg06 8 20 0 Y 32896 0 63
2066 def @arg07 5 23 0 Y 32896 31 63
2067 def @arg08 5 23 0 Y 32896 31 63
2068 def @arg09 5 23 0 Y 32896 31 63
2069 def @arg10 5 23 0 Y 32896 31 63
2070 def @arg11 246 83 0 Y 32896 38 63
2071 def @arg12 246 83 0 Y 32896 38 63
2072 def @arg13 251 16777215 0 Y 0 39 8
2073 def @arg14 251 16777215 0 Y 0 39 8
2074 def @arg15 251 16777215 19 Y 0 39 8
2075 def @arg16 251 16777215 0 Y 0 39 8
2076 def @arg17 8 20 0 Y 32928 0 63
2077 def @arg18 8 20 0 Y 32896 0 63
2078 def @arg19 8 20 0 Y 32896 0 63
2079 def @arg20 251 16777215 0 Y 0 39 8
2080 def @arg21 251 16777215 0 Y 0 39 8
2081 def @arg22 251 16777215 0 Y 0 39 8
2082 def @arg23 251 16777215 0 Y 128 39 63
2083 def @arg24 251 16777215 0 Y 0 39 8
2084 def @arg25 251 16777215 0 Y 128 39 63
2085 def @arg26 251 16777215 0 Y 0 39 8
2086 def @arg27 251 16777215 0 Y 128 39 63
2087 def @arg28 251 16777215 0 Y 0 39 8
2088 def @arg29 251 16777215 0 Y 128 39 63
2089 def @arg30 251 16777215 0 Y 0 39 8
2090 def @arg31 251 16777215 0 Y 0 39 8
2091 def @arg32 251 16777215 0 Y 0 39 8
2092 @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
2093 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
2094 prepare stmt1 from "select ? := c1 from t9 where c1= 1" ;
2095 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':= c1 from t9 where c1= 1' at line 1
2096 test_sequence
2097 ------ select column, .. into @parm,.. ------
2098 select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
2099 c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
2100 c25, c26, c27, c28, c29, c30, c31, c32
2101 into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
2102 @arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
2103 @arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
2104 @arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
2105 from t9 where c1= 1 ;
2106 execute full_info ;
2107 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2108 def @arg01 8 20 1 Y 32896 0 63
2109 def @arg02 8 20 1 Y 32896 0 63
2110 def @arg03 8 20 1 Y 32896 0 63
2111 def @arg04 8 20 1 Y 32896 0 63
2112 def @arg05 8 20 1 Y 32896 0 63
2113 def @arg06 8 20 1 Y 32896 0 63
2114 def @arg07 5 23 1 Y 32896 31 63
2115 def @arg08 5 23 1 Y 32896 31 63
2116 def @arg09 5 23 1 Y 32896 31 63
2117 def @arg10 5 23 1 Y 32896 31 63
2118 def @arg11 246 83 6 Y 32896 38 63
2119 def @arg12 246 83 6 Y 32896 38 63
2120 def @arg13 251 16777215 10 Y 0 39 8
2121 def @arg14 251 16777215 19 Y 0 39 8
2122 def @arg15 251 16777215 19 Y 0 39 8
2123 def @arg16 251 16777215 8 Y 0 39 8
2124 def @arg17 8 20 4 Y 32928 0 63
2125 def @arg18 8 20 1 Y 32896 0 63
2126 def @arg19 8 20 1 Y 32896 0 63
2127 def @arg20 251 16777215 1 Y 0 39 8
2128 def @arg21 251 16777215 10 Y 0 39 8
2129 def @arg22 251 16777215 30 Y 0 39 8
2130 def @arg23 251 16777215 8 Y 128 39 63
2131 def @arg24 251 16777215 8 Y 0 39 8
2132 def @arg25 251 16777215 4 Y 128 39 63
2133 def @arg26 251 16777215 4 Y 0 39 8
2134 def @arg27 251 16777215 10 Y 128 39 63
2135 def @arg28 251 16777215 10 Y 0 39 8
2136 def @arg29 251 16777215 8 Y 128 39 63
2137 def @arg30 251 16777215 8 Y 0 39 8
2138 def @arg31 251 16777215 3 Y 0 39 8
2139 def @arg32 251 16777215 6 Y 0 39 8
2140 @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
2141 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
2142 select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
2143 c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
2144 c25, c26, c27, c28, c29, c30, c31, c32
2145 into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
2146 @arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
2147 @arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
2148 @arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
2149 from t9 where c1= 0 ;
2150 execute full_info ;
2151 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2152 def @arg01 8 20 1 Y 32896 0 63
2153 def @arg02 8 20 0 Y 32896 0 63
2154 def @arg03 8 20 0 Y 32896 0 63
2155 def @arg04 8 20 0 Y 32896 0 63
2156 def @arg05 8 20 0 Y 32896 0 63
2157 def @arg06 8 20 0 Y 32896 0 63
2158 def @arg07 5 23 0 Y 32896 31 63
2159 def @arg08 5 23 0 Y 32896 31 63
2160 def @arg09 5 23 0 Y 32896 31 63
2161 def @arg10 5 23 0 Y 32896 31 63
2162 def @arg11 246 83 0 Y 32896 38 63
2163 def @arg12 246 83 0 Y 32896 38 63
2164 def @arg13 251 16777215 0 Y 0 39 8
2165 def @arg14 251 16777215 0 Y 0 39 8
2166 def @arg15 251 16777215 19 Y 0 39 8
2167 def @arg16 251 16777215 0 Y 0 39 8
2168 def @arg17 8 20 0 Y 32928 0 63
2169 def @arg18 8 20 0 Y 32896 0 63
2170 def @arg19 8 20 0 Y 32896 0 63
2171 def @arg20 251 16777215 0 Y 0 39 8
2172 def @arg21 251 16777215 0 Y 0 39 8
2173 def @arg22 251 16777215 0 Y 0 39 8
2174 def @arg23 251 16777215 0 Y 128 39 63
2175 def @arg24 251 16777215 0 Y 0 39 8
2176 def @arg25 251 16777215 0 Y 128 39 63
2177 def @arg26 251 16777215 0 Y 0 39 8
2178 def @arg27 251 16777215 0 Y 128 39 63
2179 def @arg28 251 16777215 0 Y 0 39 8
2180 def @arg29 251 16777215 0 Y 128 39 63
2181 def @arg30 251 16777215 0 Y 0 39 8
2182 def @arg31 251 16777215 0 Y 0 39 8
2183 def @arg32 251 16777215 0 Y 0 39 8
2184 @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
2185 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
2186 prepare stmt1 from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
2187 c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
2188 c25, c26, c27, c28, c29, c30, c31, c32
2189 into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
2190 @arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
2191 @arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
2192 @arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
2193 from t9 where c1= ?" ;
2194 set @my_key= 1 ;
2195 execute stmt1 using @my_key ;
2196 execute full_info ;
2197 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2198 def @arg01 8 20 1 Y 32896 0 63
2199 def @arg02 8 20 1 Y 32896 0 63
2200 def @arg03 8 20 1 Y 32896 0 63
2201 def @arg04 8 20 1 Y 32896 0 63
2202 def @arg05 8 20 1 Y 32896 0 63
2203 def @arg06 8 20 1 Y 32896 0 63
2204 def @arg07 5 23 1 Y 32896 31 63
2205 def @arg08 5 23 1 Y 32896 31 63
2206 def @arg09 5 23 1 Y 32896 31 63
2207 def @arg10 5 23 1 Y 32896 31 63
2208 def @arg11 246 83 6 Y 32896 38 63
2209 def @arg12 246 83 6 Y 32896 38 63
2210 def @arg13 251 16777215 10 Y 0 39 8
2211 def @arg14 251 16777215 19 Y 0 39 8
2212 def @arg15 251 16777215 19 Y 0 39 8
2213 def @arg16 251 16777215 8 Y 0 39 8
2214 def @arg17 8 20 4 Y 32928 0 63
2215 def @arg18 8 20 1 Y 32896 0 63
2216 def @arg19 8 20 1 Y 32896 0 63
2217 def @arg20 251 16777215 1 Y 0 39 8
2218 def @arg21 251 16777215 10 Y 0 39 8
2219 def @arg22 251 16777215 30 Y 0 39 8
2220 def @arg23 251 16777215 8 Y 128 39 63
2221 def @arg24 251 16777215 8 Y 0 39 8
2222 def @arg25 251 16777215 4 Y 128 39 63
2223 def @arg26 251 16777215 4 Y 0 39 8
2224 def @arg27 251 16777215 10 Y 128 39 63
2225 def @arg28 251 16777215 10 Y 0 39 8
2226 def @arg29 251 16777215 8 Y 128 39 63
2227 def @arg30 251 16777215 8 Y 0 39 8
2228 def @arg31 251 16777215 3 Y 0 39 8
2229 def @arg32 251 16777215 6 Y 0 39 8
2230 @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
2231 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
2232 set @my_key= 0 ;
2233 execute stmt1 using @my_key ;
2234 execute full_info ;
2235 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2236 def @arg01 8 20 1 Y 32896 0 63
2237 def @arg02 8 20 0 Y 32896 0 63
2238 def @arg03 8 20 0 Y 32896 0 63
2239 def @arg04 8 20 0 Y 32896 0 63
2240 def @arg05 8 20 0 Y 32896 0 63
2241 def @arg06 8 20 0 Y 32896 0 63
2242 def @arg07 5 23 0 Y 32896 31 63
2243 def @arg08 5 23 0 Y 32896 31 63
2244 def @arg09 5 23 0 Y 32896 31 63
2245 def @arg10 5 23 0 Y 32896 31 63
2246 def @arg11 246 83 0 Y 32896 38 63
2247 def @arg12 246 83 0 Y 32896 38 63
2248 def @arg13 251 16777215 0 Y 0 39 8
2249 def @arg14 251 16777215 0 Y 0 39 8
2250 def @arg15 251 16777215 19 Y 0 39 8
2251 def @arg16 251 16777215 0 Y 0 39 8
2252 def @arg17 8 20 0 Y 32928 0 63
2253 def @arg18 8 20 0 Y 32896 0 63
2254 def @arg19 8 20 0 Y 32896 0 63
2255 def @arg20 251 16777215 0 Y 0 39 8
2256 def @arg21 251 16777215 0 Y 0 39 8
2257 def @arg22 251 16777215 0 Y 0 39 8
2258 def @arg23 251 16777215 0 Y 128 39 63
2259 def @arg24 251 16777215 0 Y 0 39 8
2260 def @arg25 251 16777215 0 Y 128 39 63
2261 def @arg26 251 16777215 0 Y 0 39 8
2262 def @arg27 251 16777215 0 Y 128 39 63
2263 def @arg28 251 16777215 0 Y 0 39 8
2264 def @arg29 251 16777215 0 Y 128 39 63
2265 def @arg30 251 16777215 0 Y 0 39 8
2266 def @arg31 251 16777215 0 Y 0 39 8
2267 def @arg32 251 16777215 0 Y 0 39 8
2268 @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
2269 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
2270 prepare stmt1 from "select c1 into ? from t9 where c1= 1" ;
2271 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? from t9 where c1= 1' at line 1
2272 test_sequence
2273 -- insert into numeric columns --
2274 insert into t9
2275 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2276 values
2277 ( 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20 ) ;
2278 set @arg00= 21 ;
2279 insert into t9
2280 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2281 values
2282 ( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2283 @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2284 prepare stmt1 from "insert into t9
2285 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2286 values
2287 ( 22, 22, 22, 22, 22, 22, 22, 22, 22, 22, 22 )" ;
2288 execute stmt1 ;
2289 set @arg00= 23;
2290 prepare stmt2 from "insert into t9
2291 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2292 values
2293 ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2294 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2295 @arg00, @arg00, @arg00, @arg00 ;
2296 insert into t9
2297 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2298 values
2299 ( 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0,
2300 30.0, 30.0, 30.0 ) ;
2301 set @arg00= 31.0 ;
2302 insert into t9
2303 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2304 values
2305 ( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2306 @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2307 prepare stmt1 from "insert into t9
2308 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2309 values
2310 ( 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0,
2311 32.0, 32.0, 32.0 )" ;
2312 execute stmt1 ;
2313 set @arg00= 33.0;
2314 prepare stmt2 from "insert into t9
2315 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2316 values
2317 ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2318 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2319 @arg00, @arg00, @arg00, @arg00 ;
2320 insert into t9
2321 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2322 values
2323 ( '40', '40', '40', '40', '40', '40', '40', '40',
2324 '40', '40', '40' ) ;
2325 set @arg00= '41' ;
2326 insert into t9
2327 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2328 values
2329 ( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2330 @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2331 prepare stmt1 from "insert into t9
2332 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2333 values
2334 ( '42', '42', '42', '42', '42', '42', '42', '42',
2335 '42', '42', '42' )" ;
2336 execute stmt1 ;
2337 set @arg00= '43';
2338 prepare stmt2 from "insert into t9
2339 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2340 values
2341 ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2342 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2343 @arg00, @arg00, @arg00, @arg00 ;
2344 insert into t9
2345 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2346 values
2347 ( CAST('50' as binary), CAST('50' as binary),
2348 CAST('50' as binary), CAST('50' as binary), CAST('50' as binary),
2349 CAST('50' as binary), CAST('50' as binary), CAST('50' as binary),
2350 CAST('50' as binary), CAST('50' as binary), CAST('50' as binary) ) ;
2351 set @arg00= CAST('51' as binary) ;
2352 insert into t9
2353 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2354 values
2355 ( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2356 @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2357 prepare stmt1 from "insert into t9
2358 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2359 values
2360 ( CAST('52' as binary), CAST('52' as binary),
2361 CAST('52' as binary), CAST('52' as binary), CAST('52' as binary),
2362 CAST('52' as binary), CAST('52' as binary), CAST('52' as binary),
2363 CAST('52' as binary), CAST('52' as binary), CAST('52' as binary) )" ;
2364 execute stmt1 ;
2365 set @arg00= CAST('53' as binary) ;
2366 prepare stmt2 from "insert into t9
2367 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2368 values
2369 ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2370 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2371 @arg00, @arg00, @arg00, @arg00 ;
2372 set @arg00= 2 ;
2373 set @arg00= NULL ;
2374 insert into t9
2375 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2376 values
2377 ( 60, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2378 NULL, NULL, NULL ) ;
2379 insert into t9
2380 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2381 values
2382 ( 61, @arg00, @arg00, @arg00, @arg00, @arg00,
2383 @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2384 prepare stmt1 from "insert into t9
2385 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2386 values
2387 ( 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2388 NULL, NULL, NULL )" ;
2389 execute stmt1 ;
2390 prepare stmt2 from "insert into t9
2391 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2392 values
2393 ( 63, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2394 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2395 @arg00, @arg00, @arg00, @arg00 ;
2396 set @arg00= 8.0 ;
2397 set @arg00= NULL ;
2398 insert into t9
2399 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2400 values
2401 ( 71, @arg00, @arg00, @arg00, @arg00, @arg00,
2402 @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2403 prepare stmt2 from "insert into t9
2404 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2405 values
2406 ( 73, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2407 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2408 @arg00, @arg00, @arg00, @arg00 ;
2409 set @arg00= 'abc' ;
2410 set @arg00= NULL ;
2411 insert into t9
2412 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2413 values
2414 ( 81, @arg00, @arg00, @arg00, @arg00, @arg00,
2415 @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
2416 prepare stmt2 from "insert into t9
2417 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2418 values
2419 ( 83, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2420 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2421 @arg00, @arg00, @arg00, @arg00 ;
2422 select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12
2423 from t9 where c1 >= 20
2424 order by c1 ;
2425 c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c12
2426 20 20 20 20 20 20 20 20 20 20 20.0000
2427 21 21 21 21 21 21 21 21 21 21 21.0000
2428 22 22 22 22 22 22 22 22 22 22 22.0000
2429 23 23 23 23 23 23 23 23 23 23 23.0000
2430 30 30 30 30 30 30 30 30 30 30 30.0000
2431 31 31 31 31 31 31 31 31 31 31 31.0000
2432 32 32 32 32 32 32 32 32 32 32 32.0000
2433 33 33 33 33 33 33 33 33 33 33 33.0000
2434 40 40 40 40 40 40 40 40 40 40 40.0000
2435 41 41 41 41 41 41 41 41 41 41 41.0000
2436 42 42 42 42 42 42 42 42 42 42 42.0000
2437 43 43 43 43 43 43 43 43 43 43 43.0000
2438 50 50 50 50 50 50 50 50 50 50 50.0000
2439 51 51 51 51 51 51 51 51 51 51 51.0000
2440 52 52 52 52 52 52 52 52 52 52 52.0000
2441 53 53 53 53 53 53 53 53 53 53 53.0000
2442 60 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2443 61 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2444 62 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2445 63 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2446 71 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2447 73 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2448 81 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2449 83 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2450 test_sequence
2451 -- select .. where numeric column = .. --
2452 set @arg00= 20;
2453 select 'true' as found from t9
2454 where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20
2455 and c8= 20 and c9= 20 and c10= 20 and c12= 20;
2456 found
2457 true
2458 select 'true' as found from t9
2459 where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
2460 and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
2461 and c12= @arg00;
2462 found
2463 true
2464 prepare stmt1 from "select 'true' as found from t9
2465 where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20
2466 and c8= 20 and c9= 20 and c10= 20 and c12= 20 ";
2467 execute stmt1 ;
2468 found
2469 true
2470 prepare stmt1 from "select 'true' as found from t9
2471 where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
2472 and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
2473 and c12= ? ";
2474 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2475 @arg00, @arg00, @arg00, @arg00 ;
2476 found
2477 true
2478 set @arg00= 20.0;
2479 select 'true' as found from t9
2480 where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0
2481 and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0;
2482 found
2483 true
2484 select 'true' as found from t9
2485 where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
2486 and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
2487 and c12= @arg00;
2488 found
2489 true
2490 prepare stmt1 from "select 'true' as found from t9
2491 where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0
2492 and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0 ";
2493 execute stmt1 ;
2494 found
2495 true
2496 prepare stmt1 from "select 'true' as found from t9
2497 where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
2498 and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
2499 and c12= ? ";
2500 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2501 @arg00, @arg00, @arg00, @arg00 ;
2502 found
2503 true
2504 select 'true' as found from t9
2505 where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'
2506 and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20';
2507 found
2508 true
2509 prepare stmt1 from "select 'true' as found from t9
2510 where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'
2511 and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20' ";
2512 execute stmt1 ;
2513 found
2514 true
2515 set @arg00= '20';
2516 select 'true' as found from t9
2517 where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
2518 and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
2519 and c12= @arg00;
2520 found
2521 true
2522 prepare stmt1 from "select 'true' as found from t9
2523 where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
2524 and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
2525 and c12= ? ";
2526 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2527 @arg00, @arg00, @arg00, @arg00 ;
2528 found
2529 true
2530 select 'true' as found from t9
2531 where c1= CAST('20' as binary) and c2= CAST('20' as binary) and
2532 c3= CAST('20' as binary) and c4= CAST('20' as binary) and
2533 c5= CAST('20' as binary) and c6= CAST('20' as binary) and
2534 c7= CAST('20' as binary) and c8= CAST('20' as binary) and
2535 c9= CAST('20' as binary) and c10= CAST('20' as binary) and
2536 c12= CAST('20' as binary);
2537 found
2538 true
2539 prepare stmt1 from "select 'true' as found from t9
2540 where c1= CAST('20' as binary) and c2= CAST('20' as binary) and
2541 c3= CAST('20' as binary) and c4= CAST('20' as binary) and
2542 c5= CAST('20' as binary) and c6= CAST('20' as binary) and
2543 c7= CAST('20' as binary) and c8= CAST('20' as binary) and
2544 c9= CAST('20' as binary) and c10= CAST('20' as binary) and
2545 c12= CAST('20' as binary) ";
2546 execute stmt1 ;
2547 found
2548 true
2549 set @arg00= CAST('20' as binary) ;
2550 select 'true' as found from t9
2551 where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
2552 and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
2553 and c12= @arg00;
2554 found
2555 true
2556 prepare stmt1 from "select 'true' as found from t9
2557 where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
2558 and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
2559 and c12= ? ";
2560 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2561 @arg00, @arg00, @arg00, @arg00 ;
2562 found
2563 true
2564 delete from t9 ;
2565 test_sequence
2566 -- some numeric overflow experiments --
2567 prepare my_insert from "insert into t9
2568 ( c21, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
2569 values
2570 ( 'O', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2571 prepare my_select from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12
2572 from t9 where c21 = 'O' ";
2573 prepare my_delete from "delete from t9 where c21 = 'O' ";
2574 set @arg00= 9223372036854775807 ;
2575 set statement sql_mode = '' for
2576 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2577 @arg00, @arg00, @arg00, @arg00, @arg00 ;
2578 Warnings:
2579 Warning 1264 Out of range value for column 'c1' at row 1
2580 Warning 1264 Out of range value for column 'c2' at row 1
2581 Warning 1264 Out of range value for column 'c3' at row 1
2582 Warning 1264 Out of range value for column 'c4' at row 1
2583 Warning 1264 Out of range value for column 'c5' at row 1
2584 Warning 1264 Out of range value for column 'c12' at row 1
2585 execute my_select ;
2586 c1 127
2587 c2 32767
2588 c3 8388607
2589 c4 2147483647
2590 c5 2147483647
2591 c6 9223372036854775807
2592 c7 9.22337e18
2593 c8 9.223372036854776e18
2594 c9 9.223372036854776e18
2595 c10 9.223372036854776e18
2596 c12 9999.9999
2597 execute my_delete ;
2598 set @arg00= '9223372036854775807' ;
2599 set statement sql_mode = '' for
2600 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2601 @arg00, @arg00, @arg00, @arg00, @arg00 ;
2602 Warnings:
2603 Warning 1264 Out of range value for column 'c1' at row 1
2604 Warning 1264 Out of range value for column 'c2' at row 1
2605 Warning 1264 Out of range value for column 'c3' at row 1
2606 Warning 1264 Out of range value for column 'c4' at row 1
2607 Warning 1264 Out of range value for column 'c5' at row 1
2608 Warning 1264 Out of range value for column 'c12' at row 1
2609 execute my_select ;
2610 c1 127
2611 c2 32767
2612 c3 8388607
2613 c4 2147483647
2614 c5 2147483647
2615 c6 9223372036854775807
2616 c7 9.22337e18
2617 c8 9.223372036854776e18
2618 c9 9.223372036854776e18
2619 c10 9.223372036854776e18
2620 c12 9999.9999
2621 execute my_delete ;
2622 set @arg00= -9223372036854775808 ;
2623 set statement sql_mode = '' for
2624 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2625 @arg00, @arg00, @arg00, @arg00, @arg00 ;
2626 Warnings:
2627 Warning 1264 Out of range value for column 'c1' at row 1
2628 Warning 1264 Out of range value for column 'c2' at row 1
2629 Warning 1264 Out of range value for column 'c3' at row 1
2630 Warning 1264 Out of range value for column 'c4' at row 1
2631 Warning 1264 Out of range value for column 'c5' at row 1
2632 Warning 1264 Out of range value for column 'c12' at row 1
2633 execute my_select ;
2634 c1 -128
2635 c2 -32768
2636 c3 -8388608
2637 c4 -2147483648
2638 c5 -2147483648
2639 c6 -9223372036854775808
2640 c7 -9.22337e18
2641 c8 -9.223372036854776e18
2642 c9 -9.223372036854776e18
2643 c10 -9.223372036854776e18
2644 c12 -9999.9999
2645 execute my_delete ;
2646 set @arg00= '-9223372036854775808' ;
2647 set statement sql_mode = '' for
2648 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2649 @arg00, @arg00, @arg00, @arg00, @arg00 ;
2650 Warnings:
2651 Warning 1264 Out of range value for column 'c1' at row 1
2652 Warning 1264 Out of range value for column 'c2' at row 1
2653 Warning 1264 Out of range value for column 'c3' at row 1
2654 Warning 1264 Out of range value for column 'c4' at row 1
2655 Warning 1264 Out of range value for column 'c5' at row 1
2656 Warning 1264 Out of range value for column 'c12' at row 1
2657 execute my_select ;
2658 c1 -128
2659 c2 -32768
2660 c3 -8388608
2661 c4 -2147483648
2662 c5 -2147483648
2663 c6 -9223372036854775808
2664 c7 -9.22337e18
2665 c8 -9.223372036854776e18
2666 c9 -9.223372036854776e18
2667 c10 -9.223372036854776e18
2668 c12 -9999.9999
2669 execute my_delete ;
2670 set @arg00= 1.11111111111111111111e+50 ;
2671 set statement sql_mode = '' for
2672 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2673 @arg00, @arg00, @arg00, @arg00, @arg00 ;
2674 Warnings:
2675 Warning 1264 Out of range value for column 'c1' at row 1
2676 Warning 1264 Out of range value for column 'c2' at row 1
2677 Warning 1264 Out of range value for column 'c3' at row 1
2678 Warning 1264 Out of range value for column 'c4' at row 1
2679 Warning 1264 Out of range value for column 'c5' at row 1
2680 Warning 1264 Out of range value for column 'c6' at row 1
2681 Warning 1264 Out of range value for column 'c7' at row 1
2682 Warning 1264 Out of range value for column 'c12' at row 1
2683 execute my_select ;
2684 c1 127
2685 c2 32767
2686 c3 8388607
2687 c4 2147483647
2688 c5 2147483647
2689 c6 9223372036854775807
2690 c7 3.40282e38
2691 c8 1.111111111111111e50
2692 c9 1.111111111111111e50
2693 c10 1.111111111111111e50
2694 c12 9999.9999
2695 execute my_delete ;
2696 set @arg00= '1.11111111111111111111e+50' ;
2697 set statement sql_mode = '' for
2698 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2699 @arg00, @arg00, @arg00, @arg00, @arg00 ;
2700 Warnings:
2701 Warning 1264 Out of range value for column 'c1' at row 1
2702 Warning 1264 Out of range value for column 'c2' at row 1
2703 Warning 1264 Out of range value for column 'c3' at row 1
2704 Warning 1264 Out of range value for column 'c4' at row 1
2705 Warning 1264 Out of range value for column 'c5' at row 1
2706 Warning 1264 Out of range value for column 'c6' at row 1
2707 Warning 1264 Out of range value for column 'c7' at row 1
2708 Warning 1264 Out of range value for column 'c12' at row 1
2709 execute my_select ;
2710 c1 127
2711 c2 32767
2712 c3 8388607
2713 c4 2147483647
2714 c5 2147483647
2715 c6 9223372036854775807
2716 c7 3.40282e38
2717 c8 1.111111111111111e50
2718 c9 1.111111111111111e50
2719 c10 1.111111111111111e50
2720 c12 9999.9999
2721 execute my_delete ;
2722 set @arg00= -1.11111111111111111111e+50 ;
2723 set statement sql_mode = '' for
2724 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2725 @arg00, @arg00, @arg00, @arg00, @arg00 ;
2726 Warnings:
2727 Warning 1264 Out of range value for column 'c1' at row 1
2728 Warning 1264 Out of range value for column 'c2' at row 1
2729 Warning 1264 Out of range value for column 'c3' at row 1
2730 Warning 1264 Out of range value for column 'c4' at row 1
2731 Warning 1264 Out of range value for column 'c5' at row 1
2732 Warning 1264 Out of range value for column 'c6' at row 1
2733 Warning 1264 Out of range value for column 'c7' at row 1
2734 Warning 1264 Out of range value for column 'c12' at row 1
2735 execute my_select ;
2736 c1 -128
2737 c2 -32768
2738 c3 -8388608
2739 c4 -2147483648
2740 c5 -2147483648
2741 c6 -9223372036854775808
2742 c7 -3.40282e38
2743 c8 -1.111111111111111e50
2744 c9 -1.111111111111111e50
2745 c10 -1.111111111111111e50
2746 c12 -9999.9999
2747 execute my_delete ;
2748 set @arg00= '-1.11111111111111111111e+50' ;
2749 set statement sql_mode = '' for
2750 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2751 @arg00, @arg00, @arg00, @arg00, @arg00 ;
2752 Warnings:
2753 Warning 1264 Out of range value for column 'c1' at row 1
2754 Warning 1264 Out of range value for column 'c2' at row 1
2755 Warning 1264 Out of range value for column 'c3' at row 1
2756 Warning 1264 Out of range value for column 'c4' at row 1
2757 Warning 1264 Out of range value for column 'c5' at row 1
2758 Warning 1264 Out of range value for column 'c6' at row 1
2759 Warning 1264 Out of range value for column 'c7' at row 1
2760 Warning 1264 Out of range value for column 'c12' at row 1
2761 execute my_select ;
2762 c1 -128
2763 c2 -32768
2764 c3 -8388608
2765 c4 -2147483648
2766 c5 -2147483648
2767 c6 -9223372036854775808
2768 c7 -3.40282e38
2769 c8 -1.111111111111111e50
2770 c9 -1.111111111111111e50
2771 c10 -1.111111111111111e50
2772 c12 -9999.9999
2773 execute my_delete ;
2774 test_sequence
2775 -- insert into string columns --
2776 set sql_mode = '';
2777 insert into t9
2778 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2779 values
2780 ( 20, '20', '20', '20', '20', '20', '20', '20', '20', '20', '20', '20' ) ;
2781 Warnings:
2782 Warning 1265 Data truncated for column 'c20' at row 1
2783 set @arg00= '21' ;
2784 insert into t9
2785 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2786 values
2787 ( 21, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2788 @arg00, @arg00, @arg00 ) ;
2789 Warnings:
2790 Warning 1265 Data truncated for column 'c20' at row 1
2791 prepare stmt1 from "insert into t9
2792 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2793 values
2794 ( 22, '22', '22', '22', '22', '22', '22', '22', '22', '22', '22', '22' )" ;
2795 execute stmt1 ;
2796 Warnings:
2797 Warning 1265 Data truncated for column 'c20' at row 1
2798 set @arg00= '23';
2799 prepare stmt2 from "insert into t9
2800 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2801 values
2802 ( 23, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2803 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2804 @arg00, @arg00, @arg00, @arg00 ;
2805 Warnings:
2806 Warning 1265 Data truncated for column 'c20' at row 1
2807 insert into t9
2808 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2809 values
2810 ( 30, CAST('30' as binary), CAST('30' as binary), CAST('30' as binary),
2811 CAST('30' as binary), CAST('30' as binary), CAST('30' as binary),
2812 CAST('30' as binary), CAST('30' as binary), CAST('30' as binary),
2813 CAST('30' as binary), CAST('30' as binary) ) ;
2814 Warnings:
2815 Warning 1265 Data truncated for column 'c20' at row 1
2816 set @arg00= '31' ;
2817 insert into t9
2818 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2819 values
2820 ( 31, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2821 @arg00, @arg00, @arg00 ) ;
2822 Warnings:
2823 Warning 1265 Data truncated for column 'c20' at row 1
2824 prepare stmt1 from "insert into t9
2825 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2826 values
2827 ( 32, CAST('32' as binary), CAST('32' as binary), CAST('32' as binary),
2828 CAST('32' as binary), CAST('32' as binary), CAST('32' as binary),
2829 CAST('32' as binary), CAST('32' as binary), CAST('32' as binary),
2830 CAST('32' as binary), CAST('32' as binary) )" ;
2831 execute stmt1 ;
2832 Warnings:
2833 Warning 1265 Data truncated for column 'c20' at row 1
2834 set @arg00= CAST('33' as binary);
2835 prepare stmt2 from "insert into t9
2836 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2837 values
2838 ( 33, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2839 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2840 @arg00, @arg00, @arg00, @arg00 ;
2841 Warnings:
2842 Warning 1265 Data truncated for column 'c20' at row 1
2843 insert into t9
2844 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2845 values
2846 ( 40, 40, 40, 40, 40, 40, 40, 40, 40, 40, 40, 40 ) ;
2847 Warnings:
2848 Warning 1265 Data truncated for column 'c20' at row 1
2849 set @arg00= 41 ;
2850 insert into t9
2851 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2852 values
2853 ( 41, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2854 @arg00, @arg00, @arg00 ) ;
2855 Warnings:
2856 Warning 1265 Data truncated for column 'c20' at row 1
2857 prepare stmt1 from "insert into t9
2858 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2859 values
2860 ( 42, 42, 42, 42, 42, 42, 42, 42, 42, 42, 42, 42 )" ;
2861 execute stmt1 ;
2862 Warnings:
2863 Warning 1265 Data truncated for column 'c20' at row 1
2864 set @arg00= 43;
2865 prepare stmt2 from "insert into t9
2866 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2867 values
2868 ( 43, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2869 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2870 @arg00, @arg00, @arg00, @arg00 ;
2871 Warnings:
2872 Warning 1265 Data truncated for column 'c20' at row 1
2873 insert into t9
2874 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2875 values
2876 ( 50, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0 ) ;
2877 Warnings:
2878 Warning 1265 Data truncated for column 'c20' at row 1
2879 set @arg00= 51.0 ;
2880 insert into t9
2881 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2882 values
2883 ( 51, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2884 @arg00, @arg00, @arg00 ) ;
2885 Warnings:
2886 Warning 1265 Data truncated for column 'c20' at row 1
2887 prepare stmt1 from "insert into t9
2888 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2889 values
2890 ( 52, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0 )" ;
2891 execute stmt1 ;
2892 Warnings:
2893 Warning 1265 Data truncated for column 'c20' at row 1
2894 set @arg00= 53.0;
2895 prepare stmt2 from "insert into t9
2896 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2897 values
2898 ( 53, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2899 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2900 @arg00, @arg00, @arg00, @arg00 ;
2901 Warnings:
2902 Warning 1265 Data truncated for column 'c20' at row 1
2903 insert into t9
2904 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2905 values
2906 ( 54, 5.4e+1, 5.4e+1, 5.4e+1, 5.4e+1, 5.4e+1, 5.4e+1, 5.4e+1, 5.4e+1,
2907 5.4e+1, 5.4e+1, 5.4e+1 ) ;
2908 Warnings:
2909 Warning 1265 Data truncated for column 'c20' at row 1
2910 set @arg00= 5.5e+1 ;
2911 insert into t9
2912 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2913 values
2914 ( 55, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2915 @arg00, @arg00, @arg00 ) ;
2916 Warnings:
2917 Warning 1265 Data truncated for column 'c20' at row 1
2918 prepare stmt1 from "insert into t9
2919 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2920 values
2921 ( 56, 5.6e+1, 5.6e+1, 5.6e+1, 5.6e+1, 5.6e+1, 5.6e+1, 5.6e+1, 5.6e+1,
2922 5.6e+1, 5.6e+1, 5.6e+1 )" ;
2923 execute stmt1 ;
2924 Warnings:
2925 Warning 1265 Data truncated for column 'c20' at row 1
2926 set @arg00= 5.7e+1;
2927 prepare stmt2 from "insert into t9
2928 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2929 values
2930 ( 57, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2931 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2932 @arg00, @arg00, @arg00, @arg00 ;
2933 Warnings:
2934 Warning 1265 Data truncated for column 'c20' at row 1
2935 set sql_mode = default;
2936 set @arg00= 'abc' ;
2937 set @arg00= NULL ;
2938 insert into t9
2939 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2940 values
2941 ( 60, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ) ;
2942 insert into t9
2943 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2944 values
2945 ( 61, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2946 @arg00, @arg00, @arg00 ) ;
2947 prepare stmt1 from "insert into t9
2948 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2949 values
2950 ( 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )" ;
2951 execute stmt1 ;
2952 prepare stmt2 from "insert into t9
2953 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2954 values
2955 ( 63, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2956 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2957 @arg00, @arg00, @arg00, @arg00 ;
2958 set @arg00= 2 ;
2959 set @arg00= NULL ;
2960 insert into t9
2961 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2962 values
2963 ( 71, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2964 @arg00, @arg00, @arg00 ) ;
2965 prepare stmt2 from "insert into t9
2966 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2967 values
2968 ( 73, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2969 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2970 @arg00, @arg00, @arg00, @arg00 ;
2971 set @arg00= 8 ;
2972 set @arg00= NULL ;
2973 insert into t9
2974 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2975 values
2976 ( 81, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2977 @arg00, @arg00, @arg00 ) ;
2978 prepare stmt2 from "insert into t9
2979 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
2980 values
2981 ( 83, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
2982 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
2983 @arg00, @arg00, @arg00, @arg00 ;
2984 select c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30
2985 from t9 where c1 >= 20
2986 order by c1 ;
2987 c1 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30
2988 20 2 20 20 20 20 20 20 20 20 20 20
2989 21 2 21 21 21 21 21 21 21 21 21 21
2990 22 2 22 22 22 22 22 22 22 22 22 22
2991 23 2 23 23 23 23 23 23 23 23 23 23
2992 30 3 30 30 30 30 30 30 30 30 30 30
2993 31 3 31 31 31 31 31 31 31 31 31 31
2994 32 3 32 32 32 32 32 32 32 32 32 32
2995 33 3 33 33 33 33 33 33 33 33 33 33
2996 40 4 40 40 40 40 40 40 40 40 40 40
2997 41 4 41 41 41 41 41 41 41 41 41 41
2998 42 4 42 42 42 42 42 42 42 42 42 42
2999 43 4 43 43 43 43 43 43 43 43 43 43
3000 50 5 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0
3001 51 5 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0
3002 52 5 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0
3003 53 5 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0
3004 54 5 54 54 54 54 54 54 54 54 54 54
3005 55 6 55 55 55 55 55 55 55 55 55 55
3006 56 6 56 56 56 56 56 56 56 56 56 56
3007 57 6 57 57 57 57 57 57 57 57 57 57
3008 60 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
3009 61 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
3010 62 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
3011 63 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
3012 71 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
3013 73 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
3014 81 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
3015 83 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
3016 test_sequence
3017 -- select .. where string column = .. --
3018 set @arg00= '20';
3019 select 'true' as found from t9
3020 where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and
3021 c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and
3022 c27= '20' and c28= '20' and c29= '20' and c30= '20' ;
3023 found
3024 true
3025 select 'true' as found from t9
3026 where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
3027 c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
3028 c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
3029 found
3030 true
3031 prepare stmt1 from "select 'true' as found from t9
3032 where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and
3033 c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and
3034 c27= '20' and c28= '20' and c29= '20' and c30= '20'" ;
3035 execute stmt1 ;
3036 found
3037 true
3038 prepare stmt1 from "select 'true' as found from t9
3039 where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
3040 c21= ? and c22= ? and c23= ? and c25= ? and
3041 c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
3042 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
3043 @arg00, @arg00, @arg00, @arg00, @arg00 ;
3044 found
3045 true
3046 set @arg00= CAST('20' as binary);
3047 select 'true' as found from t9
3048 where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))
3049 = CAST('20' as binary) and c21= CAST('20' as binary)
3050 and c22= CAST('20' as binary) and c23= CAST('20' as binary) and
3051 c24= CAST('20' as binary) and c25= CAST('20' as binary) and
3052 c26= CAST('20' as binary) and c27= CAST('20' as binary) and
3053 c28= CAST('20' as binary) and c29= CAST('20' as binary) and
3054 c30= CAST('20' as binary) ;
3055 found
3056 true
3057 select 'true' as found from t9
3058 where c1= 20 and concat(c20,substr(@arg00,1+length(c20))) = @arg00 and
3059 c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
3060 c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and
3061 c30= @arg00;
3062 found
3063 true
3064 prepare stmt1 from "select 'true' as found from t9
3065 where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))
3066 = CAST('20' as binary) and c21= CAST('20' as binary)
3067 and c22= CAST('20' as binary) and c23= CAST('20' as binary) and
3068 c24= CAST('20' as binary) and c25= CAST('20' as binary) and
3069 c26= CAST('20' as binary) and c27= CAST('20' as binary) and
3070 c28= CAST('20' as binary) and c29= CAST('20' as binary) and
3071 c30= CAST('20' as binary)" ;
3072 execute stmt1 ;
3073 found
3074 true
3075 prepare stmt1 from "select 'true' as found from t9
3076 where c1= 20 and concat(c20,substr(?,1+length(c20))) = ? and c21= ? and
3077 c22= ? and c23= ? and c25= ? and c26= ? and c27= ? and c28= ? and
3078 c29= ? and c30= ?";
3079 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
3080 @arg00, @arg00, @arg00, @arg00, @arg00 ;
3081 found
3082 true
3083 set @arg00= 20;
3084 select 'true' as found from t9
3085 where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and
3086 c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and
3087 c27= 20 and c28= 20 and c29= 20 and c30= 20 ;
3088 found
3089 true
3090 select 'true' as found from t9
3091 where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
3092 c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
3093 c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
3094 found
3095 true
3096 prepare stmt1 from "select 'true' as found from t9
3097 where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and
3098 c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and
3099 c27= 20 and c28= 20 and c29= 20 and c30= 20" ;
3100 execute stmt1 ;
3101 found
3102 true
3103 prepare stmt1 from "select 'true' as found from t9
3104 where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
3105 c21= ? and c22= ? and c23= ? and c25= ? and
3106 c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
3107 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
3108 @arg00, @arg00, @arg00, @arg00, @arg00 ;
3109 found
3110 true
3111 set @arg00= 20.0;
3112 select 'true' as found from t9
3113 where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and
3114 c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and
3115 c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0 ;
3116 found
3117 true
3118 select 'true' as found from t9
3119 where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
3120 c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
3121 c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
3122 found
3123 true
3124 prepare stmt1 from "select 'true' as found from t9
3125 where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and
3126 c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and
3127 c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0" ;
3128 execute stmt1 ;
3129 found
3130 true
3131 prepare stmt1 from "select 'true' as found from t9
3132 where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
3133 c21= ? and c22= ? and c23= ? and c25= ? and
3134 c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
3135 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
3136 @arg00, @arg00, @arg00, @arg00, @arg00 ;
3137 found
3138 true
3139 delete from t9 ;
3140 test_sequence
3141 -- insert into date/time columns --
3142 set sql_mode = '';
3143 set @arg00= '1991-01-01 01:01:01' ;
3144 insert into t9
3145 ( c1, c13, c14, c15, c16, c17 )
3146 values
3147 ( 20, '1991-01-01 01:01:01', '1991-01-01 01:01:01', '1991-01-01 01:01:01',
3148 '1991-01-01 01:01:01', '1991-01-01 01:01:01') ;
3149 Warnings:
3150 Note 1265 Data truncated for column 'c13' at row 1
3151 Note 1265 Data truncated for column 'c16' at row 1
3152 Warning 1265 Data truncated for column 'c17' at row 1
3153 insert into t9
3154 ( c1, c13, c14, c15, c16, c17 )
3155 values
3156 ( 21, @arg00, @arg00, @arg00, @arg00, @arg00) ;
3157 Warnings:
3158 Note 1265 Data truncated for column 'c13' at row 1
3159 Note 1265 Data truncated for column 'c16' at row 1
3160 Warning 1265 Data truncated for column 'c17' at row 1
3161 prepare stmt1 from "insert into t9
3162 ( c1, c13, c14, c15, c16, c17 )
3163 values
3164 ( 22, '1991-01-01 01:01:01', '1991-01-01 01:01:01', '1991-01-01 01:01:01',
3165 '1991-01-01 01:01:01', '1991-01-01 01:01:01')" ;
3166 execute stmt1 ;
3167 Warnings:
3168 Note 1265 Data truncated for column 'c13' at row 1
3169 Note 1265 Data truncated for column 'c16' at row 1
3170 Warning 1265 Data truncated for column 'c17' at row 1
3171 prepare stmt2 from "insert into t9
3172 ( c1, c13, c14, c15, c16, c17 )
3173 values
3174 ( 23, ?, ?, ?, ?, ? )" ;
3175 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
3176 Warnings:
3177 Note 1265 Data truncated for column 'c13' at row 1
3178 Note 1265 Data truncated for column 'c16' at row 1
3179 Warning 1265 Data truncated for column 'c17' at row 1
3180 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
3181 insert into t9
3182 ( c1, c13, c14, c15, c16, c17 )
3183 values
3184 ( 30, CAST('1991-01-01 01:01:01' as datetime),
3185 CAST('1991-01-01 01:01:01' as datetime),
3186 CAST('1991-01-01 01:01:01' as datetime),
3187 CAST('1991-01-01 01:01:01' as datetime),
3188 CAST('1991-01-01 01:01:01' as datetime)) ;
3189 Warnings:
3190 Note 1265 Data truncated for column 'c13' at row 1
3191 Note 1265 Data truncated for column 'c16' at row 1
3192 Warning 1265 Data truncated for column 'c17' at row 1
3193 insert into t9
3194 ( c1, c13, c14, c15, c16, c17 )
3195 values
3196 ( 31, @arg00, @arg00, @arg00, @arg00, @arg00) ;
3197 Warnings:
3198 Note 1265 Data truncated for column 'c13' at row 1
3199 Note 1265 Data truncated for column 'c16' at row 1
3200 Warning 1265 Data truncated for column 'c17' at row 1
3201 prepare stmt1 from "insert into t9
3202 ( c1, c13, c14, c15, c16, c17 )
3203 values
3204 ( 32, CAST('1991-01-01 01:01:01' as datetime),
3205 CAST('1991-01-01 01:01:01' as datetime),
3206 CAST('1991-01-01 01:01:01' as datetime),
3207 CAST('1991-01-01 01:01:01' as datetime),
3208 CAST('1991-01-01 01:01:01' as datetime))" ;
3209 execute stmt1 ;
3210 Warnings:
3211 Note 1265 Data truncated for column 'c13' at row 1
3212 Note 1265 Data truncated for column 'c16' at row 1
3213 Warning 1265 Data truncated for column 'c17' at row 1
3214 prepare stmt2 from "insert into t9
3215 ( c1, c13, c14, c15, c16, c17 )
3216 values
3217 ( 33, ?, ?, ?, ?, ? )" ;
3218 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
3219 Warnings:
3220 Note 1265 Data truncated for column 'c13' at row 1
3221 Note 1265 Data truncated for column 'c16' at row 1
3222 Warning 1265 Data truncated for column 'c17' at row 1
3223 set @arg00= 2000000000 ;
3224 insert into t9
3225 ( c1, c13, c14, c15, c16, c17 )
3226 values
3227 ( 40, 2000000000, 2000000000, 2000000000, 2000000000, 2000000000 ) ;
3228 Warnings:
3229 Warning 1265 Data truncated for column 'c13' at row 1
3230 Warning 1265 Data truncated for column 'c14' at row 1
3231 Warning 1265 Data truncated for column 'c15' at row 1
3232 Warning 1265 Data truncated for column 'c16' at row 1
3233 Warning 1264 Out of range value for column 'c17' at row 1
3234 insert into t9
3235 ( c1, c13, c14, c15, c16, c17 )
3236 values
3237 ( 41, @arg00, @arg00, @arg00, @arg00, @arg00) ;
3238 Warnings:
3239 Warning 1265 Data truncated for column 'c13' at row 1
3240 Warning 1265 Data truncated for column 'c14' at row 1
3241 Warning 1265 Data truncated for column 'c15' at row 1
3242 Warning 1265 Data truncated for column 'c16' at row 1
3243 Warning 1264 Out of range value for column 'c17' at row 1
3244 prepare stmt1 from "insert into t9
3245 ( c1, c13, c14, c15, c16, c17 )
3246 values
3247 ( 42, 2000000000, 2000000000, 2000000000, 2000000000, 2000000000 )" ;
3248 execute stmt1 ;
3249 Warnings:
3250 Warning 1265 Data truncated for column 'c13' at row 1
3251 Warning 1265 Data truncated for column 'c14' at row 1
3252 Warning 1265 Data truncated for column 'c15' at row 1
3253 Warning 1265 Data truncated for column 'c16' at row 1
3254 Warning 1264 Out of range value for column 'c17' at row 1
3255 prepare stmt2 from "insert into t9
3256 ( c1, c13, c14, c15, c16, c17 )
3257 values
3258 ( 43, ?, ?, ?, ?, ? )" ;
3259 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
3260 Warnings:
3261 Warning 1265 Data truncated for column 'c13' at row 1
3262 Warning 1265 Data truncated for column 'c14' at row 1
3263 Warning 1265 Data truncated for column 'c15' at row 1
3264 Warning 1265 Data truncated for column 'c16' at row 1
3265 Warning 1264 Out of range value for column 'c17' at row 1
3266 set @arg00= 1.0e+10 ;
3267 insert into t9
3268 ( c1, c13, c14, c15, c16, c17 )
3269 values
3270 ( 50, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 ) ;
3271 Warnings:
3272 Warning 1265 Data truncated for column 'c15' at row 1
3273 Note 1265 Data truncated for column 'c16' at row 1
3274 Warning 1264 Out of range value for column 'c17' at row 1
3275 insert into t9
3276 ( c1, c13, c14, c15, c16, c17 )
3277 values
3278 ( 51, @arg00, @arg00, @arg00, @arg00, @arg00) ;
3279 Warnings:
3280 Warning 1265 Data truncated for column 'c15' at row 1
3281 Note 1265 Data truncated for column 'c16' at row 1
3282 Warning 1264 Out of range value for column 'c17' at row 1
3283 prepare stmt1 from "insert into t9
3284 ( c1, c13, c14, c15, c16, c17 )
3285 values
3286 ( 52, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 )" ;
3287 execute stmt1 ;
3288 Warnings:
3289 Warning 1265 Data truncated for column 'c15' at row 1
3290 Note 1265 Data truncated for column 'c16' at row 1
3291 Warning 1264 Out of range value for column 'c17' at row 1
3292 prepare stmt2 from "insert into t9
3293 ( c1, c13, c14, c15, c16, c17 )
3294 values
3295 ( 53, ?, ?, ?, ?, ? )" ;
3296 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
3297 Warnings:
3298 Warning 1265 Data truncated for column 'c15' at row 1
3299 Note 1265 Data truncated for column 'c16' at row 1
3300 Warning 1264 Out of range value for column 'c17' at row 1
3301 set sql_mode = default;
3302 set @arg00= 'abc' ;
3303 set @arg00= NULL ;
3304 insert into t9
3305 ( c1, c13, c14, c15, c16, c17 )
3306 values
3307 ( 60, NULL, NULL, '1991-01-01 01:01:01',
3308 NULL, NULL) ;
3309 insert into t9
3310 ( c1, c13, c14, c15, c16, c17 )
3311 values
3312 ( 61, @arg00, @arg00, '1991-01-01 01:01:01', @arg00, @arg00) ;
3313 prepare stmt1 from "insert into t9
3314 ( c1, c13, c14, c15, c16, c17 )
3315 values
3316 ( 62, NULL, NULL, '1991-01-01 01:01:01',
3317 NULL, NULL)" ;
3318 execute stmt1 ;
3319 prepare stmt2 from "insert into t9
3320 ( c1, c13, c14, c15, c16, c17 )
3321 values
3322 ( 63, ?, ?, '1991-01-01 01:01:01', ?, ? )" ;
3323 execute stmt2 using @arg00, @arg00, @arg00, @arg00 ;
3324 set @arg00= 8 ;
3325 set @arg00= NULL ;
3326 insert into t9
3327 ( c1, c13, c14, c15, c16, c17 )
3328 values
3329 ( 71, @arg00, @arg00, '1991-01-01 01:01:01', @arg00, @arg00) ;
3330 prepare stmt2 from "insert into t9
3331 ( c1, c13, c14, c15, c16, c17 )
3332 values
3333 ( 73, ?, ?, '1991-01-01 01:01:01', ?, ? )" ;
3334 execute stmt2 using @arg00, @arg00, @arg00, @arg00 ;
3335 set @arg00= 8.0 ;
3336 set @arg00= NULL ;
3337 insert into t9
3338 ( c1, c13, c14, c15, c16, c17 )
3339 values
3340 ( 81, @arg00, @arg00, '1991-01-01 01:01:01', @arg00, @arg00) ;
3341 prepare stmt2 from "insert into t9
3342 ( c1, c13, c14, c15, c16, c17 )
3343 values
3344 ( 83, ?, ?, '1991-01-01 01:01:01', ?, ? )" ;
3345 execute stmt2 using @arg00, @arg00, @arg00, @arg00 ;
3346 select c1, c13, c14, c15, c16, c17 from t9 order by c1 ;
3347 c1 c13 c14 c15 c16 c17
3348 20 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3349 21 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3350 22 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3351 23 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3352 30 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3353 31 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3354 32 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3355 33 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991
3356 40 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000
3357 41 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000
3358 42 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000
3359 43 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000
3360 50 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000
3361 51 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000
3362 52 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000
3363 53 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000
3364 60 NULL NULL 1991-01-01 01:01:01 NULL NULL
3365 61 NULL NULL 1991-01-01 01:01:01 NULL NULL
3366 62 NULL NULL 1991-01-01 01:01:01 NULL NULL
3367 63 NULL NULL 1991-01-01 01:01:01 NULL NULL
3368 71 NULL NULL 1991-01-01 01:01:01 NULL NULL
3369 73 NULL NULL 1991-01-01 01:01:01 NULL NULL
3370 81 NULL NULL 1991-01-01 01:01:01 NULL NULL
3371 83 NULL NULL 1991-01-01 01:01:01 NULL NULL
3372 test_sequence
3373 -- select .. where date/time column = .. --
3374 set @arg00= '1991-01-01 01:01:01' ;
3375 select 'true' as found from t9
3376 where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
3377 c15= '1991-01-01 01:01:01' and
3378 c17= '1991-01-01 01:01:01' ;
3379 found
3380 true
3381 select 'true' as found from t9
3382 where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00
3383 and c17= @arg00 ;
3384 found
3385 true
3386 prepare stmt1 from "select 'true' as found from t9
3387 where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
3388 c15= '1991-01-01 01:01:01' and
3389 c17= '1991-01-01 01:01:01'" ;
3390 execute stmt1 ;
3391 found
3392 true
3393 prepare stmt1 from "select 'true' as found from t9
3394 where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c17= ?" ;
3395 execute stmt1 using @arg00, @arg00, @arg00, @arg00 ;
3396 found
3397 true
3398 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
3399 select 'true' as found from t9
3400 where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
3401 c14= CAST('1991-01-01 01:01:01' as datetime) and
3402 c15= CAST('1991-01-01 01:01:01' as datetime) and
3403 c17= CAST('1991-01-01 01:01:01' as datetime) ;
3404 found
3405 true
3406 select 'true' as found from t9
3407 where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00
3408 and c17= @arg00 ;
3409 found
3410 true
3411 prepare stmt1 from "select 'true' as found from t9
3412 where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
3413 c14= CAST('1991-01-01 01:01:01' as datetime) and
3414 c15= CAST('1991-01-01 01:01:01' as datetime) and
3415 c17= CAST('1991-01-01 01:01:01' as datetime)" ;
3416 execute stmt1 ;
3417 found
3418 true
3419 prepare stmt1 from "select 'true' as found from t9
3420 where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c17= ?" ;
3421 execute stmt1 using @arg00, @arg00, @arg00, @arg00 ;
3422 found
3423 true
3424 set @arg00= '01:01:01' ;
3425 select 'true' as found from t9 where c1= 20 and c16= '01:01:01' ;
3426 found
3427 true
3428 select 'true' as found from t9 where c1= 20 and c16= @arg00 ;
3429 found
3430 true
3431 prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= '01:01:01'" ;
3432 execute stmt1 ;
3433 found
3434 true
3435 prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= ?" ;
3436 execute stmt1 using @arg00 ;
3437 found
3438 true
3439 set @arg00= CAST('01:01:01' as time) ;
3440 select 'true' as found from t9 where c1= 20 and c16= CAST('01:01:01' as time) ;
3441 found
3442 true
3443 select 'true' as found from t9 where c1= 20 and c16= @arg00 ;
3444 found
3445 true
3446 prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= CAST('01:01:01' as time)" ;
3447 execute stmt1 ;
3448 found
3449 true
3450 prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= ?" ;
3451 execute stmt1 using @arg00 ;
3452 found
3453 true
3454 set @arg00= 1991 ;
3455 select 'true' as found from t9
3456 where c1= 20 and c17= 1991 ;
3457 found
3458 true
3459 select 'true' as found from t9
3460 where c1= 20 and c17= @arg00 ;
3461 found
3462 true
3463 prepare stmt1 from "select 'true' as found from t9
3464 where c1= 20 and c17= 1991" ;
3465 execute stmt1 ;
3466 found
3467 true
3468 prepare stmt1 from "select 'true' as found from t9
3469 where c1= 20 and c17= ?" ;
3470 execute stmt1 using @arg00 ;
3471 found
3472 true
3473 set @arg00= 1.991e+3 ;
3474 select 'true' as found from t9
3475 where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;
3476 found
3477 true
3478 select 'true' as found from t9
3479 where c1= 20 and abs(c17 - @arg00) < 0.01 ;
3480 found
3481 true
3482 prepare stmt1 from "select 'true' as found from t9
3483 where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;
3484 execute stmt1 ;
3485 found
3486 true
3487 prepare stmt1 from "select 'true' as found from t9
3488 where c1= 20 and abs(c17 - ?) < 0.01" ;
3489 execute stmt1 using @arg00 ;
3490 found
3491 true
3492 drop table t1, t9;