BUG #6139: LIMIT doesn't return correct result when the value is huge

The following bug has been logged online:

Bug reference: 6139
Logged by: Hitoshi Harada
Email address: umi(dot)tanuki(at)gmail(dot)com
PostgreSQL version: 8.2+
Operating system: Any
Description: LIMIT doesn't return correct result when the value is
huge
Details:

db1=# select count(*) from test_xy;
count
-------
31
(1 row)

db1=# select * from test_xy order by x LIMIT 9223372036854775807 OFFSET 6;
gid | x | y
-----+--------------------+--------------------
13 | -0.591943957968476 | -0.481611208406305
(1 row)

db1=# select * from test_xy order by x LIMIT 9223372036854775806 OFFSET 6;
gid | x | y
-----+--------------------+--------------------
13 | -0.591943957968476 | -0.481611208406305
(1 row)

db1=# select * from test_xy order by x LIMIT 9223 OFFSET 6;
gid | x | y
-----+---------------------+---------------------
13 | -0.591943957968476 | -0.481611208406305
12 | -0.577933450087566 | -0.513134851138354
15 | -0.476357267950963 | -0.502626970227671
6 | -0.227670753064799 | 0.32399299474606
8 | -0.220665499124343 | 0.373029772329247
7 | -0.199649737302977 | 0.345008756567426
11 | -0.182136602451839 | 0.281961471103328
10 | -0.115586690017513 | 0.2784588441331
9 | -0.0980735551663747 | 0.197898423817863
16 | 0.0980735551663749 | -0.113835376532399
19 | 0.353765323992995 | 0.180385288966725
18 | 0.413309982486865 | 0.152364273204904
17 | 0.434325744308231 | 0.169877408056042
21 | 0.458844133099825 | 0.145359019264448
20 | 0.486865148861646 | 0.0928196147110332
24 | 1.50963222416813 | -0.0507880910683012
23 | 1.50963222416813 | -0.0928196147110333
27 | 1.55516637478109 | 0.544658493870403
31 | 1.55516637478109 | 0.660245183887916
28 | 1.59369527145359 | 0.737302977232925
29 | 1.64273204903678 | 0.618213660245184
db1=# select * from test_xy order by x LIMIT pow(2, 63);
ERROR: bigint out of range
ERROR: bigint out of range

Maybe a parser converts literal unexpectedly?

On Tue, Aug 2, 2011 at 00:25, Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> wrote:

> db1=# select * from test_xy order by x LIMIT 9223372036854775807 OFFSET 6;
[ ...]

> db1=# select * from test_xy order by x LIMIT pow(2, 63);
> ERROR:  bigint out of range
> ERROR:  bigint out of range
>
> Maybe a parser converts literal unexpectedly?

pow(2, 63) != 9223372036854775807, pow(2, 63) - 1 does :-). On top of
that pow(2, 63) seems to default to the double variant of pow() which
causes rounding issues. You probably want LIMIT pow(2, 63::numeric)-1.

On 02.08.2011 09:25, Hitoshi Harada wrote:
> db1=# select count(*) from test_xy;
> count
> -------
> 31
> (1 row)
>
> db1=# select * from test_xy order by x LIMIT 9223372036854775807 OFFSET 6;
> gid | x | y
> -----+--------------------+--------------------
> 13 | -0.591943957968476 | -0.481611208406305
> (1 row)

The bug seems to occur when LIMIT + OFFSET >= 2^63. In ExecLimit
function, we check if current position >= offset + limit, and that
overflows.

I'll commit the attached patch to fix that. Thanks for the report

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com