Re: switching from mysql

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Brad Hilton <bhilton(at)vpop(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: switching from mysql
Date: 2001-11-16 06:16:27
Message-ID: 23686.1005891387@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Brad Hilton <bhilton(at)vpop(dot)net> writes:
> I issue
> select * from items GROUP BY id
> I get:
> "Attribute items.name must be GROUPed or used in an aggregate function"
> It appears in MySQL if you group on a unique key, then you aren't
> required to group on the rest of the fields you select from the table.

If you know it's a unique key, why are you bothering to GROUP?
If it's not a unique key, then there's no unique value for the other
columns. Either way, I can see no sense to this query. The SQL92
spec doesn't see any sense in it either.

> Postgres evidently doesn't work this way. Is there any way to select
> all fields from a table without grouping on each of those fields if the
> group by field is a unique key?

Please define what answer you want, and then we can talk about how to
get it.

> The second problem is with LEFT JOIN. Here's a sample query that works
> on Mysql but not on postgresql:

> select count(*) from a, b
> LEFT JOIN c on
> c.foo = a.foo and
> c.foo = b.foo
> where
> c.foo is not null and
> a.aid = b.bid

> This raises an error:

> ERROR: JOIN/ON clause refers to "a", which is not part of JOIN

I believe MySQL thinks that this query means

... FROM (a CROSS JOIN b) LEFT JOIN c ON condition

which unfortunately for them is not what the SQL spec says the
syntax means. The spec's interpretation is

... FROM a CROSS JOIN (b LEFT JOIN c ON condition)

from which the error complaint follows.

regards, tom lane

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Colin Barnard 2001-11-16 14:26:57 Migrating db from Oracle to Postgresql
Previous Message Tom Lane 2001-11-16 04:55:02 Re: PostgreSQL performance deteriorates over time?