Thank you for the explanation and the reference to the Wang Ke's post. So this is a bug related to the equivalence transfer optimization. This query is different from Wang Ke's one in the ways that it compares the two tables in its subquery, where the parent query only has one single table in the FROM clause so it uses the whereShortCut() path. The root problem is the same.