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