Teiid not pushing the Join SQL to the translator
Hi,
We are using Teiid 8.7 and looks like in one of the case Teiid is not pushing the joined SQL to the translator. Instead it is breaking the SQL into two and sending individual SQL's to the translator.
Since the translator supports joins we expect Teiid to push the complete SQL to the translator.
Our translator expects some columns in the where clause using which it uses to processes the SQL. Since after breaking of the SQL into two individual SQL's the required parameters are not passed as part of the second SQL.
The following are the properties that are set in the translator's constructor:
super.setSupportsOuterJoins(true);
super.setSupportsFullOuterJoins(true);
super.setSupportsInnerJoins(true);
super.setSupportsOrderBy(true);
super.setSupportsSelectDistinct(true);
The following is the SQL and attached is the Query Plan:
BEGIN
SELECT
*
FROM chorus_time.timetable
WHERE period = 30000 WITHOUT RETURN; WITH TB AS
(
SELECT
*
FROM
(
SELECT
TB.*
FROM (SELECT * FROM DBMzDB2c.CAT_TABLES_Rbase_V111) AS TB
)
AS TB
WHERE (TB.dsConf = 'QA8800')
AND (TB.dsGroup = '')
AND (TB.dsSystem = 'CA11')
AND (TB.dsSSID = 'DH0G') LIMIT 12
)
SELECT
CHORUS_B.dsSSID,
CHORUS_B.NAME,
CHORUS_B.dsGroup,
CHORUS_B.CREATOR,
CHORUS_B.dsSystem,
CHORUS_B.dsConf,
CHORUS_B.TSNAME,
CHORUS_B.APPEND,
CHORUS_B.CHILDREN,
CHORUS_B.OWNER,
CHORUS_B.CHECKS,
CHORUS_B.DBID,
CHORUS_B.CREATEDBY,
CHORUS_B.EDPROC,
CHORUS_B.VERSION,
CHORUS_B.TBNAME,
CHORUS_B.ARCHIVING_TABLE,
CHORUS_B.OWNERTYPE,
CHORUS_B.CLUSTERTYPE,
CHORUS_B.NUM_DEP_MQTS,
CHORUS_B.TYPE,
CHORUS_B.RELCREATED,
CHORUS_B.RECLENGTH,
CHORUS_B.KEYCOLUMNS,
CHORUS_B.SECURITY_LABEL,
CHORUS_B.HASHKEYCOLUMNS,
CHORUS_B.VERSIONING_SCHEMA,
CHORUS_B.AUDITING,
CHORUS_B.AVGROWLEN,
CHORUS_B.CARD,
NVL(CHORUS_J0.COUNTS, 0) AS "NotesCount@@@@COUNTS",
CHORUS_B.COLCOUNT,
CHORUS_B.OBID,
CHORUS_B.NPAGESF,
CHORUS_B.NPAGES,
CHORUS_B.VALPROC,
CHORUS_B.ENCODING_SCHEME,
CHORUS_B.CHECKFLAG,
CHORUS_B.STATS_FEEDBACK,
CHORUS_B.PCTPAGES,
CHORUS_B.TABLESTATUS,
CHORUS_B.REMARKS,
CHORUS_B.CONTROL,
CHORUS_B.DATACAPTURE,
CHORUS_B.IBMREQD,
CHORUS_B.STATUS,
CHORUS_B.PCTROWCOMP,
CHORUS_B.SPLIT_ROWS,
CHORUS_B.PARTKEYCOLNUM,
CHORUS_B.CHECKRID,
CHORUS_B.ARCHIVING_SCHEMA,
CHORUS_B.CLUSTERRID,
CHORUS_B.PARENTS,
CHORUS_B.SPACEF,
CHORUS_B.LABEL,
CHORUS_B.CREATEDTS,
CHORUS_B.RBA1,
CHORUS_B.RBA2,
CHORUS_B.LOCATION,
CHORUS_B.ALTEREDTS,
CHORUS_B.CHECKRID5B,
CHORUS_B.STATSTIME,
CHORUS_B.KEYOBID,
CHORUS_B.TBCREATOR,
CHORUS_B.VERSIONING_TABLE,
CHORUS_B.CARDF,
CHORUS_B.DBNAME,
'DBMzDB2c.CAT_TABLES_Rbase_V111' AS "__objecttype__"
FROM
(
SELECT
TB.*
FROM (SELECT * FROM (SELECT * FROM TB) AS TB) AS TB,
(
SELECT
PD.*
FROM (SELECT * FROM DBMzDB2c.CAT_SYSPLANDEP_Rbase_V111) AS PD
)
AS PD
WHERE (PD.DNAME = 'DSNESPCS')
AND (PD.BTYPE IN ('B', 'C', 'G', 'M', 'T'))
AND (TB.CREATOR = PD.BCREATOR)
AND (TB.NAME = PD.BNAME)
)
AS CHORUS_B LEFT OUTER
JOIN
(
SELECT
*
FROM (SELECT * FROM chorusdata.NOTES_COUNT_SUMMARY) AS CHORUS_J0
)
AS CHORUS_J0 ON primary_key
(
'DBMzDB2c.CAT_TABLES_Rbase_V111',
'dsSSID',
CHORUS_B.dsSSID,
'NAME',
CHORUS_B.NAME,
'CREATOR',
CHORUS_B.CREATOR,
'dsGroup',
CHORUS_B.dsGroup,
'dsConf',
CHORUS_B.dsConf,
'dsSystem',
CHORUS_B.dsSystem
)
= CHORUS_J0.OBJECT_PKEY
AND IFNULL
(
CHORUS_J0.USER_ID, 0
)
=
(
SELECT
CASE COUNT
(
NOTES_COUNT_SUMMARY.USER_ID
)
WHEN 0 THEN 0 ELSE
(
SELECT
get_chorus_user."USER"
FROM
(
SELECT
*
FROM
(
SELECT
*
FROM chorus_platform_config.get_chorus_user
)
AS get_chorus_user
)
AS get_chorus_user
)
END
FROM
(
SELECT
*
FROM (SELECT * FROM chorusdata.NOTES_COUNT_SUMMARY) AS NOTES_COUNT_SUMMARY
)
AS NOTES_COUNT_SUMMARY
WHERE
(
primary_key
(
'DBMzDB2c.CAT_TABLES_Rbase_V111',
'dsSSID',
CHORUS_B.dsSSID,
'NAME',
CHORUS_B.NAME,
'CREATOR',
CHORUS_B.CREATOR,
'dsGroup',
CHORUS_B.dsGroup,
'dsConf',
CHORUS_B.dsConf,
'dsSystem',
CHORUS_B.dsSystem
)
= NOTES_COUNT_SUMMARY.OBJECT_PKEY
)
AND
(
NOTES_COUNT_SUMMARY.USER_ID =
(
SELECT
get_chorus_user."USER"
FROM
(
SELECT
*
FROM
(
SELECT
*
FROM chorus_platform_config.get_chorus_user
)
AS get_chorus_user
)
AS get_chorus_user
)
)
)
WHERE (CHORUS_B.dsConf = 'QA8800')
AND (CHORUS_B.dsGroup = '')
AND (CHORUS_B.dsSystem = 'CA11')
AND (CHORUS_B.dsSSID = 'DH0G');
END
Thanks
Rakesh
-
QueryPlan.txt.zip 29.7 KB
