diff options
Diffstat (limited to 'mysql-test/main/type_num.test')
| -rw-r--r-- | mysql-test/main/type_num.test | 721 |
1 files changed, 721 insertions, 0 deletions
diff --git a/mysql-test/main/type_num.test b/mysql-test/main/type_num.test new file mode 100644 index 00000000000..cc715d6cda2 --- /dev/null +++ b/mysql-test/main/type_num.test @@ -0,0 +1,721 @@ +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-8468 CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces +--echo # +SET sql_mode='STRICT_ALL_TABLES'; + +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES ('1 '); +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES ('1 '); +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +INSERT INTO t1 VALUES ('1 '); +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +INSERT INTO t1 VALUES ('1 '); +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES ('1 '); +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES ('1 '); +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +INSERT INTO t1 VALUES ('1 '); +DROP TABLE t1; + + +CREATE TABLE t1 (a FLOAT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (''); +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (''); +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (''); +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (''); +DROP TABLE t1; +CREATE TABLE t1 (a INT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (''); +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (''); +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (''); +DROP TABLE t1; + + +CREATE TABLE t1 (a FLOAT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('x'); +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('x'); +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('x'); +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('x'); +DROP TABLE t1; +CREATE TABLE t1 (a INT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('x'); +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('x'); +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('x'); +DROP TABLE t1; + + +CREATE TABLE t1 (a FLOAT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (' x'); +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (' x'); +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (' x'); +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (' x'); +DROP TABLE t1; +CREATE TABLE t1 (a INT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (' x'); +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (' x'); +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (' x'); +DROP TABLE t1; + + +CREATE TABLE t1 (a FLOAT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('.'); +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('.'); +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('.'); +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('.'); +DROP TABLE t1; +CREATE TABLE t1 (a INT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('.'); +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('.'); +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('.'); +DROP TABLE t1; + + +CREATE TABLE t1 (a FLOAT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('-'); +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('-'); +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('-'); +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('-'); +DROP TABLE t1; +CREATE TABLE t1 (a INT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('-'); +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('-'); +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('-'); +DROP TABLE t1; + + +CREATE TABLE t1 (a FLOAT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('+'); +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('+'); +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('+'); +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('+'); +DROP TABLE t1; +CREATE TABLE t1 (a INT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('+'); +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('+'); +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES ('+'); +DROP TABLE t1; + + +CREATE TABLE t1 (a FLOAT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1x'); +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1x'); +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1x'); +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1x'); +DROP TABLE t1; +CREATE TABLE t1 (a INT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1x'); +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1x'); +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1x'); +DROP TABLE t1; + + +CREATE TABLE t1 (a FLOAT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e'); +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e'); +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e'); +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e'); +DROP TABLE t1; +CREATE TABLE t1 (a INT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e'); +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e'); +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e'); +DROP TABLE t1; + + +CREATE TABLE t1 (a FLOAT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e+'); +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e+'); +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e+'); +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e+'); +DROP TABLE t1; +CREATE TABLE t1 (a INT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e+'); +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e+'); +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e+'); +DROP TABLE t1; + + +CREATE TABLE t1 (a FLOAT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1E-'); +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1E-'); +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1E-'); +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1E-'); +DROP TABLE t1; +CREATE TABLE t1 (a INT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1E-'); +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1E-'); +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1E-'); +DROP TABLE t1; + + +CREATE TABLE t1 (a FLOAT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1Ex'); +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1Ex'); +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1Ex'); +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1Ex'); +DROP TABLE t1; +CREATE TABLE t1 (a INT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1Ex'); +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1Ex'); +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1Ex'); +DROP TABLE t1; + + +CREATE TABLE t1 (a FLOAT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e+x'); +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e+x'); +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e+x'); +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e+x'); +DROP TABLE t1; +CREATE TABLE t1 (a INT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e+x'); +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e+x'); +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +--error WARN_DATA_TRUNCATED +INSERT INTO t1 VALUES ('1e+x'); +DROP TABLE t1; + + +CREATE TABLE t1 (a FLOAT); +--error ER_WARN_DATA_OUT_OF_RANGE +INSERT INTO t1 VALUES ('1e1000'); +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +--error ER_WARN_DATA_OUT_OF_RANGE +INSERT INTO t1 VALUES ('1e1000'); +DROP TABLE t1; +CREATE TABLE t1 (a TINYINT); +--error ER_WARN_DATA_OUT_OF_RANGE +INSERT INTO t1 VALUES ('1e1000'); +DROP TABLE t1; +CREATE TABLE t1 (a SMALLINT); +--error ER_WARN_DATA_OUT_OF_RANGE +INSERT INTO t1 VALUES ('1e1000'); +DROP TABLE t1; +CREATE TABLE t1 (a INT); +--error ER_WARN_DATA_OUT_OF_RANGE +INSERT INTO t1 VALUES ('1e1000'); +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +--error ER_WARN_DATA_OUT_OF_RANGE +INSERT INTO t1 VALUES ('1e1000'); +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL); +--error ER_WARN_DATA_OUT_OF_RANGE +INSERT INTO t1 VALUES ('1e1000'); +DROP TABLE t1; + + +SET sql_mode=DEFAULT; + +CREATE TABLE t1 ( + f4 FLOAT, + f8 DOUBLE, + i1 TINYINT, + i2 SMALLINT, + i4 INT, + i8 BIGINT, + d DECIMAL +); + +INSERT INTO t1 VALUES ('1 ','1 ','1 ','1 ','1 ','1 ','1 '); +INSERT IGNORE INTO t1 VALUES ('','','','','','',''); +INSERT IGNORE INTO t1 VALUES ('x','x','x','x','x','x','x'); +INSERT IGNORE INTO t1 VALUES (' x',' x',' x',' x',' x',' x',' x'); +INSERT IGNORE INTO t1 VALUES ('.','.','.','.','.','.','.'); +INSERT IGNORE INTO t1 VALUES ('-','-','-','-','-','-','-'); +INSERT IGNORE INTO t1 VALUES ('+','+','+','+','+','+','+'); +INSERT IGNORE INTO t1 VALUES ('1x','1x','1x','1x','1x','1x','1x'); +INSERT IGNORE INTO t1 VALUES ('1e','1e','1e','1e','1e','1e','1e'); +INSERT IGNORE INTO t1 VALUES ('1e-','1e-','1e-','1e-','1e-','1e-','1e-'); +INSERT IGNORE INTO t1 VALUES ('1E+','1E+','1E+','1E+','1E+','1E+','1E+'); +INSERT IGNORE INTO t1 VALUES ('1e1000','1e1000','1e1000','1e1000','1e1000','1e1000','1e1000'); + +DELETE FROM t1; +INSERT INTO t1 VALUES (0,0,0,0,0,0,0); +INSERT INTO t1 VALUES (0,0,0,0,0,0,0); +INSERT INTO t1 VALUES (1,1,1,1,1,1,1); +INSERT INTO t1 VALUES (1,1,1,1,1,1,1); +INSERT INTO t1 VALUES (2,2,2,2,2,2,2); +INSERT INTO t1 VALUES (2,2,2,2,2,2,2); +INSERT INTO t1 VALUES (3,3,3,3,3,3,3); +INSERT INTO t1 VALUES (3,3,3,3,3,3,3); +INSERT INTO t1 VALUES (4,4,4,4,4,4,4); +INSERT INTO t1 VALUES (4,4,4,4,4,4,4); +INSERT INTO t1 VALUES (5,5,5,5,5,5,5); +INSERT INTO t1 VALUES (5,5,5,5,5,5,5); + +SELECT COUNT(*) FROM t1 WHERE f4='1 '; +SELECT COUNT(*) FROM t1 WHERE f8='1 '; +SELECT COUNT(*) FROM t1 WHERE i1='1 '; +SELECT COUNT(*) FROM t1 WHERE i2='1 '; +SELECT COUNT(*) FROM t1 WHERE i4='1 '; +SELECT COUNT(*) FROM t1 WHERE i8='1 '; +SELECT COUNT(*) FROM t1 WHERE d='1 '; + +SELECT COUNT(*) FROM t1 WHERE f4=''; +SELECT COUNT(*) FROM t1 WHERE f8=''; +SELECT COUNT(*) FROM t1 WHERE i1=''; +SELECT COUNT(*) FROM t1 WHERE i2=''; +SELECT COUNT(*) FROM t1 WHERE i4=''; +SELECT COUNT(*) FROM t1 WHERE i8=''; +SELECT COUNT(*) FROM t1 WHERE d=''; + +SELECT COUNT(*) FROM t1 WHERE f4='x'; +SELECT COUNT(*) FROM t1 WHERE f8='x'; +SELECT COUNT(*) FROM t1 WHERE i1='x'; +SELECT COUNT(*) FROM t1 WHERE i2='x'; +SELECT COUNT(*) FROM t1 WHERE i4='x'; +SELECT COUNT(*) FROM t1 WHERE i8='x'; +SELECT COUNT(*) FROM t1 WHERE d='x'; + +SELECT COUNT(*) FROM t1 WHERE f4=' x'; +SELECT COUNT(*) FROM t1 WHERE f8=' x'; +SELECT COUNT(*) FROM t1 WHERE i1=' x'; +SELECT COUNT(*) FROM t1 WHERE i2=' x'; +SELECT COUNT(*) FROM t1 WHERE i4=' x'; +SELECT COUNT(*) FROM t1 WHERE i8=' x'; +SELECT COUNT(*) FROM t1 WHERE d=' x'; + +SELECT COUNT(*) FROM t1 WHERE f4='.'; +SELECT COUNT(*) FROM t1 WHERE f8='.'; +SELECT COUNT(*) FROM t1 WHERE i1='.'; +SELECT COUNT(*) FROM t1 WHERE i2='.'; +SELECT COUNT(*) FROM t1 WHERE i4='.'; +SELECT COUNT(*) FROM t1 WHERE i8='.'; +SELECT COUNT(*) FROM t1 WHERE d='.'; + +SELECT COUNT(*) FROM t1 WHERE f4='-'; +SELECT COUNT(*) FROM t1 WHERE f8='-'; +SELECT COUNT(*) FROM t1 WHERE i1='-'; +SELECT COUNT(*) FROM t1 WHERE i2='-'; +SELECT COUNT(*) FROM t1 WHERE i4='-'; +SELECT COUNT(*) FROM t1 WHERE i8='-'; +SELECT COUNT(*) FROM t1 WHERE d='-'; + +SELECT COUNT(*) FROM t1 WHERE f4='+'; +SELECT COUNT(*) FROM t1 WHERE f8='+'; +SELECT COUNT(*) FROM t1 WHERE i1='+'; +SELECT COUNT(*) FROM t1 WHERE i2='+'; +SELECT COUNT(*) FROM t1 WHERE i4='+'; +SELECT COUNT(*) FROM t1 WHERE i8='+'; +SELECT COUNT(*) FROM t1 WHERE d='+'; + +SELECT COUNT(*) FROM t1 WHERE f4='1x'; +SELECT COUNT(*) FROM t1 WHERE f8='1x'; +SELECT COUNT(*) FROM t1 WHERE i1='1x'; +SELECT COUNT(*) FROM t1 WHERE i2='1x'; +SELECT COUNT(*) FROM t1 WHERE i4='1x'; +SELECT COUNT(*) FROM t1 WHERE i8='1x'; +SELECT COUNT(*) FROM t1 WHERE d='1x'; + +SELECT COUNT(*) FROM t1 WHERE f4='1e'; +SELECT COUNT(*) FROM t1 WHERE f8='1e'; +SELECT COUNT(*) FROM t1 WHERE i1='1e'; +SELECT COUNT(*) FROM t1 WHERE i2='1e'; +SELECT COUNT(*) FROM t1 WHERE i4='1e'; +SELECT COUNT(*) FROM t1 WHERE i8='1e'; +SELECT COUNT(*) FROM t1 WHERE d='1e'; + +SELECT COUNT(*) FROM t1 WHERE f4='1e+'; +SELECT COUNT(*) FROM t1 WHERE f8='1e+'; +SELECT COUNT(*) FROM t1 WHERE i1='1e+'; +SELECT COUNT(*) FROM t1 WHERE i2='1e+'; +SELECT COUNT(*) FROM t1 WHERE i4='1e+'; +SELECT COUNT(*) FROM t1 WHERE i8='1e+'; +SELECT COUNT(*) FROM t1 WHERE d='1e+'; + +SELECT COUNT(*) FROM t1 WHERE f4='1E-'; +SELECT COUNT(*) FROM t1 WHERE f8='1E-'; +SELECT COUNT(*) FROM t1 WHERE i1='1E-'; +SELECT COUNT(*) FROM t1 WHERE i2='1E-'; +SELECT COUNT(*) FROM t1 WHERE i4='1E-'; +SELECT COUNT(*) FROM t1 WHERE i8='1E-'; +SELECT COUNT(*) FROM t1 WHERE d='1E-'; + +SELECT COUNT(*) FROM t1 WHERE f4='1Ex'; +SELECT COUNT(*) FROM t1 WHERE f8='1Ex'; +SELECT COUNT(*) FROM t1 WHERE i1='1Ex'; +SELECT COUNT(*) FROM t1 WHERE i2='1Ex'; +SELECT COUNT(*) FROM t1 WHERE i4='1Ex'; +SELECT COUNT(*) FROM t1 WHERE i8='1Ex'; +SELECT COUNT(*) FROM t1 WHERE d='1Ex'; + +SELECT COUNT(*) FROM t1 WHERE f4='1e+x'; +SELECT COUNT(*) FROM t1 WHERE f8='1e+x'; +SELECT COUNT(*) FROM t1 WHERE i1='1e+x'; +SELECT COUNT(*) FROM t1 WHERE i2='1e+x'; +SELECT COUNT(*) FROM t1 WHERE i4='1e+x'; +SELECT COUNT(*) FROM t1 WHERE i8='1e+x'; +SELECT COUNT(*) FROM t1 WHERE d='1e+x'; + +SELECT COUNT(*) FROM t1 WHERE f4='1e1000'; +SELECT COUNT(*) FROM t1 WHERE f8='1e1000'; +SELECT COUNT(*) FROM t1 WHERE i1='1e1000'; +SELECT COUNT(*) FROM t1 WHERE i2='1e1000'; +SELECT COUNT(*) FROM t1 WHERE i4='1e1000'; +SELECT COUNT(*) FROM t1 WHERE i8='1e1000'; +SELECT COUNT(*) FROM t1 WHERE d='1e1000'; + +ALTER TABLE t1 + ADD KEY f4(f4), + ADD KEY f8(f8), + ADD KEY i1(i1), + ADD KEY i2(i2), + ADD KEY i4(i4), + ADD KEY i8(i8), + ADD KEY d(d); + +SELECT COUNT(*) FROM t1 FORCE INDEX(f4) WHERE f4='1 '; +SELECT COUNT(*) FROM t1 FORCE INDEX(f8) WHERE f8='1 '; +SELECT COUNT(*) FROM t1 FORCE INDEX(i1) WHERE i1='1 '; +SELECT COUNT(*) FROM t1 FORCE INDEX(i2) WHERE i2='1 '; +SELECT COUNT(*) FROM t1 FORCE INDEX(i4) WHERE i4='1 '; +SELECT COUNT(*) FROM t1 FORCE INDEX(i8) WHERE i8='1 '; +SELECT COUNT(*) FROM t1 FORCE INDEX(d) WHERE d='1 '; + +SELECT COUNT(*) FROM t1 FORCE INDEX(f4) WHERE f4=''; +SELECT COUNT(*) FROM t1 FORCE INDEX(f8) WHERE f8=''; +SELECT COUNT(*) FROM t1 FORCE INDEX(i1) WHERE i1=''; +SELECT COUNT(*) FROM t1 FORCE INDEX(i2) WHERE i2=''; +SELECT COUNT(*) FROM t1 FORCE INDEX(i4) WHERE i4=''; +SELECT COUNT(*) FROM t1 FORCE INDEX(i8) WHERE i8=''; +SELECT COUNT(*) FROM t1 FORCE INDEX(d) WHERE d=''; + +# TODO: Add tests for all of the other tricky examples (see above) when +# MDEV-8490 INT,DOUBLE,DECIMAL produce different warnings on comparison +# is fixed + +DROP TABLE t1; + +SELECT + CAST('1 ' AS SIGNED), + CAST('1 ' AS UNSIGNED), + CAST('1 ' AS DECIMAL), + CAST('1 ' AS DOUBLE); + +SELECT + CAST('' AS SIGNED), + CAST('' AS UNSIGNED), + CAST('' AS DECIMAL), + CAST('' AS DOUBLE); + +SELECT + CAST('x' AS SIGNED), + CAST('x' AS UNSIGNED), + CAST('x' AS DECIMAL), + CAST('x' AS DOUBLE); + +SELECT + CAST(' x' AS SIGNED), + CAST(' x' AS UNSIGNED), + CAST(' x' AS DECIMAL), + CAST(' x' AS DOUBLE); + +SELECT + CAST('.' AS SIGNED), + CAST('.' AS UNSIGNED), + CAST('.' AS DECIMAL), + CAST('.' AS DOUBLE); + +SELECT + CAST('-' AS SIGNED), + CAST('-' AS UNSIGNED), + CAST('-' AS DECIMAL), + CAST('-' AS DOUBLE); + +SELECT + CAST('+' AS SIGNED), + CAST('+' AS UNSIGNED), + CAST('+' AS DECIMAL), + CAST('+' AS DOUBLE); + +SELECT + CAST('1x' AS SIGNED), + CAST('1x' AS UNSIGNED), + CAST('1x' AS DECIMAL), + CAST('1x' AS DOUBLE); + +SELECT + CAST('1e' AS SIGNED), + CAST('1e' AS UNSIGNED), + CAST('1e' AS DECIMAL), + CAST('1e' AS DOUBLE); + +SELECT + CAST('1e-' AS SIGNED), + CAST('1e-' AS UNSIGNED), + CAST('1e-' AS DECIMAL), + CAST('1e-' AS DOUBLE); + +SELECT + CAST('1E+' AS SIGNED), + CAST('1E+' AS UNSIGNED), + CAST('1E+' AS DECIMAL), + CAST('1E+' AS DOUBLE); + +# TODO: Uncomment this test when +# MDEV-8468 CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces +# is fixed +#SELECT +# CAST('1e1000' AS SIGNED), +# CAST('1e1000' AS UNSIGNED), +# CAST('1e1000' AS DECIMAL), +# CAST('1e1000' AS DOUBLE); + + +--echo # +--echo # Start of 10.1 tests +--echo # + +--echo # +--echo # MDEV-9653 Assertion `length || !scale' failed in uint my_decimal_length_to_precision(uint, uint, bool) +--echo # + +--disable_ps_protocol +--enable_metadata +SELECT NULL+1 AS c0, + COALESCE(NULL)+1 AS c2, + COALESCE(COALESCE(NULL))+1 AS c3, + ''+1 AS c4, + COALESCE('')+1 AS c5, + COALESCE(COALESCE(''))+1 AS c6; +--disable_metadata +--enable_ps_protocol + +--echo # +--echo # Start of 10.2 tests +--echo # + +# +# MDEV-8659 Conflicting declaration is accepted: INT SIGNED ZEROFILL +# +--error ER_PARSE_ERROR +CREATE TABLE t1 (a INT SIGNED ZEROFILL); +--error ER_PARSE_ERROR +CREATE TABLE t1 (a INT SIGNED UNSIGNED); +--error ER_PARSE_ERROR +CREATE TABLE t1 (a INT ZEROFILL UNSIGNED ZEROFILL); + +# documented syntax: +CREATE OR REPLACE TABLE t1 (a INT SIGNED); +CREATE OR REPLACE TABLE t1 (a INT UNSIGNED); +CREATE OR REPLACE TABLE t1 (a INT ZEROFILL); +# not documented, supported for backward compatibility +CREATE OR REPLACE TABLE t1 (a INT UNSIGNED ZEROFILL); +CREATE OR REPLACE TABLE t1 (a INT ZEROFILL UNSIGNED); +DROP TABLE t1; |