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