db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4001) Sequence comparison with "ALL" does not yield correct results
Date Fri, 27 Mar 2009 14:20:50 GMT

    [ 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:
>         Environment: Windows
>            Reporter: Venkateswaran Iyer
>            Priority: Minor
>             Fix For:
> 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.

View raw message