Description:
MySQL produces an unexpectedly padded binary result when a numeric BIT_XOR() aggregate is combined with an empty EXCEPT branch containing a temporal expression.
The original query returns a single row, but the resulting value is represented as a 29-byte binary string consisting of the ASCII character '0' followed by 28 NUL (0x00) bytes.
This behavior appears to be related to inconsistent type or collation metadata associated with the numeric BIT_XOR() result during set-operation type resolution.
According to the MySQL documentation, BIT_XOR() with a numeric argument performs numeric evaluation and returns an unsigned 64-bit integer.
However, the following metadata discrepancy was observed:
COERCIBILITY(BIT_XOR(0)) returns 4.
COERCIBILITY(CAST(BIT_XOR(0) AS UNSIGNED)) returns 5.
According to the documented coercibility categories, 4 corresponds to a coercible string literal, whereas 5 corresponds to a numeric or temporal expression.
In the affected query, the result appears to be resolved to a fixed-length binary representation, causing the value '0' to be padded with 28 additional NUL bytes.
The padding is observable through HEX() and LENGTH(), so it is not merely a client-side display issue.
Explicitly casting BIT_XOR(0) to UNSIGNED avoids the unexpected padding in the tested case, even though it preserves the numeric value.
The issue may affect downstream string functions, binary comparisons, and other operations that depend on the actual byte representation of the result.
The suspected defect concerns the interaction between numeric aggregate type metadata and set-operation type aggregation. The exact source-level cause has not yet been established.
How to repeat:
No tables or additional setup are required.
1. Execute the original query:
SELECT
HEX(v) AS hex_value,
LENGTH(v) AS byte_length,
CHARSET(v) AS charset_name
FROM (
SELECT BIT_XOR(0) AS v
EXCEPT
SELECT SUBTIME(NULL, 0) WHERE FALSE
) AS q;
Actual result on the affected MySQL version:
hex_value: 30 followed by 28 repetitions of 00
byte_length: 29
charset_name: binary
The second branch returns no rows, so the resulting row originates entirely from the numeric BIT_XOR(0) expression.
2. Explicitly preserve the numeric type:
SELECT
HEX(v) AS hex_value,
LENGTH(v) AS byte_length,
CHARSET(v) AS charset_name
FROM (
SELECT CAST(BIT_XOR(0) AS UNSIGNED) AS v
EXCEPT
SELECT SUBTIME(NULL, 0) WHERE FALSE
) AS q;
Observed result:
hex_value: 30
byte_length: 1
No trailing NUL-byte padding
This modification preserves the numeric value of the aggregate but changes the resulting byte representation in the same set-operation context.
3. Inspect the coercibility metadata:
SELECT
COERCIBILITY(BIT_XOR(0)) AS original_coercibility,
COERCIBILITY(CAST(BIT_XOR(0) AS UNSIGNED))
AS cast_coercibility;
Observed result:
original_coercibility | cast_coercibility
4 | 5
This discrepancy suggests that the numeric BIT_XOR() expression may carry inappropriate collation or coercibility metadata.
Expected behavior:
Numeric BIT_XOR() results should be handled consistently with their documented unsigned-integer result type.
Although an empty branch may still participate in set-operation type resolution, such resolution should not unexpectedly introduce binary padding as a consequence of inconsistent numeric aggregate metadata.
The original and explicitly cast variants should not acquire different byte representations solely because of an incorrect type or coercibility classification.
Actual behavior:
The original query yields a 29-byte binary string containing 28 trailing NUL bytes, while the explicitly cast variant produces a single-byte representation of '0'.
Suggested fix:
Investigate the type and collation metadata propagation for numeric BIT_XOR() aggregates, particularly when their results participate in UNION, EXCEPT, or INTERSECT type resolution.
Possible areas to examine include:
1. The numeric-result branch of Item_sum_bit::resolve_type() and related aggregate metadata initialization.
2. Coercibility and collation assignment for numeric BIT_XOR() expressions.
3. Common result-type aggregation for mixed numeric and temporal expressions in set operations.
4. The selection of fixed-length BINARY storage and subsequent NUL-byte padding.
Ensure that numeric BIT_XOR() results carry metadata consistent with their documented unsigned-integer type, and that set-operation type conversion does not introduce unintended padding.
A regression test covering numeric BIT_XOR(), an empty temporal EXCEPT branch, and byte-sensitive result functions such as HEX() and LENGTH() is recommended.
© 2026, Oracle Corporation and/or its affiliates
Description: MySQL produces an unexpectedly padded binary result when a numeric BIT_XOR() aggregate is combined with an empty EXCEPT branch containing a temporal expression. The original query returns a single row, but the resulting value is represented as a 29-byte binary string consisting of the ASCII character '0' followed by 28 NUL (0x00) bytes. This behavior appears to be related to inconsistent type or collation metadata associated with the numeric BIT_XOR() result during set-operation type resolution. According to the MySQL documentation, BIT_XOR() with a numeric argument performs numeric evaluation and returns an unsigned 64-bit integer. However, the following metadata discrepancy was observed: COERCIBILITY(BIT_XOR(0)) returns 4. COERCIBILITY(CAST(BIT_XOR(0) AS UNSIGNED)) returns 5. According to the documented coercibility categories, 4 corresponds to a coercible string literal, whereas 5 corresponds to a numeric or temporal expression. In the affected query, the result appears to be resolved to a fixed-length binary representation, causing the value '0' to be padded with 28 additional NUL bytes. The padding is observable through HEX() and LENGTH(), so it is not merely a client-side display issue. Explicitly casting BIT_XOR(0) to UNSIGNED avoids the unexpected padding in the tested case, even though it preserves the numeric value. The issue may affect downstream string functions, binary comparisons, and other operations that depend on the actual byte representation of the result. The suspected defect concerns the interaction between numeric aggregate type metadata and set-operation type aggregation. The exact source-level cause has not yet been established. How to repeat: No tables or additional setup are required. 1. Execute the original query: SELECT HEX(v) AS hex_value, LENGTH(v) AS byte_length, CHARSET(v) AS charset_name FROM ( SELECT BIT_XOR(0) AS v EXCEPT SELECT SUBTIME(NULL, 0) WHERE FALSE ) AS q; Actual result on the affected MySQL version: hex_value: 30 followed by 28 repetitions of 00 byte_length: 29 charset_name: binary The second branch returns no rows, so the resulting row originates entirely from the numeric BIT_XOR(0) expression. 2. Explicitly preserve the numeric type: SELECT HEX(v) AS hex_value, LENGTH(v) AS byte_length, CHARSET(v) AS charset_name FROM ( SELECT CAST(BIT_XOR(0) AS UNSIGNED) AS v EXCEPT SELECT SUBTIME(NULL, 0) WHERE FALSE ) AS q; Observed result: hex_value: 30 byte_length: 1 No trailing NUL-byte padding This modification preserves the numeric value of the aggregate but changes the resulting byte representation in the same set-operation context. 3. Inspect the coercibility metadata: SELECT COERCIBILITY(BIT_XOR(0)) AS original_coercibility, COERCIBILITY(CAST(BIT_XOR(0) AS UNSIGNED)) AS cast_coercibility; Observed result: original_coercibility | cast_coercibility 4 | 5 This discrepancy suggests that the numeric BIT_XOR() expression may carry inappropriate collation or coercibility metadata. Expected behavior: Numeric BIT_XOR() results should be handled consistently with their documented unsigned-integer result type. Although an empty branch may still participate in set-operation type resolution, such resolution should not unexpectedly introduce binary padding as a consequence of inconsistent numeric aggregate metadata. The original and explicitly cast variants should not acquire different byte representations solely because of an incorrect type or coercibility classification. Actual behavior: The original query yields a 29-byte binary string containing 28 trailing NUL bytes, while the explicitly cast variant produces a single-byte representation of '0'. Suggested fix: Investigate the type and collation metadata propagation for numeric BIT_XOR() aggregates, particularly when their results participate in UNION, EXCEPT, or INTERSECT type resolution. Possible areas to examine include: 1. The numeric-result branch of Item_sum_bit::resolve_type() and related aggregate metadata initialization. 2. Coercibility and collation assignment for numeric BIT_XOR() expressions. 3. Common result-type aggregation for mixed numeric and temporal expressions in set operations. 4. The selection of fixed-length BINARY storage and subsequent NUL-byte padding. Ensure that numeric BIT_XOR() results carry metadata consistent with their documented unsigned-integer type, and that set-operation type conversion does not introduce unintended padding. A regression test covering numeric BIT_XOR(), an empty temporal EXCEPT branch, and byte-sensitive result functions such as HEX() and LENGTH() is recommended.