Q: Sorting by the sum of columns?

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.

On 16 May, 04:47, Thomas Gagne <tga...@wide-open-west.com> wrote:
> 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?

I have used others, in particular DB/2. However, this discussion is
about MySQL (this is comp.databases.mysql).
The manual that I was refering to was the MySQL manual. I would not
make statements about another database system without having checked
the manuals for them. The extensions to SQL standard regarding "GROUP
BY" that have been mentioned above may not be valid for other systems.

> 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."
But this discussion is about MySQL. It is the MySQL Manual that I was
looking at.

> 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...
Maybe some of them are. But the MySQL manual is quite clear on when
column data is referred to and it is MySQL we are discussing here.

>> 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.