Problem in `NOT ANY` Operator

Hi,

We are using Crate DB 4.3.2 and we have an index with a field test_field of type ARRAY(TEXT)

Whenever we are doing a query like
SELECT test_field FROM "test" WHERE (("test_field" IS NOT NULL) AND ( NOT ''=any("test_field"))) LIMIT 100;

It gives us NullPoninterException (Intermittent)

LOGS

y: io.crate.shade.org.postgresql.util.PSQLException: ERROR: Error in NullPointerException
\tat io.crate.shade.org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
\tat io.crate.shade.org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
\tat io.crate.shade.org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
\tat io.crate.shade.org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:442)
\tat io.crate.shade.org.postgresql.jdbc.PgStatement.execute(PgStatement.java:366)
\tat io.crate.shade.org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:308)
\tat io.crate.shade.org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:294)
\tat io.crate.shade.org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:271)
\tat io.crate.shade.org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:225)
\tat com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111)
\tat com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
\tat com.exzeo.spock.crate.dal.CrateQueryExecutor.executeQuery(CrateQueryExecutor.java:18)
\tat com.exzeo.spock.crate.CrateQueryHelperImpl.result(CrateQueryHelperImpl.java:59)
\t... 38 common frames omitted

Also, we tried analyzing the query but that too doesn’t help.

We are still figuring out the root cause of this exception.
Any help would be much appreciated @proddata

Regards,
Sambhav

HI @sambhav,

I tried a quick example locally with some data on 4.5.1 and 4.3.2 and couldn’t reproduce your NullPointerException.

Is my understanding of intermittent NPEs correct that this query sometimes computes and sometimes throws the NPE?

Can you please share the table schema with us and give us a rough indication on how big your dataset is (number of records, min, max, mean length of test_field array, etc)?

Thanks

Is my understanding of intermittent NPEs correct that this query sometimes computes and sometimes throws the NPE?

Yes Correct, Sometimes it works and sometimes it gives Null Pointer

Can you please share the table schema with us and give us a rough indication on how big your dataset is (number of records, min, max, mean length of test_field array, etc)?

Schema

CREATE TABLE IF NOT EXISTS "doc"."test" (
   "test_label" TEXT,
   "test_vis" TEXT,
   "test_field" ARRAY(TEXT),
   "id" BIGINT,
   "test_field2" ARRAY(TEXT),
   "temId" ARRAY(BIGINT)
)
CLUSTERED INTO 4 SHARDS
WITH (
   "allocation.max_retries" = 5,
   "blocks.metadata" = false,
   "blocks.read" = false,
   "blocks.read_only" = false,
   "blocks.read_only_allow_delete" = false,
   "blocks.write" = false,
   codec = 'default',
   column_policy = 'strict',
   "mapping.total_fields.limit" = 1000,
   max_ngram_diff = 1,
   max_shingle_diff = 3,
   number_of_replicas = '0-1',
   refresh_interval = 1000,
   "routing.allocation.enable" = 'all',
   "routing.allocation.total_shards_per_node" = -1,
   "store.type" = 'fs',
   "translog.durability" = 'REQUEST',
   "translog.flush_threshold_size" = 536870912,
   "translog.sync_interval" = 5000,
   "unassigned.node_left.delayed_timeout" = 60000,
   "write.wait_for_active_shards" = '1'
)

Total Number of Records = 369457
Min Length of Text Array = 1
Max Length of Text Array = 10

Hi @sambhav,

I did try to replicate this with your table and 400k records. I did run your query a couple thousand times but could not observe any NPE.

Can you maybe share your dataset with us? Can you give us a rough estimation on how often the NPE occur (1 out of 10 query executions? 1 out of 10k?)?

Thanks

Can you maybe share your dataset with us? Can you give us a rough estimation on how often the NPE occur (1 out of 10 query executions? 1 out of 10k?)?

I will try to reproduce the same (on my local machine) with testing data as this issue is occurring on our QA env.

So I recently checked and hit the same query 10 times every time it is giving NPE now

Hi @sambhav,

were you able to reproduce the NPE when testing locally?

Johannes