xiedeyantu commented on code in PR #4417:
URL: https://github.com/apache/calcite/pull/4417#discussion_r2157871990
##########
core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java:
##########
@@ -6589,12 +6610,14 @@ public boolean isBangEqualAllowed() {
sql("select deptno + empno as d, deptno + empno + mgr from emp"
+ " group by d,mgr")
.withConformance(lenient).ok();
- // When alias is equal to one or more columns in the query then giving
- // priority to alias. But Postgres may throw ambiguous column error or give
- // priority to column name.
+ // Alias is not used since there is a single column in the SELECT already
matching
sql("select count(*) from (\n"
- + " select ename AS deptno FROM emp GROUP BY deptno) t")
- .withConformance(lenient).ok();
+ + " select ^ename^ AS deptno FROM emp GROUP BY deptno) t")
+ .withConformance(strict)
+ .fails("Expression 'ENAME' is not being grouped")
+ .withConformance(lenient)
Review Comment:
Thank you very much for your detailed reply. I've tested this example:
```
CREATE TABLE emps (
empid int,
deptno int,
name varchar(255),
salary int,
commission int
);
INSERT INTO emps VALUES (100, 10, "Bill", 10000, 1000);
INSERT INTO emps VALUES (110, 10, "Theodore", 11500, 250);
INSERT INTO emps VALUES (150, 10, "Sebastian", 7000, null);
INSERT INTO emps VALUES (200, 20, "Eric", 8000, 500);
-- first query --
select count(*) from (select name AS deptno FROM emps GROUP BY deptno) t;
-- second query --
select name AS deptno FROM emps GROUP BY deptno;
```
Both queries return the same error in MySQL:
```
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'test.emps.name' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by
```
From my observation, MySQL's `only_full_group_by` SQL mode checks both:
1. Original column names in the SELECT clause for `deptno`
2. Column aliases in the SELECT clause for `deptno`
When setting `sql_mode=''`, MySQL returns what it considers correct results:
```
select name AS deptno FROM emps GROUP BY deptno;
+--------+
| deptno |
+--------+
| Bill |
| Eric |
+--------+
```
In this case, it recognizes the original column name `deptno`, not the alias
`deptno`.
I believe we can ignore the `sql_mode=''` special case since MySQL defaults
to `sql_mode=only_full_group_by`. Based on this, I found a code comment about
GROUP BY handling:
```
// This behavior is from MySQL:
// - if there is no column in the SELECT with the name used in the GROUP BY
// then look for a column alias in the SELECT
// - if there are multiple columns in the SELECT with the name used in the
GROUP BY,
// then also look for a column alias in the SELECT
```
I understand the first point, as demonstrated by this example:
```
select name AS deptno1 FROM emps GROUP BY deptno1;
+-----------+
| deptno1 |
+-----------+
| Bill |
| Theodore |
| Sebastian |
| Eric |
+-----------+
```
The second point is what confuses me. I thought it would work like this
example:
```
set sql_mode=only_full_group_by;
select name AS deptno FROM emps GROUP BY deptno;
```
But MySQL doesn't look for the alias - it just errors out. This doesn't
match the comment.
These tests were conducted on MySQL 8.0.23.
Perhaps I've misunderstood something - I'd appreciate any corrections to my
understanding. Thank you!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
