select ... where ... <> ALL (select ... join ...) bug [CORE1554] #1971
Comments
|
Commented by: @dyemanov Sorry, but what's the point in entering these tickets if you know the issue is fixed in v2.0? The fixes won't be backported into the v1.5 series anyway. |
|
Commented by: Saulius Vabalas (svabalas) Added reference to other case |
Modified by: Saulius Vabalas (svabalas)description: 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(*) PLAN JOIN (T2 INDEX (RDB$INDEX_8),T3 INDEX (RDB$INDEX_8)) ============ #2. '<>all' does not work, join in sub-select is important (does not return anything) select count(*) PLAN JOIN (T2 INDEX (RDB$INDEX_8),T3 INDEX (RDB$INDEX_8)) ============ 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. => 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(*) PLAN JOIN (T2 INDEX (RDB$INDEX_8),T3 INDEX (RDB$INDEX_8)) ============ #2. '<>all' does not work, join in sub-select is important (does not return anything) select count(*) PLAN JOIN (T2 INDEX (RDB$INDEX_8),T3 INDEX (RDB$INDEX_8)) ============ 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. For failing "NOT IN" case see CORE1555. |
|
Commented by: Saulius Vabalas (svabalas) The point is that FB1.5x users should know about existing bugs and should not spend time confirming them between versions. Also - it gives more reasons upgrading to 2.0 rather than staying on old one. |
|
Commented by: @dyemanov Perhaps it's worth looking at CORE85, CORE115, CORE142, CORE925 and some others I don't remember at the moment. All of them are about a closely related set of bugs proving neither ALL nor NOT IN working before v2.0. I'm not sure it's very useful duplicating that info with new test cases, but I don't really mind either. I just have no idea whether I should close your tickets as "fixed in v2.0" or as "duplicated". |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 2.0.0 [ 10091 ] assignee: Dmitry Yemanov [ dimitr ] |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @pcisarWorkflow: jira [ 13362 ] => Firebird [ 14039 ] |
Submitted by: Saulius Vabalas (svabalas)
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)
============
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)
============
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. For failing "NOT IN" case see CORE1555.
The text was updated successfully, but these errors were encountered: