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:
None 
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
Description:
The results of aggregation function on BIT type are inconsistent when the optimizer chooses different join types.

How to repeat:
CREATE TABLE t1(a BIT, b INT);

INSERT INTO t1 VALUES (b'0', 0), (b'1', 1);

CREATE INDEX i1 ON t1(b);

> 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)

> SELECT MIN(a) FROM t1 GROUP BY b;
+----------------+
| MIN(a)         |
+----------------+
| 0x30           |
| 0x31           |
+----------------+
2 rows in set (0.00 sec)

> 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)

> SELECT MIN(a) FROM t1 IGNORE INDEX(i1) GROUP BY b;
+----------------+
| MIN(a)         |
+----------------+
| 0x00           |
| 0x01           |
+----------------+
2 rows in set (0.00 sec)

I think the result below is correct. The result using index scan is wrong.
[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.