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.