Strange behaviors with ranges

On 8/27/24 10:29 AM, Jean-Christophe Boggio wrote:

Hello,

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".

What does:

SELECT numrange( qtep1+1 , qtep2, '[]') AS rangep FROM paliers WHERE
idcontrat=1003

return?

This fails on PG 16.4 and 15.7

Thanks a lot for your enlightenment.

--
Adrian Klaver
adrian.klaver@aklaver.com

What does:

SELECT numrange( qtep1+1   , qtep2,  '[]') AS rangep FROM paliers
WHERE idcontrat=1003

return?

It returns:

(,)

(as expected)

I guess this query comes back non-empty:

SELECT * FROM paliers JOIN tmp_limitcontrats USING(idcontrat) WHERE
qtep1 >= qtep2

This would then lead somewhere to this expression numrange(3,2)

Check out idpalier=805

On Tue, Aug 27, 2024 at 7:37 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 8/27/24 10:29, 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".

The issue is the order-of-operations used by the planner. If I put EXPLAIN on your last query, I see:

Hash Join (cost=16.64..109.90 rows=2410 width=64)
Hash Cond: (tmp_limitcontrats.idcontrat = paliers.idcontrat)
-> Seq Scan on tmp_limitcontrats (cost=0.00..35.50 rows=2550 width=4)
-> Hash (cost=14.27..14.27 rows=189 width=12)
-> Seq Scan on paliers (cost=0.00..14.27 rows=189 width=12)
Filter: (numrange(((qtep1 + 1))::numeric, (qtep2)::numeric) <> '(,)'::numrange)

So we are applying that filter to every row in paliers, not just the one with idcontrat = 1003.

Indeed this simplified version also fails:

SELECT numrange(qtep1+1,qtep2) as rangep FROM paliers;

Yours,

--
Paul ~{:-)
pj@illuminatedcomputing.com

Le 27/08/2024 à 19:51, Torsten Förtsch a écrit :

I guess this query comes back non-empty:

SELECT * FROM paliers JOIN tmp_limitcontrats USING(idcontrat) WHERE
qtep1 >= qtep2

Yes, it is empty if I keep tmp_limitcontrats to idcontrat=1003

Otherwise, you are right, there are irregular data but not that I'm
concerned with in that particular case.

On 8/27/24 11:13 AM, Jean-Christophe BOGGIO wrote:

Le 27/08/2024 à 19:51, Torsten Förtsch a écrit :

I guess this query comes back non-empty:

SELECT * FROM paliers JOIN tmp_limitcontrats USING(idcontrat) WHERE
qtep1 >= qtep2

Yes, it is empty if I keep tmp_limitcontrats to idcontrat=1003

Otherwise, you are right, there are irregular data but not that I'm
concerned with in that particular case.

?:
SELECT numrange( qtep1+1 , qtep2, '[]') AS rangep FROM paliers JOIN
tmp_limitcontrats USING(idcontrat) where qtep1+1 < qtep2

--
Adrian Klaver
adrian.klaver@aklaver.com

On 8/27/24 11:16 AM, Adrian Klaver wrote:

On 8/27/24 11:13 AM, Jean-Christophe BOGGIO wrote:

Le 27/08/2024 à 19:51, Torsten Förtsch a écrit :

I guess this query comes back non-empty:

SELECT * FROM paliers JOIN tmp_limitcontrats USING(idcontrat) WHERE
qtep1 >= qtep2

Yes, it is empty if I keep tmp_limitcontrats to idcontrat=1003

Otherwise, you are right, there are irregular data but not that I'm
concerned with in that particular case.

?:
SELECT numrange( qtep1+1   , qtep2,  '[]') AS rangep FROM paliers JOIN
tmp_limitcontrats USING(idcontrat) where qtep1+1 < qtep2

Actually that should be:

qtep1+1 <= qtep2

--
Adrian Klaver
adrian.klaver@aklaver.com

Paul,

Le 27/08/2024 à 20:11, Paul Jungwirth a écrit :

The issue is the order-of-operations used by the planner. If I put
EXPLAIN on your last query, I see:

 Hash Join  (cost=16.64..109.90 rows=2410 width=64)
   Hash Cond: (tmp_limitcontrats.idcontrat = paliers.idcontrat)
   ->  Seq Scan on tmp_limitcontrats  (cost=0.00..35.50 rows=2550
width=4)
   ->  Hash  (cost=14.27..14.27 rows=189 width=12)
         ->  Seq Scan on paliers  (cost=0.00..14.27 rows=189 width=12)
               Filter: (numrange(((qtep1 + 1))::numeric,
(qtep2)::numeric) <> '(,)'::numrange)

So we are applying that filter to every row in paliers, not just the
one with idcontrat = 1003.

I understand, makes perfect sense. Thanks for the explanation. Have a
nice day,

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