Joined inheritance does not work reliably on Oracle12c

Description

When selecting data from tables that use InheritanceType.JOINED, Hibernate does not always choose the correct type for the returned entities.

The attached test case always returns the same type for all found entities, when three different types should be returned.

It only happens on Oracle12c, if a certain amount of child table definitions exist and a certain set of IDs are queried. Everything works well on Oracle11g.

While this might be an Oracle bug, there exist a SQL that would return the correct entity types:

Environment

Hibernate 5.2.2, Oracle12c

Activity

Show:
Olaf Ziems
November 8, 2016, 3:49 PM
Edited

Hi there,

any progress/new information concerning this issue?

According to new information on our side, the statement hibernate generates will NEVER return a deterministic result. This is due to how the statement is formulated and interpreted by the Oracle optimizer. It needs to be reformulated so that it will return always the same result independent of the data contained in the table.

Regards,

Olaf

Olaf Ziems
January 18, 2017, 10:48 AM

It turned out, that this issue is (at least partly) due to the following Oracle optimizer setting, that is non-standard: OPT_PARAM('optimizer_index_cost_adj' 20)
For details see this stackoverflow discussion: http://stackoverflow.com/questions/41701851/oracle12-outer-join-invents-column-values

It would be great, if someone from the Hibernate team can test the attached JUnit test on an Oracle12c with this special optimizer setting. If the test fails it clearly is an Oracle bug and this one can be closed.

Chris Cranford
January 19, 2017, 6:35 AM

, I am setting the parameter as follows:

Even with the session parameter set, I cannot get the test to fail.

I also pasting your RANK() OVER statement and verified the results from the query:

account0_.id

account0_1_.id

account0_2_.id

account0_3_.id

creditLimit1_1_

clazz_org

clazz

1

1

null

null

100

1

1

3

null

3

null

null

2

2

4

null

null

4

null

3

3

If you review this against your last query in your text attachment, you'll notice the last column is (1, 2, 3) instead of (1, 1, 1) as your file indicates. As far as I can tell, this returns the right expected results against 5.2.6.Final even with optimizer_index_cost_adj specified for the current session.

Olaf Ziems
January 19, 2017, 7:11 AM

Hi Chris,

there must be something else in our optimizer/Oracle settings that affects the results. I do not think, that hibernate could/should do anything about that.

You may close this bug without changes.

Thanks,

Olaf

Chris Cranford
January 19, 2017, 11:34 PM

Closing this issue at user request.

Assignee

Unassigned

Reporter

Olaf Ziems

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure