OpenJPA
  1. OpenJPA
  2. OPENJPA-2179

'distinct' and 'join' combinations lead lots of unneccessary sub-queries for @Embedded and @Lob fields

    Details

    • Type: Bug Bug
    • Status: In Progress
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.2.0
    • Fix Version/s: 2.4.0
    • Component/s: kernel
    • Labels:
      None

      Description

      I have an Entity (Course) with a simple @Embedded field and a @Lob. I do not use any LAZY attribution on them!

      If I do a normal em.find, the entity will be loaded as a whole (all the fields, including the embedded and the lob will be fetched immediately).
      Sidenote: the Lecturer referred in the select is defined as
      @OneToMany(mappedBy = "course",
      cascade =

      {CascadeType.PERSIST, CascadeType.REMOVE, CascadeType.MERGE}

      ,
      orphanRemoval = true, fetch = FetchType.EAGER)
      @OrderColumn(name = "POSITION")
      private List<Lecturer> lecturers;

      The following selects DO work

      • "select c from Course c join c.lecturers l "
      • "select distinct c from Course c"

      The following selects create tons of subqueries! 1 separate sub-query for each @Embedded field, and also for each @Lob

      • "select distinct c from Course c join c.lecturers l "
      • "select distinct c from Lecturer l join l.course c"
      • "select c from Lecturer l join l.course c"

      This happens ONLY if I run this stuff against Oracle. In MySQL it seems to work properly.

      I'll try to create a unit test for it.

        Activity

        Hide
        Mark Struberg added a comment -

        I'm close to a working unit test now. Just need to figure a way how to detect those subselects automatically via a unit test?
        Of course, the first best thing is to have it on the console already. But that cannot be easily checked via an assert...
        Any tips?

        Show
        Mark Struberg added a comment - I'm close to a working unit test now. Just need to figure a way how to detect those subselects automatically via a unit test? Of course, the first best thing is to have it on the console already. But that cannot be easily checked via an assert... Any tips?
        Hide
        Mark Struberg added a comment -

        Btw, I preserved an old output of this phenomenon in a unit test of our app:

        2012-04-26 17:13:36,657 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> executing prepstmnt 1053577980 SELECT DISTINCT t1.id, t1.optlock, t1.blocked, t1.businessKey, t1.cancelled, t1.comStuOk, t1.courseNr, t1.createdAt, t1.createdBy, t1.deanAcademicAffairsOk, t1.deleted, t1.denied, t1.depStudOk, t1.ects, t1.elearningUrl, t1.firstAnnouncement, t1.formalError, t1.headOk, t1.held, t1.hours, t1.language, t1.lecturerOk, t1.LINKEDCOURSE_ID, t1.modifiedAt, t1.modifiedBy, t1.orgUntCode, t1.semesterCode, t1.titleDe, t1.titleEn, t1.tuwelUrl, t1.typeKey, t1.url, t1.veniaOk, t1.visibilityDateElearningUrl, t1.visibilityDateTuwelUrl FROM Lecturer t0, Course t1 WHERE t0.COURSE_ID = t1.id
        2012-04-26 17:13:36,664 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> [7 ms] spent
        2012-04-26 17:13:36,665 [main] DEBUG jdbc.SQLDiag load: class myapp.core.be.i18n.entities.LocalizedLongString oid: 10060
        2012-04-26 17:13:36,665 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> executing prepstmnt 196270141 SELECT t0.examModalitiesDe, t0.examModalitiesEn FROM Course t0 WHERE t0.id = ? [params=?]
        2012-04-26 17:13:36,669 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> [4 ms] spent
        2012-04-26 17:13:36,673 [main] DEBUG jdbc.SQLDiag load: class myapp.core.be.i18n.entities.LocalizedLongString oid: 10060
        2012-04-26 17:13:36,673 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> executing prepstmnt 802857302 SELECT t0.lectureNotesInfoDe, t0.lectureNotesInfoEn FROM Course t0 WHERE t0.id = ? [params=?]
        2012-04-26 17:13:36,677 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> [4 ms] spent
        2012-04-26 17:13:36,682 [main] DEBUG jdbc.SQLDiag load: class myapp.core.be.i18n.entities.LocalizedLongString oid: 10060
        2012-04-26 17:13:36,682 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> executing prepstmnt 1253026437 SELECT t0.objectiveDe, t0.objectiveEn FROM Course t0 WHERE t0.id = ? [params=?]
        2012-04-26 17:13:36,686 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> [4 ms] spent
        2012-04-26 17:13:36,698 [main] DEBUG jdbc.SQLDiag load: class myapp.core.be.i18n.entities.LocalizedLongString oid: 10060
        2012-04-26 17:13:36,698 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> executing prepstmnt 199943961 SELECT t0.recommendationDe, t0.recommendationEn FROM Course t0 WHERE t0.id = ? [params=?]
        2012-04-26 17:13:36,702 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> [4 ms] spent
        2012-04-26 17:13:36,728 [main] DEBUG jdbc.SQLDiag load: class myapp.core.be.i18n.entities.LocalizedLongString oid: 10060
        2012-04-26 17:13:36,728 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> executing prepstmnt 25287543 SELECT t0.remarkDe, t0.remarkEn FROM Course t0 WHERE t0.id = ? [params=?]
        2012-04-26 17:13:36,732 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> [4 ms] spent
        2012-04-26 17:13:36,738 [main] DEBUG jdbc.SQLDiag load: class myapp.core.be.i18n.entities.LocalizedLongString oid: 10060
        2012-04-26 17:13:36,738 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> executing prepstmnt 1802848163 SELECT t0.contentDe, t0.contentEn FROM Course t0 WHERE t0.id = ? [params=?]
        2012-04-26 17:13:36,742 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> [4 ms] spent
        2012-04-26 17:13:36,750 [main] DEBUG jdbc.SQLDiag load: class myapp.course.be.entities.Course oid: 10060

        all the LocalizedLongString are @Embedded fields directly in the Course entity. This effect doesn't happen with the MySqlDictionary.

        Show
        Mark Struberg added a comment - Btw, I preserved an old output of this phenomenon in a unit test of our app: 2012-04-26 17:13:36,657 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> executing prepstmnt 1053577980 SELECT DISTINCT t1.id, t1.optlock, t1.blocked, t1.businessKey, t1.cancelled, t1.comStuOk, t1.courseNr, t1.createdAt, t1.createdBy, t1.deanAcademicAffairsOk, t1.deleted, t1.denied, t1.depStudOk, t1.ects, t1.elearningUrl, t1.firstAnnouncement, t1.formalError, t1.headOk, t1.held, t1.hours, t1.language, t1.lecturerOk, t1.LINKEDCOURSE_ID, t1.modifiedAt, t1.modifiedBy, t1.orgUntCode, t1.semesterCode, t1.titleDe, t1.titleEn, t1.tuwelUrl, t1.typeKey, t1.url, t1.veniaOk, t1.visibilityDateElearningUrl, t1.visibilityDateTuwelUrl FROM Lecturer t0, Course t1 WHERE t0.COURSE_ID = t1.id 2012-04-26 17:13:36,664 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> [7 ms] spent 2012-04-26 17:13:36,665 [main] DEBUG jdbc.SQLDiag load: class myapp.core.be.i18n.entities.LocalizedLongString oid: 10060 2012-04-26 17:13:36,665 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> executing prepstmnt 196270141 SELECT t0.examModalitiesDe, t0.examModalitiesEn FROM Course t0 WHERE t0.id = ? [params=?] 2012-04-26 17:13:36,669 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> [4 ms] spent 2012-04-26 17:13:36,673 [main] DEBUG jdbc.SQLDiag load: class myapp.core.be.i18n.entities.LocalizedLongString oid: 10060 2012-04-26 17:13:36,673 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> executing prepstmnt 802857302 SELECT t0.lectureNotesInfoDe, t0.lectureNotesInfoEn FROM Course t0 WHERE t0.id = ? [params=?] 2012-04-26 17:13:36,677 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> [4 ms] spent 2012-04-26 17:13:36,682 [main] DEBUG jdbc.SQLDiag load: class myapp.core.be.i18n.entities.LocalizedLongString oid: 10060 2012-04-26 17:13:36,682 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> executing prepstmnt 1253026437 SELECT t0.objectiveDe, t0.objectiveEn FROM Course t0 WHERE t0.id = ? [params=?] 2012-04-26 17:13:36,686 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> [4 ms] spent 2012-04-26 17:13:36,698 [main] DEBUG jdbc.SQLDiag load: class myapp.core.be.i18n.entities.LocalizedLongString oid: 10060 2012-04-26 17:13:36,698 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> executing prepstmnt 199943961 SELECT t0.recommendationDe, t0.recommendationEn FROM Course t0 WHERE t0.id = ? [params=?] 2012-04-26 17:13:36,702 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> [4 ms] spent 2012-04-26 17:13:36,728 [main] DEBUG jdbc.SQLDiag load: class myapp.core.be.i18n.entities.LocalizedLongString oid: 10060 2012-04-26 17:13:36,728 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> executing prepstmnt 25287543 SELECT t0.remarkDe, t0.remarkEn FROM Course t0 WHERE t0.id = ? [params=?] 2012-04-26 17:13:36,732 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> [4 ms] spent 2012-04-26 17:13:36,738 [main] DEBUG jdbc.SQLDiag load: class myapp.core.be.i18n.entities.LocalizedLongString oid: 10060 2012-04-26 17:13:36,738 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> executing prepstmnt 1802848163 SELECT t0.contentDe, t0.contentEn FROM Course t0 WHERE t0.id = ? [params=?] 2012-04-26 17:13:36,742 [main] DEBUG jdbc.SQL <t 169937755, conn 1482440345> [4 ms] spent 2012-04-26 17:13:36,750 [main] DEBUG jdbc.SQLDiag load: class myapp.course.be.entities.Course oid: 10060 all the LocalizedLongString are @Embedded fields directly in the Course entity. This effect doesn't happen with the MySqlDictionary.
        Hide
        Mark Struberg added a comment -

        I have a pretty hard time reproducing this in OpenJPA.

        Now I imported the 1:1 tables from our real project and getting the following Exception:

        Caused by: java.sql.SQLException: Ung?ltiger Spaltentyp: getCLOB not implemented for class oracle.jdbc.driver.T4CVarcharAccessor
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
        at oracle.jdbc.driver.Accessor.unimpl(Accessor.java:358)
        at oracle.jdbc.driver.Accessor.getCLOB(Accessor.java:1328)
        at oracle.jdbc.driver.OracleResultSetImpl.getCLOB(OracleResultSetImpl.java:1311)
        at oracle.jdbc.driver.OracleResultSetImpl.getClob(OracleResultSetImpl.java:1477)
        at org.apache.commons.dbcp.DelegatingResultSet.getClob(DelegatingResultSet.java:553)
        at org.apache.commons.dbcp.DelegatingResultSet.getClob(DelegatingResultSet.java:553)
        at org.apache.openjpa.lib.jdbc.DelegatingResultSet.getClob(DelegatingResultSet.java:592)
        at org.apache.openjpa.jdbc.sql.DBDictionary.getClob(DBDictionary.java:746)
        at org.apache.openjpa.jdbc.sql.OracleDictionary.getClobString(OracleDictionary.java:659)

        The funny thing is that I do NOT get this error in prodution! The only difference is that we are using c3p0 as pool.

        Show
        Mark Struberg added a comment - I have a pretty hard time reproducing this in OpenJPA. Now I imported the 1:1 tables from our real project and getting the following Exception: Caused by: java.sql.SQLException: Ung?ltiger Spaltentyp: getCLOB not implemented for class oracle.jdbc.driver.T4CVarcharAccessor at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.Accessor.unimpl(Accessor.java:358) at oracle.jdbc.driver.Accessor.getCLOB(Accessor.java:1328) at oracle.jdbc.driver.OracleResultSetImpl.getCLOB(OracleResultSetImpl.java:1311) at oracle.jdbc.driver.OracleResultSetImpl.getClob(OracleResultSetImpl.java:1477) at org.apache.commons.dbcp.DelegatingResultSet.getClob(DelegatingResultSet.java:553) at org.apache.commons.dbcp.DelegatingResultSet.getClob(DelegatingResultSet.java:553) at org.apache.openjpa.lib.jdbc.DelegatingResultSet.getClob(DelegatingResultSet.java:592) at org.apache.openjpa.jdbc.sql.DBDictionary.getClob(DBDictionary.java:746) at org.apache.openjpa.jdbc.sql.OracleDictionary.getClobString(OracleDictionary.java:659) The funny thing is that I do NOT get this error in prodution! The only difference is that we are using c3p0 as pool.
        Hide
        Mark Struberg added a comment -

        The attached patch is a first version which doesn't yet show the problem. Still trying to determine what really causes the subselects.

        btw, here is the commandline I use to test this in openjpa-persistence-jdbc:

        mvn clean test -Dtest=TestOracleDistinctJoin -Doracle.artifactid=ojdbc14 -Doracle.version=10.2.0.4.0 -Dopenjpa.oracle.url="jdbc:oracle:thin:@192.168.0.101/XE" -Dopenjpa.oracle.username=username -Dopenjpa.oracle.password=password -Dopenjpa.Log=DefaultLevel=TRACE -Dtest-oracle | tee mvn.log

        Show
        Mark Struberg added a comment - The attached patch is a first version which doesn't yet show the problem. Still trying to determine what really causes the subselects. btw, here is the commandline I use to test this in openjpa-persistence-jdbc: mvn clean test -Dtest=TestOracleDistinctJoin -Doracle.artifactid=ojdbc14 -Doracle.version=10.2.0.4.0 -Dopenjpa.oracle.url="jdbc:oracle:thin:@192.168.0.101/XE" -Dopenjpa.oracle.username=username -Dopenjpa.oracle.password=password -Dopenjpa.Log=DefaultLevel=TRACE -Dtest-oracle | tee mvn.log
        Hide
        Mark Struberg added a comment -

        could this have something to do with the weirdo broken Oracle behaviour that you need to read a CLOB which has <4000 bytes different than one with >= 4000?

        (whoever invented this crazyness at Oracle should publicly confess to not ever touch a database core code anymore in his life).

        Show
        Mark Struberg added a comment - could this have something to do with the weirdo broken Oracle behaviour that you need to read a CLOB which has <4000 bytes different than one with >= 4000? (whoever invented this crazyness at Oracle should publicly confess to not ever touch a database core code anymore in his life).
        Hide
        Mark Struberg added a comment -

        I've now committed a unit test which shows the problem. The new TestOracleDistinctJoin also contains a guide howto invoke this test on the commandline.

        The test will give the following output (amonst others), where you can clearly see that SomeEmbeddable get's fetched via a separate SQL statement, even though there is no LAZY loading set on the Embedded field:

        1348 test TRACE [main] openjpa.Query - Executing query: select c from Lecturer l join l.course c
        1348 test TRACE [main] openjpa.jdbc.SQL - <t 270142182, conn 754307120> executing prepstmnt 960389670 SELECT t1.ID, t1.OPTLOCK, t1.COURSENUMBER, t1.NORMALATTRIBUTE FROM SMSGW.Lecturer t0, SMSGW.Course t1 WHERE t0.COURSE_ID = t1.ID
        1350 test TRACE [main] openjpa.jdbc.SQL - <t 270142182, conn 754307120> [2 ms] spent
        1350 test TRACE [main] openjpa.jdbc.SQLDiag - load: class org.apache.openjpa.jdbc.oracle.SomeEmbeddable oid: 2551
        1350 test TRACE [main] openjpa.jdbc.SQL - <t 270142182, conn 754307120> executing prepstmnt 2034408626 SELECT t0.OBJECTIVEDE, t0.OBJECTIVEEN FROM SMSGW.Course t0 WHERE t0.ID = ? [params=?]
        1351 test TRACE [main] openjpa.jdbc.SQL - <t 270142182, conn 754307120> [1 ms] spent
        1354 test TRACE [main] openjpa.jdbc.SQLDiag - load: class org.apache.openjpa.jdbc.oracle.Course oid: 2551
        1354 test TRACE [main] openjpa.jdbc.SQLDiag - Eager relations: [org.apache.openjpa.jdbc.oracle.Course.lecturers]
        1354 test TRACE [main] openjpa.jdbc.SQL - <t 270142182, conn 754307120> executing prepstmnt 698114403 SELECT t0.LOBCOLUMN, t1.COURSE_ID, t1.POSITION, t1.ID, t1.OPTLOCK, t1.NAME FROM SMSGW.Course t0, SMSGW.Lecturer t1 WHERE t0.ID = ? AND t0.ID = t1.COURSE_ID ORDER BY t1.COURSE_ID ASC, t1.POSITION ASC [params=?]
        1355 test TRACE [main] openjpa.jdbc.SQL - <t 270142182, conn 754307120> [1 ms] spent

        Show
        Mark Struberg added a comment - I've now committed a unit test which shows the problem. The new TestOracleDistinctJoin also contains a guide howto invoke this test on the commandline. The test will give the following output (amonst others), where you can clearly see that SomeEmbeddable get's fetched via a separate SQL statement, even though there is no LAZY loading set on the Embedded field: 1348 test TRACE [main] openjpa.Query - Executing query: select c from Lecturer l join l.course c 1348 test TRACE [main] openjpa.jdbc.SQL - <t 270142182, conn 754307120> executing prepstmnt 960389670 SELECT t1.ID, t1.OPTLOCK, t1.COURSENUMBER, t1.NORMALATTRIBUTE FROM SMSGW.Lecturer t0, SMSGW.Course t1 WHERE t0.COURSE_ID = t1.ID 1350 test TRACE [main] openjpa.jdbc.SQL - <t 270142182, conn 754307120> [2 ms] spent 1350 test TRACE [main] openjpa.jdbc.SQLDiag - load: class org.apache.openjpa.jdbc.oracle.SomeEmbeddable oid: 2551 1350 test TRACE [main] openjpa.jdbc.SQL - <t 270142182, conn 754307120> executing prepstmnt 2034408626 SELECT t0.OBJECTIVEDE, t0.OBJECTIVEEN FROM SMSGW.Course t0 WHERE t0.ID = ? [params=?] 1351 test TRACE [main] openjpa.jdbc.SQL - <t 270142182, conn 754307120> [1 ms] spent 1354 test TRACE [main] openjpa.jdbc.SQLDiag - load: class org.apache.openjpa.jdbc.oracle.Course oid: 2551 1354 test TRACE [main] openjpa.jdbc.SQLDiag - Eager relations: [org.apache.openjpa.jdbc.oracle.Course.lecturers] 1354 test TRACE [main] openjpa.jdbc.SQL - <t 270142182, conn 754307120> executing prepstmnt 698114403 SELECT t0.LOBCOLUMN, t1.COURSE_ID, t1.POSITION, t1.ID, t1.OPTLOCK, t1.NAME FROM SMSGW.Course t0, SMSGW.Lecturer t1 WHERE t0.ID = ? AND t0.ID = t1.COURSE_ID ORDER BY t1.COURSE_ID ASC, t1.POSITION ASC [params=?] 1355 test TRACE [main] openjpa.jdbc.SQL - <t 270142182, conn 754307120> [1 ms] spent
        Hide
        Mark Struberg added a comment -

        If I change the fields inside the @Embedded entity from @Lob to String, then all things work fine again. I'm currently digging into the MaxEmbeddedClobFieldStrategy. Any hints/ideas what could be going wrong?

        Show
        Mark Struberg added a comment - If I change the fields inside the @Embedded entity from @Lob to String, then all things work fine again. I'm currently digging into the MaxEmbeddedClobFieldStrategy. Any hints/ideas what could be going wrong?
        Hide
        Rick Curtis added a comment -

        Sorry, I meant to reply to this earlier....

        > Just need to figure a way how to detect those subselects automatically via a unit test?
        @See org.apache.openjpa.persistence.test.SQLListenerTestCase.

        Show
        Rick Curtis added a comment - Sorry, I meant to reply to this earlier.... > Just need to figure a way how to detect those subselects automatically via a unit test? @See org.apache.openjpa.persistence.test.SQLListenerTestCase.
        Hide
        Albert Lee added a comment -

        I am see the following exception in TestOracleDistinctJoin.testJoinOnly, ONLY in Oracle 10g

        Caused by: <openjpa-2.3.0-SNAPSHOT-rexported fatal store error> org.apache.openjpa.persistence.EntityExistsException: ORA-01400: cannot insert NULL into ("SCOTT"."COURSE"."COURSEID")

        {prepstmnt 87607598 INSERT INTO SCOTT.Course (ID, COURSENUMBER, LOBCOLUMN, NORMALATTRIBUTE, OPTLOCK, OBJECTIVEDE, OBJECTIVEEN) VALUES (?, ?, ?, ?, ?, ?, ?)} [code=1400, state=23000]
        FailedObject: org.apache.openjpa.jdbc.oracle.Course@13124d36
        at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4910)
        at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4885)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:78)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:143)
        at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.batchOrExecuteRow(BatchingPreparedStatementManagerImpl.java:100)
        at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushAndUpdate(BatchingPreparedStatementManagerImpl.java:83)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:99)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:87)
        at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:550)
        at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:106)
        at org.apache.openjpa.jdbc.kernel.BatchingConstraintUpdateManager.flush(BatchingConstraintUpdateManager.java:59)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:103)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:76)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:735)
        at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:131)
        ... 37 more
        Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ORA-01400: cannot insert NULL into ("SCOTT"."COURSE"."COURSEID")
        {prepstmnt 87607598 INSERT INTO SCOTT.Course (ID, COURSENUMBER, LOBCOLUMN, NORMALATTRIBUTE, OPTLOCK, OBJECTIVEDE, OBJECTIVEEN) VALUES (?, ?, ?, ?, ?, ?, ?)}

        [code=1400, state=23000]
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:219)
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:195)
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$1000(LoggingConnectionDecorator.java:59)
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:1134)
        at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:272)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1763)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.executeUpdate(PreparedStatementManagerImpl.java:267)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:118)
        ... 48 more

        Show
        Albert Lee added a comment - I am see the following exception in TestOracleDistinctJoin.testJoinOnly, ONLY in Oracle 10g Caused by: <openjpa-2.3.0-SNAPSHOT-rexported fatal store error> org.apache.openjpa.persistence.EntityExistsException: ORA-01400: cannot insert NULL into ("SCOTT"."COURSE"."COURSEID") {prepstmnt 87607598 INSERT INTO SCOTT.Course (ID, COURSENUMBER, LOBCOLUMN, NORMALATTRIBUTE, OPTLOCK, OBJECTIVEDE, OBJECTIVEEN) VALUES (?, ?, ?, ?, ?, ?, ?)} [code=1400, state=23000] FailedObject: org.apache.openjpa.jdbc.oracle.Course@13124d36 at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4910) at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4885) at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136) at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:78) at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:143) at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.batchOrExecuteRow(BatchingPreparedStatementManagerImpl.java:100) at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushAndUpdate(BatchingPreparedStatementManagerImpl.java:83) at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:99) at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:87) at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:550) at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:106) at org.apache.openjpa.jdbc.kernel.BatchingConstraintUpdateManager.flush(BatchingConstraintUpdateManager.java:59) at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:103) at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:76) at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:735) at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:131) ... 37 more Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ORA-01400: cannot insert NULL into ("SCOTT"."COURSE"."COURSEID") {prepstmnt 87607598 INSERT INTO SCOTT.Course (ID, COURSENUMBER, LOBCOLUMN, NORMALATTRIBUTE, OPTLOCK, OBJECTIVEDE, OBJECTIVEEN) VALUES (?, ?, ?, ?, ?, ?, ?)} [code=1400, state=23000] at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:219) at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:195) at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$1000(LoggingConnectionDecorator.java:59) at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:1134) at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:272) at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1763) at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.executeUpdate(PreparedStatementManagerImpl.java:267) at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:118) ... 48 more
        Hide
        Mark Struberg added a comment -

        weird, I did the tests on an Oracle 10 XE windows installation in a VM (the easiest way to do oracle tests on a mac).

        Actually you can remove the courseId field at all. It does not have any impact on the superfluous sub-queries. txs!

        Show
        Mark Struberg added a comment - weird, I did the tests on an Oracle 10 XE windows installation in a VM (the easiest way to do oracle tests on a mac). Actually you can remove the courseId field at all. It does not have any impact on the superfluous sub-queries. txs!
        Hide
        Mark Struberg added a comment -

        I digged deeper and found the reason for the sub-selects to be the DISTINCT.

        Of course a CLOB or BLOB field cannot be used in a distinct by most DBs. But most databases also optimize them away if they there is the primary key in the result.

        The corresponding code is in the HandlerFieldStrategy line 174:
        http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/meta/strats/HandlerFieldStrategy.java?view=markup

        Show
        Mark Struberg added a comment - I digged deeper and found the reason for the sub-selects to be the DISTINCT. Of course a CLOB or BLOB field cannot be used in a distinct by most DBs. But most databases also optimize them away if they there is the primary key in the result. The corresponding code is in the HandlerFieldStrategy line 174: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/meta/strats/HandlerFieldStrategy.java?view=markup
        Hide
        Mark Struberg added a comment -

        I now went further to the last select: "select c from Lecturer l join l.course c"

        And here we also do too many subselects - and this time there is no DISTINCT as excuse :

        What I find a bit weird is that PCPath#selectColumns() (line 876) invokes the Select chain with JDBCFetchConfiguration.EAGER_NONE and not check for the configured fetch type?

        Also, why does a @Lob not get a LobFieldStrategy but a HandlerFieldStrategy?

        Show
        Mark Struberg added a comment - I now went further to the last select: "select c from Lecturer l join l.course c" And here we also do too many subselects - and this time there is no DISTINCT as excuse : What I find a bit weird is that PCPath#selectColumns() (line 876) invokes the Select chain with JDBCFetchConfiguration.EAGER_NONE and not check for the configured fetch type? Also, why does a @Lob not get a LobFieldStrategy but a HandlerFieldStrategy?

          People

          • Assignee:
            Mark Struberg
            Reporter:
            Mark Struberg
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:

              Development