Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 81869 invoked from network); 15 Nov 2006 18:47:43 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 15 Nov 2006 18:47:43 -0000 Received: (qmail 12591 invoked by uid 500); 15 Nov 2006 18:47:43 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 12566 invoked by uid 500); 15 Nov 2006 18:47:43 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 12557 invoked by uid 99); 15 Nov 2006 18:47:42 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 15 Nov 2006 10:47:42 -0800 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 (herse.apache.org: domain of manish.khettry@gmail.com designates 66.249.92.169 as permitted sender) Received: from [66.249.92.169] (HELO ug-out-1314.google.com) (66.249.92.169) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 15 Nov 2006 10:47:29 -0800 Received: by ug-out-1314.google.com with SMTP id o2so215551uge for ; Wed, 15 Nov 2006 10:47:08 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=ZG3J6rSDXLaK7Ww5P0/2UbZiYmsG9nzBeuPtvn+m4utU1raRp0+dSwFs/C/xWVtr6qRXx5QhKe4O6cVS7xGx2zkDhWJsMFkkIg85lLawrLkcCkuu76vMntP5qlXKxGQWliw0BAexSBtCaEJCKA2GWzXVUUMa7QV9Zzfn0ynkUqE= Received: by 10.78.204.7 with SMTP id b7mr410732hug.1163616427676; Wed, 15 Nov 2006 10:47:07 -0800 (PST) Received: by 10.78.160.16 with HTTP; Wed, 15 Nov 2006 10:47:07 -0800 (PST) Message-ID: Date: Wed, 15 Nov 2006 10:47:07 -0800 From: "Manish Khettry" To: derby-dev@db.apache.org Subject: Re: Functions in GROUP BY expressions? (related to DERBY-883) In-Reply-To: <455AF336.5090406@sun.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9592_1571522.1163616427550" References: <454135ED.70401@gmail.com> <20061027062440.GB22897@localhost.localdomain> <45421F3F.6080904@gmail.com> <45422A96.3000803@apache.org> <20061031094946.GC16469@atum01.norway.sun.com> <4547BFAC.6020908@gmail.com> <4559C23A.4070606@sun.com> <455AF336.5090406@sun.com> X-Virus-Checked: Checked by ClamAV on apache.org ------=_Part_9592_1571522.1163616427550 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Actually, when I was implementing this functionality I thought that comparing java nodes for equivalence was not possible or perhaps hard to do= . I could be wrong on this, so someone who knows this stuff better can commen= t on this. At any rate, I find it strange that one of these query works but not the other. I think right now as things stand, both should fail. If there is consensus that we should allow java value nodes in group by expressions, then we should add isEquivalence methods to all the JavaValueNodes. m On 11/15/06, Oystein Grovlen - Sun Norway wrote: > > Manish Khettry wrote: > > Hmm strange. I am not sure why this > > > > select avg(i) from t group by sin(i) > > > > works but not this, > > > > select avg(i), sin(i) from test group by sin(i); > > > > I think its a bug-- both should be disallowed. > > Why should they be disallowed? I agree that in most cases grouping by > non-deterministic functions do not make sense, but I am not sure that > is a reason for disallowing it. Also, a function that is generally > non-deterministic may be deterministic in a given context. > > Theoretically, I do not see why equivalence and non-deterministic > functions are an issue for GROUP BY. I do not think an implementation > need to execute the function twice if it appears both in the group-by > clause and in the select list. The value returned when determining > group could be used for the select list also. > > -- > =D8ystein > ------=_Part_9592_1571522.1163616427550 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Actually, when I was implementing this functionality I thought that comparing java nodes for equivalence was not possible or perhaps hard to do. I could be wrong on this, so someone who knows this stuff better can comment on this.

At any rate, I find it strange that one of the= se query works but not the other. I think right now as things stand, both s= hould fail. If there is consensus that we should allow java value nodes in = group by expressions, then we should add isEquivalence methods to all the J= avaValueNodes.

m


On 11/15/06, Oystein Grovlen - Sun Norway <Oystein.Grovlen@sun.com> wrote:
Manish Khettry wrote:
> Hmm strange. I am not sure why this
>=
>    select avg(i) from t group by sin(i)
&= gt;
> works but not this,
>
>   select avg(i= ), sin(i) from test group by sin(i);
>
> I think its a bug-- both should be disallowed.

Wh= y should they be disallowed?  I agree that in most cases grouping= by
non-deterministic functions do not make sense, but I am not sure tha= t
is a reason for disallowing it.  Also, a function that is ge= nerally
non-deterministic may be deterministic in a given context.

Theor= etically, I do not see why equivalence and non-deterministic
functions a= re an issue for GROUP BY.  I do not think an implementation
ne= ed to execute the function twice if it appears both in the group-by
clause and in the select list.  The value returned when deter= mining
group could be used for the select list also.

--
=D8yst= ein

------=_Part_9592_1571522.1163616427550--