Queries with the following characteristics may produce wrong results due to an incorrectly assigned runtime filter:
- The query option RUNTIME_FILTER_MODE is set to GLOBAL
- The query has an outer join
- A scan on the nullable side of that outer join has a runtime filter with a NULL-checking expression such as COALESCE/IFNULL/CASE
- The latter point imples that there is another join above the outer join with a NULL-checking expression in it's join condition
We expect a count of 0. A count of 8 is incorrect.
- RF000 filters out all rows in scan 01
- In join 03 there are no join matches since the right-hand is empty. All rows from the right-hand side are nulled.
- The join condition in join 04 now satisfies all input rows because every "t2.id" is NULL, so after the COALESCE() the join condition becomes 100 = 100
- Set RUNTIME_FILTER_MODE to LOCAL or OFF