public inbox for [email protected]  

help / color / mirror / Atom feed

From: Torsten Förtsch <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: Jean-Christophe Boggio <[email protected]>
Cc: [email protected]
Subject: Re: Strange behaviors with ranges
Date: Tue, 27 Aug 2024 19:51:58 +0200
Message-ID: <CAKkG4_=nj6sdNg2VGfsbWsRbdgTGAoOWLusZTcVdCeNpnt1-DA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

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 <[email protected]>
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=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
> [email protected]
>
>
>

view thread (2+ messages)

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], [email protected]
  Subject: Re: Strange behaviors with ranges
  In-Reply-To: <CAKkG4_=nj6sdNg2VGfsbWsRbdgTGAoOWLusZTcVdCeNpnt1-DA@mail.gmail.com>

* 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