IN subquery predicate with entity aliases produces wrong SQL "too few columns in subquery"
Description
relates to
Activity
Christian Beikov March 17, 2022 at 9:43 AM
Well, according to the issue you reported, the query execution fails due to invalid SQL, so a simple test that produces a result is proof enough that it works.
Stefan Seidel March 17, 2022 at 9:38 AM
Sounds good. I am not sure how to test for the generated SQL - any hints how I can best do that?
Christian Beikov March 17, 2022 at 9:09 AM
Yes, this fix could be the cause for https://hibernate.atlassian.net/browse/HHH-14945. I’d appreciate if you could provide a PR with a test and a fix for this.
Stefan Seidel March 17, 2022 at 8:54 AM
Sorry, I meant getCurrentStatementType , not getCurrentTopLevelClauseType.
Stefan Seidel March 17, 2022 at 8:50 AM
Is it possible that the fix for this caused https://hibernate.atlassian.net/browse/HHH-14945 ? I’m looking at the code in IdentNode and it seems to me that parentheses in SELECT components should never occur according to any SQL dialect? So I’m tempted to say that adding an additional condition || getWalker().getCurrentTopLevelClauseType() == HqlSqlTokenTypes.SELECT would be a more general solution - or would that break other stuff?
Details
Details
Assignee
Reporter
Components
Fix versions
Priority
Major
To reproduce the SQL error, define an an entity with a composite id and use the following query:
from EntityWithCompositeId e where e in (select e2 from EntityWithCompositeId e2)The resulting SQL query currently looks like this:
select ... from tbl e where (e.id1, e.id2) in (select (e2.id1, e2.id2) from tbl e2)Note that the parenthesis in the subquery are wrong. The fix is to always skip the parenthesis wrapping in
org.hibernate.hql.internal.ast.tree.IdentNode#resolveAsAliaswhengetWalker().isInSelect()is true andgetWalker().isInCase()is false. In such a scenario it should never be necessary to use parenthesis.Note that H2 allowed this syntax up until 1.4.197 but rightfully throws an exception as of 1.4.198, just like other DBs.
Also see https://github.com/h2database/h2database/issues/2821