Bug #37075 offset of limit clause might be truncated on 32-bits server w/o big tables
Submitted: 29 May 2008 18:09 Modified: 10 Nov 2008 18:03
Reporter: BJ Dierkes Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.24-rc, 6.0.7, 5.1 bzr OS:Linux (Redhat EL5 Server i386, Mac OSX)
Assigned to: Davi Arnaut
Triage: D3 (Medium)

[29 May 2008 18:09] BJ Dierkes
Description:
main.limit test case fails when running 'make test'.  The output from mysql test is:

[ pass ]            555
main.key_diff                  [ pass ]             58
main.key_primary               
[ pass ]            125
main.keywords                  
[ pass ]            206
main.kill                      
[ pass ]          23017
main.limit                     
[ fail ]
--- /builddir/build/BUILD/mysql-5.1.24-rc/mysql-test/r/limit.result     2008-04-08 14:54:47.000000000 +0300
+++ /builddir/build/BUILD/mysql-5.1.24-rc/mysql-test/r/limit.reject     2008-05-29 19:46:17.000000000 +0300
@@ -107,6 +107,7 @@
 set @a=14632475938453979136;
 execute s using @a, @a;
 1
+1
 set @a=-14632475938453979136;
 execute s using @a, @a;
 ERROR HY000: Incorrect arguments to EXECUTE
mysqltest: Result length mismatch
Warnings from just before the error:
Error 1292 Truncated incorrect DECIMAL value: ''
Aborting: main.limit failed in default mode. 
To continue, re-run with '--force'.
Stopping All Servers
make: *** [test-ns] Error 1

How to repeat:
make && make test

Suggested fix:
unsure.
[7 Jun 2008 10:57] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior. Please provide configure options you used.
[25 Jun 2008 16:37] BJ Dierkes
This was also verified again on 5.1.25-rc (el5.i386):

./configure --build=i686-redhat-linux-gnu 
--host=i686-redhat-linux-gnu 
--target=i386-redhat-linux-gnu 
--program-prefix= 
--prefix=/usr 
--exec-prefix=/usr 
--bindir=/usr/bin 
--sbindir=/usr/sbin 
--sysconfdir=/etc
--datadir=/usr/share 
--includedir=/usr/include 
--libdir=/usr/lib 
--libexecdir=/usr/libexec 
--localstatedir=/var 
--sharedstatedir=/usr/com 
--mandir=/usr/share/man 
--infodir=/usr/share/info 
--with-readline 
--with-ssl 
--without-debug 
--enable-shared 
--with-bench 
--localstatedir=/var/lib/mysql 
--with-unix-socket-path=/var/lib/mysql/mysql.sock 
--with-mysqld-user=mysql 
--with-extra-charsets=all 
--with-innodb 
--with-berkeley-db 
--enable-local-infile 
--enable-largefile 
--enable-thread-safe-client 
--disable-dependency-tracking 
--with-plugin-partition 
--with-plugin-csv 
--with-plugin-ndbcluster 
--with-named-thread-libs=-lpthread
[25 Jun 2008 18:43] Sveta Smirnova
Thnak you for the feedback.

I still can not repeat described behavior. Please try with current version 5.1.25 and inform us if problem still exists on your side.
[26 Jun 2008 21:35] Bill Karwin
I can reproduce this using the source checked out from Bazaar.

Platform: Mac OS X 10.5.3 Intel (apple-darwin9.3.0)

MySQL 5.0.66 (revno 2644):      PASS
MySQL 5.1.27 (revno 2662):      FAIL
MySQL 6.0.6-alpha (revno 2671): FAIL

Steps to reproduce:

$ configure --with-innodb --with-ssl
$ make
$ cd mysql-test
$ perl mysql-test-run limit

Output for MySQL 5.1.27 and 6.0.6-alpha:

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

main.limit                     [ fail ]

--- /Users/bill/workspace/bzr/mysql-server/mysql-test/r/limit.result	2008-06-26 01:04:04.000000000 +0300
+++ /Users/bill/workspace/bzr/mysql-server/mysql-test/r/limit.reject	2008-06-26 23:47:59.000000000 +0300
@@ -107,6 +107,7 @@
 set @a=14632475938453979136;
 execute s using @a, @a;
 1
+1
 set @a=-14632475938453979136;
 execute s using @a, @a;
 ERROR HY000: Incorrect arguments to EXECUTE

mysqltest: Result length mismatch

