[teiid-issues] [JBoss JIRA] (TEIID-5093) Amazon s3 csv/xml lookup querry exception

[Jan Stastny (JIRA)]

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

Jan Stastny commented on TEIID-5093:
------------------------------------

[~rareddy],[~shawkins],[~mmajerni]
The issue here is not with lookup function. If you notice the query in the description, it is a union of two queries on a single table.

Simplified reproducers:
{code:sql}
SELECT IntKey, StringKey FROM BQT1.SmallA UNION SELECT IntKey, StringKey FROM BQT1.SmallA
{code}
{code:sql}
SELECT a.IntKey, b.StringKey FROM BQT1.SmallA a , BQT1.SmallA b
{code}

Also note, that the issue happens for csv, where XMLPARSE is not being used at all.

To complete the report, if I change the query in description, (so that it doesn't do union on a single table, but on two different) then it works well.
{code:sql}
SELECT IntKey, StringKey, lookup('BQT1.SmallB', 'DoubleNum', 'IntKey', IntKey) AS DoubleNum  FROM BQT1.SmallA UNION SELECT IntKey, lookup('BQT1.SmallA', 'StringKey', 'IntKey', IntKey) AS StringKey, DoubleNum FROM BQT1.SmallB
{code}

> Amazon s3 csv/xml lookup querry exception
> -----------------------------------------
>
>                 Key: TEIID-5093
>                 URL: https://issues.jboss.org/browse/TEIID-5093
>             Project: Teiid
>          Issue Type: Bug
>          Components: Misc. Connectors
>    Affects Versions: 8.12.x-6.4
>            Reporter: Mario Majernik
>            Assignee: Steven Hawkins
>
> Querry : 
> {code:java}
> SELECT IntKey, StringKey, lookup('BQT1.SmallB', 'DoubleNum', 'IntKey', IntKey) AS DoubleNum  FROM BQT1.SmallA UNION SELECT IntKey, lookup('BQT1.SmallA', 'StringKey', 'IntKey', IntKey) AS StringKey, DoubleNum FROM BQT1.SmallA
> {code}
> returns TeiidSQLException for csv and xml files from amazon s3.
> Stacktrace:
> {code:java}
>  WARN  [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue41) TEIID30020 Processing exception for request 8O9K6VJIjd9y.7 'TEIID30180 java.sql.SQLException: java.sql.SQLException: TEIID60019 Streaming result has already been read once.  Ensure that only one read operation needs to be performed, for example XMLPARSE without the WELLFORMED operation must read the entire stream to validate its contents.  Or you may choose to use a non-streaming result.   '. Originally TeiidProcessingException BinaryWSProcedureExecution.java:78. Enable more detailed logging to see the entire stacktrace.
> {code}
> Teiid command log for querry:
> {code:java}
> 15:11:34,160 INFO  [org.teiid.COMMAND_LOG] (New I/O worker #2) 8O9K6VJIjd9y 	START USER COMMAND:	startTime=2017-10-09 15:11:34.16	requestID=8O9K6VJIjd9y.7	txID=null	sessionID=8O9K6VJIjd9y	applicationName=JDBC	principal=user at teiid-security	vdbName=csv	vdbVersion=1	sql=SELECT IntKey, StringKey, lookup('BQT1.SmallB', 'DoubleNum', 'IntKey', IntKey) AS DoubleNum  FROM BQT1.SmallA UNION SELECT IntKey, lookup('BQT1.SmallA', 'StringKey', 'IntKey', IntKey) AS StringKey, DoubleNum FROM BQT1.SmallA
> 15:11:34,192 DEBUG [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue40) 8O9K6VJIjd9y 	START DATA SRC COMMAND:	startTime=2017-10-09 15:11:34.192	requestID=8O9K6VJIjd9y.7	sourceCommandID=6	executionID=2	txID=null	modelName=sourceModel	translatorName=user-s3	sessionID=8O9K6VJIjd9y	principal=user at teiid-security	sql=EXEC sourceModel.getTextFile('csv/smallaCsv.csv')
> 15:11:34,856 DEBUG [org.teiid.COMMAND_LOG] (Worker2_QueryProcessorQueue41) 8O9K6VJIjd9y 	END SRC COMMAND:	endTime=2017-10-09 15:11:34.856	requestID=8O9K6VJIjd9y.7	sourceCommandID=6	executionID=2	txID=null	modelName=sourceModel	translatorName=user-s3	sessionID=8O9K6VJIjd9y	principal=user at teiid-security	finalRowCount=1	cpuTime(ns)=34673443
> 15:11:34,862 INFO  [org.teiid.COMMAND_LOG] (Worker2_QueryProcessorQueue41) 8O9K6VJIjd9y 	ERROR USER COMMAND:	endTime=2017-10-09 15:11:34.862	requestID=8O9K6VJIjd9y.7	txID=null	sessionID=8O9K6VJIjd9y	principal=user at teiid-security	vdbName=csv	vdbVersion=1	finalRowCount=null
> 15:11:34,876 INFO  [org.teiid.COMMAND_LOG] (Worker2_QueryProcessorQueue41) 8O9K6VJIjd9y 	END USER COMMAND:	endTime=2017-10-09 15:11:34.876	requestID=8O9K6VJIjd9y.7	txID=null	sessionID=8O9K6VJIjd9y	principal=user at teiid-security	vdbName=csv	vdbVersion=1	finalRowCount=0
> {code}



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)