On 07/07/2017 11:19, Paul Reeves wrote: > Given this simple SQL statement > > select e.emp_no, e.full_name > from employee e > where e.emp_no = coalesce(value1,value2 ) > > the plan varies depending on the following : > > 1. where e.EMP_NO = COALESCE(?, ? ) -- PLAN (E INDEX (RDB$PRIMARY7)) > > 2. where e.EMP_NO = COALESCE(2, ? ) -- PLAN (E INDEX (RDB$PRIMARY7)) > > 3. where e.EMP_NO = COALESCE(?, NULL ) -- PLAN (E INDEX (RDB$PRIMARY7)) > > 4. where e.EMP_NO = COALESCE(2, NULL ) -- PLAN (E INDEX (RDB$PRIMARY7)) > > 5. where e.EMP_NO = COALESCE(NULL, 2 ) -- PLAN (E INDEX (RDB$PRIMARY7)) > > 6. where e.EMP_NO = COALESCE(NULL, e.emp_no ) -- PLAN (E NATURAL) > > 7. where e.EMP_NO = COALESCE(2, e.emp_no ) -- PLAN (E NATURAL) > > 8. where e.EMP_NO = COALESCE(?, e.emp_no ) -- PLAN (E NATURAL) > >
... > And examples 2, and 4 seem to prove that the optimiser is capable of > evaluating the COALESCE during prepare and choosing an index. ... > However we have seen in > examples 1 and 3 that it is equally capable of choosing an index when > evaluating the coalesce even if the final evaluation may be null. > > And so here are the questions > > - is the plan produced in example 8 wrong or right ? > - the above examples seem to lack coherence so how much is COALESCE > evaluated during a prepare ? > - should the optimiser assume that the first parameter will > always resolve to a value > - how does the optimiser consider COALESCE when this construct is used > in a stored procedure? > > It does not evaluate COALESCE at prepare time. It's just a expression which may or may not contain fields. If it doesn't contain fields, it will be the same as a simple "?" for the plan calculation purposes. Adriano ------------------------------------------------------------------------------ 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
