[
https://issues.apache.org/jira/browse/DERBY-4380?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12759023#action_12759023
]
Mamta A. Satoor commented on DERBY-4380:
----------------------------------------
I looked at SQL 2003 Foundation spec and through various links, it shows that
ON clauses can have subqueries.
Section 7.7 <joined table> (page 312)
which shows
<joined table> ::=
<cross join>
| <qualified join>
| <natural join>
On the same page (312), qualified join is defined as
<qualified join> ::=
<table reference> [ <join type> ] JOIN <table reference> <join specification>
Clicking on <join specification> takes to following definition on the same page
<join specification> ::=
<join condition>
| <named columns join>
And <join condition> talks about the ON clause and <search condition> as follows
<join condition> ::= ON <search condition>
Clicking on <search condition> takes to page 416. Following the bnf of <search
condition> finally leads to <predicate> on page 371 and one of the
possibilities for <predicate> is <quantified comparison predicate> and page 397
for <quantified comparison predicate> shows that we can have subquery here with
SOME/ANY.
So, long story short, the change suggested by this jira entry is SQL compatible.
> SOME not allowed in ON clause
> -----------------------------
>
> Key: DERBY-4380
> URL: https://issues.apache.org/jira/browse/DERBY-4380
> Project: Derby
> Issue Type: Bug
> Components: Store
> Reporter: Bernt M. Johnsen
> Attachments: on_subquery.diff
>
>
> 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.
