MDEV-20785 Converting INET6 to CHAR(39) produces garbage without a warning

2 files changed, 226 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 7d88cce3d81..b402e1cae2a 100644
--- a/plugin/type_inet/mysql-test/type_inet/type_inet6.result
+++ b/plugin/type_inet/mysql-test/type_inet/type_inet6.result
@@ -1266,6 +1266,117 @@ Warning	1292	Incorrect inet6 value: '::192.168.0.1'
 Warning	1292	Incorrect inet6 value: '::ffff:192.168.0.1'
 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;
+#
 # Limit clause parameter
 # TODO: this should fail.
 # The test for a valid data type should be moved
@@ -1274,3 +1385,16 @@ DROP TABLE t1;
 #
 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;
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 a28caa4d7d3..2abcb2a9fc5 100644
--- a/plugin/type_inet/mysql-test/type_inet/type_inet6.test
+++ b/plugin/type_inet/mysql-test/type_inet/type_inet6.test
@@ -813,6 +813,96 @@ 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 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 # Limit clause parameter
 --echo # TODO: this should fail.
@@ -833,3 +923,15 @@ EXECUTE IMMEDIATE 'SELECT 1 FROM DUAL LIMIT ?' USING CAST('::' AS INET6);
 ## - 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;