diff options
Diffstat (limited to 'plugin/type_inet/mysql-test/type_inet/type_inet4_mix_datetime.result')
-rw-r--r-- | plugin/type_inet/mysql-test/type_inet/type_inet4_mix_datetime.result | 353 |
1 files changed, 353 insertions, 0 deletions
diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet4_mix_datetime.result b/plugin/type_inet/mysql-test/type_inet/type_inet4_mix_datetime.result new file mode 100644 index 00000000..7c7194f6 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet4_mix_datetime.result @@ -0,0 +1,353 @@ +# +# Start of 10.10 tests +# +# +# MDEV-23287 The INET4 data type +# +CREATE TABLE t1 (target INET4 DEFAULT '99.99.99.99', source DATETIME DEFAULT '2001-01-01 10:20:30'); +# 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` inet4 DEFAULT '99.99.99.99', + `source` datetime DEFAULT '2001-01-01 10:20:30', + PRIMARY KEY (`id`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +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)); +ERROR HY000: Cannot cast 'datetime' as 'inet4' in assignment of `test`.`t3`.`target` +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 inet4 value: '2001-01-01 10:20:30' for column `test`.`t3`.`target` at row 2 +SELECT * FROM v3; +id target source +1 99.99.99.99 2001-01-01 10:20:30 +2 0.0.0.0 2001-01-01 10:20:30 +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 inet4 value: '2001-01-01 10:20:30' for column `test`.`t3`.`target` at row 2 +SELECT * FROM v3; +id target source +1 99.99.99.99 2001-01-01 10:20:30 +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; +ERROR HY000: Cannot cast 'datetime' as 'inet4' in assignment of `test`.`t3`.`target` +INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; +ERROR HY000: Cannot cast 'datetime' as 'inet4' in assignment of `test`.`t3`.`target` +INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; +ERROR HY000: Cannot cast 'datetime' as 'inet4' in assignment of `test`.`t3`.`target` +INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; +ERROR HY000: Cannot cast 'datetime' as 'inet4' 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: Cannot cast 'datetime' as 'inet4' 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: Cannot cast 'datetime' as 'inet4' in assignment of `test`.`t3`.`target` +UPDATE t3 SET target=source; +ERROR HY000: Cannot cast 'datetime' as 'inet4' in assignment of `test`.`t3`.`target` +UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; +ERROR HY000: Cannot cast 'datetime' as 'inet4' 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 datetime +EXECUTE IMMEDIATE @alter; +ERROR HY000: Cannot cast 'inet4' as 'datetime' in assignment of `test`.`t3`.`target` +DROP TABLE t3; +DROP TABLE t2; +CREATE PROCEDURE p1() +BEGIN +DECLARE src datetime DEFAULT NULL; +DECLARE dst inet4 DEFAULT NULL; +SET dst=src; +END; +$$ +CALL p1; +ERROR HY000: Cannot cast 'datetime' as 'inet4' in assignment of `dst` +DROP PROCEDURE p1; +CREATE FUNCTION f1(a inet4) RETURNS INT RETURN NULL;; +SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); +ERROR HY000: Cannot cast 'datetime' as 'inet4' in assignment of `a` +DROP FUNCTION f1; +CREATE PROCEDURE p1(a inet4) BEGIN END;; +CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); +ERROR HY000: Cannot cast 'datetime' as 'inet4' in assignment of `a` +DROP PROCEDURE p1; +CREATE PROCEDURE p1(OUT dst inet4) +BEGIN +DECLARE src datetime DEFAULT NULL; +SET dst=src; +END; +$$ +CREATE PROCEDURE p2() +BEGIN +DECLARE dst inet4 DEFAULT NULL; +CALL p1(dst); +END; +$$ +CALL p2(); +ERROR HY000: Cannot cast 'datetime' as 'inet4' in assignment of `dst` +SHOW WARNINGS; +Level Code Message +Error 4078 Cannot cast 'datetime' as 'inet4' 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 inet4 +BEGIN +DECLARE rc datetime DEFAULT NULL; +RETURN rc; +END; +$$ +SELECT f1(); +ERROR HY000: Cannot cast 'datetime' as 'inet4' in assignment of `f1()` +DROP FUNCTION f1; +CREATE PROCEDURE p1() +BEGIN +DECLARE src datetime DEFAULT NULL; +DECLARE cur1 CURSOR(t inet4) FOR SELECT * FROM t1 WHERE target=t; +OPEN cur1(src); +CLOSE cur1; +END; +$$ +CALL p1(); +ERROR HY000: Cannot cast 'datetime' as 'inet4' in assignment of `t` +DROP PROCEDURE p1; +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES (); +CREATE PROCEDURE p1() +BEGIN +DECLARE dst inet4 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 'datetime' as 'inet4' in assignment of `dst` +DROP PROCEDURE p1; +DROP TABLE t2; +# End of type_store_assignment_incompatible.inc +DROP TABLE t1; +CREATE TABLE t1 (target DATETIME DEFAULT '2001-01-01 10:20:30', source INET4 DEFAULT '99.99.99.99'); +# 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` datetime DEFAULT '2001-01-01 10:20:30', + `source` inet4 DEFAULT '99.99.99.99', + PRIMARY KEY (`id`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +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)); +ERROR HY000: Cannot cast 'inet4' as 'datetime' in assignment of `test`.`t3`.`target` +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 1265 Data truncated for column 'target' at row 2 +SELECT * FROM v3; +id target source +1 2001-01-01 10:20:30 99.99.99.99 +2 0000-00-00 00:00:00 99.99.99.99 +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 datetime value: '99.99.99.99' for column `test`.`t3`.`target` at row 2 +SELECT * FROM v3; +id target source +1 2001-01-01 10:20:30 99.99.99.99 +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; +ERROR HY000: Cannot cast 'inet4' as 'datetime' in assignment of `test`.`t3`.`target` +INSERT INTO t3 (id,target,source) SELECT id,source,source FROM t2; +ERROR HY000: Cannot cast 'inet4' as 'datetime' in assignment of `test`.`t3`.`target` +INSERT INTO t3 VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; +ERROR HY000: Cannot cast 'inet4' as 'datetime' in assignment of `test`.`t3`.`target` +INSERT INTO t3 (id,target,source) VALUES (1,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE target=source; +ERROR HY000: Cannot cast 'inet4' as 'datetime' 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: Cannot cast 'inet4' as 'datetime' 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: Cannot cast 'inet4' as 'datetime' in assignment of `test`.`t3`.`target` +UPDATE t3 SET target=source; +ERROR HY000: Cannot cast 'inet4' as 'datetime' in assignment of `test`.`t3`.`target` +UPDATE t2, t3 SET t3.target=t2.source WHERE t2.id=t3.id; +ERROR HY000: Cannot cast 'inet4' as 'datetime' 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 inet4 +EXECUTE IMMEDIATE @alter; +ERROR HY000: Cannot cast 'datetime' as 'inet4' in assignment of `test`.`t3`.`target` +DROP TABLE t3; +DROP TABLE t2; +CREATE PROCEDURE p1() +BEGIN +DECLARE src inet4 DEFAULT NULL; +DECLARE dst datetime DEFAULT NULL; +SET dst=src; +END; +$$ +CALL p1; +ERROR HY000: Cannot cast 'inet4' as 'datetime' in assignment of `dst` +DROP PROCEDURE p1; +CREATE FUNCTION f1(a datetime) RETURNS INT RETURN NULL;; +SELECT f1((SELECT source FROM t1 ORDER BY source LIMIT 1)); +ERROR HY000: Cannot cast 'inet4' as 'datetime' in assignment of `a` +DROP FUNCTION f1; +CREATE PROCEDURE p1(a datetime) BEGIN END;; +CALL p1((SELECT source FROM t1 ORDER BY source LIMIT 1)); +ERROR HY000: Cannot cast 'inet4' as 'datetime' in assignment of `a` +DROP PROCEDURE p1; +CREATE PROCEDURE p1(OUT dst datetime) +BEGIN +DECLARE src inet4 DEFAULT NULL; +SET dst=src; +END; +$$ +CREATE PROCEDURE p2() +BEGIN +DECLARE dst datetime DEFAULT NULL; +CALL p1(dst); +END; +$$ +CALL p2(); +ERROR HY000: Cannot cast 'inet4' as 'datetime' in assignment of `dst` +SHOW WARNINGS; +Level Code Message +Error 4078 Cannot cast 'inet4' as 'datetime' 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 datetime +BEGIN +DECLARE rc inet4 DEFAULT NULL; +RETURN rc; +END; +$$ +SELECT f1(); +ERROR HY000: Cannot cast 'inet4' as 'datetime' in assignment of `f1()` +DROP FUNCTION f1; +CREATE PROCEDURE p1() +BEGIN +DECLARE src inet4 DEFAULT NULL; +DECLARE cur1 CURSOR(t datetime) FOR SELECT * FROM t1 WHERE target=t; +OPEN cur1(src); +CLOSE cur1; +END; +$$ +CALL p1(); +ERROR HY000: Cannot cast 'inet4' as 'datetime' in assignment of `t` +DROP PROCEDURE p1; +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES (); +CREATE PROCEDURE p1() +BEGIN +DECLARE dst datetime 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 'inet4' as 'datetime' in assignment of `dst` +DROP PROCEDURE p1; +DROP TABLE t2; +# End of type_store_assignment_incompatible.inc +DROP TABLE t1; +# +# End of 10.10 tests +# |