diff options
21 files changed, 4308 insertions, 3 deletions
diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6-debug.result b/plugin/type_inet/mysql-test/type_inet/type_inet6-debug.result new file mode 100644 index 00000000000..0e879aad58f --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6-debug.result @@ -0,0 +1,18 @@ +# +# MDEV-274 The data type for IPv6/IPv4 addresses in MariaDB +# +SET @old_debug_dbug=@@debug_dbug; +SET debug_dbug="+d,frm_data_type_info"; +CREATE TABLE t1 (c01 INET6, c02 INET6); +Warnings: +Note 1105 build_frm_image: Field data type info length: 14 +Note 1105 DBUG: [0] name='c01' type_info='inet6' +Note 1105 DBUG: [1] name='c02' type_info='inet6' +SET debug_dbug=@old_debug_dbug; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c01` inet6 DEFAULT NULL, + `c02` inet6 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6-debug.test b/plugin/type_inet/mysql-test/type_inet/type_inet6-debug.test new file mode 100644 index 00000000000..ef5ea8363f2 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6-debug.test @@ -0,0 +1,14 @@ +--source include/have_debug.inc + +--echo # +--echo # MDEV-274 The data type for IPv6/IPv4 addresses in MariaDB +--echo # + +SET @old_debug_dbug=@@debug_dbug; + +SET debug_dbug="+d,frm_data_type_info"; +CREATE TABLE t1 (c01 INET6, c02 INET6); +SET debug_dbug=@old_debug_dbug; + +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6.result b/plugin/type_inet/mysql-test/type_inet/type_inet6.result new file mode 100644 index 00000000000..7d88cce3d81 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6.result @@ -0,0 +1,1276 @@ +# +# Basic CREATE functionality, defaults, metadata +# +CREATE TABLE t1 (a INET6 AUTO_INCREMENT); +ERROR 42000: Incorrect column specifier for column 'a' +CREATE TABLE t1 (a INET6); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` inet6 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DESCRIBE t1; +Field Type Null Key Default Extra +a inet6 YES NULL +SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' AND table_name='t1'; +TABLE_CATALOG def +TABLE_SCHEMA test +TABLE_NAME t1 +COLUMN_NAME a +ORDINAL_POSITION 1 +COLUMN_DEFAULT NULL +IS_NULLABLE YES +DATA_TYPE inet6 +CHARACTER_MAXIMUM_LENGTH NULL +CHARACTER_OCTET_LENGTH NULL +NUMERIC_PRECISION NULL +NUMERIC_SCALE NULL +DATETIME_PRECISION NULL +CHARACTER_SET_NAME NULL +COLLATION_NAME NULL +COLUMN_TYPE inet6 +COLUMN_KEY +EXTRA +PRIVILEGES # +COLUMN_COMMENT +IS_GENERATED NEVER +GENERATION_EXPRESSION NULL +DROP TABLE t1; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::1'); +SELECT * FROM t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 t1 a a 254 39 3 Y 160 0 8 +a +::1 +SELECT CAST('::' AS INET6) AS a; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def a 254 39 2 N 33 0 8 +a +:: +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; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` inet6 DEFAULT '::', + `c2` inet6 DEFAULT 'ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff', + `c3` inet6 DEFAULT '::', + `c4` inet6 DEFAULT 'ffff::ffff', + `c5` inet6 DEFAULT cast(X'ffffffffffffffffffffffffffffffff' as inet6) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DESCRIBE t1; +Field Type Null Key Default Extra +c1 inet6 YES :: +c2 inet6 YES ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff +c3 inet6 YES :: +c4 inet6 YES ffff::ffff +c5 inet6 YES cast(X'ffffffffffffffffffffffffffffffff' as inet6) +SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' AND table_name='t1'; +TABLE_CATALOG def +TABLE_SCHEMA test +TABLE_NAME t1 +COLUMN_NAME c1 +ORDINAL_POSITION 1 +COLUMN_DEFAULT '::' +IS_NULLABLE YES +DATA_TYPE inet6 +CHARACTER_MAXIMUM_LENGTH NULL +CHARACTER_OCTET_LENGTH NULL +NUMERIC_PRECISION NULL +NUMERIC_SCALE NULL +DATETIME_PRECISION NULL +CHARACTER_SET_NAME NULL +COLLATION_NAME NULL +COLUMN_TYPE inet6 +COLUMN_KEY +EXTRA +PRIVILEGES # +COLUMN_COMMENT +IS_GENERATED NEVER +GENERATION_EXPRESSION NULL +TABLE_CATALOG def +TABLE_SCHEMA test +TABLE_NAME t1 +COLUMN_NAME c2 +ORDINAL_POSITION 2 +COLUMN_DEFAULT 'ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff' +IS_NULLABLE YES +DATA_TYPE inet6 +CHARACTER_MAXIMUM_LENGTH NULL +CHARACTER_OCTET_LENGTH NULL +NUMERIC_PRECISION NULL +NUMERIC_SCALE NULL +DATETIME_PRECISION NULL +CHARACTER_SET_NAME NULL +COLLATION_NAME NULL +COLUMN_TYPE inet6 +COLUMN_KEY +EXTRA +PRIVILEGES # +COLUMN_COMMENT +IS_GENERATED NEVER +GENERATION_EXPRESSION NULL +TABLE_CATALOG def +TABLE_SCHEMA test +TABLE_NAME t1 +COLUMN_NAME c3 +ORDINAL_POSITION 3 +COLUMN_DEFAULT '::' +IS_NULLABLE YES +DATA_TYPE inet6 +CHARACTER_MAXIMUM_LENGTH NULL +CHARACTER_OCTET_LENGTH NULL +NUMERIC_PRECISION NULL +NUMERIC_SCALE NULL +DATETIME_PRECISION NULL +CHARACTER_SET_NAME NULL +COLLATION_NAME NULL +COLUMN_TYPE inet6 +COLUMN_KEY +EXTRA +PRIVILEGES # +COLUMN_COMMENT +IS_GENERATED NEVER +GENERATION_EXPRESSION NULL +TABLE_CATALOG def +TABLE_SCHEMA test +TABLE_NAME t1 +COLUMN_NAME c4 +ORDINAL_POSITION 4 +COLUMN_DEFAULT 'ffff::ffff' +IS_NULLABLE YES +DATA_TYPE inet6 +CHARACTER_MAXIMUM_LENGTH NULL +CHARACTER_OCTET_LENGTH NULL +NUMERIC_PRECISION NULL +NUMERIC_SCALE NULL +DATETIME_PRECISION NULL +CHARACTER_SET_NAME NULL +COLLATION_NAME NULL +COLUMN_TYPE inet6 +COLUMN_KEY +EXTRA +PRIVILEGES # +COLUMN_COMMENT +IS_GENERATED NEVER +GENERATION_EXPRESSION NULL +TABLE_CATALOG def +TABLE_SCHEMA test +TABLE_NAME t1 +COLUMN_NAME c5 +ORDINAL_POSITION 5 +COLUMN_DEFAULT cast(X'ffffffffffffffffffffffffffffffff' as inet6) +IS_NULLABLE YES +DATA_TYPE inet6 +CHARACTER_MAXIMUM_LENGTH NULL +CHARACTER_OCTET_LENGTH NULL +NUMERIC_PRECISION NULL +NUMERIC_SCALE NULL +DATETIME_PRECISION NULL +CHARACTER_SET_NAME NULL +COLLATION_NAME NULL +COLUMN_TYPE inet6 +COLUMN_KEY +EXTRA +PRIVILEGES # +COLUMN_COMMENT +IS_GENERATED NEVER +GENERATION_EXPRESSION NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 INET6 DEFAULT 0x00); +ERROR 42000: Invalid default value for 'c1' +CREATE TABLE t1 (c1 INET6 DEFAULT ''); +ERROR 42000: Invalid default value for 'c1' +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 22007: Incorrect inet6 value: '1' for column `test`.`t1`.`a` at row 1 +INSERT INTO t1 VALUES (TIME'10:20:30'); +ERROR 22007: Incorrect inet6 value: '10:20:30' for column `test`.`t1`.`a` at row 1 +INSERT INTO t1 VALUES (0x00); +ERROR 22007: Incorrect inet6 value: '\x00' for column `test`.`t1`.`a` at row 1 +DROP TABLE t1; +# +# CAST +# +SELECT CAST('garbage' AS INET6); +CAST('garbage' AS INET6) +NULL +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +SELECT CAST(0x01 AS INET6); +CAST(0x01 AS INET6) +NULL +Warnings: +Warning 1292 Incorrect inet6 value: '\x01' +SELECT CAST(REPEAT(0x00,16) AS INET6); +CAST(REPEAT(0x00,16) AS INET6) +:: +SELECT CAST(REPEAT(0x11,16) AS INET6); +CAST(REPEAT(0x11,16) AS INET6) +1111:1111:1111:1111:1111:1111:1111:1111 +CREATE TABLE t1 AS SELECT CAST('::' AS INET6); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `CAST('::' AS INET6)` inet6 NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# Text and binary formats, comparison operators +# +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; +a +:: +::1 +ffff::1 +ffff::2 +SELECT * FROM t1 ORDER BY a DESC; +a +ffff::2 +ffff::1 +::1 +:: +SELECT HEX(a),a FROM t1 ORDER BY a; +HEX(a) a +00000000000000000000000000000000 :: +00000000000000000000000000000001 ::1 +FFFF0000000000000000000000000001 ffff::1 +FFFF0000000000000000000000000002 ffff::2 +SELECT * FROM t1 WHERE a='::'; +a +:: +SELECT * FROM t1 WHERE a='::1'; +a +::1 +SELECT * FROM t1 WHERE a='ffff::1'; +a +ffff::1 +SELECT * FROM t1 WHERE a='ffff::2'; +a +ffff::2 +SELECT * FROM t1 WHERE a=0x00000000000000000000000000000000; +a +:: +SELECT * FROM t1 WHERE a=0x00000000000000000000000000000001; +a +::1 +SELECT * FROM t1 WHERE a=0xffff0000000000000000000000000001; +a +ffff::1 +SELECT * FROM t1 WHERE a=0xffff0000000000000000000000000002; +a +ffff::2 +SELECT * FROM t1 WHERE a<'::'; +a +SELECT * FROM t1 WHERE a<='::'; +a +:: +SELECT * FROM t1 WHERE a>='ffff::2'; +a +ffff::2 +SELECT * FROM t1 WHERE a>'ffff::2'; +a +SELECT * FROM t1 WHERE a IN ('::', 'ffff::1') ORDER BY a; +a +:: +ffff::1 +SELECT * FROM t1 WHERE a IN ('::', 0xffff0000000000000000000000000002) ORDER BY a; +a +:: +ffff::2 +SELECT * FROM t1 WHERE a<'garbage'; +a +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +SELECT * FROM t1 WHERE a<='garbage'; +a +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +SELECT * FROM t1 WHERE a='garbage'; +a +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +SELECT * FROM t1 WHERE a>='garbage'; +a +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +SELECT * FROM t1 WHERE a>'garbage'; +a +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +SELECT * FROM t1 WHERE a<0x01; +a +Warnings: +Warning 1292 Incorrect inet6 value: '\x01' +SELECT * FROM t1 WHERE a<=0x01; +a +Warnings: +Warning 1292 Incorrect inet6 value: '\x01' +SELECT * FROM t1 WHERE a=0x01; +a +Warnings: +Warning 1292 Incorrect inet6 value: '\x01' +Warning 1292 Incorrect inet6 value: '\x01' +SELECT * FROM t1 WHERE a>=0x01; +a +Warnings: +Warning 1292 Incorrect inet6 value: '\x01' +SELECT * FROM t1 WHERE a>0x01; +a +Warnings: +Warning 1292 Incorrect inet6 value: '\x01' +SELECT * FROM t1 WHERE a='0::0'; +a +:: +SELECT * FROM t1 WHERE a='0::00'; +a +:: +SELECT * FROM t1 WHERE a='0::000'; +a +:: +SELECT * FROM t1 WHERE a='0::0000'; +a +:: +SELECT * FROM t1 WHERE a=0; +ERROR HY000: Illegal parameter data types inet6 and int for operation '=' +SELECT * FROM t1 WHERE a=0.0; +ERROR HY000: Illegal parameter data types inet6 and decimal for operation '=' +SELECT * FROM t1 WHERE a=0e0; +ERROR HY000: Illegal parameter data types inet6 and double for operation '=' +SELECT * FROM t1 WHERE a=TIME'10:20:30'; +ERROR HY000: Illegal parameter data types inet6 and time for operation '=' +SELECT * FROM t1 WHERE a IN ('::', 10); +ERROR HY000: Illegal parameter data types inet6 and int for operation 'in' +DROP TABLE t1; +# +# cmp_item_inet6: IN for non-constants +# +CREATE TABLE t1 (a INET6, b INET6); +INSERT INTO t1 VALUES ('::1', '::2'); +SELECT * FROM t1 WHERE '::' IN (a, b); +a b +SELECT * FROM t1 WHERE '::1' IN (a, b); +a b +::1 ::2 +SELECT * FROM t1 WHERE '::01' IN (a, b); +a b +::1 ::2 +SELECT * FROM t1 WHERE '00::01' IN (a, b); +a b +::1 ::2 +DROP TABLE t1; +# +# cmp_item_inet6: DECODE_ORACLE +# +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES (NULL),('::01'),('::02'); +SELECT a, DECODE_ORACLE(a, '::01', '01') AS d FROM t1; +a d +NULL NULL +::1 01 +::2 NULL +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; +a d0 d1 d2 +NULL NULL <NULL> <NULL> +::1 01 01 01 +::2 NULL NULL NULL +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +DROP TABLE t1; +# +# CASE abbreviations +# +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; +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(c, c_char)` inet6 DEFAULT NULL, + `COALESCE(c, c_varchar)` inet6 DEFAULT NULL, + `COALESCE(c, c_tinytext)` inet6 DEFAULT NULL, + `COALESCE(c, c_text)` inet6 DEFAULT NULL, + `COALESCE(c, c_mediumtext)` inet6 DEFAULT NULL, + `COALESCE(c, c_longtext)` inet6 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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; +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(c, c_char)` inet6 DEFAULT NULL, + `LEAST(c, c_varchar)` inet6 DEFAULT NULL, + `LEAST(c, c_tinytext)` inet6 DEFAULT NULL, + `LEAST(c, c_text)` inet6 DEFAULT NULL, + `LEAST(c, c_mediumtext)` inet6 DEFAULT NULL, + `LEAST(c, c_longtext)` inet6 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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; +COALESCE(a, '::') +:: +::1 +::2 +SELECT a, LEAST(a,'::0'), LEAST(a,'::f') FROM t1 ORDER BY a; +a LEAST(a,'::0') LEAST(a,'::f') +NULL NULL NULL +::1 :: ::1 +::2 :: ::2 +SELECT a, GREATEST(a,'::0'), GREATEST(a,'::f') FROM t1 ORDER BY a; +a GREATEST(a,'::0') GREATEST(a,'::f') +NULL NULL NULL +::1 ::1 ::f +::2 ::2 ::f +CREATE TABLE t2 AS SELECT +COALESCE(a, '::'), +LEAST(a,'::'), +GREATEST(a,'::') +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a, '::')` inet6 DEFAULT NULL, + `LEAST(a,'::')` inet6 DEFAULT NULL, + `GREATEST(a,'::')` inet6 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +SELECT COALESCE(a, 0x00000000000000000000000000000000) FROM t1 ORDER BY a; +COALESCE(a, 0x00000000000000000000000000000000) +:: +::1 +::2 +SELECT a, +LEAST(a, 0x00000000000000000000000000000000), +LEAST(a, 0x0000000000000000000000000000000f) +FROM t1 ORDER BY a; +a LEAST(a, 0x00000000000000000000000000000000) LEAST(a, 0x0000000000000000000000000000000f) +NULL NULL NULL +::1 :: ::1 +::2 :: ::2 +SELECT a, +GREATEST(a, 0x00000000000000000000000000000000), +GREATEST(a, 0x0000000000000000000000000000000f) +FROM t1 ORDER BY a; +a GREATEST(a, 0x00000000000000000000000000000000) GREATEST(a, 0x0000000000000000000000000000000f) +NULL NULL NULL +::1 ::1 ::f +::2 ::2 ::f +CREATE TABLE t2 AS SELECT +COALESCE(a, 0x00000000000000000000000000000000), +LEAST(a,0x00000000000000000000000000000000), +GREATEST(a,0x00000000000000000000000000000000) +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a, 0x00000000000000000000000000000000)` inet6 DEFAULT NULL, + `LEAST(a,0x00000000000000000000000000000000)` inet6 DEFAULT NULL, + `GREATEST(a,0x00000000000000000000000000000000)` inet6 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +SELECT COALESCE(a, 10) FROM t1; +ERROR HY000: Illegal parameter data types inet6 and int for operation 'coalesce' +SELECT LEAST(a, 10) FROM t1; +ERROR HY000: Illegal parameter data types inet6 and int for operation 'least' +SELECT GREATEST(a, 10) FROM t1; +ERROR HY000: Illegal parameter data types inet6 and int for operation 'greatest' +DROP TABLE t1; +SELECT COALESCE('garbage', CAST('::1' AS INET6)); +COALESCE('garbage', CAST('::1' AS INET6)) +::1 +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +SELECT COALESCE(0x01, CAST('::1' AS INET6)); +COALESCE(0x01, CAST('::1' AS INET6)) +::1 +Warnings: +Warning 1292 Incorrect inet6 value: '\x01' +# +# Uniqueness +# +CREATE TABLE t1 (a INET6 NOT NULL PRIMARY KEY); +INSERT INTO t1 VALUES ('41::1'),('61::1'); +INSERT INTO t1 VALUES ('41::1'); +ERROR 23000: Duplicate entry '41::1' for key 'PRIMARY' +SELECT * FROM t1; +a +41::1 +61::1 +DROP TABLE t1; +# +# Indexes +# +CREATE TABLE t1 (a INET6, KEY(a(1))); +ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys +# +# Explicit CAST on INSERT +# +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; +a +1::1 +1::2 +1::3 +2::1 +2::2 +2::3 +DROP TABLE t1; +# +# Explicit CAST and implicit CAST on ALTER +# +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; +a CAST(a AS INET6) +:: :: +::1 ::1 +ffff::1 ffff::1 +ffff::2 ffff::2 +garbage NULL +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +SELECT a, CAST(a AS INET6) FROM t1 ORDER BY CAST(a AS INET6); +a CAST(a AS INET6) +garbage NULL +:: :: +::1 ::1 +ffff::1 ffff::1 +ffff::2 ffff::2 +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +ALTER TABLE t1 MODIFY a INET6; +ERROR 22007: Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +SET sql_mode=''; +ALTER TABLE t1 MODIFY a INET6; +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +SET sql_mode=DEFAULT; +SELECT * FROM t1 ORDER BY a; +a +NULL +:: +::1 +ffff::1 +ffff::2 +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; +HEX(a) CAST(a AS INET6) +00000000000000000000000000000000 :: +00000000000000000000000000000001 ::1 +FFFF0000000000000000000000000001 ffff::1 +FFFF0000000000000000000000000002 ffff::2 +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1 ORDER BY a; +a +:: +::1 +ffff::1 +ffff::2 +DROP TABLE t1; +# +# INSERT..SELECT, same data types +# +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; +a +:: +::1 +::2 +DROP TABLE t1,t2; +# +# Implicit CAST on INSERT..SELECT, text format +# +CREATE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES ('garbage'),('::'),('::1'),('ffff::1'),('ffff::2'); +CREATE TABLE t2 (a INET6); +INSERT INTO t2 SELECT a FROM t1; +ERROR 22007: Incorrect inet6 value: 'garbage' for column `test`.`t2`.`a` at row 1 +SET sql_mode=''; +INSERT INTO t2 SELECT a FROM t1; +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t2`.`a` at row 1 +SELECT * FROM t2 ORDER BY a; +a +NULL +:: +::1 +ffff::1 +ffff::2 +SET sql_mode=DEFAULT; +DROP TABLE t2; +CREATE TABLE t2 (a INET6 NOT NULL); +INSERT INTO t2 SELECT a FROM t1; +ERROR 22007: Incorrect inet6 value: 'garbage' for column `test`.`t2`.`a` at row 1 +SET sql_mode=''; +INSERT INTO t2 SELECT a FROM t1; +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t2`.`a` at row 1 +SELECT * FROM t2 ORDER BY a; +a +:: +:: +::1 +ffff::1 +ffff::2 +SET sql_mode=DEFAULT; +DROP TABLE t2; +DROP TABLE t1; +# +# Implicit CAST on INSERT..SELECT, binary format +# +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; +a +:: +::1 +ffff::1 +ffff::2 +DROP TABLE t1,t2; +# +# CAST to other data types +# +SELECT CAST(CAST('::' AS INET6) AS DOUBLE); +ERROR HY000: Illegal parameter data type inet6 for operation 'double_typecast' +SELECT CAST(CAST('::' AS INET6) AS DECIMAL); +ERROR HY000: Illegal parameter data type inet6 for operation 'decimal_typecast' +SELECT CAST(CAST('::' AS INET6) AS SIGNED); +ERROR HY000: Illegal parameter data type inet6 for operation 'cast_as_signed' +SELECT CAST(CAST('::' AS INET6) AS UNSIGNED); +ERROR HY000: Illegal parameter data type inet6 for operation 'cast_as_unsigned' +SELECT CAST(CAST('::' AS INET6) AS TIME); +ERROR HY000: Illegal parameter data type inet6 for operation 'cast_as_time' +SELECT CAST(CAST('::' AS INET6) AS DATE); +ERROR HY000: Illegal parameter data type inet6 for operation 'cast_as_date' +SELECT CAST(CAST('::' AS INET6) AS DATETIME); +ERROR HY000: Illegal parameter data type inet6 for operation 'cast_as_datetime' +SELECT CAST(CAST('::' AS INET6) AS CHAR); +CAST(CAST('::' AS INET6) AS CHAR) +:: +CREATE TABLE t1 AS SELECT CAST(CAST('::' AS INET6) AS CHAR) AS a; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(39) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# Implicit conversion to other types in INSERT +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (CAST('::' AS INET6)); +ERROR 22007: Incorrect integer value: '::' for column `test`.`t1`.`a` at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES (CAST('::' AS INET6)); +ERROR 22007: Incorrect double value: '::' for column `test`.`t1`.`a` at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL(32,0)); +INSERT INTO t1 VALUES (CAST('::' AS INET6)); +ERROR 22007: Incorrect decimal value: '::' for column `test`.`t1`.`a` at row 1 +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; +# +# Boolean context +# +SELECT +CAST('::' AS INET6) IS TRUE, +CAST('::' AS INET6) IS FALSE, +CAST('::1' AS INET6) IS TRUE, +CAST('::1' AS INET6) IS FALSE; +CAST('::' AS INET6) IS TRUE CAST('::' AS INET6) IS FALSE CAST('::1' AS INET6) IS TRUE CAST('::1' AS INET6) IS FALSE +0 1 1 0 +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::1'); +SELECT a, a IS TRUE, a IS FALSE FROM t1 ORDER BY a; +a a IS TRUE a IS FALSE +:: 0 1 +::1 1 0 +DROP TABLE t1; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::1'),('::2'); +SELECT * FROM t1 WHERE a; +ERROR HY000: Illegal parameter data types inet6 and bigint for operation '<>' +DROP TABLE t1; +# +# GROUP BY +# +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; +a COUNT(*) +:: 2 +::1 3 +::2 4 +DROP TABLE t1; +# +# Aggregate functions +# +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; +MIN(a) MAX(a) +:: ::2 +CREATE TABLE t2 AS SELECT MIN(a), MAX(a) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `MIN(a)` inet6 DEFAULT NULL, + `MAX(a)` inet6 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +SELECT AVG(a) FROM t1; +ERROR HY000: Illegal parameter data type inet6 for operation 'avg(' +SELECT AVG(DISTINCT a) FROM t1; +ERROR HY000: Illegal parameter data type inet6 for operation 'avg(distinct ' +SELECT SUM(a) FROM t1; +ERROR HY000: Illegal parameter data type inet6 for operation 'sum(' +SELECT SUM(DISTINCT a) FROM t1; +ERROR HY000: Illegal parameter data type inet6 for operation 'sum(distinct ' +SELECT STDDEV(a) FROM t1; +ERROR HY000: Illegal parameter data type inet6 for operation 'std(' +SELECT GROUP_CONCAT(a ORDER BY a) FROM t1; +GROUP_CONCAT(a ORDER BY a) +::,::,::1,::1,::1,::2,::2,::2,::2 +SELECT a, GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a; +a GROUP_CONCAT(a ORDER BY a) +:: ::,:: +::1 ::1,::1,::1 +::2 ::2,::2,::2,::2 +DROP TABLE t1; +# +# Window functions +# +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; +a LAG(a) OVER (ORDER BY a) LEAD(a) OVER (ORDER BY a) +::1 NULL ::2 +::2 ::1 ::3 +::3 ::2 ::4 +::4 ::3 NULL +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; +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) +::1 ::1 ::2 +::2 ::1 ::3 +::3 ::2 ::4 +::4 ::3 ::4 +DROP TABLE t1; +# +# Prepared statements +# +EXECUTE IMMEDIATE 'CREATE TABLE t1 AS SELECT ? AS a' USING CAST('::' AS INET6); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` inet6 NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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; +a +:: +::1 +::2 +::3 +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING '::1'; +a +::1 +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING CAST('::2' AS INET6); +a +::2 +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING 0x00000000000000000000000000000003; +a +::3 +DROP TABLE t1; +# +# Character set and collation aggregation +# +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; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` varchar(39) DEFAULT NULL, + `c2` varchar(39) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` varchar(40) CHARACTER SET utf8 DEFAULT NULL, + `c2` varchar(40) CHARACTER SET utf8 DEFAULT NULL, + `c3` varchar(40) CHARACTER SET utf8 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` varchar(40) DEFAULT NULL, + `c2` varchar(40) DEFAULT NULL, + `c3` varchar(40) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +# +# UNION +# +CREATE TABLE t1 AS SELECT CAST('::' AS INET6) AS c UNION SELECT CAST('::1' AS INET6); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` inet6 NOT NULL DEFAULT '::' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT CAST('::' AS INET6) AS c UNION SELECT '::1'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` inet6 NOT NULL DEFAULT '::' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT '::' AS c UNION SELECT CAST('::1' AS INET6); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` inet6 NOT NULL DEFAULT '::' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT CAST('::' AS INET6) AS c UNION SELECT 0x00000000000000000000000000000001; +SELECT * FROM t1; +c +:: +::1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT CAST('::' AS INET6) AS c UNION SELECT 1; +ERROR HY000: Illegal parameter data types inet6 and int for operation 'UNION' +# +# Unary operators +# +SELECT -CAST('::' AS INET6); +ERROR HY000: Illegal parameter data type inet6 for operation '-' +SELECT ABS(CAST('::' AS INET6)); +ERROR HY000: Illegal parameter data type inet6 for operation 'abs' +SELECT ROUND(CAST('::' AS INET6)); +ERROR HY000: Illegal parameter data type inet6 for operation 'round' +SELECT CEILING(CAST('::' AS INET6)); +ERROR HY000: Illegal parameter data type inet6 for operation 'ceiling' +SELECT FLOOR(CAST('::' AS INET6)); +ERROR HY000: Illegal parameter data type inet6 for operation 'floor' +# +# Arithmetic operators +# +SELECT CAST('::' AS INET6) + 1; +ERROR HY000: Illegal parameter data types inet6 and int for operation '+' +SELECT CAST('::' AS INET6) - 1; +ERROR HY000: Illegal parameter data types inet6 and int for operation '-' +SELECT CAST('::' AS INET6) * 1; +ERROR HY000: Illegal parameter data types inet6 and int for operation '*' +SELECT CAST('::' AS INET6) / 1; +ERROR HY000: Illegal parameter data types inet6 and int for operation '/' +SELECT CAST('::' AS INET6) MOD 1; +ERROR HY000: Illegal parameter data types inet6 and int for operation 'MOD' +# +# Misc +# +SELECT RAND(CAST('::' AS INET6)); +ERROR HY000: Illegal parameter data type inet6 for operation 'rand' +SELECT FROM_UNIXTIME(CAST('::' AS INET6)); +ERROR HY000: Illegal parameter data type inet6 for operation 'from_unixtime' +SELECT HOUR(CAST('::' AS INET6)); +ERROR HY000: Illegal parameter data type inet6 for operation 'hour' +SELECT YEAR(CAST('::' AS INET6)); +ERROR HY000: Illegal parameter data type inet6 for operation 'year' +SELECT RELEASE_LOCK(CAST('::' AS INET6)); +ERROR HY000: Illegal parameter data type inet6 for operation 'release_lock' +SELECT JSON_LENGTH(CAST('::' AS INET6)); +JSON_LENGTH(CAST('::' AS INET6)) +NULL +Warnings: +Warning 4038 Syntax error in JSON text in argument 1 to function 'json_length' at position 1 +# +# Virtual columns +# +CREATE TABLE t1 ( +a INT, +b INET6 GENERATED ALWAYS AS (CAST(CONCAT(RAND(),a) AS INET6)), INDEX(b) +); +ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `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; +a b +0 :: +1 ::1 +2 ::2 +3 ::3 +4 ::4 +5 ::5 +6 ::6 +7 ::7 +8 ::8 +9 ::9 +10 ::a +11 ::b +12 ::c +13 ::d +14 ::e +15 ::f +DROP TABLE t1; +# +# VIEW +# +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; +a +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +15 +CREATE VIEW v1 AS SELECT (CAST(CONCAT('::',HEX(a)) AS INET6)) AS c FROM t1; +SELECT * FROM v1 ORDER BY c; +c +:: +::1 +::2 +::3 +::4 +::5 +::6 +::7 +::8 +::9 +::a +::b +::c +::d +::e +::f +DROP VIEW v1; +DROP TABLE t1; +CREATE TABLE t1 (a INET6 DEFAULT '::'); +CREATE VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci +DESCRIBE v1; +Field Type Null Key Default Extra +a inet6 YES :: +INSERT INTO v1 VALUES ('::'),('::1'),('::2'); +SELECT * FROM t1; +a +:: +::1 +::2 +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; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci +DESCRIBE v1; +Field Type Null Key Default Extra +a inet6 YES cast('::' as inet6) +INSERT INTO v1 VALUES ('::'),('::1'),('::2'); +SELECT * FROM t1; +a +:: +::1 +::2 +DROP VIEW v1; +DROP TABLE t1; +# +# Subqueries +# +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::1'),('::2'); +SELECT * FROM t1 WHERE a=(SELECT MIN(a) FROM t1) ORDER BY a; +a +:: +SELECT * FROM t1 WHERE a=(SELECT MAX(a) FROM t1) ORDER BY a; +a +::2 +SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 WHERE a>'::') ORDER BY a; +a +::1 +::2 +DROP TABLE t1; +# +# Stored routines +# +CREATE PROCEDURE p1(a INET6) +BEGIN +DECLARE b INET6 DEFAULT CONCAT('1', a); +SELECT a, b; +END; +$$ +CALL p1('::1'); +a b +::1 1::1 +CALL p1(CAST('::2' AS INET6)); +a b +::2 1::2 +DROP PROCEDURE p1; +CREATE FUNCTION f1(a INET6) RETURNS INET6 +BEGIN +RETURN CONCAT('1',a); +END; +$$ +SELECT f1('::1'); +f1('::1') +1::1 +SELECT f1(CAST('::1' AS INET6)); +f1(CAST('::1' AS INET6)) +1::1 +DROP FUNCTION f1; +# +# Anchored data types in SP variables +# +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::1'); +CREATE PROCEDURE p1() +BEGIN +DECLARE va TYPE OF t1.a; +SELECT MAX(a) INTO va FROM t1; +SELECT va; +END; +$$ +CALL p1; +va +::1 +DROP PROCEDURE p1; +DROP TABLE t1; +CREATE TABLE t1 (a INET6, b INET6); +INSERT INTO t1 VALUES ('::a', '::b'); +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; +$$ +CALL p1; +va.a va.b +::a ::b +DROP PROCEDURE p1; +DROP TABLE t1; +# +# Optimizer: make_const_item_for_comparison +# +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = INET6'::1' and `test`.`t1`.`id` > 0 +DROP TABLE t1; +# +# Optimizer: equal field propagation +# +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = INET6'::1' and octet_length(concat(INET6'::1',rand())) > 1 +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE a=COALESCE(CAST('::1' AS INET6)) +AND LENGTH(a)>1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = INET6'::1' +DROP TABLE t1; +# +# Optimizer: equal expression propagation +# +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); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '::1' and concat(`test`.`t1`.`a`) = '::1' +DROP TABLE t1; +# +# Subquery materialization +# +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); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED t1 index NULL a 17 NULL 2 Using index +EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT a AS a_inner FROM t1 GROUP BY a_inner); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t1 index_subquery a a 17 func 2 Using index; Using where +SET @@optimizer_switch=DEFAULT; +DROP TABLE t1; +# +# IS_IPV4_MAPPED(), IS_IPV4_COMPAT() now understand text notation +# +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'); +# This is a text notation +SELECT id, length(a), a, IS_IPV4_MAPPED(a) FROM t1 ORDER BY id; +id length(a) a IS_IPV4_MAPPED(a) +1 13 ::192.168.0.1 0 +2 16 ::192.168.10.111 0 +3 16 ::ffff:10.10.0.1 1 +4 18 ::ffff:192.168.0.1 1 +SELECT id, length(a), a, IS_IPV4_COMPAT(a) FROM t1 ORDER BY id; +id length(a) a IS_IPV4_COMPAT(a) +1 13 ::192.168.0.1 1 +2 16 ::192.168.10.111 1 +3 16 ::ffff:10.10.0.1 0 +4 18 ::ffff:192.168.0.1 0 +# 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; +id length(a) a IS_IPV4_MAPPED(BINARY a) +1 13 ::192.168.0.1 0 +2 16 ::192.168.10.111 0 +3 16 ::ffff:10.10.0.1 0 +4 18 ::ffff:192.168.0.1 0 +Warnings: +Warning 1292 Incorrect inet6 value: '::192.168.0.1' +Warning 1292 Incorrect inet6 value: '::ffff:192.168.0.1' +SELECT id, length(a), a, IS_IPV4_COMPAT(BINARY a) FROM t1 ORDER BY id; +id length(a) a IS_IPV4_COMPAT(BINARY a) +1 13 ::192.168.0.1 0 +2 16 ::192.168.10.111 0 +3 16 ::ffff:10.10.0.1 0 +4 18 ::ffff:192.168.0.1 0 +Warnings: +Warning 1292 Incorrect inet6 value: '::192.168.0.1' +Warning 1292 Incorrect inet6 value: '::ffff:192.168.0.1' +DROP TABLE t1; +# +# Limit clause parameter +# TODO: this should fail. +# The test for a valid data type should be moved +# from parse time to fix_fields() time, and performed +# for both Item_splocal and Item_param. +# +EXECUTE IMMEDIATE 'SELECT 1 FROM DUAL LIMIT ?' USING CAST('::' AS INET6); +1 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..a28caa4d7d3 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6.test @@ -0,0 +1,835 @@ + +--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 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; + + +--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 # 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 # 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...) +## diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_engines.inc b/plugin/type_inet/mysql-test/type_inet/type_inet6_engines.inc new file mode 100644 index 00000000000..596036fc0ee --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_engines.inc @@ -0,0 +1,38 @@ +--echo # +--echo # Range optimizer +--echo # + +CREATE TABLE t1 (a INET6, INDEX(a)); +SHOW CREATE TABLE t1; + +DELIMITER $$; +FOR i IN 0..255 +DO + INSERT INTO t1 VALUES (CONCAT('::', HEX(i))); +END FOR +$$ +DELIMITER ;$$ +SELECT * FROM t1 WHERE a='::ff'; +EXPLAIN SELECT * FROM t1 WHERE a='::ff'; +SELECT * FROM t1 WHERE a='garbage'; +EXPLAIN SELECT * FROM t1 WHERE a='garbage'; + +SELECT * FROM t1 WHERE a>='::fe'; +EXPLAIN SELECT * FROM t1 WHERE a>='::fe'; +SELECT * FROM t1 WHERE a>='garbage'; +EXPLAIN SELECT * FROM t1 WHERE a>='garbage'; + +SELECT * FROM t1 WHERE a IN ('::80','::a0','::f0'); +EXPLAIN SELECT * FROM t1 WHERE a IN ('::80','::a0','::f0'); +SELECT * FROM t1 WHERE a IN ('::80','::a0','garbage'); +EXPLAIN SELECT * FROM t1 WHERE a IN ('::80','::a0','garbage'); + +SELECT * FROM t1 WHERE a BETWEEN '::80' AND '::81'; +EXPLAIN SELECT * FROM t1 WHERE a BETWEEN '::80' AND '::81'; +SELECT * FROM t1 WHERE a BETWEEN '::80' AND 'garbage'; +EXPLAIN SELECT * FROM t1 WHERE a BETWEEN '::80' AND 'garbage'; + +SELECT * FROM t1 WHERE a=CAST('::ff' AS INET6); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST('::ff' AS INET6); + +DROP TABLE t1; diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_innodb.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_innodb.result new file mode 100644 index 00000000000..ad2d22c2153 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_innodb.result @@ -0,0 +1,104 @@ +# +# Start of 10.5 tests +# +# +# MDEV-274 The data type for IPv6/IPv4 addresses in MariaDB +# +SET default_storage_engine=InnoDB; +# +# Range optimizer +# +CREATE TABLE t1 (a INET6, INDEX(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` inet6 DEFAULT NULL, + KEY `a` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +FOR i IN 0..255 +DO +INSERT INTO t1 VALUES (CONCAT('::', HEX(i))); +END FOR +$$ +SELECT * FROM t1 WHERE a='::ff'; +a +::ff +EXPLAIN SELECT * FROM t1 WHERE a='::ff'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 17 const 1 Using where; Using index +SELECT * FROM t1 WHERE a='garbage'; +a +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +EXPLAIN SELECT * FROM t1 WHERE a='garbage'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +SELECT * FROM t1 WHERE a>='::fe'; +a +::fe +::ff +EXPLAIN SELECT * FROM t1 WHERE a>='::fe'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 17 NULL 2 Using where; Using index +SELECT * FROM t1 WHERE a>='garbage'; +a +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +EXPLAIN SELECT * FROM t1 WHERE a>='garbage'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +SELECT * FROM t1 WHERE a IN ('::80','::a0','::f0'); +a +::80 +::a0 +::f0 +EXPLAIN SELECT * FROM t1 WHERE a IN ('::80','::a0','::f0'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 17 NULL 3 Using where; Using index +SELECT * FROM t1 WHERE a IN ('::80','::a0','garbage'); +a +::80 +::a0 +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +EXPLAIN SELECT * FROM t1 WHERE a IN ('::80','::a0','garbage'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 17 NULL 2 Using where; Using index +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +SELECT * FROM t1 WHERE a BETWEEN '::80' AND '::81'; +a +::80 +::81 +EXPLAIN SELECT * FROM t1 WHERE a BETWEEN '::80' AND '::81'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 17 NULL 2 Using where; Using index +SELECT * FROM t1 WHERE a BETWEEN '::80' AND 'garbage'; +a +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +EXPLAIN SELECT * FROM t1 WHERE a BETWEEN '::80' AND 'garbage'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +SELECT * FROM t1 WHERE a=CAST('::ff' AS INET6); +a +::ff +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST('::ff' AS INET6); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref a a 17 const 1 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = INET6'::ff' +DROP TABLE t1; +# +# End of 10.5 tests +# diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_innodb.test b/plugin/type_inet/mysql-test/type_inet/type_inet6_innodb.test new file mode 100644 index 00000000000..dd6049abbf3 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_innodb.test @@ -0,0 +1,18 @@ +--source include/have_innodb.inc + +--echo # +--echo # Start of 10.5 tests +--echo # + +--echo # +--echo # MDEV-274 The data type for IPv6/IPv4 addresses in MariaDB +--echo # + + +SET default_storage_engine=InnoDB; +--source type_inet6_engines.inc + + +--echo # +--echo # End of 10.5 tests +--echo # diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_memory.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_memory.result new file mode 100644 index 00000000000..56c4ae8231d --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_memory.result @@ -0,0 +1,163 @@ +# +# Start of 10.5 tests +# +# +# MDEV-274 The data type for IPv6/IPv4 addresses in MariaDB +# +SET default_storage_engine=MEMORY; +# +# Range optimizer +# +CREATE TABLE t1 (a INET6, INDEX(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` inet6 DEFAULT NULL, + KEY `a` (`a`) +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +FOR i IN 0..255 +DO +INSERT INTO t1 VALUES (CONCAT('::', HEX(i))); +END FOR +$$ +SELECT * FROM t1 WHERE a='::ff'; +a +::ff +EXPLAIN SELECT * FROM t1 WHERE a='::ff'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 17 const 2 Using where +SELECT * FROM t1 WHERE a='garbage'; +a +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +EXPLAIN SELECT * FROM t1 WHERE a='garbage'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +SELECT * FROM t1 WHERE a>='::fe'; +a +::fe +::ff +EXPLAIN SELECT * FROM t1 WHERE a>='::fe'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL a NULL NULL NULL 256 Using where +SELECT * FROM t1 WHERE a>='garbage'; +a +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +EXPLAIN SELECT * FROM t1 WHERE a>='garbage'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL a NULL NULL NULL 256 Using where +SELECT * FROM t1 WHERE a IN ('::80','::a0','::f0'); +a +::80 +::a0 +::f0 +EXPLAIN SELECT * FROM t1 WHERE a IN ('::80','::a0','::f0'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 17 NULL 6 Using where +SELECT * FROM t1 WHERE a IN ('::80','::a0','garbage'); +a +::80 +::a0 +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +EXPLAIN SELECT * FROM t1 WHERE a IN ('::80','::a0','garbage'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 17 NULL 4 Using where +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +SELECT * FROM t1 WHERE a BETWEEN '::80' AND '::81'; +a +::80 +::81 +EXPLAIN SELECT * FROM t1 WHERE a BETWEEN '::80' AND '::81'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL a NULL NULL NULL 256 Using where +SELECT * FROM t1 WHERE a BETWEEN '::80' AND 'garbage'; +a +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' +EXPLAIN SELECT * FROM t1 WHERE a BETWEEN '::80' AND 'garbage'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL a NULL NULL NULL 256 Using where +SELECT * FROM t1 WHERE a=CAST('::ff' AS INET6); +a +::ff +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST('::ff' AS INET6); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref a a 17 const 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = INET6'::ff' +DROP TABLE t1; +# +# End of 10.5 tests +# diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_memory.test b/plugin/type_inet/mysql-test/type_inet/type_inet6_memory.test new file mode 100644 index 00000000000..ff503ce524d --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_memory.test @@ -0,0 +1,18 @@ +--source include/have_innodb.inc + +--echo # +--echo # Start of 10.5 tests +--echo # + +--echo # +--echo # MDEV-274 The data type for IPv6/IPv4 addresses in MariaDB +--echo # + + +SET default_storage_engine=MEMORY; +--source type_inet6_engines.inc + + +--echo # +--echo # End of 10.5 tests +--echo # diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_myisam.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_myisam.result new file mode 100644 index 00000000000..6525bda5aa5 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_myisam.result @@ -0,0 +1,104 @@ +# +# Start of 10.5 tests +# +# +# MDEV-274 The data type for IPv6/IPv4 addresses in MariaDB +# +SET default_storage_engine=MyISAM; +# +# Range optimizer +# +CREATE TABLE t1 (a INET6, INDEX(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` inet6 DEFAULT NULL, + KEY `a` (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +FOR i IN 0..255 +DO +INSERT INTO t1 VALUES (CONCAT('::', HEX(i))); +END FOR +$$ +SELECT * FROM t1 WHERE a='::ff'; +a +::ff +EXPLAIN SELECT * FROM t1 WHERE a='::ff'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 17 const 1 Using where; Using index +SELECT * FROM t1 WHERE a='garbage'; +a +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +EXPLAIN SELECT * FROM t1 WHERE a='garbage'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +SELECT * FROM t1 WHERE a>='::fe'; +a +::fe +::ff +EXPLAIN SELECT * FROM t1 WHERE a>='::fe'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 17 NULL 2 Using where; Using index +SELECT * FROM t1 WHERE a>='garbage'; +a +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +EXPLAIN SELECT * FROM t1 WHERE a>='garbage'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +SELECT * FROM t1 WHERE a IN ('::80','::a0','::f0'); +a +::80 +::a0 +::f0 +EXPLAIN SELECT * FROM t1 WHERE a IN ('::80','::a0','::f0'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 17 NULL 3 Using where; Using index +SELECT * FROM t1 WHERE a IN ('::80','::a0','garbage'); +a +::80 +::a0 +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +EXPLAIN SELECT * FROM t1 WHERE a IN ('::80','::a0','garbage'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 17 NULL 2 Using where; Using index +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +SELECT * FROM t1 WHERE a BETWEEN '::80' AND '::81'; +a +::80 +::81 +EXPLAIN SELECT * FROM t1 WHERE a BETWEEN '::80' AND '::81'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 17 NULL 2 Using where; Using index +SELECT * FROM t1 WHERE a BETWEEN '::80' AND 'garbage'; +a +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +EXPLAIN SELECT * FROM t1 WHERE a BETWEEN '::80' AND 'garbage'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Warning 1292 Incorrect inet6 value: 'garbage' for column `test`.`t1`.`a` at row 1 +SELECT * FROM t1 WHERE a=CAST('::ff' AS INET6); +a +::ff +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST('::ff' AS INET6); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref a a 17 const 1 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = INET6'::ff' +DROP TABLE t1; +# +# End of 10.5 tests +# diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_myisam.test b/plugin/type_inet/mysql-test/type_inet/type_inet6_myisam.test new file mode 100644 index 00000000000..232f874640a --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_myisam.test @@ -0,0 +1,18 @@ +--source include/have_innodb.inc + +--echo # +--echo # Start of 10.5 tests +--echo # + +--echo # +--echo # MDEV-274 The data type for IPv6/IPv4 addresses in MariaDB +--echo # + + +SET default_storage_engine=MyISAM; +--source type_inet6_engines.inc + + +--echo # +--echo # End of 10.5 tests +--echo # diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_mysql.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_mysql.result new file mode 100644 index 00000000000..468e9fea41e --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_mysql.result @@ -0,0 +1,15 @@ +CREATE TABLE t1 (a INET6); +Field 1: `a` +Catalog: `def` +Database: `test` +Table: `t1` +Org_table: `t1` +Type: STRING +Collation: latin1_swedish_ci (8) +Length: 39 +Max_length: 0 +Decimals: 0 +Flags: UNSIGNED BINARY + + +DROP TABLE t1; diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_mysql.test b/plugin/type_inet/mysql-test/type_inet/type_inet6_mysql.test new file mode 100644 index 00000000000..5e6ac6f3804 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_mysql.test @@ -0,0 +1,6 @@ +-- source include/have_working_dns.inc +-- source include/not_embedded.inc + +CREATE TABLE t1 (a INET6); +--exec $MYSQL -t test --column-type-info -e "SELECT * FROM t1" 2>&1 +DROP TABLE t1; diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_plugin.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_plugin.result new file mode 100644 index 00000000000..8369ef07de8 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_plugin.result @@ -0,0 +1,31 @@ +# +# Start of 10.5 tests +# +# +# MDEV-274 The data type for IPv6/IPv4 addresses in MariaDB +# +SELECT +PLUGIN_NAME, +PLUGIN_VERSION, +PLUGIN_STATUS, +PLUGIN_TYPE, +PLUGIN_AUTHOR, +PLUGIN_DESCRIPTION, +PLUGIN_LICENSE, +PLUGIN_MATURITY, +PLUGIN_AUTH_VERSION +FROM INFORMATION_SCHEMA.PLUGINS +WHERE PLUGIN_TYPE='DATA TYPE' + AND PLUGIN_NAME='inet6'; +PLUGIN_NAME inet6 +PLUGIN_VERSION 1.0 +PLUGIN_STATUS ACTIVE +PLUGIN_TYPE DATA TYPE +PLUGIN_AUTHOR MariaDB Corporation +PLUGIN_DESCRIPTION Data type TEST_INT8 +PLUGIN_LICENSE GPL +PLUGIN_MATURITY Experimental +PLUGIN_AUTH_VERSION 1.0 +# +# End of 10.5 tests +# diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_plugin.test b/plugin/type_inet/mysql-test/type_inet/type_inet6_plugin.test new file mode 100644 index 00000000000..ccc22b16f4f --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_plugin.test @@ -0,0 +1,27 @@ +--echo # +--echo # Start of 10.5 tests +--echo # + +--echo # +--echo # MDEV-274 The data type for IPv6/IPv4 addresses in MariaDB +--echo # + +--vertical_results +SELECT + PLUGIN_NAME, + PLUGIN_VERSION, + PLUGIN_STATUS, + PLUGIN_TYPE, + PLUGIN_AUTHOR, + PLUGIN_DESCRIPTION, + PLUGIN_LICENSE, + PLUGIN_MATURITY, + PLUGIN_AUTH_VERSION +FROM INFORMATION_SCHEMA.PLUGINS + WHERE PLUGIN_TYPE='DATA TYPE' + AND PLUGIN_NAME='inet6'; +--horizontal_results + +--echo # +--echo # End of 10.5 tests +--echo # diff --git a/plugin/type_inet/plugin.cc b/plugin/type_inet/plugin.cc index f76a786573b..6623492bf01 100644 --- a/plugin/type_inet/plugin.cc +++ b/plugin/type_inet/plugin.cc @@ -24,6 +24,16 @@ #include <mysql/plugin_function_collection.h> +Type_handler_inet6 type_handler_inet6; + + +static struct st_mariadb_data_type plugin_descriptor_type_inet6= +{ + MariaDB_DATA_TYPE_INTERFACE_VERSION, + &type_handler_inet6 +}; + + /*************************************************************************/ class Create_func_inet_ntoa : public Create_func_arg1 @@ -186,5 +196,20 @@ maria_declare_plugin(type_inet) NULL, // System variables "1.0", // String version representation MariaDB_PLUGIN_MATURITY_EXPERIMENTAL // Maturity(see include/mysql/plugin.h)*/ +}, +{ + MariaDB_DATA_TYPE_PLUGIN, // the plugin type (see include/mysql/plugin.h) + &plugin_descriptor_type_inet6,// pointer to type-specific plugin descriptor + type_handler_inet6.name().ptr(),// plugin name + "MariaDB Corporation", // plugin author + "Data type TEST_INT8", // the plugin description + PLUGIN_LICENSE_GPL, // the plugin license (see include/mysql/plugin.h) + 0, // Pointer to plugin initialization function + 0, // Pointer to plugin deinitialization function + 0x0100, // Numeric version 0xAABB means AA.BB veriosn + NULL, // Status variables + NULL, // System variables + "1.0", // String version representation + MariaDB_PLUGIN_MATURITY_EXPERIMENTAL // Maturity(see include/mysql/plugin.h)*/ } maria_declare_plugin_end; diff --git a/plugin/type_inet/sql_type_inet.cc b/plugin/type_inet/sql_type_inet.cc index 66db71b7683..cb66b479cae 100644 --- a/plugin/type_inet/sql_type_inet.cc +++ b/plugin/type_inet/sql_type_inet.cc @@ -510,6 +510,17 @@ size_t Inet6::to_string(char *dst, size_t dstsize) const bool Inet6::make_from_item(Item *item) { + if (item->type_handler() == &type_handler_inet6) + { + Native tmp(m_buffer, sizeof(m_buffer)); + bool rc= item->val_native(current_thd, &tmp); + if (rc) + return true; + DBUG_ASSERT(tmp.length() == sizeof(m_buffer)); + if (tmp.ptr() != m_buffer) + memcpy(m_buffer, tmp.ptr(), sizeof(m_buffer)); + return false; + } StringBufferInet6 tmp; String *str= item->val_str(&tmp); return str ? make_from_character_or_binary_string(str) : true; @@ -518,7 +529,7 @@ bool Inet6::make_from_item(Item *item) bool Inet6::make_from_character_or_binary_string(const String *str) { - static Name name= Name(STRING_WITH_LEN("inet6")); + static Name name= type_handler_inet6.name(); if (str->charset() != &my_charset_bin) { bool rc= character_string_to_ipv6(str->ptr(), str->length(), @@ -540,3 +551,910 @@ bool Inet6::make_from_character_or_binary_string(const String *str) memcpy(m_buffer, str->ptr(), sizeof(m_buffer)); return false; }; + + +/********************************************************************/ + + +class cmp_item_inet6: public cmp_item_scalar +{ + Inet6 m_native; +public: + cmp_item_inet6() + :cmp_item_scalar(), + m_native(Inet6_zero()) + { } + void store_value(Item *item) override + { + m_native= Inet6(item, &m_null_value); + } + int cmp_not_null(const Value *val) override + { + DBUG_ASSERT(!val->is_null()); + DBUG_ASSERT(val->is_string()); + Inet6_null tmp(val->m_string); + DBUG_ASSERT(!tmp.is_null()); + return m_native.cmp(tmp); + } + int cmp(Item *arg) override + { + Inet6_null tmp(arg); + return m_null_value || tmp.is_null() ? UNKNOWN : m_native.cmp(tmp) != 0; + } + int compare(cmp_item *ci) override + { + cmp_item_inet6 *tmp= static_cast<cmp_item_inet6*>(ci); + DBUG_ASSERT(!m_null_value); + DBUG_ASSERT(!tmp->m_null_value); + return m_native.cmp(tmp->m_native); + } + cmp_item *make_same() override + { + return new cmp_item_inet6(); + } +}; + + +class Field_inet6: public Field +{ + static void set_min_value(char *ptr) + { + memset(ptr, 0, Inet6::binary_length()); + } + static void set_max_value(char *ptr) + { + memset(ptr, 0xFF, Inet6::binary_length()); + } + void store_warning(const ErrConv &str, + Sql_condition::enum_warning_level level) + { + static const Name type_name= type_handler_inet6.name(); + get_thd()->push_warning_truncated_value_for_field(level, type_name.ptr(), + str.ptr(), table->s, + field_name.str); + } + int set_null_with_warn(const ErrConv &str) + { + store_warning(str, Sql_condition::WARN_LEVEL_WARN); + set_null(); + return 1; + } + int set_min_value_with_warn(const ErrConv &str) + { + store_warning(str, Sql_condition::WARN_LEVEL_WARN); + set_min_value((char*) ptr); + return 1; + } + int set_max_value_with_warn(const ErrConv &str) + { + store_warning(str, Sql_condition::WARN_LEVEL_WARN); + set_max_value((char*) ptr); + return 1; + } + +public: + Field_inet6(const LEX_CSTRING *field_name_arg, const Record_addr &rec) + :Field(rec.ptr(), Inet6::max_char_length(), + rec.null_ptr(), rec.null_bit(), Field::NONE, field_name_arg) + { + flags|= BINARY_FLAG | UNSIGNED_FLAG; + } + const Type_handler *type_handler() const override + { + return &type_handler_inet6; + } + uint32 max_display_length() const override { return field_length; } + bool str_needs_quotes() const override { return true; } + const DTCollation &dtcollation() const override + { + static DTCollation_numeric c; + return c; + } + CHARSET_INFO *charset(void) const override { return &my_charset_numeric; } + const CHARSET_INFO *sort_charset(void) const override { return &my_charset_bin; } + /** + This makes client-server protocol convert the value according + to @@character_set_client. + */ + bool binary() const override { return false; } + enum ha_base_keytype key_type() const override { return HA_KEYTYPE_BINARY; } + + bool is_equal(const Column_definition &new_field) const override + { + return new_field.type_handler() == type_handler(); + } + bool eq_def(const Field *field) const override + { + return Field::eq_def(field); + } + double pos_in_interval(Field *min, Field *max) override + { + return pos_in_interval_val_str(min, max, 0); + } + int cmp(const uchar *a, const uchar *b) const override + { return memcmp(a, b, pack_length()); } + + void sort_string(uchar *to, uint length) override + { + DBUG_ASSERT(length == pack_length()); + memcpy(to, ptr, length); + } + uint32 pack_length() const override + { + return Inet6::binary_length(); + } + uint pack_length_from_metadata(uint field_metadata) const override + { + return Inet6::binary_length(); + } + + void sql_type(String &str) const override + { + static Name name= type_handler_inet6.name(); + str.set_ascii(name.ptr(), name.length()); + } + + bool validate_value_in_record(THD *thd, const uchar *record) const override + { + return false; + } + + String *val_str(String *val_buffer, + String *val_ptr __attribute__((unused))) override + { + DBUG_ASSERT(marked_for_read()); + Inet6_null tmp((const char *) ptr, pack_length()); + return tmp.to_string(val_buffer) ? NULL : val_buffer; + } + + my_decimal *val_decimal(my_decimal *to) override + { + DBUG_ASSERT(marked_for_read()); + my_decimal_set_zero(to); + return to; + } + + longlong val_int() override + { + DBUG_ASSERT(marked_for_read()); + return 0; + } + + double val_real() override + { + DBUG_ASSERT(marked_for_read()); + return 0; + } + + bool get_date(MYSQL_TIME *ltime, date_mode_t fuzzydate) override + { + DBUG_ASSERT(marked_for_read()); + set_zero_time(ltime, MYSQL_TIMESTAMP_TIME); + return false; + } + + bool val_bool(void) override + { + DBUG_ASSERT(marked_for_read()); + return !Inet6::only_zero_bytes((const char *) ptr, Inet6::binary_length()); + } + + int store_native(const Native &value) override + { + DBUG_ASSERT(marked_for_write_or_computed()); + DBUG_ASSERT(value.length() == Inet6::binary_length()); + memcpy(ptr, value.ptr(), value.length()); + return 0; + } + + int store(const char *str, size_t length, CHARSET_INFO *cs) override + { + DBUG_ASSERT(marked_for_write_or_computed()); + Inet6_null tmp= cs == &my_charset_bin ? + Inet6_null(str, length) : + Inet6_null(str, length, cs); + if (tmp.is_null()) + { + return maybe_null() ? + set_null_with_warn(ErrConvString(str, length, cs)) : + set_min_value_with_warn(ErrConvString(str, length, cs)); + } + tmp.to_binary((char *) ptr, Inet6::binary_length()); + return 0; + } + + int store_hex_hybrid(const char *str, size_t length) override + { + return store(str, length, &my_charset_bin); + } + + int store_decimal(const my_decimal *num) override + { + DBUG_ASSERT(marked_for_write_or_computed()); + return set_min_value_with_warn(ErrConvDecimal(num)); + } + + int store(longlong nr, bool unsigned_flag) override + { + DBUG_ASSERT(marked_for_write_or_computed()); + return set_min_value_with_warn( + ErrConvInteger(Longlong_hybrid(nr, unsigned_flag))); + } + + int store(double nr) override + { + DBUG_ASSERT(marked_for_write_or_computed()); + return set_min_value_with_warn(ErrConvDouble(nr)); + } + + int store_time_dec(const MYSQL_TIME *ltime, uint dec) override + { + DBUG_ASSERT(marked_for_write_or_computed()); + return set_min_value_with_warn(ErrConvTime(ltime)); + } + + /*** Field conversion routines ***/ + int store_field(Field *from) override + { + // INSERT INTO t1 (inet6_field) SELECT different_field_type FROM t2; + return from->save_in_field(this); + } + int save_in_field(Field *to) override + { + // INSERT INTO t2 (different_field_type) SELECT inet6_field FROM t1; + switch (to->cmp_type()) { + case INT_RESULT: + case REAL_RESULT: + case DECIMAL_RESULT: + case TIME_RESULT: + { + my_decimal buff; + return to->store_decimal(val_decimal(&buff)); + } + case STRING_RESULT: + return save_in_field_str(to); + case ROW_RESULT: + break; + } + DBUG_ASSERT(0); + to->reset(); + return 0; + } + Copy_func *get_copy_func(const Field *from) const override + { + // ALTER to INET6 from another field + /* + if (eq_def(from)) + return get_identical_copy_func(); + switch (from->cmp_type()) { + case STRING_RESULT: + return do_field_string; + case TIME_RESULT: + return do_field_temporal; + case DECIMAL_RESULT: + return do_field_decimal; + case REAL_RESULT: + return do_field_real; + case INT_RESULT: + return do_field_int; + case ROW_RESULT: + DBUG_ASSERT(0); + break; + } + */ + return do_field_string;//QQ + } + + bool memcpy_field_possible(const Field *from) const override + { + // INSERT INTO t1 (inet6_field) SELECT field2 FROM t2; + return type_handler() == from->type_handler(); + } + enum_conv_type rpl_conv_type_from(const Conv_source &source, + const Relay_log_info *rli, + const Conv_param ¶m) const + { + if (type_handler() == source.type_handler()) + return rpl_conv_type_from_same_data_type(source.metadata(), rli, param); + return CONV_TYPE_IMPOSSIBLE; + } + + /*** Optimizer routines ***/ + bool test_if_equality_guarantees_uniqueness(const Item *const_item) const override + { + /* + This condition: + WHERE inet6_field=const + should return a single distinct value only, + as comparison is done according to INET6. + */ + return true; + } + bool can_be_substituted_to_equal_item(const Context &ctx, + const Item_equal *item_equal) + override + { + switch (ctx.subst_constraint()) { + case ANY_SUBST: + return ctx.compare_type_handler() == item_equal->compare_type_handler(); + case IDENTITY_SUBST: + return true; + } + return false; + } + Item *get_equal_const_item(THD *thd, const Context &ctx, + Item *const_item) override; + bool can_optimize_keypart_ref(const Item_bool_func *cond, + const Item *item) const override + { + /* + Mixing of two different non-traditional types is currently prevented. + This may change in the future. For example, INET4 and INET6 + data types can be made comparable. + */ + DBUG_ASSERT(item->type_handler()->is_traditional_scalar_type() || + item->type_handler() == type_handler()); + return true; + } + /** + Test if Field can use range optimizer for a standard comparison operation: + <=, <, =, <=>, >, >= + Note, this method does not cover spatial operations. + */ + bool can_optimize_range(const Item_bool_func *cond, + const Item *item, + bool is_eq_func) const override + { + // See the DBUG_ASSERT comment in can_optimize_keypart_ref() + DBUG_ASSERT(item->type_handler()->is_traditional_scalar_type() || + item->type_handler() == type_handler()); + return true; + } + SEL_ARG *get_mm_leaf(RANGE_OPT_PARAM *prm, KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, Item *value) override + { + DBUG_ENTER("Field_inet6::get_mm_leaf"); + if (!can_optimize_scalar_range(prm, key_part, cond, op, value)) + DBUG_RETURN(0); + int err= value->save_in_field_no_warnings(this, 1); + if ((op != SCALAR_CMP_EQUAL && is_real_null()) || err < 0) + DBUG_RETURN(&null_element); + if (err > 0) + { + if (op == SCALAR_CMP_EQ || op == SCALAR_CMP_EQUAL) + DBUG_RETURN(new (prm->mem_root) SEL_ARG_IMPOSSIBLE(this)); + DBUG_RETURN(NULL); /* Cannot infer anything */ + } + DBUG_RETURN(stored_field_make_mm_leaf(prm, key_part, op, value)); + } + bool can_optimize_hash_join(const Item_bool_func *cond, + const Item *item) const override + { + return can_optimize_keypart_ref(cond, item); + } + bool can_optimize_group_min_max(const Item_bool_func *cond, + const Item *const_item) const override + { + return true; + } + + /**********/ + uint size_of() const override { return sizeof(*this); } +}; + + +class Item_typecast_inet6: public Item_func +{ +public: + Item_typecast_inet6(THD *thd, Item *a) :Item_func(thd, a) {} + + const Type_handler *type_handler() const override + { return &type_handler_inet6; } + + enum Functype functype() const override { return CHAR_TYPECAST_FUNC; } + bool eq(const Item *item, bool binary_cmp) const override + { + if (this == item) + return true; + if (item->type() != FUNC_ITEM || + functype() != ((Item_func*)item)->functype()) + return false; + if (type_handler() != item->type_handler()) + return false; + Item_typecast_inet6 *cast= (Item_typecast_inet6*) item; + return args[0]->eq(cast->args[0], binary_cmp); + } + const char *func_name() const override { return "cast_as_inet6"; } + void print(String *str, enum_query_type query_type) + { + str->append(STRING_WITH_LEN("cast(")); + args[0]->print(str, query_type); + str->append(STRING_WITH_LEN(" as inet6)")); + } + bool fix_length_and_dec() + { + Type_std_attributes::operator=(Type_std_attributes_inet6()); + return false; + } + String *val_str(String *to) + { + Inet6_null tmp(args[0]); + return (null_value= tmp.is_null() || tmp.to_string(to)) ? NULL : to; + } + longlong val_int() + { + return 0; + } + double val_real() + { + return 0; + } + my_decimal *val_decimal(my_decimal *to) + { + my_decimal_set_zero(to); + return to; + } + bool get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate) + { + set_zero_time(ltime, MYSQL_TIMESTAMP_TIME); + return false; + } + bool val_native(THD *thd, Native *to) + { + Inet6_null tmp(args[0]); + return null_value= tmp.is_null() || tmp.to_native(to); + } + Item *get_copy(THD *thd) + { return get_item_copy<Item_typecast_inet6>(thd, this); } +}; + + +class Item_cache_inet6: public Item_cache +{ + NativeBufferInet6 m_value; +public: + Item_cache_inet6(THD *thd) + :Item_cache(thd, &type_handler_inet6) + { } + Item *get_copy(THD *thd) + { return get_item_copy<Item_cache_inet6>(thd, this); } + bool cache_value() + { + if (!example) + return false; + value_cached= true; + null_value= example->val_native_with_conversion_result(current_thd, + &m_value, + type_handler()); + return true; + } + String* val_str(String *to) + { + if (!has_value()) + return NULL; + Inet6_null tmp(m_value.ptr(), m_value.length()); + return tmp.is_null() || tmp.to_string(to) ? NULL : to; + } + my_decimal *val_decimal(my_decimal *to) + { + if (!has_value()) + return NULL; + my_decimal_set_zero(to); + return to; + } + longlong val_int() + { + if (!has_value()) + return 0; + return 0; + } + double val_real() + { + if (!has_value()) + return 0; + return 0; + } + longlong val_datetime_packed(THD *thd) + { + DBUG_ASSERT(0); + if (!has_value()) + return 0; + return 0; + } + longlong val_time_packed(THD *thd) + { + DBUG_ASSERT(0); + if (!has_value()) + return 0; + return 0; + } + bool get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate) + { + if (!has_value()) + return true; + set_zero_time(ltime, MYSQL_TIMESTAMP_TIME); + return false; + } + bool val_native(THD *thd, Native *to) + { + if (!has_value()) + return true; + return to->copy(m_value.ptr(), m_value.length()); + } +}; + + +class Item_literal_inet6: public Item_literal +{ + Inet6 m_value; +public: + Item_literal_inet6(THD *thd) + :Item_literal(thd), + m_value(Inet6_zero()) + { } + Item_literal_inet6(THD *thd, const Inet6 &value) + :Item_literal(thd), + m_value(value) + { } + const Type_handler *type_handler() const override + { + return &type_handler_inet6; + } + longlong val_int() override + { + return 0; + } + double val_real() override + { + return 0; + } + String *val_str(String *to) override + { + return m_value.to_string(to) ? NULL : to; + } + my_decimal *val_decimal(my_decimal *to) override + { + my_decimal_set_zero(to); + return to; + } + bool get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate) override + { + set_zero_time(ltime, MYSQL_TIMESTAMP_TIME); + return false; + } + bool val_native(THD *thd, Native *to) override + { + return m_value.to_native(to); + } + void print(String *str, enum_query_type query_type) override + { + StringBufferInet6 tmp; + m_value.to_string(&tmp); + str->append("INET6'"); + str->append(tmp); + str->append('\''); + } + Item *get_copy(THD *thd) override + { return get_item_copy<Item_literal_inet6>(thd, this); } + + // Non-overriding methods + void set_value(const Inet6 &value) + { + m_value= value; + } +}; + + +class in_inet6 :public in_vector +{ + Inet6 m_value; + static int cmp_inet6(void *cmp_arg, Inet6 *a, Inet6 *b) + { + return a->cmp(*b); + } +public: + in_inet6(THD *thd, uint elements) + :in_vector(thd, elements, sizeof(Inet6), (qsort2_cmp) cmp_inet6, 0), + m_value(Inet6_zero()) + { } + const Type_handler *type_handler() const override + { + return &type_handler_inet6; + } + void set(uint pos, Item *item) override + { + Inet6 *buff= &((Inet6 *) base)[pos]; + Inet6_null value(item); + if (value.is_null()) + *buff= Inet6_zero(); + else + *buff= value; + } + uchar *get_value(Item *item) override + { + Inet6_null value(item); + if (value.is_null()) + return 0; + m_value= value; + return (uchar *) &m_value; + } + Item* create_item(THD *thd) override + { + return new (thd->mem_root) Item_literal_inet6(thd); + } + void value_to_item(uint pos, Item *item) override + { + const Inet6 &buff= (((Inet6*) base)[pos]); + static_cast<Item_literal_inet6*>(item)->set_value(buff); + } +}; + + +bool +Type_handler_inet6::character_or_binary_string_to_native(THD *thd, + const String *str, + Native *to) const +{ + if (str->charset() == &my_charset_bin) + { + // Convert from a binary string + if (str->length() != Inet6::binary_length() || + to->copy(str->ptr(), str->length())) + { + thd->push_warning_wrong_value(Sql_condition::WARN_LEVEL_WARN, + name().ptr(), + ErrConvString(str).ptr()); + return true; + } + return false; + } + // Convert from a character string + Inet6_null tmp(*str); + if (tmp.is_null()) + thd->push_warning_wrong_value(Sql_condition::WARN_LEVEL_WARN, + name().ptr(), + ErrConvString(str).ptr()); + return tmp.is_null() || tmp.to_native(to); +} + + +bool +Type_handler_inet6::Item_save_in_value(THD *thd, + Item *item, + st_value *value) const +{ + value->m_type= DYN_COL_STRING; + String *str= item->val_str(&value->m_string); + if (str != &value->m_string && !item->null_value) + { + // "item" returned a non-NULL value + if (Inet6_null(*str).is_null()) + { + /* + The value was not-null, but conversion to INET6 failed: + SELECT a, DECODE_ORACLE(inet6col, 'garbage', '<NULL>', '::01', '01') + FROM t1; + */ + thd->push_warning_wrong_value(Sql_condition::WARN_LEVEL_WARN, + name().ptr(), + ErrConvString(str).ptr()); + value->m_type= DYN_COL_NULL; + return true; + } + // "item" returned a non-NULL value, and it was a valid INET6 + value->m_string.set(str->ptr(), str->length(), str->charset()); + } + return check_null(item, value); +} + + +void Type_handler_inet6::Item_param_setup_conversion(THD *thd, + Item_param *param) const +{ + param->setup_conversion_string(thd, thd->variables.character_set_client); +} + + +void Type_handler_inet6::make_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const +{ + DBUG_ASSERT(item->type_handler() == this); + NativeBufferInet6 tmp; + item->val_native_result(current_thd, &tmp); + if (item->maybe_null) + { + if (item->null_value) + { + memset(to, 0, Inet6::binary_length() + 1); + return; + } + *to++= 1; + } + DBUG_ASSERT(!item->null_value); + DBUG_ASSERT(Inet6::binary_length() == tmp.length()); + DBUG_ASSERT(Inet6::binary_length() == sort_field->length); + memcpy(to, tmp.ptr(), tmp.length()); +} + + +void Type_handler_inet6::sortlength(THD *thd, + const Type_std_attributes *item, + SORT_FIELD_ATTR *attr) const +{ + attr->length= Inet6::binary_length(); + attr->suffix_length= 0; +} + + +cmp_item *Type_handler_inet6::make_cmp_item(THD *thd, CHARSET_INFO *cs) const +{ + return new (thd->mem_root) cmp_item_inet6; +} + + + +in_vector * +Type_handler_inet6::make_in_vector(THD *thd, const Item_func_in *func, + uint nargs) const +{ + return new (thd->mem_root) in_inet6(thd, nargs); +} + + +Item *Type_handler_inet6::create_typecast_item(THD *thd, Item *item, + const Type_cast_attributes &attr) + const +{ + return new (thd->mem_root) Item_typecast_inet6(thd, item); +} + + +Item_cache *Type_handler_inet6::Item_get_cache(THD *thd, const Item *item) const +{ + return new (thd->mem_root) Item_cache_inet6(thd); +} + + +Item * +Type_handler_inet6::make_const_item_for_comparison(THD *thd, + Item *src, + const Item *cmp) const +{ + Inet6_null tmp(src); + if (tmp.is_null()) + return new (thd->mem_root) Item_null(thd, src->name.str); + return new (thd->mem_root) Item_literal_inet6(thd, tmp); +} + + +Item *Field_inet6::get_equal_const_item(THD *thd, const Context &ctx, + Item *const_item) +{ + Inet6_null tmp(const_item); + if (tmp.is_null()) + return NULL; + return new (thd->mem_root) Item_literal_inet6(thd, tmp); +} + + +Field * +Type_handler_inet6::make_table_field_from_def( + TABLE_SHARE *share, + MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &addr, + const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) Field_inet6(name, addr); +} + + +Field *Type_handler_inet6::make_table_field(MEM_ROOT *root, + const LEX_CSTRING *name, + const Record_addr &addr, + const Type_all_attributes &attr, + TABLE_SHARE *share) const +{ + return new (root) Field_inet6(name, addr); +} + + +Field *Type_handler_inet6::make_conversion_table_field(MEM_ROOT *root, + TABLE *table, + uint metadata, + const Field *target) + const +{ + const Record_addr tmp(NULL, Bit_addr(true)); + return new (table->in_use->mem_root) Field_inet6(&empty_clex_str, tmp); +} + + +/***************************************************************/ + + +class Type_collection_inet: public Type_collection +{ + const Type_handler *aggregate_common(const Type_handler *a, + const Type_handler *b) const + { + if (a == b) + return a; + return NULL; + } + const Type_handler *aggregate_if_string(const Type_handler *a, + const Type_handler *b) const + { + static const Type_aggregator::Pair agg[]= + { + {&type_handler_inet6, &type_handler_null, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_varchar, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_string, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_tiny_blob, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_blob, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_medium_blob, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_long_blob, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_hex_hybrid, &type_handler_inet6}, + {NULL,NULL,NULL} + }; + return Type_aggregator::find_handler_in_array(agg, a, b, true); + } +public: + const Type_handler *aggregate_for_result(const Type_handler *a, + const Type_handler *b) + const override + { + const Type_handler *h; + if ((h= aggregate_common(a, b)) || + (h= aggregate_if_string(a, b))) + return h; + return NULL; + } + + const Type_handler *aggregate_for_min_max(const Type_handler *a, + const Type_handler *b) + const override + { + return aggregate_for_result(a, b); + } + + const Type_handler *aggregate_for_comparison(const Type_handler *a, + const Type_handler *b) + const override + { + if (const Type_handler *h= aggregate_common(a, b)) + return h; + static const Type_aggregator::Pair agg[]= + { + {&type_handler_inet6, &type_handler_null, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_long_blob, &type_handler_inet6}, + {NULL,NULL,NULL} + }; + return Type_aggregator::find_handler_in_array(agg, a, b, true); + } + + const Type_handler *aggregate_for_num_op(const Type_handler *a, + const Type_handler *b) + const override + { + return NULL; + } + + const Type_handler *handler_by_name(const LEX_CSTRING &name) const override + { + if (type_handler_inet6.name().eq(name)) + return &type_handler_inet6; + return NULL; + } +}; + + +const Type_collection *Type_handler_inet6::type_collection() const +{ + static Type_collection_inet type_collection_inet; + return &type_collection_inet; +} diff --git a/plugin/type_inet/sql_type_inet.h b/plugin/type_inet/sql_type_inet.h index db3349c2581..29dee0c1846 100644 --- a/plugin/type_inet/sql_type_inet.h +++ b/plugin/type_inet/sql_type_inet.h @@ -300,4 +300,680 @@ public: }; +class Type_std_attributes_inet6: public Type_std_attributes +{ +public: + Type_std_attributes_inet6() + :Type_std_attributes( + Type_numeric_attributes(Inet6::max_char_length(), 0, true), + DTCollation_numeric()) + { } +}; + + +class Type_handler_inet6: public Type_handler +{ + bool character_or_binary_string_to_native(THD *thd, const String *str, + Native *to) const; +public: + ~Type_handler_inet6() override {} + + const Name name() const override + { + static const Name name(STRING_WITH_LEN("inet6")); + return name; + } + const Type_collection *type_collection() const override; + const Name &default_value() const override + { + static Name def(STRING_WITH_LEN("::")); + return def; + } + protocol_send_type_t protocol_send_type() const override + { + return PROTOCOL_SEND_STRING; + } + + enum_field_types field_type() const override + { + return MYSQL_TYPE_STRING; + } + + Item_result result_type() const override + { + return STRING_RESULT; + } + + Item_result cmp_type() const override + { + return STRING_RESULT; + } + + enum_dynamic_column_type dyncol_type(const Type_all_attributes *attr) + const override + { + return DYN_COL_STRING; + } + + uint32 max_display_length_for_field(const Conv_source &src) const + { + return Inet6::max_char_length(); + } + + const Type_handler *type_handler_for_comparison() const override + { + return this; + } + + int + stored_field_cmp_to_item(THD *thd, Field *field, Item *item) const override + { + DBUG_ASSERT(field->type_handler() == this); + Inet6_null ni(item); // Convert Item to INET6 + if (ni.is_null()) + return 0; + NativeBufferInet6 tmp; + if (field->val_native(&tmp)) + { + DBUG_ASSERT(0); + return 0; + } + return -ni.cmp(tmp); + } + CHARSET_INFO *charset_for_protocol(const Item *item) const override + { + return item->collation.collation; + } + + bool is_scalar_type() const override { return true; } + bool can_return_int() const override { return false; } + bool can_return_decimal() const override { return false; } + bool can_return_real() const override { return false; } + bool can_return_str() const override { return true; } + bool can_return_text() const override { return true; } + bool can_return_date() const override { return false; } + bool can_return_time() const override { return false; } + + uint Item_time_precision(THD *thd, Item *item) const override + { + return 0; + } + uint Item_datetime_precision(THD *thd, Item *item) const override + { + return 0; + } + uint Item_decimal_scale(const Item *item) const override + { + return 0; + } + uint Item_decimal_precision(const Item *item) const override + { + /* + This will be needed if we ever allow cast from INET6 to DECIMAL. + Decimal precision of INET6 is 39 digits: + 'ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff' = + 340282366920938463463374607431768211456 = 39 digits + */ + return 39; + } + + /* + Returns how many digits a divisor adds into a division result. + See Item::divisor_precision_increment() in item.h for more comments. + */ + uint Item_divisor_precision_increment(const Item *) const override + { + return 0; + } + /** + Makes a temporary table Field to handle numeric aggregate functions, + e.g. SUM(DISTINCT expr), AVG(DISTINCT expr), etc. + */ + Field *make_num_distinct_aggregator_field(MEM_ROOT *, + const Item *) const override + { + DBUG_ASSERT(0); + return 0; + } + Field *make_conversion_table_field(MEM_ROOT *root, + TABLE *TABLE, + uint metadata, + const Field *target) const override; + // Fix attributes after the parser + bool Column_definition_fix_attributes(Column_definition *c) const override + { + c->length= Inet6::max_char_length(); + return false; + } + + bool Column_definition_prepare_stage1(THD *thd, + MEM_ROOT *mem_root, + Column_definition *def, + handler *file, + ulonglong table_flags) const override + { + def->create_length_to_internal_length_simple(); + return false; + } + + bool Column_definition_redefine_stage1(Column_definition *def, + const Column_definition *dup, + const handler *file, + const Schema_specification_st *schema) + const override + { + def->redefine_stage1_common(dup, file, schema); + def->set_compression_method(dup->compression_method()); + def->create_length_to_internal_length_string(); + return false; + } + + bool Column_definition_prepare_stage2(Column_definition *def, + handler *file, + ulonglong table_flags) const override + { + def->pack_flag= FIELDFLAG_BINARY; + return false; + } + + Field *make_table_field(MEM_ROOT *root, + const LEX_CSTRING *name, + const Record_addr &addr, + const Type_all_attributes &attr, + TABLE_SHARE *table) const override; + + Field * + make_table_field_from_def(TABLE_SHARE *share, + MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &addr, + const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const override; + void + Column_definition_attributes_frm_pack(const Column_definition_attributes *def, + uchar *buff) const override + { + def->frm_pack_basic(buff); + def->frm_pack_charset(buff); + } + bool + Column_definition_attributes_frm_unpack(Column_definition_attributes *def, + TABLE_SHARE *share, + const uchar *buffer, + LEX_CUSTRING *gis_options) + const override + { + def->frm_unpack_basic(buffer); + return def->frm_unpack_charset(share, buffer); + } + void make_sort_key(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, Sort_param *param) + const override; + void sortlength(THD *thd, + const Type_std_attributes *item, + SORT_FIELD_ATTR *attr) const override; + uint32 max_display_length(const Item *item) const override + { + return Inet6::max_char_length(); + } + uint32 calc_pack_length(uint32 length) const override + { + return Inet6::binary_length(); + } + void Item_update_null_value(Item *item) const override + { + NativeBufferInet6 tmp; + item->val_native(current_thd, &tmp); + } + bool Item_save_in_value(THD *thd, Item *item, st_value *value) const override; + void Item_param_setup_conversion(THD *thd, Item_param *param) const override; + void Item_param_set_param_func(Item_param *param, + uchar **pos, ulong len) const override + { + param->set_param_str(pos, len); + } + bool Item_param_set_from_value(THD *thd, + Item_param *param, + const Type_all_attributes *attr, + const st_value *val) const override + { + param->unsigned_flag= false;//QQ + param->setup_conversion_string(thd, attr->collation.collation); + /* + Exact value of max_length is not known unless data is converted to + charset of connection, so we have to set it later. + */ + return param->set_str(val->m_string.ptr(), val->m_string.length(), + attr->collation.collation, + attr->collation.collation); + } + bool Item_param_val_native(THD *thd, Item_param *item, Native *to) + const override + { + StringBufferInet6 buffer; + String *str= item->val_str(&buffer); + if (!str) + return true; + Inet6_null tmp(*str); + return tmp.is_null() || tmp.to_native(to); + } + bool Item_send(Item *item, Protocol *p, st_value *buf) const override + { + return Item_send_str(item, p, buf); + } + int Item_save_in_field(Item *item, Field *field, bool no_conversions) + const override + { + if (field->type_handler() == this) + { + NativeBuffer<MAX_FIELD_WIDTH> tmp; + bool rc= item->val_native(current_thd, &tmp); + if (rc || item->null_value) + return set_field_to_null_with_conversions(field, no_conversions); + field->set_notnull(); + return field->store_native(tmp); + } + return item->save_str_in_field(field, no_conversions); + } + + String *print_item_value(THD *thd, Item *item, String *str) const override + { + StringBufferInet6 buf; + String *result= item->val_str(&buf); + return !result || + str->realloc(name().length() + result->length() + 2) || + str->copy(name().ptr(), name().length(), &my_charset_latin1) || + str->append('\'') || + str->append(result->ptr(), result->length()) || + str->append('\'') ? + NULL : + str; + } + + /** + Check if + WHERE expr=value AND expr=const + can be rewritten as: + WHERE const=value AND expr=const + + "this" is the comparison handler that is used by "target". + + @param target - the predicate expr=value, + whose "expr" argument will be replaced to "const". + @param target_expr - the target's "expr" which will be replaced to "const". + @param target_value - the target's second argument, it will remain unchanged. + @param source - the equality predicate expr=const (or expr<=>const) + that can be used to rewrite the "target" part + (under certain conditions, see the code). + @param source_expr - the source's "expr". It should be exactly equal to + the target's "expr" to make condition rewrite possible. + @param source_const - the source's "const" argument, it will be inserted + into "target" instead of "expr". + */ + bool + can_change_cond_ref_to_const(Item_bool_func2 *target, + Item *target_expr, Item *target_value, + Item_bool_func2 *source, + Item *source_expr, Item *source_const) + const override + { + /* + WHERE COALESCE(inet6_col)='::1' AND COALESCE(inet6_col)=CONCAT(a); --> + WHERE COALESCE(inet6_col)='::1' AND '::1'=CONCAT(a); + */ + return target->compare_type_handler() == source->compare_type_handler(); + } + bool + subquery_type_allows_materialization(const Item *inner, + const Item *outer) const override + { + /* + Example: + SELECT * FROM t1 WHERE a IN (SELECT inet6col FROM t1 GROUP BY inet6col); + Allow materialization only if the outer column is also INET6. + This can be changed for more relaxed rules in the future. + */ + DBUG_ASSERT(inner->type_handler() == this); + return outer->type_handler() == this; + } + /** + Make a simple constant replacement item for a constant "src", + so the new item can futher be used for comparison with "cmp", e.g.: + src = cmp -> replacement = cmp + + "this" is the type handler that is used to compare "src" and "cmp". + + @param thd - current thread, for mem_root + @param src - The item that we want to replace. It's a const item, + but it can be complex enough to calculate on every row. + @param cmp - The src's comparand. + @retval - a pointer to the created replacement Item + @retval - NULL, if could not create a replacement (e.g. on EOM). + NULL is also returned for ROWs, because instead of replacing + a Item_row to a new Item_row, Type_handler_row just replaces + its elements. + */ + Item *make_const_item_for_comparison(THD *thd, + Item *src, + const Item *cmp) const override; + Item_cache *Item_get_cache(THD *thd, const Item *item) const override; + + Item *create_typecast_item(THD *thd, Item *item, + const Type_cast_attributes &attr) const override; + + int cmp_native(const Native &a, const Native &b) const override + { + DBUG_ASSERT(a.length() == Inet6::binary_length()); + DBUG_ASSERT(b.length() == Inet6::binary_length()); + return memcmp(a.ptr(), b.ptr(), Inet6::binary_length()); + } + bool set_comparator_func(Arg_comparator *cmp) const override + { + return cmp->set_cmp_func_native(); + } + bool Item_const_eq(const Item_const *a, const Item_const *b, + bool binary_cmp) const override + { + return false;//QQ + } + bool Item_eq_value(THD *thd, const Type_cmp_attributes *attr, + Item *a, Item *b) const override + { + Inet6_null na(a); + Inet6_null nb(b); + return !na.is_null() && !nb.is_null() && !na.cmp(nb); + } + bool Item_hybrid_func_fix_attributes(THD *thd, + const char *name, + Type_handler_hybrid_field_type *h, + Type_all_attributes *attr, + Item **items, + uint nitems) const override + { + attr->Type_std_attributes::operator=(Type_std_attributes_inet6()); + h->set_handler(this); + return false; + } + bool Item_func_min_max_fix_attributes(THD *thd, + Item_func_min_max *func, + Item **items, + uint nitems) const override + { + return Item_hybrid_func_fix_attributes(thd, func->func_name(), + func, func, items, nitems); + + } + bool Item_sum_hybrid_fix_length_and_dec(Item_sum_hybrid *func) const override + { + func->Type_std_attributes::operator=(Type_std_attributes_inet6()); + func->set_handler(this); + return false; + } + bool Item_sum_sum_fix_length_and_dec(Item_sum_sum *func) const override + { + return Item_func_or_sum_illegal_param(func); + } + bool Item_sum_avg_fix_length_and_dec(Item_sum_avg *func) const override + { + return Item_func_or_sum_illegal_param(func); + } + bool Item_sum_variance_fix_length_and_dec(Item_sum_variance *func) const override + { + return Item_func_or_sum_illegal_param(func); + } + + bool Item_val_native_with_conversion(THD *thd, Item *item, + Native *to) const override + { + if (item->type_handler() == this) + return item->val_native(thd, to); // No conversion needed + StringBufferInet6 buffer; + String *str= item->val_str(&buffer); + return str ? character_or_binary_string_to_native(thd, str, to) : true; + } + bool Item_val_native_with_conversion_result(THD *thd, Item *item, + Native *to) const override + { + if (item->type_handler() == this) + return item->val_native_result(thd, to); // No conversion needed + StringBufferInet6 buffer; + String *str= item->str_result(&buffer); + return str ? character_or_binary_string_to_native(thd, str, to) : true; + } + + bool Item_val_bool(Item *item) const override + { + NativeBufferInet6 tmp; + if (item->val_native(current_thd, &tmp)) + return false; + return !Inet6::only_zero_bytes(tmp.ptr(), tmp.length()); + } + void Item_get_date(THD *thd, Item *item, + Temporal::Warn *buff, MYSQL_TIME *ltime, + date_mode_t fuzzydate) const override + { + set_zero_time(ltime, MYSQL_TIMESTAMP_TIME); + } + + longlong Item_val_int_signed_typecast(Item *item) const override + { + DBUG_ASSERT(0); + return 0; + } + + longlong Item_val_int_unsigned_typecast(Item *item) const override + { + DBUG_ASSERT(0); + return 0; + } + + String *Item_func_hex_val_str_ascii(Item_func_hex *item, String *str) + const override + { + NativeBufferInet6 tmp; + if ((item->null_value= item->arguments()[0]->val_native(current_thd, &tmp))) + return NULL; + DBUG_ASSERT(tmp.length() == Inet6::binary_length()); + if (str->set_hex(tmp.ptr(), tmp.length())) + { + str->length(0); + str->set_charset(item->collation.collation); + } + return str; + } + + String *Item_func_hybrid_field_type_val_str(Item_func_hybrid_field_type *item, + String *str) const override + { + NativeBufferInet6 native; + if (item->val_native(current_thd, &native)) + { + DBUG_ASSERT(item->null_value); + return NULL; + } + DBUG_ASSERT(native.length() == Inet6::binary_length()); + Inet6_null tmp(native.ptr(), native.length()); + return tmp.is_null() || tmp.to_string(str) ? NULL : str; + } + double Item_func_hybrid_field_type_val_real(Item_func_hybrid_field_type *) + const override + { + return 0; + } + longlong Item_func_hybrid_field_type_val_int(Item_func_hybrid_field_type *) + const override + { + return 0; + } + my_decimal * + Item_func_hybrid_field_type_val_decimal(Item_func_hybrid_field_type *, + my_decimal *to) const override + { + my_decimal_set_zero(to); + return to; + } + void Item_func_hybrid_field_type_get_date(THD *, + Item_func_hybrid_field_type *, + Temporal::Warn *, + MYSQL_TIME *to, + date_mode_t fuzzydate) + const override + { + set_zero_time(to, MYSQL_TIMESTAMP_TIME); + } + // WHERE is Item_func_min_max_val_native??? + String *Item_func_min_max_val_str(Item_func_min_max *func, String *str) + const override + { + Inet6_null tmp(func); + return tmp.is_null() || tmp.to_string(str) ? NULL : str; + } + double Item_func_min_max_val_real(Item_func_min_max *) const override + { + return 0; + } + longlong Item_func_min_max_val_int(Item_func_min_max *) const override + { + return 0; + } + my_decimal *Item_func_min_max_val_decimal(Item_func_min_max *, + my_decimal *to) const override + { + my_decimal_set_zero(to); + return to; + } + bool Item_func_min_max_get_date(THD *thd, Item_func_min_max*, + MYSQL_TIME *to, date_mode_t fuzzydate) + const override + { + set_zero_time(to, MYSQL_TIMESTAMP_TIME); + return false; + } + + bool + Item_func_between_fix_length_and_dec(Item_func_between *func) const override + { + return false; + } + longlong Item_func_between_val_int(Item_func_between *func) const override + { + return func->val_int_cmp_native(); + } + + cmp_item *make_cmp_item(THD *thd, CHARSET_INFO *cs) const override; + + in_vector *make_in_vector(THD *thd, const Item_func_in *func, + uint nargs) const override; + + bool Item_func_in_fix_comparator_compatible_types(THD *thd, + Item_func_in *func) + const override + { + if (func->compatible_types_scalar_bisection_possible()) + { + return func->value_list_convert_const_to_int(thd) || + func->fix_for_scalar_comparison_using_bisection(thd); + } + return + func->fix_for_scalar_comparison_using_cmp_items(thd, + 1U << (uint) STRING_RESULT); + } + bool + Item_func_round_fix_length_and_dec(Item_func_round *func) const override + { + return Item_func_or_sum_illegal_param(func); + } + bool + Item_func_int_val_fix_length_and_dec(Item_func_int_val *func) const override + { + return Item_func_or_sum_illegal_param(func); + } + + bool Item_func_abs_fix_length_and_dec(Item_func_abs *func) const override + { + return Item_func_or_sum_illegal_param(func); + } + + bool Item_func_neg_fix_length_and_dec(Item_func_neg *func) const override + { + return Item_func_or_sum_illegal_param(func); + } + + bool + Item_func_signed_fix_length_and_dec(Item_func_signed *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_func_unsigned_fix_length_and_dec(Item_func_unsigned *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_double_typecast_fix_length_and_dec(Item_double_typecast *item) + const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_decimal_typecast_fix_length_and_dec(Item_decimal_typecast *item) + const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_char_typecast_fix_length_and_dec(Item_char_typecast *item) + const override + { + item->fix_length_and_dec_str(); + return false; + } + bool + Item_time_typecast_fix_length_and_dec(Item_time_typecast *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_date_typecast_fix_length_and_dec(Item_date_typecast *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_datetime_typecast_fix_length_and_dec(Item_datetime_typecast *item) + const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_func_plus_fix_length_and_dec(Item_func_plus *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_func_minus_fix_length_and_dec(Item_func_minus *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_func_mul_fix_length_and_dec(Item_func_mul *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_func_div_fix_length_and_dec(Item_func_div *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_func_mod_fix_length_and_dec(Item_func_mod *item) const override + { + return Item_func_or_sum_illegal_param(item); + } +}; + + +extern MYSQL_PLUGIN_IMPORT Type_handler_inet6 type_handler_inet6; + + #endif /* SQL_TYPE_INET_H */ diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 8dae20fc30f..a2eee4e196b 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -405,7 +405,7 @@ bool get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key, uchar *max_key,uint max_key_flag); static bool eq_tree(SEL_ARG* a,SEL_ARG *b); -static SEL_ARG null_element(SEL_ARG::IMPOSSIBLE); +SEL_ARG null_element(SEL_ARG::IMPOSSIBLE); static bool null_part_in_key(KEY_PART *key_part, const uchar *key, uint length); static bool is_key_scan_ror(PARAM *param, uint keynr, uint8 nparts); diff --git a/sql/opt_range.h b/sql/opt_range.h index 73def7bde92..a504e35bf45 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -598,6 +598,7 @@ public: SEL_ARG *clone_tree(RANGE_OPT_PARAM *param); }; +extern MYSQL_PLUGIN_IMPORT SEL_ARG null_element; class SEL_ARG_IMPOSSIBLE: public SEL_ARG { diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 81c571e66e7..2b589a327d9 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -6731,7 +6731,7 @@ get_innobase_type_from_mysql_type( } case MYSQL_TYPE_BIT: case MYSQL_TYPE_STRING: - if (field->binary()) { + if (field->binary() || field->key_type() == HA_KEYTYPE_BINARY) { return(DATA_FIXBINARY); } else if (field->charset() == &my_charset_latin1) { return(DATA_CHAR); |