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

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> 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).

Just for the record, this is something that was added in SQL:99 ---
our behavior conforms to the letter of earlier SQL versions. I think
we have a TODO item to add at least some support for allowing implicit
GROUP BY using functional dependencies, but it's kind of a worrisome
thing. I don't know of any other part of the SQL spec whereby ALTER
TABLE DROP CONSTRAINT could turn a formerly semantically legal query
into an illegal query. Could have some unpleasant implications for the
behavior of prepared statements.

(Also, I'll bet a lot of money that mysql has not implemented this
feature according to spec. The last I heard, as long as you have a
GROUP BY they just blithely assume you know what you're doing and
didn't write a query whose results are ambiguous.)

regards, tom lane