Pagination does not work with Oracle views

Description

Hibernate pagination generated script execution return incorrect data.

I found this when I have retrieved data from a view. I have created table, data, view

for you to reproduce this bug.

According to hibernate sources getLimitString method from org.hibernate.dialect.Oracle9iDialect

Lets first select all rows from view:

Result:

RowNumber

ID

VERSION

CAPTION

STATUS

1

1

7

t40

2

2

16

1

t47

2

3

11

2

t43

2

4

6

4

t31

2

5

15

1

t46

2

6

2

6

t39

2

7

14

1

t45

2

8

4

5

t38

2

9

8

2

t29

2

10

17

1

t48

2

11

3

3

t21

2

12

7

2

t23

2

13

9

2

t30

2

14

10

3

t42

2

15

12

1

t41

2

16

5

6

t37

1

17

13

1

t44

1

After that is we are using hibernate pagination for first page then we have an appended code at first and last line of request:

Result:

RowNumber

ID

VERSION

CAPTION

STATUS

1

1

7

t40

2

2

14

1

t45

2

3

2

6

t39

2

4

6

4

t31

2

5

11

2

t43

2

And for second page we are having offset so hibernate execute:

Result:

RowNumber

ID

VERSION

CAPTION

STATUS

ROWNUM_

1

4

5

t38

2

6

2

14

1

t45

2

7

3

2

6

t39

2

8

4

6

4

t31

2

9

5

11

2

t43

2

10

You can see for the second page ROWNUM_ seem to be correct but the data is not.
I was wonder to find this bug. I found it reproducible only for my view, not for table.

To solve this issue I has to override dialect and use my own implementation:

This code is working correct for views as well as for tables.

Environment

Hibernate core 4.3.11 or 5.2.12
Oracle 11.2g

Assignee

Vlad Mihalcea

Reporter

Rustem Yagudin

Fix versions

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure