Re: Issues with generate_series using integer boundaries

The proposed generate_series(1,9,-1) behavior seems unusual.  I think it
should throw a warning if the step direction and the start-end directions do
not match.  Alternatively, the series generated could go from 9 -> 1 instead
of returning an empty series (basically the first two arguments are simply
bounds and the step sign determines which is upper and which is lower).  The
result where the set contains the sole member { 1 } makes sense to me in
that you wanted to start with 1 and then increment by -1 until you are
either less-than 1 or greater-than 9; which is the same thing you are doing
when you have a positive step value and always treat the first argument as
the initial value.  With that behavior you are ALWAYS returning the first
argument, then stepping, then returning any other argument that still fall
within the range.  If you do not return the first argument you are
implicitly starting with zero (0) and incrementing and then seeing whether
the first step falls inside the specified range.

David J

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Thom Brown
Sent: Thursday, February 03, 2011 8:58 AM
To: Alban Hertroys
Cc: Tom Lane; PGSQL Mailing List
Subject: Re: [GENERAL] Issues with generate_series using integer boundaries

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)

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