Bug #33368 rows count wrapped when inserting 2^31 or more records

• Submitted: 19 Dec 2007 14:33
• Modified: 20 Dec 2007 12:33
• Reporter: Giuseppe Maxia
• Status: Verified
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.1.23
• OS: Linux
• CPU Architecture: Any

[19 Dec 2007 14:33] Giuseppe Maxia

Description:
When you insert 2^31 (2,147,483,648) rows or more, the rows count is wrapped.

INSERT INTO t1 select 1 from v1024 a, v1024 b, v1024 c, v2 ;

INSERT INTO t1 select 1 from v1024 a, v1024 b, v1024 c, v2 ;
Query OK, -2147483648 rows affected (32 min 12.83 sec)
Records: -2147483648  Duplicates: 0  Warnings: 0

If we insert one row less than that, the row count is correct.

insert into t1 select 1 from v1024 a, v1024 b, v1024 c, v2 limit 2147483647;
Query OK, 2147483647 rows affected (32 min 14.31 sec)
Records: 2147483647  Duplicates: 0  Warnings: 0

Using MySQL Proxy, you can inspect the result before is sent to the client, and appreciate the fact that the value comes incorrect from the server.

How to repeat:
drop table if exists t1;
drop view if exists v2,v4,v1024;

create table t1 (
 id tinyint not null
);

CREATE VIEW v2 AS
 select NULL
 union all select NULL;

CREATE VIEW v4 AS
 select NULL
 union all select NULL
 union all select NULL
 union all select NULL;

CREATE VIEW v1024 AS
 select null from v4 a, v4 b, v4 c, v4 d, v4 e;

INSERT INTO t1 select 1 from v1024 a, v1024 b, v1024 c, v2 ;

# to inspect the result with MySQL Proxy, use the following Lua script:
--
function read_query( packet )
    if packet:byte() ~= proxy.COM_QUERY then
        return
    end
    proxy.queries:append(1, packet) 
    return proxy.PROXY_SEND_QUERY
end

function read_query_result (inj)
    local res = assert(inj.resultset)
    if (res.affected_rows) then
        print('         query: ' .. inj.query)
        print ('affected rows: ' .. res.affected_rows)
    end
end

[19 Dec 2007 14:37] Valeriy Kravchuk

Thank you for a bug report. Do you use 32-bit or 64-bit OS and MySQL server?

[19 Dec 2007 14:44] Giuseppe Maxia

I am using Linux 32 bit.

[20 Dec 2007 2:09] MySQL Verification Team

On Windows:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-rc-nt Source distribution

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

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> drop view if exists v2,v4,v1024;
Query OK, 0 rows affected, 3 warnings (0.01 sec)

mysql>
mysql> create table t1 (
    ->  id tinyint not null
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> CREATE VIEW v2 AS
    ->  select NULL
    ->  union all select NULL;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> CREATE VIEW v4 AS
    ->  select NULL
    ->  union all select NULL
    ->  union all select NULL
    ->  union all select NULL;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> CREATE VIEW v1024 AS
    ->  select null from v4 a, v4 b, v4 c, v4 d, v4 e;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO t1 select 1 from v1024 a, v1024 b, v1024 c, v2 ;
Query OK, -2147483648 rows affected (1 hour 10 min 47.70 sec)
Records: -2147483648  Duplicates: 0  Warnings: 0

mysql>

[20 Dec 2007 12:33] MySQL Verification Team

Thank you for the bug report.