[
https://issues.apache.org/jira/browse/CALCITE-4242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17199548#comment-17199548
]
James Starr edited comment on CALCITE-4242 at 9/21/20, 5:40 PM:
----------------------------------------------------------------
One nit for your example, what if the resulting filter should return x when it
is null? I think adding a simple boolean column handles some of the edge cases
but I think the join could be much more complicated.
I think the an important insight from you approach is that the filtered query
needs to be apart of the filter. So the algorithm goes:
Terms(open to suggestions):
Data Query - the query that will eventually be filtered
Data Filter - the filter with sub-queries
Filter Query - A query that will generate all combinations of columns used from
data query that will pass the filters
1. Create the base of the filter query from the data query.
2. Add rewrite sub query filters as LEFT JOINS to build up the filter query
while building up a filter statement. I believe the aforementioned hoisting
algorithm is still applicable here.
3. Use some mechanism to perform a LEFT JOIN from the data query to filter
query, this needs to handle the multiplicity
4. Rewrite the the sub-query filter in the data filter to use a field from the
filter query.
was (Author: jamesstarr):
One nit for your example, what if the resulting filter should return x when it
is null? I think adding a simple boolean column handles some of the edge cases
but I think the join could be much more complicated.
I think the an important insight from you approach is that the filtered query
needs to be apart of the filter. So the algorithm goes:
Terms(open to suggestions):
Data Query - the query that will eventually be filtered
Data Filter - the filter with sub-queries
Filter Query - A query that will generate all combinations of columns used from
data query that will pass the filters
1. Create the base of the filter query from the data query.
2. Add rewrite sub query filters as LEFT JOINS to build up the filter query.
3. Use some mechanism to perform a LEFT JOIN from the data query to filter
query, this needs to handle the multiplicity
4. Rewrite the the sub-query filter in the data filter to use a field from the
filter query.
> Wrong plan for nested NOT EXISTS subqueries
> -------------------------------------------
>
> Key: CALCITE-4242
> URL: https://issues.apache.org/jira/browse/CALCITE-4242
> Project: Calcite
> Issue Type: Bug
> Reporter: Martin Raszyk
> Priority: Major
>
> Suppose we initialize an empty database as follows.
>
> {code:java}
> CREATE TABLE P(x INTEGER);
> CREATE TABLE Q(y INTEGER);
> CREATE TABLE R(z INTEGER);
> INSERT INTO P VALUES (1);
> INSERT INTO Q VALUES (1);{code}
>
> The following query is supposed to yield an empty table as the result.
>
> {code:java}
> SELECT x FROM P
> WHERE NOT EXISTS (
> SELECT y FROM Q
> WHERE NOT EXISTS (
> SELECT z FROM R
> WHERE x = z
> )
> ){code}
>
> However, the query is parsed and converted to the following plan
> {code:java}
> LogicalProject(X=[$0])
> LogicalFilter(condition=[IS NULL($2)])
> LogicalJoin(condition=[=($0, $1)], joinType=[left])
> LogicalTableScan(table=[[Bug, P]])
> LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
> LogicalProject(Z=[$1], $f0=[true])
> LogicalFilter(condition=[IS NULL($2)])
> LogicalJoin(condition=[true], joinType=[left])
> LogicalTableScan(table=[[Bug, Q]])
> LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
> LogicalProject(Z=[$0], $f0=[true])
> LogicalTableScan(table=[[Bug, R]])
> {code}
> that corresponds to the following SQL query
> {code:java}
> SELECT P.X
> FROM Bug.P
> LEFT JOIN (SELECT t0.Z, MIN(TRUE) AS $f1
> FROM Bug.Q
> LEFT JOIN (SELECT Z, MIN(TRUE) AS $f1
> FROM Bug.R
> GROUP BY Z) AS t0 ON TRUE
> WHERE t0.$f1 IS NULL
> GROUP BY t0.Z) AS t3 ON P.X = t3.Z
> WHERE t3.$f1 IS NULL
> {code}
> which yields the (non-empty) table P as the result.
> Hence, the parsed and converted query is not equivalent to the input query.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
