This is an automated email from the ASF dual-hosted git repository. mblow pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/asterixdb.git
commit 934eb698c328e1b854d40a567b4af42db6670fb9 Author: preetham0202 <[email protected]> AuthorDate: Mon Dec 16 16:32:45 2024 +0530 [ASTERIXDB-3538][COMP] Remove subplan operators that produce empty variables - user model changes: no - storage format changes: no - interface changes: no Details: Subplans that dont produce any variables are redundant, we can eliminate them and optimize the query plan. Ext-ref: MB-64328 Change-Id: Ie123ccaafbd528d127bd91ecf0a6b5e93c497683 Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/19291 Reviewed-by: Ali Alsuliman <[email protected]> Tested-by: Ali Alsuliman <[email protected]> --- .../query-ASTERIXDB-3538.1.ddl.sqlpp | 67 ++++++++++++++++++++++ .../query-ASTERIXDB-3538.2.update.sqlpp | 31 ++++++++++ .../query-ASTERIXDB-3538.3.query.sqlpp | 25 ++++++++ .../query-ASTERIXDB-3538.4.query.sqlpp | 25 ++++++++ .../query-ASTERIXDB-3538.5.query.sqlpp | 25 ++++++++ .../query-ASTERIXDB-3538.6.query.sqlpp | 24 ++++++++ .../query-ASTERIXDB-3538.3.plan | 24 ++++++++ .../query-ASTERIXDB-3538.4.adm | 8 +++ .../query-ASTERIXDB-3538.5.plan | 41 +++++++++++++ .../query-ASTERIXDB-3538.6.adm | 4 ++ .../src/test/resources/runtimets/sqlpp_queries.xml | 5 ++ .../rules/RemoveUnusedAssignAndAggregateRule.java | 4 ++ .../dataflow/common/utils/FrameDebugUtils.java | 2 +- 13 files changed, 284 insertions(+), 1 deletion(-) diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.1.ddl.sqlpp new file mode 100644 index 0000000000..92ebdfd805 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.1.ddl.sqlpp @@ -0,0 +1,67 @@ +/* + * 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. + */ + +drop dataverse test if exists; +create dataverse test; + +use test; + +create type t1 as { + _id: uuid + }; + +create dataset dat1(t1) primary key _id autogenerated; + +CREATE VIEW dat2 (id STRING, a STRING) DEFAULT NULL PRIMARY KEY (id) NOT ENFORCED AS +SELECT + s.id, + a +FROM dat1 s + LET + w1 = ( + SELECT SUM(x.a) , COUNT(x) , SUM(x.b) , + SUM(x.c), SUM(x.d) + FROM s.a x + )[0], + w2 = ( + SELECT SUM(x.a) , COUNT(x) + FROM s.b x + )[0], + w3 = (SELECT SUM(x.a) FROM s.c x), + w4= (SELECT count(*) FROM dat1); + +CREATE VIEW dat3 (id STRING, a STRING, d INT) DEFAULT NULL PRIMARY KEY (id) NOT ENFORCED AS +SELECT + s.id, + a, + s.d +FROM dat1 s + LET + w1 = ( + SELECT SUM(x.a) , COUNT(x) , SUM(x.b) , + SUM(x.c), SUM(x.d) + FROM s.a x + )[0], + w2 = ( + SELECT SUM(x.a) , COUNT(x) + FROM s.b x + )[0], + w3 = (SELECT SUM(x.a) FROM s.c x), + w4= (SELECT RAW count(*) FROM s.e)[0] +WHERE s.d <= w4; diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.2.update.sqlpp new file mode 100644 index 0000000000..5901544ffa --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.2.update.sqlpp @@ -0,0 +1,31 @@ +/* + * 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. + */ + +use test; + +insert into dat1 ([ +{ "id": "1", "a": "2", "c":{ "b": 111 } , "d" :8 , "e" : [1] }, + { "id": "2", "a": "3", "c":{ "b": 222 } , "d" :7 , "e" : [2,3] }, + { "id": "3", "a": "4", "c":{ "b": 333 } , "d" :6 , "e" : [4,5,6] }, + { "id": "4", "a": "5","c": { "b": 444 } , "d" :5 , "e" : [7,8,9,10] }, + { "id": "5", "a": "6", "c":{ "b": 555 } , "d" :4 , "e" : [10,11,12,13,14] }, + { "id": "6", "a": "7","c": { "b": 666 } , "d" :3 , "e" : [13,14,15,16,17,18] }, + { "id": "7", "a": "8","c": { "b": 777 } , "d" :2 , "e" : [16,17,18,19,20,21,22] }, + { "id": "8", "a": "9", "c":{ "b": 888 } , "d" :1 , "e" : [19,20,21,22,23,24,25,26] } + ]); diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.query.sqlpp new file mode 100644 index 0000000000..a7397a08ef --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.query.sqlpp @@ -0,0 +1,25 @@ +/* + * 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. + */ + + +use test; + + +EXPLAIN +SELECT id FROM dat2; diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.4.query.sqlpp new file mode 100644 index 0000000000..7aa5872ec4 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.4.query.sqlpp @@ -0,0 +1,25 @@ +/* + * 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. + */ + + +use test; + + + +SELECT * FROM dat2 order by id; diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.query.sqlpp new file mode 100644 index 0000000000..d71470c40e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.query.sqlpp @@ -0,0 +1,25 @@ +/* + * 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. + */ + + +use test; + +EXPLAIN +SELECT * FROM dat3; + diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.6.query.sqlpp new file mode 100644 index 0000000000..deacf2f4b1 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.6.query.sqlpp @@ -0,0 +1,24 @@ +/* + * 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. + */ + + +use test; + + +SELECT * FROM dat3 order by id; diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.plan new file mode 100644 index 0000000000..c5f8436831 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.plan @@ -0,0 +1,24 @@ +distribute result [$$297] [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1] +-- DISTRIBUTE_RESULT |PARTITIONED| + exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + project ([$$297]) [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1] + -- STREAM_PROJECT |PARTITIONED| + assign [$$297] <- [{"id": $$331}] [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1] + -- ASSIGN |PARTITIONED| + select (not(is-null($$331))) [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1] + -- STREAM_SELECT |PARTITIONED| + project ([$$331]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 2.1] + -- STREAM_PROJECT |PARTITIONED| + assign [$$331] <- [string-default-null($$s.getField("id"))] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 2.1] + -- ASSIGN |PARTITIONED| + project ([$$s]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 2.1] + -- STREAM_PROJECT |PARTITIONED| + exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 2.1] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + data-scan []<-[$$300, $$s] <- test.dat1 [cardinality: 1000000.0, op-cost: 2.1, total-cost: 2.1] + -- DATASOURCE_SCAN |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- EMPTY_TUPLE_SOURCE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.4.adm new file mode 100644 index 0000000000..934eb5797e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.4.adm @@ -0,0 +1,8 @@ +{ "dat2": { "id": "1", "a": "2" } } +{ "dat2": { "id": "2", "a": "3" } } +{ "dat2": { "id": "3", "a": "4" } } +{ "dat2": { "id": "4", "a": "5" } } +{ "dat2": { "id": "5", "a": "6" } } +{ "dat2": { "id": "6", "a": "7" } } +{ "dat2": { "id": "7", "a": "8" } } +{ "dat2": { "id": "8", "a": "9" } } diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.plan new file mode 100644 index 0000000000..302961126a --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.plan @@ -0,0 +1,41 @@ +distribute result [$$303] [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1] +-- DISTRIBUTE_RESULT |PARTITIONED| + exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + project ([$$303]) [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1] + -- STREAM_PROJECT |PARTITIONED| + assign [$$303] <- [{"dat3": {"id": $$337, "a": string-default-null($$331), "d": int64-default-null($$304)}}] [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1] + -- ASSIGN |PARTITIONED| + project ([$$337, $$331, $$304]) [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1] + -- STREAM_PROJECT |PARTITIONED| + select (le($$304, get-item($$281, 0))) [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1] + -- STREAM_SELECT |PARTITIONED| + project ([$$337, $$331, $$304, $$281]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 2.1] + -- STREAM_PROJECT |PARTITIONED| + subplan { + aggregate [$$281] <- [listify($$316)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- AGGREGATE |LOCAL| + aggregate [$$316] <- [agg-sql-count(1)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- AGGREGATE |LOCAL| + unnest $$319 <- scan-collection($$329) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- UNNEST |LOCAL| + nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- NESTED_TUPLE_SOURCE |LOCAL| + } [cardinality: 1000000.0, op-cost: 0.0, total-cost: 2.1] + -- SUBPLAN |PARTITIONED| + select (not(is-null($$337))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_SELECT |PARTITIONED| + project ([$$337, $$331, $$304, $$329]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 2.1] + -- STREAM_PROJECT |PARTITIONED| + assign [$$337, $$331, $$304, $$329] <- [string-default-null($$s.getField("id")), $$s.getField("a"), $$s.getField("d"), $$s.getField("e")] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 2.1] + -- ASSIGN |PARTITIONED| + project ([$$s]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 2.1] + -- STREAM_PROJECT |PARTITIONED| + exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 2.1] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + data-scan []<-[$$305, $$s] <- test.dat1 [cardinality: 1000000.0, op-cost: 2.1, total-cost: 2.1] + -- DATASOURCE_SCAN |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- EMPTY_TUPLE_SOURCE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.6.adm new file mode 100644 index 0000000000..65999fcc93 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.6.adm @@ -0,0 +1,4 @@ +{ "dat3": { "id": "5", "a": "6", "d": 4 } } +{ "dat3": { "id": "6", "a": "7", "d": 3 } } +{ "dat3": { "id": "7", "a": "8", "d": 2 } } +{ "dat3": { "id": "8", "a": "9", "d": 1 } } diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml index b0c4c44adf..85ac43b821 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml +++ b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml @@ -11544,6 +11544,11 @@ <output-dir compare="Text">query-ASTERIXDB-3006</output-dir> </compilation-unit> </test-case> + <test-case FilePath="subquery"> + <compilation-unit name="query-ASTERIXDB-3538"> + <output-dir compare="Text">query-ASTERIXDB-3538</output-dir> + </compilation-unit> + </test-case> </test-group> <test-group name="subset-collection"> <test-case FilePath="subset-collection"> diff --git a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/RemoveUnusedAssignAndAggregateRule.java b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/RemoveUnusedAssignAndAggregateRule.java index 84346fdbcd..ab6d91ae24 100644 --- a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/RemoveUnusedAssignAndAggregateRule.java +++ b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/RemoveUnusedAssignAndAggregateRule.java @@ -269,6 +269,10 @@ public class RemoveUnusedAssignAndAggregateRule implements IAlgebraicRewriteRule isTransformed = true; } return winOp.getVariables().size() + winOp.getNestedPlans().size(); + case SUBPLAN: + ListSet<LogicalVariable> producedVars = new ListSet<>(); + VariableUtilities.getProducedVariables(op, producedVars); + return producedVars.size(); default: break; } diff --git a/hyracks-fullstack/hyracks/hyracks-dataflow-common/src/main/java/org/apache/hyracks/dataflow/common/utils/FrameDebugUtils.java b/hyracks-fullstack/hyracks/hyracks-dataflow-common/src/main/java/org/apache/hyracks/dataflow/common/utils/FrameDebugUtils.java index d09b890882..66ba62c62f 100644 --- a/hyracks-fullstack/hyracks/hyracks-dataflow-common/src/main/java/org/apache/hyracks/dataflow/common/utils/FrameDebugUtils.java +++ b/hyracks-fullstack/hyracks/hyracks-dataflow-common/src/main/java/org/apache/hyracks/dataflow/common/utils/FrameDebugUtils.java @@ -147,7 +147,7 @@ public class FrameDebugUtils { } sb.append("}"); } - sb.append("\n"); + sb.append("]\n"); } /**
