github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/prepare/prepare_all.result (about) 1 set time_zone="+08:00"; 2 drop table if exists numbers; 3 CREATE TABLE numbers 4 (pk INTEGER PRIMARY KEY, 5 ui BIGINT UNSIGNED, 6 si BIGINT 7 ); 8 INSERT INTO numbers VALUES 9 (0, 0, -9223372036854775808), (1, 18446744073709551615, 9223372036854775807); 10 SET @ui_min = CAST(0 AS UNSIGNED); 11 SET @ui_min = 0; 12 SET @ui_max = 18446744073709551615; 13 SET @si_min = -9223372036854775808; 14 SET @si_max = 9223372036854775807; 15 PREPARE s1 FROM 'SELECT * FROM numbers WHERE ui=?'; 16 EXECUTE s1 USING @ui_min; 17 pk ui si 18 0 0 -9223372036854775808 19 EXECUTE s1 USING @ui_max; 20 pk ui si 21 1 18446744073709551615 9223372036854775807 22 EXECUTE s1 USING @si_min; 23 pk ui si 24 EXECUTE s1 USING @si_max; 25 pk ui si 26 DEALLOCATE PREPARE s1; 27 PREPARE s2 FROM 'SELECT * FROM numbers WHERE si=?'; 28 EXECUTE s2 USING @ui_min; 29 pk ui si 30 EXECUTE s2 USING @ui_max; 31 Data truncation: data out of range: data type int64, 32 EXECUTE s2 USING @si_min; 33 pk ui si 34 0 0 -9223372036854775808 35 EXECUTE s2 USING @si_max; 36 pk ui si 37 1 18446744073709551615 9223372036854775807 38 DEALLOCATE PREPARE s2; 39 DROP TABLE numbers; 40 drop table if exists test_table; 41 CREATE TABLE test_table 42 (pk INTEGER PRIMARY KEY, 43 fl FLOAT, 44 dou DOUBLE 45 ); 46 set @float1_num=1.2345678; 47 set @float2_num=1.8765432; 48 set @double_num1=1.223344556677889900; 49 set @double_num2=1.223344556677889900; 50 INSERT INTO test_table VALUES(0, @float1_num, @double_num1), (1, @float2_num, @double_num2); 51 INSERT INTO test_table VALUES(0, 1.2345678, 1.223344556677889900), (1, 1.876599999432, 1.223344556677889900); 52 Duplicate entry '0' for key 'pk' 53 select * from test_table; 54 pk fl dou 55 0 1.23457 1.22334455667789 56 1 1.87654 1.22334455667789 57 select * from test_table where fl=1.2345678; 58 pk fl dou 59 SET @fl_hit=1.2345678; 60 SET @fl_not_hit=1.234567800; 61 SET @dou_not_hit=1.223344556677889; 62 SET @dou_hit=1.223344556677889900; 63 PREPARE s1 FROM 'SELECT * FROM test_table WHERE fl=?'; 64 PREPARE s2 FROM 'SELECT * FROM test_table WHERE dou=?'; 65 EXECUTE s1 USING @fl_hit; 66 pk fl dou 67 0 1.2345678 1.22334455667789 68 EXECUTE s1 USING @fl_not_hit; 69 pk fl dou 70 0 1.2345678 1.22334455667789 71 EXECUTE s1 USING @dou_hit; 72 pk fl dou 73 EXECUTE s1 USING @dou_not_hit; 74 pk fl dou 75 EXECUTE s2 USING @fl_hit; 76 pk fl dou 77 EXECUTE s2 USING @fl_not_hit; 78 pk fl dou 79 EXECUTE s2 USING @dou_hit; 80 pk fl dou 81 0 1.23457 1.22334455667789 82 1 1.87654 1.22334455667789 83 EXECUTE s2 USING @dou_not_hit; 84 pk fl dou 85 DEALLOCATE PREPARE s1; 86 DEALLOCATE PREPARE s2; 87 DROP TABLE test_table; 88 drop table if exists t1; 89 create table t1 ( 90 str1 varchar(25), 91 str2 char(25) 92 ); 93 insert into t1 values('a1','b1'),('a2', 'b2'),('a3', ''); 94 insert into t1(str1) values('a4'); 95 prepare s1 from 'update t1 set str1="xx1" where str2=?'; 96 set @hit_str2='b1'; 97 set @not_hit_str2='b'; 98 execute s1 using @hit_str2; 99 execute s1 using @not_hit_str2; 100 select * from t1; 101 str1 str2 102 a2 b2 103 a3 104 a4 null 105 xx1 b1 106 DEALLOCATE PREPARE s1; 107 prepare s2 from 'update t1 set str2="yy1" where str1=?'; 108 set @hit_str1='a2'; 109 set @not_hit_str2='a'; 110 execute s2 using @hit_str1; 111 execute s2 using @not_hit_str1; 112 select * from t1; 113 str1 str2 114 xx1 b1 115 a2 yy1 116 a3 117 a4 null 118 DEALLOCATE PREPARE s2; 119 prepare s3 from 'select * from t1 where str1 like ?'; 120 prepare s4 from 'select * from t1 where str2 not like ?'; 121 set @a='a%'; 122 execute s3 using @a; 123 str1 str2 124 a2 yy1 125 a3 126 a4 null 127 DEALLOCATE PREPARE s3; 128 DEALLOCATE PREPARE s4; 129 prepare s5 from 'select * from t1 where str2=?'; 130 set @hit_empty=''; 131 execute s5 using @hit_empty; 132 str1 str2 133 a3 134 DEALLOCATE PREPARE s5; 135 DROP TABLE t1; 136 drop table if exists t2; 137 create table t2 ( 138 time1 Date, 139 time2 DateTime, 140 time3 TIMESTAMP 141 ); 142 insert into t2 values ('1000-01-01', '0001-01-01 00:00:00.000000', '2038-01-19 03:14:07.999999'); 143 insert into t2 values ('1000-01-01', '9999-12-31 23:59:59.999999', '2038-01-19 03:14:07.999999'); 144 invalid input: invalid datatime value 9999-12-31 23:59:59.999999 145 insert into t2 values ('9999-12-31', '9999-12-31 23:59:59.999999', '2038-01-19 03:14:07.999999'); 146 invalid input: invalid datatime value 9999-12-31 23:59:59.999999 147 insert into t2 values ('1000-01-01', '0001-01-01 00:00:00.000000', '1970-01-01 00:00:01.000000'); 148 insert into t2 values ('1000-01-01', '0001-01-01 00:00:00.000000', '1970-01-01 00:00:01.000000'); 149 insert into t2 values ('1000-01-01', '0001-01-01 00:00:00.000000', '1970-01-01 00:00:01.000000'); 150 insert into t2 values ('2022-10-24', '2022-10-24 10:10:10.000000', '2022-10-24 00:00:01.000000'); 151 insert into t2 values ('2022-10-25', '2022-10-25 10:10:10.000000', '2022-10-25 00:00:01.000000'); 152 insert into t2 values ('2022-10-26', '2022-10-26 10:10:10.000000', '2022-10-26 00:00:01.000000'); 153 select * from t2; 154 time1 time2 time3 155 1000-01-01 0001-01-01 00:00:00 2038-01-19 03:14:08 156 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 157 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 158 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 159 2022-10-24 2022-10-24 10:10:10 2022-10-24 00:00:01 160 2022-10-25 2022-10-25 10:10:10 2022-10-25 00:00:01 161 2022-10-26 2022-10-26 10:10:10 2022-10-26 00:00:01 162 set @max_date='9999-12-31'; 163 set @min_date='1000-01-01'; 164 set @max_datetime='9999-12-31 23:59:59.999999'; 165 set @min_datetime='0001-01-01 00:00:00.000000'; 166 set @max_timestamp='1970-01-01 00:00:01.000000'; 167 set @min_timestamp='2038-01-19 03:14:07.999999'; 168 prepare s1 from 'select * from t2 where time1=?'; 169 execute s1 using @max_date; 170 time1 time2 time3 171 execute s1 using @min_date; 172 time1 time2 time3 173 1000-01-01 0001-01-01 00:00:00 2038-01-19 03:14:08 174 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 175 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 176 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 177 execute s1 using @max_datetime; 178 time1 time2 time3 179 execute s1 using @min_datetime; 180 time1 time2 time3 181 execute s1 using @max_timestamp; 182 time1 time2 time3 183 execute s1 using @min_timestamp; 184 time1 time2 time3 185 DEALLOCATE PREPARE s1; 186 prepare s2 from 'select * from t2 where time2=?'; 187 execute s2 using @max_date; 188 time1 time2 time3 189 execute s2 using @min_date; 190 time1 time2 time3 191 execute s2 using @max_datetime; 192 time1 time2 time3 193 execute s2 using @min_datetime; 194 time1 time2 time3 195 1000-01-01 0001-01-01 00:00:00 2038-01-19 03:14:08 196 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 197 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 198 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 199 execute s2 using @max_timestamp; 200 time1 time2 time3 201 execute s2 using @min_timestamp; 202 time1 time2 time3 203 DEALLOCATE PREPARE s2; 204 prepare s3 from 'select * from t2 where time3=?'; 205 execute s3 using @max_date; 206 time1 time2 time3 207 execute s3 using @min_date; 208 time1 time2 time3 209 execute s3 using @max_datetime; 210 invalid argument parse timestamp, bad value 9999-12-31 23:59:59.999999 211 execute s3 using @min_datetime; 212 time1 time2 time3 213 execute s3 using @max_timestamp; 214 time1 time2 time3 215 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 216 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 217 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 218 execute s3 using @min_timestamp; 219 time1 time2 time3 220 1000-01-01 0001-01-01 00:00:00 2038-01-19 03:14:08 221 DEALLOCATE PREPARE s3; 222 set @time1='2022-10-24'; 223 set @time2='2022-10-25 10:10:10.000000'; 224 set @time3='2022-10-26 00:00:01.000000'; 225 prepare s4 from 'delete from t2 where time1=?'; 226 prepare s5 from 'delete from t2 where time2=?'; 227 prepare s6 from 'delete from t2 where time3=?'; 228 execute s4 using @time1; 229 execute s5 using @time2; 230 execute s6 using @time3; 231 select * from t2; 232 time1 time2 time3 233 1000-01-01 0001-01-01 00:00:00 2038-01-19 03:14:08 234 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 235 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 236 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 237 DEALLOCATE PREPARE s4; 238 DEALLOCATE PREPARE s5; 239 DEALLOCATE PREPARE s6; 240 drop table t2; 241 drop table if exists t3; 242 create table t3( 243 dec1 decimal(5,2) default NULL, 244 dec2 decimal(25,10) 245 ); 246 insert into t3 values (12.345, 10000.222223333344444); 247 insert into t3 values (123.45, 1111122222.222223333344444); 248 insert into t3 values (133.45, 1111122222.222223333344444); 249 insert into t3 values (153.45, 1111122222.222223333344444); 250 insert into t3 values (123.45678, 111112222233333.222223333344444); 251 insert into t3(dec2) values (111112222233333.222223333344444); 252 select * from t3; 253 dec1 dec2 254 12.35 10000.2222233333 255 123.45 1111122222.2222233333 256 133.45 1111122222.2222233333 257 153.45 1111122222.2222233333 258 123.46 111112222233333.2222233333 259 null 111112222233333.2222233333 260 set @hit_dec1=12.34; 261 set @hit_dec2=1111122222.2222233333; 262 set @dec1_max=200; 263 set @dec1_min=10; 264 set @dec2_max=111112222233339; 265 set @dec2_min=1000; 266 prepare s1 from 'select * from t3 where dec1>?'; 267 prepare s2 from 'select * from t3 where dec1>=?'; 268 prepare s3 from 'select * from t3 where dec1<?'; 269 prepare s4 from 'select * from t3 where dec1<=?'; 270 prepare s5 from 'select * from t3 where dec1<>?'; 271 prepare s6 from 'select * from t3 where dec1!=?'; 272 prepare s7 from 'select * from t3 where dec1 between ? and ?'; 273 prepare s8 from 'select * from t3 where dec1 not between ? and ?'; 274 execute s1 using @hit_dec1; 275 dec1 dec2 276 123.45 1111122222.2222233333 277 133.45 1111122222.2222233333 278 153.45 1111122222.2222233333 279 123.46 111112222233333.2222233333 280 execute s1 using @dec1_max; 281 dec1 dec2 282 execute s1 using @dec1_min; 283 dec1 dec2 284 12.35 10000.2222233333 285 123.45 1111122222.2222233333 286 133.45 1111122222.2222233333 287 153.45 1111122222.2222233333 288 123.46 111112222233333.2222233333 289 execute s2 using @hit_dec1; 290 dec1 dec2 291 12.35 10000.2222233333 292 123.45 1111122222.2222233333 293 133.45 1111122222.2222233333 294 153.45 1111122222.2222233333 295 123.46 111112222233333.2222233333 296 execute s2 using @dec1_max; 297 dec1 dec2 298 execute s2 using @dec1_min; 299 dec1 dec2 300 12.35 10000.2222233333 301 123.45 1111122222.2222233333 302 133.45 1111122222.2222233333 303 153.45 1111122222.2222233333 304 123.46 111112222233333.2222233333 305 execute s3 using @hit_dec1; 306 dec1 dec2 307 execute s3 using @dec1_max; 308 dec1 dec2 309 12.35 10000.2222233333 310 123.45 1111122222.2222233333 311 133.45 1111122222.2222233333 312 153.45 1111122222.2222233333 313 123.46 111112222233333.2222233333 314 execute s3 using @dec1_min; 315 dec1 dec2 316 execute s4 using @hit_dec1; 317 dec1 dec2 318 12.34 10000.2222233333 319 execute s4 using @dec1_max; 320 dec1 dec2 321 12.35 10000.2222233333 322 123.45 1111122222.2222233333 323 133.45 1111122222.2222233333 324 153.45 1111122222.2222233333 325 123.46 111112222233333.2222233333 326 execute s4 using @dec1_min; 327 dec1 dec2 328 execute s5 using @hit_dec1; 329 dec1 dec2 330 123.45 1111122222.2222233333 331 133.45 1111122222.2222233333 332 153.45 1111122222.2222233333 333 123.46 111112222233333.2222233333 334 execute s5 using @dec1_max; 335 dec1 dec2 336 12.35 10000.2222233333 337 123.45 1111122222.2222233333 338 133.45 1111122222.2222233333 339 153.45 1111122222.2222233333 340 123.46 111112222233333.2222233333 341 execute s5 using @dec1_min; 342 dec1 dec2 343 12.35 10000.2222233333 344 123.45 1111122222.2222233333 345 133.45 1111122222.2222233333 346 153.45 1111122222.2222233333 347 123.46 111112222233333.2222233333 348 execute s6 using @hit_dec1; 349 dec1 dec2 350 123.45 1111122222.2222233333 351 133.45 1111122222.2222233333 352 153.45 1111122222.2222233333 353 123.46 111112222233333.2222233333 354 execute s6 using @dec1_max; 355 dec1 dec2 356 12.35 10000.2222233333 357 123.45 1111122222.2222233333 358 133.45 1111122222.2222233333 359 153.45 1111122222.2222233333 360 123.46 111112222233333.2222233333 361 execute s6 using @dec1_min; 362 dec1 dec2 363 12.35 10000.2222233333 364 123.45 1111122222.2222233333 365 133.45 1111122222.2222233333 366 153.45 1111122222.2222233333 367 123.46 111112222233333.2222233333 368 execute s7 using @dec1_min, @dec1_max; 369 dec1 dec2 370 12.35 10000.2222233333 371 123.45 1111122222.2222233333 372 133.45 1111122222.2222233333 373 153.45 1111122222.2222233333 374 123.46 111112222233333.2222233333 375 execute s7 using @dec1_max, @dec1_min; 376 dec1 dec2 377 execute s8 using @dec1_min, @dec1_max; 378 dec1 dec2 379 execute s8 using @dec1_max, @dec1_min; 380 dec1 dec2 381 12.35 10000.2222233333 382 123.45 1111122222.2222233333 383 133.45 1111122222.2222233333 384 153.45 1111122222.2222233333 385 123.46 111112222233333.2222233333 386 DEALLOCATE PREPARE s1; 387 DEALLOCATE PREPARE s2; 388 DEALLOCATE PREPARE s3; 389 DEALLOCATE PREPARE s4; 390 DEALLOCATE PREPARE s5; 391 DEALLOCATE PREPARE s6; 392 DEALLOCATE PREPARE s7; 393 DEALLOCATE PREPARE s8; 394 drop table t3; 395 drop table if exists t4; 396 create table t4( 397 a1 INT, 398 str1 varchar(25) 399 ); 400 insert into t4 values (10, 'aaa'); 401 insert into t4 values (10, 'bbb'); 402 insert into t4 values (20, 'aaa'); 403 insert into t4 values (20, 'bbb'); 404 insert into t4 values (20, 'bbb'); 405 insert into t4 values (20, 'bbb'); 406 insert into t4 values (20, 'bbb'); 407 insert into t4 values (20, 'ccc'); 408 set @min=1; 409 set @max=5; 410 prepare s1 from 'select str1,count(a1) as c from t4 group by str1 having count(a1)>?'; 411 prepare s2 from 'select str1,count(a1) as c from t4 group by str1 having count(a1)>=?'; 412 prepare s3 from 'select str1,count(a1) as c from t4 group by str1 having count(a1)<?'; 413 prepare s4 from 'select str1,count(a1) as c from t4 group by str1 having count(a1)<=?'; 414 execute s1 using @min; 415 str1 c 416 aaa 2 417 bbb 5 418 execute s2 using @min; 419 str1 c 420 aaa 2 421 bbb 5 422 ccc 1 423 execute s3 using @max; 424 str1 c 425 aaa 2 426 ccc 1 427 execute s4 using @max; 428 str1 c 429 aaa 2 430 bbb 5 431 ccc 1 432 DEALLOCATE PREPARE s1; 433 DEALLOCATE PREPARE s2; 434 DEALLOCATE PREPARE s3; 435 DEALLOCATE PREPARE s4; 436 drop table t4; 437 drop table if exists t5; 438 create table t5( 439 a1 int, 440 a2 varchar(25) 441 ); 442 drop table if exists t6; 443 create table t6( 444 b1 int, 445 b2 varchar(25) 446 ); 447 insert into t5 values (10, 'xxx1'); 448 insert into t5 values (20, 'xxx1'); 449 insert into t5 values (30, 'xxx1'); 450 insert into t5 values (10, 'yyy1'); 451 insert into t5 values (10, 'zzz1'); 452 insert into t5 values (20, 'yyy1'); 453 insert into t5 values (40, 'xxx1'); 454 insert into t6 values (10, 'aaa1'); 455 insert into t6 values (20, 'aaa1'); 456 insert into t6 values (30, 'aaa1'); 457 insert into t6 values (40, 'bbb1'); 458 insert into t6 values (50, 'aaa1'); 459 insert into t6 values (60, 'ccc1'); 460 insert into t6 values (10, 'aaa1'); 461 insert into t6 values (20, 'ccc1'); 462 set @a2_val='yyy1'; 463 set @min=10; 464 prepare s1 from 'select * from t5 a inner join t6 b on a.a1=b.b1 where a.a2=?'; 465 prepare s2 from 'select * from t5 a inner join t6 b on a.a1=b.b1 where a.a1>=?'; 466 prepare s3 from 'select * from t5 a inner join t6 b on a.a1=b.b1 where b.b1>=?'; 467 prepare s4 from 'select * from t5 a left join t6 b on a.a1=b.b1 where a.a2=?'; 468 prepare s5 from 'select * from t5 a left join t6 b on a.a1=b.b1 where a.a1>=?'; 469 prepare s6 from 'select * from t5 a left join t6 b on a.a1=b.b1 where b.b1>=?'; 470 prepare s7 from 'select * from t5 a right join t6 b on a.a1=b.b1 where a.a2=?'; 471 prepare s8 from 'select * from t5 a right join t6 b on a.a1=b.b1 where a.a1>=?'; 472 prepare s9 from 'select * from t5 a right join t6 b on a.a1=b.b1 where b.b1>=?'; 473 execute s1 using @a2_val; 474 a1 a2 b1 b2 475 10 yyy1 10 aaa1 476 20 yyy1 20 aaa1 477 10 yyy1 10 aaa1 478 20 yyy1 20 ccc1 479 execute s2 using @min; 480 a1 a2 b1 b2 481 10 zzz1 10 aaa1 482 10 yyy1 10 aaa1 483 10 xxx1 10 aaa1 484 20 yyy1 20 aaa1 485 20 xxx1 20 aaa1 486 30 xxx1 30 aaa1 487 40 xxx1 40 bbb1 488 10 zzz1 10 aaa1 489 10 yyy1 10 aaa1 490 10 xxx1 10 aaa1 491 20 yyy1 20 ccc1 492 20 xxx1 20 ccc1 493 execute s3 using @min; 494 a1 a2 b1 b2 495 10 xxx1 10 aaa1 496 10 xxx1 10 aaa1 497 20 xxx1 20 ccc1 498 20 xxx1 20 aaa1 499 30 xxx1 30 aaa1 500 10 yyy1 10 aaa1 501 10 yyy1 10 aaa1 502 10 zzz1 10 aaa1 503 10 zzz1 10 aaa1 504 20 yyy1 20 ccc1 505 20 yyy1 20 aaa1 506 40 xxx1 40 bbb1 507 execute s4 using @a2_val; 508 a1 a2 b1 b2 509 10 yyy1 10 aaa1 510 10 yyy1 10 aaa1 511 20 yyy1 20 ccc1 512 20 yyy1 20 aaa1 513 execute s5 using @min; 514 a1 a2 b1 b2 515 10 xxx1 10 aaa1 516 10 xxx1 10 aaa1 517 20 xxx1 20 ccc1 518 20 xxx1 20 aaa1 519 30 xxx1 30 aaa1 520 10 yyy1 10 aaa1 521 10 yyy1 10 aaa1 522 10 zzz1 10 aaa1 523 10 zzz1 10 aaa1 524 20 yyy1 20 ccc1 525 20 yyy1 20 aaa1 526 40 xxx1 40 bbb1 527 execute s6 using @min; 528 a1 a2 b1 b2 529 10 xxx1 10 aaa1 530 10 xxx1 10 aaa1 531 20 xxx1 20 ccc1 532 20 xxx1 20 aaa1 533 30 xxx1 30 aaa1 534 10 yyy1 10 aaa1 535 10 yyy1 10 aaa1 536 10 zzz1 10 aaa1 537 10 zzz1 10 aaa1 538 20 yyy1 20 ccc1 539 20 yyy1 20 aaa1 540 40 xxx1 40 bbb1 541 execute s7 using @a2_val; 542 a1 a2 b1 b2 543 10 yyy1 10 aaa1 544 20 yyy1 20 aaa1 545 10 yyy1 10 aaa1 546 20 yyy1 20 ccc1 547 execute s8 using @min; 548 a1 a2 b1 b2 549 10 zzz1 10 aaa1 550 10 yyy1 10 aaa1 551 10 xxx1 10 aaa1 552 20 yyy1 20 aaa1 553 20 xxx1 20 aaa1 554 30 xxx1 30 aaa1 555 40 xxx1 40 bbb1 556 10 zzz1 10 aaa1 557 10 yyy1 10 aaa1 558 10 xxx1 10 aaa1 559 20 yyy1 20 ccc1 560 20 xxx1 20 ccc1 561 execute s9 using @min; 562 a1 a2 b1 b2 563 10 zzz1 10 aaa1 564 10 yyy1 10 aaa1 565 10 xxx1 10 aaa1 566 20 yyy1 20 aaa1 567 20 xxx1 20 aaa1 568 30 xxx1 30 aaa1 569 40 xxx1 40 bbb1 570 null null 50 aaa1 571 null null 60 ccc1 572 10 zzz1 10 aaa1 573 10 yyy1 10 aaa1 574 10 xxx1 10 aaa1 575 20 yyy1 20 ccc1 576 20 xxx1 20 ccc1 577 DEALLOCATE PREPARE s1; 578 DEALLOCATE PREPARE s2; 579 DEALLOCATE PREPARE s3; 580 DEALLOCATE PREPARE s4; 581 DEALLOCATE PREPARE s5; 582 DEALLOCATE PREPARE s6; 583 DEALLOCATE PREPARE s7; 584 DEALLOCATE PREPARE s8; 585 DEALLOCATE PREPARE s9; 586 set @a1=10; 587 set @b1=10; 588 prepare s1 from 'select * from t5 where a1 > ? union select * from t6 where b1 > ?'; 589 prepare s2 from 'select * from t5 where a1 > ? union all select * from t6 where b1 > ?'; 590 execute s1 using @a1, @b1; 591 a1 a2 592 20 xxx1 593 30 xxx1 594 20 yyy1 595 40 xxx1 596 20 aaa1 597 30 aaa1 598 40 bbb1 599 50 aaa1 600 60 ccc1 601 20 ccc1 602 execute s2 using @a1, @b1; 603 a1 a2 604 20 xxx1 605 30 xxx1 606 20 yyy1 607 40 xxx1 608 20 aaa1 609 30 aaa1 610 40 bbb1 611 50 aaa1 612 60 ccc1 613 20 ccc1 614 drop table t5; 615 drop table t6; 616 set @maxint=18446744073709551615; 617 select @maxint; 618 @maxint 619 18446744073709551615 620 SELECT @maxint + 0e0; 621 @maxint + 0e0 622 1.8446744073709552E19 623 SELECT 18446744073709551615 + 0e0; 624 18446744073709551615 + 0e0 625 1.8446744073709552E19 626 SELECT @maxint + 0.0; 627 @maxint + 0.0 628 18446744073709551615.0 629 SELECT 18446744073709551615 + 0.0; 630 18446744073709551615 + 0.0 631 18446744073709551615.0 632 PREPARE s FROM 'SELECT 0e0 + ?'; 633 EXECUTE s USING @maxint; 634 0e0 + ? 635 1.8446744073709552E19 636 DEALLOCATE PREPARE s; 637 PREPARE s FROM 'SELECT 0.0 + ?'; 638 EXECUTE s USING @maxint; 639 0.0 + ? 640 18446744073709551615.000000000000000000000000000000 641 DEALLOCATE PREPARE s; 642 PREPARE s FROM 'SELECT 0 + ?'; 643 EXECUTE s USING @maxint; 644 Data truncation: data out of range: data type int64, 645 DEALLOCATE PREPARE s; 646 PREPARE s FROM 'SELECT concat(?,"")'; 647 EXECUTE s USING @maxint; 648 concat(?,"") 649 18446744073709551615 650 DEALLOCATE PREPARE s;