bug with insert select?

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

On 18 Oct, 10:28, fmardini <f.mard...@gmail.com> wrote:
> 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)
You wouldn't get the ambiguous reference error if you simply gave your
table references unque aliases.

> INSERT INTO t1
> SELECT 1, id2, 0, 100
> FROM t1v WHERE id1 = 2
> ON DUPLICATE KEY UPDATE t1.val2 = t1v.val1 + 100
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.

On 18 Oct, 10:28, fmardini <f.mard...@gmail.com> wrote:
> 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

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?

On 18 Oct, 11:21, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 18 Oct, 10:28, fmardini <f.mard...@gmail.com> wrote:
>
>
>
>
>
> > 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
>
> 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?- Hide quoted text -
>
> - Show quoted text -

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.

On Oct 18, 12:54 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 18 Oct, 10:28, fmardini <f.mard...@gmail.com> wrote:
>
> > 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)
>
> You wouldn't get the ambiguous reference error if you simply gave your
> table references unque aliases.
>

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

> > INSERT INTO t1
> > SELECT 1, id2, 0, 100
> > FROM t1v WHERE id1 = 2
> > ON DUPLICATE KEY UPDATE t1.val2 = t1v.val1 + 100
>
> 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.
>

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

> 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.

On Oct 18, 1:22 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 18 Oct, 11:21, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 18 Oct, 10:28, fmardini <f.mard...@gmail.com> wrote:
>
> > > 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
>
> > 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?- Hide quoted text -
>
> > - Show quoted text -
>
> 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.

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)

On 18 Oct, 11:39, fmardini <f.mard...@gmail.com> wrote:
> On Oct 18, 1:22 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > On 18 Oct, 11:21, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > On 18 Oct, 10:28, fmardini <f.mard...@gmail.com> wrote:
>
> > > > 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
>
> > > 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?- Hide quoted text -
>
> > > - Show quoted text -
>
> > 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.
>
> 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)- Hide quoted text -
>
> - Show quoted text -

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.

On Oct 18, 3:17 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 18 Oct, 11:39, fmardini <f.mard...@gmail.com> wrote:
>
>
>
> > On Oct 18, 1:22 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > On 18 Oct, 11:21, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > On 18 Oct, 10:28, fmardini <f.mard...@gmail.com> wrote:
>
> > > > > 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
>
> > > > 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?- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > 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.
>
> > 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)- Hide quoted text -
>
> > - Show quoted text -
>
> 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.

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

fmardini

On 18 Oct, 13:24, fmardini <f.mard...@gmail.com> wrote:
> On Oct 18, 3:17 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > On 18 Oct, 11:39, fmardini <f.mard...@gmail.com> wrote:
>
> > > On Oct 18, 1:22 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > On 18 Oct, 11:21, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > > On 18 Oct, 10:28, fmardini <f.mard...@gmail.com> wrote:
>
> > > > > > 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
>
> > > > > 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?- Hide quoted text -
>
> > > > > - Show quoted text -
>
> > > > 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.
>
> > > 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)- Hide quoted text -
>
> > > - Show quoted text -
>
> > 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.
>
> yeah, to me it looks like a bug
> i really appreciate your assistance
>
> fmardini- Hide quoted text -
>
> - Show quoted text -

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.

On Oct 18, 3:42 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 18 Oct, 13:24, fmardini <f.mard...@gmail.com> wrote:
>
>
>
> > On Oct 18, 3:17 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > On 18 Oct, 11:39, fmardini <f.mard...@gmail.com> wrote:
>
> > > > On Oct 18, 1:22 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > > On 18 Oct, 11:21, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > > > On 18 Oct, 10:28, fmardini <f.mard...@gmail.com> wrote:
>
> > > > > > > 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
>
> > > > > > 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?- Hide quoted text -
>
> > > > > > - Show quoted text -
>
> > > > > 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.
>
> > > > 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)- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > 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.
>
> > yeah, to me it looks like a bug
> > i really appreciate your assistance
>
> > fmardini- Hide quoted text -
>
> > - Show quoted text -
>
> 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.

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