MDEV-20795 CAST(inet6 AS BINARY) returns wrong result

4 files changed, 219 insertions, 5 deletions

diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6.result b/plugin/type_inet/mysql-test/type_inet/type_inet6.result
index 3e5905cecec..14a43acb342 100644
--- a/plugin/type_inet/mysql-test/type_inet/type_inet6.result
+++ b/plugin/type_inet/mysql-test/type_inet/type_inet6.result
@@ -720,6 +720,99 @@ 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
 #
@@ -1516,3 +1609,15 @@ 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;
diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6.test b/plugin/type_inet/mysql-test/type_inet/type_inet6.test
index 03b83214edc..99d0921ebfa 100644
--- a/plugin/type_inet/mysql-test/type_inet/type_inet6.test
+++ b/plugin/type_inet/mysql-test/type_inet/type_inet6.test
@@ -383,6 +383,61 @@ 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
@@ -1049,3 +1104,13 @@ 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;
diff --git a/plugin/type_inet/sql_type_inet.cc b/plugin/type_inet/sql_type_inet.cc
index 0c50cfe034c..97e433cb053 100644
--- a/plugin/type_inet/sql_type_inet.cc
+++ b/plugin/type_inet/sql_type_inet.cc
@@ -1216,6 +1216,54 @@ public:
 };
 
 
+class Item_char_typecast_func_handler_inet6_to_binary:
+                                       public Item_handled_func::Handler_str
+{
+public:
+  const Type_handler *return_type_handler() const override
+  {
+    return &type_handler_string;
+  }
+  const Type_handler *
+      type_handler_for_create_select(const Item_handled_func *item) const
+  {
+    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<const Item_char_typecast*>(item));
+    return static_cast<Item_char_typecast*>(item)->
+             val_str_binary_from_native(to);
+  }
+};
+
+
+static const 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,
diff --git a/plugin/type_inet/sql_type_inet.h b/plugin/type_inet/sql_type_inet.h
index cfc44f66af6..57e34f3c2d5 100644
--- a/plugin/type_inet/sql_type_inet.h
+++ b/plugin/type_inet/sql_type_inet.h
@@ -930,11 +930,7 @@ public:
   }
   bool
   Item_char_typecast_fix_length_and_dec(Item_char_typecast *item)
-                                        const override
-  {
-    item->fix_length_and_dec_str();
-    return false;
-  }
+                                        const override;
   bool
   Item_time_typecast_fix_length_and_dec(Item_time_typecast *item) const override
   {