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 ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.