Re: Replace IN VALUES with ANY in WHERE clauses during optimization

Hi!

On 04.10.2024 12:05, Andrei Lepikhov wrote:
> We also have an implementation of VALUES -> ARRAY transformation.
> Because enterprises must deal with users' problems, many of these
> users employ automatically generated queries.
> Being informed very well of the consensus about that stuff, we've
> designed it as a library. But, looking into the code now, I see that
> it only needs a few cycles if no one 'x IN VALUES' expression is
> presented in the query. Who knows? It may be OK for the core.
> So, I've rewritten the code into the patch - see it in the attachment.
>
> The idea is quite simple - at the same place as
> convert_ANY_sublink_to_join, we can test the SubLink on proper VALUES
> RTE and perform the transformation if it's convertible.

I updated the patch due to the problem with the coercion types for both
sides of the expression.

We must find a common type for both leftop of the expression and rightop
including constants for correct transformation, and at the same time
check that the resulting types are compatible.

To do this we find an operator for the two input types if it is
possible, and also remember the target types for the left and right
sides, and after that make a coercion.

This processing is only needed in cases where we are not working with
parameters since the final type is not specified for the parameters.

--
Regards,
Alena Rybakina
Postgres Professional