Dmitry Lychagin has uploaded this change for review. ( https://asterix-gerrit.ics.uci.edu/3442
Change subject: [NO ISSUE][FUN] Allow ORDER BY in RATIO_TO_REPORT() ...................................................................... [NO ISSUE][FUN] Allow ORDER BY in RATIO_TO_REPORT() - user model changes: yes - storage format changes: no - interface changes: no Details: - Support ORDER BY specification in RATIO_TO_REPORT() window function call Change-Id: I031b3eff1aee8be12f1943d39f5f1c640476e4e8 --- M asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java C asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ratio_to_report_01/ratio_to_report_01.6.query.sqlpp R asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ratio_to_report_01/ratio_to_report_01.7.query.sqlpp M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_negative/win_negative.2.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/results/window/ratio_to_report_01/ratio_to_report_01.6.adm A asterixdb/asterix-app/src/test/resources/runtimets/results/window/ratio_to_report_01/ratio_to_report_01.7.adm M asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml M asterixdb/asterix-om/src/main/java/org/apache/asterix/om/functions/BuiltinFunctions.java 8 files changed, 37 insertions(+), 15 deletions(-) git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/42/3442/1 diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java index 988723f..1c5750c 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java @@ -1228,6 +1228,11 @@ createOperatorExpr(fargs.get(1), OperatorType.MINUS, new IntegerLiteral(1), sourceLoc); } else if (BuiltinFunctions.RATIO_TO_REPORT_IMPL.equals(fi)) { // ratio_to_report(x) over (...) --> x / sum(x) over (...) + winFrameMode = WindowExpression.FrameMode.RANGE; + winFrameStartKind = WindowExpression.FrameBoundaryKind.UNBOUNDED_PRECEDING; + winFrameEndKind = WindowExpression.FrameBoundaryKind.CURRENT_ROW; + winFrameExclusionKind = WindowExpression.FrameExclusionKind.NO_OTHERS; + nestedAggFunc = BuiltinFunctions.SCALAR_SQL_SUM; postWinResultFunc = BuiltinFunctions.NUMERIC_DIVIDE; postWinExpr = fargs.get(1); diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_negative/win_negative.9.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ratio_to_report_01/ratio_to_report_01.6.query.sqlpp similarity index 71% copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_negative/win_negative.9.query.sqlpp copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ratio_to_report_01/ratio_to_report_01.6.query.sqlpp index c03e763..537d373 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_negative/win_negative.9.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ratio_to_report_01/ratio_to_report_01.6.query.sqlpp @@ -17,9 +17,12 @@ * under the License. */ /* - * Description : Function that doesn't support FROM FIRST/LAST - * Expected Res : FAILURE + * Description : Test RATIO_TO_REPORT() with ORDER BY (and with PARTITION BY) + * Expected Res : SUCCESS */ -from range(1, 4) x -select value first_value(x) from last over (order by x) \ No newline at end of file +FROM range(1, 2) x, range(1, 4) y +SELECT x, y, + round_half_to_even(ratio_to_report(y) over (partition by x), 2) rr, + round_half_to_even(ratio_to_report(y) over (partition by x order by y), 2) rr_oby +ORDER BY x, y diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_negative/win_negative.9.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ratio_to_report_01/ratio_to_report_01.7.query.sqlpp similarity index 74% rename from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_negative/win_negative.9.query.sqlpp rename to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ratio_to_report_01/ratio_to_report_01.7.query.sqlpp index c03e763..3c98f01 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_negative/win_negative.9.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/ratio_to_report_01/ratio_to_report_01.7.query.sqlpp @@ -17,9 +17,12 @@ * under the License. */ /* - * Description : Function that doesn't support FROM FIRST/LAST - * Expected Res : FAILURE + * Description : Test RATIO_TO_REPORT() with ORDER BY (without PARTITION BY) + * Expected Res : SUCCESS */ -from range(1, 4) x -select value first_value(x) from last over (order by x) \ No newline at end of file +FROM range(1, 4) y +SELECT y, + round_half_to_even(ratio_to_report(y) over (), 2) rr, + round_half_to_even(ratio_to_report(y) over (order by y), 2) rr_oby +ORDER BY y diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_negative/win_negative.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_negative/win_negative.2.query.sqlpp index b539629..c03e763 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_negative/win_negative.2.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/window/win_negative/win_negative.2.query.sqlpp @@ -17,9 +17,9 @@ * under the License. */ /* - * Description : Test RATIO_TO_REPORT() - * Expected Res : FAILURE (unexpected ORDER BY) + * Description : Function that doesn't support FROM FIRST/LAST + * Expected Res : FAILURE */ -FROM range(1, 4) x, range(1, 2) y -SELECT ratio_to_report(x) over (order by y) +from range(1, 4) x +select value first_value(x) from last over (order by x) \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/window/ratio_to_report_01/ratio_to_report_01.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/ratio_to_report_01/ratio_to_report_01.6.adm new file mode 100644 index 0000000..e57f30c --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/ratio_to_report_01/ratio_to_report_01.6.adm @@ -0,0 +1,8 @@ +{ "x": 1, "y": 1, "rr": 0.1, "rr_oby": 1.0 } +{ "x": 1, "y": 2, "rr": 0.2, "rr_oby": 0.67 } +{ "x": 1, "y": 3, "rr": 0.3, "rr_oby": 0.5 } +{ "x": 1, "y": 4, "rr": 0.4, "rr_oby": 0.4 } +{ "x": 2, "y": 1, "rr": 0.1, "rr_oby": 1.0 } +{ "x": 2, "y": 2, "rr": 0.2, "rr_oby": 0.67 } +{ "x": 2, "y": 3, "rr": 0.3, "rr_oby": 0.5 } +{ "x": 2, "y": 4, "rr": 0.4, "rr_oby": 0.4 } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/window/ratio_to_report_01/ratio_to_report_01.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/ratio_to_report_01/ratio_to_report_01.7.adm new file mode 100644 index 0000000..fc1b362 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/window/ratio_to_report_01/ratio_to_report_01.7.adm @@ -0,0 +1,4 @@ +{ "y": 1, "rr": 0.1, "rr_oby": 1.0 } +{ "y": 2, "rr": 0.2, "rr_oby": 0.67 } +{ "y": 3, "rr": 0.3, "rr_oby": 0.5 } +{ "y": 4, "rr": 0.4, "rr_oby": 0.4 } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml index 9d5d44f..15c0702 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml +++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml @@ -10399,13 +10399,12 @@ <compilation-unit name="win_negative"> <output-dir compare="Text">misc_01</output-dir> <expected-error>ASX0002: Type mismatch</expected-error> - <expected-error>ASX1101: Unexpected ORDER BY clause in window expression</expected-error> + <expected-error>ASX1104: Invalid modifier FROM FIRST/LAST for function</expected-error> <expected-error>ASX1037: Invalid query parameter compiler.windowmemory</expected-error> <expected-error>ASX1102: Expected window or aggregate function, got: unknown_func</expected-error> <expected-error>ASX1079: Compilation error: count is a SQL-92 aggregate function</expected-error> <expected-error>ASX1104: Invalid modifier RESPECT/IGNORE NULLS for function</expected-error> <expected-error>ASX1104: Invalid modifier RESPECT/IGNORE NULLS for function</expected-error> - <expected-error>ASX1104: Invalid modifier FROM FIRST/LAST for function</expected-error> <expected-error>ASX1104: Invalid modifier FROM FIRST/LAST for function</expected-error> <source-location>false</source-location> </compilation-unit> diff --git a/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/functions/BuiltinFunctions.java b/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/functions/BuiltinFunctions.java index 43664fb..7afda20 100644 --- a/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/functions/BuiltinFunctions.java +++ b/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/functions/BuiltinFunctions.java @@ -2967,7 +2967,7 @@ addWindowFunction(NTILE, NTILE_IMPL, NO_FRAME_CLAUSE, MATERIALIZE_PARTITION); addWindowFunction(PERCENT_RANK, PERCENT_RANK_IMPL, NO_FRAME_CLAUSE, INJECT_ORDER_ARGS, MATERIALIZE_PARTITION); addWindowFunction(RANK, RANK_IMPL, NO_FRAME_CLAUSE, INJECT_ORDER_ARGS); - addWindowFunction(RATIO_TO_REPORT, RATIO_TO_REPORT_IMPL, NO_ORDER_CLAUSE, NO_FRAME_CLAUSE, HAS_LIST_ARG); + addWindowFunction(RATIO_TO_REPORT, RATIO_TO_REPORT_IMPL, NO_FRAME_CLAUSE, HAS_LIST_ARG); addWindowFunction(ROW_NUMBER, ROW_NUMBER_IMPL, NO_FRAME_CLAUSE); addWindowFunction(WIN_PARTITION_LENGTH, WIN_PARTITION_LENGTH_IMPL, NO_FRAME_CLAUSE, MATERIALIZE_PARTITION); } -- To view, visit https://asterix-gerrit.ics.uci.edu/3442 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-MessageType: newchange Gerrit-Change-Id: I031b3eff1aee8be12f1943d39f5f1c640476e4e8 Gerrit-Change-Number: 3442 Gerrit-PatchSet: 1 Gerrit-Owner: Dmitry Lychagin <dmitry.lycha...@couchbase.com>