MDEV-27743 Remove Lex::charset

This patch also fixes:

MDEV-27690 Crash on `CHARACTER SET csname COLLATE DEFAULT` in column definition
MDEV-27853 Wrong data type on column `COLLATE DEFAULT` and table `COLLATE some_non_default_collation`
MDEV-28067 Multiple conflicting column COLLATE clauses are not rejected
MDEV-28118 Wrong collation of `CAST(.. AS CHAR COLLATE DEFAULT)`
MDEV-28119 Wrong column collation on MODIFY + CONVERT

2 files changed, 142 insertions, 0 deletions

diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result
index 30c2c8bde50..a8c96dde5c8 100644
--- a/mysql-test/suite/json/r/json_table.result
+++ b/mysql-test/suite/json/r/json_table.result
@@ -987,3 +987,78 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
 #
 # End of 10.6 tests
 #
+#
+# Start of 10.9 tests
+#
+#
+#  MDEV-27743 Remove Lex::charset
+#
+SELECT collation(name)
+FROM json_table('[{"name":"Jeans"}]', '$[*]'
+  COLUMNS(
+name  VARCHAR(10) PATH '$.name'
+  )
+) AS jt;
+collation(name)
+utf8mb4_general_ci
+SELECT collation(name)
+FROM json_table('[{"name":"Jeans"}]', '$[*]'
+  COLUMNS(
+name  VARCHAR(10) COLLATE DEFAULT PATH '$.name'
+  )
+) AS jt;
+collation(name)
+utf8mb4_general_ci
+SELECT collation(name)
+FROM json_table('[{"name":"Jeans"}]', '$[*]'
+  COLUMNS(
+name  VARCHAR(10) BINARY PATH '$.name'
+  )
+) AS jt;
+collation(name)
+utf8mb4_bin
+CREATE VIEW v1 AS
+SELECT *
+FROM json_table('[{"name":"Jeans"}]', '$[*]'
+  COLUMNS(
+name  VARCHAR(10) 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":"Jeans"}]', '$[*]' COLUMNS (`name` varchar(10) PATH '$.name')) `jt`	latin1	latin1_swedish_ci
+SELECT collation(name) FROM v1;
+collation(name)
+utf8mb4_general_ci
+DROP VIEW v1;
+CREATE VIEW v1 AS
+SELECT *
+FROM json_table('[{"name":"Jeans"}]', '$[*]'
+  COLUMNS(
+name  VARCHAR(10) COLLATE DEFAULT 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":"Jeans"}]', '$[*]' COLUMNS (`name` varchar(10) PATH '$.name')) `jt`	latin1	latin1_swedish_ci
+SELECT collation(name) FROM v1;
+collation(name)
+utf8mb4_general_ci
+DROP VIEW v1;
+CREATE VIEW v1 AS
+SELECT *
+FROM json_table('[{"name":"Jeans"}]', '$[*]'
+  COLUMNS(
+name  VARCHAR(10) 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":"Jeans"}]', '$[*]' COLUMNS (`name` varchar(10) CHARSET utf8mb4 COLLATE utf8mb4_bin PATH '$.name')) `jt`	latin1	latin1_swedish_ci
+SELECT collation(name) FROM v1;
+collation(name)
+utf8mb4_bin
+DROP VIEW v1;
+#
+# 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 2867c5f89d0..ace059b7ec4 100644
--- a/mysql-test/suite/json/t/json_table.test
+++ b/mysql-test/suite/json/t/json_table.test
@@ -845,3 +845,70 @@ SELECT * FROM json_table('[{"name":"str"}]', '$[*]'
 --echo #
 --echo # End of 10.6 tests
 --echo #
+
+--echo #
+--echo # Start of 10.9 tests
+--echo #
+
+--echo #
+--echo #  MDEV-27743 Remove Lex::charset
+--echo #
+
+SELECT collation(name)
+FROM json_table('[{"name":"Jeans"}]', '$[*]'
+  COLUMNS(
+    name  VARCHAR(10) PATH '$.name'
+  )
+) AS jt;
+
+SELECT collation(name)
+FROM json_table('[{"name":"Jeans"}]', '$[*]'
+  COLUMNS(
+    name  VARCHAR(10) COLLATE DEFAULT PATH '$.name'
+  )
+) AS jt;
+
+SELECT collation(name)
+FROM json_table('[{"name":"Jeans"}]', '$[*]'
+  COLUMNS(
+    name  VARCHAR(10) BINARY PATH '$.name'
+  )
+) AS jt;
+
+
+CREATE VIEW v1 AS
+SELECT *
+FROM json_table('[{"name":"Jeans"}]', '$[*]'
+  COLUMNS(
+    name  VARCHAR(10) PATH '$.name'
+  )
+) AS jt;
+SHOW CREATE VIEW v1;
+SELECT collation(name) FROM v1;
+DROP VIEW v1;
+
+CREATE VIEW v1 AS
+SELECT *
+FROM json_table('[{"name":"Jeans"}]', '$[*]'
+  COLUMNS(
+    name  VARCHAR(10) COLLATE DEFAULT PATH '$.name'
+  )
+) AS jt;
+SHOW CREATE VIEW v1;
+SELECT collation(name) FROM v1;
+DROP VIEW v1;
+
+CREATE VIEW v1 AS
+SELECT *
+FROM json_table('[{"name":"Jeans"}]', '$[*]'
+  COLUMNS(
+    name  VARCHAR(10) BINARY PATH '$.name'
+  )
+) AS jt;
+SHOW CREATE VIEW v1;
+SELECT collation(name) FROM v1;
+DROP VIEW v1;
+
+--echo #
+--echo # End of 10.9 tests
+--echo #