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 "Site"."SiteID" "Site_ID",
case when "Site"."SiteID" = 1 then 'Mel' else 'Non Mel' end EGM
from "AD_D" "Site", "AD_F" "EGM_Daily"
where "Site"."SiteID" = "EGM_Daily"."SiteID"
and "Site"."SiteID" = 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("Site"."SiteID" as integer) = 1, then the query will execute successfully.
2) if removing the "Site"."SiteID" = 1, the query also executes successfully.
3) if changing the "Site"."SiteID" = 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 "Site"."SiteID" 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.