Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 304923

Summary:

JPQL simple child query generates unnecessary table join

Product:

z_Archived

Reporter:

Bernard Missing name <bht237>

Component:

Eclipselink

Assignee:

Nobody - feel free to take it <nobody>

Status:

RESOLVED FIXED

QA Contact:

Severity:

major

Priority:

CC:

jamesssss, tom.ware

Version:

unspecified

Target Milestone:

---

Hardware:

OS:

Windows Vista

Whiteboard:

Attachments:

Description	Flags
NetBeans project in zip file	none
patch to optimize 1-1 joins	none

Description Bernard Missing name

2010-03-06 15:42:08 EST

Build Identifier: Eclipse Persistence Services - 2.0.0.v20091127-r5931

SELECT t1.ID, t1.PARENT_ID FROM PARENT t0, CHILD t1 WHERE ((t0.ID = ?) AND (t0.ID = t1.PARENT_ID))

The attached testcase demonstrates that for a basic child query, EclipseLink generates SQL that contains two joined tables where only one is required.
This creates performance issues. Please note that this is a simplified testcase - performance issues are of course not present in it.

It can be expected that JPA that provides a layer of abstraction by nature, generates optimized SQL.

Instead it is producing spaghetti code which is an indication of immaturity.

In light of the fact that this is a 2.0 release, the priority of solving this should be high.

Reproducible: Always

Steps to Reproduce:
Please refer to the attached testcase

Comment 1 Bernard Missing name

2010-03-06 15:44:37 EST

Created attachment 161230 [details]
NetBeans project in zip file

Comment 2 Tom Ware

2010-03-18 10:53:47 EDT

Setting target and priority.  For information about the meaning of these settings see:

http://wiki.eclipse.org/EclipseLink/Development/Bugs/Guidelines

Comment 3 James Sutherland

2010-08-09 14:23:42 EDT

Note that the JPQL,

"Select c from Child c where c.parent.id = :id"

Is explictly doing a join from child to parent, so not very surprizing that the same join occurs in SQL.

To correctly do this in JPQL you can use,

"Select c from Child c where c.parent = :parent"

and pass a parent object with its id set as parameter.

EclipseLink also allows you to define QueryKeys that can be define for the foreign key column to allow querying like,

"Select c from Child c where c.parentId = :id"

...

But we could optimize this JPQL as it is a common mistake.

Comment 4 James Sutherland

2010-08-09 14:24:32 EDT

Created attachment 176175 [details]
patch to optimize 1-1 joins

Comment 5 James Sutherland

2010-08-09 14:25:37 EDT

This has been fixed in trunk (2.2).

Please verify your use case now works on the latest EclipseLink build.
(as of tomorrow)

Comment 6 James Sutherland

2010-08-09 14:30:30 EDT

SVN trunk commit: Bug#304923 optimize 1-1 joins

https://bugs.eclipse.org/bugs/show_bug.cgi?id=304923

Code review: Andrei

Changes:
- Optimizes expression normalization to optimize unnecessary 1-1 joins (emp.addresss.id = :id => emp.ADDR_ID = :id).
- Switches getOperations to public in PerformanceMonitor.
- Fixes cache size monitor to exclude unit of work cache.
- Test for 1-1 join optimization.

Comment 7 Bernard Missing name

2010-08-13 17:12:34 EDT

Thanks very much for fixing this!

There are still two open issues of the same type, 298494 and 300625. It might be easier to address these sooner than later (knowledge, setup costs).

Comment 8 Eclipse Webmaster

2022-06-09 10:36:13 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink