db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (Created) (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DERBY-5613) Queries with group by column not included in the column list for JOIN(INNER or OUTER) with NATURAL or USING does not fail
Date Mon, 13 Feb 2012 20:56:59 GMT
Queries with group by column not included in the column list for JOIN(INNER or OUTER) with
NATURAL or USING does not fail
-------------------------------------------------------------------------------------------------------------------------

                 Key: DERBY-5613
                 URL: https://issues.apache.org/jira/browse/DERBY-5613
             Project: Derby
          Issue Type: Bug
          Components: SQL
    Affects Versions: 10.8.2.2
            Reporter: Mamta A. Satoor


A query like following does not raise an error even though countries.country is not part of
the SELECT column list.
SELECT country,count(country) FROM 
COUNTRIES JOIN CITIES USING (COUNTRY) group by countries.country 

This jira is related to DERBY-4631. As noted by Knut in DERBY-4631, SQL:2003 says that the
join columns in a natural join or in a named columns join should be added to the select list
by coalescing the column from the left table with the column from the right table. 

Section 7.7, <joined table>, syntax rules: 

> 1) Let TR1 be the first <table reference>, and let TR2 be the <table 
> reference> or <table factor> that is the second operand of the 
> <joined table>. Let RT1 and RT2 be the row types of TR1 and TR2, 
> respectively. Let TA and TB be the range variables of TR1 and TR2, 
> respectively. (...) 

and 

> 7) If NATURAL is specified or if a <join specification> immediately 
> containing a <named columns join> is specified, then: 
(...) 
> d) If there is at least one corresponding join column, then let SLCC 
> be a <select list> of <derived column>s of the form 
> 
> COALESCE ( TA.C, TB.C ) AS C 
> 
> for every column C that is a corresponding join column, taken in 
> order of their ordinal positions in RT1. 

Derby has it's on logic to retrieve the join column values. It always picks up join column's
value from the left table when we are working with natural left outer join and it picks up
the join column's value from the right table when we are working with natural right outer
join. But this logic does not work for all cases for right outer join. The fix being worked
for DERBY-4631 is to pick the join column's value based on following logic 
1)if the left table's column value is null then pick up the right table's column's value.

2)If the left table's column value is non-null, then pick up that value 

Although this new logic will in essence implement what adding a COALESCE function for a join
colunm might have done but it still allows following query to compile and run
SELECT country,count(country) FROM 
COUNTRIES JOIN CITIES USING (COUNTRY) group by countries.country 

I think query above succeeds because in case of an INNER JOIN or LEFT OUTER JOIN, Derby associates
the join column with the left table during it's bind phase. In case of RIGHT OUTER JOIN, Derby
associates the join column with right table during it's bind phase. I believe, for these reasons,
a query like above will not give an error for the group by column.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message