Advanced Lookup on sub-tab of a window triggers a PSQL Exception
Description
Steps to reproduce: 1. Open Table and Column Window and click OK to load all records 2. On the Column tab, click the 'Edit Record' button 3. Click on the 'Lookup Record' button and select the 'Advanced' tab 4. Add a filter condition, eg. Allow Logging = Yes 5. Click OK
The following exception is then generated: org.postgresql.util.PSQLException: ERROR: syntax error at or near "IsAllowLogging"
This is the generated SQL, which is missing an 'AND' TabInfo_FindSQL == SELECT COUNT FROM AD_Column WHERE AD_Column.AD_Table_ID=539 AND (AD_Column.AD_Table_ID=539 IsAllowLogging='Y') AND AD_Client_ID=0 AND AD_Org_ID=0 AND (AD_Column_ID IS NULL OR AD_Column_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 101 AND AD_User_ID <> 100 AND IsActive = 'Y' ))
Was able to reproduce on test.idempiere.org, but the error does not occur on 8.2 Release demo.globalqss.com.
I suspect the issue was introduced with the changes implemented in IDEMPIERE-4470
I have updated the patch to include a second commit fixing the issues relating to “Product Category” column and columns with display type “Chosen Multiple Selection List”.
Tony Snook
October 18, 2021 at 1:16 AM
(edited)
After further investigation it appears has broken other Advanced Search functionality, in addittion to what I have reported above.
For example:
Any advanced search including “Product Category” column will fail.
Any advanced search including columns with display type “Chosen Multiple Selection List” will fail.
Steps to reproduce:
1. Open Table and Column Window and click OK to load all records
2. On the Column tab, click the 'Edit Record' button
3. Click on the 'Lookup Record' button and select the 'Advanced' tab
4. Add a filter condition, eg. Allow Logging = Yes
5. Click OK
The following exception is then generated:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "IsAllowLogging"
This is the generated SQL, which is missing an 'AND'
TabInfo_FindSQL ==
SELECT COUNT FROM AD_Column WHERE AD_Column.AD_Table_ID=539
AND (AD_Column.AD_Table_ID=539 IsAllowLogging='Y') AND AD_Client_ID=0
AND AD_Org_ID=0 AND (AD_Column_ID IS NULL OR AD_Column_ID NOT IN
( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 101
AND AD_User_ID <> 100 AND IsActive = 'Y' ))
Was able to reproduce on test.idempiere.org, but the error does not occur on 8.2 Release demo.globalqss.com.
I suspect the issue was introduced with the changes implemented in IDEMPIERE-4470
I have attached a patch, as a possible fix.