[teiid-issues] [JBoss JIRA] (TEIID-1828) Dependent critiera in query plan but missing in actual query

[Steven Hawkins (Commented) (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-1828?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12642638#comment-12642638 ] 

Steven Hawkins commented on TEIID-1828:
---------------------------------------

This is expected behavior.  For a cost based dependent join we also determine upper bound values after which we believe that a dependent join will be ineffective.  See also https://issues.jboss.org/browse/TEIID-1533.  In this particular query the relevant estimated source query cardinalities are 83 and 3408, while the actual number of distinct values from the actual executions are greater than 924 and 4687 respectively.  In other words there are many more independent values than we expected.

A couple of issues then are:
The bound values are not visible in the plan.  We also need to add a sql warning or a server log entry clarifying the decision to not use a dependent join.

For this case the main question is whether we made the right decision.  Given the cost information we have, we believe so.  However the bound may be too tight and is based upon conservative assumptions (in part based upon other side of join which is also unknown) about distinct value counts. 

Additional cost information would help us make a better decision.  For example if I change the ndv of account_code from the unknown value to 1/10th of the table cardinality (of course, the actual value can be different), then the back off value goes from 924 to 435523 - since we are more assured that additional independent values will return limited results.
                
> Dependent critiera in query plan but missing in actual query
> ------------------------------------------------------------
>
>                 Key: TEIID-1828
>                 URL: https://issues.jboss.org/browse/TEIID-1828
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 7.4.1
>         Environment: Oracle datasource, but likely all
>            Reporter: Tom Fonteyne
>            Assignee: Van Halbert
>
> tested on EDS 5.2 ER3 and ER6, same results:
> We're running a query which has two sets of dependent criteria in one of the queries. The plan looks ok, and in the command log we see that EDS is getting both sets of dependent criteria and bringing back rows which we would expect to be passed into the final query.
> The problem is that the final query has 1=1 in the place of where the dependent criteria should be.
> In the plan:
> + Query:SELECT g_0.account_code AS c_0, g_0.book_code AS c_1, SUM(g_0.func_amount) AS c_2 FROM TSL_SQL_Source.TSL.TSL_FINANCIAL_ACCOUNT_ITEMS_T2 AS g_0 WHERE (g_0.business_date = {d'2011-06-29'}) AND (g_0.account_code IN (<dependent values>)) AND (g_0.book_code IN (<dependent values>)) GROUP BY g_0.account_code, g_0.book_code ORDER BY c_0 NULLS FIRST
> The source specific query from the command log:
> 2011-11-14 11:35:35,313 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue60) Source-specific command: SELECT g_0.ACCOUNT_CODE AS c_0, g_0.BOOK_CODE AS c_1, SUM(g_0.FUNC_AMOUNT) AS c_2 FROM TSL_FINANCIAL_ACCOUNT_ITEMS_T2 g_0 WHERE g_0.BUSINESS_DATE = {d '2011-06-29'} AND 1 = 1 AND 1 = 1 GROUP BY g_0.ACCOUNT_CODE, g_0.BOOK_CODE ORDER BY c_0 NULLS FIRST
> Stats should be correct in the VDB. We also updated the stats using the system procedures just before executing the query.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira