MySQL Bugs: #30639: limit offset,rowcount wraps when rowcount >= 2^32 in windows

Bug #30639	limit offset,rowcount wraps when rowcount >= 2^32 in windows
Submitted:	26 Aug 2007 10:56	Modified:	22 Oct 2007 17:20
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.0.46	OS:	Windows
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
On windows, the LIMIT clause appears to wrap at 2^32.  This can lead to malfunctioning SQL queries!

Windows 5.0.46:
-----------------
mysql> drop table if exists `tbl1`;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> create table `tbl1`(`a` tinyint unsigned not null,key(`a`))engine=myisam;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into `tbl1`(`a`) values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.14 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from `tbl1` limit 2;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.01 sec)

mysql> select * from `tbl1` limit 2,4294967296;
Empty set (0.00 sec)

mysql>
mysql> select * from `tbl1` limit 2,4294967297;
+---+
| a |
+---+
| 3 |
+---+
1 row in set (0.00 sec)

Linux 5.0.48:
---------------
mysql> drop table if exists `tbl1`;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> create table `tbl1`(`a` tinyint unsigned not null,key(`a`))engine=myisam;
Query OK, 0 rows affected (2.17 sec)

mysql> insert into `tbl1`(`a`) values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.63 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from `tbl1` limit 2;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.02 sec)

mysql> select * from `tbl1` limit 2,4294967296;
+---+
| a |
+---+
| 3 |
| 4 |
| 5 |
+---+
3 rows in set (0.00 sec)

mysql> select * from `tbl1` limit 2,4294967297;
+---+
| a |
+---+
| 3 |
| 4 |
| 5 |
+---+
3 rows in set (0.00 sec)

How to repeat:
drop table if exists `tbl1`;
create table `tbl1`(`a` tinyint unsigned not null,key(`a`))engine=myisam;
insert into `tbl1`(`a`) values (1),(2),(3),(4),(5);
select * from `tbl1` limit 2;
select * from `tbl1` limit 2,4294967296;
select * from `tbl1` limit 2,4294967297;

Suggested fix:
I see no reason windows versions cannot use ulonglong values like linux obviously does?

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/33513

ChangeSet@1.2583, 2007-08-31 10:59:21+03:00, gkodinov@magare.gmz +4 -0
  Bug #30639: limit offset,rowcount wraps when rowcount >= 2^32 in windows
  
   The parser uses ulonglong to store the LIMIT number. This number
   then is stored into a variable of type ha_rows. ha_rows is either
   4 or 8 byte depending on the BIG_TABLES define from config.h
   So an overflow may occur (and LIMIT becomes zero) while storing an
   ulonglong value in ha_rows.
   Fixed by :
    1. Using the maximum possible value for ha_rows on overflow
    2. Defining BIG_TABLES for the windows builds (to match the others)

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/34405

ChangeSet@1.2583, 2007-09-19 17:47:52+03:00, gkodinov@magare.gmz +4 -0
  Bug #30639: limit offset,rowcount wraps when rowcount >= 2^32 in windows
  
   The parser uses ulonglong to store the LIMIT number. This number
   then is stored into a variable of type ha_rows. ha_rows is either
   4 or 8 byte depending on the BIG_TABLES define from config.h
   So an overflow may occur (and LIMIT becomes zero) while storing an
   ulonglong value in ha_rows.
   Fixed by :
    1. Using the maximum possible value for ha_rows on overflow
    2. Defining BIG_TABLES for the windows builds (to match the others)

Pushed into 5.1.23-beta

Noted in 5.1.23 changelog.

On Windows, LIMIT arguments greater than 2^32 did not work correctly.