Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
N/A
-
None
Description
This query returns an error as it is supposed to, because of ERROR ON EMPTY clause for column a:
MariaDB [test]> SELECT * FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt; |
ERROR 4175 (HY000): Field 'a' can't be set for JSON_TABLE 'jt'. |
However, if don't select the column a, it works:
MariaDB [test]> SELECT o FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt; |
+------+ |
| o |
|
+------+ |
| 1 |
|
+------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> SELECT COUNT(*) FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt; |
+----------+ |
| COUNT(*) | |
+----------+ |
| 1 |
|
+----------+ |
1 row in set (0.001 sec) |
Same applies for ERROR ON ERROR clauses.
It is not quite clear from the standard what the expected behavior is. It only says that the clauses have the same semantics as for JSON_VALUE, but for JSON_VALUE the situation when the value is not selected is not applicable. Still, from the common sense alone, it just doesn't look right:
|
bb-10.6-mdev17399-hf 160bd1691 |
MariaDB [test]> SELECT * FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt; |
ERROR 4175 (HY000): Field 'a' can't be set for JSON_TABLE 'jt'. |
|
|
MariaDB [test]> SELECT COUNT(*) FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt; |
+----------+ |
| COUNT(*) | |
+----------+ |
| 1 |
|
+----------+ |
1 row in set (0.001 sec) |
MySQL 8.0.23 returns an error for all queries above:
|
MySQL 8.0.23 |
MySQL [(none)]> SELECT * FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt; |
ERROR 3665 (22035): Missing value for JSON_TABLE column 'a' |
MySQL [(none)]> SELECT o FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt; |
ERROR 3665 (22035): Missing value for JSON_TABLE column 'a' |
MySQL [(none)]> SELECT COUNT(*) FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt; |
ERROR 3665 (22035): Missing value for JSON_TABLE column 'a' |
Attachments
Issue Links
- relates to
-
MDEV-17399 Add support for JSON_TABLE
-
- Closed
-
Activity
Updated patch by Alexey: https://github.com/MariaDB/server/commit/e1dfe60e8024e2bfa07e735b17cff69cec0b5be7
Review:
Is there anything that prevents from just unpacking into table->record[0]?
(If you hit a DBUG_ASSERT with this, you can use dbug_tmp_use_all_columns)
I think the code would be simpler if it just unpacks all columns into
table->record[0].
The patch should also add a comment that ha_json_table::fill_column_values()
unpacks all columns in order to hit possible errors.
fixing patch https://github.com/MariaDB/server/commit/cea5e01ee5feffbcf2a7446241a69cef58ee8e9f