Re: Oracle purchases Sleepycat - is this the "other shoe"

Yeah, that's how I remember mysql doing it. I'm sure postgres
doesn't want anything to do with how they do it. If I recall it was
kind of convenient sometimes as long as you only select fields that
are unambiguous.

For instance take the query where table "first_table" has primary key
"a":

select first_table.a, first_table.b from first_table inner join
second_table on second_table.a = first_table.a group by first_table.a

Because first_table.id is a primary key tables first_table and
second_table have either a one to one or a one to many
relationship. So if you group by first_table.a you know that you can
safely select any other field in that table and it will be unambiguous.

But in postgres you must do:

select first_table.a from first_table inner join second_table on
second_table.a = first_table.a group by first_table.a
or
select first_table.a, first_table.b from first_table inner join
second_table on second_table.a = first_table.a group by
first_table.a, first_table.b

But in mysql you can just do
select first_table.a, first_table.b from first_table inner join
second_table on second_table.a = first_table.a group by first_table.a

The problem is mysql will also allow:
select second_table.x, second_table.y from first_table inner join
second_table on second_table.a = first_table.a group by first_table.a

I just looked up the docs here:

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

They call it group by with hidden fields and consider it a feature
with the following caveat:

"Do not use this feature if the columns you omit from the GROUP BY
part are not unique in the group! You get unpredictable results."

I could swear that when I looked it up in the docs many years ago
that that it tried to actually explain what value would get picked so
you could actually try to get some use out the undefined cases but I
could be smoking crack. That was a long time ago. Some of the
comments are amusing and actually want the docs to clarify when you
might want to use the undefined cases.

Apparently you can also turn that feature off. Maybe the ability to
turn that "feature" off is one of the new enterprise friendly
features of mysql 5. :)

This is one of the reasons I am soooo glad I made the switch a long,
long time ago before I became too tied to mysql to easily change. If
I ever get around to porting over that last ancient barely used
application (yes it uses enums) I can avoid ever having to run mysql
again.

I think it's great if postgres wants to do this intelligently and per
spec but I doubt that mysql has anything to offer here. They just
handle all of the cases. Even the ones that shouldn't work.

Rick

On Feb 15, 2006, at 10:39 PM, Tom Lane wrote:

> Chris <dmagick(at)gmail(dot)com> writes:
>> Quick test:
>
>> create table a(a int primary key, b int, c varchar(200));
>> insert into a(a, b, c) values (1,1,'one');
>> insert into a(a, b, c) values (2,2,'two');
>> insert into a(a, b, c) values (3,1,'one');
>> insert into a(a, b, c) values (4,2,'two');
>
>> mysql> select a,b,c from a group by b;
>> +---+------+------+
>> | a | b | c |
>> +---+------+------+
>> | 1 | 1 | one |
>> | 2 | 2 | two |
>> +---+------+------+
>> 2 rows in set (0.00 sec)
>
> Egad :-(. At least the SQL spec has some notion of wanting the answer
> to a query to be well-defined ...
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly
>