Teiid Impala Translator decimal pushdown
don.krapohl Feb 25, 2016 4:43 PMI'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