MDEV-25822 JSON_TABLE: default values should allow non-string literals.bb-10.6-mdev17399-hf

Default values of other types handled.

7 files changed, 98 insertions, 30 deletions

diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result
index 27a3c7022fb..e521aab8e10 100644
--- a/mysql-test/suite/json/r/json_table.result
+++ b/mysql-test/suite/json/r/json_table.result
@@ -57,14 +57,14 @@ Jeans	{"color": "blue", "price": 50}	blue
 select * from t1 right join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1;
 ERROR 42S22: Unknown column 't1.item_props' in 'JSON_TABLE argument'
 DROP TABLE t1;
-select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
+select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default 101 on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 a	b
 101	11
 101	111
 2	22
 2	222
 3	NULL
-select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
+select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default 202 on error, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 a	b
 NULL	11
 NULL	111
@@ -85,6 +85,48 @@ a	b
 2	22
 2	222
 3	NULL
+select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 0.5 on empty)) as T;
+col1
+0.5
+select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 5 on empty)) as T;
+col1
+5
+select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 'asdf' on empty)) as T;
+col1
+asdf
+select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default -0.5 on empty)) as T;
+col1
+-0.5
+select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 18446744073709551615 on empty)) as T;
+col1
+18446744073709551615
+select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default DATE '2021-01-01' on empty)) as T;
+col1
+2021-01-01
+create view v as select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 5 on empty)) as T;
+select * from v;
+col1
+5
+show create view v;
+View	Create View	character_set_client	collation_connection
+v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `T`.`col1` AS `col1` from JSON_TABLE('{"a": "b"}', '$' COLUMNS (`col1` varchar(32) PATH '$.fooo' DEFAULT 5 ON EMPTY)) `T`	latin1	latin1_swedish_ci
+drop view v;
+create view v as select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 18446744073709551615 on empty)) as T;
+select * from v;
+col1
+18446744073709551615
+show create view v;
+View	Create View	character_set_client	collation_connection
+v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `T`.`col1` AS `col1` from JSON_TABLE('{"a": "b"}', '$' COLUMNS (`col1` varchar(32) PATH '$.fooo' DEFAULT 18446744073709551615 ON EMPTY)) `T`	latin1	latin1_swedish_ci
+drop view v;
+create view v as select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 'asdf' on empty)) as T;
+select * from v;
+col1
+asdf
+show create view v;
+View	Create View	character_set_client	collation_connection
+v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `T`.`col1` AS `col1` from JSON_TABLE('{"a": "b"}', '$' COLUMNS (`col1` varchar(32) PATH '$.fooo' DEFAULT 'asdf' ON EMPTY)) `T`	latin1	latin1_swedish_ci
+drop view v;
 select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3} xx YY]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 ERROR HY000: Syntax error in JSON text in argument 1 to function 'JSON_TABLE' at position 65
 select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' error on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
diff --git a/mysql-test/suite/json/r/json_table_mysql.result b/mysql-test/suite/json/r/json_table_mysql.result
index 656dcab1643..ad8834a363b 100644
--- a/mysql-test/suite/json/r/json_table_mysql.result
+++ b/mysql-test/suite/json/r/json_table_mysql.result
@@ -545,26 +545,32 @@ Warning	1366	Incorrect double value: 'asdf' for column ``.`(temporary)`.`f` at r
 Warning	1366	Incorrect decimal value: 'asdf' for column ``.`(temporary)`.`d` at row 1
 SELECT * FROM
 JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL 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 'NULL ON EMPTY)) jt' at line 2
+x
+NULL
 SELECT * FROM
 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
+x
+NULL
 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'
                                   DEFAULT DATE'2020-01-01' 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 'DATE'2020-01-01' ON EMPTY)) jt' at line 4
+x
+2020-01-01
 SELECT * FROM
 JSON_TABLE('{}', '$' COLUMNS (x DATE
 PATH '$.x'
                                   DEFAULT DATE'2020-01-01' 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 'DATE'2020-01-01' ON ERROR)) jt' at line 4
+x
+NULL
 #
 # Bug#25413069: SIG11 IN CHECK_COLUMN_GRANT_IN_TABLE_REF
 #
diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test
index 2e2274a542b..dd11f7604c7 100644
--- a/mysql-test/suite/json/t/json_table.test
+++ b/mysql-test/suite/json/t/json_table.test
@@ -30,14 +30,34 @@ select * from t1 right join json_table(t1.item_props,'$' columns( color varchar(
 
 DROP TABLE t1;
 
-select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
+select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default 101 on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 
-select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
+select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default 202 on error, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 
 select * from JSON_TABLE( '[ {"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 
 select * from JSON_TABLE( '[ {"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 
+select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 0.5 on empty)) as T;
+select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 5 on empty)) as T;
+select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 'asdf' on empty)) as T;
+select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default -0.5 on empty)) as T;
+select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 18446744073709551615 on empty)) as T;
+select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default DATE '2021-01-01' on empty)) as T;
+
+create view v as select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 5 on empty)) as T;
+select * from v;
+show create view v;
+drop view v;
+create view v as select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 18446744073709551615 on empty)) as T;
+select * from v;
+show create view v;
+drop view v;
+create view v as select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 'asdf' on empty)) as T;
+select * from v;
+show create view v;
+drop view v;
+
 --error ER_JSON_SYNTAX
 select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3} xx YY]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 
