--- In firebird-support@yahoogroups.com, Mark Rotteveel <mark@...> wrote:
>
> On 7-10-2011 11:12, tomc7777777 wrote:
> >> There's no way subselects can be materialized, at least doing ..
> >
> > My use of the term 'materialized' may have been misunderstood, it was meant
> > to relate to whether/how the sub-select row is retrieved and compared when
> > * vs 1 are used.
> >
> > I've a copy of Celko's 'SQL for Smarties' and he outlines the theoretical
> > way this happens but I think says it would never happen this way in reality
> > and is implementation-specific. If someone with intimate knowledge of the
> > FB source (and ideally a humble disposition) could show a briefly summary
> > of the actual steps in resolving the SQL above then that would be much
> > appreciated.
>
> You are reversing the argument. First the row needs to be found based on
> the WHERE clause (using indices and/or natural scan of all the rows),
> and then the columns specified in the select are returned (be it a
> specific list or *). Using the reverse would be absurd, because then the
> selection of rows would depend on the columns you want to be returned
> from a row.
> --
> Mark Rotteveel
>
You are misunderstanding me. For SELECTs this was understood.
However, for UPDATES + similar statements, the scanning of the main table in
relation to any sub-selects was not (i.e how the UPDATE was actually carried
out) plus whether all columns were really returned or not.
e.g.
UPDATE TableA TA
SET TA.USED = 'T'
WHERE EXISTS(SELECT * FROM TableB TB WHERE TA.LINE = TB.LINE)
1. using a cursor
for each row in tablea ta
(using index or not) locate a match in tableb for TA.LINE = TB.LINE
do an update in place
2. Selects data as a completely seperate list using normal SELECT
update list as a single statement
Tom
