MySQL Bugs: #98630: The same expression in GROUP BY and in output is not distinguished

Bug #98630	The same expression in GROUP BY and in output is not distinguished
Submitted:	17 Feb 2020 9:36	Modified:	18 Feb 2020 13:33
Reporter:	Владислав Сокол	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL reports "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.val' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by", whereas the expression is literally the same.

How to repeat:
-- source table
CREATE TABLE test (val INT);

-- example data
INSERT INTO test VALUES (11), (13), (15), (21), (23), (25), (31);

-- Expected result
-- dec    cnt
-- 10-19  3
-- 20-29  3
-- 30-39  1

SELECT CONCAT((val DIV 10), '0-', (val DIV 10) MOD 10, '9') dec, 
       COUNT(*) cnt
FROM test
GROUP BY (val DIV 10);

-- but error message is generated, (val DIV 10) is not distinguished.

Suggested fix:
Excess ANY_VALUE() function usage allows to fix the issue:

SELECT CONCAT(ANY_VALUE(val DIV 10), '0-', ANY_VALUE(val DIV 10) MOD 10, '9') dec, 
       COUNT(*) cnt
FROM test
GROUP BY (val DIV 10);

Hi Mr. Sokol,

Thank you for your bug report.

Please, try setting an alias for the expression CONCAT((val DIV 10) and then use that alias in the GROUP BY clause.

Waiting on your feedback.

> try setting an alias for the expression CONCAT((val DIV 10) and then use that alias in the GROUP BY clause.

If I use the whole expression or its alias taken from output the query is executed without problems. Of course.

Hi Mr. Sokol,

That is how things work with SQL and we have to conform to the standards that are set by its committee. 

Not a bug.