incorrect query result & plan using vector index

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t57e8e84a` (
  `col_48` vector(10) DEFAULT NULL,
  VECTOR INDEX `idx_22`((VEC_L2_DISTANCE(`col_48`)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `t57e8e84a` VALUES ('[0.051269,0.853917,0.455115,0.435704,0.155638,0.917716,0.150548,0.758539,0.994081,0.337299]'), ('[0.181088,0.99404,0.443104,0.103317,0.347481,0.613621,0.67831,0.156942,0.628124,0.404317]'), ('[0.107276,0.176616,0.538764,0.897124,0.240044,0.610647,0.419338,0.068536,0.277225,0.350954]'), ('[0.084592,0.754598,0.954016,0.699647,0.196896,0.149874,0.605503,0.382313,0.135533,0.958966]'), ('[0.463696,0.971825,0.58321,0.110151,0.43132,0.313766,0.586065,0.933863,0.237381,0.704989]'), ('[0.866258,0.230093,0.184715,0.193287,0.075423,0.651197,0.52681,0.172773,0.446979,0.09783]'), ('[0.06036,0.677434,0.143703,0.398705,0.762962,0.585483,0.175388,0.113405,0.655121,0.952397]'), ('[0.294816,0.911044,0.971908,0.209044,0.579311,0.925539,0.746836,0.664886,0.727223,0.473452]'), ('[0.465338,0.255249,0.376805,0.157014,0.634387,0.707727,0.694444,0.583405,0.665894,0.037715]'), ('[0.749314,0.710764,0.642532,0.825355,0.106786,0.356603,0.933181,0.527412,0.314265,0.196263]');

desc SELECT `t57e8e84a`.`col_48` AS `r0` FROM `t57e8e84a` WHERE `t57e8e84a`.`col_48`<'[0.465338, 0.255249, 0.376805, 0.157014, 0.634387, 0.707727, 0.694444, 0.583405, 0.665894, 0.037715]';

2. What did you expect to see? (Required)

Table Scan

3. What did you see instead (Required)

mysql> desc SELECT `t57e8e84a`.`col_48` AS `r0` FROM `t57e8e84a` WHERE `t57e8e84a`.`col_48`<'[0.465338, 0.255249, 0.376805, 0.157014, 0.634387, 0.707727, 0.694444, 0.583405, 0.665894, 0.037715]';
+------------------------+---------+-----------+---------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
| id                     | estRows | task      | access object                         | operator info                                                                                                                            |
+------------------------+---------+-----------+---------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
| IndexReader_6          | 4.17    | root      |                                       | index:IndexRangeScan_5                                                                                                                   |
| └─IndexRangeScan_5     | 4.17    | cop[tikv] | table:t57e8e84a, index:idx_22(col_48) | range:[-inf,[0.465338,0.255249,0.376805,0.157014,0.634387,0.707727,0.694444,0.583405,0.665894,0.037715]), keep order:false, stats:pseudo |
+------------------------+---------+-----------+---------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

f773b6e

[JaySon-Huang]

In my testing cluster, the plan will change after serval retry. And finally, stick to a correct plan

CREATE TABLE `t57e8e84a` (
   `col_48` vector(10) DEFAULT NULL,
   VECTOR INDEX `idx_22`((VEC_L2_DISTANCE(`col_48`)))
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
 INSERT INTO `t57e8e84a` VALUES ('[0.051269,0.853917,0.455115,0.435704,0.155638,0.917716,0.150548,0.758539,0.994081,0.337299]'), ('[0.181088,0.99404,0.443104,0.103317,0.347481,0.613621,0.67831,0.156942,0.628124,0.404317]'), ('[0.107276,0.176616,0.538764,0.897124,0.240044,0.610647,0.419338,0.068536,0.277225,0.350954]'), ('[0.084592,0.7
 54598,0.954016,0.699647,0.196896,0.149874,0.605503,0.382313,0.135533,0.958966]'), ('[0.463696,0.971825,0.58321,0.110151,0.43132,0.313766,0.586065,0.933863,0.237381,0.704989]'), ('[0.866258,0.230093,0.184715,0.193287,0.075423,0.651197,0.52681,0.172773,0.446979,0.09783]'), ('[0.06036,0.677434,0.143703,0.398705,0.762962,0.585483,0.17538
 8,0.113405,0.655121,0.952397]'), ('[0.294816,0.911044,0.971908,0.209044,0.579311,0.925539,0.746836,0.664886,0.727223,0.473452]'), ('[0.465338,0.255249,0.376805,0.157014,0.634387,0.707727,0.694444,0.583405,0.665894,0.037715]'), ('[0.749314,0.710764,0.642532,0.825355,0.106786,0.356603,0.933181,0.527412,0.314265,0.196263]');

 desc SELECT `t57e8e84a`.`col_48` AS `r0` FROM `t57e8e84a` WHERE `t57e8e84a`.`col_48`<'[0.465338, 0.255249, 0.376805, 0.157014, 0.634387, 0.707727, 0.694444, 0.583405, 0.665894, 0.037715]';
+--------------------+---------+-----------+---------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
| id                 | estRows | task      | access object                         | operator info                                                                                                                            |
+--------------------+---------+-----------+---------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
| IndexReader_6      | 3323.33 | root      |                                       | index:IndexRangeScan_5                                                                                                                   |
| └─IndexRangeScan_5 | 3323.33 | cop[tikv] | table:t57e8e84a, index:idx_22(col_48) | range:[-inf,[0.465338,0.255249,0.376805,0.157014,0.634387,0.707727,0.694444,0.583405,0.665894,0.037715]), keep order:false, stats:pseudo |
+--------------------+---------+-----------+---------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+


TiDB>  desc SELECT `t57e8e84a`.`col_48` AS `r0` FROM `t57e8e84a` WHERE `t57e8e84a`.`col_48`<'[0.465338, 0.255249, 0.376805, 0.157014, 0.634387, 0.707727, 0.694444, 0.583405, 0.665894, 0.037715]';
+------------------------+----------+--------------+-----------------+-------------------------------------------------------------------+
| id                     | estRows  | task         | access object   | operator info                                                     |
+------------------------+----------+--------------+-----------------+-------------------------------------------------------------------+
| TableReader_18         | 3323.33  | root         |                 | MppVersion: 2, data:ExchangeSender_17                             |
| └─ExchangeSender_17    | 3323.33  | mpp[tiflash] |                 | ExchangeType: PassThrough                                         |
|   └─Selection_15       | 3323.33  | mpp[tiflash] |                 | lt(test.t57e8e84a.col_48, [0.47,0.26,0.38,0.16,0.63,(5 more)...]) |
|     └─TableFullScan_14 | 10000.00 | mpp[tiflash] | table:t57e8e84a | pushed down filter:empty, keep order:false, stats:pseudo          |
+------------------------+----------+--------------+-----------------+-------------------------------------------------------------------+
4 rows in set
Time: 0.007s
TiDB>  desc SELECT `t57e8e84a`.`col_48` AS `r0` FROM `t57e8e84a` WHERE `t57e8e84a`.`col_48`<'[0.465338, 0.255249, 0.376805, 0.157014, 0.634387, 0.707727, 0.694444, 0.583405, 0.665894, 0.037715]';
+---------------------+---------+-----------+-----------------+-------------------------------------------------------------------+
| id                  | estRows | task      | access object   | operator info                                                     |
+---------------------+---------+-----------+-----------------+-------------------------------------------------------------------+
| TableReader_8       | 3.32    | root      |                 | data:Selection_7                                                  |
| └─Selection_7       | 3.32    | cop[tikv] |                 | lt(test.t57e8e84a.col_48, [0.47,0.26,0.38,0.16,0.63,(5 more)...]) |
|   └─TableFullScan_6 | 10.00   | cop[tikv] | table:t57e8e84a | keep order:false, stats:pseudo                                    |
+---------------------+---------+-----------+-----------------+-------------------------------------------------------------------+