diff options
-rw-r--r-- | mysql-test/suite/json/r/json_table.result | 45 | ||||
-rw-r--r-- | mysql-test/suite/json/t/json_table.test | 48 | ||||
-rw-r--r-- | sql/json_table.cc | 2 |
3 files changed, 94 insertions, 1 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 # diff --git a/sql/json_table.cc b/sql/json_table.cc index e51b71d5650..5f9ba8d3351 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -984,7 +984,7 @@ int Json_table_column::print(THD *thd, Field **f, String *str) ((*f)->has_charset() && m_explicit_cs && (str->append(STRING_WITH_LEN(" CHARSET ")) || str->append(&m_explicit_cs->cs_name) || - (!(m_explicit_cs->state & MY_CS_PRIMARY) && + (Charset(m_explicit_cs).can_have_collate_clause() && (str->append(STRING_WITH_LEN(" COLLATE ")) || str->append(&m_explicit_cs->coll_name))))) || str->append(m_column_type == PATH ? &path : &exists_path) || |