Description:
The following two queries contain the same AES_ENCRYPT() expression for ref1, but MySQL returns different results depending on whether an unrelated expression, ref0, is present in the projection list.
In Query 1, ref1 is evaluated together with another independent AES_ENCRYPT() expression, ref0, under SELECT DISTINCT.
In Query 2, the unrelated ref0 expression is removed. The definition of ref1 remains unchanged.
However, removing ref0 changes the result of ref1.
The ref1 expression is textually identical in both queries:
AES_ENCRYPT(
(SELECT t0.c0 WHERE t0.c0 > 0),
'str42',
NULL
)
Since ref0 is not referenced by ref1 and has no data dependency on it, removing ref0 from the projection should not change the value produced by ref1.
This suggests that the optimizer, expression evaluation, or DISTINCT execution path may incorrectly depend on the presence of another projected expression.
How to repeat:
Run the following statements on MySQL Community Server 26.7.0:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
c0 FLOAT
);
INSERT INTO t0 (c0)
VALUES (0.318998);
First execute:
SELECT DISTINCT
AES_ENCRYPT(
(SELECT t0.c0 WHERE 1),
'str42',
NULL
) AS ref0,
AES_ENCRYPT(
(SELECT t0.c0 WHERE t0.c0 > 0),
'str42',
NULL
) AS ref1
FROM t0;
Observed result:
fdea9663-6450-1dd6-1d46-c7bbdb799993,
e8ee8e09-f7ef-ad1f-3e1d-558286c8005d
Then remove only the unrelated ref0 projection and execute:
SELECT DISTINCT
AES_ENCRYPT(
(SELECT t0.c0 WHERE t0.c0 > 0),
'str42',
NULL
) AS ref1
FROM t0;
Observed result:
e8ee8e09-f7ef-ad1f-3e1d-558286c8005d
The value of ref1 changes when the unrelated ref0 expression is removed.
Expected Result
The value of ref1 should remain unchanged.
The transformation:
SELECT DISTINCT ref0, ref1
to:
SELECT DISTINCT ref1
removes only an independent projected expression. The definition of ref1, its input data, and its predicates remain unchanged.
Therefore, the AES_ENCRYPT() result for ref1 should be identical in both queries.
Actual Result
Query 1 returns:
fdea9663-6450-1dd6-1d46-c7bbdb799993,
e8ee8e09-f7ef-ad1f-3e1d-558286c8005d
Query 2 returns:
e8ee8e09-f7ef-ad1f-3e1d-558286c8005d
The ref1 expression is identical in both queries, but its result depends on the presence of the unrelated ref0 projection.
Description: The following two queries contain the same AES_ENCRYPT() expression for ref1, but MySQL returns different results depending on whether an unrelated expression, ref0, is present in the projection list. In Query 1, ref1 is evaluated together with another independent AES_ENCRYPT() expression, ref0, under SELECT DISTINCT. In Query 2, the unrelated ref0 expression is removed. The definition of ref1 remains unchanged. However, removing ref0 changes the result of ref1. The ref1 expression is textually identical in both queries: AES_ENCRYPT( (SELECT t0.c0 WHERE t0.c0 > 0), 'str42', NULL ) Since ref0 is not referenced by ref1 and has no data dependency on it, removing ref0 from the projection should not change the value produced by ref1. This suggests that the optimizer, expression evaluation, or DISTINCT execution path may incorrectly depend on the presence of another projected expression. How to repeat: Run the following statements on MySQL Community Server 26.7.0: DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 FLOAT ); INSERT INTO t0 (c0) VALUES (0.318998); First execute: SELECT DISTINCT AES_ENCRYPT( (SELECT t0.c0 WHERE 1), 'str42', NULL ) AS ref0, AES_ENCRYPT( (SELECT t0.c0 WHERE t0.c0 > 0), 'str42', NULL ) AS ref1 FROM t0; Observed result: fdea9663-6450-1dd6-1d46-c7bbdb799993, e8ee8e09-f7ef-ad1f-3e1d-558286c8005d Then remove only the unrelated ref0 projection and execute: SELECT DISTINCT AES_ENCRYPT( (SELECT t0.c0 WHERE t0.c0 > 0), 'str42', NULL ) AS ref1 FROM t0; Observed result: e8ee8e09-f7ef-ad1f-3e1d-558286c8005d The value of ref1 changes when the unrelated ref0 expression is removed. Expected Result The value of ref1 should remain unchanged. The transformation: SELECT DISTINCT ref0, ref1 to: SELECT DISTINCT ref1 removes only an independent projected expression. The definition of ref1, its input data, and its predicates remain unchanged. Therefore, the AES_ENCRYPT() result for ref1 should be identical in both queries. Actual Result Query 1 returns: fdea9663-6450-1dd6-1d46-c7bbdb799993, e8ee8e09-f7ef-ad1f-3e1d-558286c8005d Query 2 returns: e8ee8e09-f7ef-ad1f-3e1d-558286c8005d The ref1 expression is identical in both queries, but its result depends on the presence of the unrelated ref0 projection.