From dev-return-5530-apmail-openjpa-dev-archive=openjpa.apache.org@openjpa.apache.org Tue Aug 14 13:11:39 2007 Return-Path: Delivered-To: apmail-openjpa-dev-archive@www.apache.org Received: (qmail 3609 invoked from network); 14 Aug 2007 13:11:38 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 14 Aug 2007 13:11:38 -0000 Received: (qmail 2555 invoked by uid 500); 14 Aug 2007 13:11:36 -0000 Delivered-To: apmail-openjpa-dev-archive@openjpa.apache.org Received: (qmail 2527 invoked by uid 500); 14 Aug 2007 13:11:36 -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 2510 invoked by uid 99); 14 Aug 2007 13:11:36 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 14 Aug 2007 06:11:36 -0700 X-ASF-Spam-Status: No, hits=2.0 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of kwsutter@gmail.com designates 209.85.146.180 as permitted sender) Received: from [209.85.146.180] (HELO wa-out-1112.google.com) (209.85.146.180) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 14 Aug 2007 13:11:34 +0000 Received: by wa-out-1112.google.com with SMTP id m28so2439864wag for ; Tue, 14 Aug 2007 06:11:13 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=pyP4znwvp5xdqdEWX0FhO1zh7GLO4oD/yKBMnr9lx20lF5nydJWnVwzooBfSxmEoDiiGO2qE7qXzH2WiCA+8u201P6U+WqdU6CHtNzZk0CQML7RNF5E84aBBNWrG7oxaIUzzeN8rYKIctUans8xisaWwH//jjvOakBpnYNKDikY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=h/a0d1cXosL6gnZvFkp0+aHqHc7+P6p2gvEk1LGHIpvfXCCFXI6tJK1yFSKsgB3J5yhqGlvHbj6QrkiPunUqMxNCGtC6WEC/qsRUEvgYtZ6k/UJBum4WwnY8eSLGy0rIv/7xAPjfEVHqYWlJOtoTtH4hubp2WiHi9UaYG36hF7Q= Received: by 10.114.57.1 with SMTP id f1mr2485653waa.1187097072787; Tue, 14 Aug 2007 06:11:12 -0700 (PDT) Received: by 10.114.75.15 with HTTP; Tue, 14 Aug 2007 06:11:12 -0700 (PDT) Message-ID: <89c0c52c0708140611w61af84cax93c493437fdbf483@mail.gmail.com> Date: Tue, 14 Aug 2007 08:11:12 -0500 From: "Kevin Sutter" To: dev@openjpa.apache.org Subject: Re: [jira] Commented: (OPENJPA-312) derby fails with duplicate primary key(s) in group by list In-Reply-To: <70d5df710708131013n4b066652x44dc3f630dd4ca37@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_199740_16431239.1187097072736" References: <8026787.1186710222542.JavaMail.jira@brutus> <31176585.1187022091917.JavaMail.jira@brutus> <70d5df710708131013n4b066652x44dc3f630dd4ca37@mail.gmail.com> X-Virus-Checked: Checked by ClamAV on apache.org ------=_Part_199740_16431239.1187097072736 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Daniel, On 8/13/07, Daniel Lee wrote: > > The problem with indexOf() is with a column name as a substring of an > existing ones. For example, BC is part of ABCD and using indexOf will > overlook and miss the column BC if we don't check the leading and tailing > characters of the target column name. Tokenizer is then chosen for this > reason. Good reasons for not using indexOf(). Nice catch. But, I still don't like the use of StringTokenizer. Maybe my information is out of date, but this seems like a lot of overkill for this particular usage. I noticed that we have other uses of StringTokenizer in the OpenJPA code, but these uses are for isolated, post-processing of strings. In your proposed usage, StringTokenizer would be called every time a new column is added to the order by grouping. Your simple example would mean 13 instantiations and associated method invocations. This seems excessive. A better fundamental change should be keeping a List of column objects just > like rderBY which keeps more context (semantic information) for better > query > processing but the fixing cost and risk is much higher and may not worth > it > on processing group by. Looking at the code, this doesn't seem like too bad of an idea. Actually, if you just made it part of the SQLBuffer class, then it could optionally be used by anybody using the SQLBuffer class. Not just for the order by grouping. Or, you could just use a separate _groupingMap to keep track of the tokens in your _grouping string. This seems relatively straightforward and a lot less expensive. Thanks, Kevin Thanks, > Daniel > > > On 8/13/07, Kevin Sutter (JIRA) wrote: > > > > > > [ > > > https://issues.apache.org/jira/browse/OPENJPA-312?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12519456 > ] > > > > Kevin Sutter commented on OPENJPA-312: > > -------------------------------------- > > > > Daniel, > > The code is much cleaner now. Thanks. But, I don't think the > performance > > of StringTokenizer is worth any advantages it might provide. Is there > some > > reason why the previous mechanism of just using indexOf() wasn't > > sufficient? I like the idea of isolating this check to a single boolean > > method like you have, but I would like to see a better performing > > implementation. > > > > Thanks, > > Kevin > > > > > derby fails with duplicate primary key(s) in group by list > > > ---------------------------------------------------------- > > > > > > Key: OPENJPA-312 > > > URL: https://issues.apache.org/jira/browse/OPENJPA-312 > > > Project: OpenJPA > > > Issue Type: Bug > > > Components: sql > > > Affects Versions: 1.0.0 > > > Reporter: Daniel Lee > > > Assignee: Daniel Lee > > > Priority: Minor > > > Fix For: 1.0.0 > > > > > > Attachments: OPENJPA-312.patch, OPENJPA-312.patch > > > > > > > > > derby fails with duplicate primary key(s) in group by list > > > With query "select o.customer, avg(o.amount) from Order o group by > > o.customer" the push-down query contains duplicate columns in the group > by > > clause. This is okay when DB2 and other DB that tolerate the duplicates > but > > Derby returns error. > > > Of course, we can ask fix on Derby but we can also easy fix in OpenJPA > > to avoid duplicates in the group by list. Please refer to the following > for > > the error result and the attach patch for the fix. > > > Output from running the query that generate duplicate in the group by > > list: > > > 6429 demo TRACE [main] openjpa.Query - Executing query: select > > o.customer, avg(o.amount) from Order o group by o.customer > > > 6639 demo TRACE [main] openjpa.jdbc.SQL - > 1639735740> executing prepstmnt 1405375428 SELECT t1.countryCode, t1.id, > > t1.version, t1.city, t1.state, t1.street, t1.zip, t1.creditRating, > t1.name, > > AVG(t0.amount) FROM Order t0 INNER JOIN Customer t1 ON > > t0.customer_countryCode = t1.countryCode AND t0.customer_id = t1.idGROUP > > BY t1.countryCode, t1.id, t1.version, t1.countryCode, t1.id, t1.city, > > t1.state, t1.street, t1.zip, t1.countryCode, t1.id, t1.creditRating, > > t1.name > > > > -- > > This message is automatically generated by JIRA. > > - > > You can reply to this email to add a comment to the issue online. > > > > > ------=_Part_199740_16431239.1187097072736--