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] Updated: (DERBY-4001) Sequence comparison with "ALL" does not yield correct results
Date Tue, 14 Apr 2009 13:36:15 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4001?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Knut Anders Hatlen updated DERBY-4001:
--------------------------------------

    Attachment: dontFlatten.diff

I had another look at the code, and I think I've found a way to
distinguish between safe and unsafe queries. See the attached patch
(which lacks comments and test cases, and is not ready for commit).

lang/subqueryFlattening.sql runs cleanly with the patch, so all the
queries that the test expected to be flattened are still flattened.

There are three essential changes in the patch:

1) ProjectRestrictNode.pullOptPredicates()

Don't pull any predicates if the from table is marked as a not exists
table. This way the flattening of queries like the ones below will
work, because the predicate 1<>1 is not pulled out and applied on the
outer table.

  SELECT * FROM T WHERE NOT EXISTS (SELECT * FROM T WHERE 1<>1)
  SELECT * FROM T WHERE X < ALL (SELECT X FROM T WHERE 1<>1)
  SELECT * FROM T WHERE X NOT IN (SELECT X FROM T WHERE 1<>1)

2) SubqueryNode.preprocess()

Don't allow not exists flattening unless all the predicates in the
subquery reference the base table of the inner query. When all the
predicates reference that table, none of them will be used in the
outer query, so they won't cause any trouble for the flattening. This
makes queries like the one below work:

  SELECT * FROM T T1 WHERE NOT EXISTS (SELECT * FROM T T2 WHERE T1.X > 100)

No flattening will happen in this case, though.

Although it may sound like (2) would prevent the example queries in
(1) from being flattened, that's not the case. This is because simple
predicates like 1<>1 are pushed down before SubqueryNode.preprocess()
gets to the flattening, so it doesn't see those predicates. The
flattening is still safe, since we have made sure that those
predicates won't be pulled out again.

3) SubqueryNode.preprocess()

If an ALL subquery or a NOT IN subquery is flattened, a new join
condition is created, for instance

   WHERE X < ALL (SELECT Y ...) results in the join condition X >= Y
and
   WHERE X NOT IN (SELECT Y ...) results in the join condition X = Y

The patch adds a check so that the flattening only happens if the
right side of the join condition references the base table of the
subquery. If it does, we know that the join condition cannot be used
to filter rows from the outer table, so it's safe to do the
flattening. This prevents queries like the ones below from being
flattened, and they now work as expected:

  SELECT * FROM T WHERE X < ALL (SELECT 100 FROM T)
  SELECT * FROM T T1 WHERE X = ALL (SELECT T1.X FROM T)
  SELECT * FROM T WHERE X NOT IN (SELECT 100 FROM T)
  SELECT * FROM T T1 WHERE X NOT IN (SELECT T1.X+100 FROM T)

> 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
>
>         Attachments: dontFlatten.diff
>
>
> 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