Debian Codegraph @

mysql-test/suite/ndb/r/ps_7ndb.result

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