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

Activity

Show:
Gail Badner
November 10, 2017, 8:39 PM

, we no longer support 4.3. Does this happen with 5.2.12? If so, then please attach a test case that reproduces your issue. You can find test templates at: https://github.com/hibernate/hibernate-test-case-templates

Rustem Yagudin
November 13, 2017, 8:46 AM

Hi Gail. As I see your hibernate test case templates using H2 database. But this bug is very specific and reproducible only on oracle database because there is a bug in specific dialect file. As I see there is no changes in dialect file for 5.2.12 there is the same code so I guess it reproducible for last version.

I'm not sure if I can to create a test case since it's necessary to use oracle database. Do you have any suggestions? Should I can create a test case supposing that tester have local oracle db instance?

Rustem Yagudin
November 13, 2017, 2:41 PM
Edited

Test case: https://github.com/gunboxer/hibernate-test-case-HHH-12087

I have implemented test case but to execute it you need to have Oracle db instance and provide there url and credentials. For more information you can read README of test case project.

Also I confirmed that this bug reproducible for latest Hibernate core 5.2.12.

I have created two persistence units for standard dialect implementation and for my fixed version.
Output for standard dialect:

--------------------
1 should be equal to 1
16 should be equal to 14
11 should be equal to 2
6 should be equal to 6
15 should be equal to 11
2 should be equal to 9
14 should be equal to 7
4 should be equal to 3
8 should be equal to 17
17 should be equal to 8
--------------------

Output for my fixed dialect:

--------------------
1 should be equal to 1
16 should be equal to 16
11 should be equal to 11
6 should be equal to 6
15 should be equal to 15
2 should be equal to 2
14 should be equal to 14
4 should be equal to 4
8 should be equal to 8
17 should be equal to 17
--------------------

Vlad Mihalcea
November 20, 2017, 3:26 PM

Applied PR upstream after integrating the test case as well.

Vlad Mihalcea
November 21, 2017, 10:33 AM

The current pagination follows the Ask Tom guidelines, and, if we apply the current patch, we break the follow-on-locking mechanism as well.

I modified the test case and showed that if you compare the pages against a larger page which selects all comparing pages, then everything works as expected:

Now, comparing it against getAllRows will fail for one row, but from the status column alone, both sets are ordered.

If you want to match the ordering for those two, you need to provide more filtering criteria and then the pages will align.

For the moment, I'm going to reject this change.

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