MySQL Bugs: #101801: Client parses results of long long type as bit type and displays wrong results

Bug #101801	Client parses results of long long type as bit type and displays wrong results
Submitted:	30 Nov 2020 12:34	Modified:	30 Nov 2020 14:55
Reporter:	Hope Lee (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	8.0.22	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
The latest MySQL client can recognize MYSQL_TYPE_BIT field and display the result in the hexadecimal format. This feature is convenient in many cases for displaying data of BIT type well. But in some cases when the metadata m_data_type of result is MYSQL_TYPE_BIT, but the Item_result type is INT_RESULT, it will bring regression of result mismatch.

How to repeat:
Try the following SQL:
CREATE TABLE t1(a BIT(3), b BIT(9), c INT, d INT);
INSERT INTO t1 VALUES (b'111', b'111110101', 0, 1), (b'000', b'010000101', 1, 1);

Actual result:
mysql 8.0.22 > SELECT MAX(CASE WHEN c = 0 THEN a ELSE b END) FROM t1 GROUP BY d;
+--------------------------------------------------------------------------------+
| MAX(CASE WHEN c = 0 THEN a ELSE b END)                                         |
+--------------------------------------------------------------------------------+
| 0x313333                                                                       |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Expected result:
mysql 8.0.16 > SELECT MAX(CASE WHEN c = 0 THEN a ELSE b END) FROM t1 GROUP BY d;
+----------------------------------------+
| MAX(CASE WHEN c = 0 THEN a ELSE b END) |
+----------------------------------------+
| 133                                    |
+----------------------------------------+
1 row in set (0.00 sec)

Actually, the result's bytes returned by MySQL server is all right. The 0x313333 represents the string of integer '133'. But the MySQL client with the latest feature wrongly regard this as BIT type and displays as above.

The MySQL client of older version doesn't have this issue(I have tried with version 8.0.16).

Suggested fix:
This issue is caused by the unequal relations between Item_result types and enum_field_types types. In function Item_func_case::resolve_type(), the server sets the the m_data_type to MYSQL_TYPE_BIT, while setting cached_result_type to INT_RESULT. And then the server calculates and returns the result of this expression as an integer, but sets the data type of results' metadata to MYSQL_TYPE_BIT. This makes the client recognize the return value in BIT format.

We can have the two following possible fix ways:
1. When sending the metadata of the result to the client, make the m_data_type metadata consistent with Item_result returned by Item::result_type(). If the result_type() is INT_RESULT, set the m_data_type to MYSQL_TYPE_LONGLONG instead.
2. We can change the enum_field_types of field_types_merge_rules[MYSQL_TYPE_BIT][MYSQL_TYPE_BIT] returned by Field::field_type_merge() from MYSQL_TYPE_BIT to MYSQL_TYPE_LONGLONG. The first reason is that the server calculates the expressions involved MYSQL_TYPE_BIT as INT_RESULT. The second reason is that it's hard to tell the result's bit format of two MYSQL_TYPE_BIT in different formats.

Hello Hope Lee ,

Thank you for the report and feedback.
I'm not seeing any issues at my end while checking on 8.0.22. Am I missing anything? Please let us know.

-
 bin/mysql -uroot -S /tmp/mysql_ushastry.sock --column-type-info
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.22 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.02 sec)

mysql> use test
Database changed
mysql> CREATE TABLE t1(a BIT(3), b BIT(9), c INT, d INT);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t1 VALUES (b'111', b'111110101', 0, 1), (b'000', b'010000101', 1, 1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT MAX(CASE WHEN c = 0 THEN a ELSE b END) FROM t1 GROUP BY d;
Field   1:  `MAX(CASE WHEN c = 0 THEN a ELSE b END)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     9
Max_length: 3
Decimals:   0
Flags:      UNSIGNED NUM

+----------------------------------------+
| MAX(CASE WHEN c = 0 THEN a ELSE b END) |
+----------------------------------------+
|                                    133 |
+----------------------------------------+
1 row in set (0.00 sec)

regards,
Umesh

Sorry, that's my fault to leave out something. Can you add an index and try again? Using the following SQL:
CREATE INDEX i1 ON t1(d);

root@localhost:test 8.0.22-rds-dev-debug> EXPLAIN SELECT MAX(CASE WHEN c = 0 THEN a ELSE b END) FROM t1 GROUP BY d;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+
| 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)

root@localhost:test 8.0.22-rds-dev-debug> SELECT MAX(CASE WHEN c = 0 THEN a ELSE b END) FROM t1 GROUP BY d;
+--------------------------------------------------------------------------------+
| MAX(CASE WHEN c = 0 THEN a ELSE b END)                                         |
+--------------------------------------------------------------------------------+
| 0x313333                                                                       |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Thank you, Lee.
Verified as described.

regards,
Umesh

Mmmm, something even more strange is when you use MySQL Shell:

Without index:

 SQL  SELECT MAX(CASE WHEN c = 0 THEN a ELSE b END) FROM test.t1 GROUP BY d;
+----------------------------------------+
| MAX(CASE WHEN c = 0 THEN a ELSE b END) |
+----------------------------------------+
|                                    133 |
+----------------------------------------+

With the index:

 SQL  SELECT MAX(CASE WHEN c = 0 THEN a ELSE b END) FROM test.t1 GROUP BY d;
+----------------------------------------+
| MAX(CASE WHEN c = 0 THEN a ELSE b END) |
+----------------------------------------+
| 307                                    |
+----------------------------------------+