Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Sat, Feb 13, 2021, at 12:35, Joel Jacobson wrote:
>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 think I've figured this one out.

It looks like the ANY() case converts the float4-value to numeric and then compare it with the numeric-value,
while in the <<@ case converts the numeric-value to float4 and then compare it with the float4-value.

Since '285970053'::numeric::float4 = '285970053'::float4 is TRUE,
while '285970053'::float4::numeric = '285970053'::numeric is FALSE,
this gives a different result.

Is it documented somewhere which type is picked as the type for the comparison?

"The common type is selected following the same rules as for UNION and related constructs (see Section 10.5)."
(https://www.postgresql.org/docs/current/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC)

SELECT (SELECT '285970053'::numeric UNION SELECT '285970053'::float4) = '285970053'::float4;
?column?
----------
t
(1 row)

Apparently float4 is selected as the common type according to these rules.

So the <<@ operator seems to be doing the right thing. But in the ANY() case, it seems to convert the float4 element in the float4[] array to numeric, and then compare the numeric values.

I can see how this is normal and expected, but it was a bit surprising to me at first.

/Joel