Skip to content

Instantly share code, notes, and snippets.

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)
->
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 (
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
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
--- ../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
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)
###
### 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 |
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());
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,
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,