Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Fri, Feb 12, 2021, at 20:56, Mark Rofail wrote:
>Attachments:
>anyarray_anyelement_operators-v2.patch

I've created a quite sophisticated test in PL/pgSQL,
that takes advantage of all the test data produced
by the official PostgreSQL regression test suite.

It goes through all tables and columns, and extracts values
for all the different types it can find.

It then uses these values to probe for differences between

some_value::some_type = ANY(ARRAY[some_other_value]::some_other_type[])
and
some_value::some_type <<@ ARRAY[some_other_value]::some_other_type[]

psql:type-test.sql:165: NOTICE:
========================
144 of 21632 tests failed.
========================

Out of these 144 differences found, this one was really interesting:

psql:type-test.sql:165: WARNING:
SQL queries produced different results:
SELECT '285970053'::pg_catalog."numeric" = ANY(ARRAY['285970053']::pg_catalog.float4[])
false
SELECT '285970053'::pg_catalog."numeric" <<@ ARRAY['285970053']::pg_catalog.float4[]
true

I don't see why one of them returns false and the other true?

If testing for equality:

SELECT '285970053'::pg_catalog.float4 = '285970053'::numeric;

You get "false".

So it looks like ANY() does the right thing here, and <<@ has a problem.

To run, first run the PostgreSQL regression tests, to produce data in the "regression" database.

Then run:

$ psql -f type-test.sql regression

/Joel