Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 8022 invoked from network); 14 Nov 2006 23:50:46 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 14 Nov 2006 23:50:46 -0000 Received: (qmail 88088 invoked by uid 500); 14 Nov 2006 23:50:56 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 88051 invoked by uid 500); 14 Nov 2006 23:50:56 -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 88038 invoked by uid 99); 14 Nov 2006 23:50:56 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 14 Nov 2006 15:50:56 -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 64.233.182.186 as permitted sender) Received: from [64.233.182.186] (HELO nf-out-0910.google.com) (64.233.182.186) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 14 Nov 2006 15:50:43 -0800 Received: by nf-out-0910.google.com with SMTP id x37so81731nfc for ; Tue, 14 Nov 2006 15:50:21 -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=uj9JB6LrG3eoY9ofaj2Yz2GXJQdeBkc6aynoVlUdd1Wy8l/LZgLP7jG5xCvGHwCGfydlFQzWRUbwXV1kkFkiDX7zHQEkdN+FMF+NMLqXOPOw4kTZ0iAmttc6/JvIWUxs1dKRO1s7xY8lih10xOKxIAYrE7lcbF+/Syf0Gkjz1T4= Received: by 10.78.118.19 with SMTP id q19mr1630008huc.1163548220904; Tue, 14 Nov 2006 15:50:20 -0800 (PST) Received: by 10.78.160.16 with HTTP; Tue, 14 Nov 2006 15:50:20 -0800 (PST) Message-ID: Date: Tue, 14 Nov 2006 15:50:20 -0800 From: "Manish Khettry" To: derby-dev@db.apache.org Subject: Re: Functions in GROUP BY expressions? (related to DERBY-883) In-Reply-To: <4559C23A.4070606@sun.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_106608_6010967.1163548220667" 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> X-Virus-Checked: Checked by ClamAV on apache.org ------=_Part_106608_6010967.1163548220667 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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. m On 11/14/06, Oystein Grovlen - Sun Norway wrote: > > Yip Ng wrote: > > > For SIN, COS, TAN functions, they are also considered deterministics. > > However, they do not work currently in Derby with group by expression a= s > > they are mapped to JavaToSQLValueNode and such nodes always return > > false in isEquivalent() method, so I think this is an implementation > > restriction for those built-in functions. > > Just some late follow-up here. As far as I can tell, it is not strictly > correct to say that non-deterministic functions does not work with group > by expressions. What does not work is to use a non-deterministic > function in the select list of a statement when grouping on the same > function. However, the following query works: > > select avg(i) from t group by sin(i) > > I assume that it is the check for equivalence between the select list > and the group by list that requires deterministic functions. (This may > have been clear to others when this was discussed, but at least not to > me.) > > -- > =D8ystein > ------=_Part_106608_6010967.1163548220667 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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.

m

On 11/14/06, Oystein Grovlen - Sun Norway <Oystein.Grovlen@sun.com> wrote: Yip Ng wrote:

> For SIN, COS, TAN functions,  they are = also considered deterministics.
> However, they do not work currently= in Derby with group by expression as
> they are mapped to  = ;JavaToSQLValueNode and such nodes always return
> false in isEquivalent() method, so I think this is an implementati= on
> restriction for those built-in functions.

Just some late = follow-up here.  As far as I can tell, it is not strictly
corr= ect to say that non-deterministic functions does not work with group
by expressions.  What does not work is to use a non-determini= stic
function in the select list of a statement when grouping on the sam= e
function.  However, the following query works:

 =        select avg(i) from t group by sin= (i)

I assume that it is the check for equivalence between the select li= st
and the group by list that requires deterministic functions. &nb= sp;(This may
have been clear to others when this was discussed, but at l= east not to me.)

--
=D8ystein

------=_Part_106608_6010967.1163548220667--