You are viewing a plain text version of this content. The canonical link for it is here.

Posted to commits@druid.apache.org by GitBox <gi...@apache.org> on 2022/11/21 22:04:53 UTC

[GitHub] [druid] techdocsmith opened a new pull request, #13404: Add CROSS JOIN as option for join datasource

techdocsmith opened a new pull request, #13404:
URL: https://github.com/apache/druid/pull/13404

   Adds CROSS JOIN to SQL syntax for join datasources.
   Didn't modify the native docs since EXPLAIN PLAN FOR seemed to translate CROSS JOIN to INNER in my tests.
   
   @gianm PTAL
   
   This PR has:
   
   - [x ] been self-reviewed.
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org

Re: [PR] Add CROSS JOIN as option for join datasource (druid)

Posted by "benkrug (via GitHub)" <gi...@apache.org>.

benkrug commented on PR #13404:
URL: https://github.com/apache/druid/pull/13404#issuecomment-1675532103

   I notice a number of syntax options that work, based on an error message I got.  The error message was 
   
   `Error: druidException
   
   Received an unexpected token [outer join] (line [33], column [18]), acceptable options: [<EOF>, "ORDER", "LIMIT", "OFFSET", "FETCH", "NATURAL", "JOIN", "INNER", "LEFT", "RIGHT", "FULL", "CROSS", ",", "OUTER" "APPLY", "(", "TABLESAMPLE", "WHERE", "GROUP", "HAVING", "WINDOW", "UNION", "INTERSECT", "EXCEPT", "MINUS"]`
   
   These seem to all be accepted for JOIN - `"NATURAL", "JOIN", "INNER", "LEFT", "RIGHT", "FULL", "CROSS", "OUTER"`.  I tried combinations of RIGHT/LEFT INNER/OUTER that worked.  Also, NATURAL, FULL, and CROSS work, but don't accept an "ON" condition (iirc).  


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org

[GitHub] [druid] paul-rogers commented on a diff in pull request #13404: Add CROSS JOIN as option for join datasource

Posted by "paul-rogers (via GitHub)" <gi...@apache.org>.

paul-rogers commented on code in PR #13404:
URL: https://github.com/apache/druid/pull/13404#discussion_r1091020943


##########
docs/querying/datasource.md:
##########
@@ -334,7 +336,7 @@ Native join datasources have the following properties. All are required.
 |`right`|Right-hand datasource. Must be of type `lookup`, `query`, or `inline`. Note that this is more rigid than what Druid SQL requires.|
 |`rightPrefix`|String prefix that will be applied to all columns from the right-hand datasource, to prevent them from colliding with columns from the left-hand datasource. Can be any string, so long as it is nonempty and is not be a prefix of the string `__time`. Any columns from the left-hand side that start with your `rightPrefix` will be shadowed. It is up to you to provide a prefix that will not shadow any important columns from the left side.|
 |`condition`|[Expression](../misc/math-expr.md) that must be an equality where one side is an expression of the left-hand side, and the other side is a simple column reference to the right-hand side. Note that this is more rigid than what Druid SQL requires: here, the right-hand reference must be a simple column reference; in SQL it can be an expression.|
-|`joinType`|`INNER` or `LEFT`.|
+|`joinType`|`INNER`, `LEFT`|
 

Review Comment:
   In Druid,`LEFT` is a more generic term for `OUTER`. `OUTER` meant "LEFT OUTER" in older versions of SQL. So, the term "RIGHT OUTER" had to be introduced. Given that, it seems modern syntax omits "OUTER" to give just `LEFT` and `RIGHT`. Reference: [left join](https://www.w3schools.com/sql/sql_join_left.asp), [right join](https://www.w3schools.com/sql/sql_join_right.asp).
   
   However, [this reference](https://mode.com/sql-tutorial/sql-outer-joins/) says, "FULL OUTER JOIN is also refered to as OUTER JOIN." which didn't used to be true on many databases.
   
   Checking the code (`Parser.jj`) finds that, in the Calcite dialect, `OUTER` is synonymous with `LEFT`.



##########
docs/querying/datasource.md:
##########
@@ -302,12 +302,14 @@ use join datasources.
 SQL joins take the form:
 
 ```
-<o1> [ INNER | LEFT [OUTER] ] JOIN <o2> ON <condition>
+<o1> [ [ INNER | LEFT [OUTER] JOIN ON ] | CROSS JOIN <o2> 

Review Comment:
   Given multiple conditions, I'd go back to the original, simpler format, then use the explanation to say which combinations are not allowed.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org