MySQL Bugs: #79179: bit data type was not full supported by MySQL cluster

Bug #79179	bit data type was not full supported by MySQL cluster
Submitted:	9 Nov 2015 1:31	Modified:	9 Nov 2015 13:58
Reporter:	tim tim	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	7.4.7	OS:	CentOS (6.5 x64)
Assigned to:		CPU Architecture:	Any

Description:
I create a tale in MySQL cluster 7.4.7，and one column is bit data type.
when I use sub-query in a  SQL statement with the ‘where’ condition on the bit data type,the query result is not the excepted.

My table script is: 

CREATE TABLE `rt_urls` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `serverid` varchar(50) NOT NULL COMMENT 'server ID',
  `s_check_urls_id` int(11) NOT NULL COMMENT ‘setting置ID',
  `url` varchar(255) NOT NULL COMMENT 'access  Url',
  `enabled` bit(1) NOT NULL DEFAULT b'0' ,
  `msg` varchar(2000) DEFAULT NULL,
  `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster AUTO_INCREMENT=8533 DEFAULT CHARSET=utf8

 
And the query statement and result is below:
In firt statement,I use the sub query,the result is empty  
mysql>  select * from (select id,enabled from rt_urls) as t where enabled=1;
Empty set (0.00 sec) 

In the second statement,I don’t use subquery.
mysql> select id,enabled from rt_urls where enabled=1;
+----+---------+

| id | enabled |

+----+---------+

|  1 |        |

|  2 |        |

|  4 |        |

+----+---------+

3 rows in set (0.00 sec)
notice:the "enabled" is not null actually,it's 1 in bit type.

When I change the data type of column ‘enable’ to Int,the problem will gone.
So what happened in MySQL cluster,does it a bug?

How to repeat:
 I change the data type of column ‘enable’ to Int,the problem will gone.
also if i use innodb,the problem will disappear too.

mysql> use t1;
Database changed
mysql> CREATE LOGFILE GROUP logfile_group ADD UNDOFILE 'undo_file' engine=ndbcluster;
Query OK, 0 rows affected (4.42 sec)

mysql> CREATE TABLESPACE ts_1 ADD DATAFILE 'ts1' USE LOGFILE GROUP logfile_group  engine=ndbcluster;
Query OK, 0 rows affected (4.50 sec)

mysql> CREATE TABLE `rt_urls` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
    ->   `serverid` varchar(50) NOT NULL COMMENT 'server ID',
    ->   `s_check_urls_id` int(11) NOT NULL COMMENT 'setting ID',
    ->   `url` varchar(255) NOT NULL COMMENT 'access  Url',
    ->   `enabled` bit(1) NOT NULL DEFAULT b'0' ,
    ->   `msg` varchar(2000) DEFAULT NULL,
    ->   `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`id`)
    -> ) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster AUTO_INCREMENT=8533 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.76 sec)

mysql> create database t2;
Query OK, 1 row affected (0.07 sec)

