Hello, Courtney. I did some investigation.
It seems, current behavior is correct, in general. Looks like passing list of values as a parameter is common issue for JDBC SQL queries [1] [2] [3]. Tried to find formal requirements in JDBC spec but didn’t find clear description :) Anyway, If you are interested in details: H2 engine optimize IN clause of your query to Equals query, because it contains only one item [4]. So in time Ignite receive filter it looks like «find string equals to array» - this leads to the exception after [5]. Calcite engine behave a little bit different and returns empty results for the same query. Didn’t look deeper to explain this. I will close ticket [6] as «Won’t fix». Feel free to reopen it. [1] https://www.baeldung.com/spring-jdbctemplate-in-list [2] https://stackoverflow.com/questions/45696465/how-to-pass-list-of-values-as-a-parameter-to-in-clause-using-jdbc-template <https://stackoverflow.com/questions/45696465/how-to-pass-list-of-values-as-a-parameter-to-in-clause-using-jdbc-template> [3] https://stackoverflow.com/questions/1327074/how-to-execute-in-sql-queries-with-springs-jdbctemplate-effectively <https://stackoverflow.com/questions/1327074/how-to-execute-in-sql-queries-with-springs-jdbctemplate-effectively> [4] https://github.com/h2database/h2database/blob/master/h2/src/main/org/h2/expression/condition/ConditionIn.java#L129 [5] https://issues.apache.org/jira/browse/IGNITE-13056 <https://issues.apache.org/jira/browse/IGNITE-13056> [6] https://issues.apache.org/jira/browse/IGNITE-16991 > 17 мая 2022 г., в 12:16, Courtney Robinson <court...@crlog.info> написал(а): > > Hey, > I just checked and it doesn't seem as if any of the test cases use arrays > with more than 1 element in them. > It also gives the wrong results if I use an array with multiple values. > > > On Tue, 17 May 2022 at 09:56, Николай Ижиков <nizhi...@apache.org > <mailto:nizhi...@apache.org>> wrote: > Hello, Courtney. > > I’m able to reproduce your issue [1] > > Can you, please, confirm - Do you have use-case when memberIds contains more > then one element? > Is it return correct results? > > Before 2.11 SQL query with IN clause and array argument executed and return > some results. > But, in my reproducer results are wrong when I pass more then one element > array. > > Anyway, I will continue investigation of the issue [1] > > // This work as expected. > assertEquals(1, sql( > "SELECT IID FROM T1 WHERE ID IN (?) AND IID = ?", > new Object[] {Arrays.asList("1").toArray(), "1"} > ).size()); > > // And this works OK. > assertEquals(2, sql( > "SELECT IID FROM T1 WHERE ID IN ('1', '4') AND IID = ?", > new Object[] {"1"} > ).size()); > > // Executed without exception but return no results. > assertEquals(2, sql( > "SELECT IID FROM T1 WHERE ID IN (?) AND IID = ?", > new Object[] {Arrays.asList("1", "4").toArray(), "1"}).size() > ); > > [1] https://issues.apache.org/jira/browse/IGNITE-16991 > <https://issues.apache.org/jira/browse/IGNITE-16991> > > > >> 17 мая 2022 г., в 08:36, Courtney Robinson <court...@crlog.info >> <mailto:court...@crlog.info>> написал(а): >> >> Hey Николай, >> >> Java code: >> >> private FieldsQueryCursor<List<?>> doQuery(boolean isMutation, String sql, >> Object... args) { >> var timer = isMutation ? rawMutTimer : rawQryTimer; >> return timer.record(() -> { >> try { >> var query = new SqlFieldsQuery(sql) >> .setTimeout(5, SECONDS) >> //.setDistributedJoins(true) >> .setSchema(PUBLIC_SCHEMA_NAME); >> if (args != null && args.length > 0) { >> query.setArgs(args); >> } >> var res = cache.query(query); >> if (isMutation) { >> ctx.backup(sql, args); >> } >> return res; >> } catch (Exception e) { >> this.rawDBErrCntr.count(); >> if (e.getCause() instanceof CacheStoppedException) { >> log.error("Ignite cache stopped unexpectedly. No further queries are >> possible so must exit. Shutting down node"); >> System.exit(-1); >> } >> if (e instanceof DBException) { >> throw e; >> } else { >> throw new DBException("Unexpected error whilst executing database >> query", e); >> } >> } >> }); >> } >> The call that used to work is: >> var results = repo.query(false, "SELECT HYPI_INSTANCEID, COUNT(HYPI_ID) FROM >> " + >> TABLE_ACC + " WHERE HYPI_ID IN (?) AND HYPI_INSTANCEID=? GROUP BY >> HYPI_INSTANCEID", memberIds.toArray(), instanceId); >> >> We had to change this to: >> List<Object> args = new ArrayList<>(); >> String qs = memberIds.stream() >> .peek(args::add) >> .map(v -> "?") >> .collect(Collectors.joining(",")); >> args.add(instanceId); >> var results = repo.query( >> false, >> "SELECT HYPI_INSTANCEID, COUNT(HYPI_ID) FROM " + >> TABLE_ACC + " WHERE HYPI_ID IN (" + qs + ") AND HYPI_INSTANCEID=? GROUP >> BY HYPI_INSTANCEID", >> args.toArray() >> ); >> >> memberIds is a List<String>. >> repo.query is the public method that will eventually call doQuery after some >> internal stuff. >> >> The table here referred to as TABLE_ACC is >> CREATE TABLE PUBLIC.ACCOUNT ( >> VERIFIED BOOLEAN, >> ENABLED BOOLEAN, >> HYPI_INSTANCEID VARCHAR, >> HYPI_ID VARCHAR, >> USERNAME VARCHAR, >> CONSTRAINT PK_PUBLIC_HYPI_01E8NPNFADNKECH7BR0K5FDE2C_ACCOUNT PRIMARY >> KEY (HYPI_INSTANCEID,HYPI_ID) >> ); >> >> I removed most fields as they're not necessary to reproduce >> >> >> On Fri, 13 May 2022 at 15:24, Николай Ижиков <nizhi...@apache.org >> <mailto:nizhi...@apache.org>> wrote: >> Hello, Courtney. >> >> Can, you, please, send SQL table definition and example of query (java code >> and SQL) that worked on 2.8 and start failing on 2.13. >> >> > This seems like a regression, was this intentional? >> >> Looks like a bug to me. >> Details of your schema and query can help in further investigation. >> >> >>> 10 мая 2022 г., в 10:14, Courtney Robinson <court...@crlog.info >>> <mailto:court...@crlog.info>> написал(а): >>> >>> Hi all, >>> >>> We're looking to do a major upgrade from 2.8.0 to 2.13.0 >>> After the initial upgrade our test suite started failing (about 15% of >>> tests now fail). >>> No other change has been made other than the Ignite version number. >>> >>> org.apache.ignite.internal.processors.query.IgniteSQLException: General >>> error: "class org.apache.ignite.IgniteException: Failed to wrap >>> value[type=17, value=[Ljava.lang.Object;@667eb78]"; SQL statement: >>> SELECT HYPI_INSTANCEID, COUNT(HYPI_ID) FROM >>> hypi_01E8NPNFADNKECH7BR0K5FDE2C_Account WHERE HYPI_ID IN (?) AND >>> HYPI_INSTANCEID=? GROUP BY HYPI_INSTANCEID [50000-197] >>> at >>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:898) >>> at >>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQueryWithTimer(IgniteH2Indexing.java:985) >>> at >>> org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest0(GridMapQueryExecutor.java:471) >>> at >>> org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest(GridMapQueryExecutor.java:284) >>> at >>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.onMessage(IgniteH2Indexing.java:2219) >>> at >>> org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor$1.applyx(GridReduceQueryExecutor.java:157) >>> at >>> org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor$1.applyx(GridReduceQueryExecutor.java:152) >>> at >>> org.apache.ignite.internal.util.lang.IgniteInClosure2X.apply(IgniteInClosure2X.java:38) >>> at >>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.send(IgniteH2Indexing.java:2344) >>> at >>> org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.send(GridReduceQueryExecutor.java:1201) >>> at >>> org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:463) >>> at >>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$7.iterator(IgniteH2Indexing.java:1846) >>> at >>> org.apache.ignite.internal.processors.cache.QueryCursorImpl.iter(QueryCursorImpl.java:102) >>> at >>> org.apache.ignite.internal.processors.cache.query.RegisteredQueryCursor.iter(RegisteredQueryCursor.java:91) >>> at >>> org.apache.ignite.internal.processors.cache.QueryCursorImpl.getAll(QueryCursorImpl.java:124) >>> >>> Investigating this I found that IndexKeyFactory has since been added in a >>> release after 2.8.0. >>> It is the source of the exception >>> throw new IgniteException("Failed to wrap value[type=" + keyType + ", >>> value=" + o + "]"); >>> >>> The key type 17 is ARRAY, defined in `org.h2.value.Value` (ARRAY enum value >>> line 137) >>> Looking further I can see that IndexKeyFactory registers: >>> IndexKeyFactory.register(IndexKeyTypes.DATE, DateIndexKey::new); >>> IndexKeyFactory.register(IndexKeyTypes.TIME, TimeIndexKey::new); >>> IndexKeyFactory.register(IndexKeyTypes.TIMESTAMP, TimestampIndexKey::new);-- >>> >>> And these are the only additional key types registered anywhere in the >>> 2.13.0 code base. >>> >>> Looking further, I found that the problem is wherever we use the `IN` clause >>> In 2.8.0 we had a query like this: >>> DELETE FROM permission_cause WHERE instanceId = ? AND policyId = ? AND >>> rowId IN (?) AND accountId = ? >>> And we would pass in a Java array as the 3rd argument >>> instanceId, policyId, toDelete.toArray(), accountId >>> >>> This would work fine with the toDelete.toArray() >>> Now, we have to change it and expand from IN(?) to IN(?,?,?) putting in as >>> many ? as there are entries in the array and pass in the values >>> individually. >>> >>> This seems like a regression, was this intentional? >>> >>> Best, >>> Courtney >> >> >> >> -- >> Courtney Robinson >> court...@crlog.info <mailto:court...@crlog.info> >> http://zcourts.com <http://zcourts.com/> >> 020 3287 0961 > > > > -- > Courtney Robinson > court...@crlog.info <mailto:court...@crlog.info> > http://zcourts.com <http://zcourts.com/> > 020 3287 0961
