MDEV-22360: Sufficient conditions for accurate calculation of join cardinality10.6-mdev22360

The aim of this task is to check if the estimate of join cardinality are accurate or not.
The implementation to check if we have the accurate estimate of the join cardinality is a
simple one, we have to walk over the WHERE clause.

The approach can be broken into 2 cases:

Case 1: WHERE clause is an AND conjunct
	For an AND item at the top level, we need to walk over all the top level conjuncts and call walk
	individually on them. This is done in such a way because for an AND conjunct at the top
	level we may have accurate selectivity, even if the predicate belongs to a different column.
	Eg: t1.a > 10 and t2.a < 5.

	For this AND item we will have accurate selectivities.
	For AND conjuncts (not at the top level), the entire conjunct needs to be resolved to one column.
	Eg: t1.a = t2.a AND ( (t1.a > 5 AND t2.a < 10) OR t1.a <= 0)

Case 2:
	2a) OR item
		For an OR item at the top level, we need to make sure that all the columns inside the OR
		conjunct need to belong to one column directly or indirectly.
		This needs to happen for an OR conjunct even if it is not at the
		top level.
		Eg: (t1.a > 5 or t1.a < 0);

	2b) Single predicate at the top level
	    Eg:
	      t1.a= t2.a [ For this case we need to make sure we know number of distinct values for t1.a and t2.a ]
	      t1.a > 5 [ sargable predicate, get the estimate from the range optimizer ]

We need to make sure that for the predicates in the WHERE clause we have estimates either
from the first component of the index or from the EITS.
The implementation of these is covered with the callback
function passed to walk function.

1 files changed, 10 insertions, 0 deletions

diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index fa715badfc7..f467efd0e10 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -227,6 +227,7 @@ public:
   bool fix_length_and_dec() { decimals=0; max_length=1; return FALSE; }
   uint decimal_precision() const { return 1; }
   bool need_parentheses_in_default() { return true; }
+  bool predicate_selectivity_checker(void *arg) { return TRUE; }
 };
 
 
@@ -418,6 +419,7 @@ public:
   COND *remove_eq_conds(THD *thd, Item::cond_result *cond_value,
                         bool top_level);
   bool count_sargable_conds(void *arg);
+  bool predicate_selectivity_checker(void *arg);
   /*
     Specifies which result type the function uses to compare its arguments.
     This method is used in equal field propagation.
@@ -936,6 +938,7 @@ public:
   bool find_not_null_fields(table_map allowed);
   void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   bool count_sargable_conds(void *arg);
+  bool predicate_selectivity_checker(void *arg);
   void add_key_fields(JOIN *join, KEY_FIELD **key_fields,
                       uint *and_level, table_map usable_tables,
                       SARGABLE_PARAM **sargables);
@@ -2473,6 +2476,7 @@ public:
   bool find_not_null_fields(table_map allowed);
   void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   bool count_sargable_conds(void *arg);
+  bool predicate_selectivity_checker(void *arg);
   Item *get_copy(THD *thd)
   { return get_item_copy<Item_func_in>(thd, this); }
   Item *build_clone(THD *thd)
@@ -2570,6 +2574,7 @@ public:
     return FALSE;
   }
   bool count_sargable_conds(void *arg);
+  bool predicate_selectivity_checker(void *arg);
 };
 
 
@@ -2813,6 +2818,7 @@ public:
   
   Item *get_copy(THD *thd)
   { return get_item_copy<Item_func_like>(thd, this); }
+  bool predicate_selectivity_checker(void *arg);
 };
 
 
@@ -3220,6 +3226,8 @@ public:
   uint elements_count() { return equal_items.elements; }
   friend class Item_equal_fields_iterator;
   bool count_sargable_conds(void *arg);
+  bool predicate_selectivity_checker(void *arg);
+  bool is_statistics_available_for_range_predicates();
   Item *multiple_equality_transformer(THD *thd, uchar *arg);
   friend class Item_equal_iterator<List_iterator_fast,Item>;
   friend class Item_equal_iterator<List_iterator,Item>;
@@ -3371,6 +3379,7 @@ public:
   SEL_TREE *get_mm_tree(RANGE_OPT_PARAM *param, Item **cond_ptr);
   Item *get_copy(THD *thd)
   { return get_item_copy<Item_cond_and>(thd, this); }
+  bool predicate_selectivity_checker(void *arg) { return FALSE; }
 };
 
 inline bool is_cond_and(Item *item)
@@ -3395,6 +3404,7 @@ public:
   Item *neg_transformer(THD *thd);
   Item *get_copy(THD *thd)
   { return get_item_copy<Item_cond_or>(thd, this); }
+  bool predicate_selectivity_checker(void *arg) { return FALSE; }
 };
 
 class Item_func_dyncol_check :public Item_bool_func