This is an automated email from the ASF dual-hosted git repository.
jakevin pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 77fbbf63ed4 [test](Nereids): add more test for eliminate inner join
by fk (#29390)
77fbbf63ed4 is described below
commit 77fbbf63ed4b5f0ed5f2bda88d228222d6925851
Author: 谢健 <jianx...@gmail.com>
AuthorDate: Fri Jan 5 16:21:24 2024 +0800
[test](Nereids): add more test for eliminate inner join by fk (#29390)
---
.../data/nereids_rules_p0/pkfk/eliminate_inner.out | 309 +++++++++++++++++++++
.../nereids_rules_p0/pkfk/eliminate_inner.groovy | 113 ++++++++
2 files changed, 422 insertions(+)
diff --git a/regression-test/data/nereids_rules_p0/pkfk/eliminate_inner.out
b/regression-test/data/nereids_rules_p0/pkfk/eliminate_inner.out
new file mode 100644
index 00000000000..bc465978ccb
--- /dev/null
+++ b/regression-test/data/nereids_rules_p0/pkfk/eliminate_inner.out
@@ -0,0 +1,309 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !name --
+simple_case
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk))
otherCondition=()
+----PhysicalOlapScan[pkt]
+----PhysicalOlapScan[fkt_not_null]
+
+-- !res --
+
+-- !name --
+with_pk_col
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk))
otherCondition=()
+----PhysicalOlapScan[pkt]
+----PhysicalOlapScan[fkt_not_null]
+
+-- !res --
+
+-- !name --
+with_pk_col
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk))
otherCondition=()
+----PhysicalOlapScan[pkt]
+----hashJoin[INNER_JOIN] hashCondition=((fkt_not_null1.fk = fkt_not_null2.fk))
otherCondition=()
+------PhysicalOlapScan[fkt_not_null]
+------PhysicalOlapScan[fkt_not_null]
+
+-- !res --
+
+-- !name --
+with_pk_col
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk))
otherCondition=() build RFs:RF1 fk->[pk]
+----filter((pkt.pk > 1))
+------PhysicalOlapScan[pkt] apply RFs: RF1
+----hashJoin[INNER_JOIN] hashCondition=((fkt_not_null1.fk = fkt_not_null2.fk))
otherCondition=() build RFs:RF0 fk->[fk]
+------filter((fkt_not_null1.fk > 1))
+--------PhysicalOlapScan[fkt_not_null] apply RFs: RF0
+------filter((fkt_not_null2.fk > 1))
+--------PhysicalOlapScan[fkt_not_null]
+
+-- !res --
+
+-- !name --
+with_pk_col
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk))
otherCondition=()
+----PhysicalOlapScan[pkt]
+----hashAgg[LOCAL]
+------PhysicalOlapScan[fkt_not_null]
+
+-- !res --
+
+-- !name --
+with_pk_col
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk))
otherCondition=()
+----PhysicalOlapScan[pkt]
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalUnion
+----------PhysicalOlapScan[fkt_not_null]
+----------PhysicalOlapScan[fkt_not_null]
+
+-- !res --
+
+-- !name --
+fk with window
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk))
otherCondition=()
+----PhysicalOlapScan[pkt]
+----PhysicalWindow
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalOlapScan[fkt_not_null]
+
+-- !res --
+
+-- !name --
+fk with limit
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk))
otherCondition=() build RFs:RF0 fk->[pk]
+----PhysicalOlapScan[pkt] apply RFs: RF0
+----PhysicalLimit[GLOBAL]
+------PhysicalLimit[LOCAL]
+--------PhysicalOlapScan[fkt_not_null]
+
+-- !res --
+
+-- !name --
+pk with filter that same as fk
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk))
otherCondition=() build RFs:RF0 fk->[pk]
+----filter((pkt.pk = 1))
+------PhysicalOlapScan[pkt] apply RFs: RF0
+----filter((fkt_not_null.fk = 1))
+------PhysicalOlapScan[fkt_not_null]
+
+-- !res --
+
+-- !name --
+pk with filter that included same as fk
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk))
otherCondition=() build RFs:RF0 fk->[pk]
+----filter((pkt.pk = 1))
+------PhysicalOlapScan[pkt] apply RFs: RF0
+----filter((cast(f as DOUBLE) = 1) and (fkt_not_null.fk = 1))
+------PhysicalOlapScan[fkt_not_null]
+
+-- !res --
+
+-- !name --
+pk with filter that not same as fk
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk))
otherCondition=() build RFs:RF0 fk->[pk]
+----filter((cast(p as DOUBLE) = 1) and (pkt.pk = 1))
+------PhysicalOlapScan[pkt] apply RFs: RF0
+----filter((cast(f as DOUBLE) = 1) and (fkt_not_null.fk = 1))
+------PhysicalOlapScan[fkt_not_null]
+
+-- !res --
+
+-- !name --
+simple_case
+
+-- !shape --
+PhysicalResultSink
+--filter(( not fk IS NULL))
+----PhysicalOlapScan[fkt]
+
+-- !res --
+1 John
+1 John
+2 Alice
+2 Alice
+3 Bob
+
+-- !name --
+with_pk_col
+
+-- !shape --
+PhysicalResultSink
+--filter(( not fk IS NULL))
+----PhysicalOlapScan[fkt]
+
+-- !res --
+1 John 1
+1 John 1
+2 Alice 2
+2 Alice 2
+3 Bob 3
+
+-- !name --
+with_pk_col
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((pk = fkt2.fk)) otherCondition=()
+----filter(( not fk IS NULL))
+------PhysicalOlapScan[fkt]
+----PhysicalOlapScan[fkt]
+
+-- !res --
+1 John 1
+1 John 1
+1 John 1
+1 John 1
+2 Alice 2
+2 Alice 2
+2 Alice 2
+2 Alice 2
+3 Bob 3
+
+-- !name --
+with_pk_col
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((pk = fkt2.fk)) otherCondition=() build
RFs:RF0 fk->[fk]
+----filter(( not fk IS NULL) and (fkt1.fk > 1))
+------PhysicalOlapScan[fkt] apply RFs: RF0
+----filter((fkt2.fk > 1))
+------PhysicalOlapScan[fkt]
+
+-- !res --
+2 Alice 2
+2 Alice 2
+2 Alice 2
+2 Alice 2
+3 Bob 3
+
+-- !name --
+with_pk_col
+
+-- !shape --
+PhysicalResultSink
+--hashAgg[LOCAL]
+----filter(( not fk IS NULL))
+------PhysicalOlapScan[fkt]
+
+-- !res --
+1 1
+2 2
+3 3
+
+-- !name --
+with_pk_col
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt.fk)) otherCondition=()
+----PhysicalOlapScan[pkt]
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalUnion
+----------PhysicalOlapScan[fkt]
+----------PhysicalOlapScan[fkt]
+
+-- !res --
+1 John 1
+2 Alice 2
+3 Bob 3
+
+-- !name --
+fk with window
+
+-- !shape --
+PhysicalResultSink
+--PhysicalWindow
+----PhysicalQuickSort[LOCAL_SORT]
+------filter(( not fk IS NULL))
+--------PhysicalOlapScan[fkt]
+
+-- !res --
+1 1 1
+1 2 1
+2 1 2
+2 2 2
+3 1 3
+
+-- !name --
+fk with limit
+
+-- !shape --
+PhysicalResultSink
+--filter(( not fk IS NULL))
+----PhysicalLimit[GLOBAL]
+------PhysicalLimit[LOCAL]
+--------PhysicalOlapScan[fkt]
+
+-- !res --
+
+-- !name --
+pk with filter that same as fk
+
+-- !shape --
+PhysicalResultSink
+--filter(( not fk IS NULL) and (fkt.fk = 1))
+----PhysicalOlapScan[fkt]
+
+-- !res --
+1 John 1
+1 John 1
+
+-- !name --
+pk with filter that included same as fk
+
+-- !shape --
+PhysicalResultSink
+--filter(( not fk IS NULL) and (cast(f as DOUBLE) = 1) and (fkt.fk = 1))
+----PhysicalOlapScan[fkt]
+
+-- !res --
+
+-- !name --
+pk with filter that not same as fk
+
+-- !shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt.fk)) otherCondition=()
build RFs:RF0 fk->[pk]
+----filter((cast(p as DOUBLE) = 1) and (pkt.pk = 1))
+------PhysicalOlapScan[pkt] apply RFs: RF0
+----filter((cast(f as DOUBLE) = 1) and (fkt.fk = 1))
+------PhysicalOlapScan[fkt]
+
+-- !res --
+
diff --git
a/regression-test/suites/nereids_rules_p0/pkfk/eliminate_inner.groovy
b/regression-test/suites/nereids_rules_p0/pkfk/eliminate_inner.groovy
new file mode 100644
index 00000000000..30a96f5405c
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/pkfk/eliminate_inner.groovy
@@ -0,0 +1,113 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("eliminate_inner") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+ sql "SET ignore_shape_nodes='PhysicalDistribute,PhysicalProject'"
+ sql "SET disable_join_reorder=true"
+
+ sql """
+ DROP TABLE IF EXISTS pkt
+ """
+
+ sql """
+ DROP TABLE IF EXISTS fkt
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS pkt(
+ `pk` int(11) NULL,
+ `p` text NULL
+ ) ENGINE = OLAP
+ DISTRIBUTED BY HASH(pk) BUCKETS 4
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS fkt(
+ `fk` int(11) NULL,
+ `f` text NULL
+ ) ENGINE = OLAP
+ DISTRIBUTED BY HASH(fk) BUCKETS 4
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS fkt_not_null(
+ `fk` int(11) NOT NULL,
+ `f` text NULL
+ ) ENGINE = OLAP
+ DISTRIBUTED BY HASH(fk) BUCKETS 4
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql """
+ INSERT INTO pkt VALUES (1, 'John'), (2, 'Alice'), (3, 'Bob'), (null,
'Jack');
+ """
+ sql """
+ INSERT INTO fkt VALUES (1, 'John'), (2, 'Alice'), (null, 'Bob');
+ """
+ sql """
+ INSERT INTO fkt VALUES (1, 'John'), (2, 'Alice'), (3, 'Bob');
+ """
+ sql """
+ alter table pkt add constraint pk primary key (pk)
+ """
+ sql """
+ alter table fkt add constraint fk foreign key (fk) references pkt(pk)
+ """
+ def check_shape_res = { sql, name ->
+ qt_name "select \"${name}\""
+ qt_shape "explain shape plan ${sql}"
+ order_qt_res "${sql}"
+ }
+ def simple_case = """
+ select * from pkt inner join fkt on pkt.pk = fkt.fk;
+ """
+ // nullable
+ check_shape_res("select fkt_not_null.* from pkt inner join fkt_not_null on
pkt.pk = fkt_not_null.fk;", "simple_case")
+ check_shape_res("select fkt_not_null.*, pkt.pk from pkt inner join
fkt_not_null on pkt.pk = fkt_not_null.fk;", "with_pk_col")
+ check_shape_res("select fkt_not_null.*, pkt.pk from pkt inner join (select
fkt_not_null1.* from fkt_not_null as fkt_not_null1 join fkt_not_null as
fkt_not_null2 on fkt_not_null1.fk = fkt_not_null2.fk) fkt_not_null on pkt.pk =
fkt_not_null.fk;", "with_pk_col")
+ check_shape_res("select fkt_not_null.*, pkt.pk from pkt inner join (select
fkt_not_null1.* from fkt_not_null as fkt_not_null1 join fkt_not_null as
fkt_not_null2 on fkt_not_null1.fk = fkt_not_null2.fk where fkt_not_null1.fk >
1) fkt_not_null on pkt.pk = fkt_not_null.fk;", "with_pk_col")
+ check_shape_res("select fkt_not_null.*, pkt.pk from pkt inner join (select
fkt_not_null1.fk from fkt_not_null as fkt_not_null1 group by fkt_not_null1.fk)
fkt_not_null on pkt.pk = fkt_not_null.fk;", "with_pk_col")
+ check_shape_res("select fkt_not_null.*, pkt.pk from pkt inner join (select
* from fkt_not_null union select * from fkt_not_null) fkt_not_null on pkt.pk =
fkt_not_null.fk;", "with_pk_col")
+ check_shape_res("select fkt_not_null.*, pkt.pk from pkt inner join (select
fk, ROW_NUMBER() OVER (PARTITION BY fk ORDER BY fk) AS RowNum from
fkt_not_null) fkt_not_null on pkt.pk = fkt_not_null.fk;", "fk with window")
+ check_shape_res("select fkt_not_null.*, pkt.pk from pkt inner join (select
fk from fkt_not_null limit 1) fkt_not_null on pkt.pk = fkt_not_null.fk;", "fk
with limit")
+ check_shape_res("select fkt_not_null.*, pkt.pk from pkt inner join
fkt_not_null on pkt.pk = fkt_not_null.fk where pkt.pk = 1 and fkt_not_null.fk =
1;", "pk with filter that same as fk")
+ check_shape_res("select fkt_not_null.*, pkt.pk from pkt inner join
fkt_not_null on pkt.pk = fkt_not_null.fk where pkt.pk = 1 and fkt_not_null.fk =
1 and fkt_not_null.f = 1;", "pk with filter that included same as fk")
+ check_shape_res("select fkt_not_null.*, pkt.pk from pkt inner join
fkt_not_null on pkt.pk = fkt_not_null.fk where pkt.p = 1 and fkt_not_null.fk =
1 and fkt_not_null.f = 1;;", "pk with filter that not same as fk")
+
+ // not nullable
+ check_shape_res("select fkt.* from pkt inner join fkt on pkt.pk =
fkt.fk;", "simple_case")
+ check_shape_res("select fkt.*, pkt.pk from pkt inner join fkt on pkt.pk =
fkt.fk;", "with_pk_col")
+ check_shape_res("select fkt.*, pkt.pk from pkt inner join (select fkt1.*
from fkt as fkt1 join fkt as fkt2 on fkt1.fk = fkt2.fk) fkt on pkt.pk =
fkt.fk;", "with_pk_col")
+ check_shape_res("select fkt.*, pkt.pk from pkt inner join (select fkt1.*
from fkt as fkt1 join fkt as fkt2 on fkt1.fk = fkt2.fk where fkt1.fk > 1) fkt
on pkt.pk = fkt.fk;", "with_pk_col")
+ check_shape_res("select fkt.*, pkt.pk from pkt inner join (select fkt1.fk
from fkt as fkt1 group by fkt1.fk) fkt on pkt.pk = fkt.fk;", "with_pk_col")
+ check_shape_res("select fkt.*, pkt.pk from pkt inner join (select * from
fkt union select * from fkt) fkt on pkt.pk = fkt.fk;", "with_pk_col")
+ check_shape_res("select fkt.*, pkt.pk from pkt inner join (select fk,
ROW_NUMBER() OVER (PARTITION BY fk ORDER BY fk) AS RowNum from fkt) fkt on
pkt.pk = fkt.fk;", "fk with window")
+ check_shape_res("select fkt.*, pkt.pk from pkt inner join (select fk from
fkt limit 1) fkt on pkt.pk = fkt.fk;", "fk with limit")
+ check_shape_res("select fkt.*, pkt.pk from pkt inner join fkt on pkt.pk =
fkt.fk where pkt.pk = 1 and fkt.fk = 1;", "pk with filter that same as fk")
+ check_shape_res("select fkt.*, pkt.pk from pkt inner join fkt on pkt.pk =
fkt.fk where pkt.pk = 1 and fkt.fk = 1 and fkt.f = 1;", "pk with filter that
included same as fk")
+ check_shape_res("select fkt.*, pkt.pk from pkt inner join fkt on pkt.pk =
fkt.fk where pkt.p = 1 and fkt.fk = 1 and fkt.f = 1;;", "pk with filter that
not same as fk")
+
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
