[#MDEV-25140] JSON_TABLE: Success of query execution depends on the outcome of previous queries

This is a problem with possibly wide-spread consequences.

Here the first query is valid, and the second one works too as probably expected (at least I don't see why it shouldn't):

title=bb-10.6-mdev17399-psergey2 8b533cc1d5
MariaDB [test]> select * from json_table('[]', '$' COLUMNS(x FOR ORDINALITY)) a;
+------+
\| x \|
+------+
\| 1 \|
+------+
1 row in set (0.000 sec)

MariaDB [test]> select min(x) from json_table('[]', '$' COLUMNS(x FOR ORDINALITY)) a;
+--------+
\| min(x) \|
+--------+
\| 1 \|
+--------+
1 row in set (0.000 sec)

Now let's make the first query invalid (missing the alias), run it and repeat the 2nd query without any modifications:

MariaDB [test]> select * from json_table('[]', '$' COLUMNS(x FOR ORDINALITY));

ERROR 1064 (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 '' at line 1

MariaDB [test]> select min(x) from json_table('[]', '$' COLUMNS(x FOR ORDINALITY)) a;

ERROR 1111 (HY000): Invalid use of group function

So, now the second query fails, and it continues failing upon further executions:

MariaDB [test]> select min(x) from json_table('[]', '$' COLUMNS(x FOR ORDINALITY)) a;

ERROR 1111 (HY000): Invalid use of group function

MariaDB [test]>

MariaDB [test]> select min(x) from json_table('[]', '$' COLUMNS(x FOR ORDINALITY)) a;

ERROR 1111 (HY000): Invalid use of group function

But if we fix the first one, run it successfully, and repeat the 2nd one, it works again:

MariaDB [test]> select * from json_table('[]', '$' COLUMNS(x FOR ORDINALITY)) a;

+------+

| x    |

+------+

|    1 |

+------+

1 row in set (0.000 sec)

MariaDB [test]> select min(x) from json_table('[]', '$' COLUMNS(x FOR ORDINALITY)) a;

+--------+

| min(x) |

+--------+

|      1 |

+--------+

1 row in set (0.001 sec)

In MySQL, nothing of the kind happens, the 2nd query consistently succeeds.

[MDEV-25140] JSON_TABLE: Success of query execution depends on the outcome of previous queries Created: 2021-03-15 Updated: 2021-03-19 Resolved: 2021-03-16
Status:	Closed
Project:	MariaDB Server
Component/s:	JSON
Affects Version/s:	N/A
Fix Version/s:	10.6.0

[MDEV-25140] JSON_TABLE: Success of query execution depends on the outcome of previous queries Created: 2021-03-15 Updated: 2021-03-19 Resolved: 2021-03-16