Issue Details (XML | Word | Printable)

Key: CORE-5850
Type: Improvement Improvement
Status: Open Open
Priority: Minor Minor
Assignee: Unassigned
Reporter: Pavel Zotov
Votes: 0
Watchers: 3

If you were logged in you would be able to see more operations.
Firebird Core

Optimizer must take in account result of previous evaluation of separate values in "NOT IN( ...)"-list

Created: 20/Jun/18 11:25 AM   Updated: 20/Jun/18 11:27 AM
Component/s: Engine
Affects Version/s: 4.0 Alpha 1, 2.5.8, 3.0.3
Fix Version/s: None

QA Status: No test

 Description  « Hide
Launch TRACE and then run this script:
recreate table t1(id int);
recreate table t2(id int);
recreate table t3(id int);
recreate table t4(id int);
recreate table t5(id int);

insert into t1(id) select null from rdb$types rows 11;
insert into t2(id) select null from rdb$types rows 22;
insert into t3(id) select null from rdb$types rows 33;
insert into t4(id) select null from rdb$types rows 44;
insert into t5(id) select null from rdb$types rows 55;

set list on;

select sum(id) from t1;
select sum(id) from t2 ;
select count(id) from t3;
select sum(id) from t4 ;
select sum(id) from t5 ;

set count on;

select 1
from rdb$database
where 0 not in (
      ( select sum(id) from t1 )
    , ( select sum(id) from t2 )
    , 1000 / (select count( id) from t3 ) ----------------------- [1 ]
    , ( select sum(id) from t4 )
    , ( select sum(id) from t5 )

Output will be:
SUM <null>
SUM <null>
SUM <null>
SUM <null>

Statement failed, SQLSTATE = 22012
arithmetic exception, numeric overflow, or string truncation
-Integer divide by zero. The code attempted to divide an integer value by an integer divisor of zero.
Records affected: 0

Consider line marked as "[ 1 ]".
Why this is evaluated ?
Does not optimizer need to stop any further evaluation after get NULL in "(select sum(id) from t1) " ?

Trace shows that
1) optimizer evaluates NOT-IN list from left to right;
2) THREE tables was scanned instead on one (T1):

0 records fetched
      0 ms, 79 fetch(es)

Table Natural
T1 11
T2 22
T3 33

This question was originally raised in discuss with dimitr, 14-JUL-2015. I remember that some optimizations related to this issue should be made in 4.0 only. But i decided to write here this because otherwise it can be forgotten.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.