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 Tue, 24 Mar 2009 14:59:50 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12688706#action_12688706
] 

Knut Anders Hatlen commented on DERBY-4001:
-------------------------------------------

The query "SELECT x FROM t WHERE x < ALL (SELECT 'b' FROM t)" will be
rewritten internally to a not exists join between "SELECT x FROM t t1"
(left side) and "SELECT * FROM t t2" (right side) with t1.x >= 'b' as
the join condition. All is correct so far.

But then at some point (not sure where), the optimizer decides that
the join condition should be pushed down into the scan on the left
side of the join. However, there are a some problems with pushing
predicates into the left side of a not exists join:

  - the condition would probably have to be negated, otherwise you'll
    end up with just the uninteresting rows from the left side (those
    rows that have a match in the right result set and should
    therefore be filtered out of the join result).

  - even if the join condition is negated negated before it is pushed,
    I think you'll still need to keep the original join condition and
    apply it on the right side of the join (or perhaps change the not
    exists join to an exists join). If we remove the original join
    condition, all the rows returned from the left side will have a
    match in the right side result, and they will be removed from the
    result when they should have been kept

  - if the right side of the join happens to be empty, all the rows
    from the left side should be returned, so in that case the pushed
    predicate shouldn't be used to restrict the left side

The condition is not negated before it is pushed, though, so the left
side of the join will give you all the rows you are not interested
in. The result of the join will be empty if the right side is not
empty, and it might miss some rows if the right side is empty.

Examples:

CREATE TABLE T1 (X INT NOT NULL);
INSERT INTO T1 VALUES (1),(2),(3);

These queries are expected to return 3 rows, but they return 0 rows:

SELECT * FROM T1 WHERE X < ALL (SELECT 100 FROM T1);
SELECT * FROM T1 WHERE NOT X > ANY (SELECT 100 FROM T1);

(They reason why they return 0 rows is that the join condition
(X>=100) is pushed to the left side of the not exists join, so that no
rows are returned from the left side.)

This query is expected to return 3 rows, but it returns 1 row:

SELECT * FROM T1 WHERE X < ALL (SELECT 3 FROM T1 WHERE X = 100);

(Similarly here, the join condition (X>=3) is pushed to the left side
without being negated. This condition happens to match one row, and it
is returned because the right side of the join is empty. Since the ALL
subquery is empty, all three rows should have been returned.)

> 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.


Mime
View raw message