Illegal parenthesis in generated Oracle SQL SELECT clause (when using subselect)

Description

Hi,

when using an HQL subselect with EXISTS where the selected object in the subselect is an entity with a composite primary key (mapping a join-table), Hibernate adds parenthesis to the query-list with the selected columns. Oracle does not accept SELECT statements with parenthesis around the query-list and fails.

Consider the following entities (non-relevant members omitted):

EvaluationRequest is a join table between Evaluation and ModulesRequest, hence the primary key is the composite of the primary keys of Evaluation and ModulesRequest.

If I now execute the following EJB-QL query

Hibernate generates the following SQL command from the above EJB-QL:

This SQL fails in the second SELECT because Oracle does not accept parantheses in a select statement:

The parantheses do not get included if the EJBQL selects directly on EvalauationRequest (and not as a part of a subselect in EXISTS).

Environment

Hibernate 3.6.0.Final (shipped as part of JBoss runtime environment 6), Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit

Activity

Show:
Brett Meyer
April 7, 2014, 5:48 PM

In an effort to clean up, in bulk, tickets that are most likely out of date, we're transitioning all ORM 3 tickets to an "Awaiting Test Case" state. Please see http://in.relation.to/Bloggers/HibernateORMJIRAPoliciesAndCleanUpTactics for more information.

If this is still a legitimate bug in ORM 4, please provide either a test case that reproduces it or enough detail (entities, mappings, snippets, etc.) to show that it still fails on 4. If nothing is received within 3 months or so, we'll be automatically closing them.

Thank you!

Brett Meyer
July 8, 2014, 3:11 PM

Bulk rejecting stale issues. If this is still a legitimate issue on ORM 4, feel free to comment and attach a test case. I'll address responses case-by-case. Thanks!

Sascha Szott
October 6, 2014, 1:49 PM

The problem still exists in Hibernate 4.3.5. Please reopen the ticket. Seems as this ticket is a duplicate to HHH-8583, HHH-4051, HHH-3096, HHH-2845, HHH-2409.

Tóth István
February 6, 2017, 9:10 AM

The problem still exists in Hibernate 5.0.10.

My environment is Wildfly 10.1 and mysql 5.7.15.

I will make a proper test case later this week.

Tóth István
February 7, 2017, 10:09 AM

I have created a test case for this bug with Mysql at

https://github.com/stoty/HHH-5998-TestCase

It uses Mysql, but the bug seems to be dialect independent, the same query is generated for H2 and Mysql, only H2 accepts it and Mysql does not.

Assignee

Unassigned

Reporter

Thomas Zangerl

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