diff options
43 files changed, 1599 insertions, 613 deletions
diff --git a/mysql-test/include/type_mix_incompatible.inc b/mysql-test/include/type_mix_incompatible.inc index 750dc7bcac6..92a75be6e81 100644 --- a/mysql-test/include/type_mix_incompatible.inc +++ b/mysql-test/include/type_mix_incompatible.inc @@ -1,5 +1,9 @@ --echo # Start of type_store_assignment_incompatible.inc +--disable_abort_on_error + +SET @sql_mode_save= @@sql_mode; + SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -10,8 +14,15 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +# 'IGNORE' -> ' IGNORE' +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) + ELSE @ignore + END; + let $source_type= `(SELECT @source_type)`; let $target_type= `(SELECT @target_type)`; +let $ignore=`(SELECT @ignore)`; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; @@ -23,8 +34,7 @@ SHOW CREATE TABLE t2; # CREATE TABLE t3 LIKE t2; ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION -INSERT INTO t3 VALUES +eval INSERT$ignore INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); @@ -52,8 +62,7 @@ EXECUTE IMMEDIATE IF(@source_type='geometry','AsText(source)','source'), ' AS source ', ' FROM t3'); ---error 0,ER_CANT_CREATE_GEOMETRY_OBJECT -INSERT INTO t3 VALUES +eval INSERT$ignore INTO t3 VALUES (1, (SELECT target FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)), @@ -64,8 +73,7 @@ SELECT * FROM v3; TRUNCATE TABLE t3; SET sql_mode=STRICT_ALL_TABLES; ---error ER_TRUNCATED_WRONG_VALUE, ER_TRUNCATED_WRONG_VALUE_FOR_FIELD, WARN_DATA_TRUNCATED, ER_CANT_CREATE_GEOMETRY_OBJECT -INSERT INTO t3 VALUES +eval INSERT$ignore INTO t3 VALUES (1, (SELECT target FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)), @@ -74,7 +82,7 @@ INSERT INTO t3 VALUES (SELECT source FROM t2 ORDER BY id LIMIT 1)); SELECT * FROM v3; TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; @@ -83,55 +91,46 @@ DROP VIEW v3; # CREATE TABLE t3 LIKE t2; ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION -INSERT INTO t3 SELECT id,source,source FROM t2; +eval INSERT$ignore INTO t3 SELECT id,source,source FROM t2; ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION -INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; +eval INSERT$ignore INTO t3 (id,target,source) SELECT id,source,source FROM t2; # # INSERT .. VALUES .. ON DUPLICATE KEY UPDATE target=source # ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION -INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; +eval INSERT$ignore INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION -INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; +eval INSERT$ignore INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; # # INSERT .. SELECT .. ON DUPLICATE KEY UPDATE target=source # ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION -INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; +eval INSERT$ignore INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION -INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; +eval INSERT$ignore INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; # # UPDATE # ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION -UPDATE t3 SET target=source; +eval UPDATE$ignore t3 SET target=source; # # UPDATE, multi-table # ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION -UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; +eval UPDATE$ignore t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; # # ALTER # -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION EXECUTE IMMEDIATE @alter; @@ -154,7 +153,6 @@ BEGIN END; $$ DELIMITER ;$$ ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION CALL p1; DROP PROCEDURE p1; @@ -163,12 +161,10 @@ DROP PROCEDURE p1; # --eval CREATE FUNCTION f1(a $target_type) RETURNS INT RETURN NULL; ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); DROP FUNCTION f1; --eval CREATE PROCEDURE p1(a $target_type) BEGIN END; ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); DROP PROCEDURE p1; @@ -190,7 +186,6 @@ BEGIN END; $$ DELIMITER ;$$ ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION CALL p2(); SHOW WARNINGS; DROP PROCEDURE p2; @@ -209,7 +204,6 @@ BEGIN END; $$ DELIMITER ;$$ ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION SELECT f1(); DROP FUNCTION f1; @@ -227,7 +221,6 @@ BEGIN END; $$ DELIMITER ;$$ ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION CALL p1(); DROP PROCEDURE p1; @@ -249,10 +242,11 @@ BEGIN END; $$ DELIMITER ;$$ ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION CALL p1(); DROP PROCEDURE p1; DROP TABLE t2; +--enable_abort_on_error + --echo # End of type_store_assignment_incompatible.inc diff --git a/mysql-test/main/get_diagnostics.result b/mysql-test/main/get_diagnostics.result index 1636c463f70..48eab8e0734 100644 --- a/mysql-test/main/get_diagnostics.result +++ b/mysql-test/main/get_diagnostics.result @@ -1152,11 +1152,11 @@ SELECT @var62, @var63; @var62 @var63 1 NULL INSERT INTO t1 SELECT id2, val2, p2 from t2; -ERROR HY000: Illegal parameter data types double and point for operation 'SET' +ERROR HY000: Cannot cast 'point' as 'double' in assignment of `test`.`t1`.`d1` GET DIAGNOSTICS CONDITION 1 @var64= ROW_NUMBER; GET DIAGNOSTICS CONDITION 2 @var65= ROW_NUMBER; Warnings: -Error 4078 Illegal parameter data types double and point for operation 'SET' +Error 4078 Cannot cast 'point' as 'double' in assignment of `test`.`t1`.`d1` Error 1758 Invalid condition number SELECT @var64, @var65; @var64 @var65 @@ -1396,11 +1396,11 @@ SELECT @var103, @var104; @var103 @var104 1 NULL INSERT INTO t1 SELECT id2, val2, p2 from t2; -ERROR HY000: Illegal parameter data types double and point for operation 'SET' +ERROR HY000: Cannot cast 'point' as 'double' in assignment of `test`.`t1`.`d1` GET DIAGNOSTICS CONDITION 1 @var105= ROW_NUMBER; GET DIAGNOSTICS CONDITION 2 @var106= ROW_NUMBER; Warnings: -Error 4078 Illegal parameter data types double and point for operation 'SET' +Error 4078 Cannot cast 'point' as 'double' in assignment of `test`.`t1`.`d1` Error 1758 Invalid condition number SELECT @var105, @var106; @var105 @var106 diff --git a/mysql-test/main/gis.result b/mysql-test/main/gis.result index 5fdea9cb353..88e56c4ea96 100644 --- a/mysql-test/main/gis.result +++ b/mysql-test/main/gis.result @@ -688,9 +688,9 @@ object_id geometrytype(geo) ISSIMPLE(GEO) ASTEXT(centroid(geo)) drop table t1; create table t1 (fl geometry not null); insert into t1 values (1); -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t1`.`fl` insert into t1 values (1.11); -ERROR HY000: Illegal parameter data types geometry and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'geometry' in assignment of `test`.`t1`.`fl` insert into t1 values ("qwerty"); ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field insert into t1 values (pointfromtext('point(1,1)')); diff --git a/mysql-test/main/sp-row.result b/mysql-test/main/sp-row.result index 1cbfa8c639f..f3c0a9e343a 100644 --- a/mysql-test/main/sp-row.result +++ b/mysql-test/main/sp-row.result @@ -20,7 +20,7 @@ RETURN a; END; $$ SELECT f1(ROW(10,20)); -ERROR HY000: Illegal parameter data types int and row for operation 'SET' +ERROR HY000: Cannot cast 'row' as 'int' in assignment of `f1(ROW(10,20))` DROP FUNCTION f1; # # ROW as an SP parameter @@ -236,7 +236,7 @@ SELECT f1(a); END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types int and row for operation 'SET' +ERROR HY000: Cannot cast 'row' as 'int' in assignment of `a` DROP PROCEDURE p1; DROP FUNCTION f1; # @@ -286,7 +286,7 @@ RETURN rec; END; $$ SELECT f1(10); -ERROR HY000: Illegal parameter data types int and row for operation 'SET' +ERROR HY000: Cannot cast 'row' as 'int' in assignment of `f1(10)` DROP FUNCTION f1; # # Using the entire ROW in SELECT..CREATE diff --git a/mysql-test/main/sp-vars.result b/mysql-test/main/sp-vars.result index 127a0993e2b..feef67ccb1d 100644 --- a/mysql-test/main/sp-vars.result +++ b/mysql-test/main/sp-vars.result @@ -1026,11 +1026,11 @@ BEGIN SELECT arg; END| CALL p1((1, 2)); -ERROR HY000: Illegal parameter data types tinyint and row for operation 'SET' +ERROR 21000: Operand should contain 1 column(s) CALL p1((SELECT * FROM t1 LIMIT 1)); -ERROR HY000: Illegal parameter data types tinyint and row for operation 'SET' +ERROR 21000: Operand should contain 1 column(s) CALL p1((SELECT col1, col2 FROM t1 LIMIT 1)); -ERROR HY000: Illegal parameter data types tinyint and row for operation 'SET' +ERROR 21000: Operand should contain 1 column(s) DROP PROCEDURE p1; DROP TABLE t1; diff --git a/mysql-test/main/sp-vars.test b/mysql-test/main/sp-vars.test index 5e1e07888ca..9edf245acbe 100644 --- a/mysql-test/main/sp-vars.test +++ b/mysql-test/main/sp-vars.test @@ -1221,13 +1221,13 @@ BEGIN END| delimiter ;| ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +--error ER_OPERAND_COLUMNS CALL p1((1, 2)); ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +--error ER_OPERAND_COLUMNS CALL p1((SELECT * FROM t1 LIMIT 1)); ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +--error ER_OPERAND_COLUMNS CALL p1((SELECT col1, col2 FROM t1 LIMIT 1)); # diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result index 364e7b88ccf..359deffb4d1 100644 --- a/mysql-test/main/sp.result +++ b/mysql-test/main/sp.result @@ -255,7 +255,7 @@ return i+1| call sub1("sub1a", (select 7))| call sub1("sub1b", (select max(i) from t2))| call sub1("sub1c", (select i,d from t2 limit 1))| -ERROR HY000: Illegal parameter data types int and row for operation 'SET' +ERROR HY000: Cannot cast 'row' as 'int' in assignment of `x` call sub1("sub1d", (select 1 from (select 1) a))| call sub2("sub2")| select * from t1 order by id| diff --git a/mysql-test/main/type_geometry_mix_int.result b/mysql-test/main/type_geometry_mix_int.result index e5d00c63727..bf05ca1829d 100644 --- a/mysql-test/main/type_geometry_mix_int.result +++ b/mysql-test/main/type_geometry_mix_int.result @@ -6,6 +6,7 @@ # CREATE TABLE t1 (target GEOMETRY DEFAULT POINT(1,1), source INT DEFAULT 0); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -14,6 +15,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -30,7 +35,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -47,6 +52,7 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field SELECT * FROM v3; id target source 1 POINT(1 1) 0 @@ -59,37 +65,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field SELECT * FROM v3; id target source 1 POINT(1 1) 0 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target int(11) EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -100,15 +106,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a geometry) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a geometry) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst geometry) BEGIN @@ -123,10 +129,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types geometry and int for operation 'SET' +Error 4078 Cannot cast 'int' as 'geometry' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -138,7 +144,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -149,7 +155,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -163,13 +169,14 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc DROP TABLE t1; CREATE TABLE t1 (target INT DEFAULT 0, source GEOMETRY DEFAULT POINT(1,1)); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -178,6 +185,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -194,7 +205,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -226,37 +237,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect integer value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\xF0?\x00\x00\x00\x00\x00\x00\xF0?' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 0 POINT(1 1) TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target geometry EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -267,15 +278,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a int(11)) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a int(11)) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst int(11)) BEGIN @@ -290,10 +301,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types int and geometry for operation 'SET' +Error 4078 Cannot cast 'geometry' as 'int' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -305,7 +316,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -316,7 +327,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -330,7 +341,7 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types int and geometry for operation 'SET' +ERROR HY000: Cannot cast 'geometry' as 'int' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc diff --git a/mysql-test/suite/archive/archive_gis.result b/mysql-test/suite/archive/archive_gis.result index 25854db1feb..527a6daeee8 100644 --- a/mysql-test/suite/archive/archive_gis.result +++ b/mysql-test/suite/archive/archive_gis.result @@ -452,9 +452,9 @@ ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field drop table t1; create table t1 (pk integer primary key auto_increment, fl geometry not null); insert into t1 (fl) values (1); -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t1`.`fl` insert into t1 (fl) values (1.11); -ERROR HY000: Illegal parameter data types geometry and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'geometry' in assignment of `test`.`t1`.`fl` insert into t1 (fl) values ("qwerty"); ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field insert into t1 (fl) values (pointfromtext('point(1,1)')); diff --git a/mysql-test/suite/compat/oracle/r/sp-row.result b/mysql-test/suite/compat/oracle/r/sp-row.result index a15fe30d8af..7fd986a71c8 100644 --- a/mysql-test/suite/compat/oracle/r/sp-row.result +++ b/mysql-test/suite/compat/oracle/r/sp-row.result @@ -24,7 +24,7 @@ RETURN a; END; $$ SELECT f1(ROW(10,20)); -ERROR HY000: Illegal parameter data types int and row for operation 'SET' +ERROR 21000: Operand should contain 1 column(s) DROP FUNCTION f1; # # ROW as an SP parameter @@ -261,7 +261,7 @@ SELECT f1(a); END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types int and row for operation 'SET' +ERROR 21000: Operand should contain 1 column(s) DROP PROCEDURE p1; DROP FUNCTION f1; CREATE FUNCTION f1(a INT) RETURN INT @@ -278,7 +278,7 @@ SELECT f1(a); END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types int and row for operation 'SET' +ERROR 21000: Operand should contain 1 column(s) DROP PROCEDURE p1; DROP FUNCTION f1; # @@ -332,7 +332,7 @@ RETURN rec; END; $$ SELECT f1(10); -ERROR HY000: Illegal parameter data types int and row for operation 'SET' +ERROR 21000: Operand should contain 1 column(s) DROP FUNCTION f1; # # Using the entire ROW in SELECT..CREATE diff --git a/mysql-test/suite/compat/oracle/t/sp-row.test b/mysql-test/suite/compat/oracle/t/sp-row.test index c7658c76838..ebd0a2a2137 100644 --- a/mysql-test/suite/compat/oracle/t/sp-row.test +++ b/mysql-test/suite/compat/oracle/t/sp-row.test @@ -35,7 +35,7 @@ BEGIN END; $$ DELIMITER ;$$ ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +--error ER_OPERAND_COLUMNS SELECT f1(ROW(10,20)); DROP FUNCTION f1; @@ -334,7 +334,7 @@ BEGIN END; $$ DELIMITER ;$$ ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +--error ER_OPERAND_COLUMNS CALL p1(); DROP PROCEDURE p1; DROP FUNCTION f1; @@ -355,7 +355,7 @@ BEGIN END; $$ DELIMITER ;$$ ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +--error ER_OPERAND_COLUMNS CALL p1(); DROP PROCEDURE p1; DROP FUNCTION f1; @@ -427,7 +427,7 @@ BEGIN END; $$ DELIMITER ;$$ ---error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +--error ER_OPERAND_COLUMNS SELECT f1(10); DROP FUNCTION f1; diff --git a/mysql-test/suite/innodb/r/innodb_gis.result b/mysql-test/suite/innodb/r/innodb_gis.result index c222e7d053a..57f894d85ef 100644 --- a/mysql-test/suite/innodb/r/innodb_gis.result +++ b/mysql-test/suite/innodb/r/innodb_gis.result @@ -452,9 +452,9 @@ ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field drop table t1; create table t1 (pk integer primary key auto_increment, fl geometry not null); insert into t1 (fl) values (1); -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t1`.`fl` insert into t1 (fl) values (1.11); -ERROR HY000: Illegal parameter data types geometry and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'geometry' in assignment of `test`.`t1`.`fl` insert into t1 (fl) values ("qwerty"); ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field insert into t1 (fl) values (pointfromtext('point(1,1)')); diff --git a/mysql-test/suite/innodb_gis/r/0.result b/mysql-test/suite/innodb_gis/r/0.result index 3f72baadd12..355f8958018 100644 --- a/mysql-test/suite/innodb_gis/r/0.result +++ b/mysql-test/suite/innodb_gis/r/0.result @@ -452,9 +452,9 @@ ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field drop table t1; create table t1 (pk integer primary key auto_increment, fl geometry not null); insert into t1 (fl) values (1); -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t1`.`fl` insert into t1 (fl) values (1.11); -ERROR HY000: Illegal parameter data types geometry and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'geometry' in assignment of `test`.`t1`.`fl` insert into t1 (fl) values ("qwerty"); ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field insert into t1 (fl) values (pointfromtext('point(1,1)')); diff --git a/mysql-test/suite/innodb_gis/r/1.result b/mysql-test/suite/innodb_gis/r/1.result index 1c286ff6ce4..1d7b188e0e3 100644 --- a/mysql-test/suite/innodb_gis/r/1.result +++ b/mysql-test/suite/innodb_gis/r/1.result @@ -660,9 +660,9 @@ object_id ST_geometrytype(geo) ST_ISSIMPLE(GEO) ST_ASTEXT(ST_centroid(geo)) drop table t1; create table t1 (fl geometry not null); insert into t1 values (1); -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t1`.`fl` insert into t1 values (1.11); -ERROR HY000: Illegal parameter data types geometry and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'geometry' in assignment of `test`.`t1`.`fl` insert into t1 values ("qwerty"); ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field insert into t1 values (ST_pointfromtext('point(1,1)')); diff --git a/mysql-test/suite/innodb_gis/r/gis.result b/mysql-test/suite/innodb_gis/r/gis.result index a976bbccaa9..0aa379df487 100644 --- a/mysql-test/suite/innodb_gis/r/gis.result +++ b/mysql-test/suite/innodb_gis/r/gis.result @@ -661,9 +661,9 @@ object_id ST_geometrytype(geo) ST_ISSIMPLE(GEO) ST_ASTEXT(ST_centroid(geo)) drop table t1; create table t1 (fl geometry not null); insert into t1 values (1); -ERROR HY000: Illegal parameter data types geometry and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'geometry' in assignment of `test`.`t1`.`fl` insert into t1 values (1.11); -ERROR HY000: Illegal parameter data types geometry and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'geometry' in assignment of `test`.`t1`.`fl` insert into t1 values ("qwerty"); ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field insert into t1 values (ST_pointfromtext('point(1,1)')); diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6.result b/plugin/type_inet/mysql-test/type_inet/type_inet6.result index 570655a972b..cfb9189ea28 100644 --- a/plugin/type_inet/mysql-test/type_inet/type_inet6.result +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6.result @@ -192,9 +192,9 @@ CREATE TABLE t1 (a INET6); INSERT INTO t1 VALUES ('x'); ERROR 22007: Incorrect inet6 value: 'x' for column `test`.`t1`.`a` at row 1 INSERT INTO t1 VALUES (1); -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `test`.`t1`.`a` INSERT INTO t1 VALUES (TIME'10:20:30'); -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `test`.`t1`.`a` INSERT INTO t1 VALUES (0x00); ERROR 22007: Incorrect inet6 value: '\x00' for column `test`.`t1`.`a` at row 1 DROP TABLE t1; @@ -820,15 +820,15 @@ DROP TABLE t1; # CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (CAST('::' AS INET6)); -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `test`.`t1`.`a` DROP TABLE t1; CREATE TABLE t1 (a DOUBLE); INSERT INTO t1 VALUES (CAST('::' AS INET6)); -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `test`.`t1`.`a` DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(32,0)); INSERT INTO t1 VALUES (CAST('::' AS INET6)); -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `test`.`t1`.`a` DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(64)); INSERT INTO t1 VALUES (CAST('::' AS INET6)); @@ -1606,7 +1606,7 @@ DROP TABLE t1; CREATE TABLE t1 (a INET6, b INT); INSERT INTO t1 VALUES ('ffff::ffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1614,7 +1614,7 @@ DROP TABLE t1; CREATE TABLE t1 (a INET6, b DOUBLE); INSERT INTO t1 VALUES ('ffff::ffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1622,7 +1622,7 @@ DROP TABLE t1; CREATE TABLE t1 (a INET6, b DECIMAL(32,0)); INSERT INTO t1 VALUES ('ffff::ffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1630,7 +1630,7 @@ DROP TABLE t1; CREATE TABLE t1 (a INET6, b YEAR); INSERT INTO t1 VALUES ('ffff::ffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types year and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'year' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1641,7 +1641,7 @@ DROP TABLE t1; CREATE TABLE t1 (a INT, b INET6); INSERT INTO t1 VALUES (1, NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1649,7 +1649,7 @@ DROP TABLE t1; CREATE TABLE t1 (a DOUBLE, b INET6); INSERT INTO t1 VALUES (1, NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1657,7 +1657,7 @@ DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(32,0), b INET6); INSERT INTO t1 VALUES (1, NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1665,7 +1665,7 @@ DROP TABLE t1; CREATE TABLE t1 (a YEAR, b INET6); INSERT INTO t1 VALUES (1, NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types inet6 and year for operation 'SET' +ERROR HY000: Cannot cast 'year' as 'inet6' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1676,7 +1676,7 @@ DROP TABLE t1; CREATE TABLE t1 (a INET6, b TIME); INSERT INTO t1 VALUES ('ffff::ffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1684,7 +1684,7 @@ DROP TABLE t1; CREATE TABLE t1 (a INET6, b DATE); INSERT INTO t1 VALUES ('ffff::ffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types date and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'date' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1692,7 +1692,7 @@ DROP TABLE t1; CREATE TABLE t1 (a INET6, b DATETIME); INSERT INTO t1 VALUES ('ffff::ffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types datetime and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'datetime' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1700,7 +1700,7 @@ DROP TABLE t1; CREATE TABLE t1 (a INET6, b TIMESTAMP NULL DEFAULT NULL); INSERT INTO t1 VALUES ('ffff::ffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types timestamp and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'timestamp' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1711,7 +1711,7 @@ DROP TABLE t1; CREATE TABLE t1 (a TIME, b INET6); INSERT INTO t1 VALUES ('00:00:00', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1719,7 +1719,7 @@ DROP TABLE t1; CREATE TABLE t1 (a DATE, b INET6); INSERT INTO t1 VALUES ('2001-01:01', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types inet6 and date for operation 'SET' +ERROR HY000: Cannot cast 'date' as 'inet6' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1727,7 +1727,7 @@ DROP TABLE t1; CREATE TABLE t1 (a DATETIME, b INET6); INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types inet6 and datetime for operation 'SET' +ERROR HY000: Cannot cast 'datetime' as 'inet6' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1735,7 +1735,7 @@ DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP, b INET6); INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types inet6 and timestamp for operation 'SET' +ERROR HY000: Cannot cast 'timestamp' as 'inet6' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -1922,7 +1922,7 @@ DROP TABLE t1; CREATE TABLE t1 (a INET6, b INT); INSERT INTO t1 (a) VALUES ('::'); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `test`.`t1`.`b` SELECT * FROM t1; a b :: NULL @@ -1931,7 +1931,7 @@ SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); CREATE TABLE t1 (a INET6, b TIMESTAMP); INSERT INTO t1 (a) VALUES ('::'); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types timestamp and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'timestamp' in assignment of `test`.`t1`.`b` SELECT * FROM t1; a b :: 2001-01-01 10:20:30 @@ -1940,7 +1940,7 @@ SET timestamp=DEFAULT; CREATE OR REPLACE TABLE t1 (a INET6); INSERT INTO t1 (a) VALUES ('::'); ALTER TABLE t1 MODIFY a DATE; -ERROR HY000: Illegal parameter data types date and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'date' in assignment of `test`.`t1`.`a` DROP TABLE t1; # # MDEV-20818 ER_CRASHED_ON_USAGE or Assertion `length <= column->length' failed in write_block_record on temporary table diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_decimal.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_decimal.result index 024b9d012be..230b19f5a42 100644 --- a/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_decimal.result +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_decimal.result @@ -6,6 +6,7 @@ # CREATE TABLE t1 (target INET6 DEFAULT '::0', source DECIMAL(38,0) DEFAULT 0); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -14,6 +15,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -30,7 +35,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -62,37 +67,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 :: 0 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target decimal(38,0) EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -103,15 +108,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a inet6) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a inet6) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst inet6) BEGIN @@ -126,10 +131,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types inet6 and decimal for operation 'SET' +Error 4078 Cannot cast 'decimal' as 'inet6' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -141,7 +146,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -152,7 +157,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -166,13 +171,14 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc DROP TABLE t1; CREATE TABLE t1 (target DECIMAL(38,0) DEFAULT 0, source INET6 DEFAULT '::0'); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -181,6 +187,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -197,7 +207,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -229,37 +239,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect decimal value: '::' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 0 :: TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target inet6 EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types inet6 and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'inet6' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -270,15 +280,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a decimal(38,0)) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a decimal(38,0)) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst decimal(38,0)) BEGIN @@ -293,10 +303,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types decimal and inet6 for operation 'SET' +Error 4078 Cannot cast 'inet6' as 'decimal' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -308,7 +318,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -319,7 +329,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -333,7 +343,7 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types decimal and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'decimal' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double.result index 41baf7b8ca3..f677e768a7d 100644 --- a/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double.result +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double.result @@ -6,6 +6,7 @@ # CREATE TABLE t1 (target INET6 DEFAULT '::0', source DOUBLE DEFAULT 0); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -14,6 +15,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -30,7 +35,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -62,37 +67,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 :: 0 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target double EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -103,15 +108,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a inet6) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a inet6) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst inet6) BEGIN @@ -126,10 +131,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types inet6 and double for operation 'SET' +Error 4078 Cannot cast 'double' as 'inet6' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -141,7 +146,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -152,7 +157,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -166,13 +171,14 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc DROP TABLE t1; CREATE TABLE t1 (target DOUBLE DEFAULT 0, source INET6 DEFAULT '::0'); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -181,6 +187,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -197,7 +207,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -229,37 +239,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect double value: '::' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 0 :: TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target inet6 EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types inet6 and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -270,15 +280,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a double) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a double) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst double) BEGIN @@ -293,10 +303,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types double and inet6 for operation 'SET' +Error 4078 Cannot cast 'inet6' as 'double' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -308,7 +318,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -319,7 +329,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -333,7 +343,7 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types double and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double_loose.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double_loose.result new file mode 100644 index 00000000000..e624b95b980 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double_loose.result @@ -0,0 +1,406 @@ +# +# Start of 10.7 tests +# +# +# MDEV-28918 Implicit cast from INET6 UNSIGNED works differently on UPDATE vs ALTER +# +SET sql_mode=''; +CREATE TABLE t1 (target INET6 DEFAULT '::0', source DOUBLE DEFAULT 0); +# Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; +SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS +WHERE COLUMN_NAME='source' + AND TABLE_NAME='t1' + AND TABLE_SCHEMA='test'); +SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS +WHERE COLUMN_NAME='target' + AND TABLE_NAME='t1' + AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; +CREATE TABLE t2 LIKE t1; +ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; +INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) NOT NULL, + `target` inet6 DEFAULT '::', + `source` double DEFAULT 0, + PRIMARY KEY (`id`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +CREATE TABLE t3 LIKE t2; +INSERT INTO t3 VALUES +(1, +(SELECT source FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)); +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +DROP TABLE t3; +SET sql_mode=''; +CREATE TABLE t3 LIKE t2; +ALTER TABLE t3 ENGINE=MyISAM; +EXECUTE IMMEDIATE +CONCAT('CREATE VIEW v3 AS SELECT id,', +IF(@target_type='geometry','AsText(target)','target'), ' AS target,', +IF(@source_type='geometry','AsText(source)','source'), ' AS source ', +' FROM t3'); +INSERT INTO t3 VALUES +(1, +(SELECT target FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)), +(2, +(SELECT source FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)); +Warnings: +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 2 +SELECT * FROM v3; +id target source +1 :: 0 +2 :: 0 +TRUNCATE TABLE t3; +SET sql_mode=STRICT_ALL_TABLES; +INSERT INTO t3 VALUES +(1, +(SELECT target FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)), +(2, +(SELECT source FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)); +ERROR 22007: Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 2 +SELECT * FROM v3; +id target source +1 :: 0 +TRUNCATE TABLE t3; +SET sql_mode=@sql_mode_save; +DROP TABLE t3; +DROP VIEW v3; +CREATE TABLE t3 LIKE t2; +INSERT INTO t3 SELECT id,source,source FROM t2; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +UPDATE t3 SET target=source; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column ``.`(temporary)`.`target` at row 1 +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); +SELECT @alter; +@alter +ALTER TABLE t3 MODIFY target double +EXECUTE IMMEDIATE @alter; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +DROP TABLE t3; +DROP TABLE t2; +CREATE PROCEDURE p1() +BEGIN +DECLARE src double DEFAULT NULL; +DECLARE dst inet6 DEFAULT NULL; +SET dst=src; +END; +$$ +CALL p1; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `dst` +DROP PROCEDURE p1; +CREATE FUNCTION f1(a inet6) RETURNS INT RETURN NULL;; +SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); +f1((SELECT source FROM t1 ORDER BY source LIMIT 1)) +NULL +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `a` +DROP FUNCTION f1; +CREATE PROCEDURE p1(a inet6) BEGIN END;; +CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `a` +DROP PROCEDURE p1; +CREATE PROCEDURE p1(OUT dst inet6) +BEGIN +DECLARE src double DEFAULT NULL; +SET dst=src; +END; +$$ +CREATE PROCEDURE p2() +BEGIN +DECLARE dst inet6 DEFAULT NULL; +CALL p1(dst); +END; +$$ +CALL p2(); +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `dst` +SHOW WARNINGS; +Level Code Message +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `dst` +DROP PROCEDURE p2; +DROP PROCEDURE p1; +CREATE FUNCTION f1() RETURNS inet6 +BEGIN +DECLARE rc double DEFAULT NULL; +RETURN rc; +END; +$$ +SELECT f1(); +f1() +NULL +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `f1()` +DROP FUNCTION f1; +CREATE PROCEDURE p1() +BEGIN +DECLARE src double DEFAULT NULL; +DECLARE cur1 CURSOR(t inet6) FOR SELECT * FROM t1 WHERE target=t; +OPEN cur1(src); +CLOSE cur1; +END; +$$ +CALL p1(); +DROP PROCEDURE p1; +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES (); +CREATE PROCEDURE p1() +BEGIN +DECLARE dst inet6 DEFAULT NULL; +DECLARE cur2 CURSOR FOR SELECT source FROM t2 ORDER BY source LIMIT 1; +OPEN cur2; +FETCH cur2 INTO dst; +CLOSE cur2; +END; +$$ +CALL p1(); +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `dst` +Warning 1292 Incorrect inet6 value: '0' for column ``.``.`dst` at row 1 +DROP PROCEDURE p1; +DROP TABLE t2; +# End of type_store_assignment_incompatible.inc +DROP TABLE t1; +CREATE TABLE t1 (target DOUBLE DEFAULT 0, source INET6 DEFAULT '::0'); +# Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; +SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS +WHERE COLUMN_NAME='source' + AND TABLE_NAME='t1' + AND TABLE_SCHEMA='test'); +SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS +WHERE COLUMN_NAME='target' + AND TABLE_NAME='t1' + AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; +CREATE TABLE t2 LIKE t1; +ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; +INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) NOT NULL, + `target` double DEFAULT 0, + `source` inet6 DEFAULT '::', + PRIMARY KEY (`id`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +CREATE TABLE t3 LIKE t2; +INSERT INTO t3 VALUES +(1, +(SELECT source FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)); +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +DROP TABLE t3; +SET sql_mode=''; +CREATE TABLE t3 LIKE t2; +ALTER TABLE t3 ENGINE=MyISAM; +EXECUTE IMMEDIATE +CONCAT('CREATE VIEW v3 AS SELECT id,', +IF(@target_type='geometry','AsText(target)','target'), ' AS target,', +IF(@source_type='geometry','AsText(source)','source'), ' AS source ', +' FROM t3'); +INSERT INTO t3 VALUES +(1, +(SELECT target FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)), +(2, +(SELECT source FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)); +Warnings: +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 2 +SELECT * FROM v3; +id target source +1 0 :: +2 0 :: +TRUNCATE TABLE t3; +SET sql_mode=STRICT_ALL_TABLES; +INSERT INTO t3 VALUES +(1, +(SELECT target FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)), +(2, +(SELECT source FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)); +ERROR 22007: Incorrect double value: '::' for column `test`.`t3`.`target` at row 2 +SELECT * FROM v3; +id target source +1 0 :: +TRUNCATE TABLE t3; +SET sql_mode=@sql_mode_save; +DROP TABLE t3; +DROP VIEW v3; +CREATE TABLE t3 LIKE t2; +INSERT INTO t3 SELECT id,source,source FROM t2; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +UPDATE t3 SET target=source; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column ``.`(temporary)`.`target` at row 1 +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); +SELECT @alter; +@alter +ALTER TABLE t3 MODIFY target inet6 +EXECUTE IMMEDIATE @alter; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +DROP TABLE t3; +DROP TABLE t2; +CREATE PROCEDURE p1() +BEGIN +DECLARE src inet6 DEFAULT NULL; +DECLARE dst double DEFAULT NULL; +SET dst=src; +END; +$$ +CALL p1; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `dst` +DROP PROCEDURE p1; +CREATE FUNCTION f1(a double) RETURNS INT RETURN NULL;; +SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); +f1((SELECT source FROM t1 ORDER BY source LIMIT 1)) +NULL +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `a` +DROP FUNCTION f1; +CREATE PROCEDURE p1(a double) BEGIN END;; +CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `a` +DROP PROCEDURE p1; +CREATE PROCEDURE p1(OUT dst double) +BEGIN +DECLARE src inet6 DEFAULT NULL; +SET dst=src; +END; +$$ +CREATE PROCEDURE p2() +BEGIN +DECLARE dst double DEFAULT NULL; +CALL p1(dst); +END; +$$ +CALL p2(); +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `dst` +SHOW WARNINGS; +Level Code Message +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `dst` +DROP PROCEDURE p2; +DROP PROCEDURE p1; +CREATE FUNCTION f1() RETURNS double +BEGIN +DECLARE rc inet6 DEFAULT NULL; +RETURN rc; +END; +$$ +SELECT f1(); +f1() +NULL +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `f1()` +DROP FUNCTION f1; +CREATE PROCEDURE p1() +BEGIN +DECLARE src inet6 DEFAULT NULL; +DECLARE cur1 CURSOR(t double) FOR SELECT * FROM t1 WHERE target=t; +OPEN cur1(src); +CLOSE cur1; +END; +$$ +CALL p1(); +DROP PROCEDURE p1; +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES (); +CREATE PROCEDURE p1() +BEGIN +DECLARE dst double DEFAULT NULL; +DECLARE cur2 CURSOR FOR SELECT source FROM t2 ORDER BY source LIMIT 1; +OPEN cur2; +FETCH cur2 INTO dst; +CLOSE cur2; +END; +$$ +CALL p1(); +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `dst` +Warning 1366 Incorrect double value: '::' for column ``.``.`dst` at row 1 +DROP PROCEDURE p1; +DROP TABLE t2; +# End of type_store_assignment_incompatible.inc +DROP TABLE t1; +# +# End of 10.7 tests +# diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double_loose.test b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double_loose.test new file mode 100644 index 00000000000..2721d9c5643 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double_loose.test @@ -0,0 +1,21 @@ +--echo # +--echo # Start of 10.7 tests +--echo # + +--echo # +--echo # MDEV-28918 Implicit cast from INET6 UNSIGNED works differently on UPDATE vs ALTER +--echo # + +SET sql_mode=''; + +CREATE TABLE t1 (target INET6 DEFAULT '::0', source DOUBLE DEFAULT 0); +--source include/type_mix_incompatible.inc +DROP TABLE t1; + +CREATE TABLE t1 (target DOUBLE DEFAULT 0, source INET6 DEFAULT '::0'); +--source include/type_mix_incompatible.inc +DROP TABLE t1; + +--echo # +--echo # End of 10.7 tests +--echo # diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double_strict_ignore.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double_strict_ignore.result new file mode 100644 index 00000000000..9b76306d510 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double_strict_ignore.result @@ -0,0 +1,400 @@ +# +# Start of 10.7 tests +# +# +# MDEV-28918 Implicit cast from INET6 UNSIGNED works differently on UPDATE vs ALTER +# +SET @ignore='IGNORE'; +CREATE TABLE t1 (target INET6 DEFAULT '::0', source DOUBLE DEFAULT 0); +# Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; +SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS +WHERE COLUMN_NAME='source' + AND TABLE_NAME='t1' + AND TABLE_SCHEMA='test'); +SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS +WHERE COLUMN_NAME='target' + AND TABLE_NAME='t1' + AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; +CREATE TABLE t2 LIKE t1; +ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; +INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) NOT NULL, + `target` inet6 DEFAULT '::', + `source` double DEFAULT 0, + PRIMARY KEY (`id`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +CREATE TABLE t3 LIKE t2; +INSERT IGNORE INTO t3 VALUES +(1, +(SELECT source FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)); +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +DROP TABLE t3; +SET sql_mode=''; +CREATE TABLE t3 LIKE t2; +ALTER TABLE t3 ENGINE=MyISAM; +EXECUTE IMMEDIATE +CONCAT('CREATE VIEW v3 AS SELECT id,', +IF(@target_type='geometry','AsText(target)','target'), ' AS target,', +IF(@source_type='geometry','AsText(source)','source'), ' AS source ', +' FROM t3'); +INSERT IGNORE INTO t3 VALUES +(1, +(SELECT target FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)), +(2, +(SELECT source FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)); +Warnings: +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 2 +SELECT * FROM v3; +id target source +1 :: 0 +2 :: 0 +TRUNCATE TABLE t3; +SET sql_mode=STRICT_ALL_TABLES; +INSERT IGNORE INTO t3 VALUES +(1, +(SELECT target FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)), +(2, +(SELECT source FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)); +Warnings: +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 2 +SELECT * FROM v3; +id target source +1 :: 0 +2 :: 0 +TRUNCATE TABLE t3; +SET sql_mode=@sql_mode_save; +DROP TABLE t3; +DROP VIEW v3; +CREATE TABLE t3 LIKE t2; +INSERT IGNORE INTO t3 SELECT id,source,source FROM t2; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +INSERT IGNORE INTO t3 (id,target,source) SELECT id,source,source FROM t2; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +Warning 1062 Duplicate entry '1' for key 'PRIMARY' +INSERT IGNORE INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +INSERT IGNORE INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +INSERT IGNORE INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +INSERT IGNORE INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +UPDATE IGNORE t3 SET target=source; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +UPDATE IGNORE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column ``.`(temporary)`.`target` at row 1 +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); +SELECT @alter; +@alter +ALTER IGNORE TABLE t3 MODIFY target double +EXECUTE IMMEDIATE @alter; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +DROP TABLE t3; +DROP TABLE t2; +CREATE PROCEDURE p1() +BEGIN +DECLARE src double DEFAULT NULL; +DECLARE dst inet6 DEFAULT NULL; +SET dst=src; +END; +$$ +CALL p1; +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `dst` +DROP PROCEDURE p1; +CREATE FUNCTION f1(a inet6) RETURNS INT RETURN NULL;; +SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `a` +DROP FUNCTION f1; +CREATE PROCEDURE p1(a inet6) BEGIN END;; +CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `a` +DROP PROCEDURE p1; +CREATE PROCEDURE p1(OUT dst inet6) +BEGIN +DECLARE src double DEFAULT NULL; +SET dst=src; +END; +$$ +CREATE PROCEDURE p2() +BEGIN +DECLARE dst inet6 DEFAULT NULL; +CALL p1(dst); +END; +$$ +CALL p2(); +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `dst` +SHOW WARNINGS; +Level Code Message +Error 4078 Cannot cast 'double' as 'inet6' in assignment of `dst` +Note 4094 At line 4 in test.p1 +Note 4094 At line 4 in test.p2 +DROP PROCEDURE p2; +DROP PROCEDURE p1; +CREATE FUNCTION f1() RETURNS inet6 +BEGIN +DECLARE rc double DEFAULT NULL; +RETURN rc; +END; +$$ +SELECT f1(); +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `f1()` +DROP FUNCTION f1; +CREATE PROCEDURE p1() +BEGIN +DECLARE src double DEFAULT NULL; +DECLARE cur1 CURSOR(t inet6) FOR SELECT * FROM t1 WHERE target=t; +OPEN cur1(src); +CLOSE cur1; +END; +$$ +CALL p1(); +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `t` +DROP PROCEDURE p1; +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES (); +CREATE PROCEDURE p1() +BEGIN +DECLARE dst inet6 DEFAULT NULL; +DECLARE cur2 CURSOR FOR SELECT source FROM t2 ORDER BY source LIMIT 1; +OPEN cur2; +FETCH cur2 INTO dst; +CLOSE cur2; +END; +$$ +CALL p1(); +ERROR HY000: Cannot cast 'double' as 'inet6' in assignment of `dst` +DROP PROCEDURE p1; +DROP TABLE t2; +# End of type_store_assignment_incompatible.inc +DROP TABLE t1; +CREATE TABLE t1 (target DOUBLE DEFAULT 0, source INET6 DEFAULT '::0'); +# Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; +SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS +WHERE COLUMN_NAME='source' + AND TABLE_NAME='t1' + AND TABLE_SCHEMA='test'); +SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS +WHERE COLUMN_NAME='target' + AND TABLE_NAME='t1' + AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; +CREATE TABLE t2 LIKE t1; +ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; +INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) NOT NULL, + `target` double DEFAULT 0, + `source` inet6 DEFAULT '::', + PRIMARY KEY (`id`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +CREATE TABLE t3 LIKE t2; +INSERT IGNORE INTO t3 VALUES +(1, +(SELECT source FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)); +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +DROP TABLE t3; +SET sql_mode=''; +CREATE TABLE t3 LIKE t2; +ALTER TABLE t3 ENGINE=MyISAM; +EXECUTE IMMEDIATE +CONCAT('CREATE VIEW v3 AS SELECT id,', +IF(@target_type='geometry','AsText(target)','target'), ' AS target,', +IF(@source_type='geometry','AsText(source)','source'), ' AS source ', +' FROM t3'); +INSERT IGNORE INTO t3 VALUES +(1, +(SELECT target FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)), +(2, +(SELECT source FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)); +Warnings: +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 2 +SELECT * FROM v3; +id target source +1 0 :: +2 0 :: +TRUNCATE TABLE t3; +SET sql_mode=STRICT_ALL_TABLES; +INSERT IGNORE INTO t3 VALUES +(1, +(SELECT target FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)), +(2, +(SELECT source FROM t2 ORDER BY id LIMIT 1), +(SELECT source FROM t2 ORDER BY id LIMIT 1)); +Warnings: +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 2 +SELECT * FROM v3; +id target source +1 0 :: +2 0 :: +TRUNCATE TABLE t3; +SET sql_mode=@sql_mode_save; +DROP TABLE t3; +DROP VIEW v3; +CREATE TABLE t3 LIKE t2; +INSERT IGNORE INTO t3 SELECT id,source,source FROM t2; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +INSERT IGNORE INTO t3 (id,target,source) SELECT id,source,source FROM t2; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +Warning 1062 Duplicate entry '1' for key 'PRIMARY' +INSERT IGNORE INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +INSERT IGNORE INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +INSERT IGNORE INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +INSERT IGNORE INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +UPDATE IGNORE t3 SET target=source; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column `test`.`t3`.`target` at row 1 +UPDATE IGNORE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; +Warnings: +Warning 4078 Cannot cast 'inet6' as 'double' in assignment of `test`.`t3`.`target` +Warning 1366 Incorrect double value: '::' for column ``.`(temporary)`.`target` at row 1 +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); +SELECT @alter; +@alter +ALTER IGNORE TABLE t3 MODIFY target inet6 +EXECUTE IMMEDIATE @alter; +Warnings: +Warning 4078 Cannot cast 'double' as 'inet6' in assignment of `test`.`t3`.`target` +Warning 1292 Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 1 +DROP TABLE t3; +DROP TABLE t2; +CREATE PROCEDURE p1() +BEGIN +DECLARE src inet6 DEFAULT NULL; +DECLARE dst double DEFAULT NULL; +SET dst=src; +END; +$$ +CALL p1; +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `dst` +DROP PROCEDURE p1; +CREATE FUNCTION f1(a double) RETURNS INT RETURN NULL;; +SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `a` +DROP FUNCTION f1; +CREATE PROCEDURE p1(a double) BEGIN END;; +CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `a` +DROP PROCEDURE p1; +CREATE PROCEDURE p1(OUT dst double) +BEGIN +DECLARE src inet6 DEFAULT NULL; +SET dst=src; +END; +$$ +CREATE PROCEDURE p2() +BEGIN +DECLARE dst double DEFAULT NULL; +CALL p1(dst); +END; +$$ +CALL p2(); +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `dst` +SHOW WARNINGS; +Level Code Message +Error 4078 Cannot cast 'inet6' as 'double' in assignment of `dst` +Note 4094 At line 4 in test.p1 +Note 4094 At line 4 in test.p2 +DROP PROCEDURE p2; +DROP PROCEDURE p1; +CREATE FUNCTION f1() RETURNS double +BEGIN +DECLARE rc inet6 DEFAULT NULL; +RETURN rc; +END; +$$ +SELECT f1(); +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `f1()` +DROP FUNCTION f1; +CREATE PROCEDURE p1() +BEGIN +DECLARE src inet6 DEFAULT NULL; +DECLARE cur1 CURSOR(t double) FOR SELECT * FROM t1 WHERE target=t; +OPEN cur1(src); +CLOSE cur1; +END; +$$ +CALL p1(); +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `t` +DROP PROCEDURE p1; +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES (); +CREATE PROCEDURE p1() +BEGIN +DECLARE dst double DEFAULT NULL; +DECLARE cur2 CURSOR FOR SELECT source FROM t2 ORDER BY source LIMIT 1; +OPEN cur2; +FETCH cur2 INTO dst; +CLOSE cur2; +END; +$$ +CALL p1(); +ERROR HY000: Cannot cast 'inet6' as 'double' in assignment of `dst` +DROP PROCEDURE p1; +DROP TABLE t2; +# End of type_store_assignment_incompatible.inc +DROP TABLE t1; +# +# End of 10.7 tests +# diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double_strict_ignore.test b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double_strict_ignore.test new file mode 100644 index 00000000000..ebb41854df2 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_double_strict_ignore.test @@ -0,0 +1,21 @@ +--echo # +--echo # Start of 10.7 tests +--echo # + +--echo # +--echo # MDEV-28918 Implicit cast from INET6 UNSIGNED works differently on UPDATE vs ALTER +--echo # + +SET @ignore='IGNORE'; + +CREATE TABLE t1 (target INET6 DEFAULT '::0', source DOUBLE DEFAULT 0); +--source include/type_mix_incompatible.inc +DROP TABLE t1; + +CREATE TABLE t1 (target DOUBLE DEFAULT 0, source INET6 DEFAULT '::0'); +--source include/type_mix_incompatible.inc +DROP TABLE t1; + +--echo # +--echo # End of 10.7 tests +--echo # diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_int.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_int.result index 80a2e70cd7a..dd078efc38f 100644 --- a/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_int.result +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_int.result @@ -6,6 +6,7 @@ # CREATE TABLE t1 (target INET6 DEFAULT '::0', source INT DEFAULT 0); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -14,6 +15,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -30,7 +35,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -62,37 +67,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 :: 0 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target int(11) EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -103,15 +108,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a inet6) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a inet6) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst inet6) BEGIN @@ -126,10 +131,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types inet6 and int for operation 'SET' +Error 4078 Cannot cast 'int' as 'inet6' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -141,7 +146,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -152,7 +157,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -166,13 +171,14 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc DROP TABLE t1; CREATE TABLE t1 (target INT DEFAULT 0, source INET6 DEFAULT '::0'); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -181,6 +187,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -197,7 +207,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -229,37 +239,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect integer value: '::' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 0 :: TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target inet6 EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -270,15 +280,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a int(11)) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a int(11)) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst int(11)) BEGIN @@ -293,10 +303,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types int and inet6 for operation 'SET' +Error 4078 Cannot cast 'inet6' as 'int' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -308,7 +318,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -319,7 +329,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -333,7 +343,7 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types int and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_time.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_time.result index ffca3fb441f..afdbd9e1999 100644 --- a/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_time.result +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_time.result @@ -6,6 +6,7 @@ # CREATE TABLE t1 (target INET6 DEFAULT '::0', source TIME DEFAULT '00:00:00'); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -14,6 +15,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -30,7 +35,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -62,37 +67,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect inet6 value: '00:00:00' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 :: 00:00:00 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target time EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -103,15 +108,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a inet6) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a inet6) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst inet6) BEGIN @@ -126,10 +131,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types inet6 and time for operation 'SET' +Error 4078 Cannot cast 'time' as 'inet6' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -141,7 +146,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -152,7 +157,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -166,13 +171,14 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc DROP TABLE t1; CREATE TABLE t1 (target TIME DEFAULT '00:00:00', source INET6 DEFAULT '::0'); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -181,6 +187,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -197,7 +207,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -229,37 +239,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect time value: '::' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 00:00:00 :: TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target inet6 EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types inet6 and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'inet6' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -270,15 +280,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a time) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a time) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst time) BEGIN @@ -293,10 +303,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types time and inet6 for operation 'SET' +Error 4078 Cannot cast 'inet6' as 'time' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -308,7 +318,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -319,7 +329,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -333,7 +343,7 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types time and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'time' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_uint.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_uint.result index 7fa4df73dab..88439222de2 100644 --- a/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_uint.result +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_mix_uint.result @@ -6,6 +6,7 @@ # CREATE TABLE t1 (target INET6 DEFAULT '::0', source INT UNSIGNED DEFAULT 0); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -14,6 +15,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -30,7 +35,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -62,37 +67,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect inet6 value: '0' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 :: 0 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target int(10) unsigned EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -103,15 +108,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a inet6) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a inet6) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst inet6) BEGIN @@ -126,10 +131,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types inet6 and int unsigned for operation 'SET' +Error 4078 Cannot cast 'int unsigned' as 'inet6' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -141,7 +146,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -152,7 +157,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -166,13 +171,14 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc DROP TABLE t1; CREATE TABLE t1 (target INT UNSIGNED DEFAULT 0, source INET6 DEFAULT '::0'); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -181,6 +187,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -197,7 +207,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -229,37 +239,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect integer value: '::' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 0 :: TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target inet6 EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types inet6 and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'inet6' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -270,15 +280,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a int(10) unsigned) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a int(10) unsigned) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst int(10) unsigned) BEGIN @@ -293,10 +303,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types int unsigned and inet6 for operation 'SET' +Error 4078 Cannot cast 'inet6' as 'int unsigned' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -308,7 +318,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -319,7 +329,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -333,7 +343,7 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types int unsigned and inet6 for operation 'SET' +ERROR HY000: Cannot cast 'inet6' as 'int unsigned' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid.result index adcafc51ece..2a96b2df592 100644 --- a/plugin/type_uuid/mysql-test/type_uuid/type_uuid.result +++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid.result @@ -195,9 +195,9 @@ CREATE TABLE t1 (a UUID); INSERT INTO t1 VALUES ('x'); ERROR 22007: Incorrect uuid value: 'x' for column `test`.`t1`.`a` at row 1 INSERT INTO t1 VALUES (1); -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `test`.`t1`.`a` INSERT INTO t1 VALUES (TIME'10:20:30'); -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `test`.`t1`.`a` INSERT INTO t1 VALUES (0x00); ERROR 22007: Incorrect uuid value: '\x00' for column `test`.`t1`.`a` at row 1 DROP TABLE t1; @@ -1931,15 +1931,15 @@ DROP TABLE t1; # CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID)); -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `test`.`t1`.`a` DROP TABLE t1; CREATE TABLE t1 (a DOUBLE); INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID)); -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `test`.`t1`.`a` DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(32,0)); INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID)); -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `test`.`t1`.`a` DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(64)); INSERT INTO t1 VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID)); @@ -2762,7 +2762,7 @@ DROP TABLE t1; CREATE TABLE t1 (a UUID, b INT); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -2770,7 +2770,7 @@ DROP TABLE t1; CREATE TABLE t1 (a UUID, b DOUBLE); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -2778,7 +2778,7 @@ DROP TABLE t1; CREATE TABLE t1 (a UUID, b DECIMAL(32,0)); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -2786,7 +2786,7 @@ DROP TABLE t1; CREATE TABLE t1 (a UUID, b YEAR); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types year and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'year' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -2797,7 +2797,7 @@ DROP TABLE t1; CREATE TABLE t1 (a INT, b UUID); INSERT INTO t1 VALUES (1, NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -2805,7 +2805,7 @@ DROP TABLE t1; CREATE TABLE t1 (a DOUBLE, b UUID); INSERT INTO t1 VALUES (1, NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -2813,7 +2813,7 @@ DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(32,0), b UUID); INSERT INTO t1 VALUES (1, NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -2821,7 +2821,7 @@ DROP TABLE t1; CREATE TABLE t1 (a YEAR, b UUID); INSERT INTO t1 VALUES (1, NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types uuid and year for operation 'SET' +ERROR HY000: Cannot cast 'year' as 'uuid' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -2832,7 +2832,7 @@ DROP TABLE t1; CREATE TABLE t1 (a UUID, b TIME); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -2840,7 +2840,7 @@ DROP TABLE t1; CREATE TABLE t1 (a UUID, b DATE); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types date and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'date' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -2848,7 +2848,7 @@ DROP TABLE t1; CREATE TABLE t1 (a UUID, b DATETIME); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types datetime and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'datetime' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -2856,7 +2856,7 @@ DROP TABLE t1; CREATE TABLE t1 (a UUID, b TIMESTAMP NULL DEFAULT NULL); INSERT INTO t1 VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types timestamp and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'timestamp' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -2867,7 +2867,7 @@ DROP TABLE t1; CREATE TABLE t1 (a TIME, b UUID); INSERT INTO t1 VALUES ('00:00:00', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -2875,7 +2875,7 @@ DROP TABLE t1; CREATE TABLE t1 (a DATE, b UUID); INSERT INTO t1 VALUES ('2001-01:01', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types uuid and date for operation 'SET' +ERROR HY000: Cannot cast 'date' as 'uuid' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -2883,7 +2883,7 @@ DROP TABLE t1; CREATE TABLE t1 (a DATETIME, b UUID); INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types uuid and datetime for operation 'SET' +ERROR HY000: Cannot cast 'datetime' as 'uuid' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL @@ -2891,7 +2891,7 @@ DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP, b UUID); INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL); UPDATE t1 SET b=a; -ERROR HY000: Illegal parameter data types uuid and timestamp for operation 'SET' +ERROR HY000: Cannot cast 'timestamp' as 'uuid' in assignment of `test`.`t1`.`b` SELECT b FROM t1; b NULL diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_decimal.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_decimal.result index 91519b1e677..4eae75fbfc3 100644 --- a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_decimal.result +++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_decimal.result @@ -6,6 +6,7 @@ # CREATE TABLE t1 (target UUID DEFAULT '00000000-0000-0000-0000-000000000000', source DECIMAL(38,0) DEFAULT 0); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -14,6 +15,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -30,7 +35,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -62,37 +67,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect uuid value: '0' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 00000000-0000-0000-0000-000000000000 0 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target decimal(38,0) EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -103,15 +108,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a uuid) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a uuid) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst uuid) BEGIN @@ -126,10 +131,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types uuid and decimal for operation 'SET' +Error 4078 Cannot cast 'decimal' as 'uuid' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -141,7 +146,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -152,7 +157,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -166,13 +171,14 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc DROP TABLE t1; CREATE TABLE t1 (target DECIMAL(38,0) DEFAULT 0, source UUID DEFAULT '00000000-0000-0000-0000-000000000000'); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -181,6 +187,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -197,7 +207,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -229,37 +239,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 01000: Data truncated for column 'target' at row 2 SELECT * FROM v3; id target source 1 0 00000000-0000-0000-0000-000000000000 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target uuid EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types uuid and decimal for operation 'SET' +ERROR HY000: Cannot cast 'decimal' as 'uuid' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -270,15 +280,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a decimal(38,0)) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a decimal(38,0)) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst decimal(38,0)) BEGIN @@ -293,10 +303,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types decimal and uuid for operation 'SET' +Error 4078 Cannot cast 'uuid' as 'decimal' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -308,7 +318,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -319,7 +329,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -333,7 +343,7 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types decimal and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'decimal' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_double.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_double.result index 2f045078eb7..60f1a1a09ae 100644 --- a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_double.result +++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_double.result @@ -6,6 +6,7 @@ # CREATE TABLE t1 (target UUID DEFAULT '00000000-0000-0000-0000-000000000000', source DOUBLE DEFAULT 0); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -14,6 +15,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -30,7 +35,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -62,37 +67,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect uuid value: '0' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 00000000-0000-0000-0000-000000000000 0 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target double EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -103,15 +108,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a uuid) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a uuid) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst uuid) BEGIN @@ -126,10 +131,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types uuid and double for operation 'SET' +Error 4078 Cannot cast 'double' as 'uuid' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -141,7 +146,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -152,7 +157,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -166,13 +171,14 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc DROP TABLE t1; CREATE TABLE t1 (target DOUBLE DEFAULT 0, source UUID DEFAULT '00000000-0000-0000-0000-000000000000'); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -181,6 +187,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -197,7 +207,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -229,37 +239,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 01000: Data truncated for column 'target' at row 2 SELECT * FROM v3; id target source 1 0 00000000-0000-0000-0000-000000000000 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target uuid EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types uuid and double for operation 'SET' +ERROR HY000: Cannot cast 'double' as 'uuid' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -270,15 +280,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a double) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a double) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst double) BEGIN @@ -293,10 +303,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types double and uuid for operation 'SET' +Error 4078 Cannot cast 'uuid' as 'double' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -308,7 +318,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -319,7 +329,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -333,7 +343,7 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types double and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'double' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_int.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_int.result index 4eb2b803a98..cb3f4c282e3 100644 --- a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_int.result +++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_int.result @@ -6,6 +6,7 @@ # CREATE TABLE t1 (target UUID DEFAULT '00000000-0000-0000-0000-000000000000', source INT DEFAULT 0); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -14,6 +15,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -30,7 +35,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -62,37 +67,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect uuid value: '0' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 00000000-0000-0000-0000-000000000000 0 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target int(11) EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -103,15 +108,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a uuid) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a uuid) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst uuid) BEGIN @@ -126,10 +131,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types uuid and int for operation 'SET' +Error 4078 Cannot cast 'int' as 'uuid' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -141,7 +146,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -152,7 +157,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -166,13 +171,14 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc DROP TABLE t1; CREATE TABLE t1 (target INT DEFAULT 0, source UUID DEFAULT '00000000-0000-0000-0000-000000000000'); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -181,6 +187,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -197,7 +207,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -229,37 +239,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 01000: Data truncated for column 'target' at row 2 SELECT * FROM v3; id target source 1 0 00000000-0000-0000-0000-000000000000 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target uuid EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types uuid and int for operation 'SET' +ERROR HY000: Cannot cast 'int' as 'uuid' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -270,15 +280,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a int(11)) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a int(11)) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst int(11)) BEGIN @@ -293,10 +303,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types int and uuid for operation 'SET' +Error 4078 Cannot cast 'uuid' as 'int' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -308,7 +318,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -319,7 +329,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -333,7 +343,7 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types int and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_time.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_time.result index bb41af1ebf0..c86d46085e9 100644 --- a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_time.result +++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_time.result @@ -6,6 +6,7 @@ # CREATE TABLE t1 (target UUID DEFAULT '00000000-0000-0000-0000-000000000000', source TIME DEFAULT '00:00:00'); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -14,6 +15,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -30,7 +35,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -62,37 +67,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect uuid value: '00:00:00' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 00000000-0000-0000-0000-000000000000 00:00:00 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target time EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -103,15 +108,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a uuid) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a uuid) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst uuid) BEGIN @@ -126,10 +131,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types uuid and time for operation 'SET' +Error 4078 Cannot cast 'time' as 'uuid' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -141,7 +146,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -152,7 +157,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -166,13 +171,14 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc DROP TABLE t1; CREATE TABLE t1 (target TIME DEFAULT '00:00:00', source UUID DEFAULT '00000000-0000-0000-0000-000000000000'); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -181,6 +187,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -197,7 +207,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -229,37 +239,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect time value: '00000000-0000-0000-0000-000000000000' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 00:00:00 00000000-0000-0000-0000-000000000000 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target uuid EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types uuid and time for operation 'SET' +ERROR HY000: Cannot cast 'time' as 'uuid' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -270,15 +280,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a time) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a time) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst time) BEGIN @@ -293,10 +303,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types time and uuid for operation 'SET' +Error 4078 Cannot cast 'uuid' as 'time' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -308,7 +318,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -319,7 +329,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -333,7 +343,7 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types time and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'time' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_uint.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_uint.result index 9c61cfb1171..5202cd83865 100644 --- a/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_uint.result +++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid_mix_uint.result @@ -6,6 +6,7 @@ # CREATE TABLE t1 (target UUID DEFAULT '00000000-0000-0000-0000-000000000000', source INT UNSIGNED DEFAULT 0); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -14,6 +15,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -30,7 +35,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -62,37 +67,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 22007: Incorrect uuid value: '0' for column `test`.`t3`.`target` at row 2 SELECT * FROM v3; id target source 1 00000000-0000-0000-0000-000000000000 0 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target int(10) unsigned EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -103,15 +108,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a uuid) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a uuid) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst uuid) BEGIN @@ -126,10 +131,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types uuid and int unsigned for operation 'SET' +Error 4078 Cannot cast 'int unsigned' as 'uuid' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -141,7 +146,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -152,7 +157,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -166,13 +171,14 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc DROP TABLE t1; CREATE TABLE t1 (target INT UNSIGNED DEFAULT 0, source UUID DEFAULT '00000000-0000-0000-0000-000000000000'); # Start of type_store_assignment_incompatible.inc +SET @sql_mode_save= @@sql_mode; SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='source' AND TABLE_NAME='t1' @@ -181,6 +187,10 @@ SET @target_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='target' AND TABLE_NAME='t1' AND TABLE_SCHEMA='test'); +SET @ignore= CASE WHEN @ignore IS NULL OR @ignore = '' THEN '' + WHEN @ignore NOT LIKE ' %' THEN CONCAT(' ',@ignore) +ELSE @ignore +END; CREATE TABLE t2 LIKE t1; ALTER TABLE t2 ADD id INT NOT NULL PRIMARY KEY FIRST; INSERT INTO t2 VALUES (1,DEFAULT,DEFAULT); @@ -197,7 +207,7 @@ INSERT INTO t3 VALUES (1, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `test`.`t3`.`target` DROP TABLE t3; SET sql_mode=''; CREATE TABLE t3 LIKE t2; @@ -229,37 +239,37 @@ INSERT INTO t3 VALUES (2, (SELECT source FROM t2 ORDER BY id LIMIT 1), (SELECT source FROM t2 ORDER BY id LIMIT 1)); -Got one of the listed errors +ERROR 01000: Data truncated for column 'target' at row 2 SELECT * FROM v3; id target source 1 0 00000000-0000-0000-0000-000000000000 TRUNCATE TABLE t3; -SET sql_mode=DEFAULT; +SET sql_mode=@sql_mode_save; DROP TABLE t3; DROP VIEW v3; CREATE TABLE t3 LIKE t2; INSERT INTO t3 SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `test`.`t3`.`target` INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `test`.`t3`.`target` INSERT INTO t3 SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `test`.`t3`.`target` INSERT INTO t3 (id,target,source) SELECT 1,DEFAULT(t2.target),DEFAULT(t2.source) FROM t2 ON DUPLICATE KEY UPDATE t3.target=t2.source; -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `test`.`t3`.`target` UPDATE t3 SET target=source; -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `test`.`t3`.`target` UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' -SET @alter=CONCAT('ALTER TABLE t3 MODIFY target ', @source_type); +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `test`.`t3`.`target` +SET @alter=CONCAT('ALTER', @ignore, ' TABLE t3 MODIFY target ', @source_type); SELECT @alter; @alter ALTER TABLE t3 MODIFY target uuid EXECUTE IMMEDIATE @alter; -ERROR HY000: Illegal parameter data types uuid and int unsigned for operation 'SET' +ERROR HY000: Cannot cast 'int unsigned' as 'uuid' in assignment of `test`.`t3`.`target` DROP TABLE t3; DROP TABLE t2; CREATE PROCEDURE p1() @@ -270,15 +280,15 @@ SET dst=src; END; $$ CALL p1; -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `dst` DROP PROCEDURE p1; CREATE FUNCTION f1(a int(10) unsigned) RETURNS INT RETURN NULL;; SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(a int(10) unsigned) BEGIN END;; CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `a` DROP PROCEDURE p1; CREATE PROCEDURE p1(OUT dst int(10) unsigned) BEGIN @@ -293,10 +303,10 @@ CALL p1(dst); END; $$ CALL p2(); -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `dst` SHOW WARNINGS; Level Code Message -Error 4078 Illegal parameter data types int unsigned and uuid for operation 'SET' +Error 4078 Cannot cast 'uuid' as 'int unsigned' in assignment of `dst` Note 4094 At line 4 in test.p1 Note 4094 At line 4 in test.p2 DROP PROCEDURE p2; @@ -308,7 +318,7 @@ RETURN rc; END; $$ SELECT f1(); -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `f1()` DROP FUNCTION f1; CREATE PROCEDURE p1() BEGIN @@ -319,7 +329,7 @@ CLOSE cur1; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `t` DROP PROCEDURE p1; CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (); @@ -333,7 +343,7 @@ CLOSE cur2; END; $$ CALL p1(); -ERROR HY000: Illegal parameter data types int unsigned and uuid for operation 'SET' +ERROR HY000: Cannot cast 'uuid' as 'int unsigned' in assignment of `dst` DROP PROCEDURE p1; DROP TABLE t2; # End of type_store_assignment_incompatible.inc diff --git a/sql/field.cc b/sql/field.cc index 249269a6b1d..225e34e7b9e 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -964,7 +964,8 @@ Type_handler::aggregate_for_result_traditional(const Type_handler *a, } -bool Field::check_assignability_from(const Type_handler *from) const +bool Field::check_assignability_from(const Type_handler *from, + bool ignore) const { /* Using type_handler_for_item_field() here to get the data type handler @@ -982,9 +983,26 @@ bool Field::check_assignability_from(const Type_handler *from) const type_handler_for_item_field()); if (th.aggregate_for_result(from->type_handler_for_item_field())) { - my_error(ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION, MYF(0), - type_handler()->name().ptr(), from->name().ptr(), "SET"); - return true; + bool error= !ignore && get_thd()->is_strict_mode(); + /* + Display fully qualified column name for table columns. + Display non-qualified names for other things, + e.g. SP variables, SP return values, SP and CURSOR parameters. + */ + if (table->s->db.str && table->s->table_name.str) + my_printf_error(ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION, + "Cannot cast '%s' as '%s' in assignment of %`s.%`s.%`s", + MYF(error ? 0 : ME_WARNING), + from->name().ptr(), type_handler()->name().ptr(), + table->s->db.str, table->s->table_name.str, + field_name.str); + else + my_printf_error(ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION, + "Cannot cast '%s' as '%s' in assignment of %`s", + MYF(error ? 0 : ME_WARNING), + from->name().ptr(), type_handler()->name().ptr(), + field_name.str); + return error; } return false; } diff --git a/sql/field.h b/sql/field.h index 2198066d962..a485c56a9af 100644 --- a/sql/field.h +++ b/sql/field.h @@ -905,10 +905,10 @@ public: bool is_unsigned() const { return flags & UNSIGNED_FLAG; } - bool check_assignability_from(const Type_handler *from) const; - bool check_assignability_from(const Field *from) const + bool check_assignability_from(const Type_handler *from, bool ignore) const; + bool check_assignability_from(const Field *from, bool ignore) const { - return check_assignability_from(from->type_handler()); + return check_assignability_from(from->type_handler(), ignore); } /** diff --git a/sql/item.cc b/sql/item.cc index 0d6f6cda544..991825663c2 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -4509,13 +4509,13 @@ bool Item_param::is_evaluable_expression() const } -bool Item_param::check_assignability_to(const Field *to) const +bool Item_param::check_assignability_to(const Field *to, bool ignore) const { switch (state) { case SHORT_DATA_VALUE: case LONG_DATA_VALUE: case NULL_VALUE: - return to->check_assignability_from(type_handler()); + return to->check_assignability_from(type_handler(), ignore); case NO_VALUE: case IGNORE_VALUE: case DEFAULT_VALUE: diff --git a/sql/item.h b/sql/item.h index 117a252c025..c720ab47a92 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1838,14 +1838,14 @@ public: */ virtual bool is_evaluable_expression() const { return true; } - virtual bool check_assignability_to(const Field *to) const + virtual bool check_assignability_to(const Field *to, bool ignore) const { /* "this" must be neither DEFAULT/IGNORE, nor Item_param bound to DEFAULT/IGNORE. */ DBUG_ASSERT(is_evaluable_expression()); - return to->check_assignability_from(type_handler()); + return to->check_assignability_from(type_handler(), ignore); } /** @@ -4101,7 +4101,7 @@ class Item_param :public Item_basic_value, const String *value_query_val_str(THD *thd, String* str) const; Item *value_clone_item(THD *thd); bool is_evaluable_expression() const override; - bool check_assignability_to(const Field *field) const override; + bool check_assignability_to(const Field *field, bool ignore) const override; bool can_return_value() const; public: @@ -6782,7 +6782,7 @@ public: { str->append(STRING_WITH_LEN("default")); } - bool check_assignability_to(const Field *to) const override + bool check_assignability_to(const Field *to, bool ignore) const override { return false; } @@ -6819,7 +6819,7 @@ public: { str->append(STRING_WITH_LEN("ignore")); } - bool check_assignability_to(const Field *to) const override + bool check_assignability_to(const Field *to, bool ignore) const override { return false; } diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 3557e21d53b..340aca29c03 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -424,7 +424,7 @@ Item *THD::sp_fix_func_item_for_assignment(const Field *to, Item **it_addr) { DBUG_ENTER("THD::sp_fix_func_item_for_assignment"); Item *res= sp_fix_func_item(it_addr); - if (res && (!res->check_assignability_to(to))) + if (res && (!res->check_assignability_to(to, false))) DBUG_RETURN(res); DBUG_RETURN(NULL); } diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 9507aaf3ab7..9b60e072896 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -768,7 +768,7 @@ bool mysql_insert(THD *thd, TABLE_LIST *table_list, value_count= values->elements; if ((res= mysql_prepare_insert(thd, table_list, fields, values, - update_fields, update_values, duplic, + update_fields, update_values, duplic, ignore, &unused_conds, FALSE))) { retval= thd->is_error(); @@ -839,7 +839,8 @@ bool mysql_insert(THD *thd, TABLE_LIST *table_list, behaviour for non-transactional tables. */ if (values->elements && - table_list->table->check_assignability_opt_fields(fields, *values)) + table_list->table->check_assignability_opt_fields(fields, *values, + ignore)) goto abort; while ((values= its++)) @@ -1638,7 +1639,8 @@ static void prepare_for_positional_update(TABLE *table, TABLE_LIST *tables) int mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, List<Item> &fields, List_item *values, List<Item> &update_fields, List<Item> &update_values, - enum_duplicates duplic, COND **where, + enum_duplicates duplic, bool ignore, + COND **where, bool select_insert) { SELECT_LEX *select_lex= thd->lex->first_select_lex(); @@ -1713,7 +1715,8 @@ int mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, ON DUPLICATE KEY UPDATE col=expr [, col=expr]; */ TABLE::check_assignability_explicit_fields(update_fields, - update_values); + update_values, + ignore); select_lex->no_wrap_view_item= FALSE; } @@ -3814,7 +3817,7 @@ int mysql_insert_select_prepare(THD *thd, select_result *sel_res) if ((res= mysql_prepare_insert(thd, lex->query_tables, lex->field_list, 0, lex->update_list, lex->value_list, - lex->duplicates, + lex->duplicates, lex->ignore, &select_lex->where, TRUE))) DBUG_RETURN(res); @@ -3915,7 +3918,8 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u) INSERT INTO t1 (col1, col2) VALUES (expr1, expr2); INSERT INTO t1 SET col1=expr1, col2=expr2; */ - res= table_list->table->check_assignability_opt_fields(*fields, values); + res= table_list->table->check_assignability_opt_fields(*fields, values, + lex->ignore); } if (!res && fields->elements) @@ -3978,7 +3982,8 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u) ON DUPLICATE KEY UPDATE col=expr [, col=expr] */ TABLE::check_assignability_explicit_fields(*info.update_fields, - *info.update_values); + *info.update_values, + lex->ignore); if (!res) { /* diff --git a/sql/sql_insert.h b/sql/sql_insert.h index 80666a81c50..8b034c25877 100644 --- a/sql/sql_insert.h +++ b/sql/sql_insert.h @@ -27,6 +27,7 @@ int mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, List<Item> &fields, List_item *values, List<Item> &update_fields, List<Item> &update_values, enum_duplicates duplic, + bool ignore, COND **where, bool select_insert); bool mysql_insert(THD *thd,TABLE_LIST *table,List<Item> &fields, List<List_item> &values, List<Item> &update_fields, diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 8606bc10dbc..9631fdd331d 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -1288,7 +1288,8 @@ static bool mysql_test_insert_common(Prepared_statement *stmt, List<List_item> &values_list, List<Item> &update_fields, List<Item> &update_values, - enum_duplicates duplic) + enum_duplicates duplic, + bool ignore) { THD *thd= stmt->thd; List_iterator_fast<List_item> its(values_list); @@ -1324,7 +1325,8 @@ static bool mysql_test_insert_common(Prepared_statement *stmt, } if (mysql_prepare_insert(thd, table_list, fields, values, update_fields, - update_values, duplic, &unused_conds, FALSE)) + update_values, duplic, ignore, + &unused_conds, FALSE)) goto error; value_count= values->elements; @@ -1379,7 +1381,7 @@ static bool mysql_test_insert(Prepared_statement *stmt, List<List_item> &values_list, List<Item> &update_fields, List<Item> &update_values, - enum_duplicates duplic) + enum_duplicates duplic, bool ignore) { THD *thd= stmt->thd; @@ -1395,7 +1397,7 @@ static bool mysql_test_insert(Prepared_statement *stmt, } return mysql_test_insert_common(stmt, table_list, fields, values_list, - update_fields, update_values, duplic); + update_fields, update_values, duplic, ignore); } @@ -2473,14 +2475,14 @@ static bool check_prepared_statement(Prepared_statement *stmt) res= mysql_test_insert(stmt, tables, lex->field_list, lex->many_values, lex->update_list, lex->value_list, - lex->duplicates); + lex->duplicates, lex->ignore); break; case SQLCOM_LOAD: res= mysql_test_insert_common(stmt, tables, lex->field_list, lex->many_values, lex->update_list, lex->value_list, - lex->duplicates); + lex->duplicates, lex->ignore); break; case SQLCOM_UPDATE: diff --git a/sql/sql_table.cc b/sql/sql_table.cc index c685d8b2985..15b8bd22fdf 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -11084,7 +11084,7 @@ copy_data_between_tables(THD *thd, TABLE *from, TABLE *to, if (!(*ptr)->vcol_info) { bitmap_set_bit(from->read_set, def->field->field_index); - if ((*ptr)->check_assignability_from(def->field)) + if ((*ptr)->check_assignability_from(def->field, ignore)) goto err; (copy_end++)->set(*ptr,def->field,0); } diff --git a/sql/sql_update.cc b/sql/sql_update.cc index e8f10920504..eda1eb8ef9e 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -523,7 +523,8 @@ int mysql_update(THD *thd, DBUG_RETURN(1); /* purecov: inspected */ } - if (table_list->table->check_assignability_explicit_fields(fields, values)) + if (table_list->table->check_assignability_explicit_fields(fields, values, + ignore)) DBUG_RETURN(true); if (check_unique_table(thd, table_list)) @@ -2086,7 +2087,8 @@ int multi_update::prepare(List<Item> ¬_used_values, int error= setup_fields(thd, Ref_ptr_array(), *values, MARK_COLUMNS_READ, 0, NULL, 0) || - TABLE::check_assignability_explicit_fields(*fields, *values); + TABLE::check_assignability_explicit_fields(*fields, *values, + ignore); ti.rewind(); while ((table_ref= ti++)) diff --git a/sql/table.cc b/sql/table.cc index 98b487ba991..d6854ebd0f8 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -9248,7 +9248,8 @@ bool TABLE::validate_default_values_of_unset_fields(THD *thd) const INSERT INTO t1 (a,b) VALUES (1,2); */ bool TABLE::check_assignability_explicit_fields(List<Item> fields, - List<Item> values) + List<Item> values, + bool ignore) { DBUG_ENTER("TABLE::check_assignability_explicit_fields"); DBUG_ASSERT(fields.elements == values.elements); @@ -9268,7 +9269,7 @@ bool TABLE::check_assignability_explicit_fields(List<Item> fields, */ continue; } - if (value->check_assignability_to(item_field->field)) + if (value->check_assignability_to(item_field->field, ignore)) DBUG_RETURN(true); } DBUG_RETURN(false); @@ -9280,7 +9281,8 @@ bool TABLE::check_assignability_explicit_fields(List<Item> fields, all visible fields of the table, e.g. INSERT INTO t1 VALUES (1,2); */ -bool TABLE::check_assignability_all_visible_fields(List<Item> &values) const +bool TABLE::check_assignability_all_visible_fields(List<Item> &values, + bool ignore) const { DBUG_ENTER("TABLE::check_assignability_all_visible_fields"); DBUG_ASSERT(s->visible_fields == values.elements); @@ -9289,7 +9291,7 @@ bool TABLE::check_assignability_all_visible_fields(List<Item> &values) const for (uint i= 0; i < s->fields; i++) { if (!field[i]->invisible && - (vi++)->check_assignability_to(field[i])) + (vi++)->check_assignability_to(field[i], ignore)) DBUG_RETURN(true); } DBUG_RETURN(false); diff --git a/sql/table.h b/sql/table.h index ddb0b183cf2..bf0d4f530a0 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1688,9 +1688,11 @@ public: // Check if the value list is assignable to the explicit field list static bool check_assignability_explicit_fields(List<Item> fields, - List<Item> values); + List<Item> values, + bool ignore); // Check if the value list is assignable to all visible fields - bool check_assignability_all_visible_fields(List<Item> &values) const; + bool check_assignability_all_visible_fields(List<Item> &values, + bool ignore) const; /* Check if the value list is assignable to: - The explicit field list if fields.elements > 0, e.g. @@ -1699,12 +1701,13 @@ public: INSERT INTO t1 VALUES (1,2); */ bool check_assignability_opt_fields(List<Item> fields, - List<Item> values) const + List<Item> values, + bool ignore) const { DBUG_ASSERT(values.elements); return fields.elements ? - check_assignability_explicit_fields(fields, values) : - check_assignability_all_visible_fields(values); + check_assignability_explicit_fields(fields, values, ignore) : + check_assignability_all_visible_fields(values, ignore); } bool insert_all_rows_into_tmp_table(THD *thd, |