Functionally dependent columns shouldn't need to be included in GROUP BY if the
determining column is included in the GROUP BY
------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4584
URL: http://tracker.firebirdsql.org/browse/CORE-4584
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Affects Versions: 2.5.3, 3.0 Alpha 2, 3.0 Alpha 1, 2.5.2 Update 1, 2.5.2
Reporter: Mark Rotteveel
When using a group by in a query where the select list includes the primary key
(or unique key) and other columns of that table (or derived from those
columns), then it should not be necessary to include those other columns in the
group by clause as they are already uniquely grouped/identified by that primary
key (which means: those other columns are functionally dependent on the primary
or unique key, or the key determines the other columns).
For example a simple group by on a join between sales and customer in the
employee example database:
select sum(sales.total_value), customer.cust_no, customer.customer
from sales
inner join customer using (cust_no)
group by customer.cust_no, customer.customer
It should be possible to drop the customer.customer column from the group by:
select sum(sales.total_value), customer.cust_no, customer.customer
from sales
inner join customer using (cust_no)
group by customer.cust_no
This should even apply when referencing sales.cust_no instead of
customer.cust_no:
select sum(sales.total_value), sales.cust_no, customer.customer
from sales
inner join customer using (cust_no)
group by sales.cust_no
This is specified in SQL:2011 Foundation 7.12 <query specification>:
"
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.
"
Functional dependency is defined in 4.19 Functional dependencies
Note that the use of "functional dependency" implies more than just columns
from the same table as that primary or unique key (for examples joins from that
table to another and so on are also a functional dependency, as are strings
constructed from values of that table).
So for example joining from customer to country for the currency should be
possible without including that column in the grouping set:
select sum(sales.total_value), customer.cust_no, customer.customer,
country.currency
from sales
inner join customer using (cust_no)
inner join country using (country)
group by customer.cust_no
Or creating a label for the customer consisting of the primary key and another
field (NOTE: cust_no isn't even a column in the select list!)
select sum(sales.total_value), CAST(customer.cust_no AS VARCHAR(10)) || ' ' ||
customer.customer)
from sales
inner join customer using (cust_no)
group by customer.cust_no
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Comprehensive Server Monitoring with Site24x7.
Monitor 10 servers for $9/Month.
Get alerted through email, SMS, voice calls or mobile push notifications.
Take corrective actions from your mobile device.
http://p.sf.net/sfu/Zoho
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
