ZeldorBlat wrote:
> On May 15, 6:57 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>> <snip>
>> Sorry for the delay in answering this one. My source is the manual.
>> On the pagehttp://dev.mysql.com/doc/refman/5.0/en/select.html
>> Where it says:
>>
>> "Columns selected for output can be referred to in ORDER BY and GROUP
>> BY clauses using column names, column aliases, or column positions."
>>
>> And of course it defines an Order By statement as:
>>
>> ORDER BY {col_name | expr | position}
>>
>> It does not say that columns selected for output can be referred to by
>> using an expression that happens to match the expression that created
>> the data for that column. Thus, unless you use one of the methods to
>> refer to the output column, you are left with an expression that will
>> have to be evaluated.
>>
>
> Ok, but nowhere does that say that the expression will or will not be
> internally evaluated a second time. If the database is smart enough
> to recognize when you have an expression in the group by clause that
> is not in the select list (in which case it complains), I would expect
> that it is smart enough to match an expression in the select list to
> an expression in the order by clause.
>
>
It's barely worth discussing. As focused as database companies are on
performance I doubt any would re-evaluate any expression that lexically
matches another either before or after compilation.

Referring to column aliases is a syntactic feature, not a performance
feature. It's a good thing that MySQL has column aliases. I remember
that being one of my favorite features of Teradata's SQL.

Regardless, the answer to the question is unlikely to be found in the
manuals. To know for sure the answer would either have to come from a
MySQL engineer or someone else familiar with MySQL's parser, compiler,
evaluator, or looking through the code.

However, as confident as Mr. Lautman is of the "facts," there are none
supporting the /assumption/ the ORDER BY clause' re-evaluates duplicate
expressions. Though I have no "facts" supporting my assumptions, I'm
equally confident that considering SQL databases maturity, the amount of
engineering given them, the papers published on optimization strategies,
the competition for paying licensees, and the constant pressure to
improve performance, that such an issue as duplicate expression
evaluation is unlikely to have gone unnoticed for 30+ years.

--
Visit <http://blogs.instreamfinancial.com/anything.php>
to read my rants on technology and the finance industry.