This is an automated email from the ASF dual-hosted git repository. dlych pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/asterixdb.git
commit 486aac395b41c35c66e1db94ab5a0b801a8d1b42 Author: Dmitry Lychagin <[email protected]> AuthorDate: Mon Jan 18 14:01:19 2021 -0800 [NO ISSUE][COMP] Window opeator in subquery gives wrong result - user model changes: no - storage format changes: no - interface changes: no Details: - Fix Window operator handling during subplan removal Change-Id: Idf6763c12062a7e4658cb3db008fda9cce62b448 Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/9664 Integration-Tests: Jenkins <[email protected]> Tested-by: Jenkins <[email protected]> Reviewed-by: Dmitry Lychagin <[email protected]> Reviewed-by: Ali Alsuliman <[email protected]> (cherry picked from commit 118a06f6d4b7bd215c46a49ebc73b5779c489de4) Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10004 --- .../subplan/InlineAllNtsInSubplanVisitor.java | 12 +++- .../queries/subquery/query-ASTERIXDB-2815-2.sqlpp | 42 +++++++++++++ .../queries/subquery/query-ASTERIXDB-2815-3.sqlpp | 45 +++++++++++++ .../results/subquery/query-ASTERIXDB-2815-2.plan | 72 +++++++++++++++++++++ .../results/subquery/query-ASTERIXDB-2815-3.plan | 73 ++++++++++++++++++++++ .../query-ASTERIXDB-2815.4.query.sqlpp | 32 ++++++++++ .../query-ASTERIXDB-2815.5.query.sqlpp | 35 +++++++++++ .../query-ASTERIXDB-2815.4.adm | 5 ++ .../query-ASTERIXDB-2815.5.adm | 5 ++ 9 files changed, 320 insertions(+), 1 deletion(-) diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineAllNtsInSubplanVisitor.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineAllNtsInSubplanVisitor.java index f7cbb9d..02dc6f6 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineAllNtsInSubplanVisitor.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineAllNtsInSubplanVisitor.java @@ -655,7 +655,17 @@ class InlineAllNtsInSubplanVisitor implements IQueryOperatorVisitor<ILogicalOper @Override public ILogicalOperator visitWindowOperator(WindowOperator op, Void arg) throws AlgebricksException { - return visitSingleInputOperator(op); + visitSingleInputOperator(op); + List<LogicalVariable> partitionByVars = op.getPartitionVarList(); + for (LogicalVariable keyVar : correlatedKeyVars) { + if (!partitionByVars.contains(keyVar)) { + VariableReferenceExpression keyVarRef = new VariableReferenceExpression(keyVar); + keyVarRef.setSourceLocation(op.getSourceLocation()); + op.getPartitionExpressions().add(new MutableObject<>(keyVarRef)); + } + } + context.computeAndSetTypeEnvironmentForOperator(op); + return op; } /** diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/query-ASTERIXDB-2815-2.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/query-ASTERIXDB-2815-2.sqlpp new file mode 100644 index 0000000..74034d6 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/query-ASTERIXDB-2815-2.sqlpp @@ -0,0 +1,42 @@ +/* + * 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 RawTweet(t1) primary key _id autogenerated; + +create dataset Evidence(t1) primary key _id autogenerated; + +create dataset Verification(t1) primary key _id autogenerated; + +select t.id, array_sort(ranks) ranks +from RawTweet t +let ranks = ( + select value rank() over(order by e.url) + from Verification v, v.evidence ve, Evidence e + where t.id = v.tweet_id and ve = e.ev_id +) +order by t.id; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/query-ASTERIXDB-2815-3.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/query-ASTERIXDB-2815-3.sqlpp new file mode 100644 index 0000000..6b0501e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/query-ASTERIXDB-2815-3.sqlpp @@ -0,0 +1,45 @@ +/* + * 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 RawTweet(t1) primary key _id autogenerated; + +create dataset Evidence(t1) primary key _id autogenerated; + +create dataset Verification(t1) primary key _id autogenerated; + +select t.id, array_sort(ranks) ranks +from RawTweet t +let ranks = ( + select value rank() over( + partition by (tobigint(substring(e.url, -4)) % 2) + order by e.url + ) + from Verification v, v.evidence ve, Evidence e + where t.id = v.tweet_id and ve = e.ev_id +) +order by t.id; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/query-ASTERIXDB-2815-2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/query-ASTERIXDB-2815-2.plan new file mode 100644 index 0000000..0a23b38 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/query-ASTERIXDB-2815-2.plan @@ -0,0 +1,72 @@ +-- DISTRIBUTE_RESULT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- SORT_MERGE_EXCHANGE [$$82(ASC) ] |PARTITIONED| + -- STABLE_SORT [$$82(ASC)] |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- PRE_CLUSTERED_GROUP_BY[$$78] |PARTITIONED| + { + -- AGGREGATE |LOCAL| + -- STREAM_SELECT |LOCAL| + -- NESTED_TUPLE_SOURCE |LOCAL| + } + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- HYBRID_HASH_JOIN [$$78][$$89] |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- REPLICATE |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- DATASOURCE_SCAN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- EMPTY_TUPLE_SOURCE |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- WINDOW_STREAM |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STABLE_SORT [$$89(ASC), $$e.url(ASC)] |PARTITIONED| + -- HASH_PARTITION_EXCHANGE [$$89] |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- HYBRID_HASH_JOIN [$$ve][$$81] |PARTITIONED| + -- HASH_PARTITION_EXCHANGE [$$ve] |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- UNNEST |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- HYBRID_HASH_JOIN [$$87][$$83] |PARTITIONED| + -- HASH_PARTITION_EXCHANGE [$$87] |PARTITIONED| + -- REPLICATE |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- DATASOURCE_SCAN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- EMPTY_TUPLE_SOURCE |PARTITIONED| + -- HASH_PARTITION_EXCHANGE [$$83] |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- DATASOURCE_SCAN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- EMPTY_TUPLE_SOURCE |PARTITIONED| + -- HASH_PARTITION_EXCHANGE [$$81] |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- DATASOURCE_SCAN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- EMPTY_TUPLE_SOURCE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/query-ASTERIXDB-2815-3.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/query-ASTERIXDB-2815-3.plan new file mode 100644 index 0000000..3c2caaa --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/query-ASTERIXDB-2815-3.plan @@ -0,0 +1,73 @@ +-- DISTRIBUTE_RESULT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- SORT_MERGE_EXCHANGE [$$88(ASC) ] |PARTITIONED| + -- STABLE_SORT [$$88(ASC)] |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- PRE_CLUSTERED_GROUP_BY[$$84] |PARTITIONED| + { + -- AGGREGATE |LOCAL| + -- STREAM_SELECT |LOCAL| + -- NESTED_TUPLE_SOURCE |LOCAL| + } + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- HYBRID_HASH_JOIN [$$84][$$95] |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- REPLICATE |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- DATASOURCE_SCAN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- EMPTY_TUPLE_SOURCE |PARTITIONED| + -- HASH_PARTITION_EXCHANGE [$$95] |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- WINDOW_STREAM |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STABLE_SORT [$$72(ASC), $$95(ASC), $$83(ASC)] |PARTITIONED| + -- HASH_PARTITION_EXCHANGE [$$72, $$95] |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- HYBRID_HASH_JOIN [$$ve][$$87] |PARTITIONED| + -- HASH_PARTITION_EXCHANGE [$$ve] |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- UNNEST |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- HYBRID_HASH_JOIN [$$93][$$89] |PARTITIONED| + -- HASH_PARTITION_EXCHANGE [$$93] |PARTITIONED| + -- REPLICATE |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- DATASOURCE_SCAN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- EMPTY_TUPLE_SOURCE |PARTITIONED| + -- HASH_PARTITION_EXCHANGE [$$89] |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- DATASOURCE_SCAN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- EMPTY_TUPLE_SOURCE |PARTITIONED| + -- HASH_PARTITION_EXCHANGE [$$87] |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ASSIGN |PARTITIONED| + -- STREAM_PROJECT |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- DATASOURCE_SCAN |PARTITIONED| + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + -- EMPTY_TUPLE_SOURCE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-2815/query-ASTERIXDB-2815.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-2815/query-ASTERIXDB-2815.4.query.sqlpp new file mode 100644 index 0000000..d94a669 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-2815/query-ASTERIXDB-2815.4.query.sqlpp @@ -0,0 +1,32 @@ +/* + * 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. + */ +/* + * Test decorrelation of window functions + */ + +use test; + +select t.id, array_sort(ranks) ranks +from RawTweet t +let ranks = ( + select value rank() over(order by e.url) + from Verification v, v.evidence ve, Evidence e + where t.id = v.tweet_id and ve = e.ev_id +) +order by t.id; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-2815/query-ASTERIXDB-2815.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-2815/query-ASTERIXDB-2815.5.query.sqlpp new file mode 100644 index 0000000..3bec7f9 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-2815/query-ASTERIXDB-2815.5.query.sqlpp @@ -0,0 +1,35 @@ +/* + * 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. + */ +/* + * Test decorrelation of window functions + */ + +use test; + +select t.id, array_sort(ranks) ranks +from RawTweet t +let ranks = ( + select value rank() over( + partition by (tobigint(substring(e.url, -4)) % 2) + order by e.url + ) + from Verification v, v.evidence ve, Evidence e + where t.id = v.tweet_id and ve = e.ev_id +) +order by t.id; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-2815/query-ASTERIXDB-2815.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-2815/query-ASTERIXDB-2815.4.adm new file mode 100644 index 0000000..c35bf4e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-2815/query-ASTERIXDB-2815.4.adm @@ -0,0 +1,5 @@ +{ "ranks": [ 1, 2 ], "id": 1 } +{ "ranks": [ 1, 2, 3, 4 ], "id": 2 } +{ "ranks": [ ], "id": 3 } +{ "ranks": [ 1, 2, 3, 4, 5, 6 ], "id": 4 } +{ "ranks": [ 1 ], "id": 5 } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-2815/query-ASTERIXDB-2815.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-2815/query-ASTERIXDB-2815.5.adm new file mode 100644 index 0000000..9ddd8aa --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-2815/query-ASTERIXDB-2815.5.adm @@ -0,0 +1,5 @@ +{ "ranks": [ 1, 1 ], "id": 1 } +{ "ranks": [ 1, 1, 2, 2 ], "id": 2 } +{ "ranks": [ ], "id": 3 } +{ "ranks": [ 1, 1, 2, 2, 3, 3 ], "id": 4 } +{ "ranks": [ 1 ], "id": 5 } \ No newline at end of file
