Query results differ from mysql with indexed null

[wcurrie]

Bug Report

Result filtering seems broken when using an index containing null.

1. Minimal reproduce step

create table t1 (
    _id int PRIMARY KEY,
    c1 char,
    index (c1)
);

insert into t1 values (1, null);

select * from t1 where c1 is null and _id < 1;

2. What did you expect to see?

No rows returned. Using mysql (8.0.23) I get:

Empty set (0.00 sec)

3. What did you see instead

tidb shows a row that doesn't satisfy _id < 1 from the where clause:

+-----+------+
| _id | c1   |
+-----+------+
|   1 | NULL |
+-----+------+
1 row in set (0.00 sec)

If I drop the index, I see tidb filters out the row correctly

mysql> select * from t1 where c1 is null and _id < 1;
+-----+------+
| _id | c1   |
+-----+------+
|   1 | NULL |
+-----+------+
1 row in set (0.00 sec)

mysql> explain  select * from t1 where c1 is null and _id < 1;
+------------------------+---------+-----------+------------------------+----------------------------------------------------------+
| id                     | estRows | task      | access object          | operator info                                            |
+------------------------+---------+-----------+------------------------+----------------------------------------------------------+
| IndexReader_6          | 33.23   | root      |                        | index:IndexRangeScan_5                                   |
| └─IndexRangeScan_5     | 33.23   | cop[tikv] | table:t1, index:c1(c1) | range:[NULL -inf,NULL 1), keep order:false, stats:pseudo |
+------------------------+---------+-----------+------------------------+----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> alter table t1 drop index c1;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t1 where c1 is null and _id < 1;
Empty set (0.00 sec)

mysql> explain select * from t1 where c1 is null and _id < 1;
+--------------------------+---------+-----------+---------------+------------------------------------------------+
| id                       | estRows | task      | access object | operator info                                  |
+--------------------------+---------+-----------+---------------+------------------------------------------------+
| TableReader_7            | 0.00    | root      |               | data:Selection_6                               |
| └─Selection_6            | 0.00    | cop[tikv] |               | isnull(test.t1.c1)                             |
|   └─TableRangeScan_5     | 1.00    | cop[tikv] | table:t1      | range:[-inf,1), keep order:false, stats:pseudo |
+--------------------------+---------+-----------+---------------+------------------------------------------------+
3 rows in set (0.01 sec)

4. What is your TiDB version?

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v6.1.3
Edition: Community
Git Commit Hash: 6b02a5d8ba7dda28170ae2114b62e1e3b50dd974
Git Branch: heads/refs/tags/v6.1.3
UTC Build Time: 2022-11-26 07:36:17
GoVersion: go1.19.3
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

Also on tidbcloud

tidb_version(): Release Version: v6.3.0-serverless
Edition: Community
Git Commit Hash: 952d218e8066a04cf32bba13d536428e2695bd54
Git Branch: release-6.3-serverless
UTC Build Time: 2022-12-12 03:06:54
GoVersion: go1.19
Race Enabled: false
TiKV Min Version: 6.1.0
Check Table Before Drop: false
Store: tikv

[tiancaiamao]

This bug is caused by the ranger.Range to distsql key range convertion.

[NULL -inf,NULL 1) is converted to
7480000000000000505f6980000000000000010001, 7480000000000000505f6980000000000000010003800000000000000100

and encoded key for NULL 1 is 7480000000000000505f69800000000000000100038000000000000001 which is within the distsql key range, so the record is mistakenly read.

If you take a close look, there is an extra 00 after the NULL 1 encoded value (end key range), cause the bug.

The code is here, comment those lines, this bug disappear:

tidb/distsql/request_builder.go

Lines 794 to 799 in afbef28

	// NOTE: this is a hard-code operation to avoid wrong results when accessing unique index with NULL;
	// Please see https://github.com/pingcap/tidb/issues/29650 for more details
	if hasNull {
	// Append 0 to make unique-key range [null, null] to be a scan rather than point-get.
	high = kv.Key(high).Next()
	}

Git blame it, this change date back to a very early commit #7163