public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Jean-Christophe Boggio <[email protected]>
To: [email protected]
Subject: Re: Strange behaviors with ranges
Date: Wed, 28 Aug 2024 10:56:34 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Strange behaviors with ranges
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox