[teiid-issues] [JBoss JIRA] (TEIID-4469) Insert with query expression does not apply source hint to target

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-4469:
---------------------------------------

Please provide your vdb or confirm what the source names are for the Oracle sources.  As I see the source hint being applied to the Source model query: SELECT /*+sh Source2:'append' */ g_0.intkey, g_0.intnum FROM Source.smalla AS g_0 WHERE g_0.intkey = 1

> Insert with query expression does not apply source hint to target
> -----------------------------------------------------------------
>
>                 Key: TEIID-4469
>                 URL: https://issues.jboss.org/browse/TEIID-4469
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 8.7.1.6_2
>            Reporter: Marc Shirley
>            Assignee: Steven Hawkins
>             Fix For: 9.1, 9.0.5, 8.7.10_6.2
>
>
> "INSERT INTO ... SELECT ..." statements result in a ProjectIntoNode which seems to prevent any source hints from being passed down to the relevant source.  These work correctly with an "INSERT INTO ... VALUES ..." statement.  I've included example user and final query plan data below for the non-working [1] and working [2] insert statements.
> [1] source hint not applied to ProjectIntoNode:
> USER COMMAND:
> INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") SELECT X.id AS ID, X."value" AS "VALUE" FROM (SELECT bqt.SMALLA.INTKEY AS id, bqt.SMALLA.INTNUM AS "value" FROM bqt.SMALLA WHERE bqt.SMALLA.INTKEY = 0) AS X
> ...
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> ProjectIntoNode(0) output=[Count] test.TEST
>   AccessNode(1) output=[bqt.SMALLA.INTKEY AS ID, bqt.SMALLA.INTNUM AS "VALUE"] SELECT /*+sh test:'append' */ g_0.INTKEY, g_0.INTNUM FROM bqt.SMALLA AS g_0 WHERE g_0.INTKEY = 0
> [2] source hint applied to AccessNode:
> USER COMMAND:
> INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") VALUES ('-1', '-1')
> ...
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> AccessNode(0) output=[Count] INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") VALUES ('-1', '-1')



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)