Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sj0Ma-00CZFP-Gm for pgsql-general@arkaria.postgresql.org; Tue, 27 Aug 2024 17:52:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sj0MY-00AZNP-BX for pgsql-general@arkaria.postgresql.org; Tue, 27 Aug 2024 17:52:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sj0MX-00AZM2-Rx for pgsql-general@lists.postgresql.org; Tue, 27 Aug 2024 17:52:14 +0000 Received: from mail-lj1-x234.google.com ([2a00:1450:4864:20::234]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sj0MW-001ijl-1D for pgsql-general@lists.postgresql.org; Tue, 27 Aug 2024 17:52:13 +0000 Received: by mail-lj1-x234.google.com with SMTP id 38308e7fff4ca-2f3eabcd293so66400961fa.2 for ; Tue, 27 Aug 2024 10:52:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724781130; x=1725385930; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=jUt75FywbOE0iy9NzngjpW/E+0/9RhNVQ8KyiUYY0/8=; b=F1JLdFlCL7k5krLncsiwo8ysHLk0nFhzG9n+lD4iKNuGO3Pl5m6IzeXig6DOJv62fA ltW2D16S9o5HKcRmKtmXcomRE2Dm3HTRE9gnePndfP31ytBev3V3ujqvvQ5yH9Z1fMgr JTma6KJGvZ+O3Es219Aff3+fOIRpdU9llFxbgAJ3GJtioHfKwACLV9si1w/mdrDp4vlX Q6pn5/GNRLOVovQunYEYhBdffWLtFr0tE//5Gq8Q/ftAAAoh1+/PIxFjv6k/xdVU8GKF fLM5GYv+6wZvGROv5U+gTDEGknWpLuLy+IOMcTyjLRTgrvHHoOex6Ftg/6p4vz8th7G1 VaaQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724781130; x=1725385930; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=jUt75FywbOE0iy9NzngjpW/E+0/9RhNVQ8KyiUYY0/8=; b=lmID3RSAq0Bo2jKRUiKVBYNce3Y6ukPsGj8/YqjNx9rAUMtfXLYWVczuvXlAcI0blE r5O+xVzsyaLBDZFQnPoyMPxmDUuDlvJKEyv4bZobJNIQcAPCDcpGaJoTsnp2X7eD3Spv DCc+bHYdlPbolcLVAX7fOKEgyXAU984g94eVymNDAgz7hw4+b3thvjEPGXfmqN61F9rG RYecVJpzvne5Ukz/0Ot0KwDO3g5nepyt/CLgfiu3pHTEd7lzG4MEBkSjZincKYK27/s6 5cywHMStaA4ObvN6FcblJTm/sk2KJOGYdWqK/aK+2V185Exc8PvKvd2OwnlRwwJ0LwLi INow== X-Forwarded-Encrypted: i=1; AJvYcCXE2foe+YDy4FLii3Ky1txYR3F5F5H/qkwCbsXW3dzG4XqfHOHET5oIqfNwHOlKU7PGxal760QrTm+hCSch@lists.postgresql.org X-Gm-Message-State: AOJu0YwcJg2AofruzjxD1hRChgDSkAh2JBIPWr05jLZORQy2ZvTQ0y/h y0LRVU6XB8t+6FzkOklup0tbC8T6saksS8yyGCpLuSUFiFPxcXip4MzhXLlnCBcQ2dcUxG4Y7Hu bf6DU64NpdSslKXvlvFp7xjEGgxd/dER2Qg== X-Google-Smtp-Source: AGHT+IFfvfE89ykkDFIu5/JpMs4GXXK+HlmZzSmDkTZt/KOFCeKpQ2TJMckeF1qOtKkv8TBLEZG/gk4LZE7z+fhNlzw= X-Received: by 2002:a2e:be04:0:b0:2ef:2d80:b8ff with SMTP id 38308e7fff4ca-2f4f573a2c0mr122500741fa.6.1724781129677; Tue, 27 Aug 2024 10:52:09 -0700 (PDT) MIME-Version: 1.0 References: <77691006-45f1-4ef9-bc1b-2eecd44f7d0d@thefreecat.org> <572cc9d2-a981-4f0e-802a-69515dac1722@aklaver.com> In-Reply-To: <572cc9d2-a981-4f0e-802a-69515dac1722@aklaver.com> From: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= Date: Tue, 27 Aug 2024 19:51:58 +0200 Message-ID: Subject: Re: Strange behaviors with ranges To: Adrian Klaver Cc: Jean-Christophe Boggio , pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000008cc6680620ade642" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008cc6680620ade642 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I guess this query comes back non-empty: SELECT * FROM paliers JOIN tmp_limitcontrats USING(idcontrat) WHERE qtep1 >=3D qtep2 This would then lead somewhere to this expression numrange(3,2) Check out idpalier=3D805 On Tue, Aug 27, 2024 at 7:37=E2=80=AFPM Adrian Klaver wrote: > > > 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=3D1003 > > ) > > ,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=3D1003". > > What does: > > SELECT numrange( qtep1+1 , qtep2, '[]') AS rangep FROM paliers WHERE > idcontrat=3D1003 > > return? > > > > > > This fails on PG 16.4 and 15.7 > > > > Thanks a lot for your enlightenment. > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > > --0000000000008cc6680620ade642 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I guess this query comes back non-empty:

SELECT * FROM=C2=A0paliers JOIN tmp_li= mitcontrats USING(idcontrat) WHERE qtep1=C2=A0>=3D qtep2

This would then lead somewhere to this expression numran= ge(3,2)

Check out idpalier=3D805


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


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:
>
>=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( qtep= 1+1=C2=A0=C2=A0 , qtep2,=C2=A0 '[]') AS rangep FROM
> 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
> 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 <> NUMR= ANGE(NULL, NULL) -- ERROR IS HERE
>=C2=A0 =C2=A0=C2=A0 =C2=A0)
>=C2=A0 =C2=A0=C2=A0 =C2=A0select * 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&= #39;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 onl= y
> one row which contains "idcontrat=3D1003".

What does:

SELECT numrange( qtep1+1=C2=A0 =C2=A0, qtep2,=C2=A0 '[]') AS rangep= FROM paliers WHERE
idcontrat=3D1003

return?


>
> This fails on PG 16.4 and 15.7
>
> Thanks a lot for your enlightenment.
>

--
Adrian Klaver
adrian.klave= r@aklaver.com


--0000000000008cc6680620ade642--