DataNucleus JIRA is now in read-only mode. Raise any new issues in GitHub against the plugin that it applies to. DataNucleus JIRA will remain for the foreseeable future but will eventually be discontinued
Issue Details (XML | Word | Printable)

Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Testcase Required Testcase Required
Assignee: Unassigned
Reporter: Yang ZHONG
Votes: 0
Watchers: 0

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

DB2 : "union all" instead of "union" to work around DB2's limitation of querying CLOB/LongVarChar

Created: 03/Nov/08 09:21 PM   Updated: 19/Jan/13 09:19 AM   Resolved: 19/Jan/13 09:18 AM
Component/s: Queries
Affects Version/s: 1.1.0.m2
Fix Version/s: 3.2.0.m3

Environment: JDK 5, Linux 2.6.9, DataNucleus 1.1 M2, DB2 9.1

Forum Thread URL: HTTP://,5221
Datastore: IBM DB2

 Description  « Hide
DB2 has a known limitation of using CLOB, LongVarChar & long VarChar in

". A SELECT DISTINCT statement
. A GROUP BY clause
. An ORDER BY clause
. A column function with DISTINCT
. A SELECT or VALUES statement of a set operator other than UNION ALL"

However, DataNucleus uses "union" to union JDO instances containing CLOB/LongVarChar from different tables which is complained by DB2:

"DB2 SQL error: SQLCODE: -134, SQLSTATE: 42907, SQLERRMC: "the_CLOB/LongVarChar_field

Proposing "union all" instead of "union" to work around the DB2's limitation of querying CLOB/LongVarChar.
For your reference, "union all" selects all values while "union" only selects distinct values, and DB2 has difficulties w/ "distinct" CLOB/LongVarChar.


Sort Order: Ascending order - Click to sort in descending order
Andy Jefferson added a comment - 04/Nov/08 08:42 AM
No testcase provided as per all docs, so downgrading to Incomplete. If you think UNIONALL would be a good idea you can easily add your own DatabaseAdapter that extends the provided one.

Andy Jefferson added a comment - 30/Apr/10 02:26 PM
Derby also has a limit like that but then with "JDOQL2" the adapter can be tuned to not select a field of that type when DISTINCT/UNION are used. But since no testcase is provide it's impossible to comment further.

Andy Jefferson added a comment - 19/Jan/13 09:18 AM
SVN trunk uses UNION ALL