Paul Lautman wrote:
> <snip>
>
> The manual tells you when an output column will be referenced. If it would
> be referenced in all cases then the writers would not have bothered to list
> the only 2 that applied. The manual doesn't tell you everything that does
> not apply. For instance it tells you that the SELECT syntax starts with
> SELECT. It doesn't tell you that you cannot spell SELECT as SELETC or any
> other way.
>
> ZeldorBlat's mention of the GROUP BY clause is totally unrelated and in fact
> wrong. It is indeed perfectly possible to have an expression in the GROUP BY
> clause that is not in the SELECT list. And it does not "complain" as he
> states. Instead it works! I just tried it.
>
Have you used other SQL products, or is MySQL the first? How did
grouping by expressions not in the select list work then?
> Regardless of what you say about optimisation strategies, the developers of
> databases do, as I have said before, assume a bit of intelligence in the
> people who use them.
You might assume so, but for many SQL servers optimization strategies
routinely dispense with table and join order, and instead rely on
indexes and statistics to decide the best query plan. I'm not a MySQL
expert, but perhaps you've tried using (or have read about) "forceplan,"
which for SQL Server and Sybase basically neuters the optimizer, tells
it to stay home, and says, "I'm the programmer, do it the way I've
written it."

In many instances, the optimizer can be smarter than the programmer.
And based on what I've seen from many programmers, that's not that hard
to do. In my early days I'm pretty sure it was smarter than me. Maybe
it still is...
> They have stated quite clearly in the manual when data
> in a column can be referenced. The would not bother, no matter how many
> papers had been written to try to optimise a query for something like this
> when they have already given and documented the 2 correct ways to do it.
The two correct ways to express syntax. Not ways to force or avoid
redundant expression evaluation.

Perhaps a SHOWPLAN might help resolve this?


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