[MonetDB-users] updates in a transaction
Hi, I tried to update a column of a record more than once within a transaction. But it seemed that only the first update took effect. For example, for a table "CREATE TABLE people (id TINYINT PRIMARY KEY, name VARCHAR(128) NOT NULL)", sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | 0 | Phil Ivey | | 1 | Michael Jordan | | 2 | Lionel Messi | +------+-----------------------------+ sql>start transaction; auto commit mode: off sql>update people set id = -1 where name='Phil Ivey'; 1 affected row sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | 1 | Michael Jordan | | 2 | Lionel Messi | | -1 | Phil Ivey | +------+-----------------------------+ sql>update people set id = -2 where name='Phil Ivey'; 1 affected row sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | 1 | Michael Jordan | | 2 | Lionel Messi | | -1 | Phil Ivey | +------+-----------------------------+ sql>commit; auto commit mode: on sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | -1 | Phil Ivey | | 1 | Michael Jordan | | 2 | Lionel Messi | +------+-----------------------------+ It is not what we would expect, isn't it? My MonetDB version is v5.18.3 Min
Dear Min, ..
sql>update people set id = -2 where name='Phil Ivey'; 1 affected row
sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | 1 | Michael Jordan | | 2 | Lionel Messi | | -1 | Phil Ivey | +------+-----------------------------+
..
It is not what we would expect, isn't it? My MonetDB version is v5.18.3
I could also reproduce your problem on MonetDB version 5.19.0 and SQL version 2.37.0, where I found that the BAT storage code for updating would not save data to updated columns if the same row was already updated in the same transaction. The attached patch (to be applied to the SQL module) fixes this for me. Maybe it also applies to the version you are running. Best regards, Isidor
Dear Isidor, Thank you very much for your time to check this. May I have the patch? I didn't see it in attachment. Thanks, Min On May 17, 2010, at 4:37 AM, Isidor Zeuner wrote:
Dear Min,
..
sql>update people set id = -2 where name='Phil Ivey'; 1 affected row
sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | 1 | Michael Jordan | | 2 | Lionel Messi | | -1 | Phil Ivey | +------+-----------------------------+
..
It is not what we would expect, isn't it? My MonetDB version is v5.18.3
I could also reproduce your problem on MonetDB version 5.19.0 and SQL version 2.37.0, where I found that the BAT storage code for updating would not save data to updated columns if the same row was already updated in the same transaction. The attached patch (to be applied to the SQL module) fixes this for me. Maybe it also applies to the version you are running.
Best regards,
Isidor------------------------------------------------------------------------------
_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Dear Min,
Thank you very much for your time to check this. May I have the patch? I didn't see it in attachment.
Maybe some problem with your mail software? Anyway, I created a bug report for this issue ([1]), where you can find the patch under "attachments". I also included your SQL example, so with a bit of luck it will be included in the regression test suite, preventing future releases with that problem. Best regards, Isidor [1] http://bugs.monetdb.org/show_bug.cgi?id=2543
On 17-05-2010 10:37:44 +0200, Isidor Zeuner wrote:
I could also reproduce your problem on MonetDB version 5.19.0 and SQL version 2.37.0, where I found that the BAT storage code for updating would not save data to updated columns if the same row was already updated in the same transaction. The attached patch (to be applied to the SQL module) fixes this for me. Maybe it also applies to the version you are running.
Thank you for the patch. Could you please file a bug with this patch before it gets off of our radar? It needs to be checked by the proper persons before it can be committed.
On 17-05-2010 10:37:44 +0200, Isidor Zeuner wrote:
I could also reproduce your problem on MonetDB version 5.19.0 and SQL version 2.37.0, where I found that the BAT storage code for updating would not save data to updated columns if the same row was already updated in the same transaction. The attached patch (to be applied to the SQL module) fixes this for me. Maybe it also applies to the version you are running.
Thank you for the patch. Could you please file a bug with this patch before it gets off of our radar? It needs to be checked by the proper persons before it can be committed.
Just did: [1] [1] http://bugs.monetdb.org/show_bug.cgi?id=2543
participants (3)
-
Fabian Groffen -
Isidor Zeuner -
Min Lu