[Firebird-devel] [FB-Tracker] Created: (CORE-1554) select ... where ... <> ALL (select ... join ...) bug

[Saulius V. (JIRA) <tr...@fi...>]

select ... where ... <> ALL (select ... join ...) bug
-----------------------------------------------------

                 Key: CORE-1554
                 URL: http://tracker.firebirdsql.org/browse/CORE-1554
             Project: Firebird Core
          Issue Type: Bug
          Components: Engine
    Affects Versions: 1.5.4, 1.5.5
         Environment: FB 1.54 CS on Windows OS
            Reporter: Saulius Vabalas


select ... where ... <> ALL (select ... join ...) does not work correctly when primary table in sub-select uses index (see #2 below). Replacing '<>all' with 'not in' helps.

#1. Expected result.  'not in' works and uses indices (returns all system triggers):

select count(*)
from rdb$triggers t1
where t1.RDB$SYSTEM_FLAG=1 and
      t1.rdb$trigger_name not in (select t2.rdb$trigger_name
                                   from rdb$triggers t2
                                   join rdb$triggers t3 on t3.rdb$trigger_name=t2.rdb$trigger_name
                                   where t2.rdb$trigger_name = upper('xxx'));

PLAN JOIN (T2 INDEX (RDB$INDEX_8),T3 INDEX (RDB$INDEX_8))
PLAN (T1 NATURAL)

       COUNT
============
          34

#2. '<>all' does not work, join in sub-select is important (does not return anything) 

select count(*)
from rdb$triggers t1
where t1.RDB$SYSTEM_FLAG=1 and
      t1.rdb$trigger_name <>all (select t2.rdb$trigger_name
                                   from rdb$triggers t2
                                   join rdb$triggers t3 on t3.rdb$trigger_name=t2.rdb$trigger_name
                                   where t2.rdb$trigger_name='xxx');

PLAN JOIN (T2 INDEX (RDB$INDEX_8),T3 INDEX (RDB$INDEX_8))
PLAN (T1 NATURAL)

       COUNT
============
           0


This issue is already fixed in FB 2.0. Unfortunately for FB1.53, I have another case where NOT IN subselect with join returns incorrect results if index is involved, what makes this case dependant on the other one and each SQL containing NOT IN or <> ALL has to be tested for all possible variations to avoid wrong results. So there is no clean way of using just single operator, what makes problem really nasty. Will add "NOT IN" case and link both cases later.




-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira