Procedure for feature requests?

Tim Landscheidt wrote:

Hi,

suppose I thought that PostgreSQL would benefit greatly from
a "generate_series(DATE, DATE[, INT]) RETURNS DATE" function
- where do I suggest such a thing? Here on -general? On
-hackers? Directly edit
<URI:http://wiki.postgresql.org/wiki/Todo&gt;?

I think direct edition of Todo is discouraged, particularly for
outsiders. Suppose we decided that we didn't want to implement your
suggestion for whatever reason? We get lots of people coming out of the
blue with a patch to implement something they found on Todo, only to
figure out that we didn't want the idea implemented in the first place.
Surely we don't want to turn people away from development just because
Todo is not well managed.

Suppose the feature request was not a trivial one, but
maybe a "DEPENDS ON <object>" clause for "CREATE FUNCTION"
to allow PostgreSQL to deny requests to drop a table/view/
function that is needed by a function - where would I pro-
pose that?

On -hackers, just like any other feature request, trivial or not.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Thu, Oct 01, 2009 at 11:35:25PM +0000, Tim Landscheidt wrote:

suppose I thought that PostgreSQL would benefit greatly from
a "generate_series(DATE, DATE[, INT]) RETURNS DATE" function

8.4 has a generate_series(timestamp,timestamp,interval) which would seem
to be a bit more flexible than you want.

--
Sam http://samason.me.uk/

Alvaro Herrera <alvherre@commandprompt.com> wrote:

[...]

Suppose the feature request was not a trivial one, but
maybe a "DEPENDS ON <object>" clause for "CREATE FUNCTION"
to allow PostgreSQL to deny requests to drop a table/view/
function that is needed by a function - where would I pro-
pose that?

On -hackers, just like any other feature request, trivial or not.

Thanks. Any particular form? A quick glance at the archives
did not reveal any feature requests that were not accompa-
nied by a patch :-).

Tim

Sam Mason <sam@samason.me.uk> wrote:

suppose I thought that PostgreSQL would benefit greatly from
a "generate_series(DATE, DATE[, INT]) RETURNS DATE" function

8.4 has a generate_series(timestamp,timestamp,interval) which would seem
to be a bit more flexible than you want.

Yes, I know :-). But as "generate_series(A, B, C)" can also
be written as "A + generate_series(0, (C - B) / C) * C" (or
something "flexible" like that :-)), a
"generate_series(DATE, DATE)" would inter alia get rid off
the need to cast the result from TIMESTAMP to DATE and to
explicitly specify "'1 day'". Just a small, trivial enhance-
ment for a popular use case :-).

Tim

On Fri, 2009-10-02 at 21:37 +0000, Tim Landscheidt wrote:

Alvaro Herrera <alvherre@commandprompt.com> wrote:

[...]

Suppose the feature request was not a trivial one, but
maybe a "DEPENDS ON <object>" clause for "CREATE FUNCTION"
to allow PostgreSQL to deny requests to drop a table/view/
function that is needed by a function - where would I pro-
pose that?

On -hackers, just like any other feature request, trivial or not.

Thanks. Any particular form? A quick glance at the archives
did not reveal any feature requests that were not accompa-
nied by a patch :-).

There is not a specific format but a good thing to do is:

Request Feature
Provide Use Case
Provide Example syntax (if applicable)

Joshua D. Drake

Tim

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

On Fri, Oct 02, 2009 at 09:48:14PM +0000, Tim Landscheidt wrote:

Sam Mason <sam@samason.me.uk> wrote:

8.4 has a generate_series(timestamp,timestamp,interval) which would seem
to be a bit more flexible than you want.

Yes, I know :-). But as "generate_series(A, B, C)" can also
be written as "A + generate_series(0, (C - B) / C) * C" (or
something "flexible" like that :-)), a

For things as complicated as timestamps I'm not sure if this is such a
trivial transform. If you can figure out the limit then it seems easy,
though I'm not sure how you'd do that.

"generate_series(DATE, DATE)" would inter alia get rid off
the need to cast the result from TIMESTAMP to DATE and to
explicitly specify "'1 day'". Just a small, trivial enhance-
ment for a popular use case :-).

Interesting, I tend to aim for maximum expressiveness not ease of
expressiveness. It would be somewhat easy to add the above if you want
though:

CREATE FUNCTION generate_series(date,date)
RETURNS SETOF date
IMMUTABLE LANGUAGE sql AS $$
SELECT generate_series($1::timestamp,$2::timestamp,interval '1 day')::date;
$$;

or I suppose you could use the integer series generation:

SELECT $1 + generate_series(0,$2 - $1);

Hum, now I'll have to see which is "better".

That second version seems to be slightly quicker (20 to 30%, for ranges
from a year up to a century respectively) so you may prefer it, but the
difference is going to be in the noise for any query I've ever used
generate_series for.

--
Sam http://samason.me.uk/

is there a way to create a cast with assignment e.g.?
CREATE CAST ((date,date) AS int4) WITH FUNCTION generate_series(date,date) AS ASSIGNMENT;http://www.postgresql.org/docs/8.4/static/sql-createcast.html
?
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

Date: Sat, 3 Oct 2009 16:23:36 +0100
From: sam@samason.me.uk
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Procedure for feature requests?

On Fri, Oct 02, 2009 at 09:48:14PM +0000, Tim Landscheidt wrote:

Sam Mason <sam@samason.me.uk> wrote:

8.4 has a generate_series(timestamp,timestamp,interval) which would seem
to be a bit more flexible than you want.

Yes, I know :-). But as "generate_series(A, B, C)" can also
be written as "A + generate_series(0, (C - B) / C) * C" (or
something "flexible" like that :-)), a

For things as complicated as timestamps I'm not sure if this is such a
trivial transform. If you can figure out the limit then it seems easy,
though I'm not sure how you'd do that.

"generate_series(DATE, DATE)" would inter alia get rid off
the need to cast the result from TIMESTAMP to DATE and to
explicitly specify "'1 day'". Just a small, trivial enhance-
ment for a popular use case :-).

Interesting, I tend to aim for maximum expressiveness not ease of
expressiveness. It would be somewhat easy to add the above if you want
though:

CREATE FUNCTION generate_series(date,date)
RETURNS SETOF date
IMMUTABLE LANGUAGE sql AS $$
SELECT generate_series($1::timestamp,$2::timestamp,interval '1 day')::date;
$$;

or I suppose you could use the integer series generation:

SELECT $1 + generate_series(0,$2 - $1);

Hum, now I'll have to see which is "better".

That second version seems to be slightly quicker (20 to 30%, for ranges
from a year up to a century respectively) so you may prefer it, but the
difference is going to be in the noise for any query I've ever used
generate_series for.

--
Sam http://samason.me.uk/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

_________________________________________________________________
Hotmail: Powerful Free email with security by Microsoft.
http://clk.atdmt.com/GBL/go/171222986/direct/01/

Hi all,

I'm a recent MySQL convert, and I am having some difficulty with
syntax on grouping a table by a foreign key and returning only the
newest entry that matches. In MySQL, you can do something like

event_log
------------
id
event_type
event_date
meaningful_data

SELECT
meaningful_data,
event_type,
event_date
FROM
event_log
GROUP BY
event_type
ORDER BY
event_date DESC

And this would return back the most recent event and meaningful data
for each event type. When I try this in postgres, I get errors about
either grouping by id or using id in an aggregate function. I am
afraid if I use it in an aggregate function, it will disrupt the sort
order I am attempting, and if I group by it, I no longer get the most
recent data for each event type, but I get every event. How would I
accomplish this is postresql? Is there some aggregate functions that
help with this, or should I be trying a different method altogether?
Grouping feels pretty different, and I suspect closer to standards, in
postgres. Unfortunately, my mind works (for the short term) in mysql.
Will someone please give me some pointers?

Thanks!
Corey

[ please don't top-post it's difficult to follow for those not directly
involved in the discussion ]

On Sat, Oct 03, 2009 at 12:14:19PM -0400, Martin Gainty wrote:

is there a way to create a cast with assignment e.g.?

Hum, I'm unsure how this would help. Maybe more explanation would help?

CREATE CAST ((date,date) AS int4) WITH FUNCTION generate_series(date,date) AS ASSIGNMENT;

generate_series returns to a SETOF values. It also has *much* more
complicated semantics than I'd expect most people would attribute as
useful to a datatype conversion function. For example, why would
casting from a pair of dates end up as a set of rows containing a single
date value?

I have a large problem understanding the real purpose casts so maybe I'm
missing something. My problem is that I don't understand the purpose
of trying to provide a "standard" way of converting between arbitrary
datatypes, it seems much easier to just provide a standard set of domain
specific functions that are explicitly used by the user. The SQL
standard specifies that they need to exist so PG has to support them,
but their purpose still confuses me!

--
Sam http://samason.me.uk/

Sam Mason <sam@samason.me.uk> writes:

I have a large problem understanding the real purpose casts so maybe I'm
missing something. My problem is that I don't understand the purpose
of trying to provide a "standard" way of converting between arbitrary
datatypes,

I think the reason CREATE CAST exists is exactly that the cast mechanism
*isn't* intended to provide conversions between any arbitrary pair of
datatypes. It's only intended to provide conversions in those cases
where the conversion semantics are obvious to some degree or other.
Since that's somewhat in the eye of the beholder, we allow the user
to adjust edge cases by creating/removing casts --- but there's no
expectation that when you define a new datatype, you'll provide casts
to or from unrelated types.

regards, tom lane

Corey Tisdale <corey@eyewantmedia.com> writes:

SELECT
meaningful_data,
event_type,
event_date
FROM
event_log
GROUP BY
event_type
ORDER BY
event_date DESC

Is event_type a primary key, or at least a candidate key, for this
table? (I would guess not based on the name.)

If it is, then the above is actually well-defined, because there is
only one possible input row for each group. The GROUP BY is actually
kinda pointless in that case.

If it is not, then the above is *not* well-defined --- there are
multiple possible meaningful_data and event_date values for each
event_type value, and you have absolutely no idea which ones you
will get. This is not allowed per SQL standard, and MySQL has
done you no service by failing to detect the ambiguity.

What you might be after is something like Postgres' DISTINCT ON
feature, which allows you to resolve the ambiguity by specifying
a sort order for the rows within each group (and then taking the
first row in each group). See the "weather reports" example in
our SELECT reference page.

I have never really played around with this aspect of MySQL ...
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.

regards, tom lane

On Sat, Oct 03, 2009 at 12:48:57PM -0400, Tom Lane wrote:

I think the reason CREATE CAST exists is exactly that the cast mechanism
*isn't* intended to provide conversions between any arbitrary pair of
datatypes. It's only intended to provide conversions in those cases
where the conversion semantics are obvious to some degree or other.

Yup, but the decision to officially bless some code as being a cast
rather than "just" a function seems very arbitrary, I think this is why
I don't understand its purpose.

Since that's somewhat in the eye of the beholder, we allow the user
to adjust edge cases by creating/removing casts --- but there's no
expectation that when you define a new datatype, you'll provide casts
to or from unrelated types.

I know there's no expectation to create any casts. I think what I'm
confused about is why anyone would ever bother creating any in the first
place. I have a feeling I may have used the functionality once, but
I can't think why or for what now. Having a function seems just as
expressive to me, which is why I think I'm missing the point.

--
Sam http://samason.me.uk/

On Sat, Oct 03, 2009 at 01:05:49PM -0400, Tom Lane wrote:

What you might be after is something like Postgres' DISTINCT ON
feature

Yup, looks that way to me as well.

I have never really played around with this aspect of MySQL ...

Me neither.

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.

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.
But if you want to do *anything* else (i.e. hash aggregate in PG) then
you want to keep the semantics of GROUP BY and ORDER BY separate as the
spec and indeed PG does.

--
Sam http://samason.me.uk/

Sam Mason <sam@samason.me.uk> writes:

On Sat, Oct 03, 2009 at 12:48:57PM -0400, Tom Lane wrote:

I think the reason CREATE CAST exists is exactly that the cast mechanism
*isn't* intended to provide conversions between any arbitrary pair of
datatypes. It's only intended to provide conversions in those cases
where the conversion semantics are obvious to some degree or other.

Yup, but the decision to officially bless some code as being a cast
rather than "just" a function seems very arbitrary, I think this is why
I don't understand its purpose.

It's useful when the conversion semantics are sufficiently natural that
you want the conversion to be applied implicitly. I agree that the
explicit CAST syntax hasn't got very much to recommend it over a
function call. That part you can blame on the SQL committee ;-) ...
the historical PostQUEL syntax for this was exactly a function call,
and you can still write it that way if you choose.

regards, tom lane

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

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:

Most Database Administrators dont allow jpg/png/gifs into BLOB columns simply because its Run-length
encoding and MUCH easier to store the picture's link e.g. http://www.mywebsite.com/PictureOfFido.jpg

Oracle on the other hand can store multi-gb images into blobs then again you're paying for that 'luxury'

Also keep in mind Postgres is under BSD license so you're getting what you pay for
<LegalStuff/>

PostgreSQL Database Management System

(formerly known as Postgres, then as Postgres95)

Portions Copyright (c) 1996-2009, The PostgreSQL Global Development Group

Portions Copyright (c) 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its
documentation for any purpose, without fee, and without a written agreement
is hereby granted, provided that the above copyright notice and this
paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR
DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO
PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
</LegalStuff>

what types of caching issues are you experencing?
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

From: corey@eyewantmedia.com
To: tgl@sss.pgh.pa.us
Subject: Re: [GENERAL] Embarassing GROUP question
Date: Sat, 3 Oct 2009 16:56:02 -0500
CC: sam@samason.me.uk; pgsql-general@postgresql.org

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

_________________________________________________________________
Hotmail: Trusted email with powerful SPAM protection.
http://clk.atdmt.com/GBL/go/177141665/direct/01/

Corey Tisdale <corey@eyewantmedia.com> writes:

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.

Okay, I got sufficiently interested to drag out the nearest copy of
mysql and try it ...

mysql> create table t (f1 int, f2 int, f3 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(1,11,111), (1,22,222), (1,44,444), (1,33,333);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> insert into t values(2,55,555), (2,22,222), (2,44,444), (2,33,333);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> insert into t values(3,55,555), (3,22,222), (3,44,444), (3,77,777);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from t group by f1 order by f2;
+------+------+------+
| f1 | f2 | f3 |
+------+------+------+
| 1 | 11 | 111 |
| 2 | 55 | 555 |
| 3 | 55 | 555 |
+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from t group by f1 order by f2 desc;
+------+------+------+
| f1 | f2 | f3 |
+------+------+------+
| 2 | 55 | 555 |
| 3 | 55 | 555 |
| 1 | 11 | 111 |
+------+------+------+
3 rows in set (0.00 sec)

Looks to me like we're arbitrarily getting the physically-first row in
each f1 group. It's certainly not looking for the minimum or maximum f2.

The above is with 5.1.37, but I find essentially the same wording in
the 3.x/4.x manual as in the 5.1 manual.

Now it's certainly possible that in particular circumstances you might
happen to get the right results --- for example, a scan that was using
an index might happen to deliver the rows in the right order. But I
don't see any evidence that mysql is reliably producing groupwise
minimums or maximums with this syntax. The long discussions in the
comments here:
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html
don't suggest that anyone else believes it works, either.

regards, tom lane

You may have nailed it. Everythig would have been indexed I. The order
it was grouped by, so perhaps the order in which things are indexed
and accesse is the kicker, or perhaps we've been consistantly lucky.

We also weren't adding image data to blobs, we were bit mapping
faceted data to blob and shifting to allow people to shop by artist or
color or subject matter across millions of posters. Normalized tables
just weren't cutting it, and bit shifting up to 32 bit was crazy fast.
After we rolled it out in production, we found mysql converts blobs to
32 bit unsigned ints before shifting. Postgres appears to not do this
at all, or our arbitrarily large test data did not trigger it on
postgres.

After the last few days, it is becoming apparent how much of a joke
mysql has been. Thanks again for such quick direction!

Corey Tisdale

On Oct 3, 2009, at 5:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: