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

["Mikhail Petrov (Jira)" <ji...@apache.org>]

Mikhail Petrov created IGNITE-16806:
---------------------------------------

             Summary: Cache put/SQL table insert fails if SQL index created and LocalDateTime is used as value.
                 Key: IGNITE-16806
                 URL: https://issues.apache.org/jira/browse/IGNITE-16806
             Project: Ignite
          Issue Type: Bug
            Reporter: Mikhail Petrov


Reproducer:
{code:java}
/** */
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;
        }
    }
}
{code}

Exception:

{code:java}
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)
{code}

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.

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.






--
This message was sent by Atlassian Jira
(v8.20.1#820001)