[OPENJPA-608] Select Union queries generated to fetch data from all subclasses fails if there is a CLOB field defined in a descending class on Oracle and Sybase

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.1.0
• Component/s: jdbc

Environment

$ java org.apache.openjpa.conf.OpenJPAVersion
OpenJPA 1.1.0
version id: openjpa-1.1.0-r422266:656510
Apache svn revision: 422266:656510

os.name: Windows XP
os.version: 5.1
os.arch: x86

java.version: 1.6.0_05
java.vendor: BEA Systems, Inc.

• Assignee: Unassigned
• Reporter: Sandeep Shrivastava
• Created: 22/May/08 22:23
• Updated: 26/Jul/09 07:14

Description

Consiider the following entity classes with the given fields.

public class PlaceholderTypesA {
private int intA;
}

public class PlaceholderTypesB extends PlaceholderTypesA {

private boolean booleanB;
private byte byteB;
private char charB;
private float floatB;
private int intB;
private String stringB;
private String clobB;
private Object blobB;
}

The following code:
org.apache.openjpa.kernel.Extent extent = broker.newExtent(PlaceholderTypesA.class, true);
List list = extent.list();
generates a SELECT UNION query like:

SELECT 0, t0.ID, t0.TYP, t0.VERSN, t0.INTA, NULL, 0, 0, 0, '', 0.0, 0, '' FROM PLACEHOLDERTYPESA t0
UNION ALL
SELECT 1, t0.ID, '', t0.VERSN, t0.INTA, t0.BLOBB, t0.BOOLEANB, t0.BYTEB, t0.CHARB, t0.CLOBB, t0.FLOATB, t0.INTB, t0.STRINGB
FROM PLACEHOLDERTYPESB t0

Notice that the placeholder value for the CLOBB field is an empty string ''

This fails on both Oracle and Sybase as the jdbc drivers for these databases do not do any implicit conversion but instead throw an exception.

testlogic] java.sql.SQLSyntaxErrorException: ORA-01790: expression must have
same datatype as corresponding expression

[testlogic] at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
[testlogic] at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
[testlogic] at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
[testlogic] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
[testlogic] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
[testlogic] at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
[testlogic] at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
[testlogic] at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:785)
[testlogic] at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:860)
[testlogic] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
[testlogic] at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
[testlogic] at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3425)
[testlogic] at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1490)
[testlogic] at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:264)
[testlogic] at org.apache.openjpa.lib.jdbc.JDBCEventConnectionDecorator$EventPreparedStatement.executeQuery(JDBCEventConnectionDecorator.java:237)
[testlogic] at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:262)
[testlogic] at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeQuery(JDBCStoreManager.java:1407)
[testlogic] at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:252)
[testlogic] at kodo.jdbc.sql.TrueUnion.execute(TrueUnion.java:193)
[testlogic] at kodo.jdbc.sql.TrueUnion.execute(TrueUnion.java:170)
[testlogic] at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:208)
[testlogic] at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:94)
[testlogic] at org.apache.openjpa.lib.rop.ResultObjectProviderIterator.hasNext(ResultObjectProviderIterator.java:69)
[testlogic] at org.apache.commons.collections.iterators.IteratorChain.updateCurrentIterator(IteratorChain.java:233)
[testlogic] at org.apache.commons.collections.iterators.IteratorChain.hasNext(IteratorChain.java:247)
[testlogic] at org.apache.openjpa.kernel.ExtentImpl$CloseableIteratorChain.hasNext(ExtentImpl.java:238)
[testlogic] at org.apache.commons.collections.iterators.FilterIterator.setNextObject(FilterIterator.java:182)
[testlogic] at org.apache.commons.collections.iterators.FilterIterator.hasNext(FilterIterator.java:93)
[testlogic] at org.apache.openjpa.kernel.ExtentImpl$FilterDeletedIterator.hasNext(ExtentImpl.java:288)
[testlogic] at org.apache.openjpa.kernel.ExtentImpl.list(ExtentImpl.java:102)
[testlogic] at kodo.test.KodoTestCase.deleteAll(KodoTestCase.java:441)
[testlogic] at kodo.test.KodoTestCase.deleteAll(KodoTestCase.java:397)
[testlogic] at kodo.jdbc.meta.tableperclass.TestUnionPlaceholderTypes.testUnion(TestUnionPlaceholderTypes.java:28)
[testlogic] at sun.reflect.GeneratedMethodAccessor206.invoke(Unknown Source)

On sybase,

[testlogic] 16535 WARN [jdo.jdbcR2.suite.testjdbcstandalone (Main
Thread_19)] openjpa.MetaData - OpenJPA cannot map field
'kodo.jdbc.meta.tableperclass.PlaceholderTypesB.blobB' efficiently. It is of an unsupported type. The field value will be serialized to a BLOB by default.
[testlogic] com.sybase.jdbc3.jdbc.SybSQLException: Implicit conversion from datatype 'TEXT' to 'VARCHAR' is not allowed. Use the CONVERT function to run
this query.

[testlogic] at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
[testlogic] at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source)
[testlogic] at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown Source)
[testlogic] at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
[testlogic] at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
[testlogic] at com.sybase.jdbc3.jdbc.SybStatement.queryLoop(Unknown Source)
[testlogic] at com.sybase.jdbc3.jdbc.SybStatement.executeQuery(Unknown Source)
[testlogic] at com.sybase.jdbc3.jdbc.SybPreparedStatement.executeQuery(Unknown Source)
[testlogic] at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:264)
[testlogic] at com.solarmetric.jdbc.PoolConnection$PoolPreparedStatement.executeQuery(PoolConnection.java:260)
[testlogic] at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:262)
[testlogic] at org.apache.openjpa.lib.jdbc.JDBCEventConnectionDecorator$EventPreparedStatement.executeQuery(JDBCEventConnectionDecorator.java:237)
[testlogic] at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:262)
[testlogic] at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:262)
[testlogic] at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeQuery(JDBCStoreManager.java:1407)
[testlogic] at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:252)
[testlogic] at kodo.jdbc.sql.TrueUnion.execute(TrueUnion.java:193)
[testlogic] at kodo.jdbc.sql.TrueUnion.execute(TrueUnion.java:170)

Issue Links

• is related to: OPENJPA-982 ERROR PERFORMING QUERIES ON ORACLE TABLES WITH CLOB COLUMN