0

I am trying to run a query on Ignite cluster which has IN clause. It is as below.

SELECT _key,_val FROM ORDER_ERRORS WHERE ORDER_TYPES in (?) AND STATUS='OPEN' limit ? offset ?

I am writing query code as below.

public List<OrderError> getOpenErrors(List<String> orderTypes, int offset, int limit) {
    SqlFieldsQuery sqlFieldsQuery = new SqlFieldsQuery(FIND_REC_QUERY);
    sqlFieldsQuery.setArgs(orderTypes.toArray(), limit, offset);
    FieldsQueryCursor<List<?>> res = orderErrorsCache.query(sql);

This returns me 0 records. Now when I remove either orderTypes or limit and offset from that query. It returns me some records. But a combination of all 3 gives me nothing. Surprisingly I have a query before this, from which I obtain number of records for pagination. That query runs fine. Which is like below.

SELECT count(1) FROM ORDER_ERRORS WHERE ORDER_TYPES in (?) and STATUS='OPEN'

Now here there's a single parameter. And I run this query as below, and this works!

SqlFieldsQuery sqlFieldsQuery = new SqlFieldsQuery(TOTAL_REC_QUERY);
sqlFieldsQuery.setArgs(orderTypes.toArray());
logger.info("Finding record set size: {}", sqlFieldsQuery);
FieldsQueryCursor<List<?>> res = orderErrorsCache.query(sqlFieldsQuery);

What is going on here? What am I missing? Or is there something amiss with Ignite itself? Please help.

Ignite version: 2.9.0

Shades88
  • 7,934
  • 22
  • 88
  • 130
  • 1
    your code will replace ? with '123,223,224,225' as complete string and will so not find a match. – nbk Sep 22 '22 at 12:10

1 Answers1

0

SqlFieldsQuery in Apache Ignite works more or less like a prepared statement. It looks like you are missing the part that there's only one placeholder in your query (that's why a single argument works). You need it to match with the argument array size.

In general a pretty similar question has already been answered here.

Vladimir Pligin
  • 1,547
  • 11
  • 18