Bug #105835 JSON_TABLE() function is lateral by fact, but this is not mentioned
Submitted: 8 Dec 2021 13:23 Modified: 11 Dec 2021 2:39
Reporter: Владислав Сокол Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: Jon Stephens CPU Architecture:Any

[8 Dec 2021 13:23] Владислав Сокол
The Reference Manual https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html claims that "A derived table cannot normally refer to (depend on) columns of preceding tables in the same FROM clause. As of MySQL 8.0.14, a derived table may be defined as a lateral derived table to specify that such references are permitted."

But there exists a function JSON_TABLE() https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html#function_json-table which was introduced in MySQL 8.0.4. And this function is lateral by fact, because it allows the next usage:

FROM table_with_json_column As twjc
JOIN JSON_TABLE(twjc.json_column, ...) ...

This function acts as derived table, and at the same time it refers to the column of previously mentioned table. This completely matches the LATERAL term.

Maybe it would be correct to mention this in the article dedicated to this function?

Additionally I think that it would be correct to add/edit at least one sample code which demonstrates the query where the function uses the table's column value. Now all example codes uses JSON literals only.

This everything may make the article more clear.

How to repeat:
not needed
[8 Dec 2021 14:12] MySQL Verification Team
Hi Mr. Сокол,

Thank you for your documentation bug report.

We find that your report is fully justified.

Verified as reported.
[10 Dec 2021 21:59] Jon Stephens
Hi Vladislav,

Just noting that "Later Derived Tables" referenced above already states,

    ·Per the SQL standard, a table function has an implicit LATERAL, 
    so it behaves as in MySQL 8.0 versions prior to 8.0.14. However, 
    per the standard, the LATERAL word is not allowed before 
    JSON_TABLE(), even though it is implicit.    

But I think that can be improved.

In progress.
[11 Dec 2021 2:39] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.
[13 Dec 2021 12:50] MySQL Verification Team
Thank you, Jon ......