MySQL Bugs: #112169: Using LOWER in HAVING clause falsifies result of GROUP

Bug #112169	Using LOWER in HAVING clause falsifies result of GROUP_CONCAT
Submitted:	23 Aug 2023 15:41	Modified:	24 Aug 2023 13:51
Reporter:	PM User	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	group_concat, group_concat(), having, LOWER, SELECT

Description:
When I use the LOWER() function in the HAVING clause on a column aggregated by GROUP_CONCAT(), then the aggregation column is lowercased in the result and therefore falsified.

How to repeat:
Example schema preparation:

CREATE TABLE `user` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE address (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id int NOT NULL,
  address VARCHAR(255) NOT NULL
);

INSERT INTO `user` (name) VALUES ("A");

INSERT INTO address (user_id, address) VALUES(1, "X"), (1, "Y");

Execute this query:

SELECT u.id,
       GROUP_CONCAT(a.address) AS addresses
FROM `user` u
JOIN address a ON a.user_id = u.id
GROUP BY u.id
HAVING LOWER(addresses) LIKE '%X%';

Expected result: "addresses" contains "X,Y"

Actual result: "addresses" contains "x,y" (lowercase)

The same behaviour occurs for UPPER() instead of LOWER(), if you adjust the test data before.

I added a fiddle to make it easier to reproduce: http://sqlfiddle.com/#!9/b8b064a/11

Suggested fix:
Casting the result of GROUP_CONCAT to CHAR fixed the problem.

SELECT u.id,
       CAST(GROUP_CONCAT(a.address) AS CHAR) AS addresses 
FROM `user` u
JOIN address a ON a.user_id = u.id
GROUP BY u.id
HAVING LOWER(addresses) LIKE '%X%';

Copying the statement instead of using the alias does also work.

SELECT u.id,
       GROUP_CONCAT(a.address) AS addresses
FROM `user` u
JOIN address a ON a.user_id = u.id
GROUP BY u.id
HAVING LOWER(GROUP_CONCAT(a.address)) LIKE '%X%';

Hi Mr. User,

This is not a bug.

Simply, HAVING clause is applicable only to fields in the GROUP BY clause. On the other hand, GROUP BY clause must contain only elements from the SELECT list. Hence, that is how HAVING forced that LOWER(addresses) replaced the addresses in the SELECT list.

This is specified in the SQL standard 2016, which MySQL follows strictly.

Not a bug.

Hi,

Actually this turned out that it is a small bug. 

The expression in the HAVING clause should have been added (without any output) to the SELECT list. It should not have replaced the actual field.

This bug is an old one that existed even in early 5.7,

Verified as reported.

Ok, let's assume it is an intended behaviour, that HAVING replaces the addresses in the result set.

What about this example? I use MIN instead of GROUP_CONCAT. Now the result is the same as in the database (uppercase) and is not transformed to lowercase. Why is this different?

SELECT u.id,
       MIN(a.address) AS addresses
FROM `user` u
JOIN address a ON a.user_id = u.id
GROUP BY u.id
HAVING LOWER(addresses) LIKE '%X%';

Result: "X" (uppercase)

Or let's have a look at other string converting operations. When I replace LOWER with CONCAT, the result matches the underlaying data. But when I use REPLACE, then it is modified again.

SELECT u.id,
       GROUP_CONCAT(a.address) AS addresses
FROM `user` u
JOIN address a ON a.user_id = u.id
GROUP BY u.id
HAVING CONCAT("test", addresses) LIKE '%X%';

Result: "X,Y"

SELECT u.id,
       GROUP_CONCAT(a.address) AS addresses
FROM `user` u
JOIN address a ON a.user_id = u.id
GROUP BY u.id
HAVING REPLACE(addresses, "Y", "X") LIKE '%X%';

Result: "X,X"

Sorry, I saw your answer only after I replied. Thanks for verifying the bug.