Bug #40565 Update Query Results in "1 Row Affected" But Should Be "Zero Rows"
Submitted: 6 Nov 2008 22:25 Modified: 20 Jun 2010 1:00
Reporter: Chris Tysh Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0.51a, 5.0.72 OS:Linux (Ubuntu 5.1)
Assigned to: Satya B CPU Architecture:Any
Tags: decimal, MySQL, null, true, UPDATE

[6 Nov 2008 22:25] Chris Tysh
Description:
We have a table which has a DECIMAL column. If the DECIMAL column has a value of NULL, and we run an UPDATE query which attempts to set the same DECIMAL value to NULL, we receive a "1 Row Updated" (aka "true") result, when in fact it should be giving us a "No rows affected" (aka "false") result. 

How to repeat:
Create a table with two columns: ID (INT), Dollar (Decimal(4,2))

Insert a row into the table with the following values
ID: 1
Dollar: NULL

Then run this query
UPDATE table SET Dollar = NULL WHERE ID = 1

This should return "No rows affected" but it returns "1 row affected".
[7 Nov 2008 4:38] Valeriy Kravchuk
Thank you for a problem report. What exact version of MySQL server you had used? I can not repeat the behaviour decribed with 5.1.29:

mysql> create table tn(id int, value decimal(4,2));
Query OK, 0 rows affected (0.64 sec)

mysql> insert into tn values (1, null), (2,2);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tn where id=1;
+------+-------+
| id   | value |
+------+-------+
|    1 |  NULL |
+------+-------+
1 row in set (0.01 sec)

mysql> update tn set value=NULL;
Query OK, 1 row affected (0.05 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql> update tn set value=NULL;
Query OK, 0 rows affected (0.05 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.1.29-rc-community |
+---------------------+
1 row in set (0.00 sec)

Note 0 rows affected and changed above in case column already is NULL in the rows.
[7 Nov 2008 14:09] Chris Tysh
Sorry, we were a little off with our version number.

We are running MySQL 5.0.51a on Ubuntu 5.1
[7 Nov 2008 14:18] MySQL Verification Team
Do you get the same behavior using the mysql client instead of Query Browser?.
[7 Nov 2008 17:31] Chris Tysh
Yes, we get the same problem when executing this through the MySQL Client. We get this result: 
Matched: 1; Changed: 1; Warnings: 0;

We've tried it through the MySQL Client, Query Browser and a webpage running PHP/MySQL. We receive the same error through all 3 options.

We also ran this test:
1. Create a row with ID: 2, Dollar: 1.50
2. Run Query: UPDATE table SET Dollar = 1.50 WHERE ID = 2

This example returns "0 Rows Updated", as expected.
[9 Nov 2008 14:38] Peter Laursen
It is reproducable for me on 5.0.67, but not on 5.1.29 - both on Windows.

server 5.0.67
**************

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.67-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> drop table if exists tn;
Query OK, 0 rows affected (0.09 sec)
 
mysql> create table tn(id int, value decimal(4,2));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tn values (1, null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tn where id=1;
+------+-------+
| id   | value |
+------+-------+
|    1 |  NULL |
+------+-------+
1 row in set (0.00 sec)

mysql> update tn set value=NULL;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update tn set value=NULL;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update tn set value=NULL;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> drop table if exists tn;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tn(id int, value decimal(4,2));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tn values (1, null), (2,4.2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tn where id=1;
+------+-------+
| id   | value |
+------+-------+
|    1 |  NULL |
+------+-------+
1 row in set (0.00 sec)

mysql> update tn set value=NULL;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> update tn set value=NULL;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> update tn set value=NULL;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql>

server 5.1.29
**************

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.29-rc-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> drop table if exists tn;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table tn(id int, value decimal(4,2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tn values (1, null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tn where id=1;
+------+-------+
| id   | value |
+------+-------+
|    1 |  NULL |
+------+-------+
1 row in set (0.00 sec)

mysql> update tn set value=NULL;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update tn set value=NULL;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update tn set value=NULL;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> drop table if exists tn;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tn(id int, value decimal(4,2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tn values (1, null), (2,4.2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tn where id=1;
+------+-------+
| id   | value |
+------+-------+
|    1 |  NULL |
+------+-------+
1 row in set (0.00 sec)

mysql> update tn set value=NULL;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql> update tn set value=NULL;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql> update tn set value=NULL;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql>
[9 Nov 2008 15:39] Peter Laursen
So this is a server bug .. not a QB bug!
[10 Nov 2008 17:35] Chris Tysh
Okay I guess I'm a little confused. If the bug shows up when you're using certain versions of MySQL, doesn't that mean it is a bug in MySQL?
[10 Nov 2008 17:46] Peter Laursen
IMHO it is a bug in the MySQL 5.0 server. No such bug in server 5.1 and also not a bug in a client program (like Query Browser)!

FYI: I am not a MySQL person!
[11 Dec 2008 8:56] Valeriy Kravchuk
This is 5.0-specific server bug. Verified with 5.0.72 also:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.72-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table tn;
ERROR 1051 (42S02): Unknown table 'tn'
mysql> create table tn(id int, value decimal(4,2));
Query OK, 0 rows affected (0.20 sec)

mysql> insert into tn values (1, null), (2,2);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tn where id=1;
+------+-------+
| id   | value |
+------+-------+
|    1 |  NULL |
+------+-------+
1 row in set (0.05 sec)

mysql> update tn set value=NULL;
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> update tn set value=NULL;
Query OK, 2 rows affected (0.05 sec)
Rows matched: 2  Changed: 2  Warnings: 0
[11 Dec 2008 9:01] Valeriy Kravchuk
Looks like it is already fixed in current source though:

openxs@suse:/home2/openxs/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.76-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tn(id int, value decimal(4,2));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tn values (1, null), (2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tn where id=1;
+------+-------+
| id   | value |
+------+-------+
|    1 |  NULL |
+------+-------+
1 row in set (0.01 sec)

mysql> update tn set value=NULL;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql> update tn set value=NULL;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 2  Changed: 0  Warnings: 0
[12 Dec 2008 23:30] james bond
I was still experiencing something like this in 5.0.51a as well, when using the ON DUPLICATE KEY UPDATE extension I recieved row update counts of 1 (row added) even if the row had the same values as an already stored row.

I'm using java, and after endless web scouring, I finally found a workaround in the latest version of the MYSQL Connector/J (5.1.7) (the useAffectedRows property, check the changelogs for details). 

Hope this helps if someone hits a similar problem!
[22 Jan 2009 15:14] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63816

2735 Staale Smedseng	2009-01-22
      A verification test for Bug#40565 'Update Query Results 
      in "1 Row Affected" But Should Be "Zero Rows"'
[23 Jan 2009 12:55] Valeriy Kravchuk
Actually, it still fails with 5.0.76 with InnoDB, but works on 5.0.74 with MyISAM. Engine-specific problem.
[27 Jan 2009 14:34] Staale Smedseng
The problem appears to be specific for InnoDB and the 5.0 branch. (I.e., it is fixed in head of 5.1-bugteam and 6.0-bugteam branches for InnoDB engine, and the problem doesn't appear on any of these three branches for the MyISAM engine.)

Category set to InnoDB.
[28 Jan 2009 16:41] Mikhail Izioumtchenko
Marko figured out another bug related to NULL to NULL update.
I'm not sure if it's InnoDB bug or only InnoDB bug. 
If MySQL has the row before updating it, it probably should find
out on its own that there's no real need to update it.
[3 Jun 2009 8:57] Marko Mäkelä
This is a duplicate of Bug #39648, which was fixed in MySQL 5.1 but not in 5.0. Porting the fix to 5.0 fixed also this problem.
[11 Jun 2009 13:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/76118

2938 Satya B	2009-06-11
      Applying InnoDB snashot 5.1-ss5282, Add TestCase for BUG#40565
      
      Merge the test case from 5.0 to 5.1 for BUG#40565
      
      Detailed revision comments:
      
      r5233 | marko | 2009-06-03 15:12:44 +0300 (Wed, 03 Jun 2009) | 11 lines
      branches/5.1: Merge the test case from r5232 from branches/5.0:
        ------------------------------------------------------------------------
        r5232 | marko | 2009-06-03 14:31:04 +0300 (Wed, 03 Jun 2009) | 21 lines
      
        branches/5.0: Merge r3590 from branches/5.1 in order to fix Bug #40565
        (Update Query Results in "1 Row Affected" But Should Be "Zero Rows").
      
        Also, add a test case for Bug #40565.
      
        rb://128 approved by Heikki Tuuri
        ------------------------------------------------------------------------
      added:
        mysql-test/r/innodb_bug40565.result
        mysql-test/t/innodb_bug40565.test
[25 Jun 2009 9:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77133

2785 Satya B	2009-06-25
      Applying InnoDB snashot 5.0-ss5406, part 2. Fixes BUG#40565
      
      BUG#40565 - Update Query Results in "1 Row Affected" But Should Be "Zero Rows"
      
      Detailed revision comments:
      
      r5232 | marko | 2009-06-03 14:31:04 +0300 (Wed, 03 Jun 2009) | 21 lines
      branches/5.0: Merge r3590 from branches/5.1 in order to fix Bug #40565
      (Update Query Results in "1 Row Affected" But Should Be "Zero Rows").
      
      Also, add a test case for Bug #40565.
      
      rb://128 approved by Heikki Tuuri
        ------------------------------------------------------------------------
        r3590 | marko | 2008-12-18 15:33:36 +0200 (Thu, 18 Dec 2008) | 11 lines
      
        branches/5.1: When converting a record to MySQL format, copy the default
        column values for columns that are SQL NULL.  This addresses failures in
        row-based replication (Bug #39648).
      
        row_prebuilt_t: Add default_rec, for the default values of the columns in
        MySQL format.
      
        row_sel_store_mysql_rec(): Use prebuilt->default_rec instead of
        padding columns.
      
        rb://64 approved by Heikki Tuuri
        ------------------------------------------------------------------------
      added:
        mysql-test/r/innodb_bug40565.result
        mysql-test/t/innodb_bug40565.test
      modified:
        innobase/include/row0mysql.h
        innobase/row/row0mysql.c
        innobase/row/row0sel.c
        sql/ha_innodb.cc
[29 Jun 2009 12:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77451

2787 Satya B	2009-06-29
      Additional Fix for BUG#40565 - Update Query Results in "1 Row Affected" 
                                     But Should Be "Zero Rows"
      
      
      After applying the innodb snapshot 5.0-ss5406 for bug#40565, the windows push build
      tests failed because of the missing cast of void * pointer in row0sel.c file
      
      Informed the innodb developers and received patch by email.
      modified:
        innobase/row/row0sel.c
[7 Jul 2009 7:52] Bugs System
Pushed into 5.0.84 (revid:joro@sun.com-20090707074938-ksah1ibn0vs92cem) (version source revid:satya.bn@sun.com-20090629122722-gpio6e9de74ztgdw) (merge vers: 5.0.84) (pib:11)
[8 Jul 2009 13:30] Bugs System
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:satya.bn@sun.com-20090629130311-dy1x480i0rx49cen) (merge vers: 5.1.37) (pib:11)
[9 Jul 2009 7:35] Bugs System
Pushed into 5.0.84 (revid:joro@sun.com-20090707074938-ksah1ibn0vs92cem) (version source revid:satya.bn@sun.com-20090629122722-gpio6e9de74ztgdw) (merge vers: 5.0.84) (pib:11)
[9 Jul 2009 7:37] Bugs System
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:satya.bn@sun.com-20090629130311-dy1x480i0rx49cen) (merge vers: 5.1.37) (pib:11)
[10 Jul 2009 11:21] Bugs System
Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:satya.bn@sun.com-20090629130606-3o5j3wii9kta3z05) (merge vers: 5.4.4-alpha) (pib:11)
[4 Aug 2009 0:02] Paul DuBois
Noted in 5.0.84, 5.1.37, 5.4.4 changelogs.

Some UPDATE statements that affected no rows returned a rows-affected
count of one.
[4 Aug 2009 5:40] Satya B
The patch is not really pushed to 5.4. Did a NULL merge only.
[12 Aug 2009 22:49] Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 2:04] Paul DuBois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:45] Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48] Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:32] Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[8 Oct 2009 19:39] Paul DuBois
The 5.4 fix has been pushed to 5.4.2.
[5 May 2010 15:25] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 16:53] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[28 May 2010 5:52] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:21] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:49] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 23:04] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[15 Jun 2010 8:22] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:39] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 11:53] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:30] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:18] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)