MySQL Bugs: #82308: use of DISTINCT with BIT and LONGTEXT leads to erroneous results

Bug #82308	use of DISTINCT with BIT and LONGTEXT leads to erroneous results
Submitted:	21 Jul 2016 14:54	Modified:	21 Jul 2016 17:39
Reporter:	Andreas Heinzel	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	bit, distinct, longtext

Description:
Using DISTINCT in a subquery creating a projection of BIT and LONGTEXT columns leads to erroneous results (bits become active in the result set). 

eq:
table bit_test has two columns attr_bit(BIT) and attr_text (LONGTEXT)

SELECT CAST(attr_bit AS UNSIGNED) AS attr_bit_as_uint, attr_text
FROM(
	SELECT DISTINCT *
	FROM bit_test
) a;

result set: column attr_bit_as_uint contains only numerical ones.

------------------------------------------------
environment:
mysql client:
#mysql --version
mysql  Ver 14.14 Distrib 5.7.12, for Linux (x86_64) using  EditLine wrapper

mysql server:
#SHOW VARIABLES LIKE '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.12                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.12                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+

server host os:
Ubuntu 14.04.4 LTS 
Linux xxx 3.13.0-32-generic #57-Ubuntu SMP Tue Jul 15 03:51:08 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

How to repeat:
CREATE TABLE bit_test(
       attr_bit BIT,
       attr_text LONGTEXT
);

INSERT INTO bit_test
VALUES
(0, 't1'),
(1, 't2'),
(1, 't3'),
(1, 't3'),
(0, 't3');

SELECT CAST(attr_bit AS UNSIGNED), attr_text
FROM(
	SELECT DISTINCT *
	FROM bit_test
) a;

#expected:
#0, t1
#1, t2
#1, t3
#0, t3

#actual:
#+----------------------------+-----------+
#| CAST(attr_bit AS UNSIGNED) | attr_text |
#+----------------------------+-----------+
#|                          1 | t1        |
#|                          1 | t2        |
#|                          1 | t3        |
#|                          1 | t3        |
#+----------------------------+-----------+

####################################
#     some additional interesting observations   #
####################################

#selection on attr_bit in the outer query fails:
SELECT CAST(attr_bit AS UNSIGNED), attr_text
FROM(
	SELECT DISTINCT *
	FROM bit_test
) a
WHERE a.attr_bit = 0; #alternatively a.attr_bit = (0) OR a.attr_bit = b'0'
#returns the four rows as the query above

SELECT CAST(attr_bit AS UNSIGNED), attr_text
FROM(
	SELECT DISTINCT *
	FROM bit_test
) a
WHERE a.attr_bit = 1;
#empty result set

####################
#selection issue was also observed on a similar table table with VARCHAR(255) instead of LONGTEXT
CREATE TABLE bit_test_varchar(
       attr_bit BIT,
       attr_text VARCHAR(255)
);

INSERT INTO bit_test_varchar
VALUES
(0, 't1'),
(1, 't2'),
(1, 't3'),
(1, 't3'),
(0, 't3');

#works
SELECT CAST(attr_bit AS UNSIGNED), attr_text
FROM(
	SELECT DISTINCT *
	FROM bit_test_varchar
) a;

#+----------------------------+-----------+
#| CAST(attr_bit AS UNSIGNED) | attr_text |
#+----------------------------+-----------+
#|                          0 | t1        |
#|                          1 | t2        |
#|                          1 | t3        |
#|                          0 | t3        |
#+----------------------------+-----------+

#however selection on attr_bit in outer select still produces unexpected result
SELECT CAST(attr_bit AS UNSIGNED), attr_text
FROM(
	SELECT DISTINCT *
	FROM bit_test_varchar
) a
WHERE a.attr_bit = b'0';

#+----------------------------+-----------+
#| CAST(attr_bit AS UNSIGNED) | attr_text |
#+----------------------------+-----------+
#|                          0 | t1        |
#+----------------------------+-----------+

#even though
SELECT a.attr_bit = b'0', attr_text
FROM(
	SELECT DISTINCT *
	FROM bit_test_varchar
) a;

#+-------------------+-----------+
#| a.attr_bit = b'0' | attr_text |
#+-------------------+-----------+
#|                 1 | t1        |
#|                 0 | t2        |
#|                 0 | t3        |
#|                 1 | t3        |
#+-------------------+-----------+

actually the error is not limited to subqueries (in the example the sq is only necessary to ensure that elimination of duplicates is performed on bit values)

How to repeat:
mysql>
CREATE TABLE bit_test(
       attr_bit BIT,
       attr_text LONGTEXT
);

INSERT INTO bit_test
VALUES
(0, 't1'),
(1, 't2'),
(1, 't3'),
(1, 't3'),
(0, 't3');

shell>
mysql -e"SELECT DISTINCT * FROM bit_test" TEST_DB | cat -evt
#expected:
attr_bit^Iattr_text$
#\0^It1$
#^A^It2$
#^A^It3$
#\0^It3$

#actual
#attr_bit^Iattr_text$
#^A^It1$
#^A^It2$
#^A^It3$
#^A^It3$

mysql 5.7 > use test
Database changed
mysql 5.7 > CREATE TABLE bit_test(
    ->        attr_bit BIT,
    ->        attr_text LONGTEXT
    -> );
Query OK, 0 rows affected (0.35 sec)

