db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jayaram Subramanian <rsjay1...@gmail.com>
Subject Re: Buddy Testing of Using Clause
Date Fri, 07 May 2010 09:19:20 GMT
Hi,
The  natural join with using clause functionality has been tested for
the following scenarios. Please let me know if i missed testing any
features of this functionality.

1) natural joins with 3 tables and repeated join scenario
ResultSet rs=s.executeQuery("select t3.c from t1 join t2 using (a)
join t3 using (c)");
2) multiple join column scenario
select t3.c from t1 join t2 using (a,c) join t3 using (c)
3)joins in a nested query scenario
Select * from t1 where a in (select a from t2 join t1 using (a))
4) joins in a view scenario
create view aview(a,b) as select a,t3.c from t1 join t3 using (c,a)
select * from aview join t1 using (b)
5) joins on a qualified table name scenario
SELECT distinct countries.country,cities.country,cities.city_name FROM
COUNTRIES JOIN CITIES USING (COUNTRY)
6) Left join scenario
SELECT distinct countries.country,cities.country,cities.city_name FROM
COUNTRIES LEFT JOIN CITIES USING (COUNTRY) WHERE CITIES.COUNTRY IS
NULL
6)Joins on a temp table scenario
select flight_id from  flights AS fl(flight_id,
segment_number,airport, depart_time,dest_airport, arrive_time,meal,
flying_time,miles, aircraft) join cities using (airport) where
city_name = 'Seattle'
7)Join with a where clause scenario
SELECT country FROM COUNTRIES JOIN CITIES USING (COUNTRY) where
cities.country='united states'
8) join with aggregate column scenario
SELECT country,count(country) FROM COUNTRIES JOIN CITIES USING
(COUNTRY) group by cities.country

With Regards
Jayaram


On Thu, May 6, 2010 at 6:46 AM, Knut Anders Hatlen <Knut.Hatlen@sun.com> wrote:
> 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