Warnings from just before the error:
Error 1292 Truncated incorrect DECIMAL value: ''

Aborting: main.limit failed in default mode. 
To continue, re-run with '--force'.
[1 Aug 2008 18:41] Olav Sandstå
I too get this error when running main.limit using Ubuntu 8.04 using the source code from the mysql-6.0-falcon tree (6.0.7 alpha):

main.limit                     [ fail ]

--- /home/olav/mysql/develop/falcon-nb/mysql-test/r/limit.result	2008-07-29 18:49:49.000000000 +0300
+++ /home/olav/mysql/develop/falcon-nb/mysql-test/r/limit.reject	2008-08-01 21:18:57.000000000 +0300
@@ -107,6 +107,7 @@
 set @a=14632475938453979136;
 execute s using @a, @a;
 1
+1
 set @a=-14632475938453979136;
 execute s using @a, @a;
 ERROR HY000: Incorrect arguments to EXECUTE

mysqltest: Result length mismatch

Warnings from just before the error:
Warning 1292 Truncated incorrect DECIMAL value: ''

Aborting: main.limit failed in default mode.
[1 Aug 2008 19:47] Sveta Smirnova
Thank you for the report.

Verified with configure provided by Olav (configure --with-innodb --with-ssl)
[10 Oct 2008 18:11] Davi Arnaut
A variation of Bug#30639
[10 Oct 2008 18:40] Davi Arnaut
To reliably repeat, build a 32-bit MySQL with the configure option --without-big-tables.

Test case:

select 1 as a limit 4294967296,10;

This causes 4294967296 to be truncated to 0 since offset_limit_cnt (ha_rows) is 32-bits integer and the limit offset is a 64-bits integer (st_select_lex_unit::set_limit). Casting a larger type to a narrower one causes the excess bits on the left to be discarded.
[10 Oct 2008 20: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/56058

2772 Davi Arnaut	2008-10-10
      Bug#37075: offset of limit clause might be truncated to 0 on 32-bits server w/o big tables
      
      The problem is that the offset argument of the limit clause
      might be truncated to 0 on 32-bits server built without big
      tables support. The truncation was happening because the
      original 64-bits long argument was being cast to a 32-bits
      (ha_rows) offset counter.
      
      The solution is to check if the conversing resulted in value
      truncation and if so, the offset is set to the maximum possible
      value that can fit on the type.
[15 Oct 2008 21:35] 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/56304

2706 Davi Arnaut	2008-10-15
      Bug#37075: offset of limit clause might be truncated on 32-bits server w/o big tables
      
      The problem is that the offset argument of the limit clause
      might be truncated on a 32-bits server built without big
      tables support. The truncation was happening because the
      original 64-bits long argument was being cast to a 32-bits
      (ha_rows) offset counter.
      
      The solution is to check if the conversing resulted in value
      truncation and if so, the offset is set to the maximum possible
      value that can fit on the type.
[15 Oct 2008 22:22] Davi Arnaut
Queued to 5.0-bugteam
[24 Oct 2008 8:42] Bugs System
Pushed into 5.0.72  (revid:davi.arnaut@sun.com-20081015213451-migc3adfkx1sbxdy) (version source revid:kgeorge@mysql.com-20081020132607-0xfdc16b9p1xrd83) (pib:5)
[24 Oct 2008 20:21] Paul Dubois
Noted in 5.0.72 changelog.

On a 32-bit server built without big tables support, the offset
argument in a LIMIT clause might be truncated due to a 64-bit to
32-bit cast.

Setting report to NDI pending push into 5.1.x, 6.0.x.
[10 Nov 2008 10:51] Bugs System
Pushed into 6.0.8-alpha  (revid:davi.arnaut@sun.com-20081015213451-migc3adfkx1sbxdy) (version source revid:davi.arnaut@sun.com-20081016021316-p7etwjgausmhe08d) (pib:5)
[10 Nov 2008 11:35] Bugs System
Pushed into 5.1.30  (revid:davi.arnaut@sun.com-20081015213451-migc3adfkx1sbxdy) (version source revid:davi.arnaut@sun.com-20081016015056-tii2mzf5tirlcshs) (pib:5)
[10 Nov 2008 18:03] Paul Dubois
Noted in 5.1.31, 6.0.8 changelogs.
[11 Nov 2008 16:35] Paul Dubois
6.0.9 changelog, not 6.0.8.
[19 Jan 2009 11:34] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:10] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:16] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)