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
> 17 мая 2022 г., в 08:36, Courtney Robinson <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
