Horton

Re: Issues with generate_series using integer boundaries

Thom Brown <thom@linux.com>

View thread
From: Thom Brown <thom@linux.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, PGSQL Mailing List <pgsql-general@postgresql.org>
Date: 2011-02-04T12:32:33Z
Lists: pgsql-hackers

Attachments

On 3 February 2011 13:58, Thom Brown <thom@linux.com> wrote:
> On 3 February 2011 13:32, Thom Brown <thom@linux.com> wrote:
>> Actually, further testing indicates this causes other problems:
>>
>> postgres=# SELECT x FROM generate_series(1, 9,-1) AS a(x);
>>  x
>> ---
>>  1
>> (1 row)
>>
>> Should return no rows.
>>
>> postgres=# SELECT x FROM generate_series(1, 9,3) AS a(x);
>>  x
>> ----
>>  1
>>  4
>>  7
>>  10
>> (4 rows)
>>
>> Should return 3 rows.
>
> Still messy code, but the attached patch does the job now:
>
> postgres=# SELECT x FROM
> generate_series(2147483643::int4,2147483647::int4) AS a(x);
>     x
> ------------
>  2147483643
>  2147483644
>  2147483645
>  2147483646
>  2147483647
> (5 rows)
>
> postgres=# SELECT x FROM
> generate_series(2147483642::int4,2147483647::int4, 2) AS a(x);
>     x
> ------------
>  2147483642
>  2147483644
>  2147483646
> (3 rows)
>
> postgres=# SELECT x FROM
> generate_series(2147483643::int4,2147483647::int4, 6) AS a(x);
>     x
> ------------
>  2147483643
> (1 row)
>
> postgres=# SELECT x FROM generate_series((-2147483643)::int4,
> (-2147483648)::int4, -1) AS a(x);
>      x
> -------------
>  -2147483643
>  -2147483644
>  -2147483645
>  -2147483646
>  -2147483647
>  -2147483648
> (6 rows)
>
> postgres=# SELECT x FROM generate_series(1, 9,-1) AS a(x);
>  x
> ---
> (0 rows)
>
> postgres=# SELECT x FROM generate_series(1, 9,3) AS a(x);
>  x
> ---
>  1
>  4
>  7
> (3 rows)
>

Copying to -hackers.

The issue is that generate_series will not return if the series hits
either the upper or lower boundary during increment, or goes beyond
it.  The attached patch fixes this behaviour, but should probably be
done a better way.  The first 3 examples above will not return.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935