We found out some more details about this one.
OpenJPA causes a PersistenceException: ORA-00936: missing expression when a
named query is executed for "ExportOrder". The sql statement contains empty
IN() expressions in the where clause. "ExportOrder" has a one-to-many
relation to "ExportOrderVm" (fetch type "EAGER"). The entity "ExportOrderVm"
has a composite primary key that is represented by the id class
"ExportOrderVmId".
In 4 different scenarios everything works perfectly:
- if the fetch type is changed to "LAZY"
- if execution is performed as native query
- if "ExportOrderVm" wouldn’t have a composite primary key
- if using H2 database instead of Oracle
Why does the named query fail in the constellation described above
(FetchType.EAGER, Oracle, Composite Primary Key, NamedQuery)?
We think this is a bug in how OpenJPA generates SELECT statements. Could you
please have a look? Thank you!
This doesn’t work:
Query query = em.createNamedQuery("selectAllExportOrder");
List<ExportOrder> exportOrderList = query.getResultList();
This works fine:
Query query = em.createNativeQuery("SELECT * FROM EXPORT_ORDER",
ExportOrder.class);
List<ExportOrder> exportOrderList = query.getResultList();
public class ExportOrderVmId implements Serializable {
private Long exportOrderId;
private Long vmId;
:
}
@Entity
@IdClass(ExportOrderVmId.class)
@Table(name = "EXPORT_ORDER_VM", schema = "INFOP_SYSTEM")
public class ExportOrderVm implements Serializable {
@Id
@Column(name = "VM_ID")
private Long vmId;
@Id
@ManyToOne
@JoinColumn(name = "EXPORT_ORDER_ID")
@ForeignKey
private ExportOrder exportOrderId;
:
}
@Entity
@NamedQuery(name = "selectAllExportOrder", query = "SELECT ea FROM
ExportOrder ea")
@Table(name = "EXPORT_ORDER", schema = "INFOP_SYSTEM")
public class ExportOrder implements Serializable {
@Id
@SequenceGenerator(name = "InfopSystem.seqExportOrder", sequenceName =
"INFOP_SYSTEM.SEQ_EXPORT_ORDER")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator =
"InfopSystem.seqExportOrder")
@Column(name = ID)
private Long id;
@Size(max = 100)
@Column(name = NAME)
private String name;
@ElementDependent
@OneToMany(mappedBy = "exportOrderId", fetch = FetchType.EAGER, cascade
= CascadeType.ALL)
private List<ExportOrderVm> exportOrderVmList = new
ArrayList<ExportOrderVm>();
:
}
<openjpa-1.2.3-SNAPSHOT-r422266:1053401 nonfatal general error>
org.apache.openjpa.persistence.PersistenceException: ORA-00936: missing
expression
{prepstmnt 328799129
SELECT t0.ID, t1.EXPORT_ORDER_ID, t1.VM_ID, t2.ID, t2.NAME
FROM INFOP_SYSTEM.EXPORT_ORDER t0,
INFOP_SYSTEM.EXPORT_ORDER_VM t1,
INFOP_SYSTEM.EXPORT_ORDER t2
WHERE (t0.ID IN (?, ?) OR t0.ID IN () OR t0.ID IN () OR
t0.ID IN () OR t0.ID IN ()) AND t0.ID =
t1.EXPORT_ORDER_ID AND t1.EXPORT_ORDER_ID =
t2.ID(+)
ORDER BY t0.ID ASC
[params=(long) 780, (long) 778]} [code=936, state=42000]
at
org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4247)
at
org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4212)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:88)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:64)
at
org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.handleCheckedException(SelectResultObjectProvider.java:155)
at
org.apache.openjpa.lib.rop.WindowResultList.getInternal(WindowResultList.java:150)
at
org.apache.openjpa.lib.rop.AbstractNonSequentialResultList.get(AbstractNonSequentialResultList.java:73)
at
org.apache.openjpa.kernel.DelegatingResultList.get(DelegatingResultList.java:244)
:
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ORA-00936:
missing expression
{prepstmnt 328799129
SELECT t0.ID, t1.EXPORT_ORDER_ID, t1.VM_ID, t2.ID, t2.NAME
FROM INFOP_SYSTEM.EXPORT_ORDER t0,
INFOP_SYSTEM.EXPORT_ORDER_VM t1,
INFOP_SYSTEM.EXPORT_ORDER t2
WHERE (t0.ID IN (?, ?) OR t0.ID IN () OR t0.ID IN () OR
t0.ID IN () OR t0.ID IN ()) AND t0.ID =
t1.EXPORT_ORDER_ID AND t1.EXPORT_ORDER_ID =
t2.ID(+)
ORDER BY t0.ID ASC
[params=(long) 780, (long) 778]} [code=936, state=42000]
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:215)
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:203)
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$700(LoggingConnectionDecorator.java:57)
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeQuery(LoggingConnectionDecorator.java:895)
at
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:262)
at
org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeQuery(JDBCStoreManager.java:1599)
at
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:252)
at
org.apache.openjpa.jdbc.sql.SelectImpl.executeQuery(SelectImpl.java:509)
at
org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:384)
at
org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:325)
at
org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:331)
at
org.apache.openjpa.jdbc.kernel.PagingResultObjectProvider.executeEagerSelects(PagingResultObjectProvider.java:380)
at
org.apache.openjpa.jdbc.kernel.PagingResultObjectProvider.executeEagerSelects(PagingResultObjectProvider.java:299)
at
org.apache.openjpa.jdbc.kernel.PagingResultObjectProvider.fillPage(PagingResultObjectProvider.java:270)
at
org.apache.openjpa.jdbc.kernel.PagingResultObjectProvider.getResultObject(PagingResultObjectProvider.java:219)
at
org.apache.openjpa.lib.rop.WindowResultList.getInternal(WindowResultList.java:131)
... 29 more
NestedThrowables:
java.sql.SQLSyntaxErrorException: ORA-00936: missing expression
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at
oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
at
oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:264)
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeQuery(LoggingConnectionDecorator.java:893)
at
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:262)
at
org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeQuery(JDBCStoreManager.java:1599)
at
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:252)
at
org.apache.openjpa.jdbc.sql.SelectImpl.executeQuery(SelectImpl.java:509)
at
org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:384)
at
org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:325)
at
org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:331)
at
org.apache.openjpa.jdbc.kernel.PagingResultObjectProvider.executeEagerSelects(PagingResultObjectProvider.java:380)
at
org.apache.openjpa.jdbc.kernel.PagingResultObjectProvider.executeEagerSelects(PagingResultObjectProvider.java:299)
at
org.apache.openjpa.jdbc.kernel.PagingResultObjectProvider.fillPage(PagingResultObjectProvider.java:270)
at
org.apache.openjpa.jdbc.kernel.PagingResultObjectProvider.getResultObject(PagingResultObjectProvider.java:219)
at
org.apache.openjpa.lib.rop.WindowResultList.getInternal(WindowResultList.java:131)
at
org.apache.openjpa.lib.rop.AbstractNonSequentialResultList.get(AbstractNonSequentialResultList.java:73)
at
org.apache.openjpa.kernel.DelegatingResultList.get(DelegatingResultList.java:244)
:
--
View this message in context:
http://openjpa.208410.n2.nabble.com/Wrong-SQL-statement-being-generated-tp7470306p7484773.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
