Arno Brinkman wrote:

>
>>However your quoting of the rules does beg the question about
>>what to do with actual derived columns (expressions)...

...

>>
>>(ie. the derivation of the value is actually performed twice,
>>which could be important in more complicated derivations)

As far as I can see, the standard is mute on that question - and the
question certainly is interesting if the expression is something that
changes when re-run. The committee that produced the 99 standard was
absolutely adamant that nothing could change during the execution of a
single statement. Their successors seem a tad looser on that subject
and on others. Absent Diane Brown and here magic decoder ring, I doubt
that we'll find solid guidance from the standards committee on when the
value represented by the alias is established.

For the sake of weirdness, consider a subselect on a separate table
in a read-committed transaction - each reference to the result could be
different...


My instinct is that

select x.a, gen_id (gen_1, 1) as b, x.c
from table1 x
where b < 30
order by b

should evaluate b once and bind the value to the alias rather than
reevaluating the expression on each reference. I've got low confidence
in that instinct.

Regards,


Ann