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: gentle reminder: buddy testers needed
Date Wed, 28 Apr 2010 10:49:41 GMT
Thanks for your comments Knut
I verified that the error did go off when i tried something like this

ResultSet rs=s.executeQuery("select t3.c from t1 join t2 using (a,c)
join t3 using (c)");

Also i was trying a subquery based scenario
ResultSet rs=s.executeQuery("Select * from t1 where a in (select a
from t2 join t1 using a)");

I tried looking up in 10.5 reference but couldnt find the "in"
clause...  Is it supported?

It gave the following error

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "a" at
line 1, column 61.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:91)
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:256)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2269)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
	at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:614)

With Regards
Jayaram

On Tue, Apr 27, 2010 at 9:36 AM, Knut Anders Hatlen <Knut.Hatlen@sun.com> wrote:
> Hi Jayaram,
>
> Thanks for testing the functionality so thoroughly! Please see my
> comments inline.
>
> On 04/27/10 14:14, Jayaram Subramanian wrote:
>> Thank Knut,
>>
>> But i observed one more thing...  When i did
>> ResultSet rs=s.executeQuery("select * from t1 join t2 using (a) join
>> t3 using (a)"); the query went through and was able to produce
>> results.
>
> In this case, the query is nested like this:
>
> SELECT * FROM (T1 JOIN T2 USING (A)) JOIN T3 USING (A)
>
> The columns returned by the first join operator, T1 JOIN T2 USING (A),
> are: A, T1.B, T1.C, T2.B, T2.C (note: the column in the USING clause is
> only returned once, even though it exists both in T1 and T2)
>
> This result is OK to join with T3 using column A, since both operands
> have exactly one column named A.
>
>> But when i did  s.executeQuery("select * from t1 join t2 using (a)
>> join t3 using (c)");  the query errored out
>
> This query is nested the same way as the query above, so the columns
> returned by the first join operator are still A, T1.B, T1.C, T2.B, T2.C.
> Now, since the second join operator specifies column C in the USING
> clause, it fails because the right operand has two columns named C (T1.C
> and T2.C), so the join operator doesn't know which one to use.
>
> --
> Knut Anders
>

Mime
View raw message