MySQL Bugs: #40565: Update Query Results in "1 Row Affected" But Should Be "Zero Rows"

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

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

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.

Sorry, we were a little off with our version number.

We are running MySQL 5.0.51a on Ubuntu 5.1

Do you get the same behavior using the mysql client instead of Query Browser?.

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.

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>

So this is a server bug .. not a QB bug!

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?

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!

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

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

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!

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"'

Actually, it still fails with 5.0.76 with InnoDB, but works on 5.0.74 with MyISAM. Engine-specific problem.

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.

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.

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.

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

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

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

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)

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)

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)

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)

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)

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.

The patch is not really pushed to 5.4. Did a NULL merge only.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

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)

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)

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)

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)

The 5.4 fix has been pushed to 5.4.2.

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

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)

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

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)

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)

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)