Why Hash Join optimization applies even for Cartesian product?

Jacky Wang <[email protected]> Thu, 13 Oct 2022 18:08:14 -0700 (PDT)
Newsgroups comp.databases.mysql
Message-ID <[email protected]>
In MySQL 8.0 Reference Manual, Hash Join Optimization(https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html), it says that a hash join is also applied for a Cartesian product and Inner non-equi-join can also be optimized using hash joins.

I am curious that how does Hash Join make such queries faster? No any join condition or no equi-join in these cases.

mysql> EXPLAIN FORMAT=TREE
    -> SELECT *
    ->     FROM t1
    ->     JOIN t2
    ->     WHERE t1.c2 > 50\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join  (cost=0.70 rows=1)
    -> Table scan on t2  (cost=0.35 rows=1)
    -> Hash
        -> Filter: (t1.c2 > 50)  (cost=0.35 rows=1)
            -> Table scan on t1  (cost=0.35 rows=1)


mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t1.c1 < t2.c1)  (cost=4.70 rows=12)
    -> Inner hash join (no condition)  (cost=4.70 rows=12)
        -> Table scan on t2  (cost=0.08 rows=6)
        -> Hash
            -> Table scan on t1  (cost=0.85 rows=6)


Or it is just because MySQL no longer to support BNL?

> Beginning with MySQL 8.0.20, support for block nested loop is removed, and the server employs a hash join wherever a block nested loop would have been used previously.