[
https://issues.apache.org/jira/browse/CALCITE-3589?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17182164#comment-17182164
]
Julian Hyde commented on CALCITE-3589:
--------------------------------------
[~bingfeng], I agree that we should keep LENIENT's isGroupByAlias behavior.
I ran some tests on PostgreSQL:
{noformat}
drop table t;
create table t (x integer, y integer);
insert into t values (1, 1), (2, 2), (3, 2);
# Q1
select *
from t;
x y
- -
1 1
2 2
3 2
# Q2
select x - y
from t
group by x - y;
?
-
1
0
# Q3
select distinct x - y
from t;
?
-
1
0
# Q4
select x - y as x
from t
group by x - y;
x
-
1
0
# Q5
select x - y as a
from t
group by a - y;
42703: column "a" does not exist
# Q6
select x - y as a
from t
group by a
a
-
1
0
# Q7
select x - y as a, count(*) as b
from t
group by a;
a b
- -
1 1
0 2
# Q8
select x - y as a, count(*) as b
from t
group by a, b;
42803: aggregate functions are not allowed in GROUP BY
# Q9
select x - y as x
from t
group by t.x, t.y
x
-
0
0
1
# Q10
select x - y as x
from t
group by x;
42803: column "t.y" must appear in the GROUP BY clause or be used in an
aggregate function
# Q11
select t.x + 1 as y
from t, (select 1 as x) as t2
group by y;
42803: column "t.x" must appear in the GROUP BY clause or be used in an
aggregate function
# Q12
select t.x + 1 as y
from t, (select 1 as x) as t2
group by t.x + 1;
y
-
4
3
2
# Q13
select x + 1 as y
from t
group by y;
42803: column "t.x" must appear in the GROUP BY clause or be used in an
aggregate function
# Q14
select x + 1 as x
from t
group by x;
x
-
2
4
3{noformat}
[~bingfeng], Your theory "only when the underlying column and alias exist at
the same time, group by the underlying column" does not explain why Q5 fails.
> 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)
