On Thu, Sep 23, 2010 at 7:55 PM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
On 2010-09-23 1:31 PM +0300, Boxuan Zhai wrote:
I have just generate a new patch of MERGE command.
+-- we now have a duplicate key in Buy, so when we join to
+-- Stock we will generate 2 matching rows, not one.
+-- According to standard this command should fail.
+-- But it suceeds in PostgreSQL implementation by simply ignoring the second
It doesn't seem like a very good idea to go against the standard here. The "second" row is not well defined in this case so the results are unpredictable.
Yes, the result is uncertain. It depends on which row is scanned first, which is almost out of the control of users.
But, in postgres, this is what the system do for UPDATE.
For example, consider a simple update query like the following:
CREATE TABLE target (id int, val int);
INSERT INTO target VALUES (1, 10);
CREATE TABLE source (id int, add int);
INSERT INTO source VALUES (1, 100);
INSERT INTO source VALUES (1, 100000);
-- DO the update query with source table, which has multiple matched rows
UPDATE target SET val = val + add FROM source
t=# SELECT * FROM target;
id | val
----+-----
1 | 110
(1 row)
The target tuple has two matched source tuples, but it is only updated once. And, yet, this query is not forbidden by postgres. The result is also uncertain.
The patch is also missing a (trivial) change to explain.c.
Sorry, I massed up the files. Here comes the new patch file, with EXPLAIN in it.
Regards,
Marko Tiikkaja