Description:
The same AES_ENCRYPT() expression produces different results depending only on whether SELECT DISTINCT is used.
The two queries below evaluate the same projected AES_ENCRYPT() expression over the same table. The only difference is that one query uses SELECT DISTINCT.
However, MySQL returns different values, and the difference is not limited to duplicate-row elimination or presentation formatting. The returned encrypted values have different lengths and contents.
With SELECT DISTINCT, the result is:
e8ee8e09-f7ef-ad1f-3e1d-558286c8005d
Without DISTINCT, the result is:
0xE8EE8E09F7EFAD1F3E1D558286C8005D1C14464503F246A7D0D785065A7E3639
The query contains only one row, so DISTINCT has no duplicate rows to eliminate.
The AES_ENCRYPT() expression is:
AES_ENCRYPT(
(SELECT t0.c0 WHERE t0.c0 > 0),
'str42',
NULL
)
Adding DISTINCT should not cause this expression to produce a different encrypted value when the underlying input is unchanged.
This suggests that the DISTINCT execution or optimization path may incorrectly affect the evaluation, type representation, or materialization of the correlated scalar-subquery result passed to AES_ENCRYPT().
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);
Then 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
Now execute the same expression without DISTINCT:
SELECT
AES_ENCRYPT(
(SELECT t0.c0 WHERE t0.c0 > 0),
'str42',
NULL
) AS ref1
FROM t0;
Observed result:
0xE8EE8E09F7EFAD1F3E1D558286C8005D1C14464503F246A7D0D785065A7E3639
The two queries return different AES_ENCRYPT() results.
Expected Result
The two queries should produce the same AES_ENCRYPT() value.
DISTINCT should only affect duplicate result rows. Since the table contains only one row, adding DISTINCT should not change the value produced by the projected AES_ENCRYPT() expression.
Actual Result
With DISTINCT:
e8ee8e09-f7ef-ad1f-3e1d-558286c8005d
Without DISTINCT:
0xE8EE8E09F7EFAD1F3E1D558286C8005D1C14464503F246A7D0D785065A7E3639
The encrypted outputs differ in both content and length.
Description: The same AES_ENCRYPT() expression produces different results depending only on whether SELECT DISTINCT is used. The two queries below evaluate the same projected AES_ENCRYPT() expression over the same table. The only difference is that one query uses SELECT DISTINCT. However, MySQL returns different values, and the difference is not limited to duplicate-row elimination or presentation formatting. The returned encrypted values have different lengths and contents. With SELECT DISTINCT, the result is: e8ee8e09-f7ef-ad1f-3e1d-558286c8005d Without DISTINCT, the result is: 0xE8EE8E09F7EFAD1F3E1D558286C8005D1C14464503F246A7D0D785065A7E3639 The query contains only one row, so DISTINCT has no duplicate rows to eliminate. The AES_ENCRYPT() expression is: AES_ENCRYPT( (SELECT t0.c0 WHERE t0.c0 > 0), 'str42', NULL ) Adding DISTINCT should not cause this expression to produce a different encrypted value when the underlying input is unchanged. This suggests that the DISTINCT execution or optimization path may incorrectly affect the evaluation, type representation, or materialization of the correlated scalar-subquery result passed to AES_ENCRYPT(). 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); Then 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 Now execute the same expression without DISTINCT: SELECT AES_ENCRYPT( (SELECT t0.c0 WHERE t0.c0 > 0), 'str42', NULL ) AS ref1 FROM t0; Observed result: 0xE8EE8E09F7EFAD1F3E1D558286C8005D1C14464503F246A7D0D785065A7E3639 The two queries return different AES_ENCRYPT() results. Expected Result The two queries should produce the same AES_ENCRYPT() value. DISTINCT should only affect duplicate result rows. Since the table contains only one row, adding DISTINCT should not change the value produced by the projected AES_ENCRYPT() expression. Actual Result With DISTINCT: e8ee8e09-f7ef-ad1f-3e1d-558286c8005d Without DISTINCT: 0xE8EE8E09F7EFAD1F3E1D558286C8005D1C14464503F246A7D0D785065A7E3639 The encrypted outputs differ in both content and length.