1 Reply Latest reply on Feb 25, 2016 6:22 PM by Steven Hawkins

    Teiid Impala Translator decimal pushdown

    Don Krapohl Newbie

      I'm using the Impala translator and can't seem to get the decimal or bigdecimal data types to push down with aggregate functions.  Here's my scenarios, the first of which using DOUBLE pushes down, the latter as DECIMAL does not.  I'm wondering if it's an issue with casting, mixed datatypes (long/bigdecimal/decimal) or decimal pushdown (again, even though I set the databaseVersion to 2.0 in line with advice in the comments of [TEIID-3937] Multiplying two columns in CASE statement overrides pushdown (Impala) - JBoss Issue Tracker)

       

      Scenario 1:

      This does not push down, even using the translator override databaseVersion set to 2.0 or 2.2:

       

      data types:

      FAPD

      ok long

      pk long

      ak long

      date_key string

      num_x long

      c_comm bigdecimal

      c_totl bigdecimal

       

       

      RCC

      ok long

      pk long

      ak long

      start_date_key string

      end_date_key string

      cc_comm decimal

      num_x long

       

       

      OUTPUT type of ccc_calc is DECIMAL, column casts are DECIMAL

       

       

       

       

      select

      (CASE WHEN cast(

        fapd.rcc.cc_comm AS decimal

        ) IS NOT NULL

        THEN

        (

        cast(

        fapd.rcc.cc_comm AS decimal

        )

        *

        ifnull(

        cast(

        fapd.fapd.num_x AS decimal

        ), 0.0

        )

        )

        ELSE ifnull(

        cast(

        fapd.fapd.c_comm AS decimal

        ), 0.0

        )

        END

        +

        ifnull(

        cast(

        fapd.fapd.c_totl AS decimal

        ), 0.0

        )

      ) AS ccc_calc

       

       

      from fapd.fapd

      LEFT OUTER JOIN /*+ optional */ fapd.rcc

      ON fapd.fapd.ok = fapd.rcc.ok

      AND fapd.fapd.pk = fapd.rcc.pk

      AND fapd.fapd.ak = fapd.rcc.ak

      AND fapd.fapd.date_key BETWEEN fapd.rcc.start_date_key AND fapd.rcc.end_date_key

       

       

      ------------------------

      This DOES push down, even without a translator override (only change is query casts are double and output is double)

       

       

      data types:

      FAPD

      ok long

      pk long

      ak long

      date_key string

      num_x long

      c_comm bigdecimal

      c_totl bigdecimal

       

       

      RCC

      ok long

      pk long

      ak long

      start_date_key string

      end_date_key string

      cc_comm decimal

      num_x long

       

       

      OUTPUT type of ccc_calc is DOUBLE

       

       

      select

      (CASE WHEN cast(

        fapd.rcc.cc_comm AS DOUBLE

        ) IS NOT NULL

        THEN

        (

        cast(

        fapd.rcc.cc_comm AS DOUBLE

        )

        *

        ifnull(

        cast(

        fapd.fapd.num_x AS DOUBLE

        ), 0.0

        )

        )

        ELSE ifnull(

        cast(

        fapd.fapd.c_comm AS DOUBLE

        ), 0.0

        )

        END

        +

        ifnull(

        cast(

        fapd.fapd.c_totl AS DOUBLE

        ), 0.0

        )

      ) AS ccc_calc

      from fapd.fapd

      LEFT OUTER JOIN /*+ optional */ fapd.rcc

      ON fapd.fapd.ok = fapd.rcc.ok

      AND fapd.fapd.pk = fapd.rcc.pk

      AND fapd.fapd.ak = fapd.rcc.ak

      AND fapd.fapd.date_key BETWEEN fapd.rcc.start_date_key AND fapd.rcc.end_date_key