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 ]