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

Bug 364625

Summary:	Extremely inefficient SQL code generation for exist joins
Product:	z_Archived	Reporter:	APB <brusseau>
Component:	Eclipselink	Assignee:	Nobody - feel free to take it <nobody>
Status:	CLOSED INVALID	QA Contact:
Severity:	major
Priority:	P3
Version:	unspecified
Target Milestone:	---
Hardware:	PC
OS:	Linux
Whiteboard:

Description APB

2011-11-23 12:01:51 EST

Build Identifier: 2.0.3.v201010191057


Generates the following extremely inefficient SQL code:

SELECT DISTINCT t0.TERM_ID AS a1, t0.ASSERTED_ARGUMENT AS a2, t0.ASSERTED_TIMESTAMP AS a3, t0.DIRECTION AS a4, t0.STRENGTH AS a5, t0.TRUTH AS a6, t0.ASSERTED_BY AS a7, t0.ASSERTED_REASON AS a8, t0.FORMULA_ID AS a9, t0.MICROTHEORY_ID AS a10 
FROM KB.GAF_ASSERTION_TERM t0, KB.FORMULA_TERM t1 
WHERE ((((t0.FORMULA_ID = t1.TERM_ID) AND (t1.ARG0 = 1407374883554030)) AND (t1.FORMULA_TYPE = 1)) AND 
EXISTS (SELECT 1 
FROM KB.ITEM t4, KB.FORMULA_TERM t3, KB.FORMULA_ENTRIES t2 
WHERE ((((t3.ARG2 = t4.ITEM_ID) AND (t2.ARG_0_TERM = 1407374883556273)) AND (t1.TERM_ID = t3.TERM_ID)) AND (t4.ITEM_ID = t2.ITEM_ID))) ) 
OFFSET 0 ROWS FETCH NEXT 2048 ROWS ONLY

When I believe it should be generating the following, vastly most efficient code:

SELECT DISTINCT t0.TERM_ID AS a1, t0.ASSERTED_ARGUMENT AS a2, t0.ASSERTED_TIMESTAMP AS a3, t0.DIRECTION AS a4, t0.STRENGTH AS a5, t0.TRUTH AS a6, t0.ASSERTED_BY AS a7, t0.ASSERTED_REASON AS a8, t0.FORMULA_ID AS a9, t0.MICROTHEORY_ID AS a10 
FROM KB.GAF_ASSERTION_TERM t0, KB.FORMULA_TERM t1 
WHERE ((((t0.FORMULA_ID = t1.TERM_ID) AND (t1.ARG0 = 1407374883554030)) AND (t1.FORMULA_TYPE = 1)) AND 
EXISTS (SELECT 1 
FROM KB.FORMULA_ENTRIES t2 
WHERE ((t1.ARG2 = t2.ITEM_ID) AND (t2.ARG_0_TERM = 1407374883556273))))
OFFSET 0 ROWS FETCH NEXT 2048 ROWS ONLY

Note that the inefficient version is add redundant joins to KB.ITEM t4 and KB.FORMULA_ENTRIES t2.


Reproducible: Always

Steps to Reproduce:
1.See details
2.
3.

Comment 1 APB

2011-11-23 12:02:39 EST

Woops, this is the named query that is generating the SQL, I forgot in the original post.

The following named JP QL query:

  @NamedQuery(name = "GAFAssertionTerm.TOUWithFunction", query =
  "SELECT DISTINCT a FROM GAFAssertionTerm a, FormulaTerm ft "
  + "WHERE (a.formula = ft) AND (ft.arg0 = :arg0) AND (ft.formulaType = :gaf) AND "
  + "EXISTS (SELECT DISTINCT ft2 FROM FormulaTerm ft2 WHERE (ft.arg2.itemId = ft2.termId) AND "
  + "(ft2.arg0 = :function))"),

Comment 2 APB

2011-11-23 12:04:37 EST

Actually this is the named query in question, the previous one was a mistake. I sure wish bugzilla let one edit messages 8-(

  @NamedQuery(name = "GAFAssertionTerm.TOUWithFunction", query =
  "SELECT DISTINCT a FROM GAFAssertionTerm a, FormulaTerm ft "
  + "WHERE (a.formula = ft) AND (ft.arg0 = :arg0) AND (ft.formulaType = :gaf) AND "
  + "EXISTS (SELECT fe FROM FormulaEntries fe WHERE (ft.arg2 = fe.formulaItemId) AND "
  + "(fe.arg0Term = :function))"),

Comment 3 APB

2011-11-23 12:06:31 EST

Im closing this and reopening with better comments.

Comment 4 Eclipse Webmaster

2022-06-09 10:14:56 EDT

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

Comment 5 Eclipse Webmaster

2022-06-09 10:31:53 EDT

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