Re: Issues with generate_series using integer boundaries

On 3 February 2011 13:58, Thom Brown <thom(at)linux(dot)com> wrote:
> On 3 February 2011 13:32, Thom Brown <thom(at)linux(dot)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