Description:
MySQL returns inconsistent result with/without index.
How to repeat:
create table t(a bigint, b varchar(50), key idx(a));
create table t1(b varchar(50));
insert into t values(13790462480960267,'13790462480960267');
insert into t values(13790462480960268,'13790462480960267');
insert into t values(13790462480960269,'13790462480960267');
insert into t1 values ('13790462480960267');
select t.a from t, t1 where t.a=t1.b;
+-------------------+
| a |
+-------------------+
| 13790462480960267 |
+-------------------+
1 row in set (0.00 sec)
alter table t drop index idx;
select t.a from t, t1 where t.a=t1.b;
+-------------------+
| a |
+-------------------+
| 13790462480960267 |
| 13790462480960268 |
| 13790462480960269 |
+-------------------+
3 rows in set (0.00 sec)
Suggested fix:
Since the comparison results of varchar and bigint values are true, it looks like the join results should return three rows.
mysql> select a=b from t;
+------+
| a=b |
+------+
| 1 |
| 1 |
| 1 |
+------+
3 rows in set (0.00 sec)
Description: MySQL returns inconsistent result with/without index. How to repeat: create table t(a bigint, b varchar(50), key idx(a)); create table t1(b varchar(50)); insert into t values(13790462480960267,'13790462480960267'); insert into t values(13790462480960268,'13790462480960267'); insert into t values(13790462480960269,'13790462480960267'); insert into t1 values ('13790462480960267'); select t.a from t, t1 where t.a=t1.b; +-------------------+ | a | +-------------------+ | 13790462480960267 | +-------------------+ 1 row in set (0.00 sec) alter table t drop index idx; select t.a from t, t1 where t.a=t1.b; +-------------------+ | a | +-------------------+ | 13790462480960267 | | 13790462480960268 | | 13790462480960269 | +-------------------+ 3 rows in set (0.00 sec) Suggested fix: Since the comparison results of varchar and bigint values are true, it looks like the join results should return three rows. mysql> select a=b from t; +------+ | a=b | +------+ | 1 | | 1 | | 1 | +------+ 3 rows in set (0.00 sec)