[
https://issues.apache.org/jira/browse/DERBY-4001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12689909#action_12689909
]
Knut Anders Hatlen commented on DERBY-4001:
-------------------------------------------
I'm beginning to think that we should stop flattening NOT EXISTS (and ALL and
NOT IN queries that are rewritten to NOT EXISTS), at least in some of the
cases. In general, it it not safe to flatten a NOT EXISTS join because the join
predicate should have the opposite effect on the left side and the right side
of the join, and also because it should not in any case filter out rows on the
left side if the right side happens to be empty.
The only case where I believe that it is safe to flatten a NOT EXISTS join, is
when the join predicate does not have any effect on the left side. (Does not
have any effect on the left side does not mean that all predicates that don't
reference the table on the left side are OK, since a predicate that always
evaluates to false, like 1<>1, would still have an effect on the left side of
the join.) It might be possible to detect this condition in
SubqueryNode.preprocess() when we set the flattenableNotExists flag, which is
where we decide whether or not the query should be flattened.
It looks like flattening of NOT EXISTS, ALL and NOT IN was added in Beetle
5173, and there are some test cases in lang/subqueryFlattening.sql that check
that the queries actually are flattened, so I guess we should try to continue
flattening the queries that are safe to flatten.
> Sequence comparison with "ALL" does not yield correct results
> -------------------------------------------------------------
>
> Key: DERBY-4001
> URL: https://issues.apache.org/jira/browse/DERBY-4001
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.2.1
> Environment: Windows
> Reporter: Venkateswaran Iyer
> Priority: Minor
> Fix For: 10.4.2.0
>
>
> A query involving "< ALL" does not yield the right results for decimal
> datatype. "< ANY" works, though.
> To reproduce the issue:
> % create table t1(col1 decimal(10,5));
> % insert into t1 values (-21483.64800);
> % insert into t1 values (74837.00000);
> % select col1 from t1 where col1 < ALL (select 0.0 from t1);
> The above yields no results whereas it should return the first row.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
