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

Hi, Alena!

On Tue, Apr 1, 2025 at 2:11 AM Alena Rybakina <a(dot)rybakina(at)postgrespro(dot)ru>
wrote:
> Yes, I agree with that - this is precisely why we need to call
IncrementVarSublevelsUp() unconditionally for all types.
>
> As you mentioned earlier, Var nodes can be nested more deeply, and
skipping this step could lead to incorrect behavior in those cases. So, now
it works fine)
>
> Thank you for an example.
>
> I analyzed this transformation with various types of values that might be
used in conditions.
>
> First, I verified whether the change would affect semantics, especially
in the presence of NULL elements. The only notable behavior I observed was
> the coercion of NULL to an integer type. However, this behavior remains
the same even without our transformation, so everything is fine.

Thank you for your experiments! I've also rechecked we don't sacrifice
lazy evaluation. But it appears we don't have one anyway.

CREATE FUNCTION my_func() RETURNS text AS $$
BEGIN
RAISE NOTICE 'notice';
RETURN 'b';
END;
$$ LANGUAGE 'plpgsql';

# create table test (val text);
# insert into test values ('a');
# explain analyze select * from test where val in (VALUES ('a'),
(my_func()));
NOTICE: notice
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=0.05..21.26 rows=9 width=64) (actual
time=0.178..0.183 rows=1.00 loops=1)
Hash Cond: (test.val = ("*VALUES*".column1)::text)
Buffers: shared hit=1
-> Seq Scan on test (cost=0.00..18.80 rows=880 width=64) (actual
time=0.045..0.048 rows=1.00 loops=1)
Buffers: shared hit=1
-> Hash (cost=0.03..0.03 rows=2 width=32) (actual time=0.111..0.112
rows=2.00 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=32)
(actual time=0.004..0.065 rows=2.00 loops=1)
Planning Time: 0.250 ms
Execution Time: 0.267 ms
(10 rows)

------
Regards,
Alexander Korotkov
Supabase