You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "TaiNing Wang (Jira)" <ji...@apache.org> on 2021/06/09 03:16:00 UTC
[jira] [Comment Edited] (CALCITE-4643) AssertionError for outerjoin
query
[ https://issues.apache.org/jira/browse/CALCITE-4643?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17359726#comment-17359726 ]
TaiNing Wang edited comment on CALCITE-4643 at 6/9/21, 3:15 AM:
----------------------------------------------------------------
Hi [~julianhyde],
Thank you for the comment. I am using calcite to optimize outerjoin queries in my research project. Is the error suggesting that calcite has problems if queries have correlating variables from the null-generating side of joins? Is this a known bug or something? Such queries are common in my project. Is there a way to rewrite the queries so that calcite can optimize them?
was (Author: taining):
Hi [~julianhyde],
Thank you for the comment. I am using calcite to optimize outerjoin queries in my research project. Is the error suggesting that calcite has problems if queries have correlating variables from the null-generating side of joins? Is this a known bug or something? Such queries are common in my project. Is there a way to rewrite the queries so that calcite can opimize them?
> AssertionError for outerjoin query
> ----------------------------------
>
> Key: CALCITE-4643
> URL: https://issues.apache.org/jira/browse/CALCITE-4643
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.26.0
> Environment: OS: Ubuntu 16.04
> Reporter: TaiNing Wang
> Priority: Major
>
> Hello,
>
> I ran into some error when trying to run the following query in calcite-1.26.0:
>
>
>
> {code:java}
> SELECT * FROM aka_name INNER JOIN name ON aka_name.person_id = name.id
> INNER JOIN cast_info ON aka_name.person_id = cast_info.person_id AND name.id = cast_info.person_id
> INNER JOIN title ON cast_info.movie_id = title.id
> LEFT JOIN (SELECT * FROM role_type WHERE role = 'writer') AS t1 ON cast_info.role_id = t1.id
> LEFT JOIN movie_companies ON cast_info.movie_id = movie_companies.movie_id AND title.id = movie_companies.movie_id
> WHERE NOT EXISTS (SELECT 1 FROM company_name WHERE country_code = '[us]' AND movie_companies.company_id = company_name.id)
> {code}
>
> Error:
>
> {code:java}
> java.lang.AssertionError
> at org.apache.calcite.sql2rel.SqlToRelConverter.getCorrelationUse(SqlToRelConverter.java:2754)
> at org.apache.calcite.sql2rel.SqlToRelConverter.createJoin(SqlToRelConverter.java:2636)
> at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.register(SqlToRelConverter.java:4526)
> at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.register(SqlToRelConverter.java:4451)
> at org.apache.calcite.sql2rel.SqlToRelConverter.substituteSubQuery(SqlToRelConverter.java:1253)
> at org.apache.calcite.sql2rel.SqlToRelConverter.replaceSubQueries(SqlToRelConverter.java:1063)
> at org.apache.calcite.sql2rel.SqlToRelConverter.convertWhere(SqlToRelConverter.java:1029)
> at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:666)
> at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:644)
> at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3438)
> at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:570)
> at org.apache.calcite.prepare.PlannerImpl.rel(PlannerImpl.java:247)
> ^[[0;1mat MyTest.query0(MyTest.java:44)
> {code}
>
>
> The parser config used:
>
> {code:java}
> private static final SqlParser.Config PARSER_CONFIG
> = SqlParser.configBuilder().setLex(Lex.MYSQL).build();
> {code}
>
>
> A simpler query that gives the same error:
>
> {code:java}
> SELECT * FROM cast_info LEFT JOIN movie_companies ON cast_info.movie_id = movie_companies.movie_id
> WHERE NOT EXISTS (SELECT 1 FROM company_name WHERE country_code = '[us]' AND movie_companies.company_id = company_name.id)
> {code}
>
>
> Any idea what the problem is? Thanks a lot!
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)