diff options
Diffstat (limited to 'plugin/type_inet/mysql-test/type_inet/type_inet6.test')
| -rw-r--r-- | plugin/type_inet/mysql-test/type_inet/type_inet6.test | 1643 |
1 files changed, 1643 insertions, 0 deletions
diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6.test b/plugin/type_inet/mysql-test/type_inet/type_inet6.test new file mode 100644 index 00000000000..becc063ddc9 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6.test @@ -0,0 +1,1643 @@ + +--echo # +--echo # Basic CREATE functionality, defaults, metadata +--echo # + +--error ER_WRONG_FIELD_SPEC +CREATE TABLE t1 (a INET6 AUTO_INCREMENT); + +CREATE TABLE t1 (a INET6); +SHOW CREATE TABLE t1; +DESCRIBE t1; +--vertical_results +--replace_column 19 # +SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' AND table_name='t1'; +--horizontal_results +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::1'); +--enable_metadata +SELECT * FROM t1; +SELECT CAST('::' AS INET6) AS a; +--disable_metadata +DROP TABLE t1; + + +CREATE TABLE t1 ( + c1 INET6 DEFAULT 0x00000000000000000000000000000000, + c2 INET6 DEFAULT 0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF, + c3 INET6 DEFAULT '::', + c4 INET6 DEFAULT 'FFFF::ffff', + c5 INET6 DEFAULT CAST(X'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF' AS INET6) +); +SHOW CREATE TABLE t1; +DESCRIBE t1; +--vertical_results +--replace_column 19 # +SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' AND table_name='t1'; +--horizontal_results +DROP TABLE t1; + +--error ER_INVALID_DEFAULT +CREATE TABLE t1 (c1 INET6 DEFAULT 0x00); +--error ER_INVALID_DEFAULT +CREATE TABLE t1 (c1 INET6 DEFAULT ''); + + +CREATE TABLE t1 (a INET6); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES ('x'); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES (1); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES (TIME'10:20:30'); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES (0x00); +DROP TABLE t1; + +--echo # +--echo # CAST +--echo # + +SELECT CAST('garbage' AS INET6); +SELECT CAST(0x01 AS INET6); +SELECT CAST(REPEAT(0x00,16) AS INET6); +SELECT CAST(REPEAT(0x11,16) AS INET6); + +CREATE TABLE t1 AS SELECT CAST('::' AS INET6); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # +--echo # Text and binary formats, comparison operators +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES (0x00000000000000000000000000000000); +INSERT INTO t1 VALUES (0x00000000000000000000000000000001); +INSERT INTO t1 VALUES (0xFFFF0000000000000000000000000001); +INSERT INTO t1 VALUES (0xFFFF0000000000000000000000000002); +SELECT * FROM t1 ORDER BY a; +SELECT * FROM t1 ORDER BY a DESC; +SELECT HEX(a),a FROM t1 ORDER BY a; +SELECT * FROM t1 WHERE a='::'; +SELECT * FROM t1 WHERE a='::1'; +SELECT * FROM t1 WHERE a='ffff::1'; +SELECT * FROM t1 WHERE a='ffff::2'; +SELECT * FROM t1 WHERE a=0x00000000000000000000000000000000; +SELECT * FROM t1 WHERE a=0x00000000000000000000000000000001; +SELECT * FROM t1 WHERE a=0xffff0000000000000000000000000001; +SELECT * FROM t1 WHERE a=0xffff0000000000000000000000000002; +SELECT * FROM t1 WHERE a<'::'; +SELECT * FROM t1 WHERE a<='::'; +SELECT * FROM t1 WHERE a>='ffff::2'; +SELECT * FROM t1 WHERE a>'ffff::2'; +SELECT * FROM t1 WHERE a IN ('::', 'ffff::1') ORDER BY a; +SELECT * FROM t1 WHERE a IN ('::', 0xffff0000000000000000000000000002) ORDER BY a; + +SELECT * FROM t1 WHERE a<'garbage'; +SELECT * FROM t1 WHERE a<='garbage'; +SELECT * FROM t1 WHERE a='garbage'; +SELECT * FROM t1 WHERE a>='garbage'; +SELECT * FROM t1 WHERE a>'garbage'; + +SELECT * FROM t1 WHERE a<0x01; +SELECT * FROM t1 WHERE a<=0x01; +SELECT * FROM t1 WHERE a=0x01; +SELECT * FROM t1 WHERE a>=0x01; +SELECT * FROM t1 WHERE a>0x01; + +SELECT * FROM t1 WHERE a='0::0'; +SELECT * FROM t1 WHERE a='0::00'; +SELECT * FROM t1 WHERE a='0::000'; +SELECT * FROM t1 WHERE a='0::0000'; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a=0; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a=0.0; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a=0e0; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a=TIME'10:20:30'; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a IN ('::', 10); + +DROP TABLE t1; + +--echo # +--echo # cmp_item_inet6: IN for non-constants +--echo # + +CREATE TABLE t1 (a INET6, b INET6); +INSERT INTO t1 VALUES ('::1', '::2'); +SELECT * FROM t1 WHERE '::' IN (a, b); +SELECT * FROM t1 WHERE '::1' IN (a, b); +SELECT * FROM t1 WHERE '::01' IN (a, b); +SELECT * FROM t1 WHERE '00::01' IN (a, b); +DROP TABLE t1; + + +--echo # +--echo # cmp_item_inet6: DECODE_ORACLE +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES (NULL),('::01'),('::02'); +SELECT a, DECODE_ORACLE(a, '::01', '01') AS d FROM t1; +SELECT + a, + DECODE_ORACLE(a, '::01', '01') AS d0, + DECODE_ORACLE(a, NULL, '<NULL>', '::01', '01') AS d1, + DECODE_ORACLE(a, 'garbage', '<NULL>', '::01', '01') AS d2 +FROM t1; +DROP TABLE t1; + + +--echo # +--echo # CASE abbreviations +--echo # + +CREATE TABLE t1 ( + c INET6, + c_char CHAR(32), + c_varchar VARCHAR(32), + c_tinytext TINYTEXT, + c_text TEXT, + c_mediumtext TEXT, + c_longtext LONGTEXT +); +CREATE TABLE t2 AS SELECT + COALESCE(c, c_char), + COALESCE(c, c_varchar), + COALESCE(c, c_tinytext), + COALESCE(c, c_text), + COALESCE(c, c_mediumtext), + COALESCE(c, c_longtext) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +CREATE TABLE t2 AS SELECT + LEAST(c, c_char), + LEAST(c, c_varchar), + LEAST(c, c_tinytext), + LEAST(c, c_text), + LEAST(c, c_mediumtext), + LEAST(c, c_longtext) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES (NULL),('::1'),('::2'); +SELECT COALESCE(a, '::') FROM t1 ORDER BY a; +SELECT a, LEAST(a,'::0'), LEAST(a,'::f') FROM t1 ORDER BY a; +SELECT a, GREATEST(a,'::0'), GREATEST(a,'::f') FROM t1 ORDER BY a; + +CREATE TABLE t2 AS SELECT + COALESCE(a, '::'), + LEAST(a,'::'), + GREATEST(a,'::') +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +SELECT COALESCE(a, 0x00000000000000000000000000000000) FROM t1 ORDER BY a; +SELECT a, + LEAST(a, 0x00000000000000000000000000000000), + LEAST(a, 0x0000000000000000000000000000000f) +FROM t1 ORDER BY a; +SELECT a, + GREATEST(a, 0x00000000000000000000000000000000), + GREATEST(a, 0x0000000000000000000000000000000f) +FROM t1 ORDER BY a; + +CREATE TABLE t2 AS SELECT + COALESCE(a, 0x00000000000000000000000000000000), + LEAST(a,0x00000000000000000000000000000000), + GREATEST(a,0x00000000000000000000000000000000) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(a, 10) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(a, 10) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT GREATEST(a, 10) FROM t1; +DROP TABLE t1; + +SELECT COALESCE('garbage', CAST('::1' AS INET6)); +SELECT COALESCE(0x01, CAST('::1' AS INET6)); + + +--echo # +--echo # Uniqueness +--echo # + +CREATE TABLE t1 (a INET6 NOT NULL PRIMARY KEY); +INSERT INTO t1 VALUES ('41::1'),('61::1'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('41::1'); +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # Indexes +--echo # + +--error ER_WRONG_SUB_KEY +CREATE TABLE t1 (a INET6, KEY(a(1))); + + +--echo # +--echo # Explicit CAST on INSERT +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES (CAST('1::1' AS INET6)); +INSERT INTO t1 VALUES (CAST('1::2' AS INET6)); +INSERT INTO t1 VALUES (CAST('1::3' AS INET6)); +INSERT INTO t1 VALUES (CAST(CONCAT('2','::1') AS INET6)); +INSERT INTO t1 VALUES (CAST(CONCAT('2','::2') AS INET6)); +INSERT INTO t1 VALUES (CAST(CONCAT('2','::3') AS INET6)); +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; + + +--echo # +--echo # Explicit CAST and implicit CAST on ALTER +--echo # + +CREATE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES ('garbage'),('::'),('::1'),('ffff::1'),('ffff::2'); +SELECT a, CAST(a AS INET6) FROM t1 ORDER BY a; +SELECT a, CAST(a AS INET6) FROM t1 ORDER BY CAST(a AS INET6); +--error ER_TRUNCATED_WRONG_VALUE +ALTER TABLE t1 MODIFY a INET6; +SET sql_mode=''; +ALTER TABLE t1 MODIFY a INET6; +SET sql_mode=DEFAULT; +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; + + +CREATE TABLE t1 (a BINARY(16)); +INSERT INTO t1 VALUES (0x00000000000000000000000000000000); +INSERT INTO t1 VALUES (0x00000000000000000000000000000001); +INSERT INTO t1 VALUES (0xffff0000000000000000000000000001); +INSERT INTO t1 VALUES (0xffff0000000000000000000000000002); +SELECT HEX(a), CAST(a AS INET6) FROM t1 ORDER BY a; +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; + + +--echo # +--echo # INSERT..SELECT, same data types +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::1'),('::2'); +CREATE TABLE t2 (a INET6); +INSERT INTO t2 SELECT a FROM t1; +SELECT * FROM t2; +DROP TABLE t1,t2; + + +--echo # +--echo # Implicit CAST on INSERT..SELECT, text format +--echo # + +CREATE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES ('garbage'),('::'),('::1'),('ffff::1'),('ffff::2'); + +CREATE TABLE t2 (a INET6); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t2 SELECT a FROM t1; +SET sql_mode=''; +INSERT INTO t2 SELECT a FROM t1; +SELECT * FROM t2 ORDER BY a; +SET sql_mode=DEFAULT; +DROP TABLE t2; + +CREATE TABLE t2 (a INET6 NOT NULL); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t2 SELECT a FROM t1; +SET sql_mode=''; +INSERT INTO t2 SELECT a FROM t1; +SELECT * FROM t2 ORDER BY a; +SET sql_mode=DEFAULT; +DROP TABLE t2; + +DROP TABLE t1; + + +--echo # +--echo # Implicit CAST on INSERT..SELECT, binary format +--echo # + +CREATE TABLE t1 (a BINARY(16)); +INSERT INTO t1 VALUES (0x00000000000000000000000000000000); +INSERT INTO t1 VALUES (0x00000000000000000000000000000001); +INSERT INTO t1 VALUES (0xffff0000000000000000000000000001); +INSERT INTO t1 VALUES (0xffff0000000000000000000000000002); +CREATE TABLE t2 (a INET6); +INSERT INTO t2 SELECT a FROM t1; +SELECT a FROM t2 ORDER BY a; +DROP TABLE t1,t2; + + +--echo # +--echo # CAST to other data types +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS DOUBLE); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS FLOAT); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS DECIMAL); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS SIGNED); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS UNSIGNED); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS TIME); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS DATE); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS DATETIME); + +SELECT CAST(CAST('::' AS INET6) AS CHAR); +CREATE TABLE t1 AS SELECT CAST(CAST('::' AS INET6) AS CHAR) AS a; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('ffff::ffff'); +CREATE TABLE t2 AS SELECT + CAST(a AS CHAR), + CAST(a AS CHAR(39)), + CAST(a AS CHAR(530)), + CAST(a AS CHAR(65535)), + CAST(a AS CHAR(66000)), + CAST(a AS CHAR(16777215)), + CAST(a AS CHAR(16777216)) +FROM t1; +SHOW CREATE TABLE t2; +--vertical_results +SELECT * FROM t2; +--horizontal_results +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('ffff::ffff'); +CREATE TABLE t2 AS SELECT + CAST(a AS BINARY(4)) AS cb4, + CAST(a AS BINARY) AS cb, + CAST(a AS BINARY(16)) AS cb16, + CAST(a AS BINARY(32)) AS cb32, + CAST(a AS BINARY(530)) AS cb530, + CAST(a AS BINARY(65535)) AS cb65535, + CAST(a AS BINARY(66000)) AS cb66000, + CAST(a AS BINARY(16777215)) AS cb16777215, + CAST(a AS BINARY(16777216)) AS cb16777216 +FROM t1 LIMIT 0; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + CAST(a AS BINARY(4)) AS cb4, + CAST(a AS BINARY) AS cb, + CAST(a AS BINARY(16)) AS cb16, + CAST(a AS BINARY(32)) AS cb32, + CAST(a AS BINARY(530)) AS cb530, + CAST(a AS BINARY(65535)) AS cb65535 +FROM t1; +SHOW CREATE TABLE t2; +--vertical_results +SELECT + HEX(cb4), + HEX(cb), + HEX(cb16), + HEX(cb32), + LENGTH(cb530), + LENGTH(cb65535) +FROM t2; +--horizontal_results +DROP TABLE t2; +DROP TABLE t1; + +--echo # +--echo # Implicit conversion to other types in INSERT +--echo # + +CREATE TABLE t1 (a INT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (CAST('::' AS INET6)); +DROP TABLE t1; + +CREATE TABLE t1 (a DOUBLE); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (CAST('::' AS INET6)); +DROP TABLE t1; + +CREATE TABLE t1 (a DECIMAL(32,0)); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (CAST('::' AS INET6)); +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES (CAST('::' AS INET6)); +DROP TABLE t1; + +CREATE TABLE t1 (a TEXT); +INSERT INTO t1 VALUES (CAST('::' AS INET6)); +DROP TABLE t1; + + + +--echo # +--echo # Boolean context +--echo # + +SELECT + CAST('::' AS INET6) IS TRUE, + CAST('::' AS INET6) IS FALSE, + CAST('::1' AS INET6) IS TRUE, + CAST('::1' AS INET6) IS FALSE; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::1'); +SELECT a, a IS TRUE, a IS FALSE FROM t1 ORDER BY a; +DROP TABLE t1; + +# +# TODO: Error looks like a bug. This should return rows where a<>'::'. +# The same problem is repeatable with GEOMETRY. +# +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::1'),('::2'); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a; +DROP TABLE t1; + + +--echo # +--echo # GROUP BY +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::'); +INSERT INTO t1 VALUES ('::1'),('::01'),('::0001'); +INSERT INTO t1 VALUES ('::2'),('::2'),('::2'),('::2'); +SELECT a, COUNT(*) FROM t1 GROUP BY a; +DROP TABLE t1; + +--echo # +--echo # Aggregate functions +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::'); +INSERT INTO t1 VALUES ('::1'),('::01'),('::0001'); +INSERT INTO t1 VALUES ('::2'),('::2'),('::2'),('::2'); +SELECT MIN(a),MAX(a) FROM t1; + +CREATE TABLE t2 AS SELECT MIN(a), MAX(a) FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT AVG(a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT AVG(DISTINCT a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT SUM(a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT SUM(DISTINCT a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT STDDEV(a) FROM t1; +SELECT GROUP_CONCAT(a ORDER BY a) FROM t1; +SELECT a, GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a; +DROP TABLE t1; + +--echo # +--echo # MDEV-21765 Possibly inconsistent behavior of BIT_xx functions with INET6 field +--echo # + +CREATE TABLE t1 (a INET6); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT BIT_AND(a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT BIT_OR(a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT BIT_XOR(a) FROM t1; +DROP TABLE t1; + + +--echo # +--echo # Window functions +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::1'),('::2'),('::3'),('::4'); +SELECT + a, + LAG(a) OVER (ORDER BY a), + LEAD(a) OVER (ORDER BY a) +FROM t1 ORDER BY a; + +SELECT + a, + FIRST_VALUE(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), + LAST_VALUE(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +FROM t1 ORDER BY a; +DROP TABLE t1; + + +--echo # +--echo # Prepared statements +--echo # + +EXECUTE IMMEDIATE 'CREATE TABLE t1 AS SELECT ? AS a' USING CAST('::' AS INET6); +SHOW CREATE TABLE t1; +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING '::1'; +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING CAST('::2' AS INET6); +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING 0x00000000000000000000000000000003; +SELECT a FROM t1 ORDER BY a; +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING '::1'; +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING CAST('::2' AS INET6); +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING 0x00000000000000000000000000000003; +DROP TABLE t1; + + +--echo # +--echo # Character set and collation aggregation +--echo # + +CREATE TABLE t1 (a INET6); + +CREATE TABLE t2 AS SELECT + CONCAT(a) AS c1, + CONCAT(CAST('::' AS INET6)) AS c2 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + CONCAT(_utf8'1', a) AS c1, + CONCAT(_utf8'1', CAST('::1' AS INET6)) AS c2, + CONCAT(_utf8'1', COALESCE(a)) AS c3 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + CONCAT(_latin1'1', a) AS c1, + CONCAT(_latin1'1', CAST('::1' AS INET6)) AS c2, + CONCAT(_latin1'1', COALESCE(a)) AS c3 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +DROP TABLE t1; + + +--echo # +--echo # UNION +--echo # + +CREATE TABLE t1 AS SELECT CAST('::' AS INET6) AS c UNION SELECT CAST('::1' AS INET6); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT CAST('::' AS INET6) AS c UNION SELECT '::1'; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT '::' AS c UNION SELECT CAST('::1' AS INET6); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT CAST('::' AS INET6) AS c UNION SELECT 0x00000000000000000000000000000001; +SELECT * FROM t1; +DROP TABLE t1; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +CREATE TABLE t1 AS SELECT CAST('::' AS INET6) AS c UNION SELECT 1; + + +--echo # +--echo # Unary operators +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT -CAST('::' AS INET6); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT ABS(CAST('::' AS INET6)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT ROUND(CAST('::' AS INET6)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CEILING(CAST('::' AS INET6)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT FLOOR(CAST('::' AS INET6)); + + +--echo # +--echo # Arithmetic operators +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT CAST('::' AS INET6) + 1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT CAST('::' AS INET6) - 1; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT CAST('::' AS INET6) * 1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT CAST('::' AS INET6) / 1; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT CAST('::' AS INET6) MOD 1; + + +--echo # +--echo # Misc +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT RAND(CAST('::' AS INET6)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT FROM_UNIXTIME(CAST('::' AS INET6)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT HOUR(CAST('::' AS INET6)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT YEAR(CAST('::' AS INET6)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT RELEASE_LOCK(CAST('::' AS INET6)); + + +SELECT JSON_LENGTH(CAST('::' AS INET6)); + +--echo # +--echo # Virtual columns +--echo # + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + a INT, + b INET6 GENERATED ALWAYS AS (CAST(CONCAT(RAND(),a) AS INET6)), INDEX(b) +); + +CREATE TABLE t1 ( + a INT, + b INET6 GENERATED ALWAYS AS (CAST(CONCAT('::',HEX(a)) AS INET6)), INDEX(b) +); +INSERT INTO t1 (a) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15); +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # VIEW +--echo # + +CREATE TABLE t1 (a INT DEFAULT 0); +INSERT INTO t1 (a) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15); +SELECT * FROM t1 ORDER BY a; +CREATE VIEW v1 AS SELECT (CAST(CONCAT('::',HEX(a)) AS INET6)) AS c FROM t1; +SELECT * FROM v1 ORDER BY c; +DROP VIEW v1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6 DEFAULT '::'); +CREATE VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +DESCRIBE v1; +INSERT INTO v1 VALUES ('::'),('::1'),('::2'); +SELECT * FROM t1; +DROP VIEW v1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6 DEFAULT CAST('::' AS INET6)); +CREATE VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +DESCRIBE v1; +INSERT INTO v1 VALUES ('::'),('::1'),('::2'); +SELECT * FROM t1; +DROP VIEW v1; +DROP TABLE t1; + + +--echo # +--echo # Subqueries +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::1'),('::2'); +SELECT * FROM t1 WHERE a=(SELECT MIN(a) FROM t1) ORDER BY a; +SELECT * FROM t1 WHERE a=(SELECT MAX(a) FROM t1) ORDER BY a; +SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 WHERE a>'::') ORDER BY a; +DROP TABLE t1; + +--echo # +--echo # Stored routines +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(a INET6) +BEGIN + DECLARE b INET6 DEFAULT CONCAT('1', a); + SELECT a, b; +END; +$$ +DELIMITER ;$$ +CALL p1('::1'); +CALL p1(CAST('::2' AS INET6)); +DROP PROCEDURE p1; + +DELIMITER $$; +CREATE FUNCTION f1(a INET6) RETURNS INET6 +BEGIN + RETURN CONCAT('1',a); +END; +$$ +DELIMITER ;$$ +SELECT f1('::1'); +SELECT f1(CAST('::1' AS INET6)); +DROP FUNCTION f1; + +--echo # +--echo # Anchored data types in SP variables +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::1'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE va TYPE OF t1.a; + SELECT MAX(a) INTO va FROM t1; + SELECT va; +END; +$$ +DELIMITER ;$$ +CALL p1; +DROP PROCEDURE p1; +DROP TABLE t1; + + +CREATE TABLE t1 (a INET6, b INET6); +INSERT INTO t1 VALUES ('::a', '::b'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE va ROW TYPE OF t1; + SELECT MAX(a), MAX(b) INTO va FROM t1; + SELECT va.a, va.b; +END; +$$ +DELIMITER ;$$ +CALL p1; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Optimizer: make_const_item_for_comparison +--echo # + +CREATE TABLE t1 (id INT, a INET6); +INSERT INTO t1 VALUES (1,'::1'),(2,'::2'); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=COALESCE(CAST('::1' AS INET6)) AND id>0; +DROP TABLE t1; + +--echo # +--echo # Optimizer: equal field propagation +--echo # + +CREATE TABLE t1 (id INT, a INET6); +INSERT INTO t1 VALUES (1,'::1'),(2,'::2'); +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE a=COALESCE(CAST('::1' AS INET6)) + AND LENGTH(CONCAT(a,RAND()))>1; +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE a=COALESCE(CAST('::1' AS INET6)) + AND LENGTH(a)>1; +DROP TABLE t1; + + +--echo # +--echo # Optimizer: equal expression propagation +--echo # + + +CREATE TABLE t1 (id INT, a INET6); +INSERT INTO t1 VALUES (1,'::1'),(2,'::2'); +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE COALESCE(a)='::1' AND COALESCE(a)=CONCAT(a); +DROP TABLE t1; + +--echo # +--echo # Subquery materialization +--echo # + +CREATE TABLE t1 (a INET6, b VARCHAR(32), KEY (a), KEY(b)) ; +INSERT INTO t1 VALUES ('::a','::a'),('::a','::b'); +SET @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=off,subquery_cache=off'; +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS a_inner FROM t1 GROUP BY a_inner); +EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT a AS a_inner FROM t1 GROUP BY a_inner); +SET @@optimizer_switch=DEFAULT; +DROP TABLE t1; + +--echo # +--echo # IS_IPV4_MAPPED(), IS_IPV4_COMPAT() now understand text notation +--echo # +CREATE TABLE t1 (id SERIAL, a VARCHAR(32)); +INSERT INTO t1 (a) VALUES ('::192.168.0.1'),('::192.168.10.111'),('::ffff:10.10.0.1'),('::ffff:192.168.0.1'); +--echo # This is a text notation +SELECT id, length(a), a, IS_IPV4_MAPPED(a) FROM t1 ORDER BY id; +SELECT id, length(a), a, IS_IPV4_COMPAT(a) FROM t1 ORDER BY id; +--echo # This is not a text notation: it is a binary input only looking like text notation +SELECT id, length(a), a, IS_IPV4_MAPPED(BINARY a) FROM t1 ORDER BY id; +SELECT id, length(a), a, IS_IPV4_COMPAT(BINARY a) FROM t1 ORDER BY id; +DROP TABLE t1; + + +--echo # +--echo # ALTER from INET6 to INET6 +--echo # + +CREATE TABLE t1 (a INET6, b INT); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329', 1); +ALTER TABLE t1 MODIFY b DECIMAL(10,2); +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # ALTER to character string data types +--echo # + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS CHAR(39)) FROM t1; +ALTER TABLE t1 MODIFY a CHAR(39); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a VARCHAR(39); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a TINYTEXT; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a TEXT; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a MEDIUMTEXT; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a LONGTEXT; +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # ALTER from character string data types +--echo # + +CREATE OR REPLACE TABLE t1 (a CHAR(64)); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a TINYTEXT); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a TEXT); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a MEDIUMTEXT); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a LONGTEXT); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # ALTER to binary string data types +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a BINARY(16); +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a BINARY(17); +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +--error ER_DATA_TOO_LONG +ALTER TABLE t1 MODIFY a BINARY(15); +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a TINYBLOB; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a BLOB; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a MEDIUMBLOB; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a LONGBLOB; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + + +--echo # +--echo # ALTER from binary string data types +--echo # + +CREATE TABLE t1 (a BINARY(16)); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a INET6; +SELECT a FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a BINARY(17)); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF000042832900'); +--error ER_TRUNCATED_WRONG_VALUE +ALTER TABLE t1 MODIFY a INET6; +DROP TABLE t1; + +CREATE TABLE t1 (a BINARY(15)); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF00004283'); +--error ER_TRUNCATED_WRONG_VALUE +ALTER TABLE t1 MODIFY a INET6; +DROP TABLE t1; + +CREATE TABLE t1 (a TINYBLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a INET6; +SELECT a FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a BLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a INET6; +SELECT a FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a MEDIUMBLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a INET6; +SELECT a FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a BLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a INET6; +SELECT a FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from INET6 to INET6 +--echo # + +CREATE TABLE t1 (a INET6, b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +--echo # +--echo # SET from INET6 to numeric +--echo # + +CREATE TABLE t1 (a INET6, b INT); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b DOUBLE); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b DECIMAL(32,0)); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b YEAR); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from numeric to INET6 +--echo # + +CREATE TABLE t1 (a INT, b INET6); +INSERT INTO t1 VALUES (1, NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a DOUBLE, b INET6); +INSERT INTO t1 VALUES (1, NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a DECIMAL(32,0), b INET6); +INSERT INTO t1 VALUES (1, NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a YEAR, b INET6); +INSERT INTO t1 VALUES (1, NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from INET6 to temporal +--echo # + +CREATE TABLE t1 (a INET6, b TIME); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b DATE); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b DATETIME); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b TIMESTAMP NULL DEFAULT NULL); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from temporal to INET6 +--echo # + +CREATE TABLE t1 (a TIME, b INET6); +INSERT INTO t1 VALUES ('00:00:00', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a DATE, b INET6); +INSERT INTO t1 VALUES ('2001-01:01', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a DATETIME, b INET6); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TIMESTAMP, b INET6); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from INET6 to character string +--echo # + +CREATE TABLE t1 (a INET6, b CHAR(39)); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b VARCHAR(39)); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b TEXT); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b ENUM('ffff::ffff')); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b SET('ffff::ffff')); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from character string to INET6 +--echo # + +CREATE TABLE t1 (a CHAR(39), b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(39), b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TEXT, b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a ENUM('ffff::ffff'), b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a SET('ffff::ffff'), b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from INET6 to binary +--echo # + +CREATE TABLE t1 (a INET6, b BINARY(16)); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT HEX(b) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b VARBINARY(39)); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT HEX(b) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b BLOB); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT HEX(b) FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from binary to INET6 +--echo # + +CREATE TABLE t1 (a BINARY(16), b INET6); +INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a VARBINARY(16), b INET6); +INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a BLOB, b INET6); +INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # Limit clause parameter +--echo # TODO: this should fail. +--echo # The test for a valid data type should be moved +--echo # from parse time to fix_fields() time, and performed +--echo # for both Item_splocal and Item_param. +--echo # + +EXECUTE IMMEDIATE 'SELECT 1 FROM DUAL LIMIT ?' USING CAST('::' AS INET6); + + +## TODO: +## - Add hooks to run mysql_client_test with pluggable data types +## +## - This should fail with the "illegal data type" error: +##SELECT CAST('::' AS INET6) DIV 1; +## +## - This should fail with the "illegal data type" error: +## EXTRACT(MINUTE...) +## + + +--echo # +--echo # MDEV-20785 Converting INET6 to CHAR(39) produces garbage without a warning +--echo # + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS CHAR(39)) FROM t1; +ALTER TABLE t1 MODIFY a CHAR(39); +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-20783 INET6 cannot be converted to BINARY(16) (requires clarification in documentation) +--echo # + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a BINARY(16); +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-20795 CAST(inet6 AS BINARY) returns wrong result +--echo # + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT HEX(CAST(a AS BINARY)) FROM t1; +SELECT HEX(CAST(a AS BINARY(16))) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-20808 CAST from INET6 to FLOAT does not produce an error +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(a AS FLOAT) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-20798 Conversion from INET6 to other types performed without errors or warnings +--echo # + +CREATE TABLE t1 (a INET6, b INT); +INSERT INTO t1 (a) VALUES ('::'); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +UPDATE t1 SET b=a; +SELECT * FROM t1; +DROP TABLE t1; + +SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); +CREATE TABLE t1 (a INET6, b TIMESTAMP); +INSERT INTO t1 (a) VALUES ('::'); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT * FROM t1; +DROP TABLE t1; +SET timestamp=DEFAULT; + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 (a) VALUES ('::'); +--error ER_TRUNCATED_WRONG_VALUE +ALTER TABLE t1 MODIFY a DATE; +DROP TABLE t1; + +--echo # +--echo # MDEV-20818 ER_CRASHED_ON_USAGE or Assertion `length <= column->length' failed in write_block_record on temporary table +--echo # + +CREATE TABLE t1 (a INET6); +--enable_metadata +SELECT + CAST(a AS BINARY(0)), + CAST(a AS BINARY(1)), + CAST(a AS BINARY(16)), + CAST(a AS BINARY(255)), + CAST(a AS BINARY(256)), + CAST(a AS BINARY(512)), + CAST(a AS BINARY(513)), + CAST(a AS BINARY(65532)), + CAST(a AS BINARY(65533)), + CAST(a AS BINARY(65534)), + CAST(a AS BINARY(65535)), + CAST(a AS BINARY(65536)), + CAST(a AS BINARY(16777215)), + CAST(a AS BINARY(16777216)) +FROM t1; +--disable_metadata +DROP TABLE t1; + +--echo # +--echo # MDEV-20826 Wrong result of MIN(inet6) with GROUP BY +--echo # + +CREATE TABLE t1 (id INT, a INET6) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1, 'fff::'),(1, '8888::'); +SELECT MIN(a), MAX(a) FROM t1 GROUP BY id; +DROP TABLE t1; + + +--echo # +--echo # MDEV-20809 EXTRACT from INET6 value does not produce any warnings +--echo # + +CREATE TABLE t1 (a INET6); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT EXTRACT(DAY FROM a) FROM t1; +DROP TABLE t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT EXTRACT(DAY FROM CAST('::' AS INET6)); + + +--echo # +--echo # MDEV-22764 Crash with a stored aggregate function returning INET6 +--echo # + +DELIMITER $$; +CREATE OR REPLACE AGGREGATE FUNCTION aggregate_min_inet6(x INET6) RETURNS INET6 +BEGIN + DECLARE res INET6 DEFAULT NULL; + DECLARE CONTINUE HANDLER FOR NOT FOUND + RETURN res; + LOOP + FETCH GROUP NEXT ROW; + IF (res IS NULL) OR (res > x) THEN + SET res= x; + END IF; + END LOOP; +END; +$$ +DELIMITER ;$$ + +CREATE OR REPLACE TABLE t1 (name CHAR(30), val INET6); +INSERT INTO t1 VALUES ('a', '::05'); +INSERT INTO t1 VALUES ('a', '::03'); +INSERT INTO t1 VALUES ('b', '::01'); +INSERT INTO t1 VALUES ('b', '::02'); +INSERT INTO t1 VALUES ('b', '::05'); +SELECT name, aggregate_min_inet6(val) pc FROM t1 GROUP BY name; + +CREATE OR REPLACE TABLE t2 (name CHAR(30), val INET6); +INSERT INTO t2 SELECT name, aggregate_min_inet6(val) pc FROM t1 GROUP BY name; +SELECT * FROM t2; +DROP TABLE t2; + +DROP TABLE t1; +DROP FUNCTION aggregate_min_inet6; + + +--echo # +--echo # MDEV-20280 PERCENTILE_DISC() rejects temporal and string input +--echo # + +CREATE TABLE t1 (name CHAR(30), star_rating INET6); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', '::5'); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', '::3'); +INSERT INTO t1 VALUES ('Lady of the Flies', '::1'); +INSERT INTO t1 VALUES ('Lady of the Flies', '::2'); +INSERT INTO t1 VALUES ('Lady of the Flies', '::5'); +SELECT name, PERCENTILE_DISC(0.5) + WITHIN GROUP (ORDER BY star_rating) + OVER (PARTITION BY name) AS pc FROM t1; +SELECT name, PERCENTILE_DISC(0) + WITHIN GROUP (ORDER BY star_rating) + OVER (PARTITION BY name) AS pc FROM t1; +SELECT name, PERCENTILE_DISC(1) + WITHIN GROUP (ORDER BY star_rating) + OVER (PARTITION BY name) AS pc FROM t1; +DROP TABLE t1; + + +--echo # +--echo # MDEV-22758 Assertion `!item->null_value' failed in Type_handler_inet6::make_sort_key_part +--echo # + +CREATE TABLE t1 (a VARCHAR(8) NOT NULL, b INET6 NOT NULL); +INSERT INTO t1 VALUES ('foo','::'),('bar','1::1'); +SELECT * FROM t1 ORDER BY CASE WHEN a THEN b ELSE a END; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a VARCHAR(8) NOT NULL); +INSERT INTO t1 VALUES ('foo'),('bar'); +SELECT * FROM t1 ORDER BY CAST(a AS INET6); +DROP TABLE t1; + +CREATE TABLE t1 (a INET6 NOT NULL, b VARCHAR(32) NOT NULL); +CREATE TABLE t2 AS SELECT CAST(a AS INET6) AS ca, CAST(b AS INET6) AS cb FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT COALESCE(a,a), COALESCE(a,b) FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT a AS ca,a AS cb FROM t1 UNION SELECT a,b FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +DROP TABLE t1; + +--echo # +--echo # MDEV-22758 Assertion `!item->null_value' failed in Type_handler_inet6::make_sort_key_part +--echo # + +CREATE TABLE t1 (c INET6); +INSERT INTO t1 VALUES ('::'),(NULL); +SELECT * FROM t1 ORDER BY IFNULL(c, 'foo'); +DROP TABLE t1; + +CREATE TABLE t1 (c INET6); +INSERT INTO t1 VALUES ('::'),(NULL); + +# Expect a NULL column +CREATE TABLE t2 AS SELECT IFNULL(c, 'foo') FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; + +# Expect a NOT NULL column +CREATE TABLE t2 AS SELECT IFNULL(c, '::1') FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; + +DROP TABLE t1; + +--echo # +--echo # MDEV-26732 Assertion `0' failed in Item::val_native +--echo # + +# This tests Item_copy_inet6::val_native() +SELECT CAST(CONCAT('::', REPEAT('',RAND())) AS INET6) AS f, var_pop('x') FROM dual HAVING f > ''; +SELECT CAST(CONCAT('::', REPEAT('',RAND())) AS INET6) AS f, var_pop(1) FROM dual HAVING f >= '::'; + +# This tests Item_copy_inet6::save_in_field() +CREATE TABLE t1(id INET6 NOT NULL PRIMARY KEY, dsc INET6); +INSERT INTO t1 VALUES ('::1', '1::1'),('::3', '1::3'),('::4', NULL); +CREATE TABLE t2 SELECT COALESCE(t1.dsc), COUNT(*) FROM t1 GROUP BY t1.id; +SELECT * FROM t2 ORDER BY 1,2; +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-24619 Wrong result or Assertion `0' in Item::val_native / Type_handler_inet6::Item_val_native_with_conversion +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::'); +SELECT IF(1, '::', a) AS f FROM t1 GROUP BY 'foo' HAVING f != ''; +SELECT IF(1, '::', a) AS f FROM t1 GROUP BY 'foo' HAVING f != '::'; +SELECT IF(1, '::', a) AS f FROM t1 GROUP BY 'foo' HAVING f != '::1'; +DROP TABLE t1; + + +--echo # +--echo # MDEV-28491 Uuid. "UPDATE/DELETE" not working "WHERE id IN (SELECT id FROM ..)" +--echo # + +CREATE TABLE companies (id INET6, name varchar(10)); +INSERT INTO companies (id) values ('00::01'); + +CREATE TABLE divisions (company_id INET6); +INSERT INTO divisions (company_id) values ('00::01'); +SELECT * FROM companies WHERE id IN (SELECT company_id FROM divisions); +UPDATE companies SET name = 'value' WHERE id IN (SELECT company_id FROM divisions); +SELECT * FROM companies; +DELETE FROM companies WHERE id IN (SELECT company_id FROM divisions); +SELECT * FROM companies; +DROP TABLE divisions; +DROP TABLE companies; + +--echo # +--echo # MDEV-27099 Subquery using the ALL keyword on INET6 columns produces a wrong result +--echo # + +CREATE TABLE t1 (d INET6); +INSERT INTO t1 VALUES ('1::0'), ('12::0'); +SELECT * FROM t1 ORDER BY d; +SELECT * FROM t1 WHERE d <= ALL (SELECT * FROM t1); +SELECT * FROM t1 WHERE d >= ALL (SELECT * FROM t1); +DROP TABLE t1; |