[teiid-issues] [JBoss JIRA] (TEIID-3802) HANA translator modifies boolean to tinyint in type conversion

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-3802?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins reassigned TEIID-3802:
-------------------------------------

    Assignee: Ted Jones  (was: Steven Hawkins)


Ted was the reasoning here that older Hana versions did not have a boolean type - http://scn.sap.com/thread/3323449

It looks like it does now - https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/a1569875191014b507cf392724b7eb/content.htm

So we may need to just disallow usage of the convert function to boolean on older Hana instances.

> HANA translator modifies boolean to tinyint in type conversion
> --------------------------------------------------------------
>
>                 Key: TEIID-3802
>                 URL: https://issues.jboss.org/browse/TEIID-3802
>             Project: Teiid
>          Issue Type: Bug
>          Components: Misc. Connectors
>    Affects Versions: 8.7.1.6_2
>            Reporter: Jan Stastny
>            Assignee: Ted Jones
>
> Teiid modifies boolean data type to tinyint before pushing the query down to the HANA instance.
> This brings following issue while having a query similar to this one:
> {code:sql}
> SELECT BQT1.SMallA.BooleanValue,cast(BQT2.SmallB.CharValue as boolean) FROM BQT1.SmallA,BQT2.SmallB WHERE BQT1.SmallA.BooleanValue = cast(BQT2.SmallB.CharValue as boolean)
> {code}
> which Teiid modifies to:
> {code:sql}
> SELECT g_0."BOOLEANVALUE", cast(g_1."CHARVALUE" AS tinyint) FROM "BQT1"."SMALLA" AS g_0, "BQT2"."SMALLB" AS g_1 WHERE g_0."BOOLEANVALUE" = cast(g_1."CHARVALUE" AS tinyint)
> {code}
> But the problem is not present for:
> {code:sql}
> SELECT BQT1.SMallA.BooleanValue FROM BQT1.SmallA WHERE BQT1.SmallA.BooleanValue = cast(BQT1.SmallA.CharValue as boolean)
> {code}
> It seems that the Cartesian product and comparing values from different sources are the cause of the issue. But HANA has internal type BOOLEAN, so there might be no point at modifying the type to TINYINT after all. I also checked, that the pushed query (which causes the issue) with the casting altered to BOOLEAN runs well on HANA. The following query returns expected results when pushed directly to HANA instance:
> {code:sql}
> SELECT g_0."BOOLEANVALUE", cast(g_1."CHARVALUE" AS boolean) FROM "BQT1"."SMALLA" AS g_0, "BQT2"."SMALLB" AS g_1 WHERE g_0."BOOLEANVALUE" = cast(g_1."CHARVALUE" AS boolean)
> {code}
> NOTE: The CharValue columns are modelled as VARCHAR/string in fact. Thus the cast is a valid operation.



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)