Optimizer must take in account result of previous evaluation of separate values in "NOT IN( ...)"-list ------------------------------------------------------------------------------------------------------
Key: CORE-5850 URL: http://tracker.firebirdsql.org/browse/CORE-5850 Project: Firebird Core Issue Type: Improvement Components: Engine Affects Versions: 3.0.3, 2.5.8, 4.0 Alpha 1 Reporter: Pavel Zotov Priority: Minor 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 ? 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. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel