Symptom
IQ server returned an error "invalid expression near ' 1=1 '" against a specific query.
The problematic query worked fine in IQ 15.4, but failed after migrating to IQ 16.0.
Also, setting a temporary option REVERT_TO_V15_OPTIMIZER='ON' in IQ 16 still got the same issue.
[Query]
Select distinct ALS001.COL001 ALS002,
case when ALS001.COL001 = 1 then 'AAA' else 'BBB' end ALS003
from "TAB001" ALS001, "TAB002" "ALS004"
where ALS001.COL001 = "ALS004".COL001
and ALS001.COL001 = 1 // THE PROBLEMATIC CONDITION
[Error]
+21,E,27,-156,Invalid expression near ' 1=1 '
[Findings]
If no foreign key constraint specified, the error does not happen.
If a foreign key constraint is added and,
1) if changing the filter to cast(ALS001.COL001 as integer) = 1, then the query will execute successfully.
2) if removing the ALS001.COL001 = 1, the query also executes successfully.
3) if changing the ALS001.COL001 = 3, the query also executes successfully(Of course, it's wrong results.).
4) if replacing "=" in the CASE statement with "IN" the query also executed successfully. // when ALS001.COL001 IN (1)
5) if changing the query to Ansi Join, It's still getting an invalid error.
Read more...
Environment
IQ Version: SAP IQ16 SP11 PL06
OS info: Windows 2003
Product
Keywords
SAP IQ, IQ, invalid expression near, invalid expression, CR 809805, CR#809805, CR809805 , KBA , BC-SYB-IQ , Sybase IQ , Problem
About this page
This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.
SAP Knowledge Base Article - Preview