You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Jayaram Subramanian <rs...@gmail.com> on 2010/05/01 13:23:32 UTC
Re: Buddy Testing of Using Clause
Hi,
I am trying to test along the comments. I am taking the 10.5 as
reference .. Is that correct? Also when i saw 10.5 reference guide for
join i saw the following sql
SELECT SAMP.EMP_ACT.*, LASTNAME
FROM SAMP.EMP_ACT JOIN SAMP.EMPLOYEE
ON EMP_ACT.EMPNO = EMPLOYEE.EMPNO
Is there any samp schema which gets installed along with Derby install?
With Regards
Jayaram
On Thu, Apr 29, 2010 at 11:23 AM, Rick Hillegas
<ri...@oracle.com> wrote:
> Hi Jayaram,
>
> The Reference Guide documents a fair amount of complexity related to the
> USING clause:
>
> 1) In expanding * in the SELECT list, both when * occurs by itself and when
> it is qualified with a table name
>
> 2) In transforming the USING clause into an ON clause when the column names
> are unqualified for different kinds of joins
>
> It would be great if you could verify these cases.
>
> Thanks,
> -Rick
>
> Jayaram Subramanian wrote:
>>
>> Hi,
>> Could i have your thoughts and more ideas for testing the "using"
>> functionality..
>>
>> With Regards
>> Jayaram
>>
>> On Wed, Apr 28, 2010 at 10:31 PM, Jayaram Subramanian
>> <rs...@gmail.com> wrote:
>>
>>>
>>> Hi,
>>> I tried the following view based scenario of the using clause and
>>> found the code flow through without issues
>>> s.execute("create table t1(a int, b int, c int)");
>>> s.execute("create table t2(a int, b int, c int)");
>>> s.execute("create table t3(a int, b varchar(5), c int)");
>>> s.execute("create view aview(a,b) as select a,t3.c from t1 join t3
>>> using (c,a)");
>>> s.execute("insert into t1 values (1,2,3),(2,3,4),(4,4,4)");
>>> s.execute("insert into t2 values (1,2,3),(2,3,4),(5,5,5)");
>>> s.execute("insert into t3 values " +
>>> "(2,'abc',8),(4,'def',10),(null,null,null)");
>>> ResultSet rs=s.executeQuery("select * from aview join t1 using (b)");
>>>
>>> With Regards
>>> Jayaram
>>>
>>>
>>
>>
>
>
Re: Buddy Testing of Using Clause
Posted by Jayaram Subramanian <rs...@gmail.com>.
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 <Kn...@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
>
>
Re: Buddy Testing of Using Clause
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
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
Re: Buddy Testing of Using Clause
Posted by Jayaram Subramanian <rs...@gmail.com>.
Hi
Thanks for clarification.I validated both scenarios mentioned
yesterday and verified that the code going through without issues..
Also as a testing on aggregate functions i was trying the following
When i tried
ResultSet rs=s.executeQuery("SELECT country FROM COUNTRIES JOIN CITIES
USING (COUNTRY) where cities.country='UNITED STATES'"), the qiuery
went through without issues
When i tried
ResultSet rs=s.executeQuery("SELECT country,count(country) FROM
COUNTRIES JOIN CITIES USING (COUNTRY) group by country") the query
went through
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...
With Regards
Jayaram
On Wed, May 5, 2010 at 7:43 AM, Knut Anders Hatlen <Kn...@sun.com> wrote:
> On 05/05/10 11:48, Jayaram Subramanian wrote:
>> Hi,
>> As per the review comments i used toursdb and executed the following
>> queries and found them going through without issues
>>
>> SELECT countries.* FROM COUNTRIES JOIN CITIES USING (COUNTRY)
>> SELECT distinct countries.country,cities.country FROM COUNTRIES JOIN
>> CITIES USING (COUNTRY)
>> SELECT distinct countries.country FROM COUNTRIES LEFT JOIN CITIES
>> USING (COUNTRY) WHERE CITIES.COUNTRY IS NULL
>>
>> But i have a few clairifications in the following 2 scenarios
>> Scenario 1
>> =================
>> When i tried doing
>> SELECT * FROM FLIGHTAVAILABILITY JOIN FLIGHTS ON (FLIGHT_ID) WHERE
>> FLIGHT_DATE > '2004-04-20'
>> I got an error message stating "Column name 'FLIGHT_ID' is in more
>> than one table in the FROM list. Do we need to issue the query
>> differently?
>
> I think there's a typo in the query. It says ON instead of USING. If I
> change it from ON to USING, it works.
>
>> Scenario 2
>> ============
>> Given a scenario where we have to get flights originating from a given
>> city (Ex:Seattle)
>> How can we use the "using" clause in this scenario given that the
>> column name is "ORIG_AIRPPORT" in flights table and "AIPRORT" in the
>> cities table?...
>
> That's trickier... :) But you can write such a query with USING if you
> assign a correlation name to the column with an AS clause:
>
> 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'
>
> or
>
> select flight_id from
> (select flight_id, orig_airport AS airport from flights) f
> join cities using (airport)
> where city_name = 'Seattle'
>
>
> --
> Knut Anders
>
Re: Buddy Testing of Using Clause
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
On 05/05/10 11:48, Jayaram Subramanian wrote:
> Hi,
> As per the review comments i used toursdb and executed the following
> queries and found them going through without issues
>
> SELECT countries.* FROM COUNTRIES JOIN CITIES USING (COUNTRY)
> SELECT distinct countries.country,cities.country FROM COUNTRIES JOIN
> CITIES USING (COUNTRY)
> SELECT distinct countries.country FROM COUNTRIES LEFT JOIN CITIES
> USING (COUNTRY) WHERE CITIES.COUNTRY IS NULL
>
> But i have a few clairifications in the following 2 scenarios
> Scenario 1
> =================
> When i tried doing
> SELECT * FROM FLIGHTAVAILABILITY JOIN FLIGHTS ON (FLIGHT_ID) WHERE
> FLIGHT_DATE > '2004-04-20'
> I got an error message stating "Column name 'FLIGHT_ID' is in more
> than one table in the FROM list. Do we need to issue the query
> differently?
I think there's a typo in the query. It says ON instead of USING. If I
change it from ON to USING, it works.
> Scenario 2
> ============
> Given a scenario where we have to get flights originating from a given
> city (Ex:Seattle)
> How can we use the "using" clause in this scenario given that the
> column name is "ORIG_AIRPPORT" in flights table and "AIPRORT" in the
> cities table?...
That's trickier... :) But you can write such a query with USING if you
assign a correlation name to the column with an AS clause:
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'
or
select flight_id from
(select flight_id, orig_airport AS airport from flights) f
join cities using (airport)
where city_name = 'Seattle'
--
Knut Anders
Re: Buddy Testing of Using Clause
Posted by Jayaram Subramanian <rs...@gmail.com>.
Hi,
As per the review comments i used toursdb and executed the following
queries and found them going through without issues
SELECT countries.* FROM COUNTRIES JOIN CITIES USING (COUNTRY)
SELECT distinct countries.country,cities.country FROM COUNTRIES JOIN
CITIES USING (COUNTRY)
SELECT distinct countries.country FROM COUNTRIES LEFT JOIN CITIES
USING (COUNTRY) WHERE CITIES.COUNTRY IS NULL
But i have a few clairifications in the following 2 scenarios
Scenario 1
=================
When i tried doing
SELECT * FROM FLIGHTAVAILABILITY JOIN FLIGHTS ON (FLIGHT_ID) WHERE
FLIGHT_DATE > '2004-04-20'
I got an error message stating "Column name 'FLIGHT_ID' is in more
than one table in the FROM list. Do we need to issue the query
differently?
Scenario 2
============
Given a scenario where we have to get flights originating from a given
city (Ex:Seattle)
How can we use the "using" clause in this scenario given that the
column name is "ORIG_AIRPPORT" in flights table and "AIPRORT" in the
cities table?...
With Regards
Jayaram
On Sun, May 2, 2010 at 11:47 AM, Jayaram Subramanian
<rs...@gmail.com> wrote:
> Thanks a lot.... I will proceed based on the comments.
>
> With Regards
> Jayaram
>
> On Sun, May 2, 2010 at 11:32 AM, Lily Wei <li...@yahoo.com> wrote:
>> Hi Jayaram:
>> On top of Derby reference menu, this is some join example with using
>> keyword for you to reference too.
>> http://www.java2s.com/Tutorial/Oracle/0140__Table-Joins/SimplifyingJoinswiththeUSINGKeyword.htm
>>
>> Lily
>>
>> ________________________________
>> From: Knut Anders Hatlen <Kn...@Sun.COM>
>> To: derby-dev@db.apache.org
>> Sent: Sun, May 2, 2010 5:33:15 AM
>> Subject: Re: Buddy Testing of Using Clause
>>
>> On 05/ 1/10 01:23 PM, Jayaram Subramanian wrote:
>>> Hi,
>>>
>>> I am trying to test along the comments. I am taking the 10.5 as
>>> reference .. Is that correct?
>>
>> The USING clause was not supported in 10.5, so you won't find anything
>> about it in the 10.5 manuals. You can find the latest development
>> version of the reference guide here:
>> http://db.apache.org/derby/docs/dev/ref/
>>
>>> Also when i saw 10.5 reference guide for
>>> join i saw the following sql
>>>
>>> SELECT SAMP.EMP_ACT.*, LASTNAME
>>> FROM SAMP.EMP_ACT JOIN SAMP.EMPLOYEE
>>> ON EMP_ACT.EMPNO = EMPLOYEE.EMPNO
>>>
>>> Is there any samp schema which gets installed along with Derby install?
>>>
>>
>> I don't think so. I suspect this example is based on a DB2 sample
>> database (at least it looks like many of the examples in the DB2 manual
>> use a similar database). Many other examples in the Derby manuals use
>> toursdb, which you can find under java/demo/toursdb/toursdb after you
>> have run ant all.
>>
>> --
>> Knut Anders
>>
>>
>>
>
Re: Buddy Testing of Using Clause
Posted by Jayaram Subramanian <rs...@gmail.com>.
Thanks a lot.... I will proceed based on the comments.
With Regards
Jayaram
On Sun, May 2, 2010 at 11:32 AM, Lily Wei <li...@yahoo.com> wrote:
> Hi Jayaram:
> On top of Derby reference menu, this is some join example with using
> keyword for you to reference too.
> http://www.java2s.com/Tutorial/Oracle/0140__Table-Joins/SimplifyingJoinswiththeUSINGKeyword.htm
>
> Lily
>
> ________________________________
> From: Knut Anders Hatlen <Kn...@Sun.COM>
> To: derby-dev@db.apache.org
> Sent: Sun, May 2, 2010 5:33:15 AM
> Subject: Re: Buddy Testing of Using Clause
>
> On 05/ 1/10 01:23 PM, Jayaram Subramanian wrote:
>> Hi,
>>
>> I am trying to test along the comments. I am taking the 10.5 as
>> reference .. Is that correct?
>
> The USING clause was not supported in 10.5, so you won't find anything
> about it in the 10.5 manuals. You can find the latest development
> version of the reference guide here:
> http://db.apache.org/derby/docs/dev/ref/
>
>> Also when i saw 10.5 reference guide for
>> join i saw the following sql
>>
>> SELECT SAMP.EMP_ACT.*, LASTNAME
>> FROM SAMP.EMP_ACT JOIN SAMP.EMPLOYEE
>> ON EMP_ACT.EMPNO = EMPLOYEE.EMPNO
>>
>> Is there any samp schema which gets installed along with Derby install?
>>
>
> I don't think so. I suspect this example is based on a DB2 sample
> database (at least it looks like many of the examples in the DB2 manual
> use a similar database). Many other examples in the Derby manuals use
> toursdb, which you can find under java/demo/toursdb/toursdb after you
> have run ant all.
>
> --
> Knut Anders
>
>
>
Re: Buddy Testing of Using Clause
Posted by Lily Wei <li...@yahoo.com>.
Hi Jayaram:
On top of Derby reference menu, this is some join example with
using keyword for you to reference too. http://www.java2s.com/Tutorial/Oracle/0140__Table-Joins/SimplifyingJoinswiththeUSINGKeyword.htm
Lily
________________________________
From: Knut Anders Hatlen <Kn...@Sun.COM>
To: derby-dev@db.apache.org
Sent: Sun, May 2, 2010 5:33:15 AM
Subject: Re: Buddy Testing of Using Clause
On 05/ 1/10 01:23 PM, Jayaram Subramanian wrote:
> Hi,
>
> I am trying to test along the comments. I am taking the 10.5 as
> reference .. Is that correct?
The USING clause was not supported in 10.5, so you won't find anything
about it in the 10.5 manuals. You can find the latest development
version of the reference guide here:
http://db.apache.org/derby/docs/dev/ref/
> Also when i saw 10.5 reference guide for
> join i saw the following sql
>
> SELECT SAMP.EMP_ACT.*, LASTNAME
> FROM SAMP.EMP_ACT JOIN SAMP.EMPLOYEE
> ON EMP_ACT.EMPNO = EMPLOYEE.EMPNO
>
> Is there any samp schema which gets installed along with Derby install?
>
I don't think so. I suspect this example is based on a DB2 sample
database (at least it looks like many of the examples in the DB2 manual
use a similar database). Many other examples in the Derby manuals use
toursdb, which you can find under java/demo/toursdb/toursdb after you
have run ant all.
--
Knut Anders
Re: Buddy Testing of Using Clause
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
On 05/ 1/10 01:23 PM, Jayaram Subramanian wrote:
> Hi,
>
> I am trying to test along the comments. I am taking the 10.5 as
> reference .. Is that correct?
The USING clause was not supported in 10.5, so you won't find anything
about it in the 10.5 manuals. You can find the latest development
version of the reference guide here:
http://db.apache.org/derby/docs/dev/ref/
> Also when i saw 10.5 reference guide for
> join i saw the following sql
>
> SELECT SAMP.EMP_ACT.*, LASTNAME
> FROM SAMP.EMP_ACT JOIN SAMP.EMPLOYEE
> ON EMP_ACT.EMPNO = EMPLOYEE.EMPNO
>
> Is there any samp schema which gets installed along with Derby install?
>
I don't think so. I suspect this example is based on a DB2 sample
database (at least it looks like many of the examples in the DB2 manual
use a similar database). Many other examples in the Derby manuals use
toursdb, which you can find under java/demo/toursdb/toursdb after you
have run ant all.
--
Knut Anders