Repository: matrixorigin/matrixone
Pull request #12331 (2023-10-25 02:39:59)

[Bug]: should not push a filter down into limit
## What type of PR is this?

- [ ] API-change
- [x] BUG
- [ ] Improvement
- [ ] Documentation
- [ ] Feature
- [ ] Test and CI
- [ ] Code Refactoring

## Which issue(s) this PR fixes:

issue #12325

## What this PR does / why we need it:


Patch:
diff --git a/pkg/sql/plan/join_order.go b/pkg/sql/plan/join_order.go
index 8918dc793..eb5f5d106 100644
--- a/pkg/sql/plan/join_order.go
+++ b/pkg/sql/plan/join_order.go
@@ -332,7 +332,7 @@ func (builder *QueryBuilder) determineJoinOrder(nodeID int32) int32 {
 }
 
 func (builder *QueryBuilder) gatherJoinLeavesAndConds(joinNode *plan.Node, leaves []*plan.Node, conds []*plan.Expr) ([]*plan.Node, []*plan.Expr) {
-	if joinNode.NodeType != plan.Node_JOIN || joinNode.JoinType != plan.Node_INNER {
+	if joinNode.NodeType != plan.Node_JOIN || joinNode.JoinType != plan.Node_INNER || joinNode.Limit != nil {
 		nodeID := builder.determineJoinOrder(joinNode.NodeId)
 		leaves = append(leaves, builder.qry.Nodes[nodeID])
 		return leaves, conds
diff --git a/pkg/sql/plan/opt_misc.go b/pkg/sql/plan/opt_misc.go
index edecc0f07..d213aeede 100644
--- a/pkg/sql/plan/opt_misc.go
+++ b/pkg/sql/plan/opt_misc.go
@@ -194,6 +194,12 @@ func (builder *QueryBuilder) pushdownFilters(nodeID int32, filters []*plan.Expr,
 
 	var canPushdown, cantPushdown []*plan.Expr
 
+	if node.Limit != nil {
+		// can not push down over limit
+		cantPushdown = filters
+		filters = nil
+	}
+
 	switch node.NodeType {
 	case plan.Node_AGG:
 		groupTag := node.BindingTags[0]


Test patch:
diff --git a/test/distributed/cases/optimizer/pushdown.result b/test/distributed/cases/optimizer/pushdown.result
new file mode 100644
index 000000000..b5f89d521
--- /dev/null
+++ b/test/distributed/cases/optimizer/pushdown.result
@@ -0,0 +1,63 @@
+drop database if exists d1;
+create database d1;
+use d1;
+drop table if exists t1;
+drop table if exists t2;
+drop table if exists t3;
+create table t1(c1 int primary key);
+create table t2(c1 int primary key, c2 int, c3 int);
+create table t3(c1 int, c2 int, c3 int, primary key(c1,c2));
+insert into t1 select * from generate_series(10000) g;
+insert into t2 select c1, c1, c1 from t1;
+insert into t2 select c1+10000, c1+10000, c1+10000 from t1;
+insert into t3 select c1, c1, c1 from t1;
+insert into t3 select c1+10000, c1+10000, c1+10000 from t1;
+insert into t3 select c1+20000, c1+20000, c1+20000 from t1;
+select mo_ctl('dn', 'flush', 'd1.t1');
+mo_ctl(dn, flush, d1.t1)
+{\n  "method": "Flush",\n  "result": [\n    {\n      "returnStr": "OK"\n    }\n  ]\n}\n
+select mo_ctl('dn', 'flush', 'd1.t2');
+mo_ctl(dn, flush, d1.t2)
+{\n  "method": "Flush",\n  "result": [\n    {\n      "returnStr": "OK"\n    }\n  ]\n}\n
+select mo_ctl('dn', 'flush', 'd1.t3');
+mo_ctl(dn, flush, d1.t3)
+{\n  "method": "Flush",\n  "result": [\n    {\n      "returnStr": "OK"\n    }\n  ]\n}\n
+explain select * from (select * from t1 where c1%3=0 order by c1 desc limit 10) tmpt where c1 % 2 = 0;
+QUERY PLAN
+Project
+  ->  Filter
+        Filter Cond: ((cast(t1.c1 AS BIGINT) % 2) = 0)
+        ->  Sort
+              Sort Key: t1.c1 DESC
+              Limit: 10
+              ->  Table Scan on d1.t1
+                    Filter Cond: ((cast(t1.c1 AS BIGINT) % 3) = 0)
+explain select * from (select v2.c1 as c1, v2.c2 as c2, v2.c3 as c3 from (select * from t1 where c1!=0 limit 10) v1 join (select * from t2 where c1!=1 limit 10) v2 on v1.c1=v2.c1 where v2.c2>0 limit 10) v3 join (select * from t3 where c1!=2 limit 10) v4 on v3.c3=v4.c3 where v4.c2>0 limit 10;
+QUERY PLAN
+Project
+  Limit: 10
+  ->  Join
+        Join Type: INNER
+        Join Cond: (v3.c3 = v4.c3)
+        ->  Project
+              Limit: 10
+              ->  Join
+                    Join Type: INNER
+                    Join Cond: (v1.c1 = v2.c1)
+                    ->  Project
+                          Limit: 10
+                          ->  Table Scan on d1.t1
+                                Filter Cond: (t1.c1 <> 0)
+                    ->  Filter
+                          Filter Cond: (v2.c2 > 0)
+                          ->  Project
+                                Limit: 10
+                                ->  Table Scan on d1.t2
+                                      Filter Cond: (t2.c1 <> 1)
+        ->  Filter
+              Filter Cond: (v4.c2 > 0)
+              ->  Project
+                    Limit: 10
+                    ->  Table Scan on d1.t3
+                          Filter Cond: (t3.c1 <> 2)
+drop database if exists d1;
\ No newline at end of file
diff --git a/test/distributed/cases/optimizer/pushdown.test b/test/distributed/cases/optimizer/pushdown.test
new file mode 100644
index 000000000..bd2cb705f
--- /dev/null
+++ b/test/distributed/cases/optimizer/pushdown.test
@@ -0,0 +1,26 @@
+drop database if exists d1;
+create database d1;
+use d1;
+drop table if exists t1;
+drop table if exists t2;
+drop table if exists t3;
+create table t1(c1 int primary key);
+create table t2(c1 int primary key, c2 int, c3 int);
+create table t3(c1 int, c2 int, c3 int, primary key(c1,c2));
+insert into t1 select * from generate_series(10000) g;
+insert into t2 select c1, c1, c1 from t1;
+insert into t2 select c1+10000, c1+10000, c1+10000 from t1;
+insert into t3 select c1, c1, c1 from t1;
+insert into t3 select c1+10000, c1+10000, c1+10000 from t1;
+insert into t3 select c1+20000, c1+20000, c1+20000 from t1;
+-- @separator:table
+select mo_ctl('dn', 'flush', 'd1.t1');
+-- @separator:table
+select mo_ctl('dn', 'flush', 'd1.t2');
+-- @separator:table
+select mo_ctl('dn', 'flush', 'd1.t3');
+-- @separator:table
+explain select * from (select * from t1 where c1%3=0 order by c1 desc limit 10) tmpt where c1 % 2 = 0;
+-- @separator:table
+explain select * from (select v2.c1 as c1, v2.c2 as c2, v2.c3 as c3 from (select * from t1 where c1!=0 limit 10) v1 join (select * from t2 where c1!=1 limit 10) v2 on v1.c1=v2.c1 where v2.c2>0 limit 10) v3 join (select * from t3 where c1!=2 limit 10) v4 on v3.c3=v4.c3 where v4.c2>0 limit 10;
+drop database if exists d1;
\ No newline at end of file