[teiid-issues] [JBoss JIRA] (TEIID-2377) Query results are lost

Tue Feb 5 08:45:51 EST 2013

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

Steven Hawkins commented on TEIID-2377:
---------------------------------------

This seems more like a forum question rather than a issue.  Also the stackTrace attachment shows no stackTraces.  The approach when there is any doubt about an issue would be to narrow things down first:
1. ideally reproduce on 8.2 or preferably using 8.3 Beta1/latest
2. try reproducing with a smaller form of the query (removing views or higher level limit/order bys)
3. Examine the query plan to understand the execution flow
3.a. When you have two similar scenarios compare the two logs at a detail/trace level to see all of the relevant source counts and other information
 - note that from your snippet, which I presume is from the second query, shows 18 rows returned from the source:
END SRC COMMAND: endTime=2013-02-04 23:01:45.113 requestID=5L+SB5Q54KWY.44 sourceCommandID=4 txID=null modelName=datapipe translatorName=datapipe.delegated-translator sessionID=5L+SB5Q54KWY principal=admin at chorus-login-security finalRowCount=18
etc.

In this particular case it simply appears that you are duplicating the limit/offset at multiple levels of your query.  You are first pushing down limit 18, 18 and thus fetching 18 rows, then on the top level query applying limit 18, 18 again - which due to the 18 offset will not return any results.

