Message-ID: <9e38681b60ada2a1161cc278344678ca563bce83.camel@cybertec.at>
Subject: Re: Strange behaviors with ranges
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Jean-Christophe Boggio <postgresql@thefreecat.org>, 
	pgsql-general@lists.postgresql.org
Date: Wed, 28 Aug 2024 10:56:34 +0200
In-Reply-To: <77691006-45f1-4ef9-bc1b-2eecd44f7d0d@thefreecat.org>
References: <77691006-45f1-4ef9-bc1b-2eecd44f7d0d@thefreecat.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/9e38681b60ada2a1161cc278344678ca563bce83.camel%40cybertec.at>
Precedence: bulk

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

The explanation is in the execution plans.

With your sample data, the plan looks like

 Hash Join
   Hash Cond: (tmp_limitcontrats.idcontrat =3D 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 =3D 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