Issue Details (XML | Word | Printable)

Key: NUCRDBMS-373
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Andy Jefferson
Reporter: Marco Schulze
Votes: 0
Watchers: 0
Operations

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

ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT

Created: 23/Apr/10 11:05 PM   Updated: 26/Apr/10 03:38 PM   Resolved: 24/Apr/10 04:34 PM
Component/s: Queries
Affects Version/s: 2.1.0.m1
Fix Version/s: 2.1.0.m2

Datastore: Apache Derby


 Description  « Hide
Hello Andy,

DataNucleus works pretty well with JFire and MySQL. I've not yet tested everything with MySQL, but before continuing with MySQL, I first have to make sure the essential basics are working with Derby, too.

It works quite well with Derby, but I've still one blocking exception - see below.

Best regards, Marco :-)


*** Persistence-capable class ***
public class User
implements Serializable, Comparable<User>, AttachCallback, DetachCallback, StoreCallback
{
// some constants

@PrimaryKey
@Column(length=100)
private String organisationID;

@PrimaryKey
@Column(length=100)
private String userID;

@Persistent(persistenceModifier=PersistenceModifier.PERSISTENT)
@Column(length=100)
private String userType;

@Persistent(persistenceModifier=PersistenceModifier.PERSISTENT)
@Column(length=255)
private String name;

@Persistent(persistenceModifier=PersistenceModifier.PERSISTENT)
@Column(sqlType="clob")
private String description;

@Persistent(persistenceModifier=PersistenceModifier.PERSISTENT)
private Date changeDT;

@Persistent(persistenceModifier=PersistenceModifier.PERSISTENT)
private boolean autogenerateName = true;

// some methods
}

*** JDOQL ***
(candidate class is User above)

SELECT WHERE this.userType == paramUserType && this.userID != paramSystemUserID PARAMETERS String paramUserType, String paramSystemUserID import java.lang.String

*** Exception ***
javax.jdo.JDOException: Exception thrown when executing query
at org.datanucleus.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:440)
at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:275)
at org.datanucleus.test.User.getUsersByType(User.java:192)
at org.datanucleus.test.Main$QueryDataTransRunnable0.run(Main.java:88)
at org.datanucleus.test.Main.executeInTransaction(Main.java:45)
at org.datanucleus.test.Main.main(Main.java:105)
NestedThrowablesStackTrace:
java.sql.SQLException: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.datanucleus.store.rdbms.SQLController.getStatementForQuery(SQLController.java:311)
at org.datanucleus.store.rdbms.query.RDBMSQueryUtils.getPreparedStatementForQuery(RDBMSQueryUtils.java:191)
at org.datanucleus.store.rdbms.query.JDOQLQuery.performExecute(JDOQLQuery.java:530)
at org.datanucleus.store.query.Query.executeQuery(Query.java:1672)
at org.datanucleus.store.query.Query.executeWithArray(Query.java:1514)
at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:266)
at org.datanucleus.test.User.getUsersByType(User.java:192)
at org.datanucleus.test.Main$QueryDataTransRunnable0.run(Main.java:88)
at org.datanucleus.test.Main.executeInTransaction(Main.java:45)
at org.datanucleus.test.Main.main(Main.java:105)
Caused by: java.sql.SQLException: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 23 more
Caused by: ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.ResultColumn.verifyOrderable(Unknown Source)
at org.apache.derby.impl.sql.compile.ResultColumnList.verifyAllOrderable(Unknown Source)
at org.apache.derby.impl.sql.compile.SelectNode.genProjectRestrict(Unknown Source)
at org.apache.derby.impl.sql.compile.SelectNode.modifyAccessPaths(Unknown Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown Source)
at org.apache.derby.impl.sql.compile.CursorNode.optimizeStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
... 17 more

*** Test ***
http://www.nightlabs.de/~marco/datanucleus/2010-04-23.00/

Sort Order: Ascending order - Click to sort in descending order
Andy Jefferson added a comment - 24/Apr/10 07:38 AM
Obviously a bug cannot be in something that hasn't been released yet, since it doesn't exist. The bug report seems to have omitted the actual SQL being invoked that Derby is complaining about

SELECT DISTINCT 'org.datanucleus.test.User' AS NUCLEUS_TYPE,A0.AUTOGENERATENAME,A0.CHANGEDT,A0.DESCRIPTION,A0."NAME",A0.ORGANISATIONID,A0.USERID,A0.USERTYPE
FROM JFIREBASE_USER A0
WHERE A0.USERTYPE = ?
AND A0.USERID <> ?

So Derby has thrown its toys out of the pram due to the use of DISTINCT on the query when there is a CLOB column (in the SELECT in this case).

Andy Jefferson added a comment - 24/Apr/10 04:34 PM
SVN trunk will not add SELECT for any candidate field when using Derby when that field is a CLOB, and when either UNION or DISTINCT are being used. A mechanism is added so that other RDBMS-specifics can be excluded in the future via the RDBMSAdapter