Re: PGSQL-to-MYSQL Migration: Error in a 'simple' inner join query

On Mon, 2009-05-04 at 12:30 -0500, Andy Colson wrote:
> Yes, that query works in mysql, but only in mysql... and probably not in
> any other db anywhere. It is not standard sql. My guess is that mysql
> is "helping" you out by adding the customer.name for you... but maybe
> not? Maybe its returning something else? Too bad it lets you write
> confusing questions.

Section 4.18 of SQL200n, "Functional Dependencies", shows some
interesting ways that the DBMS can make the proper inferences (I think
this is an optional feature, so I don't think PostgreSQL violates the
standard here).

I'm not sure if what DaNieL is asking for is actually covered by this
feature, because it would need to infer the function dependencies:

orders.id -> orders.code
orders.id -> customer.name

and the second one needs to be inferred through a couple steps, and that
may be more sophisticated than what the standard asks for. It can be
done though, because:

orders.id -> orders.id_customer (implied by key on orders.id)
orders.id_customer = customer.id (from join condition)
customer.id -> customer.name (implied by key on customer.id)

Therefore:

orders.id -> customer.name

So he's not asking for anything ridiculous, and it looks to me like he's
supported by the standard (although I'm not an expert on the SQL
standard). However, sometimes it's a good idea to be a little more
explicit in the queries, just for the sake of readability.

Regards,
Jeff Davis