[teiid-issues] [JBoss JIRA] (TEIID-2345) source hints are not honoured for nested sqls

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-2345:
---------------------------------------

I'll add an example to make this clear, but the usage of keep aliases is using square braces to indicate the syntax as optional.  So you'd use:

{code}SELECT /*+ sh KEEP ALIASES:'foo' bar:'leading' */ ...{code}

KEEP ALIASES simply directs the optimizer to allow the aliases used in the user query flow to source presumably because the hint is specific to some tables and the user needs a stable way to reference them (and doesn't want to use the auto generated aliases).  Otherwise KEEP ALIASES does not change the functionality of how the source hint is propagated to the source executions.

> If i use below general hint is available only to the first execution.

What do you mean by first execution?  There is only a single source access represented by that user query.


                
> source hints are not honoured for nested sqls
> ---------------------------------------------
>
>                 Key: TEIID-2345
>                 URL: https://issues.jboss.org/browse/TEIID-2345
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 8.1
>            Reporter: Vineela Gampa
>            Assignee: Steven Hawkins
>
> Hi
> I want to use general hints below. I observed i was able to put my sourcehint as below which yields generalhint information to data_window procedure. As per this document https://docs.jboss.org/author/display/TEIID/Query+Plans if i use KEEP_ALIASES general hint is available for all nested queries but i was not able to get it working
> SELECT /*+ sh[KEEP ALIASES]:'foo' bar:'leading' */ \r\n" when i use something like this general hint is null in all the executions. 
> If i use below general hint is available only to the first execution.
> "SELECT /*+ sh:'foo' bar:'leading' */ \r\n"
>                                                                                + "    chorus_results.* \r\n"
>                                                                                + "FROM (\r\n"
>                                                                                + "    EXEC data_window('select pkey, counter from testing.bigtable WHERE pkey=? LIMIT 300', ?)\r\n"
>                                                                                + ") AS chorus_data_window, ARRAYTABLE(chorus_data_window.tuple COLUMNS pkey long, counter long) AS chorus_results LIMIT 0, 20",

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira