1use test; 2drop table if exists t1, t9 ; 3create table t1 4( 5a int, b varchar(30), 6primary key(a) 7) engine = 'MYISAM' ; 8create table t9 9( 10c1 tinyint, c2 smallint, c3 mediumint, c4 int, 11c5 integer, c6 bigint, c7 float, c8 double, 12c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), 13c13 date, c14 datetime, c15 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 14ON UPDATE CURRENT_TIMESTAMP, c16 time, 15c17 year, c18 tinyint, c19 bool, c20 char, 16c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, 17c25 blob, c26 text, c27 mediumblob, c28 mediumtext, 18c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'), 19c32 set('monday', 'tuesday', 'wednesday'), 20primary key(c1) 21) engine = 'MYISAM' ; 22delete from t1 ; 23insert into t1 values (1,'one'); 24insert into t1 values (2,'two'); 25insert into t1 values (3,'three'); 26insert into t1 values (4,'four'); 27commit ; 28delete from t9 ; 29insert into t9 30set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1, 31c10= 1, c11= 1, c12 = 1, 32c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11', 33c16= '11:11:11', c17= '2004', 34c18= 1, c19=true, c20= 'a', c21= '123456789a', 35c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext', 36c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext', 37c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday'; 38insert into t9 39set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9, 40c10= 9, c11= 9, c12 = 9, 41c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11', 42c16= '11:11:11', c17= '2004', 43c18= 1, c19=false, c20= 'a', c21= '123456789a', 44c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext', 45c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext', 46c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday'; 47commit ; 48test_sequence 49------ simple select tests ------ 50prepare stmt1 from ' select * from t9 order by c1 ' ; 51execute stmt1; 52Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 53def test t9 t9 c1 c1 1 4 1 N 53251 0 63 54def test t9 t9 c2 c2 2 6 1 Y 32768 0 63 55def test t9 t9 c3 c3 9 9 1 Y 32768 0 63 56def test t9 t9 c4 c4 3 11 1 Y 32768 0 63 57def test t9 t9 c5 c5 3 11 1 Y 32768 0 63 58def test t9 t9 c6 c6 8 20 1 Y 32768 0 63 59def test t9 t9 c7 c7 4 12 1 Y 32768 31 63 60def test t9 t9 c8 c8 5 22 1 Y 32768 31 63 61def test t9 t9 c9 c9 5 22 1 Y 32768 31 63 62def test t9 t9 c10 c10 5 22 1 Y 32768 31 63 63def test t9 t9 c11 c11 246 9 6 Y 32768 4 63 64def test t9 t9 c12 c12 246 10 6 Y 32768 4 63 65def test t9 t9 c13 c13 10 10 10 Y 128 0 63 66def test t9 t9 c14 c14 12 19 19 Y 128 0 63 67def test t9 t9 c15 c15 7 19 19 N 9377 0 63 68def test t9 t9 c16 c16 11 10 8 Y 128 0 63 69def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 70def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 71def test t9 t9 c19 c19 1 1 1 Y 32768 0 63 72def test t9 t9 c20 c20 254 1 1 Y 0 0 8 73def test t9 t9 c21 c21 254 10 10 Y 0 0 8 74def test t9 t9 c22 c22 253 30 30 Y 0 0 8 75def test t9 t9 c23 c23 252 255 8 Y 144 0 63 76def test t9 t9 c24 c24 252 255 8 Y 16 0 8 77def test t9 t9 c25 c25 252 65535 4 Y 144 0 63 78def test t9 t9 c26 c26 252 65535 4 Y 16 0 8 79def test t9 t9 c27 c27 252 16777215 10 Y 144 0 63 80def test t9 t9 c28 c28 252 16777215 10 Y 16 0 8 81def test t9 t9 c29 c29 252 4294967295 8 Y 144 0 63 82def test t9 t9 c30 c30 252 4294967295 8 Y 16 0 8 83def test t9 t9 c31 c31 254 5 3 Y 256 0 8 84def test t9 t9 c32 c32 254 24 7 Y 2048 0 8 85c1 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 861 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 879 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 88set @arg00='SELECT' ; 89@arg00 a from t1 where a=1; 90ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@arg00 a from t1 where a=1' at line 1 91prepare stmt1 from ' ? a from t1 where a=1 '; 92ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? a from t1 where a=1' at line 1 93set @arg00=1 ; 94select @arg00, b from t1 where a=1 ; 95@arg00 b 961 one 97prepare stmt1 from ' select ?, b from t1 where a=1 ' ; 98execute stmt1 using @arg00 ; 99? b 1001 one 101set @arg00='lion' ; 102select @arg00, b from t1 where a=1 ; 103@arg00 b 104lion one 105prepare stmt1 from ' select ?, b from t1 where a=1 ' ; 106execute stmt1 using @arg00 ; 107? b 108lion one 109set @arg00=NULL ; 110select @arg00, b from t1 where a=1 ; 111@arg00 b 112NULL one 113prepare stmt1 from ' select ?, b from t1 where a=1 ' ; 114execute stmt1 using @arg00 ; 115? b 116NULL one 117set @arg00=1 ; 118select b, a - @arg00 from t1 where a=1 ; 119b a - @arg00 120one 0 121prepare stmt1 from ' select b, a - ? from t1 where a=1 ' ; 122execute stmt1 using @arg00 ; 123b a - ? 124one 0 125set @arg00=null ; 126select @arg00 as my_col ; 127my_col 128NULL 129prepare stmt1 from ' select ? as my_col'; 130execute stmt1 using @arg00 ; 131my_col 132NULL 133select @arg00 + 1 as my_col ; 134my_col 135NULL 136prepare stmt1 from ' select ? + 1 as my_col'; 137execute stmt1 using @arg00 ; 138my_col 139NULL 140select 1 + @arg00 as my_col ; 141my_col 142NULL 143prepare stmt1 from ' select 1 + ? as my_col'; 144execute stmt1 using @arg00 ; 145my_col 146NULL 147set @arg00='MySQL' ; 148select substr(@arg00,1,2) from t1 where a=1 ; 149substr(@arg00,1,2) 150My 151prepare stmt1 from ' select substr(?,1,2) from t1 where a=1 ' ; 152execute stmt1 using @arg00 ; 153substr(?,1,2) 154My 155set @arg00=3 ; 156select substr('MySQL',@arg00,5) from t1 where a=1 ; 157substr('MySQL',@arg00,5) 158SQL 159prepare stmt1 from ' select substr(''MySQL'',?,5) from t1 where a=1 ' ; 160execute stmt1 using @arg00 ; 161substr('MySQL',?,5) 162SQL 163select substr('MySQL',1,@arg00) from t1 where a=1 ; 164substr('MySQL',1,@arg00) 165MyS 166prepare stmt1 from ' select substr(''MySQL'',1,?) from t1 where a=1 ' ; 167execute stmt1 using @arg00 ; 168substr('MySQL',1,?) 169MyS 170set @arg00='MySQL' ; 171select a , concat(@arg00,b) from t1 order by a; 172a concat(@arg00,b) 1731 MySQLone 1742 MySQLtwo 1753 MySQLthree 1764 MySQLfour 177prepare stmt1 from ' select a , concat(?,b) from t1 order by a ' ; 178execute stmt1 using @arg00; 179a concat(?,b) 1801 MySQLone 1812 MySQLtwo 1823 MySQLthree 1834 MySQLfour 184select a , concat(b,@arg00) from t1 order by a ; 185a concat(b,@arg00) 1861 oneMySQL 1872 twoMySQL 1883 threeMySQL 1894 fourMySQL 190prepare stmt1 from ' select a , concat(b,?) from t1 order by a ' ; 191execute stmt1 using @arg00; 192a concat(b,?) 1931 oneMySQL 1942 twoMySQL 1953 threeMySQL 1964 fourMySQL 197set @arg00='MySQL' ; 198select group_concat(@arg00,b order by a) from t1 199group by 'a' ; 200group_concat(@arg00,b order by a) 201MySQLone,MySQLtwo,MySQLthree,MySQLfour 202prepare stmt1 from ' select group_concat(?,b order by a) from t1 203group by ''a'' ' ; 204execute stmt1 using @arg00; 205group_concat(?,b order by a) 206MySQLone,MySQLtwo,MySQLthree,MySQLfour 207select group_concat(b,@arg00 order by a) from t1 208group by 'a' ; 209group_concat(b,@arg00 order by a) 210oneMySQL,twoMySQL,threeMySQL,fourMySQL 211prepare stmt1 from ' select group_concat(b,? order by a) from t1 212group by ''a'' ' ; 213execute stmt1 using @arg00; 214group_concat(b,? order by a) 215oneMySQL,twoMySQL,threeMySQL,fourMySQL 216set @arg00='first' ; 217set @arg01='second' ; 218set @arg02=NULL; 219select @arg00, @arg01 from t1 where a=1 ; 220@arg00 @arg01 221first second 222prepare stmt1 from ' select ?, ? from t1 where a=1 ' ; 223execute stmt1 using @arg00, @arg01 ; 224? ? 225first second 226execute stmt1 using @arg02, @arg01 ; 227? ? 228NULL second 229execute stmt1 using @arg00, @arg02 ; 230? ? 231first NULL 232execute stmt1 using @arg02, @arg02 ; 233? ? 234NULL NULL 235drop table if exists t5 ; 236create table t5 (id1 int(11) not null default '0', 237value2 varchar(100), value1 varchar(100)) ; 238insert into t5 values (1,'hh','hh'),(2,'hh','hh'), 239(1,'ii','ii'),(2,'ii','ii') ; 240prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? order by id1,value1 ' ; 241set @arg00=1 ; 242set @arg01='hh' ; 243execute stmt1 using @arg00, @arg01 ; 244id1 value1 2451 hh 2461 ii 2472 hh 248drop table t5 ; 249drop table if exists t5 ; 250create table t5(session_id char(9) not null) ; 251insert into t5 values ('abc') ; 252prepare stmt1 from ' select * from t5 253where ?=''1111'' and session_id = ''abc'' ' ; 254set @arg00='abc' ; 255execute stmt1 using @arg00 ; 256session_id 257set @arg00='1111' ; 258execute stmt1 using @arg00 ; 259session_id 260abc 261set @arg00='abc' ; 262execute stmt1 using @arg00 ; 263session_id 264drop table t5 ; 265set @arg00='FROM' ; 266select a @arg00 t1 where a=1 ; 267ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@arg00 t1 where a=1' at line 1 268prepare stmt1 from ' select a ? t1 where a=1 ' ; 269ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? t1 where a=1' at line 1 270set @arg00='t1' ; 271select a from @arg00 where a=1 ; 272ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@arg00 where a=1' at line 1 273prepare stmt1 from ' select a from ? where a=1 ' ; 274ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? where a=1' at line 1 275set @arg00='WHERE' ; 276select a from t1 @arg00 a=1 ; 277ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@arg00 a=1' at line 1 278prepare stmt1 from ' select a from t1 ? a=1 ' ; 279ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? a=1' at line 1 280set @arg00=1 ; 281select a FROM t1 where a=@arg00 ; 282a 2831 284prepare stmt1 from ' select a FROM t1 where a=? ' ; 285execute stmt1 using @arg00 ; 286a 2871 288set @arg00=1000 ; 289execute stmt1 using @arg00 ; 290a 291set @arg00=NULL ; 292select a FROM t1 where a=@arg00 ; 293a 294prepare stmt1 from ' select a FROM t1 where a=? ' ; 295execute stmt1 using @arg00 ; 296a 297set @arg00=4 ; 298select a FROM t1 where a=sqrt(@arg00) ; 299a 3002 301prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ; 302execute stmt1 using @arg00 ; 303a 3042 305set @arg00=NULL ; 306select a FROM t1 where a=sqrt(@arg00) ; 307a 308prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ; 309execute stmt1 using @arg00 ; 310a 311set @arg00=2 ; 312set @arg01=3 ; 313select a FROM t1 where a in (@arg00,@arg01) order by a; 314a 3152 3163 317prepare stmt1 from ' select a FROM t1 where a in (?,?) order by a '; 318execute stmt1 using @arg00, @arg01; 319a 3202 3213 322set @arg00= 'one' ; 323set @arg01= 'two' ; 324set @arg02= 'five' ; 325prepare stmt1 from ' select b FROM t1 where b in (?,?,?) order by b ' ; 326execute stmt1 using @arg00, @arg01, @arg02 ; 327b 328one 329two 330prepare stmt1 from ' select b FROM t1 where b like ? '; 331set @arg00='two' ; 332execute stmt1 using @arg00 ; 333b 334two 335set @arg00='tw%' ; 336execute stmt1 using @arg00 ; 337b 338two 339set @arg00='%wo' ; 340execute stmt1 using @arg00 ; 341b 342two 343set @arg00=null ; 344insert into t9 set c1= 0, c5 = NULL ; 345select c5 from t9 where c5 > NULL ; 346c5 347prepare stmt1 from ' select c5 from t9 where c5 > ? '; 348execute stmt1 using @arg00 ; 349c5 350select c5 from t9 where c5 < NULL ; 351c5 352prepare stmt1 from ' select c5 from t9 where c5 < ? '; 353execute stmt1 using @arg00 ; 354c5 355select c5 from t9 where c5 = NULL ; 356c5 357prepare stmt1 from ' select c5 from t9 where c5 = ? '; 358execute stmt1 using @arg00 ; 359c5 360select c5 from t9 where c5 <=> NULL ; 361c5 362NULL 363prepare stmt1 from ' select c5 from t9 where c5 <=> ? '; 364execute stmt1 using @arg00 ; 365c5 366NULL 367delete from t9 where c1= 0 ; 368set @arg00='>' ; 369select a FROM t1 where a @arg00 1 ; 370ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@arg00 1' at line 1 371prepare stmt1 from ' select a FROM t1 where a ? 1 ' ; 372ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? 1' at line 1 373set @arg00=1 ; 374select a,b FROM t1 where a is not NULL 375AND b is not NULL group by a - @arg00 ; 376a b 3771 one 3782 two 3793 three 3804 four 381prepare stmt1 from ' select a,b FROM t1 where a is not NULL 382AND b is not NULL group by a - ? ' ; 383execute stmt1 using @arg00 ; 384a b 3851 one 3862 two 3873 three 3884 four 389set @arg00='two' ; 390select a,b FROM t1 where a is not NULL 391AND b is not NULL having b <> @arg00 order by a ; 392a b 3931 one 3943 three 3954 four 396prepare stmt1 from ' select a,b FROM t1 where a is not NULL 397AND b is not NULL having b <> ? order by a ' ; 398execute stmt1 using @arg00 ; 399a b 4001 one 4013 three 4024 four 403set @arg00=1 ; 404select a,b FROM t1 where a is not NULL 405AND b is not NULL order by a - @arg00 ; 406a b 4071 one 4082 two 4093 three 4104 four 411prepare stmt1 from ' select a,b FROM t1 where a is not NULL 412AND b is not NULL order by a - ? ' ; 413execute stmt1 using @arg00 ; 414a b 4151 one 4162 two 4173 three 4184 four 419set @arg00=2 ; 420select a,b from t1 order by 2 ; 421a b 4224 four 4231 one 4243 three 4252 two 426prepare stmt1 from ' select a,b from t1 427order by ? '; 428execute stmt1 using @arg00; 429a b 4304 four 4311 one 4323 three 4332 two 434set @arg00=1 ; 435execute stmt1 using @arg00; 436a b 4371 one 4382 two 4393 three 4404 four 441set @arg00=0 ; 442execute stmt1 using @arg00; 443ERROR 42S22: Unknown column '?' in 'order clause' 444set @arg00=1; 445prepare stmt1 from ' select a,b from t1 order by a 446limit 1 '; 447execute stmt1 ; 448a b 4491 one 450prepare stmt1 from ' select a,b from t1 order by a limit ? '; 451execute stmt1 using @arg00; 452a b 4531 one 454set @arg00='b' ; 455set @arg01=0 ; 456set @arg02=2 ; 457set @arg03=2 ; 458select sum(a), @arg00 from t1 where a > @arg01 459and b is not null group by substr(b,@arg02) 460having sum(a) <> @arg03 ; 461sum(a) @arg00 4623 b 4631 b 4644 b 465prepare stmt1 from ' select sum(a), ? from t1 where a > ? 466and b is not null group by substr(b,?) 467having sum(a) <> ? '; 468execute stmt1 using @arg00, @arg01, @arg02, @arg03; 469sum(a) ? 4703 b 4711 b 4724 b 473test_sequence 474------ join tests ------ 475select first.a as a1, second.a as a2 476from t1 first, t1 second 477where first.a = second.a order by a1 ; 478a1 a2 4791 1 4802 2 4813 3 4824 4 483prepare stmt1 from ' select first.a as a1, second.a as a2 484 from t1 first, t1 second 485 where first.a = second.a order by a1 '; 486execute stmt1 ; 487a1 a2 4881 1 4892 2 4903 3 4914 4 492set @arg00='ABC'; 493set @arg01='two'; 494set @arg02='one'; 495select first.a, @arg00, second.a FROM t1 first, t1 second 496where @arg01 = first.b or first.a = second.a or second.b = @arg02 497order by second.a, first.a; 498a @arg00 a 4991 ABC 1 5002 ABC 1 5013 ABC 1 5024 ABC 1 5032 ABC 2 5042 ABC 3 5053 ABC 3 5062 ABC 4 5074 ABC 4 508prepare stmt1 from ' select first.a, ?, second.a FROM t1 first, t1 second 509 where ? = first.b or first.a = second.a or second.b = ? 510 order by second.a, first.a'; 511execute stmt1 using @arg00, @arg01, @arg02; 512a ? a 5131 ABC 1 5142 ABC 1 5153 ABC 1 5164 ABC 1 5172 ABC 2 5182 ABC 3 5193 ABC 3 5202 ABC 4 5214 ABC 4 522drop table if exists t2 ; 523create table t2 as select * from t1 ; 524set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ; 525set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ; 526set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ; 527set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ; 528set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ; 529set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ; 530set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ; 531set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ; 532set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ; 533the join statement is: 534SELECT * FROM t2 right join t1 using(a) order by t2.a 535prepare stmt1 from @query9 ; 536execute stmt1 ; 537a b b 5381 one one 5392 two two 5403 three three 5414 four four 542execute stmt1 ; 543a b b 5441 one one 5452 two two 5463 three three 5474 four four 548execute stmt1 ; 549a b b 5501 one one 5512 two two 5523 three three 5534 four four 554the join statement is: 555SELECT * FROM t2 natural right join t1 order by t2.a 556prepare stmt1 from @query8 ; 557execute stmt1 ; 558a b 5591 one 5602 two 5613 three 5624 four 563execute stmt1 ; 564a b 5651 one 5662 two 5673 three 5684 four 569execute stmt1 ; 570a b 5711 one 5722 two 5733 three 5744 four 575the join statement is: 576SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a 577prepare stmt1 from @query7 ; 578execute stmt1 ; 579a b a b 5801 one 1 one 5812 two 2 two 5823 three 3 three 5834 four 4 four 584execute stmt1 ; 585a b a b 5861 one 1 one 5872 two 2 two 5883 three 3 three 5894 four 4 four 590execute stmt1 ; 591a b a b 5921 one 1 one 5932 two 2 two 5943 three 3 three 5954 four 4 four 596the join statement is: 597SELECT * FROM t2 left join t1 using(a) order by t2.a 598prepare stmt1 from @query6 ; 599execute stmt1 ; 600a b b 6011 one one 6022 two two 6033 three three 6044 four four 605execute stmt1 ; 606a b b 6071 one one 6082 two two 6093 three three 6104 four four 611execute stmt1 ; 612a b b 6131 one one 6142 two two 6153 three three 6164 four four 617the join statement is: 618SELECT * FROM t2 natural left join t1 order by t2.a 619prepare stmt1 from @query5 ; 620execute stmt1 ; 621a b 6221 one 6232 two 6243 three 6254 four 626execute stmt1 ; 627a b 6281 one 6292 two 6303 three 6314 four 632execute stmt1 ; 633a b 6341 one 6352 two 6363 three 6374 four 638the join statement is: 639SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a 640prepare stmt1 from @query4 ; 641execute stmt1 ; 642a b a b 6431 one 1 one 6442 two 2 two 6453 three 3 three 6464 four 4 four 647execute stmt1 ; 648a b a b 6491 one 1 one 6502 two 2 two 6513 three 3 three 6524 four 4 four 653execute stmt1 ; 654a b a b 6551 one 1 one 6562 two 2 two 6573 three 3 three 6584 four 4 four 659the join statement is: 660SELECT * FROM t2 join t1 using(a) order by t2.a 661prepare stmt1 from @query3 ; 662execute stmt1 ; 663a b b 6641 one one 6652 two two 6663 three three 6674 four four 668execute stmt1 ; 669a b b 6701 one one 6712 two two 6723 three three 6734 four four 674execute stmt1 ; 675a b b 6761 one one 6772 two two 6783 three three 6794 four four 680the join statement is: 681SELECT * FROM t2 natural join t1 order by t2.a 682prepare stmt1 from @query2 ; 683execute stmt1 ; 684a b 6851 one 6862 two 6873 three 6884 four 689execute stmt1 ; 690a b 6911 one 6922 two 6933 three 6944 four 695execute stmt1 ; 696a b 6971 one 6982 two 6993 three 7004 four 701the join statement is: 702SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a 703prepare stmt1 from @query1 ; 704execute stmt1 ; 705a b a b 7061 one 1 one 7072 two 2 two 7083 three 3 three 7094 four 4 four 710execute stmt1 ; 711a b a b 7121 one 1 one 7132 two 2 two 7143 three 3 three 7154 four 4 four 716execute stmt1 ; 717a b a b 7181 one 1 one 7192 two 2 two 7203 three 3 three 7214 four 4 four 722drop table t2 ; 723test_sequence 724------ subquery tests ------ 725prepare stmt1 from ' select a, b FROM t1 outer_table where 726 a = (select a from t1 where b = ''two'') '; 727execute stmt1 ; 728a b 7292 two 730set @arg00='two' ; 731select a, b FROM t1 outer_table where 732a = (select a from t1 where b = 'two' ) and b=@arg00 ; 733a b 7342 two 735prepare stmt1 from ' select a, b FROM t1 outer_table where 736 a = (select a from t1 where b = ''two'') and b=? '; 737execute stmt1 using @arg00; 738a b 7392 two 740set @arg00='two' ; 741select a, b FROM t1 outer_table where 742a = (select a from t1 where b = @arg00 ) and b='two' ; 743a b 7442 two 745prepare stmt1 from ' select a, b FROM t1 outer_table where 746 a = (select a from t1 where b = ? ) and b=''two'' ' ; 747execute stmt1 using @arg00; 748a b 7492 two 750set @arg00=3 ; 751set @arg01='three' ; 752select a,b FROM t1 where (a,b) in (select 3, 'three'); 753a b 7543 three 755select a FROM t1 where (a,b) in (select @arg00,@arg01); 756a 7573 758prepare stmt1 from ' select a FROM t1 where (a,b) in (select ?, ?) '; 759execute stmt1 using @arg00, @arg01; 760a 7613 762set @arg00=1 ; 763set @arg01='two' ; 764set @arg02=2 ; 765set @arg03='two' ; 766select a, @arg00, b FROM t1 outer_table where 767b=@arg01 and a = (select @arg02 from t1 where b = @arg03 ) ; 768a @arg00 b 7692 1 two 770prepare stmt1 from ' select a, ?, b FROM t1 outer_table where 771 b=? and a = (select ? from t1 where b = ? ) ' ; 772execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; 773a ? b 7742 1 two 775prepare stmt1 from 'select c4 FROM t9 where 776 c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ; 777execute stmt1 using @arg01, @arg02; 778c4 779prepare stmt1 from ' select a, b FROM t1 outer_table where 780 a = (select a from t1 where b = outer_table.b ) order by a '; 781execute stmt1 ; 782a b 7831 one 7842 two 7853 three 7864 four 787prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= 788 (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; 789execute stmt1 ; 790ccc 7911 792deallocate prepare stmt1 ; 793prepare 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) '; 795execute stmt1 ; 796ccc 7971 798deallocate prepare stmt1 ; 799prepare 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) '; 801execute stmt1 ; 802ccc 8031 804deallocate prepare stmt1 ; 805set @arg00='two' ; 806select a, b FROM t1 outer_table where 807a = (select a from t1 where b = outer_table.b ) and b=@arg00 ; 808a b 8092 two 810prepare stmt1 from ' select a, b FROM t1 outer_table where 811 a = (select a from t1 where b = outer_table.b) and b=? '; 812execute stmt1 using @arg00; 813a b 8142 two 815set @arg00=2 ; 816select a, b FROM t1 outer_table where 817a = (select a from t1 where a = @arg00 and b = outer_table.b) and b='two' ; 818a b 8192 two 820prepare stmt1 from ' select a, b FROM t1 outer_table where 821 a = (select a from t1 where a = ? and b = outer_table.b) and b=''two'' ' ; 822execute stmt1 using @arg00; 823a b 8242 two 825set @arg00=2 ; 826select a, b FROM t1 outer_table where 827a = (select a from t1 where outer_table.a = @arg00 and a=2) and b='two' ; 828a b 8292 two 830prepare stmt1 from ' select a, b FROM t1 outer_table where 831 a = (select a from t1 where outer_table.a = ? and a=2) and b=''two'' ' ; 832execute stmt1 using @arg00; 833a b 8342 two 835set @arg00=1 ; 836set @arg01='two' ; 837set @arg02=2 ; 838set @arg03='two' ; 839select a, @arg00, b FROM t1 outer_table where 840b=@arg01 and a = (select @arg02 from t1 where outer_table.b = @arg03 841and outer_table.a=a ) ; 842a @arg00 b 8432 1 two 844prepare stmt1 from ' select a, ?, b FROM t1 outer_table where 845 b=? and a = (select ? from t1 where outer_table.b = ? 846 and outer_table.a=a ) ' ; 847execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; 848a ? b 8492 1 two 850set @arg00=1 ; 851set @arg01=0 ; 852select a, @arg00 853from ( select a - @arg00 as a from t1 where a=@arg00 ) as t2 854where a=@arg01; 855a @arg00 8560 1 857prepare stmt1 from ' select a, ? 858 from ( select a - ? as a from t1 where a=? ) as t2 859 where a=? '; 860execute stmt1 using @arg00, @arg00, @arg00, @arg01 ; 861a ? 8620 1 863drop table if exists t2 ; 864create table t2 as select * from t1; 865prepare stmt1 from ' select a in (select a from t2) from t1 ' ; 866execute stmt1 ; 867a in (select a from t2) 8681 8691 8701 8711 872drop table if exists t5, t6, t7 ; 873create table t5 (a int , b int) ; 874create table t6 like t5 ; 875create table t7 like t5 ; 876insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7), 877(2, -1), (3, 10) ; 878insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1) ; 879insert into t7 values (3, 3), (2, 2), (1, 1) ; 880prepare stmt1 from ' select a, (select count(distinct t5.b) as sum from t5, t6 881 where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b 882 group by t5.a order by sum limit 1) from t7 ' ; 883execute stmt1 ; 884a (select count(distinct t5.b) as sum from t5, t6 885 where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b 886 group by t5.a order by sum limit 1) 8873 1 8882 2 8891 2 890execute stmt1 ; 891a (select count(distinct t5.b) as sum from t5, t6 892 where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b 893 group by t5.a order by sum limit 1) 8943 1 8952 2 8961 2 897execute stmt1 ; 898a (select count(distinct t5.b) as sum from t5, t6 899 where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b 900 group by t5.a order by sum limit 1) 9013 1 9022 2 9031 2 904drop table t5, t6, t7 ; 905drop table if exists t2 ; 906create table t2 as select * from t9; 907set @stmt= ' SELECT 908 (SELECT SUM(c1 + c12 + 0.0) FROM t2 909 where (t9.c2 - 0e-3) = t2.c2 910 GROUP BY t9.c15 LIMIT 1) as scalar_s, 911 exists (select 1.0e+0 from t2 912 where t2.c3 * 9.0000000000 = t9.c4) as exists_s, 913 c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, 914 (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s 915FROM t9, 916(select c25 x, c32 y from t2) tt WHERE x = c25 ' ; 917prepare stmt1 from @stmt ; 918execute stmt1 ; 919execute stmt1 ; 920set @stmt= concat('explain ',@stmt); 921prepare stmt1 from @stmt ; 922execute stmt1 ; 923execute stmt1 ; 924set @stmt= ' SELECT 925 (SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2 926 GROUP BY t9.c15 LIMIT 1) as scalar_s, 927 exists (select ? from t2 928 where t2.c3*?=t9.c4) as exists_s, 929 c5*? in (select c6+? from t2) as in_s, 930 (c7-?, c8-?) in (select c9+?, c10+? from t2) as in_row_s 931FROM t9, 932(select c25 x, c32 y from t2) tt WHERE x =c25 ' ; 933set @arg00= 0.0 ; 934set @arg01= 0e-3 ; 935set @arg02= 1.0e+0 ; 936set @arg03= 9.0000000000 ; 937set @arg04= 4 ; 938set @arg05= 0.3e+1 ; 939set @arg06= 4 ; 940set @arg07= 4 ; 941set @arg08= 4.0 ; 942set @arg09= 40e-1 ; 943prepare stmt1 from @stmt ; 944execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, 945@arg07, @arg08, @arg09 ; 946execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, 947@arg07, @arg08, @arg09 ; 948set @stmt= concat('explain ',@stmt); 949prepare stmt1 from @stmt ; 950execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, 951@arg07, @arg08, @arg09 ; 952execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, 953@arg07, @arg08, @arg09 ; 954drop table t2 ; 955select 1 < (select a from t1) ; 956ERROR 21000: Subquery returns more than 1 row 957prepare stmt1 from ' select 1 < (select a from t1) ' ; 958execute stmt1 ; 959ERROR 21000: Subquery returns more than 1 row 960select 1 as my_col ; 961my_col 9621 963test_sequence 964------ union tests ------ 965prepare stmt1 from ' select a FROM t1 where a=1 966 union distinct 967 select a FROM t1 where a=1 '; 968execute stmt1 ; 969a 9701 971execute stmt1 ; 972a 9731 974prepare stmt1 from ' select a FROM t1 where a=1 975 union all 976 select a FROM t1 where a=1 '; 977execute stmt1 ; 978a 9791 9801 981prepare stmt1 from ' SELECT 1, 2 union SELECT 1 ' ; 982ERROR 21000: The used SELECT statements have a different number of columns 983prepare stmt1 from ' SELECT 1 union SELECT 1, 2 ' ; 984ERROR 21000: The used SELECT statements have a different number of columns 985prepare stmt1 from ' SELECT * from t1 union SELECT 1 ' ; 986ERROR 21000: The used SELECT statements have a different number of columns 987prepare stmt1 from ' SELECT 1 union SELECT * from t1 ' ; 988ERROR 21000: The used SELECT statements have a different number of columns 989set @arg00=1 ; 990select @arg00 FROM t1 where a=1 991union distinct 992select 1 FROM t1 where a=1; 993@arg00 9941 995prepare stmt1 from ' select ? FROM t1 where a=1 996 union distinct 997 select 1 FROM t1 where a=1 ' ; 998execute stmt1 using @arg00; 999? 10001 1001set @arg00=1 ; 1002select 1 FROM t1 where a=1 1003union distinct 1004select @arg00 FROM t1 where a=1; 10051 10061 1007prepare stmt1 from ' select 1 FROM t1 where a=1 1008 union distinct 1009 select ? FROM t1 where a=1 ' ; 1010execute stmt1 using @arg00; 10111 10121 1013set @arg00='a' ; 1014select @arg00 FROM t1 where a=1 1015union distinct 1016select @arg00 FROM t1 where a=1; 1017@arg00 1018a 1019prepare stmt1 from ' select ? FROM t1 where a=1 1020 union distinct 1021 select ? FROM t1 where a=1 '; 1022execute stmt1 using @arg00, @arg00; 1023? 1024a 1025prepare stmt1 from ' select ? 1026 union distinct 1027 select ? '; 1028execute stmt1 using @arg00, @arg00; 1029? 1030a 1031set @arg00='a' ; 1032set @arg01=1 ; 1033set @arg02='a' ; 1034set @arg03=2 ; 1035select @arg00 FROM t1 where a=@arg01 1036union distinct 1037select @arg02 FROM t1 where a=@arg03; 1038@arg00 1039a 1040prepare stmt1 from ' select ? FROM t1 where a=? 1041 union distinct 1042 select ? FROM t1 where a=? ' ; 1043execute stmt1 using @arg00, @arg01, @arg02, @arg03; 1044? 1045a 1046set @arg00=1 ; 1047prepare stmt1 from ' select sum(a) + 200, ? from t1 1048union distinct 1049select sum(a) + 200, 1 from t1 1050group by b ' ; 1051execute stmt1 using @arg00; 1052sum(a) + 200 ? 1053210 1 1054204 1 1055201 1 1056203 1 1057202 1 1058set @Oporto='Oporto' ; 1059set @Lisboa='Lisboa' ; 1060set @0=0 ; 1061set @1=1 ; 1062set @2=2 ; 1063set @3=3 ; 1064set @4=4 ; 1065select @Oporto,@Lisboa,@0,@1,@2,@3,@4 ; 1066@Oporto @Lisboa @0 @1 @2 @3 @4 1067Oporto Lisboa 0 1 2 3 4 1068select sum(a) + 200 as the_sum, @Oporto as the_town from t1 1069group by b 1070union distinct 1071select sum(a) + 200, @Lisboa from t1 1072group by b ; 1073the_sum the_town 1074204 Oporto 1075201 Oporto 1076203 Oporto 1077202 Oporto 1078204 Lisboa 1079201 Lisboa 1080203 Lisboa 1081202 Lisboa 1082prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1 1083 group by b 1084 union distinct 1085 select sum(a) + 200, ? from t1 1086 group by b ' ; 1087execute stmt1 using @Oporto, @Lisboa; 1088the_sum the_town 1089204 Oporto 1090201 Oporto 1091203 Oporto 1092202 Oporto 1093204 Lisboa 1094201 Lisboa 1095203 Lisboa 1096202 Lisboa 1097select sum(a) + 200 as the_sum, @Oporto as the_town from t1 1098where a > @1 1099group by b 1100union distinct 1101select sum(a) + 200, @Lisboa from t1 1102where a > @2 1103group by b ; 1104the_sum the_town 1105204 Oporto 1106203 Oporto 1107202 Oporto 1108204 Lisboa 1109203 Lisboa 1110prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1 1111 where a > ? 1112 group by b 1113 union distinct 1114 select sum(a) + 200, ? from t1 1115 where a > ? 1116 group by b ' ; 1117execute stmt1 using @Oporto, @1, @Lisboa, @2; 1118the_sum the_town 1119204 Oporto 1120203 Oporto 1121202 Oporto 1122204 Lisboa 1123203 Lisboa 1124select sum(a) + 200 as the_sum, @Oporto as the_town from t1 1125where a > @1 1126group by b 1127having avg(a) > @2 1128union distinct 1129select sum(a) + 200, @Lisboa from t1 1130where a > @2 1131group by b 1132having avg(a) > @3; 1133the_sum the_town 1134204 Oporto 1135203 Oporto 1136204 Lisboa 1137prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1 1138 where a > ? 1139 group by b 1140 having avg(a) > ? 1141 union distinct 1142 select sum(a) + 200, ? from t1 1143 where a > ? 1144 group by b 1145 having avg(a) > ? '; 1146execute stmt1 using @Oporto, @1, @2, @Lisboa, @2, @3; 1147the_sum the_town 1148204 Oporto 1149203 Oporto 1150204 Lisboa 1151test_sequence 1152------ explain select tests ------ 1153prepare stmt1 from ' explain select * from t9 ' ; 1154execute stmt1; 1155Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1156def id 8 3 1 Y 32928 0 63 1157def select_type 253 19 6 N 1 39 8 1158def table 253 64 2 Y 0 39 8 1159def type 253 10 3 Y 0 39 8 1160def possible_keys 253 4_OR_8_K 0 Y 0 39 8 1161def key 253 64 0 Y 0 39 8 1162def key_len 253 4_OR_8_K 0 Y 0 39 8 1163def ref 253 2048 0 Y 0 39 8 1164def rows 253 64 1 Y 0 39 8 1165def Extra 253 255 0 N 1 39 8 1166id select_type table type possible_keys key key_len ref rows Extra 11671 SIMPLE t9 ALL NULL NULL NULL NULL 2 1168drop table if exists t2 ; 1169create table t2 (s varchar(25), fulltext(s)) 1170ENGINE = 'MYISAM' ; 1171insert into t2 values ('Gravedigger'), ('Greed'),('Hollow Dogs') ; 1172commit ; 1173prepare stmt1 from ' select s from t2 where match (s) against (?) ' ; 1174set @arg00='Dogs' ; 1175execute stmt1 using @arg00 ; 1176s 1177Hollow Dogs 1178prepare stmt1 from ' SELECT s FROM t2 1179where match (s) against (concat(?,''digger'')) '; 1180set @arg00='Grave' ; 1181execute stmt1 using @arg00 ; 1182s 1183Gravedigger 1184drop table t2 ; 1185test_sequence 1186------ delete tests ------ 1187delete from t1 ; 1188insert into t1 values (1,'one'); 1189insert into t1 values (2,'two'); 1190insert into t1 values (3,'three'); 1191insert into t1 values (4,'four'); 1192commit ; 1193delete from t9 ; 1194insert into t9 1195set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1, 1196c10= 1, c11= 1, c12 = 1, 1197c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11', 1198c16= '11:11:11', c17= '2004', 1199c18= 1, c19=true, c20= 'a', c21= '123456789a', 1200c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext', 1201c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext', 1202c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday'; 1203insert into t9 1204set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9, 1205c10= 9, c11= 9, c12 = 9, 1206c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11', 1207c16= '11:11:11', c17= '2004', 1208c18= 1, c19=false, c20= 'a', c21= '123456789a', 1209c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext', 1210c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext', 1211c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday'; 1212commit ; 1213prepare stmt1 from 'delete from t1 where a=2' ; 1214execute stmt1; 1215select a,b from t1 where a=2; 1216a b 1217execute stmt1; 1218insert into t1 values(0,NULL); 1219set @arg00=NULL; 1220prepare stmt1 from 'delete from t1 where b=?' ; 1221execute stmt1 using @arg00; 1222select a,b from t1 where b is NULL ; 1223a b 12240 NULL 1225set @arg00='one'; 1226execute stmt1 using @arg00; 1227select a,b from t1 where b=@arg00; 1228a b 1229prepare stmt1 from 'truncate table t1' ; 1230test_sequence 1231------ update tests ------ 1232delete from t1 ; 1233insert into t1 values (1,'one'); 1234insert into t1 values (2,'two'); 1235insert into t1 values (3,'three'); 1236insert into t1 values (4,'four'); 1237commit ; 1238delete from t9 ; 1239insert into t9 1240set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1, 1241c10= 1, c11= 1, c12 = 1, 1242c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11', 1243c16= '11:11:11', c17= '2004', 1244c18= 1, c19=true, c20= 'a', c21= '123456789a', 1245c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext', 1246c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext', 1247c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday'; 1248insert into t9 1249set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9, 1250c10= 9, c11= 9, c12 = 9, 1251c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11', 1252c16= '11:11:11', c17= '2004', 1253c18= 1, c19=false, c20= 'a', c21= '123456789a', 1254c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext', 1255c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext', 1256c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday'; 1257commit ; 1258prepare stmt1 from 'update t1 set b=''a=two'' where a=2' ; 1259execute stmt1; 1260select a,b from t1 where a=2; 1261a b 12622 a=two 1263execute stmt1; 1264select a,b from t1 where a=2; 1265a b 12662 a=two 1267set @arg00=NULL; 1268prepare stmt1 from 'update t1 set b=? where a=2' ; 1269execute stmt1 using @arg00; 1270select a,b from t1 where a=2; 1271a b 12722 NULL 1273set @arg00='two'; 1274execute stmt1 using @arg00; 1275select a,b from t1 where a=2; 1276a b 12772 two 1278set @arg00=2; 1279prepare stmt1 from 'update t1 set b=NULL where a=?' ; 1280execute stmt1 using @arg00; 1281select a,b from t1 where a=@arg00; 1282a b 12832 NULL 1284update t1 set b='two' where a=@arg00; 1285set @arg00=2000; 1286execute stmt1 using @arg00; 1287select a,b from t1 where a=@arg00; 1288a b 1289set @arg00=2; 1290set @arg01=22; 1291prepare stmt1 from 'update t1 set a=? where a=?' ; 1292execute stmt1 using @arg00, @arg00; 1293select a,b from t1 where a=@arg00; 1294a b 12952 two 1296execute stmt1 using @arg01, @arg00; 1297select a,b from t1 where a=@arg01; 1298a b 129922 two 1300execute stmt1 using @arg00, @arg01; 1301select a,b from t1 where a=@arg00; 1302a b 13032 two 1304set @arg00=NULL; 1305set @arg01=2; 1306set sql_mode = ''; 1307execute stmt1 using @arg00, @arg01; 1308Warnings: 1309Warning 1048 Column 'a' cannot be null 1310set sql_mode = default; 1311select a,b from t1 order by a; 1312a b 13130 two 13141 one 13153 three 13164 four 1317set @arg00=0; 1318execute stmt1 using @arg01, @arg00; 1319select a,b from t1 order by a; 1320a b 13211 one 13222 two 13233 three 13244 four 1325set @arg00=23; 1326set @arg01='two'; 1327set @arg02=2; 1328set @arg03='two'; 1329set @arg04=2; 1330drop table if exists t2; 1331create table t2 as select a,b from t1 ; 1332prepare stmt1 from 'update t1 set a=? where b=? 1333 and a in (select ? from t2 1334 where b = ? or a = ?)'; 1335execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ; 1336affected rows: 1 1337info: Rows matched: 1 Changed: 1 Warnings: 0 1338select a,b from t1 where a = @arg00 ; 1339a b 134023 two 1341prepare stmt1 from 'update t1 set a=? where b=? 1342 and a not in (select ? from t2 1343 where b = ? or a = ?)'; 1344execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ; 1345affected rows: 1 1346info: Rows matched: 1 Changed: 1 Warnings: 0 1347select a,b from t1 order by a ; 1348a b 13491 one 13502 two 13513 three 13524 four 1353drop table t2 ; 1354create table t2 1355( 1356a int, b varchar(30), 1357primary key(a) 1358) engine = 'MYISAM' ; 1359insert into t2(a,b) select a, b from t1 ; 1360prepare stmt1 from 'update t1 set a=? where b=? 1361 and a in (select ? from t2 1362 where b = ? or a = ?)'; 1363execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ; 1364affected rows: 1 1365info: Rows matched: 1 Changed: 1 Warnings: 0 1366select a,b from t1 where a = @arg00 ; 1367a b 136823 two 1369prepare stmt1 from 'update t1 set a=? where b=? 1370 and a not in (select ? from t2 1371 where b = ? or a = ?)'; 1372execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ; 1373affected rows: 1 1374info: Rows matched: 1 Changed: 1 Warnings: 0 1375select a,b from t1 order by a ; 1376a b 13771 one 13782 two 13793 three 13804 four 1381drop table t2 ; 1382set @arg00=1; 1383prepare stmt1 from 'update t1 set b=''bla'' 1384where a=2 1385limit 1'; 1386execute stmt1 ; 1387select a,b from t1 where b = 'bla' ; 1388a b 13892 bla 1390prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?'; 1391execute stmt1 using @arg00; 1392test_sequence 1393------ insert tests ------ 1394delete from t1 ; 1395insert into t1 values (1,'one'); 1396insert into t1 values (2,'two'); 1397insert into t1 values (3,'three'); 1398insert into t1 values (4,'four'); 1399commit ; 1400delete from t9 ; 1401insert into t9 1402set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1, 1403c10= 1, c11= 1, c12 = 1, 1404c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11', 1405c16= '11:11:11', c17= '2004', 1406c18= 1, c19=true, c20= 'a', c21= '123456789a', 1407c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext', 1408c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext', 1409c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday'; 1410insert into t9 1411set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9, 1412c10= 9, c11= 9, c12 = 9, 1413c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11', 1414c16= '11:11:11', c17= '2004', 1415c18= 1, c19=false, c20= 'a', c21= '123456789a', 1416c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext', 1417c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext', 1418c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday'; 1419commit ; 1420prepare stmt1 from 'insert into t1 values(5, ''five'' )'; 1421execute stmt1; 1422select a,b from t1 where a = 5; 1423a b 14245 five 1425set @arg00='six' ; 1426prepare stmt1 from 'insert into t1 values(6, ? )'; 1427execute stmt1 using @arg00; 1428select a,b from t1 where b = @arg00; 1429a b 14306 six 1431execute stmt1 using @arg00; 1432ERROR 23000: Duplicate entry '6' for key 'PRIMARY' 1433set @arg00=NULL ; 1434prepare stmt1 from 'insert into t1 values(0, ? )'; 1435execute stmt1 using @arg00; 1436select a,b from t1 where b is NULL; 1437a b 14380 NULL 1439set @arg00=8 ; 1440set @arg01='eight' ; 1441prepare stmt1 from 'insert into t1 values(?, ? )'; 1442execute stmt1 using @arg00, @arg01 ; 1443select a,b from t1 where b = @arg01; 1444a b 14458 eight 1446set @NULL= null ; 1447set @arg00= 'abc' ; 1448execute stmt1 using @NULL, @NULL ; 1449ERROR 23000: Column 'a' cannot be null 1450execute stmt1 using @NULL, @NULL ; 1451ERROR 23000: Column 'a' cannot be null 1452execute stmt1 using @NULL, @arg00 ; 1453ERROR 23000: Column 'a' cannot be null 1454execute stmt1 using @NULL, @arg00 ; 1455ERROR 23000: Column 'a' cannot be null 1456set @arg01= 10000 + 2 ; 1457execute stmt1 using @arg01, @arg00 ; 1458set @arg01= 10000 + 1 ; 1459execute stmt1 using @arg01, @arg00 ; 1460select * from t1 where a > 10000 order by a ; 1461a b 146210001 abc 146310002 abc 1464delete from t1 where a > 10000 ; 1465set @arg01= 10000 + 2 ; 1466execute stmt1 using @arg01, @NULL ; 1467set @arg01= 10000 + 1 ; 1468execute stmt1 using @arg01, @NULL ; 1469select * from t1 where a > 10000 order by a ; 1470a b 147110001 NULL 147210002 NULL 1473delete from t1 where a > 10000 ; 1474set @arg01= 10000 + 10 ; 1475execute stmt1 using @arg01, @arg01 ; 1476set @arg01= 10000 + 9 ; 1477execute stmt1 using @arg01, @arg01 ; 1478set @arg01= 10000 + 8 ; 1479execute stmt1 using @arg01, @arg01 ; 1480set @arg01= 10000 + 7 ; 1481execute stmt1 using @arg01, @arg01 ; 1482set @arg01= 10000 + 6 ; 1483execute stmt1 using @arg01, @arg01 ; 1484set @arg01= 10000 + 5 ; 1485execute stmt1 using @arg01, @arg01 ; 1486set @arg01= 10000 + 4 ; 1487execute stmt1 using @arg01, @arg01 ; 1488set @arg01= 10000 + 3 ; 1489execute stmt1 using @arg01, @arg01 ; 1490set @arg01= 10000 + 2 ; 1491execute stmt1 using @arg01, @arg01 ; 1492set @arg01= 10000 + 1 ; 1493execute stmt1 using @arg01, @arg01 ; 1494select * from t1 where a > 10000 order by a ; 1495a b 149610001 10001 149710002 10002 149810003 10003 149910004 10004 150010005 10005 150110006 10006 150210007 10007 150310008 10008 150410009 10009 150510010 10010 1506delete from t1 where a > 10000 ; 1507set @arg00=81 ; 1508set @arg01='8-1' ; 1509set @arg02=82 ; 1510set @arg03='8-2' ; 1511prepare stmt1 from 'insert into t1 values(?,?),(?,?)'; 1512execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; 1513select a,b from t1 where a in (@arg00,@arg02) ; 1514a b 151581 8-1 151682 8-2 1517set @arg00=9 ; 1518set @arg01='nine' ; 1519prepare stmt1 from 'insert into t1 set a=?, b=? '; 1520execute stmt1 using @arg00, @arg01 ; 1521select a,b from t1 where a = @arg00 ; 1522a b 15239 nine 1524set @arg00=6 ; 1525set @arg01=1 ; 1526prepare stmt1 from 'insert into t1 set a=?, b=''sechs'' 1527 on duplicate key update a=a + ?, b=concat(b,''modified'') '; 1528execute stmt1 using @arg00, @arg01; 1529select * from t1 order by a; 1530a b 15310 NULL 15321 one 15332 two 15343 three 15354 four 15365 five 15377 sixmodified 15388 eight 15399 nine 154081 8-1 154182 8-2 1542set @arg00=81 ; 1543set @arg01=1 ; 1544execute stmt1 using @arg00, @arg01; 1545ERROR 23000: Duplicate entry '82' for key 'PRIMARY' 1546drop table if exists t2 ; 1547create table t2 (id int auto_increment primary key) 1548ENGINE= 'MYISAM' ; 1549prepare stmt1 from ' select last_insert_id() ' ; 1550insert into t2 values (NULL) ; 1551execute stmt1 ; 1552last_insert_id() 15531 1554insert into t2 values (NULL) ; 1555execute stmt1 ; 1556last_insert_id() 15572 1558drop table t2 ; 1559set @1000=1000 ; 1560set @x1000_2="x1000_2" ; 1561set @x1000_3="x1000_3" ; 1562set @x1000="x1000" ; 1563set @1100=1100 ; 1564set @x1100="x1100" ; 1565set @100=100 ; 1566set @updated="updated" ; 1567insert into t1 values(1000,'x1000_1') ; 1568insert into t1 values(@1000,@x1000_2),(@1000,@x1000_3) 1569on duplicate key update a = a + @100, b = concat(b,@updated) ; 1570select a,b from t1 where a >= 1000 order by a ; 1571a b 15721000 x1000_3 15731100 x1000_1updated 1574delete from t1 where a >= 1000 ; 1575insert into t1 values(1000,'x1000_1') ; 1576prepare stmt1 from ' insert into t1 values(?,?),(?,?) 1577 on duplicate key update a = a + ?, b = concat(b,?) '; 1578execute stmt1 using @1000, @x1000_2, @1000, @x1000_3, @100, @updated ; 1579select a,b from t1 where a >= 1000 order by a ; 1580a b 15811000 x1000_3 15821100 x1000_1updated 1583delete from t1 where a >= 1000 ; 1584insert into t1 values(1000,'x1000_1') ; 1585execute stmt1 using @1000, @x1000_2, @1100, @x1000_3, @100, @updated ; 1586select a,b from t1 where a >= 1000 order by a ; 1587a b 15881200 x1000_1updatedupdated 1589delete from t1 where a >= 1000 ; 1590prepare stmt1 from ' replace into t1 (a,b) select 100, ''hundred'' '; 1591execute stmt1; 1592execute stmt1; 1593execute stmt1; 1594test_sequence 1595------ multi table tests ------ 1596delete from t1 ; 1597delete from t9 ; 1598insert into t1(a,b) values (1, 'one'), (2, 'two'), (3, 'three') ; 1599insert into t9 (c1,c21) 1600values (1, 'one'), (2, 'two'), (3, 'three') ; 1601prepare stmt_delete from " delete t1, t9 1602 from t1, t9 where t1.a=t9.c1 and t1.b='updated' "; 1603prepare stmt_update from " update t1, t9 1604 set t1.b='updated', t9.c21='updated' 1605 where t1.a=t9.c1 and t1.a=? "; 1606prepare stmt_select1 from " select a, b from t1 order by a" ; 1607prepare stmt_select2 from " select c1, c21 from t9 order by c1" ; 1608set @arg00= 1 ; 1609execute stmt_update using @arg00 ; 1610execute stmt_delete ; 1611execute stmt_select1 ; 1612a b 16132 two 16143 three 1615execute stmt_select2 ; 1616c1 c21 16172 two 16183 three 1619set @arg00= @arg00 + 1 ; 1620execute stmt_update using @arg00 ; 1621execute stmt_delete ; 1622execute stmt_select1 ; 1623a b 16243 three 1625execute stmt_select2 ; 1626c1 c21 16273 three 1628set @arg00= @arg00 + 1 ; 1629execute stmt_update using @arg00 ; 1630execute stmt_delete ; 1631execute stmt_select1 ; 1632a b 1633execute stmt_select2 ; 1634c1 c21 1635set @arg00= @arg00 + 1 ; 1636delete from t1 ; 1637insert into t1 values (1,'one'); 1638insert into t1 values (2,'two'); 1639insert into t1 values (3,'three'); 1640insert into t1 values (4,'four'); 1641commit ; 1642delete from t9 ; 1643insert into t9 1644set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1, 1645c10= 1, c11= 1, c12 = 1, 1646c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11', 1647c16= '11:11:11', c17= '2004', 1648c18= 1, c19=true, c20= 'a', c21= '123456789a', 1649c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext', 1650c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext', 1651c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday'; 1652insert into t9 1653set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9, 1654c10= 9, c11= 9, c12 = 9, 1655c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11', 1656c16= '11:11:11', c17= '2004', 1657c18= 1, c19=false, c20= 'a', c21= '123456789a', 1658c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext', 1659c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext', 1660c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday'; 1661commit ; 1662insert into t1 values(0,NULL) ; 1663set @duplicate='duplicate ' ; 1664set @1000=1000 ; 1665set @5=5 ; 1666select a,b from t1 where a < 5 order by a ; 1667a b 16680 NULL 16691 one 16702 two 16713 three 16724 four 1673insert into t1 select a + @1000, concat(@duplicate,b) from t1 1674where a < @5 ; 1675affected rows: 5 1676info: Records: 5 Duplicates: 0 Warnings: 0 1677select a,b from t1 where a >= 1000 order by a ; 1678a b 16791000 NULL 16801001 duplicate one 16811002 duplicate two 16821003 duplicate three 16831004 duplicate four 1684delete from t1 where a >= 1000 ; 1685prepare stmt1 from ' insert into t1 select a + ?, concat(?,b) from t1 1686where a < ? ' ; 1687execute stmt1 using @1000, @duplicate, @5; 1688affected rows: 5 1689info: Records: 5 Duplicates: 0 Warnings: 0 1690select a,b from t1 where a >= 1000 order by a ; 1691a b 16921000 NULL 16931001 duplicate one 16941002 duplicate two 16951003 duplicate three 16961004 duplicate four 1697delete from t1 where a >= 1000 ; 1698set @1=1 ; 1699set @2=2 ; 1700set @100=100 ; 1701set @float=1.00; 1702set @five='five' ; 1703drop table if exists t2; 1704create table t2 like t1 ; 1705insert into t2 (b,a) 1706select @duplicate, sum(first.a) from t1 first, t1 second 1707where first.a <> @5 and second.b = first.b 1708and second.b <> @five 1709group by second.b 1710having sum(second.a) > @2 1711union 1712select b, a + @100 from t1 1713where (a,b) in ( select sqrt(a+@1)+CAST(@float AS signed),b 1714from t1); 1715affected rows: 3 1716info: Records: 3 Duplicates: 0 Warnings: 0 1717select a,b from t2 order by a ; 1718a b 17193 duplicate 17204 duplicate 1721103 three 1722delete from t2 ; 1723prepare stmt1 from ' insert into t2 (b,a) 1724select ?, sum(first.a) 1725 from t1 first, t1 second 1726 where first.a <> ? and second.b = first.b and second.b <> ? 1727 group by second.b 1728 having sum(second.a) > ? 1729union 1730select b, a + ? from t1 1731 where (a,b) in ( select sqrt(a+?)+CAST(? AS signed),b 1732 from t1 ) ' ; 1733execute stmt1 using @duplicate, @5, @five, @2, @100, @1, @float ; 1734affected rows: 3 1735info: Records: 3 Duplicates: 0 Warnings: 0 1736select a,b from t2 order by a ; 1737a b 17383 duplicate 17394 duplicate 1740103 three 1741drop table t2; 1742drop table if exists t5 ; 1743set @arg01= 8; 1744set @arg02= 8.0; 1745set @arg03= 80.00000000000e-1; 1746set @arg04= 'abc' ; 1747set @arg05= CAST('abc' as binary) ; 1748set @arg06= '1991-08-05' ; 1749set @arg07= CAST('1991-08-05' as date); 1750set @arg08= '1991-08-05 01:01:01' ; 1751set @arg09= CAST('1991-08-05 01:01:01' as datetime) ; 1752set @arg10= unix_timestamp('1991-01-01 01:01:01'); 1753set @arg11= YEAR('1991-01-01 01:01:01'); 1754set @arg12= 8 ; 1755set @arg12= NULL ; 1756set @arg13= 8.0 ; 1757set @arg13= NULL ; 1758set @arg14= 'abc'; 1759set @arg14= NULL ; 1760set @arg15= CAST('abc' as binary) ; 1761set @arg15= NULL ; 1762create table t5 engine = MyISAM as select 17638 as const01, @arg01 as param01, 17648.0 as const02, @arg02 as param02, 176580.00000000000e-1 as const03, @arg03 as param03, 1766'abc' as const04, @arg04 as param04, 1767CAST('abc' as binary) as const05, @arg05 as param05, 1768'1991-08-05' as const06, @arg06 as param06, 1769CAST('1991-08-05' as date) as const07, @arg07 as param07, 1770'1991-08-05 01:01:01' as const08, @arg08 as param08, 1771CAST('1991-08-05 01:01:01' as datetime) as const09, @arg09 as param09, 1772unix_timestamp('1991-01-01 01:01:01') as const10, @arg10 as param10, 1773YEAR('1991-01-01 01:01:01') as const11, @arg11 as param11, 1774NULL as const12, @arg12 as param12, 1775@arg13 as param13, 1776@arg14 as param14, 1777@arg15 as param15; 1778show create table t5 ; 1779Table Create Table 1780t5 CREATE TABLE `t5` ( 1781 `const01` int(1) NOT NULL, 1782 `param01` bigint(20) DEFAULT NULL, 1783 `const02` decimal(2,1) NOT NULL, 1784 `param02` decimal(65,38) DEFAULT NULL, 1785 `const03` double NOT NULL, 1786 `param03` double DEFAULT NULL, 1787 `const04` varchar(3) NOT NULL, 1788 `param04` longtext DEFAULT NULL, 1789 `const05` varbinary(3) DEFAULT NULL, 1790 `param05` longblob DEFAULT NULL, 1791 `const06` varchar(10) NOT NULL, 1792 `param06` longtext DEFAULT NULL, 1793 `const07` date DEFAULT NULL, 1794 `param07` longtext DEFAULT NULL, 1795 `const08` varchar(19) NOT NULL, 1796 `param08` longtext DEFAULT NULL, 1797 `const09` datetime DEFAULT NULL, 1798 `param09` longtext DEFAULT NULL, 1799 `const10` bigint(17) DEFAULT NULL, 1800 `param10` bigint(20) DEFAULT NULL, 1801 `const11` int(4) DEFAULT NULL, 1802 `param11` bigint(20) DEFAULT NULL, 1803 `const12` binary(0) DEFAULT NULL, 1804 `param12` bigint(20) DEFAULT NULL, 1805 `param13` decimal(65,38) DEFAULT NULL, 1806 `param14` longtext DEFAULT NULL, 1807 `param15` longblob DEFAULT NULL 1808) ENGINE=MyISAM DEFAULT CHARSET=latin1 1809select * from t5 ; 1810Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1811def test t5 t5 const01 const01 3 1 1 N 36865 0 63 1812def test t5 t5 param01 param01 8 20 1 Y 32768 0 63 1813def test t5 t5 const02 const02 246 4 3 N 36865 1 63 1814def test t5 t5 param02 param02 246 67 40 Y 32768 38 63 1815def test t5 t5 const03 const03 5 17 1 N 36865 31 63 1816def test t5 t5 param03 param03 5 23 1 Y 32768 31 63 1817def test t5 t5 const04 const04 253 3 3 N 4097 0 8 1818def test t5 t5 param04 param04 252 4294967295 3 Y 16 0 8 1819def test t5 t5 const05 const05 253 3 3 Y 128 0 63 1820def test t5 t5 param05 param05 252 4294967295 3 Y 144 0 63 1821def test t5 t5 const06 const06 253 10 10 N 4097 0 8 1822def test t5 t5 param06 param06 252 4294967295 10 Y 16 0 8 1823def test t5 t5 const07 const07 10 10 10 Y 128 0 63 1824def test t5 t5 param07 param07 252 4294967295 10 Y 16 0 8 1825def test t5 t5 const08 const08 253 19 19 N 4097 0 8 1826def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 1827def test t5 t5 const09 const09 12 19 19 Y 128 0 63 1828def test t5 t5 param09 param09 252 4294967295 19 Y 16 0 8 1829def test t5 t5 const10 const10 8 17 9 Y 32768 0 63 1830def test t5 t5 param10 param10 8 20 9 Y 32768 0 63 1831def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 1832def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 1833def test t5 t5 const12 const12 254 0 0 Y 128 0 63 1834def test t5 t5 param12 param12 8 20 0 Y 32768 0 63 1835def test t5 t5 param13 param13 246 67 0 Y 32768 38 63 1836def test t5 t5 param14 param14 252 4294967295 0 Y 16 0 8 1837def test t5 t5 param15 param15 252 4294967295 0 Y 144 0 63 1838const01 8 1839param01 8 1840const02 8.0 1841param02 8.00000000000000000000000000000000000000 1842const03 8 1843param03 8 1844const04 abc 1845param04 abc 1846const05 abc 1847param05 abc 1848const06 1991-08-05 1849param06 1991-08-05 1850const07 1991-08-05 1851param07 1991-08-05 1852const08 1991-08-05 01:01:01 1853param08 1991-08-05 01:01:01 1854const09 1991-08-05 01:01:01 1855param09 1991-08-05 01:01:01 1856const10 662680861 1857param10 662680861 1858const11 1991 1859param11 1991 1860const12 NULL 1861param12 NULL 1862param13 NULL 1863param14 NULL 1864param15 NULL 1865drop table t5 ; 1866test_sequence 1867------ data type conversion tests ------ 1868delete from t1 ; 1869insert into t1 values (1,'one'); 1870insert into t1 values (2,'two'); 1871insert into t1 values (3,'three'); 1872insert into t1 values (4,'four'); 1873commit ; 1874delete from t9 ; 1875insert into t9 1876set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1, 1877c10= 1, c11= 1, c12 = 1, 1878c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11', 1879c16= '11:11:11', c17= '2004', 1880c18= 1, c19=true, c20= 'a', c21= '123456789a', 1881c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext', 1882c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext', 1883c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday'; 1884insert into t9 1885set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9, 1886c10= 9, c11= 9, c12 = 9, 1887c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11', 1888c16= '11:11:11', c17= '2004', 1889c18= 1, c19=false, c20= 'a', c21= '123456789a', 1890c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext', 1891c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext', 1892c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday'; 1893commit ; 1894insert into t9 set c1= 0, c15= '1991-01-01 01:01:01' ; 1895select * from t9 order by c1 ; 1896c1 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 18970 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 18981 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 18999 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 1900test_sequence 1901------ select @parameter:= column ------ 1902prepare full_info from "select @arg01, @arg02, @arg03, @arg04, 1903 @arg05, @arg06, @arg07, @arg08, 1904 @arg09, @arg10, @arg11, @arg12, 1905 @arg13, @arg14, @arg15, @arg16, 1906 @arg17, @arg18, @arg19, @arg20, 1907 @arg21, @arg22, @arg23, @arg24, 1908 @arg25, @arg26, @arg27, @arg28, 1909 @arg29, @arg30, @arg31, @arg32" ; 1910select @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4, 1911@arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8, 1912@arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12, 1913@arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16, 1914@arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20, 1915@arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24, 1916@arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28, 1917@arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32 1918from t9 where c1= 1 ; 1919@arg01:= c1 @arg02:= c2 @arg03:= c3 @arg04:= c4 @arg05:= c5 @arg06:= c6 @arg07:= c7 @arg08:= c8 @arg09:= c9 @arg10:= c10 @arg11:= c11 @arg12:= c12 @arg13:= c13 @arg14:= c14 @arg15:= c15 @arg16:= c16 @arg17:= c17 @arg18:= c18 @arg19:= c19 @arg20:= c20 @arg21:= c21 @arg22:= c22 @arg23:= c23 @arg24:= c24 @arg25:= c25 @arg26:= c26 @arg27:= c27 @arg28:= c28 @arg29:= c29 @arg30:= c30 @arg31:= c31 @arg32:= c32 19201 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 1921execute full_info ; 1922Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1923def @arg01 8 20 1 Y 32896 0 63 1924def @arg02 8 20 1 Y 32896 0 63 1925def @arg03 8 20 1 Y 32896 0 63 1926def @arg04 8 20 1 Y 32896 0 63 1927def @arg05 8 20 1 Y 32896 0 63 1928def @arg06 8 20 1 Y 32896 0 63 1929def @arg07 5 23 1 Y 32896 31 63 1930def @arg08 5 23 1 Y 32896 31 63 1931def @arg09 5 23 1 Y 32896 31 63 1932def @arg10 5 23 1 Y 32896 31 63 1933def @arg11 246 83 6 Y 32896 38 63 1934def @arg12 246 83 6 Y 32896 38 63 1935def @arg13 251 16777215 10 Y 0 39 8 1936def @arg14 251 16777215 19 Y 0 39 8 1937def @arg15 251 16777215 19 Y 0 39 8 1938def @arg16 251 16777215 8 Y 0 39 8 1939def @arg17 8 20 4 Y 32928 0 63 1940def @arg18 8 20 1 Y 32896 0 63 1941def @arg19 8 20 1 Y 32896 0 63 1942def @arg20 251 16777215 1 Y 0 39 8 1943def @arg21 251 16777215 10 Y 0 39 8 1944def @arg22 251 16777215 30 Y 0 39 8 1945def @arg23 251 16777215 8 Y 128 39 63 1946def @arg24 251 16777215 8 Y 0 39 8 1947def @arg25 251 16777215 4 Y 128 39 63 1948def @arg26 251 16777215 4 Y 0 39 8 1949def @arg27 251 16777215 10 Y 128 39 63 1950def @arg28 251 16777215 10 Y 0 39 8 1951def @arg29 251 16777215 8 Y 128 39 63 1952def @arg30 251 16777215 8 Y 0 39 8 1953def @arg31 251 16777215 3 Y 0 39 8 1954def @arg32 251 16777215 6 Y 0 39 8 1955@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32 19561 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 1957select @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4, 1958@arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8, 1959@arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12, 1960@arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16, 1961@arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20, 1962@arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24, 1963@arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28, 1964@arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32 1965from t9 where c1= 0 ; 1966@arg01:= c1 @arg02:= c2 @arg03:= c3 @arg04:= c4 @arg05:= c5 @arg06:= c6 @arg07:= c7 @arg08:= c8 @arg09:= c9 @arg10:= c10 @arg11:= c11 @arg12:= c12 @arg13:= c13 @arg14:= c14 @arg15:= c15 @arg16:= c16 @arg17:= c17 @arg18:= c18 @arg19:= c19 @arg20:= c20 @arg21:= c21 @arg22:= c22 @arg23:= c23 @arg24:= c24 @arg25:= c25 @arg26:= c26 @arg27:= c27 @arg28:= c28 @arg29:= c29 @arg30:= c30 @arg31:= c31 @arg32:= c32 19670 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 1968execute full_info ; 1969Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 1970def @arg01 8 20 1 Y 32896 0 63 1971def @arg02 8 20 0 Y 32896 0 63 1972def @arg03 8 20 0 Y 32896 0 63 1973def @arg04 8 20 0 Y 32896 0 63 1974def @arg05 8 20 0 Y 32896 0 63 1975def @arg06 8 20 0 Y 32896 0 63 1976def @arg07 5 23 0 Y 32896 31 63 1977def @arg08 5 23 0 Y 32896 31 63 1978def @arg09 5 23 0 Y 32896 31 63 1979def @arg10 5 23 0 Y 32896 31 63 1980def @arg11 246 83 0 Y 32896 38 63 1981def @arg12 246 83 0 Y 32896 38 63 1982def @arg13 251 16777215 0 Y 0 39 8 1983def @arg14 251 16777215 0 Y 0 39 8 1984def @arg15 251 16777215 19 Y 0 39 8 1985def @arg16 251 16777215 0 Y 0 39 8 1986def @arg17 8 20 0 Y 32928 0 63 1987def @arg18 8 20 0 Y 32896 0 63 1988def @arg19 8 20 0 Y 32896 0 63 1989def @arg20 251 16777215 0 Y 0 39 8 1990def @arg21 251 16777215 0 Y 0 39 8 1991def @arg22 251 16777215 0 Y 0 39 8 1992def @arg23 251 16777215 0 Y 128 39 63 1993def @arg24 251 16777215 0 Y 0 39 8 1994def @arg25 251 16777215 0 Y 128 39 63 1995def @arg26 251 16777215 0 Y 0 39 8 1996def @arg27 251 16777215 0 Y 128 39 63 1997def @arg28 251 16777215 0 Y 0 39 8 1998def @arg29 251 16777215 0 Y 128 39 63 1999def @arg30 251 16777215 0 Y 0 39 8 2000def @arg31 251 16777215 0 Y 0 39 8 2001def @arg32 251 16777215 0 Y 0 39 8 2002@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32 20030 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 2004prepare stmt1 from "select 2005 @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4, 2006 @arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8, 2007 @arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12, 2008 @arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16, 2009 @arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20, 2010 @arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24, 2011 @arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28, 2012 @arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32 2013from t9 where c1= ?" ; 2014set @my_key= 1 ; 2015execute stmt1 using @my_key ; 2016@arg01:= c1 @arg02:= c2 @arg03:= c3 @arg04:= c4 @arg05:= c5 @arg06:= c6 @arg07:= c7 @arg08:= c8 @arg09:= c9 @arg10:= c10 @arg11:= c11 @arg12:= c12 @arg13:= c13 @arg14:= c14 @arg15:= c15 @arg16:= c16 @arg17:= c17 @arg18:= c18 @arg19:= c19 @arg20:= c20 @arg21:= c21 @arg22:= c22 @arg23:= c23 @arg24:= c24 @arg25:= c25 @arg26:= c26 @arg27:= c27 @arg28:= c28 @arg29:= c29 @arg30:= c30 @arg31:= c31 @arg32:= c32 20171 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 2018execute full_info ; 2019Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2020def @arg01 8 20 1 Y 32896 0 63 2021def @arg02 8 20 1 Y 32896 0 63 2022def @arg03 8 20 1 Y 32896 0 63 2023def @arg04 8 20 1 Y 32896 0 63 2024def @arg05 8 20 1 Y 32896 0 63 2025def @arg06 8 20 1 Y 32896 0 63 2026def @arg07 5 23 1 Y 32896 31 63 2027def @arg08 5 23 1 Y 32896 31 63 2028def @arg09 5 23 1 Y 32896 31 63 2029def @arg10 5 23 1 Y 32896 31 63 2030def @arg11 246 83 6 Y 32896 38 63 2031def @arg12 246 83 6 Y 32896 38 63 2032def @arg13 251 16777215 10 Y 0 39 8 2033def @arg14 251 16777215 19 Y 0 39 8 2034def @arg15 251 16777215 19 Y 0 39 8 2035def @arg16 251 16777215 8 Y 0 39 8 2036def @arg17 8 20 4 Y 32928 0 63 2037def @arg18 8 20 1 Y 32896 0 63 2038def @arg19 8 20 1 Y 32896 0 63 2039def @arg20 251 16777215 1 Y 0 39 8 2040def @arg21 251 16777215 10 Y 0 39 8 2041def @arg22 251 16777215 30 Y 0 39 8 2042def @arg23 251 16777215 8 Y 128 39 63 2043def @arg24 251 16777215 8 Y 0 39 8 2044def @arg25 251 16777215 4 Y 128 39 63 2045def @arg26 251 16777215 4 Y 0 39 8 2046def @arg27 251 16777215 10 Y 128 39 63 2047def @arg28 251 16777215 10 Y 0 39 8 2048def @arg29 251 16777215 8 Y 128 39 63 2049def @arg30 251 16777215 8 Y 0 39 8 2050def @arg31 251 16777215 3 Y 0 39 8 2051def @arg32 251 16777215 6 Y 0 39 8 2052@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32 20531 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 2054set @my_key= 0 ; 2055execute stmt1 using @my_key ; 2056@arg01:= c1 @arg02:= c2 @arg03:= c3 @arg04:= c4 @arg05:= c5 @arg06:= c6 @arg07:= c7 @arg08:= c8 @arg09:= c9 @arg10:= c10 @arg11:= c11 @arg12:= c12 @arg13:= c13 @arg14:= c14 @arg15:= c15 @arg16:= c16 @arg17:= c17 @arg18:= c18 @arg19:= c19 @arg20:= c20 @arg21:= c21 @arg22:= c22 @arg23:= c23 @arg24:= c24 @arg25:= c25 @arg26:= c26 @arg27:= c27 @arg28:= c28 @arg29:= c29 @arg30:= c30 @arg31:= c31 @arg32:= c32 20570 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 2058execute full_info ; 2059Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2060def @arg01 8 20 1 Y 32896 0 63 2061def @arg02 8 20 0 Y 32896 0 63 2062def @arg03 8 20 0 Y 32896 0 63 2063def @arg04 8 20 0 Y 32896 0 63 2064def @arg05 8 20 0 Y 32896 0 63 2065def @arg06 8 20 0 Y 32896 0 63 2066def @arg07 5 23 0 Y 32896 31 63 2067def @arg08 5 23 0 Y 32896 31 63 2068def @arg09 5 23 0 Y 32896 31 63 2069def @arg10 5 23 0 Y 32896 31 63 2070def @arg11 246 83 0 Y 32896 38 63 2071def @arg12 246 83 0 Y 32896 38 63 2072def @arg13 251 16777215 0 Y 0 39 8 2073def @arg14 251 16777215 0 Y 0 39 8 2074def @arg15 251 16777215 19 Y 0 39 8 2075def @arg16 251 16777215 0 Y 0 39 8 2076def @arg17 8 20 0 Y 32928 0 63 2077def @arg18 8 20 0 Y 32896 0 63 2078def @arg19 8 20 0 Y 32896 0 63 2079def @arg20 251 16777215 0 Y 0 39 8 2080def @arg21 251 16777215 0 Y 0 39 8 2081def @arg22 251 16777215 0 Y 0 39 8 2082def @arg23 251 16777215 0 Y 128 39 63 2083def @arg24 251 16777215 0 Y 0 39 8 2084def @arg25 251 16777215 0 Y 128 39 63 2085def @arg26 251 16777215 0 Y 0 39 8 2086def @arg27 251 16777215 0 Y 128 39 63 2087def @arg28 251 16777215 0 Y 0 39 8 2088def @arg29 251 16777215 0 Y 128 39 63 2089def @arg30 251 16777215 0 Y 0 39 8 2090def @arg31 251 16777215 0 Y 0 39 8 2091def @arg32 251 16777215 0 Y 0 39 8 2092@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32 20930 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 2094prepare stmt1 from "select ? := c1 from t9 where c1= 1" ; 2095ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':= c1 from t9 where c1= 1' at line 1 2096test_sequence 2097------ select column, .. into @parm,.. ------ 2098select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, 2099c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, 2100c25, c26, c27, c28, c29, c30, c31, c32 2101into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08, 2102@arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16, 2103@arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24, 2104@arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32 2105from t9 where c1= 1 ; 2106execute full_info ; 2107Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2108def @arg01 8 20 1 Y 32896 0 63 2109def @arg02 8 20 1 Y 32896 0 63 2110def @arg03 8 20 1 Y 32896 0 63 2111def @arg04 8 20 1 Y 32896 0 63 2112def @arg05 8 20 1 Y 32896 0 63 2113def @arg06 8 20 1 Y 32896 0 63 2114def @arg07 5 23 1 Y 32896 31 63 2115def @arg08 5 23 1 Y 32896 31 63 2116def @arg09 5 23 1 Y 32896 31 63 2117def @arg10 5 23 1 Y 32896 31 63 2118def @arg11 246 83 6 Y 32896 38 63 2119def @arg12 246 83 6 Y 32896 38 63 2120def @arg13 251 16777215 10 Y 0 39 8 2121def @arg14 251 16777215 19 Y 0 39 8 2122def @arg15 251 16777215 19 Y 0 39 8 2123def @arg16 251 16777215 8 Y 0 39 8 2124def @arg17 8 20 4 Y 32928 0 63 2125def @arg18 8 20 1 Y 32896 0 63 2126def @arg19 8 20 1 Y 32896 0 63 2127def @arg20 251 16777215 1 Y 0 39 8 2128def @arg21 251 16777215 10 Y 0 39 8 2129def @arg22 251 16777215 30 Y 0 39 8 2130def @arg23 251 16777215 8 Y 128 39 63 2131def @arg24 251 16777215 8 Y 0 39 8 2132def @arg25 251 16777215 4 Y 128 39 63 2133def @arg26 251 16777215 4 Y 0 39 8 2134def @arg27 251 16777215 10 Y 128 39 63 2135def @arg28 251 16777215 10 Y 0 39 8 2136def @arg29 251 16777215 8 Y 128 39 63 2137def @arg30 251 16777215 8 Y 0 39 8 2138def @arg31 251 16777215 3 Y 0 39 8 2139def @arg32 251 16777215 6 Y 0 39 8 2140@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32 21411 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 2142select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, 2143c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, 2144c25, c26, c27, c28, c29, c30, c31, c32 2145into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08, 2146@arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16, 2147@arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24, 2148@arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32 2149from t9 where c1= 0 ; 2150execute full_info ; 2151Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2152def @arg01 8 20 1 Y 32896 0 63 2153def @arg02 8 20 0 Y 32896 0 63 2154def @arg03 8 20 0 Y 32896 0 63 2155def @arg04 8 20 0 Y 32896 0 63 2156def @arg05 8 20 0 Y 32896 0 63 2157def @arg06 8 20 0 Y 32896 0 63 2158def @arg07 5 23 0 Y 32896 31 63 2159def @arg08 5 23 0 Y 32896 31 63 2160def @arg09 5 23 0 Y 32896 31 63 2161def @arg10 5 23 0 Y 32896 31 63 2162def @arg11 246 83 0 Y 32896 38 63 2163def @arg12 246 83 0 Y 32896 38 63 2164def @arg13 251 16777215 0 Y 0 39 8 2165def @arg14 251 16777215 0 Y 0 39 8 2166def @arg15 251 16777215 19 Y 0 39 8 2167def @arg16 251 16777215 0 Y 0 39 8 2168def @arg17 8 20 0 Y 32928 0 63 2169def @arg18 8 20 0 Y 32896 0 63 2170def @arg19 8 20 0 Y 32896 0 63 2171def @arg20 251 16777215 0 Y 0 39 8 2172def @arg21 251 16777215 0 Y 0 39 8 2173def @arg22 251 16777215 0 Y 0 39 8 2174def @arg23 251 16777215 0 Y 128 39 63 2175def @arg24 251 16777215 0 Y 0 39 8 2176def @arg25 251 16777215 0 Y 128 39 63 2177def @arg26 251 16777215 0 Y 0 39 8 2178def @arg27 251 16777215 0 Y 128 39 63 2179def @arg28 251 16777215 0 Y 0 39 8 2180def @arg29 251 16777215 0 Y 128 39 63 2181def @arg30 251 16777215 0 Y 0 39 8 2182def @arg31 251 16777215 0 Y 0 39 8 2183def @arg32 251 16777215 0 Y 0 39 8 2184@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32 21850 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 2186prepare stmt1 from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, 2187 c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, 2188 c25, c26, c27, c28, c29, c30, c31, c32 2189into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08, 2190 @arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16, 2191 @arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24, 2192 @arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32 2193from t9 where c1= ?" ; 2194set @my_key= 1 ; 2195execute stmt1 using @my_key ; 2196execute full_info ; 2197Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2198def @arg01 8 20 1 Y 32896 0 63 2199def @arg02 8 20 1 Y 32896 0 63 2200def @arg03 8 20 1 Y 32896 0 63 2201def @arg04 8 20 1 Y 32896 0 63 2202def @arg05 8 20 1 Y 32896 0 63 2203def @arg06 8 20 1 Y 32896 0 63 2204def @arg07 5 23 1 Y 32896 31 63 2205def @arg08 5 23 1 Y 32896 31 63 2206def @arg09 5 23 1 Y 32896 31 63 2207def @arg10 5 23 1 Y 32896 31 63 2208def @arg11 246 83 6 Y 32896 38 63 2209def @arg12 246 83 6 Y 32896 38 63 2210def @arg13 251 16777215 10 Y 0 39 8 2211def @arg14 251 16777215 19 Y 0 39 8 2212def @arg15 251 16777215 19 Y 0 39 8 2213def @arg16 251 16777215 8 Y 0 39 8 2214def @arg17 8 20 4 Y 32928 0 63 2215def @arg18 8 20 1 Y 32896 0 63 2216def @arg19 8 20 1 Y 32896 0 63 2217def @arg20 251 16777215 1 Y 0 39 8 2218def @arg21 251 16777215 10 Y 0 39 8 2219def @arg22 251 16777215 30 Y 0 39 8 2220def @arg23 251 16777215 8 Y 128 39 63 2221def @arg24 251 16777215 8 Y 0 39 8 2222def @arg25 251 16777215 4 Y 128 39 63 2223def @arg26 251 16777215 4 Y 0 39 8 2224def @arg27 251 16777215 10 Y 128 39 63 2225def @arg28 251 16777215 10 Y 0 39 8 2226def @arg29 251 16777215 8 Y 128 39 63 2227def @arg30 251 16777215 8 Y 0 39 8 2228def @arg31 251 16777215 3 Y 0 39 8 2229def @arg32 251 16777215 6 Y 0 39 8 2230@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32 22311 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 2232set @my_key= 0 ; 2233execute stmt1 using @my_key ; 2234execute full_info ; 2235Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 2236def @arg01 8 20 1 Y 32896 0 63 2237def @arg02 8 20 0 Y 32896 0 63 2238def @arg03 8 20 0 Y 32896 0 63 2239def @arg04 8 20 0 Y 32896 0 63 2240def @arg05 8 20 0 Y 32896 0 63 2241def @arg06 8 20 0 Y 32896 0 63 2242def @arg07 5 23 0 Y 32896 31 63 2243def @arg08 5 23 0 Y 32896 31 63 2244def @arg09 5 23 0 Y 32896 31 63 2245def @arg10 5 23 0 Y 32896 31 63 2246def @arg11 246 83 0 Y 32896 38 63 2247def @arg12 246 83 0 Y 32896 38 63 2248def @arg13 251 16777215 0 Y 0 39 8 2249def @arg14 251 16777215 0 Y 0 39 8 2250def @arg15 251 16777215 19 Y 0 39 8 2251def @arg16 251 16777215 0 Y 0 39 8 2252def @arg17 8 20 0 Y 32928 0 63 2253def @arg18 8 20 0 Y 32896 0 63 2254def @arg19 8 20 0 Y 32896 0 63 2255def @arg20 251 16777215 0 Y 0 39 8 2256def @arg21 251 16777215 0 Y 0 39 8 2257def @arg22 251 16777215 0 Y 0 39 8 2258def @arg23 251 16777215 0 Y 128 39 63 2259def @arg24 251 16777215 0 Y 0 39 8 2260def @arg25 251 16777215 0 Y 128 39 63 2261def @arg26 251 16777215 0 Y 0 39 8 2262def @arg27 251 16777215 0 Y 128 39 63 2263def @arg28 251 16777215 0 Y 0 39 8 2264def @arg29 251 16777215 0 Y 128 39 63 2265def @arg30 251 16777215 0 Y 0 39 8 2266def @arg31 251 16777215 0 Y 0 39 8 2267def @arg32 251 16777215 0 Y 0 39 8 2268@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32 22690 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 2270prepare stmt1 from "select c1 into ? from t9 where c1= 1" ; 2271ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? from t9 where c1= 1' at line 1 2272test_sequence 2273-- insert into numeric columns -- 2274insert into t9 2275( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2276values 2277( 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20 ) ; 2278set @arg00= 21 ; 2279insert into t9 2280( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2281values 2282( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2283@arg00, @arg00, @arg00, @arg00, @arg00 ) ; 2284prepare stmt1 from "insert into t9 2285 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2286values 2287 ( 22, 22, 22, 22, 22, 22, 22, 22, 22, 22, 22 )" ; 2288execute stmt1 ; 2289set @arg00= 23; 2290prepare stmt2 from "insert into t9 2291 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2292values 2293 ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2294execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2295@arg00, @arg00, @arg00, @arg00 ; 2296insert into t9 2297( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2298values 2299( 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 230030.0, 30.0, 30.0 ) ; 2301set @arg00= 31.0 ; 2302insert into t9 2303( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2304values 2305( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2306@arg00, @arg00, @arg00, @arg00, @arg00 ) ; 2307prepare stmt1 from "insert into t9 2308 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2309values 2310 ( 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 2311 32.0, 32.0, 32.0 )" ; 2312execute stmt1 ; 2313set @arg00= 33.0; 2314prepare stmt2 from "insert into t9 2315 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2316values 2317 ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2318execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2319@arg00, @arg00, @arg00, @arg00 ; 2320insert into t9 2321( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2322values 2323( '40', '40', '40', '40', '40', '40', '40', '40', 2324'40', '40', '40' ) ; 2325set @arg00= '41' ; 2326insert into t9 2327( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2328values 2329( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2330@arg00, @arg00, @arg00, @arg00, @arg00 ) ; 2331prepare stmt1 from "insert into t9 2332 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2333values 2334 ( '42', '42', '42', '42', '42', '42', '42', '42', 2335 '42', '42', '42' )" ; 2336execute stmt1 ; 2337set @arg00= '43'; 2338prepare stmt2 from "insert into t9 2339 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2340values 2341 ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2342execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2343@arg00, @arg00, @arg00, @arg00 ; 2344insert into t9 2345( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2346values 2347( CAST('50' as binary), CAST('50' as binary), 2348CAST('50' as binary), CAST('50' as binary), CAST('50' as binary), 2349CAST('50' as binary), CAST('50' as binary), CAST('50' as binary), 2350CAST('50' as binary), CAST('50' as binary), CAST('50' as binary) ) ; 2351set @arg00= CAST('51' as binary) ; 2352insert into t9 2353( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2354values 2355( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2356@arg00, @arg00, @arg00, @arg00, @arg00 ) ; 2357prepare stmt1 from "insert into t9 2358 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2359values 2360 ( CAST('52' as binary), CAST('52' as binary), 2361 CAST('52' as binary), CAST('52' as binary), CAST('52' as binary), 2362 CAST('52' as binary), CAST('52' as binary), CAST('52' as binary), 2363 CAST('52' as binary), CAST('52' as binary), CAST('52' as binary) )" ; 2364execute stmt1 ; 2365set @arg00= CAST('53' as binary) ; 2366prepare stmt2 from "insert into t9 2367 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2368values 2369 ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2370execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2371@arg00, @arg00, @arg00, @arg00 ; 2372set @arg00= 2 ; 2373set @arg00= NULL ; 2374insert into t9 2375( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2376values 2377( 60, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2378NULL, NULL, NULL ) ; 2379insert into t9 2380( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2381values 2382( 61, @arg00, @arg00, @arg00, @arg00, @arg00, 2383@arg00, @arg00, @arg00, @arg00, @arg00 ) ; 2384prepare stmt1 from "insert into t9 2385 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2386values 2387 ( 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2388 NULL, NULL, NULL )" ; 2389execute stmt1 ; 2390prepare stmt2 from "insert into t9 2391 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2392values 2393 ( 63, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2394execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2395@arg00, @arg00, @arg00, @arg00 ; 2396set @arg00= 8.0 ; 2397set @arg00= NULL ; 2398insert into t9 2399( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2400values 2401( 71, @arg00, @arg00, @arg00, @arg00, @arg00, 2402@arg00, @arg00, @arg00, @arg00, @arg00 ) ; 2403prepare stmt2 from "insert into t9 2404 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2405values 2406 ( 73, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2407execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2408@arg00, @arg00, @arg00, @arg00 ; 2409set @arg00= 'abc' ; 2410set @arg00= NULL ; 2411insert into t9 2412( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2413values 2414( 81, @arg00, @arg00, @arg00, @arg00, @arg00, 2415@arg00, @arg00, @arg00, @arg00, @arg00 ) ; 2416prepare stmt2 from "insert into t9 2417 ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2418values 2419 ( 83, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2420execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2421@arg00, @arg00, @arg00, @arg00 ; 2422select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 2423from t9 where c1 >= 20 2424order by c1 ; 2425c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c12 242620 20 20 20 20 20 20 20 20 20 20.0000 242721 21 21 21 21 21 21 21 21 21 21.0000 242822 22 22 22 22 22 22 22 22 22 22.0000 242923 23 23 23 23 23 23 23 23 23 23.0000 243030 30 30 30 30 30 30 30 30 30 30.0000 243131 31 31 31 31 31 31 31 31 31 31.0000 243232 32 32 32 32 32 32 32 32 32 32.0000 243333 33 33 33 33 33 33 33 33 33 33.0000 243440 40 40 40 40 40 40 40 40 40 40.0000 243541 41 41 41 41 41 41 41 41 41 41.0000 243642 42 42 42 42 42 42 42 42 42 42.0000 243743 43 43 43 43 43 43 43 43 43 43.0000 243850 50 50 50 50 50 50 50 50 50 50.0000 243951 51 51 51 51 51 51 51 51 51 51.0000 244052 52 52 52 52 52 52 52 52 52 52.0000 244153 53 53 53 53 53 53 53 53 53 53.0000 244260 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 244361 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 244462 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 244563 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 244671 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 244773 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 244881 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 244983 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2450test_sequence 2451-- select .. where numeric column = .. -- 2452set @arg00= 20; 2453select 'true' as found from t9 2454where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20 2455and c8= 20 and c9= 20 and c10= 20 and c12= 20; 2456found 2457true 2458select 'true' as found from t9 2459where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00 2460and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00 2461and c12= @arg00; 2462found 2463true 2464prepare stmt1 from "select 'true' as found from t9 2465where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20 2466 and c8= 20 and c9= 20 and c10= 20 and c12= 20 "; 2467execute stmt1 ; 2468found 2469true 2470prepare stmt1 from "select 'true' as found from t9 2471where c1= ? and c2= ? and c3= ? and c4= ? and c5= ? 2472 and c6= ? and c7= ? and c8= ? and c9= ? and c10= ? 2473 and c12= ? "; 2474execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2475@arg00, @arg00, @arg00, @arg00 ; 2476found 2477true 2478set @arg00= 20.0; 2479select 'true' as found from t9 2480where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0 2481and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0; 2482found 2483true 2484select 'true' as found from t9 2485where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00 2486and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00 2487and c12= @arg00; 2488found 2489true 2490prepare stmt1 from "select 'true' as found from t9 2491where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0 2492 and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0 "; 2493execute stmt1 ; 2494found 2495true 2496prepare stmt1 from "select 'true' as found from t9 2497where c1= ? and c2= ? and c3= ? and c4= ? and c5= ? 2498 and c6= ? and c7= ? and c8= ? and c9= ? and c10= ? 2499 and c12= ? "; 2500execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2501@arg00, @arg00, @arg00, @arg00 ; 2502found 2503true 2504select 'true' as found from t9 2505where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20' 2506 and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20'; 2507found 2508true 2509prepare stmt1 from "select 'true' as found from t9 2510where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20' 2511 and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20' "; 2512execute stmt1 ; 2513found 2514true 2515set @arg00= '20'; 2516select 'true' as found from t9 2517where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00 2518and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00 2519and c12= @arg00; 2520found 2521true 2522prepare stmt1 from "select 'true' as found from t9 2523where c1= ? and c2= ? and c3= ? and c4= ? and c5= ? 2524 and c6= ? and c7= ? and c8= ? and c9= ? and c10= ? 2525 and c12= ? "; 2526execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2527@arg00, @arg00, @arg00, @arg00 ; 2528found 2529true 2530select 'true' as found from t9 2531where c1= CAST('20' as binary) and c2= CAST('20' as binary) and 2532c3= CAST('20' as binary) and c4= CAST('20' as binary) and 2533c5= CAST('20' as binary) and c6= CAST('20' as binary) and 2534c7= CAST('20' as binary) and c8= CAST('20' as binary) and 2535c9= CAST('20' as binary) and c10= CAST('20' as binary) and 2536c12= CAST('20' as binary); 2537found 2538true 2539prepare stmt1 from "select 'true' as found from t9 2540where c1= CAST('20' as binary) and c2= CAST('20' as binary) and 2541 c3= CAST('20' as binary) and c4= CAST('20' as binary) and 2542 c5= CAST('20' as binary) and c6= CAST('20' as binary) and 2543 c7= CAST('20' as binary) and c8= CAST('20' as binary) and 2544 c9= CAST('20' as binary) and c10= CAST('20' as binary) and 2545 c12= CAST('20' as binary) "; 2546execute stmt1 ; 2547found 2548true 2549set @arg00= CAST('20' as binary) ; 2550select 'true' as found from t9 2551where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00 2552and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00 2553and c12= @arg00; 2554found 2555true 2556prepare stmt1 from "select 'true' as found from t9 2557where c1= ? and c2= ? and c3= ? and c4= ? and c5= ? 2558 and c6= ? and c7= ? and c8= ? and c9= ? and c10= ? 2559 and c12= ? "; 2560execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2561@arg00, @arg00, @arg00, @arg00 ; 2562found 2563true 2564delete from t9 ; 2565test_sequence 2566-- some numeric overflow experiments -- 2567prepare my_insert from "insert into t9 2568 ( c21, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 ) 2569values 2570 ( 'O', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2571prepare my_select from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 2572from t9 where c21 = 'O' "; 2573prepare my_delete from "delete from t9 where c21 = 'O' "; 2574set @arg00= 9223372036854775807 ; 2575set statement sql_mode = '' for 2576execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2577@arg00, @arg00, @arg00, @arg00, @arg00 ; 2578Warnings: 2579Warning 1264 Out of range value for column 'c1' at row 1 2580Warning 1264 Out of range value for column 'c2' at row 1 2581Warning 1264 Out of range value for column 'c3' at row 1 2582Warning 1264 Out of range value for column 'c4' at row 1 2583Warning 1264 Out of range value for column 'c5' at row 1 2584Warning 1264 Out of range value for column 'c12' at row 1 2585execute my_select ; 2586c1 127 2587c2 32767 2588c3 8388607 2589c4 2147483647 2590c5 2147483647 2591c6 9223372036854775807 2592c7 9.22337e18 2593c8 9.223372036854776e18 2594c9 9.223372036854776e18 2595c10 9.223372036854776e18 2596c12 9999.9999 2597execute my_delete ; 2598set @arg00= '9223372036854775807' ; 2599set statement sql_mode = '' for 2600execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2601@arg00, @arg00, @arg00, @arg00, @arg00 ; 2602Warnings: 2603Warning 1264 Out of range value for column 'c1' at row 1 2604Warning 1264 Out of range value for column 'c2' at row 1 2605Warning 1264 Out of range value for column 'c3' at row 1 2606Warning 1264 Out of range value for column 'c4' at row 1 2607Warning 1264 Out of range value for column 'c5' at row 1 2608Warning 1264 Out of range value for column 'c12' at row 1 2609execute my_select ; 2610c1 127 2611c2 32767 2612c3 8388607 2613c4 2147483647 2614c5 2147483647 2615c6 9223372036854775807 2616c7 9.22337e18 2617c8 9.223372036854776e18 2618c9 9.223372036854776e18 2619c10 9.223372036854776e18 2620c12 9999.9999 2621execute my_delete ; 2622set @arg00= -9223372036854775808 ; 2623set statement sql_mode = '' for 2624execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2625@arg00, @arg00, @arg00, @arg00, @arg00 ; 2626Warnings: 2627Warning 1264 Out of range value for column 'c1' at row 1 2628Warning 1264 Out of range value for column 'c2' at row 1 2629Warning 1264 Out of range value for column 'c3' at row 1 2630Warning 1264 Out of range value for column 'c4' at row 1 2631Warning 1264 Out of range value for column 'c5' at row 1 2632Warning 1264 Out of range value for column 'c12' at row 1 2633execute my_select ; 2634c1 -128 2635c2 -32768 2636c3 -8388608 2637c4 -2147483648 2638c5 -2147483648 2639c6 -9223372036854775808 2640c7 -9.22337e18 2641c8 -9.223372036854776e18 2642c9 -9.223372036854776e18 2643c10 -9.223372036854776e18 2644c12 -9999.9999 2645execute my_delete ; 2646set @arg00= '-9223372036854775808' ; 2647set statement sql_mode = '' for 2648execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2649@arg00, @arg00, @arg00, @arg00, @arg00 ; 2650Warnings: 2651Warning 1264 Out of range value for column 'c1' at row 1 2652Warning 1264 Out of range value for column 'c2' at row 1 2653Warning 1264 Out of range value for column 'c3' at row 1 2654Warning 1264 Out of range value for column 'c4' at row 1 2655Warning 1264 Out of range value for column 'c5' at row 1 2656Warning 1264 Out of range value for column 'c12' at row 1 2657execute my_select ; 2658c1 -128 2659c2 -32768 2660c3 -8388608 2661c4 -2147483648 2662c5 -2147483648 2663c6 -9223372036854775808 2664c7 -9.22337e18 2665c8 -9.223372036854776e18 2666c9 -9.223372036854776e18 2667c10 -9.223372036854776e18 2668c12 -9999.9999 2669execute my_delete ; 2670set @arg00= 1.11111111111111111111e+50 ; 2671set statement sql_mode = '' for 2672execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2673@arg00, @arg00, @arg00, @arg00, @arg00 ; 2674Warnings: 2675Warning 1264 Out of range value for column 'c1' at row 1 2676Warning 1264 Out of range value for column 'c2' at row 1 2677Warning 1264 Out of range value for column 'c3' at row 1 2678Warning 1264 Out of range value for column 'c4' at row 1 2679Warning 1264 Out of range value for column 'c5' at row 1 2680Warning 1264 Out of range value for column 'c6' at row 1 2681Warning 1264 Out of range value for column 'c7' at row 1 2682Warning 1264 Out of range value for column 'c12' at row 1 2683execute my_select ; 2684c1 127 2685c2 32767 2686c3 8388607 2687c4 2147483647 2688c5 2147483647 2689c6 9223372036854775807 2690c7 3.40282e38 2691c8 1.111111111111111e50 2692c9 1.111111111111111e50 2693c10 1.111111111111111e50 2694c12 9999.9999 2695execute my_delete ; 2696set @arg00= '1.11111111111111111111e+50' ; 2697set statement sql_mode = '' for 2698execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2699@arg00, @arg00, @arg00, @arg00, @arg00 ; 2700Warnings: 2701Warning 1264 Out of range value for column 'c1' at row 1 2702Warning 1264 Out of range value for column 'c2' at row 1 2703Warning 1264 Out of range value for column 'c3' at row 1 2704Warning 1264 Out of range value for column 'c4' at row 1 2705Warning 1264 Out of range value for column 'c5' at row 1 2706Warning 1264 Out of range value for column 'c6' at row 1 2707Warning 1264 Out of range value for column 'c7' at row 1 2708Warning 1264 Out of range value for column 'c12' at row 1 2709execute my_select ; 2710c1 127 2711c2 32767 2712c3 8388607 2713c4 2147483647 2714c5 2147483647 2715c6 9223372036854775807 2716c7 3.40282e38 2717c8 1.111111111111111e50 2718c9 1.111111111111111e50 2719c10 1.111111111111111e50 2720c12 9999.9999 2721execute my_delete ; 2722set @arg00= -1.11111111111111111111e+50 ; 2723set statement sql_mode = '' for 2724execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2725@arg00, @arg00, @arg00, @arg00, @arg00 ; 2726Warnings: 2727Warning 1264 Out of range value for column 'c1' at row 1 2728Warning 1264 Out of range value for column 'c2' at row 1 2729Warning 1264 Out of range value for column 'c3' at row 1 2730Warning 1264 Out of range value for column 'c4' at row 1 2731Warning 1264 Out of range value for column 'c5' at row 1 2732Warning 1264 Out of range value for column 'c6' at row 1 2733Warning 1264 Out of range value for column 'c7' at row 1 2734Warning 1264 Out of range value for column 'c12' at row 1 2735execute my_select ; 2736c1 -128 2737c2 -32768 2738c3 -8388608 2739c4 -2147483648 2740c5 -2147483648 2741c6 -9223372036854775808 2742c7 -3.40282e38 2743c8 -1.111111111111111e50 2744c9 -1.111111111111111e50 2745c10 -1.111111111111111e50 2746c12 -9999.9999 2747execute my_delete ; 2748set @arg00= '-1.11111111111111111111e+50' ; 2749set statement sql_mode = '' for 2750execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2751@arg00, @arg00, @arg00, @arg00, @arg00 ; 2752Warnings: 2753Warning 1264 Out of range value for column 'c1' at row 1 2754Warning 1264 Out of range value for column 'c2' at row 1 2755Warning 1264 Out of range value for column 'c3' at row 1 2756Warning 1264 Out of range value for column 'c4' at row 1 2757Warning 1264 Out of range value for column 'c5' at row 1 2758Warning 1264 Out of range value for column 'c6' at row 1 2759Warning 1264 Out of range value for column 'c7' at row 1 2760Warning 1264 Out of range value for column 'c12' at row 1 2761execute my_select ; 2762c1 -128 2763c2 -32768 2764c3 -8388608 2765c4 -2147483648 2766c5 -2147483648 2767c6 -9223372036854775808 2768c7 -3.40282e38 2769c8 -1.111111111111111e50 2770c9 -1.111111111111111e50 2771c10 -1.111111111111111e50 2772c12 -9999.9999 2773execute my_delete ; 2774test_sequence 2775-- insert into string columns -- 2776set sql_mode = ''; 2777insert into t9 2778( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2779values 2780( 20, '20', '20', '20', '20', '20', '20', '20', '20', '20', '20', '20' ) ; 2781Warnings: 2782Warning 1265 Data truncated for column 'c20' at row 1 2783set @arg00= '21' ; 2784insert into t9 2785( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2786values 2787( 21, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2788@arg00, @arg00, @arg00 ) ; 2789Warnings: 2790Warning 1265 Data truncated for column 'c20' at row 1 2791prepare stmt1 from "insert into t9 2792 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2793values 2794 ( 22, '22', '22', '22', '22', '22', '22', '22', '22', '22', '22', '22' )" ; 2795execute stmt1 ; 2796Warnings: 2797Warning 1265 Data truncated for column 'c20' at row 1 2798set @arg00= '23'; 2799prepare stmt2 from "insert into t9 2800 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2801values 2802 ( 23, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2803execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2804@arg00, @arg00, @arg00, @arg00 ; 2805Warnings: 2806Warning 1265 Data truncated for column 'c20' at row 1 2807insert into t9 2808( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2809values 2810( 30, CAST('30' as binary), CAST('30' as binary), CAST('30' as binary), 2811CAST('30' as binary), CAST('30' as binary), CAST('30' as binary), 2812CAST('30' as binary), CAST('30' as binary), CAST('30' as binary), 2813CAST('30' as binary), CAST('30' as binary) ) ; 2814Warnings: 2815Warning 1265 Data truncated for column 'c20' at row 1 2816set @arg00= '31' ; 2817insert into t9 2818( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2819values 2820( 31, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2821@arg00, @arg00, @arg00 ) ; 2822Warnings: 2823Warning 1265 Data truncated for column 'c20' at row 1 2824prepare stmt1 from "insert into t9 2825 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2826values 2827 ( 32, CAST('32' as binary), CAST('32' as binary), CAST('32' as binary), 2828 CAST('32' as binary), CAST('32' as binary), CAST('32' as binary), 2829 CAST('32' as binary), CAST('32' as binary), CAST('32' as binary), 2830 CAST('32' as binary), CAST('32' as binary) )" ; 2831execute stmt1 ; 2832Warnings: 2833Warning 1265 Data truncated for column 'c20' at row 1 2834set @arg00= CAST('33' as binary); 2835prepare stmt2 from "insert into t9 2836 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2837values 2838 ( 33, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2839execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2840@arg00, @arg00, @arg00, @arg00 ; 2841Warnings: 2842Warning 1265 Data truncated for column 'c20' at row 1 2843insert into t9 2844( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2845values 2846( 40, 40, 40, 40, 40, 40, 40, 40, 40, 40, 40, 40 ) ; 2847Warnings: 2848Warning 1265 Data truncated for column 'c20' at row 1 2849set @arg00= 41 ; 2850insert into t9 2851( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2852values 2853( 41, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2854@arg00, @arg00, @arg00 ) ; 2855Warnings: 2856Warning 1265 Data truncated for column 'c20' at row 1 2857prepare stmt1 from "insert into t9 2858 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2859values 2860 ( 42, 42, 42, 42, 42, 42, 42, 42, 42, 42, 42, 42 )" ; 2861execute stmt1 ; 2862Warnings: 2863Warning 1265 Data truncated for column 'c20' at row 1 2864set @arg00= 43; 2865prepare stmt2 from "insert into t9 2866 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2867values 2868 ( 43, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2869execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2870@arg00, @arg00, @arg00, @arg00 ; 2871Warnings: 2872Warning 1265 Data truncated for column 'c20' at row 1 2873insert into t9 2874( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2875values 2876( 50, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0 ) ; 2877Warnings: 2878Warning 1265 Data truncated for column 'c20' at row 1 2879set @arg00= 51.0 ; 2880insert into t9 2881( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2882values 2883( 51, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2884@arg00, @arg00, @arg00 ) ; 2885Warnings: 2886Warning 1265 Data truncated for column 'c20' at row 1 2887prepare stmt1 from "insert into t9 2888 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2889values 2890 ( 52, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0 )" ; 2891execute stmt1 ; 2892Warnings: 2893Warning 1265 Data truncated for column 'c20' at row 1 2894set @arg00= 53.0; 2895prepare stmt2 from "insert into t9 2896 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2897values 2898 ( 53, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2899execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2900@arg00, @arg00, @arg00, @arg00 ; 2901Warnings: 2902Warning 1265 Data truncated for column 'c20' at row 1 2903insert into t9 2904( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2905values 2906( 54, 5.4e+1, 5.4e+1, 5.4e+1, 5.4e+1, 5.4e+1, 5.4e+1, 5.4e+1, 5.4e+1, 29075.4e+1, 5.4e+1, 5.4e+1 ) ; 2908Warnings: 2909Warning 1265 Data truncated for column 'c20' at row 1 2910set @arg00= 5.5e+1 ; 2911insert into t9 2912( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2913values 2914( 55, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2915@arg00, @arg00, @arg00 ) ; 2916Warnings: 2917Warning 1265 Data truncated for column 'c20' at row 1 2918prepare stmt1 from "insert into t9 2919 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2920values 2921 ( 56, 5.6e+1, 5.6e+1, 5.6e+1, 5.6e+1, 5.6e+1, 5.6e+1, 5.6e+1, 5.6e+1, 2922 5.6e+1, 5.6e+1, 5.6e+1 )" ; 2923execute stmt1 ; 2924Warnings: 2925Warning 1265 Data truncated for column 'c20' at row 1 2926set @arg00= 5.7e+1; 2927prepare stmt2 from "insert into t9 2928 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2929values 2930 ( 57, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2931execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2932@arg00, @arg00, @arg00, @arg00 ; 2933Warnings: 2934Warning 1265 Data truncated for column 'c20' at row 1 2935set sql_mode = default; 2936set @arg00= 'abc' ; 2937set @arg00= NULL ; 2938insert into t9 2939( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2940values 2941( 60, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ) ; 2942insert into t9 2943( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2944values 2945( 61, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2946@arg00, @arg00, @arg00 ) ; 2947prepare stmt1 from "insert into t9 2948 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2949values 2950 ( 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )" ; 2951execute stmt1 ; 2952prepare stmt2 from "insert into t9 2953 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2954values 2955 ( 63, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2956execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2957@arg00, @arg00, @arg00, @arg00 ; 2958set @arg00= 2 ; 2959set @arg00= NULL ; 2960insert into t9 2961( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2962values 2963( 71, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2964@arg00, @arg00, @arg00 ) ; 2965prepare stmt2 from "insert into t9 2966 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2967values 2968 ( 73, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2969execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2970@arg00, @arg00, @arg00, @arg00 ; 2971set @arg00= 8 ; 2972set @arg00= NULL ; 2973insert into t9 2974( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2975values 2976( 81, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2977@arg00, @arg00, @arg00 ) ; 2978prepare stmt2 from "insert into t9 2979 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) 2980values 2981 ( 83, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ; 2982execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 2983@arg00, @arg00, @arg00, @arg00 ; 2984select c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 2985from t9 where c1 >= 20 2986order by c1 ; 2987c1 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 298820 2 20 20 20 20 20 20 20 20 20 20 298921 2 21 21 21 21 21 21 21 21 21 21 299022 2 22 22 22 22 22 22 22 22 22 22 299123 2 23 23 23 23 23 23 23 23 23 23 299230 3 30 30 30 30 30 30 30 30 30 30 299331 3 31 31 31 31 31 31 31 31 31 31 299432 3 32 32 32 32 32 32 32 32 32 32 299533 3 33 33 33 33 33 33 33 33 33 33 299640 4 40 40 40 40 40 40 40 40 40 40 299741 4 41 41 41 41 41 41 41 41 41 41 299842 4 42 42 42 42 42 42 42 42 42 42 299943 4 43 43 43 43 43 43 43 43 43 43 300050 5 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 300151 5 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0 300252 5 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0 300353 5 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0 300454 5 54 54 54 54 54 54 54 54 54 54 300555 6 55 55 55 55 55 55 55 55 55 55 300656 6 56 56 56 56 56 56 56 56 56 56 300757 6 57 57 57 57 57 57 57 57 57 57 300860 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 300961 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 301062 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 301163 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 301271 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 301373 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 301481 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 301583 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3016test_sequence 3017-- select .. where string column = .. -- 3018set @arg00= '20'; 3019select 'true' as found from t9 3020where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and 3021c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and 3022c27= '20' and c28= '20' and c29= '20' and c30= '20' ; 3023found 3024true 3025select 'true' as found from t9 3026where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and 3027c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and 3028c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00; 3029found 3030true 3031prepare stmt1 from "select 'true' as found from t9 3032where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and 3033 c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and 3034 c27= '20' and c28= '20' and c29= '20' and c30= '20'" ; 3035execute stmt1 ; 3036found 3037true 3038prepare stmt1 from "select 'true' as found from t9 3039where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and 3040 c21= ? and c22= ? and c23= ? and c25= ? and 3041 c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ; 3042execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 3043@arg00, @arg00, @arg00, @arg00, @arg00 ; 3044found 3045true 3046set @arg00= CAST('20' as binary); 3047select 'true' as found from t9 3048where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20))) 3049= CAST('20' as binary) and c21= CAST('20' as binary) 3050and c22= CAST('20' as binary) and c23= CAST('20' as binary) and 3051c24= CAST('20' as binary) and c25= CAST('20' as binary) and 3052c26= CAST('20' as binary) and c27= CAST('20' as binary) and 3053c28= CAST('20' as binary) and c29= CAST('20' as binary) and 3054c30= CAST('20' as binary) ; 3055found 3056true 3057select 'true' as found from t9 3058where c1= 20 and concat(c20,substr(@arg00,1+length(c20))) = @arg00 and 3059c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and 3060c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and 3061c30= @arg00; 3062found 3063true 3064prepare stmt1 from "select 'true' as found from t9 3065where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20))) 3066 = CAST('20' as binary) and c21= CAST('20' as binary) 3067 and c22= CAST('20' as binary) and c23= CAST('20' as binary) and 3068 c24= CAST('20' as binary) and c25= CAST('20' as binary) and 3069 c26= CAST('20' as binary) and c27= CAST('20' as binary) and 3070 c28= CAST('20' as binary) and c29= CAST('20' as binary) and 3071 c30= CAST('20' as binary)" ; 3072execute stmt1 ; 3073found 3074true 3075prepare stmt1 from "select 'true' as found from t9 3076where c1= 20 and concat(c20,substr(?,1+length(c20))) = ? and c21= ? and 3077 c22= ? and c23= ? and c25= ? and c26= ? and c27= ? and c28= ? and 3078 c29= ? and c30= ?"; 3079execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 3080@arg00, @arg00, @arg00, @arg00, @arg00 ; 3081found 3082true 3083set @arg00= 20; 3084select 'true' as found from t9 3085where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and 3086c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and 3087c27= 20 and c28= 20 and c29= 20 and c30= 20 ; 3088found 3089true 3090select 'true' as found from t9 3091where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and 3092c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and 3093c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00; 3094found 3095true 3096prepare stmt1 from "select 'true' as found from t9 3097where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and 3098 c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and 3099 c27= 20 and c28= 20 and c29= 20 and c30= 20" ; 3100execute stmt1 ; 3101found 3102true 3103prepare stmt1 from "select 'true' as found from t9 3104where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and 3105 c21= ? and c22= ? and c23= ? and c25= ? and 3106 c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ; 3107execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 3108@arg00, @arg00, @arg00, @arg00, @arg00 ; 3109found 3110true 3111set @arg00= 20.0; 3112select 'true' as found from t9 3113where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and 3114c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and 3115c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0 ; 3116found 3117true 3118select 'true' as found from t9 3119where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and 3120c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and 3121c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00; 3122found 3123true 3124prepare stmt1 from "select 'true' as found from t9 3125where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and 3126 c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and 3127 c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0" ; 3128execute stmt1 ; 3129found 3130true 3131prepare stmt1 from "select 'true' as found from t9 3132where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and 3133 c21= ? and c22= ? and c23= ? and c25= ? and 3134 c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ; 3135execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 3136@arg00, @arg00, @arg00, @arg00, @arg00 ; 3137found 3138true 3139delete from t9 ; 3140test_sequence 3141-- insert into date/time columns -- 3142set sql_mode = ''; 3143set @arg00= '1991-01-01 01:01:01' ; 3144insert into t9 3145( c1, c13, c14, c15, c16, c17 ) 3146values 3147( 20, '1991-01-01 01:01:01', '1991-01-01 01:01:01', '1991-01-01 01:01:01', 3148'1991-01-01 01:01:01', '1991-01-01 01:01:01') ; 3149Warnings: 3150Note 1265 Data truncated for column 'c13' at row 1 3151Note 1265 Data truncated for column 'c16' at row 1 3152Warning 1265 Data truncated for column 'c17' at row 1 3153insert into t9 3154( c1, c13, c14, c15, c16, c17 ) 3155values 3156( 21, @arg00, @arg00, @arg00, @arg00, @arg00) ; 3157Warnings: 3158Note 1265 Data truncated for column 'c13' at row 1 3159Note 1265 Data truncated for column 'c16' at row 1 3160Warning 1265 Data truncated for column 'c17' at row 1 3161prepare stmt1 from "insert into t9 3162 ( c1, c13, c14, c15, c16, c17 ) 3163values 3164 ( 22, '1991-01-01 01:01:01', '1991-01-01 01:01:01', '1991-01-01 01:01:01', 3165 '1991-01-01 01:01:01', '1991-01-01 01:01:01')" ; 3166execute stmt1 ; 3167Warnings: 3168Note 1265 Data truncated for column 'c13' at row 1 3169Note 1265 Data truncated for column 'c16' at row 1 3170Warning 1265 Data truncated for column 'c17' at row 1 3171prepare stmt2 from "insert into t9 3172 ( c1, c13, c14, c15, c16, c17 ) 3173values 3174 ( 23, ?, ?, ?, ?, ? )" ; 3175execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ; 3176Warnings: 3177Note 1265 Data truncated for column 'c13' at row 1 3178Note 1265 Data truncated for column 'c16' at row 1 3179Warning 1265 Data truncated for column 'c17' at row 1 3180set @arg00= CAST('1991-01-01 01:01:01' as datetime) ; 3181insert into t9 3182( c1, c13, c14, c15, c16, c17 ) 3183values 3184( 30, CAST('1991-01-01 01:01:01' as datetime), 3185CAST('1991-01-01 01:01:01' as datetime), 3186CAST('1991-01-01 01:01:01' as datetime), 3187CAST('1991-01-01 01:01:01' as datetime), 3188CAST('1991-01-01 01:01:01' as datetime)) ; 3189Warnings: 3190Note 1265 Data truncated for column 'c13' at row 1 3191Note 1265 Data truncated for column 'c16' at row 1 3192Warning 1265 Data truncated for column 'c17' at row 1 3193insert into t9 3194( c1, c13, c14, c15, c16, c17 ) 3195values 3196( 31, @arg00, @arg00, @arg00, @arg00, @arg00) ; 3197Warnings: 3198Note 1265 Data truncated for column 'c13' at row 1 3199Note 1265 Data truncated for column 'c16' at row 1 3200Warning 1265 Data truncated for column 'c17' at row 1 3201prepare stmt1 from "insert into t9 3202 ( c1, c13, c14, c15, c16, c17 ) 3203values 3204 ( 32, CAST('1991-01-01 01:01:01' as datetime), 3205 CAST('1991-01-01 01:01:01' as datetime), 3206 CAST('1991-01-01 01:01:01' as datetime), 3207 CAST('1991-01-01 01:01:01' as datetime), 3208 CAST('1991-01-01 01:01:01' as datetime))" ; 3209execute stmt1 ; 3210Warnings: 3211Note 1265 Data truncated for column 'c13' at row 1 3212Note 1265 Data truncated for column 'c16' at row 1 3213Warning 1265 Data truncated for column 'c17' at row 1 3214prepare stmt2 from "insert into t9 3215 ( c1, c13, c14, c15, c16, c17 ) 3216values 3217 ( 33, ?, ?, ?, ?, ? )" ; 3218execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ; 3219Warnings: 3220Note 1265 Data truncated for column 'c13' at row 1 3221Note 1265 Data truncated for column 'c16' at row 1 3222Warning 1265 Data truncated for column 'c17' at row 1 3223set @arg00= 2000000000 ; 3224insert into t9 3225( c1, c13, c14, c15, c16, c17 ) 3226values 3227( 40, 2000000000, 2000000000, 2000000000, 2000000000, 2000000000 ) ; 3228Warnings: 3229Warning 1265 Data truncated for column 'c13' at row 1 3230Warning 1265 Data truncated for column 'c14' at row 1 3231Warning 1265 Data truncated for column 'c15' at row 1 3232Warning 1265 Data truncated for column 'c16' at row 1 3233Warning 1264 Out of range value for column 'c17' at row 1 3234insert into t9 3235( c1, c13, c14, c15, c16, c17 ) 3236values 3237( 41, @arg00, @arg00, @arg00, @arg00, @arg00) ; 3238Warnings: 3239Warning 1265 Data truncated for column 'c13' at row 1 3240Warning 1265 Data truncated for column 'c14' at row 1 3241Warning 1265 Data truncated for column 'c15' at row 1 3242Warning 1265 Data truncated for column 'c16' at row 1 3243Warning 1264 Out of range value for column 'c17' at row 1 3244prepare stmt1 from "insert into t9 3245 ( c1, c13, c14, c15, c16, c17 ) 3246values 3247 ( 42, 2000000000, 2000000000, 2000000000, 2000000000, 2000000000 )" ; 3248execute stmt1 ; 3249Warnings: 3250Warning 1265 Data truncated for column 'c13' at row 1 3251Warning 1265 Data truncated for column 'c14' at row 1 3252Warning 1265 Data truncated for column 'c15' at row 1 3253Warning 1265 Data truncated for column 'c16' at row 1 3254Warning 1264 Out of range value for column 'c17' at row 1 3255prepare stmt2 from "insert into t9 3256 ( c1, c13, c14, c15, c16, c17 ) 3257values 3258 ( 43, ?, ?, ?, ?, ? )" ; 3259execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ; 3260Warnings: 3261Warning 1265 Data truncated for column 'c13' at row 1 3262Warning 1265 Data truncated for column 'c14' at row 1 3263Warning 1265 Data truncated for column 'c15' at row 1 3264Warning 1265 Data truncated for column 'c16' at row 1 3265Warning 1264 Out of range value for column 'c17' at row 1 3266set @arg00= 1.0e+10 ; 3267insert into t9 3268( c1, c13, c14, c15, c16, c17 ) 3269values 3270( 50, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 ) ; 3271Warnings: 3272Warning 1265 Data truncated for column 'c15' at row 1 3273Note 1265 Data truncated for column 'c16' at row 1 3274Warning 1264 Out of range value for column 'c17' at row 1 3275insert into t9 3276( c1, c13, c14, c15, c16, c17 ) 3277values 3278( 51, @arg00, @arg00, @arg00, @arg00, @arg00) ; 3279Warnings: 3280Warning 1265 Data truncated for column 'c15' at row 1 3281Note 1265 Data truncated for column 'c16' at row 1 3282Warning 1264 Out of range value for column 'c17' at row 1 3283prepare stmt1 from "insert into t9 3284 ( c1, c13, c14, c15, c16, c17 ) 3285values 3286 ( 52, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 )" ; 3287execute stmt1 ; 3288Warnings: 3289Warning 1265 Data truncated for column 'c15' at row 1 3290Note 1265 Data truncated for column 'c16' at row 1 3291Warning 1264 Out of range value for column 'c17' at row 1 3292prepare stmt2 from "insert into t9 3293 ( c1, c13, c14, c15, c16, c17 ) 3294values 3295 ( 53, ?, ?, ?, ?, ? )" ; 3296execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ; 3297Warnings: 3298Warning 1265 Data truncated for column 'c15' at row 1 3299Note 1265 Data truncated for column 'c16' at row 1 3300Warning 1264 Out of range value for column 'c17' at row 1 3301set sql_mode = default; 3302set @arg00= 'abc' ; 3303set @arg00= NULL ; 3304insert into t9 3305( c1, c13, c14, c15, c16, c17 ) 3306values 3307( 60, NULL, NULL, '1991-01-01 01:01:01', 3308NULL, NULL) ; 3309insert into t9 3310( c1, c13, c14, c15, c16, c17 ) 3311values 3312( 61, @arg00, @arg00, '1991-01-01 01:01:01', @arg00, @arg00) ; 3313prepare stmt1 from "insert into t9 3314 ( c1, c13, c14, c15, c16, c17 ) 3315values 3316 ( 62, NULL, NULL, '1991-01-01 01:01:01', 3317 NULL, NULL)" ; 3318execute stmt1 ; 3319prepare stmt2 from "insert into t9 3320 ( c1, c13, c14, c15, c16, c17 ) 3321values 3322 ( 63, ?, ?, '1991-01-01 01:01:01', ?, ? )" ; 3323execute stmt2 using @arg00, @arg00, @arg00, @arg00 ; 3324set @arg00= 8 ; 3325set @arg00= NULL ; 3326insert into t9 3327( c1, c13, c14, c15, c16, c17 ) 3328values 3329( 71, @arg00, @arg00, '1991-01-01 01:01:01', @arg00, @arg00) ; 3330prepare stmt2 from "insert into t9 3331 ( c1, c13, c14, c15, c16, c17 ) 3332values 3333 ( 73, ?, ?, '1991-01-01 01:01:01', ?, ? )" ; 3334execute stmt2 using @arg00, @arg00, @arg00, @arg00 ; 3335set @arg00= 8.0 ; 3336set @arg00= NULL ; 3337insert into t9 3338( c1, c13, c14, c15, c16, c17 ) 3339values 3340( 81, @arg00, @arg00, '1991-01-01 01:01:01', @arg00, @arg00) ; 3341prepare stmt2 from "insert into t9 3342 ( c1, c13, c14, c15, c16, c17 ) 3343values 3344 ( 83, ?, ?, '1991-01-01 01:01:01', ?, ? )" ; 3345execute stmt2 using @arg00, @arg00, @arg00, @arg00 ; 3346select c1, c13, c14, c15, c16, c17 from t9 order by c1 ; 3347c1 c13 c14 c15 c16 c17 334820 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991 334921 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991 335022 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991 335123 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991 335230 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991 335331 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991 335432 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991 335533 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991 335640 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000 335741 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000 335842 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000 335943 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000 336050 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000 336151 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000 336252 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000 336353 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 0000 336460 NULL NULL 1991-01-01 01:01:01 NULL NULL 336561 NULL NULL 1991-01-01 01:01:01 NULL NULL 336662 NULL NULL 1991-01-01 01:01:01 NULL NULL 336763 NULL NULL 1991-01-01 01:01:01 NULL NULL 336871 NULL NULL 1991-01-01 01:01:01 NULL NULL 336973 NULL NULL 1991-01-01 01:01:01 NULL NULL 337081 NULL NULL 1991-01-01 01:01:01 NULL NULL 337183 NULL NULL 1991-01-01 01:01:01 NULL NULL 3372test_sequence 3373-- select .. where date/time column = .. -- 3374set @arg00= '1991-01-01 01:01:01' ; 3375select 'true' as found from t9 3376where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and 3377c15= '1991-01-01 01:01:01' and 3378c17= '1991-01-01 01:01:01' ; 3379found 3380true 3381select 'true' as found from t9 3382where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 3383and c17= @arg00 ; 3384found 3385true 3386prepare stmt1 from "select 'true' as found from t9 3387where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and 3388 c15= '1991-01-01 01:01:01' and 3389 c17= '1991-01-01 01:01:01'" ; 3390execute stmt1 ; 3391found 3392true 3393prepare stmt1 from "select 'true' as found from t9 3394where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c17= ?" ; 3395execute stmt1 using @arg00, @arg00, @arg00, @arg00 ; 3396found 3397true 3398set @arg00= CAST('1991-01-01 01:01:01' as datetime) ; 3399select 'true' as found from t9 3400where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and 3401c14= CAST('1991-01-01 01:01:01' as datetime) and 3402c15= CAST('1991-01-01 01:01:01' as datetime) and 3403c17= CAST('1991-01-01 01:01:01' as datetime) ; 3404found 3405true 3406select 'true' as found from t9 3407where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 3408and c17= @arg00 ; 3409found 3410true 3411prepare stmt1 from "select 'true' as found from t9 3412where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and 3413 c14= CAST('1991-01-01 01:01:01' as datetime) and 3414 c15= CAST('1991-01-01 01:01:01' as datetime) and 3415 c17= CAST('1991-01-01 01:01:01' as datetime)" ; 3416execute stmt1 ; 3417found 3418true 3419prepare stmt1 from "select 'true' as found from t9 3420where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c17= ?" ; 3421execute stmt1 using @arg00, @arg00, @arg00, @arg00 ; 3422found 3423true 3424set @arg00= '01:01:01' ; 3425select 'true' as found from t9 where c1= 20 and c16= '01:01:01' ; 3426found 3427true 3428select 'true' as found from t9 where c1= 20 and c16= @arg00 ; 3429found 3430true 3431prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= '01:01:01'" ; 3432execute stmt1 ; 3433found 3434true 3435prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= ?" ; 3436execute stmt1 using @arg00 ; 3437found 3438true 3439set @arg00= CAST('01:01:01' as time) ; 3440select 'true' as found from t9 where c1= 20 and c16= CAST('01:01:01' as time) ; 3441found 3442true 3443select 'true' as found from t9 where c1= 20 and c16= @arg00 ; 3444found 3445true 3446prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= CAST('01:01:01' as time)" ; 3447execute stmt1 ; 3448found 3449true 3450prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= ?" ; 3451execute stmt1 using @arg00 ; 3452found 3453true 3454set @arg00= 1991 ; 3455select 'true' as found from t9 3456where c1= 20 and c17= 1991 ; 3457found 3458true 3459select 'true' as found from t9 3460where c1= 20 and c17= @arg00 ; 3461found 3462true 3463prepare stmt1 from "select 'true' as found from t9 3464where c1= 20 and c17= 1991" ; 3465execute stmt1 ; 3466found 3467true 3468prepare stmt1 from "select 'true' as found from t9 3469where c1= 20 and c17= ?" ; 3470execute stmt1 using @arg00 ; 3471found 3472true 3473set @arg00= 1.991e+3 ; 3474select 'true' as found from t9 3475where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ; 3476found 3477true 3478select 'true' as found from t9 3479where c1= 20 and abs(c17 - @arg00) < 0.01 ; 3480found 3481true 3482prepare stmt1 from "select 'true' as found from t9 3483where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ; 3484execute stmt1 ; 3485found 3486true 3487prepare stmt1 from "select 'true' as found from t9 3488where c1= 20 and abs(c17 - ?) < 0.01" ; 3489execute stmt1 using @arg00 ; 3490found 3491true 3492drop table t1, t9; 3493