Description:
MySQL produces different results from AES_ENCRYPT() when its first argument is supplied by two scalar subqueries that return the same value.
The two scalar subqueries below are semantically equivalent for the given table contents:
(SELECT t0.c0 WHERE 1)
and
(SELECT t0.c0 WHERE t0.c0 > 0)
The table contains a single non-NULL value:
c0 = 0.318998
Therefore, both scalar subqueries return the same value, 0.318998.
However, passing these two equivalent expressions to AES_ENCRYPT() produces different results.
How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0
(
c0 FLOAT
);
INSERT INTO t0 (c0)
VALUES (0.318998);
SELECT
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;
Actual Result
The query returns different values:
ref0 = fdea9663-6450-1dd6-1d46-c7bbdb799993
ref1 = e8ee8e09-f7ef-ad1f-3e1d-558286c8005d
Expected Result
Both scalar subqueries return the same underlying value:
0.318998
They therefore should provide the same input to AES_ENCRYPT().
Consequently, the two AES_ENCRYPT() calls should produce the same result.
For example, the following expressions should be equivalent for this data:
AES_ENCRYPT(
(SELECT t0.c0 WHERE 1),
'str42',
NULL
)
and
AES_ENCRYPT(
(SELECT t0.c0 WHERE t0.c0 > 0),
'str42',
NULL
)
However, MySQL returns different ciphertexts.
Why this appears incorrect
The difference does not appear to be caused by the plaintext value itself. Both scalar subqueries select the same FLOAT column from the same single-row table, and both predicates are true for the only row.
The only difference is the predicate used inside the scalar subquery:
WHERE 1
versus:
WHERE t0.c0 > 0
Since both subqueries return the same value, changing the predicate in this way should not change the value supplied to AES_ENCRYPT().
The fact that AES_ENCRYPT() produces different results suggests that some internal type, representation, conversion, or expression-evaluation state may differ depending on how the equivalent scalar subquery is constructed.
Minimal Observation
For the same table state:
(SELECT c0 WHERE 1)
and
(SELECT c0 WHERE c0 > 0)
return the same value, but:
AES_ENCRYPT((SELECT c0 WHERE 1), 'str42', NULL)
and
AES_ENCRYPT((SELECT c0 WHERE c0 > 0), 'str42', NULL)
produce different results.
Impact
This may cause semantically equivalent expressions involving AES_ENCRYPT() to produce different ciphertexts, depending on the form of an equivalent scalar subquery used to obtain the plaintext.
Please investigate whether the scalar-subquery execution, FLOAT value conversion, or AES_ENCRYPT() argument evaluation incorrectly preserves different internal representations for these semantically equivalent expressions.
Description: MySQL produces different results from AES_ENCRYPT() when its first argument is supplied by two scalar subqueries that return the same value. The two scalar subqueries below are semantically equivalent for the given table contents: (SELECT t0.c0 WHERE 1) and (SELECT t0.c0 WHERE t0.c0 > 0) The table contains a single non-NULL value: c0 = 0.318998 Therefore, both scalar subqueries return the same value, 0.318998. However, passing these two equivalent expressions to AES_ENCRYPT() produces different results. How to repeat: DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 FLOAT ); INSERT INTO t0 (c0) VALUES (0.318998); SELECT 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; Actual Result The query returns different values: ref0 = fdea9663-6450-1dd6-1d46-c7bbdb799993 ref1 = e8ee8e09-f7ef-ad1f-3e1d-558286c8005d Expected Result Both scalar subqueries return the same underlying value: 0.318998 They therefore should provide the same input to AES_ENCRYPT(). Consequently, the two AES_ENCRYPT() calls should produce the same result. For example, the following expressions should be equivalent for this data: AES_ENCRYPT( (SELECT t0.c0 WHERE 1), 'str42', NULL ) and AES_ENCRYPT( (SELECT t0.c0 WHERE t0.c0 > 0), 'str42', NULL ) However, MySQL returns different ciphertexts. Why this appears incorrect The difference does not appear to be caused by the plaintext value itself. Both scalar subqueries select the same FLOAT column from the same single-row table, and both predicates are true for the only row. The only difference is the predicate used inside the scalar subquery: WHERE 1 versus: WHERE t0.c0 > 0 Since both subqueries return the same value, changing the predicate in this way should not change the value supplied to AES_ENCRYPT(). The fact that AES_ENCRYPT() produces different results suggests that some internal type, representation, conversion, or expression-evaluation state may differ depending on how the equivalent scalar subquery is constructed. Minimal Observation For the same table state: (SELECT c0 WHERE 1) and (SELECT c0 WHERE c0 > 0) return the same value, but: AES_ENCRYPT((SELECT c0 WHERE 1), 'str42', NULL) and AES_ENCRYPT((SELECT c0 WHERE c0 > 0), 'str42', NULL) produce different results. Impact This may cause semantically equivalent expressions involving AES_ENCRYPT() to produce different ciphertexts, depending on the form of an equivalent scalar subquery used to obtain the plaintext. Please investigate whether the scalar-subquery execution, FLOAT value conversion, or AES_ENCRYPT() argument evaluation incorrectly preserves different internal representations for these semantically equivalent expressions.