[IGNITE-16806] Cache put/SQL table insert fails if SQL index created and LocalDateTime is used as value.

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 2.14
• Labels: ise ise.lts
• Release Note: Fixed insertion of values with java LocalDate, LocalTime and LocalDateTime types via SQL.
• Ignite Flags: Release Notes Required
• Assignee: Mikhail Petrov
• Reporter: Mikhail Petrov
• Created: 06/Apr/22 16:11
• Updated: 12/Oct/22 20:16
• Resolved: 12/May/22 12:41

Description

Reproducer:

/** */
public class LocalDateIndexTest extends AbstractIndexingCommonTest {
    /** */
    @Test
    public void test() throws Exception {
        IgniteEx ignite = startGrids(2);

        SqlFieldsQuery qry = new SqlFieldsQuery(
            "CREATE TABLE DATA (STR VARCHAR PRIMARY KEY, LOCDATETIME TIMESTAMP) WITH" +
                " \"KEY_TYPE=java.lang.String" +
                ", VALUE_TYPE=org.apache.ignite.internal.processors.query.LocalDateIndexTest$Data" +
                ", CACHE_NAME=" + DEFAULT_CACHE_NAME + "\"");

        ignite.context().query().querySqlFields(qry, false).getAll();

        qry = new SqlFieldsQuery("CREATE INDEX TEST_IDX ON DATA(LOCDATETIME DESC);");

        ignite.context().query().querySqlFields(qry, false).getAll();

//        ignite.cache(DEFAULT_CACHE_NAME).put("0", new Data("0", LocalDateTime.MAX));

        qry = new SqlFieldsQuery("INSERT INTO DATA(_key, str, locDateTime) values(?, ?, ?)").setArgs("0", "0", LocalDateTime.MAX);

        ignite.context().query().querySqlFields(qry, false).getAll();
    }

    public static class Data implements Serializable {
        /** Serial version UID. */
        private static final long serialVersionUID = 1L;

        /** */
        public String str;

        /** */
        public LocalDateTime locDateTime;

        /** */
        public Data(String str, LocalDateTime locDateTime) {
            this.str = str;
            this.locDateTime = locDateTime;
        }
    }
}

Exception:

class org.apache.ignite.internal.processors.query.IgniteSQLException: Type for a column 'LOCDATETIME' is not compatible with index definition. Expected 'Timestamp', actual type 'LocalDateTime'

	at org.apache.ignite.internal.processors.query.QueryTypeDescriptorImpl.validateIndexes(QueryTypeDescriptorImpl.java:735)
	at org.apache.ignite.internal.processors.query.QueryTypeDescriptorImpl.validateKeyAndValue(QueryTypeDescriptorImpl.java:606)
	at org.apache.ignite.internal.processors.query.h2.dml.UpdatePlan.processRow(UpdatePlan.java:295)
	at org.apache.ignite.internal.processors.query.h2.dml.DmlUtils.dmlDoInsert(DmlUtils.java:212)
	at org.apache.ignite.internal.processors.query.h2.dml.DmlUtils.processSelectResult(DmlUtils.java:185)
	at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeUpdateNonTransactional(IgniteH2Indexing.java:2902)
	at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeUpdate(IgniteH2Indexing.java:2747)
	at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeUpdateDistributed(IgniteH2Indexing.java:2673)
	at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeDml(IgniteH2Indexing.java:1263)
	at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1185)
	at org.apache.ignite.internal.processors.query.GridQueryProcessor$2.applyx(GridQueryProcessor.java:3005)
	at org.apache.ignite.internal.processors.query.GridQueryProcessor$2.applyx(GridQueryProcessor.java:2988)
	at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
	at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:3650)
	at org.apache.ignite.internal.processors.query.GridQueryProcessor.lambda$querySqlFields$3(GridQueryProcessor.java:3022)
	at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuerySafe(GridQueryProcessor.java:3094)
	at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2982)
	at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2909)
	at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2882)
	at org.apache.ignite.internal.processors.query.LocalDateIndexTest.test(LocalDateIndexTest.java:50)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.apache.ignite.testframework.junits.GridAbstractTest$6.run(GridAbstractTest.java:2431)
	at java.lang.Thread.run(Thread.java:748)

The root cause of this issue:
During cache insertion (whether through SQL or the cache API), we check that the type of the inserted value matches the type of the table column. The same is done for indexed columns (see QueryTypeDescriptorImpl#validateProps and QueryTypeDescriptorImpl#validateIndexes) As QueryTypeDescriptorImpl#validateProps validation is disabled by default the mentioned above reproducer is not affected by this check. It means that if this check is enabled we could not insert LocalDateTime instance to Timestamp column even indexes are not created.

H2 determines locDateTime column type as java.sql.Timestamp which does not match with java.time.LocalDateTime class and QueryTypeDescriptorImpl#validateIndexes validation fails.

The problem also reproduces for DATE and TIME SQL types if LocalDate or LocalTime is used as inserted value.

Issue Links

• links to: GitHub Pull Request #9988