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:
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)
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)