0034787: DBinconsistency in 1 view between 9.3 & 9.5 versions of postgres which seems to need dbsm changes

diff -r 3f8def1dd53f src-db/database/model/views/OBWPL_PICKINGLIST_PICK_EDIT.xml --- a/src-db/database/model/views/OBWPL_PICKINGLIST_PICK_EDIT.xml Tue Dec 13 11:18:48 2016 +0100 +++ b/src-db/database/model/views/OBWPL_PICKINGLIST_PICK_EDIT.xml Tue Dec 20 13:21:55 2016 +0100 @@ -1,4 +1,4 @@ <?xml version="1.0"?> <database name="VIEW OBWPL_PICKINGLIST_PICK_EDIT"> - <view name="OBWPL_PICKINGLIST_PICK_EDIT"><![CDATA[SELECT COALESCE(iol.em_obwpl_pickinglist_id, sol.c_orderline_id) || stock.m_storage_detail_id AS obwpl_pickinglist_pick_edit_id, sol.ad_client_id, sol.ad_org_id, to_char('Y') AS isactive, sol.updated, sol.updatedby, sol.created, sol.createdby, CASE WHEN iol.m_inoutline_id IS NULL THEN to_char('N') ELSE to_char('Y') END AS ob_selected, iol.m_inoutline_id, stock.m_warehouse_id, stock.m_locator_id, stock.m_attributesetinstance_id, stock.availableqty, COALESCE(stock.resqty, 0) - COALESCE(iol.movementqty, 0) AS reservedinothers, iol.movementqty AS quantity, sol.qtyordered, sol.qtydelivered, sol.c_orderline_id AS sales_orderline_id, iol.em_obwpl_pickinglist_id AS obwpl_pickinglist_id, stock.m_storage_detail_id FROM (SELECT ol.c_orderline_id, ol.m_product_id, ol.qtyordered, ol.qtydelivered, COALESCE(ol.m_attributesetinstance_id, '0') AS so_asi, ol.c_uom_id, ol.ad_org_id, ol.ad_client_id, ol.updated, ol.updatedby, ol.created, ol.createdby FROM c_orderline ol JOIN c_order o ON o.c_order_id = ol.c_order_id AND o.issotrx = 'Y' AND o.docstatus = 'CO' AND o.em_obwpl_isinpickinglist = 'Y') sol JOIN (SELECT sd.m_product_id, sd.c_uom_id, sd.m_locator_id, l.m_warehouse_id, COALESCE(sd.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, sum(res.quantity) AS resqty, sd.qtyonhand AS availableqty, sd.m_storage_detail_id FROM m_storage_detail sd JOIN m_locator l ON sd.m_locator_id = l.m_locator_id LEFT JOIN (SELECT rs.quantity - COALESCE(rs.releasedqty, 0) AS quantity, COALESCE(rs.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, rs.m_locator_id, r.m_product_id, r.c_uom_id FROM m_reservation_stock rs JOIN m_reservation r ON rs.m_reservation_id = r.m_reservation_id WHERE r.res_status NOT IN ('CL', 'DR')) res ON res.m_product_id = sd.m_product_id AND res.c_uom_id = sd.c_uom_id AND res.m_attributesetinstance_id = COALESCE(sd.m_attributesetinstance_id, '0') AND res.m_locator_id = sd.m_locator_id WHERE sd.qtyonhand > 0 AND sd.m_product_uom_id IS NULL GROUP BY sd.m_product_id, sd.c_uom_id, sd.m_locator_id, l.m_warehouse_id, COALESCE(sd.m_attributesetinstance_id, '0'), sd.qtyonhand, sd.m_storage_detail_id) stock ON sol.m_product_id = stock.m_product_id AND (sol.so_asi = stock.m_attributesetinstance_id OR sol.so_asi = '0') LEFT JOIN (SELECT il.c_orderline_id, il.m_locator_id, il.m_attributesetinstance_id, il.em_obwpl_pickinglist_id, il.m_inoutline_id, il.movementqty FROM m_inoutline il JOIN m_inout i ON i.m_inout_id = il.m_inout_id AND i.processed = 'N') iol ON sol.c_orderline_id = iol.c_orderline_id AND stock.m_locator_id = iol.m_locator_id AND stock.m_attributesetinstance_id = COALESCE(iol.m_attributesetinstance_id, '0') AND iol.em_obwpl_pickinglist_id IS NOT NULL WHERE iol.m_inoutline_id IS NOT NULL OR (stock.m_warehouse_id IN (SELECT ow.m_warehouse_id FROM ad_org_warehouse ow WHERE ow.ad_org_id = sol.ad_org_id))]]></view> + <view name="OBWPL_PICKINGLIST_PICK_EDIT"><![CDATA[SELECT COALESCE(iol.em_obwpl_pickinglist_id, sol.c_orderline_id) || stock.m_storage_detail_id AS obwpl_pickinglist_pick_edit_id, sol.ad_client_id, sol.ad_org_id, to_char('Y') AS isactive, sol.updated, sol.updatedby, sol.created, sol.createdby, CASE WHEN iol.m_inoutline_id IS NULL THEN to_char('N') ELSE to_char('Y') END AS ob_selected, iol.m_inoutline_id, stock.m_warehouse_id, stock.m_locator_id, stock.m_attributesetinstance_id, stock.availableqty, COALESCE(stock.resqty, 0) - COALESCE(iol.movementqty, 0) AS reservedinothers, iol.movementqty AS quantity, sol.qtyordered, sol.qtydelivered, sol.c_orderline_id AS sales_orderline_id, iol.em_obwpl_pickinglist_id AS obwpl_pickinglist_id, stock.m_storage_detail_id FROM (SELECT ol.c_orderline_id, ol.m_product_id, ol.qtyordered, ol.qtydelivered, COALESCE(ol.m_attributesetinstance_id, '0') AS so_asi, ol.c_uom_id, ol.ad_org_id, ol.ad_client_id, ol.updated, ol.updatedby, ol.created, ol.createdby FROM c_orderline ol JOIN c_order o ON o.c_order_id = ol.c_order_id AND o.issotrx = 'Y' AND o.docstatus = 'CO' AND o.em_obwpl_isinpickinglist = 'Y') sol JOIN (SELECT sd.m_product_id, sd.c_uom_id, sd.m_locator_id, l.m_warehouse_id, COALESCE(sd.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, sum(res.quantity) AS resqty, sd.qtyonhand AS availableqty, sd.m_storage_detail_id FROM m_storage_detail sd JOIN m_locator l ON sd.m_locator_id = l.m_locator_id LEFT JOIN (SELECT rs.quantity - COALESCE(rs.releasedqty, 0) AS quantity, COALESCE(rs.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, rs.m_locator_id, r.m_product_id, r.c_uom_id FROM m_reservation_stock rs JOIN m_reservation r ON rs.m_reservation_id = r.m_reservation_id WHERE r.res_status NOT IN ('CL', 'DR')) res ON res.m_product_id = sd.m_product_id AND res.c_uom_id = sd.c_uom_id AND res.m_attributesetinstance_id = COALESCE(sd.m_attributesetinstance_id, '0') AND res.m_locator_id = sd.m_locator_id WHERE sd.qtyonhand > 0 AND sd.m_product_uom_id IS NULL GROUP BY sd.m_product_id, sd.c_uom_id, sd.m_locator_id, l.m_warehouse_id, (COALESCE(sd.m_attributesetinstance_id, '0')), sd.qtyonhand, sd.m_storage_detail_id) stock ON sol.m_product_id = stock.m_product_id AND (sol.so_asi = stock.m_attributesetinstance_id OR sol.so_asi = '0') LEFT JOIN (SELECT il.c_orderline_id, il.m_locator_id, il.m_attributesetinstance_id, il.em_obwpl_pickinglist_id, il.m_inoutline_id, il.movementqty FROM m_inoutline il JOIN m_inout i ON i.m_inout_id = il.m_inout_id AND i.processed = 'N') iol ON sol.c_orderline_id = iol.c_orderline_id AND stock.m_locator_id = iol.m_locator_id AND stock.m_attributesetinstance_id = COALESCE(iol.m_attributesetinstance_id, '0') AND iol.em_obwpl_pickinglist_id IS NOT NULL WHERE iol.m_inoutline_id IS NOT NULL OR (stock.m_warehouse_id IN (SELECT ow.m_warehouse_id FROM ad_org_warehouse ow WHERE ow.ad_org_id = sol.ad_org_id))]]></view> </database>

Notes
(0092856) shuehner (administrator) 2016-12-20 14:04	Example diff of export.db on PostgreSQL 9.5 compared to tip of the module repo.

(0110331) hgbot (developer) 2019-03-10 19:24	Repository: erp/pmods/org.openbravo.warehouse.pickinglist Changeset: ded71ebe516565655c94055fe367eee8ea0b5538 Author: Stefan Hühner <stefan.huehner <at> openbravo.com> Date: Sun Mar 10 18:34:10 2019 +0100 URL: http://code.openbravo.com/erp/pmods/org.openbravo.warehouse.pickinglist/rev/ded71ebe516565655c94055fe367eee8ea0b5538 [^] Fixed 34787. Avoid dbconsistency issue with PostgreSQL >=9.5 As explained in issue 34787 there is a non-solvable dbconsistency issue with this view depending on postgres version. When exporting it with PostgreSQL >= 9.5 an extra set of parenthesis is added: from - COALESCE(sd.m_attributesetinstance_id, '0'), to - (COALESCE(sd.m_attributesetinstance_id, '0')), This is a formal change only with no functional impact. As preparation to move Openbravo CI to default to PostgreSQL 10 when running test commit this change once to get a clean export on this newer version. Note: This will create the inverse problem when running dbconsistency check on older postgres like 9.3 (no issue as not tested by default in CI anymore soon). --- M src-db/database/model/views/OBWPL_PICKINGLIST_PICK_EDIT.xml ---

(0110336) vmromanos (manager) 2019-03-11 09:58	Code review OK

Issue History
Date Modified	Username	Field	Change
2016-12-20 14:03	shuehner	New Issue
2016-12-20 14:03	shuehner	Assigned To	=> platform
2016-12-20 14:03	shuehner	Modules	=> Core
2016-12-20 14:03	shuehner	Triggers an Emergency Pack	=> No
2016-12-20 14:04	shuehner	Note Added: 0092856
2016-12-20 14:04	shuehner	File Added: org.openbravo.warehouse.pickinglist.dbcons95.diff
2016-12-21 09:39	alostale	Status	new => acknowledged
2019-03-10 19:24	hgbot	Checkin
2019-03-10 19:24	hgbot	Note Added: 0110331
2019-03-10 19:24	hgbot	Status	acknowledged => resolved
2019-03-10 19:24	hgbot	Resolution	open => fixed
2019-03-10 19:24	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/pmods/org.openbravo.warehouse.pickinglist/rev/ded71ebe516565655c94055fe367eee8ea0b5538 [^]
2019-03-10 19:25	shuehner	Assigned To	platform => shuehner
2019-03-10 19:25	shuehner	Review Assigned To	=> vmromanos
2019-03-11 09:58	vmromanos	Note Added: 0110336
2019-03-11 09:58	vmromanos	Status	resolved => closed

Relationships [ Relation Graph ] [ Dependency Graph ]