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

Hi, Alena!

On Sat, Mar 1, 2025 at 1:39 PM Alena Rybakina <a(dot)rybakina(at)postgrespro(dot)ru> wrote:
> On 09.02.2025 18:38, Alexander Korotkov wrote:
>>
>> Also, aren't we too restrictive while requiring is_simple_values_sequence()?
>> For instance, I believe cases like this (containing Var) could be transformed too.
>>
>> select * from t t1, lateral (select * from t t2 where t2.i in (values (t1.i), (1)));
>
>
> I added it and attached a patch with diff file. To be honest, I didn't find queries except for var with volatile functions where the transform can't be applied.
>
> I removed the function volatility check that I added in the previous version, since we already check it in is_simple_values_sequence.
>
> I'm not sure about only cases where var can refer to something outside available_rels list but I couldn't come up with an example where that's possible, what do you think?
>
> Considering it again, I think we can't face problems like that because we don't work with join.
>
> I attached a diff file as a difference with the 3rd version of the patch, when we did not consider the values with var for transformation.

I take detailed look at makeSAOPArrayExpr() function, which is much
more complex than corresponding fragment from
match_orclause_to_indexcol(). And I found it to be mostly wrong. We
are working in post parse-analyze stage. That means it's too late to
do type coercion or lookup operator by name. We have already all the
catalog objects nailed down. In connection with that, second argument
of OpExpr shouldn't be ignored as it might contain amrelevant type
cast. I think I've fixed the most of them problems in the attached
patchset.

------
Regards,
Alexander Korotkov
Supabase