Tidb: bigint unsigned primary key lookup problem

[darren]

1. What did you do?
   If possible, provide a recipe for reproducing the error.

create a table with bigint unsigned as its primary key:

CREATE TABLE `t1` (
  `id` bigint(20) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
)

populate the table with some sample data:

mysql> select * from t1 limit 10;
+---------------------+
| id                  |
+---------------------+
| 9223798566436992824 |
| 9223799665948621035 |
| 9223800765460249246 |
| 9223806263018390301 |
| 9223923837981016848 |
| 9223924937492645059 |
| 9223926037004273270 |
| 9223931534562414325 |
| 9223971165275585096 |
| 9223972264787213307 |
+---------------------+
10 rows in set (0.00 sec)

select by uint64 id, it returns very quickly:

mysql> select * from t1 where id=9223798566436992824;
+---------------------+
| id                  |
+---------------------+
| 9223798566436992824 |
+---------------------+
1 row in set (0.00 sec)

select by the same id, but pass it as string, the query takes a lot of time

mysql> select * from t1 where id='9223798566436992824';
+---------------------+
| id                  |
+---------------------+
| 9223798566436992824 |
+---------------------+
1 row in set (3.76 sec)

mysql> desc select * from t1 where id='9223798566436992824';
+---------------------+----------+------+---------------------------------------------+
| id                  | count    | task | operator info                               |
+---------------------+----------+------+---------------------------------------------+
| Selection_5         | 54248.80 | root | eq(cast(test.t1.id), 9.223798566436993e+18) |
| └─TableReader_7     | 67811.00 | root | data:TableScan_6                            |
|   └─TableScan_6     | 67811.00 | cop  | table:t1, range:[0,+inf], keep order:false  |
+---------------------+----------+------+---------------------------------------------+
3 rows in set (0.00 sec)

mysql> desc select * from t1 where id=9223798566436992824;
+-------------------+-------+------+-----------------------------------------------------------------------------+
| id                | count | task | operator info                                                               |
+-------------------+-------+------+-----------------------------------------------------------------------------+
| TableReader_6     | 1.00  | root | data:TableScan_5                                                            |
| └─TableScan_5     | 1.00  | cop  | table:t1, range:[9223798566436992824,9223798566436992824], keep order:false |
+-------------------+-------+------+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

1. What did you expect to see?

   select * from t1 where id='9223798566436992824'; should be as quick as select * from t1 where id=9223798566436992824;

2. What did you see instead?

   select * from t1 where id='9223798566436992824'; is very slow.

3. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

Release Version: v2.1.0-beta-65-g329c12c6a
Git Commit Hash: 329c12c6a2e8d60659bc98ef09e262d9421115ac
Git Branch: master
UTC Build Time: 2018-07-17 11:02:02
GoVersion: go version go1.10.3 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e

[darren]

we encountered this problem because for go-sql-driver/mysql, uint64 params will be passed as string

    db, err := sql.Open("mysql", "root@tcp(127.0.0.1:3306)/test")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    var i uint64 = 12161962213042174405
    rows, err := db.Query("select * from  t1 where id=?", i)

    if err != nil {
        log.Fatal(err)
    }

ref: https://github.com/go-sql-driver/mysql/blob/749ddf1598b47e3cd909414bda735fe790ef3d30/statement.go#L172

some issues related:

https://github.com/golang/go/issues/9373
https://github.com/go-sql-driver/mysql/issues/329

[XuHuaiyu]

hi, @Darren what if you try this:
select title from t1 where id=cast( ? as unsigned)

[darren]

@XuHuaiyu

That will work.

it seems that MySQL will do this cast implicitly and work as quickly as expected, programmer do not have to worry about that.

when we migrated data from MySQL to TiDB, and didn't change the application code.
we do a lot of concurrently updates to a table with bigint unsigned as key, and tidb-server constantly get killed because of OOM, and one tikv-server got OOM too once.

[XuHuaiyu]

Thanks for your reply, and sorry for what you've encountered when migrating from MySQL to TiDB. @Darren

1. int column <compare> string constant will be compared as double both in MySQL and TiDB,
   but we do not support to push cast function down to TiKV now (we are working on this). After using cast(? as unsigned), the constant will be folded during plan building phase, and no cast will be wrapped during int column <compare> unsigned int constant. Thus the where condition can be pushed down to TiKV, and the evaluation can speed up. So after we support push cast down, you would not modify the code.

2. It'll be very nice if you can provide us with more detailed info about what you've encountered, such as what you've done, the schema of the table, tidb-server version, tikv-server version, log of tidb-server and tikv-server.

[winoros]

@XuHuaiyu Mysql will do special check if the int column is part of a index.
@Darren This is a known issue. It may not be solved in 2.1 which will be release soon. But it should be solved this year.

[darren]

@XuHuaiyu
Thanks for your detailed explanation.

We realized that using bigint unsigned in the table was really a bad idea, it caused too much trouble accessing from both jdbc and golang. we should redesign the tables.

BTW, I am not familiar with the MySQL protocol, is bigint unsigned supported natively int the MySQL protocol? why is it neccessary that uint64 be converted to string in go-sql-driver/mysql?
I tried fiddling around go-sql-driver/mysql but to no avail.

[XuHuaiyu]

@Darren
I'm not really sure whether the behavior of go-sql-driver is necessary.
The process of unsigned int in TiDB is here.

[darren]

@XuHuaiyu

Thanks for the info! it saves me a lot of time.

It works by passing uint64 over the wire both with TiDB and MySQL.
I am preparing a pull request for go-sql-driver/mysql for this.

[darren]

Since it's a known issue and we can work around this in go-sql-driver, I am closing this.

[darren]

before #7108 is merged, the following sql returns incorrect result.

drop table t1;
create table t1 (id bigint unsigned, count int, primary key(id));
insert into t1 values (18446744073709551615, 0);
insert into t1 values (18446744073709551614, 0);
update t1 set count=3 where id='18446744073709551615';
select * from t1;

now it returns correct result!