From dev-return-15751-apmail-openjpa-dev-archive=openjpa.apache.org@openjpa.apache.org Thu Mar 18 17:58:51 2010 Return-Path: Delivered-To: apmail-openjpa-dev-archive@www.apache.org Received: (qmail 33335 invoked from network); 18 Mar 2010 17:58:51 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 18 Mar 2010 17:58:51 -0000 Received: (qmail 3857 invoked by uid 500); 18 Mar 2010 17:58:50 -0000 Delivered-To: apmail-openjpa-dev-archive@openjpa.apache.org Received: (qmail 3821 invoked by uid 500); 18 Mar 2010 17:58:50 -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 3813 invoked by uid 99); 18 Mar 2010 17:58:50 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 18 Mar 2010 17:58:50 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.140] (HELO brutus.apache.org) (140.211.11.140) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 18 Mar 2010 17:58:48 +0000 Received: from brutus.apache.org (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id 3F07B234C4AE for ; Thu, 18 Mar 2010 17:58:27 +0000 (UTC) Message-ID: <331020079.347641268935107257.JavaMail.jira@brutus.apache.org> Date: Thu, 18 Mar 2010 17:58:27 +0000 (UTC) From: "Catalina Wei (JIRA)" To: dev@openjpa.apache.org Subject: [jira] Created: (OPENJPA-1584) PreparedQuery gives wrong result if query has subquery and parameters are used in both main select and subselect MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 X-Virus-Checked: Checked by ClamAV on apache.org PreparedQuery gives wrong result if query has subquery and parameters are used in both main select and subselect ---------------------------------------------------------------------------------------------------------------- Key: OPENJPA-1584 URL: https://issues.apache.org/jira/browse/OPENJPA-1584 Project: OpenJPA Issue Type: Bug Components: performance Affects Versions: 2.0.0 Reporter: Catalina Wei Assignee: Catalina Wei a back-to-back of the following JPQL query providing different set of parameter values, the second execution gives wrong answer. "select o from OrderJPA o where o.OrderId in (select max(o1.OrderId) from OrderJPA o1 where ((o1.CustomerId = :customerId) and (o1.DistrictId = :districtId) and (o1.WarehouseId = :warehouseId))) and (o.CustomerId = :customerId) and (o.DistrictId = :districtId) and (o.WarehouseId = :warehouseId)" SQL trace shown the first time query execution, let say customerId=339, districtId=3, warehouseId=23, then query returns 1 row: the SQL trace looked fine: [3/16/10 17:40:36:831 CDT] 00000045 OpenJPA 3 openjpa.jdbc.SQL: Trace: executing prepstmnt 1547852866 SELECT t0.O_D_ID, t0.O_ID, t0.O_W_ID, t0.VERSION, t0.O_ALL_LOCAL, t0.O_CARRIER_ID, t0.O_C_ID, t0.O_ENTRY_D, t0.O_OL_CNT FROM ORDERS t0 WHERE (t0.O_ID IN (SELECT MAX(t1.O_ID) FROM ORDERS t1 WHERE (t1.O_C_ID = ? AND t1.O_D_ID = ? AND t1.O_W_ID = ?) ) AND t0.O_C_ID = ? AND t0.O_D_ID = ? AND t0.O_W_ID = ?) optimize for 1 row [params=(short) 339, (short) 3, (short) 23, (short) 339, (short) 3, (short) 23] On the next execution of the same JPQL, the PreparedQueryImpl (which is cached before) gets reused. In processing user provided parameters, for example, customerId=2967, districtId=5, warehouseId=22, It is observed that the parameter values are incorrect: the last 3 values were incorrectly copied from the previously cached version. [3/16/10 17:45:42:411 CDT] 00000043 OpenJPA 3 openjpa.jdbc.SQL: Trace: executing prepstmnt 1531796301 SELECT t0.O_D_ID, t0.O_ID, t0.O_W_ID, t0.VERSION, t0.O_ALL_LOCAL, t0.O_CARRIER_ID, t0.O_C_ID, t0.O_ENTRY_D, t0.O_OL_CNT FROM ORDERS t0 WHERE (t0.O_ID IN (SELECT MAX(t1.O_ID) FROM ORDERS t1 WHERE (t1.O_C_ID = ? AND t1.O_D_ID = ? AND t1.O_W_ID = ?) ) AND t0.O_C_ID = ? AND t0.O_D_ID = ? AND t0.O_W_ID = ?) optimize for 1 row [params=(short) 2967, (short) 5, (short) 22, (short) 339, (short) 3, (short) 23] -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.