This is an automated email from the ASF dual-hosted git repository.
mmior pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new 67b439f [CALCITE-2786] Add order by clause support for JSON_ARRAYAGG
67b439f is described below
commit 67b439fc1d98fd0082517684b74b53cd1050bbf4
Author: Hongze Zhang <[email protected]>
AuthorDate: Sat Feb 23 01:52:39 2019 +0800
[CALCITE-2786] Add order by clause support for JSON_ARRAYAGG
---
core/src/main/codegen/templates/Parser.jj | 72 ++++++++++++++++++----
.../apache/calcite/runtime/CalciteResource.java | 3 +
.../sql/fun/SqlJsonArrayAggAggFunction.java | 23 ++++++-
.../calcite/runtime/CalciteResource.properties | 1 +
.../apache/calcite/sql/parser/SqlParserTest.java | 12 +++-
.../apache/calcite/test/SqlToRelConverterTest.java | 20 +++++-
.../apache/calcite/test/SqlToRelConverterTest.xml | 41 +++++++++++-
core/src/test/resources/sql/agg.iq | 37 +++++++++++
site/_docs/reference.md | 3 +-
9 files changed, 194 insertions(+), 18 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index a546495..07c8cf8 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -5323,28 +5323,64 @@ SqlCall JsonArrayFunctionCall() :
}
}
+SqlNodeList JsonArrayAggOrderByClause() :
+{
+ final SqlNodeList orderList;
+}
+{
+ (
+ orderList = OrderBy(true)
+ |
+ { orderList = null; }
+ )
+ {
+ return orderList;
+ }
+}
+
SqlCall JsonArrayAggFunctionCall() :
{
- final SqlNode[] args = new SqlNode[1];
+ final SqlNode valueExpr;
+ SqlNodeList orderList = null;
List<SqlNode> list;
final Span span;
SqlJsonConstructorNullClause nullClause =
SqlJsonConstructorNullClause.ABSENT_ON_NULL;
- SqlNode e;
+ SqlNode e = null;
+ final SqlNode aggCall;
}
{
<JSON_ARRAYAGG> { span = span(); }
<LPAREN> e = JsonValueExpression(false) {
- args[0] = e;
+ valueExpr = e;
}
+ orderList = JsonArrayAggOrderByClause()
[
e = JsonConstructorNullClause() {
nullClause = (SqlJsonConstructorNullClause) ((SqlLiteral)
e).getValue();
}
]
- <RPAREN> {
+ <RPAREN>
+ {
+ aggCall = SqlStdOperatorTable.JSON_ARRAYAGG.with(nullClause)
+ .createCall(span.end(this), valueExpr, orderList);
+ }
+ [
+ e = withinGroup(aggCall) {
+ if (orderList != null) {
+ throw
SqlUtil.newContextException(span.pos().plus(e.getParserPosition()),
+ RESOURCE.ambiguousSortOrderInJsonArrayAggFunc());
+ }
+ return (SqlCall) e;
+ }
+ ]
+ {
+ if (orderList == null) {
+ return SqlStdOperatorTable.JSON_ARRAYAGG.with(nullClause)
+ .createCall(span.end(this), valueExpr);
+ }
return SqlStdOperatorTable.JSON_ARRAYAGG.with(nullClause)
- .createCall(span.end(this), args);
+ .createCall(span.end(this), valueExpr, orderList);
}
}
@@ -5503,6 +5539,23 @@ SqlCall MatchRecognizeNavigationPhysical() :
}
}
+SqlCall withinGroup(SqlNode arg) :
+{
+ final Span withinGroupSpan;
+ final SqlNodeList orderList;
+}
+{
+
+ <WITHIN> { withinGroupSpan = span(); }
+ <GROUP>
+ <LPAREN>
+ orderList = OrderBy(true)
+ <RPAREN> {
+ return SqlStdOperatorTable.WITHIN_GROUP.createCall(
+ withinGroupSpan.end(this), arg, orderList);
+ }
+}
+
/**
* Parses a call to a named function (could be a builtin with regular
* syntax, or else a UDF).
@@ -5562,14 +5615,7 @@ SqlNode NamedFunctionCall() :
call = createCall(qualifiedName, s.end(this), funcType, quantifier,
args);
}
[
- <WITHIN> { withinGroupSpan = span(); }
- <GROUP>
- <LPAREN>
- orderList = OrderBy(true)
- <RPAREN> {
- call = SqlStdOperatorTable.WITHIN_GROUP.createCall(
- withinGroupSpan.end(this), call, orderList);
- }
+ call = withinGroup(call)
]
[
<FILTER> { filterSpan = span(); }
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index 6ae9bea..c9da744 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -850,6 +850,9 @@ public interface CalciteResource {
@BaseMessage("Timeout of ''{0}'' ms for query execution is reached. Query
execution started at ''{1}''")
ExInst<CalciteException> queryExecutionTimeoutReached(String timeout, String
queryStart);
+ @BaseMessage("Including both WITHIN GROUP(...) and inside ORDER BY in a
single JSON_ARRAYAGG call is not allowed")
+ ExInst<CalciteException> ambiguousSortOrderInJsonArrayAggFunc();
+
@BaseMessage("While executing SQL [{0}] on JDBC sub-schema")
ExInst<RuntimeException> exceptionWhilePerformingQueryOnJdbcSubSchema(String
sql);
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonArrayAggAggFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonArrayAggAggFunction.java
index a5d1520..0569bea 100644
---
a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonArrayAggAggFunction.java
+++
b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonArrayAggAggFunction.java
@@ -22,8 +22,10 @@ import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlFunctionCategory;
import org.apache.calcite.sql.SqlJsonConstructorNullClause;
import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlWriter;
+import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
import org.apache.calcite.sql.type.SqlTypeFamily;
@@ -45,7 +47,7 @@ public class SqlJsonArrayAggAggFunction extends
SqlAggFunction {
SqlJsonConstructorNullClause nullClause) {
super(name, null, SqlKind.JSON_ARRAYAGG, ReturnTypes.VARCHAR_2000, null,
OperandTypes.family(SqlTypeFamily.ANY), SqlFunctionCategory.SYSTEM,
- false, false, Optionality.FORBIDDEN);
+ false, false, Optionality.OPTIONAL);
this.nullClause = Objects.requireNonNull(nullClause);
}
@@ -72,6 +74,25 @@ public class SqlJsonArrayAggAggFunction extends
SqlAggFunction {
return getName() + String.format(Locale.ROOT, "<%s>", nullClause);
}
+ @Override public SqlCall createCall(SqlLiteral functionQualifier,
+ SqlParserPos pos, SqlNode... operands) {
+ assert operands.length == 1 || operands.length == 2;
+ final SqlNode valueExpr = operands[0];
+ if (operands.length == 2) {
+ final SqlNode orderList = operands[1];
+ if (orderList != null) {
+ // call has an order by clause, e.g. json_arrayagg(col_1 order by
col_1)
+ return SqlStdOperatorTable.WITHIN_GROUP.createCall(SqlParserPos.ZERO,
+ createCall_(functionQualifier, pos, valueExpr), orderList);
+ }
+ }
+ return createCall_(functionQualifier, pos, valueExpr);
+ }
+
+ private SqlCall createCall_(SqlLiteral functionQualifier, SqlParserPos pos,
SqlNode valueExpr) {
+ return super.createCall(functionQualifier, pos, valueExpr);
+ }
+
public SqlJsonArrayAggAggFunction with(SqlJsonConstructorNullClause
nullClause) {
return this.nullClause == nullClause ? this
: new SqlJsonArrayAggAggFunction(getName(), nullClause);
diff --git
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index 9202536..7c90a9b 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -276,6 +276,7 @@
ArrayOrObjectValueRequiredInStrictModeOfJsonQueryFunc=Strict jsonpath mode requi
IllegalErrorBehaviorInJsonQueryFunc=Illegal error behavior ''{0}'' specified
in JSON_VALUE function
NullKeyOfJsonObjectNotAllowed=Null key of JSON object is not allowed
QueryExecutionTimeoutReached=Timeout of ''{0}'' ms for query execution is
reached. Query execution started at ''{1}''
+AmbiguousSortOrderInJsonArrayAggFunc=Including both WITHIN GROUP(...) and
inside ORDER BY in a single JSON_ARRAYAGG call is not allowed
ExceptionWhilePerformingQueryOnJdbcSubSchema = While executing SQL [{0}] on
JDBC sub-schema
UnknownObjectOfJsonType=Unknown JSON type in JSON_TYPE function, and the
object is: ''{0}''
# End CalciteResource.properties
diff --git
a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
index 83d4b8d..a4ce6c9 100644
--- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -8417,7 +8417,7 @@ public class SqlParserTest {
"JSON_ARRAY(JSON_ARRAY('foo', 'bar' ABSENT ON NULL) FORMAT JSON ABSENT
ON NULL)");
}
- @Test public void testJsonArrayAgg() {
+ @Test public void testJsonArrayAgg1() {
checkExp("json_arrayagg(\"column\")",
"JSON_ARRAYAGG(`column` ABSENT ON NULL)");
checkExp("json_arrayagg(\"column\" null on null)",
@@ -8426,6 +8426,16 @@ public class SqlParserTest {
"JSON_ARRAYAGG(JSON_ARRAY(`column` ABSENT ON NULL) FORMAT JSON ABSENT
ON NULL)");
}
+ @Test public void testJsonArrayAgg2() {
+ checkExp("json_arrayagg(\"column\" order by \"column\")",
+ "(JSON_ARRAYAGG(`column` ABSENT ON NULL) WITHIN GROUP (ORDER BY
`column`))");
+ checkExp("json_arrayagg(\"column\") within group (order by \"column\")",
+ "(JSON_ARRAYAGG(`column` ABSENT ON NULL) WITHIN GROUP (ORDER BY
`column`))");
+ checkFails("^json_arrayagg(\"column\" order by \"column\") within group
(order by \"column\")^",
+ "(?s).*Including both WITHIN GROUP\\(\\.\\.\\.\\) and inside ORDER BY "
+ + "in a single JSON_ARRAYAGG call is not allowed.*");
+ }
+
@Test public void testJsonPredicate() {
checkExp("'{}' is json",
"('{}' IS JSON VALUE)");
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 5e43e1d..d3b3f73 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -2906,12 +2906,30 @@ public class SqlToRelConverterTest extends
SqlToRelTestBase {
sql(sql).ok();
}
- @Test public void testJsonArrayAgg() {
+ @Test public void testJsonArrayAgg1() {
final String sql = "select json_arrayagg(ename)\n"
+ "from emp";
sql(sql).ok();
}
+ @Test public void testJsonArrayAgg2() {
+ final String sql = "select json_arrayagg(ename order by ename)\n"
+ + "from emp";
+ sql(sql).ok();
+ }
+
+ @Test public void testJsonArrayAgg3() {
+ final String sql = "select json_arrayagg(ename order by ename null on
null)\n"
+ + "from emp";
+ sql(sql).ok();
+ }
+
+ @Test public void testJsonArrayAgg4() {
+ final String sql = "select json_arrayagg(ename null on null) within group
(order by ename)\n"
+ + "from emp";
+ sql(sql).ok();
+ }
+
@Test public void testJsonObject() {
final String sql = "select json_object(ename: deptno, ename: deptno)\n"
+ "from emp";
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index fbe4df8..5c670bf 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -5362,7 +5362,7 @@ LogicalProject(EXPR$0=[JSON_ARRAY(FLAG(ABSENT_ON_NULL),
JSON_STRUCTURED_VALUE_EX
]]>
</Resource>
</TestCase>
- <TestCase name="testJsonArrayAgg">
+ <TestCase name="testJsonArrayAgg1">
<Resource name="sql">
<![CDATA[select json_arrayagg(ename)
from emp]]>
@@ -5375,6 +5375,45 @@ LogicalAggregate(group=[{}],
EXPR$0=[JSON_ARRAYAGG<ABSENT_ON_NULL>($0)])
]]>
</Resource>
</TestCase>
+ <TestCase name="testJsonArrayAgg2">
+ <Resource name="sql">
+ <![CDATA[select json_arrayagg(ename order by ename)
+from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalAggregate(group=[{}], EXPR$0=[JSON_ARRAYAGG<ABSENT_ON_NULL>($0) WITHIN
GROUP ([1])])
+ LogicalProject($f0=[JSON_STRUCTURED_VALUE_EXPRESSION($1)], ENAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJsonArrayAgg3">
+ <Resource name="sql">
+ <![CDATA[select json_arrayagg(ename order by ename null on null)
+from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalAggregate(group=[{}], EXPR$0=[JSON_ARRAYAGG<NULL_ON_NULL>($0) WITHIN
GROUP ([1])])
+ LogicalProject($f0=[JSON_STRUCTURED_VALUE_EXPRESSION($1)], ENAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJsonArrayAgg4">
+ <Resource name="sql">
+ <![CDATA[select json_arrayagg(ename null on null) within group
(order by ename)
+from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalAggregate(group=[{}], EXPR$0=[JSON_ARRAYAGG<NULL_ON_NULL>($0) WITHIN
GROUP ([1])])
+ LogicalProject($f0=[JSON_STRUCTURED_VALUE_EXPRESSION($1)], ENAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testJsonObject">
<Resource name="sql">
<![CDATA[select json_object(ename: deptno, ename: deptno)
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index 539d1c6..a1b52cd 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -2568,4 +2568,41 @@ select json_object('deptno': deptno, 'employees':
json_arrayagg(json_object('ena
!ok
+# [CALCITE-2786] Add order by clause support for JSON_ARRAYAGG
+select gender,
+json_arrayagg(deptno order by deptno),
+json_arrayagg(deptno order by deptno desc)
+from emp group by gender;
++--------+------------------+------------------+
+| GENDER | EXPR$1 | EXPR$2 |
++--------+------------------+------------------+
+| F | [10,30,30,50,60] | [60,50,30,30,10] |
+| M | [10,20,50] | [50,20,10] |
++--------+------------------+------------------+
+(2 rows)
+
+!ok
+EnumerableAggregate(group=[{0}], EXPR$1=[JSON_ARRAYAGG<ABSENT_ON_NULL>($1)
WITHIN GROUP ([2])], EXPR$2=[JSON_ARRAYAGG<ABSENT_ON_NULL>($1) WITHIN GROUP ([2
DESC])])
+ EnumerableCalc(expr#0..1=[{inputs}],
expr#2=[JSON_STRUCTURED_VALUE_EXPRESSION($t0)], GENDER=[$t1], $f1=[$t2],
DEPTNO=[$t0])
+ EnumerableUnion(all=[true])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[10], expr#2=['F'],
EXPR$1=[$t1], EXPR$2=[$t2])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[10], expr#2=['M'],
EXPR$1=[$t1], EXPR$2=[$t2])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[20], expr#2=['M'],
EXPR$1=[$t1], EXPR$2=[$t2])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[30], expr#2=['F'],
EXPR$1=[$t1], EXPR$2=[$t2])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[30], expr#2=['F'],
EXPR$1=[$t1], EXPR$2=[$t2])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[50], expr#2=['M'],
EXPR$1=[$t1], EXPR$2=[$t2])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[50], expr#2=['F'],
EXPR$1=[$t1], EXPR$2=[$t2])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[60], expr#2=['F'],
EXPR$1=[$t1], EXPR$2=[$t2])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[null:INTEGER], expr#2=['F'],
EXPR$1=[$t1], EXPR$2=[$t2])
+ EnumerableValues(tuples=[[{ 0 }]])
+!plan
+
# End agg.iq
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index d429ac3..2ade413 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1975,12 +1975,13 @@ Not implemented:
| JSON_OBJECT( { [ KEY ] name VALUE value [ FORMAT JSON ] | name : value
[ FORMAT JSON ] } * [ { NULL | ABSENT } ON NULL ] ) | Construct json
object using a series of key (**name**) value (**value**) pairs
| JSON_OBJECTAGG( { [ KEY ] name VALUE value [ FORMAT JSON ] | name :
value [ FORMAT JSON ] } [ { NULL | ABSENT } ON NULL ] ) | Aggregate
function to construct json object using a key (**name**) value (**value**) pair
| JSON_ARRAY( { value [ FORMAT JSON ] } * [ { NULL | ABSENT } ON NULL ] )
| Construct json array using a series of values (**value**)
-| JSON_ARRAYAGG( value [ FORMAT JSON ] [ { NULL | ABSENT } ON NULL ] ) |
Aggregate function to construct json array using a value (**value**)
+| JSON_ARRAYAGG( value [ FORMAT JSON ] [ ORDER BY orderItem [, orderItem ]* ]
[ { NULL | ABSENT } ON NULL ] ) | Aggregate function to construct json
array using a value (**value**)
Note:
* The flag **FORMAT JSON** indicates the value is formatted as JSON character
string. When **FORMAT JSON** is used, value should be de-parse from JSON
character string to SQL structured value.
* **ON NULL** clause defines how the JSON output represents null value. The
default null behavior of **JSON_OBJECT** and **JSON_OBJECTAGG** is *NULL ON
NULL*, and for **JSON_ARRAY** and **JSON_ARRAYAGG** it is *ABSENT ON NULL*.
+* If **ORDER BY** clause is provided, **JSON_ARRAYAGG** will sort the input
rows by the specified order before performing aggregation.
#### Comparison Operators
