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

[Lucie Fabrikova (JIRA)]

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

Lucie Fabrikova commented on TEIID-4469:
----------------------------------------

I simulated the query with two oracle databases on different machines, but the source hint doesn't seem to be applied ProjectIntoNode, contents of teiid-command.log and server.log:

21:03:54,633 DEBUG [org.teiid.COMMAND_LOG] (New I/O worker #4) wMQbVS84H1pO     START USER COMMAND:     startTime=2016-10-08 21:03:54.633       requestID=wMQbVS84H1pO.0        txID=null       sessionID=wMQbVS84H1pO  applicationName=JDBC    principal=teiidUser at teiid-security      vdbName=oracledba       vdbVersion=1    sql=INSERT /*+ sh Source2:'append' */ INTO test (ID, myvalue) SELECT X.id AS ID, X.myvalue AS myvalue FROM (SELECT SMALLA.INTKEY AS id, SMALLA.INTNUM AS myvalue FROM SMALLA WHERE SMALLA.INTKEY = 1) AS X
21:03:58,070 DEBUG [org.teiid.COMMAND_LOG] (Worker1_QueryProcessorQueue1) wMQbVS84H1pO  START DATA SRC COMMAND: startTime=2016-10-08 21:03:58.069       requestID=wMQbVS84H1pO.0        sourceCommandID=1       executionID=0   txID=null       modelName=Source        translatorName=oracle   sessionID=wMQbVS84H1pO  principal=teiidUser at teiid-security      sql=SELECT /*+sh Source2:'append' */ g_0.intkey, g_0.intnum FROM Source.smalla AS g_0 WHERE g_0.intkey = 1
21:03:58,567 DEBUG [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue2) wMQbVS84H1pO  END SRC COMMAND:        endTime=2016-10-08 21:03:58.567 requestID=wMQbVS84H1pO.0        sourceCommandID=1       executionID=0   txID=null       modelName=Source        translatorName=oracle   sessionID=wMQbVS84H1pO  principal=teiidUser at teiid-security      finalRowCount=1
21:04:01,127 DEBUG [org.teiid.COMMAND_LOG] (Worker1_QueryProcessorQueue3) wMQbVS84H1pO  START DATA SRC COMMAND: startTime=2016-10-08 21:04:01.127       requestID=wMQbVS84H1pO.0        sourceCommandID=0       executionID=1   txID=null       modelName=Source2       translatorName=oracle   sessionID=wMQbVS84H1pO  principal=teiidUser at teiid-security      sql=INSERT INTO Source2.test (ID, myvalue) VALUES (...)
21:04:01,635 DEBUG [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue4) wMQbVS84H1pO  END SRC COMMAND:        endTime=2016-10-08 21:04:01.635 requestID=wMQbVS84H1pO.0        sourceCommandID=0       executionID=1   txID=null       modelName=Source2       translatorName=oracle   sessionID=wMQbVS84H1pO  principal=teiidUser at teiid-security      finalRowCount=1
21:04:01,697 DEBUG [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue5) wMQbVS84H1pO  END USER COMMAND:       endTime=2016-10-08 21:04:01.697 requestID=wMQbVS84H1pO.0        txID=null       sessionID=wMQbVS84H1pO  principal=teiidUser at teiid-security      vdbName=oracledba       vdbVersion=1    finalRowCount=1

Logging set to DEBUG, server.log:
DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue3) wMQbVS84H1pO.0.0.1 Processing NEW request: INSERT INTO Source2.test (ID, myvalue) VALUES (...)
21:03:58,572 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2) Request Thread wMQbVS84H1pO.0 - processor blocked
21:04:01,126 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue3) wMQbVS84H1pO.0.0.1 Obtained execution
21:04:01,127 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue3) Source-specific command: INSERT INTO test (ID, myvalue) VALUES (?, ?)
21:04:01,633 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue3) wMQbVS84H1pO.0.0.1 Executed command

> 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)