Bug #21818 Return value of ROW_COUNT() is incorrect for ALTER TABLE, LOAD DATA
Submitted: 24 Aug 2006 19:55 Modified: 17 Jun 2010 19:08
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0/5.1BK OS:Linux (Suse Linux)
Assigned to: Alexander Nozdrin CPU Architecture:Any

[24 Aug 2006 19:55] Konstantin Osipov
Description:
MySQL documentation says:
ROW_COUNT() returns the number of rows updated, inserted, or deleted by the preceding statement. This is the same as the row count that the mysql client displays and the value from the mysql_affected_rows() C API function.

For a number of SQL statements return value of ROW_COUNT() is not the same as the value from mysql_affected_rows().

Examples:

mysql> select * from t1 into outfile "foo.txt";
Query OK, 8 rows affected (0.00 sec)
mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

mysql> select a from t1 limit 1 into @a;
Query OK, 1 row affected (0.00 sec)
mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

mysql> create database test; select row_count();
Query OK, 1 row affected (0.00 sec)
+-------------+
| row_count() |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

mysql> load data infile '/home/kostja/work/mysql-5.0-root/mysql-test/std_data/words.dat' into table t1 (a);
Query OK, 70 rows affected (0.03 sec)
Records: 70  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|          -1 | 
+-------------+
1 row in set (0.00 sec)

mysql> alter table t1 add column b varchar(255);
Query OK, 70 rows affected (0.01 sec)
Records: 70  Duplicates: 0  Warnings: 0
mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

How to repeat:
See description.

Suggested fix:
Remove the "affected rows" argument from send_ok and always use thd->row_count_func. This will force developers to assign thd->row_count_func before calling send_ok.
[24 Aug 2006 20:02] MySQL Verification Team
Thank you for the bug report.
[17 Oct 2006 15:19] 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/13808

ChangeSet@1.2283, 2006-10-17 17:17:16+02:00, andrey@example.com +17 -0
  Fix for bug#21818 "Return value of ROW_COUNT() is incorrect for 
  ALTER TABLE, LOAD DATA"
  
  This patch refactores the server code so the only place, where the
  affected number of rows of the last statement is stored, is 
  THD::row_count_func
[17 Oct 2006 15:46] 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/13811

ChangeSet@1.2283, 2006-10-17 17:45:21+02:00, andrey@example.com +17 -0
  Fix for bug#21818 "Return value of ROW_COUNT() is incorrect for 
  ALTER TABLE, LOAD DATA"
  
  This patch refactores the server code so the only place, where the
  affected number of rows of the last statement is stored, is 
  THD::row_count_func
[13 Nov 2006 15: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/15242

ChangeSet@1.2237, 2006-11-13 16:10:57+01:00, andrey@example.com +16 -0
  Fix for bug#21818 "Return value of ROW_COUNT() is incorrect for 
  ALTER TABLE, LOAD DATA"
    
  This patch refactores the server code so the only place, where the
  affected number of rows of the last statement is stored, is 
  THD::row_count_func
  
  This patch includes a back-port from 5.1 of sql_commands_flags
[25 Jan 2007 22:54] Konstantin Osipov
Approved by email with several comments.
[17 Feb 2007 13:31] Konstantin Osipov
Tomash, could you please implement my review comments and push the approved patch?
I do not seem to have time for this :(
Please contact me for any additional information you may need.
[14 May 2010 5:29] 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/108286

3043 Alexander Nozdrin	2010-05-14
      Patch for Bug#21818 (Return value of ROW_COUNT() is incorrect
      for ALTER TABLE, LOAD DATA).
      
      ROW_COUNT is now assigned according to the following rules:
      
        - In my_ok():
          - for DML statements: to the number of affected rows;
          - for DDL statements: to 0.
      
        - In my_eof(): to -1 to indicate that there was a result set.
      
          We derive this semantics from the JDBC specification, where int
          java.sql.Statement.getUpdateCount() is defined to (sic) "return the
          current result as an update count; if the result is a ResultSet
          object or there are no more results, -1 is returned".
      
        - In my_error(): to -1 to be compatible with the MySQL C API and
          MySQL ODBC driver.
      
        - For SIGNAL statements: to 0 per WL#2110 specification. Zero is used
          since that's the "default" value of ROW_COUNT in the diagnostics area.
     @ sql/protocol.cc
        Fix a typo.
     @ sql/sql_class.h
        - Introduce THD::get_row_count_func() / THD::set_row_count_func();
        - Remove the CF_HAS_ROW_COUNT define
     @ sql/sql_parse.cc
        CF_HAS_ROW_COUNT was eliminated.
[14 May 2010 5:41] Alexander Nozdrin
Pushed into mysql-trunk-bugfixing.
[14 May 2010 15:02] 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/108361

3878 Alexander Nozdrin	2010-05-14
      Post-fix for Bug#21818.
[20 May 2010 10:04] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100520100225-oe4iuu5kuzsx0knq) (version source revid:alik@sun.com-20100520100057-rmn5y3o3ij726bm7) (merge vers: 6.0.14-alpha) (pib:16)
[20 May 2010 10:05] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100520100041-rf8ujv598zf86wjt) (version source revid:alik@sun.com-20100520100041-rf8ujv598zf86wjt) (merge vers: 5.5.5-m3) (pib:16)
[20 May 2010 10:06] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100520100140-5bzrtadw4w419i3m) (version source revid:alik@sun.com-20100520100049-1njm09rkvnhmysnr) (pib:16)
[17 Jun 2010 19:08] Paul Dubois
Noted in 5.5.5, 6.0.14 changelogs.

ROW_COUNT() returned a meaningful value only for some DML statements.
Now it returns a value as follows:

* DDL statements: 0. This applies to statements such as CREATE TABLE or
DROP TABLE.

* DML statements other than SELECT: The number of affected rows.
This applies to statements such as UPDATE, INSERT, or DELETE (as
before), but now also to statements such as ALTER TABLE and LOAD
DATA INFILE.

* SELECT: -1 if the statement returns a result set, or the number
of rows "affected" if it does not (for example, if it uses INTO)

mysql> SELECT 'abc'; SELECT ROW_COUNT();
+-----+
| abc |
+-----+
| abc |
+-----+
1 row in set (0.00 sec)

+-------------+
| ROW_COUNT() |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT 'abc' INTO @x; SELECT ROW_COUNT();
Query OK, 1 row affected (0.00 sec)

+-------------+
| ROW_COUNT() |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

* SIGNAL statements: 0.