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

Tue Feb 5 02:15:51 EST 2013

Vineela Gampa created TEIID-2377:
------------------------------------

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

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