ANY does not respect collation

[smonkewitz]

What happens?

ANY doesn't take the collation into account.

To Reproduce

create table test(s text[]);
insert into test values (['XYZ', 'ABC']);
select * from test;

┌────────────┐
│     s      │
│ varchar[]  │
├────────────┤
│ [XYZ, ABC] │
└────────────┘

select count(*) from test where 'abc' collate nocase = any(s);

┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│            0 │
└──────────────┘

But:

select count(*) from test where exists(select 1 from unnest(s) t(unnested) where 'abc' collate nocase = unnested);

┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│            1 │
└──────────────┘

Setting the default collation to be case insensitive does not help:

SET default_collation = NOCASE;
select count(*) from test where 'abc' = any(s);

┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│            0 │
└──────────────┘

OS:

macOS 13.6 aarch64, nixOS 23.05 x86-64

DuckDB Version:

0.10.1,0.10.2

DuckDB Client:

CLI

Full Name:

Serge Monkewitz

Affiliation:

Arista Networks

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[smonkewitz]

The same issue arises when using ALL instead of ANY, or != instead of =.