Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-3802

HANA translator modifies boolean to tinyint in type conversion

    Details

    • Steps to Reproduce:
      Hide
      1. Query through Teiid:

        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)

      2. Notice the pushed query:

        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)

      3. Query HANA directly:

        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)

      Show
      Query through Teiid: 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) Notice the pushed query: 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) Query HANA directly: 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)
    • Bugzilla Update:
      Perform

      Description

      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:

      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)
      

      which Teiid modifies to:

      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)
      

      But the problem is not present for:

      SELECT BQT1.SMallA.BooleanValue FROM BQT1.SmallA WHERE BQT1.SmallA.BooleanValue = cast(BQT1.SmallA.CharValue as boolean)
      

      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:

      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)
      

      NOTE: The CharValue columns are modelled as VARCHAR/string in fact. Thus the cast is a valid operation.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                jstastny Jan Stastny
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: