MySQL Bugs: #21818: Return value of ROW_COUNT() is incorrect for ALTER TABLE, LOAD DATA

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

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.

Thank you for the bug report.

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

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

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

Approved by email with several comments.

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.

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.

Pushed into mysql-trunk-bugfixing.

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.

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)

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100520100140-5bzrtadw4w419i3m) (version source revid:alik@sun.com-20100520100049-1njm09rkvnhmysnr) (pib:16)

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.