db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4380) Subqueries not allowed in ON clause
Date Tue, 17 Nov 2009 16:55:39 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4380?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Knut Anders Hatlen updated DERBY-4380:
--------------------------------------

    Attachment: on_subquery_v2.diff
                on_subquery_v2.stat

Here's an updated patch which removes the double binding of the ON
clause which caused NullPointerExceptions in lang/lojreorder.sql. The
removal of the double binding changed the behaviour of some more
queries that used to fail before. As far as I know, the patch only
affects queries that used to fail. Here's the full list of behavioural
changes:

-------------------------------

1) JOIN queries with a subquery in the ON clause used to fail with the
following message:
> ERROR 42972: An ON clause associated with a JOIN operator is not valid.
Now, those queries will be accepted and return a result.

2) ON clauses with an unqualified column reference whose name matches
a column in some other table in the FROM list used to fail. For
instance:

ij> create table t(x int);
0 rows inserted/updated/deleted
ij> create table tt(y int);
0 rows inserted/updated/deleted
ij> select * from t t1, t t2 join tt on x=y;
ERROR 42X03: Column name 'X' is in more than one table in the FROM list.

Since T1 is not in the scope of the ON clause, X must be T2.X and
there is no ambiguity. With the patch, the query does not fail.

3) If an ON clause referenced a column not in one of the tables that
are being joined, it used to fail with this error message:

ij> select * from t t1, t t2 join t t3 on t1.x=t2.x;
ERROR 42972: An ON clause associated with a JOIN operator is not valid.

With the patch, the error message is changed into:

ERROR 42X04: Column 'T1.X' is either not in any table in the FROM list
or appears within a join specification and is outside the scope of the
join specification or appears in a HAVING clause and is not in the
GROUP BY list. If this is a CREATE or ALTER TABLE statement then
'T1.X' is not a column in the target table.

-------------------------------

Both (1) and (2) make our implementation more liberal, and shouldn't
cause backward-compatibility issues. I've checked all the queries in
our regression tests that are affected by (1) and (2), and all of them
now give the same results as PostgreSQL and MySQL. As far as I can
tell, accepting these queries is in compliance with the
standard. (Note that we're still more restrictive than PostgreSQL and
MySQL, since we don't accept correlated columns from outer queries in
the ON clause, whereas they do.)

(3) changes the SQLState and the message of a certain failure, which
could introduce backwards-compatibility issues. However, I don't think
it's very likely that many applications depend on this particular
error code, since this error is something you'd expect to see during
development, before you've got your SQL statements right, and not
after the application is in production. So I'm leaning towards just
adding a release note for the changed SQLState, and not trying to
change the code to use the same SQLState as before.

I also think the new message is somewhat more useful, since it both
mentions the name of the problematic column, and that it might be
outside the scope of the join specification. The old message just said
that there was something wrong with one of the ON clauses in the
statement.



Here are the details from the patch:

Engine code:

* JoinNode.java

- Removed the code that raised an exception if the ON clause contained
  a subquery

- Removed the first call to joinClause.bindExpression() since its only
  purpose was to make the statements in (2) above fail, and the double
  binding made the query tree inconsistent when the ON clause
  contained subqueries

* messages.xml

- Removed now unused message

* SQLState.java

- Removed now unused message

Test code:

* JoinTest.java:

- Added a test case for ON with subqueries

* lojreorder.sql / lojreorder.out , outerjoin.sql / outerjoin.out ,
  innerjoin.sql / innerjoin.out

- Updated comments and canons for queries that were expected to fail
  before

- Updated canon for queries whose error message changed

* dml158.out, dml160.out

- Updated canon for queries whose error message changed

> Subqueries not allowed in ON clause
> -----------------------------------
>
>                 Key: DERBY-4380
>                 URL: https://issues.apache.org/jira/browse/DERBY-4380
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Bernt M. Johnsen
>            Assignee: Knut Anders Hatlen
>         Attachments: on_subquery.diff, on_subquery_v2.diff, on_subquery_v2.stat
>
>
> SOME is not allowed in ON-clause:
> ij> create table t1 (i integer);
> 0 rows inserted/updated/deleted
> ij> create table t2 (i integer);
> 0 rows inserted/updated/deleted
> ij> create table t3 (i integer);
> 0 rows inserted/updated/deleted
> ij> insert into t1 values (1);
> 1 row inserted/updated/deleted
> ij> insert into t2 values (2);
> 1 row inserted/updated/deleted
> ij> insert into t3 values 2,3,4;
> 3 rows inserted/updated/deleted
> ij> select * from t1 where t1.i = some (select i from t3);
> I          
> -----------
> 0 rows selected
> ij> select * from t1 inner join t2 on  t1.i = some (select i from t3);
> ERROR 42972: An ON clause associated with a JOIN operator is not valid.
> ij> 

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message