[9.1.8]: BUG: Strange error condition in query window for JSON_TABLE.

[tfrancois]

For some strange reason, the following valid and correct JSON_TABLE command generates an error when executing the following statement in a new query window or stored procedure.

Code: Select all

SET @json = CONVERT('[1095, 1106, 1193, 1288, 1357, 1385, 1390]', JSON);
SELECT ids.* FROM JSON_TABLE(@json, '$[*]' COLUMNS (id INT UNSIGNED PATH '$')) `ids`;

The command returns correct results in the rows section below, but still generates a SQL error as shown:


The exact SQL error text that occurs:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`json_table` WHERE non_unique = 0' at line 1

Please fix this error - it took me forever to realize there was no issue with the syntax and it is a weird error that only occurs in dbForge. Thank you.

[dzhanhira]

Could you please clarify the version of server MYSQL?

[tfrancois]

The server version is Amazon Aurora v3 MySQL - which is wire equivalent to MySQL 8.0.23.

[tfrancois]

Any update on this issue - it it still a problem.

[dzhanhira]

Could you please confirm that the issue still persists on the last released version?

[tfrancois]

Hello,

Yes I can absolutely confirm that the issue persists even with current version 9.1.21 of dbForge.

Here is an INTERESTING tidbit - the error goes away when I alter the SELECT statement as follows:

Code: Select all

SELECT GROUP_CONCAT(id) FROM JSON_TABLE(@json, '$[*]' COLUMNS (id INT UNSIGNED PATH '$')) `ids` GROUP BY id;

This forces the query to return multiple rows by using the GROUP_CONCAT function combined with GROUP BY clause - effectively returning the same multiple rows as the original query. But this is a workaround - the error should not be present because the original query is valid.

Thank you.