Reverse Engineering - search constraints are not explicitly stated in the tables from the VIEW - Mailing list pgsql-hackers

I am analyzing query plans generated by the view in the database PostgreSQL
8.3, looking for missing information "constraints not explicitly registrants
in the tables."
In nested queries, (ex. IN clause, ...), the query plan consist in the
evaluation of the subplane derived from clause (SELECT * ....) and external
queries.
In the present case:
HashAggregate  (cost=15.14..15.16 rows=1 width=247)->  Nested Loop IN Join  (cost=3.46..15.12 rows=1 width=247)
JoinFilter: (o.c_doctype_id = c_doctype.c_doctype_id)    ->  Hash Left Join  (cost=3.46..12.38 rows=1 width=247)
 Hash Cond: (bp.c_invoiceschedule_id = si.c_invoiceschedule_id)"          Filter: ((o.invoicerule = 'I'::bpchar) OR
((o.invoicerule=
 
'O'::bpchar) AND (NOT (subplan))) OR ((o.invoicerule = 'D'::bpchar) AND
(l.qtyinvoiced <> l.qtydelivered)) OR ((o.invoicerule = 'S'::bpchar) AND
(bp.c_invoiceschedule_id IS NULL)) OR ((o.invoicerule = 'S'::bpchar) AND
(bp.c_invoiceschedule_id IS NOT NULL) AND ((si.invoicefrequency IS NULL) OR
(si.invoicefrequency = 'D'::bpchar) OR (si.invoicefrequency = 'W'::bpchar)
OR ((si.invoicefrequency = 'T'::bpchar) AND
(((adempiere.trunc((o.dateordered)::timestamp with time zone) <=
(((adempiere.firstof(adempiere.getdate(), 'MM'::character
varying))::timestamp with time zone OPERATOR(adempiere.+)
si.invoicedaycutoff) - 1)) AND (adempiere.trunc(adempiere.getdate()) >=
(((adempiere.firstof((o.dateordered)::timestamp with time zone,
'MM'::character varying))::timestamp with time zone OPERATOR(adempiere.+)
si.invoiceday) - 1))) OR ((adempiere.trunc((o.dateordered)::timestamp with
time zone) <= (((adempiere.firstof(adempiere.getdate(), 'MM'::character
varying))::timestamp with time zone OPERATOR(adempiere.+)
si.invoicedaycutoff) + 14)) AND (adempiere.trunc(adempiere.getdate()) >=
(((adempiere.firstof((o.dateordered)::timestamp with time zone,
'MM'::character varying))::timestamp with time zone OPERATOR(adempiere.+)
si.invoiceday) + 14))))) OR ((si.invoicefrequency = 'M'::bpchar) AND
(adempiere.trunc((o.dateordered)::timestamp with time zone) <=
(((adempiere.firstof(adempiere.getdate(), 'MM'::character
varying))::timestamp with time zone OPERATOR(adempiere.+)
si.invoicedaycutoff) - 1)) AND (adempiere.trunc(adempiere.getdate()) >=
(((adempiere.firstof((o.dateordered)::timestamp with time zone,
'MM'::character varying))::timestamp with time zone OPERATOR(adempiere.+)
si.invoiceday) - 1))))))         ->  Hash Join  (cost=2.44..3.87 rows=3 width=300)               Hash Cond:
(l.c_order_id= o.c_order_id)               ->  Seq Scan on c_orderline l  (cost=0.00..1.31 rows=25
 
width=141)                     Filter: (qtyordered <> qtyinvoiced)              ->  Hash  (cost=2.40..2.40 rows=3
width=172)"                    ->  Hash Join  (cost=1.13..2.40 rows=3 width=172)                           Hash Cond:
(bp.c_bpartner_id= o.c_bpartner_id)                           ->  Seq Scan on c_bpartner bp  (cost=0.00..1.17
 
rows=17 width=26)                           ->  Hash  (cost=1.10..1.10 rows=3 width=159)
->  Seq Scan on c_order o 
 
