MDEV-9744: session optimizer_use_condition_selectivity=5 causing SQL Error (1918):

           Encountered illegal value '' when converting to DECIMAL

The issue was that EITS data was allocated but then not read for some reason (one being to avoid a deadlock),
then the optimizer was using these bzero'ed buffers as EITS statistics.
This should not be allowed, we should use statistcs for a table only when we have successfully loaded/read
the stats from the statistical tables.

4 files changed, 72 insertions, 6 deletions

diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result
index bd6a0849e3d..74997c92d3e 100644
--- a/mysql-test/r/statistics.result
+++ b/mysql-test/r/statistics.result
@@ -1641,3 +1641,21 @@ FROM mysql.column_stats;
 db_name	table_name	column_name	HEX(min_value)	HEX(max_value)	nulls_ratio	avg_frequency	hist_size	hist_type	HEX(histogram)
 test	t1	a	D879626AF872675F73E662F8	D879626AF872675F73E662F8	0.0000	1.0000	0	NULL	NULL
 drop table t1;
+#
+# MDEB-9744: session optimizer_use_condition_selectivity=5 causing SQL Error (1918):
+# Encountered illegal value '' when converting to DECIMAL
+#
+set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
+set optimizer_use_condition_selectivity=3, use_stat_tables=preferably;
+create table t1 (id int(10),cost decimal(9,2)) engine=innodb;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+create temporary table t2  (id int);
+insert into t2 (id) select id from t1 where cost > 0;
+select * from t2;
+id
+set use_stat_tables=@save_use_stat_tables;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+drop table t1,t2;
diff --git a/mysql-test/t/statistics.test b/mysql-test/t/statistics.test
index 2c8dec307cc..0ab42453125 100644
--- a/mysql-test/t/statistics.test
+++ b/mysql-test/t/statistics.test
@@ -717,3 +717,23 @@ select db_name, table_name, column_name,
   FROM mysql.column_stats;
 
 drop table t1;
+
+--echo #
+--echo # MDEB-9744: session optimizer_use_condition_selectivity=5 causing SQL Error (1918):
+--echo # Encountered illegal value '' when converting to DECIMAL
+--echo #
+
+set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
+set optimizer_use_condition_selectivity=3, use_stat_tables=preferably;
+
+create table t1 (id int(10),cost decimal(9,2)) engine=innodb;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+
+create temporary table t2  (id int);
+insert into t2 (id) select id from t1 where cost > 0;
+select * from t2;
+
+set use_stat_tables=@save_use_stat_tables;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+drop table t1,t2;
+
diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc
index 70080a6b4f1..6c42efd6068 100644
--- a/sql/sql_statistics.cc
+++ b/sql/sql_statistics.cc
@@ -2944,18 +2944,19 @@ bool statistics_for_tables_is_needed(THD *thd, TABLE_LIST *tables)
     return FALSE;
 
   /* 
-    Do not read statistics for any query over non-user tables.
-    If the query references some statistical tables, but not all 
-    of them, reading the statistics may lead to a deadlock
-  */ 
+    Do not read statistics for any query that explicity involves
+    statistical tables, failure to to do so we may end up
+    in a deadlock.
+  */
+
   for (TABLE_LIST *tl= tables; tl; tl= tl->next_global)
   {
     if (!tl->is_view_or_derived() && tl->table)
     {
       TABLE_SHARE *table_share= tl->table->s;
       if (table_share && 
-          (table_share->table_category != TABLE_CATEGORY_USER ||
-           table_share->tmp_table != NO_TMP_TABLE))
+          table_share->table_category != TABLE_CATEGORY_USER
+          && is_stat_table(tl->db, tl->alias))
         return FALSE;
     }
   }
@@ -3634,6 +3635,15 @@ double get_column_range_cardinality(Field *field,
 
   if (!col_stats)
     return tab_records;
+  /*
+    Use statistics for a table only when we have actually read
+    the statistics from the stat tables. For example due to
+    chances of getting a deadlock we disable reading statistics for
+    a table.
+  */
+
+  if (!table->stats_is_read)
+    return tab_records;
 
   double col_nulls= tab_records * col_stats->get_nulls_ratio();
 
@@ -3840,3 +3850,20 @@ double Histogram::point_selectivity(double pos, double avg_sel)
   return sel;
 }
 
+/*
+  Check whether the table is one of the persistent statistical tables.
+*/
+bool is_stat_table(const char *db, const char *table)
+{
+  DBUG_ASSERT(db && table);
+
+  if (!memcmp(db, stat_tables_db_name.str, stat_tables_db_name.length))
+  {
+    for (uint i= 0; i < STATISTICS_TABLES; i ++)
+    {
+      if (!memcmp(table, stat_table_name[i].str, stat_table_name[i].length))
+        return true;
+    }
+  }
+  return false;
+}
\ No newline at end of file
diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h
index 8e5f8107849..20b2eb66449 100644
--- a/sql/sql_statistics.h
+++ b/sql/sql_statistics.h
@@ -107,6 +107,7 @@ double get_column_range_cardinality(Field *field,
                                     key_range *min_endp,
                                     key_range *max_endp,
                                     uint range_flag);
+bool is_stat_table(const char *db, const char *table);
 
 class Histogram
 {