[teiid-issues] [JBoss JIRA] Resolved: (TEIID-1000) Dependent Join in Virtual Model not pushing criteria to both sources

[Steven Hawkins (JIRA)]

     [ https://jira.jboss.org/browse/TEIID-1000?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-1000.
-----------------------------------

    Fix Version/s:     (was: 8.0)
       Resolution: Won't Fix


upon reexamining this issue it's not clear to me what the customer expectation was.  Since this is using a NOT IN, in general a dependent join based upon the subquery is not an option.  A more specific case could be that for a non-correlated NOT IN we use stats to guess at the set size and if sufficiently small we perform the subquery, then if it actually is smaller than the max in of the dependent side, then perform a dependent join.

Another possibility is that if this is not an optional join scenario, then it would not be possible to push the subquery criteria to the source (if it is an optional join scenario an earlier change now allows subqueries to be pushed through views).  

Another is that like criteria and the costing stats to should trigger a dependent join.  Without having the stats it's impossible it's hard to know if we are making the right decision.

Given the age of the issue and the narrow applicability of the first scenario I'm inclined to mark as not fixed.

> Dependent Join in Virtual Model not pushing criteria to both sources
> --------------------------------------------------------------------
>
>                 Key: TEIID-1000
>                 URL: https://jira.jboss.org/browse/TEIID-1000
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>            Reporter: Steven Hawkins
>
> Sample models are attached.  User query is:
> SELECT * FROM v_TV_SHOWS.v_SHOWS_ACTORS2
> WHERE SHOW_NAME LIKE '%E%' AND 
> NOT ACTOR_SHOW_ID IN (SELECT ACTOR_SHOW_ID FROM v_TV_SHOWS.v_SHOWS_ACTORS2  WHERE ACTOR_NAME = 'MALCOLM JAMAL-WARNER')
> OPTION DEBUG and MMProcess.log are attached.
> Customer complaint is that the query contains criteria on ACTOR_NAME and ACTOR_SHOW_ID which is coming from Oracle and on SHOW_NAME which is coming from SQL Server.  In the query plan, there is a SELECT node that does not have criteria to Oracle.  Instead it is SELECT * which pulls all records.  Customer tables are huge so this is an issue for them.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira