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> 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
>
>
>
> 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> 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>
>> написал(а):
>>
>> 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
> http://zcourts.com
> 020 3287 0961
>
>
>
--
Courtney Robinson
court...@crlog.info
http://zcourts.com
020 3287 0961
