Developer Forum

I think I might have found a bug with insert select when inserting
into the same table and using a composite primary key
I made the smallest example I could come up with

CREATE TABLE `t1` (
`id1` int(11) NOT NULL,
`id2` int(11) NOT NULL,
`val1` int(11) default '0',
`val2` int(11) default '0',
PRIMARY KEY (`id1`,`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE VIEW t1v AS SELECT * FROM t1

INSERT INTO t1 (`id1`, `id2`, `val1`, `val2`) VALUES (1, 1, 1, 10),
(1, 2, 2, 20), (1, 3, 3, 30), (2, 1, 1, 40), (2, 2, 2, 50), (2, 3, 3,
60)

I want to insert into the table from itself
I use the view as a way to reference the selected row as opposed to
the found (duplicate) row (otherwise i get an ambiguous reference
error)

INSERT INTO t1
SELECT 1, id2, 0, 100
FROM t1v WHERE id1 = 2
ON DUPLICATE KEY UPDATE t1.val2 = t1v.val1 + 100

After executing the query, the contents of the table are

1 1 1 103
1 2 2 103
1 3 3 103
2 1 1 40
2 2 2 50
2 3 3 60

I was expecting the following

1 1 1 101
1 2 2 102
1 3 3 103
2 1 1 40
2 2 2 50
2 3 3 60

Am I doing something wrong or is this a bug with INSERT SELECT
If this is the wrong list, please tell me where to post

thanks
fmardini

You wouldn't get the ambiguous reference error if you simply gave your
table references unque aliases.

You are not using the complete composite primary key, so the last row
encountered with the partial key in the WHERE clause will update the
rows. This is not a bug, it is what you told it to do.

You have posted sample data inserts and create statements so I will
load it into a table so I can see what you want the transformation to
look like.

Actually, the more I look at this, the more confused I become.

What are you trying to achieve here? Are you just wanting to update
val2 (where id1 = 1) to (val1 where id1 = 2) + 100?

If so wouldn't a multi-table update with aliases be easier?

Since the val1 values for id1 = 2 are the same as those for id1 = 1,
it is not at all clear.

Please re-hash your sample data to make it clear what is required to
happen.

I tried using SELECT ... AS but it didn't work


I think i use the complete composite key since the select statement
selects 1, id2, i might be missing something though
I still don't understand why does it use the last row

the select part return three rows
1 1 0 100
1 2 0 100
1 3 0 100

and all those rows are duplicates (based on the composite primary key)
thanks again for your help

Ok this table is supposed to track a relation between two kinds of
objects, and the composite primary key is made up of the ids of the
two related objects
What i am trying to do is create a relation between object1 and all
objects object2 is related to, but if the relation already exists i
have to update some parameters in the relation (the on duplicate key
thing)

n 18 Oct, 11:39, fmardini < XXXX@XXXXX.COM > wrote:

Hmmm, I changed your sample data to make it plainer what was coming
from where, and changed the query to use an alias instead of a view,
once again to make it obvious what was coming from where:

INSERT INTO t1( `id1` , `id2` , `val1` , `val2` )
VALUES ( 1, 1, 1, 10 ) , ( 1, 2, 2, 20 ) , ( 1, 3, 3, 30 ) , ( 2, 1,
4, 40 ) , ( 2, 2, 5, 50 ) , ( 2, 3, 6, 60 )

INSERT INTO t1
SELECT 1, a2.id2, 0, 100
FROM t1 a2 WHERE a2.id1 = 2
ON DUPLICATE KEY UPDATE t1.val2 = a2.val1 + 100

Now, I tried it with a non-composite key and the effect is the same. I
don't think this is anything to do with composite keys.

It seems as if it logs the need to perform an update and then does
them all at the end, at which time a2.val1 has the value 6.

n Oct 18, 3:17 pm, Captain Paralytic < XXXX@XXXXX.COM > wrote:

yeah, to me it looks like a bug
i really appreciate your assistance

fmardini

n 18 Oct, 13:24, fmardini < XXXX@XXXXX.COM > wrote:

Hmm, I may have thought of another way of accomplishing this.

Could you provide a before and after table of what you expect to see.
The before table should require an insert as well as an update and the
sample data should make it obvious what data is coming from what row
(as in my sample).

So 5 rows in the before table and 6 in the after one. Just to check
that I have really sussed what you expect to see.

n Oct 18, 3:42 pm, Captain Paralytic < XXXX@XXXXX.COM > wrote:

Thanks,

working with your data, if you remove the value ( 1, 3, 3, 30 ) thus
having

INSERT INTO t1( `id1` , `id2` , `val1` , `val2` )
VALUES ( 1, 1, 1, 10 ) , ( 1, 2, 2, 20 ) , ( 1, 3, 3, 30 ) , ( 2, 1,
4, 40 ) , ( 2, 2, 5, 50 ) , ( 2, 3, 6, 60 )

and then running the query u provided we get

1 1 1 106
1 2 2 106
1 3 0 100
2 1 4 40
2 2 5 50
2 3 6 60

but what i would expect is

1 1 1 104
1 2 2 105
1 3 0 100
2 1 4 40
2 2 5 50
2 3 6 60

thanks

OK, try this:

INSERT INTO t1
(id1, id2, val1, val2)
SELECT
1,
a1.id2,
0,
a1.val2
FROM t1 a1
LEFT JOIN t1 a2 ON a2.id1 = 1
AND a1.id2 = a2.id2
WHERE a1.id1 = 2 AND a2.id1 IS NULL
UNION
SELECT
1,
a1.id2,
a1.val1,
a1.val1 + 100
FROM t1 a1
LEFT JOIN t1 a2 ON a2.id1 = 1
AND a1.id2 = a2.id2
WHERE a1.id1 = 2 AND a2.id1 IS NOT NULL
ON DUPLICATE KEY UPDATE
t1.val2 = VALUES(val2)