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)

Key: NUCRDBMS-83
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Testcase Required Testcase Required
Assignee: Unassigned
Reporter: Yang ZHONG
Votes: 0
Watchers: 0
Operations

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://WWW.JPOx.org/servlet/forum/viewthread_thread,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.

Thanks!

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 made changes - 04/Nov/08 08:42 AM
Field Original Value New Value
Priority Major [ 3 ] Incomplete [ 6 ]
Andy Jefferson made changes - 10/Dec/09 11:20 AM
Summary "union all" instead of "union" to work around DB2's limitation of querying CLOB/LongVarChar DB2 : "union all" instead of "union" to work around DB2's limitation of querying CLOB/LongVarChar
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

Andy Jefferson made changes - 19/Jan/13 09:18 AM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 3.2.0.m3 [ 11840 ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 19/Jan/13 09:19 AM
Status Resolved [ 5 ] Closed [ 6 ]