Description:
Queries using LIKE clauses against SET type columns in NDB do not return correct results.
How to repeat:
mysql> create table t1 ( a int unsigned auto_increment primary key, b set ('one','two','three')) engine=ndb;
Query OK, 0 rows affected (2.90 sec)
mysql> insert into t1 (b) values ('one'),('two'),('three');
Query OK, 3 rows affected (0.32 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1 where b like 'one'; (FAIL)
Empty set (0.09 sec)
mysql> select * from t1 where b like '%one%'; (FAIL)
Empty set (0.00 sec)
mysql> select * from t1 where b = 'one'; (PASS)
+---+------+
| a | b |
+---+------+
| 1 | one |
+---+------+
1 row in set (0.01 sec)
mysql> alter table t1 engine=myisam;
Query OK, 3 rows affected (1.23 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1 where b like 'one'; (PASS)
+---+------+
| a | b |
+---+------+
| 1 | one |
+---+------+
1 row in set (0.01 sec)
mysql> select * from t1 where b like '%one%'; (PASS)
+---+------+
| a | b |
+---+------+
| 1 | one |
+---+------+
1 row in set (0.00 sec)
mysql> select * from t1 where b = 'one'; (PASS)
+---+------+
| a | b |
+---+------+
| 1 | one |
+---+------+
1 row in set (0.00 sec)
mysql> alter table t1 engine=innodb;
Query OK, 3 rows affected (0.99 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1 where b like 'one'; (PASS)
+---+------+
| a | b |
+---+------+
| 1 | one |
+---+------+
1 row in set (0.00 sec)
mysql> select * from t1 where b like '%one%'; (PASS)
+---+------+
| a | b |
+---+------+
| 1 | one |
+---+------+
1 row in set (0.00 sec)
mysql> select * from t1 where b = 'one'; (PASS)
+---+------+
| a | b |
+---+------+
| 1 | one |
+---+------+
1 row in set (0.00 sec)
Suggested fix:
.
Description: Queries using LIKE clauses against SET type columns in NDB do not return correct results. How to repeat: mysql> create table t1 ( a int unsigned auto_increment primary key, b set ('one','two','three')) engine=ndb; Query OK, 0 rows affected (2.90 sec) mysql> insert into t1 (b) values ('one'),('two'),('three'); Query OK, 3 rows affected (0.32 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t1 where b like 'one'; (FAIL) Empty set (0.09 sec) mysql> select * from t1 where b like '%one%'; (FAIL) Empty set (0.00 sec) mysql> select * from t1 where b = 'one'; (PASS) +---+------+ | a | b | +---+------+ | 1 | one | +---+------+ 1 row in set (0.01 sec) mysql> alter table t1 engine=myisam; Query OK, 3 rows affected (1.23 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t1 where b like 'one'; (PASS) +---+------+ | a | b | +---+------+ | 1 | one | +---+------+ 1 row in set (0.01 sec) mysql> select * from t1 where b like '%one%'; (PASS) +---+------+ | a | b | +---+------+ | 1 | one | +---+------+ 1 row in set (0.00 sec) mysql> select * from t1 where b = 'one'; (PASS) +---+------+ | a | b | +---+------+ | 1 | one | +---+------+ 1 row in set (0.00 sec) mysql> alter table t1 engine=innodb; Query OK, 3 rows affected (0.99 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t1 where b like 'one'; (PASS) +---+------+ | a | b | +---+------+ | 1 | one | +---+------+ 1 row in set (0.00 sec) mysql> select * from t1 where b like '%one%'; (PASS) +---+------+ | a | b | +---+------+ | 1 | one | +---+------+ 1 row in set (0.00 sec) mysql> select * from t1 where b = 'one'; (PASS) +---+------+ | a | b | +---+------+ | 1 | one | +---+------+ 1 row in set (0.00 sec) Suggested fix: .