openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Milosz Tylenda (JIRA)" <>
Subject [jira] [Updated] (OPENJPA-487) Generated SUBSTRING SQL is ugly and inefficient
Date Sun, 03 Jul 2011 12:51:21 GMT


Milosz Tylenda updated OPENJPA-487:

    Attachment: handle_params_in_group_by.patch

I noticed that parameters in GROUP BY are handled in a half-baked fashion -
parameter markers are correctly inserted into SQL but are not filled with
values. A patch is attached but seems unnecessary since most databases will
reject parameters in GROUP BY anyway. I found only MySQL accepting such a
query (TestJDBCGrouping.testSubstringInGroupBy), probably because MySQL
only emulates prepared statements by default:

SELECT SUBSTRING(t0.stringField, ?, ?), COUNT( FROM AllFieldTypes t0 GROUP BY SUBSTRING(t0.stringField,
?, ?)

Other databases require parameters be inlined into SQL:

SELECT SUBSTRING(t0.stringField, 1, 1), COUNT( FROM AllFieldTypes t0 GROUP BY SUBSTRING(t0.stringField,
1, 1)

That's why DBDictionary.substring still uses inlining where possible.

> Generated SUBSTRING SQL is ugly and inefficient
> -----------------------------------------------
>                 Key: OPENJPA-487
>                 URL:
>             Project: OpenJPA
>          Issue Type: Improvement
>          Components: sql
>    Affects Versions: 0.9.0, 0.9.6, 0.9.7, 1.0.0, 1.0.1, 1.0.2, 1.1.0
>            Reporter: Patrick Linskey
>            Assignee: Milosz Tylenda
>            Priority: Minor
>             Fix For: 2.2.0
>         Attachments: handle_params_in_group_by.patch
> When performing a JPQL query with a SUBSTRING clause, OpenJPA converts from one-based
index + length parameters to zero-based index + end index for internal handling, and then
re-converts back to one-based + length when generating SQL. This conversion is entirely performed
in query values, so the generated SQL has a lot of math in it. This extra math is never necessary.

This message is automatically generated by JIRA.
For more information on JIRA, see:


View raw message