db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Buddy Testing of Using Clause
Date Thu, 06 May 2010 11:46:10 GMT
On 05/06/10 12:35, Jayaram Subramanian wrote:
> But when i tried
> ResultSet rs=s.executeQuery("SELECT country,count(country) FROM
> COUNTRIES JOIN CITIES USING (COUNTRY) group by cities.country")
>
> It gave
> "Column reference 'COUNTRY' is invalid, or is part of an invalid
> expression.  For a SELECT list with a GROUP BY, the columns and
> expressions being selected may only contain valid grouping expressions
> and valid aggregate expressions."
>
> \Just curious in finding out the reason for this behaviour...
>   

The column country specified in the select list is formally defined as
coalesce(countries.country, cities.country) as country, which is not the
same column as cities.country that's specified in the group by clause.

Now, the implementation doesn't really insert a coalesce here, but
instead replaces country with countries.country (or with cities.country
in right outer joins). So I think this would work if you instead
specified countries.country in group by, even though that strictly
speaking should have failed too.

There's a related bug report for USING/NATURAL JOIN not generating the
COALESCE expression: https://issues.apache.org/jira/browse/DERBY-4631

I'll add a note about this statement that's not properly rejected to
that bug report.

Thanks,

-- 
Knut Anders


Mime
View raw message