MySQL Bugs: #115476: BIT query wrong result

Bug #115476	BIT query wrong result
Submitted:	1 Jul 2024 16:22	Modified:	3 Oct 2024 0:23
Reporter:	Pedro Ferreira	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	bit, distinct, sum

Description:
Run:

SELECT sum(b'1100'), sum(DISTINCT b'1100');
Outputs 12 and 9.

I would expect both sums to return the same value.

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the query above.

HI MR. Ferreira,

Thank you for your bug report.

We managed to repeat the behaviour in 8.0, 8.4 and 9.0.

Verified for all versions 8.0 and higher.

Documented fix as follows in the MySQL 9.1.0 changelog:

    SUM() yielded a different result in some cases for the same
    value when DISTINCT was also used. This was because, when using
    DISTINCT, a temporary table is used to hold the values so that
    in the end only the unique values are returned to the user. When
    creating such table while setting up DISTINCT, we did not take
    into consideration that the data type and length had been
    determined and use these, recalculating them instead. Now we use
    the data type and length already determined.

Closed.

Thank you, Jon.