> Query results are lost 
> -----------------------
>
>                 Key: TEIID-2377
>                 URL: https://issues.jboss.org/browse/TEIID-2377
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 8.1
>            Reporter: Vineela Gampa
>            Assignee: Steven Hawkins
>         Attachments: stackTrace.txt
>
>
> Hi Steve,
> We are seeing some problem incase of one usecase.
> 1)User issues a query - and server issues a query
> SELECT chorus_results.CHORUS_H_healthstate, chorus_results.userid, chorus_results.sysid, chorus_results.CHORUS_C_notesForObject, chorus_results.name, chorus_results.age, 'demodata.user' AS "__objecttype__" FROM (EXEC data_window('SELECT CHORUS_H_healthstate, userid, sysid, CHORUS_C_notesForObject, name, age, ''demodata.user'' AS "__objecttype__" FROM (SELECT /*+sh:''data_window'' */ CHORUS_B.*, (SELECT COALESCE(MIN(healthstatefkey), 10) FROM healthstatestream.latest_health_states WHERE latest_health_states.objfkeyxml = replace(primary_key(''demodata.user'', ''userid'', cast(CHORUS_B.userid AS String), ''sysid'', cast(CHORUS_B.sysid AS String)), ''"'', '''''''')) AS CHORUS_H_healthstate, (SELECT COUNT(*) FROM (SELECT * FROM chorus_notes.RETRIEVE_NOTES WHERE OBJECT_PKEY = primary_key(''demodata.user'', ''userid'', CHORUS_B.userid, ''sysid'', CHORUS_B.sysid)) AS foo) AS CHORUS_C_notesForObject, ''demodata.user'' AS "__objecttype__" FROM (SELECT CHORUS_B.* FROM (SELECT * FROM demodata."user") AS CHORUS_B, (SELECT * FROM chorus_time.timetable WHERE period = 30000) AS chorus_refresh_expression) AS CHORUS_B LIMIT 100) AS CHORUS_B LIMIT 18', ?)) AS chorus_data_window, ARRAYTABLE(chorus_data_window.tuple COLUMNS sysid string, userid string, name string, phone string, zip string, age biginteger, salary bigdecimal, CHORUS_H_healthstate integer, CHORUS_C_notesForObject integer, "__objecttype__" string) AS chorus_results LIMIT 18
> 2)User scrolls down the page to fetch more results
> SELECT chorus_results.CHORUS_H_healthstate, chorus_results.userid, chorus_results.sysid, chorus_results.CHORUS_C_notesForObject, chorus_results.name, chorus_results.age, 'demodata.user' AS "__objecttype__" FROM (EXEC data_window('SELECT CHORUS_H_healthstate, userid, sysid, CHORUS_C_notesForObject, name, age, ''demodata.user'' AS "__objecttype__" FROM (SELECT /*+sh:''data_window'' */ CHORUS_B.*, (SELECT COALESCE(MIN(healthstatefkey), 10) FROM healthstatestream.latest_health_states WHERE latest_health_states.objfkeyxml = replace(primary_key(''demodata.user'', ''userid'', cast(CHORUS_B.userid AS String), ''sysid'', cast(CHORUS_B.sysid AS String)), ''"'', '''''''')) AS CHORUS_H_healthstate, (SELECT COUNT(*) FROM (SELECT * FROM chorus_notes.RETRIEVE_NOTES WHERE OBJECT_PKEY = primary_key(''demodata.user'', ''userid'', CHORUS_B.userid, ''sysid'', CHORUS_B.sysid)) AS foo) AS CHORUS_C_notesForObject, ''demodata.user'' AS "__objecttype__" FROM (SELECT CHORUS_B.* FROM (SELECT * FROM demodata."user") AS CHORUS_B, (SELECT * FROM chorus_time.timetable WHERE period = 30000) AS chorus_refresh_expression) AS CHORUS_B LIMIT 100) AS CHORUS_B LIMIT 18, 18', ?)) AS chorus_data_window, ARRAYTABLE(chorus_data_window.tuple COLUMNS sysid string, userid string, name string, phone string, zip string, age biginteger, salary bigdecimal, CHORUS_H_healthstate integer, CHORUS_C_notesForObject integer, "__objecttype__" string) AS chorus_results LIMIT 18, 18
> Above queries are issues using preparedstatement.exuecute . We are calling a translator storedproc which inturn executes a asynchronous (submitExecute) query to fetch the results and store 100 records in temptable.Incase of 1 we get the results normally . Incase of 2 as the data is already stored in temptables we execute results against temptable. From the logs its evident that teiid processed the data. But the resultset output from ps.ExecuteQuery has resultset of size 0. 
> Any idea of what could be hapennign would greatly help. Will attach the entire log for the second query.
> snippet of logs
> 04 Feb 2013 23:01:45,092 PST DEBUG [org.teiid.BUFFER_MGR] (http--127.0.0.1-8080-2) Creating TupleBuffer: 1483 [chorus_results.CHORUS_H_healthstate, chorus_results.userid, chorus_results.sysid, chorus_results.CHORUS_C_notesForObject, chorus_results.name, chorus_results.age] [class java.lang.Integer, class java.lang.String, class java.lang.String, class java.lang.Integer, class java.lang.String, class java.math.BigInteger] of type PROCESSOR
> 04 Feb 2013 23:01:45,094 PST DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue2395) 5L+SB5Q54KWY.44.4.1084 Processing Close : EXEC data_window('SELECT CHORUS_H_healthstate, userid, sysid, CHORUS_C_notesForObject, name, age, ''demodata.user'' AS "__objecttype__" FROM (SELECT /*+sh:''data_window'' */ CHORUS_B.*, (SELECT COALESCE(MIN(healthstatefkey), 10) FROM healthstatestream.latest_health_states WHERE latest_health_states.objfkeyxml = replace(primary_key(''demodata.user'', ''userid'', cast(CHORUS_B.userid AS String), ''sysid'', cast(CHORUS_B.sysid AS String)), ''"'', '''''''')) AS CHORUS_H_healthstate, (SELECT COUNT(*) FROM (SELECT * FROM chorus_notes.RETRIEVE_NOTES WHERE OBJECT_PKEY = primary_key(''demodata.user'', ''userid'', CHORUS_B.userid, ''sysid'', CHORUS_B.sysid)) AS foo) AS CHORUS_C_notesForObject, ''demodata.user'' AS "__objecttype__" FROM (SELECT CHORUS_B.* FROM (SELECT * FROM demodata."user") AS CHORUS_B, (SELECT * FROM chorus_time.timetable WHERE period = 30000) AS chorus_refresh_expression) AS CHORUS_B LIMIT 100) AS CHORUS_B LIMIT 18, 18', '[]')
> 04 Feb 2013 23:01:45,102 PST DEBUG [org.teiid.BUFFER_MGR] (http--127.0.0.1-8080-2) Removing TupleBuffer: 1483
> 04 Feb 2013 23:01:45,113 PST DEBUG [org.teiid.COMMAND_LOG] (Worker1_QueryProcessorQueue2395) 	END SRC COMMAND:	endTime=2013-02-04 23:01:45.113	requestID=5L+SB5Q54KWY.44	sourceCommandID=4	txID=null	modelName=datapipe	translatorName=datapipe.delegated-translator	sessionID=5L+SB5Q54KWY	principal=admin at chorus-login-security	finalRowCount=18
> 04 Feb 2013 23:01:45,114 PST DEBUG [org.teiid.BUFFER_MGR] (http--127.0.0.1-8080-2) Creating TupleBuffer: 1484 [chorus_results.CHORUS_H_healthstate, chorus_results.userid, chorus_results.sysid, chorus_results.CHORUS_C_notesForObject, chorus_results.name, chorus_results.age] [class java.lang.Integer, class java.lang.String, class java.lang.String, class java.lang.Integer, class java.lang.String, class java.math.BigInteger] of type PROCESSOR

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