Thank you Adrian!
Στις 19/7/22 18:36, ο/η Adrian Klaver έγραψε:
On 7/19/22 03:38, Achilleas Mantzios wrote:
I reformatted queries to see thing better.
-- still has problem testing the range
select it.id ,cept.value::numeric as val,
numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric
,'()') as range from items it, cept_report cept , dynacom.vessels
vsl, machdefs md, cept_reportlimits ceptl wh
ere it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND
it.vslwhid=vsl.id AND vsl.vslstatus='Acti
ve' and md.application = 'Critical Equipment Performance Test' AND
cept.systemdate>= (now()-'1 year'::interval
) AND
numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric,'()') @>
cept.value::numeric ORDER BY 1;
ERROR: cannot convert infinity to numeric
SELECT
it.id,
cept.value::numeric AS val,
numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric, '()')
AS RANGE
FROM
items it,
cept_report cept,
dynacom.vessels vsl,
machdefs md,
cept_reportlimits ceptl wh ere it.id = cept.id
AND md.defid = ceptl.defid
AND it.defid = md.defid
AND it.vslwhid = vsl.id
AND vsl.vslstatus = 'Acti
ve' and md.application = 'Critical Equipment Performance Test' AND
cept.systemdate>= (now()-'1 year'::interval)
AND numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric, '()')
@> cept.value::numeric
ORDER BY
1;
So the above fails. In your title when you say there is no infinity
that means the cept.value, ceptl.min_alarm or ceptl.max_alarm fields
do not have any '-infinity' or 'infinity' values, correct?
There is infinity in cept.value , just not in this result set.
I got confused and wrongly assumed that since the result set (without
the filter in the WHERE clause including cept.value::numeric) did not
contain any infinity it should also work with the filter in the WHERE
clause. Apparently a subplan executes this conversion in the WHERE
before the other filters. I did not do any analyze to prove this.
-- no problem if the query goes into its barrier and the test done
outside
with bar as (select it.id ,cept.value::numeric as val,
numrange(ceptl.min_alarm::numeric,ceptl.max_a
larm::numeric,'()') as range from items it, cept_report cept ,
dynacom.vessels vsl, machdefs md, cept_reportli
mits ceptl where it.id=cept.id AND md.defid=ceptl.defid AND
it.defid=md.defid AND it.vslwhid=vsl.id AND vsl.vs
lstatus='Active' and md.application = 'Critical Equipment Performance
Test' AND cept.systemdate>= (now()-'1 ye
ar'::interval) ORDER BY 1)
select * from bar where NOT range @> val;
-- good data here
WITH bar AS (
SELECT
it.id,
cept.value::numeric AS val,
numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric,
'()') AS
RANGE
FROM
items it,
cept_report cept,
dynacom.vessels vsl,
machdefs md,
cept_reportli mits ceptl
WHERE
it.id = cept.id
AND md.defid = ceptl.defid
AND it.defid = md.defid
AND it.vslwhid = vsl.id
AND vsl.vs lstatus = 'Active'
AND md.application = 'Critical Equipment Performance Test'
AND cept.systemdate >= (now() - '1 ye
ar'::interval)
ORDER BY
1
)
SELECT
*
FROM
bar
WHERE
NOT RANGE @> val;
This version succeeds, correct? So breaking the range construction
into one step and the @> test into another works. Though I am not sure
why <range> @> changed to NOT <range> @>?
Yes this succeeds. The correct is with the NOT, it does not change the
behavior of the initial query.
--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt
