We're coming from mysql 4, and changing the sort order changes the
values of all columns as you would expect, given that you would expect
a sort statement to affect grouping. This certainly isn't the only
time I've used this syntax. I've been mysql user for ten years, and
the outcome has been consistant across hundreds of tables and millions
of rows and thousands of queries. If you ever have to use or modify a
mysql db, just keep this in mind in case it saves you some time.
That being said, we've discovered a few instances where docs were
wrong, found numerous bugs with bitshifting and blob objects and cache
usage and io buffering. We even sarted working on our own storage
engine until we came to our senses and switched RDBMSeses.
5.1 has chased more than a few folks off, and rather than upgrade to
it, we started porting to postgres. I didn't mean for my comparison to
appearas a knock against postgres, merely to explain why I was having
such a problem with such a simple issue. Thanks again for the help.
Corey Tisdale
On Oct 3, 2009, at 3:39 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Sam Mason <sam@xxxxxxxxxxxxx> writes:
On Sat, Oct 03, 2009 at 01:05:49PM -0400, Tom Lane wrote:
but looking at this example, and presuming that you find that
it actually does something useful, I wonder whether they interpret
the combination of GROUP BY and ambiguous-per-spec ORDER BY
in some fashion similar to DISTINCT ON.
Yup, does look that way doesn't it. It's still a weird pair of
semantics to conflate.
I poked around in the MySQL 5.1 manual to see if this is true.
I think it isn't --- it says very clearly here:
http://dev.mysql.com/doc/refman/5.1/en/group-by-hidden-columns.html
that you simply get an arbitrary choice among the possible values
when you reference an ambiguous column. It's possible that Corey's
query actually does give him the answers he wants, but apparently
it would be an implementation artifact that they're not promising
to maintain.
Hum, if they were assuming that you'd always have to implement
GROUP BY
by doing a sort step first then I can see why they'd end up with
this.
It's worse than that --- they actually are promising that GROUP BY
orders the results! In
http://dev.mysql.com/doc/refman/5.1/en/select.html
I find
If you use GROUP BY, output rows are sorted according to the
GROUP BY columns as if you had an ORDER BY for the same
columns. To avoid the overhead of sorting that GROUP BY
produces, add ORDER BY NULL:
SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;
MySQL extends the GROUP BY clause so that you can also specify
ASC and DESC after columns named in the clause:
SELECT a, COUNT(b) FROM test_table GROUP BY a DESC;
The first of these examples implies that they allow ORDER BY to
override
the default GROUP BY sorting, which would mean that the ORDER BY sort
has to happen after the GROUP BY operation, unlike the approach we
take
for DISTINCT ON. So that means the ORDER BY *isn't* going to affect
which row gets chosen out of each event_type group.
What I am currently betting is that Corey's query does not really do
what he thinks it does in MySQL. It probably is selecting a random
representative row in each group and then sorting on the basis of the
event_dates in those rows.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
