OBIEE 12c - Impala - Nested TIMESTAMPADD,TIMESTAMPDIFF Does Not Get Submitted In The Physical Query

Last updated on MARCH 02, 2018

Applies to:

Symptoms

OBIEE 12c - Impala - Nested TIMESTAMPADD,TIMESTAMPDIFF does not get submitted in the physical query

After installing the patch 26437161 - IMPALA TRIM AND TIMESTAMPADD,TIMESTAMPDIFF ISSUE. The nested timestampadd functions does not get propagated/submitted in the physical SQL to the DB.

For example TimeStampAdd(SQL_TSI_MONTH, -12, Current_Date) gets submitted as add_months('2017-09-26', -12) but TimeStampAdd(SQL_TSI_DAY,-(DayOfMonth(Current_Date)), TimeStampAdd(SQL_TSI_MONTH, -12, Current_Date)) does not show up on the physical SQL.

Nested timestampadds are supported with Oracle as a DB, the function gets translated and gets pushed down to the DB in the SQL.

This is coming after customer applied this patch:
BUG 26437161 - IMPALA TRIM AND TIMESTAMPADD,TIMESTAMPDIFF ISSUE

After applying the patch, nested TIMESTAMPADD functions does not get
propagated/submitted in the physical SQL to the DB.

For example TimeStampAdd(SQL_TSI_MONTH, -12, Current_Date) gets submitted as
add_months('2017-09-26', -12) but
TimeStampAdd(SQL_TSI_DAY,-(DayOfMonth(Current_Date)),
TimeStampAdd(SQL_TSI_MONTH, -12, Current_Date)) does not show up on the
physical SQL.

Nested TIMESTAMPADD are supported with Oracle as a DB, the function gets
translated and gets pushed down to the DB in the SQL. Need same
functionality with IMPALA DB. Patch provided is not fixing the issue.

Changes

 [2017-10-02T18:43:33.376+00:00] [OBIS] [TRACE:2] [] [] [ecid:
759a78b0-ff35-45d3-a3f9-bb6df579c925-000004f0,0:1:9:3] [sik: ssi] [tid:
ca6d3700] [messageid: USER-0] [requestid: 80c60012] [sessionid: 80c60000]
[username: administrator] ############################################## [[
-------------------- SQL Request, logical request hash:
7054ff27
SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/shared/Company
T-Mobile/Multi_Timestampadd_Issue';SELECT
0 s_0,
"GSM Analysis Roaming Outbound (Home)"."Process Date"."Process Date" s_1,
"GSM Analysis Roaming Outbound (Home)"."Their Operator"."Their PMN (TADIG)
Code" s_2,
TimeStampAdd(SQL_TSI_DAY,-(DayOfMonth(Current_Date)),
TimeStampAdd(SQL_TSI_MONTH, -12, Current_Date)) s_3,
TimeStampAdd(SQL_TSI_MONTH, -12, Current_Date) s_4,
"GSM Analysis Roaming Outbound (Home)"."TAP Detail Information"."Number of
Unique Roamers" s_5
FROM "GSM Analysis Roaming Outbound (Home)"
WHERE
(("Their Operator"."Their Country Name" = 'UNITED STATES') AND ("Process
Date"."Process Date" = date '2017-08-27'))
ORDER BY 3 ASC NULLS LAST, 5 ASC NULLS LAST, 4 ASC NULLS LAST, 2 ASC NULLS
LAST
FETCH FIRST 10000000 ROWS ONLY

]]
[2017-10-02T18:43:33.377+00:00] [OBIS] [TRACE:2] [] [] [ecid:
759a78b0-ff35-45d3-a3f9-bb6df579c925-000004f0,0:1:9:3] [sik: ssi] [tid:
ca6d3700] [messageid: USER-23] [requestid: 80c60012] [sessionid: 80c60000]
[username: administrator] -------------------- General Query Info: [[
Repository: ssi, Subject Area: GSM Analysis Roaming Outbound (Home),
Presentation: GSM Analysis Roaming Outbound (Home)

]]
[2017-10-02T18:43:33.387+00:00] [OBIS] [TRACE:2] [] [] [ecid:
759a78b0-ff35-45d3-a3f9-bb6df579c925-000004f0,0:1:9:5] [sik: ssi] [tid:
ca6d3700] [messageid: USER-18] [requestid: 80c60012] [sessionid: 80c60000]
[username: administrator] -------------------- Sending query to database
named ACDW - Impala DW (id: <<184260>>), connection pool named ACDW, logical
request hash 7054ff27, physical request hash 191159a7: [[
select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c5 as c5,
D1.c6 as c6
from
(select distinct 0 as c1,
D1.c3 as c2,
D1.c2 as c3,
add_months('2017-10-02', -12) as c5,
D1.c1 as c6
from
(select count(distinct T592190.imsi) as c1,
T592190.vpmn as c2,
T592190.process_date as c3
from

company_dim T596201 inner join
v_cef_hpmn T592190 On (T592190.process_date =
'2017-08-27' and T592190.visited_company_id = T596201.company_id and
T596201.country_name = 'UNITED STATES')
group by T592190.vpmn, T592190.process_date
) D1
) D1

]]

Cause