>From Ali Alsuliman <[email protected]>: Ali Alsuliman has submitted this change. ( https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/21130?usp=email )
Change subject: [ASTERIXDB-3752][COMP] Maintain correlated keys with LIMIT ...................................................................... [ASTERIXDB-3752][COMP] Maintain correlated keys with LIMIT - user model changes: no - storage format changes: no - interface changes: no Details: When a correlated subquery has a LIMIT, the LIMIT operator is enclosed inside a GROUP-BY operator. The GROUP-BY operator re-assigns all current correlated keys to new variables. Access to the previous correlated keys needs to be added to the assign operator that recovers the input live variables. Ext-ref: MB-71438 Change-Id: I3457e3bf027a3a4e0d2552f62990e97adb34fe5f Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/21093 Reviewed-by: Ali Alsuliman <[email protected]> Integration-Tests: Jenkins <[email protected]> Tested-by: Ali Alsuliman <[email protected]> Reviewed-by: Ian Maxon <[email protected]> (cherry picked from commit 90af3734e3abf6054d6479bd82b6d52e40ef8a4e) Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/21130 Tested-by: Jenkins <[email protected]> --- M asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineAllNtsInSubplanVisitor.java A asterixdb/asterix-app/src/test/resources/optimizerts/queries/limit/ASTERIXDB-3752-LIMIT-in-subquery.sqlpp A asterixdb/asterix-app/src/test/resources/optimizerts/results/limit/ASTERIXDB-3752-LIMIT-in-subquery.plan A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.1.ddl.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.2.update.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.3.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/results/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.3.adm M asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml 8 files changed, 285 insertions(+), 7 deletions(-) Approvals: Ian Maxon: Looks good to me, approved Jenkins: Verified Objections: Anon. E. Moose #1000171: Violations found 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 497d21c..07eba63 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 @@ -274,7 +274,7 @@ } // Get live variables before limit (or running aggregate) operator. - Set<LogicalVariable> inputLiveVars = new HashSet<LogicalVariable>(); + Set<LogicalVariable> inputLiveVars = new HashSet<>(); VariableUtilities.getSubplanLocalLiveVariables(op.getInputs().get(0).getValue(), inputLiveVars); // Creates a record construction assign operator. @@ -286,9 +286,9 @@ assignOp.getInputs().add(new MutableObject<>(inputOp)); // Rewrites limit (or running aggregate) to a group-by with limit (or running aggregate) as its nested operator. - Pair<ILogicalOperator, LogicalVariable> gbyOpAndAggVar = + Pair<GroupByOperator, LogicalVariable> gbyOpAndAggVar = wrapLimitOrRunningAggregateInGroupBy(op, recordVar, inputLiveVars); - ILogicalOperator gbyOp = gbyOpAndAggVar.first; + GroupByOperator gbyOp = gbyOpAndAggVar.first; LogicalVariable aggVar = gbyOpAndAggVar.second; gbyOp.getInputs().add(new MutableObject<>(assignOp)); @@ -301,7 +301,7 @@ // Adds field accesses to recover input live variables. ILogicalOperator fieldAccessAssignOp = - createFieldAccessAssignOperator(unnestVar, inputLiveVars, op.getSourceLocation()); + createFieldAccessAssignOperator(unnestVar, inputLiveVars, op.getSourceLocation(), gbyOp); fieldAccessAssignOp.getInputs().add(new MutableObject<>(unnestOp)); OperatorManipulationUtil.computeTypeEnvironmentBottomUp(fieldAccessAssignOp, context); @@ -331,7 +331,7 @@ return new Pair<>(assignOp, recordVar); } - private Pair<ILogicalOperator, LogicalVariable> wrapLimitOrRunningAggregateInGroupBy(ILogicalOperator op, + private Pair<GroupByOperator, LogicalVariable> wrapLimitOrRunningAggregateInGroupBy(ILogicalOperator op, LogicalVariable recordVar, Set<LogicalVariable> inputLiveVars) throws AlgebricksException { SourceLocation sourceLoc = op.getSourceLocation(); GroupByOperator gbyOp = new GroupByOperator(); @@ -417,7 +417,7 @@ } private ILogicalOperator createFieldAccessAssignOperator(LogicalVariable recordVar, - Set<LogicalVariable> inputLiveVars, SourceLocation sourceLoc) { + Set<LogicalVariable> inputLiveVars, SourceLocation sourceLoc, GroupByOperator gbyOp) { List<LogicalVariable> fieldAccessVars = new ArrayList<>(); List<Mutable<ILogicalExpression>> fieldAccessExprs = new ArrayList<>(); // Adds field access by name. @@ -426,7 +426,21 @@ // field Var LogicalVariable newVar = context.newVar(); fieldAccessVars.add(newVar); - // fieldAcess expr + List<LogicalVariable> groupByVarList = gbyOp.getGroupByVarList(); + int i = groupByVarList.indexOf(inputLiveVar); + if (i >= 0) { + // this means the input live variable used to be in correlatedKeyVars, but was removed from + // correlatedKeyVars since it was added as a group-by key and re-assigned to a new variable in the + // group-by operator, e.g. GROUP-BY [$1 = $inputLiveVar]. In this case, we can directly access it + // without field access expression via $1. + LogicalVariable gbyOutVar = gbyOp.getGroupByList().get(i).getFirst(); + VariableReferenceExpression gbyOutVarRef = new VariableReferenceExpression(gbyOutVar); + gbyOutVarRef.setSourceLocation(sourceLoc); + fieldAccessExprs.add(new MutableObject<>(gbyOutVarRef)); + updateInputToOutputVarMapping(gbyOutVar, newVar, false); + continue; + } + // fieldAccess expr List<Mutable<ILogicalExpression>> argRefs = new ArrayList<>(); VariableReferenceExpression recordVarRef = new VariableReferenceExpression(recordVar); recordVarRef.setSourceLocation(sourceLoc); diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/limit/ASTERIXDB-3752-LIMIT-in-subquery.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/limit/ASTERIXDB-3752-LIMIT-in-subquery.sqlpp new file mode 100644 index 0000000..6055521 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/limit/ASTERIXDB-3752-LIMIT-in-subquery.sqlpp @@ -0,0 +1,37 @@ +/* + * 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 MovieTest IF EXISTS; +CREATE DATAVERSE MovieTest; + +USE MovieTest; +CREATE TYPE MovieType AS {id: int}; +CREATE DATASET movies (MovieType) PRIMARY KEY id; + +SET `compiler.sort.parallel` "false"; +USE MovieTest; +EXPLAIN +FROM movies m +SELECT m.*, (FROM movies im +LET diff = ABS(m.runtime - im.runtime) +SELECT im.* +ORDER BY diff, im.id +LIMIT 3 +) AS close +ORDER BY m.id; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/limit/ASTERIXDB-3752-LIMIT-in-subquery.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/limit/ASTERIXDB-3752-LIMIT-in-subquery.plan new file mode 100644 index 0000000..c7ef1bb --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/limit/ASTERIXDB-3752-LIMIT-in-subquery.plan @@ -0,0 +1,110 @@ +distribute result [$$53] +-- DISTRIBUTE_RESULT |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + project ([$$53]) + -- STREAM_PROJECT |PARTITIONED| + assign [$$53] <- [object-concat-strict(if-missing-or-null(to-object($$m), cast({ })), {"close": $$52})] + -- ASSIGN |PARTITIONED| + project ([$$52, $$m]) + -- STREAM_PROJECT |PARTITIONED| + exchange + -- SORT_MERGE_EXCHANGE [$$77(ASC) ] |PARTITIONED| + group by ([$$77 := $$55]) decor ([$$m]) { + aggregate [$$52] <- [listify($$69)] + -- AGGREGATE |LOCAL| + select (not(is-missing($$76))) + -- STREAM_SELECT |LOCAL| + nested tuple source + -- NESTED_TUPLE_SOURCE |LOCAL| + } + -- PRE_CLUSTERED_GROUP_BY[$$55] |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + order (ASC, $$55) (ASC, $$70) (ASC, $$71) + -- STABLE_SORT [$$55(ASC), $$70(ASC), $$71(ASC)] |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + project ([$$m, $$69, $$70, $$71, $$76, $$55]) + -- STREAM_PROJECT |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + left outer join (eq($$55, $$65)) + -- HYBRID_HASH_JOIN [$$55][$$65] |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + data-scan []<-[$$55, $$m] <- MovieTest.movies + -- DATASOURCE_SCAN |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + empty-tuple-source + -- EMPTY_TUPLE_SOURCE |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + project ([$$69, $$70, $$71, $$76, $$65]) + -- STREAM_PROJECT |PARTITIONED| + assign [$$76, $$69, $$70, $$71] <- [true, $$67.getField(1), $$67.getField("35"), $$67.getField(2)] + -- ASSIGN |PARTITIONED| + project ([$$65, $$67]) + -- STREAM_PROJECT |PARTITIONED| + unnest $$67 <- scan-collection($$66) + -- UNNEST |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + group by ([$$65 := $$63]) decor ([]) { + aggregate [$$66] <- [listify($$64)] + -- AGGREGATE |LOCAL| + limit 3 + -- STREAM_LIMIT |LOCAL| + order (ASC, $$diff) (ASC, $$56) + -- MICRO_STABLE_SORT [$$diff(ASC), $$56(ASC)] |LOCAL| + nested tuple source + -- NESTED_TUPLE_SOURCE |LOCAL| + } + -- PRE_CLUSTERED_GROUP_BY[$$63] |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + order (ASC, $$63) + -- STABLE_SORT [$$63(ASC)] |PARTITIONED| + exchange + -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$63(ASC), $$diff(ASC), $$56(ASC)] HASH:[$$63] |PARTITIONED| + project ([$$64, $$diff, $$56, $$63]) + -- STREAM_PROJECT |PARTITIONED| + assign [$$64] <- [{"34": $$im, "50": if-missing-or-null(to-object($$im), cast({ })), "35": $$diff, "56": $$56, "58": $$58, "61": $$61, "62": $$62}] + -- ASSIGN |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + order (ASC, $$63) (ASC, $$diff) (ASC, $$56) + -- STABLE_SORT [$$63(ASC), $$diff(ASC), $$56(ASC)] |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + assign [$$diff] <- [abs(numeric-subtract($$61, $$58))] + -- ASSIGN |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + join (true) + -- NESTED_LOOP |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + assign [$$61] <- [$$62.getField("runtime")] + -- ASSIGN |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + data-scan []<-[$$63, $$62] <- MovieTest.movies + -- DATASOURCE_SCAN |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + empty-tuple-source + -- EMPTY_TUPLE_SOURCE |PARTITIONED| + exchange + -- BROADCAST_EXCHANGE |PARTITIONED| + assign [$$58] <- [$$im.getField("runtime")] + -- ASSIGN |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + data-scan []<-[$$56, $$im] <- MovieTest.movies + -- DATASOURCE_SCAN |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + empty-tuple-source + -- EMPTY_TUPLE_SOURCE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.1.ddl.sqlpp new file mode 100644 index 0000000..13e02ff --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.1.ddl.sqlpp @@ -0,0 +1,29 @@ +/* + * 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. + */ +/* + * Description : This test case is to verify the fix for issue 3752. + * Issue link : https://issues.apache.org/jira/browse/ASTERIXDB-3752 + */ + +DROP DATAVERSE MovieTest IF EXISTS; +CREATE DATAVERSE MovieTest; + +USE MovieTest; +CREATE TYPE MovieType AS {id: int}; +CREATE DATASET movies (MovieType) PRIMARY KEY id; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.2.update.sqlpp new file mode 100644 index 0000000..ce6f102 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.2.update.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. + */ + +USE MovieTest; + +INSERT INTO movies([ +{"id":19995,"title":"Avatar","revenue":2923706026,"runtime":162,"overview":"In the 22nd century, a paraplegic Marine is dispatched to the moon Pandora on a unique mission, but becomes torn between following orders and protecting an alien civilization.","genres":"Action, Adventure, Fantasy, Science Fiction"} +, +{"id":299536,"title":"Avengers: Infinity War","revenue":2052415039,"runtime":149,"overview":"As the Avengers and their allies have continued to protect the world from threats too large for any one hero to handle, a new danger has emerged from the cosmic shadows: Thanos. A despot of intergalactic infamy, his goal is to collect all six Infinity Stones, artifacts of unimaginable power, and use them to inflict his twisted will on all of reality. Everything the Avengers have fought for has led up to this moment - the fate of Earth and existence itself has never been more uncertain.","genres":"Adventure, Action, Science Fiction"} +, +{"id":299534,"title":"Avengers: Endgame","revenue":2800000000,"runtime":181,"overview":"After the devastating events of Avengers: Infinity War, the universe is in ruins due to the efforts of the Mad Titan, Thanos. With the help of remaining allies, the Avengers must assemble once more in order to undo Thanos' actions and restore order to the universe once and for all, no matter what consequences may be in store.","genres":"Adventure, Science Fiction, Action"} +, +{"id":135397,"title":"Jurassic World","revenue":1671537444,"runtime":124,"overview":"Twenty-two years after the events of Jurassic Park, Isla Nublar now features a fully functioning dinosaur theme park, Jurassic World, as originally envisioned by John Hammond.","genres":"Action, Adventure, Science Fiction, Thriller"} +, +{"id":140607,"title":"Star Wars: The Force Awakens","revenue":2068223624,"runtime":136,"overview":"Thirty years after defeating the Galactic Empire, Han Solo and his allies face a new threat from the evil Kylo Ren and his army of Stormtroopers.","genres":"Adventure, Action, Science Fiction"} +, +{"m_id":"0025931c-a350-031a-0bf0-71d366e6d8a6","id":634649,"title":"Spider-Man: No Way Home","revenue":1921847111,"runtime":148,"overview":"Peter Parker is unmasked and no longer able to separate his normal life from the high-stakes of being a super-hero. When he asks for help from Doctor Strange the stakes become even more dangerous, forcing him to discover what it truly means to be Spider-Man.","genres":"Action, Adventure, Science Fiction"} +, +{"id":24428,"title":"The Avengers","revenue":1518815515,"runtime":143,"overview":"When an unexpected enemy emerges and threatens global safety and security, Nick Fury, director of the international peacekeeping agency known as S.H.I.E.L.D., finds himself in need of a team to pull the world back from the brink of disaster. Spanning the globe, a daring recruitment effort begins!","genres":"Science Fiction, Action, Adventure"} +, +{"id":597,"title":"Titanic","revenue":2264162353,"runtime":194,"overview":"101-year-old Rose DeWitt Bukater tells the story of her life aboard the Titanic, 84 years later. A young Rose boards the ship with her mother and fiancé. Meanwhile, Jack Dawson and Fabrizio De Rossi win third-class tickets aboard the ship. Rose tells the whole story from Titanic's departure through to its death—on its first and last voyage—on April 15, 1912.","genres":"Drama, Romance"} +, +{"id":76600,"title":"Avatar: The Way of Water","revenue":2320250281,"runtime":192,"overview":"Set more than a decade after the events of the first film, learn the story of the Sully family (Jake, Neytiri, and their kids), the trouble that follows them, the lengths they go to keep each other safe, the battles they fight to stay alive, and the tragedies they endure.","genres":"Science Fiction, Adventure, Action"} +, +{"id":420818,"title":"The Lion King","revenue":1663075401,"runtime":118,"overview":"Simba idolizes his father, King Mufasa, and takes to heart his own royal destiny. But not everyone in the kingdom celebrates the new cub's arrival. Scar, Mufasa's brother—and former heir to the throne—has plans of his own. The battle for Pride Rock is ravaged with betrayal, tragedy and drama, ultimately resulting in Simba's exile. With help from a curious pair of newfound friends, Simba will have to figure out how to grow up and take back what is rightfully his.","genres":"Adventure, Drama, Family"} +]); diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.3.query.sqlpp new file mode 100644 index 0000000..0a76133 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.3.query.sqlpp @@ -0,0 +1,29 @@ +/* + * 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. + */ +SET `compiler.sort.parallel` "false"; +USE MovieTest; + +FROM movies m +SELECT m.*, (FROM movies im +LET diff = ABS(m.runtime - im.runtime) +SELECT im.* +ORDER BY diff, im.id +LIMIT 3 +) AS close +ORDER BY m.id; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.3.adm new file mode 100644 index 0000000..aa2ff91 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/correlation/ASTERIXDB-3752-LIMIT-in-subquery/ASTERIXDB-3752-LIMIT-in-subquery.3.adm @@ -0,0 +1,10 @@ +{ "close": [ { "id": 597, "title": "Titanic", "revenue": 2264162353, "runtime": 194, "overview": "101-year-old Rose DeWitt Bukater tells the story of her life aboard the Titanic, 84 years later. A young Rose boards the ship with her mother and fiancé. Meanwhile, Jack Dawson and Fabrizio De Rossi win third-class tickets aboard the ship. Rose tells the whole story from Titanic's departure through to its death—on its first and last voyage—on April 15, 1912.", "genres": "Drama, Romance" }, { "id": 76600, "title": "Avatar: The Way of Water", "revenue": 2320250281, "runtime": 192, "overview": "Set more than a decade after the events of the first film, learn the story of the Sully family (Jake, Neytiri, and their kids), the trouble that follows them, the lengths they go to keep each other safe, the battles they fight to stay alive, and the tragedies they endure.", "genres": "Science Fiction, Adventure, Action" }, { "id": 299534, "title": "Avengers: Endgame", "revenue": 2800000000, "runtime": 181, "overview": "After the devastating events of Avengers: Infinity War, the universe is in ruins due to the efforts of the Mad Titan, Thanos. With the help of remaining allies, the Avengers must assemble once more in order to undo Thanos' actions and restore order to the universe once and for all, no matter what consequences may be in store.", "genres": "Adventure, Science Fiction, Action" } ], "id": 597, "title": "Titanic", "revenue": 2264162353, "runtime": 194, "overview": "101-year-old Rose DeWitt Bukater tells the story of her life aboard the Titanic, 84 years later. A young Rose boards the ship with her mother and fiancé. Meanwhile, Jack Dawson and Fabrizio De Rossi win third-class tickets aboard the ship. Rose tells the whole story from Titanic's departure through to its death—on its first and last voyage—on April 15, 1912.", "genres": "Drama, Romance" } +{ "close": [ { "id": 19995, "title": "Avatar", "revenue": 2923706026, "runtime": 162, "overview": "In the 22nd century, a paraplegic Marine is dispatched to the moon Pandora on a unique mission, but becomes torn between following orders and protecting an alien civilization.", "genres": "Action, Adventure, Fantasy, Science Fiction" }, { "id": 299536, "title": "Avengers: Infinity War", "revenue": 2052415039, "runtime": 149, "overview": "As the Avengers and their allies have continued to protect the world from threats too large for any one hero to handle, a new danger has emerged from the cosmic shadows: Thanos. A despot of intergalactic infamy, his goal is to collect all six Infinity Stones, artifacts of unimaginable power, and use them to inflict his twisted will on all of reality. Everything the Avengers have fought for has led up to this moment - the fate of Earth and existence itself has never been more uncertain.", "genres": "Adventure, Action, Science Fiction" }, { "id": 634649, "m_id": "0025931c-a350-031a-0bf0-71d366e6d8a6", "title": "Spider-Man: No Way Home", "revenue": 1921847111, "runtime": 148, "overview": "Peter Parker is unmasked and no longer able to separate his normal life from the high-stakes of being a super-hero. When he asks for help from Doctor Strange the stakes become even more dangerous, forcing him to discover what it truly means to be Spider-Man.", "genres": "Action, Adventure, Science Fiction" } ], "id": 19995, "title": "Avatar", "revenue": 2923706026, "runtime": 162, "overview": "In the 22nd century, a paraplegic Marine is dispatched to the moon Pandora on a unique mission, but becomes torn between following orders and protecting an alien civilization.", "genres": "Action, Adventure, Fantasy, Science Fiction" } +{ "close": [ { "id": 24428, "title": "The Avengers", "revenue": 1518815515, "runtime": 143, "overview": "When an unexpected enemy emerges and threatens global safety and security, Nick Fury, director of the international peacekeeping agency known as S.H.I.E.L.D., finds himself in need of a team to pull the world back from the brink of disaster. Spanning the globe, a daring recruitment effort begins!", "genres": "Science Fiction, Action, Adventure" }, { "id": 634649, "m_id": "0025931c-a350-031a-0bf0-71d366e6d8a6", "title": "Spider-Man: No Way Home", "revenue": 1921847111, "runtime": 148, "overview": "Peter Parker is unmasked and no longer able to separate his normal life from the high-stakes of being a super-hero. When he asks for help from Doctor Strange the stakes become even more dangerous, forcing him to discover what it truly means to be Spider-Man.", "genres": "Action, Adventure, Science Fiction" }, { "id": 299536, "title": "Avengers: Infinity War", "revenue": 2052415039, "runtime": 149, "overview": "As the Avengers and their allies have continued to protect the world from threats too large for any one hero to handle, a new danger has emerged from the cosmic shadows: Thanos. A despot of intergalactic infamy, his goal is to collect all six Infinity Stones, artifacts of unimaginable power, and use them to inflict his twisted will on all of reality. Everything the Avengers have fought for has led up to this moment - the fate of Earth and existence itself has never been more uncertain.", "genres": "Adventure, Action, Science Fiction" } ], "id": 24428, "title": "The Avengers", "revenue": 1518815515, "runtime": 143, "overview": "When an unexpected enemy emerges and threatens global safety and security, Nick Fury, director of the international peacekeeping agency known as S.H.I.E.L.D., finds himself in need of a team to pull the world back from the brink of disaster. Spanning the globe, a daring recruitment effort begins!", "genres": "Science Fiction, Action, Adventure" } +{ "close": [ { "id": 76600, "title": "Avatar: The Way of Water", "revenue": 2320250281, "runtime": 192, "overview": "Set more than a decade after the events of the first film, learn the story of the Sully family (Jake, Neytiri, and their kids), the trouble that follows them, the lengths they go to keep each other safe, the battles they fight to stay alive, and the tragedies they endure.", "genres": "Science Fiction, Adventure, Action" }, { "id": 597, "title": "Titanic", "revenue": 2264162353, "runtime": 194, "overview": "101-year-old Rose DeWitt Bukater tells the story of her life aboard the Titanic, 84 years later. A young Rose boards the ship with her mother and fiancé. Meanwhile, Jack Dawson and Fabrizio De Rossi win third-class tickets aboard the ship. Rose tells the whole story from Titanic's departure through to its death—on its first and last voyage—on April 15, 1912.", "genres": "Drama, Romance" }, { "id": 299534, "title": "Avengers: Endgame", "revenue": 2800000000, "runtime": 181, "overview": "After the devastating events of Avengers: Infinity War, the universe is in ruins due to the efforts of the Mad Titan, Thanos. With the help of remaining allies, the Avengers must assemble once more in order to undo Thanos' actions and restore order to the universe once and for all, no matter what consequences may be in store.", "genres": "Adventure, Science Fiction, Action" } ], "id": 76600, "title": "Avatar: The Way of Water", "revenue": 2320250281, "runtime": 192, "overview": "Set more than a decade after the events of the first film, learn the story of the Sully family (Jake, Neytiri, and their kids), the trouble that follows them, the lengths they go to keep each other safe, the battles they fight to stay alive, and the tragedies they endure.", "genres": "Science Fiction, Adventure, Action" } +{ "close": [ { "id": 135397, "title": "Jurassic World", "revenue": 1671537444, "runtime": 124, "overview": "Twenty-two years after the events of Jurassic Park, Isla Nublar now features a fully functioning dinosaur theme park, Jurassic World, as originally envisioned by John Hammond.", "genres": "Action, Adventure, Science Fiction, Thriller" }, { "id": 420818, "title": "The Lion King", "revenue": 1663075401, "runtime": 118, "overview": "Simba idolizes his father, King Mufasa, and takes to heart his own royal destiny. But not everyone in the kingdom celebrates the new cub's arrival. Scar, Mufasa's brother—and former heir to the throne—has plans of his own. The battle for Pride Rock is ravaged with betrayal, tragedy and drama, ultimately resulting in Simba's exile. With help from a curious pair of newfound friends, Simba will have to figure out how to grow up and take back what is rightfully his.", "genres": "Adventure, Drama, Family" }, { "id": 140607, "title": "Star Wars: The Force Awakens", "revenue": 2068223624, "runtime": 136, "overview": "Thirty years after defeating the Galactic Empire, Han Solo and his allies face a new threat from the evil Kylo Ren and his army of Stormtroopers.", "genres": "Adventure, Action, Science Fiction" } ], "id": 135397, "title": "Jurassic World", "revenue": 1671537444, "runtime": 124, "overview": "Twenty-two years after the events of Jurassic Park, Isla Nublar now features a fully functioning dinosaur theme park, Jurassic World, as originally envisioned by John Hammond.", "genres": "Action, Adventure, Science Fiction, Thriller" } +{ "close": [ { "id": 140607, "title": "Star Wars: The Force Awakens", "revenue": 2068223624, "runtime": 136, "overview": "Thirty years after defeating the Galactic Empire, Han Solo and his allies face a new threat from the evil Kylo Ren and his army of Stormtroopers.", "genres": "Adventure, Action, Science Fiction" }, { "id": 24428, "title": "The Avengers", "revenue": 1518815515, "runtime": 143, "overview": "When an unexpected enemy emerges and threatens global safety and security, Nick Fury, director of the international peacekeeping agency known as S.H.I.E.L.D., finds himself in need of a team to pull the world back from the brink of disaster. Spanning the globe, a daring recruitment effort begins!", "genres": "Science Fiction, Action, Adventure" }, { "id": 135397, "title": "Jurassic World", "revenue": 1671537444, "runtime": 124, "overview": "Twenty-two years after the events of Jurassic Park, Isla Nublar now features a fully functioning dinosaur theme park, Jurassic World, as originally envisioned by John Hammond.", "genres": "Action, Adventure, Science Fiction, Thriller" } ], "id": 140607, "title": "Star Wars: The Force Awakens", "revenue": 2068223624, "runtime": 136, "overview": "Thirty years after defeating the Galactic Empire, Han Solo and his allies face a new threat from the evil Kylo Ren and his army of Stormtroopers.", "genres": "Adventure, Action, Science Fiction" } +{ "close": [ { "id": 299534, "title": "Avengers: Endgame", "revenue": 2800000000, "runtime": 181, "overview": "After the devastating events of Avengers: Infinity War, the universe is in ruins due to the efforts of the Mad Titan, Thanos. With the help of remaining allies, the Avengers must assemble once more in order to undo Thanos' actions and restore order to the universe once and for all, no matter what consequences may be in store.", "genres": "Adventure, Science Fiction, Action" }, { "id": 76600, "title": "Avatar: The Way of Water", "revenue": 2320250281, "runtime": 192, "overview": "Set more than a decade after the events of the first film, learn the story of the Sully family (Jake, Neytiri, and their kids), the trouble that follows them, the lengths they go to keep each other safe, the battles they fight to stay alive, and the tragedies they endure.", "genres": "Science Fiction, Adventure, Action" }, { "id": 597, "title": "Titanic", "revenue": 2264162353, "runtime": 194, "overview": "101-year-old Rose DeWitt Bukater tells the story of her life aboard the Titanic, 84 years later. A young Rose boards the ship with her mother and fiancé. Meanwhile, Jack Dawson and Fabrizio De Rossi win third-class tickets aboard the ship. Rose tells the whole story from Titanic's departure through to its death—on its first and last voyage—on April 15, 1912.", "genres": "Drama, Romance" } ], "id": 299534, "title": "Avengers: Endgame", "revenue": 2800000000, "runtime": 181, "overview": "After the devastating events of Avengers: Infinity War, the universe is in ruins due to the efforts of the Mad Titan, Thanos. With the help of remaining allies, the Avengers must assemble once more in order to undo Thanos' actions and restore order to the universe once and for all, no matter what consequences may be in store.", "genres": "Adventure, Science Fiction, Action" } +{ "close": [ { "id": 299536, "title": "Avengers: Infinity War", "revenue": 2052415039, "runtime": 149, "overview": "As the Avengers and their allies have continued to protect the world from threats too large for any one hero to handle, a new danger has emerged from the cosmic shadows: Thanos. A despot of intergalactic infamy, his goal is to collect all six Infinity Stones, artifacts of unimaginable power, and use them to inflict his twisted will on all of reality. Everything the Avengers have fought for has led up to this moment - the fate of Earth and existence itself has never been more uncertain.", "genres": "Adventure, Action, Science Fiction" }, { "id": 634649, "m_id": "0025931c-a350-031a-0bf0-71d366e6d8a6", "title": "Spider-Man: No Way Home", "revenue": 1921847111, "runtime": 148, "overview": "Peter Parker is unmasked and no longer able to separate his normal life from the high-stakes of being a super-hero. When he asks for help from Doctor Strange the stakes become even more dangerous, forcing him to discover what it truly means to be Spider-Man.", "genres": "Action, Adventure, Science Fiction" }, { "id": 24428, "title": "The Avengers", "revenue": 1518815515, "runtime": 143, "overview": "When an unexpected enemy emerges and threatens global safety and security, Nick Fury, director of the international peacekeeping agency known as S.H.I.E.L.D., finds himself in need of a team to pull the world back from the brink of disaster. Spanning the globe, a daring recruitment effort begins!", "genres": "Science Fiction, Action, Adventure" } ], "id": 299536, "title": "Avengers: Infinity War", "revenue": 2052415039, "runtime": 149, "overview": "As the Avengers and their allies have continued to protect the world from threats too large for any one hero to handle, a new danger has emerged from the cosmic shadows: Thanos. A despot of intergalactic infamy, his goal is to collect all six Infinity Stones, artifacts of unimaginable power, and use them to inflict his twisted will on all of reality. Everything the Avengers have fought for has led up to this moment - the fate of Earth and existence itself has never been more uncertain.", "genres": "Adventure, Action, Science Fiction" } +{ "close": [ { "id": 420818, "title": "The Lion King", "revenue": 1663075401, "runtime": 118, "overview": "Simba idolizes his father, King Mufasa, and takes to heart his own royal destiny. But not everyone in the kingdom celebrates the new cub's arrival. Scar, Mufasa's brother—and former heir to the throne—has plans of his own. The battle for Pride Rock is ravaged with betrayal, tragedy and drama, ultimately resulting in Simba's exile. With help from a curious pair of newfound friends, Simba will have to figure out how to grow up and take back what is rightfully his.", "genres": "Adventure, Drama, Family" }, { "id": 135397, "title": "Jurassic World", "revenue": 1671537444, "runtime": 124, "overview": "Twenty-two years after the events of Jurassic Park, Isla Nublar now features a fully functioning dinosaur theme park, Jurassic World, as originally envisioned by John Hammond.", "genres": "Action, Adventure, Science Fiction, Thriller" }, { "id": 140607, "title": "Star Wars: The Force Awakens", "revenue": 2068223624, "runtime": 136, "overview": "Thirty years after defeating the Galactic Empire, Han Solo and his allies face a new threat from the evil Kylo Ren and his army of Stormtroopers.", "genres": "Adventure, Action, Science Fiction" } ], "id": 420818, "title": "The Lion King", "revenue": 1663075401, "runtime": 118, "overview": "Simba idolizes his father, King Mufasa, and takes to heart his own royal destiny. But not everyone in the kingdom celebrates the new cub's arrival. Scar, Mufasa's brother—and former heir to the throne—has plans of his own. The battle for Pride Rock is ravaged with betrayal, tragedy and drama, ultimately resulting in Simba's exile. With help from a curious pair of newfound friends, Simba will have to figure out how to grow up and take back what is rightfully his.", "genres": "Adventure, Drama, Family" } +{ "close": [ { "id": 634649, "m_id": "0025931c-a350-031a-0bf0-71d366e6d8a6", "title": "Spider-Man: No Way Home", "revenue": 1921847111, "runtime": 148, "overview": "Peter Parker is unmasked and no longer able to separate his normal life from the high-stakes of being a super-hero. When he asks for help from Doctor Strange the stakes become even more dangerous, forcing him to discover what it truly means to be Spider-Man.", "genres": "Action, Adventure, Science Fiction" }, { "id": 299536, "title": "Avengers: Infinity War", "revenue": 2052415039, "runtime": 149, "overview": "As the Avengers and their allies have continued to protect the world from threats too large for any one hero to handle, a new danger has emerged from the cosmic shadows: Thanos. A despot of intergalactic infamy, his goal is to collect all six Infinity Stones, artifacts of unimaginable power, and use them to inflict his twisted will on all of reality. Everything the Avengers have fought for has led up to this moment - the fate of Earth and existence itself has never been more uncertain.", "genres": "Adventure, Action, Science Fiction" }, { "id": 24428, "title": "The Avengers", "revenue": 1518815515, "runtime": 143, "overview": "When an unexpected enemy emerges and threatens global safety and security, Nick Fury, director of the international peacekeeping agency known as S.H.I.E.L.D., finds himself in need of a team to pull the world back from the brink of disaster. Spanning the globe, a daring recruitment effort begins!", "genres": "Science Fiction, Action, Adventure" } ], "id": 634649, "m_id": "0025931c-a350-031a-0bf0-71d366e6d8a6", "title": "Spider-Man: No Way Home", "revenue": 1921847111, "runtime": 148, "overview": "Peter Parker is unmasked and no longer able to separate his normal life from the high-stakes of being a super-hero. When he asks for help from Doctor Strange the stakes become even more dangerous, forcing him to discover what it truly means to be Spider-Man.", "genres": "Action, Adventure, Science Fiction" } \ No newline at end of file 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 eb46490..2a1bfd0 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml +++ b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml @@ -16190,4 +16190,11 @@ </compilation-unit> </test-case> </test-group> + <test-group name="correlation"> + <test-case FilePath="correlation"> + <compilation-unit name="ASTERIXDB-3752-LIMIT-in-subquery"> + <output-dir compare="Text">ASTERIXDB-3752-LIMIT-in-subquery</output-dir> + </compilation-unit> + </test-case> + </test-group> </test-group> -- To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/21130?usp=email To unsubscribe, or for help writing mail filters, visit https://asterix-gerrit.ics.uci.edu/settings?usp=email Gerrit-MessageType: merged Gerrit-Project: asterixdb Gerrit-Branch: trinity Gerrit-Change-Id: I3457e3bf027a3a4e0d2552f62990e97adb34fe5f Gerrit-Change-Number: 21130 Gerrit-PatchSet: 4 Gerrit-Owner: Ali Alsuliman <[email protected]> Gerrit-Reviewer: Ali Alsuliman <[email protected]> Gerrit-Reviewer: Anon. E. Moose #1000171 Gerrit-Reviewer: Ian Maxon <[email protected]> Gerrit-Reviewer: Jenkins <[email protected]> Gerrit-Reviewer: Michael Blow <[email protected]>
