Hi *, (similar message I posted already in Firegird-general, but it seems, that this list is not so active)
As a example I have select like: SELECT col1, col2, (select sum(col3) from table2 where col1=a.col1 and col2=a.col2) as col3 FROM table1 a; In this case query PLAN is like: PLAN (table2 INDEX(IX_table2_col1)) <--once PLAN ... But when I use COALESCE: SELECT col1, col2, coalesce((select sum(col3) from table2 where col1=a.col1 and col2=a.col2), 0) as col3 FROM table1 a; then PLAN changes: PLAN (table2 INDEX(IX_table2_col1)) <--twice PLAN (table2 INDEX(IX_table2_col1)) <--twice PLAN ... I guess, that it is because COALESCE is internaly transformed into CASE WHEN <subselect> IS NULL then 0 else <subselect> end So in expression is subselect twice. My question is if <subselect> is really executed twice for every row in table1? or is there internal optimization where duplicate expressions are reduced? If subselect is executed twice then IMO it is not very optimal, isn't it ?
