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 ?