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