lowka commented on code in PR #6057: URL: https://github.com/apache/ignite-3/pull/6057#discussion_r2158748465
########## modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/datatypes/ItCastTemporalPrecisionTest.java: ########## @@ -0,0 +1,564 @@ +/* + * 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.sql.engine.datatypes; + +import static org.apache.calcite.sql.type.SqlTypeName.TIME; +import static org.apache.calcite.sql.type.SqlTypeName.TIMESTAMP; +import static org.apache.calcite.sql.type.SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE; +import static org.apache.calcite.sql.type.SqlTypeName.VARCHAR; +import static org.apache.ignite.internal.lang.IgniteStringFormatter.format; +import static org.apache.ignite.internal.sql.engine.datatypes.ItCastTemporalPrecisionTest.Parser.dateTime; +import static org.apache.ignite.internal.sql.engine.datatypes.ItCastTemporalPrecisionTest.Parser.instant; +import static org.apache.ignite.internal.sql.engine.datatypes.ItCastTemporalPrecisionTest.Parser.time; + +import java.time.Instant; +import java.time.LocalDateTime; +import java.time.LocalTime; +import java.time.ZoneOffset; +import java.time.temporal.ChronoField; +import java.time.temporal.Temporal; +import java.util.List; +import java.util.Objects; +import java.util.concurrent.TimeUnit; +import java.util.concurrent.atomic.AtomicInteger; +import java.util.stream.Collectors; +import java.util.stream.Stream; +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.ignite.internal.lang.IgniteStringBuilder; +import org.apache.ignite.internal.sql.BaseSqlIntegrationTest; +import org.apache.ignite.internal.sql.engine.util.Commons; +import org.apache.ignite.internal.sql.engine.util.MetadataMatcher; +import org.apache.ignite.internal.sql.engine.util.TypeUtils; +import org.apache.ignite.sql.ColumnType; +import org.hamcrest.BaseMatcher; +import org.hamcrest.Description; +import org.hamcrest.Matcher; +import org.hamcrest.Matchers; +import org.junit.jupiter.api.Assumptions; +import org.junit.jupiter.api.BeforeAll; +import org.junit.jupiter.params.ParameterizedTest; +import org.junit.jupiter.params.provider.Arguments; +import org.junit.jupiter.params.provider.MethodSource; + +/** + * End-to-End tests that verify precision of the result of a CAST on temporal types. + */ +public class ItCastTemporalPrecisionTest extends BaseSqlIntegrationTest { + /** List of tested precisions. */ + private static final List<Integer> PRECISIONS = List.of(0, 1, 2, 3, 6, 9); + + /** Rows counter. */ + private final AtomicInteger rowsCounter = new AtomicInteger(1); + + @Override + protected int initialNodes() { + return 1; + } + + @BeforeAll + void createTables() { + IgniteStringBuilder scriptBuf = new IgniteStringBuilder(); + + // Creates four tables for each specified type (T_TIME, T_TIMESTAMP...). + // With seven columns- for each tested precision (0, 1, ...) and PK (int) column. + // The suffix number of the column name is equal to the precision. + // For example T_TIME: + // ID INT, C0 TIME(0). C1 TIME(1)... + List.of(TIME, TIMESTAMP, TIMESTAMP_WITH_LOCAL_TIME_ZONE, VARCHAR) + .forEach(type -> { + String columns = PRECISIONS.stream() + .map(p -> ", c" + p + " " + sqlNameWithPrecision(type, type == VARCHAR ? 64 : p) + ).collect(Collectors.joining()); + + scriptBuf.app(format("CREATE TABLE t_{} (ID INT PRIMARY KEY{});\n", type.getName(), columns)); + }); + + String script = scriptBuf.toString(); + + log.info("Executing setup script:\n" + script); + + CLUSTER.aliveNode().sql().executeScript(script); + } + + /** + * Test verifies precision of the result of a CAST from literal (and dynamic parameter) to the specified type. + */ + @ParameterizedTest(name = "{0} ''{1}''::{2}({3}) = {4}") + @MethodSource("selectCastArgs") + public void selectCast(SqlTypeName sourceType, String literal, SqlTypeName targetType, int targetPrecision, + Matcher<Iterable<?>> matcher) { + RelDataType targetDataType = Commons.typeFactory().createSqlType(targetType, targetPrecision); + ColumnType expectColumnType = TypeUtils.columnType(targetDataType); + String literalType = sourceType == VARCHAR ? "" : sourceType.getSpaceName(); + String targetTypeString = sqlNameWithPrecision(targetType, targetPrecision); + + // Literal. + { + assertQuery(format("SELECT {} '{}'::{}", literalType, literal, targetTypeString)) + .withTimeZoneId(ZoneOffset.UTC) + .columnMetadata(new MetadataMatcher().type(expectColumnType).precision(targetPrecision)) + .returnMatched(matcher) + .check(); + } + + // Literal with format. + { + String query = format("SELECT CAST({} '{}' AS {} FORMAT '{}')", + literalType, literal, targetTypeString, sqlFormat(targetType)); + + assertQuery(query) + .withTimeZoneId(ZoneOffset.UTC) + .columnMetadata(new MetadataMatcher().type(expectColumnType).precision(targetPrecision)) + .returnMatched(matcher) + .check(); + } + + // Dynamic parameter. + { + Object param = parseSourceLiteral(literal, sourceType); + + // TODO https://issues.apache.org/jira/browse/IGNITE-25716 Remove assumption. + Assumptions.assumeFalse(sourceType == TIME && targetType == TIME && targetPrecision == 0); + + assertQuery(format("SELECT ?::{}", sqlNameWithPrecision(targetType, targetPrecision))) + .withParam(param) + .withTimeZoneId(ZoneOffset.UTC) + .columnMetadata(new MetadataMatcher().type(expectColumnType).precision(targetPrecision)) + .returnMatched(matcher) + .check(); + } + } + + private static Stream<Arguments> selectCastArgs() { + return castArgs().map(SelectArgs::toArgs); + } + + /** + * The test verifies the precision of the CAST result when inserting/updating from one table column to another. + */ + @ParameterizedTest(name = "{0}({1}) {2} :: {3}({4}) = {5}") + @MethodSource("dmlCastArgs") + public void dmlCast(SqlTypeName sourceType, int sourcePrecision, String literal, SqlTypeName targetType, int targetPrecision, + Matcher<Iterable<?>> matcher) { + String sourceColumnName = "c" + sourcePrecision; + String targetColumnName = "c" + targetPrecision; + + // UPSERT initial literal value (id = 0). + { + int soruceCastPrecision = 9; + + if (sourceType == VARCHAR) { + soruceCastPrecision = sourcePrecision == 0 + ? literal.length() - 10 + : literal.length() - 9 + sourcePrecision; + } + + // Upsert the provided literal value to a column (that has specified precision) in the source table. + String query = format("MERGE INTO t_{} dst " + + "USING (" + + " SELECT 0 AS ID, '{}'::{} as {}" + + ") as src ON dst.id=src.id " + + "WHEN MATCHED THEN UPDATE SET dst.{}=src.{} " + + "WHEN NOT MATCHED THEN INSERT (id, {}) VALUES (src.id, src.{})", + sourceType.getName(), literal, sqlNameWithPrecision(sourceType, soruceCastPrecision), + sourceColumnName, sourceColumnName, sourceColumnName, sourceColumnName, sourceColumnName); + + assertQuery(query) + .withTimeZoneId(ZoneOffset.UTC) + .returnSomething() + .check(); + } + + RelDataType targetDataType = Commons.typeFactory().createSqlType(targetType, targetPrecision); + ColumnType expectColumnType = TypeUtils.columnType(targetDataType); + String expr = sourceType.getFamily() == targetType.getFamily() + ? sourceColumnName + : format("{}::{}", sourceColumnName, sqlNameWithPrecision(targetType, targetPrecision)); + + int rowNum = rowsCounter.incrementAndGet(); + + // Insert value from the source table (with id = 0) to target table column. + { + assertQuery(format("INSERT INTO t_{} (ID, {}) SELECT {}, {} FROM t_{} WHERE id=0", + targetType.getName(), targetColumnName, rowNum, expr, sourceType.getName())) + .withTimeZoneId(ZoneOffset.UTC) + .returnSomething() + .check(); + + assertQuery(format("SELECT {} FROM t_{} WHERE id={}", targetColumnName, targetType.getName(), rowNum)) + .withTimeZoneId(ZoneOffset.UTC) + .columnMetadata(new MetadataMatcher().type(expectColumnType).precision(targetPrecision)) + .returnMatched(matcher) + .check(); + } + + // Update value in the target column. + { + assertQuery(format("UPDATE t_{} t0 SET t0.{}=(SELECT {} FROM t_{} WHERE id=0) WHERE t0.id={}", + targetType.getName(), targetColumnName, expr, sourceType.getName(), rowNum)) + .withTimeZoneId(ZoneOffset.UTC) + .returnSomething() + .check(); + + assertQuery(format("SELECT {} FROM t_{} WHERE id={}", targetColumnName, targetType.getName(), rowNum)) + .withTimeZoneId(ZoneOffset.UTC) + .columnMetadata(new MetadataMatcher().type(expectColumnType).precision(targetPrecision)) + .returnMatched(matcher) + .check(); + } + } + + private static Stream<Arguments> dmlCastArgs() { + return castArgs() + .flatMap(args -> PRECISIONS.stream() + .map(sourcePrecision -> new DmlArgs(args, sourcePrecision).toArgs()) + ); + } + + private static Stream<SelectArgs> castArgs() { + return Stream.of( + // VARCHAR => TIME + new SelectArgs(VARCHAR, "00:00:00.999999999", TIME, 0, time("00:00:00")), + new SelectArgs(VARCHAR, "00:00:00.999999999", TIME, 1, time("00:00:00.9")), + new SelectArgs(VARCHAR, "00:00:00.999999999", TIME, 2, time("00:00:00.99")), + new SelectArgs(VARCHAR, "00:00:00.999999999", TIME, 3, time("00:00:00.999")), + new SelectArgs(VARCHAR, "00:00:00.999999999", TIME, 6, time("00:00:00.999")), + new SelectArgs(VARCHAR, "00:00:00.999999999", TIME, 9, time("00:00:00.999")), + + // VARCHAR => TIMESTAMP + new SelectArgs(VARCHAR, "2024-01-01 00:00:00.999999999", TIMESTAMP, 0, dateTime("2024-01-01 00:00:00")), + new SelectArgs(VARCHAR, "2024-01-01 00:00:00.999999999", TIMESTAMP, 1, dateTime("2024-01-01 00:00:00.9")), + new SelectArgs(VARCHAR, "2024-01-01 00:00:00.999999999", TIMESTAMP, 2, dateTime("2024-01-01 00:00:00.99")), + new SelectArgs(VARCHAR, "0024-01-01 00:00:00.999999999", TIMESTAMP, 3, dateTime("0024-01-01 00:00:00.999")), + new SelectArgs(VARCHAR, "2024-01-01 00:00:00.999999999", TIMESTAMP, 6, dateTime("2024-01-01 00:00:00.999")), + new SelectArgs(VARCHAR, "2024-01-01 00:00:00.999999999", TIMESTAMP, 9, dateTime("2024-01-01 00:00:00.999")), + + // VARCHAR => TIMESTAMP_LTZ + new SelectArgs(VARCHAR, "2024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 0, instant("2024-01-01 00:00:00")), + new SelectArgs(VARCHAR, "2024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 1, instant("2024-01-01 00:00:00.9")), + new SelectArgs(VARCHAR, "2024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 2, instant("2024-01-01 00:00:00.99")), + new SelectArgs(VARCHAR, "0024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 3, instant("0024-01-01 00:00:00.999")), + new SelectArgs(VARCHAR, "2024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 6, instant("2024-01-01 00:00:00.999")), + new SelectArgs(VARCHAR, "2024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 9, instant("2024-01-01 00:00:00.999")), + + // TIMESTAMP => TIME + new SelectArgs(TIMESTAMP, "2024-01-01 00:00:00.999999999", TIME, 0, time("00:00:00")), + new SelectArgs(TIMESTAMP, "2024-01-01 00:00:00.999999999", TIME, 1, time("00:00:00.9")), + new SelectArgs(TIMESTAMP, "2024-01-01 00:00:00.999999999", TIME, 2, time("00:00:00.99")), + new SelectArgs(TIMESTAMP, "0024-01-01 00:00:00.999999999", TIME, 3, time("00:00:00.999")), + new SelectArgs(TIMESTAMP, "2024-01-01 00:00:00.999999999", TIME, 6, time("00:00:00.999")), + new SelectArgs(TIMESTAMP, "2024-01-01 00:00:00.999999999", TIME, 9, time("00:00:00.999")), + + // TIMESTAMP => TIMESTAMP + new SelectArgs(TIMESTAMP, "2024-01-01 00:00:00.999999999", TIMESTAMP, 0, dateTime("2024-01-01 00:00:00")), + new SelectArgs(TIMESTAMP, "2024-01-01 00:00:00.999999999", TIMESTAMP, 1, dateTime("2024-01-01 00:00:00.9")), + new SelectArgs(TIMESTAMP, "2024-01-01 00:00:00.999999999", TIMESTAMP, 2, dateTime("2024-01-01 00:00:00.99")), + new SelectArgs(TIMESTAMP, "0024-01-01 00:00:00.999999999", TIMESTAMP, 3, dateTime("0024-01-01 00:00:00.999")), + new SelectArgs(TIMESTAMP, "2024-01-01 00:00:00.999999999", TIMESTAMP, 6, dateTime("2024-01-01 00:00:00.999")), + new SelectArgs(TIMESTAMP, "2024-01-01 00:00:00.999999999", TIMESTAMP, 9, dateTime("2024-01-01 00:00:00.999")), + + // TIMESTAMP => TIMESTAMP_LTZ + new SelectArgs(TIMESTAMP, "2024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 0, instant("2024-01-01 00:00:00")), + new SelectArgs(TIMESTAMP, "2024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 1, instant("2024-01-01 00:00:00.9")), + new SelectArgs(TIMESTAMP, "2024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 2, instant("2024-01-01 00:00:00.99")), + new SelectArgs(TIMESTAMP, "0024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 3, instant("0024-01-01 00:00:00.999")), + new SelectArgs(TIMESTAMP, "2024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 6, instant("2024-01-01 00:00:00.999")), + new SelectArgs(TIMESTAMP, "2024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 9, instant("2024-01-01 00:00:00.999")), + + // TIMESTAMP_LTZ => TIME + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "2024-01-01 00:00:00.999999999", TIME, 0, time("00:00:00")), + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "2024-01-01 00:00:00.999999999", TIME, 1, time("00:00:00.9")), + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "2024-01-01 00:00:00.999999999", TIME, 2, time("00:00:00.99")), + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "0024-01-01 00:00:00.999999999", TIME, 3, time("00:00:00.999")), + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "2024-01-01 00:00:00.999999999", TIME, 6, time("00:00:00.999")), + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "2024-01-01 00:00:00.999999999", TIME, 9, time("00:00:00.999")), + + // TIMESTAMP_LTZ => TIMESTAMP + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "2024-01-01 00:00:00.999999999", + TIMESTAMP, 0, dateTime("2024-01-01 00:00:00")), + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "2024-01-01 00:00:00.999999999", + TIMESTAMP, 1, dateTime("2024-01-01 00:00:00.9")), + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "2024-01-01 00:00:00.999999999", + TIMESTAMP, 2, dateTime("2024-01-01 00:00:00.99")), + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "0024-01-01 00:00:00.999999999", + TIMESTAMP, 3, dateTime("0024-01-01 00:00:00.999")), + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "2024-01-01 00:00:00.999999999", + TIMESTAMP, 6, dateTime("2024-01-01 00:00:00.999")), + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "2024-01-01 00:00:00.999999999", + TIMESTAMP, 9, dateTime("2024-01-01 00:00:00.999")), + + // TIMESTAMP_LTZ => TIMESTAMP_LTZ + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "2024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 0, instant("2024-01-01 00:00:00")), + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "2024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 1, instant("2024-01-01 00:00:00.9")), + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "2024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 2, instant("2024-01-01 00:00:00.99")), + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "0024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 3, instant("0024-01-01 00:00:00.999")), + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "2024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 6, instant("2024-01-01 00:00:00.999")), + new SelectArgs(TIMESTAMP_WITH_LOCAL_TIME_ZONE, "2024-01-01 00:00:00.999999999", + TIMESTAMP_WITH_LOCAL_TIME_ZONE, 9, instant("2024-01-01 00:00:00.999")), + + // TIME => TIME + new SelectArgs(TIME, "00:00:00.999999999", TIME, 0, time("00:00:00")), + new SelectArgs(TIME, "00:00:00.999999999", TIME, 1, time("00:00:00.9")), + new SelectArgs(TIME, "00:00:00.999999999", TIME, 2, time("00:00:00.99")), + new SelectArgs(TIME, "00:00:00.999999999", TIME, 3, time("00:00:00.999")), + new SelectArgs(TIME, "00:00:00.999999999", TIME, 6, time("00:00:00.999")), + new SelectArgs(TIME, "00:00:00.999999999", TIME, 9, time("00:00:00.999")), + + // TIME => TIMESTAMP (only time part are verified) + new SelectArgs(TIME, "00:00:00.999999999", TIMESTAMP, 0, dateTime("9999-01-01 00:00:00")), + new SelectArgs(TIME, "00:00:00.999999999", TIMESTAMP, 1, dateTime("9999-01-01 00:00:00.9")), + new SelectArgs(TIME, "00:00:00.999999999", TIMESTAMP, 2, dateTime("9999-01-01 00:00:00.99")), + new SelectArgs(TIME, "00:00:00.999999999", TIMESTAMP, 3, dateTime("9999-01-01 00:00:00.999")), + new SelectArgs(TIME, "00:00:00.999999999", TIMESTAMP, 6, dateTime("9999-01-01 00:00:00.999")), + new SelectArgs(TIME, "00:00:00.999999999", TIMESTAMP, 9, dateTime("9999-01-01 00:00:00.999")), + + // TIME => TIMESTAMP_WITH_LOCAL_TIME_ZONE (only nanos are verified) + new SelectArgs(TIME, "00:00:00.999999999", TIMESTAMP_WITH_LOCAL_TIME_ZONE, 0, instant("9999-01-01 00:00:00")), Review Comment: CAST TIME -> TIMESTAMP it should use current_date for year, month, day. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: notifications-unsubscr...@ignite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
