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".
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.
You can reproduce the problem using the attached file:
CREATE TABLE paliers (
idpalier integer NOT NULL,
idcontrat integer NOT NULL,
isdefault boolean NOT NULL,
name character varying(30),
qtep1 integer,
qtep2 integer,
qtep3 integer,
qtep4 integer,
qtep5 integer,
qtep6 integer,
qtep7 integer,
qtep8 integer,
qtep9 integer,
qtep10 integer,
qpp1 double precision,
qpp2 double precision,
qpp3 double precision,
qpp4 double precision,
qpp5 double precision,
qpp6 double precision,
qpp7 double precision,
qpp8 double precision,
qpp9 double precision,
qpp10 double precision,
idpalier_clonedfrom integer,
assessonamounts boolean DEFAULT false,
amountp1 numeric(15,2),
amountp2 numeric(15,2),
amountp3 numeric(15,2),
amountp4 numeric(15,2),
amountp5 numeric(15,2),
amountp6 numeric(15,2),
amountp7 numeric(15,2),
amountp8 numeric(15,2),
amountp9 numeric(15,2),
tauxmini numeric(5,2)
);
copy
paliers(idpalier,idcontrat,isdefault,name,qtep1,qtep2,qtep3,qtep4,qtep5,qtep6,qtep7,qtep8,qtep9,qtep10,qpp1,qpp2,qpp3,qpp4,qpp5,qpp6,qpp7,qpp8,qpp9,qpp10,idpalier_clonedfrom,assessonamounts,amountp1,amountp2,amountp3,amountp4,amountp5,amountp6,amountp7,amountp8,amountp9,tauxmini)
from '/tmp/paliers.csv'
delimiter ','
csv header;
DROP TABLE IF EXISTS tmp_limitcontrats;
CREATE TABLE tmp_limitcontrats AS
SELECT 1003 AS idcontrat;
WITH rangespaliers AS (
SELECT numrange(qtep1+1,qtep2) as rangep FROM paliers JOIN
tmp_limitcontrats USING(idcontrat)
)
, rangespaliers2 AS (
SELECT rangep, numrange(null,null)
FROM rangespaliers
WHERE rangep <> NUMRANGE(null,null)
)
select * from rangespaliers2;
This fails on PG 16.4 and 15.7
Thanks a lot for your enlightenment.
idpalier,idcontrat,isdefault,name,qtep1,qtep2,qtep3,qtep4,qtep5,qtep6,qtep7,qtep8,qtep9,qtep10,qpp1,qpp2,qpp3,qpp4,qpp5,qpp6,qpp7,qpp8,qpp9,qpp10,idpalier_clonedfrom,assessonamounts,amountp1,amountp2,amountp3,amountp4,amountp5,amountp6,amountp7,amountp8,amountp9,tauxmini
268,217,t,default,,,1,,,f,,
395,573,t,default,5,10,12,13,15,f,,
697,916,t,default,5,10,16,17,18656,f,,
698,916,f,Digital,10,,18,20,,f,,
701,918,t,default,,,3,,612,f,,
702,919,t,default,5,10,11,12,14697,f,,
704,920,t,default,5,10,11,12,14702,f,,
705,921,t,default,,,2,,43,f,,
709,925,t,default,,,2,,568,f,,1.00
710,926,t,default,,,6.5,,668,f,,
711,927,t,default,,,7,,710,f,,
712,928,t,default,,,2,,711,f,,
713,929,t,default,,,10,,711,f,,
714,930,t,default,,,6,,713,f,,
715,931,t,default,,,3,,714,f,,
716,932,t,default,,,8,,713,f,,
717,933,t,default,,,1,,716,f,,
718,934,t,default,,,1,,717,f,,
719,935,t,default,,,1.5,,713,f,,
720,936,t,default,,,15,,713,f,,
721,937,t,default,,,1,,719,f,,
722,938,t,default,,,9,,716,f,,
723,939,t,default,,,2,,722,f,,
724,940,t,default,,,8,,722,f,,
725,941,t,default,,,1,,724,f,,
726,942,t,default,,,1,,725,f,,
727,943,t,default,,,8,,722,f,,
728,944,t,default,,,3,,727,f,,
729,945,t,default,,,8,,722,f,,
730,946,t,default,,,1,,729,f,,
731,947,t,default,,,1,,730,f,,
737,953,t,default,,,12,,668,f
