X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 001D6475AE5
	for <pgsql-sql@postgresql.org>; Thu, 10 Apr 2003 11:16:12 -0400 (EDT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id A77A5474E42
	for <pgsql-sql@postgresql.org>; Thu, 10 Apr 2003 11:16:07 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h3AFGA2L004276;
	Thu, 10 Apr 2003 11:16:10 -0400 (EDT)
To: Ian Barwick <barwick@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL) 
In-reply-to: <200304101627.43804.barwick@gmx.net> 
References: <200304101627.43804.barwick@gmx.net>
Comments: In-reply-to Ian Barwick <barwick@gmx.net>
	message dated "Thu, 10 Apr 2003 16:27:43 +0200"
Date: Thu, 10 Apr 2003 11:16:09 -0400
Message-ID: <4275.1049987769@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Spam-Status: No, hits=-31.9 required=5.0
	tests=BAYES_00,EMAIL_ATTRIBUTION,HTML_TAG_BALANCE_TABLE,
	IN_REP_TO,QUOTED_EMAIL_TEXT,REFERENCES,REPLY_WITH_QUOTES
	autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200304/164
X-Sequence-Number: 12865

Ian Barwick <barwick@gmx.net> writes:
> The MySQL manual says:

>   "The target table of the INSERT statement cannot appear in the 
>   FROM clause of the SELECT part of the query because it's forbidden 
>   in standard SQL to SELECT from the same table into which you are 
>   inserting. (The problem is that the SELECT possibly would find
>   records that were inserted earlier during the same run. 
>   When using subquery clauses, the situation could easily be very 
>   confusing!)"

> Can anyone shed light on whether the above statement (especially
> the bit about "standard SQL") is correct?

As usual ;-), the MySQL guys shade the truth to suit themselves.

The body of the spec's description of INSERT INTO clearly allows this
operation.  SQL92 says

         3) The <query expression> is effectively evaluated before inserting
            any rows into B.

which SQL99 renders as

         5) QT is effectively evaluated before insertion of any rows into T.

so they have a perfectly clear model of how it should work.  MySQL's
explanation of why it's undefined is just an explanation of why their
implementation cannot support it.

It is true that this is considered an advanced feature.  SQL92
classifies it as Full SQL, while SQL99 calls it Feature F781:

         Leveling Rules

         1) The following restrictions apply for Intermediate SQL:

            a) The leaf generally underlying table of T shall not be gen-
              erally contained in the <query expression> immediately
              contained in the <insert columns and source> except as the
              <qualifier> of a <column reference>.

resp.

         Conformance Rules

         1) Without Feature F781, "Self-referencing operations", no leaf
            generally underlying table of T shall be generally contained in
            the <query expression> immediately contained in the <insert
            columns and source> except as the <table or query name> or
            <correlation name> of a column reference.

At least, I *think* this is what these restrictions are talking about.
I'm not sure what the "except" phrases purport to allow (in standard
SQL it's not meaningful to reference a table not mentioned in FROM,
so what are they giving permission for here??).  Maybe these
restrictions are talking about something else entirely?  But there
is no other part of the spec that could possibly be read MySQL's way.

			regards, tom lane