Wrong result with non-default JOIN_CACHE_LEVEL=[4|5] and USE_STAT_TABLES=[NEVER|COMPLEMENTARY]

MariaDB [test]> SELECT SUM( l_partkey ) 

FROM region JOIN nation ON ( r_regionkey = n_regionkey ) 

LEFT JOIN supplier ON ( s_nationkey = n_nationkey ) JOIN partsupp ON ( s_suppkey = ps_suppkey ) 

RIGHT JOIN lineitem ON ( ps_partkey = l_partkey AND ps_suppkey = l_suppkey ) 

JOIN orders ON ( l_orderkey = o_orderkey ) 

WHERE o_comment IS NOT NULL OR n_comment IS NOT NULL AND ps_availqty = l_quantity;

+------------------+

| SUM( l_partkey ) |

+------------------+

|         15457500 |

+------------------+

1 row in set (0.657 sec)

MariaDB [test]> set USE_STAT_TABLES=DEFAULT, JOIN_BUFFER_SPACE_LIMIT=DEFAULT, JOIN_CACHE_LEVEL=DEFAULT;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> SELECT SUM( l_partkey ) 

FROM region JOIN nation ON ( r_regionkey = n_regionkey ) 

LEFT JOIN supplier ON ( s_nationkey = n_nationkey ) JOIN partsupp ON ( s_suppkey = ps_suppkey ) 

RIGHT JOIN lineitem ON ( ps_partkey = l_partkey AND ps_suppkey = l_suppkey ) 

JOIN orders ON ( l_orderkey = o_orderkey ) 

WHERE o_comment IS NOT NULL OR n_comment IS NOT NULL AND ps_availqty = l_quantity;

+------------------+

| SUM( l_partkey ) |

+------------------+

|             6183 |

+------------------+

1 row in set (0.002 sec)

MariaDB [test]> explain extended SELECT SUM( l_partkey ) FROM region JOIN nation ON ( r_regionkey = n_regionkey ) LEFT JOIN supplier ON ( s_nationkey = n_nationkey ) JOIN partsupp ON ( s_suppkey = ps_suppkey ) RIGHT JOIN lineitem ON ( ps_partkey = l_partkey AND ps_suppkey = l_suppkey ) JOIN orders ON ( l_orderkey = o_orderkey ) WHERE o_comment IS NOT NULL OR n_comment IS NOT NULL AND ps_availqty = l_quantity;

+------+-------------+----------+------------+--------------------------------------------+-----------------------------+---------+-------------------------------------------------+------+----------+----------------------------------------------------------------------+

| id   | select_type | table    | type       | possible_keys                              | key                         | key_len | ref                                             | rows | filtered | Extra                                                                |

+------+-------------+----------+------------+--------------------------------------------+-----------------------------+---------+-------------------------------------------------+------+----------+----------------------------------------------------------------------+

|    1 | SIMPLE      | orders   | ALL        | PRIMARY                                    | NULL                        | NULL    | NULL                                            | 150  |   100.00 |                                                                      |

|    1 | SIMPLE      | lineitem | hash_ALL   | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | #hash#PRIMARY               | 4       | test.orders.o_orderkey                          | 586  |     0.51 | Using join buffer (flat, BNLH join)                                  |

|    1 | SIMPLE      | supplier | hash_index | PRIMARY,i_s_nationkey                      | #hash#PRIMARY:i_s_nationkey | 4:5     | test.lineitem.l_suppkey                         | 1    |   100.00 | Using where; Using index; Using join buffer (incremental, BNLH join) |

|    1 | SIMPLE      | nation   | hash_ALL   | PRIMARY,i_n_regionkey                      | #hash#PRIMARY               | 4       | test.supplier.s_nationkey                       | 25   |     4.00 | Using where; Using join buffer (incremental, BNLH join)              |

|    1 | SIMPLE      | partsupp | hash_ALL   | PRIMARY,i_ps_partkey,i_ps_suppkey          | #hash#PRIMARY               | 8       | test.lineitem.l_partkey,test.lineitem.l_suppkey | 20   |     5.00 | Using where; Using join buffer (incremental, BNLH join)              |

|    1 | SIMPLE      | region   | hash_ALL   | PRIMARY                                    | #hash#PRIMARY               | 4       | test.nation.n_regionkey                         | 5    |    20.00 | Using join buffer (incremental, BNLH join)                           |

+------+-------------+----------+------------+--------------------------------------------+-----------------------------+---------+-------------------------------------------------+------+----------+----------------------------------------------------------------------+

6 rows in set, 1 warning (0.000 sec)

MariaDB [test]> show warnings;

+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |

+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Note  | 1003 | select sum(`test`.`lineitem`.`l_partkey`) AS `SUM( l_partkey )` from `test`.`lineitem` left join (`test`.`region` join `test`.`nation` join `test`.`supplier` join `test`.`partsupp`) on(`test`.`partsupp`.`ps_partkey` = `test`.`lineitem`.`l_partkey` and `test`.`supplier`.`s_suppkey` = `test`.`lineitem`.`l_suppkey` and `test`.`partsupp`.`ps_suppkey` = `test`.`lineitem`.`l_suppkey` and `test`.`nation`.`n_nationkey` = `test`.`supplier`.`s_nationkey` and `test`.`region`.`r_regionkey` = `test`.`nation`.`n_regionkey` and `test`.`lineitem`.`l_suppkey` is not null and `test`.`supplier`.`s_nationkey` is not null and `test`.`lineitem`.`l_partkey` is not null and `test`.`lineitem`.`l_suppkey` is not null and `test`.`nation`.`n_regionkey` is not null) join `test`.`orders` where `test`.`lineitem`.`l_orderkey` = `test`.`orders`.`o_orderkey` and (`test`.`orders`.`o_comment` is not null or `test`.`nation`.`n_comment` is not null and `test`.`partsupp`.`ps_availqty` = `test`.`lineitem`.`l_quantity`) |

+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.000 sec)