Hi Alexey,

> On Sun, May 30, 2021 at 06:44:19AM +0400, Alexey Botchkov wrote:
> > Hi, Sergey!
> >
> > I meditated about it for some time. I remember i was thinking on that part
> > before and
> > did that so for some reason. Though either i was wrong or didn't finish
> > what i planned.
> > This time i'd say we should allow numeric constants there too.
> > Here's the patch i'd push to fix this:
> > https://github.com/MariaDB/server/commit/9c518e4cc9b0569cae2daa5a4024e209293eca45

In the next patch, please refer to MDEV-25822 in the commit comment.

select * from
json_table('{"a": "b"}',
           '$' columns(col1 varchar(32) path '$.fooo' default 123456 on empty)
             ) as T;
+--------+
| col1   |
+--------+
| 123456 |
+--------+

Ok.

"default 0.5" also works
"default -0.5" DOESN'T - it produces a parse error.
"default 8446744073709551615" - works, bigint is accepted
"default 18446744073709551615" - DOESN'T, bigint unsigned is not accepted

Other kinds of literals like "DATE '20201-01-01'" are not accepted either.


The cause of this is the grammar, which allows to accept only certain kinds of
literals:

> diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
> index 37cdfc20030ab..192a4879b751d 100644
> --- a/sql/sql_yacc.yy
> +++ b/sql/sql_yacc.yy
> @@ -1324,6 +1324,7 @@ End SQL_MODE_ORACLE_SPECIFIC */
>          TEXT_STRING
>          NCHAR_STRING
>          json_text_literal
> +        json_text_literal_or_num
> 
>  %type <lex_str_ptr>
>          opt_table_alias_clause
> @@ -11550,6 +11551,26 @@ json_text_literal:
>            }
>          ;
> 
> +json_text_literal_or_num:
> +          json_text_literal
> +        | NUM
> +          {
> +            Lex->json_table->m_text_literal_cs= NULL;
> +          }
> +        | LONG_NUM
> +          {
> +            Lex->json_table->m_text_literal_cs= NULL;
> +          }
> +        | DECIMAL_NUM
> +          {
> +            Lex->json_table->m_text_literal_cs= NULL;
> +          }
> +        | FLOAT_NUM
> +          {
> +            Lex->json_table->m_text_literal_cs= NULL;
> +          }
> +        ;
> +
>  join_table_list:
>            derived_table_list { MYSQL_YYABORT_UNLESS($$=$1); }
>          ;

When I see this, I wonder if it was possible to make use of a general 'literal'
rule instead? 
That rule differs from the current symbols: it produces Item expressions. Is
this a problem? (I don't think it should be).

The only concern about allowing more kinds of literals is NULL literals: JSON
TABLE has special provision for emitting NULL:

  NULL ON {EMPTY|ERROR}

which makes supporting

  DEFAULT NULL ON {EMPTY|ERROR}

redundant. I don't see any problem if we support this, though.

BR
 Sergei
--
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net