rand() in a semi-join subquery is checked on outer rows

With semi-join materialization, this query returns 0. The correct result is 1.

  create table t1 (a int);

  insert into t1 values (1),(2),(3),(100);

  create table t2 (c int, d int);

  insert into t2 select 1, 1 from seq_1_to_80;

  set optimizer_switch='firstmatch=off';

  select count(*) from t1

  where t1.a in (select c from t2 where rand(1) < 0.09);

The subquery checks rand(1) < 0.09 once for each of the 80 rows of t2. Since we can choose the seed for rand, and with rand(1), we know that its first four results are not below 0.09; however, the fifth is, so c=1 is in the subquery result with t1.a=1 matching.

IN-to-semijoin conversion moves the subquery's WHERE into the parent's WHERE. There, rand(1) < 0.09 doesn't reference any table columns. The optimizer attaches such a conjunct to the last toplevel table in the join order. Then, with SJ-Materialization, the join order becomes t1 followed by <subquery2>, with t2 is inside the materialization nest. The conjunct is attached to t1 but t2 gets no condition. It is checked once for each of the four rows of t1, and all four values fail the condition, so every row of t1 is rejected.

Changing the condition to rand(1) < 0.09 + 0*t2.d leaves its value unchanged, but the conjunct now reads a column of t2. It is attached to t2, checked 80 times, and the query returns 1. With optimizer_switch='semijoin=off' the subquery is not converted to a semijoin. It remains a separate SELECT and the WHERE condition isn't moved up. t2 is the only table of that subquery, so the WHERE condition rand(1) < 0.09 attaches to t2. The condition is checked once for each of the 80 rows of t2 and returns 1 (the correct result).

The repro case needs only firstmatch=off. With default settings the optimizer picks FirstMatch, which returns 1 for this query.