Re: Strange behaviors with ranges

On Tue, 2024-08-27 at 19:29 +0200, Jean-Christophe Boggio wrote:
> I have 2 very confusing behaviors when using ranges.
>
> It all started with this query:
>
>      WITH rangespaliers AS (
>          SELECT numrange( qtep1+1   , qtep2,  '[]') AS rangep FROM
> paliers JOIN tmp_limitcontrats USING(idcontrat)
> --        SELECT numrange( qtep1+1   , qtep2,  '[]') AS rangep FROM
> paliers WHERE idcontrat=1003
>      )
>      ,rangespaliers2 AS (
>          select *
>          FROM rangespaliers
>          WHERE rangep <> NUMRANGE(NULL, NULL) -- ERROR IS HERE
>      )
>      select * from rangespaliers2;
>
> When I run this query, I get the error "Range lower bound must be less
> than or equal to range upper bound".
>
> (a) If I comment out the line marked "ERROR IS HERE", I don't have an
> error (but I'm missing the filter of course).
>
> (b) Also, if I uncomment line 3 and comment out line 2, I get the
> correct behavior. Very strange thing is that tmp_limitcontrats has only
> one row which contains "idcontrat=1003".
>
> Now, in that table "paliers", the line for idcontrat=1003 has value NULL
> for both qtep1 and qtep2. So the final behavior should be an empty
> result set.

The explanation is in the execution plans.

With your sample data, the plan looks like

Hash Join
Hash Cond: (tmp_limitcontrats.idcontrat = paliers.idcontrat)
-> Seq Scan on tmp_limitcontrats
-> Hash
-> Seq Scan on paliers
Filter: (numrange(((qtep1 + 1))::numeric, (qtep2)::numeric) <> '(,)'::numrange)

If you remove the WHERE condition from the second CTE, the plan becomes

Hash Join
Hash Cond: (tmp_limitcontrats.idcontrat = paliers.idcontrat)
-> Seq Scan on tmp_limitcontrats
-> Hash
-> Seq Scan on paliers

In the second case, "rangep" is never used, so PostgreSQL optimizes the
query so that it does not calculate the column at all, which avoids the
runtime error.

Yours,
Laurenz Albe