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

2 files changed, 170 insertions, 0 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;