Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

unexpected selection in index-look-up's build-side after add a probe-side condition #21280

Open
lysu opened this issue Nov 25, 2020 · 2 comments · May be fixed by #21834
Open

unexpected selection in index-look-up's build-side after add a probe-side condition #21280

lysu opened this issue Nov 25, 2020 · 2 comments · May be fixed by #21834
Assignees
@xiaodong-ji
Labels
sig/planner SIG: Planner type/enhancement The issue or PR belongs to an enhancement.

Comments

@lysu
Copy link
Contributor

lysu commented Nov 25, 2020 •
edited
Loading

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  KEY `kk` (`a`,`b`,`c`)
)

analyze table t;

run sql1

explain select * from t where a = 1 and ((b = 1 and c = 1) or (b = 3));

will get the right plan

MySQL [test]> explain select * from t where a = 1 and ((b = 1 and c = 1) or (b = 3));
+-------------------------------+---------+-----------+----------------------------+----------------------------------------------------------------+
| id                            | estRows | task      | access object              | operator info                                                  |
+-------------------------------+---------+-----------+----------------------------+----------------------------------------------------------------+
| IndexLookUp_10                | 0.10    | root      |                            |                                                                |
| ├─IndexRangeScan_8(Build)     | 0.10    | cop[tikv] | table:t, index:kk(a, b, c) | range:[1 1 1,1 1 1], [1 3,1 3], keep order:false, stats:pseudo |
| └─TableRowIDScan_9(Probe)     | 0.10    | cop[tikv] | table:t                    | keep order:false, stats:pseudo                                 |
+-------------------------------+---------+-----------+----------------------------+----------------------------------------------------------------+

but just add addition filter condition and d = 3

explain select * from t where a = 1 and ((b = 1 and c = 1) or (b = 3)) and d = 3;

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

MySQL [test]> explain select * from t where a = 1 and ((b = 1 and c = 1) or (b = 3)) and d = 3;
+----------------------------+---------+-----------+----------------------------+------------------------------------------------------------+
| id                         | estRows | task      | access object              | operator info                                              |
+----------------------------+---------+-----------+----------------------------+------------------------------------------------------------+
| IndexLookUp_12             | 0.00    | root      |                            |                                                            |
| ├─IndexRangeScan_8(Build)     | 0.10    | cop[tikv] | table:t, index:kk(a, b, c) | range:[1 1 1,1 1 1], [1 3,1 3], keep order:false, stats:pseudo |
| └─Selection_11(Probe)      | 0.00    | cop[tikv] |                            | eq(test.t.d, 3)                                            |
|   └─TableRowIDScan_9       | 0.00    | cop[tikv] | table:t                    | keep order:false, stats:pseudo                             |
+----------------------------+---------+-----------+----------------------------+------------------------------------------------------------+

3. What did you see instead (Required)

MySQL [test]> explain select * from t where a = 1 and ((b = 1 and c = 1) or (b = 3)) and d = 3;
+----------------------------+---------+-----------+----------------------------+------------------------------------------------------------+
| id                         | estRows | task      | access object              | operator info                                              |
+----------------------------+---------+-----------+----------------------------+------------------------------------------------------------+
| IndexLookUp_12             | 0.00    | root      |                            |                                                            |
| ├─Selection_10(Build)      | 0.00    | cop[tikv] |                            | or(and(eq(test.t.b, 1), eq(test.t.c, 1)), eq(test.t.b, 3)) |
| │ └─IndexRangeScan_8       | 0.20    | cop[tikv] | table:t, index:kk(a, b, c) | range:[1 1,1 1], [1 3,1 3], keep order:false, stats:pseudo |
| └─Selection_11(Probe)      | 0.00    | cop[tikv] |                            | eq(test.t.d, 3)                                            |
|   └─TableRowIDScan_9       | 0.00    | cop[tikv] | table:t                    | keep order:false, stats:pseudo                             |
+----------------------------+---------+-----------+----------------------------+------------------------------------------------------------+
5 rows in set (0.003 sec)

it should build [1 1 1, 1 1 1] , [1 3, 1 3] ranges but it generate [1 1, 1 1], [1 3, 1 3] and add addition selection..

4. What is your TiDB version? (Required)

Release Version: None
Edition: Community
Git Commit Hash: None
Git Branch: None
UTC Build Time: None
GoVersion: go1.15
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
@lysu lysu added type/bug The issue is confirmed as a bug. sig/planner SIG: Planner labels Nov 25, 2020
@lysu lysu changed the title unexpected plan after add condition unexpected selection in index-look-up's build-side after add a probe-side condition Nov 25, 2020
@lysu
Copy link
Contributor Author

lysu commented Nov 25, 2020

and if we change schema as

create table t(a int, b int, c int, d int, index kk(a, b, c, d));

we will get plan:

explain select * from t where a = 1 and ((b = 1 and c = 1) or (b = 3)) and d = 3;
+--------------------------+---------+-----------+-------------------------------+-----------------------------------------------------------------------------+
| id                       | estRows | task      | access object                 | operator info                                                               |
+--------------------------+---------+-----------+-------------------------------+-----------------------------------------------------------------------------+
| IndexReader_7            | 0.00    | root      |                               | index:Selection_6                                                           |
| └─Selection_6            | 0.00    | cop[tikv] |                               | eq(test.t.d, 3), or(and(eq(test.t.b, 1), eq(test.t.c, 1)), eq(test.t.b, 3)) |
|   └─IndexRangeScan_5     | 0.20    | cop[tikv] | table:t, index:kk(a, b, c, d) | range:[1 1,1 1], [1 3,1 3], keep order:false, stats:pseudo                  |
+--------------------------+---------+-----------+-------------------------------+-----------------------------------------------------------------------------+
3 rows in set (0.003 sec)

but ideal we should get

+--------------------------+---------+-----------+-------------------------------+-----------------------------------------------------------------------------+
| id                       | estRows | task      | access object                 | operator info                                                               |
+--------------------------+---------+-----------+-------------------------------+-----------------------------------------------------------------------------+
| IndexReader_7            | 0.00    | root      |                               | index:Selection_6                                                           |
| └─Selection_6            | 0.00    | cop[tikv] |                               | eq(test.t.d, 3)  |
|   └─IndexRangeScan_5     | 0.20    | cop[tikv] | table:t, index:kk(a, b, c, d) | range:[1 1 1 3,1 1 1 3], [1 3,1 3], keep order:false, stats:pseudo                  |
+--------------------------+---------+-----------+-------------------------------+-----------------------------------------------------------------------------+

@lysu lysu added type/enhancement The issue or PR belongs to an enhancement. and removed type/bug The issue is confirmed as a bug. labels Nov 25, 2020
@xiaodong-ji xiaodong-ji removed their assignment Dec 15, 2020
@xiaodong-ji
Copy link
Contributor

/assign

@xiaodong-ji xiaodong-ji linked a pull request Dec 16, 2020 that will close this issue
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@xiaodong-ji xiaodong-ji

Labels
sig/planner SIG: Planner type/enhancement The issue or PR belongs to an enhancement.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

planner: enhanced index range calculation plan xiaodong-ji/tidb
2 participants
@lysu @xiaodong-ji