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.
Modified by: @pavel-zotov
description: 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
SUM
COUNT 0
SUM
SUM
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\):
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\.
=\>
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
SUM
COUNT 0
SUM
SUM
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\.
firebird-automations
commented
6 years ago
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.