Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> 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@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux