AD_Tab => Where Clause => Using "WHERE" in uppercase causes the tab's SQL to be malformed
Description
Environment
Activity
Chuck Boecking November 2, 2015 at 7:45 PM
Closing as a documentation issue.
Carlos Ruiz October 30, 2015 at 11:41 PM
I would say that's a misconfiguration - if you configure it properly then it works - if you configure it wrongly then it doesn't work.
I think the most prominent issue here is that this is an undocumented feature - you could consider it a "documentation bug" better.
Chuck Boecking October 30, 2015 at 11:36 PM
I think the ability to type in a "WHERE" statement to bypass the MRole.addAccessSQL is a feature. That is pretty cool - I did not know that. Having the result end up in a malformed SQL statement like stated in the description (see bold text) is a bug. It causes the system to fail due to a SQL exception.
Carlos Ruiz October 30, 2015 at 11:25 PM
I think is not a bug, it is a feature, and consequently the ticket closure must be "Won't fix" or "Incomplete"
Chuck Boecking October 30, 2015 at 10:59 PM
- so I think your answer is yes (at least sometimes) to " is this because the system is expecting users admins to enter SQL key terms in upper case?". And yes - this is a bug. And yes - the issue goes much deeper that this ticket represents. Therefore, I guess we can close this ticket as unresolved unless someone wants to deep dive and fix it.
Details
Details
Assignee
Reporter
Components
Priority
Minor
UPDATE: Closing this ticket as a documentation issue. Here are the comments/advice moving forward:
Generally speaking, admins should use upper case when writing SQL key words.
There are times when iDempiere's is not capable parsing the SQL correctly when admins inject SQL in ColumnSQL or WhereCause fields. This ticket represents an example. Others are listed below.
You can identify this situation by reviewing the logs and reading the resulting SQL generated by the parser. Error results might include "More than one FROM clause", or you receive a SQLException.
The solution is to make your SQL keywords lowercase, and therefore prevent your clause from being caught in the SQL Parser.
-----------------
Original Post
-----------------
Using the word "WHERE" in upper case in the AD_Tab => whereClause field causes a SQL exception because of a poorly formed SQL statement.
Here is how to reproduce. Add the following to the Sales Order window's Sales Order Line tab's where clause:
C_Order_ID in (Select o.C_Order_ID from C_Order o WHERE o.C_Order_ID = @C_Order_ID@)
This will cause a SQL exception. If you make the "WHERE" word lowercase, the all works as expected.
The easy solution is to modify the MTab.getWhereClause() to return a .lower version of the string.
If you want to investigate further, set a break point at GridTable line 410 where it executes the MRole.addAccessSQL.
Here is an example of the malformed SQL. See the bolded text.
SEVERE: Count SQL=SELECT COUNT FROM C_OrderLine WHERE ((C_Order_ID in (Select o.C_Order_ID from C_Order o WHERE o.C_Order_ID = 1000000)) AND C_OrderLine.C_Order_ID=1000000) AND C_OrderLine.AD_Client_ID IN(0,11) AND C_OrderLine.AD_Org_ID IN(50007,0,50004,50005,50006,50000,50001,50002,11,12) AND (WHERE.C_OrderLine_ID IS NULL OR WHERE.C_OrderLine_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 260 AND AD_User_ID <> 100 AND IsActive = 'Y' ))
Please review. If you agree with my assessment, just let me know and I can make the patch to make the whereClause getter return lower case.
Regards,
Chuck Boecking
www.chuckboecking.com