You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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):
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.
The text was updated successfully, but these errors were encountered:
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 ? Don't 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):
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.
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):
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.
Submitted by: @pavel-zotov
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.
The text was updated successfully, but these errors were encountered: