Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.2, 10.3, 10.4, 10.5, 10.6
-
None
Description
Set to Minor because the queries are silly, probably both are incorrect and should be rejected.
CREATE TABLE t (a TEXT); |
CREATE VIEW v AS SELECT * FROM t; |
|
|
INSERT INTO t VALUES ('{}'),('[]'); |
|
|
SELECT COUNT(*) FROM v HAVING (SELECT o FROM JSON_TABLE(v.a, '$' COLUMNS(o FOR ORDINALITY)) AS jt); |
SELECT COUNT(*) FROM t HAVING (SELECT o FROM JSON_TABLE(t.a, '$' COLUMNS(o FOR ORDINALITY)) AS jt); |
|
|
# Cleanup
|
DROP VIEW v; |
DROP TABLE t; |
The difference between two SELECTs is that the first one uses the view, while the second one uses the table.
The first one works, the second one fails:
|
bb-10.6-mdev17399-hf 160bd1691 |
MariaDB [test]> SELECT COUNT(*) FROM v HAVING (SELECT o FROM JSON_TABLE(v.a, '$' COLUMNS(o FOR ORDINALITY)) AS jt); |
+----------+ |
| COUNT(*) | |
+----------+ |
| 2 |
|
+----------+ |
1 row in set (0.002 sec) |
|
|
MariaDB [test]> SELECT COUNT(*) FROM t HAVING (SELECT o FROM JSON_TABLE(t.a, '$' COLUMNS(o FOR ORDINALITY)) AS jt); |
ERROR 1054 (42S22): Unknown column 'test.t.a' in 'JSON_TABLE argument' |
On MySQL both fail, although with different errors:
|
MySQL 8.0.23 |
MySQL [test]> SELECT COUNT(*) FROM v HAVING (SELECT o FROM JSON_TABLE(v.a, '$' COLUMNS(o FOR ORDINALITY)) AS jt); |
ERROR 1210 (HY000): Incorrect arguments to JSON_TABLE |
|
|
MySQL [test]> SELECT COUNT(*) FROM t HAVING (SELECT o FROM JSON_TABLE(t.a, '$' COLUMNS(o FOR ORDINALITY)) AS jt); |
ERROR 1054 (42S22): Unknown column 'test.t.a' in 'a table function argument' |