agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedINSERT INTO ... SELECT (PostgreSQL vs. MySQL)
7+ messages / 5 participants
[nested] [flat]
* INSERT INTO ... SELECT (PostgreSQL vs. MySQL)
@ 2003-04-10 14:27 Ian Barwick <barwick@gmx.net>
2003-04-10 15:01 ` Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL) Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-04-10 15:10 ` Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL) Rod Taylor <rbt@rbt.ca>
2003-04-10 15:16 ` Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL) Tom Lane <tgl@sss.pgh.pa.us>
2003-04-10 16:09 ` Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL) Jan Wieck <JanWieck@Yahoo.com>
2003-04-10 17:35 ` Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL) Ian Barwick <barwick@gmx.net>
0 siblings, 5 replies; 7+ messages in thread
From: Ian Barwick @ 2003-04-10 14:27 UTC (permalink / raw)
To: pgsql-sql
I'm currently "porting" a smallish application from Postgres
to MySQL [*]. I see that with MySQL it is not possible to perform
INSERT INTO ... SELECT
when the target table is the same as the source table, e.g.
INSERT INTO foo (abc, xyz)
SELECT abc, xyz FROM foo WHERE id = 1
MySQL says: ERROR 1066: Not unique table/alias: 'foo'
This statement works as expected in both PostgreSQL (at least 7.3.x)
and also in Oracle 8i.
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!)"
( http://www.mysql.com/doc/en/INSERT_SELECT.html )
Can anyone shed light on whether the above statement (especially
the bit about "standard SQL") is correct? I can't get my head
around MySQL being more standards compliant than Postgres here...
[*] I have probably committed some very heinous deed in a previous life ;-)
Ian Barwick
barwick@gmx.net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL)
2003-04-10 14:27 INSERT INTO ... SELECT (PostgreSQL vs. MySQL) Ian Barwick <barwick@gmx.net>
@ 2003-04-10 15:01 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
4 siblings, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2003-04-10 15:01 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; +Cc: pgsql-sql
On Thu, 10 Apr 2003, Ian Barwick wrote:
>
> I'm currently "porting" a smallish application from Postgres
> to MySQL [*]. I see that with MySQL it is not possible to perform
>
> INSERT INTO ... SELECT
>
> when the target table is the same as the source table, e.g.
>
> INSERT INTO foo (abc, xyz)
> SELECT abc, xyz FROM foo WHERE id = 1
>
> MySQL says: ERROR 1066: Not unique table/alias: 'foo'
>
> This statement works as expected in both PostgreSQL (at least 7.3.x)
> and also in Oracle 8i.
>
> 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!)"
>
> ( http://www.mysql.com/doc/en/INSERT_SELECT.html )
>
> Can anyone shed light on whether the above statement (especially
> the bit about "standard SQL") is correct? I can't get my head
> around MySQL being more standards compliant than Postgres here...
I'm guessing they're speaking of (13.8 leveling rules 1)
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>.
I think when they mention the spec. :)
However, that's a leveling rule, so it's optional (if you don't support
it you can't claim the full level, you can only claim Intermediate SQL).
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL)
2003-04-10 14:27 INSERT INTO ... SELECT (PostgreSQL vs. MySQL) Ian Barwick <barwick@gmx.net>
@ 2003-04-10 15:10 ` Rod Taylor <rbt@rbt.ca>
2003-04-10 15:40 ` Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL) Rod Taylor <rbt@rbt.ca>
4 siblings, 1 reply; 7+ messages in thread
From: Rod Taylor @ 2003-04-10 15:10 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; +Cc: pgsql-sql
> Can anyone shed light on whether the above statement (especially
> the bit about "standard SQL") is correct? I can't get my head
> around MySQL being more standards compliant than Postgres here...
MySQL would have a tough time determining whether the tuple at the end
of the table was a result of the current insert or not (possibly making
a loop). PostgreSQL and most other major databases are perfectly capable
of such acts, so they have extended the spec for obvious reasons -- it's
a silly limitation to put in place for capable databases.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
[application/pgp-signature] signature.asc (186B, ../../1049987441.55410.13.camel@jester/2-signature.asc)
download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL)
2003-04-10 14:27 INSERT INTO ... SELECT (PostgreSQL vs. MySQL) Ian Barwick <barwick@gmx.net>
2003-04-10 15:10 ` Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL) Rod Taylor <rbt@rbt.ca>
@ 2003-04-10 15:40 ` Rod Taylor <rbt@rbt.ca>
0 siblings, 0 replies; 7+ messages in thread
From: Rod Taylor @ 2003-04-10 15:40 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; +Cc: pgsql-sql
> of such acts, so they have extended the spec for obvious reasons -- it's
Not extended the spec, implemented non-basic parts.
> a silly limitation to put in place for capable databases.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
[application/pgp-signature] signature.asc (186B, ../../1049989252.55410.15.camel@jester/2-signature.asc)
download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL)
2003-04-10 14:27 INSERT INTO ... SELECT (PostgreSQL vs. MySQL) Ian Barwick <barwick@gmx.net>
@ 2003-04-10 15:16 ` Tom Lane <tgl@sss.pgh.pa.us>
4 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2003-04-10 15:16 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL)
2003-04-10 14:27 INSERT INTO ... SELECT (PostgreSQL vs. MySQL) Ian Barwick <barwick@gmx.net>
@ 2003-04-10 16:09 ` Jan Wieck <JanWieck@Yahoo.com>
4 siblings, 0 replies; 7+ messages in thread
From: Jan Wieck @ 2003-04-10 16:09 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; +Cc: pgsql-sql
Ian Barwick wrote:
>
> I'm currently "porting" a smallish application from Postgres
> to MySQL [*]. I see that with MySQL it is not possible to perform
>
> INSERT INTO ... SELECT
>
> when the target table is the same as the source table, e.g.
>
> INSERT INTO foo (abc, xyz)
> SELECT abc, xyz FROM foo WHERE id = 1
>
> MySQL says: ERROR 1066: Not unique table/alias: 'foo'
>
> This statement works as expected in both PostgreSQL (at least 7.3.x)
> and also in Oracle 8i.
Microsoft SQL-Server 2000 has no problem with it either.
>
> 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!)"
I didn't find anything like that in 15.8 of the SQL3 draft. Must be in
small print on the backside of the PostScript file I have. Sure, the
spec can be a bit confusing if one looks at it for the first time. I
didn't know that it confused the MySQL developer(s) that much.
For sure is it another perfect example why one has to be very carefull
when reading the MySQL documentation. Half of the reasons for why MySQL
differs from the standard or does not support basic standard
functionality are simply made up. The manual is full of lame excuses for
missing features and full of wrong or even dangerous advices or
workarounds every database professional can only shake his head over.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT INTO ... SELECT (PostgreSQL vs. MySQL)
2003-04-10 14:27 INSERT INTO ... SELECT (PostgreSQL vs. MySQL) Ian Barwick <barwick@gmx.net>
@ 2003-04-10 17:35 ` Ian Barwick <barwick@gmx.net>
4 siblings, 0 replies; 7+ messages in thread
From: Ian Barwick @ 2003-04-10 17:35 UTC (permalink / raw)
To: pgsql-sql; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; Tom Lane <tgl@sss.pgh.pa.us>; Rod Taylor <rbt@rbt.ca>; Jan Wieck <JanWieck@Yahoo.com>
On Thursday 10 April 2003 16:27, Ian Barwick wrote:
> I'm currently "porting" a smallish application from Postgres
> to MySQL [*].
Many thanks for all your feedback on this. I've just had a very long and
frustrating afternoon coercing the app to work with MySQL ("it's what
the customers have") and was harbouring thoughts of committing unspeakable
acts of violence to inanimate objects. [ Incoherent rambling rant about
database software which happily and warninglessly accepts swathes of syntax it
has every intention of ignoring silently snipped].
Anyway, that's another point for my growing MySQL gotcha list.
Ian Barwick
barwick@gmx.net
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-04-10 17:35 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-10 14:27 INSERT INTO ... SELECT (PostgreSQL vs. MySQL) Ian Barwick <barwick@gmx.net>
2003-04-10 15:01 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-04-10 15:10 ` Rod Taylor <rbt@rbt.ca>
2003-04-10 15:40 ` Rod Taylor <rbt@rbt.ca>
2003-04-10 15:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-10 16:09 ` Jan Wieck <JanWieck@Yahoo.com>
2003-04-10 17:35 ` Ian Barwick <barwick@gmx.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox