Prepared statement with OFFSET/FETCH gives different results on subsequent
execute
----------------------------------------------------------------------------------
Key: DERBY-4212
URL: https://issues.apache.org/jira/browse/DERBY-4212
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.5.1.1
Reporter: Steve Radman
A prepared statement with OFFSET/FETCH does not use the OFFSET/FETCH values
after the first EXECUTE (e.g. 2nd invocation of EXECUTE).
NOTE: Only tested in ij with toursdb database
ij> prepare foo as 'select * from cities where city_id <= 10 fetch first 5 rows
only';
ij> execute foo;
CITY_ID |CITY_NAME |COUNTRY |AIR&|LANGUAGE
|COU&
------------------------------------------------------------------------------------------
1 |Amsterdam |Netherlands |AMS |Dutch
|NL
2 |Athens |Greece |ATH |Greek
|GR
3 |Auckland |New Zealand |AKL |English
|NZ
4 |Beirut |Lebanon |BEY |Arabic
|LB
5 |Bogota |Colombia |BOG |Spanish
|CO
5 rows selected
ij> execute foo;
CITY_ID |CITY_NAME |COUNTRY |AIR&|LANGUAGE
|COU&
------------------------------------------------------------------------------------------
0 rows selected
ij> remove foo;
ij> prepare foo as 'select * from cities where city_id <= 10 offset 5 rows';
ij> execute foo;
CITY_ID |CITY_NAME |COUNTRY |AIR&|LANGUAGE
|COU&
------------------------------------------------------------------------------------------
6 |Bombay |India |BOM |Hindi
|IN
7 |Budapest |Hungary |BUD |Hungarian
|HU
8 |Buenos Aires |Argentina |BUE |Spanish
|AR
9 |Cairo |Egypt |CAI |Arabic
|EG
10 |Calcutta |India |CCU |Bengali
|IN
5 rows selected
ij> execute foo;
CITY_ID |CITY_NAME |COUNTRY |AIR&|LANGUAGE
|COU&
------------------------------------------------------------------------------------------
1 |Amsterdam |Netherlands |AMS |Dutch
|NL
2 |Athens |Greece |ATH |Greek
|GR
3 |Auckland |New Zealand |AKL |English
|NZ
4 |Beirut |Lebanon |BEY |Arabic
|LB
5 |Bogota |Colombia |BOG |Spanish
|CO
6 |Bombay |India |BOM |Hindi
|IN
7 |Budapest |Hungary |BUD |Hungarian
|HU
8 |Buenos Aires |Argentina |BUE |Spanish
|AR
9 |Cairo |Egypt |CAI |Arabic
|EG
10 |Calcutta |India |CCU |Bengali
|IN
10 rows selected
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
