I format query to read it more easy. See attachment
Tuesday, December 8, 2020, 3:43:13 PM, you wrote:
Hello Tom,
>>> Also would be useful if PG point at query where this bad value was
>>> calculated or occur.
>> This is not the first time we've seen this request and it usually ends up
>> getting stalled because its non-trivial to implement and thus isn't
>> feasible for the benefit it brings.
> I do still have ambitions to make that happen, but you're right that it's
> a major undertaking. Don't hold your breath.
Another case posted below.
Here in database are values which could not construct correct daterange.
There are thousands of rows. It is hard to find even a way or make an assumption
how to get record which cause this error.
It will be very impressive if database could report the line at database where
error is occur.
Via HINT for example.
If this is not possible, then current cursor position or raw data dump in debug
mode.
Probably some memory buffers or so.
Any additional info would be helpful.
Thank you.
DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::Pg::st execute
failed: ERROR: range lower bound must be less than or equal to range upper
bound?CONTEXT: SQL function "accounting_ready" statement 1 [for Statement
"SELECT COUNT( * ) FROM (( WITH?target_date AS ( SELECT ?::timestamptz
),?target_order as (? SELECT? usage_range as bill_range,? o.*? FROM (
SELECT * FROM "order_bt" WHERE sys_period @> sys_time() ) o? LEFT JOIN period
prd on prd.id = o.period_id? LEFT JOIN accounting_ready(? CASE WHEN
prd.INTERVAL = '1 mon' THEN date_trunc( 'month', o.docdate ) ELSE o.docdate
END,? prd.INTERVAL, (SELECT * FROM target_date)? ) acc ON true? WHERE
FALSE? OR ? = 0? OR ? = 1 AND o.id = ?? AND acc.usage AND EXISTS (?
SELECT * FROM order_bt prev_order? WHERE sys_period @> sys_time()
AND? prev_order.id = o.id AND prev_order.app_period &&
acc.usage_range? )? AND o.app_period && acc.usage_range? OR ? = 2
AND o.agreement_id = ? and o.period_id = ?? AND acc.usage AND EXISTS (?
SELECT * FROM order_bt prev_order? WHERE sys_period @> sys_time()
AND? prev_order.id = o.id AND prev_order.app_period &&
acc.usage_range? )? AND o.app_period && acc.usage_range? OR ? =
3?),?USAGE AS ( SELECT? (ocd.o).id as order_id,?
(dense_rank() over (PARTITION BY o.agreement_id, o.id ORDER BY
(ocd.ic).consumed_period )) as group_id,? (ocd.c).id as
detail_id,? (ocd.c).service_type_id as service_type,?
(ocd.c).resource_type_id as detail_type,? (ocd.c).amount
as detail_amount,? (ocd.c).allocated_resource_id as resource_id,? null
as resource_uuid,? null as
resource_desc,? rt.unit as resource_unit,? -- count
changes. Logic is next: How many times configration for this order is met at
this period? count(*) OVER (PARTITION BY (ocd.o).id, (ocd.c).id ) as
consumed_count,? (ocd.ic).consumed as consumed_days,? null
as consumed_amount,? null as
consumed_last,?? a.id as agreement_id,? coalesce(
substring( a.docn from '^A?(.*?)(?:\s*-[^-]*)?$' ), a.docn ) as agreement,?
a.docdate as docdate,?? pkg.id
as package_id,? pkg.link_1c_id as package_1c_id,?
coalesce( pkg.display, pkg.name ) as package,?? coalesce( st.display, st.name,
rt.display, rt.name ) as sr_name,? COALESCE( (ocd.p).label, rt.LABEL )
as unit_label,?? coalesce( (ocd.c).sort_order, pd.sort_order ) as
sort_order,?? ocd.item_price AS price,? --
We want to display QTY for resources too? coalesce( ocd.item_qty,
(ocd.c).amount/rt.unit ) AS qty,? 0
AS month_length,? 0 AS
days_count,? o.bill_range,? lower( (ocd.ic).consumed_period )
as consumed_from,? upper( (ocd.ic).consumed_period ) -interval '1sec' as
consumed_till,??? ocd.item_suma,?? 0 as discount,? (sum( ocd.item_suma )
OVER( PARTITION BY (ocd.o).id, (ocd.ic).consumed_period ))::numeric( 10, 2) AS
group_suma,? (sum( ocd.item_cost ) OVER( PARTITION BY (ocd.o).id,
(ocd.ic).consumed_period ))::numeric( 10, 2) AS order_cost?FROM target_order
o?LEFT JOIN order_cost_details( o.bill_range ) ocd? ON (ocd.o).id = o.id AND
(ocd.ic).consumed_period && o.app_period? LEFT JOIN agreement a ON a.id
= o.agreement_id? LEFT JOIN package pkg ON pkg.id = o.package_id? LEFT
JOIN package_detail pd ON pd.package_id = (ocd.o).package_id? AND
pd.resource_type_id IS NOT DISTINCT FROM (ocd.c).resource_type_id? AND
pd.service_type_id IS NOT DISTINCT FROM (ocd.c).service_type_id? LEFT JOIN
resource_type rt ON rt.id = (ocd.c).resource_type_id? LEFT JOIN service_type
st on st.id = (ocd.c).service_type_id?)??SELECT *,? (group_suma/6)
::numeric( 10, 2 ) as group_nds,? (SELECT sum(x) from (SELECT sum( DISTINCT
group_suma ) AS x FROM usage sub_u WHERE
sub_u.agreement_id = usage.agreement_id GROUP BY agreement_id, order_id) t) as
total_suma,? (SELECT sum(x) from (SELECT sum( DISTINCT
(group_suma/6)::numeric( 10, 2 ) ) AS x FROM usage sub_u WHERE
sub_u.agreement_id = usage.agreement_id GROUP BY agreement_id, order_id) t) as
total_nds?FROM usage?where ? <> 3 OR consumed_count > 1?ORDER BY? /* put
order first then allocated resource without Order */? agreement,? order_id,?
bill_range,? group_id,? sort_order nulls last,? detail_type nulls last,?
price desc nulls last,? detail_amount desc,? service_type nulls last,?
detail_id?)?) "me"" with ParamValues: 1='2020-08-01', 2='2', 3='2', 4=undef,
5='2', 6='3493', 7='10', 8='2', 9='2'] at
/home/kes/work/projects/tucha/monkeyman/lib/MaitreD/Controller/Cart.pm line 828
--
Best regards,
Eugen Konkov
--
Best regards,
Eugen Konkov
DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::Pg::st execute
failed: ERROR: range lower bound must be less than or equal to range upper
bound
CONTEXT: SQL function "accounting_ready" statement 1 [for Statement "SELECT
COUNT( * ) FROM (( WITH
target_date AS ( SELECT ?::timestamptz ),
target_order as (
SELECT
usage_range as bill_range,
o.*
FROM ( SELECT * FROM "order_bt" WHERE sys_period @> sys_time() ) o
LEFT JOIN period prd on prd.id = o.period_id
LEFT JOIN accounting_ready(
CASE WHEN prd.INTERVAL = '1 mon' THEN date_trunc( 'month', o.docdate ) ELSE
o.docdate END,
prd.INTERVAL, (SELECT * FROM target_date)
) acc ON true
WHERE FALSE
OR ? = 0
OR ? = 1 AND o.id = ?
AND acc.usage AND EXISTS (
SELECT * FROM order_bt prev_order
WHERE sys_period @> sys_time() AND
prev_order.id = o.id AND prev_order.app_period && acc.usage_range
)
AND o.app_period && acc.usage_range
OR ? = 2 AND o.agreement_id = ? and o.period_id = ?
AND acc.usage AND EXISTS (
SELECT * FROM order_bt prev_order
WHERE sys_period @> sys_time() AND
prev_order.id = o.id AND prev_order.app_period && acc.usage_range
)
AND o.app_period && acc.usage_range
OR ? = 3
),
USAGE AS ( SELECT
(ocd.o).id as order_id,
(dense_rank() over (PARTITION BY o.agreement_id, o.id ORDER BY
(ocd.ic).consumed_period )) as group_id,
(ocd.c).id as detail_id,
(ocd.c).service_type_id as service_type,
(ocd.c).resource_type_id as detail_type,
(ocd.c).amount as detail_amount,
(ocd.c).allocated_resource_id as resource_id,
null as resource_uuid,
null as resource_desc,
rt.unit as resource_unit,
-- count changes. Logic is next: How many times configration for this order
is met at this period
count(*) OVER (PARTITION BY (ocd.o).id, (ocd.c).id ) as consumed_count,
(ocd.ic).consumed as consumed_days,
null as consumed_amount,
null as consumed_last,
a.id as agreement_id,
coalesce( substring( a.docn from '^A?(.*?)(?:\s*-[^-]*)?$' ), a.docn ) as
agreement,
a.docdate as docdate,
pkg.id as package_id,
pkg.link_1c_id as package_1c_id,
coalesce( pkg.display, pkg.name ) as package,
coalesce( st.display, st.name, rt.display, rt.name ) as sr_name,
COALESCE( (ocd.p).label, rt.LABEL ) as unit_label,
coalesce( (ocd.c).sort_order, pd.sort_order ) as sort_order,
ocd.item_price AS price,
-- We want to display QTY for resources too
coalesce( ocd.item_qty, (ocd.c).amount/rt.unit ) AS qty,
0 AS month_length,
0 AS days_count,
o.bill_range,
lower( (ocd.ic).consumed_period ) as consumed_from,
upper( (ocd.ic).consumed_period ) -interval '1sec' as consumed_till,
ocd.item_suma,
0 as discount,
(sum( ocd.item_suma ) OVER( PARTITION BY (ocd.o).id, (ocd.ic).consumed_period
))::numeric( 10, 2) AS group_suma,
(sum( ocd.item_cost ) OVER( PARTITION BY (ocd.o).id, (ocd.ic).consumed_period
))::numeric( 10, 2) AS order_cost
FROM target_order o
LEFT JOIN order_cost_details( o.bill_range ) ocd
ON (ocd.o).id = o.id AND (ocd.ic).consumed_period && o.app_period
LEFT JOIN agreement a ON a.id = o.agreement_id
LEFT JOIN package pkg ON pkg.id = o.package_id
LEFT JOIN package_detail pd ON pd.package_id = (ocd.o).package_id
AND pd.resource_type_id IS NOT DISTINCT FROM (ocd.c).resource_type_id
AND pd.service_type_id IS NOT DISTINCT FROM (ocd.c).service_type_id
LEFT JOIN resource_type rt ON rt.id = (ocd.c).resource_type_id
LEFT JOIN service_type st on st.id = (ocd.c).service_type_id
)
SELECT *,
(group_suma/6) ::numeric( 10, 2 ) as group_nds,
(SELECT sum(x) from (SELECT sum( DISTINCT group_suma )
AS x FROM usage sub_u WHERE sub_u.agreement_id = usage.agreement_id GROUP BY
agreement_id, order_id) t) as total_suma,
(SELECT sum(x) from (SELECT sum( DISTINCT (group_suma/6)::numeric( 10, 2 ) )
AS x FROM usage sub_u WHERE sub_u.agreement_id = usage.agreement_id GROUP BY
agreement_id, order_id) t) as total_nds
FROM usage
where ? <> 3 OR consumed_count > 1
ORDER BY
/* put order first then allocated resource without Order */
agreement,
order_id,
bill_range,
group_id,
sort_order nulls last,
detail_type nulls last,
price desc nulls last,
detail_amount desc,
service_type nulls last,
detail_id
)
) "me"" with ParamValues: 1='2020-08-01', 2='2', 3='2', 4=undef, 5='2',
6='3493', 7='10', 8='2', 9='2'] at
/home/kes/work/projects/tucha/monkeyman/lib/MaitreD/Controller/Cart.pm line 828