[jira] [Commented] (CALCITE-3387) Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is ambiguous" error

2019-12-20 Thread Chunwei Lei (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-3387?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17000866#comment-17000866
 ] 

Chunwei Lei commented on CALCITE-3387:
--

Comments were addressed.

> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error
> --
>
> Key: CALCITE-3387
> URL: https://issues.apache.org/jira/browse/CALCITE-3387
> Project: Calcite
>  Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Chunwei Lei
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 1h 40m
>  Remaining Estimate: 0h
>
> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error. Here is the query:
> {code}
> select deptno, count(*)
> from emp
> join dept using (deptno)
> group by deptno;
> {code}
> Because of USING, the two deptno fields should be merged into one, and 
> therefore {{deptno}} is not ambiguous. That query works in Oracle. Also note 
> that
> {code}select deptno
> from emp
> join dept using (deptno)
> {code}
> works correctly in Calcite, and has since CALCITE-2227 was fixed.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3387) Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is ambiguous" error

2019-12-19 Thread Jin Xing (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-3387?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17000630#comment-17000630
 ] 

Jin Xing commented on CALCITE-3387:
---

Thanks for ping me, Chunwei ~

I just added minor comments.

> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error
> --
>
> Key: CALCITE-3387
> URL: https://issues.apache.org/jira/browse/CALCITE-3387
> Project: Calcite
>  Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Chunwei Lei
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 1.5h
>  Remaining Estimate: 0h
>
> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error. Here is the query:
> {code}
> select deptno, count(*)
> from emp
> join dept using (deptno)
> group by deptno;
> {code}
> Because of USING, the two deptno fields should be merged into one, and 
> therefore {{deptno}} is not ambiguous. That query works in Oracle. Also note 
> that
> {code}select deptno
> from emp
> join dept using (deptno)
> {code}
> works correctly in Calcite, and has since CALCITE-2227 was fixed.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3387) Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is ambiguous" error

2019-12-19 Thread Chunwei Lei (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-3387?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16999836#comment-16999836
 ] 

Chunwei Lei commented on CALCITE-3387:
--

Thanks for your review, [~jinxing6...@126.com]. PR was updated.

> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error
> --
>
> Key: CALCITE-3387
> URL: https://issues.apache.org/jira/browse/CALCITE-3387
> Project: Calcite
>  Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Chunwei Lei
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 1.5h
>  Remaining Estimate: 0h
>
> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error. Here is the query:
> {code}
> select deptno, count(*)
> from emp
> join dept using (deptno)
> group by deptno;
> {code}
> Because of USING, the two deptno fields should be merged into one, and 
> therefore {{deptno}} is not ambiguous. That query works in Oracle. Also note 
> that
> {code}select deptno
> from emp
> join dept using (deptno)
> {code}
> works correctly in Calcite, and has since CALCITE-2227 was fixed.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3387) Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is ambiguous" error

2019-12-17 Thread Jin Xing (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-3387?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16997997#comment-16997997
 ] 

Jin Xing commented on CALCITE-3387:
---

Given tables like below
{code:java}
emp:
  empno
  deptno

dept:
  deptno
  buildingno

location:
  buildingno
  addr
  {code}
If user write sql (multi-join) like
{code:java}
select empno, deptno, buldingno, addr
from emp
 join dept using (deptno) 
 join location using (buildingno)  {code}
What is correct behavior for Calcite ?

With PR-1655, it throws Exception for ambiguous column. Do we need to support 
such case ?

 

> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error
> --
>
> Key: CALCITE-3387
> URL: https://issues.apache.org/jira/browse/CALCITE-3387
> Project: Calcite
>  Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Chunwei Lei
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 40m
>  Remaining Estimate: 0h
>
> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error. Here is the query:
> {code}
> select deptno, count(*)
> from emp
> join dept using (deptno)
> group by deptno;
> {code}
> Because of USING, the two deptno fields should be merged into one, and 
> therefore {{deptno}} is not ambiguous. That query works in Oracle. Also note 
> that
> {code}select deptno
> from emp
> join dept using (deptno)
> {code}
> works correctly in Calcite, and has since CALCITE-2227 was fixed.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3387) Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is ambiguous" error

2019-12-13 Thread Chunwei Lei (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-3387?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16996108#comment-16996108
 ] 

Chunwei Lei commented on CALCITE-3387:
--

Thanks for your review, [~julianhyde].

On one hand, the behavior is kind of the same with {{expandStar}} in which 
common columns will be rewrited to {{COALESCE(...)}}[1]. On the other hand, I 
think it makes sense to rewrite it to COALESCE since the columns will be 
formatted to fully-qualify columns during validation. 

 

[1] 
[https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L6475]
 {{}}

 

> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error
> --
>
> Key: CALCITE-3387
> URL: https://issues.apache.org/jira/browse/CALCITE-3387
> Project: Calcite
>  Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Chunwei Lei
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 10m
>  Remaining Estimate: 0h
>
> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error. Here is the query:
> {code}
> select deptno, count(*)
> from emp
> join dept using (deptno)
> group by deptno;
> {code}
> Because of USING, the two deptno fields should be merged into one, and 
> therefore {{deptno}} is not ambiguous. That query works in Oracle. Also note 
> that
> {code}select deptno
> from emp
> join dept using (deptno)
> {code}
> works correctly in Calcite, and has since CALCITE-2227 was fixed.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3387) Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is ambiguous" error

2019-12-13 Thread Julian Hyde (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-3387?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16996069#comment-16996069
 ] 

Julian Hyde commented on CALCITE-3387:
--

The fix looks OK. But it's better to avoid modifying the SqlNode tree as we 
validate it. Would it be possible to keep the SqlNode tree the same and perform 
the necessary rewrite during Sql-to-rel conversion?

> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error
> --
>
> Key: CALCITE-3387
> URL: https://issues.apache.org/jira/browse/CALCITE-3387
> Project: Calcite
>  Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Chunwei Lei
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 10m
>  Remaining Estimate: 0h
>
> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error. Here is the query:
> {code}
> select deptno, count(*)
> from emp
> join dept using (deptno)
> group by deptno;
> {code}
> Because of USING, the two deptno fields should be merged into one, and 
> therefore {{deptno}} is not ambiguous. That query works in Oracle. Also note 
> that
> {code}select deptno
> from emp
> join dept using (deptno)
> {code}
> works correctly in Calcite, and has since CALCITE-2227 was fixed.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3387) Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is ambiguous" error

2019-12-13 Thread Chunwei Lei (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-3387?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16995646#comment-16995646
 ] 

Chunwei Lei commented on CALCITE-3387:
--

I opened a pull request: [https://github.com/apache/calcite/pull/1655].

> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error
> --
>
> Key: CALCITE-3387
> URL: https://issues.apache.org/jira/browse/CALCITE-3387
> Project: Calcite
>  Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Chunwei Lei
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 10m
>  Remaining Estimate: 0h
>
> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error. Here is the query:
> {code}
> select deptno, count(*)
> from emp
> join dept using (deptno)
> group by deptno;
> {code}
> Because of USING, the two deptno fields should be merged into one, and 
> therefore {{deptno}} is not ambiguous. That query works in Oracle. Also note 
> that
> {code}select deptno
> from emp
> join dept using (deptno)
> {code}
> works correctly in Calcite, and has since CALCITE-2227 was fixed.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3387) Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is ambiguous" error

2019-10-04 Thread Julian Hyde (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-3387?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16944926#comment-16944926
 ] 

Julian Hyde commented on CALCITE-3387:
--

Here is a test case:
{noformat}
diff --git a/core/src/test/resources/sql/join.iq 
b/core/src/test/resources/sql/join.iq
index 18fc8f5c3..bcbe54cf9 100644
--- a/core/src/test/resources/sql/join.iq
+++ b/core/src/test/resources/sql/join.iq
@@ -172,6 +172,38 @@ group by cube(emp.deptno, dept.deptno);
 
 !ok
 
+# [CALCITE-3387] Query with GROUP BY and JOIN ... USING wrongly fails with
+# "Column 'DEPTNO' is ambiguous" error
+select deptno, count(*)
+from "scott".emp
+join "scott".dept using (deptno)
+group by deptno;
+!ok
+
+# Similar, with GROUPING SETS and GROUPING
+select deptno, grouping(deptno), grouping(deptno, job), count(*)
+from "scott".emp
+join "scott".dept using (deptno)
+group by grouping sets ((deptno), (deptno, job));
+!ok
+
+# For this query, Oracle gives
+#   ORA-25154: column part of USING clause cannot have qualifier
+# and Calcite should too.
+select dept.deptno, count(*)
+from "scott".emp
+join "scott".dept using (deptno);
+column part of USING clause cannot have qualifier
+!error
+
+# Ditto
+select count(*)
+from "scott".emp
+join "scott".dept using (deptno)
+group by dept.deptno;
+column part of USING clause cannot have qualifier
+!error
+
 # [CALCITE-688] splitCondition does not behave correctly
 # when one side of the condition references columns from
 # different inputs
{noformat}

> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error
> --
>
> Key: CALCITE-3387
> URL: https://issues.apache.org/jira/browse/CALCITE-3387
> Project: Calcite
>  Issue Type: Bug
>Reporter: Julian Hyde
>Priority: Major
>
> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is 
> ambiguous" error. Here is the query:
> {code}
> select deptno, count(*)
> from emp
> join dept using (deptno)
> group by deptno;
> {code}
> Because of USING, the two deptno fields should be merged into one, and 
> therefore {{deptno}} is not ambiguous. That query works in Oracle. Also note 
> that
> {code}select deptno
> from emp
> join dept using (deptno)
> {code}
> works correctly in Calcite, and has since CALCITE-2227 was fixed.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)