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.

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

predicate mistakenly pushed down over join #9843

Closed
eurekaka opened this issue Mar 21, 2019 · 0 comments
Closed

predicate mistakenly pushed down over join #9843

eurekaka opened this issue Mar 21, 2019 · 0 comments
Assignees
Labels
sig/planner SIG: Planner type/bug The issue is confirmed as a bug.

Comments

@eurekaka
Copy link
Contributor

Bug Report

Please answer these questions before submitting your issue. Thanks!

  1. What did you do?
mysql> create table t(a int primary key, b int, c int, index idx(b));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(1, 1, 1), (2, 2 ,2), (3, 3, 3), (4, 3, 4),(5,3,5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> analyze table t;
Query OK, 0 rows affected (0.03 sec)

mysql> explain select t.c in (select count(*) from t s use index(idx), t t1 where s.b = t.a and s.b = 3 and s.a = t1.a) from t;
+------------------------------+-------+------+----------------------------------------------------------------------------+
| id                           | count | task | operator info                                                              |
+------------------------------+-------+------+----------------------------------------------------------------------------+
| Projection_11                | 5.00  | root | 9_aux_0                                                                    |
| └─Apply_13                   | 5.00  | root | left outer semi join, inner:StreamAgg_20, other cond:eq(test.t.c, 7_col_0) |
|   ├─TableReader_15           | 5.00  | root | data:TableScan_14                                                          |
|   │ └─TableScan_14           | 5.00  | cop  | table:t, range:[-inf,+inf], keep order:false                               |
|   └─StreamAgg_20             | 1.00  | root | funcs:count(1)                                                             |
|     └─IndexJoin_35           | 3.00  | root | inner join, inner:TableReader_34, outer key:s.a, inner key:t1.a            |
|       ├─IndexReader_28       | 3.00  | root | index:IndexScan_27                                                         |
|       │ └─IndexScan_27       | 3.00  | cop  | table:s, index:b, range:[3,3], keep order:false                            |
|       └─TableReader_34       | 4.00  | root | data:Selection_33                                                          |
|         └─Selection_33       | 4.00  | cop  | eq(3, test.t.a)                                                            |
|           └─TableScan_32     | 1.00  | cop  | table:t1, range: decided by [s.a], keep order:false                        |
+------------------------------+-------+------+----------------------------------------------------------------------------+
11 rows in set (0.00 sec)
  1. What did you expect to see?

Selection_33 should be kept in join's OtherConditions.

  1. What did you see instead?

Selection_33 is mistakenly pushed down to t1 side.

  1. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
Release Version: v3.0.0-beta-250-g778c3f4a5
Git Commit Hash: 778c3f4a5a716880bcd1d71b257c8165685f0d70
Git Branch: master
UTC Build Time: 2019-03-21 06:46:43
GoVersion: go version go1.12 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
@eurekaka eurekaka self-assigned this Mar 21, 2019
@eurekaka eurekaka added type/bug The issue is confirmed as a bug. sig/planner SIG: Planner labels Mar 21, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
sig/planner SIG: Planner type/bug The issue is confirmed as a bug.
Projects
None yet
Development

No branches or pull requests

1 participant