Return-Path: 
 <derby-dev-return-82786-apmail-db-derby-dev-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-derby-dev-archive@www.apache.org
Received: (qmail 37551 invoked from network); 20 Nov 2010 02:11:03 -0000
Received: from unknown (HELO mail.apache.org) (140.211.11.3)
  by 140.211.11.9 with SMTP; 20 Nov 2010 02:11:03 -0000
Received: (qmail 83666 invoked by uid 500); 20 Nov 2010 02:11:34 -0000
Delivered-To: apmail-db-derby-dev-archive@db.apache.org
Received: (qmail 83643 invoked by uid 500); 20 Nov 2010 02:11:34 -0000
Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:derby-dev-help@db.apache.org>
List-Unsubscribe: <mailto:derby-dev-unsubscribe@db.apache.org>
List-Post: <mailto:derby-dev@db.apache.org>
List-Id: <derby-dev.db.apache.org>
Reply-To: <derby-dev@db.apache.org>
Delivered-To: mailing list derby-dev@db.apache.org
Received: (qmail 83636 invoked by uid 99); 20 Nov 2010 02:11:34 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 20 Nov 2010 02:11:34 +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.22] (HELO thor.apache.org) (140.211.11.22)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 20 Nov 2010 02:11:34 +0000
Received: from thor (localhost [127.0.0.1])
	by thor.apache.org (8.13.8+Sun/8.13.8) with ESMTP id oAK2BDLV029109
	for <derby-dev@db.apache.org>; Sat, 20 Nov 2010 02:11:13 GMT
Message-ID: <22556503.211501290219073661.JavaMail.jira@thor>
Date: Fri, 19 Nov 2010 21:11:13 -0500 (EST)
From: "Dag H. Wanvik (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Created: (DERBY-4909) Allow SELECT list column references
 not mentioned in GROUP BY if CR is functionally dependent
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394

Allow SELECT list column references not mentioned in GROUP BY if CR is functionally dependent
---------------------------------------------------------------------------------------------

                 Key: DERBY-4909
                 URL: https://issues.apache.org/jira/browse/DERBY-4909
             Project: Derby
          Issue Type: Improvement
            Reporter: Dag H. Wanvik


Presently, Derby rejects column references in a SELECT .. GROUP BY if the column reference isn't a grouping column.  This was correct SQL as per SQL 1992, but later versions of the standard has loosened this to the present wording (e.g. SQL 2003, section 7.12 <query specification>, SR 15:

 "If T is a grouped table, then let G be the set of grouping columns of T. In each <value expression> contained in <select list> , each column reference that references a column of T shall reference some column C that is functionally dependent on G or shall be contained in an aggregated argument of a <set function specification> whose aggregation query is QS."

This can be useful in certain queries, cf. the example below culled from http://rpbouman.blogspot.com/2007/05/debunking-group-by-myths.html

SELECT    f.film_id,
          f.title,
          COUNT(fa.actor_id)
FROM      film        f
LEFT JOIN film_actor  fa
ON        f.film_id = fa.film_id
GROUP BY  f.film_id;

In this case, f.title is functionally dependent on f.film_id (primary key), so the query is correct according to SQL 2003 and later.

Derby requires that f.title also be specified as a grouping column, which is more verbose, but can also have performance implications (although I didn't attempt to measure how this could impact Derby yet), at least if the functional dependency analysis is not performed to eliminate the extra grouping column. Do we do any such analysis?

Another example, a generated column would also be functionally dependent on the columns used to compute it.


-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.