10.9 specific fixes for MDEV-29446 Change SHOW CREATE TABLE to display default collations

Fixing the JSON_TABLE related code according to MDEV-29446.

2 files changed, 93 insertions, 0 deletions

diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result
index 271b4d14470..1bfcb4ca23a 100644
--- a/mysql-test/suite/json/r/json_table.result
+++ b/mysql-test/suite/json/r/json_table.result
@@ -1090,5 +1090,50 @@ SELECT * FROM JSON_TABLE('{"foo":{"bar":1},"qux":2}', '$' COLUMNS(c1 VARCHAR(8)
 c1	c2
 NULL	NULL
 #
+# MDEV-29446 Change SHOW CREATE TABLE to display default collations
+#
+CREATE VIEW v1 AS
+SELECT * FROM
+JSON_TABLE('[{"name":"Laptop"}]', '$[*]'
+COLUMNS
+(
+name VARCHAR(10) CHARACTER SET latin1 PATH '$.name')
+) AS jt;
+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 `jt`.`name` AS `name` from JSON_TABLE('[{"name":"Laptop"}]', '$[*]' COLUMNS (`name` varchar(10) CHARSET latin1 COLLATE latin1_swedish_ci PATH '$.name')) `jt`	latin1	latin1_swedish_ci
+DROP VIEW v1;
+CREATE VIEW v1 AS
+SELECT * FROM
+JSON_TABLE('[{"name":"Laptop"}]', '$[*]'
+COLUMNS
+(
+name VARCHAR(10) CHARACTER SET utf8mb3 PATH '$.name')
+) AS jt;
+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 `jt`.`name` AS `name` from JSON_TABLE('[{"name":"Laptop"}]', '$[*]' COLUMNS (`name` varchar(10) CHARSET utf8mb3 COLLATE utf8mb3_general_ci PATH '$.name')) `jt`	latin1	latin1_swedish_ci
+DROP VIEW v1;
+CREATE VIEW v1 AS
+SELECT * FROM
+JSON_TABLE('[{"name":"Laptop"}]', '$[*]'
+COLUMNS
+(
+name VARCHAR(10) CHARACTER SET BINARY PATH '$.name')
+) AS jt;
+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 `jt`.`name` AS `name` from JSON_TABLE('[{"name":"Laptop"}]', '$[*]' COLUMNS (`name` varbinary(10) PATH '$.name')) `jt`	latin1	latin1_swedish_ci
+DROP VIEW v1;
+CREATE VIEW v1 AS
+SELECT * FROM
+JSON_TABLE('[{"name":"Laptop"}]', '$[*]'
+COLUMNS
+(
+name ENUM('Laptop') CHARACTER SET BINARY PATH '$.name')
+) AS jt;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ENUM('Laptop') CHARACTER SET BINARY PATH '$.name')
+) AS jt' at line 6
+#
 # End of 10.9 tests
 #
diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test
index 5283e776656..18ea0b9f166 100644
--- a/mysql-test/suite/json/t/json_table.test
+++ b/mysql-test/suite/json/t/json_table.test
@@ -936,6 +936,54 @@ DROP VIEW v1;
 
 SELECT * FROM JSON_TABLE('{"foo":{"bar":1},"qux":2}', '$' COLUMNS(c1 VARCHAR(8) PATH '$[0]', c2 CHAR(8) PATH '$.*.x')) AS js;
 
+
+--echo #
+--echo # MDEV-29446 Change SHOW CREATE TABLE to display default collations
+--echo #
+
+CREATE VIEW v1 AS
+SELECT * FROM
+JSON_TABLE('[{"name":"Laptop"}]', '$[*]'
+COLUMNS
+(
+  name VARCHAR(10) CHARACTER SET latin1 PATH '$.name')
+) AS jt;
+SHOW CREATE VIEW v1;
+DROP VIEW v1;
+
+CREATE VIEW v1 AS
+SELECT * FROM
+JSON_TABLE('[{"name":"Laptop"}]', '$[*]'
+COLUMNS
+(
+  name VARCHAR(10) CHARACTER SET utf8mb3 PATH '$.name')
+) AS jt;
+SHOW CREATE VIEW v1;
+DROP VIEW v1;
+
+CREATE VIEW v1 AS
+SELECT * FROM
+JSON_TABLE('[{"name":"Laptop"}]', '$[*]'
+COLUMNS
+(
+  name VARCHAR(10) CHARACTER SET BINARY PATH '$.name')
+) AS jt;
+SHOW CREATE VIEW v1;
+DROP VIEW v1;
+
+# ENUM is not supported yet in JSON_TABLE.
+# But if it is eventually supported, the below
+# test should be modified to make sure that "CHARACTER SET BINARY"
+# is not followed by "COLLATE BINARY".
+--error ER_PARSE_ERROR
+CREATE VIEW v1 AS
+SELECT * FROM
+JSON_TABLE('[{"name":"Laptop"}]', '$[*]'
+COLUMNS
+(
+  name ENUM('Laptop') CHARACTER SET BINARY PATH '$.name')
+) AS jt;
+
 --echo #
 --echo # End of 10.9 tests
 --echo #