Description:
Seen on 5.6.33 and 5.7.17.
The output shown when using an order by changes as the values get converted to a different type.
How to repeat:
consider this table:
mysql> show create table BitTest\G
*************************** 1. row ***************************
Table: BitTest
Create Table: CREATE TABLE `BitTest` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`bit_column` bit(16) NOT NULL DEFAULT b'0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=95861 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select count(*) from BitTest;
+----------+
| count(*) |
+----------+
| 95860 |
+----------+
1 row in set (0.02 sec)
So we have various values here.
Note the answer when looking for distinct values:
mysql> SELECT DISTINCT bit_column from BitTest;
+------------+
| bit_column |
+------------+
| 1 |
| 4 |
| 0 |
| 8 |
| 2 |
| 16 |
| 7 |
| 5 |
| 6 |
| 3 |
| |
+------------+
11 rows in set (0.05 sec)
mysql> SELECT DISTINCT bit_column from BitTest order by 1;
+------------+
| bit_column |
+------------+
| 0 |
| 48 |
| 49 |
| 50 |
| 51 |
| 52 |
| 53 |
| 54 |
| 55 |
| 56 |
| 12598 |
+------------+
11 rows in set (0.05 sec)
Note: the first output set are showing "character values", the second select output shows the numeric equivalent of those values.
This can be more easily shown by:
mysql> SELECT DISTINCT bit_column, 0+bit_column, CHAR(0+bit_column), UNHEX(HEX(bit_column)) FROM BitTest;
+------------+--------------+--------------------+------------------------+
| bit_column | 0+bit_column | CHAR(0+bit_column) | UNHEX(HEX(bit_column)) |
+------------+--------------+--------------------+------------------------+
| 1 | 49 | 1 | 1 |
| 4 | 52 | 4 | 4 |
| 0 | 48 | 0 | 0 |
| 8 | 56 | 8 | 8 |
| 2 | 50 | 2 | 2 |
| 16 | 12598 | 16 | 16 |
| 7 | 55 | 7 | 7 |
| 5 | 53 | 5 | 5 |
| 6 | 54 | 6 | 6 |
| 3 | 51 | 3 | 3 |
| | 0 | | |
+------------+--------------+--------------------+------------------------+
11 rows in set (0.09 sec)
Suggested fix:
[ Background: The cause of this was noted as input values were inserted incorrectly as character values e.g. "1" instead of 1. MySQL thus stores the value 49 as a bit string. So "1" = 31H in hex, 49 decimal or 0011 0001 in binary. ]
I would expect the order by to NOT affect the display output, so the "order by 1" here is modifying the way the data is presented.
Description: Seen on 5.6.33 and 5.7.17. The output shown when using an order by changes as the values get converted to a different type. How to repeat: consider this table: mysql> show create table BitTest\G *************************** 1. row *************************** Table: BitTest Create Table: CREATE TABLE `BitTest` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `bit_column` bit(16) NOT NULL DEFAULT b'0', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=95861 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select count(*) from BitTest; +----------+ | count(*) | +----------+ | 95860 | +----------+ 1 row in set (0.02 sec) So we have various values here. Note the answer when looking for distinct values: mysql> SELECT DISTINCT bit_column from BitTest; +------------+ | bit_column | +------------+ | 1 | | 4 | | 0 | | 8 | | 2 | | 16 | | 7 | | 5 | | 6 | | 3 | | | +------------+ 11 rows in set (0.05 sec) mysql> SELECT DISTINCT bit_column from BitTest order by 1; +------------+ | bit_column | +------------+ | 0 | | 48 | | 49 | | 50 | | 51 | | 52 | | 53 | | 54 | | 55 | | 56 | | 12598 | +------------+ 11 rows in set (0.05 sec) Note: the first output set are showing "character values", the second select output shows the numeric equivalent of those values. This can be more easily shown by: mysql> SELECT DISTINCT bit_column, 0+bit_column, CHAR(0+bit_column), UNHEX(HEX(bit_column)) FROM BitTest; +------------+--------------+--------------------+------------------------+ | bit_column | 0+bit_column | CHAR(0+bit_column) | UNHEX(HEX(bit_column)) | +------------+--------------+--------------------+------------------------+ | 1 | 49 | 1 | 1 | | 4 | 52 | 4 | 4 | | 0 | 48 | 0 | 0 | | 8 | 56 | 8 | 8 | | 2 | 50 | 2 | 2 | | 16 | 12598 | 16 | 16 | | 7 | 55 | 7 | 7 | | 5 | 53 | 5 | 5 | | 6 | 54 | 6 | 6 | | 3 | 51 | 3 | 3 | | | 0 | | | +------------+--------------+--------------------+------------------------+ 11 rows in set (0.09 sec) Suggested fix: [ Background: The cause of this was noted as input values were inserted incorrectly as character values e.g. "1" instead of 1. MySQL thus stores the value 49 as a bit string. So "1" = 31H in hex, 49 decimal or 0011 0001 in binary. ] I would expect the order by to NOT affect the display output, so the "order by 1" here is modifying the way the data is presented.