From 02469bdead5753eccb5d70c98a158a07027f4eb2 Mon Sep 17 00:00:00 2001 From: Alexey Botchkov Date: Mon, 31 May 2021 13:48:09 +0400 Subject: MDEV-17399 JSON_TABLE. Accept JSON values for the JSON fields. --- mysql-test/suite/json/r/json_table.result | 14 ++++++++++ mysql-test/suite/json/r/json_table_mysql.result | 34 +++++++++++++------------ mysql-test/suite/json/t/json_table.test | 10 ++++++++ mysql-test/suite/json/t/json_table_mysql.test | 4 +-- sql/json_table.cc | 31 +++++++++++++++++++++- sql/json_table.h | 1 + 6 files changed, 74 insertions(+), 20 deletions(-) diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result index bb050eaee97..b8af0a1abf7 100644 --- a/mysql-test/suite/json/r/json_table.result +++ b/mysql-test/suite/json/r/json_table.result @@ -953,6 +953,20 @@ converted original Warnings: Warning 1264 Out of range value for column 'converted' at row 2 Warning 1366 Incorrect integer value: 'foo' for column ``.`(temporary)`.`converted` at row 3 +select * from +json_table('[{"color": "blue", "price": { "high": 10, "low": 5}}, + {"color": "white", "price": "pretty low"}, + {"color": "yellow", "price": 256.20}, + {"color": "red", "price": { "high": 20, "low": 8}}]', +'$[*]' columns(color varchar(100) path '$.color', +price json path '$.price' + ) +) as T; +color price +blue { "high": 10, "low": 5} +white "pretty low" +yellow 256.20 +red { "high": 20, "low": 8} # # End of 10.6 tests # diff --git a/mysql-test/suite/json/r/json_table_mysql.result b/mysql-test/suite/json/r/json_table_mysql.result index ec21f18523f..da7aa70be2b 100644 --- a/mysql-test/suite/json/r/json_table_mysql.result +++ b/mysql-test/suite/json/r/json_table_mysql.result @@ -40,7 +40,7 @@ id jpath jsn_path jexst 2 2 2 0 3 33 {"x":33} 1 4 0 0 0 -5 66 NULL 0 +5 66 [1,2] 0 select * from json_table( '[{"a":"3"},{"a":2},{"b":1},{"a":0.33},{"a":"asd"}]', @@ -55,11 +55,11 @@ jsn_path json path '$.a' default '{"x":33}' on empty, jexst int exists path '$.b') ) as tt; id jpath_i jpath_r jsn_path jexst -1 3 3 3 0 +1 3 3 "3" 0 2 2 2 2 0 3 33 33.3 {"x":33} 1 4 0 0.33 0.33 0 -5 0 0 asd 0 +5 0 0 "asd" 0 Warnings: Warning 1366 Incorrect integer value: 'asd' for column ``.`(temporary)`.`jpath_i` at row 5 Warning 1366 Incorrect double value: 'asd' for column ``.`(temporary)`.`jpath_r` at row 5 @@ -78,7 +78,7 @@ id jpath jsn_path jexst 2 2 2 0 3 33 {"x":33} 1 4 0 0 0 -5 66 NULL 0 +5 66 [1,2] 0 select * from json_table( '[{"a":"3"},{"a":2},{"b":1},{"a":0}]', @@ -88,7 +88,7 @@ json_path json path '$.a', jexst int exists path '$.b') ) as tt; id jpath json_path jexst -1 3 3 0 +1 3 "3" 0 2 2 2 0 3 NULL NULL 1 4 0 0 0 @@ -315,24 +315,24 @@ id1 jpath jexst id2 id3 jpath_3 id4 jpath_4 1 3 0 2 1 a1 NULL NULL 1 3 0 2 2 a2 NULL NULL 1 3 0 3 1 c NULL NULL -1 3 0 NULL NULL NULL 1 NULL -1 3 0 NULL NULL NULL 2 NULL -1 3 0 NULL NULL NULL 3 NULL +1 3 0 NULL NULL NULL 1 {"ll":["b1","b2","b3"]} +1 3 0 NULL NULL NULL 2 {"ll": ["a1","a2"]} +1 3 0 NULL NULL NULL 3 {"ll":["c"]} 2 2 0 1 1 1 NULL NULL 2 2 0 1 2 11 NULL NULL 2 2 0 1 3 111 NULL NULL 2 2 0 2 1 2 NULL NULL -2 2 0 NULL NULL NULL 1 NULL -2 2 0 NULL NULL NULL 2 NULL +2 2 0 NULL NULL NULL 1 {"ll":[1,11,111]} +2 2 0 NULL NULL NULL 2 {"ll":[2]} 3 NULL 1 1 1 zzz NULL NULL -3 NULL 1 NULL NULL NULL 1 NULL +3 NULL 1 NULL NULL NULL 1 {"ll":["zzz"]} 4 0 0 1 1 0.1 NULL NULL 4 0 0 1 2 0.01 NULL NULL 4 0 0 2 1 0.02 NULL NULL 4 0 0 2 2 0.002 NULL NULL 4 0 0 2 3 0.0002 NULL NULL -4 0 0 NULL NULL NULL 1 NULL -4 0 0 NULL NULL NULL 2 NULL +4 0 0 NULL NULL NULL 1 {"ll":[0.1,0.01]} +4 0 0 NULL NULL NULL 2 {"ll":[0.02,0.002,0.0002]} ord should be 1,1,1,2, which tells that first two values of 'l' are from the same object, and next two are from different objects SELECT * @@ -551,10 +551,12 @@ JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON ERROR)) 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 'NULL ON ERROR)) jt' at line 2 SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON EMPTY)) 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 '0 ON EMPTY)) jt' at line 2 +x +0 SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON ERROR)) 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 '0 ON ERROR)) jt' at line 2 +x +NULL SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (x DATE PATH '$.x' @@ -604,7 +606,7 @@ SELECT * FROM JSON_TABLE('{"a":"1"}', o FOR ORDINALITY)) AS jt WHERE o = 1; jpath o -1 1 +"1" 1 # # Bug#25427982: ASSERTION `DERIVED' FAILED IN SQL/TABLE.H # diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test index 40fcbcf69c1..9b35dd26a71 100644 --- a/mysql-test/suite/json/t/json_table.test +++ b/mysql-test/suite/json/t/json_table.test @@ -814,6 +814,16 @@ select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted tinyint path '$', original text path '$')) as jt order by original; +select * from + json_table('[{"color": "blue", "price": { "high": 10, "low": 5}}, + {"color": "white", "price": "pretty low"}, + {"color": "yellow", "price": 256.20}, + {"color": "red", "price": { "high": 20, "low": 8}}]', + '$[*]' columns(color varchar(100) path '$.color', + price json path '$.price' + ) + ) as T; + --echo # --echo # End of 10.6 tests --echo # diff --git a/mysql-test/suite/json/t/json_table_mysql.test b/mysql-test/suite/json/t/json_table_mysql.test index aaf123c6f7c..9f77ad964f3 100644 --- a/mysql-test/suite/json/t/json_table_mysql.test +++ b/mysql-test/suite/json/t/json_table_mysql.test @@ -453,13 +453,11 @@ SELECT * FROM SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON ERROR)) jt; -# The DEFAULT value must be a string on JSON format for now. ---error 1064 SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON EMPTY)) jt; ---error 1064 SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON ERROR)) jt; +# We don't accept dates in DEFAULT --error 1064 SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (x DATE diff --git a/sql/json_table.cc b/sql/json_table.cc index aebc52b0832..943b97476a6 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -19,6 +19,7 @@ #include "sql_priv.h" #include "sql_class.h" /* TMP_TABLE_PARAM */ #include "table.h" +#include "sql_type_json.h" #include "item_jsonfunc.h" #include "json_table.h" #include "sql_show.h" @@ -377,6 +378,25 @@ static void store_json_in_field(Field *f, const json_engine_t *je) } +static int store_json_in_json(Field *f, json_engine_t *je) +{ + const uchar *from= je->value_begin; + const uchar *to; + + if (json_value_scalar(je)) + to= je->value_end; + else + { + int error; + if ((error= json_skip_level(je))) + return error; + to= je->s.c_str; + } + f->store((const char *) from, (uint32) (to - from), je->s.cs); + return 0; +} + + bool Json_table_nested_path::check_error(const char *str) { if (m_engine.s.error) @@ -541,7 +561,12 @@ int ha_json_table::fill_column_values(THD *thd, uchar * buf, uchar *pos) } else { - if (!(error= !json_value_scalar(&je))) + if (jc->m_format_json) + { + if (!(error= store_json_in_json(*f, &je))) + error= er_handler.errors; + } + else if (!(error= !json_value_scalar(&je))) { store_json_in_field(*f, &je); error= er_handler.errors; @@ -868,6 +893,10 @@ int Json_table_column::set(THD *thd, enum_type ctype, const LEX_CSTRING &path) anctual content. Not sure though if we should. */ m_path.s.c_str= (const uchar *) path.str; + + if (ctype == PATH) + m_format_json= m_field->type_handler() == &type_handler_json_longtext; + return 0; } diff --git a/sql/json_table.h b/sql/json_table.h index 3560b4ca137..4e188ff4ba7 100644 --- a/sql/json_table.h +++ b/sql/json_table.h @@ -147,6 +147,7 @@ public: }; enum_type m_column_type; + bool m_format_json; json_path_t m_path; On_response m_on_error; On_response m_on_empty; -- cgit v1.2.1