Unique Constraint should ensure unique Import Loader Format Field to avoid invalid SQL

Description

CAUSE

Test this case on the Import Price List (I_PriceList) table. The error occurs, when on the corresponding Import Loader Format record multiple fields exist, that refer to the same Column (e.g. List Price in our case):

In this case the import causes the following error:

06:46:14.682===========> DB.executeUpdate: UPDATE I_PriceList SET ProductValue='9100010',M_PriceList_ID='1006005',M_PriceList_Version_ID='1005486',PriceList=83.33,PriceStd=83.33,PriceList=1,IsActive='Y',Processed='N',I_IsImported='N',Updated=getDate(),UpdatedBy=1000579 WHERE I_PriceList_ID=1625488 [null] [253462]
org.postgresql.util.PSQLException: ERROR: multiple assignments to same column "pricelist"; State=42601; ErrorCode=0
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:152)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at jdk.internal.reflect.GeneratedMethodAccessor98.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.compiere.db.StatementProxy.invoke(StatementProxy.java:130)
	at com.sun.proxy.$Proxy16.executeUpdate(Unknown Source)
	at org.compiere.util.DB.executeUpdate(DB.java:1035)
	at org.compiere.util.DB.executeUpdate(DB.java:894)
	at org.compiere.util.DB.executeUpdate(DB.java:881)
	at org.compiere.impexp.ImpFormat.updateDB(ImpFormat.java:670)
	at org.adempiere.webui.apps.form.WFileImport.cmd_process(WFileImport.java:603)
	at org.adempiere.webui.apps.form.WFileImport.onEvent(WFileImport.java:348)
	at org.zkoss.zk.ui.AbstractComponent.onEvent(AbstractComponent.java:3191)

Generated a wrong SQL with two PriceList (= List Price) columns:

UPDATE I_PriceList
   SET ProductValue = '9500013',
       M_PriceList_ID = '1006009',
       M_PriceList_Version_ID = '1005490',
       PriceList = 0.000,
       PriceStd = 0.000,
       PriceList = 0, --- CAUSE INVALID SQL QUERY !!!!!!!!!! 2nd colmumn
       IsActive = 'Y',
       Processed = 'N',
       I_IsImported = 'N',
       Updated = getDate(),
       UpdatedBy = 1000579
WHERE I_PriceList_ID = 1548821

Solution:

Trivial fix - create a unique constraint tor the AD_ImpFormat_Row table with the AD_ImpFormat_ID and AD_Column_ID columns.

Note:

Here is an SQL to find all duplicated format fields:

select r1.ad_impformat_id, f.name as formatname, r1.ad_column_id, c.name as columnname, *
from ad_impformat_row r1
join ad_impformat_row r2 on (
	r1.ad_impformat_id = r2.ad_impformat_id
	and r1.ad_impformat_row_id != r2.ad_impformat_row_id
	and r1.ad_column_id = r2.ad_column_id
)
join ad_impformat f on (r1.ad_impformat_id = f.ad_impformat_id)
join ad_column c on (r1.ad_column_id = c.ad_column_id)

Environment

v11

Attachments

03 Nov 2023, 07:22 AM

Linked issues

causes

IDEMPIERE-6224

Unique Constraint `ad_impformat_row_ad_column` breaks ImpFormat concat functions

Activity

Show:

Fixed

Details

Assignee

Peter Takacs

Reporter

Former user(Deactivated)

Fix versions

Priority

Major

Created November 3, 2023 at 6:56 AM

Updated September 5, 2024 at 12:03 PM

Resolved December 19, 2023 at 1:37 PM

Configure