diff --git a/mysql-test/suite/json/t/json_table_mysql.test b/mysql-test/suite/json/t/json_table_mysql.test
index aaf123c6f7c..8595c0e15e8 100644
--- a/mysql-test/suite/json/t/json_table_mysql.test
+++ b/mysql-test/suite/json/t/json_table_mysql.test
@@ -445,27 +445,19 @@ SELECT * FROM JSON_TABLE('"asdf"', '$' COLUMNS(
   f FLOAT PATH '$',
   d DECIMAL PATH '$')) AS jt;
 
-# DEFAULT NULL is not accepted syntax.
---error ER_PARSE_ERROR
 SELECT * FROM
   JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON EMPTY)) jt;
---error ER_PARSE_ERROR
 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;
---error 1064
 SELECT * FROM
   JSON_TABLE('{}', '$' COLUMNS (x DATE
                                   PATH '$.x'
                                   DEFAULT DATE'2020-01-01' ON EMPTY)) jt;
---error 1064
 SELECT * FROM
   JSON_TABLE('{}', '$' COLUMNS (x DATE
                                   PATH '$.x'
diff --git a/sql/json_table.cc b/sql/json_table.cc
index dc3a93da32d..235f9412dda 100644
--- a/sql/json_table.cc
+++ b/sql/json_table.cc
@@ -1008,8 +1008,7 @@ int Json_table_column::On_response::respond(Json_table_column *jc, Field *f,
       return 1;
     case Json_table_column::RESPONSE_DEFAULT:
       f->set_notnull();
-      f->store(m_default.str,
-          m_default.length, jc->m_defaults_cs);
+      m_default->save_in_field(f, TRUE);
       break;
   }
   return 0;
@@ -1019,7 +1018,11 @@ int Json_table_column::On_response::respond(Json_table_column *jc, Field *f,
 int Json_table_column::On_response::print(const char *name, String *str) const
 {
   LEX_CSTRING resp;
-  const LEX_CSTRING *ds= NULL;
+
+  char valbuf[512];
+  String val(valbuf, sizeof(valbuf), str->charset());
+  String *ds= NULL;
+
   if (m_response == Json_table_column::RESPONSE_NOT_SPECIFIED)
     return 0;
 
@@ -1034,7 +1037,7 @@ int Json_table_column::On_response::print(const char *name, String *str) const
     case Json_table_column::RESPONSE_DEFAULT:
     {
       lex_string_set3(&resp, STRING_WITH_LEN("DEFAULT"));
-      ds= &m_default;
+      ds= m_default->val_str(&val);
       break;
     }
     default:
@@ -1043,9 +1046,14 @@ int Json_table_column::On_response::print(const char *name, String *str) const
   }
 
   return (str->append(' ') || str->append(resp)  ||
-          (ds && (str->append(STRING_WITH_LEN(" '")) ||
-                  str->append_for_single_quote(ds->str, ds->length) ||
-                  str->append('\''))) ||
+          (ds &&
+            (str->append(' ') ||
+             (m_default->result_type()==STRING_RESULT && str->append('\''))||
+
+             str->append_for_single_quote(ds) ||
+
+             (m_default->result_type()==STRING_RESULT && str->append('\''))))||
+
           str->append(STRING_WITH_LEN(" ON ")) ||
           str->append(name, strlen(name)));
 }
diff --git a/sql/json_table.h b/sql/json_table.h
index 4e188ff4ba7..42f5a0c0c71 100644
--- a/sql/json_table.h
+++ b/sql/json_table.h
@@ -140,7 +140,7 @@ public:
   {
   public:
     Json_table_column::enum_on_response m_response;
-    LEX_CSTRING m_default;
+    Item *m_default;
     int respond(Json_table_column *jc, Field *f, uint error_num);
     int print(const char *name, String *str) const;
     bool specified() const { return m_response != RESPONSE_NOT_SPECIFIED; }
@@ -154,7 +154,6 @@ public:
   Create_field *m_field;
   Json_table_nested_path *m_nest;
   CHARSET_INFO *m_explicit_cs;
-  CHARSET_INFO *m_defaults_cs;
 
   void set(enum_type ctype)
   {
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index dc2eb4425f0..c47d0ff2bfc 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1500,6 +1500,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
         simple_target_specification
         condition_number
         opt_versioning_interval_start
+        json_default_literal
 
 %type <item_param> param_marker
 
@@ -11659,6 +11660,8 @@ json_opt_on_empty_or_error:
         | json_on_empty_response json_on_error_response
         ;
 
+json_default_literal: literal | signed_literal;
+
 json_on_response:
           ERROR_SYM
           {
@@ -11668,12 +11671,10 @@ json_on_response:
           {
             $$.m_response= Json_table_column::RESPONSE_NULL;
           }
-        | DEFAULT json_text_literal
+        | DEFAULT json_default_literal
           {
             $$.m_response= Json_table_column::RESPONSE_DEFAULT;
             $$.m_default= $2;
-            Lex->json_table->m_cur_json_table_column->m_defaults_cs=
-                                    thd->variables.collation_connection;
           }
         ;