BUG(?): multiple-table update problem

This is a difference between the current 4.0 and 4.1 bk trees.

In 4.0:

Set up a couple of tables that have identical structure, insert a few
records into them such that there is only one match between them:

mysql> create table t (i int, j int);
mysql> create table t2 (i int, j int);
mysql> insert into t values(1,1),(2,1);
mysql> insert into t2 values(2,1);

Verify that a join produces one match:

mysql> select t.* from t, t2 where t.i=t2.i;
+------+------+
| i | j |
+------+------+
| 2 | 1 |
+------+------+

Try a multiple-table update:

mysql> update t, t2 set t.j = t.j+1 where t.i=t2.i;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Okay, looks good, it says 1 record was changed, as I expect.

But this is what the table looks like:

mysql> select * from t;
+------+------+
| i | j |
+------+------+
| 1 | 2 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)

Hmm! The j column in both rows has been updated.

If I try the same thing with 4.1.0, the last select returns this result:

+------+------+
| i | j |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+


How-to-repeat:

Run this script, see what happens:

drop table if exists t;
drop table if exists t2;
create table t (i int, j int);
create table t2 (i int, j int);
insert into t values(1,1),(2,1);
insert into t2 values(2,1);
select t.* from t, t2 where t.i=t2.i;
update t, t2 set t.j = t.j+1 where t.i=t2.i;
select * from t;


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12640@lists.mysql.com
To unsubscribe, e-mail

Re: BUG(?): multiple-table update problem

I just tried the problem reported below with the most current 4.0
tree and it seems the problem is fixed there. Sorry. :-(

I have too many servers running, I can't keep them straight, it
seems. Grrr...


At 14:24 -0500 10/6/02, Paul DuBois wrote:
>This is a difference between the current 4.0 and 4.1 bk trees.
>
>In 4.0:
>
>Set up a couple of tables that have identical structure, insert a few
>records into them such that there is only one match between them:
>
>mysql> create table t (i int, j int);
>mysql> create table t2 (i int, j int);
>mysql> insert into t values(1,1),(2,1);
>mysql> insert into t2 values(2,1);
>
>Verify that a join produces one match:
>
>mysql> select t.* from t, t2 where t.i=t2.i;
>+------+------+
>| i | j |
>+------+------+
>| 2 | 1 |
>+------+------+
>
>Try a multiple-table update:
>
>mysql> update t, t2 set t.j = t.j+1 where t.i=t2.i;
>Query OK, 1 row affected (0.00 sec)
>Rows matched: 1 Changed: 1 Warnings: 0
>
>Okay, looks good, it says 1 record was changed, as I expect.
>
>But this is what the table looks like:
>
>mysql> select * from t;
>+------+------+
>| i | j |
>+------+------+
>| 1 | 2 |
>| 2 | 2 |
>+------+------+
>2 rows in set (0.00 sec)
>
>Hmm! The j column in both rows has been updated.
>
>If I try the same thing with 4.1.0, the last select returns this result:
>
>+------+------+
>| i | j |
>+------+------+
>| 1 | 1 |
>| 2 | 2 |
>+------+------+
>
>
>How-to-repeat:
>
>Run this script, see what happens:
>
>drop table if exists t;
>drop table if exists t2;
>create table t (i int, j int);
>create table t2 (i int, j int);
>insert into t values(1,1),(2,1);
>insert into t2 values(2,1);
>select t.* from t, t2 where t.i=t2.i;
>update t, t2 set t.j = t.j+1 where t.i=t2.i;
>select * from t;


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12641@lists.mysql.com
To unsubscribe, e-mail