[MDEV-25398] Inconsistent name resolution with subquery in HAVING clause Created: 2021-04-13  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Optimizer, Views
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: 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'



 Comments   
Comment by Sergei Petrunia [ 2021-04-13 ]

elenst, one can easily create a testcase that shows the same behavior and does not use JSON_TABLE:

create table t10 (a int);
 
create table t11 (b int);
 
create view v10 as select * from t10;
 
SELECT COUNT(*) FROM t10 HAVING (SELECT 1 from t11 where t10.a=t11.b);
 
ERROR 42S22: Unknown column 'test.t10.a' in 'where clause'
 
SELECT COUNT(*) FROM v10 HAVING (SELECT 1 from t11 where v10.a=t11.b);
 
COUNT(*)

Comment by Sergei Petrunia [ 2021-04-13 ]

Also, the second query doesn't honor the ONLY_FULL_GROUP_BY mode:

set sql_mode='only_full_group_by';
 
SELECT COUNT(*) FROM v10 HAVING (SELECT 1 from t11 where v10.a=t11.b);
 
COUNT(*)

Generated at Thu Feb 08 09:37:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.