| Bug #100859 | The results of aggregation on BIT type are inconsistent when join type differs | ||
|---|---|---|---|
| Submitted: | 16 Sep 2020 6:37 | Modified: | 13 Sep 2021 19:12 |
| Reporter: | Hope Lee (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.13,8.0.21 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
[16 Sep 2020 6:37]
Hope Lee
[16 Sep 2020 7:40]
MySQL Verification Team
Hello Lee, Thank you for the report and test case. regards, Umesh
[16 Sep 2020 7:40]
MySQL Verification Team
- bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.21 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> CREATE TABLE t1(a BIT, b INT); Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO t1 VALUES (b'0', 0), (b'1', 1); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> mysql> CREATE INDEX i1 ON t1(b); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT MIN(a) FROM t1 GROUP BY b; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | index | i1 | i1 | 5 | NULL | 2 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+--------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select min(`test`.`t1`.`a`) AS `MIN(a)` from `test`.`t1` group by `test`.`t1`.`b` | +-------+------+--------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT MIN(a) FROM t1 GROUP BY b; +----------------+ | MIN(a) | +----------------+ | 0x30 | | 0x31 | +----------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT MIN(a) FROM t1 IGNORE INDEX(i1) GROUP BY b; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT MIN(a) FROM t1 IGNORE INDEX(i1) GROUP BY b; +----------------+ | MIN(a) | +----------------+ | 0x00 | | 0x01 | +----------------+ 2 rows in set (0.00 sec) mysql>
[16 Sep 2020 7:46]
MySQL Verification Team
- 5.7.31 looks okay mysql> EXPLAIN SELECT MIN(a) FROM t1 GROUP BY b; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | index | i1 | i1 | 5 | NULL | 2 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.03 sec) mysql> SELECT MIN(a) FROM t1 GROUP BY b; +--------+ | MIN(a) | +--------+ | 0 | | 1 | +--------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT MIN(a) FROM t1 IGNORE INDEX(i1) GROUP BY b; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | i1 | NULL | NULL | NULL | 2 | 100.00 | Using temporary; Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT MIN(a) FROM t1 IGNORE INDEX(i1) GROUP BY b; +--------+ | MIN(a) | +--------+ | | | | +--------+ 2 rows in set (0.00 sec)
[30 Nov 2020 12:47]
Hope Lee
Aggregation result of MySQL BIT type is wrongly integer (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-Bugfix-Aggregation-result-of-MySQL-BIT-type-is-wrong.patch (application/octet-stream, text), 5.45 KiB.
[13 Sep 2021 19:12]
Jon Stephens
Documented fix in the MySQL 8.0.28 changelog, as follows:
A query using aggregation on a BIT type could return different
results depending on the indexes or join type employed. This was
due to the fact that a DML using such an aggregation caches the
BIT values using an integer type, and later looks up and
converts to a string format for output. The current issue arose
because this lookup treated the BIT value as an integer,
resulting in an incorrect string value.
This is fixed by adding a new internal class for cached BIT
values which can convert bit values to string formats correctly.
Our thanks to Hope Lee for the contribution.
Closed.
[22 Jan 2022 1:05]
Bradley Grainger
This may have introduced bug #106241.
