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] Commented: (DERBY-4355) Implement CROSS JOIN
Date Tue, 08 Sep 2009 20:49:57 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4355?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12752736#action_12752736

Knut Anders Hatlen commented on DERBY-4355:

Another problem with the current patch:

In the standard, the syntax for cross (and natural) join is defined with a <table reference>
on the left side of the join operator and a <table factor> on the right side. This asymmetry
is to get the correct join order without parentheses. The other joins are defined with a <table
reference> on both sides (their join order will be unambiguous thanks to the ON/USING clauses).
Since the current patch wired CROSS into the syntax rule for the other joins, it has <table
reference> on both sides too.

This leads to the following query

  select * from t1 cross join t2 right join t3 on x2=x3

being parsed as if it said

  select * from t1 cross join (t2 right join t3 on x2=x3)

whereas it should have been parsed as

  select * from (t1 cross join t2) right join t3 on x2=x3

This makes the query return wrong results. Given the tables defined below:

create table t1(x1 int);
create table t2(x2 int);
create table t3(x3 int);

insert into t1 values (1);
insert into t2 values (2);
insert into t3 values (3);

We see that the two supposedly equivalent queries return different results with the patch:

ij> select * from t1 cross join t2 right join t3 on x2=x3;
X1         |X2         |X3         
1          |NULL       |3          

1 row selected
ij> select * from (t1 cross join t2) right join t3 on x2=x3;
X1         |X2         |X3         
NULL       |NULL       |3          

1 row selected

I'll add this as a test case in the final patch.

> Implement CROSS JOIN
> --------------------
>                 Key: DERBY-4355
>                 URL: https://issues.apache.org/jira/browse/DERBY-4355
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>         Environment: any
>            Reporter: Bernt M. Johnsen
>            Assignee: Knut Anders Hatlen
>         Attachments: cross.diff, cross_v2.diff
> Implement the CROSS JOIN syntax
> SELECT * from t1 CROSS JOIN t2;
> as an alternative syntax to
> SELECT * FROM t1, t2; 
> This should be pretty straight forward and ease the migration of SQL code to Derby.

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

View raw message