MySQL Bugs: #84149: select least(bit, bit) and select bit return inconsistent results

Bug #84149	select least(bit, bit) and select bit return inconsistent results
Submitted:	9 Dec 2016 12:29	Modified:	9 Jan 2017 20:08
Reporter:	帅 Bang	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(c1 bit(64));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(97),(98),(99);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select c1 from t1;
+----------+
| c1       |
+----------+
|        a |
|        b |
|        c |
+----------+
3 rows in set (0.00 sec)

mysql> select least(c1,c1) from t1;
+--------------+
| least(c1,c1) |
+--------------+
| 97           |
| 98           |
| 99           |
+--------------+
3 rows in set (0.00 sec)

IMHO, the results are inconsistent. Why ?

Let us dig into this to see what happened.

connect to mysql server with option --column-type-info, just like this:

mysql -uroot -hIP -PPORT --column-type-info;

Again, we get:

mysql> select c1 from t1;
Field   1:  `c1`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       BIT //please note here !!! yeah, bit type!!!
Collation:  binary (63)
Length:     64
Max_length: 8
Decimals:   0
Flags:      UNSIGNED

+----------+
| c1       |
+----------+
|        a |
|        b |
|        c |
+----------+
3 rows in set (0.00 sec)

mysql> select least(c1,c1) from t1;
Field   1:  `least(c1,c1)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       BIT//also note here please !!! bit type !!!
Collation:  binary (63)
Length:     65
Max_length: 2
Decimals:   0
Flags:      BINARY

+--------------+
| least(c1,c1) |
+--------------+
| 97           |
| 98           |
| 99           |
+--------------+
3 rows in set (0.00 sec)

As can be seen that both of  the  types of the 2 statements returned are bit while they have inconsistent output rows. 

How to repeat:
drop table t1;
create table t1(c1 bit(64));
insert into t1 values(97),(98),(99);
select c1 from t1;
select least(c1,c1) from t1;

Suggested fix:
both select c1 from t1 and select least(c1,c1) from t1 return 97 98 99

or

both select c1 from t1 and select least(c1,c1) from t1 return a b c (highly recommended)

5 years ago I wrote this https://blog.webyog.com/a-little-bit-about-bit-again/

"In every respect a BIT behaves as BIGINT UNSIGNED unless it is fetched directly from storage to a client" .. and I think this report is just another example of this.

-- Peter
-- not a MySQL/Oracle person.

Thank you for the bug report. Please see the below Manual note about using BIT in numerics context:

c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.18 Source distribution PULL: 2016-NOV-20

Copyright (c) 2000, 2016, 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 5.7 > USE test
Database changed
mysql 5.7 > drop table t1;
Query OK, 0 rows affected (0.16 sec)

mysql 5.7 > create table t1(c1 bit(64));
Query OK, 0 rows affected (0.28 sec)

mysql 5.7 > insert into t1 values(97),(98),(99);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

https://dev.mysql.com/doc/refman/5.7/en/bit-value-literals.html

"In numeric contexts, MySQL treats a bit literal like an integer. To ensure numeric treatment of a bit literal, use it in numeric context. Ways to do this include adding 0 or using CAST(... AS UNSIGNED). For example,..." 

mysql 5.7 > select c1+0 from t1;
+------+
| c1+0 |
+------+
|   97 |
|   98 |
|   99 |
+------+
3 rows in set (0.00 sec)

mysql 5.7 > select least(c1,c1) from t1;
+--------------+
| least(c1,c1) |
+--------------+
| 97           |
| 98           |
| 99           |
+--------------+
3 rows in set (0.00 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".