mysql> use t2;
Database changed
mysql> CREATE TABLE `rt_urls` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
    ->   `serverid` varchar(50) NOT NULL COMMENT 'server ID',
    ->   `s_check_urls_id` int(11) NOT NULL COMMENT 'setting ID',
    ->   `url` varchar(255) NOT NULL COMMENT 'access  Url',
    ->   `enabled` bit(1) NOT NULL DEFAULT b'0',
    ->   `msg` varchar(2000) DEFAULT NULL,
    ->   `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`id`)
    -> ) engine=innodb AUTO_INCREMENT=8533 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.26 sec)

mysql> insert into rt_urls values (null, 'a1', 1, 'b1', b'1', 'message', now());
Query OK, 1 row affected (0.10 sec)

mysql> insert into rt_urls values (null, 'a2', 2, 'b2', b'1', 'message', now());
Query OK, 1 row affected (0.06 sec)

mysql> insert into rt_urls values (null, 'a3', 3, 'b3', b'1', 'message', now());
Query OK, 1 row affected (0.07 sec)

mysql> insert into rt_urls values (null, 'a4', 4, 'b4', b'1', 'message', now());
Query OK, 1 row affected (0.06 sec)

mysql> insert into rt_urls values (null, 'a5', 5, 'b5', b'1', 'message', now());
Query OK, 1 row affected (0.04 sec)

mysql> insert into rt_urls values (null, 'a6', 6, 'b6', b'1', 'message', now());
Query OK, 1 row affected (0.03 sec)

mysql> insert into rt_urls values (null, 'a7', 7, 'b7', b'0', 'message', now());
Query OK, 1 row affected (0.03 sec)

mysql> insert into rt_urls values (null, 'a8', 8, 'b8', b'1', 'message', now());
Query OK, 1 row affected (0.04 sec)

mysql> insert into rt_urls values (null, 'a9', 9, 'b9', b'1', 'message', now());
Query OK, 1 row affected (0.03 sec)

mysql> select * from (select id,enabled from rt_urls) as t where enabled=1;
+------+---------+
| id   | enabled |
+------+---------+
| 8540 |        |
| 8541 |        |
| 8533 |        |
| 8534 |        |
| 8535 |        |
| 8536 |        |
| 8537 |        |
| 8538 |        |
+------+---------+
8 rows in set (0.00 sec)

mysql> select id,enabled from rt_urls where enabled=1;
+------+---------+
| id   | enabled |
+------+---------+
| 8533 |        |
| 8534 |        |
| 8535 |        |
| 8536 |        |
| 8537 |        |
| 8538 |        |
| 8540 |        |
| 8541 |        |
+------+---------+
8 rows in set (0.00 sec)

mysql> use t1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into rt_urls values (null, 'a1', 1, 'b1', b'1', 'message', now());
Query OK, 1 row affected (0.01 sec)

mysql> insert into rt_urls values (null, 'a2', 2, 'b2', b'1', 'message', now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into rt_urls values (null, 'a3', 3, 'b3', b'1', 'message', now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into rt_urls values (null, 'a4', 4, 'b4', b'1', 'message', now());
Query OK, 1 row affected (0.01 sec)

mysql> insert into rt_urls values (null, 'a5', 5, 'b5', b'1', 'message', now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into rt_urls values (null, 'a6', 6, 'b6', b'1', 'message', now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into rt_urls values (null, 'a7', 7, 'b7', b'0', 'message', now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into rt_urls values (null, 'a8', 8, 'b8', b'1', 'message', now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into rt_urls values (null, 'a9', 9, 'b9', b'1', 'message', now());
Query OK, 1 row affected (0.00 sec)

mysql> select id,enabled from rt_urls where enabled=1;
+------+---------+
| id   | enabled |
+------+---------+
| 8535 |        |
| 8538 |        |
| 8533 |        |
| 8534 |        |
| 8536 |        |
| 8537 |        |
| 8540 |        |
| 8541 |        |
+------+---------+
8 rows in set (0.00 sec)

mysql> select * from (select id,enabled from rt_urls) as t where enabled=1;
Empty set (0.00 sec)

mysql>

Hi Tim,

Thanks for submitting the bug, I verified it on latest 7.4.8 MySQL Cluster

kind regards
Bogdan Kecman

mysql> select * from (select id,enabled from rt_urls) as t;
Field   1:  `id`
Catalog:    `def`
Database:   ``
Table:      `t`
Org_table:  `rt_urls`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 4
Decimals:   0
Flags:      NOT_NULL NUM

Field   2:  `enabled`
Catalog:    `def`
Database:   ``
Table:      `t`
Org_table:  `rt_urls`
Type:       BIT
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL UNSIGNED

+------+---------+
| id   | enabled |
+------+---------+
| 8535 |        |
| 8538 |        |
| 8533 |        |
| 8534 |        |
| 8536 |        |
| 8537 |        |
| 8539 |         |
| 8540 |        |
| 8541 |        |
+------+---------+
9 rows in set (0.00 sec)

mysql>

mysql> select *, hex(enabled) from (select id,enabled from rt_urls) as t;
Field   1:  `id`
Catalog:    `def`
Database:   ``
Table:      `t`
Org_table:  `rt_urls`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 4
Decimals:   0
Flags:      NOT_NULL NUM

Field   2:  `enabled`
Catalog:    `def`
Database:   ``
Table:      `t`
Org_table:  `rt_urls`
Type:       BIT
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL UNSIGNED

Field   3:  `hex(enabled)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     6
Max_length: 1
Decimals:   0
Flags:

+------+---------+--------------+
| id   | enabled | hex(enabled) |
+------+---------+--------------+
| 8533 |        | 1            |
| 8534 |        | 1            |
| 8536 |        | 1            |
| 8537 |        | 1            |
| 8539 |         | 0            |
| 8540 |        | 1            |
| 8541 |        | 1            |
| 8535 |        | 1            |
| 8538 |        | 1            |
+------+---------+--------------+
9 rows in set (0.02 sec)

mysql> use t2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select *, hex(enabled) from (select id,enabled from rt_urls) as t;
Field   1:  `id`
Catalog:    `def`
Database:   ``
Table:      `t`
Org_table:  `rt_urls`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 4
Decimals:   0
Flags:      NOT_NULL NUM

Field   2:  `enabled`
Catalog:    `def`
Database:   ``
Table:      `t`
Org_table:  `rt_urls`
Type:       BIT
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL UNSIGNED

Field   3:  `hex(enabled)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     6
Max_length: 1
Decimals:   0
Flags:

+------+---------+--------------+
| id   | enabled | hex(enabled) |
+------+---------+--------------+
| 8533 |        | 1            |
| 8534 |        | 1            |
| 8535 |        | 1            |
| 8536 |        | 1            |
| 8537 |        | 1            |
| 8538 |        | 1            |
| 8539 |         | 0            |
| 8540 |        | 1            |
| 8541 |        | 1            |
+------+---------+--------------+
9 rows in set (0.00 sec)

mysql>

Bug and all errors are fixed for the underlying stories for the field. Path of the field and top 3 essay writing services is suspected for the games. The turn is avoided for the mid of the sequential men’s for the candidates.
To visit the site click here: https://clubessay.com/top-essay-writing-services