[
https://issues.apache.org/jira/browse/CALCITE-4915?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17452589#comment-17452589
]
Aleksey Plekhanov commented on CALCITE-4915:
--------------------------------------------
[~julianhyde],
{quote}NATURAL is basically syntactic sugar for USING - could your fix exploit
that?
{quote}
This fix exploits that, I've just added NATURAL JOIN processing to places where
USING is already processed.
{quote}It also affects the expansion of “*”. Did you check that?
{quote}
I've checked that. Actually, in our framework, we have some issues (since our
overridden {{addToSelectList}} method filters out some system columns, I will
file a ticket later, it's not related to this bug), but without system columns
filtering everything looks correct. There is already a test exists that check
the correctness of "star" expand for NATURAL JOIN:
{{SqlValidatorTest#testJoinUsing}}
{quote}It's frustrating that you log a bug and immediately submit a pull
request. It prevents discussion before implementation.
{quote}
I've found some bugs during testing of NATURAL JOINs in our framework, each bug
should be investigated first to be sure that the problem is in Calcite and not
in our code, after investigation I've filed a ticket and pretty straightforward
fix. Of course for complex tickets, it's better to discuss first, before the
implementation.
{quote}I don’t see why you need to add validator as a parameter.
{quote}
Because {{expandExprFromJoin}} is a static method, and {{usingNames}} it's a
non-static method (which also requires the {{{}validator{}}}, since it calls
{{{}getValidatedNodeType{}}}).
{quote}if you have a query with emp NATURAL JOIN dept and join in a third table
that also has a deptno column - using variously NATURAL, USING, and ON - it
seems that there is potential for bugs. Can you test those cases.
{quote}
Yes, I've already tested it today (after submitting a fix for this ticket) and
found some bugs. I'm still investigation this, but for now, I'm almost sure
that it's a bug in Calcite. {{SqlValidatorUtil#deriveNaturalJoinColumnList}}
can't correctly derive common columns for JOIN if the left hand of it is
another NATURAL JOIN or JOIN with USING. For example, queries like this:
{noformat}
SELECT * FROM (values (1, 1)) as t1(a, b)
NATURAL JOIN (values (1, 1)) as t2(a, c)
NATURAL JOIN (values (1, 1)) as t3(a, d)
{noformat}
Can't be planned correctly. But I think it should be fixed by a separate
ticket. What do you think? If you agree I will file a new ticket for this
problem.
Also, today I've found a bug with an unqualified common column in ORDER BY. I
have the test with ORDER BY in this fix, but it also uses the same column in
the select list. If the column is not in the select list - the query fails.
Should I fix this problem with a current ticket or should I file a new ticket?
> Query with unqualified common column and NATURAL JOIN fails
> -----------------------------------------------------------
>
> Key: CALCITE-4915
> URL: https://issues.apache.org/jira/browse/CALCITE-4915
> Project: Calcite
> Issue Type: Bug
> Reporter: Aleksey Plekhanov
> Assignee: Aleksey Plekhanov
> Priority: Major
> Labels: pull-request-available
>
> Query with unqualified common column and NATURAL JOIN:
> {noformat}
> SELECT deptno FROM emp NATURAL JOIN dept{noformat}
> Fails with "Column 'DEPTNO' is ambiguous" error.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
