Description:
mysql> drop table if exists tt1;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE `tt1` (
-> `char_col` char(4) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
-> `bit_col` bit(8) DEFAULT b'0',
-> KEY `ndx_bit_col` (`bit_col`),
-> FULLTEXT KEY `ndx_char_col` (`char_col`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 2 warnings (0.14 sec)
mysql> insert into tt1 values("aaaa", 0xF0),("aaaa", 0xAA),("aaaa", 0x99),("aaaa", 0x00),("aabb", 0xF0),("aabb", 0xAA),("aabb", 0x99),("aabb", 0x00),("aacd", 0xF0),("aacd", 0xAA),("aacd", 0x99),("aacd", 0x00),(NULL, 0xF0),(NULL, 0xAA),(NULL,0x99),(NULL, 0x00);
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> drop table if exists tt2;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE `tt2` (
-> `char_col` char(4) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
0xAA),("aabb", 0x99),("aabb", 0x00),("aacd", 0xF0),("aacd", 0xAA),("aacd", 0x99),("aacd", 0x00),(NULL, 0xF0),(NULL, 0xAA),(NULL,0x99),(NULL, 0x00); -> `bit_col` bit(8) DEFAULT b'0'
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> insert into tt2 values("aaaa", 0xF0),("aaaa", 0xAA),("aaaa", 0x99),("aaaa", 0x00),("aabb", 0xF0),("aabb", 0xAA),("aabb", 0x99),("aabb", 0x00),("aacd", 0xF0),("aacd", 0xAA),("aacd", 0x99),("aacd", 0x00),(NULL, 0xF0),(NULL, 0xAA),(NULL,0x99),(NULL, 0x00);
Query OK, 16 rows affected (0.01 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> SELECT count(*) FROM tt1 AS A force index(ndx_bit_col) right join tt2 AS B ON A.bit_col=B.char_col;
+----------+
| count(*) |
+----------+
| 16 |
+----------+
1 row in set (0.01 sec)
mysql> SELECT count(*) FROM tt1 AS A ignore index(ndx_bit_col) right join tt2 AS B ON A.bit_col=B.char_col;
+----------+
| count(*) |
+----------+
| 52 |
+----------+
1 row in set, 12 warnings (0.00 sec)
How to repeat:
drop table if exists tt1;
CREATE TABLE `tt1` (
`char_col` char(4) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`bit_col` bit(8) DEFAULT b'0',
KEY `ndx_bit_col` (`bit_col`),
FULLTEXT KEY `ndx_char_col` (`char_col`)
) ENGINE=InnoDB;
insert into tt1 values("aaaa", 0xF0),("aaaa", 0xAA),("aaaa", 0x99),("aaaa", 0x00),("aabb", 0xF0),("aabb", 0xAA),("aabb", 0x99),("aabb", 0x00),("aacd", 0xF0),("aacd", 0xAA),("aacd", 0x99),("aacd", 0x00),(NULL, 0xF0),(NULL, 0xAA),(NULL,0x99),(NULL, 0x00);
drop table if exists tt2;
CREATE TABLE `tt2` (
`char_col` char(4) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`bit_col` bit(8) DEFAULT b'0'
) ENGINE=InnoDB;
insert into tt2 values("aaaa", 0xF0),("aaaa", 0xAA),("aaaa", 0x99),("aaaa", 0x00),("aabb", 0xF0),("aabb", 0xAA),("aabb", 0x99),("aabb", 0x00),("aacd", 0xF0),("aacd", 0xAA),("aacd", 0x99),("aacd", 0x00),(NULL, 0xF0),(NULL, 0xAA),(NULL,0x99),(NULL, 0x00);
SELECT count(*) FROM tt1 AS A force index(ndx_bit_col) right join tt2 AS B ON A.bit_col=B.char_col; --with index
SELECT count(*) FROM tt1 AS A ignore index(ndx_bit_col) right join tt2 AS B ON A.bit_col=B.char_col; --without index
Description: mysql> drop table if exists tt1; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE `tt1` ( -> `char_col` char(4) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL, -> `bit_col` bit(8) DEFAULT b'0', -> KEY `ndx_bit_col` (`bit_col`), -> FULLTEXT KEY `ndx_char_col` (`char_col`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected, 2 warnings (0.14 sec) mysql> insert into tt1 values("aaaa", 0xF0),("aaaa", 0xAA),("aaaa", 0x99),("aaaa", 0x00),("aabb", 0xF0),("aabb", 0xAA),("aabb", 0x99),("aabb", 0x00),("aacd", 0xF0),("aacd", 0xAA),("aacd", 0x99),("aacd", 0x00),(NULL, 0xF0),(NULL, 0xAA),(NULL,0x99),(NULL, 0x00); Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> drop table if exists tt2; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE `tt2` ( -> `char_col` char(4) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL, 0xAA),("aabb", 0x99),("aabb", 0x00),("aacd", 0xF0),("aacd", 0xAA),("aacd", 0x99),("aacd", 0x00),(NULL, 0xF0),(NULL, 0xAA),(NULL,0x99),(NULL, 0x00); -> `bit_col` bit(8) DEFAULT b'0' -> ) ENGINE=InnoDB; Query OK, 0 rows affected, 2 warnings (0.03 sec) mysql> insert into tt2 values("aaaa", 0xF0),("aaaa", 0xAA),("aaaa", 0x99),("aaaa", 0x00),("aabb", 0xF0),("aabb", 0xAA),("aabb", 0x99),("aabb", 0x00),("aacd", 0xF0),("aacd", 0xAA),("aacd", 0x99),("aacd", 0x00),(NULL, 0xF0),(NULL, 0xAA),(NULL,0x99),(NULL, 0x00); Query OK, 16 rows affected (0.01 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> SELECT count(*) FROM tt1 AS A force index(ndx_bit_col) right join tt2 AS B ON A.bit_col=B.char_col; +----------+ | count(*) | +----------+ | 16 | +----------+ 1 row in set (0.01 sec) mysql> SELECT count(*) FROM tt1 AS A ignore index(ndx_bit_col) right join tt2 AS B ON A.bit_col=B.char_col; +----------+ | count(*) | +----------+ | 52 | +----------+ 1 row in set, 12 warnings (0.00 sec) How to repeat: drop table if exists tt1; CREATE TABLE `tt1` ( `char_col` char(4) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL, `bit_col` bit(8) DEFAULT b'0', KEY `ndx_bit_col` (`bit_col`), FULLTEXT KEY `ndx_char_col` (`char_col`) ) ENGINE=InnoDB; insert into tt1 values("aaaa", 0xF0),("aaaa", 0xAA),("aaaa", 0x99),("aaaa", 0x00),("aabb", 0xF0),("aabb", 0xAA),("aabb", 0x99),("aabb", 0x00),("aacd", 0xF0),("aacd", 0xAA),("aacd", 0x99),("aacd", 0x00),(NULL, 0xF0),(NULL, 0xAA),(NULL,0x99),(NULL, 0x00); drop table if exists tt2; CREATE TABLE `tt2` ( `char_col` char(4) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL, `bit_col` bit(8) DEFAULT b'0' ) ENGINE=InnoDB; insert into tt2 values("aaaa", 0xF0),("aaaa", 0xAA),("aaaa", 0x99),("aaaa", 0x00),("aabb", 0xF0),("aabb", 0xAA),("aabb", 0x99),("aabb", 0x00),("aacd", 0xF0),("aacd", 0xAA),("aacd", 0x99),("aacd", 0x00),(NULL, 0xF0),(NULL, 0xAA),(NULL,0x99),(NULL, 0x00); SELECT count(*) FROM tt1 AS A force index(ndx_bit_col) right join tt2 AS B ON A.bit_col=B.char_col; --with index SELECT count(*) FROM tt1 AS A ignore index(ndx_bit_col) right join tt2 AS B ON A.bit_col=B.char_col; --without index