diff options
Diffstat (limited to 'plugin/type_inet/mysql-test')
36 files changed, 5142 insertions, 0 deletions
diff --git a/plugin/type_inet/mysql-test/type_inet/binlog_stm_type_inet6.result b/plugin/type_inet/mysql-test/type_inet/binlog_stm_type_inet6.result new file mode 100644 index 00000000..e09b1021 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/binlog_stm_type_inet6.result @@ -0,0 +1,34 @@ +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'); +INSERT INTO t1 VALUES ('ffff::ffff'); +PREPARE stmt FROM 'INSERT INTO t1 VALUES (?)'; +EXECUTE stmt USING CAST('::1' AS INET6); +EXECUTE stmt USING CAST(CONCAT(REPEAT(0x00,15), 0x02) AS INET6); +DEALLOCATE PREPARE stmt; +BEGIN NOT ATOMIC +DECLARE a INET6 DEFAULT '::3'; +INSERT INTO t1 VALUES (a); +END; +$$ +DROP TABLE t1; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INET6) +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ('::') +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ('ffff::ffff') +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ('::1') +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ('::2') +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('a','::3')) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ diff --git a/plugin/type_inet/mysql-test/type_inet/binlog_stm_type_inet6.test b/plugin/type_inet/mysql-test/type_inet/binlog_stm_type_inet6.test new file mode 100644 index 00000000..d5144809 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/binlog_stm_type_inet6.test @@ -0,0 +1,28 @@ +--source include/not_embedded.inc +--source include/have_binlog_format_statement.inc + +--disable_query_log +reset master; # get rid of previous tests binlog +--enable_query_log + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'); +INSERT INTO t1 VALUES ('ffff::ffff'); + +PREPARE stmt FROM 'INSERT INTO t1 VALUES (?)'; +EXECUTE stmt USING CAST('::1' AS INET6); +EXECUTE stmt USING CAST(CONCAT(REPEAT(0x00,15), 0x02) AS INET6); +DEALLOCATE PREPARE stmt; + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE a INET6 DEFAULT '::3'; + INSERT INTO t1 VALUES (a); +END; +$$ +DELIMITER ;$$ + +DROP TABLE t1; + +--let $binlog_file = LAST +source include/show_binlog_events.inc; diff --git a/plugin/type_inet/mysql-test/type_inet/binlog_table_map_optional_metadata_type_inet6.result b/plugin/type_inet/mysql-test/type_inet/binlog_table_map_optional_metadata_type_inet6.result new file mode 100644 index 00000000..7911407b --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/binlog_table_map_optional_metadata_type_inet6.result @@ -0,0 +1,60 @@ +# +# Start of 10.5 tests +# +# +# MDEV-20822 INET6 crashes in combination with RBR extended metadata +# +# Using DEFAULT_CHARSET format +RESET MASTER; +SET GLOBAL binlog_row_metadata = NO_LOG; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES('::'); +# Columns(BINARY(16)) +DROP TABLE t1; +RESET MASTER; +RESET MASTER; +SET GLOBAL binlog_row_metadata = MINIMAL; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES('::'); +# Columns(BINARY(16)) +DROP TABLE t1; +RESET MASTER; +RESET MASTER; +SET GLOBAL binlog_row_metadata = FULL; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES('::'); +# Columns(`a` BINARY(16)) +DROP TABLE t1; +RESET MASTER; +# Using COLUMN_CHARSET format +RESET MASTER; +SET GLOBAL binlog_row_metadata = NO_LOG; +CREATE TABLE t1 (a INET6, b CHAR(16) CHARACTER SET latin1, c CHAR(16) CHARACTER SET utf8); +INSERT INTO t1 VALUES('::','',''); +# Columns(BINARY(16), +# BINARY(16), +# BINARY(48)) +DROP TABLE t1; +RESET MASTER; +RESET MASTER; +SET GLOBAL binlog_row_metadata = MINIMAL; +CREATE TABLE t1 (a INET6, b CHAR(16) CHARACTER SET latin1, c CHAR(16) CHARACTER SET utf8); +INSERT INTO t1 VALUES('::','',''); +# Columns(BINARY(16), +# CHAR(16) CHARSET latin1 COLLATE latin1_swedish_ci, +# CHAR(16) CHARSET utf8 COLLATE utf8_general_ci) +DROP TABLE t1; +RESET MASTER; +RESET MASTER; +SET GLOBAL binlog_row_metadata = FULL; +CREATE TABLE t1 (a INET6, b CHAR(16) CHARACTER SET latin1, c CHAR(16) CHARACTER SET utf8); +INSERT INTO t1 VALUES('::','',''); +# Columns(`a` BINARY(16), +# `b` CHAR(16) CHARSET latin1 COLLATE latin1_swedish_ci, +# `c` CHAR(16) CHARSET utf8 COLLATE utf8_general_ci) +DROP TABLE t1; +RESET MASTER; +SET GLOBAL binlog_row_metadata = DEFAULT; +# +# End of 10.5 tests +# diff --git a/plugin/type_inet/mysql-test/type_inet/binlog_table_map_optional_metadata_type_inet6.test b/plugin/type_inet/mysql-test/type_inet/binlog_table_map_optional_metadata_type_inet6.test new file mode 100644 index 00000000..63672f06 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/binlog_table_map_optional_metadata_type_inet6.test @@ -0,0 +1,72 @@ +--source include/have_debug.inc +--source include/have_binlog_format_row.inc + +--let $MYSQLD_DATADIR= `select @@datadir` +--let $binlog_file= $MYSQLD_DATADIR/master-bin.000001 + + +--echo # +--echo # Start of 10.5 tests +--echo # + +--echo # +--echo # MDEV-20822 INET6 crashes in combination with RBR extended metadata +--echo # + +--echo # Using DEFAULT_CHARSET format + +RESET MASTER; +SET GLOBAL binlog_row_metadata = NO_LOG; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES('::'); +--source suite/binlog/include/print_optional_metadata.inc +DROP TABLE t1; +RESET MASTER; + +RESET MASTER; +SET GLOBAL binlog_row_metadata = MINIMAL; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES('::'); +--source suite/binlog/include/print_optional_metadata.inc +DROP TABLE t1; +RESET MASTER; + +RESET MASTER; +SET GLOBAL binlog_row_metadata = FULL; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES('::'); +--source suite/binlog/include/print_optional_metadata.inc +DROP TABLE t1; +RESET MASTER; + +--echo # Using COLUMN_CHARSET format + +RESET MASTER; +SET GLOBAL binlog_row_metadata = NO_LOG; +CREATE TABLE t1 (a INET6, b CHAR(16) CHARACTER SET latin1, c CHAR(16) CHARACTER SET utf8); +INSERT INTO t1 VALUES('::','',''); +--source suite/binlog/include/print_optional_metadata.inc +DROP TABLE t1; +RESET MASTER; + +RESET MASTER; +SET GLOBAL binlog_row_metadata = MINIMAL; +CREATE TABLE t1 (a INET6, b CHAR(16) CHARACTER SET latin1, c CHAR(16) CHARACTER SET utf8); +INSERT INTO t1 VALUES('::','',''); +--source suite/binlog/include/print_optional_metadata.inc +DROP TABLE t1; +RESET MASTER; + +RESET MASTER; +SET GLOBAL binlog_row_metadata = FULL; +CREATE TABLE t1 (a INET6, b CHAR(16) CHARACTER SET latin1, c CHAR(16) CHARACTER SET utf8); +INSERT INTO t1 VALUES('::','',''); +--source suite/binlog/include/print_optional_metadata.inc +DROP TABLE t1; +RESET MASTER; + +SET GLOBAL binlog_row_metadata = DEFAULT; + +--echo # +--echo # End of 10.5 tests +--echo # diff --git a/plugin/type_inet/mysql-test/type_inet/func_inet_plugin.result b/plugin/type_inet/mysql-test/type_inet/func_inet_plugin.result new file mode 100644 index 00000000..9ee1a020 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/func_inet_plugin.result @@ -0,0 +1,112 @@ +# +# Start of 10.5 tests +# +# +# MDEV-20768 Turn INET functions into a function collection plugin +# +SELECT +'----' AS `----`, +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='FUNCTION' + AND PLUGIN_NAME IN +('inet_aton', +'inet_ntoa', +'inet6_aton', +'inet6_ntoa', +'is_ipv4', +'is_ipv6', +'is_ipv4_compat', +'is_ipv4_mapped') +ORDER BY PLUGIN_NAME; +---- ---- +PLUGIN_NAME inet6_aton +PLUGIN_VERSION 1.0 +PLUGIN_STATUS ACTIVE +PLUGIN_TYPE FUNCTION +PLUGIN_AUTHOR MariaDB Corporation +PLUGIN_DESCRIPTION Function INET6_ATON() +PLUGIN_LICENSE GPL +PLUGIN_MATURITY Stable +PLUGIN_AUTH_VERSION 1.0 +---- ---- +PLUGIN_NAME inet6_ntoa +PLUGIN_VERSION 1.0 +PLUGIN_STATUS ACTIVE +PLUGIN_TYPE FUNCTION +PLUGIN_AUTHOR MariaDB Corporation +PLUGIN_DESCRIPTION Function INET6_NTOA() +PLUGIN_LICENSE GPL +PLUGIN_MATURITY Stable +PLUGIN_AUTH_VERSION 1.0 +---- ---- +PLUGIN_NAME inet_aton +PLUGIN_VERSION 1.0 +PLUGIN_STATUS ACTIVE +PLUGIN_TYPE FUNCTION +PLUGIN_AUTHOR MariaDB Corporation +PLUGIN_DESCRIPTION Function INET_ATON() +PLUGIN_LICENSE GPL +PLUGIN_MATURITY Stable +PLUGIN_AUTH_VERSION 1.0 +---- ---- +PLUGIN_NAME inet_ntoa +PLUGIN_VERSION 1.0 +PLUGIN_STATUS ACTIVE +PLUGIN_TYPE FUNCTION +PLUGIN_AUTHOR MariaDB Corporation +PLUGIN_DESCRIPTION Function INET_NTOA() +PLUGIN_LICENSE GPL +PLUGIN_MATURITY Stable +PLUGIN_AUTH_VERSION 1.0 +---- ---- +PLUGIN_NAME is_ipv4 +PLUGIN_VERSION 1.0 +PLUGIN_STATUS ACTIVE +PLUGIN_TYPE FUNCTION +PLUGIN_AUTHOR MariaDB Corporation +PLUGIN_DESCRIPTION Function IS_IPV4() +PLUGIN_LICENSE GPL +PLUGIN_MATURITY Stable +PLUGIN_AUTH_VERSION 1.0 +---- ---- +PLUGIN_NAME is_ipv4_compat +PLUGIN_VERSION 1.0 +PLUGIN_STATUS ACTIVE +PLUGIN_TYPE FUNCTION +PLUGIN_AUTHOR MariaDB Corporation +PLUGIN_DESCRIPTION Function IS_IPV4_COMPAT() +PLUGIN_LICENSE GPL +PLUGIN_MATURITY Stable +PLUGIN_AUTH_VERSION 1.0 +---- ---- +PLUGIN_NAME is_ipv4_mapped +PLUGIN_VERSION 1.0 +PLUGIN_STATUS ACTIVE +PLUGIN_TYPE FUNCTION +PLUGIN_AUTHOR MariaDB Corporation +PLUGIN_DESCRIPTION Function IS_IPV4_MAPPED() +PLUGIN_LICENSE GPL +PLUGIN_MATURITY Stable +PLUGIN_AUTH_VERSION 1.0 +---- ---- +PLUGIN_NAME is_ipv6 +PLUGIN_VERSION 1.0 +PLUGIN_STATUS ACTIVE +PLUGIN_TYPE FUNCTION +PLUGIN_AUTHOR MariaDB Corporation +PLUGIN_DESCRIPTION Function IS_IPV6() +PLUGIN_LICENSE GPL +PLUGIN_MATURITY Stable +PLUGIN_AUTH_VERSION 1.0 +# +# End of 10.5 tests +# diff --git a/plugin/type_inet/mysql-test/type_inet/func_inet_plugin.test b/plugin/type_inet/mysql-test/type_inet/func_inet_plugin.test new file mode 100644 index 00000000..45b462e8 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/func_inet_plugin.test @@ -0,0 +1,37 @@ +--echo # +--echo # Start of 10.5 tests +--echo # + +--echo # +--echo # MDEV-20768 Turn INET functions into a function collection plugin +--echo # + +--vertical_results +SELECT + '----' AS `----`, + 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='FUNCTION' + AND PLUGIN_NAME IN + ('inet_aton', + 'inet_ntoa', + 'inet6_aton', + 'inet6_ntoa', + 'is_ipv4', + 'is_ipv6', + 'is_ipv4_compat', + 'is_ipv4_mapped') +ORDER BY PLUGIN_NAME; +--horizontal_results + +--echo # +--echo # End of 10.5 tests +--echo # diff --git a/plugin/type_inet/mysql-test/type_inet/rpl_row_binary_to_inet6.result b/plugin/type_inet/mysql-test/type_inet/rpl_row_binary_to_inet6.result new file mode 100644 index 00000000..7b69217f --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/rpl_row_binary_to_inet6.result @@ -0,0 +1,35 @@ +include/master-slave.inc +[connection master] +# +# Start of 10.5 tests +# +# +# MDEV-20844 RBR from binary(16) to inet6 fails with error 171: The event was corrupt, leading to illegal data being read +# +CREATE TABLE t1 (a BINARY(16)); +connection slave; +ALTER TABLE t1 MODIFY a INET6; +connection master; +INSERT INTO t1 VALUES (INET6_ATON('::')); +INSERT INTO t1 VALUES (INET6_ATON('::192.168.0.1')); +INSERT INTO t1 VALUES (INET6_ATON('ffff::')); +INSERT INTO t1 VALUES (INET6_ATON('ffff::192.168.0.1')); +SELECT INET6_NTOA(a) FROM t1 ORDER BY a; +INET6_NTOA(a) +:: +::192.168.0.1 +ffff:: +ffff::c0a8:1 +connection slave; +SELECT * FROM t1 ORDER BY a; +a +:: +::192.168.0.1 +ffff:: +ffff::c0a8:1 +connection master; +DROP TABLE t1; +# +# End of 10.5 tests +# +include/rpl_end.inc diff --git a/plugin/type_inet/mysql-test/type_inet/rpl_row_binary_to_inet6.test b/plugin/type_inet/mysql-test/type_inet/rpl_row_binary_to_inet6.test new file mode 100644 index 00000000..f48b1c49 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/rpl_row_binary_to_inet6.test @@ -0,0 +1,33 @@ +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +--echo # +--echo # Start of 10.5 tests +--echo # + +--echo # +--echo # MDEV-20844 RBR from binary(16) to inet6 fails with error 171: The event was corrupt, leading to illegal data being read +--echo # + +CREATE TABLE t1 (a BINARY(16)); + +--sync_slave_with_master +ALTER TABLE t1 MODIFY a INET6; + +--connection master +INSERT INTO t1 VALUES (INET6_ATON('::')); +INSERT INTO t1 VALUES (INET6_ATON('::192.168.0.1')); +INSERT INTO t1 VALUES (INET6_ATON('ffff::')); +INSERT INTO t1 VALUES (INET6_ATON('ffff::192.168.0.1')); +SELECT INET6_NTOA(a) FROM t1 ORDER BY a; +--sync_slave_with_master +SELECT * FROM t1 ORDER BY a; + +--connection master +DROP TABLE t1; + +--echo # +--echo # End of 10.5 tests +--echo # + +--source include/rpl_end.inc diff --git a/plugin/type_inet/mysql-test/type_inet/rpl_row_inet6_to_binary.result b/plugin/type_inet/mysql-test/type_inet/rpl_row_inet6_to_binary.result new file mode 100644 index 00000000..932043a9 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/rpl_row_inet6_to_binary.result @@ -0,0 +1,35 @@ +include/master-slave.inc +[connection master] +# +# Start of 10.5 tests +# +# +# MDEV-20844 RBR from binary(16) to inet6 fails with error 171: The event was corrupt, leading to illegal data being read +# +CREATE TABLE t1 (a INET6); +connection slave; +ALTER TABLE t1 MODIFY a BINARY(16); +connection master; +INSERT INTO t1 VALUES ('::'); +INSERT INTO t1 VALUES ('::192.168.0.1'); +INSERT INTO t1 VALUES ('ffff::'); +INSERT INTO t1 VALUES ('ffff::192.168.0.1'); +SELECT a FROM t1 ORDER BY a; +a +:: +::192.168.0.1 +ffff:: +ffff::c0a8:1 +connection slave; +SELECT INET6_NTOA(a) FROM t1 ORDER BY a; +INET6_NTOA(a) +:: +::192.168.0.1 +ffff:: +ffff::c0a8:1 +connection master; +DROP TABLE t1; +# +# End of 10.5 tests +# +include/rpl_end.inc diff --git a/plugin/type_inet/mysql-test/type_inet/rpl_row_inet6_to_binary.test b/plugin/type_inet/mysql-test/type_inet/rpl_row_inet6_to_binary.test new file mode 100644 index 00000000..7abb4f6f --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/rpl_row_inet6_to_binary.test @@ -0,0 +1,33 @@ +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +--echo # +--echo # Start of 10.5 tests +--echo # + +--echo # +--echo # MDEV-20844 RBR from binary(16) to inet6 fails with error 171: The event was corrupt, leading to illegal data being read +--echo # + +CREATE TABLE t1 (a INET6); + +--sync_slave_with_master +ALTER TABLE t1 MODIFY a BINARY(16); + +--connection master +INSERT INTO t1 VALUES ('::'); +INSERT INTO t1 VALUES ('::192.168.0.1'); +INSERT INTO t1 VALUES ('ffff::'); +INSERT INTO t1 VALUES ('ffff::192.168.0.1'); +SELECT a FROM t1 ORDER BY a; +--sync_slave_with_master +SELECT INET6_NTOA(a) FROM t1 ORDER BY a; + +--connection master +DROP TABLE t1; + +--echo # +--echo # End of 10.5 tests +--echo # + +--source include/rpl_end.inc diff --git a/plugin/type_inet/mysql-test/type_inet/rpl_type_inet6.result b/plugin/type_inet/mysql-test/type_inet/rpl_type_inet6.result new file mode 100644 index 00000000..5bda0b07 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/rpl_type_inet6.result @@ -0,0 +1,17 @@ +include/master-slave.inc +[connection master] +# +# MDEV-274 The data type for IPv6/IPv4 addresses in MariaDB +# +connection master; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('ffff::ffff'); +connection slave; +SELECT HEX(a), a FROM t1; +HEX(a) a +00000000000000000000000000000000 :: +FFFF000000000000000000000000FFFF ffff::ffff +connection master; +DROP TABLE t1; +connection slave; +include/rpl_end.inc diff --git a/plugin/type_inet/mysql-test/type_inet/rpl_type_inet6.test b/plugin/type_inet/mysql-test/type_inet/rpl_type_inet6.test new file mode 100644 index 00000000..91c092b6 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/rpl_type_inet6.test @@ -0,0 +1,16 @@ +--source include/master-slave.inc + +--echo # +--echo # MDEV-274 The data type for IPv6/IPv4 addresses in MariaDB +--echo # + +connection master; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('ffff::ffff'); +sync_slave_with_master; +SELECT HEX(a), a FROM t1; +connection master; +DROP TABLE t1; +sync_slave_with_master; + +--source include/rpl_end.inc diff --git a/plugin/type_inet/mysql-test/type_inet/suite.pm b/plugin/type_inet/mysql-test/type_inet/suite.pm new file mode 100644 index 00000000..5893fcb7 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/suite.pm @@ -0,0 +1,9 @@ +package My::Suite::Type_inet; + +@ISA = qw(My::Suite); + +#return "No inet6 plugin" unless $::mysqld_variables{'inet6'} eq "ON"; + +sub is_default { 1 } + +bless { }; 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 00000000..0e879aad --- /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 00000000..ef5ea836 --- /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 00000000..da949481 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6.result @@ -0,0 +1,2161 @@ +# +# 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 (type=inet6) 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 (type=inet6) 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 FLOAT); +ERROR HY000: Illegal parameter data type inet6 for operation 'float_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; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('ffff::ffff'); +CREATE TABLE t2 AS SELECT +CAST(a AS CHAR), +CAST(a AS CHAR(39)), +CAST(a AS CHAR(530)), +CAST(a AS CHAR(65535)), +CAST(a AS CHAR(66000)), +CAST(a AS CHAR(16777215)), +CAST(a AS CHAR(16777216)) +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `CAST(a AS CHAR)` varchar(39) DEFAULT NULL, + `CAST(a AS CHAR(39))` varchar(39) DEFAULT NULL, + `CAST(a AS CHAR(530))` text DEFAULT NULL, + `CAST(a AS CHAR(65535))` text DEFAULT NULL, + `CAST(a AS CHAR(66000))` mediumtext DEFAULT NULL, + `CAST(a AS CHAR(16777215))` mediumtext DEFAULT NULL, + `CAST(a AS CHAR(16777216))` longtext DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +CAST(a AS CHAR) ffff::ffff +CAST(a AS CHAR(39)) ffff::ffff +CAST(a AS CHAR(530)) ffff::ffff +CAST(a AS CHAR(65535)) ffff::ffff +CAST(a AS CHAR(66000)) ffff::ffff +CAST(a AS CHAR(16777215)) ffff::ffff +CAST(a AS CHAR(16777216)) ffff::ffff +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('ffff::ffff'); +CREATE TABLE t2 AS SELECT +CAST(a AS BINARY(4)) AS cb4, +CAST(a AS BINARY) AS cb, +CAST(a AS BINARY(16)) AS cb16, +CAST(a AS BINARY(32)) AS cb32, +CAST(a AS BINARY(530)) AS cb530, +CAST(a AS BINARY(65535)) AS cb65535, +CAST(a AS BINARY(66000)) AS cb66000, +CAST(a AS BINARY(16777215)) AS cb16777215, +CAST(a AS BINARY(16777216)) AS cb16777216 +FROM t1 LIMIT 0; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `cb4` binary(4) DEFAULT NULL, + `cb` binary(16) DEFAULT NULL, + `cb16` binary(16) DEFAULT NULL, + `cb32` binary(32) DEFAULT NULL, + `cb530` varbinary(530) DEFAULT NULL, + `cb65535` blob DEFAULT NULL, + `cb66000` mediumblob DEFAULT NULL, + `cb16777215` mediumblob DEFAULT NULL, + `cb16777216` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT +CAST(a AS BINARY(4)) AS cb4, +CAST(a AS BINARY) AS cb, +CAST(a AS BINARY(16)) AS cb16, +CAST(a AS BINARY(32)) AS cb32, +CAST(a AS BINARY(530)) AS cb530, +CAST(a AS BINARY(65535)) AS cb65535 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `cb4` binary(4) DEFAULT NULL, + `cb` binary(16) DEFAULT NULL, + `cb16` binary(16) DEFAULT NULL, + `cb32` binary(32) DEFAULT NULL, + `cb530` varbinary(530) DEFAULT NULL, + `cb65535` blob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT +HEX(cb4), +HEX(cb), +HEX(cb16), +HEX(cb32), +LENGTH(cb530), +LENGTH(cb65535) +FROM t2; +HEX(cb4) FFFF0000 +HEX(cb) FFFF000000000000000000000000FFFF +HEX(cb16) FFFF000000000000000000000000FFFF +HEX(cb32) FFFF000000000000000000000000FFFF00000000000000000000000000000000 +LENGTH(cb530) 530 +LENGTH(cb65535) 65535 +DROP TABLE t2; +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; +# +# MDEV-21765 Possibly inconsistent behavior of BIT_xx functions with INET6 field +# +CREATE TABLE t1 (a INET6); +SELECT BIT_AND(a) FROM t1; +ERROR HY000: Illegal parameter data type inet6 for operation 'bit_and(' +SELECT BIT_OR(a) FROM t1; +ERROR HY000: Illegal parameter data type inet6 for operation 'bit_or(' +SELECT BIT_XOR(a) FROM t1; +ERROR HY000: Illegal parameter data type inet6 for operation 'bit_xor(' +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; +# +# ALTER from INET6 to INET6 +# +CREATE TABLE t1 (a INET6, b INT); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329', 1); +ALTER TABLE t1 MODIFY b DECIMAL(10,2); +SELECT * FROM t1; +a b +2001:db8::ff00:42:8329 1.00 +DROP TABLE t1; +# +# ALTER to character string data types +# +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS CHAR(39)) FROM t1; +CAST(a AS CHAR(39)) +2001:db8::ff00:42:8329 +ALTER TABLE t1 MODIFY a CHAR(39); +SELECT * FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a VARCHAR(39); +SELECT * FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a TINYTEXT; +SELECT * FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a TEXT; +SELECT * FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a MEDIUMTEXT; +SELECT * FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a LONGTEXT; +SELECT * FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +# +# ALTER from character string data types +# +CREATE OR REPLACE TABLE t1 (a CHAR(64)); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +CAST(a AS INET6) +2001:db8::ff00:42:8329 +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +CAST(a AS INET6) +2001:db8::ff00:42:8329 +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a TINYTEXT); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +CAST(a AS INET6) +2001:db8::ff00:42:8329 +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a TEXT); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +CAST(a AS INET6) +2001:db8::ff00:42:8329 +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a MEDIUMTEXT); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +CAST(a AS INET6) +2001:db8::ff00:42:8329 +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a LONGTEXT); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +CAST(a AS INET6) +2001:db8::ff00:42:8329 +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +# +# ALTER to binary string data types +# +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a BINARY(16); +SELECT HEX(a) FROM t1; +HEX(a) +20010DB8000000000000FF0000428329 +DROP TABLE t1; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a BINARY(17); +SELECT HEX(a) FROM t1; +HEX(a) +20010DB8000000000000FF000042832900 +DROP TABLE t1; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a BINARY(15); +ERROR 22001: Data too long for column 'a' at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a TINYBLOB; +SELECT HEX(a) FROM t1; +HEX(a) +20010DB8000000000000FF0000428329 +DROP TABLE t1; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a BLOB; +SELECT HEX(a) FROM t1; +HEX(a) +20010DB8000000000000FF0000428329 +DROP TABLE t1; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a MEDIUMBLOB; +SELECT HEX(a) FROM t1; +HEX(a) +20010DB8000000000000FF0000428329 +DROP TABLE t1; +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a LONGBLOB; +SELECT HEX(a) FROM t1; +HEX(a) +20010DB8000000000000FF0000428329 +DROP TABLE t1; +# +# ALTER from binary string data types +# +CREATE TABLE t1 (a BINARY(16)); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a INET6; +SELECT a FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +CREATE TABLE t1 (a BINARY(17)); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF000042832900'); +ALTER TABLE t1 MODIFY a INET6; +ERROR 22007: Incorrect inet6 value: ' \x01\x0D\xB8\x00\x00\x00\x00\x00\x00\xFF\x00\x00B\x83)\x00' for column `test`.`t1`.`a` at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a BINARY(15)); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF00004283'); +ALTER TABLE t1 MODIFY a INET6; +ERROR 22007: Incorrect inet6 value: ' \x01\x0D\xB8\x00\x00\x00\x00\x00\x00\xFF\x00\x00B\x83' for column `test`.`t1`.`a` at row 1 +DROP TABLE t1; +CREATE TABLE t1 (a TINYBLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a INET6; +SELECT a FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +CREATE TABLE t1 (a BLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a INET6; +SELECT a FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +CREATE TABLE t1 (a MEDIUMBLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a INET6; +SELECT a FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +CREATE TABLE t1 (a BLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a INET6; +SELECT a FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +# +# SET from INET6 to INET6 +# +CREATE TABLE t1 (a INET6, b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +b +ffff::ffff +DROP TABLE t1; +# +# SET from INET6 to numeric +# +CREATE TABLE t1 (a INET6, b INT); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect integer value: 'ffff::ffff' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +CREATE TABLE t1 (a INET6, b DOUBLE); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect double value: 'ffff::ffff' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +CREATE TABLE t1 (a INET6, b DECIMAL(32,0)); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect decimal value: 'ffff::ffff' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +CREATE TABLE t1 (a INET6, b YEAR); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect integer value: 'ffff::ffff' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +# +# SET from numeric to INET6 +# +CREATE TABLE t1 (a INT, b INET6); +INSERT INTO t1 VALUES (1, NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect inet6 value: '1' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE, b INET6); +INSERT INTO t1 VALUES (1, NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect inet6 value: '1' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL(32,0), b INET6); +INSERT INTO t1 VALUES (1, NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect inet6 value: '1' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +CREATE TABLE t1 (a YEAR, b INET6); +INSERT INTO t1 VALUES (1, NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect inet6 value: '2001' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +# +# SET from INET6 to temporal +# +CREATE TABLE t1 (a INET6, b TIME); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect time value: 'ffff::ffff' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +CREATE TABLE t1 (a INET6, b DATE); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect date value: 'ffff::ffff' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +CREATE TABLE t1 (a INET6, b DATETIME); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect datetime value: 'ffff::ffff' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +CREATE TABLE t1 (a INET6, b TIMESTAMP NULL DEFAULT NULL); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect datetime value: 'ffff::ffff' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +# +# SET from temporal to INET6 +# +CREATE TABLE t1 (a TIME, b INET6); +INSERT INTO t1 VALUES ('00:00:00', NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect inet6 value: '00:00:00' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +CREATE TABLE t1 (a DATE, b INET6); +INSERT INTO t1 VALUES ('2001-01:01', NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect inet6 value: '2001-01-01' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +CREATE TABLE t1 (a DATETIME, b INET6); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect inet6 value: '2001-01-01 10:20:30' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP, b INET6); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect inet6 value: '2001-01-01 10:20:30' for column `test`.`t1`.`b` at row 1 +SELECT b FROM t1; +b +NULL +DROP TABLE t1; +# +# SET from INET6 to character string +# +CREATE TABLE t1 (a INET6, b CHAR(39)); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +b +ffff::ffff +DROP TABLE t1; +CREATE TABLE t1 (a INET6, b VARCHAR(39)); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +b +ffff::ffff +DROP TABLE t1; +CREATE TABLE t1 (a INET6, b TEXT); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +b +ffff::ffff +DROP TABLE t1; +CREATE TABLE t1 (a INET6, b ENUM('ffff::ffff')); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +b +ffff::ffff +DROP TABLE t1; +CREATE TABLE t1 (a INET6, b SET('ffff::ffff')); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +b +ffff::ffff +DROP TABLE t1; +# +# SET from character string to INET6 +# +CREATE TABLE t1 (a CHAR(39), b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +b +ffff::ffff +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(39), b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +b +ffff::ffff +DROP TABLE t1; +CREATE TABLE t1 (a TEXT, b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +b +ffff::ffff +DROP TABLE t1; +CREATE TABLE t1 (a ENUM('ffff::ffff'), b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +b +ffff::ffff +DROP TABLE t1; +CREATE TABLE t1 (a SET('ffff::ffff'), b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +b +ffff::ffff +DROP TABLE t1; +# +# SET from INET6 to binary +# +CREATE TABLE t1 (a INET6, b BINARY(16)); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT HEX(b) FROM t1; +HEX(b) +FFFF000000000000000000000000FFFF +DROP TABLE t1; +CREATE TABLE t1 (a INET6, b VARBINARY(39)); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT HEX(b) FROM t1; +HEX(b) +FFFF000000000000000000000000FFFF +DROP TABLE t1; +CREATE TABLE t1 (a INET6, b BLOB); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT HEX(b) FROM t1; +HEX(b) +FFFF000000000000000000000000FFFF +DROP TABLE t1; +# +# SET from binary to INET6 +# +CREATE TABLE t1 (a BINARY(16), b INET6); +INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +b +ffff::ffff +DROP TABLE t1; +CREATE TABLE t1 (a VARBINARY(16), b INET6); +INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +b +ffff::ffff +DROP TABLE t1; +CREATE TABLE t1 (a BLOB, b INET6); +INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +b +ffff::ffff +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 +# +# MDEV-20785 Converting INET6 to CHAR(39) produces garbage without a warning +# +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS CHAR(39)) FROM t1; +CAST(a AS CHAR(39)) +2001:db8::ff00:42:8329 +ALTER TABLE t1 MODIFY a CHAR(39); +SELECT * FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +# +# MDEV-20783 INET6 cannot be converted to BINARY(16) (requires clarification in documentation) +# +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a BINARY(16); +SELECT HEX(a) FROM t1; +HEX(a) +20010DB8000000000000FF0000428329 +DROP TABLE t1; +# +# MDEV-20795 CAST(inet6 AS BINARY) returns wrong result +# +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT HEX(CAST(a AS BINARY)) FROM t1; +HEX(CAST(a AS BINARY)) +20010DB8000000000000FF0000428329 +SELECT HEX(CAST(a AS BINARY(16))) FROM t1; +HEX(CAST(a AS BINARY(16))) +20010DB8000000000000FF0000428329 +DROP TABLE t1; +# +# MDEV-20808 CAST from INET6 to FLOAT does not produce an error +# +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'); +SELECT CAST(a AS FLOAT) FROM t1; +ERROR HY000: Illegal parameter data type inet6 for operation 'float_typecast' +DROP TABLE t1; +# +# MDEV-20798 Conversion from INET6 to other types performed without errors or warnings +# +CREATE TABLE t1 (a INET6, b INT); +INSERT INTO t1 (a) VALUES ('::'); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect integer value: '::' for column `test`.`t1`.`b` at row 1 +SELECT * FROM t1; +a b +:: NULL +DROP TABLE t1; +SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); +CREATE TABLE t1 (a INET6, b TIMESTAMP); +INSERT INTO t1 (a) VALUES ('::'); +UPDATE t1 SET b=a; +ERROR 22007: Incorrect datetime value: '::' for column `test`.`t1`.`b` at row 1 +SELECT * FROM t1; +a b +:: 2001-01-01 10:20:30 +DROP TABLE t1; +SET timestamp=DEFAULT; +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 (a) VALUES ('::'); +ALTER TABLE t1 MODIFY a DATE; +ERROR 22007: Incorrect date value: '::' for column `test`.`t1`.`a` at row 1 +DROP TABLE t1; +# +# MDEV-20818 ER_CRASHED_ON_USAGE or Assertion `length <= column->length' failed in write_block_record on temporary table +# +CREATE TABLE t1 (a INET6); +SELECT +CAST(a AS BINARY(0)), +CAST(a AS BINARY(1)), +CAST(a AS BINARY(16)), +CAST(a AS BINARY(255)), +CAST(a AS BINARY(256)), +CAST(a AS BINARY(512)), +CAST(a AS BINARY(513)), +CAST(a AS BINARY(65532)), +CAST(a AS BINARY(65533)), +CAST(a AS BINARY(65534)), +CAST(a AS BINARY(65535)), +CAST(a AS BINARY(65536)), +CAST(a AS BINARY(16777215)), +CAST(a AS BINARY(16777216)) +FROM t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def CAST(a AS BINARY(0)) 254 0 0 Y 128 0 63 +def CAST(a AS BINARY(1)) 254 1 0 Y 128 0 63 +def CAST(a AS BINARY(16)) 254 16 0 Y 128 0 63 +def CAST(a AS BINARY(255)) 254 255 0 Y 128 0 63 +def CAST(a AS BINARY(256)) 253 256 0 Y 128 0 63 +def CAST(a AS BINARY(512)) 253 512 0 Y 128 0 63 +def CAST(a AS BINARY(513)) 253 513 0 Y 128 0 63 +def CAST(a AS BINARY(65532)) 253 65532 0 Y 128 0 63 +def CAST(a AS BINARY(65533)) 252 65533 0 Y 128 0 63 +def CAST(a AS BINARY(65534)) 252 65534 0 Y 128 0 63 +def CAST(a AS BINARY(65535)) 252 65535 0 Y 128 0 63 +def CAST(a AS BINARY(65536)) 250 65536 0 Y 128 0 63 +def CAST(a AS BINARY(16777215)) 250 16777215 0 Y 128 0 63 +def CAST(a AS BINARY(16777216)) 251 16777216 0 Y 128 0 63 +CAST(a AS BINARY(0)) CAST(a AS BINARY(1)) CAST(a AS BINARY(16)) CAST(a AS BINARY(255)) CAST(a AS BINARY(256)) CAST(a AS BINARY(512)) CAST(a AS BINARY(513)) CAST(a AS BINARY(65532)) CAST(a AS BINARY(65533)) CAST(a AS BINARY(65534)) CAST(a AS BINARY(65535)) CAST(a AS BINARY(65536)) CAST(a AS BINARY(16777215)) CAST(a AS BINARY(16777216)) +DROP TABLE t1; +# +# MDEV-20826 Wrong result of MIN(inet6) with GROUP BY +# +CREATE TABLE t1 (id INT, a INET6) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1, 'fff::'),(1, '8888::'); +SELECT MIN(a), MAX(a) FROM t1 GROUP BY id; +MIN(a) MAX(a) +fff:: 8888:: +DROP TABLE t1; +# +# MDEV-20809 EXTRACT from INET6 value does not produce any warnings +# +CREATE TABLE t1 (a INET6); +SELECT EXTRACT(DAY FROM a) FROM t1; +ERROR HY000: Illegal parameter data type inet6 for operation 'extract(day)' +DROP TABLE t1; +SELECT EXTRACT(DAY FROM CAST('::' AS INET6)); +ERROR HY000: Illegal parameter data type inet6 for operation 'extract(day)' +# +# MDEV-22764 Crash with a stored aggregate function returning INET6 +# +CREATE OR REPLACE AGGREGATE FUNCTION aggregate_min_inet6(x INET6) RETURNS INET6 +BEGIN +DECLARE res INET6 DEFAULT NULL; +DECLARE CONTINUE HANDLER FOR NOT FOUND +RETURN res; +LOOP +FETCH GROUP NEXT ROW; +IF (res IS NULL) OR (res > x) THEN +SET res= x; +END IF; +END LOOP; +END; +$$ +CREATE OR REPLACE TABLE t1 (name CHAR(30), val INET6); +INSERT INTO t1 VALUES ('a', '::05'); +INSERT INTO t1 VALUES ('a', '::03'); +INSERT INTO t1 VALUES ('b', '::01'); +INSERT INTO t1 VALUES ('b', '::02'); +INSERT INTO t1 VALUES ('b', '::05'); +SELECT name, aggregate_min_inet6(val) pc FROM t1 GROUP BY name; +name pc +a ::3 +b ::1 +CREATE OR REPLACE TABLE t2 (name CHAR(30), val INET6); +INSERT INTO t2 SELECT name, aggregate_min_inet6(val) pc FROM t1 GROUP BY name; +SELECT * FROM t2; +name val +a ::3 +b ::1 +DROP TABLE t2; +DROP TABLE t1; +DROP FUNCTION aggregate_min_inet6; +# +# MDEV-20280 PERCENTILE_DISC() rejects temporal and string input +# +CREATE TABLE t1 (name CHAR(30), star_rating INET6); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', '::5'); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', '::3'); +INSERT INTO t1 VALUES ('Lady of the Flies', '::1'); +INSERT INTO t1 VALUES ('Lady of the Flies', '::2'); +INSERT INTO t1 VALUES ('Lady of the Flies', '::5'); +SELECT name, PERCENTILE_DISC(0.5) +WITHIN GROUP (ORDER BY star_rating) +OVER (PARTITION BY name) AS pc FROM t1; +name pc +Lady of the Flies ::2 +Lady of the Flies ::2 +Lady of the Flies ::2 +Lord of the Ladybirds ::3 +Lord of the Ladybirds ::3 +SELECT name, PERCENTILE_DISC(0) +WITHIN GROUP (ORDER BY star_rating) +OVER (PARTITION BY name) AS pc FROM t1; +name pc +Lady of the Flies ::1 +Lady of the Flies ::1 +Lady of the Flies ::1 +Lord of the Ladybirds ::3 +Lord of the Ladybirds ::3 +SELECT name, PERCENTILE_DISC(1) +WITHIN GROUP (ORDER BY star_rating) +OVER (PARTITION BY name) AS pc FROM t1; +name pc +Lady of the Flies ::5 +Lady of the Flies ::5 +Lady of the Flies ::5 +Lord of the Ladybirds ::5 +Lord of the Ladybirds ::5 +DROP TABLE t1; +# +# MDEV-22758 Assertion `!item->null_value' failed in Type_handler_inet6::make_sort_key_part +# +CREATE TABLE t1 (a VARCHAR(8) NOT NULL, b INET6 NOT NULL); +INSERT INTO t1 VALUES ('foo','::'),('bar','1::1'); +SELECT * FROM t1 ORDER BY CASE WHEN a THEN b ELSE a END; +a b +foo :: +bar 1::1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'foo' +Warning 1292 Incorrect inet6 value: 'foo' +Warning 1292 Truncated incorrect DOUBLE value: 'bar' +Warning 1292 Incorrect inet6 value: 'bar' +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a VARCHAR(8) NOT NULL); +INSERT INTO t1 VALUES ('foo'),('bar'); +SELECT * FROM t1 ORDER BY CAST(a AS INET6); +a +foo +bar +Warnings: +Warning 1292 Incorrect inet6 value: 'foo' +Warning 1292 Incorrect inet6 value: 'bar' +DROP TABLE t1; +CREATE TABLE t1 (a INET6 NOT NULL, b VARCHAR(32) NOT NULL); +CREATE TABLE t2 AS SELECT CAST(a AS INET6) AS ca, CAST(b AS INET6) AS cb FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `ca` inet6 NOT NULL, + `cb` inet6 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT COALESCE(a,a), COALESCE(a,b) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,a)` inet6 NOT NULL, + `COALESCE(a,b)` inet6 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT a AS ca,a AS cb FROM t1 UNION SELECT a,b FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `ca` inet6 NOT NULL DEFAULT '::', + `cb` inet6 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +# +# MDEV-22758 Assertion `!item->null_value' failed in Type_handler_inet6::make_sort_key_part +# +CREATE TABLE t1 (c INET6); +INSERT INTO t1 VALUES ('::'),(NULL); +SELECT * FROM t1 ORDER BY IFNULL(c, 'foo'); +c +NULL +:: +Warnings: +Warning 1292 Incorrect inet6 value: 'foo' +DROP TABLE t1; +CREATE TABLE t1 (c INET6); +INSERT INTO t1 VALUES ('::'),(NULL); +CREATE TABLE t2 AS SELECT IFNULL(c, 'foo') FROM t1; +Warnings: +Warning 1292 Incorrect inet6 value: 'foo' +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(c, 'foo')` inet6 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +IFNULL(c, 'foo') +:: +NULL +DROP TABLE t2; +CREATE TABLE t2 AS SELECT IFNULL(c, '::1') FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(c, '::1')` inet6 NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +IFNULL(c, '::1') +:: +::1 +DROP TABLE t2; +DROP TABLE t1; 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 00000000..ad4cfe57 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6.test @@ -0,0 +1,1588 @@ + +--echo # +--echo # Basic CREATE functionality, defaults, metadata +--echo # + +--error ER_WRONG_FIELD_SPEC +CREATE TABLE t1 (a INET6 AUTO_INCREMENT); + +CREATE TABLE t1 (a INET6); +SHOW CREATE TABLE t1; +DESCRIBE t1; +--vertical_results +--replace_column 19 # +SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' AND table_name='t1'; +--horizontal_results +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::1'); +--enable_metadata +SELECT * FROM t1; +SELECT CAST('::' AS INET6) AS a; +--disable_metadata +DROP TABLE t1; + + +CREATE TABLE t1 ( + c1 INET6 DEFAULT 0x00000000000000000000000000000000, + c2 INET6 DEFAULT 0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF, + c3 INET6 DEFAULT '::', + c4 INET6 DEFAULT 'FFFF::ffff', + c5 INET6 DEFAULT CAST(X'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF' AS INET6) +); +SHOW CREATE TABLE t1; +DESCRIBE t1; +--vertical_results +--replace_column 19 # +SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' AND table_name='t1'; +--horizontal_results +DROP TABLE t1; + +--error ER_INVALID_DEFAULT +CREATE TABLE t1 (c1 INET6 DEFAULT 0x00); +--error ER_INVALID_DEFAULT +CREATE TABLE t1 (c1 INET6 DEFAULT ''); + + +CREATE TABLE t1 (a INET6); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES ('x'); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES (1); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES (TIME'10:20:30'); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES (0x00); +DROP TABLE t1; + +--echo # +--echo # CAST +--echo # + +SELECT CAST('garbage' AS INET6); +SELECT CAST(0x01 AS INET6); +SELECT CAST(REPEAT(0x00,16) AS INET6); +SELECT CAST(REPEAT(0x11,16) AS INET6); + +CREATE TABLE t1 AS SELECT CAST('::' AS INET6); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # +--echo # Text and binary formats, comparison operators +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES (0x00000000000000000000000000000000); +INSERT INTO t1 VALUES (0x00000000000000000000000000000001); +INSERT INTO t1 VALUES (0xFFFF0000000000000000000000000001); +INSERT INTO t1 VALUES (0xFFFF0000000000000000000000000002); +SELECT * FROM t1 ORDER BY a; +SELECT * FROM t1 ORDER BY a DESC; +SELECT HEX(a),a FROM t1 ORDER BY a; +SELECT * FROM t1 WHERE a='::'; +SELECT * FROM t1 WHERE a='::1'; +SELECT * FROM t1 WHERE a='ffff::1'; +SELECT * FROM t1 WHERE a='ffff::2'; +SELECT * FROM t1 WHERE a=0x00000000000000000000000000000000; +SELECT * FROM t1 WHERE a=0x00000000000000000000000000000001; +SELECT * FROM t1 WHERE a=0xffff0000000000000000000000000001; +SELECT * FROM t1 WHERE a=0xffff0000000000000000000000000002; +SELECT * FROM t1 WHERE a<'::'; +SELECT * FROM t1 WHERE a<='::'; +SELECT * FROM t1 WHERE a>='ffff::2'; +SELECT * FROM t1 WHERE a>'ffff::2'; +SELECT * FROM t1 WHERE a IN ('::', 'ffff::1') ORDER BY a; +SELECT * FROM t1 WHERE a IN ('::', 0xffff0000000000000000000000000002) ORDER BY a; + +SELECT * FROM t1 WHERE a<'garbage'; +SELECT * FROM t1 WHERE a<='garbage'; +SELECT * FROM t1 WHERE a='garbage'; +SELECT * FROM t1 WHERE a>='garbage'; +SELECT * FROM t1 WHERE a>'garbage'; + +SELECT * FROM t1 WHERE a<0x01; +SELECT * FROM t1 WHERE a<=0x01; +SELECT * FROM t1 WHERE a=0x01; +SELECT * FROM t1 WHERE a>=0x01; +SELECT * FROM t1 WHERE a>0x01; + +SELECT * FROM t1 WHERE a='0::0'; +SELECT * FROM t1 WHERE a='0::00'; +SELECT * FROM t1 WHERE a='0::000'; +SELECT * FROM t1 WHERE a='0::0000'; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a=0; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a=0.0; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a=0e0; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a=TIME'10:20:30'; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a IN ('::', 10); + +DROP TABLE t1; + +--echo # +--echo # cmp_item_inet6: IN for non-constants +--echo # + +CREATE TABLE t1 (a INET6, b INET6); +INSERT INTO t1 VALUES ('::1', '::2'); +SELECT * FROM t1 WHERE '::' IN (a, b); +SELECT * FROM t1 WHERE '::1' IN (a, b); +SELECT * FROM t1 WHERE '::01' IN (a, b); +SELECT * FROM t1 WHERE '00::01' IN (a, b); +DROP TABLE t1; + + +--echo # +--echo # cmp_item_inet6: DECODE_ORACLE +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES (NULL),('::01'),('::02'); +SELECT a, DECODE_ORACLE(a, '::01', '01') AS d FROM t1; +SELECT + a, + DECODE_ORACLE(a, '::01', '01') AS d0, + DECODE_ORACLE(a, NULL, '<NULL>', '::01', '01') AS d1, + DECODE_ORACLE(a, 'garbage', '<NULL>', '::01', '01') AS d2 +FROM t1; +DROP TABLE t1; + + +--echo # +--echo # CASE abbreviations +--echo # + +CREATE TABLE t1 ( + c INET6, + c_char CHAR(32), + c_varchar VARCHAR(32), + c_tinytext TINYTEXT, + c_text TEXT, + c_mediumtext TEXT, + c_longtext LONGTEXT +); +CREATE TABLE t2 AS SELECT + COALESCE(c, c_char), + COALESCE(c, c_varchar), + COALESCE(c, c_tinytext), + COALESCE(c, c_text), + COALESCE(c, c_mediumtext), + COALESCE(c, c_longtext) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +CREATE TABLE t2 AS SELECT + LEAST(c, c_char), + LEAST(c, c_varchar), + LEAST(c, c_tinytext), + LEAST(c, c_text), + LEAST(c, c_mediumtext), + LEAST(c, c_longtext) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES (NULL),('::1'),('::2'); +SELECT COALESCE(a, '::') FROM t1 ORDER BY a; +SELECT a, LEAST(a,'::0'), LEAST(a,'::f') FROM t1 ORDER BY a; +SELECT a, GREATEST(a,'::0'), GREATEST(a,'::f') FROM t1 ORDER BY a; + +CREATE TABLE t2 AS SELECT + COALESCE(a, '::'), + LEAST(a,'::'), + GREATEST(a,'::') +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +SELECT COALESCE(a, 0x00000000000000000000000000000000) FROM t1 ORDER BY a; +SELECT a, + LEAST(a, 0x00000000000000000000000000000000), + LEAST(a, 0x0000000000000000000000000000000f) +FROM t1 ORDER BY a; +SELECT a, + GREATEST(a, 0x00000000000000000000000000000000), + GREATEST(a, 0x0000000000000000000000000000000f) +FROM t1 ORDER BY a; + +CREATE TABLE t2 AS SELECT + COALESCE(a, 0x00000000000000000000000000000000), + LEAST(a,0x00000000000000000000000000000000), + GREATEST(a,0x00000000000000000000000000000000) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(a, 10) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(a, 10) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT GREATEST(a, 10) FROM t1; +DROP TABLE t1; + +SELECT COALESCE('garbage', CAST('::1' AS INET6)); +SELECT COALESCE(0x01, CAST('::1' AS INET6)); + + +--echo # +--echo # Uniqueness +--echo # + +CREATE TABLE t1 (a INET6 NOT NULL PRIMARY KEY); +INSERT INTO t1 VALUES ('41::1'),('61::1'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('41::1'); +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # Indexes +--echo # + +--error ER_WRONG_SUB_KEY +CREATE TABLE t1 (a INET6, KEY(a(1))); + + +--echo # +--echo # Explicit CAST on INSERT +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES (CAST('1::1' AS INET6)); +INSERT INTO t1 VALUES (CAST('1::2' AS INET6)); +INSERT INTO t1 VALUES (CAST('1::3' AS INET6)); +INSERT INTO t1 VALUES (CAST(CONCAT('2','::1') AS INET6)); +INSERT INTO t1 VALUES (CAST(CONCAT('2','::2') AS INET6)); +INSERT INTO t1 VALUES (CAST(CONCAT('2','::3') AS INET6)); +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; + + +--echo # +--echo # Explicit CAST and implicit CAST on ALTER +--echo # + +CREATE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES ('garbage'),('::'),('::1'),('ffff::1'),('ffff::2'); +SELECT a, CAST(a AS INET6) FROM t1 ORDER BY a; +SELECT a, CAST(a AS INET6) FROM t1 ORDER BY CAST(a AS INET6); +--error ER_TRUNCATED_WRONG_VALUE +ALTER TABLE t1 MODIFY a INET6; +SET sql_mode=''; +ALTER TABLE t1 MODIFY a INET6; +SET sql_mode=DEFAULT; +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; + + +CREATE TABLE t1 (a BINARY(16)); +INSERT INTO t1 VALUES (0x00000000000000000000000000000000); +INSERT INTO t1 VALUES (0x00000000000000000000000000000001); +INSERT INTO t1 VALUES (0xffff0000000000000000000000000001); +INSERT INTO t1 VALUES (0xffff0000000000000000000000000002); +SELECT HEX(a), CAST(a AS INET6) FROM t1 ORDER BY a; +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; + + +--echo # +--echo # INSERT..SELECT, same data types +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::1'),('::2'); +CREATE TABLE t2 (a INET6); +INSERT INTO t2 SELECT a FROM t1; +SELECT * FROM t2; +DROP TABLE t1,t2; + + +--echo # +--echo # Implicit CAST on INSERT..SELECT, text format +--echo # + +CREATE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES ('garbage'),('::'),('::1'),('ffff::1'),('ffff::2'); + +CREATE TABLE t2 (a INET6); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t2 SELECT a FROM t1; +SET sql_mode=''; +INSERT INTO t2 SELECT a FROM t1; +SELECT * FROM t2 ORDER BY a; +SET sql_mode=DEFAULT; +DROP TABLE t2; + +CREATE TABLE t2 (a INET6 NOT NULL); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t2 SELECT a FROM t1; +SET sql_mode=''; +INSERT INTO t2 SELECT a FROM t1; +SELECT * FROM t2 ORDER BY a; +SET sql_mode=DEFAULT; +DROP TABLE t2; + +DROP TABLE t1; + + +--echo # +--echo # Implicit CAST on INSERT..SELECT, binary format +--echo # + +CREATE TABLE t1 (a BINARY(16)); +INSERT INTO t1 VALUES (0x00000000000000000000000000000000); +INSERT INTO t1 VALUES (0x00000000000000000000000000000001); +INSERT INTO t1 VALUES (0xffff0000000000000000000000000001); +INSERT INTO t1 VALUES (0xffff0000000000000000000000000002); +CREATE TABLE t2 (a INET6); +INSERT INTO t2 SELECT a FROM t1; +SELECT a FROM t2 ORDER BY a; +DROP TABLE t1,t2; + + +--echo # +--echo # CAST to other data types +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS DOUBLE); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS FLOAT); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS DECIMAL); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS SIGNED); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS UNSIGNED); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS TIME); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS DATE); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(CAST('::' AS INET6) AS DATETIME); + +SELECT CAST(CAST('::' AS INET6) AS CHAR); +CREATE TABLE t1 AS SELECT CAST(CAST('::' AS INET6) AS CHAR) AS a; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('ffff::ffff'); +CREATE TABLE t2 AS SELECT + CAST(a AS CHAR), + CAST(a AS CHAR(39)), + CAST(a AS CHAR(530)), + CAST(a AS CHAR(65535)), + CAST(a AS CHAR(66000)), + CAST(a AS CHAR(16777215)), + CAST(a AS CHAR(16777216)) +FROM t1; +SHOW CREATE TABLE t2; +--vertical_results +SELECT * FROM t2; +--horizontal_results +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('ffff::ffff'); +CREATE TABLE t2 AS SELECT + CAST(a AS BINARY(4)) AS cb4, + CAST(a AS BINARY) AS cb, + CAST(a AS BINARY(16)) AS cb16, + CAST(a AS BINARY(32)) AS cb32, + CAST(a AS BINARY(530)) AS cb530, + CAST(a AS BINARY(65535)) AS cb65535, + CAST(a AS BINARY(66000)) AS cb66000, + CAST(a AS BINARY(16777215)) AS cb16777215, + CAST(a AS BINARY(16777216)) AS cb16777216 +FROM t1 LIMIT 0; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + CAST(a AS BINARY(4)) AS cb4, + CAST(a AS BINARY) AS cb, + CAST(a AS BINARY(16)) AS cb16, + CAST(a AS BINARY(32)) AS cb32, + CAST(a AS BINARY(530)) AS cb530, + CAST(a AS BINARY(65535)) AS cb65535 +FROM t1; +SHOW CREATE TABLE t2; +--vertical_results +SELECT + HEX(cb4), + HEX(cb), + HEX(cb16), + HEX(cb32), + LENGTH(cb530), + LENGTH(cb65535) +FROM t2; +--horizontal_results +DROP TABLE t2; +DROP TABLE t1; + +--echo # +--echo # Implicit conversion to other types in INSERT +--echo # + +CREATE TABLE t1 (a INT); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (CAST('::' AS INET6)); +DROP TABLE t1; + +CREATE TABLE t1 (a DOUBLE); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (CAST('::' AS INET6)); +DROP TABLE t1; + +CREATE TABLE t1 (a DECIMAL(32,0)); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +INSERT INTO t1 VALUES (CAST('::' AS INET6)); +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES (CAST('::' AS INET6)); +DROP TABLE t1; + +CREATE TABLE t1 (a TEXT); +INSERT INTO t1 VALUES (CAST('::' AS INET6)); +DROP TABLE t1; + + + +--echo # +--echo # Boolean context +--echo # + +SELECT + CAST('::' AS INET6) IS TRUE, + CAST('::' AS INET6) IS FALSE, + CAST('::1' AS INET6) IS TRUE, + CAST('::1' AS INET6) IS FALSE; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::1'); +SELECT a, a IS TRUE, a IS FALSE FROM t1 ORDER BY a; +DROP TABLE t1; + +# +# TODO: Error looks like a bug. This should return rows where a<>'::'. +# The same problem is repeatable with GEOMETRY. +# +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::1'),('::2'); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT * FROM t1 WHERE a; +DROP TABLE t1; + + +--echo # +--echo # GROUP BY +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::'); +INSERT INTO t1 VALUES ('::1'),('::01'),('::0001'); +INSERT INTO t1 VALUES ('::2'),('::2'),('::2'),('::2'); +SELECT a, COUNT(*) FROM t1 GROUP BY a; +DROP TABLE t1; + +--echo # +--echo # Aggregate functions +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::'); +INSERT INTO t1 VALUES ('::1'),('::01'),('::0001'); +INSERT INTO t1 VALUES ('::2'),('::2'),('::2'),('::2'); +SELECT MIN(a),MAX(a) FROM t1; + +CREATE TABLE t2 AS SELECT MIN(a), MAX(a) FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT AVG(a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT AVG(DISTINCT a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT SUM(a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT SUM(DISTINCT a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT STDDEV(a) FROM t1; +SELECT GROUP_CONCAT(a ORDER BY a) FROM t1; +SELECT a, GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a; +DROP TABLE t1; + +--echo # +--echo # MDEV-21765 Possibly inconsistent behavior of BIT_xx functions with INET6 field +--echo # + +CREATE TABLE t1 (a INET6); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT BIT_AND(a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT BIT_OR(a) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT BIT_XOR(a) FROM t1; +DROP TABLE t1; + + +--echo # +--echo # Window functions +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::1'),('::2'),('::3'),('::4'); +SELECT + a, + LAG(a) OVER (ORDER BY a), + LEAD(a) OVER (ORDER BY a) +FROM t1 ORDER BY a; + +SELECT + a, + FIRST_VALUE(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), + LAST_VALUE(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +FROM t1 ORDER BY a; +DROP TABLE t1; + + +--echo # +--echo # Prepared statements +--echo # + +EXECUTE IMMEDIATE 'CREATE TABLE t1 AS SELECT ? AS a' USING CAST('::' AS INET6); +SHOW CREATE TABLE t1; +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING '::1'; +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING CAST('::2' AS INET6); +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING 0x00000000000000000000000000000003; +SELECT a FROM t1 ORDER BY a; +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING '::1'; +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING CAST('::2' AS INET6); +EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING 0x00000000000000000000000000000003; +DROP TABLE t1; + + +--echo # +--echo # Character set and collation aggregation +--echo # + +CREATE TABLE t1 (a INET6); + +CREATE TABLE t2 AS SELECT + CONCAT(a) AS c1, + CONCAT(CAST('::' AS INET6)) AS c2 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + CONCAT(_utf8'1', a) AS c1, + CONCAT(_utf8'1', CAST('::1' AS INET6)) AS c2, + CONCAT(_utf8'1', COALESCE(a)) AS c3 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + CONCAT(_latin1'1', a) AS c1, + CONCAT(_latin1'1', CAST('::1' AS INET6)) AS c2, + CONCAT(_latin1'1', COALESCE(a)) AS c3 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +DROP TABLE t1; + + +--echo # +--echo # UNION +--echo # + +CREATE TABLE t1 AS SELECT CAST('::' AS INET6) AS c UNION SELECT CAST('::1' AS INET6); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT CAST('::' AS INET6) AS c UNION SELECT '::1'; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT '::' AS c UNION SELECT CAST('::1' AS INET6); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT CAST('::' AS INET6) AS c UNION SELECT 0x00000000000000000000000000000001; +SELECT * FROM t1; +DROP TABLE t1; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +CREATE TABLE t1 AS SELECT CAST('::' AS INET6) AS c UNION SELECT 1; + + +--echo # +--echo # Unary operators +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT -CAST('::' AS INET6); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT ABS(CAST('::' AS INET6)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT ROUND(CAST('::' AS INET6)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CEILING(CAST('::' AS INET6)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT FLOOR(CAST('::' AS INET6)); + + +--echo # +--echo # Arithmetic operators +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT CAST('::' AS INET6) + 1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT CAST('::' AS INET6) - 1; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT CAST('::' AS INET6) * 1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT CAST('::' AS INET6) / 1; + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT CAST('::' AS INET6) MOD 1; + + +--echo # +--echo # Misc +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT RAND(CAST('::' AS INET6)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT FROM_UNIXTIME(CAST('::' AS INET6)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT HOUR(CAST('::' AS INET6)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT YEAR(CAST('::' AS INET6)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT RELEASE_LOCK(CAST('::' AS INET6)); + + +SELECT JSON_LENGTH(CAST('::' AS INET6)); + +--echo # +--echo # Virtual columns +--echo # + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + a INT, + b INET6 GENERATED ALWAYS AS (CAST(CONCAT(RAND(),a) AS INET6)), INDEX(b) +); + +CREATE TABLE t1 ( + a INT, + b INET6 GENERATED ALWAYS AS (CAST(CONCAT('::',HEX(a)) AS INET6)), INDEX(b) +); +INSERT INTO t1 (a) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15); +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # VIEW +--echo # + +CREATE TABLE t1 (a INT DEFAULT 0); +INSERT INTO t1 (a) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15); +SELECT * FROM t1 ORDER BY a; +CREATE VIEW v1 AS SELECT (CAST(CONCAT('::',HEX(a)) AS INET6)) AS c FROM t1; +SELECT * FROM v1 ORDER BY c; +DROP VIEW v1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6 DEFAULT '::'); +CREATE VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +DESCRIBE v1; +INSERT INTO v1 VALUES ('::'),('::1'),('::2'); +SELECT * FROM t1; +DROP VIEW v1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6 DEFAULT CAST('::' AS INET6)); +CREATE VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +DESCRIBE v1; +INSERT INTO v1 VALUES ('::'),('::1'),('::2'); +SELECT * FROM t1; +DROP VIEW v1; +DROP TABLE t1; + + +--echo # +--echo # Subqueries +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'),('::1'),('::2'); +SELECT * FROM t1 WHERE a=(SELECT MIN(a) FROM t1) ORDER BY a; +SELECT * FROM t1 WHERE a=(SELECT MAX(a) FROM t1) ORDER BY a; +SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 WHERE a>'::') ORDER BY a; +DROP TABLE t1; + +--echo # +--echo # Stored routines +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(a INET6) +BEGIN + DECLARE b INET6 DEFAULT CONCAT('1', a); + SELECT a, b; +END; +$$ +DELIMITER ;$$ +CALL p1('::1'); +CALL p1(CAST('::2' AS INET6)); +DROP PROCEDURE p1; + +DELIMITER $$; +CREATE FUNCTION f1(a INET6) RETURNS INET6 +BEGIN + RETURN CONCAT('1',a); +END; +$$ +DELIMITER ;$$ +SELECT f1('::1'); +SELECT f1(CAST('::1' AS INET6)); +DROP FUNCTION f1; + +--echo # +--echo # Anchored data types in SP variables +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::1'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE va TYPE OF t1.a; + SELECT MAX(a) INTO va FROM t1; + SELECT va; +END; +$$ +DELIMITER ;$$ +CALL p1; +DROP PROCEDURE p1; +DROP TABLE t1; + + +CREATE TABLE t1 (a INET6, b INET6); +INSERT INTO t1 VALUES ('::a', '::b'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE va ROW TYPE OF t1; + SELECT MAX(a), MAX(b) INTO va FROM t1; + SELECT va.a, va.b; +END; +$$ +DELIMITER ;$$ +CALL p1; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Optimizer: make_const_item_for_comparison +--echo # + +CREATE TABLE t1 (id INT, a INET6); +INSERT INTO t1 VALUES (1,'::1'),(2,'::2'); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=COALESCE(CAST('::1' AS INET6)) AND id>0; +DROP TABLE t1; + +--echo # +--echo # Optimizer: equal field propagation +--echo # + +CREATE TABLE t1 (id INT, a INET6); +INSERT INTO t1 VALUES (1,'::1'),(2,'::2'); +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE a=COALESCE(CAST('::1' AS INET6)) + AND LENGTH(CONCAT(a,RAND()))>1; +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE a=COALESCE(CAST('::1' AS INET6)) + AND LENGTH(a)>1; +DROP TABLE t1; + + +--echo # +--echo # Optimizer: equal expression propagation +--echo # + + +CREATE TABLE t1 (id INT, a INET6); +INSERT INTO t1 VALUES (1,'::1'),(2,'::2'); +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE COALESCE(a)='::1' AND COALESCE(a)=CONCAT(a); +DROP TABLE t1; + +--echo # +--echo # Subquery materialization +--echo # + +CREATE TABLE t1 (a INET6, b VARCHAR(32), KEY (a), KEY(b)) ; +INSERT INTO t1 VALUES ('::a','::a'),('::a','::b'); +SET @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=off,subquery_cache=off'; +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS a_inner FROM t1 GROUP BY a_inner); +EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT a AS a_inner FROM t1 GROUP BY a_inner); +SET @@optimizer_switch=DEFAULT; +DROP TABLE t1; + +--echo # +--echo # IS_IPV4_MAPPED(), IS_IPV4_COMPAT() now understand text notation +--echo # +CREATE TABLE t1 (id SERIAL, a VARCHAR(32)); +INSERT INTO t1 (a) VALUES ('::192.168.0.1'),('::192.168.10.111'),('::ffff:10.10.0.1'),('::ffff:192.168.0.1'); +--echo # This is a text notation +SELECT id, length(a), a, IS_IPV4_MAPPED(a) FROM t1 ORDER BY id; +SELECT id, length(a), a, IS_IPV4_COMPAT(a) FROM t1 ORDER BY id; +--echo # This is not a text notation: it is a binary input only looking like text notation +SELECT id, length(a), a, IS_IPV4_MAPPED(BINARY a) FROM t1 ORDER BY id; +SELECT id, length(a), a, IS_IPV4_COMPAT(BINARY a) FROM t1 ORDER BY id; +DROP TABLE t1; + + +--echo # +--echo # ALTER from INET6 to INET6 +--echo # + +CREATE TABLE t1 (a INET6, b INT); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329', 1); +ALTER TABLE t1 MODIFY b DECIMAL(10,2); +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # ALTER to character string data types +--echo # + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS CHAR(39)) FROM t1; +ALTER TABLE t1 MODIFY a CHAR(39); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a VARCHAR(39); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a TINYTEXT; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a TEXT; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a MEDIUMTEXT; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a LONGTEXT; +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # ALTER from character string data types +--echo # + +CREATE OR REPLACE TABLE t1 (a CHAR(64)); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a TINYTEXT); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a TEXT); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a MEDIUMTEXT); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a LONGTEXT); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS INET6) FROM t1; +ALTER TABLE t1 MODIFY a INET6; +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # ALTER to binary string data types +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a BINARY(16); +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a BINARY(17); +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +--error ER_DATA_TOO_LONG +ALTER TABLE t1 MODIFY a BINARY(15); +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a TINYBLOB; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a BLOB; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a MEDIUMBLOB; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a LONGBLOB; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + + +--echo # +--echo # ALTER from binary string data types +--echo # + +CREATE TABLE t1 (a BINARY(16)); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a INET6; +SELECT a FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a BINARY(17)); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF000042832900'); +--error ER_TRUNCATED_WRONG_VALUE +ALTER TABLE t1 MODIFY a INET6; +DROP TABLE t1; + +CREATE TABLE t1 (a BINARY(15)); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF00004283'); +--error ER_TRUNCATED_WRONG_VALUE +ALTER TABLE t1 MODIFY a INET6; +DROP TABLE t1; + +CREATE TABLE t1 (a TINYBLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a INET6; +SELECT a FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a BLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a INET6; +SELECT a FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a MEDIUMBLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a INET6; +SELECT a FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a BLOB); +INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329'); +ALTER TABLE t1 MODIFY a INET6; +SELECT a FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from INET6 to INET6 +--echo # + +CREATE TABLE t1 (a INET6, b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +--echo # +--echo # SET from INET6 to numeric +--echo # + +CREATE TABLE t1 (a INET6, b INT); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b DOUBLE); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b DECIMAL(32,0)); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b YEAR); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from numeric to INET6 +--echo # + +CREATE TABLE t1 (a INT, b INET6); +INSERT INTO t1 VALUES (1, NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a DOUBLE, b INET6); +INSERT INTO t1 VALUES (1, NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a DECIMAL(32,0), b INET6); +INSERT INTO t1 VALUES (1, NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a YEAR, b INET6); +INSERT INTO t1 VALUES (1, NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from INET6 to temporal +--echo # + +CREATE TABLE t1 (a INET6, b TIME); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b DATE); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b DATETIME); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b TIMESTAMP NULL DEFAULT NULL); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from temporal to INET6 +--echo # + +CREATE TABLE t1 (a TIME, b INET6); +INSERT INTO t1 VALUES ('00:00:00', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a DATE, b INET6); +INSERT INTO t1 VALUES ('2001-01:01', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a DATETIME, b INET6); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TIMESTAMP, b INET6); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30', NULL); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from INET6 to character string +--echo # + +CREATE TABLE t1 (a INET6, b CHAR(39)); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b VARCHAR(39)); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b TEXT); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b ENUM('ffff::ffff')); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b SET('ffff::ffff')); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from character string to INET6 +--echo # + +CREATE TABLE t1 (a CHAR(39), b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(39), b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TEXT, b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a ENUM('ffff::ffff'), b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a SET('ffff::ffff'), b INET6); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from INET6 to binary +--echo # + +CREATE TABLE t1 (a INET6, b BINARY(16)); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT HEX(b) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b VARBINARY(39)); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT HEX(b) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INET6, b BLOB); +INSERT INTO t1 VALUES ('ffff::ffff', NULL); +UPDATE t1 SET b=a; +SELECT HEX(b) FROM t1; +DROP TABLE t1; + + +--echo # +--echo # SET from binary to INET6 +--echo # + +CREATE TABLE t1 (a BINARY(16), b INET6); +INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a VARBINARY(16), b INET6); +INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a BLOB, b INET6); +INSERT INTO t1 VALUES (CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF), NULL); +UPDATE t1 SET b=a; +SELECT b FROM t1; +DROP TABLE t1; + + +--echo # +--echo # Limit clause parameter +--echo # TODO: this should fail. +--echo # The test for a valid data type should be moved +--echo # from parse time to fix_fields() time, and performed +--echo # for both Item_splocal and Item_param. +--echo # + +EXECUTE IMMEDIATE 'SELECT 1 FROM DUAL LIMIT ?' USING CAST('::' AS INET6); + + +## TODO: +## - Add hooks to run mysql_client_test with pluggable data types +## +## - This should fail with the "illegal data type" error: +##SELECT CAST('::' AS INET6) DIV 1; +## +## - This should fail with the "illegal data type" error: +## EXTRACT(MINUTE...) +## + + +--echo # +--echo # MDEV-20785 Converting INET6 to CHAR(39) produces garbage without a warning +--echo # + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT CAST(a AS CHAR(39)) FROM t1; +ALTER TABLE t1 MODIFY a CHAR(39); +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-20783 INET6 cannot be converted to BINARY(16) (requires clarification in documentation) +--echo # + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +ALTER TABLE t1 MODIFY a BINARY(16); +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-20795 CAST(inet6 AS BINARY) returns wrong result +--echo # + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT HEX(CAST(a AS BINARY)) FROM t1; +SELECT HEX(CAST(a AS BINARY(16))) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-20808 CAST from INET6 to FLOAT does not produce an error +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('::'); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(a AS FLOAT) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-20798 Conversion from INET6 to other types performed without errors or warnings +--echo # + +CREATE TABLE t1 (a INET6, b INT); +INSERT INTO t1 (a) VALUES ('::'); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +UPDATE t1 SET b=a; +SELECT * FROM t1; +DROP TABLE t1; + +SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); +CREATE TABLE t1 (a INET6, b TIMESTAMP); +INSERT INTO t1 (a) VALUES ('::'); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET b=a; +SELECT * FROM t1; +DROP TABLE t1; +SET timestamp=DEFAULT; + +CREATE OR REPLACE TABLE t1 (a INET6); +INSERT INTO t1 (a) VALUES ('::'); +--error ER_TRUNCATED_WRONG_VALUE +ALTER TABLE t1 MODIFY a DATE; +DROP TABLE t1; + +--echo # +--echo # MDEV-20818 ER_CRASHED_ON_USAGE or Assertion `length <= column->length' failed in write_block_record on temporary table +--echo # + +CREATE TABLE t1 (a INET6); +--enable_metadata +SELECT + CAST(a AS BINARY(0)), + CAST(a AS BINARY(1)), + CAST(a AS BINARY(16)), + CAST(a AS BINARY(255)), + CAST(a AS BINARY(256)), + CAST(a AS BINARY(512)), + CAST(a AS BINARY(513)), + CAST(a AS BINARY(65532)), + CAST(a AS BINARY(65533)), + CAST(a AS BINARY(65534)), + CAST(a AS BINARY(65535)), + CAST(a AS BINARY(65536)), + CAST(a AS BINARY(16777215)), + CAST(a AS BINARY(16777216)) +FROM t1; +--disable_metadata +DROP TABLE t1; + +--echo # +--echo # MDEV-20826 Wrong result of MIN(inet6) with GROUP BY +--echo # + +CREATE TABLE t1 (id INT, a INET6) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1, 'fff::'),(1, '8888::'); +SELECT MIN(a), MAX(a) FROM t1 GROUP BY id; +DROP TABLE t1; + + +--echo # +--echo # MDEV-20809 EXTRACT from INET6 value does not produce any warnings +--echo # + +CREATE TABLE t1 (a INET6); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT EXTRACT(DAY FROM a) FROM t1; +DROP TABLE t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT EXTRACT(DAY FROM CAST('::' AS INET6)); + + +--echo # +--echo # MDEV-22764 Crash with a stored aggregate function returning INET6 +--echo # + +DELIMITER $$; +CREATE OR REPLACE AGGREGATE FUNCTION aggregate_min_inet6(x INET6) RETURNS INET6 +BEGIN + DECLARE res INET6 DEFAULT NULL; + DECLARE CONTINUE HANDLER FOR NOT FOUND + RETURN res; + LOOP + FETCH GROUP NEXT ROW; + IF (res IS NULL) OR (res > x) THEN + SET res= x; + END IF; + END LOOP; +END; +$$ +DELIMITER ;$$ + +CREATE OR REPLACE TABLE t1 (name CHAR(30), val INET6); +INSERT INTO t1 VALUES ('a', '::05'); +INSERT INTO t1 VALUES ('a', '::03'); +INSERT INTO t1 VALUES ('b', '::01'); +INSERT INTO t1 VALUES ('b', '::02'); +INSERT INTO t1 VALUES ('b', '::05'); +SELECT name, aggregate_min_inet6(val) pc FROM t1 GROUP BY name; + +CREATE OR REPLACE TABLE t2 (name CHAR(30), val INET6); +INSERT INTO t2 SELECT name, aggregate_min_inet6(val) pc FROM t1 GROUP BY name; +SELECT * FROM t2; +DROP TABLE t2; + +DROP TABLE t1; +DROP FUNCTION aggregate_min_inet6; + + +--echo # +--echo # MDEV-20280 PERCENTILE_DISC() rejects temporal and string input +--echo # + +CREATE TABLE t1 (name CHAR(30), star_rating INET6); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', '::5'); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', '::3'); +INSERT INTO t1 VALUES ('Lady of the Flies', '::1'); +INSERT INTO t1 VALUES ('Lady of the Flies', '::2'); +INSERT INTO t1 VALUES ('Lady of the Flies', '::5'); +SELECT name, PERCENTILE_DISC(0.5) + WITHIN GROUP (ORDER BY star_rating) + OVER (PARTITION BY name) AS pc FROM t1; +SELECT name, PERCENTILE_DISC(0) + WITHIN GROUP (ORDER BY star_rating) + OVER (PARTITION BY name) AS pc FROM t1; +SELECT name, PERCENTILE_DISC(1) + WITHIN GROUP (ORDER BY star_rating) + OVER (PARTITION BY name) AS pc FROM t1; +DROP TABLE t1; + + +--echo # +--echo # MDEV-22758 Assertion `!item->null_value' failed in Type_handler_inet6::make_sort_key_part +--echo # + +CREATE TABLE t1 (a VARCHAR(8) NOT NULL, b INET6 NOT NULL); +INSERT INTO t1 VALUES ('foo','::'),('bar','1::1'); +SELECT * FROM t1 ORDER BY CASE WHEN a THEN b ELSE a END; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a VARCHAR(8) NOT NULL); +INSERT INTO t1 VALUES ('foo'),('bar'); +SELECT * FROM t1 ORDER BY CAST(a AS INET6); +DROP TABLE t1; + +CREATE TABLE t1 (a INET6 NOT NULL, b VARCHAR(32) NOT NULL); +CREATE TABLE t2 AS SELECT CAST(a AS INET6) AS ca, CAST(b AS INET6) AS cb FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT COALESCE(a,a), COALESCE(a,b) FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT a AS ca,a AS cb FROM t1 UNION SELECT a,b FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +DROP TABLE t1; + +--echo # +--echo # MDEV-22758 Assertion `!item->null_value' failed in Type_handler_inet6::make_sort_key_part +--echo # + +CREATE TABLE t1 (c INET6); +INSERT INTO t1 VALUES ('::'),(NULL); +SELECT * FROM t1 ORDER BY IFNULL(c, 'foo'); +DROP TABLE t1; + +CREATE TABLE t1 (c INET6); +INSERT INTO t1 VALUES ('::'),(NULL); + +# Expect a NULL column +CREATE TABLE t2 AS SELECT IFNULL(c, 'foo') FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; + +# Expect a NOT NULL column +CREATE TABLE t2 AS SELECT IFNULL(c, '::1') FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; + +DROP TABLE t1; diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_csv.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_csv.result new file mode 100644 index 00000000..8e972235 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_csv.result @@ -0,0 +1,70 @@ +# +# Start of 10.5 tests +# +# +# MDEV-274 The data type for IPv6/IPv4 addresses in MariaDB +# +SET default_storage_engine=CSV; +CREATE TABLE t1 (a INET6 NOT NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` inet6 NOT NULL +) ENGINE=CSV 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 +SELECT * FROM t1 WHERE a>='::fe' ORDER BY a; +a +::fe +::ff +SELECT * FROM t1 WHERE a IN ('::80','::a0','::f0') ORDER BY a; +a +::80 +::a0 +::f0 +SELECT * FROM t1 WHERE a BETWEEN '::80' AND '::81' ORDER BY a; +a +::80 +::81 +SELECT * FROM t1 WHERE a=CAST('::ff' AS INET6); +a +::ff +UPDATE t1 SET a=CONCAT('ffff', a) WHERE a LIKE '::a%'; +SELECT * FROM t1 WHERE a LIKE 'ffff::%' ORDER BY a; +a +ffff::a +ffff::a0 +ffff::a1 +ffff::a2 +ffff::a3 +ffff::a4 +ffff::a5 +ffff::a6 +ffff::a7 +ffff::a8 +ffff::a9 +ffff::aa +ffff::ab +ffff::ac +ffff::ad +ffff::ae +ffff::af +DROP TABLE t1; +# +# MDEV-20790 CSV table with INET6 can be created and inserted into, but cannot be read from +# +CREATE TABLE t1 (a INET6 NOT NULL) ENGINE=CSV; +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT * FROM t1; +a +2001:db8::ff00:42:8329 +DROP TABLE t1; +# +# End of 10.5 tests +# diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_csv.test b/plugin/type_inet/mysql-test/type_inet/type_inet6_csv.test new file mode 100644 index 00000000..65761cf0 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_csv.test @@ -0,0 +1,51 @@ +--source include/have_csv.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=CSV; + +CREATE TABLE t1 (a INET6 NOT NULL); +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'; + +SELECT * FROM t1 WHERE a>='::fe' ORDER BY a; + +SELECT * FROM t1 WHERE a IN ('::80','::a0','::f0') ORDER BY a; + +SELECT * FROM t1 WHERE a BETWEEN '::80' AND '::81' ORDER BY a; + +SELECT * FROM t1 WHERE a=CAST('::ff' AS INET6); + +UPDATE t1 SET a=CONCAT('ffff', a) WHERE a LIKE '::a%'; +SELECT * FROM t1 WHERE a LIKE 'ffff::%' ORDER BY a; + +DROP TABLE t1; + +--echo # +--echo # MDEV-20790 CSV table with INET6 can be created and inserted into, but cannot be read from +--echo # + +CREATE TABLE t1 (a INET6 NOT NULL) ENGINE=CSV; +INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # End of 10.5 tests +--echo # 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 00000000..596036fc --- /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 00000000..5f7063b8 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_innodb.result @@ -0,0 +1,92 @@ +# +# 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' +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' +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 +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 +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' +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' +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 +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 +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 00000000..dd6049ab --- /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 00000000..db419636 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_memory.result @@ -0,0 +1,159 @@ +# +# 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' +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' +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' +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' +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 00000000..da3f8389 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_memory.test @@ -0,0 +1,16 @@ +--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 00000000..c8dba6ff --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_myisam.result @@ -0,0 +1,92 @@ +# +# 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' +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' +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 +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 +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' +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' +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 +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 +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 00000000..c5183f01 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_myisam.test @@ -0,0 +1,16 @@ +--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 00000000..868b9902 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_mysql.result @@ -0,0 +1,39 @@ +CREATE TABLE t1 (a INET6); +Field 1: `a` +Catalog: `def` +Database: `test` +Table: `t1` +Org_table: `t1` +Type: STRING (type=inet6) +Collation: latin1_swedish_ci (8) +Length: 39 +Max_length: 0 +Decimals: 0 +Flags: UNSIGNED BINARY + +Field 2: `b` +Catalog: `def` +Database: `` +Table: `` +Org_table: `` +Type: STRING (type=inet6) +Collation: latin1_swedish_ci (8) +Length: 39 +Max_length: 0 +Decimals: 0 +Flags: NOT_NULL UNSIGNED + +Field 3: `c` +Catalog: `def` +Database: `` +Table: `` +Org_table: `` +Type: STRING (type=inet6) +Collation: latin1_swedish_ci (8) +Length: 39 +Max_length: 0 +Decimals: 0 +Flags: UNSIGNED + + +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 00000000..dfb30081 --- /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 a, CAST('::' AS INET6) AS b, COALESCE(a) AS c FROM t1" 2>&1 +DROP TABLE t1; diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_oracle.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_oracle.result new file mode 100644 index 00000000..8b041e45 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_oracle.result @@ -0,0 +1,29 @@ +# +# Start of 10.5 tests +# +# +# MDEV-20913 sql_mode=ORACLE: INET6 does not work as a routine parameter type and return type +# +SET sql_mode=ORACLE; +CREATE OR REPLACE FUNCTION f1() RETURN INET6 AS +BEGIN +RETURN 'ffff::ffff'; +END; +$$ +SELECT f1(); +f1() +ffff::ffff +DROP FUNCTION f1; +SET sql_mode=ORACLE; +CREATE OR REPLACE FUNCTION f1(a INET6) RETURN INT AS +BEGIN +RETURN LENGTH(a); +END; +$$ +SELECT f1('0::0'); +f1('0::0') +2 +DROP FUNCTION f1; +# +# End of 10.5 tests +# diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_oracle.test b/plugin/type_inet/mysql-test/type_inet/type_inet6_oracle.test new file mode 100644 index 00000000..46754bf9 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_oracle.test @@ -0,0 +1,35 @@ +--echo # +--echo # Start of 10.5 tests +--echo # + +--echo # +--echo # MDEV-20913 sql_mode=ORACLE: INET6 does not work as a routine parameter type and return type +--echo # + +SET sql_mode=ORACLE; +DELIMITER $$; +CREATE OR REPLACE FUNCTION f1() RETURN INET6 AS +BEGIN + RETURN 'ffff::ffff'; +END; +$$ +DELIMITER ;$$ +SELECT f1(); +DROP FUNCTION f1; + + +SET sql_mode=ORACLE; +DELIMITER $$; +CREATE OR REPLACE FUNCTION f1(a INET6) RETURN INT AS +BEGIN + RETURN LENGTH(a); +END; +$$ +DELIMITER ;$$ +SELECT f1('0::0'); +DROP FUNCTION f1; + + +--echo # +--echo # End of 10.5 tests +--echo # diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_partition.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_partition.result new file mode 100644 index 00000000..48f10a39 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_partition.result @@ -0,0 +1,29 @@ +# +# MDEV-20831 Table partitioned by LIST/RANGE COLUMNS(inet6) can be created, but not inserted into +# +SET NAMES utf8; +CREATE TABLE t1 (a INET6) +PARTITION BY LIST COLUMNS(a) +(PARTITION p00 VALUES IN (10)); +ERROR HY000: Partition column values of incorrect type +CREATE TABLE t1 (a INET6) +PARTITION BY LIST COLUMNS(a) +(PARTITION p00 VALUES IN (TIME'10:20:30')); +ERROR HY000: Partition column values of incorrect type +CREATE TABLE t1 (a INET6) +PARTITION BY LIST COLUMNS(a) +(PARTITION p00 VALUES IN ('€')); +ERROR 22007: Incorrect inet6 value: '€' +CREATE TABLE t1 (a INET6) +PARTITION BY LIST COLUMNS(a) +(PARTITION p00 VALUES IN ('::'), +PARTITION pFF VALUES IN (0xFFFF000000000000000000000000FFFF)); +INSERT INTO t1 VALUES ('::'); +INSERT INTO t1 VALUES ('ffff::ffff'); +SELECT * FROM t1 PARTITION (p00); +a +:: +SELECT * FROM t1 PARTITION (pFF); +a +ffff::ffff +DROP TABLE t1; diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_partition.test b/plugin/type_inet/mysql-test/type_inet/type_inet6_partition.test new file mode 100644 index 00000000..76ab2478 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_partition.test @@ -0,0 +1,32 @@ +--source include/have_partition.inc + +--echo # +--echo # MDEV-20831 Table partitioned by LIST/RANGE COLUMNS(inet6) can be created, but not inserted into +--echo # + +SET NAMES utf8; + +--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR +CREATE TABLE t1 (a INET6) + PARTITION BY LIST COLUMNS(a) + (PARTITION p00 VALUES IN (10)); + +--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR +CREATE TABLE t1 (a INET6) + PARTITION BY LIST COLUMNS(a) + (PARTITION p00 VALUES IN (TIME'10:20:30')); + +--error ER_TRUNCATED_WRONG_VALUE +CREATE TABLE t1 (a INET6) + PARTITION BY LIST COLUMNS(a) + (PARTITION p00 VALUES IN ('€')); + +CREATE TABLE t1 (a INET6) + PARTITION BY LIST COLUMNS(a) + (PARTITION p00 VALUES IN ('::'), + PARTITION pFF VALUES IN (0xFFFF000000000000000000000000FFFF)); +INSERT INTO t1 VALUES ('::'); +INSERT INTO t1 VALUES ('ffff::ffff'); +SELECT * FROM t1 PARTITION (p00); +SELECT * FROM t1 PARTITION (pFF); +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 00000000..200ab307 --- /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 INET6 +PLUGIN_LICENSE GPL +PLUGIN_MATURITY Stable +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 00000000..ccc22b16 --- /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/mysql-test/type_inet/type_inet6_stat_tables.result b/plugin/type_inet/mysql-test/type_inet/type_inet6_stat_tables.result new file mode 100644 index 00000000..1cbedad1 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_stat_tables.result @@ -0,0 +1,31 @@ +# +# Start of 10.5 tests +# +# +# MDEV-20800 Server crashes in Field_inet6::store_warning upon updating table statistics +# +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('1::1'),('2::2'); +ANALYZE TABLE t1 PERSISTENT FOR ALL; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +INSERT INTO t1 VALUES ('3::3'); +DROP TABLE t1; +# +# MDEV-22509: Server crashes in Field_inet6::store_inet6_null_with_warn / Field::maybe_null +# +CREATE TABLE t1 (a INT, b INET6 NOT NULL); +INSERT INTO t1 VALUES (1,'::'),(2,'::'); +ANALYZE TABLE t1 PERSISTENT FOR ALL; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT t1.a from t1; +a +1 +2 +DROP TABLE t1; +# +# End of 10.5 tests +# diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6_stat_tables.test b/plugin/type_inet/mysql-test/type_inet/type_inet6_stat_tables.test new file mode 100644 index 00000000..063581b1 --- /dev/null +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6_stat_tables.test @@ -0,0 +1,29 @@ +--source include/have_stat_tables.inc + +--echo # +--echo # Start of 10.5 tests +--echo # + +--echo # +--echo # MDEV-20800 Server crashes in Field_inet6::store_warning upon updating table statistics +--echo # + +CREATE TABLE t1 (a INET6); +INSERT INTO t1 VALUES ('1::1'),('2::2'); +ANALYZE TABLE t1 PERSISTENT FOR ALL; +INSERT INTO t1 VALUES ('3::3'); +DROP TABLE t1; + +--echo # +--echo # MDEV-22509: Server crashes in Field_inet6::store_inet6_null_with_warn / Field::maybe_null +--echo # + +CREATE TABLE t1 (a INT, b INET6 NOT NULL); +INSERT INTO t1 VALUES (1,'::'),(2,'::'); +ANALYZE TABLE t1 PERSISTENT FOR ALL; +SELECT t1.a from t1; +DROP TABLE t1; + +--echo # +--echo # End of 10.5 tests +--echo # |