MDEV-28749: restore_prev_nj_state() doesn't update cur_sj_inner_tables correctly

(Try 2) (Cherry-pick back into 10.3)

The code that updates semi-join optimization state for a join order prefix
had several bugs. The visible effect was bad optimization for FirstMatch or
LooseScan strategies: they either weren't considered when they should have
been, or considered when they shouldn't have been.

In order to hit the bug, the optimizer needs to consider several different
join prefixes in a certain order. Queries with "obvious" query plans which
prune all join orders except one are not affected.

Internally, the bugs in updates of semi-join state were:
1. restore_prev_sj_state() assumed that
  "we assume remaining_tables doesnt contain @tab"
  which wasn't true.
2. Another bug in this function: it did remove bits from
   join->cur_sj_inner_tables but never added them.
3. greedy_search() adds tables into the join prefix but neglects to update
   the semi-join optimization state. (It does update nested outer join
   state, see this call:
     check_interleaving_with_nj(best_table)
   but there's no matching call to update the semi-join state.
   (This wasn't visible because most of the state is in the POSITION
    structure which is updated. But there is also state in JOIN, too)

The patch:
- Fixes all of the above
- Adds JOIN::dbug_verify_sj_inner_tables() which is used to verify the
  state is correct at every step.
- Renames advance_sj_state() to optimize_semi_joins().
  = Introduces update_sj_state() which ideally should have been called
    "advance_sj_state" but I didn't reuse the name to not create confusion.

1 files changed, 5 insertions, 3 deletions

diff --git a/sql/opt_subselect.h b/sql/opt_subselect.h
index 509fb370fd7..4744c49799a 100644
--- a/sql/opt_subselect.h
+++ b/sql/opt_subselect.h
@@ -310,9 +310,11 @@ public:
 };
 
 
-void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx,
-                      double *current_record_count, double *current_read_time,
-                      POSITION *loose_scan_pos);
+void optimize_semi_joins(JOIN *join, table_map remaining_tables, uint idx,
+                         double *current_record_count,
+                         double *current_read_time, POSITION *loose_scan_pos);
+void update_sj_state(JOIN *join, const JOIN_TAB *new_tab,
+                     uint idx, table_map remaining_tables);
 void restore_prev_sj_state(const table_map remaining_tables, 
                                   const JOIN_TAB *tab, uint idx);