spetrunia

commit 0c1b5733e192af2c6111e0077ab2e0ccd42776ad
Author: Sergei Petrunia <sergey@mariadb.com>
Date:   Sun Jun 22 21:43:34 2025 +0300

    MDEV-36948: Attempt to make optimizer infer ref access
    
    Make ref optimizer make inferences:
    
      t1.key1=t2.key2 OR (t1.key1 IS NULL AND t2.key2 IS NULL)
    ->

spetrunia

diff --git a/mysql-test/main/statistics_json.result b/mysql-test/main/statistics_json.result
index cd885877c40..ad1ec67e375 100644
--- a/mysql-test/main/statistics_json.result
+++ b/mysql-test/main/statistics_json.result
@@ -7812,5 +7812,215 @@ min_value	max_value	hist_type
 部門	部門
 部門	JSON_HB
 DROP TABLE t1;
-DELETE FROM mysql.column_stats;
+create table t1 (

spetrunia

diff --git a/mysql-test/main/opt_trace_store_ddls.result b/mysql-test/main/opt_trace_store_ddls.result
index 3039d040045..66531c2dba2 100644
--- a/mysql-test/main/opt_trace_store_ddls.result
+++ b/mysql-test/main/opt_trace_store_ddls.result
@@ -1,36 +1,36 @@
 set optimizer_record_context=ON;
-# 
+#
 show variables like 'optimizer_record_context';
 Variable_name	Value

spetrunia

diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 71d1db52e89..023b9bd2db6 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -12356,8 +12356,7 @@ JS
             "rows": 100,
             "refills": 5,
             "total_splitting_cost": 25.90426751,
-            "chosen": true,
-            "forced_by_hint": false

spetrunia

--- ../12.1-vcol-mdev-36132-try-covering/sql/opt_vcol_substitution.cc   2025-05-28 14:15:22.543115259 +0300
+++ ./sql/opt_vcol_substitution.cc      2025-05-27 12:30:10.018060987 +0300
@@ -105,7 +105,7 @@
   THD *thd;
   /* Indexed virtual columns that we can try substituting */
   List<Field> vcol_fields;
-
+  JOIN *join;
   /*
     How many times substitution was done. Used to determine whether to print

spetrunia

MariaDB [test]> create table t100 (pk int primary key, a int, b int, key(a));
Query OK, 0 rows affected (0.004 sec)

MariaDB [test]> insert into t100 select seq, seq, seq from seq_1_to_10000;
Query OK, 10000 rows affected (1.561 sec)
Records: 10000  Duplicates: 0  Warnings: 0

MariaDB [test]> begin;
Query OK, 0 rows affected (0.000 sec)

spetrunia

###
### Both hints:
###

mysql> explain insert /*+ NO_RANGE_OPTIMIZATION(t10 a) */into t1 select /*+ NO_RANGE_OPTIMIZATION(t10 b) */ a,a from t10 where a < 3 and b <3 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | INSERT      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL        |

spetrunia

diff --git a/sql/sql_base.h b/sql/sql_base.h
index a65a223b6e5..d5754d82d37 100644
--- a/sql/sql_base.h
+++ b/sql/sql_base.h
@@ -373,6 +373,8 @@ inline void setup_table_map(TABLE *table, TABLE_LIST *table_list, uint tablenr)
   table->force_index= table->force_index_join= 0;
   table->force_index_order= table->force_index_group= 0;
   table->covering_keys= table->s->keys_for_keyread;
+  fprintf(stderr, "setup_table_map(%s): setttting covering_keys=%llx\n",
+          table->alias.ptr(), table->covering_keys.to_ulonglong());

spetrunia

create table t1 (
  a int, 
  b int
);
insert into t1 select seq,seq from seq_1_to_100;

create table t2 ( 
  key1 int,
  key2 int,

spetrunia

diff --git a/sql/opt_hints.cc b/sql/opt_hints.cc
index fb09d6bd8db..d2d05a29813 100644
--- a/sql/opt_hints.cc
+++ b/sql/opt_hints.cc
@@ -716,7 +717,7 @@ bool Opt_hints_qb::set_join_hint_deps(JOIN *join,
   {
     JOIN_TAB *join_tab= &join->join_tab[i];
     const table_map dependent_tables=
-        get_other_dep(hint->hint_type, hint_tab_map,
+        get_other_dep(join, hint->hint_type, hint_tab_map,