maria-developers team mailing list archive

[Alexey Botchkov <holyfoot@xxxxxxxxxxx>]

Hello, Sergei!

New patch was made.
I think i addressed what you've noticed in last review.
https://github.com/MariaDB/server/commit/75ae330f877240e903690f282fad3ebdb02a0eeb

> CREATE TABLE t2 SELECT *
 >  FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10)
>                                 CHARSET utf8mb4 COLLATE utf8mb4_bin
>                                 PATH '$')) AS jt1;
> I am not sure if we need to support it (I saw the change_charset() call
in the
> patch). What's your opinion?

I decided we should. Not difficult to implement and some can need it.

> This is a surprise for me, too. I assumed join order dependencies created
by
> STRAIGHT_JOIN are visible in table dependency map...

I fixed it in the Table_function_json_table::setup so it doesn't allow
dependencies to the STRAIGHT_JOIN-ed table. But probably should
be fixed in more general way.


A.


On Wed, Nov 18, 2020 at 8:17 PM Sergey Petrunia <sergey@xxxxxxxxxxx> wrote:

> On Mon, Nov 16, 2020 at 01:53:39PM +0300, Sergey Petrunia wrote:
> > Input on the latest patch for MDEV-17399.
> ..
> > Please find the first few cases below.
>
> And a few more from the same source:
>
> === JSON_TABLE cannot depend on another one ? ===
>
> drop table t1;
> CREATE TABLE t1(id int, jd varchar(100));
>
> SELECT id, jt1.*, jt2.*
> FROM
>   t1,
>   JSON_TABLE(jd, '$' COLUMNS (data1 JSON PATH '$')) AS jt1,
>   JSON_TABLE(data1, '$[*]' COLUMNS (id2 INT PATH '$')) AS jt2;
>
> ERROR 1054 (42S22): Unknown column 'data1' in 'JSON_TABLE argument'
>
>
> === Dependency caused by STRAIGHT_JOIN is not visible? ===
> This one is interesting: it produces an error in MySQL but crashes MariaDB
> due
> to unability to pick a query plan.
>
> This is a surprise for me, too. I assumed join order dependencies created
> by
> STRAIGHT_JOIN are visible in table dependency map...
>
> drop table t1;
> CREATE TABLE t1(id INT, f1 JSON);
> INSERT INTO t1 VALUES
>  (1, '{\"1\": 1}'),
>  (2, '{\"1\": 2}'),
>  (3, '{\"1\": 3}'),
>  (4, '{\"1\": 4}'),
>  (5, '{\"1\": 5}'),
>  (6, '{\"1\": 6}');
> ANALYZE TABLE t1;
>
> SELECT * FROM t1 as jj1,
>   (SELECT tt2.*
>    FROM
>       t1 as tt2,
>       JSON_TABLE(tt3.f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl
>       STRAIGHT_JOIN
>       t1 AS tt3
>    ) dt
> ORDER BY 1,3 LIMIT 10;
>
> === COLLATE clause is not supported ===
>
> This fails with an error:
>
> CREATE TABLE t2 SELECT *
>   FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10)
>                                 CHARSET utf8mb4 COLLATE utf8mb4_bin
>                                 PATH '$')) AS jt1;
>
> I am not sure if we need to support it (I saw the change_charset() call in
> the
> patch). What's your opinion?
>
> === CHARSET is accepted but ignored ===
> select collation(x) from
>   JSON_TABLE(
>     '["abc"]',
>     '$[*]' COLUMNS (x VARCHAR(10) CHARSET latin1 PATH '$')
>   ) tbl;
> +-----------------+
> | collation(x)    |
> +-----------------+
> | utf8_general_ci |
> +-----------------+
>
> If we don't support columns having different charset, this should not be
> allowed.
>
> === Character set introducers are not supported ===
>
> They are supported in the first parameter, but not in the path or default
> clauses.
>
> SELECT * FROM
>   JSON_TABLE(JSON_OBJECT(),
>              _utf8mb4'$' COLUMNS (NESTED PATH _utf8mb4'$.x' COLUMNS
>                (y INT PATH _utf8mb4'$.y'
>                       DEFAULT _utf8mb4'1' ON EMPTY
>                       DEFAULT _utf8mb4'2' ON ERROR))) jt;
>
> BR
>  Sergei
> --
> Sergei Petrunia, Software Developer
> MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
>
>
>