1--echo # 2--echo # Start of 10.0 tests 3--echo # 4 5--echo # 6--echo # MDEV-8468 CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces 7--echo # 8SET sql_mode='STRICT_ALL_TABLES'; 9 10CREATE TABLE t1 (a FLOAT); 11INSERT INTO t1 VALUES ('1 '); 12DROP TABLE t1; 13CREATE TABLE t1 (a DOUBLE); 14INSERT INTO t1 VALUES ('1 '); 15DROP TABLE t1; 16CREATE TABLE t1 (a TINYINT); 17INSERT INTO t1 VALUES ('1 '); 18DROP TABLE t1; 19CREATE TABLE t1 (a SMALLINT); 20INSERT INTO t1 VALUES ('1 '); 21DROP TABLE t1; 22CREATE TABLE t1 (a INT); 23INSERT INTO t1 VALUES ('1 '); 24DROP TABLE t1; 25CREATE TABLE t1 (a BIGINT); 26INSERT INTO t1 VALUES ('1 '); 27DROP TABLE t1; 28CREATE TABLE t1 (a DECIMAL); 29INSERT INTO t1 VALUES ('1 '); 30DROP TABLE t1; 31 32 33CREATE TABLE t1 (a FLOAT); 34--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 35INSERT INTO t1 VALUES (''); 36DROP TABLE t1; 37CREATE TABLE t1 (a DOUBLE); 38--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 39INSERT INTO t1 VALUES (''); 40DROP TABLE t1; 41CREATE TABLE t1 (a TINYINT); 42--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 43INSERT INTO t1 VALUES (''); 44DROP TABLE t1; 45CREATE TABLE t1 (a SMALLINT); 46--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 47INSERT INTO t1 VALUES (''); 48DROP TABLE t1; 49CREATE TABLE t1 (a INT); 50--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 51INSERT INTO t1 VALUES (''); 52DROP TABLE t1; 53CREATE TABLE t1 (a BIGINT); 54--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 55INSERT INTO t1 VALUES (''); 56DROP TABLE t1; 57CREATE TABLE t1 (a DECIMAL); 58--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 59INSERT INTO t1 VALUES (''); 60DROP TABLE t1; 61 62 63CREATE TABLE t1 (a FLOAT); 64--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 65INSERT INTO t1 VALUES ('x'); 66DROP TABLE t1; 67CREATE TABLE t1 (a DOUBLE); 68--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 69INSERT INTO t1 VALUES ('x'); 70DROP TABLE t1; 71CREATE TABLE t1 (a TINYINT); 72--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 73INSERT INTO t1 VALUES ('x'); 74DROP TABLE t1; 75CREATE TABLE t1 (a SMALLINT); 76--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 77INSERT INTO t1 VALUES ('x'); 78DROP TABLE t1; 79CREATE TABLE t1 (a INT); 80--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 81INSERT INTO t1 VALUES ('x'); 82DROP TABLE t1; 83CREATE TABLE t1 (a BIGINT); 84--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 85INSERT INTO t1 VALUES ('x'); 86DROP TABLE t1; 87CREATE TABLE t1 (a DECIMAL); 88--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 89INSERT INTO t1 VALUES ('x'); 90DROP TABLE t1; 91 92 93CREATE TABLE t1 (a FLOAT); 94--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 95INSERT INTO t1 VALUES (' x'); 96DROP TABLE t1; 97CREATE TABLE t1 (a DOUBLE); 98--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 99INSERT INTO t1 VALUES (' x'); 100DROP TABLE t1; 101CREATE TABLE t1 (a TINYINT); 102--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 103INSERT INTO t1 VALUES (' x'); 104DROP TABLE t1; 105CREATE TABLE t1 (a SMALLINT); 106--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 107INSERT INTO t1 VALUES (' x'); 108DROP TABLE t1; 109CREATE TABLE t1 (a INT); 110--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 111INSERT INTO t1 VALUES (' x'); 112DROP TABLE t1; 113CREATE TABLE t1 (a BIGINT); 114--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 115INSERT INTO t1 VALUES (' x'); 116DROP TABLE t1; 117CREATE TABLE t1 (a DECIMAL); 118--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 119INSERT INTO t1 VALUES (' x'); 120DROP TABLE t1; 121 122 123CREATE TABLE t1 (a FLOAT); 124--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 125INSERT INTO t1 VALUES ('.'); 126DROP TABLE t1; 127CREATE TABLE t1 (a DOUBLE); 128--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 129INSERT INTO t1 VALUES ('.'); 130DROP TABLE t1; 131CREATE TABLE t1 (a TINYINT); 132--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 133INSERT INTO t1 VALUES ('.'); 134DROP TABLE t1; 135CREATE TABLE t1 (a SMALLINT); 136--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 137INSERT INTO t1 VALUES ('.'); 138DROP TABLE t1; 139CREATE TABLE t1 (a INT); 140--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 141INSERT INTO t1 VALUES ('.'); 142DROP TABLE t1; 143CREATE TABLE t1 (a BIGINT); 144--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 145INSERT INTO t1 VALUES ('.'); 146DROP TABLE t1; 147CREATE TABLE t1 (a DECIMAL); 148--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 149INSERT INTO t1 VALUES ('.'); 150DROP TABLE t1; 151 152 153CREATE TABLE t1 (a FLOAT); 154--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 155INSERT INTO t1 VALUES ('-'); 156DROP TABLE t1; 157CREATE TABLE t1 (a DOUBLE); 158--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 159INSERT INTO t1 VALUES ('-'); 160DROP TABLE t1; 161CREATE TABLE t1 (a TINYINT); 162--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 163INSERT INTO t1 VALUES ('-'); 164DROP TABLE t1; 165CREATE TABLE t1 (a SMALLINT); 166--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 167INSERT INTO t1 VALUES ('-'); 168DROP TABLE t1; 169CREATE TABLE t1 (a INT); 170--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 171INSERT INTO t1 VALUES ('-'); 172DROP TABLE t1; 173CREATE TABLE t1 (a BIGINT); 174--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 175INSERT INTO t1 VALUES ('-'); 176DROP TABLE t1; 177CREATE TABLE t1 (a DECIMAL); 178--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 179INSERT INTO t1 VALUES ('-'); 180DROP TABLE t1; 181 182 183CREATE TABLE t1 (a FLOAT); 184--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 185INSERT INTO t1 VALUES ('+'); 186DROP TABLE t1; 187CREATE TABLE t1 (a DOUBLE); 188--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 189INSERT INTO t1 VALUES ('+'); 190DROP TABLE t1; 191CREATE TABLE t1 (a TINYINT); 192--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 193INSERT INTO t1 VALUES ('+'); 194DROP TABLE t1; 195CREATE TABLE t1 (a SMALLINT); 196--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 197INSERT INTO t1 VALUES ('+'); 198DROP TABLE t1; 199CREATE TABLE t1 (a INT); 200--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 201INSERT INTO t1 VALUES ('+'); 202DROP TABLE t1; 203CREATE TABLE t1 (a BIGINT); 204--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 205INSERT INTO t1 VALUES ('+'); 206DROP TABLE t1; 207CREATE TABLE t1 (a DECIMAL); 208--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 209INSERT INTO t1 VALUES ('+'); 210DROP TABLE t1; 211 212 213CREATE TABLE t1 (a FLOAT); 214--error WARN_DATA_TRUNCATED 215INSERT INTO t1 VALUES ('1x'); 216DROP TABLE t1; 217CREATE TABLE t1 (a DOUBLE); 218--error WARN_DATA_TRUNCATED 219INSERT INTO t1 VALUES ('1x'); 220DROP TABLE t1; 221CREATE TABLE t1 (a TINYINT); 222--error WARN_DATA_TRUNCATED 223INSERT INTO t1 VALUES ('1x'); 224DROP TABLE t1; 225CREATE TABLE t1 (a SMALLINT); 226--error WARN_DATA_TRUNCATED 227INSERT INTO t1 VALUES ('1x'); 228DROP TABLE t1; 229CREATE TABLE t1 (a INT); 230--error WARN_DATA_TRUNCATED 231INSERT INTO t1 VALUES ('1x'); 232DROP TABLE t1; 233CREATE TABLE t1 (a BIGINT); 234--error WARN_DATA_TRUNCATED 235INSERT INTO t1 VALUES ('1x'); 236DROP TABLE t1; 237CREATE TABLE t1 (a DECIMAL); 238--error WARN_DATA_TRUNCATED 239INSERT INTO t1 VALUES ('1x'); 240DROP TABLE t1; 241 242 243CREATE TABLE t1 (a FLOAT); 244--error WARN_DATA_TRUNCATED 245INSERT INTO t1 VALUES ('1e'); 246DROP TABLE t1; 247CREATE TABLE t1 (a DOUBLE); 248--error WARN_DATA_TRUNCATED 249INSERT INTO t1 VALUES ('1e'); 250DROP TABLE t1; 251CREATE TABLE t1 (a TINYINT); 252--error WARN_DATA_TRUNCATED 253INSERT INTO t1 VALUES ('1e'); 254DROP TABLE t1; 255CREATE TABLE t1 (a SMALLINT); 256--error WARN_DATA_TRUNCATED 257INSERT INTO t1 VALUES ('1e'); 258DROP TABLE t1; 259CREATE TABLE t1 (a INT); 260--error WARN_DATA_TRUNCATED 261INSERT INTO t1 VALUES ('1e'); 262DROP TABLE t1; 263CREATE TABLE t1 (a BIGINT); 264--error WARN_DATA_TRUNCATED 265INSERT INTO t1 VALUES ('1e'); 266DROP TABLE t1; 267CREATE TABLE t1 (a DECIMAL); 268--error WARN_DATA_TRUNCATED 269INSERT INTO t1 VALUES ('1e'); 270DROP TABLE t1; 271 272 273CREATE TABLE t1 (a FLOAT); 274--error WARN_DATA_TRUNCATED 275INSERT INTO t1 VALUES ('1e+'); 276DROP TABLE t1; 277CREATE TABLE t1 (a DOUBLE); 278--error WARN_DATA_TRUNCATED 279INSERT INTO t1 VALUES ('1e+'); 280DROP TABLE t1; 281CREATE TABLE t1 (a TINYINT); 282--error WARN_DATA_TRUNCATED 283INSERT INTO t1 VALUES ('1e+'); 284DROP TABLE t1; 285CREATE TABLE t1 (a SMALLINT); 286--error WARN_DATA_TRUNCATED 287INSERT INTO t1 VALUES ('1e+'); 288DROP TABLE t1; 289CREATE TABLE t1 (a INT); 290--error WARN_DATA_TRUNCATED 291INSERT INTO t1 VALUES ('1e+'); 292DROP TABLE t1; 293CREATE TABLE t1 (a BIGINT); 294--error WARN_DATA_TRUNCATED 295INSERT INTO t1 VALUES ('1e+'); 296DROP TABLE t1; 297CREATE TABLE t1 (a DECIMAL); 298--error WARN_DATA_TRUNCATED 299INSERT INTO t1 VALUES ('1e+'); 300DROP TABLE t1; 301 302 303CREATE TABLE t1 (a FLOAT); 304--error WARN_DATA_TRUNCATED 305INSERT INTO t1 VALUES ('1E-'); 306DROP TABLE t1; 307CREATE TABLE t1 (a DOUBLE); 308--error WARN_DATA_TRUNCATED 309INSERT INTO t1 VALUES ('1E-'); 310DROP TABLE t1; 311CREATE TABLE t1 (a TINYINT); 312--error WARN_DATA_TRUNCATED 313INSERT INTO t1 VALUES ('1E-'); 314DROP TABLE t1; 315CREATE TABLE t1 (a SMALLINT); 316--error WARN_DATA_TRUNCATED 317INSERT INTO t1 VALUES ('1E-'); 318DROP TABLE t1; 319CREATE TABLE t1 (a INT); 320--error WARN_DATA_TRUNCATED 321INSERT INTO t1 VALUES ('1E-'); 322DROP TABLE t1; 323CREATE TABLE t1 (a BIGINT); 324--error WARN_DATA_TRUNCATED 325INSERT INTO t1 VALUES ('1E-'); 326DROP TABLE t1; 327CREATE TABLE t1 (a DECIMAL); 328--error WARN_DATA_TRUNCATED 329INSERT INTO t1 VALUES ('1E-'); 330DROP TABLE t1; 331 332 333CREATE TABLE t1 (a FLOAT); 334--error WARN_DATA_TRUNCATED 335INSERT INTO t1 VALUES ('1Ex'); 336DROP TABLE t1; 337CREATE TABLE t1 (a DOUBLE); 338--error WARN_DATA_TRUNCATED 339INSERT INTO t1 VALUES ('1Ex'); 340DROP TABLE t1; 341CREATE TABLE t1 (a TINYINT); 342--error WARN_DATA_TRUNCATED 343INSERT INTO t1 VALUES ('1Ex'); 344DROP TABLE t1; 345CREATE TABLE t1 (a SMALLINT); 346--error WARN_DATA_TRUNCATED 347INSERT INTO t1 VALUES ('1Ex'); 348DROP TABLE t1; 349CREATE TABLE t1 (a INT); 350--error WARN_DATA_TRUNCATED 351INSERT INTO t1 VALUES ('1Ex'); 352DROP TABLE t1; 353CREATE TABLE t1 (a BIGINT); 354--error WARN_DATA_TRUNCATED 355INSERT INTO t1 VALUES ('1Ex'); 356DROP TABLE t1; 357CREATE TABLE t1 (a DECIMAL); 358--error WARN_DATA_TRUNCATED 359INSERT INTO t1 VALUES ('1Ex'); 360DROP TABLE t1; 361 362 363CREATE TABLE t1 (a FLOAT); 364--error WARN_DATA_TRUNCATED 365INSERT INTO t1 VALUES ('1e+x'); 366DROP TABLE t1; 367CREATE TABLE t1 (a DOUBLE); 368--error WARN_DATA_TRUNCATED 369INSERT INTO t1 VALUES ('1e+x'); 370DROP TABLE t1; 371CREATE TABLE t1 (a TINYINT); 372--error WARN_DATA_TRUNCATED 373INSERT INTO t1 VALUES ('1e+x'); 374DROP TABLE t1; 375CREATE TABLE t1 (a SMALLINT); 376--error WARN_DATA_TRUNCATED 377INSERT INTO t1 VALUES ('1e+x'); 378DROP TABLE t1; 379CREATE TABLE t1 (a INT); 380--error WARN_DATA_TRUNCATED 381INSERT INTO t1 VALUES ('1e+x'); 382DROP TABLE t1; 383CREATE TABLE t1 (a BIGINT); 384--error WARN_DATA_TRUNCATED 385INSERT INTO t1 VALUES ('1e+x'); 386DROP TABLE t1; 387CREATE TABLE t1 (a DECIMAL); 388--error WARN_DATA_TRUNCATED 389INSERT INTO t1 VALUES ('1e+x'); 390DROP TABLE t1; 391 392 393CREATE TABLE t1 (a FLOAT); 394--error ER_WARN_DATA_OUT_OF_RANGE 395INSERT INTO t1 VALUES ('1e1000'); 396DROP TABLE t1; 397CREATE TABLE t1 (a DOUBLE); 398--error ER_WARN_DATA_OUT_OF_RANGE 399INSERT INTO t1 VALUES ('1e1000'); 400DROP TABLE t1; 401CREATE TABLE t1 (a TINYINT); 402--error ER_WARN_DATA_OUT_OF_RANGE 403INSERT INTO t1 VALUES ('1e1000'); 404DROP TABLE t1; 405CREATE TABLE t1 (a SMALLINT); 406--error ER_WARN_DATA_OUT_OF_RANGE 407INSERT INTO t1 VALUES ('1e1000'); 408DROP TABLE t1; 409CREATE TABLE t1 (a INT); 410--error ER_WARN_DATA_OUT_OF_RANGE 411INSERT INTO t1 VALUES ('1e1000'); 412DROP TABLE t1; 413CREATE TABLE t1 (a BIGINT); 414--error ER_WARN_DATA_OUT_OF_RANGE 415INSERT INTO t1 VALUES ('1e1000'); 416DROP TABLE t1; 417CREATE TABLE t1 (a DECIMAL); 418--error ER_WARN_DATA_OUT_OF_RANGE 419INSERT INTO t1 VALUES ('1e1000'); 420DROP TABLE t1; 421 422 423SET sql_mode=DEFAULT; 424 425CREATE TABLE t1 ( 426 f4 FLOAT, 427 f8 DOUBLE, 428 i1 TINYINT, 429 i2 SMALLINT, 430 i4 INT, 431 i8 BIGINT, 432 d DECIMAL 433); 434 435INSERT INTO t1 VALUES ('1 ','1 ','1 ','1 ','1 ','1 ','1 '); 436INSERT IGNORE INTO t1 VALUES ('','','','','','',''); 437INSERT IGNORE INTO t1 VALUES ('x','x','x','x','x','x','x'); 438INSERT IGNORE INTO t1 VALUES (' x',' x',' x',' x',' x',' x',' x'); 439INSERT IGNORE INTO t1 VALUES ('.','.','.','.','.','.','.'); 440INSERT IGNORE INTO t1 VALUES ('-','-','-','-','-','-','-'); 441INSERT IGNORE INTO t1 VALUES ('+','+','+','+','+','+','+'); 442INSERT IGNORE INTO t1 VALUES ('1x','1x','1x','1x','1x','1x','1x'); 443INSERT IGNORE INTO t1 VALUES ('1e','1e','1e','1e','1e','1e','1e'); 444INSERT IGNORE INTO t1 VALUES ('1e-','1e-','1e-','1e-','1e-','1e-','1e-'); 445INSERT IGNORE INTO t1 VALUES ('1E+','1E+','1E+','1E+','1E+','1E+','1E+'); 446INSERT IGNORE INTO t1 VALUES ('1e1000','1e1000','1e1000','1e1000','1e1000','1e1000','1e1000'); 447 448DELETE FROM t1; 449INSERT INTO t1 VALUES (0,0,0,0,0,0,0); 450INSERT INTO t1 VALUES (0,0,0,0,0,0,0); 451INSERT INTO t1 VALUES (1,1,1,1,1,1,1); 452INSERT INTO t1 VALUES (1,1,1,1,1,1,1); 453INSERT INTO t1 VALUES (2,2,2,2,2,2,2); 454INSERT INTO t1 VALUES (2,2,2,2,2,2,2); 455INSERT INTO t1 VALUES (3,3,3,3,3,3,3); 456INSERT INTO t1 VALUES (3,3,3,3,3,3,3); 457INSERT INTO t1 VALUES (4,4,4,4,4,4,4); 458INSERT INTO t1 VALUES (4,4,4,4,4,4,4); 459INSERT INTO t1 VALUES (5,5,5,5,5,5,5); 460INSERT INTO t1 VALUES (5,5,5,5,5,5,5); 461 462SELECT COUNT(*) FROM t1 WHERE f4='1 '; 463SELECT COUNT(*) FROM t1 WHERE f8='1 '; 464SELECT COUNT(*) FROM t1 WHERE i1='1 '; 465SELECT COUNT(*) FROM t1 WHERE i2='1 '; 466SELECT COUNT(*) FROM t1 WHERE i4='1 '; 467SELECT COUNT(*) FROM t1 WHERE i8='1 '; 468SELECT COUNT(*) FROM t1 WHERE d='1 '; 469 470SELECT COUNT(*) FROM t1 WHERE f4=''; 471SELECT COUNT(*) FROM t1 WHERE f8=''; 472SELECT COUNT(*) FROM t1 WHERE i1=''; 473SELECT COUNT(*) FROM t1 WHERE i2=''; 474SELECT COUNT(*) FROM t1 WHERE i4=''; 475SELECT COUNT(*) FROM t1 WHERE i8=''; 476SELECT COUNT(*) FROM t1 WHERE d=''; 477 478SELECT COUNT(*) FROM t1 WHERE f4='x'; 479SELECT COUNT(*) FROM t1 WHERE f8='x'; 480SELECT COUNT(*) FROM t1 WHERE i1='x'; 481SELECT COUNT(*) FROM t1 WHERE i2='x'; 482SELECT COUNT(*) FROM t1 WHERE i4='x'; 483SELECT COUNT(*) FROM t1 WHERE i8='x'; 484SELECT COUNT(*) FROM t1 WHERE d='x'; 485 486SELECT COUNT(*) FROM t1 WHERE f4=' x'; 487SELECT COUNT(*) FROM t1 WHERE f8=' x'; 488SELECT COUNT(*) FROM t1 WHERE i1=' x'; 489SELECT COUNT(*) FROM t1 WHERE i2=' x'; 490SELECT COUNT(*) FROM t1 WHERE i4=' x'; 491SELECT COUNT(*) FROM t1 WHERE i8=' x'; 492SELECT COUNT(*) FROM t1 WHERE d=' x'; 493 494SELECT COUNT(*) FROM t1 WHERE f4='.'; 495SELECT COUNT(*) FROM t1 WHERE f8='.'; 496SELECT COUNT(*) FROM t1 WHERE i1='.'; 497SELECT COUNT(*) FROM t1 WHERE i2='.'; 498SELECT COUNT(*) FROM t1 WHERE i4='.'; 499SELECT COUNT(*) FROM t1 WHERE i8='.'; 500SELECT COUNT(*) FROM t1 WHERE d='.'; 501 502SELECT COUNT(*) FROM t1 WHERE f4='-'; 503SELECT COUNT(*) FROM t1 WHERE f8='-'; 504SELECT COUNT(*) FROM t1 WHERE i1='-'; 505SELECT COUNT(*) FROM t1 WHERE i2='-'; 506SELECT COUNT(*) FROM t1 WHERE i4='-'; 507SELECT COUNT(*) FROM t1 WHERE i8='-'; 508SELECT COUNT(*) FROM t1 WHERE d='-'; 509 510SELECT COUNT(*) FROM t1 WHERE f4='+'; 511SELECT COUNT(*) FROM t1 WHERE f8='+'; 512SELECT COUNT(*) FROM t1 WHERE i1='+'; 513SELECT COUNT(*) FROM t1 WHERE i2='+'; 514SELECT COUNT(*) FROM t1 WHERE i4='+'; 515SELECT COUNT(*) FROM t1 WHERE i8='+'; 516SELECT COUNT(*) FROM t1 WHERE d='+'; 517 518SELECT COUNT(*) FROM t1 WHERE f4='1x'; 519SELECT COUNT(*) FROM t1 WHERE f8='1x'; 520SELECT COUNT(*) FROM t1 WHERE i1='1x'; 521SELECT COUNT(*) FROM t1 WHERE i2='1x'; 522SELECT COUNT(*) FROM t1 WHERE i4='1x'; 523SELECT COUNT(*) FROM t1 WHERE i8='1x'; 524SELECT COUNT(*) FROM t1 WHERE d='1x'; 525 526SELECT COUNT(*) FROM t1 WHERE f4='1e'; 527SELECT COUNT(*) FROM t1 WHERE f8='1e'; 528SELECT COUNT(*) FROM t1 WHERE i1='1e'; 529SELECT COUNT(*) FROM t1 WHERE i2='1e'; 530SELECT COUNT(*) FROM t1 WHERE i4='1e'; 531SELECT COUNT(*) FROM t1 WHERE i8='1e'; 532SELECT COUNT(*) FROM t1 WHERE d='1e'; 533 534SELECT COUNT(*) FROM t1 WHERE f4='1e+'; 535SELECT COUNT(*) FROM t1 WHERE f8='1e+'; 536SELECT COUNT(*) FROM t1 WHERE i1='1e+'; 537SELECT COUNT(*) FROM t1 WHERE i2='1e+'; 538SELECT COUNT(*) FROM t1 WHERE i4='1e+'; 539SELECT COUNT(*) FROM t1 WHERE i8='1e+'; 540SELECT COUNT(*) FROM t1 WHERE d='1e+'; 541 542SELECT COUNT(*) FROM t1 WHERE f4='1E-'; 543SELECT COUNT(*) FROM t1 WHERE f8='1E-'; 544SELECT COUNT(*) FROM t1 WHERE i1='1E-'; 545SELECT COUNT(*) FROM t1 WHERE i2='1E-'; 546SELECT COUNT(*) FROM t1 WHERE i4='1E-'; 547SELECT COUNT(*) FROM t1 WHERE i8='1E-'; 548SELECT COUNT(*) FROM t1 WHERE d='1E-'; 549 550SELECT COUNT(*) FROM t1 WHERE f4='1Ex'; 551SELECT COUNT(*) FROM t1 WHERE f8='1Ex'; 552SELECT COUNT(*) FROM t1 WHERE i1='1Ex'; 553SELECT COUNT(*) FROM t1 WHERE i2='1Ex'; 554SELECT COUNT(*) FROM t1 WHERE i4='1Ex'; 555SELECT COUNT(*) FROM t1 WHERE i8='1Ex'; 556SELECT COUNT(*) FROM t1 WHERE d='1Ex'; 557 558SELECT COUNT(*) FROM t1 WHERE f4='1e+x'; 559SELECT COUNT(*) FROM t1 WHERE f8='1e+x'; 560SELECT COUNT(*) FROM t1 WHERE i1='1e+x'; 561SELECT COUNT(*) FROM t1 WHERE i2='1e+x'; 562SELECT COUNT(*) FROM t1 WHERE i4='1e+x'; 563SELECT COUNT(*) FROM t1 WHERE i8='1e+x'; 564SELECT COUNT(*) FROM t1 WHERE d='1e+x'; 565 566SELECT COUNT(*) FROM t1 WHERE f4='1e1000'; 567SELECT COUNT(*) FROM t1 WHERE f8='1e1000'; 568SELECT COUNT(*) FROM t1 WHERE i1='1e1000'; 569SELECT COUNT(*) FROM t1 WHERE i2='1e1000'; 570SELECT COUNT(*) FROM t1 WHERE i4='1e1000'; 571SELECT COUNT(*) FROM t1 WHERE i8='1e1000'; 572SELECT COUNT(*) FROM t1 WHERE d='1e1000'; 573 574ALTER TABLE t1 575 ADD KEY f4(f4), 576 ADD KEY f8(f8), 577 ADD KEY i1(i1), 578 ADD KEY i2(i2), 579 ADD KEY i4(i4), 580 ADD KEY i8(i8), 581 ADD KEY d(d); 582 583SELECT COUNT(*) FROM t1 FORCE INDEX(f4) WHERE f4='1 '; 584SELECT COUNT(*) FROM t1 FORCE INDEX(f8) WHERE f8='1 '; 585SELECT COUNT(*) FROM t1 FORCE INDEX(i1) WHERE i1='1 '; 586SELECT COUNT(*) FROM t1 FORCE INDEX(i2) WHERE i2='1 '; 587SELECT COUNT(*) FROM t1 FORCE INDEX(i4) WHERE i4='1 '; 588SELECT COUNT(*) FROM t1 FORCE INDEX(i8) WHERE i8='1 '; 589SELECT COUNT(*) FROM t1 FORCE INDEX(d) WHERE d='1 '; 590 591SELECT COUNT(*) FROM t1 FORCE INDEX(f4) WHERE f4=''; 592SELECT COUNT(*) FROM t1 FORCE INDEX(f8) WHERE f8=''; 593SELECT COUNT(*) FROM t1 FORCE INDEX(i1) WHERE i1=''; 594SELECT COUNT(*) FROM t1 FORCE INDEX(i2) WHERE i2=''; 595SELECT COUNT(*) FROM t1 FORCE INDEX(i4) WHERE i4=''; 596SELECT COUNT(*) FROM t1 FORCE INDEX(i8) WHERE i8=''; 597SELECT COUNT(*) FROM t1 FORCE INDEX(d) WHERE d=''; 598 599# TODO: Add tests for all of the other tricky examples (see above) when 600# MDEV-8490 INT,DOUBLE,DECIMAL produce different warnings on comparison 601# is fixed 602 603DROP TABLE t1; 604 605SELECT 606 CAST('1 ' AS SIGNED), 607 CAST('1 ' AS UNSIGNED), 608 CAST('1 ' AS DECIMAL), 609 CAST('1 ' AS DOUBLE); 610 611SELECT 612 CAST('' AS SIGNED), 613 CAST('' AS UNSIGNED), 614 CAST('' AS DECIMAL), 615 CAST('' AS DOUBLE); 616 617SELECT 618 CAST('x' AS SIGNED), 619 CAST('x' AS UNSIGNED), 620 CAST('x' AS DECIMAL), 621 CAST('x' AS DOUBLE); 622 623SELECT 624 CAST(' x' AS SIGNED), 625 CAST(' x' AS UNSIGNED), 626 CAST(' x' AS DECIMAL), 627 CAST(' x' AS DOUBLE); 628 629SELECT 630 CAST('.' AS SIGNED), 631 CAST('.' AS UNSIGNED), 632 CAST('.' AS DECIMAL), 633 CAST('.' AS DOUBLE); 634 635SELECT 636 CAST('-' AS SIGNED), 637 CAST('-' AS UNSIGNED), 638 CAST('-' AS DECIMAL), 639 CAST('-' AS DOUBLE); 640 641SELECT 642 CAST('+' AS SIGNED), 643 CAST('+' AS UNSIGNED), 644 CAST('+' AS DECIMAL), 645 CAST('+' AS DOUBLE); 646 647SELECT 648 CAST('1x' AS SIGNED), 649 CAST('1x' AS UNSIGNED), 650 CAST('1x' AS DECIMAL), 651 CAST('1x' AS DOUBLE); 652 653SELECT 654 CAST('1e' AS SIGNED), 655 CAST('1e' AS UNSIGNED), 656 CAST('1e' AS DECIMAL), 657 CAST('1e' AS DOUBLE); 658 659SELECT 660 CAST('1e-' AS SIGNED), 661 CAST('1e-' AS UNSIGNED), 662 CAST('1e-' AS DECIMAL), 663 CAST('1e-' AS DOUBLE); 664 665SELECT 666 CAST('1E+' AS SIGNED), 667 CAST('1E+' AS UNSIGNED), 668 CAST('1E+' AS DECIMAL), 669 CAST('1E+' AS DOUBLE); 670 671# TODO: Uncomment this test when 672# MDEV-8468 CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces 673# is fixed 674#SELECT 675# CAST('1e1000' AS SIGNED), 676# CAST('1e1000' AS UNSIGNED), 677# CAST('1e1000' AS DECIMAL), 678# CAST('1e1000' AS DOUBLE); 679 680 681--echo # 682--echo # Start of 10.1 tests 683--echo # 684 685--echo # 686--echo # MDEV-9653 Assertion `length || !scale' failed in uint my_decimal_length_to_precision(uint, uint, bool) 687--echo # 688 689--disable_ps_protocol 690--enable_metadata 691SELECT NULL+1 AS c0, 692 COALESCE(NULL)+1 AS c2, 693 COALESCE(COALESCE(NULL))+1 AS c3, 694 ''+1 AS c4, 695 COALESCE('')+1 AS c5, 696 COALESCE(COALESCE(''))+1 AS c6; 697--disable_metadata 698--enable_ps_protocol 699 700--echo # 701--echo # Start of 10.2 tests 702--echo # 703 704# 705# MDEV-8659 Conflicting declaration is accepted: INT SIGNED ZEROFILL 706# 707--error ER_PARSE_ERROR 708CREATE TABLE t1 (a INT SIGNED ZEROFILL); 709--error ER_PARSE_ERROR 710CREATE TABLE t1 (a INT SIGNED UNSIGNED); 711--error ER_PARSE_ERROR 712CREATE TABLE t1 (a INT ZEROFILL UNSIGNED ZEROFILL); 713 714# documented syntax: 715CREATE OR REPLACE TABLE t1 (a INT SIGNED); 716CREATE OR REPLACE TABLE t1 (a INT UNSIGNED); 717CREATE OR REPLACE TABLE t1 (a INT ZEROFILL); 718# not documented, supported for backward compatibility 719CREATE OR REPLACE TABLE t1 (a INT UNSIGNED ZEROFILL); 720CREATE OR REPLACE TABLE t1 (a INT ZEROFILL UNSIGNED); 721DROP TABLE t1; 722