MDEV-21104 Wrong result (extra rows and wrong values) with incremental BNLH

This bug could affect multi-way join queries with embedded outer joins that
contained a conjunctive IS NULL predicate over a non-nullable column from
inner table of an outer join. The predicate could occur in WHERE condition
or in ON condition. Due to this bug a wrong result set could be returned by
the query. The bug manifested itself only when join buffers were employed
for join operations.

The problem appeared because
- a bug in the function JOIN_CACHE::get_match_flag_by_pos that not always
  returned proper match flags for embedding outer joins stored together
  with table rows put a join buffer.
- bug in the function JOIN_CACHE::join_matching_records that not always
  correctly determined that a row from the buffer could be skipped due
  to applied 'not_exists' optimization.
Example:
  SELECT * FROM t1 LEFT JOIN ((t2 LEFT JOIN t3 ON c = d) JOIN t4) ON b = e
    WHERE e IS NULL;

The patch introduces a new function that finds the match flag for a record
from join buffer specifying the buffer where this flag has to be found.
The function is called JOIN_CACHE::get_match_flag_by_pos_from_join_buffer().
Now this function rather than JOIN_CACHE::get_match_flag_by_pos() is used
in JOIN_CACHE::skip_if_matched() to check whether a record from the join
buffer must be ignored when extending the record by null complements.
Also the code of the function JOIN_CACHE::skip_if_not_needed_match() has
been changed. The function checks whether a record from the join buffer
still may produce some useful extensions.
Also some clarifying comments has been added.

Approved by monty@mariadb.com.

1 files changed, 10 insertions, 1 deletions

diff --git a/sql/sql_join_cache.h b/sql/sql_join_cache.h
index 1cbc6acfd79..456991037d1 100644
--- a/sql/sql_join_cache.h
+++ b/sql/sql_join_cache.h
@@ -206,7 +206,9 @@ protected:
 
   /* 
     This flag indicates that records written into the join buffer contain
-    a match flag field. The flag must be set by the init method. 
+    a match flag field. The flag must be set by the init method.
+    Currently any implementation of the virtial init method calls
+    the function JOIN_CACHE::calc_record_fields() to set this flag.
   */
   bool with_match_flag; 
   /*
@@ -646,6 +648,13 @@ public:
   /* Shall return the value of the match flag for the positioned record */
   virtual enum Match_flag get_match_flag_by_pos(uchar *rec_ptr);
 
+  /*
+    Shall return the value of the match flag for the positioned record
+    from the join buffer attached to the specified table
+  */
+  virtual enum Match_flag
+    get_match_flag_by_pos_from_join_buffer(uchar *rec_ptr, JOIN_TAB *tab);
+
   /* Shall return the position of the current record */
   virtual uchar *get_curr_rec() { return curr_rec_pos; }