(cost=0.00..1.10 rows=3 width=159)                                       Filter: (docstatus = ANY
('{CO,CL,IP}'::bpchar[]))         ->  Hash  (cost=1.01..1.01 rows=1 width=47)               ->  Seq Scan on
c_invoiceschedulesi  (cost=0.00..1.01
 
rows=1 width=47)         SubPlan           ->  Seq Scan on c_orderline zz1  (cost=0.00..1.38 rows=1
width=0)                 Filter: ((qtyordered <> qtydelivered) AND (c_order_id =
$0))   ->  Seq Scan on c_doctype  (cost=0.00..2.73 rows=1 width=13)         Filter: ((c_doctype.docbasetype =
'SOO'::bpchar)AND
 
(c_doctype.docsubtypeso <> ALL ('{ON,OB,WR}'::bpchar[])))

In the analysis of the query plan and its subplane, I can not understand
what the parameter $0 represents, without looking the SQL query. My question
is: looking only at the query plan product, you can understand what is the
parameter $0?

SELECT o.ad_client_id, o.ad_org_id, o.c_bpartner_id, o.c_order_id,
o.documentno, o.dateordered, o.c_doctype_id, sum((l.qtyordered -
l.qtyinvoiced) * l.priceactual) AS totallines
FROM c_order o
JOIN c_orderline l ON o.c_order_id = l.c_order_id
JOIN c_bpartner bp ON o.c_bpartner_id = bp.c_bpartner_id
LEFT JOIN c_invoiceschedule si ON bp.c_invoiceschedule_id =
si.c_invoiceschedule_id
WHERE (o.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar, 'IP'::bpchar]))
AND 
(o.c_doctype_id IN ( SELECT c_doctype.c_doctype_id                    FROM c_doctype          WHERE
c_doctype.docbasetype= 'SOO'::bpchar AND
 
(c_doctype.docsubtypeso <> ALL         (ARRAY['ON'::bpchar, 'OB'::bpchar,
'WR'::bpchar])))) AND l.qtyordered <> l.qtyinvoiced                 AND (o.invoicerule = 'I'::bpchar OR o.invoicerule
=
'O'::bpchar                 AND NOT (EXISTS ( SELECT 1 FROM c_orderline zz1
WHERE**zz1.c_order_id =
 
o.c_order_id** AND zz1.qtyordered <> zz1.qtydelivered)                                         )
OR o.invoicerule = 'D'::bpchar AND l.qtyinvoiced <>
 
l.qtydelivered OR o.invoicerule = 'S'::bpchar AND bp.c_invoiceschedule_id IS
NULL                         OR o.invoicerule = 'S'::bpchar AND
bp.c_invoiceschedule_id IS NOT NULL AND (si.invoicefrequency IS NULL                         OR si.invoicefrequency =
'D'::bpcharOR
 
si.invoicefrequency = 'W'::bpchar OR si.invoicefrequency = 'T'::bpchar                         AND
(trunc(o.dateordered::timestampwith time zone)
 
<= ((firstof(getdate(), 'MM'::character varying)::timestamp with time zone
OPERATOR(+) si.invoicedaycutoff) - 1)                         AND trunc(getdate()) >=
((firstof(o.dateordered::timestamp with time zone, 'MM'::character
varying)::timestamp with time zone OPERATOR(+) si.invoiceday) - 1)                         OR
trunc(o.dateordered::timestampwith time zone)
 
<= ((firstof(getdate(), 'MM'::character varying)::timestamp with time zone
OPERATOR(+) si.invoicedaycutoff) + 14)                         AND trunc(getdate()) >=
((firstof(o.dateordered::timestamp with time zone, 'MM'::character
varying)::timestamp with time zone OPERATOR(+) si.invoiceday) + 14))                         OR si.invoicefrequency =
'M'::bpchar                        AND trunc(o.dateordered::timestamp with time zone)
 
<= ((firstof(getdate(), 'MM'::character varying)::timestamp with time zone
OPERATOR(+) si.invoicedaycutoff) - 1)                         AND trunc(getdate()) >=
((firstof(o.dateordered::timestamp with time zone, 'MM'::character
varying)::timestamp with time zone OPERATOR(+) si.invoiceday) - 1)))
GROUP BY o.ad_client_id, o.ad_org_id, o.c_bpartner_id, o.c_order_id,
o.documentno, o.dateordered, o.c_doctype_id

Any suggestion?



--
View this message in context:
http://postgresql.nabble.com/Reverse-Engineering-search-constraints-are-not-explicitly-stated-in-the-tables-from-the-VIEW-tp5826625.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.