diff options
Diffstat (limited to 'mysql-test/main')
64 files changed, 1164 insertions, 526 deletions
diff --git a/mysql-test/main/check_constraint.result b/mysql-test/main/check_constraint.result index dab1d61cdb9..a6480bb6133 100644 --- a/mysql-test/main/check_constraint.result +++ b/mysql-test/main/check_constraint.result @@ -161,32 +161,32 @@ EmployeeID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(30) NOT NULL CHECK (CHAR_LENGTH(FirstName > 2)) ); INSERT INTO t1 VALUES (NULL, 'Ken'); -ERROR 22007: Truncated incorrect DOUBLE value: 'Ken' +ERROR 22007: Truncated incorrect DECIMAL value: 'Ken' SHOW WARNINGS; Level Code Message -Error 1292 Truncated incorrect DOUBLE value: 'Ken' +Error 1292 Truncated incorrect DECIMAL value: 'Ken' Error 4025 CONSTRAINT `t1.FirstName` failed for `test`.`t1` INSERT INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian'); -ERROR 22007: Truncated incorrect DOUBLE value: 'Ken' +ERROR 22007: Truncated incorrect DECIMAL value: 'Ken' SHOW WARNINGS; Level Code Message -Error 1292 Truncated incorrect DOUBLE value: 'Ken' +Error 1292 Truncated incorrect DECIMAL value: 'Ken' Error 4025 CONSTRAINT `t1.FirstName` failed for `test`.`t1` INSERT IGNORE INTO t1 VALUES (NULL, 'Ken'); Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'Ken' +Warning 1292 Truncated incorrect DECIMAL value: 'Ken' INSERT IGNORE INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian'); Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'Ken' -Warning 1292 Truncated incorrect DOUBLE value: 'Brian' +Warning 1292 Truncated incorrect DECIMAL value: 'Ken' +Warning 1292 Truncated incorrect DECIMAL value: 'Brian' set sql_mode=""; INSERT INTO t1 VALUES (NULL, 'Ken'); Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'Ken' +Warning 1292 Truncated incorrect DECIMAL value: 'Ken' INSERT INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian'); Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'Ken' -Warning 1292 Truncated incorrect DOUBLE value: 'Brian' +Warning 1292 Truncated incorrect DECIMAL value: 'Ken' +Warning 1292 Truncated incorrect DECIMAL value: 'Brian' set sql_mode=default; select * from t1; EmployeeID FirstName @@ -240,13 +240,13 @@ drop table t1; # create table t1 (v1 bigint check (v1 not in ('x' , 'x111'))) ; Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' -Warning 1292 Truncated incorrect DOUBLE value: 'x111' +Warning 1292 Truncated incorrect DECIMAL value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'x111' select * from t1; v1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' -Warning 1292 Truncated incorrect DOUBLE value: 'x111' +Warning 1292 Truncated incorrect DECIMAL value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'x111' select v1 from t1; v1 select * from t1; @@ -260,8 +260,8 @@ flush tables; select * from t1; v1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' -Warning 1292 Truncated incorrect DOUBLE value: 'x111' +Warning 1292 Truncated incorrect DECIMAL value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'x111' select * from t1; v1 deallocate prepare stmt; diff --git a/mysql-test/main/compare.result b/mysql-test/main/compare.result index c4650014326..b8883784d21 100644 --- a/mysql-test/main/compare.result +++ b/mysql-test/main/compare.result @@ -47,11 +47,11 @@ insert into t1 values (0x01,0x01); select * from t1 where a=b; a b Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '\x01' +Warning 1292 Truncated incorrect DECIMAL value: '\x01' select * from t1 where a=b and b=0x01; a b Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '\x01' +Warning 1292 Truncated incorrect DECIMAL value: '\x01' drop table if exists t1; CREATE TABLE t1 (b int(2) zerofill, c int(2) zerofill); INSERT INTO t1 (b,c) VALUES (1,2), (1,1), (2,2); diff --git a/mysql-test/main/create_windows.result b/mysql-test/main/create_windows.result new file mode 100644 index 00000000000..c064eb7c86f --- /dev/null +++ b/mysql-test/main/create_windows.result @@ -0,0 +1,2 @@ +create table `...................................................`(i int); +ERROR HY000: Can't create table `test`.`...................................................` (errno: 38 "Filename too long") diff --git a/mysql-test/main/create_windows.test b/mysql-test/main/create_windows.test new file mode 100644 index 00000000000..6fe99f6aeb6 --- /dev/null +++ b/mysql-test/main/create_windows.test @@ -0,0 +1,10 @@ +--source include/windows.inc + +# MDEV-28746 Wrong error code ER_BAD_DB_ERROR for long filenames + +# There are 51 dots in the table name below, with every dot encoded with 5 +# bytes in "mysql file name encoding", making the filename length 255 byte. +# Adding drive and extension makes it exceed MAX_PATH= 260 bytes +let $t = `SELECT(REPEAT('.',51))`; +--error ER_CANT_CREATE_TABLE +eval create table `$t`(i int); diff --git a/mysql-test/main/ctype_cp1251.result b/mysql-test/main/ctype_cp1251.result index b0e6e1e6506..baa7a038e0e 100644 --- a/mysql-test/main/ctype_cp1251.result +++ b/mysql-test/main/ctype_cp1251.result @@ -3373,17 +3373,17 @@ SELECT * FROM t1 WHERE a IN (1,2,3); a 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '`1' +Warning 1292 Truncated incorrect DECIMAL value: '`1' SELECT * FROM t1 WHERE a IN (1,2,3) AND a=' 1'; a 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '`1' +Warning 1292 Truncated incorrect DECIMAL value: '`1' SELECT * FROM t1 WHERE a IN (1,2,3,'4') AND a=' 1'; a 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '`1' +Warning 1292 Truncated incorrect DECIMAL value: '`1' EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2,3) AND a=' 1'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where diff --git a/mysql-test/main/ctype_utf8.result b/mysql-test/main/ctype_utf8.result index 34f7530e815..24a3ef232c6 100644 --- a/mysql-test/main/ctype_utf8.result +++ b/mysql-test/main/ctype_utf8.result @@ -10288,7 +10288,7 @@ SELECT * FROM t1 WHERE a=10; a 1e1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1é1' +Warning 1292 Truncated incorrect DECIMAL value: '1é1' SELECT * FROM t1 WHERE a='1e1'; a 1e1 @@ -10297,12 +10297,12 @@ SELECT * FROM t1 WHERE a=10 AND a='1e1'; a 1e1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1é1' +Warning 1292 Truncated incorrect DECIMAL value: '1é1' SELECT * FROM t1 WHERE (a,a)=(10,'1e1'); a 1e1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1é1' +Warning 1292 Truncated incorrect DECIMAL value: '1é1' EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a,a)=(10,'1e1'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where @@ -10319,17 +10319,17 @@ SELECT * FROM t1 WHERE a IN (1,2); a 1ë1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1ë1' +Warning 1292 Truncated incorrect DECIMAL value: '1ë1' SELECT * FROM t1 WHERE a IN (1,2) AND a='1ë1'; a 1ë1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1ë1' +Warning 1292 Truncated incorrect DECIMAL value: '1ë1' SELECT * FROM t1 WHERE a IN (1,2,'x') AND a='1ë1'; a 1ë1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1ë1' +Warning 1292 Truncated incorrect DECIMAL value: '1ë1' EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2) AND a='1ë1'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where diff --git a/mysql-test/main/distinct.result b/mysql-test/main/distinct.result index 8fcc45e740a..93a1ea834df 100644 --- a/mysql-test/main/distinct.result +++ b/mysql-test/main/distinct.result @@ -677,13 +677,13 @@ a A 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'A' +Warning 1292 Truncated incorrect DECIMAL value: 'A' SELECT DISTINCT a FROM t1 WHERE a=0; a A 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'A' +Warning 1292 Truncated incorrect DECIMAL value: 'A' DROP TABLE t1; CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06'); diff --git a/mysql-test/main/func_debug.result b/mysql-test/main/func_debug.result index 47bbced730b..a120411d4b5 100644 --- a/mysql-test/main/func_debug.result +++ b/mysql-test/main/func_debug.result @@ -774,28 +774,28 @@ SELECT 'a' IN ('a',2); 1 Warnings: Note 1105 DBUG: [0] arg=1 handler=0 (longblob) -Note 1105 DBUG: [1] arg=2 handler=1 (double) +Note 1105 DBUG: [1] arg=2 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no SELECT 'a' IN ('a',2,NULL); 'a' IN ('a',2,NULL) 1 Warnings: Note 1105 DBUG: [0] arg=1 handler=0 (longblob) -Note 1105 DBUG: [1] arg=2 handler=1 (double) +Note 1105 DBUG: [1] arg=2 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no SELECT 'a' NOT IN ('a',2); 'a' NOT IN ('a',2) 0 Warnings: Note 1105 DBUG: [0] arg=1 handler=0 (longblob) -Note 1105 DBUG: [1] arg=2 handler=1 (double) +Note 1105 DBUG: [1] arg=2 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no SELECT 'a' NOT IN ('a',2,NULL); 'a' NOT IN ('a',2,NULL) 0 Warnings: Note 1105 DBUG: [0] arg=1 handler=0 (longblob) -Note 1105 DBUG: [1] arg=2 handler=1 (double) +Note 1105 DBUG: [1] arg=2 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no SELECT TIME'10:20:30' IN (1,TIME'10:20:30'); TIME'10:20:30' IN (1,TIME'10:20:30') @@ -999,25 +999,25 @@ SELECT a IN (1,'1') FROM t1; a IN (1,'1') Warnings: Note 1105 DBUG: [0] arg=1 handler=0 (bigint) -Note 1105 DBUG: [1] arg=2 handler=1 (double) +Note 1105 DBUG: [1] arg=2 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no SELECT a IN (1,'1',NULL) FROM t1; a IN (1,'1',NULL) Warnings: Note 1105 DBUG: [0] arg=1 handler=0 (bigint) -Note 1105 DBUG: [1] arg=2 handler=1 (double) +Note 1105 DBUG: [1] arg=2 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no SELECT a NOT IN (1,'1') FROM t1; a NOT IN (1,'1') Warnings: Note 1105 DBUG: [0] arg=1 handler=0 (bigint) -Note 1105 DBUG: [1] arg=2 handler=1 (double) +Note 1105 DBUG: [1] arg=2 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no SELECT a NOT IN (1,'1',NULL) FROM t1; a NOT IN (1,'1',NULL) Warnings: Note 1105 DBUG: [0] arg=1 handler=0 (bigint) -Note 1105 DBUG: [1] arg=2 handler=1 (double) +Note 1105 DBUG: [1] arg=2 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no SELECT a IN (1,TIME'10:20:30') FROM t1; a IN (1,TIME'10:20:30') @@ -1197,7 +1197,7 @@ SELECT a IN ('a',1) FROM t1; a IN ('a',1) Warnings: Note 1105 DBUG: [0] arg=1 handler=0 (longblob) -Note 1105 DBUG: [1] arg=2 handler=1 (double) +Note 1105 DBUG: [1] arg=2 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no SELECT a IN ('a',TIME'10:20:30') FROM t1; a IN ('a',TIME'10:20:30') @@ -1209,7 +1209,7 @@ SELECT a NOT IN ('a',1) FROM t1; a NOT IN ('a',1) Warnings: Note 1105 DBUG: [0] arg=1 handler=0 (longblob) -Note 1105 DBUG: [1] arg=2 handler=1 (double) +Note 1105 DBUG: [1] arg=2 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no SELECT a NOT IN ('a',TIME'10:20:30') FROM t1; a NOT IN ('a',TIME'10:20:30') @@ -1581,7 +1581,7 @@ A NULL B NULL Warnings: Note 1105 DBUG: [0] arg=2 handler=0 (longblob) -Note 1105 DBUG: [1] arg=3 handler=1 (double) +Note 1105 DBUG: [1] arg=3 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN ('A',b,10); a b @@ -1589,30 +1589,30 @@ A NULL B NULL Warnings: Note 1105 DBUG: [0] arg=1 handler=0 (longblob) -Note 1105 DBUG: [1] arg=3 handler=1 (double) +Note 1105 DBUG: [1] arg=3 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (b,a,10); a b A NULL Warnings: Note 1105 DBUG: [0] arg=2 handler=0 (longblob) -Note 1105 DBUG: [1] arg=3 handler=1 (double) +Note 1105 DBUG: [1] arg=3 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no Note 1105 DBUG: [0] arg=2 handler=0 (longblob) -Note 1105 DBUG: [1] arg=3 handler=1 (double) +Note 1105 DBUG: [1] arg=3 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no -Warning 1292 Truncated incorrect DOUBLE value: 'A' +Warning 1292 Truncated incorrect DECIMAL value: 'A' SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (a,b,10); a b A NULL Warnings: Note 1105 DBUG: [0] arg=1 handler=0 (longblob) -Note 1105 DBUG: [1] arg=3 handler=1 (double) +Note 1105 DBUG: [1] arg=3 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no Note 1105 DBUG: [0] arg=1 handler=0 (longblob) -Note 1105 DBUG: [1] arg=3 handler=1 (double) +Note 1105 DBUG: [1] arg=3 handler=1 (decimal) Note 1105 DBUG: types_compatible=no bisect=no -Warning 1292 Truncated incorrect DOUBLE value: 'A' +Warning 1292 Truncated incorrect DECIMAL value: 'A' DROP TABLE t1; # # MDEV-11497 Wrong result for (int_expr IN (mixture of signed and unsigned expressions)) @@ -1930,20 +1930,20 @@ SELECT '0x' IN (0); '0x' IN (0) 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '0x' +Warning 1292 Truncated incorrect DECIMAL value: '0x' SELECT '0x' IN (0,1); '0x' IN (0,1) 1 Warnings: -Note 1105 DBUG: [0] arg=1 handler=0 (double) -Note 1105 DBUG: [1] arg=2 handler=0 (double) +Note 1105 DBUG: [0] arg=1 handler=0 (decimal) +Note 1105 DBUG: [1] arg=2 handler=0 (decimal) Note 1105 DBUG: types_compatible=yes bisect=yes -Warning 1292 Truncated incorrect DOUBLE value: '0x' +Warning 1292 Truncated incorrect DECIMAL value: '0x' SELECT ('0x',1) IN ((0,1)); ('0x',1) IN ((0,1)) 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '0x' +Warning 1292 Truncated incorrect DECIMAL value: '0x' SELECT ('0x',1) IN ((0,1),(1,1)); ('0x',1) IN ((0,1),(1,1)) 1 @@ -1954,13 +1954,13 @@ Note 1105 DBUG: ROW(3 args) level=0 Note 1105 DBUG: [0,0] handler=varchar Note 1105 DBUG: [0,1] handler=int Note 1105 DBUG: [0,2] handler=int -Note 1105 DBUG: => handler=double +Note 1105 DBUG: => handler=decimal Note 1105 DBUG: [1,0] handler=int Note 1105 DBUG: [1,1] handler=int Note 1105 DBUG: [1,2] handler=int Note 1105 DBUG: => handler=bigint Note 1105 DBUG: types_compatible=yes bisect=yes -Warning 1292 Truncated incorrect DOUBLE value: '0x' +Warning 1292 Truncated incorrect DECIMAL value: '0x' SET SESSION debug_dbug="-d,Predicant_to_list_comparator"; SET SESSION debug_dbug="-d,Item_func_in"; SET SESSION debug_dbug="-d,cmp_item"; diff --git a/mysql-test/main/func_if.result b/mysql-test/main/func_if.result index 09eb85d9fb6..6a8e578767e 100644 --- a/mysql-test/main/func_if.result +++ b/mysql-test/main/func_if.result @@ -63,7 +63,7 @@ select nullif(1,'test'); nullif(1,'test') 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'test' +Warning 1292 Truncated incorrect DECIMAL value: 'test' select NULLIF(NULL,NULL), NULLIF(NULL,1), NULLIF(NULL,1.0), NULLIF(NULL,"test"); NULLIF(NULL,NULL) NULLIF(NULL,1) NULLIF(NULL,1.0) NULLIF(NULL,"test") NULL NULL NULL NULL diff --git a/mysql-test/main/func_in.result b/mysql-test/main/func_in.result index 9a3c1dba045..9fb32ecbfaf 100644 --- a/mysql-test/main/func_in.result +++ b/mysql-test/main/func_in.result @@ -470,7 +470,7 @@ HEX(a) 7FFFFFFFFFFFFFFE 7FFFFFFFFFFFFFFF Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'abc' +Warning 1292 Truncated incorrect DECIMAL value: 'abc' CREATE TABLE t3 (a BIGINT UNSIGNED); INSERT INTO t3 VALUES (9223372036854775551); SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42); @@ -498,21 +498,21 @@ f1 a 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'b' select f1, case f1 when 'a' then '+' when 1 then '-' end from t1; f1 case f1 when 'a' then '+' when 1 then '-' end a + b NULL 1 - Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'b' create index t1f1_idx on t1(f1); select f1 from t1 where f1 in ('a',1); f1 1 a Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'b' explain select f1 from t1 where f1 in ('a',1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index @@ -527,8 +527,8 @@ select f1 from t1 where f1 in (2,1); f1 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' explain select f1 from t1 where f1 in (2,1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index @@ -539,9 +539,9 @@ f2 0 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' explain select f2 from t2 where f2 in ('a',2); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index @@ -549,21 +549,21 @@ select f2 from t2 where f2 in ('a','b'); f2 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' explain select f2 from t2 where f2 in ('a','b'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' select f2 from t2 where f2 in (1,'b'); f2 0 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'b' explain select f2 from t2 where f2 in (1,'b'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index @@ -925,22 +925,22 @@ SELECT '0x' IN (0); '0x' IN (0) 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '0x' +Warning 1292 Truncated incorrect DECIMAL value: '0x' SELECT '0x' IN (0,1); '0x' IN (0,1) 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '0x' +Warning 1292 Truncated incorrect DECIMAL value: '0x' SELECT ('0x',1) IN ((0,1)); ('0x',1) IN ((0,1)) 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '0x' +Warning 1292 Truncated incorrect DECIMAL value: '0x' SELECT ('0x',1) IN ((0,1),(1,1)); ('0x',1) IN ((0,1),(1,1)) 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '0x' +Warning 1292 Truncated incorrect DECIMAL value: '0x' # # End of 10.4 tests # diff --git a/mysql-test/main/func_math.result b/mysql-test/main/func_math.result index c3037ab769a..2363bab2cd0 100644 --- a/mysql-test/main/func_math.result +++ b/mysql-test/main/func_math.result @@ -425,9 +425,7 @@ SELECT b DIV 900 y FROM t1 GROUP BY y; y 0 Warnings: -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'str1' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'str2' SELECT c DIV 900 y FROM t1 GROUP BY y; y diff --git a/mysql-test/main/func_misc.result b/mysql-test/main/func_misc.result index 483338c405a..90f7b6c44ba 100644 --- a/mysql-test/main/func_misc.result +++ b/mysql-test/main/func_misc.result @@ -303,7 +303,7 @@ SELECT COALESCE(a) = COALESCE(b) FROM t1; COALESCE(a) = COALESCE(b) 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Truncated incorrect DECIMAL value: '' DROP TABLE t1; # # Bug #54461: crash with longblob and union or update with subquery diff --git a/mysql-test/main/func_str.result b/mysql-test/main/func_str.result index cb9393da144..37a4d025bce 100644 --- a/mysql-test/main/func_str.result +++ b/mysql-test/main/func_str.result @@ -372,7 +372,7 @@ select position("0" in "baaa" in (1)),position("0" in "1" in (1,2,3)),position(" position("0" in "baaa" in (1)) position("0" in "1" in (1,2,3)) position("sql" in ("mysql")) 1 0 3 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'baaa' +Warning 1292 Truncated incorrect DECIMAL value: 'baaa' select position(("1" in (1,2,3)) in "01"); position(("1" in (1,2,3)) in "01") 2 @@ -1184,12 +1184,12 @@ SELECT * FROM t1, t2 WHERE num=str; str num notnumber 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'notnumber' +Warning 1292 Truncated incorrect DECIMAL value: 'notnumber' SELECT * FROM t1, t2 WHERE num=substring(str from 1 for 6); str num notnumber 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'notnum' +Warning 1292 Truncated incorrect DECIMAL value: 'notnum' DROP TABLE t1,t2; CREATE TABLE t1( id int(11) NOT NULL auto_increment, diff --git a/mysql-test/main/gis.result b/mysql-test/main/gis.result index 73427052b57..1e7f7ce931d 100644 --- a/mysql-test/main/gis.result +++ b/mysql-test/main/gis.result @@ -1654,8 +1654,8 @@ SELECT 1 FROM g1 WHERE a >= ANY (SELECT 1 FROM g1 WHERE a = geomfromtext('') OR a) ; 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' DROP TABLE g1; # # Bug#16451878 GEOMETRY QUERY CRASHES SERVER diff --git a/mysql-test/main/group_by.result b/mysql-test/main/group_by.result index db75287c61c..06138b3030c 100644 --- a/mysql-test/main/group_by.result +++ b/mysql-test/main/group_by.result @@ -820,11 +820,11 @@ set sql_mode=''; SELECT a FROM t1 HAVING 'a' > 1; a Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' SELECT a FROM t1 HAVING "a" > 1; a Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' SELECT a FROM t1 HAVING `a` > 1; a 2 @@ -2325,17 +2325,17 @@ field1 field2 2004-10-11 18:13:00 1 2009-02-19 02:05:00 5 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'g' -Warning 1292 Truncated incorrect DOUBLE value: 'o' -Warning 1292 Truncated incorrect DOUBLE value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'g' +Warning 1292 Truncated incorrect DECIMAL value: 'o' +Warning 1292 Truncated incorrect DECIMAL value: 'v' SELECT alias2.f3 AS field1 , alias2.f1 AS field2 FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f1 = alias1.f2 AND alias2.f1 != alias1.f4 GROUP BY field1 , field2 ; field1 field2 2004-10-11 18:13:00 1 2009-02-19 02:05:00 5 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'g' -Warning 1292 Truncated incorrect DOUBLE value: 'o' -Warning 1292 Truncated incorrect DOUBLE value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'g' +Warning 1292 Truncated incorrect DECIMAL value: 'o' +Warning 1292 Truncated incorrect DECIMAL value: 'v' SET SESSION SQL_MODE=default; drop table t1; # diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index 732966d5561..fd9b5be4260 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -3307,11 +3307,11 @@ b min(a) d 7 f 7 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'c' -Warning 1292 Truncated incorrect DOUBLE value: 'd' -Warning 1292 Truncated incorrect DOUBLE value: 'd' -Warning 1292 Truncated incorrect DOUBLE value: 'f' -Warning 1292 Truncated incorrect DOUBLE value: 'g' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'd' +Warning 1292 Truncated incorrect DECIMAL value: 'd' +Warning 1292 Truncated incorrect DECIMAL value: 'f' +Warning 1292 Truncated incorrect DECIMAL value: 'g' explain SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/main/having.result b/mysql-test/main/having.result index b4ca607ec84..5f75c847993 100644 --- a/mysql-test/main/having.result +++ b/mysql-test/main/having.result @@ -524,7 +524,7 @@ ORDER BY t1.f2; MAX(t2.f2) NULL Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'd' +Warning 1292 Truncated incorrect DECIMAL value: 'd' DROP TABLE t1,t2; End of 5.0 tests # @@ -858,13 +858,13 @@ bar SELECT DEFAULT(f) AS h FROM t1 HAVING h > 5; h Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'A' +Warning 1292 Truncated incorrect DECIMAL value: 'A' SELECT DEFAULT(f) AS h FROM t1 HAVING h >= 0; h A A Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'A' +Warning 1292 Truncated incorrect DECIMAL value: 'A' SELECT DEFAULT(f) AS h FROM t1 HAVING h >= 'A'; h A @@ -892,7 +892,7 @@ HAVING t.f != 112 AND t.f = 'x' AND t.f != 'a'; f x Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'x' DROP TABLE t1,t2; # # MDEV-20200: AddressSanitizer: use-after-poison in diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result index ad8befb29d5..fea8f83f9a1 100644 --- a/mysql-test/main/having_cond_pushdown.result +++ b/mysql-test/main/having_cond_pushdown.result @@ -4933,11 +4933,11 @@ i SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL AND 'x' = 0); i Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'x' SELECT * FROM t1 GROUP BY i HAVING i='1' IN ( i IS NULL AND 'x' = 0); i Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'x' DROP TABLE t1; # # MDEV-28080: HAVING with NOT EXIST predicate in an equality diff --git a/mysql-test/main/insert.result b/mysql-test/main/insert.result index 8423319f557..5d7e62030e4 100644 --- a/mysql-test/main/insert.result +++ b/mysql-test/main/insert.result @@ -726,7 +726,7 @@ SET sql_mode= 'STRICT_ALL_TABLES'; CREATE TABLE t1 (f1 INT DEFAULT 0, f2 INT); CREATE ALGORITHM = MERGE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f1 = 'x' WITH CHECK OPTION; REPLACE INTO v1 SET f2 = 1; -ERROR 22007: Truncated incorrect DOUBLE value: 'x' +ERROR 22007: Truncated incorrect DECIMAL value: 'x' SELECT * from t1; f1 f2 drop view v1; @@ -739,7 +739,7 @@ drop view v1; SELECT 0,0 INTO OUTFILE 't1.txt'; CREATE ALGORITHM = MERGE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f1 = 'x' WITH CHECK OPTION; LOAD DATA INFILE 't1.txt' INTO TABLE v1; -ERROR 22007: Truncated incorrect DOUBLE value: 'x' +ERROR 22007: Truncated incorrect DECIMAL value: 'x' SELECT * from t1; f1 f2 drop view v1; @@ -748,7 +748,7 @@ SET @@sql_mode= @save_mode; CREATE TABLE t1 (f INT); CREATE VIEW v1 AS SELECT * FROM t1 WHERE f <=> 'foo' WITH CHECK OPTION; REPLACE INTO v1 SET f = NULL; -ERROR 22007: Truncated incorrect DOUBLE value: 'foo' +ERROR 22007: Truncated incorrect DECIMAL value: 'foo' DROP VIEW v1; DROP TABLE t1; # End of 10.0 tests diff --git a/mysql-test/main/insert_select.result b/mysql-test/main/insert_select.result index e85c4982137..ea770535e8f 100644 --- a/mysql-test/main/insert_select.result +++ b/mysql-test/main/insert_select.result @@ -883,4 +883,74 @@ INSERT INTO t1 SELECT a*2 FROM t1 ORDER BY a; Warnings: Warning 1264 Out of range value for column 'a' at row 4 DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 (a) SELECT SUM(1); +INSERT INTO t1 (a, b) SELECT AVG(2), MIN(3); +INSERT INTO t1 (b) SELECT AVG('x') OVER (); +ERROR 22007: Truncated incorrect DOUBLE value: 'x' +INSERT INTO t1 SELECT MIN(7) OVER (), MAX(8) OVER(); +SELECT * FROM t1; +a b +1 NULL +2 3 +7 8 +PREPARE stmt FROM 'INSERT INTO t1 (a) SELECT AVG(?)'; +EXECUTE stmt USING 9; +EXECUTE stmt USING 10; +PREPARE stmt FROM 'INSERT INTO t1 SELECT MIN(?), MAX(?)'; +EXECUTE stmt USING 11, 12; +EXECUTE stmt USING 13, 14; +DEALLOCATE PREPARE stmt; +SELECT * FROM t1; +a b +1 NULL +2 3 +7 8 +9 NULL +10 NULL +11 12 +13 14 +CREATE PROCEDURE p1(param_a INT, param_b INT) +BEGIN +INSERT INTO t1 SELECT MIN(param_a) OVER (), MAX(param_b); +END// +CALL p1(21, 22); +CALL p1(23, 24); +SELECT * FROM t1; +a b +1 NULL +2 3 +7 8 +9 NULL +10 NULL +11 12 +13 14 +21 22 +23 24 +CREATE TABLE t2 ( +a DECIMAL UNIQUE CHECK (CASE 0 * 27302337.000000 WHEN 34 THEN ++ 'x' LIKE 'x' OR a NOT IN (-1 / TRUE ^ 2) ELSE 7105743.000000 END)); +INSERT INTO t2 VALUES (90),( -1),(31152443.000000),(-32768),(NULL),(NULL); +INSERT INTO t2 SELECT AVG('x') OVER ( +PARTITION BY ((NOT AVG(76698761.000000))) IS NOT NULL); +ERROR 22007: Truncated incorrect DOUBLE value: 'x' +INSERT IGNORE INTO t2 () VALUES (0),('x'),(3751286.000000), +('x'),((a = 'x' AND 0 AND 0)); +Warnings: +Warning 1366 Incorrect decimal value: 'x' for column `test`.`t2`.`a` at row 2 +Warning 1062 Duplicate entry '0' for key 'a' +Warning 1366 Incorrect decimal value: 'x' for column `test`.`t2`.`a` at row 4 +Warning 1062 Duplicate entry '0' for key 'a' +Warning 1062 Duplicate entry '0' for key 'a' +INSERT INTO t2 VALUES (127); +INSERT INTO t2 SELECT -2147483648 END FROM t2 AS TEXT JOIN t2 JOIN t2 TABLES; +ERROR 23000: Duplicate entry '-2147483648' for key 'a' +ALTER TABLE t2 ADD ( +b INT UNIQUE CHECK ((a = 'x' AND ((-(+(BINARY 49730460.000000)))) = 'x' +BETWEEN 'x' AND 'x'))); +ERROR 22007: Truncated incorrect DECIMAL value: 'x' +UPDATE t2 SET a = -128 WHERE a IS NULL ORDER BY 78 IN ('x','x'),a; +ERROR 23000: Duplicate entry '-128' for key 'a' +DROP TABLE t1, t2; +DROP PROCEDURE p1; # End of 10.2 test diff --git a/mysql-test/main/insert_select.test b/mysql-test/main/insert_select.test index 91b2cc6f981..1f672acc203 100644 --- a/mysql-test/main/insert_select.test +++ b/mysql-test/main/insert_select.test @@ -459,4 +459,58 @@ INSERT INTO t1 SELECT a*2 FROM t1 ORDER BY a; DROP TABLE t1; +# +# MDEV-26427 MariaDB Server SEGV on INSERT .. SELECT +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 (a) SELECT SUM(1); +INSERT INTO t1 (a, b) SELECT AVG(2), MIN(3); + +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 (b) SELECT AVG('x') OVER (); +INSERT INTO t1 SELECT MIN(7) OVER (), MAX(8) OVER(); +SELECT * FROM t1; + +PREPARE stmt FROM 'INSERT INTO t1 (a) SELECT AVG(?)'; +EXECUTE stmt USING 9; +EXECUTE stmt USING 10; + +PREPARE stmt FROM 'INSERT INTO t1 SELECT MIN(?), MAX(?)'; +EXECUTE stmt USING 11, 12; +EXECUTE stmt USING 13, 14; +DEALLOCATE PREPARE stmt; +SELECT * FROM t1; + +DELIMITER //; +CREATE PROCEDURE p1(param_a INT, param_b INT) +BEGIN +INSERT INTO t1 SELECT MIN(param_a) OVER (), MAX(param_b); +END// +DELIMITER ;// +CALL p1(21, 22); +CALL p1(23, 24); +SELECT * FROM t1; + +CREATE TABLE t2 ( + a DECIMAL UNIQUE CHECK (CASE 0 * 27302337.000000 WHEN 34 THEN + + 'x' LIKE 'x' OR a NOT IN (-1 / TRUE ^ 2) ELSE 7105743.000000 END)); +INSERT INTO t2 VALUES (90),( -1),(31152443.000000),(-32768),(NULL),(NULL); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t2 SELECT AVG('x') OVER ( + PARTITION BY ((NOT AVG(76698761.000000))) IS NOT NULL); +INSERT IGNORE INTO t2 () VALUES (0),('x'),(3751286.000000), + ('x'),((a = 'x' AND 0 AND 0)); +INSERT INTO t2 VALUES (127); +--error ER_DUP_ENTRY +INSERT INTO t2 SELECT -2147483648 END FROM t2 AS TEXT JOIN t2 JOIN t2 TABLES; +--error ER_TRUNCATED_WRONG_VALUE +ALTER TABLE t2 ADD ( + b INT UNIQUE CHECK ((a = 'x' AND ((-(+(BINARY 49730460.000000)))) = 'x' + BETWEEN 'x' AND 'x'))); +--error ER_DUP_ENTRY +UPDATE t2 SET a = -128 WHERE a IS NULL ORDER BY 78 IN ('x','x'),a; + +DROP TABLE t1, t2; +DROP PROCEDURE p1; + --echo # End of 10.2 test diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index e8fe3a10b18..1837576e719 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -3887,8 +3887,8 @@ ORDER BY t2.v; MAX(t1.i) NULL Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' -Warning 1292 Truncated incorrect DOUBLE value: 'y' +Warning 1292 Truncated incorrect DECIMAL value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'y' EXPLAIN SELECT MAX(t1.i) diff --git a/mysql-test/main/json_debug_nonembedded.result b/mysql-test/main/json_debug_nonembedded.result new file mode 100644 index 00000000000..313dc2c1eb4 --- /dev/null +++ b/mysql-test/main/json_debug_nonembedded.result @@ -0,0 +1,28 @@ +# +# Beginning of 10.3 test +# MDEV-28762: recursive call of some json functions without stack control +# +SET @saved_dbug = @@debug_dbug; +SET debug_dbug='+d,json_check_min_stack_requirement'; +SET @json1= '{"key1":{"key1":"val1"}}'; +SET @json2= '{"key1":{"key1":"val1"}}'; +SELECT JSON_CONTAINS(@json1, @json2); +ERROR HY000: Thread stack overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack +SET debug_dbug='+d,temp'; +SET @json1= '[1, 2, 3, 4]'; +SET @json2= '[5, 6, 7, 8]'; +SELECT JSON_MERGE(@json1, @json2); +ERROR HY000: Thread stack overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack +SELECT JSON_MERGE_PATCH(@json1, @json2); +ERROR HY000: Thread stack overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack +SELECT JSON_CONTAINS_PATH('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[2]"); +ERROR HY000: Thread stack overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack +SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]'; +SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]'); +ERROR HY000: Thread stack overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack +SELECT JSON_EXTRACT('{"key1":"asd", "key2":[2,3]}', "$.key1", "$.key2"); +ERROR HY000: Thread stack overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack +SET @@debug_dbug= @saved_dbug; +# +# End of 10.3 test +# diff --git a/mysql-test/main/json_debug_nonembedded.test b/mysql-test/main/json_debug_nonembedded.test new file mode 100644 index 00000000000..021abd8c602 --- /dev/null +++ b/mysql-test/main/json_debug_nonembedded.test @@ -0,0 +1,47 @@ +-- source include/not_embedded.inc +--source include/have_debug.inc + +--echo # +--echo # Beginning of 10.3 test +--echo # MDEV-28762: recursive call of some json functions without stack control +--echo # + +SET @saved_dbug = @@debug_dbug; +SET debug_dbug='+d,json_check_min_stack_requirement'; + +SET @json1= '{"key1":{"key1":"val1"}}'; +SET @json2= '{"key1":{"key1":"val1"}}'; + +--replace_regex /overrun: [0-9]* bytes used of a [0-9]* byte stack, and [0-9]* bytes needed/overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed/ +--error ER_STACK_OVERRUN_NEED_MORE +SELECT JSON_CONTAINS(@json1, @json2); + +SET debug_dbug='+d,temp'; +SET @json1= '[1, 2, 3, 4]'; +SET @json2= '[5, 6, 7, 8]'; +--replace_regex /overrun: [0-9]* bytes used of a [0-9]* byte stack, and [0-9]* bytes needed/overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed/ +--error ER_STACK_OVERRUN_NEED_MORE +SELECT JSON_MERGE(@json1, @json2); + +--replace_regex /overrun: [0-9]* bytes used of a [0-9]* byte stack, and [0-9]* bytes needed/overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed/ +--error ER_STACK_OVERRUN_NEED_MORE +SELECT JSON_MERGE_PATCH(@json1, @json2); + +--replace_regex /overrun: [0-9]* bytes used of a [0-9]* byte stack, and [0-9]* bytes needed/overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed/ +--error ER_STACK_OVERRUN_NEED_MORE +SELECT JSON_CONTAINS_PATH('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[2]"); + +SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';--replace_regex /overrun: [0-9]* bytes used of a [0-9]* byte stack, and [0-9]* bytes needed/overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed/ +--replace_regex /overrun: [0-9]* bytes used of a [0-9]* byte stack, and [0-9]* bytes needed/overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed/ +--error ER_STACK_OVERRUN_NEED_MORE +SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]'); + +--replace_regex /overrun: [0-9]* bytes used of a [0-9]* byte stack, and [0-9]* bytes needed/overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed/ +--error ER_STACK_OVERRUN_NEED_MORE +SELECT JSON_EXTRACT('{"key1":"asd", "key2":[2,3]}', "$.key1", "$.key2"); + +SET @@debug_dbug= @saved_dbug; + +--echo # +--echo # End of 10.3 test +--echo # diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index 68770229926..7f0e1d1b516 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -970,10 +970,10 @@ c c b c NULL NULL c NULL NULL Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'c' -Warning 1292 Truncated incorrect DOUBLE value: 'c' -Warning 1292 Truncated incorrect DOUBLE value: 'c' -Warning 1292 Truncated incorrect DOUBLE value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'c' SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2,t3,t4; # diff --git a/mysql-test/main/null.result b/mysql-test/main/null.result index fc29f68baf0..c32574d90fd 100644 --- a/mysql-test/main/null.result +++ b/mysql-test/main/null.result @@ -442,7 +442,7 @@ NULLIF(1,1e0), NULLIF(1,'2001-01-01'), NULLIF(1,TIME'00:00:00'); Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '2001-01-01' +Warning 1292 Truncated incorrect DECIMAL value: '2001-01-01' SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/main/partition_innodb.result b/mysql-test/main/partition_innodb.result index 6be6721f0d1..2bb64205363 100644 --- a/mysql-test/main/partition_innodb.result +++ b/mysql-test/main/partition_innodb.result @@ -951,7 +951,7 @@ Warnings: Warning 1366 Incorrect integer value: 'three' for column `test`.`t2`.`b` at row 2 UPDATE v SET a = NULL; Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'foo' +Warning 1292 Truncated incorrect DECIMAL value: 'foo' DROP view v; DROP TABLE t1, t2; SET @save_isp=@@innodb_stats_persistent; diff --git a/mysql-test/main/profiling.result b/mysql-test/main/profiling.result index 6590d89bc89..d9324cd2f11 100644 --- a/mysql-test/main/profiling.result +++ b/mysql-test/main/profiling.result @@ -297,12 +297,12 @@ id 2 3 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'hello' -Warning 1292 Truncated incorrect DOUBLE value: 'hello' -Warning 1292 Truncated incorrect DOUBLE value: 'hello' -Warning 1292 Truncated incorrect DOUBLE value: 'hello' -Warning 1292 Truncated incorrect DOUBLE value: 'hello' -Warning 1292 Truncated incorrect DOUBLE value: 'hello' +Warning 1292 Truncated incorrect DECIMAL value: 'hello' +Warning 1292 Truncated incorrect DECIMAL value: 'hello' +Warning 1292 Truncated incorrect DECIMAL value: 'hello' +Warning 1292 Truncated incorrect DECIMAL value: 'hello' +Warning 1292 Truncated incorrect DECIMAL value: 'hello' +Warning 1292 Truncated incorrect DECIMAL value: 'hello' select @@profiling; @@profiling 1 diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index 134069d4717..f1ef885b4c2 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -4402,15 +4402,15 @@ set sql_mode='STRICT_ALL_TABLES'; CREATE TABLE t1 (id int, count int); insert into t1 values (1,1),(0,2); update t1 set count = count + 1 where id = '1bad'; -ERROR 22007: Truncated incorrect DOUBLE value: '1bad' +ERROR 22007: Truncated incorrect DECIMAL value: '1bad' prepare stmt from "update t1 set count = count + 1 where id = '1bad'"; execute stmt; -ERROR 22007: Truncated incorrect DOUBLE value: '1bad' +ERROR 22007: Truncated incorrect DECIMAL value: '1bad' deallocate prepare stmt; prepare stmt from 'update t1 set count = count + 1 where id = ?'; set @a = '1bad'; execute stmt using @a; -ERROR 22007: Truncated incorrect DOUBLE value: '1bad' +ERROR 22007: Truncated incorrect DECIMAL value: '1bad' deallocate prepare stmt; drop table t1; CREATE TABLE t1 (id decimal(10,5), count int); diff --git a/mysql-test/main/ps_1general.result b/mysql-test/main/ps_1general.result index 2ef5571cdca..6fe2ea7b448 100644 --- a/mysql-test/main/ps_1general.result +++ b/mysql-test/main/ps_1general.result @@ -282,8 +282,8 @@ SET @arg00=1; execute stmt4 using @arg00; Field Type Null Key Default Extra Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' prepare stmt4 from ' show columns from t2 from test like ''a%'' '; execute stmt4; Field Type Null Key Default Extra diff --git a/mysql-test/main/ps_innodb.result b/mysql-test/main/ps_innodb.result index 4a5819e4072..2b9ec621f0f 100644 --- a/mysql-test/main/ps_innodb.result +++ b/mysql-test/main/ps_innodb.result @@ -7,15 +7,15 @@ set sql_mode='STRICT_TRANS_TABLES'; CREATE TABLE t1 (id int, count int) engine=innodb; insert into t1 values (1,1),(0,2); update t1 set count = count + 1 where id = '1bad'; -ERROR 22007: Truncated incorrect DOUBLE value: '1bad' +ERROR 22007: Truncated incorrect DECIMAL value: '1bad' prepare stmt from "update t1 set count = count + 1 where id = '1bad'"; execute stmt; -ERROR 22007: Truncated incorrect DOUBLE value: '1bad' +ERROR 22007: Truncated incorrect DECIMAL value: '1bad' deallocate prepare stmt; prepare stmt from 'update t1 set count = count + 1 where id = ?'; set @a = '1bad'; execute stmt using @a; -ERROR 22007: Truncated incorrect DOUBLE value: '1bad' +ERROR 22007: Truncated incorrect DECIMAL value: '1bad' deallocate prepare stmt; drop table t1; CREATE TABLE t1 (id decimal(10,5), count int) engine=innodb; diff --git a/mysql-test/main/row.result b/mysql-test/main/row.result index 40d3e2640f0..343cd7badbb 100644 --- a/mysql-test/main/row.result +++ b/mysql-test/main/row.result @@ -18,14 +18,14 @@ Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'a' Warning 1292 Truncated incorrect DOUBLE value: 'a' Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' Warning 1292 Truncated incorrect DOUBLE value: 'a' select row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3)); row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3)) 1 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' Warning 1292 Truncated incorrect DOUBLE value: 'a' select row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3)); row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3)) @@ -49,16 +49,16 @@ select row('b',1.5,3) IN (row('b',NULL,3), row('a',1.5,3), row(1,3,3)); row('b',1.5,3) IN (row('b',NULL,3), row('a',1.5,3), row(1,3,3)) 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' select row('b',1.5,3) IN (row('b',NULL,4), row('a',1.5,3), row(1,3,3)); row('b',1.5,3) IN (row('b',NULL,4), row('a',1.5,3), row(1,3,3)) 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' select (1,2,(3,4)) IN ((3,2,(3,4)), (1,2,(3,4))); (1,2,(3,4)) IN ((3,2,(3,4)), (1,2,(3,4))) 1 diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result index 69b8a2ac544..8af44477e80 100644 --- a/mysql-test/main/select.result +++ b/mysql-test/main/select.result @@ -5262,11 +5262,11 @@ SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4 HAVING G1 ORDER BY `varchar_key` LIMIT 6 ; G1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'z' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'q' -Warning 1292 Truncated incorrect DOUBLE value: 'm' -Warning 1292 Truncated incorrect DOUBLE value: 'j' +Warning 1292 Truncated incorrect DECIMAL value: 'z' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'q' +Warning 1292 Truncated incorrect DECIMAL value: 'm' +Warning 1292 Truncated incorrect DECIMAL value: 'j' DROP TABLE CC; # End of test#45227 # diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result index 2436ca66686..5a3476cf7d0 100644 --- a/mysql-test/main/select_jcl6.result +++ b/mysql-test/main/select_jcl6.result @@ -5273,11 +5273,11 @@ SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4 HAVING G1 ORDER BY `varchar_key` LIMIT 6 ; G1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'j' -Warning 1292 Truncated incorrect DOUBLE value: 'z' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'q' -Warning 1292 Truncated incorrect DOUBLE value: 'm' +Warning 1292 Truncated incorrect DECIMAL value: 'j' +Warning 1292 Truncated incorrect DECIMAL value: 'z' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'q' +Warning 1292 Truncated incorrect DECIMAL value: 'm' DROP TABLE CC; # End of test#45227 # diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result index 69b8a2ac544..8af44477e80 100644 --- a/mysql-test/main/select_pkeycache.result +++ b/mysql-test/main/select_pkeycache.result @@ -5262,11 +5262,11 @@ SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4 HAVING G1 ORDER BY `varchar_key` LIMIT 6 ; G1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'z' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'q' -Warning 1292 Truncated incorrect DOUBLE value: 'm' -Warning 1292 Truncated incorrect DOUBLE value: 'j' +Warning 1292 Truncated incorrect DECIMAL value: 'z' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'q' +Warning 1292 Truncated incorrect DECIMAL value: 'm' +Warning 1292 Truncated incorrect DECIMAL value: 'j' DROP TABLE CC; # End of test#45227 # diff --git a/mysql-test/main/set_statement.result b/mysql-test/main/set_statement.result index 53574fb4e4f..0197495cc0d 100644 --- a/mysql-test/main/set_statement.result +++ b/mysql-test/main/set_statement.result @@ -1242,6 +1242,18 @@ t1 CREATE TABLE `t1` ( `c1` varchar(3) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; +# +# MDEV-29074 GET_BIT variables crash in SET STATEMENT +# +set statement log_queries_not_using_indexes=1 for select @@log_queries_not_using_indexes; +@@log_queries_not_using_indexes +1 +select @@log_queries_not_using_indexes; +@@log_queries_not_using_indexes +0 +# +# End of 10.3 tests +# create table t (a int); SET sql_mode=ORACLE; SET STATEMENT myisam_sort_buffer_size=800000 FOR OPTIMIZE TABLE t; diff --git a/mysql-test/main/set_statement.test b/mysql-test/main/set_statement.test index 670e9862abc..0bf9f423dab 100644 --- a/mysql-test/main/set_statement.test +++ b/mysql-test/main/set_statement.test @@ -1161,6 +1161,16 @@ SHOW CREATE TABLE t1; DROP TABLE t1; +--echo # +--echo # MDEV-29074 GET_BIT variables crash in SET STATEMENT +--echo # +set statement log_queries_not_using_indexes=1 for select @@log_queries_not_using_indexes; +select @@log_queries_not_using_indexes; + +--echo # +--echo # End of 10.3 tests +--echo # + create table t (a int); SET sql_mode=ORACLE; SET STATEMENT myisam_sort_buffer_size=800000 FOR OPTIMIZE TABLE t; diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result index 4dc033d4171..48794c7032c 100644 --- a/mysql-test/main/sp.result +++ b/mysql-test/main/sp.result @@ -8702,11 +8702,11 @@ RETURN a = timestamp'2038-01-19 03:14:07.999999' END $$ SELECT f1(e) FROM t1; -ERROR 22007: Truncated incorrect DOUBLE value: '2001-01-01 10:20:30.000000' +ERROR 22007: Truncated incorrect DECIMAL value: '2001-01-01 10:20:30.000000' SELECT f2(e) FROM t1; -ERROR 22007: Truncated incorrect DOUBLE value: '2001-01-01 10:20:30.000000' +ERROR 22007: Truncated incorrect DECIMAL value: '2001-01-01 10:20:30.000000' SELECT f3(e) FROM t1; -ERROR 22007: Truncated incorrect DOUBLE value: '2001-01-01 10:20:30.000000' +ERROR 22007: Truncated incorrect DECIMAL value: '2001-01-01 10:20:30.000000' DROP FUNCTION f1; DROP FUNCTION f2; DROP FUNCTION f3; diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 035fdf864b3..02c362616f9 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -141,7 +141,7 @@ SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a'); (SELECT 1.5,'c','a') = ROW(1.5,2,'a') 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'c' SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a); ERROR 21000: Operand should contain 1 column(s) SELECT 1 as a,(SELECT a+a) b,(SELECT b); @@ -4684,7 +4684,7 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); 1 1 @@ -5969,7 +5969,7 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); 1 1 @@ -6350,10 +6350,10 @@ ORDER BY b ); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t2 WHERE b = (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2) @@ -6361,10 +6361,10 @@ GROUP BY b ); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' DROP TABLE t1, t2; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index aa486fc0903..1d4a588977d 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -1677,7 +1677,7 @@ WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f f1 f2 8 8 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'u' +Warning 1292 Truncated incorrect DECIMAL value: 'u' EXPLAIN SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); id select_type table type possible_keys key key_len ref rows Extra @@ -1711,7 +1711,7 @@ WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f f1 f2 8 8 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'u' +Warning 1292 Truncated incorrect DECIMAL value: 'u' EXPLAIN SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); id select_type table type possible_keys key key_len ref rows Extra @@ -1903,8 +1903,8 @@ WHERE SUBQUERY2_t2.col_varchar_nokey IN (SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey)); col_int_key Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'r' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'r' set @@optimizer_switch='subquery_cache=off,materialization=off,in_to_exists=on,semijoin=off'; EXPLAIN SELECT col_int_key @@ -1928,8 +1928,8 @@ WHERE SUBQUERY2_t2.col_varchar_nokey IN (SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey)); col_int_key Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'r' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'r' drop table t1, t2; set @@optimizer_switch = @old_optimizer_switch; # @@ -2981,6 +2981,63 @@ ANALYZE } DROP TABLE t1; # End of 10.2 tests +# +# MDEV-29139: Redundannt subquery in GROUP BY clause of ANY/ALL subquery +# +create table t1 (a int); +insert into t1 values (3), (1), (2); +create table t2 (b int not null); +insert into t2 values (4), (2); +create table t3 (c int); +insert into t3 values (7), (1); +explain extended select a from t1 +where a >= any (select b from t2 group by (select c from t3 where c = 1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t1`.`a`))) +select a from t1 +where a >= any (select b from t2 group by (select c from t3 where c = 1)); +a +3 +2 +prepare stmt from "select a from t1 +where a >= any (select b from t2 group by (select c from t3 where c = 1))"; +execute stmt; +a +3 +2 +execute stmt; +a +3 +2 +deallocate prepare stmt; +explain extended select a from t1 +where a <= all (select b from t2 group by (select c from t3 where c = 1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) < <cache>(`test`.`t1`.`a`))) +select a from t1 +where a <= all (select b from t2 group by (select c from t3 where c = 1)); +a +1 +2 +explain extended select a from t1 +where a >= any (select b from t2 group by 1 + (select c from t3 where c = 1)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t1`.`a`))) +select a from t1 +where a >= any (select b from t2 group by 1 + (select c from t3 where c = 1)); +a +3 +2 +drop table t1,t2,t3; # End of 10.3 tests # # MDEV-19134: EXISTS() slower if ORDER BY is defined diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index 8834134e880..3629f58e8b6 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2438,6 +2438,45 @@ DROP TABLE t1; --echo # End of 10.2 tests + +--echo # +--echo # MDEV-29139: Redundannt subquery in GROUP BY clause of ANY/ALL subquery +--echo # + +create table t1 (a int); +insert into t1 values (3), (1), (2); +create table t2 (b int not null); +insert into t2 values (4), (2); +create table t3 (c int); +insert into t3 values (7), (1); + +let $q1= +select a from t1 + where a >= any (select b from t2 group by (select c from t3 where c = 1)); + +eval explain extended $q1; +eval $q1; + +eval prepare stmt from "$q1"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q2= +select a from t1 + where a <= all (select b from t2 group by (select c from t3 where c = 1)); + +eval explain extended $q2; +eval $q2; + +let $q3= +select a from t1 + where a >= any (select b from t2 group by 1 + (select c from t3 where c = 1)); +eval explain extended $q3; +eval $q3; + +drop table t1,t2,t3; + --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/subselect_cache.result b/mysql-test/main/subselect_cache.result index abd978de9a5..07108b12a3f 100644 --- a/mysql-test/main/subselect_cache.result +++ b/mysql-test/main/subselect_cache.result @@ -2844,70 +2844,70 @@ WHERE table1 .`col_varchar_key` ) field10 1 NULL w 1 NULL y Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' SET @@optimizer_switch='subquery_cache=on'; /* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , ( SELECT SUBQUERY2_t1 .`col_int_key` @@ -2935,32 +2935,32 @@ WHERE table1 .`col_varchar_key` ) field10 1 NULL w 1 NULL y Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'c' -Warning 1292 Truncated incorrect DOUBLE value: 'd' -Warning 1292 Truncated incorrect DOUBLE value: 'e' -Warning 1292 Truncated incorrect DOUBLE value: 'f' -Warning 1292 Truncated incorrect DOUBLE value: 'g' -Warning 1292 Truncated incorrect DOUBLE value: 'h' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'j' -Warning 1292 Truncated incorrect DOUBLE value: 'k' -Warning 1292 Truncated incorrect DOUBLE value: 'l' -Warning 1292 Truncated incorrect DOUBLE value: 'm' -Warning 1292 Truncated incorrect DOUBLE value: 'n' -Warning 1292 Truncated incorrect DOUBLE value: 'o' -Warning 1292 Truncated incorrect DOUBLE value: 'p' -Warning 1292 Truncated incorrect DOUBLE value: 'q' -Warning 1292 Truncated incorrect DOUBLE value: 'r' -Warning 1292 Truncated incorrect DOUBLE value: 's' -Warning 1292 Truncated incorrect DOUBLE value: 't' -Warning 1292 Truncated incorrect DOUBLE value: 'u' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'w' -Warning 1292 Truncated incorrect DOUBLE value: 'x' -Warning 1292 Truncated incorrect DOUBLE value: 'y' -Warning 1292 Truncated incorrect DOUBLE value: 'z' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'd' +Warning 1292 Truncated incorrect DECIMAL value: 'e' +Warning 1292 Truncated incorrect DECIMAL value: 'f' +Warning 1292 Truncated incorrect DECIMAL value: 'g' +Warning 1292 Truncated incorrect DECIMAL value: 'h' +Warning 1292 Truncated incorrect DECIMAL value: 'i' +Warning 1292 Truncated incorrect DECIMAL value: 'j' +Warning 1292 Truncated incorrect DECIMAL value: 'k' +Warning 1292 Truncated incorrect DECIMAL value: 'l' +Warning 1292 Truncated incorrect DECIMAL value: 'm' +Warning 1292 Truncated incorrect DECIMAL value: 'n' +Warning 1292 Truncated incorrect DECIMAL value: 'o' +Warning 1292 Truncated incorrect DECIMAL value: 'p' +Warning 1292 Truncated incorrect DECIMAL value: 'q' +Warning 1292 Truncated incorrect DECIMAL value: 'r' +Warning 1292 Truncated incorrect DECIMAL value: 's' +Warning 1292 Truncated incorrect DECIMAL value: 't' +Warning 1292 Truncated incorrect DECIMAL value: 'u' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'w' +Warning 1292 Truncated incorrect DECIMAL value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'y' +Warning 1292 Truncated incorrect DECIMAL value: 'z' drop table t1,t2,t3,t4; set @@optimizer_switch= default; #launchpad BUG#609045 @@ -3385,9 +3385,9 @@ WHERE table1 .`col_varchar_key` ) field10 1 NULL d 1 NULL f Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'f' -Warning 1292 Truncated incorrect DOUBLE value: 'f' -Warning 1292 Truncated incorrect DOUBLE value: 'f' +Warning 1292 Truncated incorrect DECIMAL value: 'f' +Warning 1292 Truncated incorrect DECIMAL value: 'f' +Warning 1292 Truncated incorrect DECIMAL value: 'f' SET @@optimizer_switch = 'subquery_cache=on'; /* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , ( SELECT SUBQUERY2_t1 .`col_int_key` @@ -3402,9 +3402,9 @@ WHERE table1 .`col_varchar_key` ) field10 1 NULL d 1 NULL f Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'f' -Warning 1292 Truncated incorrect DOUBLE value: 'f' -Warning 1292 Truncated incorrect DOUBLE value: 'f' +Warning 1292 Truncated incorrect DECIMAL value: 'f' +Warning 1292 Truncated incorrect DECIMAL value: 'f' +Warning 1292 Truncated incorrect DECIMAL value: 'f' drop table t1,t2,t3,t4; set @@optimizer_switch= default; #launchpad BUG#611625 diff --git a/mysql-test/main/subselect_innodb.result b/mysql-test/main/subselect_innodb.result index 5d714d9c91e..a32c9ebe09c 100644 --- a/mysql-test/main/subselect_innodb.result +++ b/mysql-test/main/subselect_innodb.result @@ -722,7 +722,7 @@ a 1 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Truncated incorrect DECIMAL value: '' drop table t1; # # End of 10.4 tests diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 34b58daa50e..0d464604730 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -578,9 +578,9 @@ select * from t1 group by (a1 in (select col from columns)); a1 a2 1 - 00 2 - 00 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1 - 00' -Warning 1292 Truncated incorrect DOUBLE value: '1 - 01' -Warning 1292 Truncated incorrect DOUBLE value: '1 - 02' +Warning 1292 Truncated incorrect DECIMAL value: '1 - 00' +Warning 1292 Truncated incorrect DECIMAL value: '1 - 01' +Warning 1292 Truncated incorrect DECIMAL value: '1 - 02' /* ORDER BY clause */ explain extended select * from t1 order by (select col from columns limit 1); diff --git a/mysql-test/main/subselect_mat_cost_bugs.result b/mysql-test/main/subselect_mat_cost_bugs.result index 0ccac8d3dd5..1b5f94c427b 100644 --- a/mysql-test/main/subselect_mat_cost_bugs.result +++ b/mysql-test/main/subselect_mat_cost_bugs.result @@ -209,8 +209,8 @@ WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10 ORDER BY field1 ; field1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'f' -Warning 1292 Truncated incorrect DOUBLE value: 'd' +Warning 1292 Truncated incorrect DECIMAL value: 'f' +Warning 1292 Truncated incorrect DECIMAL value: 'd' set optimizer_switch=@tmp_optimizer_switch; drop table t1,t2; # @@ -273,8 +273,8 @@ WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10 ); f12 f13 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'f' -Warning 1292 Truncated incorrect DOUBLE value: 'd' +Warning 1292 Truncated incorrect DECIMAL value: 'f' +Warning 1292 Truncated incorrect DECIMAL value: 'd' EXPLAIN SELECT * FROM t2 WHERE ( f12 ) IN ( @@ -292,8 +292,8 @@ FROM t1 AS alias1, t1 AS alias2 WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); f12 f13 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'f' -Warning 1292 Truncated incorrect DOUBLE value: 'd' +Warning 1292 Truncated incorrect DECIMAL value: 'f' +Warning 1292 Truncated incorrect DECIMAL value: 'd' set @@optimizer_switch=@save_optimizer_switch; drop table t1, t2; # diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index 4af48680232..a75a2527758 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -145,7 +145,7 @@ SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a'); (SELECT 1.5,'c','a') = ROW(1.5,2,'a') 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'c' SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a); ERROR 21000: Operand should contain 1 column(s) SELECT 1 as a,(SELECT a+a) b,(SELECT b); @@ -4686,7 +4686,7 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); 1 1 @@ -5969,7 +5969,7 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); 1 1 @@ -6350,10 +6350,10 @@ ORDER BY b ); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t2 WHERE b = (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2) @@ -6361,10 +6361,10 @@ GROUP BY b ); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' DROP TABLE t1, t2; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index 45687896592..5101e9ee57d 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -148,7 +148,7 @@ SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a'); (SELECT 1.5,'c','a') = ROW(1.5,2,'a') 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'c' SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a); ERROR 21000: Operand should contain 1 column(s) SELECT 1 as a,(SELECT a+a) b,(SELECT b); @@ -4684,7 +4684,7 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); 1 1 @@ -5966,7 +5966,7 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); 1 1 @@ -6345,10 +6345,10 @@ ORDER BY b ); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t2 WHERE b = (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2) @@ -6356,10 +6356,10 @@ GROUP BY b ); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' DROP TABLE t1, t2; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 0c0fe634b0c..feef7dc9cdc 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -144,7 +144,7 @@ SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a'); (SELECT 1.5,'c','a') = ROW(1.5,2,'a') 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'c' SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a); ERROR 21000: Operand should contain 1 column(s) SELECT 1 as a,(SELECT a+a) b,(SELECT b); @@ -4680,7 +4680,7 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); 1 1 @@ -5962,7 +5962,7 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); 1 1 @@ -6341,10 +6341,10 @@ ORDER BY b ); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t2 WHERE b = (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2) @@ -6352,10 +6352,10 @@ GROUP BY b ); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' DROP TABLE t1, t2; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index bf6c863a006..7eb41b4a813 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -147,7 +147,7 @@ SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a'); (SELECT 1.5,'c','a') = ROW(1.5,2,'a') 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'c' SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a); ERROR 21000: Operand should contain 1 column(s) SELECT 1 as a,(SELECT a+a) b,(SELECT b); @@ -4690,7 +4690,7 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); 1 1 @@ -5975,7 +5975,7 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); 1 1 @@ -6356,10 +6356,10 @@ ORDER BY b ); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t2 WHERE b = (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2) @@ -6367,10 +6367,10 @@ GROUP BY b ); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' DROP TABLE t1, t2; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index 027fa4ee9f3..3424a9749f4 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -144,7 +144,7 @@ SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a'); (SELECT 1.5,'c','a') = ROW(1.5,2,'a') 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'c' SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a); ERROR 21000: Operand should contain 1 column(s) SELECT 1 as a,(SELECT a+a) b,(SELECT b); @@ -4680,7 +4680,7 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); 1 1 @@ -5962,7 +5962,7 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'a' SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); 1 1 @@ -6341,10 +6341,10 @@ ORDER BY b ); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t2 WHERE b = (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2) @@ -6352,10 +6352,10 @@ GROUP BY b ); 1 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' -Warning 1292 Truncated incorrect DOUBLE value: 'a' -Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' DROP TABLE t1, t2; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index bb6858d1968..a3eefb8c354 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -1705,15 +1705,15 @@ x m c Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' -Warning 1292 Truncated incorrect DOUBLE value: 'm' -Warning 1292 Truncated incorrect DOUBLE value: 'c' -Warning 1292 Truncated incorrect DOUBLE value: 'x' -Warning 1292 Truncated incorrect DOUBLE value: 'm' -Warning 1292 Truncated incorrect DOUBLE value: 'c' -Warning 1292 Truncated incorrect DOUBLE value: 'x' -Warning 1292 Truncated incorrect DOUBLE value: 'm' -Warning 1292 Truncated incorrect DOUBLE value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'm' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'm' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'm' +Warning 1292 Truncated incorrect DECIMAL value: 'c' drop table t1,t2,t3,t4; # # BUG#751484: Valgrind warning / sporadic crash in evaluate_join_record sql_select.cc:14099 with semijoin @@ -2100,8 +2100,8 @@ f1 f2 f3 f3 4 0 0 0 0 NULL NULL NULL Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'foo' -Warning 1292 Truncated incorrect DOUBLE value: 'foo' +Warning 1292 Truncated incorrect DECIMAL value: 'foo' +Warning 1292 Truncated incorrect DECIMAL value: 'foo' DROP TABLE t1, t2, t3, t4; set @tmp803457=@@optimizer_switch; # @@ -2294,13 +2294,13 @@ PREPARE st1 FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.a != 0 AND ('j','r') IN ( EXECUTE st1; a a Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'j' -Warning 1292 Truncated incorrect DOUBLE value: 'r' +Warning 1292 Truncated incorrect DECIMAL value: 'j' +Warning 1292 Truncated incorrect DECIMAL value: 'r' EXECUTE st1; a a Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'j' -Warning 1292 Truncated incorrect DOUBLE value: 'r' +Warning 1292 Truncated incorrect DECIMAL value: 'j' +Warning 1292 Truncated incorrect DECIMAL value: 'r' DROP TABLE t1, t2, t3; # # BUG#849776: Wrong result with semijoin + "Impossible where" @@ -3336,4 +3336,17 @@ ERROR HY000: Illegal parameter data types geometry and int for operation '=' EXECUTE stmt; ERROR HY000: Illegal parameter data types geometry and int for operation '=' DROP TABLE t1, t2; +# +# MDEV-28871: Assert ... failed in JOIN::dbug_verify_sj_inner_tables with low optimizer_search_depth +# +set @tmp_28871=@@optimizer_search_depth; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t1 VALUES (3),(4); +SET optimizer_search_depth= 1; +SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 JOIN t1); +a +DROP TABLE t1, t2; +set optimizer_search_depth= @tmp_28871; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test index 324f2828de2..c869f56c837 100644 --- a/mysql-test/main/subselect_sj.test +++ b/mysql-test/main/subselect_sj.test @@ -3018,5 +3018,22 @@ EXECUTE stmt; DROP TABLE t1, t2; +--echo # +--echo # MDEV-28871: Assert ... failed in JOIN::dbug_verify_sj_inner_tables with low optimizer_search_depth +--echo # +set @tmp_28871=@@optimizer_search_depth; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); + +# Data is optional, fails either way +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t1 VALUES (3),(4); + +SET optimizer_search_depth= 1; + +SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 JOIN t1); +DROP TABLE t1, t2; +set optimizer_search_depth= @tmp_28871; + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index 56565ab1235..2547d9bd487 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -1716,15 +1716,15 @@ x m c Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' -Warning 1292 Truncated incorrect DOUBLE value: 'm' -Warning 1292 Truncated incorrect DOUBLE value: 'c' -Warning 1292 Truncated incorrect DOUBLE value: 'x' -Warning 1292 Truncated incorrect DOUBLE value: 'm' -Warning 1292 Truncated incorrect DOUBLE value: 'c' -Warning 1292 Truncated incorrect DOUBLE value: 'x' -Warning 1292 Truncated incorrect DOUBLE value: 'm' -Warning 1292 Truncated incorrect DOUBLE value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'm' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'm' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'm' +Warning 1292 Truncated incorrect DECIMAL value: 'c' drop table t1,t2,t3,t4; # # BUG#751484: Valgrind warning / sporadic crash in evaluate_join_record sql_select.cc:14099 with semijoin @@ -2111,8 +2111,8 @@ f1 f2 f3 f3 4 0 0 0 0 NULL NULL NULL Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'foo' -Warning 1292 Truncated incorrect DOUBLE value: 'foo' +Warning 1292 Truncated incorrect DECIMAL value: 'foo' +Warning 1292 Truncated incorrect DECIMAL value: 'foo' DROP TABLE t1, t2, t3, t4; set @tmp803457=@@optimizer_switch; # @@ -2305,13 +2305,13 @@ PREPARE st1 FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.a != 0 AND ('j','r') IN ( EXECUTE st1; a a Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'j' -Warning 1292 Truncated incorrect DOUBLE value: 'r' +Warning 1292 Truncated incorrect DECIMAL value: 'j' +Warning 1292 Truncated incorrect DECIMAL value: 'r' EXECUTE st1; a a Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'j' -Warning 1292 Truncated incorrect DOUBLE value: 'r' +Warning 1292 Truncated incorrect DECIMAL value: 'j' +Warning 1292 Truncated incorrect DECIMAL value: 'r' DROP TABLE t1, t2, t3; # # BUG#849776: Wrong result with semijoin + "Impossible where" @@ -3347,6 +3347,19 @@ ERROR HY000: Illegal parameter data types geometry and int for operation '=' EXECUTE stmt; ERROR HY000: Illegal parameter data types geometry and int for operation '=' DROP TABLE t1, t2; +# +# MDEV-28871: Assert ... failed in JOIN::dbug_verify_sj_inner_tables with low optimizer_search_depth +# +set @tmp_28871=@@optimizer_search_depth; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t1 VALUES (3),(4); +SET optimizer_search_depth= 1; +SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 JOIN t1); +a +DROP TABLE t1, t2; +set optimizer_search_depth= @tmp_28871; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index afc75a22962..8f681b59fc6 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -598,9 +598,9 @@ select * from t1 group by (a1 in (select col from columns)); a1 a2 1 - 00 2 - 00 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1 - 00' -Warning 1292 Truncated incorrect DOUBLE value: '1 - 01' -Warning 1292 Truncated incorrect DOUBLE value: '1 - 02' +Warning 1292 Truncated incorrect DECIMAL value: '1 - 00' +Warning 1292 Truncated incorrect DECIMAL value: '1 - 01' +Warning 1292 Truncated incorrect DECIMAL value: '1 - 02' /* ORDER BY clause */ explain extended select * from t1 order by (select col from columns limit 1); diff --git a/mysql-test/main/type_int.result b/mysql-test/main/type_int.result index 08abf2db916..96b19f142e5 100644 --- a/mysql-test/main/type_int.result +++ b/mysql-test/main/type_int.result @@ -286,6 +286,175 @@ a 127 DROP TABLE t1, t2; # +# MDEV-21445 Strange/inconsistent behavior of IN condition when mixing numbers and strings +# +CREATE TABLE t1 ( +id bigint(20) unsigned NOT NULL, +PRIMARY KEY (id) +); +INSERT INTO t1 VALUES(1234),(97716021308405775),(97716021308405770),(97716021308405780); +SELECT * FROM t1 WHERE id IN(97716021308405775); +id +97716021308405775 +SELECT * FROM t1 WHERE id IN('97716021308405775'); +id +97716021308405775 +SELECT * FROM t1 WHERE id IN('1234',97716021308405775); +id +1234 +97716021308405775 +SELECT * FROM t1 WHERE id IN('1234','97716021308405775'); +id +1234 +97716021308405775 +SELECT * FROM t1 WHERE id = 1234 OR id = '97716021308405775'; +id +1234 +97716021308405775 +SELECT * FROM t1 WHERE id IN(1234,'97716021308405775'); +id +1234 +97716021308405775 +SELECT * FROM t1 WHERE id IN('97716021308405775',1234); +id +1234 +97716021308405775 +DROP TABLE t1; +CREATE TABLE t1 (id bigint(20) unsigned NOT NULL PRIMARY KEY); +INSERT INTO t1 VALUES(1234),(97716021308405775),(97716021308405770),(97716021308405780); +SELECT +id, +CASE id +WHEN '97716021308405770' THEN '70' + WHEN '97716021308405775' THEN '75' + WHEN '97716021308405780' THEN '80' + END AS c1 +FROM t1; +id c1 +1234 NULL +97716021308405770 70 +97716021308405775 75 +97716021308405780 80 +SELECT +id, +CASE +WHEN id='97716021308405770' THEN '70' + WHEN id='97716021308405775' THEN '75' + WHEN id='97716021308405780' THEN '80' + END AS c1 +FROM t1; +id c1 +1234 NULL +97716021308405770 70 +97716021308405775 75 +97716021308405780 80 +SELECT id, id BETWEEN '97716021308405775' AND '97716021308405775' AS c1 FROM t1; +id c1 +1234 0 +97716021308405770 0 +97716021308405775 1 +97716021308405780 0 +SELECT id, id BETWEEN '97716021308405775' AND 97716021308405775 AS c1 FROM t1; +id c1 +1234 0 +97716021308405770 0 +97716021308405775 1 +97716021308405780 0 +SELECT id, id BETWEEN 97716021308405775 AND '97716021308405775' AS c1 FROM t1; +id c1 +1234 0 +97716021308405770 0 +97716021308405775 1 +97716021308405780 0 +DROP TABLE t1; +SELECT '97716021308405770'=97716021308405770; +'97716021308405770'=97716021308405770 +1 +SELECT 97716021308405770='97716021308405770'; +97716021308405770='97716021308405770' +1 +SELECT '9e+16'=90000000000000000; +'9e+16'=90000000000000000 +1 +SELECT 90000000000000000='9e+16'; +90000000000000000='9e+16' +1 +SELECT '9e+30'>90000000000000000; +'9e+30'>90000000000000000 +1 +SELECT 90000000000000000<'9e+30'; +90000000000000000<'9e+30' +1 +SELECT '9e+80'>90000000000000000; +'9e+80'>90000000000000000 +1 +SELECT 90000000000000000<'9e+80'; +90000000000000000<'9e+80' +1 +SELECT '9.9999999999999999e+80'>90000000000000000; +'9.9999999999999999e+80'>90000000000000000 +1 +SELECT 90000000000000000<'9.9999999999999999e+80'; +90000000000000000<'9.9999999999999999e+80' +1 +SELECT '1e+81'>90000000000000000; +'1e+81'>90000000000000000 +1 +Warnings: +Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated +Warning 1292 Truncated incorrect DECIMAL value: '1e+81' +SELECT 90000000000000000<'1e+81'; +90000000000000000<'1e+81' +1 +Warnings: +Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated +Warning 1292 Truncated incorrect DECIMAL value: '1e+81' +SELECT '9e+100'>90000000000000000; +'9e+100'>90000000000000000 +1 +Warnings: +Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated +Warning 1292 Truncated incorrect DECIMAL value: '9e+100' +SELECT 90000000000000000<'9e+100'; +90000000000000000<'9e+100' +1 +Warnings: +Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated +Warning 1292 Truncated incorrect DECIMAL value: '9e+100' +SELECT CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED)=18446744073709551615; +CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED)=18446744073709551615 +1 +SELECT 18446744073709551615=CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED); +18446744073709551615=CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED) +1 +SELECT CAST(0x7FFFFFFFFFFFFFFF AS UNSIGNED)=9223372036854775807; +CAST(0x7FFFFFFFFFFFFFFF AS UNSIGNED)=9223372036854775807 +1 +SELECT 9223372036854775807=CAST(0x7FFFFFFFFFFFFFFF AS UNSIGNED); +9223372036854775807=CAST(0x7FFFFFFFFFFFFFFF AS UNSIGNED) +1 +# +# MDEV-25492 BETWEEN clause returns incorrect results on quoted 64-bit ints +# +CREATE TABLE betweentest +( +id int(11) unsigned NOT NULL AUTO_INCREMENT, +range1 bigint(20) DEFAULT NULL, +range2 bigint(20) DEFAULT NULL, +PRIMARY KEY (id) +); +INSERT INTO betweentest VALUES +(1,2739499741191602369,2739499741191602369), +(2,2739499741191602192,2739499741191602192), +(3,2739138623713574912,2739138623730352127); +SELECT * FROM betweentest WHERE '2739499741191602192' BETWEEN range1 AND range2; +id range1 range2 +2 2739499741191602192 2739499741191602192 +SELECT * FROM betweentest WHERE 2739499741191602192 BETWEEN range1 AND range2; +id range1 range2 +2 2739499741191602192 2739499741191602192 +DROP TABLE betweentest; +# # End of 10.3 tests # # diff --git a/mysql-test/main/type_int.test b/mysql-test/main/type_int.test index 0505020374d..3029c8b1375 100644 --- a/mysql-test/main/type_int.test +++ b/mysql-test/main/type_int.test @@ -221,6 +221,108 @@ SELECT * FROM t2; DROP TABLE t1, t2; --echo # +--echo # MDEV-21445 Strange/inconsistent behavior of IN condition when mixing numbers and strings +--echo # + +CREATE TABLE t1 ( + id bigint(20) unsigned NOT NULL, + PRIMARY KEY (id) +); +INSERT INTO t1 VALUES(1234),(97716021308405775),(97716021308405770),(97716021308405780); + +# OK +SELECT * FROM t1 WHERE id IN(97716021308405775); +SELECT * FROM t1 WHERE id IN('97716021308405775'); +SELECT * FROM t1 WHERE id IN('1234',97716021308405775); +SELECT * FROM t1 WHERE id IN('1234','97716021308405775'); +SELECT * FROM t1 WHERE id = 1234 OR id = '97716021308405775'; + +# The following was not OK +SELECT * FROM t1 WHERE id IN(1234,'97716021308405775'); +SELECT * FROM t1 WHERE id IN('97716021308405775',1234); + +DROP TABLE t1; + + +CREATE TABLE t1 (id bigint(20) unsigned NOT NULL PRIMARY KEY); +INSERT INTO t1 VALUES(1234),(97716021308405775),(97716021308405770),(97716021308405780); +SELECT + id, + CASE id + WHEN '97716021308405770' THEN '70' + WHEN '97716021308405775' THEN '75' + WHEN '97716021308405780' THEN '80' + END AS c1 +FROM t1; + +SELECT + id, + CASE + WHEN id='97716021308405770' THEN '70' + WHEN id='97716021308405775' THEN '75' + WHEN id='97716021308405780' THEN '80' + END AS c1 +FROM t1; + +SELECT id, id BETWEEN '97716021308405775' AND '97716021308405775' AS c1 FROM t1; +SELECT id, id BETWEEN '97716021308405775' AND 97716021308405775 AS c1 FROM t1; +SELECT id, id BETWEEN 97716021308405775 AND '97716021308405775' AS c1 FROM t1; + +DROP TABLE t1; + +SELECT '97716021308405770'=97716021308405770; +SELECT 97716021308405770='97716021308405770'; + +SELECT '9e+16'=90000000000000000; +SELECT 90000000000000000='9e+16'; + +SELECT '9e+30'>90000000000000000; +SELECT 90000000000000000<'9e+30'; + +SELECT '9e+80'>90000000000000000; +SELECT 90000000000000000<'9e+80'; + +SELECT '9.9999999999999999e+80'>90000000000000000; +SELECT 90000000000000000<'9.9999999999999999e+80'; + +SELECT '1e+81'>90000000000000000; +SELECT 90000000000000000<'1e+81'; + +SELECT '9e+100'>90000000000000000; +SELECT 90000000000000000<'9e+100'; + +SELECT CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED)=18446744073709551615; +SELECT 18446744073709551615=CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED); + +SELECT CAST(0x7FFFFFFFFFFFFFFF AS UNSIGNED)=9223372036854775807; +SELECT 9223372036854775807=CAST(0x7FFFFFFFFFFFFFFF AS UNSIGNED); + + +--echo # +--echo # MDEV-25492 BETWEEN clause returns incorrect results on quoted 64-bit ints +--echo # + +CREATE TABLE betweentest +( + id int(11) unsigned NOT NULL AUTO_INCREMENT, + range1 bigint(20) DEFAULT NULL, + range2 bigint(20) DEFAULT NULL, + PRIMARY KEY (id) +); + +INSERT INTO betweentest VALUES + (1,2739499741191602369,2739499741191602369), + (2,2739499741191602192,2739499741191602192), + (3,2739138623713574912,2739138623730352127); + +SELECT * FROM betweentest WHERE '2739499741191602192' BETWEEN range1 AND range2; + +SELECT * FROM betweentest WHERE 2739499741191602192 BETWEEN range1 AND range2; + +DROP TABLE betweentest; + + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/type_num.result b/mysql-test/main/type_num.result index 2dadb4f1f8d..849190b68f4 100644 --- a/mysql-test/main/type_num.result +++ b/mysql-test/main/type_num.result @@ -550,22 +550,22 @@ SELECT COUNT(*) FROM t1 WHERE i1='1 '; COUNT(*) 2 Warnings: -Note 1292 Truncated incorrect DOUBLE value: '1 ' +Note 1292 Truncated incorrect DECIMAL value: '1 ' SELECT COUNT(*) FROM t1 WHERE i2='1 '; COUNT(*) 2 Warnings: -Note 1292 Truncated incorrect DOUBLE value: '1 ' +Note 1292 Truncated incorrect DECIMAL value: '1 ' SELECT COUNT(*) FROM t1 WHERE i4='1 '; COUNT(*) 2 Warnings: -Note 1292 Truncated incorrect DOUBLE value: '1 ' +Note 1292 Truncated incorrect DECIMAL value: '1 ' SELECT COUNT(*) FROM t1 WHERE i8='1 '; COUNT(*) 2 Warnings: -Note 1292 Truncated incorrect DOUBLE value: '1 ' +Note 1292 Truncated incorrect DECIMAL value: '1 ' SELECT COUNT(*) FROM t1 WHERE d='1 '; COUNT(*) 2 @@ -585,22 +585,22 @@ SELECT COUNT(*) FROM t1 WHERE i1=''; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Truncated incorrect DECIMAL value: '' SELECT COUNT(*) FROM t1 WHERE i2=''; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Truncated incorrect DECIMAL value: '' SELECT COUNT(*) FROM t1 WHERE i4=''; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Truncated incorrect DECIMAL value: '' SELECT COUNT(*) FROM t1 WHERE i8=''; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Truncated incorrect DECIMAL value: '' SELECT COUNT(*) FROM t1 WHERE d=''; COUNT(*) 2 @@ -620,22 +620,22 @@ SELECT COUNT(*) FROM t1 WHERE i1='x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'x' SELECT COUNT(*) FROM t1 WHERE i2='x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'x' SELECT COUNT(*) FROM t1 WHERE i4='x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'x' SELECT COUNT(*) FROM t1 WHERE i8='x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'x' SELECT COUNT(*) FROM t1 WHERE d='x'; COUNT(*) 2 @@ -655,22 +655,22 @@ SELECT COUNT(*) FROM t1 WHERE i1=' x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: ' x' +Warning 1292 Truncated incorrect DECIMAL value: ' x' SELECT COUNT(*) FROM t1 WHERE i2=' x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: ' x' +Warning 1292 Truncated incorrect DECIMAL value: ' x' SELECT COUNT(*) FROM t1 WHERE i4=' x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: ' x' +Warning 1292 Truncated incorrect DECIMAL value: ' x' SELECT COUNT(*) FROM t1 WHERE i8=' x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: ' x' +Warning 1292 Truncated incorrect DECIMAL value: ' x' SELECT COUNT(*) FROM t1 WHERE d=' x'; COUNT(*) 2 @@ -690,22 +690,22 @@ SELECT COUNT(*) FROM t1 WHERE i1='.'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '.' +Warning 1292 Truncated incorrect DECIMAL value: '.' SELECT COUNT(*) FROM t1 WHERE i2='.'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '.' +Warning 1292 Truncated incorrect DECIMAL value: '.' SELECT COUNT(*) FROM t1 WHERE i4='.'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '.' +Warning 1292 Truncated incorrect DECIMAL value: '.' SELECT COUNT(*) FROM t1 WHERE i8='.'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '.' +Warning 1292 Truncated incorrect DECIMAL value: '.' SELECT COUNT(*) FROM t1 WHERE d='.'; COUNT(*) 2 @@ -725,22 +725,22 @@ SELECT COUNT(*) FROM t1 WHERE i1='-'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '-' +Warning 1292 Truncated incorrect DECIMAL value: '-' SELECT COUNT(*) FROM t1 WHERE i2='-'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '-' +Warning 1292 Truncated incorrect DECIMAL value: '-' SELECT COUNT(*) FROM t1 WHERE i4='-'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '-' +Warning 1292 Truncated incorrect DECIMAL value: '-' SELECT COUNT(*) FROM t1 WHERE i8='-'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '-' +Warning 1292 Truncated incorrect DECIMAL value: '-' SELECT COUNT(*) FROM t1 WHERE d='-'; COUNT(*) 2 @@ -760,22 +760,22 @@ SELECT COUNT(*) FROM t1 WHERE i1='+'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '+' +Warning 1292 Truncated incorrect DECIMAL value: '+' SELECT COUNT(*) FROM t1 WHERE i2='+'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '+' +Warning 1292 Truncated incorrect DECIMAL value: '+' SELECT COUNT(*) FROM t1 WHERE i4='+'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '+' +Warning 1292 Truncated incorrect DECIMAL value: '+' SELECT COUNT(*) FROM t1 WHERE i8='+'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '+' +Warning 1292 Truncated incorrect DECIMAL value: '+' SELECT COUNT(*) FROM t1 WHERE d='+'; COUNT(*) 2 @@ -795,22 +795,22 @@ SELECT COUNT(*) FROM t1 WHERE i1='1x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1x' +Warning 1292 Truncated incorrect DECIMAL value: '1x' SELECT COUNT(*) FROM t1 WHERE i2='1x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1x' +Warning 1292 Truncated incorrect DECIMAL value: '1x' SELECT COUNT(*) FROM t1 WHERE i4='1x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1x' +Warning 1292 Truncated incorrect DECIMAL value: '1x' SELECT COUNT(*) FROM t1 WHERE i8='1x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1x' +Warning 1292 Truncated incorrect DECIMAL value: '1x' SELECT COUNT(*) FROM t1 WHERE d='1x'; COUNT(*) 2 @@ -830,22 +830,22 @@ SELECT COUNT(*) FROM t1 WHERE i1='1e'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e' +Warning 1292 Truncated incorrect DECIMAL value: '1e' SELECT COUNT(*) FROM t1 WHERE i2='1e'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e' +Warning 1292 Truncated incorrect DECIMAL value: '1e' SELECT COUNT(*) FROM t1 WHERE i4='1e'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e' +Warning 1292 Truncated incorrect DECIMAL value: '1e' SELECT COUNT(*) FROM t1 WHERE i8='1e'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e' +Warning 1292 Truncated incorrect DECIMAL value: '1e' SELECT COUNT(*) FROM t1 WHERE d='1e'; COUNT(*) 2 @@ -865,22 +865,22 @@ SELECT COUNT(*) FROM t1 WHERE i1='1e+'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e+' +Warning 1292 Truncated incorrect DECIMAL value: '1e+' SELECT COUNT(*) FROM t1 WHERE i2='1e+'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e+' +Warning 1292 Truncated incorrect DECIMAL value: '1e+' SELECT COUNT(*) FROM t1 WHERE i4='1e+'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e+' +Warning 1292 Truncated incorrect DECIMAL value: '1e+' SELECT COUNT(*) FROM t1 WHERE i8='1e+'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e+' +Warning 1292 Truncated incorrect DECIMAL value: '1e+' SELECT COUNT(*) FROM t1 WHERE d='1e+'; COUNT(*) 2 @@ -900,22 +900,22 @@ SELECT COUNT(*) FROM t1 WHERE i1='1E-'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1E-' +Warning 1292 Truncated incorrect DECIMAL value: '1E-' SELECT COUNT(*) FROM t1 WHERE i2='1E-'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1E-' +Warning 1292 Truncated incorrect DECIMAL value: '1E-' SELECT COUNT(*) FROM t1 WHERE i4='1E-'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1E-' +Warning 1292 Truncated incorrect DECIMAL value: '1E-' SELECT COUNT(*) FROM t1 WHERE i8='1E-'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1E-' +Warning 1292 Truncated incorrect DECIMAL value: '1E-' SELECT COUNT(*) FROM t1 WHERE d='1E-'; COUNT(*) 2 @@ -935,22 +935,22 @@ SELECT COUNT(*) FROM t1 WHERE i1='1Ex'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1Ex' +Warning 1292 Truncated incorrect DECIMAL value: '1Ex' SELECT COUNT(*) FROM t1 WHERE i2='1Ex'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1Ex' +Warning 1292 Truncated incorrect DECIMAL value: '1Ex' SELECT COUNT(*) FROM t1 WHERE i4='1Ex'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1Ex' +Warning 1292 Truncated incorrect DECIMAL value: '1Ex' SELECT COUNT(*) FROM t1 WHERE i8='1Ex'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1Ex' +Warning 1292 Truncated incorrect DECIMAL value: '1Ex' SELECT COUNT(*) FROM t1 WHERE d='1Ex'; COUNT(*) 2 @@ -970,22 +970,22 @@ SELECT COUNT(*) FROM t1 WHERE i1='1e+x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e+x' +Warning 1292 Truncated incorrect DECIMAL value: '1e+x' SELECT COUNT(*) FROM t1 WHERE i2='1e+x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e+x' +Warning 1292 Truncated incorrect DECIMAL value: '1e+x' SELECT COUNT(*) FROM t1 WHERE i4='1e+x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e+x' +Warning 1292 Truncated incorrect DECIMAL value: '1e+x' SELECT COUNT(*) FROM t1 WHERE i8='1e+x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e+x' +Warning 1292 Truncated incorrect DECIMAL value: '1e+x' SELECT COUNT(*) FROM t1 WHERE d='1e+x'; COUNT(*) 2 @@ -1005,22 +1005,26 @@ SELECT COUNT(*) FROM t1 WHERE i1='1e1000'; COUNT(*) 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e1000' +Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated +Warning 1292 Truncated incorrect DECIMAL value: '1e1000' SELECT COUNT(*) FROM t1 WHERE i2='1e1000'; COUNT(*) 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e1000' +Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated +Warning 1292 Truncated incorrect DECIMAL value: '1e1000' SELECT COUNT(*) FROM t1 WHERE i4='1e1000'; COUNT(*) 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e1000' +Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated +Warning 1292 Truncated incorrect DECIMAL value: '1e1000' SELECT COUNT(*) FROM t1 WHERE i8='1e1000'; COUNT(*) 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e1000' +Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated +Warning 1292 Truncated incorrect DECIMAL value: '1e1000' SELECT COUNT(*) FROM t1 WHERE d='1e1000'; COUNT(*) 0 @@ -1056,7 +1060,7 @@ SELECT COUNT(*) FROM t1 FORCE INDEX(i8) WHERE i8='1 '; COUNT(*) 2 Warnings: -Note 1292 Truncated incorrect DOUBLE value: '1 ' +Note 1292 Truncated incorrect DECIMAL value: '1 ' SELECT COUNT(*) FROM t1 FORCE INDEX(d) WHERE d='1 '; COUNT(*) 2 @@ -1081,7 +1085,7 @@ SELECT COUNT(*) FROM t1 FORCE INDEX(i8) WHERE i8=''; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Truncated incorrect DECIMAL value: '' SELECT COUNT(*) FROM t1 FORCE INDEX(d) WHERE d=''; COUNT(*) 2 diff --git a/mysql-test/main/type_num_innodb.result b/mysql-test/main/type_num_innodb.result index 581a387b2e8..92dc705de90 100644 --- a/mysql-test/main/type_num_innodb.result +++ b/mysql-test/main/type_num_innodb.result @@ -46,39 +46,22 @@ ALTER TABLE t1 MODIFY a DECIMAL(10,0); SELECT * FROM t1,t2 WHERE a=d; a b c pk d e Warnings: -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'd' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'd' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'f' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'f' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'g' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'k' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'm' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'm' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'm' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'o' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'q' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'r' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'u' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'w' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'x' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'x' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 'y' ALTER TABLE t1 MODIFY a DOUBLE; SELECT * FROM t1,t2 WHERE a=d; diff --git a/mysql-test/main/type_varchar.result b/mysql-test/main/type_varchar.result index cec279913b4..2d8d8f4b247 100644 --- a/mysql-test/main/type_varchar.result +++ b/mysql-test/main/type_varchar.result @@ -476,7 +476,6 @@ a (a DIV 2) t 0 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '1a' -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: 't ' SELECT a,CAST(a AS SIGNED) FROM t1 ORDER BY a; a CAST(a AS SIGNED) @@ -498,8 +497,8 @@ SELECT 5 = a FROM t1; 0 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 's' -Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Truncated incorrect DECIMAL value: 's' +Warning 1292 Truncated incorrect DECIMAL value: '' DROP TABLE t1; CREATE TABLE t1 (a CHAR(16)); INSERT INTO t1 VALUES ('5'), ('s'), (''); @@ -509,8 +508,8 @@ SELECT 5 = a FROM t1; 0 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 's ' -Warning 1292 Truncated incorrect DOUBLE value: ' ' +Warning 1292 Truncated incorrect DECIMAL value: 's ' +Warning 1292 Truncated incorrect DECIMAL value: ' ' DROP TABLE t1; # # MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 diff --git a/mysql-test/main/type_year.result b/mysql-test/main/type_year.result index 26f90757d09..b0fe62523c0 100644 --- a/mysql-test/main/type_year.result +++ b/mysql-test/main/type_year.result @@ -210,11 +210,11 @@ SELECT * FROM t2 WHERE yy = 'test'; yy c2 00 2000 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'test' +Warning 1292 Truncated incorrect DECIMAL value: 'test' SELECT * FROM t4 WHERE yyyy = 'test'; yyyy c4 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'test' +Warning 1292 Truncated incorrect DECIMAL value: 'test' SELECT * FROM t2 WHERE yy = '1999'; yy c2 99 1999 diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index e92ba3f184f..9cce0d0f626 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -1503,6 +1503,8 @@ execute stmt1 using @a; set @a= 301; execute stmt1 using @a; deallocate prepare stmt1; +insert into v3(a) select sum(302); +insert into v3(a) select sum(303) over (); select * from v3; a b 100 0 @@ -1521,6 +1523,14 @@ a b 301 10 301 1000 301 2000 +302 0 +302 10 +302 1000 +302 2000 +303 0 +303 10 +303 1000 +303 2000 drop view v3; drop tables t1,t2; create table t1(f1 int); @@ -6153,11 +6163,11 @@ CREATE VIEW v1 AS SELECT * FROM t1; SELECT * FROM t1 WHERE a <> 0 AND a = ' 1'; a Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '`1' +Warning 1292 Truncated incorrect DECIMAL value: '`1' SELECT * FROM v1 WHERE a <> 0 AND a = ' 1'; a Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '`1' +Warning 1292 Truncated incorrect DECIMAL value: '`1' DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (a ENUM('5','6')); @@ -6180,11 +6190,11 @@ CREATE VIEW v1 AS SELECT * FROM t1; SELECT * FROM t1 WHERE a <> 0 AND a = ' 1'; a Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '`1' +Warning 1292 Truncated incorrect DECIMAL value: '`1' SELECT * FROM v1 WHERE a <> 0 AND a = ' 1'; a Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '`1' +Warning 1292 Truncated incorrect DECIMAL value: '`1' DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (a ENUM('5','6')); diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 6e65666652b..f8a51ec7a2c 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -1334,6 +1334,8 @@ execute stmt1 using @a; set @a= 301; execute stmt1 using @a; deallocate prepare stmt1; +insert into v3(a) select sum(302); +insert into v3(a) select sum(303) over (); --sorted_result select * from v3; diff --git a/mysql-test/main/warnings.result b/mysql-test/main/warnings.result index aa4cab37741..8340cb9e981 100644 --- a/mysql-test/main/warnings.result +++ b/mysql-test/main/warnings.result @@ -327,7 +327,6 @@ select CAST(a AS DECIMAL(13,5)) FROM (SELECT '' as a) t; CAST(a AS DECIMAL(13,5)) 0.00000 Warnings: -Warning 1918 Encountered illegal value '' when converting to DECIMAL Warning 1292 Truncated incorrect DECIMAL value: '' create table t1 (a integer unsigned); insert into t1 values (1),(-1),(0),(-2); diff --git a/mysql-test/main/xtradb_mrr.result b/mysql-test/main/xtradb_mrr.result index 2f18a7c287f..28c75e1f1d7 100644 --- a/mysql-test/main/xtradb_mrr.result +++ b/mysql-test/main/xtradb_mrr.result @@ -435,18 +435,18 @@ SELECT table1 .`col_varchar_key` FROM t1 table1 STRAIGHT_JOIN ( t1 table3 JOIN t1 table4 ON table4 .`pk` = table3 .`col_int_nokey` ) ON table4 .`col_varchar_nokey` ; col_varchar_key Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'r' -Warning 1292 Truncated incorrect DOUBLE value: 'r' -Warning 1292 Truncated incorrect DOUBLE value: 'r' -Warning 1292 Truncated incorrect DOUBLE value: 'r' -Warning 1292 Truncated incorrect DOUBLE value: 'r' -Warning 1292 Truncated incorrect DOUBLE value: 'r' -Warning 1292 Truncated incorrect DOUBLE value: 'c' -Warning 1292 Truncated incorrect DOUBLE value: 'c' -Warning 1292 Truncated incorrect DOUBLE value: 'c' -Warning 1292 Truncated incorrect DOUBLE value: 'c' -Warning 1292 Truncated incorrect DOUBLE value: 'c' -Warning 1292 Truncated incorrect DOUBLE value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'r' +Warning 1292 Truncated incorrect DECIMAL value: 'r' +Warning 1292 Truncated incorrect DECIMAL value: 'r' +Warning 1292 Truncated incorrect DECIMAL value: 'r' +Warning 1292 Truncated incorrect DECIMAL value: 'r' +Warning 1292 Truncated incorrect DECIMAL value: 'r' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'c' DROP TABLE t1; set join_cache_level=@save_join_cache_level; set optimizer_switch=@save_optimizer_switch; @@ -664,70 +664,70 @@ table3.col_varchar_nokey = table2.col_varchar_key AND table3.pk > table2.col_var count(*) sum(table1.col_int_key*table2.pk) 240 185955 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' -Warning 1292 Truncated incorrect DOUBLE value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' +Warning 1292 Truncated incorrect DECIMAL value: 'v' set @my_save_join_cache_level= @@join_cache_level; set @my_save_join_buffer_size= @@join_buffer_size; set join_cache_level=6; |