mysql 5.7 >
mysql 5.7 > INSERT INTO bit_test
    -> VALUES
    -> (0, 't1'),
    -> (1, 't2'),
    -> (1, 't3'),
    -> (1, 't3'),
    -> (0, 't3');
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql 5.7 >
mysql 5.7 > SELECT CAST(attr_bit AS UNSIGNED), attr_text
    -> FROM(
    ->  SELECT DISTINCT *
    ->  FROM bit_test
    -> ) a;
+----------------------------+-----------+
| CAST(attr_bit AS UNSIGNED) | attr_text |
+----------------------------+-----------+
|                          0 | t1        |
|                          1 | t2        |
|                          1 | t3        |
|                          0 | t3        |
+----------------------------+-----------+
4 rows in set (0.03 sec)

mysql 5.7 > show variables like "%version%";
+-------------------------+---------------------------------+
| Variable_name           | Value                           |
+-------------------------+---------------------------------+
| innodb_version          | 5.7.15                          |
| protocol_version        | 10                              |
| slave_type_conversions  |                                 |
| tls_version             | TLSv1,TLSv1.1                   |
| version                 | 5.7.15                          |
| version_comment         | Source distribution 2016-JUL-09 |
| version_compile_machine | x86_64                          |
| version_compile_os      | Win64                           |
+-------------------------+---------------------------------+
8 rows in set (0.06 sec)

Are the prior and below results the expected ones?

mysql 5.7 > SELECT CAST(attr_bit AS UNSIGNED), attr_text
    -> FROM(
    ->  SELECT DISTINCT *
    ->  FROM bit_test
    -> ) a
    -> WHERE a.attr_bit = 0; #alternatively a.attr_bit = (0) OR a.attr_bit = b'0';
+----------------------------+-----------+
| CAST(attr_bit AS UNSIGNED) | attr_text |
+----------------------------+-----------+
|                          0 | t1        |
|                          0 | t3        |
+----------------------------+-----------+
2 rows in set (0.03 sec)

mysql 5.7 > SELECT CAST(attr_bit AS UNSIGNED), attr_text
    -> FROM(
    ->  SELECT DISTINCT *
    ->  FROM bit_test
    -> ) a
    -> WHERE a.attr_bit = 1;
+----------------------------+-----------+
| CAST(attr_bit AS UNSIGNED) | attr_text |
+----------------------------+-----------+
|                          1 | t2        |
|                          1 | t3        |
+----------------------------+-----------+
2 rows in set (0.00 sec)

Thanks.

Thank you Miguel for looking into this.
Yes the above results are the expected ones.

In the meantime I upgraded one of our test environments to 5.7.13 and also executed "the test" on a fresh install of 5.7.13. While, on the fresh install everything is fine, we still see the same issue in our test environment. I assume it is caused by our configuration - will look into it and report back.

Thank you for the feedback. Please comment when you have a repeatable test case. Thanks.

I'm sorry I forgot a very important detail - we are still using MyISAM as default storage engine.

Explicitly specifying the storage engine allows me to reproduce the issue. Can you please rerun the test with ENGINE = MyISAM
CREATE TABLE bit_test(
       attr_bit BIT,
       attr_text LONGTEXT
) ENGINE = MyISAM;

INSERT INTO bit_test
VALUES
(0, 't1'),
(1, 't2'),
(1, 't3'),
(1, 't3'),
(0, 't3');

SELECT CAST(attr_bit AS UNSIGNED), attr_text
FROM(
	SELECT DISTINCT *
	FROM bit_test
) a;

Thank you for the feedback.

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

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 > SHOW CREATE TABLE bit_test;
+----------+---------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                              |
+----------+---------------------------------------------------------------------------------------------------------------------------+
| bit_test | CREATE TABLE `bit_test` (
  `attr_bit` bit(1) DEFAULT NULL,
  `attr_text` longtext
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

mysql 5.7 > SELECT CAST(attr_bit AS UNSIGNED), attr_text
    -> FROM(
    ->  SELECT DISTINCT *
    ->  FROM bit_test
    -> ) a;
+----------------------------+-----------+
| CAST(attr_bit AS UNSIGNED) | attr_text |
+----------------------------+-----------+
|                          0 | t1        |
|                          1 | t2        |
|                          1 | t3        |
|                          0 | t3        |
+----------------------------+-----------+
4 rows in set (0.05 sec)

mysql 5.7 > ALTER TABLE bit_test ENGINE MyISAM;
Query OK, 5 rows affected (0.63 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql 5.7 > SELECT CAST(attr_bit AS UNSIGNED), attr_text
    -> FROM(
    ->  SELECT DISTINCT *
    ->  FROM bit_test
    -> ) a;
+----------------------------+-----------+
| CAST(attr_bit AS UNSIGNED) | attr_text |
+----------------------------+-----------+
|                          1 | t1        |
|                          1 | t2        |
|                          1 | t3        |
|                          1 | t3        |
+----------------------------+-----------+
4 rows in set (0.00 sec)

mysql 5.7 >

Just FYI, it works fine with InnoDB storage engine ....

We were affected by this issue too.
What we've observed is this:

# 5.7.6-m16 - KO
# 5.7.5 - OK

it's was working OK on 5.7.5, it's not okay 5.7.6-m16

converted tables into InnoDB and issue is gone.