SAP Knowledge Base Article - Preview

2508848 - "Invalid expression" error happened from a problematic query

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

SAP IQ 16.0

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.