Thread: 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.tanuki@gmail.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.tanuki@gmail.com> wrote:

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

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

pow(2, 63) !=3D 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