Joining with view versus joining with procedure
Exactly the same query is executed on two systems using the same version of Teiid and pointing to the same backend. The only difference is that one system defines "chorus_time.timetable" as a view and the other one as a procedure.
SELECT ........
FROM (SELECT CHORUS_B.*
FROM (SELECT * FROM chorus_time.timetable WHERE period = 30000) AS chorus_refresh_expression,
(SELECT * FROM SECURITY_USERUPDATE.BASEUSER) AS CHORUS_B)
AS CHORUS_B
LEFT OUTER JOIN
(SELECT CHORUS_J0.*
FROM (SELECT * FROM chorus_time.timetable WHERE period = 30000) AS chorus_refresh_expression,
(SELECT * FROM SECURITY.SYSINFOVIEW) AS CHORUS_J0)
AS CHORUS_J0
ON CHORUS_J0.sysid = CHORUS_B.sysid
WHERE (CHORUS_B.USERID = '....') AND (CHORUS_B.SYSID = '......') LIMIT 1
Query is successfully executed on the system where timetable defind as procedure and executes endlessly on the second one. It seems to be related to LEFT OUTER JOIN between cross product joins
All differences in execution plan seem to be related to how Teiid is processing join with timetable.
Is it expected behavior ?
Here is a fragment of one of the differences in the plan:
============= procedure (query returns results) ================
CANONICAL PLAN:
.................
Join(groups=[chorus_refresh_expression, CHORUS_B], props={JOIN_TYPE=CROSS JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[]})
Source(groups=[chorus_refresh_expression], props={NESTED_COMMAND=SELECT * FROM chorus_time.timetable WHERE period = 30000, SYMBOL_MAP={chorus_refresh_expression.ts_start=chorus_time.timetable.ts_start, chorus_refresh_expression.ts_stop=chorus_time.timetable.ts_stop, chorus_refresh_expression.EXPECTED_PERIOD=chorus_time.timetable.EXPECTED_PERIOD, chorus_refresh_expression.ACTUAL_PERIOD=chorus_time.timetable.ACTUAL_PERIOD, chorus_refresh_expression.EXECUTION_COUNT=chorus_time.timetable.EXECUTION_COUNT, chorus_refresh_expression.period=chorus_time.timetable.period}})
Project(groups=[chorus_time.timetable], props={PROJECT_COLS=[chorus_time.timetable.ts_start, chorus_time.timetable.ts_stop, chorus_time.timetable.EXPECTED_PERIOD, chorus_time.timetable.ACTUAL_PERIOD, chorus_time.timetable.EXECUTION_COUNT, chorus_time.timetable.period]})
Select(groups=[chorus_time.timetable], props={SELECT_CRITERIA=period = 30000})
Source(groups=[chorus_time.timetable], props={NESTED_COMMAND=SELECT X.*, chorus_time.timetable.period AS period FROM (EXEC chorus_time.timetable(chorus_time.timetable.period)) AS X, SYMBOL_MAP={chorus_time.timetable.ts_start=X.ts_start, chorus_time.timetable.ts_stop=X.ts_stop, chorus_time.timetable.EXPECTED_PERIOD=X.EXPECTED_PERIOD, chorus_time.timetable.ACTUAL_PERIOD=X.ACTUAL_PERIOD, chorus_time.timetable.EXECUTION_COUNT=X.EXECUTION_COUNT, chorus_time.timetable.period=chorus_time.timetable.period}})
Project(groups=[X], props={PROJECT_COLS=[X.ts_start, X.ts_stop, X.EXPECTED_PERIOD, X.ACTUAL_PERIOD, X.EXECUTION_COUNT, chorus_time.timetable.period AS period]})
Source(groups=[X], props={NESTED_COMMAND=EXEC chorus_time.timetable(chorus_time.timetable.period), SYMBOL_MAP={X.ts_start=chorus_time.timetable.ts_start, X.ts_stop=chorus_time.timetable.ts_stop, X.EXPECTED_PERIOD=chorus_time.timetable.EXPECTED_PERIOD, X.ACTUAL_PERIOD=chorus_time.timetable.ACTUAL_PERIOD, X.EXECUTION_COUNT=chorus_time.timetable.EXECUTION_COUNT}})
Project(groups=[], props={PROJECT_COLS=[chorus_time.timetable.ts_start, chorus_time.timetable.ts_stop, chorus_time.timetable.EXPECTED_PERIOD, chorus_time.timetable.ACTUAL_PERIOD, chorus_time.timetable.EXECUTION_COUNT]})
Source(groups=[chorus_time.timetable], props={VIRTUAL_COMMAND=EXEC chorus_time.timetable(chorus_time.timetable.period)})
===============view ( query hangs) ========
CANONICAL PLAN:
....................
Join(groups=[chorus_refresh_expression, CHORUS_B], props={JOIN_TYPE=CROSS JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[]})
Source(groups=[chorus_refresh_expression], props={NESTED_COMMAND=SELECT * FROM chorus_time.timetable WHERE period = 30000, SYMBOL_MAP={chorus_refresh_expression.TS_START=chorus_time.timetable.TS_START, chorus_refresh_expression.TS_STOP=chorus_time.timetable.TS_STOP, chorus_refresh_expression.PERIOD=chorus_time.timetable.PERIOD, chorus_refresh_expression.ACTUAL_PERIOD=chorus_time.timetable.ACTUAL_PERIOD, chorus_refresh_expression.EXECUTION_COUNT=chorus_time.timetable.EXECUTION_COUNT}})
Project(groups=[chorus_time.timetable], props={PROJECT_COLS=[chorus_time.timetable.TS_START, chorus_time.timetable.TS_STOP, chorus_time.timetable.PERIOD, chorus_time.timetable.ACTUAL_PERIOD, chorus_time.timetable.EXECUTION_COUNT]})
Select(groups=[chorus_time.timetable], props={SELECT_CRITERIA=period = 30000})
Source(groups=[chorus_time.timetable])
