Re: Embarassing GROUP question - Mailing list pgsql-general

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@sss.pgh.pa.us> wrote:

> Sam Mason <sam@samason.me.uk> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general