You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2015/11/17 20:31:00 UTC
[2/3] calcite git commit: [CALCITE-970] If NULLS FIRST/LAST not
specified, sort NULL values HIGH
[CALCITE-970] If NULLS FIRST/LAST not specified, sort NULL values HIGH
Add configuration parameter "defaultNullCollation" and enum
NullCollation { LOW, HIGH, FIRST, LAST } to allow alternative policies.
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/19a67e1d
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/19a67e1d
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/19a67e1d
Branch: refs/heads/master
Commit: 19a67e1de28dcb7576589222f79c804a3b81975c
Parents: 80bbb5b
Author: Julian Hyde <jh...@apache.org>
Authored: Sun Nov 15 23:54:04 2015 -0800
Committer: Julian Hyde <jh...@apache.org>
Committed: Mon Nov 16 12:48:31 2015 -0800
----------------------------------------------------------------------
.../calcite/config/CalciteConnectionConfig.java | 2 +
.../config/CalciteConnectionConfigImpl.java | 5 ++
.../config/CalciteConnectionProperty.java | 6 +++
.../apache/calcite/config/NullCollation.java | 50 +++++++++++++++++
.../calcite/prepare/CalcitePrepareImpl.java | 1 +
.../apache/calcite/rel/RelCollationImpl.java | 2 +-
.../org/apache/calcite/rel/RelCollations.java | 5 +-
.../apache/calcite/rel/RelFieldCollation.java | 45 ++++++++++++----
.../apache/calcite/rex/RexFieldCollation.java | 2 +-
.../calcite/sql/validate/SqlValidator.java | 9 ++++
.../calcite/sql/validate/SqlValidatorImpl.java | 11 ++++
.../calcite/sql2rel/SqlToRelConverter.java | 17 ++++++
.../org/apache/calcite/tools/RelBuilder.java | 11 ++--
.../java/org/apache/calcite/test/JdbcTest.java | 57 ++++++++++++++++++--
.../apache/calcite/test/MockCatalogReader.java | 3 +-
.../apache/calcite/test/SqlToRelTestBase.java | 14 ++---
core/src/test/resources/sql/sort.oq | 30 +++++++++++
17 files changed, 233 insertions(+), 37 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfig.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfig.java b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfig.java
index b41f478..d04e76d 100644
--- a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfig.java
+++ b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfig.java
@@ -30,6 +30,8 @@ public interface CalciteConnectionConfig extends ConnectionConfig {
boolean materializationsEnabled();
/** @see CalciteConnectionProperty#CREATE_MATERIALIZATIONS */
boolean createMaterializations();
+ /** @see CalciteConnectionProperty#DEFAULT_NULL_COLLATION */
+ NullCollation defaultNullCollation();
/** @see CalciteConnectionProperty#MODEL */
String model();
/** @see CalciteConnectionProperty#LEX */
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
index 99876b3..fefacaa 100644
--- a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
+++ b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
@@ -51,6 +51,11 @@ public class CalciteConnectionConfigImpl extends ConnectionConfigImpl
.getBoolean();
}
+ public NullCollation defaultNullCollation() {
+ return CalciteConnectionProperty.DEFAULT_NULL_COLLATION.wrap(properties)
+ .getEnum(NullCollation.class, NullCollation.HIGH);
+ }
+
public String model() {
return CalciteConnectionProperty.MODEL.wrap(properties).getString();
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java b/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
index 3b945ad..8cbac97 100644
--- a/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
+++ b/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
@@ -39,6 +39,11 @@ public enum CalciteConnectionProperty implements ConnectionProperty {
/** Whether Calcite should create materializations. */
CREATE_MATERIALIZATIONS("createMaterializations", Type.BOOLEAN, true, false),
+ /** How NULL values should be sorted if neither NULLS FIRST nor NULLS LAST are
+ * specified. The defult, HIGH, sorts NULL values the same as Oracle. */
+ DEFAULT_NULL_COLLATION("defaultNullCollation", Type.ENUM, NullCollation.HIGH,
+ true),
+
/** URI of the model. */
MODEL("model", Type.STRING, null, false),
@@ -128,6 +133,7 @@ public enum CalciteConnectionProperty implements ConnectionProperty {
public PropEnv wrap(Properties properties) {
return new PropEnv(parse(properties, NAME_TO_PROPS), this);
}
+
}
// End CalciteConnectionProperty.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/main/java/org/apache/calcite/config/NullCollation.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/config/NullCollation.java b/core/src/main/java/org/apache/calcite/config/NullCollation.java
new file mode 100644
index 0000000..8fc17f2
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/config/NullCollation.java
@@ -0,0 +1,50 @@
+/*
+ * 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.
+ */
+package org.apache.calcite.config;
+
+/** Strategy for how NULL values are to be sorted if NULLS FIRST or NULLS LAST
+ * are not specified in an item in the ORDER BY clause. */
+public enum NullCollation {
+ /** Nulls first for DESC, nulls last for ASC. */
+ HIGH,
+ /** Nulls last for DESC, nulls first for ASC. */
+ LOW,
+ /** Nulls first for DESC and ASC. */
+ FIRST,
+ /** Nulls last for DESC and ASC. */
+ LAST;
+
+ /** Returns whether NULL values should appear last.
+ *
+ * @param desc Whether sort is descending
+ */
+ public boolean last(boolean desc) {
+ switch (this) {
+ case FIRST:
+ return false;
+ case LAST:
+ return true;
+ case LOW:
+ return desc;
+ case HIGH:
+ default:
+ return !desc;
+ }
+ }
+}
+
+// End NullCollation.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/main/java/org/apache/calcite/prepare/CalcitePrepareImpl.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/prepare/CalcitePrepareImpl.java b/core/src/main/java/org/apache/calcite/prepare/CalcitePrepareImpl.java
index bdcd0e9..052be41 100644
--- a/core/src/main/java/org/apache/calcite/prepare/CalcitePrepareImpl.java
+++ b/core/src/main/java/org/apache/calcite/prepare/CalcitePrepareImpl.java
@@ -714,6 +714,7 @@ public class CalcitePrepareImpl implements CalcitePrepare {
final SqlValidator validator =
createSqlValidator(catalogReader, typeFactory);
validator.setIdentifierExpansion(true);
+ validator.setDefaultNullCollation(config.defaultNullCollation());
final List<Prepare.Materialization> materializations =
config.materializationsEnabled()
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/main/java/org/apache/calcite/rel/RelCollationImpl.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/RelCollationImpl.java b/core/src/main/java/org/apache/calcite/rel/RelCollationImpl.java
index 44e4a4b..df4fcbc 100644
--- a/core/src/main/java/org/apache/calcite/rel/RelCollationImpl.java
+++ b/core/src/main/java/org/apache/calcite/rel/RelCollationImpl.java
@@ -132,7 +132,7 @@ public class RelCollationImpl implements RelCollation {
RelFieldCollation e = it.next();
sb.append(e.getFieldIndex());
if (e.direction != RelFieldCollation.Direction.ASCENDING
- || e.nullDirection != RelFieldCollation.NullDirection.UNSPECIFIED) {
+ || e.nullDirection != e.direction.defaultNullDirection()) {
sb.append(' ').append(e.shortString());
}
if (!it.hasNext()) {
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/main/java/org/apache/calcite/rel/RelCollations.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/RelCollations.java b/core/src/main/java/org/apache/calcite/rel/RelCollations.java
index 07a7410..1b8d1a0 100644
--- a/core/src/main/java/org/apache/calcite/rel/RelCollations.java
+++ b/core/src/main/java/org/apache/calcite/rel/RelCollations.java
@@ -67,10 +67,7 @@ public class RelCollations {
* Creates a collation containing one field.
*/
public static RelCollation of(int fieldIndex) {
- return of(
- new RelFieldCollation(fieldIndex,
- RelFieldCollation.Direction.ASCENDING,
- RelFieldCollation.NullDirection.UNSPECIFIED));
+ return of(new RelFieldCollation(fieldIndex));
}
/**
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/main/java/org/apache/calcite/rel/RelFieldCollation.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/RelFieldCollation.java b/core/src/main/java/org/apache/calcite/rel/RelFieldCollation.java
index 7fa2274..9f605e2 100644
--- a/core/src/main/java/org/apache/calcite/rel/RelFieldCollation.java
+++ b/core/src/main/java/org/apache/calcite/rel/RelFieldCollation.java
@@ -18,6 +18,8 @@ package org.apache.calcite.rel;
import org.apache.calcite.sql.validate.SqlMonotonicity;
+import com.google.common.base.Preconditions;
+
/**
* Definition of the ordering of one field of a {@link RelNode} whose
* output is to be sorted.
@@ -120,6 +122,21 @@ public class RelFieldCollation {
throw new AssertionError("unknown: " + monotonicity);
}
}
+
+ /** Returns the null direction if not specified. Consistent with Oracle,
+ * NULLS are sorted as if they were positive infinity. */
+ public NullDirection defaultNullDirection() {
+ switch (this) {
+ case ASCENDING:
+ case STRICTLY_ASCENDING:
+ return NullDirection.LAST;
+ case DESCENDING:
+ case STRICTLY_DESCENDING:
+ return NullDirection.FIRST;
+ default:
+ return NullDirection.UNSPECIFIED;
+ }
+ }
}
/**
@@ -160,14 +177,14 @@ public class RelFieldCollation {
* Creates an ascending field collation.
*/
public RelFieldCollation(int fieldIndex) {
- this(fieldIndex, Direction.ASCENDING, NullDirection.UNSPECIFIED);
+ this(fieldIndex, Direction.ASCENDING);
}
/**
* Creates a field collation with unspecified null direction.
*/
public RelFieldCollation(int fieldIndex, Direction direction) {
- this(fieldIndex, direction, NullDirection.UNSPECIFIED);
+ this(fieldIndex, direction, direction.defaultNullDirection());
}
/**
@@ -178,10 +195,8 @@ public class RelFieldCollation {
Direction direction,
NullDirection nullDirection) {
this.fieldIndex = fieldIndex;
- this.direction = direction;
- this.nullDirection = nullDirection;
- assert direction != null;
- assert nullDirection != null;
+ this.direction = Preconditions.checkNotNull(direction);
+ this.nullDirection = Preconditions.checkNotNull(nullDirection);
}
//~ Methods ----------------------------------------------------------------
@@ -231,14 +246,22 @@ public class RelFieldCollation {
}
public String toString() {
- return fieldIndex
- + " " + direction.shortString
- + (nullDirection == NullDirection.UNSPECIFIED
- ? ""
- : " " + nullDirection);
+ if (direction == Direction.ASCENDING
+ && nullDirection == direction.defaultNullDirection()) {
+ return String.valueOf(fieldIndex);
+ }
+ final StringBuilder sb = new StringBuilder();
+ sb.append(fieldIndex).append(" ").append(direction.shortString);
+ if (nullDirection != direction.defaultNullDirection()) {
+ sb.append(" ").append(nullDirection);
+ }
+ return sb.toString();
}
public String shortString() {
+ if (nullDirection == direction.defaultNullDirection()) {
+ return direction.shortString;
+ }
switch (nullDirection) {
case FIRST:
return direction.shortString + "-nulls-first";
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/main/java/org/apache/calcite/rex/RexFieldCollation.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rex/RexFieldCollation.java b/core/src/main/java/org/apache/calcite/rex/RexFieldCollation.java
index 46c52d8..7b76bb7 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexFieldCollation.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexFieldCollation.java
@@ -63,7 +63,7 @@ public class RexFieldCollation extends Pair<RexNode, ImmutableSet<SqlKind>> {
? RelFieldCollation.NullDirection.LAST
: right.contains(SqlKind.NULLS_FIRST)
? RelFieldCollation.NullDirection.FIRST
- : RelFieldCollation.NullDirection.UNSPECIFIED;
+ : getDirection().defaultNullDirection();
}
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
index 9ff5e80..962668f 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
@@ -16,6 +16,7 @@
*/
package org.apache.calcite.sql.validate;
+import org.apache.calcite.config.NullCollation;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rel.type.RelDataTypeField;
@@ -597,6 +598,14 @@ public interface SqlValidator {
*/
boolean getColumnReferenceExpansion();
+ /** Sets how NULL values should be collated if an ORDER BY item does not
+ * contain NULLS FIRST or NULLS LAST. */
+ void setDefaultNullCollation(NullCollation nullCollation);
+
+ /** Returns how NULL values should be collated if an ORDER BY item does not
+ * contain NULLS FIRST or NULLS LAST. */
+ NullCollation getDefaultNullCollation();
+
/**
* Returns expansion of identifiers.
*
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index f20273e..cac83c1 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -16,6 +16,7 @@
*/
package org.apache.calcite.sql.validate;
+import org.apache.calcite.config.NullCollation;
import org.apache.calcite.linq4j.Ord;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
@@ -258,6 +259,8 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
private boolean rewriteCalls;
+ private NullCollation nullCollation = NullCollation.HIGH;
+
// TODO jvs 11-Dec-2008: make this local to performUnconditionalRewrites
// if it's OK to expand the signature of that method.
private boolean validatingSqlMerge;
@@ -1689,6 +1692,14 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
return expandColumnReferences;
}
+ public void setDefaultNullCollation(NullCollation nullCollation) {
+ this.nullCollation = Preconditions.checkNotNull(nullCollation);
+ }
+
+ public NullCollation getDefaultNullCollation() {
+ return nullCollation;
+ }
+
// implement SqlValidator
public void setCallRewrite(boolean rewriteCalls) {
this.rewriteCalls = rewriteCalls;
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 780b45b..b67fa72 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -2759,6 +2759,13 @@ public class SqlToRelConverter {
SqlNode converted = validator.expandOrderExpr(select, orderItem);
+ switch (nullDirection) {
+ case UNSPECIFIED:
+ nullDirection = validator.getDefaultNullCollation().last(desc(direction))
+ ? RelFieldCollation.NullDirection.LAST
+ : RelFieldCollation.NullDirection.FIRST;
+ }
+
// Scan the select list and order exprs for an identical expression.
final SelectScope selectScope = validator.getRawSelectScope(select);
int ordinal = -1;
@@ -2785,6 +2792,16 @@ public class SqlToRelConverter {
return new RelFieldCollation(ordinal + 1, direction, nullDirection);
}
+ private static boolean desc(RelFieldCollation.Direction direction) {
+ switch (direction) {
+ case DESCENDING:
+ case STRICTLY_DESCENDING:
+ return true;
+ default:
+ return false;
+ }
+ }
+
protected boolean enableDecorrelation() {
// disable subquery decorrelation when needed.
// e.g. if outer joins are not supported.
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
index e2400b8..753a0af 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -1218,8 +1218,8 @@ public class RelBuilder {
final List<RexNode> originalExtraNodes = ImmutableList.copyOf(extraNodes);
for (RexNode node : nodes) {
fieldCollations.add(
- collation(node, RelFieldCollation.Direction.ASCENDING,
- RelFieldCollation.NullDirection.UNSPECIFIED, extraNodes));
+ collation(node, RelFieldCollation.Direction.ASCENDING, null,
+ extraNodes));
}
final RexNode offsetNode = offset <= 0 ? null : literal(offset);
final RexNode fetchNode = fetch < 0 ? null : literal(fetch);
@@ -1274,8 +1274,8 @@ public class RelBuilder {
RelFieldCollation.NullDirection nullDirection, List<RexNode> extraNodes) {
switch (node.getKind()) {
case INPUT_REF:
- return new RelFieldCollation(((RexInputRef) node).getIndex(),
- direction, nullDirection);
+ return new RelFieldCollation(((RexInputRef) node).getIndex(), direction,
+ Util.first(nullDirection, direction.defaultNullDirection()));
case DESCENDING:
return collation(((RexCall) node).getOperands().get(0),
RelFieldCollation.Direction.DESCENDING,
@@ -1289,7 +1289,8 @@ public class RelBuilder {
default:
final int fieldIndex = extraNodes.size();
extraNodes.add(node);
- return new RelFieldCollation(fieldIndex, direction, nullDirection);
+ return new RelFieldCollation(fieldIndex, direction,
+ Util.first(nullDirection, direction.defaultNullDirection()));
}
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/test/java/org/apache/calcite/test/JdbcTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index e4f012a..fd98c0b 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -29,7 +29,9 @@ import org.apache.calcite.avatica.AvaticaStatement;
import org.apache.calcite.avatica.Handler;
import org.apache.calcite.avatica.HandlerImpl;
import org.apache.calcite.avatica.Meta;
+import org.apache.calcite.config.CalciteConnectionConfig;
import org.apache.calcite.config.Lex;
+import org.apache.calcite.config.NullCollation;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.jdbc.CalciteMetaImpl;
import org.apache.calcite.jdbc.CalcitePrepare;
@@ -3032,15 +3034,15 @@ public class JdbcTest {
+ "store_id=0; grocery_sqft=null\n");
}
- /** Tests ORDER BY ... DESC. Nulls come last, as for ASC. */
+ /** Tests ORDER BY ... DESC. Nulls come first (they come last for ASC). */
@Test public void testOrderByDesc() {
CalciteAssert.that()
.with(CalciteAssert.Config.FOODMART_CLONE)
.query("select \"store_id\", \"grocery_sqft\" from \"store\"\n"
+ "where \"store_id\" < 3 order by 2 desc")
- .returns("store_id=2; grocery_sqft=22271\n"
- + "store_id=1; grocery_sqft=17475\n"
- + "store_id=0; grocery_sqft=null\n");
+ .returns("store_id=0; grocery_sqft=null\n"
+ + "store_id=2; grocery_sqft=22271\n"
+ + "store_id=1; grocery_sqft=17475\n");
}
/** Tests sorting by an expression not in the select clause. */
@@ -3141,6 +3143,53 @@ public class JdbcTest {
+ "store_id=0; grocery_sqft=null\n");
}
+ /** Tests ORDER BY ... with various values of
+ * {@link CalciteConnectionConfig#defaultNullCollation()}. */
+ @Test public void testOrderByVarious() {
+ final boolean[] booleans = {false, true};
+ for (NullCollation nullCollation : NullCollation.values()) {
+ for (boolean asc : booleans) {
+ checkOrderBy(asc, nullCollation);
+ }
+ }
+ }
+
+ public void checkOrderBy(final boolean desc,
+ final NullCollation nullCollation) {
+ final Function<ResultSet, Void> checker = new Function<ResultSet, Void>() {
+ public Void apply(ResultSet resultSet) {
+ final String msg = (desc ? "DESC" : "ASC") + ":" + nullCollation;
+ final List<Number> numbers = new ArrayList<>();
+ try {
+ while (resultSet.next()) {
+ numbers.add((Number) resultSet.getObject(2));
+ }
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ assertThat(msg, numbers.size(), is(3));
+ assertThat(msg, numbers.get(nullCollation.last(desc) ? 2 : 0), nullValue());
+ return null;
+ }
+ };
+ final CalciteAssert.AssertThat with = CalciteAssert.that()
+ .with(CalciteAssert.Config.FOODMART_CLONE)
+ .with("defaultNullCollation", nullCollation.name());
+ final String sql = "select \"store_id\", \"grocery_sqft\" from \"store\"\n"
+ + "where \"store_id\" < 3 order by 2 "
+ + (desc ? " DESC" : "");
+ final String sql1 = "select \"store_id\", \"grocery_sqft\" from \"store\"\n"
+ + "where \"store_id\" < 3 order by \"florist\", 2 "
+ + (desc ? " DESC" : "");
+ final String sql2 = "select \"store_id\", \"grocery_sqft\" from \"store\"\n"
+ + "where \"store_id\" < 3 order by 2 "
+ + (desc ? " DESC" : "")
+ + ", 1";
+ with.query(sql).returns(checker);
+ with.query(sql1).returns(checker);
+ with.query(sql2).returns(checker);
+ }
+
/** Tests ORDER BY ... FETCH. */
@Test public void testOrderByFetch() {
CalciteAssert.that()
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java b/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
index 33ecff5..eccedb2 100644
--- a/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
+++ b/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
@@ -519,8 +519,7 @@ public class MockCatalogReader implements Prepare.CatalogReader {
: RelFieldCollation.Direction.ASCENDING;
collationList.add(
RelCollations.of(
- new RelFieldCollation(i, direction,
- RelFieldCollation.NullDirection.UNSPECIFIED)));
+ new RelFieldCollation(i, direction)));
}
}
return collationList;
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java b/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java
index d7af516..eca9a55 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java
@@ -226,7 +226,7 @@ public abstract class SqlToRelTestBase {
final List<RelCollation> collationList = deduceMonotonicity(table);
if (names.size() < 3) {
String[] newNames2 = {"CATALOG", "SALES", ""};
- List<String> newNames = new ArrayList<String>();
+ List<String> newNames = new ArrayList<>();
int i = 0;
while (newNames.size() < newNames2.length) {
newNames.add(i, newNames2[i]);
@@ -239,8 +239,7 @@ public abstract class SqlToRelTestBase {
private List<RelCollation> deduceMonotonicity(SqlValidatorTable table) {
final RelDataType rowType = table.getRowType();
- final List<RelCollation> collationList =
- new ArrayList<RelCollation>();
+ final List<RelCollation> collationList = new ArrayList<>();
// Deduce which fields the table is sorted on.
int i = -1;
@@ -254,9 +253,7 @@ public abstract class SqlToRelTestBase {
? RelFieldCollation.Direction.DESCENDING
: RelFieldCollation.Direction.ASCENDING;
collationList.add(
- RelCollations.of(
- new RelFieldCollation(i, direction,
- RelFieldCollation.NullDirection.UNSPECIFIED)));
+ RelCollations.of(new RelFieldCollation(i, direction)));
}
}
return collationList;
@@ -274,7 +271,7 @@ public abstract class SqlToRelTestBase {
new DelegatingRelOptTable(table) {
public List<String> getQualifiedName() {
final List<String> list =
- new ArrayList<String>(super.getQualifiedName());
+ new ArrayList<>(super.getQualifiedName());
list.set(
list.size() - 1,
list.get(list.size() - 1) + ":" + datasetName);
@@ -534,8 +531,7 @@ public abstract class SqlToRelTestBase {
public SqlNode parseQuery(String sql) throws Exception {
SqlParser parser = SqlParser.create(sql);
- SqlNode sqlNode = parser.parseQuery();
- return sqlNode;
+ return parser.parseQuery();
}
public SqlConformance getConformance() {
http://git-wip-us.apache.org/repos/asf/calcite/blob/19a67e1d/core/src/test/resources/sql/sort.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/sort.oq b/core/src/test/resources/sql/sort.oq
index 664a3c4..0253072 100644
--- a/core/src/test/resources/sql/sort.oq
+++ b/core/src/test/resources/sql/sort.oq
@@ -69,6 +69,36 @@ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[2], expr#3=[>=($t0, $t2)], expr#4=[
!plan
# [CALCITE-970] Default collation of NULL values
+# Nulls high, i.e. first if DESC
+select "store_id", "grocery_sqft" from "store"
+where "store_id" < 3
+order by 2 DESC;
++----------+--------------+
+| store_id | grocery_sqft |
++----------+--------------+
+| 0 | |
+| 2 | 22271 |
+| 1 | 17475 |
++----------+--------------+
+(3 rows)
+
+!ok
+
+# Nulls high, i.e. first if DESC, composite sort
+select "store_id", "grocery_sqft" from "store"
+where "store_id" < 3
+order by "florist", 2 DESC;
++----------+--------------+
+| store_id | grocery_sqft |
++----------+--------------+
+| 0 | |
+| 2 | 22271 |
+| 1 | 17475 |
++----------+--------------+
+(3 rows)
+
+!ok
+
# Nulls high, i.e. last if ASC
select "store_id", "grocery_sqft" from "store"
where "store_id" < 3