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
Operations

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;
commit;

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 )
);
quit;
===

Output will be:
===
SUM <null>
SUM <null>
COUNT 0
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
*******************************************
RDB$DATABASE 1
T1 11
T2 22
T3 33


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