Re: Strange behaviors with ranges - Mailing list pgsql-general

From Laurenz Albe
Subject Re: Strange behaviors with ranges
Date
Msg-id 9e38681b60ada2a1161cc278344678ca563bce83.camel@cybertec.at
Whole thread Raw
List pgsql-general
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



pgsql-general by date:

Previous
From: Justin Clift
Date:
Subject: Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
Next
From: Greg Sabino Mullane
Date:
Subject: Re: Pgbackrest specifying the default DB necessary/correct way ?