You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by al...@apache.org on 2021/10/29 10:28:42 UTC
[ignite] branch sql-calcite updated: IGNITE-14975 Interval data
types support - Fixes #9530.
This is an automated email from the ASF dual-hosted git repository.
alexpl pushed a commit to branch sql-calcite
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/sql-calcite by this push:
new 4f536c1 IGNITE-14975 Interval data types support - Fixes #9530.
4f536c1 is described below
commit 4f536c10c3ffac8798713a4ae6fd53a486dc3aed
Author: Aleksey Plekhanov <pl...@gmail.com>
AuthorDate: Fri Oct 29 13:15:49 2021 +0300
IGNITE-14975 Interval data types support - Fixes #9530.
Signed-off-by: Aleksey Plekhanov <pl...@gmail.com>
---
.../src/main/codegen/includes/parserImpls.ftl | 32 +-
.../query/calcite/CalciteQueryProcessor.java | 4 +-
.../calcite/exec/exp/ExpressionFactoryImpl.java | 2 +
.../calcite/exec/exp/IgniteBuiltInMethod.java | 10 +-
.../query/calcite/exec/exp/RexImpTable.java | 16 +-
.../query/calcite/exec/exp/RexToLixTranslator.java | 37 +-
.../query/calcite/externalize/RelJson.java | 9 +-
.../query/calcite/prepare/IgniteTypeCoercion.java | 50 +++
.../calcite/sql/IgniteSqlIntervalTypeNameSpec.java | 62 ++++
.../query/calcite/type/IgniteTypeFactory.java | 41 ++-
.../processors/query/calcite/util/TypeUtils.java | 37 +-
.../query/calcite/CalciteQueryProcessorTest.java | 7 +-
.../integration/AbstractBasicIntegrationTest.java | 27 ++
.../integration/AggregatesIntegrationTest.java | 15 +-
.../query/calcite/integration/IntervalTest.java | 379 +++++++++++++++++++++
.../calcite/integration/JoinIntegrationTest.java | 19 --
.../calcite/integration/SetOpIntegrationTest.java | 80 ++---
.../query/calcite/jdbc/JdbcQueryTest.java | 10 +-
.../ignite/testsuites/IntegrationTestSuite.java | 2 +
.../test/sql/function/interval/test_extract.test | 233 +++++++++++++
.../sql/function/interval/test_extract.test_ignore | 187 ----------
...nstants.test_ignore => interval_constants.test} | 116 ++++---
.../src/test/sql/types/interval/test_interval.test | 163 +++++++++
.../sql/types/interval/test_interval.test_ignore | 240 -------------
...ion.test_ignore => test_interval_addition.test} | 22 +-
...n.test_ignore => test_interval_comparison.test} | 30 +-
.../test/sql/types/interval/test_interval_ops.test | 51 +++
.../types/interval/test_interval_ops.test_ignore | 47 +--
28 files changed, 1276 insertions(+), 652 deletions(-)
diff --git a/modules/calcite/src/main/codegen/includes/parserImpls.ftl b/modules/calcite/src/main/codegen/includes/parserImpls.ftl
index 725aac0..ee0927a 100644
--- a/modules/calcite/src/main/codegen/includes/parserImpls.ftl
+++ b/modules/calcite/src/main/codegen/includes/parserImpls.ftl
@@ -88,6 +88,32 @@ void CreateTableOption(List<SqlNode> list) :
}
}
+SqlDataTypeSpec DataTypeEx() :
+{
+ final SqlDataTypeSpec dt;
+}
+{
+ (
+ dt = DataType()
+ |
+ dt = IntervalType()
+ )
+ {
+ return dt;
+ }
+}
+
+SqlDataTypeSpec IntervalType() :
+{
+ final Span s;
+ final SqlIntervalQualifier intervalQualifier;
+}
+{
+ <INTERVAL> { s = span(); } intervalQualifier = IntervalQualifier() {
+ return new SqlDataTypeSpec(new IgniteSqlIntervalTypeNameSpec(intervalQualifier, s.end(this)), s.pos());
+ }
+}
+
void TableElement(List<SqlNode> list) :
{
final SqlDataTypeSpec type;
@@ -99,7 +125,7 @@ void TableElement(List<SqlNode> list) :
SqlIdentifier id = null;
}
{
- id = SimpleIdentifier() type = DataType() nullable = NullableOptDefaultTrue()
+ id = SimpleIdentifier() type = DataTypeEx() nullable = NullableOptDefaultTrue()
(
<DEFAULT_> { s.add(this); } dflt = Literal() {
strategy = ColumnStrategy.DEFAULT;
@@ -287,7 +313,7 @@ void InfixCast(List<Object> list, ExprContext exprContext, Span s) :
<INFIX_CAST> {
checkNonQueryExpression(exprContext);
}
- dt = DataType() {
+ dt = DataTypeEx() {
list.add(
new SqlParserUtil.ToTreeListItem(SqlLibraryOperators.INFIX_CAST,
s.pos()));
@@ -321,7 +347,7 @@ SqlNode ColumnWithType() :
}
{
id = SimpleIdentifier()
- type = DataType()
+ type = DataTypeEx()
[
<NOT> <NULL> {
nullable = false;
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java
index 00bdc66..d4fd450 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java
@@ -60,6 +60,7 @@ import org.apache.ignite.internal.processors.query.calcite.metadata.MappingServi
import org.apache.ignite.internal.processors.query.calcite.metadata.MappingServiceImpl;
import org.apache.ignite.internal.processors.query.calcite.metadata.cost.IgniteCostFactory;
import org.apache.ignite.internal.processors.query.calcite.prepare.IgniteConvertletTable;
+import org.apache.ignite.internal.processors.query.calcite.prepare.IgniteTypeCoercion;
import org.apache.ignite.internal.processors.query.calcite.prepare.QueryPlanCache;
import org.apache.ignite.internal.processors.query.calcite.prepare.QueryPlanCacheImpl;
import org.apache.ignite.internal.processors.query.calcite.schema.SchemaHolder;
@@ -104,7 +105,8 @@ public class CalciteQueryProcessor extends GridProcessorAdapter implements Query
.sqlValidatorConfig(SqlValidator.Config.DEFAULT
.withIdentifierExpansion(true)
.withDefaultNullCollation(NullCollation.LOW)
- .withSqlConformance(IgniteSqlConformance.INSTANCE))
+ .withSqlConformance(IgniteSqlConformance.INSTANCE)
+ .withTypeCoercionFactory(IgniteTypeCoercion::new))
// Dialects support.
.operatorTable(SqlOperatorTables.chain(
SqlLibraryOperatorTableFactory.INSTANCE
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/ExpressionFactoryImpl.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/ExpressionFactoryImpl.java
index ea8d7f4..7d8e8df 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/ExpressionFactoryImpl.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/ExpressionFactoryImpl.java
@@ -377,6 +377,8 @@ public class ExpressionFactoryImpl<Row> implements ExpressionFactory<Row> {
b.append(';');
b.append(nodes.get(i));
+ b.append(':');
+ b.append(nodes.get(i).getType().getFullTypeString());
new RexShuttle() {
@Override public RexNode visitFieldAccess(RexFieldAccess fieldAccess) {
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteBuiltInMethod.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteBuiltInMethod.java
index bbcc881..13fe07b 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteBuiltInMethod.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteBuiltInMethod.java
@@ -19,6 +19,8 @@ package org.apache.ignite.internal.processors.query.calcite.exec.exp;
import java.lang.reflect.Method;
import org.apache.calcite.linq4j.tree.Types;
+import org.apache.calcite.sql.SqlIntervalQualifier;
+import org.apache.calcite.sql.parser.SqlParserUtil;
/**
* Built-in methods.
@@ -28,7 +30,13 @@ public enum IgniteBuiltInMethod {
SYSTEM_RANGE2(IgniteSqlFunctions.class, "systemRange", Object.class, Object.class),
/** */
- SYSTEM_RANGE3(IgniteSqlFunctions.class, "systemRange", Object.class, Object.class, Object.class);
+ SYSTEM_RANGE3(IgniteSqlFunctions.class, "systemRange", Object.class, Object.class, Object.class),
+
+ /** */
+ PARSE_INTERVAL_YEAR_MONTH(SqlParserUtil.class, "intervalToMonths", String.class, SqlIntervalQualifier.class),
+
+ /** */
+ PARSE_INTERVAL_DAY_TIME(SqlParserUtil.class, "intervalToMillis", String.class, SqlIntervalQualifier.class);
/** */
public final Method method;
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java
index b83bc1e..63f2d9f 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java
@@ -1331,6 +1331,8 @@ public class RexImpTable {
Expression operand = argValueList.get(1);
final SqlTypeName sqlTypeName =
call.operands.get(1).getType().getSqlTypeName();
+ final boolean isIntervalType = SqlTypeUtil.isInterval(call.operands.get(1).getType());
+
switch (unit) {
case MILLENNIUM:
case CENTURY:
@@ -1382,7 +1384,7 @@ public class RexImpTable {
if (sqlTypeName == SqlTypeName.DATE)
return Expressions.constant(0L);
- operand = mod(operand, TimeUnit.MINUTE.multiplier.longValue());
+ operand = mod(operand, TimeUnit.MINUTE.multiplier.longValue(), !isIntervalType);
return Expressions.multiply(
operand, Expressions.constant((long)(1 / unit.multiplier.doubleValue())));
case EPOCH:
@@ -1430,12 +1432,16 @@ public class RexImpTable {
break;
}
- operand = mod(operand, getFactor(unit));
+ // According to SQL standard result for interval data types should have the same sign as the source,
+ // but QUARTER is not covered by standard and negative values for QUARTER make no sense.
+ operand = mod(operand, getFactor(unit), unit == TimeUnit.QUARTER || !isIntervalType );
+
if (unit == TimeUnit.QUARTER)
operand = Expressions.subtract(operand, Expressions.constant(1L));
operand = Expressions.divide(operand,
Expressions.constant(unit.multiplier.longValue()));
+
if (unit == TimeUnit.QUARTER)
operand = Expressions.add(operand, Expressions.constant(1L));
@@ -1444,12 +1450,12 @@ public class RexImpTable {
}
/** */
- private static Expression mod(Expression operand, long factor) {
+ private static Expression mod(Expression operand, long factor, boolean floorMod) {
if (factor == 1L)
return operand;
else {
- return Expressions.call(BuiltInMethod.FLOOR_MOD.method,
- operand, Expressions.constant(factor));
+ return floorMod ? Expressions.call(BuiltInMethod.FLOOR_MOD.method, operand, Expressions.constant(factor)) :
+ Expressions.modulo(operand, Expressions.constant(factor));
}
}
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexToLixTranslator.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexToLixTranslator.java
index f4d10b2..7439e34 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexToLixTranslator.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexToLixTranslator.java
@@ -16,6 +16,7 @@
*/
package org.apache.ignite.internal.processors.query.calcite.exec.exp;
+import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.lang.reflect.Type;
import java.math.BigDecimal;
@@ -24,7 +25,6 @@ import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
-
import com.google.common.collect.ImmutableList;
import org.apache.calcite.adapter.enumerable.PhysType;
import org.apache.calcite.adapter.java.JavaTypeFactory;
@@ -63,6 +63,7 @@ import org.apache.calcite.runtime.Geometries;
import org.apache.calcite.sql.SqlIntervalQualifier;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.sql.validate.SqlConformance;
@@ -502,6 +503,40 @@ public class RexToLixTranslator implements RexVisitor<RexToLixTranslator.Result>
operand));
break;
}
+ break;
+ case INTERVAL_YEAR:
+ case INTERVAL_YEAR_MONTH:
+ case INTERVAL_MONTH:
+ case INTERVAL_DAY:
+ case INTERVAL_DAY_HOUR:
+ case INTERVAL_DAY_MINUTE:
+ case INTERVAL_DAY_SECOND:
+ case INTERVAL_HOUR:
+ case INTERVAL_HOUR_MINUTE:
+ case INTERVAL_HOUR_SECOND:
+ case INTERVAL_MINUTE:
+ case INTERVAL_MINUTE_SECOND:
+ case INTERVAL_SECOND:
+ switch (sourceType.getSqlTypeName().getFamily()) {
+ case CHARACTER:
+ SqlIntervalQualifier intervalQualifier = targetType.getIntervalQualifier();
+
+ Method method = intervalQualifier.isYearMonth() ?
+ IgniteBuiltInMethod.PARSE_INTERVAL_YEAR_MONTH.method :
+ IgniteBuiltInMethod.PARSE_INTERVAL_DAY_TIME.method;
+
+ convert = Expressions.call(
+ method,
+ operand,
+ Expressions.new_(SqlIntervalQualifier.class,
+ Expressions.constant(intervalQualifier.getStartUnit()),
+ Expressions.constant(intervalQualifier.getStartPrecisionPreservingDefault()),
+ Expressions.constant(intervalQualifier.getEndUnit()),
+ Expressions.constant(intervalQualifier.getFractionalSecondPrecisionPreservingDefault()),
+ Expressions.field(null, SqlParserPos.class, "ZERO")
+ )
+ );
+ }
}
if (targetType.getSqlTypeName() == SqlTypeName.DECIMAL)
convert = ConverterUtils.convertToDecimal(operand, targetType);
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/externalize/RelJson.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/externalize/RelJson.java
index 0ecc98f..3a969f036 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/externalize/RelJson.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/externalize/RelJson.java
@@ -361,16 +361,17 @@ class RelJson {
SqlTypeName sqlTypeName = toEnum(map.get("type"));
Integer precision = (Integer)map.get("precision");
Integer scale = (Integer)map.get("scale");
+ RelDataType type;
+
if (SqlTypeName.INTERVAL_TYPES.contains(sqlTypeName)) {
TimeUnit startUnit = sqlTypeName.getStartUnit();
TimeUnit endUnit = sqlTypeName.getEndUnit();
- return typeFactory.createSqlIntervalType(
+ type = typeFactory.createSqlIntervalType(
new SqlIntervalQualifier(startUnit, endUnit, SqlParserPos.ZERO));
}
else if (sqlTypeName == SqlTypeName.ARRAY)
- return typeFactory.createArrayType(toType(typeFactory, map.get("elementType")), -1);
- RelDataType type;
- if (precision == null)
+ type = typeFactory.createArrayType(toType(typeFactory, map.get("elementType")), -1);
+ else if (precision == null)
type = typeFactory.createSqlType(sqlTypeName);
else if (scale == null)
type = typeFactory.createSqlType(sqlTypeName, precision);
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteTypeCoercion.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteTypeCoercion.java
new file mode 100644
index 0000000..db04ad9
--- /dev/null
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteTypeCoercion.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.ignite.internal.processors.query.calcite.prepare;
+
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeFactory;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.type.SqlTypeUtil;
+import org.apache.calcite.sql.validate.SqlValidator;
+import org.apache.calcite.sql.validate.SqlValidatorScope;
+import org.apache.calcite.sql.validate.implicit.TypeCoercionImpl;
+
+/**
+ * Implementation of implicit type cast.
+ */
+public class IgniteTypeCoercion extends TypeCoercionImpl {
+ /** Ctor. */
+ public IgniteTypeCoercion(RelDataTypeFactory typeFactory, SqlValidator validator) {
+ super(typeFactory, validator);
+ }
+
+ /** {@inheritDoc} */
+ @Override protected boolean needToCast(SqlValidatorScope scope, SqlNode node, RelDataType toType) {
+ if (SqlTypeUtil.isInterval(toType)) {
+ RelDataType fromType = validator.deriveType(scope, node);
+
+ if (SqlTypeUtil.isInterval(fromType)) {
+ // Two different families of intervals: INTERVAL_DAY_TIME and INTERVAL_YEAR_MONTH.
+ return fromType.getSqlTypeName().getFamily() != toType.getSqlTypeName().getFamily();
+ }
+ }
+
+ return super.needToCast(scope, node, toType);
+ }
+}
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/IgniteSqlIntervalTypeNameSpec.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/IgniteSqlIntervalTypeNameSpec.java
new file mode 100644
index 0000000..c6535b1
--- /dev/null
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/IgniteSqlIntervalTypeNameSpec.java
@@ -0,0 +1,62 @@
+/*
+ * 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.ignite.internal.processors.query.calcite.sql;
+
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.sql.SqlIdentifier;
+import org.apache.calcite.sql.SqlIntervalQualifier;
+import org.apache.calcite.sql.SqlTypeNameSpec;
+import org.apache.calcite.sql.SqlWriter;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.validate.SqlValidator;
+import org.apache.calcite.util.Litmus;
+
+/**
+ * A SQL type name specification of interval types.
+ */
+public class IgniteSqlIntervalTypeNameSpec extends SqlTypeNameSpec {
+ /** */
+ private final SqlIntervalQualifier sqlIntervalQualifier;
+
+ /** Constructor. */
+ public IgniteSqlIntervalTypeNameSpec(SqlIntervalQualifier intervalQualifier, SqlParserPos pos) {
+ super(new SqlIdentifier(intervalQualifier.typeName().name(), pos), pos);
+ sqlIntervalQualifier = intervalQualifier;
+ }
+
+ /** {@inheritDoc} */
+ @Override public RelDataType deriveType(SqlValidator validator) {
+ return validator.getTypeFactory().createSqlIntervalType(sqlIntervalQualifier);
+ }
+
+ /** {@inheritDoc} */
+ @Override public void unparse(SqlWriter writer, int leftPrec, int rightPrec) {
+ writer.keyword("INTERVAL");
+
+ getTypeName().unparse(writer, leftPrec, rightPrec);
+ }
+
+ /** {@inheritDoc} */
+ @Override public boolean equalsDeep(SqlTypeNameSpec spec, Litmus litmus) {
+ if (!(spec instanceof IgniteSqlIntervalTypeNameSpec))
+ return false;
+
+ IgniteSqlIntervalTypeNameSpec that = (IgniteSqlIntervalTypeNameSpec) spec;
+
+ return getTypeName().equalsDeep(that.getTypeName(), litmus);
+ }
+}
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/type/IgniteTypeFactory.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/type/IgniteTypeFactory.java
index 7f7c8cb..f523978 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/type/IgniteTypeFactory.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/type/IgniteTypeFactory.java
@@ -21,16 +21,21 @@ import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.nio.charset.Charset;
import java.sql.Timestamp;
+import java.time.Duration;
import java.time.LocalDateTime;
import java.time.LocalTime;
+import java.time.Period;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import org.apache.calcite.avatica.util.ByteString;
+import org.apache.calcite.avatica.util.TimeUnit;
import org.apache.calcite.jdbc.JavaTypeFactoryImpl;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeSystem;
import org.apache.calcite.runtime.Geometries;
+import org.apache.calcite.sql.SqlIntervalQualifier;
+import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.BasicSqlType;
import org.apache.calcite.sql.type.IntervalSqlType;
import org.apache.ignite.internal.util.typedef.F;
@@ -39,6 +44,14 @@ import org.apache.ignite.internal.util.typedef.F;
* Ignite type factory.
*/
public class IgniteTypeFactory extends JavaTypeFactoryImpl {
+ /** Interval qualifier to create year-month interval types. */
+ private static final SqlIntervalQualifier INTERVAL_QUALIFIER_YEAR_MONTH = new SqlIntervalQualifier(TimeUnit.YEAR,
+ TimeUnit.MONTH, SqlParserPos.ZERO);
+
+ /** Interval qualifier to create day-time interval types. */
+ private static final SqlIntervalQualifier INTERVAL_QUALIFIER_DAY_TIME = new SqlIntervalQualifier(TimeUnit.DAY,
+ TimeUnit.SECOND, SqlParserPos.ZERO);
+
/** */
public IgniteTypeFactory() {
super(IgniteTypeSystem.INSTANCE);
@@ -147,11 +160,13 @@ public class IgniteTypeFactory extends JavaTypeFactoryImpl {
case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
return LocalDateTime.class;
case INTEGER:
+ return type.isNullable() ? Integer.class : int.class;
case INTERVAL_YEAR:
case INTERVAL_YEAR_MONTH:
case INTERVAL_MONTH:
- return type.isNullable() ? Integer.class : int.class;
+ return Period.class;
case BIGINT:
+ return type.isNullable() ? Long.class : long.class;
case INTERVAL_DAY:
case INTERVAL_DAY_HOUR:
case INTERVAL_DAY_MINUTE:
@@ -162,7 +177,7 @@ public class IgniteTypeFactory extends JavaTypeFactoryImpl {
case INTERVAL_MINUTE:
case INTERVAL_MINUTE_SECOND:
case INTERVAL_SECOND:
- return type.isNullable() ? Long.class : long.class;
+ return Duration.class;
case SMALLINT:
return type.isNullable() ? Short.class : short.class;
case TINYINT:
@@ -222,6 +237,28 @@ public class IgniteTypeFactory extends JavaTypeFactoryImpl {
return Charset.defaultCharset();
}
+ /** {@inheritDoc} */
+ @Override public RelDataType toSql(RelDataType type) {
+ if (type instanceof JavaType) {
+ Class<?> clazz = ((JavaType)type).getJavaClass();
+
+ if (clazz == Duration.class)
+ return createTypeWithNullability(createSqlIntervalType(INTERVAL_QUALIFIER_DAY_TIME), true);
+ else if (clazz == Period.class)
+ return createTypeWithNullability(createSqlIntervalType(INTERVAL_QUALIFIER_YEAR_MONTH), true);
+ }
+
+ return super.toSql(type);
+ }
+
+ /** {@inheritDoc} */
+ @Override public RelDataType createType(Type type) {
+ if (type == Duration.class || type == Period.class)
+ return createJavaType((Class<?>)type);
+
+ return super.createType(type);
+ }
+
/** */
private boolean allEquals(List<RelDataType> types) {
assert types.size() > 1;
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/TypeUtils.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/TypeUtils.java
index 036ddb1..8c7c33e 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/TypeUtils.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/TypeUtils.java
@@ -19,12 +19,14 @@ package org.apache.ignite.internal.processors.query.calcite.util;
import java.lang.reflect.Type;
import java.sql.Timestamp;
+import java.time.Duration;
+import java.time.Period;
import java.util.Arrays;
-import java.util.EnumSet;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.TimeZone;
+import java.util.concurrent.TimeUnit;
import java.util.function.Function;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
@@ -56,18 +58,13 @@ import static org.apache.ignite.internal.processors.query.calcite.util.Commons.t
/** */
public class TypeUtils {
/** */
- private static final EnumSet<SqlTypeName> CONVERTABLE_SQL_TYPES = EnumSet.of(
- SqlTypeName.DATE,
- SqlTypeName.TIME,
- SqlTypeName.TIMESTAMP
- );
-
- /** */
private static final Set<Type> CONVERTABLE_TYPES = ImmutableSet.of(
java.util.Date.class,
java.sql.Date.class,
java.sql.Time.class,
- java.sql.Timestamp.class
+ java.sql.Timestamp.class,
+ Duration.class,
+ Period.class
);
/** */
@@ -235,10 +232,11 @@ public class TypeUtils {
/** */
private static Function<Object, Object> fieldConverter(ExecutionContext<?> ectx, RelDataType fieldType) {
- if (CONVERTABLE_SQL_TYPES.contains(fieldType.getSqlTypeName())) {
- Type storageType = ectx.getTypeFactory().getJavaClass(fieldType);
+ Type storageType = ectx.getTypeFactory().getJavaClass(fieldType);
+
+ if (isConvertableType(storageType))
return v -> fromInternal(ectx, v, storageType);
- }
+
return Function.identity();
}
@@ -249,13 +247,14 @@ public class TypeUtils {
/** */
public static boolean isConvertableType(RelDataType type) {
- return CONVERTABLE_SQL_TYPES.contains(type.getSqlTypeName());
+ return type instanceof RelDataTypeFactoryImpl.JavaType
+ && isConvertableType(((RelDataTypeFactoryImpl.JavaType)type).getJavaClass());
}
/** */
private static boolean hasConvertableFields(RelDataType resultType) {
return RelOptUtil.getFieldTypeList(resultType).stream()
- .anyMatch(t -> CONVERTABLE_SQL_TYPES.contains(t.getSqlTypeName()));
+ .anyMatch(TypeUtils::isConvertableType);
}
/** */
@@ -275,6 +274,12 @@ public class TypeUtils {
return SqlFunctions.toLong((java.util.Date)val, DataContext.Variable.TIME_ZONE.get(ectx));
else if (storageType == java.util.Date.class)
return SqlFunctions.toLong((java.util.Date)val, DataContext.Variable.TIME_ZONE.get(ectx));
+ else if (storageType == Duration.class) {
+ return TimeUnit.SECONDS.toMillis(((Duration)val).getSeconds())
+ + TimeUnit.NANOSECONDS.toMillis(((Duration)val).getNano());
+ }
+ else if (storageType == Period.class)
+ return (int)((Period)val).toTotalMonths();
else
return val;
}
@@ -291,6 +296,10 @@ public class TypeUtils {
return new Timestamp(fromLocalTs(ectx, (Long)val));
else if (storageType == java.util.Date.class && val instanceof Long)
return new java.util.Date(fromLocalTs(ectx, (Long)val));
+ else if (storageType == Duration.class && val instanceof Long)
+ return Duration.ofMillis((Long)val);
+ else if (storageType == Period.class && val instanceof Integer)
+ return Period.of((Integer)val / 12, (Integer)val % 12, 0);
else
return val;
}
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessorTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessorTest.java
index ef6217a..aacefa0 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessorTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessorTest.java
@@ -17,6 +17,8 @@
package org.apache.ignite.internal.processors.query.calcite;
+import java.time.Duration;
+import java.time.Period;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
@@ -1019,7 +1021,8 @@ public class CalciteQueryProcessorTest extends GridCommonAbstractTest {
QueryEngine engineSrv = Commons.lookupComponent(grid(1).context(), QueryEngine.class);
FieldsQueryCursor<List<?>> cur = engineSrv.query(null, "PUBLIC",
- "select id, id::tinyint as tid, id::smallint as sid, id::varchar as vid from test_tbl").get(0);
+ "select id, id::tinyint as tid, id::smallint as sid, id::varchar as vid, id::interval hour, " +
+ "id::interval year from test_tbl").get(0);
assertThat(cur, CoreMatchers.instanceOf(QueryCursorEx.class));
@@ -1029,6 +1032,8 @@ public class CalciteQueryProcessorTest extends GridCommonAbstractTest {
assertThat(qCur.fieldsMeta().get(1).fieldTypeName(), equalTo(Byte.class.getName()));
assertThat(qCur.fieldsMeta().get(2).fieldTypeName(), equalTo(Short.class.getName()));
assertThat(qCur.fieldsMeta().get(3).fieldTypeName(), equalTo(String.class.getName()));
+ assertThat(qCur.fieldsMeta().get(4).fieldTypeName(), equalTo(Duration.class.getName()));
+ assertThat(qCur.fieldsMeta().get(5).fieldTypeName(), equalTo(Period.class.getName()));
}
/** Quantified predicates test. */
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AbstractBasicIntegrationTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AbstractBasicIntegrationTest.java
index 03394e6..588602e 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AbstractBasicIntegrationTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AbstractBasicIntegrationTest.java
@@ -17,9 +17,12 @@
package org.apache.ignite.internal.processors.query.calcite.integration;
+import java.util.List;
import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.cache.QueryEntity;
+import org.apache.ignite.cache.query.FieldsQueryCursor;
+import org.apache.ignite.cache.query.QueryCursor;
import org.apache.ignite.cache.query.annotations.QuerySqlField;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.internal.IgniteEx;
@@ -32,6 +35,8 @@ import org.apache.ignite.internal.util.typedef.G;
import org.apache.ignite.testframework.junits.WithSystemProperty;
import org.apache.ignite.testframework.junits.common.GridCommonAbstractTest;
+import static org.apache.ignite.testframework.GridTestUtils.assertThrowsAnyCause;
+
/**
*
*/
@@ -84,6 +89,28 @@ public class AbstractBasicIntegrationTest extends GridCommonAbstractTest {
}
/** */
+ protected List<List<?>> executeSql(String sql, Object... args) {
+ CalciteQueryProcessor qryProc = Commons.lookupComponent(client.context(), CalciteQueryProcessor.class);
+
+ List<FieldsQueryCursor<List<?>>> cur = qryProc.query(null, "PUBLIC", sql, args);
+
+ try (QueryCursor<List<?>> srvCursor = cur.get(0)) {
+ return srvCursor.getAll();
+ }
+ }
+
+ /**
+ * Asserts that executeSql throws an exception.
+ *
+ * @param sql Query.
+ * @param cls Exception class.
+ * @param msg Error message.
+ */
+ protected void assertThrows(String sql, Class<? extends Exception> cls, String msg) {
+ assertThrowsAnyCause(log, () -> executeSql(sql), cls, msg);
+ }
+
+ /** */
protected IgniteCache<Integer, Employer> createAndPopulateTable() {
IgniteCache<Integer, Employer> person = client.getOrCreateCache(new CacheConfiguration<Integer, Employer>()
.setName("person")
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AggregatesIntegrationTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AggregatesIntegrationTest.java
index 8fd187a..26809e3 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AggregatesIntegrationTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AggregatesIntegrationTest.java
@@ -20,9 +20,6 @@ package org.apache.ignite.internal.processors.query.calcite.integration;
import java.util.List;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.IgniteCheckedException;
-import org.apache.ignite.cache.query.FieldsQueryCursor;
-import org.apache.ignite.internal.processors.query.QueryEngine;
-import org.apache.ignite.internal.processors.query.calcite.util.Commons;
import org.apache.ignite.testframework.GridTestUtils;
import org.junit.Test;
@@ -143,7 +140,7 @@ public class AggregatesIntegrationTest extends AbstractBasicIntegrationTest {
public void testAnyValAggr() {
createAndPopulateTable();
- List<List<?>> res = execute("select any_value(name) from person");
+ List<List<?>> res = executeSql("select any_value(name) from person");
assertEquals(1, res.size());
@@ -152,7 +149,7 @@ public class AggregatesIntegrationTest extends AbstractBasicIntegrationTest {
assertTrue("Unexpected value: " + val, "Igor".equals(val) || "Roma".equals(val) || "Ilya".equals(val));
// Test with grouping.
- res = execute("select any_value(name), salary from person group by salary order by salary");
+ res = executeSql("select any_value(name), salary from person group by salary order by salary");
assertEquals(2, res.size());
@@ -164,12 +161,4 @@ public class AggregatesIntegrationTest extends AbstractBasicIntegrationTest {
assertEquals("Ilya", val);
}
-
- /** */
- private List<List<?>> execute(String sql) {
- List<FieldsQueryCursor<List<?>>> cursors = Commons.lookupComponent(client.context(), QueryEngine.class)
- .query(null, "PUBLIC", sql);
-
- return cursors.get(0).getAll();
- }
}
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/IntervalTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/IntervalTest.java
new file mode 100644
index 0000000..f3e94a9
--- /dev/null
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/IntervalTest.java
@@ -0,0 +1,379 @@
+/*
+ * 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.ignite.internal.processors.query.calcite.integration;
+
+import java.sql.Date;
+import java.sql.Time;
+import java.sql.Timestamp;
+import java.time.Duration;
+import java.time.Period;
+import org.apache.ignite.internal.processors.query.IgniteSQLException;
+import org.junit.Test;
+
+/**
+ * Test SQL INTERVAL data types.
+ */
+public class IntervalTest extends AbstractBasicIntegrationTest {
+ /**
+ * Test returned result for interval data types.
+ */
+ @Test
+ public void testIntervalResult() {
+ assertEquals(Duration.ofSeconds(1), eval("INTERVAL 1 SECONDS"));
+ assertEquals(Duration.ofSeconds(-1), eval("INTERVAL -1 SECONDS"));
+ assertThrows("SELECT INTERVAL '123' SECONDS", IgniteSQLException.class, "exceeds precision");
+ assertEquals(Duration.ofSeconds(123), eval("INTERVAL 123 SECONDS"));
+ assertEquals(Duration.ofSeconds(123), eval("INTERVAL '123' SECONDS(3)"));
+ assertEquals(Duration.ofMinutes(2), eval("INTERVAL 2 MINUTES"));
+ assertEquals(Duration.ofHours(3), eval("INTERVAL 3 HOURS"));
+ assertEquals(Duration.ofDays(4), eval("INTERVAL 4 DAYS"));
+ assertEquals(Period.ofMonths(5), eval("INTERVAL 5 MONTHS"));
+ assertEquals(Period.ofMonths(-5), eval("INTERVAL -5 MONTHS"));
+ assertEquals(Period.ofYears(6), eval("INTERVAL 6 YEARS"));
+ assertEquals(Period.of(1, 2, 0), eval("INTERVAL '1-2' YEAR TO MONTH"));
+ assertEquals(Duration.ofHours(25), eval("INTERVAL '1 1' DAY TO HOUR"));
+ assertEquals(Duration.ofMinutes(62), eval("INTERVAL '1:2' HOUR TO MINUTE"));
+ assertEquals(Duration.ofSeconds(63), eval("INTERVAL '1:3' MINUTE TO SECOND"));
+ assertEquals(Duration.ofSeconds(3723), eval("INTERVAL '1:2:3' HOUR TO SECOND"));
+ assertEquals(Duration.ofMillis(3723456), eval("INTERVAL '0 1:2:3.456' DAY TO SECOND"));
+ }
+
+ /**
+ * Test cast interval types to integer and integer to interval.
+ */
+ @Test
+ public void testIntervalIntCast() {
+ assertNull(eval("CAST(NULL::INTERVAL SECONDS AS INT)"));
+ assertNull(eval("CAST(NULL::INTERVAL MONTHS AS INT)"));
+ assertEquals(1, eval("CAST(INTERVAL 1 SECONDS AS INT)"));
+ assertEquals(2, eval("CAST(INTERVAL 2 MINUTES AS INT)"));
+ assertEquals(3, eval("CAST(INTERVAL 3 HOURS AS INT)"));
+ assertEquals(4, eval("CAST(INTERVAL 4 DAYS AS INT)"));
+ assertEquals(-4, eval("CAST(INTERVAL -4 DAYS AS INT)"));
+ assertEquals(5, eval("CAST(INTERVAL 5 MONTHS AS INT)"));
+ assertEquals(6, eval("CAST(INTERVAL 6 YEARS AS INT)"));
+ assertEquals(-6, eval("CAST(INTERVAL -6 YEARS AS INT)"));
+
+ assertNull(eval("CAST(NULL::INT AS INTERVAL SECONDS)"));
+ assertNull(eval("CAST(NULL::INT AS INTERVAL MONTHS)"));
+ assertEquals(Duration.ofSeconds(1), eval("CAST(1 AS INTERVAL SECONDS)"));
+ assertEquals(Duration.ofMinutes(2), eval("CAST(2 AS INTERVAL MINUTES)"));
+ assertEquals(Duration.ofHours(3), eval("CAST(3 AS INTERVAL HOURS)"));
+ assertEquals(Duration.ofDays(4), eval("CAST(4 AS INTERVAL DAYS)"));
+ assertEquals(Period.ofMonths(5), eval("CAST(5 AS INTERVAL MONTHS)"));
+ assertEquals(Period.ofYears(6), eval("CAST(6 AS INTERVAL YEARS)"));
+
+ // Compound interval types cannot be cast.
+ assertThrows("SELECT CAST(INTERVAL '1-2' YEAR TO MONTH AS INT)", IgniteSQLException.class, "cannot convert");
+ assertThrows("SELECT CAST(INTERVAL '1 2' DAY TO HOUR AS INT)", IgniteSQLException.class, "cannot convert");
+
+ assertThrows("SELECT CAST(1 AS INTERVAL YEAR TO MONTH)", IgniteSQLException.class, "cannot convert");
+ assertThrows("SELECT CAST(1 AS INTERVAL DAY TO HOUR)", IgniteSQLException.class, "cannot convert");
+ }
+
+ /**
+ * Test cast interval types to string and string to interval.
+ */
+ @Test
+ public void testIntervalStringCast() {
+ assertNull(eval("CAST(NULL::INTERVAL SECONDS AS VARCHAR)"));
+ assertNull(eval("CAST(NULL::INTERVAL MONTHS AS VARCHAR)"));
+ assertEquals("+1.234", eval("CAST(INTERVAL '1.234' SECONDS (1,3) AS VARCHAR)"));
+ assertEquals("+1.000000", eval("CAST(INTERVAL 1 SECONDS AS VARCHAR)"));
+ assertEquals("+2", eval("CAST(INTERVAL 2 MINUTES AS VARCHAR)"));
+ assertEquals("+3", eval("CAST(INTERVAL 3 HOURS AS VARCHAR)"));
+ assertEquals("+4", eval("CAST(INTERVAL 4 DAYS AS VARCHAR)"));
+ assertEquals("+5", eval("CAST(INTERVAL 5 MONTHS AS VARCHAR)"));
+ assertEquals("+6", eval("CAST(INTERVAL 6 YEARS AS VARCHAR)"));
+ assertEquals("+1-02", eval("CAST(INTERVAL '1-2' YEAR TO MONTH AS VARCHAR)"));
+ assertEquals("+1 02", eval("CAST(INTERVAL '1 2' DAY TO HOUR AS VARCHAR)"));
+ assertEquals("-1 02:03:04.000000", eval("CAST(INTERVAL '-1 2:3:4' DAY TO SECOND AS VARCHAR)"));
+
+ assertNull(eval("CAST(NULL::VARCHAR AS INTERVAL SECONDS)"));
+ assertNull(eval("CAST(NULL::VARCHAR AS INTERVAL MONTHS)"));
+ assertEquals(Duration.ofSeconds(1), eval("CAST('1' AS INTERVAL SECONDS)"));
+ assertEquals(Duration.ofMinutes(2), eval("CAST('2' AS INTERVAL MINUTES)"));
+ assertEquals(Duration.ofHours(3), eval("CAST('3' AS INTERVAL HOURS)"));
+ assertEquals(Duration.ofDays(4), eval("CAST('4' AS INTERVAL DAYS)"));
+ assertEquals(Duration.ofHours(26), eval("CAST('1 2' AS INTERVAL DAY TO HOUR)"));
+ assertEquals(Duration.ofMinutes(62), eval("CAST('1:2' AS INTERVAL HOUR TO MINUTE)"));
+ assertEquals(Duration.ofMillis(3723456), eval("CAST('0 1:2:3.456' AS INTERVAL DAY TO SECOND)"));
+ assertEquals(Duration.ofMillis(-3723456), eval("CAST('-0 1:2:3.456' AS INTERVAL DAY TO SECOND)"));
+ assertEquals(Period.ofMonths(5), eval("CAST('5' AS INTERVAL MONTHS)"));
+ assertEquals(Period.ofYears(6), eval("CAST('6' AS INTERVAL YEARS)"));
+ assertEquals(Period.of(1, 2, 0), eval("CAST('1-2' AS INTERVAL YEAR TO MONTH)"));
+ }
+
+ /**
+ * Test cast between interval types.
+ */
+ @Test
+ public void testIntervalToIntervalCast() {
+ assertNull(eval("CAST(NULL::INTERVAL MINUTE AS INTERVAL SECONDS)"));
+ assertNull(eval("CAST(NULL::INTERVAL YEAR AS INTERVAL MONTHS)"));
+ assertEquals(Duration.ofMinutes(1), eval("CAST(INTERVAL 60 SECONDS AS INTERVAL MINUTE)"));
+ assertEquals(Duration.ofHours(1), eval("CAST(INTERVAL 60 MINUTES AS INTERVAL HOUR)"));
+ assertEquals(Duration.ofDays(1), eval("CAST(INTERVAL 24 HOURS AS INTERVAL DAY)"));
+ assertEquals(Period.ofYears(1), eval("CAST(INTERVAL 1 YEAR AS INTERVAL MONTHS)"));
+ assertEquals(Period.ofYears(1), eval("CAST(INTERVAL 12 MONTHS AS INTERVAL YEARS)"));
+
+ // Cannot convert between month-year and day-time interval types.
+ assertThrows("SELECT CAST(INTERVAL 1 MONTHS AS INTERVAL DAYS)", IgniteSQLException.class, "cannot convert");
+ assertThrows("SELECT CAST(INTERVAL 1 DAYS AS INTERVAL MONTHS)", IgniteSQLException.class, "cannot convert");
+ }
+
+ /**
+ * Test DML statements with interval data type.
+ */
+ @Test
+ public void testDml() {
+ executeSql("CREATE TABLE test(ym INTERVAL YEAR, dt INTERVAL DAYS)");
+ executeSql("INSERT INTO test(ym, dt) VALUES (INTERVAL 1 MONTH, INTERVAL 2 DAYS)");
+ executeSql("INSERT INTO test(ym, dt) VALUES (INTERVAL 3 YEARS, INTERVAL 4 HOURS)");
+ executeSql("INSERT INTO test(ym, dt) VALUES (INTERVAL '4-5' YEARS TO MONTHS, INTERVAL '6:7' HOURS TO MINUTES)");
+ executeSql("INSERT INTO test(ym, dt) VALUES (NULL, NULL)");
+ assertThrows("INSERT INTO test(ym, dt) VALUES (INTERVAL 1 DAYS, INTERVAL 1 HOURS)",
+ IgniteSQLException.class, "Cannot assign");
+ assertThrows("INSERT INTO test(ym, dt) VALUES (INTERVAL 1 YEARS, INTERVAL 1 MONTHS)",
+ IgniteSQLException.class, "Cannot assign");
+
+ assertQuery("SELECT * FROM test")
+ .returns(Period.ofMonths(1), Duration.ofDays(2))
+ .returns(Period.ofYears(3), Duration.ofHours(4))
+ .returns(Period.of(4, 5, 0), Duration.ofMinutes(367))
+ .returns(null, null)
+ .check();
+
+ assertThrows("SELECT * FROM test WHERE ym = INTERVAL 6 DAYS", IgniteSQLException.class, "Cannot apply");
+ assertThrows("SELECT * FROM test WHERE dt = INTERVAL 6 YEARS", IgniteSQLException.class, "Cannot apply");
+
+ executeSql("UPDATE test SET dt = INTERVAL 3 DAYS WHERE ym = INTERVAL 1 MONTH");
+ executeSql("UPDATE test SET ym = INTERVAL 5 YEARS WHERE dt = INTERVAL 4 HOURS");
+ executeSql("UPDATE test SET ym = INTERVAL '6-7' YEARS TO MONTHS, dt = INTERVAL '8 9' DAYS TO HOURS " +
+ "WHERE ym = INTERVAL '4-5' YEARS TO MONTHS AND dt = INTERVAL '6:7' HOURS TO MINUTES");
+
+ assertThrows("UPDATE test SET dt = INTERVAL 5 YEARS WHERE ym = INTERVAL 1 MONTH", IgniteSQLException.class,
+ "Cannot assign");
+
+ assertThrows("UPDATE test SET ym = INTERVAL 8 YEARS WHERE dt = INTERVAL 1 MONTH", IgniteSQLException.class,
+ "Cannot apply");
+
+ assertQuery("SELECT * FROM test")
+ .returns(Period.ofMonths(1), Duration.ofDays(3))
+ .returns(Period.ofYears(5), Duration.ofHours(4))
+ .returns(Period.of(6, 7, 0), Duration.ofHours(201))
+ .returns(null, null)
+ .check();
+
+ assertThrows("DELETE FROM test WHERE ym = INTERVAL 6 DAYS", IgniteSQLException.class, "Cannot apply");
+ assertThrows("DELETE FROM test WHERE dt = INTERVAL 6 YEARS", IgniteSQLException.class, "Cannot apply");
+
+ executeSql("DELETE FROM test WHERE ym = INTERVAL 1 MONTH");
+ executeSql("DELETE FROM test WHERE dt = INTERVAL 4 HOURS");
+ executeSql("DELETE FROM test WHERE ym = INTERVAL '6-7' YEARS TO MONTHS AND dt = INTERVAL '8 9' DAYS TO HOURS");
+ executeSql("DELETE FROM test WHERE ym IS NULL AND dt IS NULL");
+
+ assertEquals(0, executeSql("SELECT * FROM test").size());
+
+ executeSql("ALTER TABLE test ADD (ym2 INTERVAL MONTH, dt2 INTERVAL HOURS)");
+
+ executeSql("INSERT INTO test(ym, ym2, dt, dt2) VALUES (INTERVAL 1 YEAR, INTERVAL 2 YEARS, " +
+ "INTERVAL 1 SECOND, INTERVAL 2 MINUTES)");
+
+ assertQuery("SELECT ym, ym2, dt, dt2 FROM test")
+ .returns(Period.ofYears(1), Period.ofYears(2), Duration.ofSeconds(1), Duration.ofMinutes(2))
+ .check();
+ }
+
+ /**
+ * Test interval arithmetic.
+ */
+ @Test
+ public void testIntervalArithmetic() {
+ // Date +/- interval.
+ assertEquals(Date.valueOf("2021-01-02"), eval("DATE '2021-01-01' + INTERVAL 1 DAY"));
+ assertEquals(Date.valueOf("2020-12-31"), eval("DATE '2021-01-01' - INTERVAL 1 DAY"));
+ assertEquals(Date.valueOf("2020-12-31"), eval("DATE '2021-01-01' + INTERVAL -1 DAY"));
+ assertEquals(Date.valueOf("2021-02-01"), eval("DATE '2021-01-01' + INTERVAL 1 MONTH"));
+ assertEquals(Date.valueOf("2022-01-01"), eval("DATE '2021-01-01' + INTERVAL 1 YEAR"));
+ assertEquals(Date.valueOf("2022-02-01"), eval("DATE '2021-01-01' + INTERVAL '1-1' YEAR TO MONTH"));
+
+ // Timestamp +/- interval.
+ assertEquals(Timestamp.valueOf("2021-01-01 00:00:01"),
+ eval("TIMESTAMP '2021-01-01 00:00:00' + INTERVAL 1 SECOND"));
+ assertEquals(Timestamp.valueOf("2021-01-01 00:00:01.123"),
+ eval("TIMESTAMP '2021-01-01 00:00:00.123' + INTERVAL 1 SECOND"));
+ assertEquals(Timestamp.valueOf("2021-01-01 00:00:01.123"),
+ eval("TIMESTAMP '2021-01-01 00:00:00' + INTERVAL '1.123' SECOND"));
+ assertEquals(Timestamp.valueOf("2021-01-01 00:00:01.246"),
+ eval("TIMESTAMP '2021-01-01 00:00:00.123' + INTERVAL '1.123' SECOND"));
+ assertEquals(Timestamp.valueOf("2020-12-31 23:59:59"),
+ eval("TIMESTAMP '2021-01-01 00:00:00' - INTERVAL 1 SECOND"));
+ assertEquals(Timestamp.valueOf("2020-12-31 23:59:59"),
+ eval("TIMESTAMP '2021-01-01 00:00:00' + INTERVAL -1 SECOND"));
+ assertEquals(Timestamp.valueOf("2021-01-01 00:01:00"),
+ eval("TIMESTAMP '2021-01-01 00:00:00' + INTERVAL 1 MINUTE"));
+ assertEquals(Timestamp.valueOf("2021-01-01 01:00:00"),
+ eval("TIMESTAMP '2021-01-01 00:00:00' + INTERVAL 1 HOUR"));
+ assertEquals(Timestamp.valueOf("2021-01-02 00:00:00"),
+ eval("TIMESTAMP '2021-01-01 00:00:00' + INTERVAL 1 DAY"));
+ assertEquals(Timestamp.valueOf("2021-02-01 00:00:00"),
+ eval("TIMESTAMP '2021-01-01 00:00:00' + INTERVAL 1 MONTH"));
+ assertEquals(Timestamp.valueOf("2022-01-01 00:00:00"),
+ eval("TIMESTAMP '2021-01-01 00:00:00' + INTERVAL 1 YEAR"));
+ assertEquals(Timestamp.valueOf("2021-01-02 01:01:01.123"),
+ eval("TIMESTAMP '2021-01-01 00:00:00' + INTERVAL '1 1:1:1.123' DAY TO SECOND"));
+ assertEquals(Timestamp.valueOf("2022-02-01 01:01:01.123"),
+ eval("TIMESTAMP '2021-01-01 01:01:01.123' + INTERVAL '1-1' YEAR TO MONTH"));
+
+ // Time +/- interval.
+ assertEquals(Time.valueOf("00:00:01"), eval("TIME '00:00:00' + INTERVAL 1 SECOND"));
+ assertEquals(Time.valueOf("00:01:00"), eval("TIME '00:00:00' + INTERVAL 1 MINUTE"));
+ assertEquals(Time.valueOf("01:00:00"), eval("TIME '00:00:00' + INTERVAL 1 HOUR"));
+
+ // Date - date as interval.
+ assertEquals(Duration.ofDays(1), eval("(DATE '2021-01-02' - DATE '2021-01-01') DAYS"));
+ assertEquals(Duration.ofDays(-1), eval("(DATE '2021-01-01' - DATE '2021-01-02') DAYS"));
+ assertEquals(Duration.ofDays(1), eval("(DATE '2021-01-02' - DATE '2021-01-01') HOURS"));
+ assertEquals(Period.ofYears(1), eval("(DATE '2022-01-01' - DATE '2021-01-01') YEARS"));
+ assertEquals(Period.ofMonths(1), eval("(DATE '2021-02-01' - DATE '2021-01-01') MONTHS"));
+ assertEquals(Period.ofMonths(-1), eval("(DATE '2021-01-01' - DATE '2021-02-01') MONTHS"));
+ assertEquals(Period.ofMonths(0), eval("(DATE '2021-01-20' - DATE '2021-01-01') MONTHS"));
+
+ // Timestamp - timestamp as interval.
+ assertEquals(Duration.ofDays(1),
+ eval("(TIMESTAMP '2021-01-02 00:00:00' - TIMESTAMP '2021-01-01 00:00:00') DAYS"));
+ assertEquals(Duration.ofDays(-1),
+ eval("(TIMESTAMP '2021-01-01 00:00:00' - TIMESTAMP '2021-01-02 00:00:00') DAYS"));
+ assertEquals(Duration.ofHours(1),
+ eval("(TIMESTAMP '2021-01-01 01:00:00' - TIMESTAMP '2021-01-01 00:00:00') HOURS"));
+ assertEquals(Duration.ofMinutes(1),
+ eval("(TIMESTAMP '2021-01-01 00:01:00' - TIMESTAMP '2021-01-01 00:00:00') MINUTES"));
+ assertEquals(Duration.ofSeconds(1),
+ eval("(TIMESTAMP '2021-01-01 00:00:01' - TIMESTAMP '2021-01-01 00:00:00') SECONDS"));
+ assertEquals(Duration.ofMillis(123),
+ eval("(TIMESTAMP '2021-01-01 00:00:00.123' - TIMESTAMP '2021-01-01 00:00:00') SECONDS"));
+ assertEquals(Period.ofYears(1),
+ eval("(TIMESTAMP '2022-01-01 00:00:00' - TIMESTAMP '2021-01-01 00:00:00') YEARS"));
+ assertEquals(Period.ofMonths(1),
+ eval("(TIMESTAMP '2021-02-01 00:00:00' - TIMESTAMP '2021-01-01 00:00:00') MONTHS"));
+ assertEquals(Period.ofMonths(-1),
+ eval("(TIMESTAMP '2021-01-01 00:00:00' - TIMESTAMP '2021-02-01 00:00:00') MONTHS"));
+ assertEquals(Period.ofMonths(0),
+ eval("(TIMESTAMP '2021-01-20 00:00:00' - TIMESTAMP '2021-01-01 00:00:00') MONTHS"));
+
+ // Time - time as interval.
+ assertEquals(Duration.ofHours(1), eval("(TIME '02:00:00' - TIME '01:00:00') HOURS"));
+ assertEquals(Duration.ofMinutes(1), eval("(TIME '00:02:00' - TIME '00:01:00') HOURS"));
+ assertEquals(Duration.ofMinutes(1), eval("(TIME '00:02:00' - TIME '00:01:00') MINUTES"));
+ assertEquals(Duration.ofSeconds(1), eval("(TIME '00:00:02' - TIME '00:00:01') SECONDS"));
+ assertEquals(Duration.ofMillis(123), eval("(TIME '00:00:01.123' - TIME '00:00:01') SECONDS"));
+
+ // Interval +/- interval.
+ assertEquals(Duration.ofSeconds(2), eval("INTERVAL 1 SECONDS + INTERVAL 1 SECONDS"));
+ assertEquals(Duration.ofSeconds(1), eval("INTERVAL 2 SECONDS - INTERVAL 1 SECONDS"));
+ assertEquals(Duration.ofSeconds(61), eval("INTERVAL 1 MINUTE + INTERVAL 1 SECONDS"));
+ assertEquals(Duration.ofSeconds(59), eval("INTERVAL 1 MINUTE - INTERVAL 1 SECONDS"));
+ assertEquals(Duration.ofSeconds(59), eval("INTERVAL 1 MINUTE + INTERVAL -1 SECONDS"));
+ assertEquals(Duration.ofSeconds(3723), eval("INTERVAL 1 HOUR + INTERVAL '2:3' MINUTE TO SECONDS"));
+ assertEquals(Duration.ofSeconds(3477), eval("INTERVAL 1 HOUR - INTERVAL '2:3' MINUTE TO SECONDS"));
+ assertEquals(Duration.ofHours(25), eval("INTERVAL 1 DAY + INTERVAL 1 HOUR"));
+ assertEquals(Period.ofMonths(2), eval("INTERVAL 1 MONTH + INTERVAL 1 MONTH"));
+ assertEquals(Period.ofYears(2), eval("INTERVAL 1 YEAR + INTERVAL 1 YEAR"));
+ assertEquals(Period.of(1, 1, 0), eval("INTERVAL 1 YEAR + INTERVAL 1 MONTH"));
+ assertEquals(Period.ofMonths(11), eval("INTERVAL 1 YEAR - INTERVAL 1 MONTH"));
+ assertEquals(Period.ofMonths(11), eval("INTERVAL 1 YEAR + INTERVAL -1 MONTH"));
+ assertThrows("SELECT INTERVAL 1 DAY + INTERVAL 1 MONTH", IgniteSQLException.class, "Cannot apply");
+
+ // Interval * scalar.
+ assertEquals(Duration.ofSeconds(2), eval("INTERVAL 1 SECONDS * 2"));
+ assertEquals(Duration.ofSeconds(-2), eval("INTERVAL -1 SECONDS * 2"));
+ assertEquals(Duration.ofMinutes(4), eval("INTERVAL 2 MINUTES * 2"));
+ assertEquals(Duration.ofHours(6), eval("INTERVAL 3 HOURS * 2"));
+ assertEquals(Duration.ofDays(8), eval("INTERVAL 4 DAYS * 2"));
+ assertEquals(Period.ofMonths(10), eval("INTERVAL 5 MONTHS * 2"));
+ assertEquals(Period.ofMonths(-10), eval("INTERVAL -5 MONTHS * 2"));
+ assertEquals(Period.ofYears(12), eval("INTERVAL 6 YEARS * 2"));
+ assertEquals(Period.of(2, 4, 0), eval("INTERVAL '1-2' YEAR TO MONTH * 2"));
+ assertEquals(Duration.ofHours(50), eval("INTERVAL '1 1' DAY TO HOUR * 2"));
+ assertEquals(Duration.ofMinutes(124), eval("INTERVAL '1:2' HOUR TO MINUTE * 2"));
+ assertEquals(Duration.ofSeconds(126), eval("INTERVAL '1:3' MINUTE TO SECOND * 2"));
+ assertEquals(Duration.ofSeconds(7446), eval("INTERVAL '1:2:3' HOUR TO SECOND * 2"));
+ assertEquals(Duration.ofMillis(7446912), eval("INTERVAL '0 1:2:3.456' DAY TO SECOND * 2"));
+
+ // Interval / scalar
+ assertEquals(Duration.ofSeconds(1), eval("INTERVAL 2 SECONDS / 2"));
+ assertEquals(Duration.ofSeconds(-1), eval("INTERVAL -2 SECONDS / 2"));
+ assertEquals(Duration.ofSeconds(30), eval("INTERVAL 1 MINUTES / 2"));
+ assertEquals(Duration.ofMinutes(90), eval("INTERVAL 3 HOURS / 2"));
+ assertEquals(Duration.ofDays(2), eval("INTERVAL 4 DAYS / 2"));
+ assertEquals(Period.ofMonths(2), eval("INTERVAL 5 MONTHS / 2"));
+ assertEquals(Period.ofMonths(-2), eval("INTERVAL -5 MONTHS / 2"));
+ assertEquals(Period.of(3, 6, 0), eval("INTERVAL 7 YEARS / 2"));
+ assertEquals(Period.ofMonths(7), eval("INTERVAL '1-2' YEAR TO MONTH / 2"));
+ assertEquals(Duration.ofHours(13), eval("INTERVAL '1 2' DAY TO HOUR / 2"));
+ assertEquals(Duration.ofMinutes(31), eval("INTERVAL '1:2' HOUR TO MINUTE / 2"));
+ assertEquals(Duration.ofSeconds(31), eval("INTERVAL '1:2' MINUTE TO SECOND / 2"));
+ assertEquals(Duration.ofSeconds(1862), eval("INTERVAL '1:2:4' HOUR TO SECOND / 2"));
+ assertEquals(Duration.ofMillis(1862228), eval("INTERVAL '0 1:2:4.456' DAY TO SECOND / 2"));
+ }
+
+ /**
+ * Test caching of expressions by digest.
+ */
+ @Test
+ public void testScalarCache() {
+ // These expressions differs only in return data type, so digest should include also data type correctly
+ // compile scalar for second expression (should not get compiled scalar from the cache).
+ assertEquals(Duration.ofDays(1), eval("(DATE '2021-01-02' - DATE '2021-01-01') DAYS"));
+ assertEquals(Period.ofMonths(0), eval("(DATE '2021-01-02' - DATE '2021-01-01') MONTHS"));
+ }
+
+ /**
+ * Test EXTRACT function with interval data types.
+ */
+ @Test
+ public void testExtract() {
+ assertEquals(2L, eval("EXTRACT(MONTH FROM INTERVAL 14 MONTHS)"));
+ assertEquals(0L, eval("EXTRACT(MONTH FROM INTERVAL 1 YEAR)"));
+ assertEquals(2L, eval("EXTRACT(MONTH FROM INTERVAL '1-2' YEAR TO MONTH)"));
+ assertEquals(1L, eval("EXTRACT(YEAR FROM INTERVAL '1-2' YEAR TO MONTH)"));
+ assertEquals(-1L, eval("EXTRACT(MONTH FROM INTERVAL -1 MONTHS)"));
+ assertEquals(-1L, eval("EXTRACT(YEAR FROM INTERVAL -14 MONTHS)"));
+ assertEquals(-2L, eval("EXTRACT(MONTH FROM INTERVAL -14 MONTHS)"));
+ assertEquals(-20L, eval("EXTRACT(MINUTE FROM INTERVAL '-10:20' HOURS TO MINUTES)"));
+ assertEquals(1L, eval("EXTRACT(DAY FROM INTERVAL '1 2:3:4.567' DAY TO SECOND)"));
+ assertEquals(2L, eval("EXTRACT(HOUR FROM INTERVAL '1 2:3:4.567' DAY TO SECOND)"));
+ assertEquals(3L, eval("EXTRACT(MINUTE FROM INTERVAL '1 2:3:4.567' DAY TO SECOND)"));
+ assertEquals(4L, eval("EXTRACT(SECOND FROM INTERVAL '1 2:3:4.567' DAY TO SECOND)"));
+ assertEquals(4567L, eval("EXTRACT(MILLISECOND FROM INTERVAL '1 2:3:4.567' DAY TO SECOND)"));
+ assertEquals(-1L, eval("EXTRACT(DAY FROM INTERVAL '-1 2:3:4.567' DAY TO SECOND)"));
+ assertEquals(-2L, eval("EXTRACT(HOUR FROM INTERVAL '-1 2:3:4.567' DAY TO SECOND)"));
+ assertEquals(-3L, eval("EXTRACT(MINUTE FROM INTERVAL '-1 2:3:4.567' DAY TO SECOND)"));
+ assertEquals(-4L, eval("EXTRACT(SECOND FROM INTERVAL '-1 2:3:4.567' DAY TO SECOND)"));
+ assertEquals(-4567L, eval("EXTRACT(MILLISECOND FROM INTERVAL '-1 2:3:4.567' DAY TO SECOND)"));
+
+ assertThrows("SELECT EXTRACT(DAY FROM INTERVAL 1 MONTH)", IgniteSQLException.class, "Cannot apply");
+ assertThrows("SELECT EXTRACT(MONTH FROM INTERVAL 1 DAY)", IgniteSQLException.class, "Cannot apply");
+ }
+
+ /** */
+ public Object eval(String exp) {
+ return executeSql("SELECT " + exp).get(0).get(0);
+ }
+}
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/JoinIntegrationTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/JoinIntegrationTest.java
index 376437f..7b5bc5d 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/JoinIntegrationTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/JoinIntegrationTest.java
@@ -19,12 +19,7 @@ package org.apache.ignite.internal.processors.query.calcite.integration;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
-
-import org.apache.ignite.cache.query.FieldsQueryCursor;
-import org.apache.ignite.cache.query.QueryCursor;
-import org.apache.ignite.internal.processors.query.calcite.CalciteQueryProcessor;
import org.apache.ignite.internal.processors.query.calcite.QueryChecker;
-import org.apache.ignite.internal.processors.query.calcite.util.Commons;
import org.junit.Assume;
import org.junit.Test;
import org.junit.runner.RunWith;
@@ -676,15 +671,6 @@ public class JoinIntegrationTest extends AbstractBasicIntegrationTest {
.check();
}
- /** */
- private List<List<?>> executeSql(String sql, Object... args) {
- List<FieldsQueryCursor<List<?>>> cur = queryProcessor().query(null, "PUBLIC", sql, args);
-
- try (QueryCursor<List<?>> srvCursor = cur.get(0)) {
- return srvCursor.getAll();
- }
- }
-
/** {@inheritDoc} */
@Override protected QueryChecker assertQuery(String qry) {
return super.assertQuery(qry.replace("select", "select "
@@ -692,11 +678,6 @@ public class JoinIntegrationTest extends AbstractBasicIntegrationTest {
}
/** */
- private CalciteQueryProcessor queryProcessor() {
- return Commons.lookupComponent(client.context(), CalciteQueryProcessor.class);
- }
-
- /** */
enum JoinType {
/** */
NESTED_LOOP(
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/SetOpIntegrationTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/SetOpIntegrationTest.java
index 07a7f55..f56a3d1 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/SetOpIntegrationTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/SetOpIntegrationTest.java
@@ -24,9 +24,6 @@ import org.apache.ignite.IgniteDataStreamer;
import org.apache.ignite.cache.CacheMode;
import org.apache.ignite.cache.QueryEntity;
import org.apache.ignite.configuration.CacheConfiguration;
-import org.apache.ignite.internal.IgniteInterruptedCheckedException;
-import org.apache.ignite.internal.processors.query.QueryEngine;
-import org.apache.ignite.internal.processors.query.calcite.util.Commons;
import org.apache.ignite.internal.util.typedef.F;
import org.junit.Test;
@@ -90,7 +87,7 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
public void testExcept() throws Exception {
populateTables();
- List<List<?>> rows = sql("SELECT name FROM emp1 EXCEPT SELECT name FROM emp2");
+ List<List<?>> rows = executeSql("SELECT name FROM emp1 EXCEPT SELECT name FROM emp2");
assertEquals(1, rows.size());
assertEquals("Igor", rows.get(0).get(0));
@@ -104,11 +101,11 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
copyCacheAsReplicated("emp1");
copyCacheAsReplicated("emp2");
- List<List<?>> rows = sql("SELECT name FROM emp1 WHERE salary < 0 EXCEPT SELECT name FROM emp2");
+ List<List<?>> rows = executeSql("SELECT name FROM emp1 WHERE salary < 0 EXCEPT SELECT name FROM emp2");
assertEquals(0, rows.size());
- rows = sql("SELECT name FROM emp1_repl WHERE salary < 0 EXCEPT SELECT name FROM emp2_repl");
+ rows = executeSql("SELECT name FROM emp1_repl WHERE salary < 0 EXCEPT SELECT name FROM emp2_repl");
assertEquals(0, rows.size());
}
@@ -118,7 +115,7 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
public void testExceptSeveralColumns() throws Exception {
populateTables();
- List<List<?>> rows = sql("SELECT name, salary FROM emp1 EXCEPT SELECT name, salary FROM emp2");
+ List<List<?>> rows = executeSql("SELECT name, salary FROM emp1 EXCEPT SELECT name, salary FROM emp2");
assertEquals(4, rows.size());
assertEquals(3, F.size(rows, r -> r.get(0).equals("Igor")));
@@ -130,7 +127,7 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
public void testExceptAll() throws Exception {
populateTables();
- List<List<?>> rows = sql("SELECT name FROM emp1 EXCEPT ALL SELECT name FROM emp2");
+ List<List<?>> rows = executeSql("SELECT name FROM emp1 EXCEPT ALL SELECT name FROM emp2");
assertEquals(4, rows.size());
assertEquals(3, F.size(rows, r -> r.get(0).equals("Igor")));
@@ -143,7 +140,7 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
populateTables();
List<List<?>> rows =
- sql("SELECT name FROM emp1 EXCEPT (SELECT name FROM emp1 EXCEPT SELECT name FROM emp2)");
+ executeSql("SELECT name FROM emp1 EXCEPT (SELECT name FROM emp1 EXCEPT SELECT name FROM emp2)");
assertEquals(2, rows.size());
assertEquals(1, F.size(rows, r -> r.get(0).equals("Roman")));
@@ -156,7 +153,7 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
populateTables();
copyCacheAsReplicated("emp1");
- List<List<?>> rows = sql("SELECT name FROM emp1_repl EXCEPT ALL SELECT name FROM emp2");
+ List<List<?>> rows = executeSql("SELECT name FROM emp1_repl EXCEPT ALL SELECT name FROM emp2");
assertEquals(4, rows.size());
assertEquals(3, F.size(rows, r -> r.get(0).equals("Igor")));
@@ -170,7 +167,7 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
copyCacheAsReplicated("emp1");
copyCacheAsReplicated("emp2");
- List<List<?>> rows = sql("SELECT name FROM emp1_repl EXCEPT ALL SELECT name FROM emp2_repl");
+ List<List<?>> rows = executeSql("SELECT name FROM emp1_repl EXCEPT ALL SELECT name FROM emp2_repl");
assertEquals(4, rows.size());
assertEquals(3, F.size(rows, r -> r.get(0).equals("Igor")));
@@ -183,7 +180,7 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
populateTables();
copyCacheAsReplicated("emp1");
- List<List<?>> rows = sql("SELECT name FROM emp1_repl EXCEPT ALL SELECT name FROM emp2 EXCEPT ALL " +
+ List<List<?>> rows = executeSql("SELECT name FROM emp1_repl EXCEPT ALL SELECT name FROM emp2 EXCEPT ALL " +
"SELECT name FROM emp1 WHERE salary < 11");
assertEquals(3, rows.size());
@@ -236,14 +233,14 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
List<List<?>> rows;
// Check 2 partitioned caches.
- rows = sql("SELECT _val FROM \"cache1\".table1 EXCEPT SELECT _val FROM \"cache2\".table2");
+ rows = executeSql("SELECT _val FROM \"cache1\".table1 EXCEPT SELECT _val FROM \"cache2\".table2");
assertEquals(3, rows.size());
assertEquals(1, F.size(rows, r -> r.get(0).equals(0)));
assertEquals(1, F.size(rows, r -> r.get(0).equals(2)));
assertEquals(1, F.size(rows, r -> r.get(0).equals(4)));
- rows = sql("SELECT _val FROM \"cache1\".table1 EXCEPT ALL SELECT _val FROM \"cache2\".table2");
+ rows = executeSql("SELECT _val FROM \"cache1\".table1 EXCEPT ALL SELECT _val FROM \"cache2\".table2");
assertEquals(34817, rows.size());
assertEquals(1, F.size(rows, r -> r.get(0).equals(0)));
@@ -251,27 +248,29 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
assertEquals(1920, F.size(rows, r -> r.get(0).equals(3)));
assertEquals(32768, F.size(rows, r -> r.get(0).equals(4)));
- rows = sql("SELECT _val FROM \"cache1\".table1 INTERSECT SELECT _val FROM \"cache2\".table2");
+ rows = executeSql("SELECT _val FROM \"cache1\".table1 INTERSECT SELECT _val FROM \"cache2\".table2");
assertEquals(2, rows.size());
assertEquals(1, F.size(rows, r -> r.get(0).equals(1)));
assertEquals(1, F.size(rows, r -> r.get(0).equals(3)));
- rows = sql("SELECT _val FROM \"cache1\".table1 INTERSECT ALL SELECT _val FROM \"cache2\".table2");
+ rows = executeSql("SELECT _val FROM \"cache1\".table1 INTERSECT ALL SELECT _val FROM \"cache2\".table2");
assertEquals(136, rows.size());
assertEquals(8, F.size(rows, r -> r.get(0).equals(1)));
assertEquals(128, F.size(rows, r -> r.get(0).equals(3)));
// Check 1 replicated and 1 partitioned caches.
- rows = sql("SELECT _val FROM \"cache1Replicated\".table1_repl EXCEPT SELECT _val FROM \"cache2\".table2");
+ rows = executeSql("SELECT _val FROM \"cache1Replicated\".table1_repl EXCEPT SELECT _val " +
+ "FROM \"cache2\".table2");
assertEquals(3, rows.size());
assertEquals(1, F.size(rows, r -> r.get(0).equals(0)));
assertEquals(1, F.size(rows, r -> r.get(0).equals(2)));
assertEquals(1, F.size(rows, r -> r.get(0).equals(4)));
- rows = sql("SELECT _val FROM \"cache1Replicated\".table1_repl EXCEPT ALL SELECT _val FROM \"cache2\".table2");
+ rows = executeSql("SELECT _val FROM \"cache1Replicated\".table1_repl EXCEPT ALL SELECT _val " +
+ "FROM \"cache2\".table2");
assertEquals(34817, rows.size());
assertEquals(1, F.size(rows, r -> r.get(0).equals(0)));
@@ -279,29 +278,31 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
assertEquals(1920, F.size(rows, r -> r.get(0).equals(3)));
assertEquals(32768, F.size(rows, r -> r.get(0).equals(4)));
- rows = sql("SELECT _val FROM \"cache1Replicated\".table1_repl INTERSECT SELECT _val FROM \"cache2\".table2");
+ rows = executeSql("SELECT _val FROM \"cache1Replicated\".table1_repl INTERSECT SELECT _val " +
+ "FROM \"cache2\".table2");
assertEquals(2, rows.size());
assertEquals(1, F.size(rows, r -> r.get(0).equals(1)));
assertEquals(1, F.size(rows, r -> r.get(0).equals(3)));
- rows = sql("SELECT _val FROM \"cache1Replicated\".table1_repl INTERSECT ALL SELECT _val FROM \"cache2\".table2");
+ rows = executeSql("SELECT _val FROM \"cache1Replicated\".table1_repl INTERSECT ALL SELECT _val " +
+ "FROM \"cache2\".table2");
assertEquals(136, rows.size());
assertEquals(8, F.size(rows, r -> r.get(0).equals(1)));
assertEquals(128, F.size(rows, r -> r.get(0).equals(3)));
// Check 2 replicated caches.
- rows = sql("SELECT _val FROM \"cache1Replicated\".table1_repl EXCEPT SELECT _val FROM \"cache2Replicated\"" +
- ".table2_repl");
+ rows = executeSql("SELECT _val FROM \"cache1Replicated\".table1_repl EXCEPT SELECT _val " +
+ "FROM \"cache2Replicated\".table2_repl");
assertEquals(3, rows.size());
assertEquals(1, F.size(rows, r -> r.get(0).equals(0)));
assertEquals(1, F.size(rows, r -> r.get(0).equals(2)));
assertEquals(1, F.size(rows, r -> r.get(0).equals(4)));
- rows = sql("SELECT _val FROM \"cache1Replicated\".table1_repl EXCEPT ALL SELECT _val FROM \"cache2Replicated\"" +
- ".table2_repl");
+ rows = executeSql("SELECT _val FROM \"cache1Replicated\".table1_repl EXCEPT ALL SELECT _val " +
+ "FROM \"cache2Replicated\".table2_repl");
assertEquals(34817, rows.size());
assertEquals(1, F.size(rows, r -> r.get(0).equals(0)));
@@ -309,15 +310,15 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
assertEquals(1920, F.size(rows, r -> r.get(0).equals(3)));
assertEquals(32768, F.size(rows, r -> r.get(0).equals(4)));
- rows = sql("SELECT _val FROM \"cache1Replicated\".table1_repl INTERSECT SELECT _val FROM \"cache2Replicated\"" +
- ".table2_repl");
+ rows = executeSql("SELECT _val FROM \"cache1Replicated\".table1_repl INTERSECT SELECT _val " +
+ "FROM \"cache2Replicated\".table2_repl");
assertEquals(2, rows.size());
assertEquals(1, F.size(rows, r -> r.get(0).equals(1)));
assertEquals(1, F.size(rows, r -> r.get(0).equals(3)));
- rows = sql("SELECT _val FROM \"cache1Replicated\".table1_repl INTERSECT ALL SELECT _val FROM \"cache2Replicated\"" +
- ".table2_repl");
+ rows = executeSql("SELECT _val FROM \"cache1Replicated\".table1_repl INTERSECT ALL SELECT _val " +
+ "FROM \"cache2Replicated\".table2_repl");
assertEquals(136, rows.size());
assertEquals(8, F.size(rows, r -> r.get(0).equals(1)));
@@ -329,7 +330,7 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
public void testIntersect() throws Exception {
populateTables();
- List<List<?>> rows = sql("SELECT name FROM emp1 INTERSECT SELECT name FROM emp2");
+ List<List<?>> rows = executeSql("SELECT name FROM emp1 INTERSECT SELECT name FROM emp2");
assertEquals(2, rows.size());
assertEquals(1, F.size(rows, r -> r.get(0).equals("Igor1")));
@@ -341,7 +342,7 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
public void testInstersectAll() throws Exception {
populateTables();
- List<List<?>> rows = sql("SELECT name FROM emp1 INTERSECT ALL SELECT name FROM emp2");
+ List<List<?>> rows = executeSql("SELECT name FROM emp1 INTERSECT ALL SELECT name FROM emp2");
assertEquals(3, rows.size());
assertEquals(2, F.size(rows, r -> r.get(0).equals("Igor1")));
@@ -356,11 +357,11 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
copyCacheAsReplicated("emp1");
copyCacheAsReplicated("emp2");
- List<List<?>> rows = sql("SELECT name FROM emp1 WHERE salary < 0 INTERSECT SELECT name FROM emp2");
+ List<List<?>> rows = executeSql("SELECT name FROM emp1 WHERE salary < 0 INTERSECT SELECT name FROM emp2");
assertEquals(0, rows.size());
- rows = sql("SELECT name FROM emp1_repl WHERE salary < 0 INTERSECT SELECT name FROM emp2_repl");
+ rows = executeSql("SELECT name FROM emp1_repl WHERE salary < 0 INTERSECT SELECT name FROM emp2_repl");
assertEquals(0, rows.size());
}
@@ -371,7 +372,7 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
populateTables();
copyCacheAsReplicated("emp1");
- List<List<?>> rows = sql("SELECT name FROM emp1_repl INTERSECT ALL SELECT name FROM emp2 INTERSECT ALL " +
+ List<List<?>> rows = executeSql("SELECT name FROM emp1_repl INTERSECT ALL SELECT name FROM emp2 INTERSECT ALL " +
"SELECT name FROM emp1 WHERE salary < 14");
assertEquals(2, rows.size());
@@ -385,7 +386,7 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
copyCacheAsReplicated("emp1");
copyCacheAsReplicated("emp2");
- List<List<?>> rows = sql("SELECT name FROM emp1_repl INTERSECT ALL SELECT name FROM emp2_repl");
+ List<List<?>> rows = executeSql("SELECT name FROM emp1_repl INTERSECT ALL SELECT name FROM emp2_repl");
assertEquals(3, rows.size());
assertEquals(2, F.size(rows, r -> r.get(0).equals("Igor1")));
@@ -398,7 +399,7 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
populateTables();
copyCacheAsReplicated("emp1");
- List<List<?>> rows = sql("SELECT name FROM emp1_repl INTERSECT ALL SELECT name FROM emp2");
+ List<List<?>> rows = executeSql("SELECT name FROM emp1_repl INTERSECT ALL SELECT name FROM emp2");
assertEquals(3, rows.size());
assertEquals(2, F.size(rows, r -> r.get(0).equals("Igor1")));
@@ -410,16 +411,9 @@ public class SetOpIntegrationTest extends AbstractBasicIntegrationTest {
public void testIntersectSeveralColumns() throws Exception {
populateTables();
- List<List<?>> rows = sql("SELECT name, salary FROM emp1 INTERSECT ALL SELECT name, salary FROM emp2");
+ List<List<?>> rows = executeSql("SELECT name, salary FROM emp1 INTERSECT ALL SELECT name, salary FROM emp2");
assertEquals(2, rows.size());
assertEquals(2, F.size(rows, r -> r.get(0).equals("Igor1")));
}
-
- /** */
- private List<List<?>> sql(String sql) throws IgniteInterruptedCheckedException {
- QueryEngine engine = Commons.lookupComponent(client.context(), QueryEngine.class);
-
- return engine.query(null, "PUBLIC", sql).get(0).getAll();
- }
}
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/jdbc/JdbcQueryTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/jdbc/JdbcQueryTest.java
index 270c6a8..5b3d7500 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/jdbc/JdbcQueryTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/jdbc/JdbcQueryTest.java
@@ -144,14 +144,17 @@ public class JdbcQueryTest extends GridCommonAbstractTest {
"timestamp_col_10 TIMESTAMP(10), " +
"timestamp_col_def TIMESTAMP, " +
"date_col DATE, " +
+ "interval_ym_col INTERVAL YEAR TO MONTH, " +
+ "interval_dt_col INTERVAL DAY TO SECONDS, " +
"PRIMARY KEY (id));");
grid(0).context().cache().context().exchange().affinityReadyFuture(
new AffinityTopologyVersion(3, 2)).get(10_000, TimeUnit.MILLISECONDS);
stmt.executeUpdate("INSERT INTO t1 (id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, " +
- "varchar_col, char_col, float_col, double_col, time_col, timestamp_col_14, timestamp_col_10, timestamp_col_def, date_col) " +
- "VALUES (1, null, null, null, null, null, null, null, null, null, null, null, null, null, null);");
+ "varchar_col, char_col, float_col, double_col, time_col, timestamp_col_14, timestamp_col_10, " +
+ "timestamp_col_def, date_col, interval_ym_col, interval_dt_col) " +
+ "VALUES (1, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);");
try (ResultSet rs = stmt.executeQuery("SELECT * FROM t1;")) {
assertTrue(rs.next());
@@ -174,6 +177,9 @@ public class JdbcQueryTest extends GridCommonAbstractTest {
assertEquals(Types.TIMESTAMP, md.getColumnType(13));
assertEquals(Types.TIMESTAMP, md.getColumnType(14));
assertEquals(Types.DATE, md.getColumnType(15));
+ // Custom java types Period and Duration for intervals.
+ assertEquals(Types.OTHER, md.getColumnType(16));
+ assertEquals(Types.OTHER, md.getColumnType(17));
}
stmt.execute("DROP TABLE t1");
diff --git a/modules/calcite/src/test/java/org/apache/ignite/testsuites/IntegrationTestSuite.java b/modules/calcite/src/test/java/org/apache/ignite/testsuites/IntegrationTestSuite.java
index 253f06d..50d2979 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/testsuites/IntegrationTestSuite.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/testsuites/IntegrationTestSuite.java
@@ -30,6 +30,7 @@ import org.apache.ignite.internal.processors.query.calcite.integration.Aggregate
import org.apache.ignite.internal.processors.query.calcite.integration.CalciteErrorHandlilngIntegrationTest;
import org.apache.ignite.internal.processors.query.calcite.integration.IndexDdlIntegrationTest;
import org.apache.ignite.internal.processors.query.calcite.integration.IndexSpoolIntegrationTest;
+import org.apache.ignite.internal.processors.query.calcite.integration.IntervalTest;
import org.apache.ignite.internal.processors.query.calcite.integration.JoinIntegrationTest;
import org.apache.ignite.internal.processors.query.calcite.integration.KillCommandDdlIntegrationTest;
import org.apache.ignite.internal.processors.query.calcite.integration.MetadataIntegrationTest;
@@ -77,6 +78,7 @@ import org.junit.runners.Suite;
JoinCommuteRulesTest.class,
ServerStatisticsIntegrationTest.class,
JoinIntegrationTest.class,
+ IntervalTest.class,
})
public class IntegrationTestSuite {
}
diff --git a/modules/calcite/src/test/sql/function/interval/test_extract.test b/modules/calcite/src/test/sql/function/interval/test_extract.test
new file mode 100644
index 0000000..bf34e94
--- /dev/null
+++ b/modules/calcite/src/test/sql/function/interval/test_extract.test
@@ -0,0 +1,233 @@
+# name: test/sql/function/interval/test_extract.test
+# description: Extract function
+# group: [interval]
+
+# YEAR-MONTH intervals
+statement ok
+CREATE TABLE intervals_ym(i INTERVAL YEAR TO MONTH)
+
+statement ok
+INSERT INTO intervals_ym VALUES (INTERVAL 1234 YEARS), (INTERVAL 2 YEARS), (INTERVAL 16 MONTHS), (INTERVAL '1-2' YEAR TO MONTH), (NULL)
+
+# extract various parts of the intervals
+query I
+SELECT EXTRACT(year FROM i) FROM intervals_ym ORDER BY i DESC
+----
+1234
+2
+1
+1
+NULL
+
+query I
+SELECT EXTRACT(month FROM i) FROM intervals_ym ORDER BY i DESC
+----
+0
+0
+4
+2
+NULL
+
+query I
+SELECT EXTRACT(decade FROM i) FROM intervals_ym ORDER BY i DESC
+----
+123
+0
+0
+0
+NULL
+
+query I
+SELECT EXTRACT(century FROM i) FROM intervals_ym ORDER BY i DESC
+----
+12
+0
+0
+0
+NULL
+
+query I
+SELECT EXTRACT(millennium FROM i) FROM intervals_ym ORDER BY i DESC
+----
+1
+0
+0
+0
+NULL
+
+query I
+SELECT EXTRACT(quarter FROM i) FROM intervals_ym ORDER BY i DESC
+----
+1
+1
+2
+1
+NULL
+
+statement error
+SELECT EXTRACT(day FROM i) FROM intervals_ym
+
+statement error
+SELECT EXTRACT(dayofweek FROM i) FROM intervals_ym
+
+statement error
+SELECT EXTRACT(isodow FROM i) FROM intervals_ym
+
+statement error
+SELECT EXTRACT(dayofyear FROM i) FROM intervals_ym
+
+statement error
+SELECT EXTRACT(week FROM i) FROM intervals_ym
+
+statement error
+SELECT EXTRACT(day FROM i) FROM intervals_ym
+
+statement error
+SELECT EXTRACT(hour FROM i) FROM intervals_ym
+
+statement error
+SELECT EXTRACT(minute FROM i) FROM intervals_ym
+
+statement error
+SELECT EXTRACT(second FROM i) FROM intervals_ym
+
+statement error
+SELECT EXTRACT(millisecond FROM i) FROM intervals_ym
+
+statement error
+SELECT EXTRACT(microsecond FROM i) FROM intervals_ym
+
+statement error
+SELECT EXTRACT(nanosecond FROM i) FROM intervals_ym
+
+statement error
+SELECT EXTRACT(epoch FROM i) FROM intervals_ym
+
+# DAY-TIME intervals
+statement ok
+CREATE TABLE intervals_dt(i INTERVAL DAY TO SECOND)
+
+statement ok
+INSERT INTO intervals_dt VALUES (INTERVAL 42 DAYS), (INTERVAL '10:20' HOUR TO MINUTE), (INTERVAL '2066.343' SECONDS(4)), (NULL)
+
+statement error
+SELECT EXTRACT(month FROM i) FROM intervals_dt
+
+statement error
+SELECT EXTRACT(quarter FROM i) FROM intervals_dt
+
+statement error
+SELECT EXTRACT(year FROM i) FROM intervals_dt
+
+statement error
+SELECT EXTRACT(decade FROM i) FROM intervals_dt
+
+statement error
+SELECT EXTRACT(century FROM i) FROM intervals_dt
+
+statement error
+SELECT EXTRACT(millennium FROM i) FROM intervals_dt
+
+statement error
+SELECT EXTRACT(dayofweek FROM i) FROM intervals_dt
+
+statement error
+SELECT EXTRACT(isodow FROM i) FROM intervals_dt
+
+statement error
+SELECT EXTRACT(dayofyear FROM i) FROM intervals_dt
+
+statement error
+SELECT EXTRACT(week FROM i) FROM intervals_dt
+
+statement error
+SELECT EXTRACT(epoch FROM i) FROM intervals_dt
+
+query I
+SELECT EXTRACT(microsecond FROM i) FROM intervals_dt ORDER BY i DESC
+----
+0
+0
+26343000
+NULL
+
+query I
+SELECT EXTRACT(millisecond FROM i) FROM intervals_dt ORDER BY i DESC
+----
+0
+0
+26343
+NULL
+
+query I
+SELECT EXTRACT(second FROM i) FROM intervals_dt ORDER BY i DESC
+----
+0
+0
+26
+NULL
+
+query I
+SELECT EXTRACT(minute FROM i) FROM intervals_dt ORDER BY i DESC
+----
+0
+20
+34
+NULL
+
+query I
+SELECT EXTRACT(hour FROM i) FROM intervals_dt ORDER BY i DESC
+----
+0
+10
+0
+NULL
+
+query I
+SELECT EXTRACT(day FROM i) FROM intervals_dt ORDER BY i DESC
+----
+42
+0
+0
+NULL
+
+# negative intervals
+query I
+SELECT EXTRACT(year FROM interval '-14' months)
+----
+-1
+
+query I
+SELECT EXTRACT(month FROM interval '-14' months)
+----
+-2
+
+query I
+SELECT EXTRACT(day FROM interval '-157' days(3))
+----
+-157
+
+query I
+SELECT EXTRACT(decade FROM interval '-140' months(3))
+----
+-1
+
+query I
+SELECT EXTRACT(century FROM interval '-1400' months(4))
+----
+-1
+
+query I
+SELECT EXTRACT(millennium FROM interval '-14000' months(5))
+----
+-1
+
+query I
+SELECT EXTRACT(quarter FROM interval '-14' months)
+----
+4
+
+query I
+SELECT EXTRACT(quarter FROM interval '-21' months)
+----
+1
diff --git a/modules/calcite/src/test/sql/function/interval/test_extract.test_ignore b/modules/calcite/src/test/sql/function/interval/test_extract.test_ignore
deleted file mode 100644
index ebcfae1..0000000
--- a/modules/calcite/src/test/sql/function/interval/test_extract.test_ignore
+++ /dev/null
@@ -1,187 +0,0 @@
-# name: test/sql/function/interval/test_extract.test
-# description: Extract function
-# group: [interval]
-# Ignored: https://issues.apache.org/jira/browse/IGNITE-14975
-
-statement ok
-CREATE TABLE intervals(i INTERVAL)
-
-statement ok
-INSERT INTO intervals VALUES ('2 years'), ('16 months'), ('42 days'), ('2066343400 microseconds'), (NULL)
-
-# extract various parts of the intervals
-query I
-SELECT EXTRACT(year FROM i) FROM intervals
-----
-2
-1
-0
-0
-NULL
-
-query I
-SELECT EXTRACT(month FROM i) FROM intervals
-----
-0
-4
-0
-0
-NULL
-
-query I
-SELECT EXTRACT(day FROM i) FROM intervals
-----
-0
-0
-42
-0
-NULL
-
-query I
-SELECT EXTRACT(decade FROM i) FROM intervals
-----
-0
-0
-0
-0
-NULL
-
-query I
-SELECT EXTRACT(century FROM i) FROM intervals
-----
-0
-0
-0
-0
-NULL
-
-query I
-SELECT EXTRACT(millennium FROM i) FROM intervals
-----
-0
-0
-0
-0
-NULL
-
-query I
-SELECT EXTRACT(quarter FROM i) FROM intervals
-----
-1
-2
-1
-1
-NULL
-
-statement error
-SELECT EXTRACT(dayofweek FROM i) FROM intervals
-
-statement error
-SELECT EXTRACT(isodow FROM i) FROM intervals
-
-statement error
-SELECT EXTRACT(dayofyear FROM i) FROM intervals
-
-statement error
-SELECT EXTRACT(week FROM i) FROM intervals
-
-query I
-SELECT EXTRACT(epoch FROM i) FROM intervals
-----
-62208000
-41472000
-3628800
-2066
-NULL
-
-query I
-SELECT EXTRACT(microsecond FROM i) FROM intervals
-----
-0
-0
-0
-2066343400
-NULL
-
-query I
-SELECT EXTRACT(millisecond FROM i) FROM intervals
-----
-0
-0
-0
-2066343
-NULL
-
-query I
-SELECT EXTRACT(second FROM i) FROM intervals
-----
-0
-0
-0
-2066
-NULL
-
-query I
-SELECT EXTRACT(minute FROM i) FROM intervals
-----
-0
-0
-0
-34
-NULL
-
-query I
-SELECT EXTRACT(hour FROM i) FROM intervals
-----
-0
-0
-0
-0
-NULL
-
-# negative intervals with months are strange in Postgres
-query I
-SELECT EXTRACT(year FROM interval '14 months ago')
-----
--1
-
-query I
-SELECT EXTRACT(month FROM interval '14 months ago')
-----
--2
-
-query I
-SELECT EXTRACT(day FROM interval '157 days ago')
-----
--157
-
-query I
-SELECT EXTRACT(decade FROM interval '140 months ago')
-----
--1
-
-query I
-SELECT EXTRACT(century FROM interval '1400 months ago')
-----
--1
-
-query I
-SELECT EXTRACT(millennium FROM interval '14000 months ago')
-----
--1
-
-query I
-SELECT EXTRACT(quarter FROM interval '14 months ago')
-----
-1
-
-query I
-SELECT EXTRACT(quarter FROM interval '21 months ago')
-----
--2
-
-query I
-SELECT EXTRACT(epoch FROM interval '6 months ago')
-----
--15552000
-
diff --git a/modules/calcite/src/test/sql/types/interval/interval_constants.test_ignore b/modules/calcite/src/test/sql/types/interval/interval_constants.test
similarity index 56%
rename from modules/calcite/src/test/sql/types/interval/interval_constants.test_ignore
rename to modules/calcite/src/test/sql/types/interval/interval_constants.test
index bc00456..df2f6ed 100644
--- a/modules/calcite/src/test/sql/types/interval/interval_constants.test_ignore
+++ b/modules/calcite/src/test/sql/types/interval/interval_constants.test
@@ -1,7 +1,6 @@
# name: test/sql/types/interval/interval_constants.test
# description: Test constant intervals
# group: [interval]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-14975
statement ok
PRAGMA enable_verification
@@ -10,103 +9,115 @@ PRAGMA enable_verification
query I
SELECT interval 2 days;
----
-2 days
+PT48H
-query I
+query T
SELECT interval (2) day;
----
-2 days
+PT48H
-query I
+query T
SELECT interval (1+1) days;
----
-2 days
+PT48H
-query I
+query T
SELECT interval '2' days;
----
-2 days
+PT48H
-# use the interval functions
-query IIIIII
-SELECT to_years(2), to_months(2), to_days(2), to_hours(2), to_minutes(2), to_seconds(2);
+query TTTTTT
+SELECT interval 2 years, interval 2 months, interval 2 days, interval 2 hours, interval 2 minutes, interval 2 seconds;
----
-2 years 2 months 2 days 02:00:00 00:02:00 00:00:02
+P2Y P2M PT48H PT2H PT2M PT2S
# non-constant expression
-query I
-SELECT interval (i) day from range(1, 4) tbl(i);
+query T
+SELECT interval (x) day from table(system_range(1, 3));
----
-1 day
-2 days
-3 days
+PT24H
+PT48H
+PT72H
-query I
-SELECT interval (i + 1) day from range(1, 4) tbl(i);
+query T
+SELECT interval (x + 1) day from table(system_range(1, 3));
----
-2 days
-3 days
-4 days
+PT48H
+PT72H
+PT96H
# try all the different types
-query II
+query TT
SELECT interval 2 years, interval 2 year;
----
-2 years 2 years
+P2Y P2Y
-query II
+query TT
SELECT interval 2 months, interval 2 month;
----
-2 months 2 months
+P2M P2M
-query II
+query TT
SELECT interval 2 days, interval 2 day;
----
-2 days 2 days
+PT48H PT48H
-query II
+query TT
SELECT interval 2 hours, interval 2 hour;
----
-02:00:00 02:00:00
+PT2H PT2H
-query II
+query TT
SELECT interval 2 minutes, interval 2 minute;
----
-00:02:00 00:02:00
+PT2M PT2M
-query II
+query TT
SELECT interval 2 seconds, interval 2 second;
----
-00:00:02 00:00:02
+PT2S PT2S
-# for now we don't support the weird ones
# 2 years 10 months
-statement error
-SELECT interval '2 10' years to months
+query T
+SELECT interval '2-10' years to months
+----
+P2Y10M
# 2 days 10 hours
-statement error
+query T
SELECT interval '2 10' days to hours
+----
+PT58H
# 12 days 15:06:00
-statement error
+query T
SELECT interval '12 15:06' days to minutes
+----
+PT303H6M
# 12 days 15:06:04.123
-statement error
+query T
SELECT interval '12 15:06:04.123' days to seconds
+----
+PT303H6M4.123S
# 12:30:00
-statement error
+query T
SELECT interval '12:30' hours to minutes
+----
+PT12H30M
# 15:06:04.123
-statement error
+query T
SELECT interval '15:06:04.123' hours to seconds
+----
+PT15H6M4.123S
# 00:12:30
-statement error
+query T
SELECT interval '12:30' minutes to seconds
+----
+PT12M30S
# test out of range interval constants
statement error
@@ -124,23 +135,18 @@ SELECT interval '9223372036854775807' minutes;
statement error
SELECT interval '9223372036854775807' seconds;
-# milli/microseconds
-query I
-SELECT interval 3 milliseconds;
-----
-00:00:00.003
-
+# milliseconds
query I
-SELECT interval 3 microseconds;
+SELECT interval '0.003' seconds;
----
-00:00:00.000003
+PT0.003S
query I
-select timestamp '1992-01-01 12:00:00' + interval 3 microseconds + interval 3 milliseconds
+select timestamp '1992-01-01 12:00:00' + interval '0.003' seconds
----
-1992-01-01 12:00:00.003003
+1992-01-01 12:00:00.003
query I
-select timestamp '1992-01-01 12:00:00' + (interval 3 microseconds + interval 3 milliseconds)
+select timestamp '1992-01-01 12:00:00' + (interval '0.003' seconds + interval '0.003' seconds)
----
-1992-01-01 12:00:00.003003
+1992-01-01 12:00:00.006
diff --git a/modules/calcite/src/test/sql/types/interval/test_interval.test b/modules/calcite/src/test/sql/types/interval/test_interval.test
new file mode 100644
index 0000000..3dd7218
--- /dev/null
+++ b/modules/calcite/src/test/sql/types/interval/test_interval.test
@@ -0,0 +1,163 @@
+# name: test/sql/types/interval/test_interval.test
+# description: Test basic interval usage
+# group: [interval]
+
+statement ok
+PRAGMA enable_verification
+
+# 2 years
+query T
+SELECT CAST('2' AS INTERVAL YEAR)
+----
+P2Y
+
+query T
+SELECT INTERVAL '2' YEARS::VARCHAR
+----
++2
+
+query T
+SELECT INTERVAL '90' DAY;
+----
+PT2160H
+
+query T
+SELECT INTERVAL '90' YEAR;
+----
+P90Y
+
+query T
+SELECT INTERVAL '90' MONTH;
+----
+P7Y6M
+
+query T
+SELECT INTERVAL '90' SECOND;
+----
+PT1M30S
+
+query T
+SELECT INTERVAL '90' MINUTE;
+----
+PT1H30M
+
+query T
+SELECT INTERVAL '90' HOUR;
+----
+PT90H
+
+# we can add together intervals
+query T
+SELECT INTERVAL '2' MONTH + INTERVAL '1' MONTH;
+----
+P3M
+
+# or subtract them
+query T
+SELECT INTERVAL '2' MONTH - INTERVAL '1' MONTH;
+----
+P1M
+
+# but not multiply
+statement error
+SELECT INTERVAL '2' MONTH * INTERVAL '1' MONTH;
+
+# we can, however, multiply/divide intervals by integers
+query T
+SELECT INTERVAL '2-2' YEAR TO MONTH * 2;
+----
+P4Y4M
+
+# multiplication can be done both ways
+query T
+SELECT 2 * INTERVAL '2-2' YEAR TO MONTH;
+----
+P4Y4M
+
+query T
+SELECT INTERVAL '2-2' YEAR TO MONTH / 2;
+----
+P1Y1M
+
+# division cannot!
+statement error
+SELECT 2 / INTERVAL '2-2' YEAR TO MONTH;
+
+# division by zero
+statement error
+SELECT INTERVAL '2-2' YEAR TO MONTH / 0;
+
+# invalid intervals
+# empty interval
+statement error
+SELECT INTERVAL '' DAYS;
+
+statement error
+SELECT INTERVAL ' ' DAYS;
+
+# no number
+statement error
+SELECT INTERVAL 'years' YEARS;
+
+statement error
+SELECT INTERVAL '-years' YEARS;
+
+# gibberish
+statement error
+SELECT INTERVAL 'aergjaerghiuaehrgiuhaerg' DAYS;
+
+# overflow in year
+statement error
+SELECT INTERVAL '100000000000000000' YEARS(18);
+
+# overflow in months
+statement error
+SELECT INTERVAL '100000000000000000' MONTHS(18);
+
+statement error
+SELECT INTERVAL '2147483648' MONTHS(10);
+
+query T
+SELECT INTERVAL '2147483647' MONTHS(10);
+----
+P178956970Y7M
+
+query T
+SELECT INTERVAL '2147483647' MONTHS(10) - INTERVAL '2147483647' MONTHS(10);
+----
+P0D
+
+# overflow in days
+statement error
+SELECT INTERVAL '100000000000000000' DAYS(18);
+
+statement error
+SELECT INTERVAL '100000000000000000' HOURS(18);
+
+query T
+SELECT INTERVAL '2147483647' HOURS(10);
+----
+PT2147483647H
+
+query T
+SELECT INTERVAL '-2147483647.999' SECONDS(10);
+----
+PT-596523H-14M-7.999S
+
+query T
+SELECT INTERVAL '2147483647.999' SECONDS(10);
+----
+PT596523H14M7.999S
+
+statement error
+SELECT INTERVAL '2147483648.999' SECONDS(10);
+
+statement error
+SELECT INTERVAL '-2147483648.999' SECONDS(10);
+
+# need a number here
+statement error
+SELECT INTERVAL 'aa' DAY;
+
+statement error
+SELECT INTERVAL '100 months' DAY;
diff --git a/modules/calcite/src/test/sql/types/interval/test_interval.test_ignore b/modules/calcite/src/test/sql/types/interval/test_interval.test_ignore
deleted file mode 100644
index 2abfe62..0000000
--- a/modules/calcite/src/test/sql/types/interval/test_interval.test_ignore
+++ /dev/null
@@ -1,240 +0,0 @@
-# name: test/sql/types/interval/test_interval.test
-# description: Test basic interval usage
-# group: [interval]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-14975
-
-statement ok
-PRAGMA enable_verification
-
-# 2 years
-query T
-SELECT INTERVAL '2 years'
-----
-2 years
-
-query T
-SELECT INTERVAL '2 years'::VARCHAR
-----
-2 years
-
-# 2 years one minute
-query T
-SELECT INTERVAL '2Y 1 M';
-----
-2 years 00:01:00
-
-# 2 years 4 days one minute 3 seconds 20 milliseconds
-query T
-SELECT INTERVAL '2Y 1 month 1 M 3S 20mS';
-----
-2 years 1 month 00:01:03.02
-
-query T
-SELECT INTERVAL '2Y 1 month 1M 3S 20mS'::VARCHAR;
-----
-2 years 1 month 00:01:03.02
-
-# -2 years +4 days +one minute 3 seconds 20 milliseconds
-query T
-SELECT INTERVAL '-2Y 4 days 1 MinUteS 3S 20mS';
-----
--2 years 4 days 00:01:03.02
-
-query T
-SELECT INTERVAL '-2Y 4 days 1 MinUteS 3S 20mS'::VARCHAR;
-----
--2 years 4 days 00:01:03.02
-
-# test ago usage
-query T
-SELECT INTERVAL '2Y 4 days 1 MinUteS 3S 20mS ago'::VARCHAR;
-----
--2 years -4 days -00:01:03.02
-
-# months and hours, with optional @
-query T
-SELECT INTERVAL '@2mons 1H';
-----
-2 months 01:00:00
-
-# FIXME: we can also use the ISO 8601 interval format
-statement error
-SELECT INTERVAL 'P2MT1H1M';
-
-# FIXME: or this format
-statement error
-SELECT INTERVAL 'P00-02-00T01:00:01';
-
-query T
-SELECT INTERVAL '90' DAY;
-----
-90 days
-
-query T
-SELECT INTERVAL '90' YEAR;
-----
-90 years
-
-query T
-SELECT INTERVAL '90' MONTH;
-----
-7 years 6 months
-
-query T
-SELECT INTERVAL '90' SECOND;
-----
-00:01:30
-
-query T
-SELECT INTERVAL '90' MINUTE;
-----
-01:30:00
-
-query T
-SELECT INTERVAL '90' HOUR;
-----
-90:00:00
-
-# we can add together intervals
-query T
-SELECT INTERVAL '2 month' + INTERVAL '1 month 3 days';
-----
-3 months 3 days
-
-# or subtract them
-query T
-SELECT INTERVAL '2 month' - INTERVAL '1 month 3 days';
-----
-1 month -3 days
-
-# but not multiply
-statement error
-SELECT INTERVAL '2 month' * INTERVAL '1 month 3 days';
-
-# we can, however, multiply/divide intervals by integers
-query T
-SELECT INTERVAL '1 year 2 days 2 seconds' * 2;
-----
-2 years 4 days 00:00:04
-
-# multiplication can be done both ways
-query T
-SELECT 2 * INTERVAL '1 year 2 days 2 seconds';
-----
-2 years 4 days 00:00:04
-
-query T
-SELECT INTERVAL '1 year 2 days 2 seconds' / 2;
-----
-6 months 1 day 00:00:01
-
-# division cannot!
-statement error
-SELECT 2 / INTERVAL '1 year 2 days 2 seconds';
-
-# division by zero
-query T
-SELECT INTERVAL '1 year 2 days 2 seconds' / 0;
-----
-NULL
-
-# invalid intervals
-# empty interval
-statement error
-SELECT INTERVAL '';
-
-statement error
-SELECT INTERVAL ' ';
-
-# no number
-statement error
-SELECT INTERVAL 'years';
-
-statement error
-SELECT INTERVAL '-years';
-
-# gibberish
-statement error
-SELECT INTERVAL 'aergjaerghiuaehrgiuhaerg';
-
-# overflow in year
-statement error
-SELECT INTERVAL '100000000000000000year';
-
-# overflow in months
-statement error
-SELECT INTERVAL '100000000000000000months';
-
-statement error
-SELECT INTERVAL '4294967296months';
-
-query T
-SELECT INTERVAL '1294967296months';
-----
-107913941 years 4 months
-
-statement error
-SELECT INTERVAL '1294967296months 1294967296months';
-
-query T
-SELECT INTERVAL '1294967296months -1294967296months';
-----
-00:00:00
-
-statement error
-SELECT INTERVAL '-1294967296months -1294967296months';
-
-# overflow in days
-statement error
-SELECT INTERVAL '100000000000000000days';
-
-statement error
-SELECT INTERVAL '1294967296days 1294967296days';
-
-# overflow in micro seconds
-statement error
-SELECT INTERVAL '100000000000000000000msecs';
-
-statement error
-SELECT INTERVAL '100000000000000000hours';
-
-query T
-SELECT INTERVAL '2562047788 hours';
-----
-2562047788:00:00
-
-statement error
-SELECT INTERVAL '2562047788 hours 2562047788 hours';
-
-query T
-SELECT INTERVAL '-9223372036854775msecs';
-----
--2562047788:00:54.775
-
-query T
-SELECT INTERVAL '9223372036854775msecs';
-----
-2562047788:00:54.775
-
-query T
-SELECT INTERVAL '-2147483647 months -2147483647 days -9223372036854775msecs';
-----
--178956970 years -7 months -2147483647 days -2562047788:00:54.775
-
-query T
-SELECT INTERVAL '2147483647 months 2147483647 days 9223372036854775msecs';
-----
-178956970 years 7 months 2147483647 days 2562047788:00:54.775
-
-statement error
-SELECT INTERVAL '9223372036854775810msecs';
-
-statement error
-SELECT INTERVAL '-9223372036854775810msecs';
-
-# need a number here
-statement error
-SELECT INTERVAL 'aa' DAY;
-
-statement error
-SELECT INTERVAL '100 months' DAY;
diff --git a/modules/calcite/src/test/sql/types/interval/test_interval_addition.test_ignore b/modules/calcite/src/test/sql/types/interval/test_interval_addition.test
similarity index 87%
rename from modules/calcite/src/test/sql/types/interval/test_interval_addition.test_ignore
rename to modules/calcite/src/test/sql/types/interval/test_interval_addition.test
index 0abf59f..2c7e773 100644
--- a/modules/calcite/src/test/sql/types/interval/test_interval_addition.test_ignore
+++ b/modules/calcite/src/test/sql/types/interval/test_interval_addition.test
@@ -1,7 +1,6 @@
# name: test/sql/types/interval/test_interval_addition.test
# description: Test interval addition/subtraction
# group: [interval]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-14975
statement ok
PRAGMA enable_verification
@@ -177,18 +176,18 @@ SELECT DATE '1993-03-01' - INTERVAL '1' SECOND
# but a large amount of seconds does have an impact
query T
-SELECT DATE '1993-03-01' + INTERVAL '1000000' SECOND
+SELECT DATE '1993-03-01' + INTERVAL '1000000' SECOND(7)
----
1993-03-12
query T
-SELECT DATE '1993-03-01' - INTERVAL '1000000' SECOND
+SELECT DATE '1993-03-01' - INTERVAL '1000000' SECOND(7)
----
1993-02-18
# we cannot subtract dates from intervals
statement error
-SELECT INTERVAL '1000000' SECOND - DATE '1993-03-01'
+SELECT INTERVAL '1000000' SECOND(7) - DATE '1993-03-01'
# we can add/subtract them to/from times
query T
@@ -232,25 +231,20 @@ SELECT TIME '00:00:00' + INTERVAL '-1' HOUR
query T
SELECT TIMESTAMP '1992-01-01 10:00:00' + INTERVAL '1' DAY
----
-1992-01-02 10:00:00
+1992-01-02 10:00:00.0
query T
SELECT INTERVAL '1' DAY + TIMESTAMP '1992-01-01 10:00:00'
----
-1992-01-02 10:00:00
-
-query T
-SELECT TIMESTAMP '1992-01-01 10:00:05' + INTERVAL '17 years 3 months 1 day 2 hours 1 minute 57 seconds'
-----
-2009-04-02 12:02:02
+1992-01-02 10:00:00.0
query T
SELECT TIMESTAMP '1992-01-01 10:00:00' - INTERVAL '1' DAY
----
-1991-12-31 10:00:00
+1991-12-31 10:00:00.0
query T
-select timestamp '1993-01-01 00:00:00' - timestamp '1991-01-01 01:00:30';
+select (timestamp '1993-01-01 00:00:00' - timestamp '1991-01-01 01:00:30') days;
----
-1 year 11 months 30 days 22:59:30
+PT17542H59M30S
diff --git a/modules/calcite/src/test/sql/types/interval/test_interval_comparison.test_ignore b/modules/calcite/src/test/sql/types/interval/test_interval_comparison.test
similarity index 56%
rename from modules/calcite/src/test/sql/types/interval/test_interval_comparison.test_ignore
rename to modules/calcite/src/test/sql/types/interval/test_interval_comparison.test
index 10459e3..330d17d 100644
--- a/modules/calcite/src/test/sql/types/interval/test_interval_comparison.test_ignore
+++ b/modules/calcite/src/test/sql/types/interval/test_interval_comparison.test
@@ -1,49 +1,31 @@
# name: test/sql/types/interval/test_interval_comparison.test
# description: Test interval comparisons
# group: [interval]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-14975
statement ok
PRAGMA enable_verification
-# 30 days = 1 month for ordering purposes, but NOT for equality purposes
-query T
+# Cannot compare DAY-TIME and YEAR-MONTHS intervals
+statement error
SELECT INTERVAL '30' DAY > INTERVAL '1' MONTH
-----
-0
-
-query T
-SELECT INTERVAL '30' DAY = INTERVAL '1' MONTH
-----
-0
-
-query T
-SELECT INTERVAL '30' DAY >= INTERVAL '1' MONTH
-----
-0
-
-query T
-SELECT INTERVAL '31' DAY > INTERVAL '1' MONTH
-----
-1
query T
SELECT INTERVAL '1' HOUR < INTERVAL '1' DAY
----
-1
+true
query T
SELECT INTERVAL '30' HOUR <= INTERVAL '1' DAY
----
-0
+false
query T
SELECT INTERVAL '1' HOUR = INTERVAL '1' HOUR
----
-1
+true
query T
SELECT INTERVAL '1' YEAR = INTERVAL '12' MONTH
----
-1
+true
diff --git a/modules/calcite/src/test/sql/types/interval/test_interval_ops.test b/modules/calcite/src/test/sql/types/interval/test_interval_ops.test
new file mode 100644
index 0000000..e45dd09
--- /dev/null
+++ b/modules/calcite/src/test/sql/types/interval/test_interval_ops.test
@@ -0,0 +1,51 @@
+# name: test/sql/types/interval/test_interval_ops.test
+# description: Test various ops involving intervals
+# group: [interval]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE interval_ym (t INTERVAL YEAR TO MONTH);
+
+statement ok
+INSERT INTO interval_ym VALUES (INTERVAL '20' MONTHS), (INTERVAL '1' YEAR), (INTERVAL '1' MONTH);
+
+query I
+SELECT COUNT(DISTINCT t) FROM interval_ym
+----
+3
+
+statement ok
+UPDATE interval_ym SET t=INTERVAL '1' MONTH WHERE t=INTERVAL '20' MONTHS;
+
+query T
+SELECT i1.t FROM interval_ym i1 JOIN interval_ym i2 ON (i1.t = i2.t) ORDER BY 1
+----
+P1M
+P1M
+P1M
+P1M
+P1Y
+
+query TT
+SELECT * FROM interval_ym i1 JOIN interval_ym i2 ON (i1.t <> i2.t) ORDER BY 1
+----
+P1M P1Y
+P1M P1Y
+P1Y P1M
+P1Y P1M
+
+query TT
+SELECT * FROM interval_ym i1 JOIN interval_ym i2 ON (i1.t > i2.t) ORDER BY 1
+----
+P1Y P1M
+P1Y P1M
+
+query T
+SELECT t FROM interval_ym ORDER BY 1;
+----
+P1M
+P1M
+P1Y
+
diff --git a/modules/calcite/src/test/sql/types/interval/test_interval_ops.test_ignore b/modules/calcite/src/test/sql/types/interval/test_interval_ops.test_ignore
index 8f86e68..bf1963c 100644
--- a/modules/calcite/src/test/sql/types/interval/test_interval_ops.test_ignore
+++ b/modules/calcite/src/test/sql/types/interval/test_interval_ops.test_ignore
@@ -1,52 +1,53 @@
# name: test/sql/types/interval/test_interval_ops.test
# description: Test various ops involving intervals
# group: [interval]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-14975
+# Ignore https://issues.apache.org/jira/browse/IGNITE-15569
+# Ignore https://issues.apache.org/jira/browse/IGNITE-14777
statement ok
PRAGMA enable_verification
statement ok
-CREATE TABLE interval (t INTERVAL);
+CREATE TABLE interval_ym (t INTERVAL YEAR TO MONTH);
statement ok
-INSERT INTO interval VALUES (INTERVAL '20' DAY), (INTERVAL '1' YEAR), (INTERVAL '1' MONTH);
+INSERT INTO interval_ym VALUES (INTERVAL '20' MONTHS), (INTERVAL '1' YEAR), (INTERVAL '1' MONTH);
query I
-SELECT COUNT(DISTINCT t) FROM interval
+SELECT COUNT(DISTINCT t) FROM interval_ym
----
3
statement ok
-UPDATE interval SET t=INTERVAL '1' MONTH WHERE t=INTERVAL '20' DAY;
+UPDATE interval_ym SET t=INTERVAL '1' MONTH WHERE t=INTERVAL '20' MONTHS;
query T
-SELECT * FROM interval i1 JOIN interval i2 USING (t) ORDER BY 1
+SELECT * FROM interval_ym i1 JOIN interval_ym i2 USING (t) ORDER BY 1
----
-1 month
-1 month
-1 month
-1 month
-1 year
+P1M
+P1M
+P1M
+P1M
+P1Y
query TT
-SELECT * FROM interval i1 JOIN interval i2 ON (i1.t <> i2.t) ORDER BY 1
+SELECT * FROM interval_ym i1 JOIN interval_ym i2 ON (i1.t <> i2.t) ORDER BY 1
----
-1 month 1 year
-1 month 1 year
-1 year 1 month
-1 year 1 month
+P1M P1Y
+P1M P1Y
+P1Y P1M
+P1Y P1M
query TT
-SELECT * FROM interval i1 JOIN interval i2 ON (i1.t > i2.t) ORDER BY 1
+SELECT * FROM interval_ym i1 JOIN interval_ym i2 ON (i1.t > i2.t) ORDER BY 1
----
-1 year 1 month
-1 year 1 month
+P1Y P1M
+P1Y P1M
query TI
-SELECT t, row_number() OVER (PARTITION BY t ORDER BY t) FROM interval ORDER BY 1, 2;
+SELECT t, row_number() OVER (PARTITION BY t ORDER BY t) FROM interval_ym ORDER BY 1, 2;
----
-1 month 1
-1 month 2
-1 year 1
+P1M 1
+P1M 2
+P1Y 1