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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.