Problem in `NOT ANY` Operator

CrateDB
1

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

2

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

3

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

4

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

5

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

6

Hi @sambhav,

were you able to reproduce the NPE when testing locally?

Johannes

7

No, it is not reproducible on local

8

Hi @sambhav,

bummer, thanks for the update though.

Does the NPE still occur on the QA env? If it does are you testing with the same dataset on both QA env and locally?

9

Actually we replace NOT ANY operator and handled the same from code end.

10

It would be really interesting if you could somehow replicate this with test data.
Afaik I remember there might have been a bug with NOT and arrays that has been fixed with 4.5.1 I think, but I would have to look it up again.