This is an automated email from the ASF dual-hosted git repository.
xiong pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new a88b1e81ca [CALCITE-4860] In Elasticsearch adapter, support NULLS
FIRST and NULLS LAST query
a88b1e81ca is described below
commit a88b1e81cacf2c52c52ee93b5e95ffe6fd785a12
Author: Zhe Hu <[email protected]>
AuthorDate: Mon Dec 16 22:51:51 2024 +0800
[CALCITE-4860] In Elasticsearch adapter, support NULLS FIRST and NULLS LAST
query
---
.../src/main/java/org/apache/calcite/util/Bug.java | 6 +
.../adapter/elasticsearch/ElasticsearchMethod.java | 1 +
.../adapter/elasticsearch/ElasticsearchRel.java | 12 ++
.../adapter/elasticsearch/ElasticsearchSort.java | 2 +
.../adapter/elasticsearch/ElasticsearchTable.java | 19 +++-
.../ElasticsearchToEnumerableConverter.java | 4 +-
...gationTest.java => AggregationAndSortTest.java} | 122 ++++++++++++++-------
.../elasticsearch/ElasticSearchAdapterTest.java | 24 ++--
8 files changed, 137 insertions(+), 53 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/util/Bug.java
b/core/src/main/java/org/apache/calcite/util/Bug.java
index 063d55bd9c..7eb756d5d2 100644
--- a/core/src/main/java/org/apache/calcite/util/Bug.java
+++ b/core/src/main/java/org/apache/calcite/util/Bug.java
@@ -171,6 +171,12 @@ public abstract class Bug {
* fixed. */
public static final boolean CALCITE_4645_FIXED = false;
+ /** Whether
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-4868";>[CALCITE-4868]
+ * Elasticsearch adapter fails if GROUP BY is followed by ORDER BY</a> is
+ * fixed. */
+ public static final boolean CALCITE_4868_FIXED = false;
+
/** Whether
* <a
href="https://issues.apache.org/jira/browse/CALCITE-5422";>[CALCITE-5422]
* MILLISECOND and MICROSECOND units in INTERVAL literal</a> is fixed. */
diff --git
a/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchMethod.java
b/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchMethod.java
index be49ac0ffe..27e69882dc 100644
---
a/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchMethod.java
+++
b/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchMethod.java
@@ -34,6 +34,7 @@ enum ElasticsearchMethod {
List.class, // ops - projections and other stuff
List.class, // fields
List.class, // sort
+ List.class, // nulls sort
List.class, // groupBy
List.class, // aggregations
Map.class, // item to expression mapping. Eg. _MAP['a.b.c'] and EXPR$1
diff --git
a/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchRel.java
b/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchRel.java
index 8f21d43a0c..8cc4f52497 100644
---
a/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchRel.java
+++
b/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchRel.java
@@ -56,6 +56,13 @@ public interface ElasticsearchRel extends RelNode {
*/
final List<Map.Entry<String, RelFieldCollation.Direction>> sort = new
ArrayList<>();
+ /**
+ * Sorting missing values.
+ *
+ * @see <a
href="https://www.elastic.co/guide/en/elasticsearch/reference/current/sort-search-results.html#_missing_values";>Missing
Values</a>
+ */
+ final List<Map.Entry<String, RelFieldCollation.NullDirection>> nullsSort =
new ArrayList<>();
+
/**
* Elastic aggregation ({@code MIN / MAX / COUNT} etc.) statements
(functions).
*
@@ -110,6 +117,11 @@ public interface ElasticsearchRel extends RelNode {
sort.add(Pair.of(field, direction));
}
+ void addNullsSort(String field, RelFieldCollation.NullDirection
nullDirection) {
+ requireNonNull(field, "field");
+ nullsSort.add(new Pair<>(field, nullDirection));
+ }
+
void addAggregation(String field, String expression) {
requireNonNull(field, "field");
requireNonNull(expression, "expression");
diff --git
a/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchSort.java
b/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchSort.java
index 320e7aa383..e7af286279 100644
---
a/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchSort.java
+++
b/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchSort.java
@@ -68,6 +68,8 @@ public class ElasticsearchSort extends Sort implements
ElasticsearchRel {
for (RelFieldCollation fieldCollation : collation.getFieldCollations()) {
final String name = fields.get(fieldCollation.getFieldIndex()).getName();
final String rawName = implementor.expressionItemMap.getOrDefault(name,
name);
+ // if nulls order is not specified, default NULLS LAST/FIRST for ASC/DESC
+ implementor.addNullsSort(rawName, fieldCollation.nullDirection);
implementor.addSort(rawName, fieldCollation.getDirection());
}
diff --git
a/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchTable.java
b/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchTable.java
index 11f2e506ff..db8441636b 100644
---
a/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchTable.java
+++
b/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchTable.java
@@ -104,12 +104,14 @@ public class ElasticsearchTable extends
AbstractQueryableTable implements Transl
* @param ops List of operations represented as Json strings.
* @param fields List of fields to project; or null to return map
* @param sort list of fields to sort and their direction (asc/desc)
+ * @param nullsSort List of fields to sort null value and their direction
(asc/desc)
* @param aggregations aggregation functions
* @return Enumerator of results
*/
private Enumerable<Object> find(List<String> ops,
List<Map.Entry<String, Class>> fields,
List<Map.Entry<String, RelFieldCollation.Direction>> sort,
+ List<Map.Entry<String, RelFieldCollation.NullDirection>> nullsSort,
List<String> groupBy,
List<Map.Entry<String, String>> aggregations,
Map<String, String> mappings,
@@ -128,10 +130,15 @@ public class ElasticsearchTable extends
AbstractQueryableTable implements Transl
if (!sort.isEmpty()) {
ArrayNode sortNode = query.withArray("sort");
- sort.forEach(e ->
- sortNode.add(
- mapper.createObjectNode().put(e.getKey(),
- e.getValue().isDescending() ? "desc" : "asc")));
+ for (int i = 0; i < sort.size(); i++) {
+ Map.Entry<String, RelFieldCollation.Direction> sortField = sort.get(i);
+ ObjectNode fieldSortProp = mapper.createObjectNode();
+ String nullsDirection = nullsSort.get(i).getValue() ==
RelFieldCollation.NullDirection.FIRST
+ ? "_first" : "_last";
+ fieldSortProp.put("missing", nullsDirection)
+ .put("order", sortField.getValue().isDescending() ? "desc" :
"asc");
+ sortNode.add(mapper.createObjectNode().set(sortField.getKey(),
fieldSortProp));
+ }
}
if (offset != null) {
@@ -361,12 +368,14 @@ public class ElasticsearchTable extends
AbstractQueryableTable implements Transl
public Enumerable<Object> find(List<String> ops,
List<Map.Entry<String, Class>> fields,
List<Map.Entry<String, RelFieldCollation.Direction>> sort,
+ List<Map.Entry<String, RelFieldCollation.NullDirection>> nullsSort,
List<String> groupBy,
List<Map.Entry<String, String>> aggregations,
Map<String, String> mappings,
Long offset, Long fetch) {
try {
- return getTable().find(ops, fields, sort, groupBy, aggregations,
mappings, offset, fetch);
+ return getTable().find(ops, fields, sort, nullsSort, groupBy,
aggregations, mappings,
+ offset, fetch);
} catch (IOException e) {
throw new UncheckedIOException("Failed to query " +
getTable().indexName, e);
}
diff --git
a/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchToEnumerableConverter.java
b/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchToEnumerableConverter.java
index 18ed22c4ac..a2c2f5b6ce 100644
---
a/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchToEnumerableConverter.java
+++
b/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchToEnumerableConverter.java
@@ -94,6 +94,8 @@ public class ElasticsearchToEnumerableConverter extends
ConverterImpl implements
ElasticsearchTable.ElasticsearchQueryable.class)));
final Expression ops = block.append("ops",
Expressions.constant(implementor.list));
final Expression sort = block.append("sort",
constantArrayList(implementor.sort, Pair.class));
+ final Expression nullsSort =
+ block.append("nullsSort", constantArrayList(implementor.nullsSort,
Pair.class));
final Expression groupBy = block.append("groupBy",
Expressions.constant(implementor.groupBy));
final Expression aggregations =
block.append("aggregations",
@@ -110,7 +112,7 @@ public class ElasticsearchToEnumerableConverter extends
ConverterImpl implements
block.append("enumerable",
Expressions.call(table,
ElasticsearchMethod.ELASTICSEARCH_QUERYABLE_FIND.method, ops,
- fields, sort, groupBy, aggregations, mappings, offset, fetch));
+ fields, sort, nullsSort, groupBy, aggregations, mappings,
offset, fetch));
block.add(Expressions.return_(null, enumerable));
return relImplementor.result(physType, block.toBlock());
}
diff --git
a/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/AggregationTest.java
b/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/AggregationAndSortTest.java
similarity index 79%
rename from
elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/AggregationTest.java
rename to
elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/AggregationAndSortTest.java
index 00d32f422b..27658bdfee 100644
---
a/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/AggregationTest.java
+++
b/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/AggregationAndSortTest.java
@@ -48,7 +48,7 @@ import java.util.Map;
* Testing Elasticsearch aggregation transformations.
*/
@ResourceLock(value = "elasticsearch-scrolls", mode = ResourceAccessMode.READ)
-class AggregationTest {
+class AggregationAndSortTest {
public static final EmbeddedElasticsearchPolicy NODE =
EmbeddedElasticsearchPolicy.create();
@@ -127,24 +127,24 @@ class AggregationTest {
@Test void searchInRange() {
Assumptions.assumeTrue(Bug.CALCITE_4645_FIXED, "CALCITE-4645");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select count(*) from view where val1 >= 10 and val1 <=20")
.returns("EXPR$0=1\n");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select count(*) from view where val1 <= 10 or val1 >=20")
.returns("EXPR$0=2\n");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select count(*) from view where val1 <= 10 or (val1 > 15 and
val1 <= 20)")
.returns("EXPR$0=2\n");
}
@Test void countStar() {
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select count(*) from view")
.queryContains(
ElasticsearchChecker.elasticsearchChecker(
@@ -152,34 +152,34 @@ class AggregationTest {
.returns("EXPR$0=3\n");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select count(*) from view where cat1 = 'a'")
.returns("EXPR$0=1\n");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select count(*) from view where cat1 in ('a', 'b')")
.returns("EXPR$0=2\n");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select count(*) from view where val1 in (10, 20)")
.returns("EXPR$0=0\n");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select count(*) from view where cat4 in ('2018-01-01',
'2019-12-12')")
.returns("EXPR$0=2\n");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select count(*) from view where cat4 not in ('2018-01-01',
'2019-12-12')")
.returns("EXPR$0=1\n");
}
@Test void all() {
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select count(*), sum(val1), sum(val2) from view")
.queryContains(
ElasticsearchChecker.elasticsearchChecker(
@@ -190,7 +190,7 @@ class AggregationTest {
.returns("EXPR$0=3; EXPR$1=8.0; EXPR$2=47.0\n");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select min(val1), max(val2), count(*) from view")
.queryContains(
ElasticsearchChecker.elasticsearchChecker(
@@ -203,14 +203,14 @@ class AggregationTest {
@Test void cat1() {
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat1, sum(val1), sum(val2) from view group by cat1")
.returnsUnordered("cat1=null; EXPR$1=0.0; EXPR$2=5.0",
"cat1=a; EXPR$1=1.0; EXPR$2=0.0",
"cat1=b; EXPR$1=7.0; EXPR$2=42.0");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat1, count(*) from view group by cat1")
.returnsUnordered("cat1=null; EXPR$1=1",
"cat1=a; EXPR$1=1",
@@ -218,14 +218,14 @@ class AggregationTest {
// different order for agg functions
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select count(*), cat1 from view group by cat1")
.returnsUnordered("EXPR$0=1; cat1=a",
"EXPR$0=1; cat1=b",
"EXPR$0=1; cat1=null");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat1, count(*), sum(val1), sum(val2) from view group by
cat1")
.returnsUnordered("cat1=a; EXPR$1=1; EXPR$2=1.0; EXPR$3=0.0",
"cat1=b; EXPR$1=1; EXPR$2=7.0; EXPR$3=42.0",
@@ -234,19 +234,19 @@ class AggregationTest {
@Test void cat2() {
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat2, min(val1), max(val1), min(val2), max(val2) from
view group by cat2")
.returnsUnordered("cat2=g; EXPR$1=1.0; EXPR$2=1.0; EXPR$3=5.0;
EXPR$4=5.0",
"cat2=h; EXPR$1=7.0; EXPR$2=7.0; EXPR$3=42.0; EXPR$4=42.0");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat2, sum(val1), sum(val2) from view group by cat2")
.returnsUnordered("cat2=g; EXPR$1=1.0; EXPR$2=5.0",
"cat2=h; EXPR$1=7.0; EXPR$2=42.0");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat2, count(*) from view group by cat2")
.returnsUnordered("cat2=g; EXPR$1=2",
"cat2=h; EXPR$1=1");
@@ -254,14 +254,14 @@ class AggregationTest {
@Test void cat1Cat2() {
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat1, cat2, sum(val1), sum(val2) from view group by
cat1, cat2")
.returnsUnordered("cat1=a; cat2=g; EXPR$2=1.0; EXPR$3=0.0",
"cat1=null; cat2=g; EXPR$2=0.0; EXPR$3=5.0",
"cat1=b; cat2=h; EXPR$2=7.0; EXPR$3=42.0");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat1, cat2, count(*) from view group by cat1, cat2")
.returnsUnordered("cat1=a; cat2=g; EXPR$2=1",
"cat1=null; cat2=g; EXPR$2=1",
@@ -270,7 +270,7 @@ class AggregationTest {
@Test void cat1Cat3() {
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat1, cat3, sum(val1), sum(val2) from view group by
cat1, cat3")
.returnsUnordered("cat1=a; cat3=null; EXPR$2=1.0; EXPR$3=0.0",
"cat1=null; cat3=y; EXPR$2=0.0; EXPR$3=5.0",
@@ -281,20 +281,20 @@ class AggregationTest {
* function. */
@Test void anyValue() {
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat1, any_value(cat2) from view group by cat1")
.returnsUnordered("cat1=a; EXPR$1=g",
"cat1=null; EXPR$1=g",
"cat1=b; EXPR$1=h");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat2, any_value(cat1) from view group by cat2")
.returnsUnordered("cat2=g; EXPR$1=a", // EXPR$1=null is also valid
"cat2=h; EXPR$1=b");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat2, any_value(cat3) from view group by cat2")
.returnsUnordered("cat2=g; EXPR$1=y", // EXPR$1=null is also valid
"cat2=h; EXPR$1=z");
@@ -302,21 +302,21 @@ class AggregationTest {
@Test void anyValueWithOtherAgg() {
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat1, any_value(cat2), max(val1) from view group by
cat1")
.returnsUnordered("cat1=a; EXPR$1=g; EXPR$2=1.0",
"cat1=null; EXPR$1=g; EXPR$2=null",
"cat1=b; EXPR$1=h; EXPR$2=7.0");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select max(val1), cat1, any_value(cat2) from view group by
cat1")
.returnsUnordered("EXPR$0=1.0; cat1=a; EXPR$2=g",
"EXPR$0=null; cat1=null; EXPR$2=g",
"EXPR$0=7.0; cat1=b; EXPR$2=h");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select any_value(cat2), cat1, max(val1) from view group by
cat1")
.returnsUnordered("EXPR$0=g; cat1=a; EXPR$2=1.0",
"EXPR$0=g; cat1=null; EXPR$2=null",
@@ -325,7 +325,7 @@ class AggregationTest {
@Test void cat1Cat2Cat3() {
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat1, cat2, cat3, count(*), sum(val1), sum(val2)
from view "
+ "group by cat1, cat2, cat3")
.returnsUnordered("cat1=a; cat2=g; cat3=null; EXPR$3=1;
EXPR$4=1.0; EXPR$5=0.0",
@@ -340,7 +340,7 @@ class AggregationTest {
*/
@Test void dateCat() {
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat4, sum(val1) from view group by cat4")
.returnsUnordered("cat4=1514764800000; EXPR$1=1.0",
"cat4=1576108800000; EXPR$1=0.0",
@@ -354,7 +354,7 @@ class AggregationTest {
*/
@Test void integerCat() {
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat5, sum(val1) from view group by cat5")
.returnsUnordered("cat5=1; EXPR$1=1.0",
"cat5=null; EXPR$1=0.0",
@@ -367,23 +367,23 @@ class AggregationTest {
@Test void approximateCountDistinct() {
// approx_count_distinct counts distinct *non-null* values
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select approx_count_distinct(cat1) from view")
.returnsUnordered("EXPR$0=2");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select approx_count_distinct(cat2) from view")
.returnsUnordered("EXPR$0=2");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat1, approx_count_distinct(val1) from view group by
cat1")
.returnsUnordered("cat1=a; EXPR$1=1",
"cat1=b; EXPR$1=1",
"cat1=null; EXPR$1=0");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat1, approx_count_distinct(val2) from view group by
cat1")
.returnsUnordered("cat1=a; EXPR$1=0",
"cat1=b; EXPR$1=1",
@@ -394,7 +394,7 @@ class AggregationTest {
* {@code select max(cast(_MAP['foo'] as integer)) from tbl}. */
@Test void aggregationWithCast() {
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query(
String.format(Locale.ROOT, "select max(cast(_MAP['val1'] as
integer)) as v1, "
+ "min(cast(_MAP['val2'] as integer)) as v2 from elastic.%s",
NAME))
@@ -412,16 +412,62 @@ class AggregationTest {
*/
@Test void testGroupTextField() {
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat6, count(1) as CNT from view group by cat6")
.returnsUnordered("cat6=null; CNT=2",
"cat6=text1; CNT=1");
CalciteAssert.that()
- .with(AggregationTest::createConnection)
+ .with(AggregationAndSortTest::createConnection)
.query("select cat1, cat6 from view group by cat1, cat6")
.returnsUnordered("cat1=a; cat6=null",
"cat1=b; cat6=null",
"cat1=null; cat6=text1");
}
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-4860";>[CALCITE-4860]
+ * In Elasticsearch adapter, support NULLS FIRST and NULLS LAST query</a>.
+ */
+ @Test void testNullsSort() {
+ CalciteAssert.that()
+ .with(AggregationAndSortTest::createConnection)
+ .query("select cat6, cat5 from view order by cat5 desc nulls last")
+ .returns("cat6=null; cat5=2\n"
+ + "cat6=null; cat5=1\n"
+ + "cat6=text1; cat5=null\n");
+
+ CalciteAssert.that()
+ .with(AggregationAndSortTest::createConnection)
+ .query("select cat6, cat5 from view order by cat5 desc nulls first")
+ .returns("cat6=text1; cat5=null\n"
+ + "cat6=null; cat5=2\n"
+ + "cat6=null; cat5=1\n");
+
+ CalciteAssert.that()
+ .with(AggregationAndSortTest::createConnection)
+ .query("select cat6, cat5 from view order by cat5 asc nulls last")
+ .returns("cat6=null; cat5=1\n"
+ + "cat6=null; cat5=2\n"
+ + "cat6=text1; cat5=null\n");
+
+ CalciteAssert.that()
+ .with(AggregationAndSortTest::createConnection)
+ .query("select cat6, cat5 from view order by cat5 asc nulls first")
+ .returns("cat6=text1; cat5=null\n"
+ + "cat6=null; cat5=1\n"
+ + "cat6=null; cat5=2\n");
+ }
+
+ @Test void testOrderByWithGroupBy() {
+ // Once CALCITE-4868 is fixed, we can enable this test
+ Assumptions.assumeTrue(Bug.CALCITE_4868_FIXED, "CALCITE-4868");
+ CalciteAssert.that()
+ .with(AggregationAndSortTest::createConnection)
+ .query("select cat6, cat5 from view group by cat6, cat5 "
+ + "order by cat5 desc nulls last")
+ .returns("cat6=null; cat5=2\n"
+ + "cat6=null; cat5=1\n"
+ + "cat6=text1; cat5=null\n");
+ }
}
diff --git
a/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/ElasticSearchAdapterTest.java
b/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/ElasticSearchAdapterTest.java
index 578e0e7d63..b3e769accf 100644
---
a/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/ElasticSearchAdapterTest.java
+++
b/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/ElasticSearchAdapterTest.java
@@ -251,7 +251,8 @@ class ElasticSearchAdapterTest {
.queryContains(
ElasticsearchChecker.elasticsearchChecker(
"'_source' : ['state', 'pop']",
- "sort: [ {state: 'asc'}, {pop: 'asc'}]",
+ "sort: [ {state: {'missing':'_last', 'order':'asc'}}, "
+ + "{pop: {'missing':'_last', 'order':'asc'}}]",
"from: 2",
"size: 3"));
}
@@ -318,7 +319,7 @@ class ElasticSearchAdapterTest {
.queryContains(
ElasticsearchChecker.elasticsearchChecker(
"query:{'constant_score':{filter:{term:{state:'NY'}}}}",
- "sort:[{city:'asc'}]",
+ "sort:[{city:{'missing':'_last', 'order':'asc'}}]",
String.format(Locale.ROOT, "size:%s",
ElasticsearchTransport.DEFAULT_FETCH_SIZE)))
.returnsOrdered(
"_MAP={id=11226, city=BROOKLYN, loc=[-73.956985, 40.646694],
pop=111396, state=NY}",
@@ -337,7 +338,7 @@ class ElasticSearchAdapterTest {
ElasticsearchChecker.elasticsearchChecker(
"query:{'dis_max':{'queries':[{'bool':{'should':"
+ "[{'term':{'state':'NY'}},{'term':"
- + "{'city':'BROOKLYN'}}]}}]}},'sort':[{'city':'asc'}]",
+ +
"{'city':'BROOKLYN'}}]}}]}},'sort':[{'city':{'missing':'_last',
'order':'asc'}}]",
String.format(Locale.ROOT, "size:%s",
ElasticsearchTransport.DEFAULT_FETCH_SIZE)));
@@ -380,12 +381,14 @@ class ElasticSearchAdapterTest {
calciteAssert()
.query(sql)
.returnsOrdered("city=CHICAGO; state=IL; pop=112047",
- "city=BROOKLYN; state=NY; pop=111396",
- "city=NEW YORK; state=NY; pop=106564")
+ "city=BROOKLYN; state=NY; pop=111396",
+ "city=NEW YORK; state=NY; pop=106564")
.queryContains(
ElasticsearchChecker.elasticsearchChecker(
"'_source':['city','state','pop']",
- "sort:[{pop:'desc'}, {state:'asc'}, {city:'desc'}]",
+ "sort:[{pop:{'missing':'_first', 'order':'desc'}}, "
+ + "{state:{'missing':'_last', 'order':'asc'}}, "
+ + "{city:{'missing':'_first', 'order':'desc'}}]",
"size:3"));
}
@@ -457,7 +460,8 @@ class ElasticSearchAdapterTest {
+ "pop:{script: 'params._source.pop'}, "
+ "state:{script: 'params._source.state'}, "
+ "id:{script: 'params._source.id'}}",
- "sort: [ {state: 'asc'}, {pop: 'asc'}]",
+ "sort: [ {state: {'missing':'_last', 'order':'asc'}}, "
+ + "{pop: {'missing':'_last', 'order':'asc'}}]",
String.format(Locale.ROOT, "size:%s",
ElasticsearchTransport.DEFAULT_FETCH_SIZE)))
.explainContains(explain);
}
@@ -484,7 +488,8 @@ class ElasticSearchAdapterTest {
+ "pop:{script: 'params._source.pop'}, "
+ "state:{script: 'params._source.state'}, "
+ "id:{script: 'params._source.id'}}",
- "sort: [ {state: 'asc'}, {pop: 'asc'}]",
+ "sort: [ {state: {'missing':'_last', 'order':'asc'}}, "
+ + "{pop: {'missing':'_last', 'order':'asc'}}]",
String.format(Locale.ROOT, "size:%s",
ElasticsearchTransport.DEFAULT_FETCH_SIZE)))
.explainContains(explain);
@@ -545,7 +550,8 @@ class ElasticSearchAdapterTest {
+ "{zero:{script:'0'},"
+ "state:{script:'params._source.state'},"
+ "city:{script:'params._source.city'}}",
- "sort:[{state:'asc'},{city:'asc'}]",
+ "sort:[{state:{'missing':'_last', 'order':'asc'}},"
+ + "{city:{'missing':'_last', 'order':'asc'}}]",
String.format(Locale.ROOT, "size:%d",
ElasticsearchTransport.DEFAULT_FETCH_SIZE)));
}
