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 "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2009/09/10 16:45:00 UTC
[jira] Created: (DERBY-4374) Document the CROSS JOIN operation in
the reference manual
Document the CROSS JOIN operation in the reference manual
---------------------------------------------------------
Key: DERBY-4374
URL: https://issues.apache.org/jira/browse/DERBY-4374
Project: Derby
Issue Type: Improvement
Components: Documentation
Affects Versions: 10.6.0.0
Reporter: Knut Anders Hatlen
CROSS JOIN was added in DERBY-4355. We should document it alongside the other join operations in the reference manual.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-4374) Document the CROSS JOIN operation in
the reference manual
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase reassigned DERBY-4374:
--------------------------------
Assignee: Kim Haase
> Document the CROSS JOIN operation in the reference manual
> ---------------------------------------------------------
>
> Key: DERBY-4374
> URL: https://issues.apache.org/jira/browse/DERBY-4374
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Kim Haase
>
> CROSS JOIN was added in DERBY-4355. We should document it alongside the other join operations in the reference manual.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4374) Document the CROSS JOIN operation in
the reference manual
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-4374:
-----------------------------
Issue & fix info: [Patch Available]
> Document the CROSS JOIN operation in the reference manual
> ---------------------------------------------------------
>
> Key: DERBY-4374
> URL: https://issues.apache.org/jira/browse/DERBY-4374
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Kim Haase
> Attachments: DERBY-4374.diff, DERBY-4374.stat, DERBY-4374.zip
>
>
> CROSS JOIN was added in DERBY-4355. We should document it alongside the other join operations in the reference manual.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4374) Document the CROSS JOIN operation in
the reference manual
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12756419#action_12756419 ]
Knut Anders Hatlen commented on DERBY-4374:
-------------------------------------------
We also need to add CROSS to the list of SQL reserved words in the reference manual.
> Document the CROSS JOIN operation in the reference manual
> ---------------------------------------------------------
>
> Key: DERBY-4374
> URL: https://issues.apache.org/jira/browse/DERBY-4374
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
>
> CROSS JOIN was added in DERBY-4355. We should document it alongside the other join operations in the reference manual.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4374) Document the CROSS JOIN operation in
the reference manual
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12753643#action_12753643 ]
Knut Anders Hatlen commented on DERBY-4374:
-------------------------------------------
The syntax description should say:
TableExpression CROSS JOIN { TableViewOrFunctionExpression | ( TableExpression ) }
> Document the CROSS JOIN operation in the reference manual
> ---------------------------------------------------------
>
> Key: DERBY-4374
> URL: https://issues.apache.org/jira/browse/DERBY-4374
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
>
> CROSS JOIN was added in DERBY-4355. We should document it alongside the other join operations in the reference manual.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4374) Document the CROSS JOIN operation in
the reference manual
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12761684#action_12761684 ]
Knut Anders Hatlen commented on DERBY-4374:
-------------------------------------------
The latest patch looks great! +1 to commit.
I used a debug build when I ran the queries, which hopefully explains why it was slower. I'll look more into it, and I'll also file a bug against the optimizer for its inability to optimize the inner/cross join variants.
> Document the CROSS JOIN operation in the reference manual
> ---------------------------------------------------------
>
> Key: DERBY-4374
> URL: https://issues.apache.org/jira/browse/DERBY-4374
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Kim Haase
> Attachments: DERBY-4374-2.diff, DERBY-4374-2.zip, DERBY-4374-3.diff, DERBY-4374-3.zip, DERBY-4374.diff, DERBY-4374.stat, DERBY-4374.zip
>
>
> CROSS JOIN was added in DERBY-4355. We should document it alongside the other join operations in the reference manual.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4374) Document the CROSS JOIN operation in
the reference manual
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12761498#action_12761498 ]
Knut Anders Hatlen commented on DERBY-4374:
-------------------------------------------
The CROSS JOIN operator can be replaced with an INNER JOIN where the join clause always evaluates to true, e.g. 1=1. It can also be replaced with a sub-query. So equivalent queries will be:
INNER JOIN:
SELECT * FROM CITIES LEFT OUTER JOIN
FLIGHTS INNER JOIN COUNTRIES ON 1=1
ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'
Sub-query:
SELECT * FROM CITIES LEFT OUTER JOIN
(SELECT * FROM FLIGHTS, COUNTRIES) S
ON CITIES.AIRPORT = S.ORIG_AIRPORT
WHERE S.COUNTRY_ISO_CODE = 'US'
(Curiously, the queries that use CROSS JOIN or INNER JOIN need more than three minutes to complete on the toursdb database, whereas the sub-query variant completes in less than ten seconds. Probably an optimizer issue, but at least the results are the same.)
> Document the CROSS JOIN operation in the reference manual
> ---------------------------------------------------------
>
> Key: DERBY-4374
> URL: https://issues.apache.org/jira/browse/DERBY-4374
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Kim Haase
> Attachments: DERBY-4374-2.diff, DERBY-4374-2.zip, DERBY-4374.diff, DERBY-4374.stat, DERBY-4374.zip
>
>
> CROSS JOIN was added in DERBY-4355. We should document it alongside the other join operations in the reference manual.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4374) Document the CROSS JOIN operation in
the reference manual
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-4374:
-----------------------------
Attachment: DERBY-4374-2.zip
DERBY-4374-2.diff
Thanks, Knut! I'm attaching a revised patch (DERBY-4374-2.diff, DERBY-4374-2.zip).
It seemed simpler to just revise the example using the format you suggested, since it is basically the same except for the parentheses. Is there a way to perform the same select without using a CROSS JOIN? If so, we could add that, since the other example pairs show that contrast.
> Document the CROSS JOIN operation in the reference manual
> ---------------------------------------------------------
>
> Key: DERBY-4374
> URL: https://issues.apache.org/jira/browse/DERBY-4374
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Kim Haase
> Attachments: DERBY-4374-2.diff, DERBY-4374-2.zip, DERBY-4374.diff, DERBY-4374.stat, DERBY-4374.zip
>
>
> CROSS JOIN was added in DERBY-4355. We should document it alongside the other join operations in the reference manual.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-4374) Document the CROSS JOIN operation in
the reference manual
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase closed DERBY-4374.
----------------------------
Changes have appeared in Latest Alpha Manuals, so closing.
> Document the CROSS JOIN operation in the reference manual
> ---------------------------------------------------------
>
> Key: DERBY-4374
> URL: https://issues.apache.org/jira/browse/DERBY-4374
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Kim Haase
> Fix For: 10.6.0.0
>
> Attachments: DERBY-4374-2.diff, DERBY-4374-2.zip, DERBY-4374-3.diff, DERBY-4374-3.zip, DERBY-4374.diff, DERBY-4374.stat, DERBY-4374.zip
>
>
> CROSS JOIN was added in DERBY-4355. We should document it alongside the other join operations in the reference manual.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4374) Document the CROSS JOIN operation in
the reference manual
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-4374:
-----------------------------
Attachment: DERBY-4374-3.zip
DERBY-4374-3.diff
Thanks again, Knut -- here is another patch (DERBY-4374-3.diff, DERBY-4374-3.zip) with these changes. Hope it's okay.
You're right that the inner join version is slower than the (SELECT * FROM FLIGHTS, COUNTRIES) S version (I am using 10.5.3 so I can't try the cross join myself) -- though it doesn't take 3 minutes by any means, more like 30 seconds vs. instantaneous. I wonder if something has happened since 10.5.3 to slow it down even more.
> Document the CROSS JOIN operation in the reference manual
> ---------------------------------------------------------
>
> Key: DERBY-4374
> URL: https://issues.apache.org/jira/browse/DERBY-4374
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Kim Haase
> Attachments: DERBY-4374-2.diff, DERBY-4374-2.zip, DERBY-4374-3.diff, DERBY-4374-3.zip, DERBY-4374.diff, DERBY-4374.stat, DERBY-4374.zip
>
>
> CROSS JOIN was added in DERBY-4355. We should document it alongside the other join operations in the reference manual.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4374) Document the CROSS JOIN operation in
the reference manual
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-4374:
-----------------------------
Attachment: DERBY-4374.zip
DERBY-4374.stat
DERBY-4374.diff
Attaching DERBY-4374.diff, DERBY-4374.stat, and DERBY-4374.zip, with a new CROSS JOIN topic and with updates to the "JOIN operations" and keywords topics.
I changed the examples to use one of the demo databases, but they seem to me to be rather bogus -- if you have any suggestions for improving them, I'd be grateful.
> Document the CROSS JOIN operation in the reference manual
> ---------------------------------------------------------
>
> Key: DERBY-4374
> URL: https://issues.apache.org/jira/browse/DERBY-4374
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Kim Haase
> Attachments: DERBY-4374.diff, DERBY-4374.stat, DERBY-4374.zip
>
>
> CROSS JOIN was added in DERBY-4355. We should document it alongside the other join operations in the reference manual.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4374) Document the CROSS JOIN operation in
the reference manual
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12753651#action_12753651 ]
Knut Anders Hatlen commented on DERBY-4374:
-------------------------------------------
The description of the operation could say something similar to this (inspired by the wording used in the other JOIN operators):
A CROSS JOIN is a JOIN operation that produces the Cartesian product of two tables. Unlike other JOIN operators, it does not let you specify a join clause. You may however specify a WHERE clause in the SELECT statement.
Examples that we may provide:
SELECT * FROM T1 CROSS JOIN T2
which is equivalent to
SELECT * FROM T1, T2
and
SELECT * FROM T1 CROSS JOIN T2 WHERE T1.X = T2.X
which is equivalent to
SELECT * FROM T1 INNER JOIN T2 ON T1.X = T2.X
And perhaps we should have a more complex example. For instance:
SELECT * FROM T1 LEFT OUTER JOIN T2 CROSS JOIN T3 ON T1.X = T2.X WHERE T3.Z > 50
We should probably change the examples so that they use the same table names as the other JOIN examples.
> Document the CROSS JOIN operation in the reference manual
> ---------------------------------------------------------
>
> Key: DERBY-4374
> URL: https://issues.apache.org/jira/browse/DERBY-4374
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
>
> CROSS JOIN was added in DERBY-4355. We should document it alongside the other join operations in the reference manual.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4374) Document the CROSS JOIN operation in
the reference manual
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12761183#action_12761183 ]
Knut Anders Hatlen commented on DERBY-4374:
-------------------------------------------
Thanks Kim! The docs look great.
I'm wondering if we should add a note about the ON clause to the more complex example. Users may be confused since we say that CROSS JOIN doesn't let you specify a join clause (aka ON/USING), whereas the example appears to be showing just that; a CROSS JOIN with an ON clause. What about adding another example below it and say something like
----
Note that the ON clause in the example above is associated with the LEFT OUTER JOIN operator, not with the CROSS JOIN operator, and it is therefore equivalent to the following statement:
SELECT * FROM CITIES LEFT OUTER JOIN
(FLIGHTS CROSS JOIN COUNTRIES)
ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'
----
The parentheses will hopefully make it clearer how the previous example could be correct even if it apparently had a CROSS JOIN + ON, and it'll additionally show that parens are allowed around the join operations.
> Document the CROSS JOIN operation in the reference manual
> ---------------------------------------------------------
>
> Key: DERBY-4374
> URL: https://issues.apache.org/jira/browse/DERBY-4374
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Kim Haase
> Attachments: DERBY-4374.diff, DERBY-4374.stat, DERBY-4374.zip
>
>
> CROSS JOIN was added in DERBY-4355. We should document it alongside the other join operations in the reference manual.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-4374) Document the CROSS JOIN operation in
the reference manual
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase resolved DERBY-4374.
------------------------------
Resolution: Fixed
Fix Version/s: 10.6.0.0
Issue & fix info: (was: [Patch Available])
Thanks, Knut, for all your help.
Committed patch DERBY-4374-3.diff to documentation trunk at revision 821180.
> Document the CROSS JOIN operation in the reference manual
> ---------------------------------------------------------
>
> Key: DERBY-4374
> URL: https://issues.apache.org/jira/browse/DERBY-4374
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Kim Haase
> Fix For: 10.6.0.0
>
> Attachments: DERBY-4374-2.diff, DERBY-4374-2.zip, DERBY-4374-3.diff, DERBY-4374-3.zip, DERBY-4374.diff, DERBY-4374.stat, DERBY-4374.zip
>
>
> CROSS JOIN was added in DERBY-4355. We should document it alongside the other join operations in the reference manual.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.