[
https://issues.apache.org/jira/browse/CALCITE-4512?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17327086#comment-17327086
]
yanjing.wang edited comment on CALCITE-4512 at 4/22/21, 4:16 AM:
-----------------------------------------------------------------
I agree with you, I have experimented above cases and the result has proven
your hypotheses, also I walked through the source code and found some reasons.
*1.* prepare three pairs of cases. f(x), g(x) represent some function.
{code:java}
select f(empno) as empno from t group by empno
select f(constant) as empno from t group by empno
select f(empno) as empno from t group by f(empno)
select f(constant) as empno from t group by f(constant)
select f(empno) as empno from t group by g(empno)
select f(constant) as empno from t group by g(constant)
{code}
*2.* *for the first pair of cases*, calcite expands group by clause to
{code:java}
select f(empno) as empno from t group by f(empno)
select f(constant) as empno from t group by f(constant)
{code}
when validating every item of select list, calcite checks whether which or
whose arguments equals any item of group by list. due to f(empno) within select
list clause = f(empno) within group by clause, so *aggChecker* passes. and the
second does the same.
*for the second pair of cases*, calcite expands group by clause to
{code:java}
select f(empno) as empno from t group by f(f(empno))
select f(constant) as empno from t group by f(constant)
{code}
in the first case no item within select list clause equals group by item,
f(empno) != f(f(empno)), empno != f(f(empno)), so *aggChecker* throws. the
*group by expander* has expanded the query to wrong semantic. the second case
hasn't been expanded because of no identifier.
*for the third pair of cases*, we can also deduce both cases are invalid.
*3.* use *equalsDeep* checks whether two sqlCalls equal *can't recognize some
commutative f(x) equation* such as
{code:java}
select empno + deptno from emp group by deptno + empno;
{code}
so *aggChecker* throws. but empno + deptno = deptno + empno, MySQL passes.
was (Author: yanjing.wang):
I agree with you, I have experimented above cases and the result has proven
your hypotheses, also I walked through the source code and found some reasons.
*1.* prepare three pairs of cases. f(x), g(x) represent some function.
{code:java}
select f(empno) as empno from t group by empno
select f(constant) as empno from t group by empno
select f(empno) as empno from t group by f(empno)
select f(constant) as empno from t group by f(constant)
select f(empno) as empno from t group by g(empno)
select f(constant) as empno from t group by g(constant)
{code}
*2.* *for the first pair of cases*, calcite expands group by clause to
{code:java}
select f(empno) as empno from t group by f(empno)
select f(constant) as empno from t group by f(constant)
{code}
when validating every item of select list, calcite checks whether which or
whose arguments equals any item of group by list. due to f(empno) within select
list clause = f(empno) within group by clause, so *aggChecker* passes. and the
second does the same.
*for the second pair of cases*, calcite expands group by clause to
{code:java}
select f(empno) as empno from t group by f(f(empno))
select f(constant) as empno from t group by f(constant)
{code}
in the first case no item within select list clause equals group by item,
f(empno) != f(f(empno)), empno != f(f(empno)), so *aggChecker* throws. the
*group by expander* has expanded the query to wrong semantic. the second case
hasn't been expanded because of no identifier.
*for the third pair of cases*, we can also deduce both cases are invalid.
*3.* use *equalsDeep* checks whether two sqlCalls equal *can't recognize some
commutative f(x) equation* such as
{code:java}
select empno + deptno from emp group by deptno + empno;
{code}
so *aggChecker* throws. but empno + deptno = deptno + empno, MySQL passes.
> group by expression has argument name same with select list item alias,
> causes validation error
> -----------------------------------------------------------------------------------------------
>
> Key: CALCITE-4512
> URL: https://issues.apache.org/jira/browse/CALCITE-4512
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.26.0
> Environment: jvm: open-jdk8
> Reporter: yanjing.wang
> Priority: Major
>
> String sql = "select replace(name, 'a', 'b') as name from users group by
> replace(name, 'a', 'b')";
>
> when group by expression has argument name same with select list item alias,
> the group by argument will be expanded to select list item when sql
> conformance supports 'groupByAlias', so the above sql will be expanded to
> String sql = "select replace(name, 'a', 'b') as name from users group by
> replace(replace(name, 'a', 'b'), 'a', 'b')";
>
> this is unexpected.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
