openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Albert Lee (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (OPENJPA-2356) Use new Offset/Limit/ROWNUM features in DB2 family of databases for range queries
Date Tue, 07 May 2013 14:45:16 GMT

    [ https://issues.apache.org/jira/browse/OPENJPA-2356?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13650892#comment-13650892
] 

Albert Lee commented on OPENJPA-2356:
-------------------------------------

This test failed on Oracle, MySQL, HSQLDB, PostgreSQL so far. It does pass for Derby.

The following is the MySQL trace from the run.  MySQL is at v5.5

-------------------------------------------------------
 T E S T S
-------------------------------------------------------
Running org.apache.openjpa.persistence.jdbc.meta.TestRangeQuery
58  TestConv  INFO   [main] openjpa.Runtime - Starting OpenJPA 2.3.0-SNAPSHOT
155  TestConv  INFO   [main] openjpa.jdbc.JDBC - Using dictionary class "org.apache.openjpa.jdbc.sql.MySQLDictionary".
973  TestConv  INFO   [main] openjpa.jdbc.JDBC - Connected to MySQL version 5.5 using JDBC
driver MySQL-AB JDBC Driver version mysql-connector-java-5.1.12 ( Revision: ${bzr.revision-id}
). 
1500  TestConv  WARN   [main] openjpa.MetaData - OpenJPA cannot map field "org.apache.openjpa.persistence.jdbc.common.apps.ConstantJoinPC4.manyToMany"
efficiently.  It is of an unsupported type. The field value will be serialized to a BLOB by
default.
1770  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 85031456> executing
stmnt 2008817593 DELETE FROM autoincpc1
1796  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 85031456> [0 ms]
spent
........
1796  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 85031456> executing
stmnt 1179468258 DELETE FROM OPENJPA_SEQUENCE_TABLE
1803  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 85031456> [7 ms]
spent
2029  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 830152036> executing
prepstmnt 17227669 SELECT t0.id, t1.id, t2.id, t3.id, t3.EAGER_ID, t3.strngfld, t4.id, t4.DTYPE,
t4.strngfld, t4.intField, t5.id, t5.EAGER_ID, t5.strngfld, t6.id, t7.id, t7.DTYPE, t7.strngfld,
t7.intField, t6.strngfld, t1.strngfld, t2.intField, t0.strngfld FROM HelperPC t0 LEFT OUTER
JOIN EagerPC t1 ON t0.EAGER_ID = t1.id LEFT OUTER JOIN EagerPCSub t2 ON t1.id = t2.id LEFT
OUTER JOIN HelperPC t3 ON t1.EAGER_ID = t3.id LEFT OUTER JOIN HelperPC3 t4 ON t1.eagsub =
t4.id LEFT OUTER JOIN HelperPC t5 ON t1.HELPER_ID = t5.id LEFT OUTER JOIN HelperPC2 t6 ON
t1.RECURSE_ID = t6.id LEFT OUTER JOIN HelperPC3 t7 ON t6.HELPER_ID = t7.id WHERE (t4.DTYPE
IS NULL OR t4.DTYPE IN (?)) [params=?]
2030  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 830152036> [1 ms]
spent
2055  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 138583065> executing
prepstmnt 2000169429 DELETE t0 FROM EagerOuterJoinPC t0
2055  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 138583065> [0 ms]
spent
2094  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 93343904> executing
prepstmnt 2021888176 SELECT SEQUENCE_VALUE FROM OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE
[params=?]
2098  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 93343904> [4 ms]
spent
2111  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 299878560> executing
prepstmnt 576658153 INSERT INTO OPENJPA_SEQUENCE_TABLE (ID, SEQUENCE_VALUE) VALUES (?, ?)
[params=?, ?]
2122  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 299878560> [11
ms] spent
2137  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1645300503> executing
prepstmnt 1062659565 SELECT SEQUENCE_VALUE FROM OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE
[params=?]
2137  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1645300503> [0
ms] spent
2138  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1645300503> executing
prepstmnt 2137731611 UPDATE OPENJPA_SEQUENCE_TABLE SET SEQUENCE_VALUE = ? WHERE ID = ? AND
SEQUENCE_VALUE = ? [params=?, ?, ?]
2138  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1645300503> [0
ms] spent
2158  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> executing
prepstmnt 64338076 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?,
?, ?]
2168  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> [10
ms] spent
2169  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> executing
prepstmnt 1385961324 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?,
?, ?]
2169  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> [0
ms] spent
2169  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> executing
prepstmnt 1655441108 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?,
?, ?]
2170  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> [1
ms] spent
2170  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> executing
prepstmnt 1605442425 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?,
?, ?]
2170  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> [0
ms] spent
2170  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> executing
prepstmnt 1236308610 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?,
?, ?]
2171  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> [0
ms] spent
2171  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> executing
prepstmnt 46712790 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?,
?, ?]
2171  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> [0
ms] spent
2172  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> executing
prepstmnt 160194151 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?,
?, ?]
2172  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> [0
ms] spent
2172  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> executing
prepstmnt 635681751 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?,
?, ?]
2172  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> [0
ms] spent
2173  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> executing
prepstmnt 1051225362 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?,
?, ?]
2173  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> [0
ms] spent
2174  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> executing
prepstmnt 2067115646 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?,
?, ?]
2174  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1145768891> [0
ms] spent
2225  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1682745014> executing
prepstmnt 163905324 SELECT t0.id, t0.name FROM EagerOuterJoinPC t0 ORDER BY t0.name ASC LIMIT
?, ? [params=?, ?]
2227  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1682745014> [2
ms] spent
2243  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1951982241> executing
prepstmnt 1282830982 SELECT COUNT(*) FROM (SELECT t0.id FROM EagerOuterJoinPC t0 LIMIT ?,
?) s [params=?, ?]
2244  TestConv  TRACE  [main] openjpa.jdbc.SQL - <t 1266620481, conn 1951982241> [1
ms] spent
Tests run: 1, Failures: 1, Errors: 0, Skipped: 0, Time elapsed: 2.554 sec <<< FAILURE!
testQueryRange(org.apache.openjpa.persistence.jdbc.meta.TestRangeQuery)  Time elapsed: 2.484
sec  <<< FAILURE!
junit.framework.AssertionFailedError: expected:<5> but was:<0>
        at junit.framework.Assert.fail(Assert.java:47)
        at junit.framework.Assert.failNotEquals(Assert.java:282)
        at junit.framework.Assert.assertEquals(Assert.java:64)
        at junit.framework.Assert.assertEquals(Assert.java:201)
        at junit.framework.Assert.assertEquals(Assert.java:207)
        at org.apache.openjpa.persistence.jdbc.meta.TestRangeQuery.testQueryRange(TestRangeQuery.java:74)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 
                
> Use new Offset/Limit/ROWNUM features in DB2 family of databases for range queries
> ---------------------------------------------------------------------------------
>
>                 Key: OPENJPA-2356
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2356
>             Project: OpenJPA
>          Issue Type: Improvement
>            Reporter: Pinaki Poddar
>            Assignee: Pinaki Poddar
>             Fix For: 2.3.0
>
>         Attachments: graycol.gif
>
>
> JPA queries that use setFirstResult() and/or setMaxResult() translate to some form of
Offset and Limit syntax in database SQL query. Recently DB2 and Derby has added ROWNUM and
FETCH offset respectively.
> OpenJPA should use these facilities when appropriate.   

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message