[jira] [Comment Edited] (CALCITE-3589) SqlValidatorException when conformation is set to LENIENT
[
https://issues.apache.org/jira/browse/CALCITE-3589?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17180975#comment-17180975
]
bingfeng.guo edited comment on CALCITE-3589 at 8/20/20, 6:07 AM:
-
When I run the following two SQLs on PG, I found that PG will not throw
Exception, so I think that PG does not strictly stipulate whether it is group
by alias or group by underlying column . I think it is the priority group by
underlying column, when the underlying column and alias exist at the same time,
group by underlying column.
{code:java}
> select empno as d from emp group by d
> select empno as d from emp group by empno
{code}
was (Author: bingfeng):
When I run the following two SQLs on PG, I found that PG will not throw
Exception, so I think that PG does not strictly stipulate whether it is group
by alias or group by underlying column . I think it is the priority group
byunderlying column, if there is no underlying column, then group by alias.
``` sql
{{select empno as d from emp group by d}}
{{select empno as d from emp group by empno}}
```
> SqlValidatorException when conformation is set to LENIENT
> -
>
> Key: CALCITE-3589
> URL: https://issues.apache.org/jira/browse/CALCITE-3589
> Project: Calcite
> Issue Type: Bug
>Reporter: bingfeng.guo
>Assignee: bingfeng.guo
>Priority: Critical
> Labels: pull-request-available
> Attachments: 屏幕快照 2019-12-12 下午5.56.24.png
>
> Time Spent: 2h 20m
> Remaining Estimate: 0h
>
> error occurs conditions (all three conditions must be met):
> * conformance = LENIENT
> * The alias of the expression in select is the same as the column name of
> the column used in the expression
> * group by or having use the above expression
> {color:#172b4d}a simple example is as follows：{color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00}FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00}GROUP BY SELLER_ID + 1{color}
> {color:#172b4d}will change to {color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00} FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00} GROUP BY SELLER_ID + 1 + 1{color}
> {color:#172b4d}after
> org.apache.calcite.sql.validate.SqlValidatorImpl#expandGroupByOrHavingExpr{color}
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3589) SqlValidatorException when conformation is set to LENIENT
[
https://issues.apache.org/jira/browse/CALCITE-3589?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17180975#comment-17180975
]
bingfeng.guo edited comment on CALCITE-3589 at 8/20/20, 6:02 AM:
-
When I run the following two SQLs on PG, I found that PG will not throw
Exception, so I think that PG does not strictly stipulate whether it is group
by alias or group by underlying column . I think it is the priority group
byunderlying column, if there is no underlying column, then group by alias.
``` sql
{{select empno as d from emp group by d}}
{{select empno as d from emp group by empno}}
```
was (Author: bingfeng):
When I run the following two SQLs on PG, I found that PG will not throw
Exception, so I think that PG does not strictly stipulate whether it is group
by alias or group by underlying column . I think it is the priority group
byunderlying column, if there is no underlying column, then group by alias.
``` sql
{{select empno as d from emp group by d}}
{{select empno as d from emp group by empno}}
{{```}}
> SqlValidatorException when conformation is set to LENIENT
> -
>
> Key: CALCITE-3589
> URL: https://issues.apache.org/jira/browse/CALCITE-3589
> Project: Calcite
> Issue Type: Bug
>Reporter: bingfeng.guo
>Assignee: bingfeng.guo
>Priority: Critical
> Labels: pull-request-available
> Attachments: 屏幕快照 2019-12-12 下午5.56.24.png
>
> Time Spent: 2h 20m
> Remaining Estimate: 0h
>
> error occurs conditions (all three conditions must be met):
> * conformance = LENIENT
> * The alias of the expression in select is the same as the column name of
> the column used in the expression
> * group by or having use the above expression
> {color:#172b4d}a simple example is as follows：{color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00}FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00}GROUP BY SELLER_ID + 1{color}
> {color:#172b4d}will change to {color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00} FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00} GROUP BY SELLER_ID + 1 + 1{color}
> {color:#172b4d}after
> org.apache.calcite.sql.validate.SqlValidatorImpl#expandGroupByOrHavingExpr{color}
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3589) SqlValidatorException when conformation is set to LENIENT
[
https://issues.apache.org/jira/browse/CALCITE-3589?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16997842#comment-16997842
]
bingfeng.guo edited comment on CALCITE-3589 at 12/17/19 12:26 PM:
--
@[~julianhyde] Thanks for your review，you means the following situations?
I try the case you say in postgresql，The result of postgresql is the same as
what you say
{code:java}
select tablex.A+tabley.A as A
from tablex,tabley
group by A
ERROR: column reference "a" is ambiguou
{code}
was (Author: bingfeng):
@[~julianhyde] Thanks for your review，Are you considering the following
situations?
I try the case you say in postgresql，The result of postgresql is the same as
what you say
{code:java}
select tablex.A+tabley.A as A
from tablex,tabley
group by A
ERROR: column reference "a" is ambiguou
{code}
> SqlValidatorException when conformation is set to LENIENT
> -
>
> Key: CALCITE-3589
> URL: https://issues.apache.org/jira/browse/CALCITE-3589
> Project: Calcite
> Issue Type: Bug
>Reporter: bingfeng.guo
>Assignee: bingfeng.guo
>Priority: Critical
> Labels: pull-request-available
> Attachments: 屏幕快照 2019-12-12 下午5.56.24.png
>
> Time Spent: 1h
> Remaining Estimate: 0h
>
> error occurs conditions (all three conditions must be met):
> * conformance = LENIENT
> * The alias of the expression in select is the same as the column name of
> the column used in the expression
> * group by or having use the above expression
> {color:#172b4d}a simple example is as follows：{color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00}FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00}GROUP BY SELLER_ID + 1{color}
> {color:#172b4d}will change to {color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00} FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00} GROUP BY SELLER_ID + 1 + 1{color}
> {color:#172b4d}after
> org.apache.calcite.sql.validate.SqlValidatorImpl#expandGroupByOrHavingExpr{color}
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3589) SqlValidatorException when conformation is set to LENIENT
[
https://issues.apache.org/jira/browse/CALCITE-3589?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16997842#comment-16997842
]
bingfeng.guo edited comment on CALCITE-3589 at 12/17/19 9:13 AM:
-
@[~julianhyde] Thanks for your review，Are you considering the following
situations?
I try the case you say in postgresql，The result of postgresql is the same as
what you say
{code:java}
select tablex.A+tabley.A as A
from tablex,tabley
group by A
ERROR: column reference "a" is ambiguou
{code}
was (Author: bingfeng):
@[~julianhyde] Thanks for your review，Are you considering the following
situations?
1.
{code:java}
select tableA.C+1 as A_C,tableB.C+1 as B_C
from tableA,tableB
group by A_C,B_C
{code}
A_C will still replace to tableA.C+1，like this
{code:java}
select tableA.C as A_C,tableB.C as B_C
from tableA,tableB
group by tableA.C,tableB.C
{code}
because only when tableA.C same with A_C in"tableA.C+1 as A_C" will not replace
2.
{code:java}
select tableA.C+1 as A_C,tableB.C+1 as B_C
from tableA,tableB
group by tableA.C+1,tableB.C+1
{code}
it need not replace.
3.
{code:java}
select tableA.C+tableB.C as C
from tableA,tableB
group by C
{code}
C will still replace to tableA.C+tableB.C, like this
{code:java}
select tableA.C+tableB.C as C
from tableA,tableB
group by tableA.C+tableB.C
{code}
> SqlValidatorException when conformation is set to LENIENT
> -
>
> Key: CALCITE-3589
> URL: https://issues.apache.org/jira/browse/CALCITE-3589
> Project: Calcite
> Issue Type: Bug
>Reporter: bingfeng.guo
>Assignee: bingfeng.guo
>Priority: Critical
> Labels: pull-request-available
> Attachments: 屏幕快照 2019-12-12 下午5.56.24.png
>
> Time Spent: 50m
> Remaining Estimate: 0h
>
> error occurs conditions (all three conditions must be met):
> * conformance = LENIENT
> * The alias of the expression in select is the same as the column name of
> the column used in the expression
> * group by or having use the above expression
> {color:#172b4d}a simple example is as follows：{color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00}FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00}GROUP BY SELLER_ID + 1{color}
> {color:#172b4d}will change to {color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00} FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00} GROUP BY SELLER_ID + 1 + 1{color}
> {color:#172b4d}after
> org.apache.calcite.sql.validate.SqlValidatorImpl#expandGroupByOrHavingExpr{color}
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3589) SqlValidatorException when conformation is set to LENIENT
[
https://issues.apache.org/jira/browse/CALCITE-3589?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16997842#comment-16997842
]
bingfeng.guo edited comment on CALCITE-3589 at 12/17/19 3:43 AM:
-
@[~julianhyde] Thanks for your review，Are you considering the following
situations?
1.
{code:java}
select tableA.C+1 as A_C,tableB.C+1 as B_C
from tableA,tableB
group by A_C,B_C
{code}
A_C will still replace to tableA.C+1，like this
{code:java}
select tableA.C as A_C,tableB.C as B_C
from tableA,tableB
group by tableA.C,tableB.C
{code}
because only when tableA.C same with A_C in"tableA.C+1 as A_C" will not replace
2.
{code:java}
select tableA.C+1 as A_C,tableB.C+1 as B_C
from tableA,tableB
group by tableA.C+1,tableB.C+1
{code}
it need not replace.
3.
{code:java}
select tableA.C+tableB.C as C
from tableA,tableB
group by C
{code}
C will still replace to tableA.C+tableB.C, like this
{code:java}
select tableA.C+tableB.C as C
from tableA,tableB
group by tableA.C+tableB.C
{code}
was (Author: bingfeng):
@[~julianhyde] Thanks for your review，Are you considering the following
situations?
1.
{code:java}
select tableA.C+1 as A_C,tableB.C+1 as B_C
from tableA,tableB
group by A_C,B_C
{code}
A_C will still replace to tableA.C+1，like this
{code:java}
select tableA.C as A_C,tableB.C as B_C
from tableA,tableB
group by tableA.C,tableB.C
{code}
because only when tableA.C same with A_C in"tableA.C+1 as A_C" will not replace
2.
{code:java}
select tableA.C+1 as A_C,tableB.C+1 as B_C
from tableA,tableB
group by tableA.C+1,tableB.C+1
{code}
it need not replace.
3.
{code:java}
select tableA.C+tableB.C as C
from tableA,tableB
group by C
{code}
C will still replace to tableA.C+tableB.C, like this
{code:java}
select tableA.C+tableB.C as C
from tableA,tableB
group by tableA.C+tableB.C
{code}
> SqlValidatorException when conformation is set to LENIENT
> -
>
> Key: CALCITE-3589
> URL: https://issues.apache.org/jira/browse/CALCITE-3589
> Project: Calcite
> Issue Type: Bug
>Reporter: bingfeng.guo
>Assignee: bingfeng.guo
>Priority: Critical
> Labels: pull-request-available
> Attachments: 屏幕快照 2019-12-12 下午5.56.24.png
>
> Time Spent: 40m
> Remaining Estimate: 0h
>
> error occurs conditions (all three conditions must be met):
> * conformance = LENIENT
> * The alias of the expression in select is the same as the column name of
> the column used in the expression
> * group by or having use the above expression
> {color:#172b4d}a simple example is as follows：{color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00}FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00}GROUP BY SELLER_ID + 1{color}
> {color:#172b4d}will change to {color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00} FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00} GROUP BY SELLER_ID + 1 + 1{color}
> {color:#172b4d}after
> org.apache.calcite.sql.validate.SqlValidatorImpl#expandGroupByOrHavingExpr{color}
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3589) SqlValidatorException when conformation is set to LENIENT
[
https://issues.apache.org/jira/browse/CALCITE-3589?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16994476#comment-16994476
]
bingfeng.guo edited comment on CALCITE-3589 at 12/12/19 10:34 AM:
--
In addition, when conformance = LENIENT，there is a similar problem。
The following sql will throw "column is ambiguous" error，Because SELLER_ID in
group by does not know use which alias
{color:#ff8b00}SELECT SELLER_ID, SELLER_ID{color}
{color:#ff8b00}FROM TEST_KYLIN_FACT{color}
{color:#ff8b00}GROUP BY SELLER_ID{color}
{color:#172b4d}But this error does not occur when conformance = DEFAULT,
Because when conformance = DEFAULT, aliases cannot be used on group by.
Therefore, the SELLER_ID in group by will not look for the replacement of alias
as SELLER_ID, but directly use SELLER_ID, thus bypassing this problem.{color}
was (Author: bingfeng):
In addition, when conformance = LENIENT，there is a similar problem。
The following sql will throw "column is ambiguous" error，Because SELLER_ID in
group by does not know use which alias
{color:#ff8b00}SELECT SELLER_ID, SELLER_ID{color}
{color:#ff8b00}FROM TEST_KYLIN_FACT{color}
{color:#ff8b00}GROUP BY SELLER_ID{color}
{color:#172b4d}But this error does not occur when conformance = DEFAULT,
Because when conformance = DEFAULT, aliases cannot be used on group by.
Therefore, the PRT_DT in group by will not look for the replacement of alias as
PRT_DT, but directly use PRT_DT, thus bypassing this problem.{color}
> SqlValidatorException when conformation is set to LENIENT
> -
>
> Key: CALCITE-3589
> URL: https://issues.apache.org/jira/browse/CALCITE-3589
> Project: Calcite
> Issue Type: Bug
>Reporter: bingfeng.guo
>Priority: Critical
> Attachments: 屏幕快照 2019-12-12 下午5.56.24.png
>
>
> error occurs conditions (all three conditions must be met):
> * conformance = LENIENT
> * The alias of the expression in select is the same as the column name of
> the column used in the expression
> * group by or having use the above expression
> {color:#172b4d}a simple example is as follows：{color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00}FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00}GROUP BY SELLER_ID + 1{color}
> {color:#172b4d}will change to {color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00} FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00} GROUP BY SELLER_ID + 1 + 1{color}
> {color:#172b4d}after
> org.apache.calcite.sql.validate.SqlValidatorImpl#expandGroupByOrHavingExpr{color}
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3589) SqlValidatorException when conformation is set to LENIENT
[
https://issues.apache.org/jira/browse/CALCITE-3589?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16994476#comment-16994476
]
bingfeng.guo edited comment on CALCITE-3589 at 12/12/19 10:24 AM:
--
In addition, when conformance = LENIENT，there is a similar problem。
The following sql will throw "column is ambiguous" error，Because SELLER_ID in
group by does not know use which alias
{color:#ff8b00}SELECT SELLER_ID, SELLER_ID{color}
{color:#ff8b00}FROM TEST_KYLIN_FACT{color}
{color:#ff8b00}GROUP BY SELLER_ID{color}
{color:#172b4d}But this error does not occur when conformance = DEFAULT,
Because when conformance = DEFAULT, aliases cannot be used on group by.
Therefore, the PRT_DT in group by will not look for the replacement of alias as
PRT_DT, but directly use PRT_DT, thus bypassing this problem.{color}
was (Author: bingfeng):
In addition, when conformance = LENIENT，there is a similar problem。
The following sql will throw "column is ambiguous" error，Because SELLER_ID in
group by does not know which alias
{color:#ff8b00}SELECT SELLER_ID, SELLER_ID{color}
{color:#ff8b00}FROM TEST_KYLIN_FACT{color}
{color:#ff8b00}GROUP BY SELLER_ID{color}
{color:#172b4d}But this error does not occur when conformance = DEFAULT,
Because when conformance = DEFAULT, aliases cannot be used on group by.
Therefore, the PRT_DT in group by will not look for the replacement of alias as
PRT_DT, but directly use PRT_DT, thus bypassing this problem.{color}
> SqlValidatorException when conformation is set to LENIENT
> -
>
> Key: CALCITE-3589
> URL: https://issues.apache.org/jira/browse/CALCITE-3589
> Project: Calcite
> Issue Type: Bug
>Reporter: bingfeng.guo
>Priority: Critical
> Attachments: 屏幕快照 2019-12-12 下午5.56.24.png
>
>
> error occurs conditions (all three conditions must be met):
> * conformance = LENIENT
> * The alias of the expression in select is the same as the column name of
> the column used in the expression
> * group by or having use the above expression
> {color:#172b4d}a simple example is as follows：{color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00}FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00}GROUP BY SELLER_ID + 1{color}
> {color:#172b4d}will change to {color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00} FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00} GROUP BY SELLER_ID + 1 + 1{color}
> {color:#172b4d}after
> org.apache.calcite.sql.validate.SqlValidatorImpl#expandGroupByOrHavingExpr{color}
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3589) SqlValidatorException when conformation is set to LENIENT
[
https://issues.apache.org/jira/browse/CALCITE-3589?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16994453#comment-16994453
]
bingfeng.guo edited comment on CALCITE-3589 at 12/12/19 10:13 AM:
--
@[~julianhyde] postgresql will group by the underlying column，not alias . but
calcite is group by the alias
was (Author: bingfeng):
[~julianhyde] postgresql will group by the underlying column，not alias . but
calcite is group by the alias
> SqlValidatorException when conformation is set to LENIENT
> -
>
> Key: CALCITE-3589
> URL: https://issues.apache.org/jira/browse/CALCITE-3589
> Project: Calcite
> Issue Type: Bug
>Reporter: bingfeng.guo
>Priority: Critical
> Attachments: 屏幕快照 2019-12-12 下午5.56.24.png
>
>
> error occurs conditions (all three conditions must be met):
> * conformance = LENIENT
> * The alias of the expression in select is the same as the column name of
> the column used in the expression
> * group by or having use the above expression
> {color:#172b4d}a simple example is as follows：{color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00}FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00}GROUP BY SELLER_ID + 1{color}
> {color:#172b4d}will change to {color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00} FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00} GROUP BY SELLER_ID + 1 + 1{color}
> {color:#172b4d}after
> org.apache.calcite.sql.validate.SqlValidatorImpl#expandGroupByOrHavingExpr{color}
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3589) SqlValidatorException when conformation is set to LENIENT
[
https://issues.apache.org/jira/browse/CALCITE-3589?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16994476#comment-16994476
]
bingfeng.guo edited comment on CALCITE-3589 at 12/12/19 10:12 AM:
--
In addition, when conformance = LENIENT，there is a similar problem。
The following sql will throw "column is ambiguous" error，Because SELLER_ID in
group by does not know which alias
{color:#ff8b00}SELECT SELLER_ID, SELLER_ID{color}
{color:#ff8b00}FROM TEST_KYLIN_FACT{color}
{color:#ff8b00}GROUP BY SELLER_ID{color}
{color:#172b4d}But this error does not occur when conformance = DEFAULT,
Because when conformance = DEFAULT, aliases cannot be used on group by.
Therefore, the PRT_DT in group by will not look for the replacement of alias as
PRT_DT, but directly use PRT_DT, thus bypassing this problem.{color}
was (Author: bingfeng):
In addition, when conformance = LENIENT，there is a similar problem。
The following sql will throw column is ambiguous error，Because SELLER_ID in
group by does not know which expression to use as SELLER_ID
{color:#ff8b00}SELECT SELLER_ID, SELLER_ID{color}
{color:#ff8b00}FROM TEST_KYLIN_FACT{color}
{color:#ff8b00}GROUP BY SELLER_ID{color}
{color:#172b4d}But this error does not occur when conformance = DEFAULT,
Because when conformance = DEFAULT, aliases cannot be used on group by.
Therefore, the PRT_DT in group by will not look for the replacement of alias as
PRT_DT, but directly use PRT_DT, thus bypassing this problem.{color}
> SqlValidatorException when conformation is set to LENIENT
> -
>
> Key: CALCITE-3589
> URL: https://issues.apache.org/jira/browse/CALCITE-3589
> Project: Calcite
> Issue Type: Bug
>Reporter: bingfeng.guo
>Priority: Critical
> Attachments: 屏幕快照 2019-12-12 下午5.56.24.png
>
>
> error occurs conditions (all three conditions must be met):
> * conformance = LENIENT
> * The alias of the expression in select is the same as the column name of
> the column used in the expression
> * group by or having use the above expression
> {color:#172b4d}a simple example is as follows：{color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00}FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00}GROUP BY SELLER_ID + 1{color}
> {color:#172b4d}will change to {color}
> {color:#ff8b00}SELECT SELLER_ID + 1 AS SELLER_ID, sum(PRICE){color}
> {color:#ff8b00} FROM TEST_KYLIN_FACT{color}
> {color:#ff8b00} GROUP BY SELLER_ID + 1 + 1{color}
> {color:#172b4d}after
> org.apache.calcite.sql.validate.SqlValidatorImpl#expandGroupByOrHavingExpr{color}
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3589) SqlValidatorException when conformation is set to LENIENT
[
https://issues.apache.org/jira/browse/CALCITE-3589?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16994453#comment-16994453
]
bingfeng.guo edited comment on CALCITE-3589 at 12/12/19 9:52 AM:
-
[~julianhyde] postgresql will group by the underlying column，not alias . but
calcite is group by the alias
was (Author: bingfeng):
[~julianhyde] postgresql will group by the underlying column，not alias
> SqlValidatorException when conformation is set to LENIENT
> -
>
> Key: CALCITE-3589
> URL: https://issues.apache.org/jira/browse/CALCITE-3589
> Project: Calcite
> Issue Type: Bug
>Reporter: bingfeng.guo
>Priority: Critical
>
> error occurs conditions (all three conditions must be met):
> * conformance = LENIENT
> * The alias of the expression in select is the same as the column name of
> the column used in the expression
> * group by or having use the above expression
> {color:#172b4d}a simple example is as follows：{color}
> {color:#ff8b00}SELECT MY_ID + 1 AS MY_ID, sum(PRICE){color}
> {color:#ff8b00}FROM tableA{color}
> {color:#ff8b00}GROUP BY MY_ID + 1{color}
> {color:#ff8b00}will {color}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3589) SqlValidatorException when conformation is set to LENIENT
[
https://issues.apache.org/jira/browse/CALCITE-3589?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16994219#comment-16994219
]
Jin Xing edited comment on CALCITE-3589 at 12/12/19 6:11 AM:
-
Running below tests in PostgreSQL:
{code:java}
psql> select a + 1 a from test group by a + 1;
Returns:
a
---
4
2
psql> select a + 2 from test group by a + 1;
ERROR: column "test.a" must appear in the GROUP BY clause or be used in an
aggregate function
LINE 1: select a + 2 a from test group by a + 1;
{code}
was (Author: jinxing6...@126.com):
I think the case [~bingfeng] gave in Jira description might be a bug.
Running below tests in PostgreSQL:
{code:java}
psql> select a + 1 a from test group by a + 1;
Returns:
a
---
4
2
psql> select a + 2 from test group by a + 1;
ERROR: column "test.a" must appear in the GROUP BY clause or be used in an
aggregate function
LINE 1: select a + 2 a from test group by a + 1;
{code}
> SqlValidatorException when conformation is set to LENIENT
> -
>
> Key: CALCITE-3589
> URL: https://issues.apache.org/jira/browse/CALCITE-3589
> Project: Calcite
> Issue Type: Bug
>Reporter: bingfeng.guo
>Priority: Critical
>
> {color:#172b4d}When conformance is set to LENIENT, and the alias of the
> select expression is the same as the table name in the expression, and the
> expression appears in group by, a SqlValidatorException will occur。{color}
> {color:#172b4d}a simple example is as follows：{color}
> {color:#ff8b00}SELECT MY_ID + 1 AS MY_ID, sum(PRICE){color}
> {color:#ff8b00}FROM tableA{color}
> {color:#ff8b00}GROUP BY MY_ID + 1{color}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3589) SqlValidatorException when conformation is set to LENIENT
[
https://issues.apache.org/jira/browse/CALCITE-3589?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16994158#comment-16994158
]
Jin Xing edited comment on CALCITE-3589 at 12/12/19 4:08 AM:
-
Run below test in MySQL and PostgreSQL;
{code:java}
CREATE TABLE test(a int, b int);
insert into test values (1,3), (3,1);
MySQL:
mysql> select a % 2 a from test group by a;
Returns:
+--+
| a|
+--+
|1 |
|1 |
+--+
mysql> select a + b a from test group by a;
Returns:
+--+
| a|
+--+
|4 |
|4 |
+--+
PostgreSQL:
psql> select a % 2 a from test group by a;
Returns:
a
---
1
1
psql> select a + b a from test group by a;
ERROR: column "test.b" must appear in the GROUP BY clause or be used in an
aggregate function
LINE 1: select a + b a from test group by a;
{code}
was (Author: jinxing6...@126.com):
Run below SQL in MySQL and PostgreSQL;
{code:java}
CREATE TABLE test(a int, b int);
insert into test values (1,3), (3,1);
MySQL:
mysql> select a % 2 a from test group by a;
Returns:
+--+
| a|
+--+
|1 |
|1 |
+--+
mysql> select a + b a from test group by a;
Returns:
+--+
| a|
+--+
|4 |
|4 |
+--+
PostgreSQL:
psql> select a % 2 a from test group by a;
Returns:
a
---
1
1
psql> select a + b a from test group by a;
ERROR: column "test.b" must appear in the GROUP BY clause or be used in an
aggregate function
LINE 1: select a + b a from test group by a;
{code}
> SqlValidatorException when conformation is set to LENIENT
> -
>
> Key: CALCITE-3589
> URL: https://issues.apache.org/jira/browse/CALCITE-3589
> Project: Calcite
> Issue Type: Bug
>Reporter: bingfeng.guo
>Priority: Critical
>
> {color:#172b4d}When conformance is set to LENIENT, and the alias of the
> select expression is the same as the table name in the expression, and the
> expression appears in group by, a SqlValidatorException will occur。{color}
> {color:#172b4d}a simple example is as follows：{color}
> {color:#ff8b00}SELECT MY_ID + 1 AS MY_ID, sum(PRICE){color}
> {color:#ff8b00}FROM tableA{color}
> {color:#ff8b00}GROUP BY MY_ID + 1{color}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
