Description:
MySQL produces an inconsistent result when a NULL-returning AES_ENCRYPT() expression containing a scalar subquery is used as an argument to COUNT(DISTINCT ...).
The following query demonstrates the issue:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0
(
c2 TINYTEXT
);
INSERT INTO t0 (c2)
VALUES ('');
SELECT AES_ENCRYPT(NULL, (SELECT t0.c2 FROM t0)) AS ref0,
COUNT(DISTINCT 1, NULL) AS ref1,
COUNT(DISTINCT 1,
AES_ENCRYPT(NULL, (SELECT t0.c2 FROM t0))) AS ref2
FROM t0;
The actual result is:
ref0 ref1 ref2
NULL 0 1
ref0 confirms that:
AES_ENCRYPT(NULL, (SELECT t0.c2 FROM t0))
evaluates to NULL.
The reference expression:
COUNT(DISTINCT 1, NULL)
returns 0, as expected.
However, replacing the explicit NULL with the semantically equivalent expression:
AES_ENCRYPT(NULL, (SELECT t0.c2 FROM t0))
causes:
COUNT(DISTINCT 1, AES_ENCRYPT(NULL, (SELECT t0.c2 FROM t0)))
to return 1.
Therefore, the same NULL value is treated differently by COUNT(DISTINCT ...) depending on whether it is supplied directly or produced by AES_ENCRYPT() with a scalar subquery.
This results in an incorrect aggregate count.
The discrepancy can be summarized as:
AES_ENCRYPT(NULL, (SELECT c2 FROM t0)) → NULL
COUNT(DISTINCT 1, NULL) → 0
COUNT(DISTINCT 1, AES_ENCRYPT(NULL,
(SELECT c2 FROM t0))) → 1
Since AES_ENCRYPT() independently evaluates to NULL, the latter expression should not produce a different COUNT(DISTINCT ...) result from the explicit NULL case.
This does not appear to involve floating-point precision or an undefined result order. The testcase uses a single-row table and TINYTEXT, and the discrepancy is reproducible with a minimal query.
How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0
(
c2 TINYTEXT
);
INSERT INTO t0 (c2)
VALUES ('');
SELECT AES_ENCRYPT(NULL, (SELECT t0.c2 FROM t0)) AS ref0,
COUNT(DISTINCT 1, NULL) AS ref1,
COUNT(DISTINCT 1,
AES_ENCRYPT(NULL, (SELECT t0.c2 FROM t0))) AS ref2
FROM t0;
Actual result:
ref0 ref1 ref2
NULL 0 1
Expected result:
ref0 ref1 ref2
NULL 0 0
The important observation is that ref0 and the explicit NULL reference establish that the expression used by ref2 evaluates to NULL, but COUNT(DISTINCT ...) nevertheless counts it as a distinct value.
Description: MySQL produces an inconsistent result when a NULL-returning AES_ENCRYPT() expression containing a scalar subquery is used as an argument to COUNT(DISTINCT ...). The following query demonstrates the issue: DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c2 TINYTEXT ); INSERT INTO t0 (c2) VALUES (''); SELECT AES_ENCRYPT(NULL, (SELECT t0.c2 FROM t0)) AS ref0, COUNT(DISTINCT 1, NULL) AS ref1, COUNT(DISTINCT 1, AES_ENCRYPT(NULL, (SELECT t0.c2 FROM t0))) AS ref2 FROM t0; The actual result is: ref0 ref1 ref2 NULL 0 1 ref0 confirms that: AES_ENCRYPT(NULL, (SELECT t0.c2 FROM t0)) evaluates to NULL. The reference expression: COUNT(DISTINCT 1, NULL) returns 0, as expected. However, replacing the explicit NULL with the semantically equivalent expression: AES_ENCRYPT(NULL, (SELECT t0.c2 FROM t0)) causes: COUNT(DISTINCT 1, AES_ENCRYPT(NULL, (SELECT t0.c2 FROM t0))) to return 1. Therefore, the same NULL value is treated differently by COUNT(DISTINCT ...) depending on whether it is supplied directly or produced by AES_ENCRYPT() with a scalar subquery. This results in an incorrect aggregate count. The discrepancy can be summarized as: AES_ENCRYPT(NULL, (SELECT c2 FROM t0)) → NULL COUNT(DISTINCT 1, NULL) → 0 COUNT(DISTINCT 1, AES_ENCRYPT(NULL, (SELECT c2 FROM t0))) → 1 Since AES_ENCRYPT() independently evaluates to NULL, the latter expression should not produce a different COUNT(DISTINCT ...) result from the explicit NULL case. This does not appear to involve floating-point precision or an undefined result order. The testcase uses a single-row table and TINYTEXT, and the discrepancy is reproducible with a minimal query. How to repeat: DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c2 TINYTEXT ); INSERT INTO t0 (c2) VALUES (''); SELECT AES_ENCRYPT(NULL, (SELECT t0.c2 FROM t0)) AS ref0, COUNT(DISTINCT 1, NULL) AS ref1, COUNT(DISTINCT 1, AES_ENCRYPT(NULL, (SELECT t0.c2 FROM t0))) AS ref2 FROM t0; Actual result: ref0 ref1 ref2 NULL 0 1 Expected result: ref0 ref1 ref2 NULL 0 0 The important observation is that ref0 and the explicit NULL reference establish that the expression used by ref2 evaluates to NULL, but COUNT(DISTINCT ...) nevertheless counts it as a distinct value.