Return-Path: X-Original-To: apmail-openjpa-dev-archive@www.apache.org Delivered-To: apmail-openjpa-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 14804FA7F for ; Tue, 7 May 2013 14:45:18 +0000 (UTC) Received: (qmail 12014 invoked by uid 500); 7 May 2013 14:45:17 -0000 Delivered-To: apmail-openjpa-dev-archive@openjpa.apache.org Received: (qmail 11952 invoked by uid 500); 7 May 2013 14:45:17 -0000 Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@openjpa.apache.org Delivered-To: mailing list dev@openjpa.apache.org Received: (qmail 11930 invoked by uid 99); 7 May 2013 14:45:16 -0000 Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 07 May 2013 14:45:16 +0000 Date: Tue, 7 May 2013 14:45:16 +0000 (UTC) From: "Albert Lee (JIRA)" To: dev@openjpa.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (OPENJPA-2356) Use new Offset/Limit/ROWNUM features in DB2 family of databases for range queries MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ 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 - executing stmnt 2008817593 DELETE FROM autoincpc1 1796 TestConv TRACE [main] openjpa.jdbc.SQL - [0 ms] spent ........ 1796 TestConv TRACE [main] openjpa.jdbc.SQL - executing stmnt 1179468258 DELETE FROM OPENJPA_SEQUENCE_TABLE 1803 TestConv TRACE [main] openjpa.jdbc.SQL - [7 ms] spent 2029 TestConv TRACE [main] openjpa.jdbc.SQL - 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 - [1 ms] spent 2055 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 2000169429 DELETE t0 FROM EagerOuterJoinPC t0 2055 TestConv TRACE [main] openjpa.jdbc.SQL - [0 ms] spent 2094 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 2021888176 SELECT SEQUENCE_VALUE FROM OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE [params=?] 2098 TestConv TRACE [main] openjpa.jdbc.SQL - [4 ms] spent 2111 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 576658153 INSERT INTO OPENJPA_SEQUENCE_TABLE (ID, SEQUENCE_VALUE) VALUES (?, ?) [params=?, ?] 2122 TestConv TRACE [main] openjpa.jdbc.SQL - [11 ms] spent 2137 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 1062659565 SELECT SEQUENCE_VALUE FROM OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE [params=?] 2137 TestConv TRACE [main] openjpa.jdbc.SQL - [0 ms] spent 2138 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 2137731611 UPDATE OPENJPA_SEQUENCE_TABLE SET SEQUENCE_VALUE = ? WHERE ID = ? AND SEQUENCE_VALUE = ? [params=?, ?, ?] 2138 TestConv TRACE [main] openjpa.jdbc.SQL - [0 ms] spent 2158 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 64338076 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?, ?, ?] 2168 TestConv TRACE [main] openjpa.jdbc.SQL - [10 ms] spent 2169 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 1385961324 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?, ?, ?] 2169 TestConv TRACE [main] openjpa.jdbc.SQL - [0 ms] spent 2169 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 1655441108 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?, ?, ?] 2170 TestConv TRACE [main] openjpa.jdbc.SQL - [1 ms] spent 2170 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 1605442425 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?, ?, ?] 2170 TestConv TRACE [main] openjpa.jdbc.SQL - [0 ms] spent 2170 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 1236308610 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?, ?, ?] 2171 TestConv TRACE [main] openjpa.jdbc.SQL - [0 ms] spent 2171 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 46712790 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?, ?, ?] 2171 TestConv TRACE [main] openjpa.jdbc.SQL - [0 ms] spent 2172 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 160194151 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?, ?, ?] 2172 TestConv TRACE [main] openjpa.jdbc.SQL - [0 ms] spent 2172 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 635681751 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?, ?, ?] 2172 TestConv TRACE [main] openjpa.jdbc.SQL - [0 ms] spent 2173 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 1051225362 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?, ?, ?] 2173 TestConv TRACE [main] openjpa.jdbc.SQL - [0 ms] spent 2174 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 2067115646 INSERT INTO EagerOuterJoinPC (id, name, helper) VALUES (?, ?, ?) [params=?, ?, ?] 2174 TestConv TRACE [main] openjpa.jdbc.SQL - [0 ms] spent 2225 TestConv TRACE [main] openjpa.jdbc.SQL - 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 - [2 ms] spent 2243 TestConv TRACE [main] openjpa.jdbc.SQL - executing prepstmnt 1282830982 SELECT COUNT(*) FROM (SELECT t0.id FROM EagerOuterJoinPC t0 LIMIT ?, ?) s [params=?, ?] 2244 TestConv TRACE [main] openjpa.jdbc.SQL - [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