From a175314c3e5827eb193872241446f2f8f5c9d33c Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 20:07:14 +0200 Subject: Adding upstream version 1:10.5.12. Signed-off-by: Daniel Baumann --- plugin/type_inet/CMakeLists.txt | 18 + plugin/type_inet/item_inetfunc.cc | 256 +++ plugin/type_inet/item_inetfunc.h | 227 ++ .../type_inet/binlog_stm_type_inet6.result | 34 + .../type_inet/binlog_stm_type_inet6.test | 28 + ...g_table_map_optional_metadata_type_inet6.result | 60 + ...log_table_map_optional_metadata_type_inet6.test | 72 + .../mysql-test/type_inet/func_inet_plugin.result | 112 + .../mysql-test/type_inet/func_inet_plugin.test | 37 + .../type_inet/rpl_row_binary_to_inet6.result | 35 + .../type_inet/rpl_row_binary_to_inet6.test | 33 + .../type_inet/rpl_row_inet6_to_binary.result | 35 + .../type_inet/rpl_row_inet6_to_binary.test | 33 + .../mysql-test/type_inet/rpl_type_inet6.result | 17 + .../mysql-test/type_inet/rpl_type_inet6.test | 16 + plugin/type_inet/mysql-test/type_inet/suite.pm | 9 + .../mysql-test/type_inet/type_inet6-debug.result | 18 + .../mysql-test/type_inet/type_inet6-debug.test | 14 + .../mysql-test/type_inet/type_inet6.result | 2161 ++++++++++++++++++++ .../type_inet/mysql-test/type_inet/type_inet6.test | 1588 ++++++++++++++ .../mysql-test/type_inet/type_inet6_csv.result | 70 + .../mysql-test/type_inet/type_inet6_csv.test | 51 + .../mysql-test/type_inet/type_inet6_engines.inc | 38 + .../mysql-test/type_inet/type_inet6_innodb.result | 92 + .../mysql-test/type_inet/type_inet6_innodb.test | 18 + .../mysql-test/type_inet/type_inet6_memory.result | 159 ++ .../mysql-test/type_inet/type_inet6_memory.test | 16 + .../mysql-test/type_inet/type_inet6_myisam.result | 92 + .../mysql-test/type_inet/type_inet6_myisam.test | 16 + .../mysql-test/type_inet/type_inet6_mysql.result | 39 + .../mysql-test/type_inet/type_inet6_mysql.test | 6 + .../mysql-test/type_inet/type_inet6_oracle.result | 29 + .../mysql-test/type_inet/type_inet6_oracle.test | 35 + .../type_inet/type_inet6_partition.result | 29 + .../mysql-test/type_inet/type_inet6_partition.test | 32 + .../mysql-test/type_inet/type_inet6_plugin.result | 31 + .../mysql-test/type_inet/type_inet6_plugin.test | 27 + .../type_inet/type_inet6_stat_tables.result | 31 + .../type_inet/type_inet6_stat_tables.test | 29 + plugin/type_inet/plugin.cc | 311 +++ plugin/type_inet/sql_type_inet.cc | 1642 +++++++++++++++ plugin/type_inet/sql_type_inet.h | 1028 ++++++++++ 42 files changed, 8624 insertions(+) create mode 100644 plugin/type_inet/CMakeLists.txt create mode 100644 plugin/type_inet/item_inetfunc.cc create mode 100644 plugin/type_inet/item_inetfunc.h create mode 100644 plugin/type_inet/mysql-test/type_inet/binlog_stm_type_inet6.result create mode 100644 plugin/type_inet/mysql-test/type_inet/binlog_stm_type_inet6.test create mode 100644 plugin/type_inet/mysql-test/type_inet/binlog_table_map_optional_metadata_type_inet6.result create mode 100644 plugin/type_inet/mysql-test/type_inet/binlog_table_map_optional_metadata_type_inet6.test create mode 100644 plugin/type_inet/mysql-test/type_inet/func_inet_plugin.result create mode 100644 plugin/type_inet/mysql-test/type_inet/func_inet_plugin.test create mode 100644 plugin/type_inet/mysql-test/type_inet/rpl_row_binary_to_inet6.result create mode 100644 plugin/type_inet/mysql-test/type_inet/rpl_row_binary_to_inet6.test create mode 100644 plugin/type_inet/mysql-test/type_inet/rpl_row_inet6_to_binary.result create mode 100644 plugin/type_inet/mysql-test/type_inet/rpl_row_inet6_to_binary.test create mode 100644 plugin/type_inet/mysql-test/type_inet/rpl_type_inet6.result create mode 100644 plugin/type_inet/mysql-test/type_inet/rpl_type_inet6.test create mode 100644 plugin/type_inet/mysql-test/type_inet/suite.pm create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6-debug.result create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6-debug.test create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6.result create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6.test create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_csv.result create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_csv.test create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_engines.inc create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_innodb.result create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_innodb.test create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_memory.result create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_memory.test create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_myisam.result create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_myisam.test create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_mysql.result create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_mysql.test create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_oracle.result create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_oracle.test create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_partition.result create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_partition.test create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_plugin.result create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_plugin.test create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_stat_tables.result create mode 100644 plugin/type_inet/mysql-test/type_inet/type_inet6_stat_tables.test create mode 100644 plugin/type_inet/plugin.cc create mode 100644 plugin/type_inet/sql_type_inet.cc create mode 100644 plugin/type_inet/sql_type_inet.h (limited to 'plugin/type_inet') diff --git a/plugin/type_inet/CMakeLists.txt b/plugin/type_inet/CMakeLists.txt new file mode 100644 index 00000000..0040ceec --- /dev/null +++ b/plugin/type_inet/CMakeLists.txt @@ -0,0 +1,18 @@ +# Copyright (c) 2019, MariaDB corporation. +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; version 2 of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA + +MYSQL_ADD_PLUGIN(type_inet + plugin.cc item_inetfunc.cc sql_type_inet.cc + MANDATORY RECOMPILE_FOR_EMBEDDED) diff --git a/plugin/type_inet/item_inetfunc.cc b/plugin/type_inet/item_inetfunc.cc new file mode 100644 index 00000000..50bd8281 --- /dev/null +++ b/plugin/type_inet/item_inetfunc.cc @@ -0,0 +1,256 @@ +/* Copyright (c) 2011, 2013, Oracle and/or its affiliates. All rights reserved. + Copyright (c) 2014 MariaDB Foundation + Copyright (c) 2019 MariaDB Corporation + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ + +#define MYSQL_SERVER +#include "mariadb.h" +#include "item_inetfunc.h" +#include "sql_type_inet.h" + +/////////////////////////////////////////////////////////////////////////// + +longlong Item_func_inet_aton::val_int() +{ + DBUG_ASSERT(fixed); + + uint byte_result= 0; + ulonglong result= 0; // We are ready for 64 bit addresses + const char *p,* end; + char c= '.'; // we mark c to indicate invalid IP in case length is 0 + int dot_count= 0; + + StringBuffer<36> tmp; + String *s= args[0]->val_str_ascii(&tmp); + + if (!s) // If null value + goto err; + + null_value= 0; + + end= (p = s->ptr()) + s->length(); + while (p < end) + { + c= *p++; + int digit= (int) (c - '0'); + if (digit >= 0 && digit <= 9) + { + if ((byte_result= byte_result * 10 + digit) > 255) + goto err; // Wrong address + } + else if (c == '.') + { + dot_count++; + result= (result << 8) + (ulonglong) byte_result; + byte_result= 0; + } + else + goto err; // Invalid character + } + if (c != '.') // IP number can't end on '.' + { + /* + Attempt to support short forms of IP-addresses. It's however pretty + basic one comparing to the BSD support. + Examples: + 127 -> 0.0.0.127 + 127.255 -> 127.0.0.255 + 127.256 -> NULL (should have been 127.0.1.0) + 127.2.1 -> 127.2.0.1 + */ + switch (dot_count) { + case 1: result<<= 8; /* Fall through */ + case 2: result<<= 8; /* Fall through */ + } + return (result << 8) + (ulonglong) byte_result; + } + +err: + null_value=1; + return 0; +} + + +String* Item_func_inet_ntoa::val_str(String* str) +{ + DBUG_ASSERT(fixed); + + ulonglong n= (ulonglong) args[0]->val_int(); + + /* + We do not know if args[0] is NULL until we have called + some val function on it if args[0] is not a constant! + + Also return null if n > 255.255.255.255 + */ + if ((null_value= (args[0]->null_value || n > 0xffffffff))) + return 0; // Null value + + str->set_charset(collation.collation); + str->length(0); + + uchar buf[8]; + int4store(buf, n); + + /* Now we can assume little endian. */ + + char num[4]; + num[3]= '.'; + + for (uchar *p= buf + 4; p-- > buf;) + { + uint c= *p; + uint n1, n2; // Try to avoid divisions + n1= c / 100; // 100 digits + c-= n1 * 100; + n2= c / 10; // 10 digits + c-= n2 * 10; // last digit + num[0]= (char) n1 + '0'; + num[1]= (char) n2 + '0'; + num[2]= (char) c + '0'; + uint length= (n1 ? 4 : n2 ? 3 : 2); // Remove pre-zero + uint dot_length= (p <= buf) ? 1 : 0; + (void) str->append(num + 4 - length, length - dot_length, + &my_charset_latin1); + } + + return str; +} + + +/////////////////////////////////////////////////////////////////////////// + +/** + Converts IP-address-string to IP-address-data. + + ipv4-string -> varbinary(4) + ipv6-string -> varbinary(16) + + @return Completion status. + @retval NULL Given string does not represent an IP-address. + @retval !NULL The string has been converted sucessfully. +*/ + +String *Item_func_inet6_aton::val_str(String *buffer) +{ + DBUG_ASSERT(fixed); + + Ascii_ptr_and_buffer tmp(args[0]); + if ((null_value= tmp.is_null())) + return NULL; + + Inet4_null ipv4(*tmp.string()); + if (!ipv4.is_null()) + { + ipv4.to_binary(buffer); + return buffer; + } + + Inet6_null ipv6(*tmp.string()); + if (!ipv6.is_null()) + { + ipv6.to_binary(buffer); + return buffer; + } + + null_value= true; + return NULL; +} + + +/** + Converts IP-address-data to IP-address-string. +*/ + +String *Item_func_inet6_ntoa::val_str_ascii(String *buffer) +{ + DBUG_ASSERT(fixed); + + // Binary string argument expected + if (unlikely(args[0]->result_type() != STRING_RESULT || + args[0]->collation.collation != &my_charset_bin)) + { + null_value= true; + return NULL; + } + + String_ptr_and_buffer tmp(args[0]); + if ((null_value= tmp.is_null())) + return NULL; + + Inet4_null ipv4(static_cast(*tmp.string())); + if (!ipv4.is_null()) + { + ipv4.to_string(buffer); + return buffer; + } + + Inet6_null ipv6(static_cast(*tmp.string())); + if (!ipv6.is_null()) + { + ipv6.to_string(buffer); + return buffer; + } + + DBUG_PRINT("info", ("INET6_NTOA(): varbinary(4) or varbinary(16) expected.")); + null_value= true; + return NULL; +} + + +/** + Checks if the passed string represents an IPv4-address. +*/ + +longlong Item_func_is_ipv4::val_int() +{ + DBUG_ASSERT(fixed); + String_ptr_and_buffer tmp(args[0]); + return !tmp.is_null() && !Inet4_null(*tmp.string()).is_null(); +} + + +/** + Checks if the passed string represents an IPv6-address. +*/ + +longlong Item_func_is_ipv6::val_int() +{ + DBUG_ASSERT(fixed); + String_ptr_and_buffer tmp(args[0]); + return !tmp.is_null() && !Inet6_null(*tmp.string()).is_null(); +} + + +/** + Checks if the passed IPv6-address is an IPv4-compat IPv6-address. +*/ + +longlong Item_func_is_ipv4_compat::val_int() +{ + Inet6_null ip6(args[0]); + return !ip6.is_null() && ip6.is_v4compat(); +} + + +/** + Checks if the passed IPv6-address is an IPv4-mapped IPv6-address. +*/ + +longlong Item_func_is_ipv4_mapped::val_int() +{ + Inet6_null ip6(args[0]); + return !ip6.is_null() && ip6.is_v4mapped(); +} diff --git a/plugin/type_inet/item_inetfunc.h b/plugin/type_inet/item_inetfunc.h new file mode 100644 index 00000000..94255426 --- /dev/null +++ b/plugin/type_inet/item_inetfunc.h @@ -0,0 +1,227 @@ +#ifndef ITEM_INETFUNC_INCLUDED +#define ITEM_INETFUNC_INCLUDED + +/* Copyright (c) 2011, 2013, Oracle and/or its affiliates. All rights reserved. + Copyright (c) 2014 MariaDB Foundation + Copyright (c) 2019 MariaDB Corporation + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ + + +#include "item.h" + +/************************************************************************* + Item_func_inet_aton implements INET_ATON() SQL-function. +*************************************************************************/ + +class Item_func_inet_aton : public Item_longlong_func +{ + bool check_arguments() const + { return check_argument_types_can_return_text(0, arg_count); } +public: + Item_func_inet_aton(THD *thd, Item *a): Item_longlong_func(thd, a) {} + longlong val_int(); + const char *func_name() const { return "inet_aton"; } + bool fix_length_and_dec() + { + decimals= 0; + max_length= 21; + maybe_null= 1; + unsigned_flag= 1; + return FALSE; + } + Item *get_copy(THD *thd) + { return get_item_copy(thd, this); } +}; + + +/************************************************************************* + Item_func_inet_ntoa implements INET_NTOA() SQL-function. +*************************************************************************/ + +class Item_func_inet_ntoa : public Item_str_func +{ +public: + Item_func_inet_ntoa(THD *thd, Item *a): Item_str_func(thd, a) + { } + String* val_str(String* str); + const char *func_name() const { return "inet_ntoa"; } + bool fix_length_and_dec() + { + decimals= 0; + fix_length_and_charset(3 * 8 + 7, default_charset()); + maybe_null= 1; + return FALSE; + } + Item *get_copy(THD *thd) + { return get_item_copy(thd, this); } +}; + + +/************************************************************************* + Item_func_inet_bool_base implements common code for INET6/IP-related + functions returning boolean value. +*************************************************************************/ + +class Item_func_inet_bool_base : public Item_bool_func +{ +public: + inline Item_func_inet_bool_base(THD *thd, Item *ip_addr): + Item_bool_func(thd, ip_addr) + { + null_value= false; + } + bool need_parentheses_in_default() { return false; } +}; + + +/************************************************************************* + Item_func_inet6_aton implements INET6_ATON() SQL-function. +*************************************************************************/ + +class Item_func_inet6_aton : public Item_str_func +{ +public: + inline Item_func_inet6_aton(THD *thd, Item *ip_addr): + Item_str_func(thd, ip_addr) + { } + +public: + virtual const char *func_name() const + { return "inet6_aton"; } + + virtual bool fix_length_and_dec() + { + decimals= 0; + fix_length_and_charset(16, &my_charset_bin); + maybe_null= 1; + return FALSE; + } + Item *get_copy(THD *thd) + { return get_item_copy(thd, this); } + + String *val_str(String *to); +}; + + +/************************************************************************* + Item_func_inet6_ntoa implements INET6_NTOA() SQL-function. +*************************************************************************/ + +class Item_func_inet6_ntoa : public Item_str_ascii_func +{ +public: + inline Item_func_inet6_ntoa(THD *thd, Item *ip_addr): + Item_str_ascii_func(thd, ip_addr) + { } + +public: + virtual const char *func_name() const + { return "inet6_ntoa"; } + + virtual bool fix_length_and_dec() + { + decimals= 0; + + // max length: IPv6-address -- 16 bytes + // 16 bytes / 2 bytes per group == 8 groups => 7 delimiter + // 4 symbols per group + fix_length_and_charset(8 * 4 + 7, default_charset()); + + maybe_null= 1; + return FALSE; + } + String *val_str_ascii(String *to); + Item *get_copy(THD *thd) + { return get_item_copy(thd, this); } +}; + + +/************************************************************************* + Item_func_is_ipv4 implements IS_IPV4() SQL-function. +*************************************************************************/ + +class Item_func_is_ipv4 : public Item_func_inet_bool_base +{ +public: + inline Item_func_is_ipv4(THD *thd, Item *ip_addr): + Item_func_inet_bool_base(thd, ip_addr) + { } + +public: + virtual const char *func_name() const + { return "is_ipv4"; } + Item *get_copy(THD *thd) + { return get_item_copy(thd, this); } + + longlong val_int(); +}; + + +/************************************************************************* + Item_func_is_ipv6 implements IS_IPV6() SQL-function. +*************************************************************************/ + +class Item_func_is_ipv6 : public Item_func_inet_bool_base +{ +public: + inline Item_func_is_ipv6(THD *thd, Item *ip_addr): + Item_func_inet_bool_base(thd, ip_addr) + { } + + virtual const char *func_name() const + { return "is_ipv6"; } + Item *get_copy(THD *thd) + { return get_item_copy(thd, this); } + + longlong val_int(); +}; + + +/************************************************************************* + Item_func_is_ipv4_compat implements IS_IPV4_COMPAT() SQL-function. +*************************************************************************/ + +class Item_func_is_ipv4_compat : public Item_func_inet_bool_base +{ +public: + inline Item_func_is_ipv4_compat(THD *thd, Item *ip_addr): + Item_func_inet_bool_base(thd, ip_addr) + { } + virtual const char *func_name() const + { return "is_ipv4_compat"; } + Item *get_copy(THD *thd) + { return get_item_copy(thd, this); } + longlong val_int(); +}; + + +/************************************************************************* + Item_func_is_ipv4_mapped implements IS_IPV4_MAPPED() SQL-function. +*************************************************************************/ + +class Item_func_is_ipv4_mapped : public Item_func_inet_bool_base +{ +public: + inline Item_func_is_ipv4_mapped(THD *thd, Item *ip_addr): + Item_func_inet_bool_base(thd, ip_addr) + { } + virtual const char *func_name() const + { return "is_ipv4_mapped"; } + Item *get_copy(THD *thd) + { return get_item_copy(thd, this); } + longlong val_int(); +}; + +#endif // ITEM_INETFUNC_INCLUDED 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, '', '::01', '01') AS d1, +DECODE_ORACLE(a, 'garbage', '', '::01', '01') AS d2 +FROM t1; +a d0 d1 d2 +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, '', '::01', '01') AS d1, + DECODE_ORACLE(a, 'garbage', '', '::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 # diff --git a/plugin/type_inet/plugin.cc b/plugin/type_inet/plugin.cc new file mode 100644 index 00000000..77804c82 --- /dev/null +++ b/plugin/type_inet/plugin.cc @@ -0,0 +1,311 @@ +/* Copyright (c) 2019 MariaDB Corporation + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ + +#define MYSQL_SERVER +#include "mariadb.h" +#include "sql_class.h" +#include "sql_type_inet.h" +#include "item_inetfunc.h" +#include +#include + + +Type_handler_inet6 type_handler_inet6; + + +static struct st_mariadb_data_type plugin_descriptor_type_inet6= +{ + MariaDB_DATA_TYPE_INTERFACE_VERSION, + &type_handler_inet6 +}; + + +/*************************************************************************/ + +class Create_func_inet_ntoa : public Create_func_arg1 +{ +public: + Item *create_1_arg(THD *thd, Item *arg1) override + { + return new (thd->mem_root) Item_func_inet_ntoa(thd, arg1); + } + static Create_func_inet_ntoa s_singleton; +protected: + Create_func_inet_ntoa() {} + virtual ~Create_func_inet_ntoa() {} +}; + + +class Create_func_inet_aton : public Create_func_arg1 +{ +public: + Item *create_1_arg(THD *thd, Item *arg1) override + { + return new (thd->mem_root) Item_func_inet_aton(thd, arg1); + } + static Create_func_inet_aton s_singleton; +protected: + Create_func_inet_aton() {} + virtual ~Create_func_inet_aton() {} +}; + + +class Create_func_inet6_aton : public Create_func_arg1 +{ +public: + Item *create_1_arg(THD *thd, Item *arg1) override + { + return new (thd->mem_root) Item_func_inet6_aton(thd, arg1); + } + static Create_func_inet6_aton s_singleton; +protected: + Create_func_inet6_aton() {} + virtual ~Create_func_inet6_aton() {} +}; + + +class Create_func_inet6_ntoa : public Create_func_arg1 +{ +public: + Item *create_1_arg(THD *thd, Item *arg1) override + { + return new (thd->mem_root) Item_func_inet6_ntoa(thd, arg1); + } + static Create_func_inet6_ntoa s_singleton; +protected: + Create_func_inet6_ntoa() {} + virtual ~Create_func_inet6_ntoa() {} +}; + + +class Create_func_is_ipv4 : public Create_func_arg1 +{ +public: + Item *create_1_arg(THD *thd, Item *arg1) override + { + return new (thd->mem_root) Item_func_is_ipv4(thd, arg1); + } + static Create_func_is_ipv4 s_singleton; +protected: + Create_func_is_ipv4() {} + virtual ~Create_func_is_ipv4() {} +}; + + +class Create_func_is_ipv6 : public Create_func_arg1 +{ +public: + Item *create_1_arg(THD *thd, Item *arg1) override + { + return new (thd->mem_root) Item_func_is_ipv6(thd, arg1); + } + static Create_func_is_ipv6 s_singleton; +protected: + Create_func_is_ipv6() {} + virtual ~Create_func_is_ipv6() {} +}; + + +class Create_func_is_ipv4_compat : public Create_func_arg1 +{ +public: + Item *create_1_arg(THD *thd, Item *arg1) override + { + return new (thd->mem_root) Item_func_is_ipv4_compat(thd, arg1); + } + static Create_func_is_ipv4_compat s_singleton; +protected: + Create_func_is_ipv4_compat() {} + virtual ~Create_func_is_ipv4_compat() {} +}; + + +class Create_func_is_ipv4_mapped : public Create_func_arg1 +{ +public: + Item *create_1_arg(THD *thd, Item *arg1) override + { + return new (thd->mem_root) Item_func_is_ipv4_mapped(thd, arg1); + } + static Create_func_is_ipv4_mapped s_singleton; +protected: + Create_func_is_ipv4_mapped() {} + virtual ~Create_func_is_ipv4_mapped() {} +}; + + +Create_func_inet_ntoa Create_func_inet_ntoa::s_singleton; +Create_func_inet6_aton Create_func_inet6_aton::s_singleton; +Create_func_inet6_ntoa Create_func_inet6_ntoa::s_singleton; +Create_func_inet_aton Create_func_inet_aton::s_singleton; +Create_func_is_ipv4 Create_func_is_ipv4::s_singleton; +Create_func_is_ipv6 Create_func_is_ipv6::s_singleton; +Create_func_is_ipv4_compat Create_func_is_ipv4_compat::s_singleton; +Create_func_is_ipv4_mapped Create_func_is_ipv4_mapped::s_singleton; + + +#define BUILDER(F) & F::s_singleton + + +static Plugin_function + plugin_descriptor_function_inet_aton(BUILDER(Create_func_inet_aton)), + plugin_descriptor_function_inet_ntoa(BUILDER(Create_func_inet_ntoa)), + plugin_descriptor_function_inet6_aton(BUILDER(Create_func_inet6_aton)), + plugin_descriptor_function_inet6_ntoa(BUILDER(Create_func_inet6_ntoa)), + plugin_descriptor_function_is_ipv4(BUILDER(Create_func_is_ipv4)), + plugin_descriptor_function_is_ipv6(BUILDER(Create_func_is_ipv6)), + plugin_descriptor_function_is_ipv4_compat(BUILDER(Create_func_is_ipv4_compat)), + plugin_descriptor_function_is_ipv4_mapped(BUILDER(Create_func_is_ipv4_mapped)); + + +/*************************************************************************/ + +maria_declare_plugin(type_inet) +{ + MariaDB_DATA_TYPE_PLUGIN, // the plugin type (see include/mysql/plugin.h) + &plugin_descriptor_type_inet6,// pointer to type-specific plugin descriptor + "inet6", // plugin name + "MariaDB Corporation", // plugin author + "Data type INET6", // the plugin description + PLUGIN_LICENSE_GPL, // the plugin license (see include/mysql/plugin.h) + 0, // Pointer to plugin initialization function + 0, // Pointer to plugin deinitialization function + 0x0100, // Numeric version 0xAABB means AA.BB version + NULL, // Status variables + NULL, // System variables + "1.0", // String version representation + MariaDB_PLUGIN_MATURITY_STABLE// Maturity(see include/mysql/plugin.h)*/ +}, +{ + MariaDB_FUNCTION_PLUGIN, // the plugin type (see include/mysql/plugin.h) + &plugin_descriptor_function_inet_aton, // pointer to type-specific plugin descriptor + "inet_aton", // plugin name + "MariaDB Corporation", // plugin author + "Function INET_ATON()", // the plugin description + PLUGIN_LICENSE_GPL, // the plugin license (see include/mysql/plugin.h) + 0, // Pointer to plugin initialization function + 0, // Pointer to plugin deinitialization function + 0x0100, // Numeric version 0xAABB means AA.BB version + NULL, // Status variables + NULL, // System variables + "1.0", // String version representation + MariaDB_PLUGIN_MATURITY_STABLE// Maturity(see include/mysql/plugin.h)*/ +}, +{ + MariaDB_FUNCTION_PLUGIN, // the plugin type (see include/mysql/plugin.h) + &plugin_descriptor_function_inet_ntoa, // pointer to type-specific plugin descriptor + "inet_ntoa", // plugin name + "MariaDB Corporation", // plugin author + "Function INET_NTOA()", // the plugin description + PLUGIN_LICENSE_GPL, // the plugin license (see include/mysql/plugin.h) + 0, // Pointer to plugin initialization function + 0, // Pointer to plugin deinitialization function + 0x0100, // Numeric version 0xAABB means AA.BB version + NULL, // Status variables + NULL, // System variables + "1.0", // String version representation + MariaDB_PLUGIN_MATURITY_STABLE// Maturity(see include/mysql/plugin.h)*/ +}, +{ + MariaDB_FUNCTION_PLUGIN, // the plugin type (see include/mysql/plugin.h) + &plugin_descriptor_function_inet6_aton, // pointer to type-specific plugin descriptor + "inet6_aton", // plugin name + "MariaDB Corporation", // plugin author + "Function INET6_ATON()", // the plugin description + PLUGIN_LICENSE_GPL, // the plugin license (see include/mysql/plugin.h) + 0, // Pointer to plugin initialization function + 0, // Pointer to plugin deinitialization function + 0x0100, // Numeric version 0xAABB means AA.BB version + NULL, // Status variables + NULL, // System variables + "1.0", // String version representation + MariaDB_PLUGIN_MATURITY_STABLE// Maturity(see include/mysql/plugin.h)*/ +}, +{ + MariaDB_FUNCTION_PLUGIN, // the plugin type (see include/mysql/plugin.h) + &plugin_descriptor_function_inet6_ntoa, // pointer to type-specific plugin descriptor + "inet6_ntoa", // plugin name + "MariaDB Corporation", // plugin author + "Function INET6_NTOA()", // the plugin description + PLUGIN_LICENSE_GPL, // the plugin license (see include/mysql/plugin.h) + 0, // Pointer to plugin initialization function + 0, // Pointer to plugin deinitialization function + 0x0100, // Numeric version 0xAABB means AA.BB version + NULL, // Status variables + NULL, // System variables + "1.0", // String version representation + MariaDB_PLUGIN_MATURITY_STABLE// Maturity(see include/mysql/plugin.h)*/ +}, +{ + MariaDB_FUNCTION_PLUGIN, // the plugin type (see include/mysql/plugin.h) + &plugin_descriptor_function_is_ipv4, // pointer to type-specific plugin descriptor + "is_ipv4", // plugin name + "MariaDB Corporation", // plugin author + "Function IS_IPV4()", // the plugin description + PLUGIN_LICENSE_GPL, // the plugin license (see include/mysql/plugin.h) + 0, // Pointer to plugin initialization function + 0, // Pointer to plugin deinitialization function + 0x0100, // Numeric version 0xAABB means AA.BB version + NULL, // Status variables + NULL, // System variables + "1.0", // String version representation + MariaDB_PLUGIN_MATURITY_STABLE// Maturity(see include/mysql/plugin.h)*/ +}, +{ + MariaDB_FUNCTION_PLUGIN, // the plugin type (see include/mysql/plugin.h) + &plugin_descriptor_function_is_ipv6, // pointer to type-specific plugin descriptor + "is_ipv6", // plugin name + "MariaDB Corporation", // plugin author + "Function IS_IPV6()", // the plugin description + PLUGIN_LICENSE_GPL, // the plugin license (see include/mysql/plugin.h) + 0, // Pointer to plugin initialization function + 0, // Pointer to plugin deinitialization function + 0x0100, // Numeric version 0xAABB means AA.BB version + NULL, // Status variables + NULL, // System variables + "1.0", // String version representation + MariaDB_PLUGIN_MATURITY_STABLE// Maturity(see include/mysql/plugin.h)*/ +}, +{ + MariaDB_FUNCTION_PLUGIN, // the plugin type (see include/mysql/plugin.h) + &plugin_descriptor_function_is_ipv4_compat, // pointer to type-specific plugin descriptor + "is_ipv4_compat", // plugin name + "MariaDB Corporation", // plugin author + "Function IS_IPV4_COMPAT()", // the plugin description + PLUGIN_LICENSE_GPL, // the plugin license (see include/mysql/plugin.h) + 0, // Pointer to plugin initialization function + 0, // Pointer to plugin deinitialization function + 0x0100, // Numeric version 0xAABB means AA.BB version + NULL, // Status variables + NULL, // System variables + "1.0", // String version representation + MariaDB_PLUGIN_MATURITY_STABLE// Maturity(see include/mysql/plugin.h)*/ +}, +{ + MariaDB_FUNCTION_PLUGIN, // the plugin type (see include/mysql/plugin.h) + &plugin_descriptor_function_is_ipv4_mapped, // pointer to type-specific plugin descriptor + "is_ipv4_mapped", // plugin name + "MariaDB Corporation", // plugin author + "Function IS_IPV4_MAPPED()",// the plugin description + PLUGIN_LICENSE_GPL, // the plugin license (see include/mysql/plugin.h) + 0, // Pointer to plugin initialization function + 0, // Pointer to plugin deinitialization function + 0x0100, // Numeric version 0xAABB means AA.BB version + NULL, // Status variables + NULL, // System variables + "1.0", // String version representation + MariaDB_PLUGIN_MATURITY_STABLE// Maturity(see include/mysql/plugin.h)*/ +} +maria_declare_plugin_end; diff --git a/plugin/type_inet/sql_type_inet.cc b/plugin/type_inet/sql_type_inet.cc new file mode 100644 index 00000000..14d854be --- /dev/null +++ b/plugin/type_inet/sql_type_inet.cc @@ -0,0 +1,1642 @@ +/* Copyright (c) 2011, 2013, Oracle and/or its affiliates. All rights reserved. + Copyright (c) 2014 MariaDB Foundation + Copyright (c) 2019 MariaDB Corporation + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ + +#define MYSQL_SERVER +#include "mariadb.h" +#include "my_net.h" +#include "sql_class.h" // THD, SORT_FIELD_ATTR +#include "opt_range.h" // SEL_ARG +#include "sql_type_inet.h" + +/////////////////////////////////////////////////////////////////////////// + +static const char HEX_DIGITS[]= "0123456789abcdef"; + + +/////////////////////////////////////////////////////////////////////////// + +/** + Tries to convert given string to binary IPv4-address representation. + This is a portable alternative to inet_pton(AF_INET). + + @param str String to convert. + @param str_length String length. + + @return Completion status. + @retval true - error, the given string does not represent an IPv4-address. + @retval false - ok, the string has been converted sucessfully. + + @note The problem with inet_pton() is that it treats leading zeros in + IPv4-part differently on different platforms. +*/ + +bool Inet4::ascii_to_ipv4(const char *str, size_t str_length) +{ + if (str_length < 7) + { + DBUG_PRINT("error", ("ascii_to_ipv4(%.*s): " + "invalid IPv4 address: too short.", + (int) str_length, str)); + return true; + } + + if (str_length > IN_ADDR_MAX_CHAR_LENGTH) + { + DBUG_PRINT("error", ("ascii_to_ipv4(%.*s): " + "invalid IPv4 address: too long.", + (int) str_length, str)); + return true; + } + + unsigned char *ipv4_bytes= (unsigned char *) &m_buffer; + const char *str_end= str + str_length; + const char *p= str; + int byte_value= 0; + int chars_in_group= 0; + int dot_count= 0; + char c= 0; + + while (p < str_end && *p) + { + c= *p++; + + if (my_isdigit(&my_charset_latin1, c)) + { + ++chars_in_group; + + if (chars_in_group > 3) + { + DBUG_PRINT("error", ("ascii_to_ipv4(%.*s): invalid IPv4 address: " + "too many characters in a group.", + (int) str_length, str)); + return true; + } + + byte_value= byte_value * 10 + (c - '0'); + + if (byte_value > 255) + { + DBUG_PRINT("error", ("ascii_to_ipv4(%.*s): invalid IPv4 address: " + "invalid byte value.", + (int) str_length, str)); + return true; + } + } + else if (c == '.') + { + if (chars_in_group == 0) + { + DBUG_PRINT("error", ("ascii_to_ipv4(%.*s): invalid IPv4 address: " + "too few characters in a group.", + (int) str_length, str)); + return true; + } + + ipv4_bytes[dot_count]= (unsigned char) byte_value; + + ++dot_count; + byte_value= 0; + chars_in_group= 0; + + if (dot_count > 3) + { + DBUG_PRINT("error", ("ascii_to_ipv4(%.*s): invalid IPv4 address: " + "too many dots.", (int) str_length, str)); + return true; + } + } + else + { + DBUG_PRINT("error", ("ascii_to_ipv4(%.*s): invalid IPv4 address: " + "invalid character at pos %d.", + (int) str_length, str, (int) (p - str))); + return true; + } + } + + if (c == '.') + { + DBUG_PRINT("error", ("ascii_to_ipv4(%.*s): invalid IPv4 address: " + "ending at '.'.", (int) str_length, str)); + return true; + } + + if (dot_count != 3) + { + DBUG_PRINT("error", ("ascii_to_ipv4(%.*s): invalid IPv4 address: " + "too few groups.", + (int) str_length, str)); + return true; + } + + ipv4_bytes[3]= (unsigned char) byte_value; + + DBUG_PRINT("info", ("ascii_to_ipv4(%.*s): valid IPv4 address: %d.%d.%d.%d", + (int) str_length, str, + ipv4_bytes[0], ipv4_bytes[1], + ipv4_bytes[2], ipv4_bytes[3])); + return false; +} + + +/** + Tries to convert given string to binary IPv6-address representation. + This is a portable alternative to inet_pton(AF_INET6). + + @param str String to convert. + @param str_length String length. + + @return Completion status. + @retval true - error, the given string does not represent an IPv6-address. + @retval false - ok, the string has been converted sucessfully. + + @note The problem with inet_pton() is that it treats leading zeros in + IPv4-part differently on different platforms. +*/ + +bool Inet6::ascii_to_ipv6(const char *str, size_t str_length) +{ + if (str_length < 2) + { + DBUG_PRINT("error", ("ascii_to_ipv6(%.*s): invalid IPv6 address: too short.", + (int) str_length, str)); + return true; + } + + if (str_length > IN6_ADDR_MAX_CHAR_LENGTH) + { + DBUG_PRINT("error", ("ascii_to_ipv6(%.*s): invalid IPv6 address: too long.", + (int) str_length, str)); + return true; + } + + memset(m_buffer, 0, sizeof(m_buffer)); + + const char *p= str; + + if (*p == ':') + { + ++p; + + if (*p != ':') + { + DBUG_PRINT("error", ("ascii_to_ipv6(%.*s): invalid IPv6 address: " + "can not start with ':x'.", (int) str_length, str)); + return true; + } + } + + const char *str_end= str + str_length; + char *ipv6_bytes_end= m_buffer + sizeof(m_buffer); + char *dst= m_buffer; + char *gap_ptr= NULL; + const char *group_start_ptr= p; + int chars_in_group= 0; + int group_value= 0; + + while (p < str_end && *p) + { + char c= *p++; + + if (c == ':') + { + group_start_ptr= p; + + if (!chars_in_group) + { + if (gap_ptr) + { + DBUG_PRINT("error", ("ascii_to_ipv6(%.*s): invalid IPv6 address: " + "too many gaps(::).", (int) str_length, str)); + return true; + } + + gap_ptr= dst; + continue; + } + + if (!*p || p >= str_end) + { + DBUG_PRINT("error", ("ascii_to_ipv6(%.*s): invalid IPv6 address: " + "ending at ':'.", (int) str_length, str)); + return true; + } + + if (dst + 2 > ipv6_bytes_end) + { + DBUG_PRINT("error", ("ascii_to_ipv6(%.*s): invalid IPv6 address: " + "too many groups (1).", (int) str_length, str)); + return true; + } + + dst[0]= (unsigned char) (group_value >> 8) & 0xff; + dst[1]= (unsigned char) group_value & 0xff; + dst += 2; + + chars_in_group= 0; + group_value= 0; + } + else if (c == '.') + { + if (dst + IN_ADDR_SIZE > ipv6_bytes_end) + { + DBUG_PRINT("error", ("ascii_to_ipv6(%.*s): invalid IPv6 address: " + "unexpected IPv4-part.", (int) str_length, str)); + return true; + } + + Inet4_null tmp(group_start_ptr, (size_t) (str_end - group_start_ptr), + &my_charset_latin1); + if (tmp.is_null()) + { + DBUG_PRINT("error", ("ascii_to_ipv6(%.*s): invalid IPv6 address: " + "invalid IPv4-part.", (int) str_length, str)); + return true; + } + + tmp.to_binary(dst, IN_ADDR_SIZE); + dst += IN_ADDR_SIZE; + chars_in_group= 0; + + break; + } + else + { + const char *hdp= strchr(HEX_DIGITS, my_tolower(&my_charset_latin1, c)); + + if (!hdp) + { + DBUG_PRINT("error", ("ascii_to_ipv6(%.*s): invalid IPv6 address: " + "invalid character at pos %d.", + (int) str_length, str, (int) (p - str))); + return true; + } + + if (chars_in_group >= 4) + { + DBUG_PRINT("error", ("ascii_to_ipv6(%.*s): invalid IPv6 address: " + "too many digits in group.", + (int) str_length, str)); + return true; + } + + group_value <<= 4; + group_value |= hdp - HEX_DIGITS; + + DBUG_ASSERT(group_value <= 0xffff); + + ++chars_in_group; + } + } + + if (chars_in_group > 0) + { + if (dst + 2 > ipv6_bytes_end) + { + DBUG_PRINT("error", ("ascii_to_ipv6(%.*s): invalid IPv6 address: " + "too many groups (2).", (int) str_length, str)); + return true; + } + + dst[0]= (unsigned char) (group_value >> 8) & 0xff; + dst[1]= (unsigned char) group_value & 0xff; + dst += 2; + } + + if (gap_ptr) + { + if (dst == ipv6_bytes_end) + { + DBUG_PRINT("error", ("ascii_to_ipv6(%.*s): invalid IPv6 address: " + "no room for a gap (::).", (int) str_length, str)); + return true; + } + + int bytes_to_move= (int)(dst - gap_ptr); + + for (int i= 1; i <= bytes_to_move; ++i) + { + ipv6_bytes_end[-i]= gap_ptr[bytes_to_move - i]; + gap_ptr[bytes_to_move - i]= 0; + } + + dst= ipv6_bytes_end; + } + + if (dst < ipv6_bytes_end) + { + DBUG_PRINT("error", ("ascii_to_ipv6(%.*s): invalid IPv6 address: " + "too few groups.", (int) str_length, str)); + return true; + } + + return false; +} + + +/** + Converts IPv4-binary-address to a string. This function is a portable + alternative to inet_ntop(AF_INET). + + @param[in] ipv4 IPv4-address data (byte array) + @param[out] dst A buffer to store string representation of IPv4-address. + @param[in] dstsize Number of bytes avaiable in "dst" + + @note The problem with inet_ntop() is that it is available starting from + Windows Vista, but the minimum supported version is Windows 2000. +*/ + +size_t Inet4::to_string(char *dst, size_t dstsize) const +{ + return (size_t) my_snprintf(dst, dstsize, "%d.%d.%d.%d", + (uchar) m_buffer[0], (uchar) m_buffer[1], + (uchar) m_buffer[2], (uchar) m_buffer[3]); +} + + +/** + Converts IPv6-binary-address to a string. This function is a portable + alternative to inet_ntop(AF_INET6). + + @param[in] ipv6 IPv6-address data (byte array) + @param[out] dst A buffer to store string representation of IPv6-address. + It must be at least of INET6_ADDRSTRLEN. + @param[in] dstsize Number of bytes available dst. + + @note The problem with inet_ntop() is that it is available starting from + Windows Vista, but out the minimum supported version is Windows 2000. +*/ + +size_t Inet6::to_string(char *dst, size_t dstsize) const +{ + struct Region + { + int pos; + int length; + }; + + const char *ipv6= m_buffer; + char *dstend= dst + dstsize; + const unsigned char *ipv6_bytes= (const unsigned char *) ipv6; + + // 1. Translate IPv6-address bytes to words. + // We can't just cast to short, because it's not guaranteed + // that sizeof (short) == 2. So, we have to make a copy. + + uint16 ipv6_words[IN6_ADDR_NUM_WORDS]; + + DBUG_ASSERT(dstsize > 0); // Need a space at least for the trailing '\0' + for (size_t i= 0; i < IN6_ADDR_NUM_WORDS; ++i) + ipv6_words[i]= (ipv6_bytes[2 * i] << 8) + ipv6_bytes[2 * i + 1]; + + // 2. Find "the gap" -- longest sequence of zeros in IPv6-address. + + Region gap= { -1, -1 }; + + { + Region rg= { -1, -1 }; + + for (size_t i= 0; i < IN6_ADDR_NUM_WORDS; ++i) + { + if (ipv6_words[i] != 0) + { + if (rg.pos >= 0) + { + if (rg.length > gap.length) + gap= rg; + + rg.pos= -1; + rg.length= -1; + } + } + else + { + if (rg.pos >= 0) + { + ++rg.length; + } + else + { + rg.pos= (int) i; + rg.length= 1; + } + } + } + + if (rg.pos >= 0) + { + if (rg.length > gap.length) + gap= rg; + } + } + + // 3. Convert binary data to string. + + char *p= dst; + + for (int i= 0; i < (int) IN6_ADDR_NUM_WORDS; ++i) + { + DBUG_ASSERT(dstend >= p); + size_t dstsize_available= dstend - p; + if (dstsize_available < 5) + break; + if (i == gap.pos) + { + // We're at the gap position. We should put trailing ':' and jump to + // the end of the gap. + + if (i == 0) + { + // The gap starts from the beginning of the data -- leading ':' + // should be put additionally. + + *p= ':'; + ++p; + } + + *p= ':'; + ++p; + + i += gap.length - 1; + } + else if (i == 6 && gap.pos == 0 && + (gap.length == 6 || // IPv4-compatible + (gap.length == 5 && ipv6_words[5] == 0xffff) // IPv4-mapped + )) + { + // The data represents either IPv4-compatible or IPv4-mapped address. + // The IPv6-part (zeros or zeros + ffff) has been already put into + // the string (dst). Now it's time to dump IPv4-part. + + return (size_t) (p - dst) + + Inet4_null((const char *) (ipv6_bytes + 12), 4). + to_string(p, dstsize_available); + } + else + { + // Usual IPv6-address-field. Print it out using lower-case + // hex-letters without leading zeros (recommended IPv6-format). + // + // If it is not the last field, append closing ':'. + + p += sprintf(p, "%x", ipv6_words[i]); + + if (i + 1 != IN6_ADDR_NUM_WORDS) + { + *p= ':'; + ++p; + } + } + } + + *p= 0; + return (size_t) (p - dst); +} + + +bool Inet6::fix_fields_maybe_null_on_conversion_to_inet6(Item *item) +{ + if (item->maybe_null) + return true; + if (item->type_handler() == &type_handler_inet6) + return false; + if (!item->const_item() || item->is_expensive()) + return true; + return Inet6_null(item, false).is_null(); +} + + +bool Inet6::make_from_item(Item *item, bool warn) +{ + if (item->type_handler() == &type_handler_inet6) + { + Native tmp(m_buffer, sizeof(m_buffer)); + bool rc= item->val_native(current_thd, &tmp); + if (rc) + return true; + DBUG_ASSERT(tmp.length() == sizeof(m_buffer)); + if (tmp.ptr() != m_buffer) + memcpy(m_buffer, tmp.ptr(), sizeof(m_buffer)); + return false; + } + StringBufferInet6 tmp; + String *str= item->val_str(&tmp); + return str ? make_from_character_or_binary_string(str, warn) : true; +} + + +bool Inet6::make_from_character_or_binary_string(const String *str, bool warn) +{ + static Name name= type_handler_inet6.name(); + if (str->charset() != &my_charset_bin) + { + bool rc= character_string_to_ipv6(str->ptr(), str->length(), + str->charset()); + if (rc && warn) + current_thd->push_warning_wrong_value(Sql_condition::WARN_LEVEL_WARN, + name.ptr(), + ErrConvString(str).ptr()); + return rc; + } + if (str->length() != sizeof(m_buffer)) + { + if (warn) + current_thd->push_warning_wrong_value(Sql_condition::WARN_LEVEL_WARN, + name.ptr(), + ErrConvString(str).ptr()); + return true; + } + DBUG_ASSERT(str->ptr() != m_buffer); + memcpy(m_buffer, str->ptr(), sizeof(m_buffer)); + return false; +}; + + +/********************************************************************/ + + +class cmp_item_inet6: public cmp_item_scalar +{ + Inet6 m_native; +public: + cmp_item_inet6() + :cmp_item_scalar(), + m_native(Inet6_zero()) + { } + void store_value(Item *item) override + { + m_native= Inet6(item, &m_null_value); + } + int cmp_not_null(const Value *val) override + { + DBUG_ASSERT(!val->is_null()); + DBUG_ASSERT(val->is_string()); + Inet6_null tmp(val->m_string); + DBUG_ASSERT(!tmp.is_null()); + return m_native.cmp(tmp); + } + int cmp(Item *arg) override + { + Inet6_null tmp(arg); + return m_null_value || tmp.is_null() ? UNKNOWN : m_native.cmp(tmp) != 0; + } + int compare(cmp_item *ci) override + { + cmp_item_inet6 *tmp= static_cast(ci); + DBUG_ASSERT(!m_null_value); + DBUG_ASSERT(!tmp->m_null_value); + return m_native.cmp(tmp->m_native); + } + cmp_item *make_same() override + { + return new cmp_item_inet6(); + } +}; + + +class Field_inet6: public Field +{ + static void set_min_value(char *ptr) + { + memset(ptr, 0, Inet6::binary_length()); + } + static void set_max_value(char *ptr) + { + memset(ptr, 0xFF, Inet6::binary_length()); + } + void store_warning(const ErrConv &str, + Sql_condition::enum_warning_level level) + { + static const Name type_name= type_handler_inet6.name(); + if (get_thd()->count_cuted_fields <= CHECK_FIELD_EXPRESSION) + return; + const TABLE_SHARE *s= table->s; + get_thd()->push_warning_truncated_value_for_field(level, type_name.ptr(), + str.ptr(), + s ? s->db.str : nullptr, + s ? s->table_name.str + : nullptr, + field_name.str); + } + int set_null_with_warn(const ErrConv &str) + { + store_warning(str, Sql_condition::WARN_LEVEL_WARN); + set_null(); + return 1; + } + int set_min_value_with_warn(const ErrConv &str) + { + store_warning(str, Sql_condition::WARN_LEVEL_WARN); + set_min_value((char*) ptr); + return 1; + } + int set_max_value_with_warn(const ErrConv &str) + { + store_warning(str, Sql_condition::WARN_LEVEL_WARN); + set_max_value((char*) ptr); + return 1; + } + int store_inet6_null_with_warn(const Inet6_null &inet6, + const ErrConvString &err) + { + DBUG_ASSERT(marked_for_write_or_computed()); + if (inet6.is_null()) + return maybe_null() ? set_null_with_warn(err) : + set_min_value_with_warn(err); + inet6.to_binary((char *) ptr, Inet6::binary_length()); + return 0; + } + +public: + Field_inet6(const LEX_CSTRING *field_name_arg, const Record_addr &rec) + :Field(rec.ptr(), Inet6::max_char_length(), + rec.null_ptr(), rec.null_bit(), Field::NONE, field_name_arg) + { + flags|= BINARY_FLAG | UNSIGNED_FLAG; + } + const Type_handler *type_handler() const override + { + return &type_handler_inet6; + } + uint32 max_display_length() const override { return field_length; } + bool str_needs_quotes() const override { return true; } + const DTCollation &dtcollation() const override + { + static DTCollation_numeric c; + return c; + } + CHARSET_INFO *charset(void) const override { return &my_charset_numeric; } + const CHARSET_INFO *sort_charset(void) const override { return &my_charset_bin; } + /** + This makes client-server protocol convert the value according + to @@character_set_client. + */ + bool binary() const override { return false; } + enum ha_base_keytype key_type() const override { return HA_KEYTYPE_BINARY; } + + bool is_equal(const Column_definition &new_field) const override + { + return new_field.type_handler() == type_handler(); + } + bool eq_def(const Field *field) const override + { + return Field::eq_def(field); + } + double pos_in_interval(Field *min, Field *max) override + { + return pos_in_interval_val_str(min, max, 0); + } + int cmp(const uchar *a, const uchar *b) const override + { return memcmp(a, b, pack_length()); } + + void sort_string(uchar *to, uint length) override + { + DBUG_ASSERT(length == pack_length()); + memcpy(to, ptr, length); + } + uint32 pack_length() const override + { + return Inet6::binary_length(); + } + uint pack_length_from_metadata(uint field_metadata) const override + { + return Inet6::binary_length(); + } + + void sql_type(String &str) const override + { + static Name name= type_handler_inet6.name(); + str.set_ascii(name.ptr(), name.length()); + } + + void make_send_field(Send_field *to) override + { + Field::make_send_field(to); + to->set_data_type_name(type_handler_inet6.name().lex_cstring()); + } + + bool validate_value_in_record(THD *thd, const uchar *record) const override + { + return false; + } + + String *val_str(String *val_buffer, + String *val_ptr __attribute__((unused))) override + { + DBUG_ASSERT(marked_for_read()); + Inet6_null tmp((const char *) ptr, pack_length()); + return tmp.to_string(val_buffer) ? NULL : val_buffer; + } + + my_decimal *val_decimal(my_decimal *to) override + { + DBUG_ASSERT(marked_for_read()); + my_decimal_set_zero(to); + return to; + } + + longlong val_int() override + { + DBUG_ASSERT(marked_for_read()); + return 0; + } + + double val_real() override + { + DBUG_ASSERT(marked_for_read()); + return 0; + } + + bool get_date(MYSQL_TIME *ltime, date_mode_t fuzzydate) override + { + DBUG_ASSERT(marked_for_read()); + set_zero_time(ltime, MYSQL_TIMESTAMP_TIME); + return false; + } + + bool val_bool(void) override + { + DBUG_ASSERT(marked_for_read()); + return !Inet6::only_zero_bytes((const char *) ptr, Inet6::binary_length()); + } + + int store_native(const Native &value) override + { + DBUG_ASSERT(marked_for_write_or_computed()); + DBUG_ASSERT(value.length() == Inet6::binary_length()); + memcpy(ptr, value.ptr(), value.length()); + return 0; + } + + int store(const char *str, size_t length, CHARSET_INFO *cs) override + { + return cs == &my_charset_bin ? store_binary(str, length) : + store_text(str, length, cs); + } + + int store_text(const char *str, size_t length, CHARSET_INFO *cs) override + { + return store_inet6_null_with_warn(Inet6_null(str, length, cs), + ErrConvString(str, length, cs)); + } + + int store_binary(const char *str, size_t length) override + { + return store_inet6_null_with_warn(Inet6_null(str, length), + ErrConvString(str, length, + &my_charset_bin)); + } + + int store_hex_hybrid(const char *str, size_t length) override + { + return Field_inet6::store_binary(str, length); + } + + int store_decimal(const my_decimal *num) override + { + DBUG_ASSERT(marked_for_write_or_computed()); + return set_min_value_with_warn(ErrConvDecimal(num)); + } + + int store(longlong nr, bool unsigned_flag) override + { + DBUG_ASSERT(marked_for_write_or_computed()); + return set_min_value_with_warn( + ErrConvInteger(Longlong_hybrid(nr, unsigned_flag))); + } + + int store(double nr) override + { + DBUG_ASSERT(marked_for_write_or_computed()); + return set_min_value_with_warn(ErrConvDouble(nr)); + } + + int store_time_dec(const MYSQL_TIME *ltime, uint dec) override + { + DBUG_ASSERT(marked_for_write_or_computed()); + return set_min_value_with_warn(ErrConvTime(ltime)); + } + + /*** Field conversion routines ***/ + int store_field(Field *from) override + { + // INSERT INTO t1 (inet6_field) SELECT different_field_type FROM t2; + return from->save_in_field(this); + } + int save_in_field(Field *to) override + { + // INSERT INTO t2 (different_field_type) SELECT inet6_field FROM t1; + if (to->charset() == &my_charset_bin && + dynamic_cast + (to->type_handler())) + { + NativeBufferInet6 res; + val_native(&res); + return to->store(res.ptr(), res.length(), &my_charset_bin); + } + return save_in_field_str(to); + } + Copy_func *get_copy_func(const Field *from) const override + { + // ALTER to INET6 from another field + return do_field_string; + } + + Copy_func *get_copy_func_to(const Field *to) const override + { + if (type_handler() == to->type_handler()) + { + // ALTER from INET6 to INET6 + DBUG_ASSERT(pack_length() == to->pack_length()); + DBUG_ASSERT(charset() == to->charset()); + DBUG_ASSERT(sort_charset() == to->sort_charset()); + return Field::do_field_eq; + } + // ALTER from INET6 to another data type + if (to->charset() == &my_charset_bin && + dynamic_cast + (to->type_handler())) + { + /* + ALTER from INET6 to a binary string type, e.g.: + BINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB + */ + return do_field_inet6_native_to_binary; + } + return do_field_string; + } + + static void do_field_inet6_native_to_binary(Copy_field *copy) + { + NativeBufferInet6 res; + copy->from_field->val_native(&res); + copy->to_field->store(res.ptr(), res.length(), &my_charset_bin); + } + + bool memcpy_field_possible(const Field *from) const override + { + // INSERT INTO t1 (inet6_field) SELECT field2 FROM t2; + return type_handler() == from->type_handler(); + } + enum_conv_type rpl_conv_type_from(const Conv_source &source, + const Relay_log_info *rli, + const Conv_param ¶m) const override + { + if (type_handler() == source.type_handler() || + (source.type_handler() == &type_handler_string && + source.type_handler()->max_display_length_for_field(source) == + Inet6::binary_length())) + return rpl_conv_type_from_same_data_type(source.metadata(), rli, param); + return CONV_TYPE_IMPOSSIBLE; + } + + /*** Optimizer routines ***/ + bool test_if_equality_guarantees_uniqueness(const Item *const_item) const override + { + /* + This condition: + WHERE inet6_field=const + should return a single distinct value only, + as comparison is done according to INET6. + */ + return true; + } + bool can_be_substituted_to_equal_item(const Context &ctx, + const Item_equal *item_equal) + override + { + switch (ctx.subst_constraint()) { + case ANY_SUBST: + return ctx.compare_type_handler() == item_equal->compare_type_handler(); + case IDENTITY_SUBST: + return true; + } + return false; + } + Item *get_equal_const_item(THD *thd, const Context &ctx, + Item *const_item) override; + bool can_optimize_keypart_ref(const Item_bool_func *cond, + const Item *item) const override + { + /* + Mixing of two different non-traditional types is currently prevented. + This may change in the future. For example, INET4 and INET6 + data types can be made comparable. + */ + DBUG_ASSERT(item->type_handler()->is_traditional_scalar_type() || + item->type_handler() == type_handler()); + return true; + } + /** + Test if Field can use range optimizer for a standard comparison operation: + <=, <, =, <=>, >, >= + Note, this method does not cover spatial operations. + */ + bool can_optimize_range(const Item_bool_func *cond, + const Item *item, + bool is_eq_func) const override + { + // See the DBUG_ASSERT comment in can_optimize_keypart_ref() + DBUG_ASSERT(item->type_handler()->is_traditional_scalar_type() || + item->type_handler() == type_handler()); + return true; + } + SEL_ARG *get_mm_leaf(RANGE_OPT_PARAM *prm, KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, Item *value) override + { + DBUG_ENTER("Field_inet6::get_mm_leaf"); + if (!can_optimize_scalar_range(prm, key_part, cond, op, value)) + DBUG_RETURN(0); + int err= value->save_in_field_no_warnings(this, 1); + if ((op != SCALAR_CMP_EQUAL && is_real_null()) || err < 0) + DBUG_RETURN(&null_element); + if (err > 0) + { + if (op == SCALAR_CMP_EQ || op == SCALAR_CMP_EQUAL) + DBUG_RETURN(new (prm->mem_root) SEL_ARG_IMPOSSIBLE(this)); + DBUG_RETURN(NULL); /* Cannot infer anything */ + } + DBUG_RETURN(stored_field_make_mm_leaf(prm, key_part, op, value)); + } + bool can_optimize_hash_join(const Item_bool_func *cond, + const Item *item) const override + { + return can_optimize_keypart_ref(cond, item); + } + bool can_optimize_group_min_max(const Item_bool_func *cond, + const Item *const_item) const override + { + return true; + } + + uint row_pack_length() const override { return pack_length(); } + + Binlog_type_info binlog_type_info() const override + { + DBUG_ASSERT(type() == binlog_type()); + return Binlog_type_info_fixed_string(Field_inet6::binlog_type(), + Inet6::binary_length(), + &my_charset_bin); + } + + uchar *pack(uchar *to, const uchar *from, uint max_length) override + { + DBUG_PRINT("debug", ("Packing field '%s'", field_name.str)); + return StringPack(&my_charset_bin, Inet6::binary_length()). + pack(to, from, max_length); + } + + const uchar *unpack(uchar *to, const uchar *from, const uchar *from_end, + uint param_data) override + { + return StringPack(&my_charset_bin, Inet6::binary_length()). + unpack(to, from, from_end, param_data); + } + + uint max_packed_col_length(uint max_length) override + { + return StringPack::max_packed_col_length(max_length); + } + + uint packed_col_length(const uchar *data_ptr, uint length) override + { + return StringPack::packed_col_length(data_ptr, length); + } + + /**********/ + uint size_of() const override { return sizeof(*this); } +}; + + +class Item_typecast_inet6: public Item_func +{ +public: + Item_typecast_inet6(THD *thd, Item *a) :Item_func(thd, a) {} + + const Type_handler *type_handler() const override + { return &type_handler_inet6; } + + enum Functype functype() const override { return CHAR_TYPECAST_FUNC; } + bool eq(const Item *item, bool binary_cmp) const override + { + if (this == item) + return true; + if (item->type() != FUNC_ITEM || + functype() != ((Item_func*)item)->functype()) + return false; + if (type_handler() != item->type_handler()) + return false; + Item_typecast_inet6 *cast= (Item_typecast_inet6*) item; + return args[0]->eq(cast->args[0], binary_cmp); + } + const char *func_name() const override { return "cast_as_inet6"; } + void print(String *str, enum_query_type query_type) override + { + str->append(STRING_WITH_LEN("cast(")); + args[0]->print(str, query_type); + str->append(STRING_WITH_LEN(" as inet6)")); + } + bool fix_length_and_dec() override + { + Type_std_attributes::operator=(Type_std_attributes_inet6()); + if (Inet6::fix_fields_maybe_null_on_conversion_to_inet6(args[0])) + maybe_null= true; + return false; + } + String *val_str(String *to) override + { + Inet6_null tmp(args[0]); + return (null_value= tmp.is_null() || tmp.to_string(to)) ? NULL : to; + } + longlong val_int() override + { + return 0; + } + double val_real() override + { + return 0; + } + my_decimal *val_decimal(my_decimal *to) override + { + my_decimal_set_zero(to); + return to; + } + bool get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate) override + { + set_zero_time(ltime, MYSQL_TIMESTAMP_TIME); + return false; + } + bool val_native(THD *thd, Native *to) override + { + Inet6_null tmp(args[0]); + return null_value= tmp.is_null() || tmp.to_native(to); + } + Item *get_copy(THD *thd) override + { return get_item_copy(thd, this); } +}; + + +class Item_cache_inet6: public Item_cache +{ + NativeBufferInet6 m_value; +public: + Item_cache_inet6(THD *thd) + :Item_cache(thd, &type_handler_inet6) + { } + Item *get_copy(THD *thd) + { return get_item_copy(thd, this); } + bool cache_value() + { + if (!example) + return false; + value_cached= true; + null_value= example->val_native_with_conversion_result(current_thd, + &m_value, + type_handler()); + return true; + } + String* val_str(String *to) + { + if (!has_value()) + return NULL; + Inet6_null tmp(m_value.ptr(), m_value.length()); + return tmp.is_null() || tmp.to_string(to) ? NULL : to; + } + my_decimal *val_decimal(my_decimal *to) + { + if (!has_value()) + return NULL; + my_decimal_set_zero(to); + return to; + } + longlong val_int() + { + if (!has_value()) + return 0; + return 0; + } + double val_real() + { + if (!has_value()) + return 0; + return 0; + } + longlong val_datetime_packed(THD *thd) + { + DBUG_ASSERT(0); + if (!has_value()) + return 0; + return 0; + } + longlong val_time_packed(THD *thd) + { + DBUG_ASSERT(0); + if (!has_value()) + return 0; + return 0; + } + bool get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate) + { + if (!has_value()) + return true; + set_zero_time(ltime, MYSQL_TIMESTAMP_TIME); + return false; + } + bool val_native(THD *thd, Native *to) + { + if (!has_value()) + return true; + return to->copy(m_value.ptr(), m_value.length()); + } +}; + + +class Item_literal_inet6: public Item_literal +{ + Inet6 m_value; +public: + Item_literal_inet6(THD *thd) + :Item_literal(thd), + m_value(Inet6_zero()) + { } + Item_literal_inet6(THD *thd, const Inet6 &value) + :Item_literal(thd), + m_value(value) + { } + const Type_handler *type_handler() const override + { + return &type_handler_inet6; + } + longlong val_int() override + { + return 0; + } + double val_real() override + { + return 0; + } + String *val_str(String *to) override + { + return m_value.to_string(to) ? NULL : to; + } + my_decimal *val_decimal(my_decimal *to) override + { + my_decimal_set_zero(to); + return to; + } + bool get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate) override + { + set_zero_time(ltime, MYSQL_TIMESTAMP_TIME); + return false; + } + bool val_native(THD *thd, Native *to) override + { + return m_value.to_native(to); + } + void print(String *str, enum_query_type query_type) override + { + StringBufferInet6 tmp; + m_value.to_string(&tmp); + str->append("INET6'"); + str->append(tmp); + str->append('\''); + } + Item *get_copy(THD *thd) override + { return get_item_copy(thd, this); } + + // Non-overriding methods + void set_value(const Inet6 &value) + { + m_value= value; + } +}; + + +class in_inet6 :public in_vector +{ + Inet6 m_value; + static int cmp_inet6(void *cmp_arg, Inet6 *a, Inet6 *b) + { + return a->cmp(*b); + } +public: + in_inet6(THD *thd, uint elements) + :in_vector(thd, elements, sizeof(Inet6), (qsort2_cmp) cmp_inet6, 0), + m_value(Inet6_zero()) + { } + const Type_handler *type_handler() const override + { + return &type_handler_inet6; + } + void set(uint pos, Item *item) override + { + Inet6 *buff= &((Inet6 *) base)[pos]; + Inet6_null value(item); + if (value.is_null()) + *buff= Inet6_zero(); + else + *buff= value; + } + uchar *get_value(Item *item) override + { + Inet6_null value(item); + if (value.is_null()) + return 0; + m_value= value; + return (uchar *) &m_value; + } + Item* create_item(THD *thd) override + { + return new (thd->mem_root) Item_literal_inet6(thd); + } + void value_to_item(uint pos, Item *item) override + { + const Inet6 &buff= (((Inet6*) base)[pos]); + static_cast(item)->set_value(buff); + } +}; + + +class Item_char_typecast_func_handler_inet6_to_binary: + public Item_handled_func::Handler_str +{ +public: + const Type_handler *return_type_handler(const Item_handled_func *item) + const override + { + if (item->max_length > MAX_FIELD_VARCHARLENGTH) + return Type_handler::blob_type_handler(item->max_length); + if (item->max_length > 255) + return &type_handler_varchar; + return &type_handler_string; + } + bool fix_length_and_dec(Item_handled_func *xitem) const override + { + return false; + } + String *val_str(Item_handled_func *item, String *to) const override + { + DBUG_ASSERT(dynamic_cast(item)); + return static_cast(item)-> + val_str_binary_from_native(to); + } +}; + + +static Item_char_typecast_func_handler_inet6_to_binary + item_char_typecast_func_handler_inet6_to_binary; + + +bool Type_handler_inet6:: + Item_char_typecast_fix_length_and_dec(Item_char_typecast *item) const +{ + if (item->cast_charset() == &my_charset_bin) + { + item->fix_length_and_dec_native_to_binary(Inet6::binary_length()); + item->set_func_handler(&item_char_typecast_func_handler_inet6_to_binary); + return false; + } + item->fix_length_and_dec_str(); + return false; +} + + +bool +Type_handler_inet6::character_or_binary_string_to_native(THD *thd, + const String *str, + Native *to) const +{ + if (str->charset() == &my_charset_bin) + { + // Convert from a binary string + if (str->length() != Inet6::binary_length() || + to->copy(str->ptr(), str->length())) + { + thd->push_warning_wrong_value(Sql_condition::WARN_LEVEL_WARN, + name().ptr(), + ErrConvString(str).ptr()); + return true; + } + return false; + } + // Convert from a character string + Inet6_null tmp(*str); + if (tmp.is_null()) + thd->push_warning_wrong_value(Sql_condition::WARN_LEVEL_WARN, + name().ptr(), + ErrConvString(str).ptr()); + return tmp.is_null() || tmp.to_native(to); +} + + +bool +Type_handler_inet6::Item_save_in_value(THD *thd, + Item *item, + st_value *value) const +{ + value->m_type= DYN_COL_STRING; + String *str= item->val_str(&value->m_string); + if (str != &value->m_string && !item->null_value) + { + // "item" returned a non-NULL value + if (Inet6_null(*str).is_null()) + { + /* + The value was not-null, but conversion to INET6 failed: + SELECT a, DECODE_ORACLE(inet6col, 'garbage', '', '::01', '01') + FROM t1; + */ + thd->push_warning_wrong_value(Sql_condition::WARN_LEVEL_WARN, + name().ptr(), + ErrConvString(str).ptr()); + value->m_type= DYN_COL_NULL; + return true; + } + // "item" returned a non-NULL value, and it was a valid INET6 + value->m_string.set(str->ptr(), str->length(), str->charset()); + } + return check_null(item, value); +} + + +void Type_handler_inet6::Item_param_setup_conversion(THD *thd, + Item_param *param) const +{ + param->setup_conversion_string(thd, thd->variables.character_set_client); +} + + +void Type_handler_inet6::make_sort_key_part(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const +{ + DBUG_ASSERT(item->type_handler() == this); + NativeBufferInet6 tmp; + item->val_native_result(current_thd, &tmp); + if (item->maybe_null) + { + if (item->null_value) + { + memset(to, 0, Inet6::binary_length() + 1); + return; + } + *to++= 1; + } + DBUG_ASSERT(!item->null_value); + DBUG_ASSERT(Inet6::binary_length() == tmp.length()); + DBUG_ASSERT(Inet6::binary_length() == sort_field->length); + memcpy(to, tmp.ptr(), tmp.length()); +} + +uint +Type_handler_inet6::make_packed_sort_key_part(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const +{ + DBUG_ASSERT(item->type_handler() == this); + NativeBufferInet6 tmp; + item->val_native_result(current_thd, &tmp); + if (item->maybe_null) + { + if (item->null_value) + { + *to++=0; + return 0; + } + *to++= 1; + } + DBUG_ASSERT(!item->null_value); + DBUG_ASSERT(Inet6::binary_length() == tmp.length()); + DBUG_ASSERT(Inet6::binary_length() == sort_field->length); + memcpy(to, tmp.ptr(), tmp.length()); + return tmp.length(); +} + +void Type_handler_inet6::sort_length(THD *thd, + const Type_std_attributes *item, + SORT_FIELD_ATTR *attr) const +{ + attr->original_length= attr->length= Inet6::binary_length(); + attr->suffix_length= 0; +} + + +cmp_item *Type_handler_inet6::make_cmp_item(THD *thd, CHARSET_INFO *cs) const +{ + return new (thd->mem_root) cmp_item_inet6; +} + + + +in_vector * +Type_handler_inet6::make_in_vector(THD *thd, const Item_func_in *func, + uint nargs) const +{ + return new (thd->mem_root) in_inet6(thd, nargs); +} + + +Item *Type_handler_inet6::create_typecast_item(THD *thd, Item *item, + const Type_cast_attributes &attr) + const +{ + return new (thd->mem_root) Item_typecast_inet6(thd, item); +} + + +Item_cache *Type_handler_inet6::Item_get_cache(THD *thd, const Item *item) const +{ + return new (thd->mem_root) Item_cache_inet6(thd); +} + + +Item * +Type_handler_inet6::make_const_item_for_comparison(THD *thd, + Item *src, + const Item *cmp) const +{ + Inet6_null tmp(src); + if (tmp.is_null()) + return new (thd->mem_root) Item_null(thd, src->name.str); + return new (thd->mem_root) Item_literal_inet6(thd, tmp); +} + + +Item *Field_inet6::get_equal_const_item(THD *thd, const Context &ctx, + Item *const_item) +{ + Inet6_null tmp(const_item); + if (tmp.is_null()) + return NULL; + return new (thd->mem_root) Item_literal_inet6(thd, tmp); +} + + +Field * +Type_handler_inet6::make_table_field_from_def( + TABLE_SHARE *share, + MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &addr, + const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) Field_inet6(name, addr); +} + + +Field *Type_handler_inet6::make_table_field(MEM_ROOT *root, + const LEX_CSTRING *name, + const Record_addr &addr, + const Type_all_attributes &attr, + TABLE_SHARE *share) const +{ + return new (root) Field_inet6(name, addr); +} + + +Field *Type_handler_inet6::make_conversion_table_field(MEM_ROOT *root, + TABLE *table, + uint metadata, + const Field *target) + const +{ + const Record_addr tmp(NULL, Bit_addr(true)); + return new (table->in_use->mem_root) Field_inet6(&empty_clex_str, tmp); +} + + +bool Type_handler_inet6::partition_field_check(const LEX_CSTRING &field_name, + Item *item_expr) const +{ + if (item_expr->cmp_type() != STRING_RESULT) + { + my_error(ER_WRONG_TYPE_COLUMN_VALUE_ERROR, MYF(0)); + return true; + } + return false; +} + + +bool +Type_handler_inet6::partition_field_append_value( + String *to, + Item *item_expr, + CHARSET_INFO *field_cs, + partition_value_print_mode_t mode) + const +{ + StringBufferInet6 inet6str; + Inet6_null inet6(item_expr); + if (inet6.is_null()) + { + my_error(ER_PARTITION_FUNCTION_IS_NOT_ALLOWED, MYF(0)); + return true; + } + return inet6.to_string(&inet6str) || + to->append('\'') || + to->append(inet6str) || + to->append('\''); +} + + +/***************************************************************/ + + +class Type_collection_inet: public Type_collection +{ + const Type_handler *aggregate_common(const Type_handler *a, + const Type_handler *b) const + { + if (a == b) + return a; + return NULL; + } + const Type_handler *aggregate_if_string(const Type_handler *a, + const Type_handler *b) const + { + static const Type_aggregator::Pair agg[]= + { + {&type_handler_inet6, &type_handler_null, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_varchar, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_string, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_tiny_blob, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_blob, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_medium_blob, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_long_blob, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_hex_hybrid, &type_handler_inet6}, + {NULL,NULL,NULL} + }; + return Type_aggregator::find_handler_in_array(agg, a, b, true); + } +public: + const Type_handler *aggregate_for_result(const Type_handler *a, + const Type_handler *b) + const override + { + const Type_handler *h; + if ((h= aggregate_common(a, b)) || + (h= aggregate_if_string(a, b))) + return h; + return NULL; + } + + const Type_handler *aggregate_for_min_max(const Type_handler *a, + const Type_handler *b) + const override + { + return aggregate_for_result(a, b); + } + + const Type_handler *aggregate_for_comparison(const Type_handler *a, + const Type_handler *b) + const override + { + if (const Type_handler *h= aggregate_common(a, b)) + return h; + static const Type_aggregator::Pair agg[]= + { + {&type_handler_inet6, &type_handler_null, &type_handler_inet6}, + {&type_handler_inet6, &type_handler_long_blob, &type_handler_inet6}, + {NULL,NULL,NULL} + }; + return Type_aggregator::find_handler_in_array(agg, a, b, true); + } + + const Type_handler *aggregate_for_num_op(const Type_handler *a, + const Type_handler *b) + const override + { + return NULL; + } + + const Type_handler *handler_by_name(const LEX_CSTRING &name) const override + { + if (type_handler_inet6.name().eq(name)) + return &type_handler_inet6; + return NULL; + } +}; + + +const Type_collection *Type_handler_inet6::type_collection() const +{ + static Type_collection_inet type_collection_inet; + return &type_collection_inet; +} diff --git a/plugin/type_inet/sql_type_inet.h b/plugin/type_inet/sql_type_inet.h new file mode 100644 index 00000000..4de1124f --- /dev/null +++ b/plugin/type_inet/sql_type_inet.h @@ -0,0 +1,1028 @@ +#ifndef SQL_TYPE_INET_H +#define SQL_TYPE_INET_H +/* Copyright (c) 2011, 2013, Oracle and/or its affiliates. All rights reserved. + Copyright (c) 2014 MariaDB Foundation + Copyright (c) 2019 MariaDB Corporation + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ + + +static const size_t IN_ADDR_SIZE= 4; +static const size_t IN_ADDR_MAX_CHAR_LENGTH= 15; + +static const size_t IN6_ADDR_SIZE= 16; +static const size_t IN6_ADDR_NUM_WORDS= IN6_ADDR_SIZE / 2; + +/** + Non-abbreviated syntax is 8 groups, up to 4 digits each, + plus 7 delimiters between the groups. + Abbreviated syntax is even shorter. +*/ +static const uint IN6_ADDR_MAX_CHAR_LENGTH= 8 * 4 + 7; + + +class NativeBufferInet6: public NativeBuffer +{ +}; + +class StringBufferInet6: public StringBuffer +{ +}; + +/***********************************************************************/ + +class Inet4 +{ + char m_buffer[IN_ADDR_SIZE]; +protected: + bool ascii_to_ipv4(const char *str, size_t length); + bool character_string_to_ipv4(const char *str, size_t str_length, + CHARSET_INFO *cs) + { + if (cs->state & MY_CS_NONASCII) + { + char tmp[IN_ADDR_MAX_CHAR_LENGTH]; + String_copier copier; + uint length= copier.well_formed_copy(&my_charset_latin1, tmp, sizeof(tmp), + cs, str, str_length); + return ascii_to_ipv4(tmp, length); + } + return ascii_to_ipv4(str, str_length); + } + bool binary_to_ipv4(const char *str, size_t length) + { + if (length != sizeof(m_buffer)) + return true; + memcpy(m_buffer, str, length); + return false; + } + // Non-initializing constructor + Inet4() { } +public: + void to_binary(char *dst, size_t dstsize) const + { + DBUG_ASSERT(dstsize >= sizeof(m_buffer)); + memcpy(dst, m_buffer, sizeof(m_buffer)); + } + bool to_binary(String *to) const + { + return to->copy(m_buffer, sizeof(m_buffer), &my_charset_bin); + } + size_t to_string(char *dst, size_t dstsize) const; + bool to_string(String *to) const + { + to->set_charset(&my_charset_latin1); + if (to->alloc(INET_ADDRSTRLEN)) + return true; + to->length((uint32) to_string((char*) to->ptr(), INET_ADDRSTRLEN)); + return false; + } +}; + + +class Inet4_null: public Inet4, public Null_flag +{ +public: + // Initialize from a text representation + Inet4_null(const char *str, size_t length, CHARSET_INFO *cs) + :Null_flag(character_string_to_ipv4(str, length, cs)) + { } + Inet4_null(const String &str) + :Inet4_null(str.ptr(), str.length(), str.charset()) + { } + // Initialize from a binary representation + Inet4_null(const char *str, size_t length) + :Null_flag(binary_to_ipv4(str, length)) + { } + Inet4_null(const Binary_string &str) + :Inet4_null(str.ptr(), str.length()) + { } +public: + const Inet4& to_inet4() const + { + DBUG_ASSERT(!is_null()); + return *this; + } + void to_binary(char *dst, size_t dstsize) const + { + to_inet4().to_binary(dst, dstsize); + } + bool to_binary(String *to) const + { + return to_inet4().to_binary(to); + } + size_t to_string(char *dst, size_t dstsize) const + { + return to_inet4().to_string(dst, dstsize); + } + bool to_string(String *to) const + { + return to_inet4().to_string(to); + } +}; + + +class Inet6 +{ +protected: + char m_buffer[IN6_ADDR_SIZE]; + bool make_from_item(Item *item, bool warn); + bool ascii_to_ipv6(const char *str, size_t str_length); + bool character_string_to_ipv6(const char *str, size_t str_length, + CHARSET_INFO *cs) + { + if (cs->state & MY_CS_NONASCII) + { + char tmp[IN6_ADDR_MAX_CHAR_LENGTH]; + String_copier copier; + uint length= copier.well_formed_copy(&my_charset_latin1, tmp, sizeof(tmp), + cs, str, str_length); + return ascii_to_ipv6(tmp, length); + } + return ascii_to_ipv6(str, str_length); + } + bool make_from_character_or_binary_string(const String *str, bool warn); + bool binary_to_ipv6(const char *str, size_t length) + { + if (length != sizeof(m_buffer)) + return true; + memcpy(m_buffer, str, length); + return false; + } + + Inet6() { } + +public: + static uint binary_length() { return IN6_ADDR_SIZE; } + /** + Non-abbreviated syntax is 8 groups, up to 4 digits each, + plus 7 delimiters between the groups. + Abbreviated syntax is even shorter. + */ + static uint max_char_length() { return IN6_ADDR_MAX_CHAR_LENGTH; } + + static bool only_zero_bytes(const char *ptr, uint length) + { + for (uint i= 0 ; i < length; i++) + { + if (ptr[i] != 0) + return false; + } + return true; + } + + /* + Check at Item's fix_fields() time if "item" can return a nullable value + on conversion to INET6, or conversion produces a NOT NULL INET6 value. + */ + static bool fix_fields_maybe_null_on_conversion_to_inet6(Item *item); + +public: + + Inet6(Item *item, bool *error, bool warn= true) + { + *error= make_from_item(item, warn); + } + void to_binary(char *str, size_t str_size) const + { + DBUG_ASSERT(str_size >= sizeof(m_buffer)); + memcpy(str, m_buffer, sizeof(m_buffer)); + } + bool to_binary(String *to) const + { + return to->copy(m_buffer, sizeof(m_buffer), &my_charset_bin); + } + bool to_native(Native *to) const + { + return to->copy(m_buffer, sizeof(m_buffer)); + } + size_t to_string(char *dst, size_t dstsize) const; + bool to_string(String *to) const + { + to->set_charset(&my_charset_latin1); + if (to->alloc(INET6_ADDRSTRLEN)) + return true; + to->length((uint32) to_string((char*) to->ptr(), INET6_ADDRSTRLEN)); + return false; + } + bool is_v4compat() const + { + static_assert(sizeof(in6_addr) == IN6_ADDR_SIZE, "unexpected in6_addr size"); + return IN6_IS_ADDR_V4COMPAT((struct in6_addr *) m_buffer); + } + bool is_v4mapped() const + { + static_assert(sizeof(in6_addr) == IN6_ADDR_SIZE, "unexpected in6_addr size"); + return IN6_IS_ADDR_V4MAPPED((struct in6_addr *) m_buffer); + } + int cmp(const char *str, size_t length) const + { + DBUG_ASSERT(length == sizeof(m_buffer)); + return memcmp(m_buffer, str, length); + } + int cmp(const Binary_string &other) const + { + return cmp(other.ptr(), other.length()); + } + int cmp(const Inet6 &other) const + { + return memcmp(m_buffer, other.m_buffer, sizeof(m_buffer)); + } +}; + + +class Inet6_zero: public Inet6 +{ +public: + Inet6_zero() + { + bzero(&m_buffer, sizeof(m_buffer)); + } +}; + + +class Inet6_null: public Inet6, public Null_flag +{ +public: + // Initialize from a text representation + Inet6_null(const char *str, size_t length, CHARSET_INFO *cs) + :Null_flag(character_string_to_ipv6(str, length, cs)) + { } + Inet6_null(const String &str) + :Inet6_null(str.ptr(), str.length(), str.charset()) + { } + // Initialize from a binary representation + Inet6_null(const char *str, size_t length) + :Null_flag(binary_to_ipv6(str, length)) + { } + Inet6_null(const Binary_string &str) + :Inet6_null(str.ptr(), str.length()) + { } + // Initialize from an Item + Inet6_null(Item *item, bool warn= true) + :Null_flag(make_from_item(item, warn)) + { } +public: + const Inet6& to_inet6() const + { + DBUG_ASSERT(!is_null()); + return *this; + } + void to_binary(char *str, size_t str_size) const + { + to_inet6().to_binary(str, str_size); + } + bool to_binary(String *to) const + { + return to_inet6().to_binary(to); + } + size_t to_string(char *dst, size_t dstsize) const + { + return to_inet6().to_string(dst, dstsize); + } + bool to_string(String *to) const + { + return to_inet6().to_string(to); + } + bool is_v4compat() const + { + return to_inet6().is_v4compat(); + } + bool is_v4mapped() const + { + return to_inet6().is_v4mapped(); + } +}; + + +class Type_std_attributes_inet6: public Type_std_attributes +{ +public: + Type_std_attributes_inet6() + :Type_std_attributes( + Type_numeric_attributes(Inet6::max_char_length(), 0, true), + DTCollation_numeric()) + { } +}; + + +class Type_handler_inet6: public Type_handler +{ + bool character_or_binary_string_to_native(THD *thd, const String *str, + Native *to) const; +public: + ~Type_handler_inet6() override {} + + const Type_collection *type_collection() const override; + const Name &default_value() const override + { + static Name def(STRING_WITH_LEN("::")); + return def; + } + protocol_send_type_t protocol_send_type() const override + { + return PROTOCOL_SEND_STRING; + } + bool Item_append_extended_type_info(Send_field_extended_metadata *to, + const Item *item) const override + { + return to->set_data_type_name(name().lex_cstring()); + } + + enum_field_types field_type() const override + { + return MYSQL_TYPE_STRING; + } + + Item_result result_type() const override + { + return STRING_RESULT; + } + + Item_result cmp_type() const override + { + return STRING_RESULT; + } + + enum_dynamic_column_type dyncol_type(const Type_all_attributes *attr) + const override + { + return DYN_COL_STRING; + } + + uint32 max_display_length_for_field(const Conv_source &src) const override + { + return Inet6::max_char_length(); + } + + const Type_handler *type_handler_for_comparison() const override + { + return this; + } + + int + stored_field_cmp_to_item(THD *thd, Field *field, Item *item) const override + { + DBUG_ASSERT(field->type_handler() == this); + Inet6_null ni(item); // Convert Item to INET6 + if (ni.is_null()) + return 0; + NativeBufferInet6 tmp; + if (field->val_native(&tmp)) + { + DBUG_ASSERT(0); + return 0; + } + return -ni.cmp(tmp); + } + CHARSET_INFO *charset_for_protocol(const Item *item) const override + { + return item->collation.collation; + } + + bool is_scalar_type() const override { return true; } + bool is_val_native_ready() const override { return true; } + bool can_return_int() const override { return false; } + bool can_return_decimal() const override { return false; } + bool can_return_real() const override { return false; } + bool can_return_str() const override { return true; } + bool can_return_text() const override { return true; } + bool can_return_date() const override { return false; } + bool can_return_time() const override { return false; } + bool convert_to_binary_using_val_native() const override { return true; } + + uint Item_time_precision(THD *thd, Item *item) const override + { + return 0; + } + uint Item_datetime_precision(THD *thd, Item *item) const override + { + return 0; + } + uint Item_decimal_scale(const Item *item) const override + { + return 0; + } + uint Item_decimal_precision(const Item *item) const override + { + /* + This will be needed if we ever allow cast from INET6 to DECIMAL. + Decimal precision of INET6 is 39 digits: + 'ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff' = + 340282366920938463463374607431768211456 = 39 digits + */ + return 39; + } + + /* + Returns how many digits a divisor adds into a division result. + See Item::divisor_precision_increment() in item.h for more comments. + */ + uint Item_divisor_precision_increment(const Item *) const override + { + return 0; + } + /** + Makes a temporary table Field to handle numeric aggregate functions, + e.g. SUM(DISTINCT expr), AVG(DISTINCT expr), etc. + */ + Field *make_num_distinct_aggregator_field(MEM_ROOT *, + const Item *) const override + { + DBUG_ASSERT(0); + return 0; + } + Field *make_conversion_table_field(MEM_ROOT *root, + TABLE *TABLE, + uint metadata, + const Field *target) const override; + // Fix attributes after the parser + bool Column_definition_fix_attributes(Column_definition *c) const override + { + c->length= Inet6::max_char_length(); + return false; + } + + bool Column_definition_prepare_stage1(THD *thd, + MEM_ROOT *mem_root, + Column_definition *def, + handler *file, + ulonglong table_flags, + const Column_derived_attributes + *derived_attr) + const override + { + def->prepare_stage1_simple(&my_charset_numeric); + return false; + } + + bool Column_definition_redefine_stage1(Column_definition *def, + const Column_definition *dup, + const handler *file) + const override + { + def->redefine_stage1_common(dup, file); + def->set_compression_method(dup->compression_method()); + def->create_length_to_internal_length_string(); + return false; + } + + bool Column_definition_prepare_stage2(Column_definition *def, + handler *file, + ulonglong table_flags) const override + { + def->pack_flag= FIELDFLAG_BINARY; + return false; + } + + bool partition_field_check(const LEX_CSTRING &field_name, + Item *item_expr) const override; + + bool partition_field_append_value(String *to, + Item *item_expr, + CHARSET_INFO *field_cs, + partition_value_print_mode_t mode) + const override; + + Field *make_table_field(MEM_ROOT *root, + const LEX_CSTRING *name, + const Record_addr &addr, + const Type_all_attributes &attr, + TABLE_SHARE *table) const override; + + Field * + make_table_field_from_def(TABLE_SHARE *share, + MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &addr, + const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const override; + void + Column_definition_attributes_frm_pack(const Column_definition_attributes *def, + uchar *buff) const override + { + def->frm_pack_basic(buff); + def->frm_pack_charset(buff); + } + bool + Column_definition_attributes_frm_unpack(Column_definition_attributes *def, + TABLE_SHARE *share, + const uchar *buffer, + LEX_CUSTRING *gis_options) + const override + { + def->frm_unpack_basic(buffer); + return def->frm_unpack_charset(share, buffer); + } + void make_sort_key_part(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) + const override; + uint make_packed_sort_key_part(uchar *to, Item *item, + const SORT_FIELD_ATTR *sort_field, + Sort_param *param) const override; + void sort_length(THD *thd, + const Type_std_attributes *item, + SORT_FIELD_ATTR *attr) const override; + uint32 max_display_length(const Item *item) const override + { + return Inet6::max_char_length(); + } + uint32 calc_pack_length(uint32 length) const override + { + return Inet6::binary_length(); + } + void Item_update_null_value(Item *item) const override + { + NativeBufferInet6 tmp; + item->val_native(current_thd, &tmp); + } + bool Item_save_in_value(THD *thd, Item *item, st_value *value) const override; + void Item_param_setup_conversion(THD *thd, Item_param *param) const override; + void Item_param_set_param_func(Item_param *param, + uchar **pos, ulong len) const override + { + param->set_param_str(pos, len); + } + bool Item_param_set_from_value(THD *thd, + Item_param *param, + const Type_all_attributes *attr, + const st_value *val) const override + { + param->unsigned_flag= false;//QQ + param->setup_conversion_string(thd, attr->collation.collation); + /* + Exact value of max_length is not known unless data is converted to + charset of connection, so we have to set it later. + */ + return param->set_str(val->m_string.ptr(), val->m_string.length(), + attr->collation.collation, + attr->collation.collation); + } + bool Item_param_val_native(THD *thd, Item_param *item, Native *to) + const override + { + StringBufferInet6 buffer; + String *str= item->val_str(&buffer); + if (!str) + return true; + Inet6_null tmp(*str); + return tmp.is_null() || tmp.to_native(to); + } + bool Item_send(Item *item, Protocol *p, st_value *buf) const override + { + return Item_send_str(item, p, buf); + } + int Item_save_in_field(Item *item, Field *field, bool no_conversions) + const override + { + if (field->type_handler() == this) + { + NativeBuffer tmp; + bool rc= item->val_native(current_thd, &tmp); + if (rc || item->null_value) + return set_field_to_null_with_conversions(field, no_conversions); + field->set_notnull(); + return field->store_native(tmp); + } + return item->save_str_in_field(field, no_conversions); + } + + String *print_item_value(THD *thd, Item *item, String *str) const override + { + StringBufferInet6 buf; + String *result= item->val_str(&buf); + /* + TODO: This should eventually use one of these notations: + 1. CAST('::' AS INET6) + Problem: CAST is not supported as a NAME_CONST() argument. + 2. INET6':: + Problem: This syntax is not supported by the parser yet. + */ + return !result || + str->realloc(result->length() + 2) || + str->append(STRING_WITH_LEN("'")) || + str->append(result->ptr(), result->length()) || + str->append(STRING_WITH_LEN("'")) ? + NULL : + str; + } + + /** + Check if + WHERE expr=value AND expr=const + can be rewritten as: + WHERE const=value AND expr=const + + "this" is the comparison handler that is used by "target". + + @param target - the predicate expr=value, + whose "expr" argument will be replaced to "const". + @param target_expr - the target's "expr" which will be replaced to "const". + @param target_value - the target's second argument, it will remain unchanged. + @param source - the equality predicate expr=const (or expr<=>const) + that can be used to rewrite the "target" part + (under certain conditions, see the code). + @param source_expr - the source's "expr". It should be exactly equal to + the target's "expr" to make condition rewrite possible. + @param source_const - the source's "const" argument, it will be inserted + into "target" instead of "expr". + */ + bool + can_change_cond_ref_to_const(Item_bool_func2 *target, + Item *target_expr, Item *target_value, + Item_bool_func2 *source, + Item *source_expr, Item *source_const) + const override + { + /* + WHERE COALESCE(inet6_col)='::1' AND COALESCE(inet6_col)=CONCAT(a); --> + WHERE COALESCE(inet6_col)='::1' AND '::1'=CONCAT(a); + */ + return target->compare_type_handler() == source->compare_type_handler(); + } + bool + subquery_type_allows_materialization(const Item *inner, + const Item *outer, bool) const override + { + /* + Example: + SELECT * FROM t1 WHERE a IN (SELECT inet6col FROM t1 GROUP BY inet6col); + Allow materialization only if the outer column is also INET6. + This can be changed for more relaxed rules in the future. + */ + DBUG_ASSERT(inner->type_handler() == this); + return outer->type_handler() == this; + } + /** + Make a simple constant replacement item for a constant "src", + so the new item can futher be used for comparison with "cmp", e.g.: + src = cmp -> replacement = cmp + + "this" is the type handler that is used to compare "src" and "cmp". + + @param thd - current thread, for mem_root + @param src - The item that we want to replace. It's a const item, + but it can be complex enough to calculate on every row. + @param cmp - The src's comparand. + @retval - a pointer to the created replacement Item + @retval - NULL, if could not create a replacement (e.g. on EOM). + NULL is also returned for ROWs, because instead of replacing + a Item_row to a new Item_row, Type_handler_row just replaces + its elements. + */ + Item *make_const_item_for_comparison(THD *thd, + Item *src, + const Item *cmp) const override; + Item_cache *Item_get_cache(THD *thd, const Item *item) const override; + + Item *create_typecast_item(THD *thd, Item *item, + const Type_cast_attributes &attr) const override; + + int cmp_native(const Native &a, const Native &b) const override + { + DBUG_ASSERT(a.length() == Inet6::binary_length()); + DBUG_ASSERT(b.length() == Inet6::binary_length()); + return memcmp(a.ptr(), b.ptr(), Inet6::binary_length()); + } + bool set_comparator_func(Arg_comparator *cmp) const override + { + return cmp->set_cmp_func_native(); + } + bool Item_const_eq(const Item_const *a, const Item_const *b, + bool binary_cmp) const override + { + return false;//QQ + } + bool Item_eq_value(THD *thd, const Type_cmp_attributes *attr, + Item *a, Item *b) const override + { + Inet6_null na(a); + Inet6_null nb(b); + return !na.is_null() && !nb.is_null() && !na.cmp(nb); + } + bool Item_hybrid_func_fix_attributes(THD *thd, + const char *name, + Type_handler_hybrid_field_type *h, + Type_all_attributes *attr, + Item **items, + uint nitems) const override + { + attr->Type_std_attributes::operator=(Type_std_attributes_inet6()); + h->set_handler(this); + /* + If some of the arguments cannot be safely converted to "INET6 NOT NULL", + then mark the entire function nullability as NULL-able. + Otherwise, keep the generic nullability calculated by earlier stages: + - either by the most generic way in Item_func::fix_fields() + - or by Item_func_xxx::fix_length_and_dec() before the call of + Item_hybrid_func_fix_attributes() + IFNULL() is special. It does not need to test args[0]. + */ + uint first= dynamic_cast(attr) ? 1 : 0; + for (uint i= first; i < nitems; i++) + { + if (Inet6::fix_fields_maybe_null_on_conversion_to_inet6(items[i])) + { + attr->set_maybe_null(true); + break; + } + } + return false; + } + bool Item_func_min_max_fix_attributes(THD *thd, + Item_func_min_max *func, + Item **items, + uint nitems) const override + { + return Item_hybrid_func_fix_attributes(thd, func->func_name(), + func, func, items, nitems); + + } + bool Item_sum_hybrid_fix_length_and_dec(Item_sum_hybrid *func) const override + { + func->Type_std_attributes::operator=(Type_std_attributes_inet6()); + func->set_handler(this); + return false; + } + bool Item_sum_sum_fix_length_and_dec(Item_sum_sum *func) const override + { + return Item_func_or_sum_illegal_param(func); + } + bool Item_sum_avg_fix_length_and_dec(Item_sum_avg *func) const override + { + return Item_func_or_sum_illegal_param(func); + } + bool Item_sum_variance_fix_length_and_dec(Item_sum_variance *func) const override + { + return Item_func_or_sum_illegal_param(func); + } + + bool Item_val_native_with_conversion(THD *thd, Item *item, + Native *to) const override + { + if (item->type_handler() == this) + return item->val_native(thd, to); // No conversion needed + StringBufferInet6 buffer; + String *str= item->val_str(&buffer); + return str ? character_or_binary_string_to_native(thd, str, to) : true; + } + bool Item_val_native_with_conversion_result(THD *thd, Item *item, + Native *to) const override + { + if (item->type_handler() == this) + return item->val_native_result(thd, to); // No conversion needed + StringBufferInet6 buffer; + String *str= item->str_result(&buffer); + return str ? character_or_binary_string_to_native(thd, str, to) : true; + } + + bool Item_val_bool(Item *item) const override + { + NativeBufferInet6 tmp; + if (item->val_native(current_thd, &tmp)) + return false; + return !Inet6::only_zero_bytes(tmp.ptr(), tmp.length()); + } + void Item_get_date(THD *thd, Item *item, + Temporal::Warn *buff, MYSQL_TIME *ltime, + date_mode_t fuzzydate) const override + { + set_zero_time(ltime, MYSQL_TIMESTAMP_TIME); + } + + longlong Item_val_int_signed_typecast(Item *item) const override + { + DBUG_ASSERT(0); + return 0; + } + + longlong Item_val_int_unsigned_typecast(Item *item) const override + { + DBUG_ASSERT(0); + return 0; + } + + String *Item_func_hex_val_str_ascii(Item_func_hex *item, String *str) + const override + { + NativeBufferInet6 tmp; + if ((item->null_value= item->arguments()[0]->val_native(current_thd, &tmp))) + return NULL; + DBUG_ASSERT(tmp.length() == Inet6::binary_length()); + if (str->set_hex(tmp.ptr(), tmp.length())) + { + str->length(0); + str->set_charset(item->collation.collation); + } + return str; + } + + String *Item_func_hybrid_field_type_val_str(Item_func_hybrid_field_type *item, + String *str) const override + { + NativeBufferInet6 native; + if (item->val_native(current_thd, &native)) + { + DBUG_ASSERT(item->null_value); + return NULL; + } + DBUG_ASSERT(native.length() == Inet6::binary_length()); + Inet6_null tmp(native.ptr(), native.length()); + return tmp.is_null() || tmp.to_string(str) ? NULL : str; + } + double Item_func_hybrid_field_type_val_real(Item_func_hybrid_field_type *) + const override + { + return 0; + } + longlong Item_func_hybrid_field_type_val_int(Item_func_hybrid_field_type *) + const override + { + return 0; + } + my_decimal * + Item_func_hybrid_field_type_val_decimal(Item_func_hybrid_field_type *, + my_decimal *to) const override + { + my_decimal_set_zero(to); + return to; + } + void Item_func_hybrid_field_type_get_date(THD *, + Item_func_hybrid_field_type *, + Temporal::Warn *, + MYSQL_TIME *to, + date_mode_t fuzzydate) + const override + { + set_zero_time(to, MYSQL_TIMESTAMP_TIME); + } + // WHERE is Item_func_min_max_val_native??? + String *Item_func_min_max_val_str(Item_func_min_max *func, String *str) + const override + { + Inet6_null tmp(func); + return tmp.is_null() || tmp.to_string(str) ? NULL : str; + } + double Item_func_min_max_val_real(Item_func_min_max *) const override + { + return 0; + } + longlong Item_func_min_max_val_int(Item_func_min_max *) const override + { + return 0; + } + my_decimal *Item_func_min_max_val_decimal(Item_func_min_max *, + my_decimal *to) const override + { + my_decimal_set_zero(to); + return to; + } + bool Item_func_min_max_get_date(THD *thd, Item_func_min_max*, + MYSQL_TIME *to, date_mode_t fuzzydate) + const override + { + set_zero_time(to, MYSQL_TIMESTAMP_TIME); + return false; + } + + bool + Item_func_between_fix_length_and_dec(Item_func_between *func) const override + { + return false; + } + longlong Item_func_between_val_int(Item_func_between *func) const override + { + return func->val_int_cmp_native(); + } + + cmp_item *make_cmp_item(THD *thd, CHARSET_INFO *cs) const override; + + in_vector *make_in_vector(THD *thd, const Item_func_in *func, + uint nargs) const override; + + bool Item_func_in_fix_comparator_compatible_types(THD *thd, + Item_func_in *func) + const override + { + if (func->compatible_types_scalar_bisection_possible()) + { + return func->value_list_convert_const_to_int(thd) || + func->fix_for_scalar_comparison_using_bisection(thd); + } + return + func->fix_for_scalar_comparison_using_cmp_items(thd, + 1U << (uint) STRING_RESULT); + } + bool + Item_func_round_fix_length_and_dec(Item_func_round *func) const override + { + return Item_func_or_sum_illegal_param(func); + } + bool + Item_func_int_val_fix_length_and_dec(Item_func_int_val *func) const override + { + return Item_func_or_sum_illegal_param(func); + } + + bool Item_func_abs_fix_length_and_dec(Item_func_abs *func) const override + { + return Item_func_or_sum_illegal_param(func); + } + + bool Item_func_neg_fix_length_and_dec(Item_func_neg *func) const override + { + return Item_func_or_sum_illegal_param(func); + } + + bool + Item_func_signed_fix_length_and_dec(Item_func_signed *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_func_unsigned_fix_length_and_dec(Item_func_unsigned *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_double_typecast_fix_length_and_dec(Item_double_typecast *item) + const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_float_typecast_fix_length_and_dec(Item_float_typecast *item) + const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_decimal_typecast_fix_length_and_dec(Item_decimal_typecast *item) + const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_char_typecast_fix_length_and_dec(Item_char_typecast *item) + const override; + bool + Item_time_typecast_fix_length_and_dec(Item_time_typecast *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_date_typecast_fix_length_and_dec(Item_date_typecast *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_datetime_typecast_fix_length_and_dec(Item_datetime_typecast *item) + const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_func_plus_fix_length_and_dec(Item_func_plus *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_func_minus_fix_length_and_dec(Item_func_minus *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_func_mul_fix_length_and_dec(Item_func_mul *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_func_div_fix_length_and_dec(Item_func_div *item) const override + { + return Item_func_or_sum_illegal_param(item); + } + bool + Item_func_mod_fix_length_and_dec(Item_func_mod *item) const override + { + return Item_func_or_sum_illegal_param(item); + } +}; + + +extern MYSQL_PLUGIN_IMPORT Type_handler_inet6 type_handler_inet6; + + +#endif /* SQL_TYPE_INET_H */ -- cgit v1.2.3