Hi Arno,
here is some tests with ISQL:

>set stat

> select count(*) from tani;

counts(*)
---------
160
fetches: 335

> select id,adi from tani;

fetches: 335

> select id,adi,(select sum(id) from tani) from tani

fetches: 53935

if it was runs once fetches should be 335 for main + 335 for sub=670

160 * 335 = 53600 ~= 53935

it works for each row.
is this may be to improve readcommitted capability via latest data
for long running queries?

Regards.
Ali
FFM#208

--- In firebird-support@yahoogroups.com, "Arno Brinkman"
<fbsupport@...> wrote:

>
> Hi,
>
> >> > select CustomerId, Sales, (select sum(Sales) from Customers)

as Total

> >> > from Customers
> >> >
> >> > It works fine, but for each row, Firebird executes the subquery
> >> > "select sum(Sales) from Customers".
> >>
> >> Are you sure? At one time, I'm quite sure that the

compiler/optimizer

> >> were good enough to remove loop invariants.
> >
> > It possibly has been improved since, but 1.5.3 does not appear to
> > optimise it:
> >
> > PLAN (CUSTOMERS NATURAL)
> > PLAN (CUSTOMERS NATURAL)
>
> You can't see at the PLAN if those queries are invariant or not,

but if the sub-query is not depending on outer

> relations it should only be run once.
> Although this seems not to be the case for sub-queries in the

select-list (don't have an idea why at the moment).

> You can check the reads from statistics if the query is run only

once or for every record.

>
> Regards,
> Arno Brinkman
> ABVisie
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> General database developer support:
> http://www.databasedevelopmentforum.com
>
> Firebird open source database (based on IB-OE) with many SQL-99

features:

> http://www.firebirdsql.org
> http://www.firebirdsql.info
>
> Support list for Interbase and Firebird users:
> firebird-support@yahoogroups.com
>
> Nederlandse firebird nieuwsgroep:
> news://newsgroups.firebirdsql.info
>