OpenJPA
  1. OpenJPA
  2. OPENJPA-579

JDO Projection query with for a complex object with a clob field fails

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.1.0
    • Fix Version/s: 1.1.0
    • Component/s: jdbc
    • Labels:
      None
    • Environment:

      Description

      The following test case calls setResult on the Query interface to retrieve only the QueryTest2 var with a ordering clause. The generated SQL has an inner select distinct from which the final results are selected by the outer statement.

      PersistenceManager pm = getPM();
      Query q = newQuery(pm, QueryTest4.class);
      q.declareVariables("QueryTest2 var");
      q.setResult("oneToOne2, var");
      q.setOrdering("var.num ascending");
      q.setFilter("manyToMany3.contains (var)");

      Here is the SQL generated to retrieve the result.

      SELECT s.t3_ID, s.t3_TYP, s.t3_VERSN, s.t3_BOOL, s.t3_CHARACTER0, s.t3_CLOBFIELD, s.t3_DATE0, s.t3_DECIMAL0, s.t3_NUM, s.t3_STRING FROM (SELECT DISTINCT t0.ID AS t0_ID, t3.ID AS t3_ID, t3.TYP AS t3_TYP, t3.VERSN AS t3_VERSN, t3.BOOL AS t3_BOOL, t3.CHARACTER0 AS t3_CHARACTER0, t3.DATE0 AS t3_DATE0, t3.DECIMAL0 AS t3_DECIMAL0, t3.NUM AS t3_NUM, t3.STRING AS t3_STRING, t3.ONETOONE AS t3_ONETOONE, t3.NUM2 AS t3_NUM2 FROM querytest4 t0 INNER JOIN query_manytomany3 t1 ON t0.ID = t1.ID LEFT OUTER JOIN querytest1 t3 ON t0.ONETOONE2 = t3.ID INNER JOIN querytest1 t2 ON t1.ELEMENT = t2.ID) s ORDER BY s.t2_NUM ASC

      The generated field in the outer statement s.t3_CLOBFIELD is not present in the distinct clause and leads to the failure exception below.

      1) testMultipleSameTypedObjectProjection(kodo.jdbc.query.TestJDBCProjections)<openjpa-1.1.0-SNAPSHOT-r422266:647033 nonfatal store error> kodo.jdo.DataStoreException: Unknown column 's.t3_CLOBFIELD' in 'field list'

      at org.apache.openjpa.jdbc.sql.SQLExceptions.narrow(SQLExceptions.java:146)
      at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4107)
      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.EagerResultList.<init>(EagerResultList.java:40)
      at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1222)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:989)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:804)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:774)
      at kodo.kernel.KodoQuery.execute(KodoQuery.java:43)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:770)
      at kodo.kernel.KodoQuery.execute(KodoQuery.java:39)
      at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:517)
      at kodo.jdo.QueryImpl.execute(QueryImpl.java:98)
      at kodo.query.ProjectionsTestCase.testMultipleSameTypedObjectProjection(ProjectionsTestCase.java:439)
      at kodo.jdbc.query.TestJDBCProjections.testMultipleSameTypedObjectProjection(TestJDBCProjections.java:137)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

      NestedThrowablesStackTrace:
      com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 's.t3_CLOBFIELD' in 'field list'
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:930)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2864)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1567)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1659)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1143)
      at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1256)
      at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:264)
      at com.solarmetric.jdbc.PoolConnection$PoolPreparedStatement.executeQuery(PoolConnection.java:260)
      at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:262)
      at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:262)
      at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:262)
      at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeQuery(JDBCStoreManager.java:1407)
      at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:252)
      at org.apache.openjpa.jdbc.sql.SelectImpl.executeQuery(SelectImpl.java:449)
      at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:360)
      at kodo.jdbc.sql.KodoSelectImpl.execute(KodoSelectImpl.java:28)
      at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:319)
      at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:94)
      at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:34)
      at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1222)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:989)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:804)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:774)
      at kodo.kernel.KodoQuery.execute(KodoQuery.java:43)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:770)
      at kodo.kernel.KodoQuery.execute(KodoQuery.java:39)
      at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:517)
      at kodo.jdo.QueryImpl.execute(QueryImpl.java:98)
      at kodo.query.ProjectionsTestCase.testMultipleSameTypedObjectProjection(ProjectionsTestCase.java:439)
      at kodo.jdbc.query.TestJDBCProjections.testMultipleSameTypedObjectProjection(TestJDBCProjections.java:137)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

      The fix for the above is in the HandlerFieldStrategy class. In the select distinct constructor the clobfield is being skipped but for the outer select it was getting included so adding condition to honor the eager mode fixes the problem. I will attach the patch shortly.

      1. openjpa-1.1.0-SNAPSHOT-r422266-647033.patch
        0.8 kB
        Sandeep Shrivastava
      2. openjpa-1.1.0-SNAPSHOT-r422266-650703.patch
        0.8 kB
        Sandeep Shrivastava

        Activity

        Hide
        Sandeep Shrivastava added a comment -

        Fix to the HandlerStrategy class.

        Show
        Sandeep Shrivastava added a comment - Fix to the HandlerStrategy class.
        Hide
        Patrick Linskey added a comment -

        Resolved in 1.1.x with r650217.

        Show
        Patrick Linskey added a comment - Resolved in 1.1.x with r650217.
        Hide
        Patrick Linskey added a comment -

        This was made obsolete by another fix.

        Show
        Patrick Linskey added a comment - This was made obsolete by another fix.
        Hide
        Sandeep Shrivastava added a comment -

        Reapply the patch to the MaxEmbeddedLobFieldStrategy class.

        Show
        Sandeep Shrivastava added a comment - Reapply the patch to the MaxEmbeddedLobFieldStrategy class.

          People

          • Assignee:
            Unassigned
            Reporter:
            Sandeep Shrivastava
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Time Tracking

              Estimated:
              Original Estimate - 24h
              24h
              Remaining:
              Remaining Estimate - 24h
              24h
              Logged:
              Time Spent - Not Specified
              Not Specified

                Development