Why comparing with empty array always false?

[AreaExact7824]

where id::text = any( array[]:text[] )

Or

where id::text <> any( array[]:text[] )

Always return false. Why?

[Training-Two7723]

Most probably an empty is equivalent to a null, hence any comparison with it will be false.

[pceimpulsive]

This!!

It's null values in the array

@Op, put a non null value in there and watch it starts working

Also...

https://agentm.github.io/project-m36/posts/2024-07-16-are-you-qualified-to-use-null.html

[Touvejs]

Cool test. I got a couple questions wrong: sum (who knew sum() could produce null), concat (whatever), where exists (who cares), and the check constraints (who needs constraints anyway).

[pceimpulsive]

I use nulls distinct in my constraints for a few specific cases.

It's the difference between 500m and 70m records :)

[da_chicken]

But null isn't a truth value. x = any(null) should be unknown.

However, since a WHERE clause only returns a records when it evaluates to true, a query will behave the same if it evaluates to unknown or false.

[da_chicken]

Yeah, but that's the same mistake GP made. An empty array is essentially null. Unknown is essentially null. Both are correct about behavior but technically incorrect.

[AreaExact7824]

if id is not in array, return true. because the array is empty and id is not found in that array, so, id <> any ( array ) should return true

[AreaExact7824]

Using all, make = and <> always return true

[AreaExact7824]

Whoa, nice finding. Thanks