[Commits] be6beb73e9e: MDEV-16560: [counter] rocksdb.ttl_secondary_read_filtering fail in buildbot
by Sergei Petrunia 11 Sep '19
by Sergei Petrunia 11 Sep '19
11 Sep '19
revision-id: be6beb73e9e6adf2ebd69354a2496817f03ae6ff (mariadb-10.2.27-20-gbe6beb73e9e)
parent(s): c8dc866fdeee551993ef91fb321135f9106ea00e
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-09-11 23:05:12 +0300
message:
MDEV-16560: [counter] rocksdb.ttl_secondary_read_filtering fail in buildbot
It is not reproducible, but the issue seems to be the same as with
MDEV-20490 and rocksdb.ttl_primary_read_filtering - a compaction caused
by DROP TABLE gets behind and compacts away the expired rows for the next
test. Fix this in the same way.
---
.../rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering.result | 1 +
storage/rocksdb/mysql-test/rocksdb/t/ttl_secondary_read_filtering.test | 3 +++
2 files changed, 4 insertions(+)
diff --git a/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering.result b/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering.result
index 90de5447891..395c84edfe9 100644
--- a/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering.result
+++ b/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering.result
@@ -101,6 +101,7 @@ a b
SELECT * FROM t1 FORCE INDEX (kb);
a b
DROP TABLE t1;
+set global rocksdb_compact_cf= 'default';
# Read filtering index scan tests (None of these queries should return any results)
CREATE TABLE t1 (
a int,
diff --git a/storage/rocksdb/mysql-test/rocksdb/t/ttl_secondary_read_filtering.test b/storage/rocksdb/mysql-test/rocksdb/t/ttl_secondary_read_filtering.test
index d6be7d95f8d..f6042cc517e 100644
--- a/storage/rocksdb/mysql-test/rocksdb/t/ttl_secondary_read_filtering.test
+++ b/storage/rocksdb/mysql-test/rocksdb/t/ttl_secondary_read_filtering.test
@@ -121,6 +121,9 @@ SELECT * FROM t1 FORCE INDEX (kb);
DROP TABLE t1;
+# Compact away the dropped data
+set global rocksdb_compact_cf= 'default';
+
--echo # Read filtering index scan tests (None of these queries should return any results)
CREATE TABLE t1 (
a int,
1
0
[Commits] d8b28f78535: MDEV-20371: Invalid reads at plan refinement stage: join->positions...
by psergey 11 Sep '19
by psergey 11 Sep '19
11 Sep '19
revision-id: d8b28f78535064c6505a8e355a04d2cef90f81f0 (mariadb-10.4.7-7-gd8b28f78535)
parent(s): 13f36fffeaecf316435fc497b0f3ae2a5d58d749
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-09-11 20:22:08 +0300
message:
MDEV-20371: Invalid reads at plan refinement stage: join->positions...
(re-committing in 10.4)
best_access_path() is called from two optimization phases:
1. Plan choice phase, in choose_plan(). Here, the join prefix being
considered is in join->positions[]
2. Plan refinement stage, in fix_semijoin_strategies_for_picked_join_order
Here, the join prefix is in join->best_positions[]
It used to access join->positions[] from stage #2. This didnt cause any
valgrind or asan failures (as join->positions[] has been written-to before)
but the effect was similar to that of reading the random data:
The join prefix we've picked (in join->best_positions) could have
nothing in common with the join prefix that was last to be considered
(in join->positions).
---
sql/opt_subselect.cc | 27 +++++++++++++++++----------
sql/opt_subselect.h | 8 ++++++--
sql/sql_select.cc | 37 ++++++++++++++++++++++---------------
sql/sql_select.h | 9 ++++++++-
4 files changed, 53 insertions(+), 28 deletions(-)
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 599642b3a26..d9172d7f13f 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -453,10 +453,6 @@ bool find_eq_ref_candidate(TABLE *table, table_map sj_inner_tables);
static SJ_MATERIALIZATION_INFO *
at_sjmat_pos(const JOIN *join, table_map remaining_tables, const JOIN_TAB *tab,
uint idx, bool *loose_scan);
-void best_access_path(JOIN *join, JOIN_TAB *s,
- table_map remaining_tables, uint idx,
- bool disable_jbuf, double record_count,
- POSITION *pos, POSITION *loose_scan_pos);
void trace_plan_prefix(JOIN *join, uint idx, table_map remaining_tables);
static Item *create_subq_in_equalities(THD *thd, SJ_MATERIALIZATION_INFO *sjm,
@@ -2787,6 +2783,13 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx,
NULL,
};
+#ifdef HAVE_valgrind
+ new (&pos->firstmatch_picker) Firstmatch_picker;
+ new (&pos->loosescan_picker) LooseScan_picker;
+ new (&pos->sjmat_picker) Sj_materialization_picker;
+ new (&pos->dups_weedout_picker) Duplicate_weedout_picker;
+#endif
+
if (join->emb_sjm_nest)
{
/*
@@ -3078,7 +3081,8 @@ bool Sj_materialization_picker::check_qep(JOIN *join,
Json_writer_temp_disable trace_semijoin_mat_scan(thd);
for (i= first_tab + mat_info->tables; i <= idx; i++)
{
- best_access_path(join, join->positions[i].table, rem_tables, i,
+ best_access_path(join, join->positions[i].table, rem_tables,
+ join->positions, i,
disable_jbuf, prefix_rec_count, &curpos, &dummy);
prefix_rec_count= COST_MULT(prefix_rec_count, curpos.records_read);
prefix_cost= COST_ADD(prefix_cost, curpos.read_time);
@@ -3718,7 +3722,8 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join)
Json_writer_object trace_one_table(thd);
trace_one_table.add_table_name(join->best_positions[i].table);
}
- best_access_path(join, join->best_positions[i].table, rem_tables, i,
+ best_access_path(join, join->best_positions[i].table, rem_tables,
+ join->best_positions, i,
FALSE, prefix_rec_count,
join->best_positions + i, &dummy);
prefix_rec_count *= join->best_positions[i].records_read;
@@ -3758,8 +3763,9 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join)
}
if (join->best_positions[idx].use_join_buffer)
{
- best_access_path(join, join->best_positions[idx].table,
- rem_tables, idx, TRUE /* no jbuf */,
+ best_access_path(join, join->best_positions[idx].table,
+ rem_tables, join->best_positions, idx,
+ TRUE /* no jbuf */,
record_count, join->best_positions + idx, &dummy);
}
record_count *= join->best_positions[idx].records_read;
@@ -3785,7 +3791,7 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join)
*/
join->cur_sj_inner_tables= 0;
Json_writer_object semijoin_strategy(thd);
- semijoin_strategy.add("semi_join_strategy","sj_materialize");
+ semijoin_strategy.add("semi_join_strategy","loose-scan");
Json_writer_array semijoin_plan(thd, "join_order");
for (idx= first; idx <= tablenr; idx++)
{
@@ -3797,7 +3803,8 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join)
if (join->best_positions[idx].use_join_buffer || (idx == first))
{
best_access_path(join, join->best_positions[idx].table,
- rem_tables, idx, TRUE /* no jbuf */,
+ rem_tables, join->best_positions, idx,
+ TRUE /* no jbuf */,
record_count, join->best_positions + idx,
&loose_scan_pos);
if (idx==first)
diff --git a/sql/opt_subselect.h b/sql/opt_subselect.h
index 65131f6bc89..0799402f146 100644
--- a/sql/opt_subselect.h
+++ b/sql/opt_subselect.h
@@ -91,6 +91,7 @@ class Loose_scan_opt
KEYUSE *best_loose_scan_start_key;
uint best_max_loose_keypart;
+ table_map best_ref_depend_map;
public:
Loose_scan_opt():
@@ -253,13 +254,14 @@ class Loose_scan_opt
best_loose_scan_records= records;
best_max_loose_keypart= max_loose_keypart;
best_loose_scan_start_key= start_key;
+ best_ref_depend_map= 0;
}
}
}
}
void check_ref_access_part2(uint key, KEYUSE *start_key, double records,
- double read_time)
+ double read_time, table_map ref_depend_map_arg)
{
if (part1_conds_met && read_time < best_loose_scan_cost)
{
@@ -269,6 +271,7 @@ class Loose_scan_opt
best_loose_scan_records= records;
best_max_loose_keypart= max_loose_keypart;
best_loose_scan_start_key= start_key;
+ best_ref_depend_map= ref_depend_map_arg;
}
}
@@ -284,6 +287,7 @@ class Loose_scan_opt
best_loose_scan_records= rows2double(quick->records);
best_max_loose_keypart= quick_max_loose_keypart;
best_loose_scan_start_key= NULL;
+ best_ref_depend_map= 0;
}
}
@@ -300,7 +304,7 @@ class Loose_scan_opt
pos->use_join_buffer= FALSE;
pos->table= tab;
pos->range_rowid_filter_info= tab->range_rowid_filter_info;
- // todo need ref_depend_map ?
+ pos->ref_depend_map= best_ref_depend_map;
DBUG_PRINT("info", ("Produced a LooseScan plan, key %s, %s",
tab->table->key_info[best_loose_scan_key].name.str,
best_loose_scan_start_key? "(ref access)":
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index ff07a7aea89..1b09449779e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -107,10 +107,6 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
static ha_rows get_quick_record_count(THD *thd, SQL_SELECT *select,
TABLE *table,
const key_map *keys,ha_rows limit);
-void best_access_path(JOIN *join, JOIN_TAB *s,
- table_map remaining_tables, uint idx,
- bool disable_jbuf, double record_count,
- POSITION *pos, POSITION *loose_scan_pos);
static void optimize_straight_join(JOIN *join, table_map join_tables);
static bool greedy_search(JOIN *join, table_map remaining_tables,
uint depth, uint prune_level,
@@ -5481,6 +5477,13 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
{
if (choose_plan(join, all_table_map & ~join->const_table_map))
goto error;
+
+#ifdef HAVE_valgrind
+ // JOIN::positions holds the current query plan. We've already
+ // made the plan choice, so we should only use JOIN::best_positions
+ for (uint k=join->const_tables; k < join->table_count; k++)
+ MEM_UNDEFINED(&join->positions[k], sizeof(join->positions[k]));
+#endif
}
else
{
@@ -7180,6 +7183,7 @@ void
best_access_path(JOIN *join,
JOIN_TAB *s,
table_map remaining_tables,
+ const POSITION *join_positions,
uint idx,
bool disable_jbuf,
double record_count,
@@ -7299,7 +7303,7 @@ best_access_path(JOIN *join,
if (!keyuse->val->maybe_null || keyuse->null_rejecting)
notnull_part|=keyuse->keypart_map;
- double tmp2= prev_record_reads(join->positions, idx,
+ double tmp2= prev_record_reads(join_positions, idx,
(found_ref | keyuse->used_tables));
if (tmp2 < best_prev_record_reads)
{
@@ -7341,7 +7345,7 @@ best_access_path(JOIN *join,
Really, there should be records=0.0 (yes!)
but 1.0 would be probably safer
*/
- tmp= prev_record_reads(join->positions, idx, found_ref);
+ tmp= prev_record_reads(join_positions, idx, found_ref);
records= 1.0;
trace_access_idx.add("access_type", "fulltext")
.add("index", keyinfo->name);
@@ -7368,7 +7372,7 @@ best_access_path(JOIN *join,
{
trace_access_idx.add("access_type", "eq_ref")
.add("index", keyinfo->name);
- tmp = prev_record_reads(join->positions, idx, found_ref);
+ tmp = prev_record_reads(join_positions, idx, found_ref);
records=1.0;
}
else
@@ -7655,7 +7659,8 @@ best_access_path(JOIN *join,
}
tmp= COST_ADD(tmp, s->startup_cost);
- loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp);
+ loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp,
+ found_ref);
} /* not ft_key */
if (records < DBL_MAX)
@@ -8432,7 +8437,8 @@ optimize_straight_join(JOIN *join, table_map join_tables)
trace_one_table.add_table_name(s);
}
/* Find the best access method from 's' to the current partial plan */
- best_access_path(join, s, join_tables, idx, disable_jbuf, record_count,
+ best_access_path(join, s, join_tables, join->positions, idx,
+ disable_jbuf, record_count,
position, &loose_scan_pos);
/* compute the cost of the new plan extended with 's' */
@@ -9369,8 +9375,8 @@ best_extension_by_limited_search(JOIN *join,
/* Find the best access method from 's' to the current partial plan */
POSITION loose_scan_pos;
- best_access_path(join, s, remaining_tables, idx, disable_jbuf,
- record_count, position, &loose_scan_pos);
+ best_access_path(join, s, remaining_tables, join->positions, idx,
+ disable_jbuf, record_count, position, &loose_scan_pos);
/* Compute the cost of extending the plan with 's' */
current_record_count= COST_MULT(record_count, position->records_read);
@@ -9763,11 +9769,11 @@ cache_record_length(JOIN *join,uint idx)
*/
double
-prev_record_reads(POSITION *positions, uint idx, table_map found_ref)
+prev_record_reads(const POSITION *positions, uint idx, table_map found_ref)
{
double found=1.0;
- POSITION *pos_end= positions - 1;
- for (POSITION *pos= positions + idx - 1; pos != pos_end; pos--)
+ const POSITION *pos_end= positions - 1;
+ for (const POSITION *pos= positions + idx - 1; pos != pos_end; pos--)
{
if (pos->table->table->map & found_ref)
{
@@ -16654,7 +16660,8 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab,
if ((i == first_tab && first_alt) || join->positions[i].use_join_buffer)
{
/* Find the best access method that would not use join buffering */
- best_access_path(join, rs, reopt_remaining_tables, i,
+ best_access_path(join, rs, reopt_remaining_tables,
+ join->positions, i,
TRUE, rec_count,
&pos, &loose_scan_pos);
}
diff --git a/sql/sql_select.h b/sql/sql_select.h
index b7f870bf797..416c2d35c07 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -854,6 +854,7 @@ class LooseScan_picker : public Semi_join_strategy_picker
friend void best_access_path(JOIN *join,
JOIN_TAB *s,
table_map remaining_tables,
+ const struct st_position *join_positions,
uint idx,
bool disable_jbuf,
double record_count,
@@ -2071,6 +2072,12 @@ class store_key_const_item :public store_key_item
}
};
+void best_access_path(JOIN *join, JOIN_TAB *s,
+ table_map remaining_tables,
+ const POSITION *join_positions, uint idx,
+ bool disable_jbuf, double record_count,
+ POSITION *pos, POSITION *loose_scan_pos);
+
bool cp_buffer_from_ref(THD *thd, TABLE *table, TABLE_REF *ref);
bool error_if_full_join(JOIN *join);
int report_error(TABLE *table, int error);
@@ -2435,7 +2442,7 @@ bool instantiate_tmp_table(TABLE *table, KEY *keyinfo,
ulonglong options);
bool open_tmp_table(TABLE *table);
void setup_tmp_table_column_bitmaps(TABLE *table, uchar *bitmaps);
-double prev_record_reads(POSITION *positions, uint idx, table_map found_ref);
+double prev_record_reads(const POSITION *positions, uint idx, table_map found_ref);
void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist);
double get_tmp_table_lookup_cost(THD *thd, double row_count, uint row_size);
double get_tmp_table_write_cost(THD *thd, double row_count, uint row_size);
1
0
[Commits] 7310eb64d8d: MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
by Varun 10 Sep '19
by Varun 10 Sep '19
10 Sep '19
revision-id: 7310eb64d8d3e1431fc14341415cf17682a5cf42 (mariadb-10.1.41-35-g7310eb64d8d)
parent(s): 031c695b8c865e5eb6c4c09ced404ae08f98430f
author: Varun Gupta
committer: Varun Gupta
timestamp: 2019-09-11 04:12:42 +0530
message:
MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
The issue here was that inside the function table_cond_selectivity we were trying to use
the ranges estimate for a key for which no sargable condition was present.
The fix for this issue is to make reset the quick structures when we read a TABLE structure
from the table cache. This would ensure that we don't read some estimate of previous queries
---
mysql-test/r/innodb_icp.result | 4 +-
mysql-test/r/range_vs_index_merge.result | 2 +-
mysql-test/r/range_vs_index_merge_innodb.result | 2 +-
mysql-test/r/selectivity.result | 56 +++++++++++++++++++++++++
mysql-test/r/selectivity_innodb.result | 56 +++++++++++++++++++++++++
mysql-test/t/selectivity.test | 32 ++++++++++++++
sql/opt_range.cc | 11 +++++
sql/sql_select.cc | 2 +
sql/table.cc | 20 +++++++++
sql/table.h | 1 +
10 files changed, 182 insertions(+), 4 deletions(-)
diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index a5215bf9f0d..0c95f31ae95 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -679,7 +679,7 @@ EXPLAIN
SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
HAVING t1.c != 5 ORDER BY t1.c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort
1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where
SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
HAVING t1.c != 5 ORDER BY t1.c;
@@ -690,7 +690,7 @@ EXPLAIN
SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
HAVING t1.c != 5 ORDER BY t1.c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort
1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where
SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
HAVING t1.c != 5 ORDER BY t1.c;
diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result
index bc46a4fdd0b..4f3c36b7660 100644
--- a/mysql-test/r/range_vs_index_merge.result
+++ b/mysql-test/r/range_vs_index_merge.result
@@ -1795,7 +1795,7 @@ SELECT * FROM t1 FORCE KEY (state,capital)
WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9
OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range state,capital state 71 NULL 12 Using index condition; Using where
+1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where
SELECT * FROM t1 FORCE KEY (state,capital)
WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9
OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result
index a6ec200538d..08b7df66c67 100644
--- a/mysql-test/r/range_vs_index_merge_innodb.result
+++ b/mysql-test/r/range_vs_index_merge_innodb.result
@@ -1796,7 +1796,7 @@ SELECT * FROM t1 FORCE KEY (state,capital)
WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9
OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range state,capital state 71 NULL 10 Using index condition; Using where
+1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where
SELECT * FROM t1 FORCE KEY (state,capital)
WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9
OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index 3f5db42d341..db8ff2c53c7 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -1668,4 +1668,60 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
drop table t1;
set use_stat_tables= @save_use_stat_tables;
set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
+# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
+#
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+set optimizer_switch='exists_to_in=off';
+set optimizer_use_condition_selectivity=2;
+SELECT * FROM t1
+WHERE
+EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id
+WHERE A.a=t1.a AND t2.b < 20);
+id a
+1 1
+2 2
+3 3
+4 4
+5 5
+6 6
+7 7
+8 8
+9 9
+10 10
+11 11
+12 12
+13 13
+14 14
+15 15
+16 16
+17 17
+18 18
+19 19
+explain SELECT * FROM t1
+WHERE
+EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id
+WHERE A.a=t1.a AND t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where
+2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where
+EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1
+1 SIMPLE B ref a a 5 const 1
+explain SELECT * FROM t1
+WHERE
+EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id
+WHERE A.a=t1.a AND t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where
+2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where
+drop table t1,t2;
+set optimizer_switch= @save_optimizer_switch;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
# End of 10.1 tests
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 1d73c2f5d50..87c47434b5e 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -1678,6 +1678,62 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
drop table t1;
set use_stat_tables= @save_use_stat_tables;
set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
+# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
+#
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+set optimizer_switch='exists_to_in=off';
+set optimizer_use_condition_selectivity=2;
+SELECT * FROM t1
+WHERE
+EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id
+WHERE A.a=t1.a AND t2.b < 20);
+id a
+1 1
+2 2
+3 3
+4 4
+5 5
+6 6
+7 7
+8 8
+9 9
+10 10
+11 11
+12 12
+13 13
+14 14
+15 15
+16 16
+17 17
+18 18
+19 19
+explain SELECT * FROM t1
+WHERE
+EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id
+WHERE A.a=t1.a AND t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index
+2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 Using index
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where
+EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1
+1 SIMPLE B ref a a 5 const 1 Using index
+explain SELECT * FROM t1
+WHERE
+EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id
+WHERE A.a=t1.a AND t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index
+2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 Using index
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where
+drop table t1,t2;
+set optimizer_switch= @save_optimizer_switch;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
# End of 10.1 tests
set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
set @tmp_ust= @@use_stat_tables;
diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test
index f1c9d6b31b8..6f79927e5d6 100644
--- a/mysql-test/t/selectivity.test
+++ b/mysql-test/t/selectivity.test
@@ -1124,5 +1124,37 @@ drop table t1;
set use_stat_tables= @save_use_stat_tables;
set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+--echo #
+--echo # MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
+--echo #
+
+--source include/have_sequence.inc
+
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+
+set optimizer_switch='exists_to_in=off';
+set optimizer_use_condition_selectivity=2;
+
+let $query= SELECT * FROM t1
+ WHERE
+ EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id
+ WHERE A.a=t1.a AND t2.b < 20);
+
+eval $query;
+eval explain $query;
+
+EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+
+eval explain $query;
+
+drop table t1,t2;
+set optimizer_switch= @save_optimizer_switch;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+
--echo # End of 10.1 tests
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index e8421ad052a..118e063e487 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -10131,6 +10131,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only,
RANGE_SEQ_IF seq_if = {NULL, sel_arg_range_seq_init, sel_arg_range_seq_next, 0, 0};
handler *file= param->table->file;
ha_rows rows= HA_POS_ERROR;
+ ha_rows table_records= param->table->stat_records();
uint keynr= param->real_keynr[idx];
DBUG_ENTER("check_quick_select");
@@ -10179,6 +10180,16 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only,
bufsize, mrr_flags, cost);
if (rows != HA_POS_ERROR)
{
+ /*
+ Capping the estimates of range access to atmost total records of table.
+ Also the estimate of range access is always atleast 1.
+ */
+ if (rows > table_records)
+ {
+ rows= table_records;
+ set_if_bigger(rows, 1);
+ }
+
param->quick_rows[keynr]= rows;
param->possible_keys.set_bit(keynr);
if (update_tbl_stats)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5b96c15bff5..089c8e96e49 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7644,6 +7644,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s,
keyparts++;
}
sel /= (double)table->quick_rows[key] / (double) table->stat_records();
+ set_if_smaller(sel, 1.0);
used_range_selectivity= true;
}
}
@@ -7759,6 +7760,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s,
sel*= table_multi_eq_cond_selectivity(join, idx, s, rem_tables,
keyparts, ref_keyuse_steps);
+ DBUG_ASSERT(sel > 0 && sel <= 1);
return sel;
}
diff --git a/sql/table.cc b/sql/table.cc
index 94cd174ffd7..ac46ff6a42c 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -4162,6 +4162,8 @@ void TABLE::init(THD *thd, TABLE_LIST *tl)
created= TRUE;
cond_selectivity= 1.0;
cond_selectivity_sampling_explain= NULL;
+ quick_condition_rows= 0;
+ initialize_quick_structures();
#ifdef HAVE_REPLICATION
/* used in RBR Triggers */
master_had_triggers= 0;
@@ -7546,3 +7548,21 @@ bool fk_modifies_child(enum_fk_option opt)
static bool can_write[]= { false, false, true, true, false, true };
return can_write[opt];
}
+
+/*
+ @brief
+ Initialize all the quick structures that are used to stored the
+ estimates when the range optimizer is run.
+ @details
+ This is specifically needed when we read the TABLE structure from the
+ table cache. There can be some garbage data from previous queries
+ that need to be reset here.
+*/
+
+void TABLE::initialize_quick_structures()
+{
+ bzero(quick_rows, sizeof(quick_rows));
+ bzero(quick_key_parts, sizeof(quick_key_parts));
+ bzero(quick_costs, sizeof(quick_costs));
+ bzero(quick_n_ranges, sizeof(quick_n_ranges));
+}
diff --git a/sql/table.h b/sql/table.h
index 98ec9f005ea..44803b5aacd 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1450,6 +1450,7 @@ struct TABLE
}
bool update_const_key_parts(COND *conds);
+ void initialize_quick_structures();
my_ptrdiff_t default_values_offset() const
{ return (my_ptrdiff_t) (s->default_values - record[0]); }
1
0
[Commits] 3c0c5027dfc: MDEV-20371: Invalid reads at plan refinement stage: join->positions...
by psergey 10 Sep '19
by psergey 10 Sep '19
10 Sep '19
revision-id: 3c0c5027dfc3a645eef5b3241b00bf128a6ae81c (mariadb-10.2.26-105-g3c0c5027dfc)
parent(s): acf0f2d59270ce8acc20e09c9c4d95091f13045f
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-09-10 23:51:42 +0300
message:
MDEV-20371: Invalid reads at plan refinement stage: join->positions...
best_access_path() is called from two optimization phases:
1. Plan choice phase, in choose_plan(). Here, the join prefix being
considered is in join->positions[]
2. Plan refinement stage, in fix_semijoin_strategies_for_picked_join_order
Here, the join prefix is in join->best_positions[]
It used to access join->positions[] from stage #2. This didnt cause any
valgrind or asan failures (as join->positions[] has been written-to before)
but the effect was similar to that of reading the random data:
The join prefix we've picked (in join->best_positions) could have
nothing in common with the join prefix that was last to be considered
(in join->positions).
---
sql/opt_subselect.cc | 26 ++++++++++++++++----------
sql/opt_subselect.h | 8 ++++++--
sql/sql_select.cc | 38 +++++++++++++++++++++++---------------
sql/sql_select.h | 8 +++++++-
4 files changed, 52 insertions(+), 28 deletions(-)
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 99fa1c7344f..1d3ebba4f4e 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -456,11 +456,6 @@ bool find_eq_ref_candidate(TABLE *table, table_map sj_inner_tables);
static SJ_MATERIALIZATION_INFO *
at_sjmat_pos(const JOIN *join, table_map remaining_tables, const JOIN_TAB *tab,
uint idx, bool *loose_scan);
-void best_access_path(JOIN *join, JOIN_TAB *s,
- table_map remaining_tables, uint idx,
- bool disable_jbuf, double record_count,
- POSITION *pos, POSITION *loose_scan_pos);
-
static Item *create_subq_in_equalities(THD *thd, SJ_MATERIALIZATION_INFO *sjm,
Item_in_subselect *subq_pred);
static void remove_sj_conds(THD *thd, Item **tree);
@@ -2756,6 +2751,13 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx,
NULL,
};
+#ifdef HAVE_valgrind
+ new (&pos->firstmatch_picker) Firstmatch_picker;
+ new (&pos->loosescan_picker) LooseScan_picker;
+ new (&pos->sjmat_picker) Sj_materialization_picker;
+ new (&pos->dups_weedout_picker) Duplicate_weedout_picker;
+#endif
+
if (join->emb_sjm_nest)
{
/*
@@ -3045,7 +3047,8 @@ bool Sj_materialization_picker::check_qep(JOIN *join,
bool disable_jbuf= (join->thd->variables.join_cache_level == 0);
for (i= first_tab + mat_info->tables; i <= idx; i++)
{
- best_access_path(join, join->positions[i].table, rem_tables, i,
+ best_access_path(join, join->positions[i].table, rem_tables,
+ join->positions, i,
disable_jbuf, prefix_rec_count, &curpos, &dummy);
prefix_rec_count= COST_MULT(prefix_rec_count, curpos.records_read);
prefix_cost= COST_ADD(prefix_cost, curpos.read_time);
@@ -3661,7 +3664,8 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join)
join->cur_sj_inner_tables= 0;
for (i= first + sjm->tables; i <= tablenr; i++)
{
- best_access_path(join, join->best_positions[i].table, rem_tables, i,
+ best_access_path(join, join->best_positions[i].table, rem_tables,
+ join->best_positions, i,
FALSE, prefix_rec_count,
join->best_positions + i, &dummy);
prefix_rec_count *= join->best_positions[i].records_read;
@@ -3693,8 +3697,9 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join)
{
if (join->best_positions[idx].use_join_buffer)
{
- best_access_path(join, join->best_positions[idx].table,
- rem_tables, idx, TRUE /* no jbuf */,
+ best_access_path(join, join->best_positions[idx].table,
+ rem_tables, join->best_positions, idx,
+ TRUE /* no jbuf */,
record_count, join->best_positions + idx, &dummy);
}
record_count *= join->best_positions[idx].records_read;
@@ -3724,7 +3729,8 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join)
if (join->best_positions[idx].use_join_buffer || (idx == first))
{
best_access_path(join, join->best_positions[idx].table,
- rem_tables, idx, TRUE /* no jbuf */,
+ rem_tables, join->best_positions, idx,
+ TRUE /* no jbuf */,
record_count, join->best_positions + idx,
&loose_scan_pos);
if (idx==first)
diff --git a/sql/opt_subselect.h b/sql/opt_subselect.h
index 1b4c8116135..7641a5d6196 100644
--- a/sql/opt_subselect.h
+++ b/sql/opt_subselect.h
@@ -88,6 +88,7 @@ class Loose_scan_opt
KEYUSE *best_loose_scan_start_key;
uint best_max_loose_keypart;
+ table_map best_ref_depend_map;
public:
Loose_scan_opt():
@@ -250,13 +251,14 @@ class Loose_scan_opt
best_loose_scan_records= records;
best_max_loose_keypart= max_loose_keypart;
best_loose_scan_start_key= start_key;
+ best_ref_depend_map= 0;
}
}
}
}
void check_ref_access_part2(uint key, KEYUSE *start_key, double records,
- double read_time)
+ double read_time, table_map ref_depend_map_arg)
{
if (part1_conds_met && read_time < best_loose_scan_cost)
{
@@ -266,6 +268,7 @@ class Loose_scan_opt
best_loose_scan_records= records;
best_max_loose_keypart= max_loose_keypart;
best_loose_scan_start_key= start_key;
+ best_ref_depend_map= ref_depend_map_arg;
}
}
@@ -281,6 +284,7 @@ class Loose_scan_opt
best_loose_scan_records= rows2double(quick->records);
best_max_loose_keypart= quick_max_loose_keypart;
best_loose_scan_start_key= NULL;
+ best_ref_depend_map= 0;
}
}
@@ -296,7 +300,7 @@ class Loose_scan_opt
pos->loosescan_picker.loosescan_parts= best_max_loose_keypart + 1;
pos->use_join_buffer= FALSE;
pos->table= tab;
- // todo need ref_depend_map ?
+ pos->ref_depend_map= best_ref_depend_map;
DBUG_PRINT("info", ("Produced a LooseScan plan, key %s, %s",
tab->table->key_info[best_loose_scan_key].name,
best_loose_scan_start_key? "(ref access)":
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 1b82b11fce6..24edac2dae1 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -97,10 +97,6 @@ static int sort_keyuse(KEYUSE *a,KEYUSE *b);
static bool are_tables_local(JOIN_TAB *jtab, table_map used_tables);
static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
bool allow_full_scan, table_map used_tables);
-void best_access_path(JOIN *join, JOIN_TAB *s,
- table_map remaining_tables, uint idx,
- bool disable_jbuf, double record_count,
- POSITION *pos, POSITION *loose_scan_pos);
static void optimize_straight_join(JOIN *join, table_map join_tables);
static bool greedy_search(JOIN *join, table_map remaining_tables,
uint depth, uint prune_level,
@@ -4571,6 +4567,13 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
{
if (choose_plan(join, all_table_map & ~join->const_table_map))
goto error;
+
+#ifdef HAVE_valgrind
+ // JOIN::positions holds the current query plan. We've already
+ // made the plan choice, so we should only use JOIN::best_positions
+ for (uint k=join->const_tables; k < join->table_count; k++)
+ MEM_UNDEFINED(&join->positions[k], sizeof(join->positions[k]));
+#endif
}
else
{
@@ -6285,6 +6288,7 @@ void
best_access_path(JOIN *join,
JOIN_TAB *s,
table_map remaining_tables,
+ const POSITION *join_positions,
uint idx,
bool disable_jbuf,
double record_count,
@@ -6388,7 +6392,7 @@ best_access_path(JOIN *join,
if (!(keyuse->used_tables & ~join->const_table_map))
const_part|= keyuse->keypart_map;
- double tmp2= prev_record_reads(join->positions, idx,
+ double tmp2= prev_record_reads(join_positions, idx,
(found_ref | keyuse->used_tables));
if (tmp2 < best_prev_record_reads)
{
@@ -6429,7 +6433,7 @@ best_access_path(JOIN *join,
Really, there should be records=0.0 (yes!)
but 1.0 would be probably safer
*/
- tmp= prev_record_reads(join->positions, idx, found_ref);
+ tmp= prev_record_reads(join_positions, idx, found_ref);
records= 1.0;
}
else
@@ -6445,7 +6449,7 @@ best_access_path(JOIN *join,
if ((key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME ||
MY_TEST(key_flags & HA_EXT_NOSAME))
{
- tmp = prev_record_reads(join->positions, idx, found_ref);
+ tmp = prev_record_reads(join_positions, idx, found_ref);
records=1.0;
}
else
@@ -6689,7 +6693,8 @@ best_access_path(JOIN *join,
}
tmp= COST_ADD(tmp, s->startup_cost);
- loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp);
+ loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp,
+ found_ref);
} /* not ft_key */
if (tmp + 0.0001 < best_time - records/(double) TIME_FOR_COMPARE)
{
@@ -7367,7 +7372,8 @@ optimize_straight_join(JOIN *join, table_map join_tables)
for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++)
{
/* Find the best access method from 's' to the current partial plan */
- best_access_path(join, s, join_tables, idx, disable_jbuf, record_count,
+ best_access_path(join, s, join_tables, join->positions, idx,
+ disable_jbuf, record_count,
join->positions + idx, &loose_scan_pos);
/* compute the cost of the new plan extended with 's' */
@@ -8285,8 +8291,9 @@ best_extension_by_limited_search(JOIN *join,
/* Find the best access method from 's' to the current partial plan */
POSITION loose_scan_pos;
- best_access_path(join, s, remaining_tables, idx, disable_jbuf,
- record_count, join->positions + idx, &loose_scan_pos);
+ best_access_path(join, s, remaining_tables, join->positions, idx,
+ disable_jbuf, record_count, join->positions + idx,
+ &loose_scan_pos);
/* Compute the cost of extending the plan with 's' */
current_record_count= COST_MULT(record_count, position->records_read);
@@ -8672,11 +8679,11 @@ cache_record_length(JOIN *join,uint idx)
*/
double
-prev_record_reads(POSITION *positions, uint idx, table_map found_ref)
+prev_record_reads(const POSITION *positions, uint idx, table_map found_ref)
{
double found=1.0;
- POSITION *pos_end= positions - 1;
- for (POSITION *pos= positions + idx - 1; pos != pos_end; pos--)
+ const POSITION *pos_end= positions - 1;
+ for (const POSITION *pos= positions + idx - 1; pos != pos_end; pos--)
{
if (pos->table->table->map & found_ref)
{
@@ -15400,7 +15407,8 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab,
if ((i == first_tab && first_alt) || join->positions[i].use_join_buffer)
{
/* Find the best access method that would not use join buffering */
- best_access_path(join, rs, reopt_remaining_tables, i,
+ best_access_path(join, rs, reopt_remaining_tables,
+ join->positions, i,
TRUE, rec_count,
&pos, &loose_scan_pos);
}
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 60c2ce55a16..fe44f448446 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -792,6 +792,7 @@ class LooseScan_picker : public Semi_join_strategy_picker
friend void best_access_path(JOIN *join,
JOIN_TAB *s,
table_map remaining_tables,
+ const struct st_position *join_positions,
uint idx,
bool disable_jbuf,
double record_count,
@@ -1960,6 +1961,11 @@ class store_key_const_item :public store_key_item
}
};
+void best_access_path(JOIN *join, JOIN_TAB *s,
+ table_map remaining_tables,
+ const POSITION *join_positions, uint idx,
+ bool disable_jbuf, double record_count,
+ POSITION *pos, POSITION *loose_scan_pos);
bool cp_buffer_from_ref(THD *thd, TABLE *table, TABLE_REF *ref);
bool error_if_full_join(JOIN *join);
int report_error(TABLE *table, int error);
@@ -2277,7 +2283,7 @@ bool instantiate_tmp_table(TABLE *table, KEY *keyinfo,
ulonglong options);
bool open_tmp_table(TABLE *table);
void setup_tmp_table_column_bitmaps(TABLE *table, uchar *bitmaps);
-double prev_record_reads(POSITION *positions, uint idx, table_map found_ref);
+double prev_record_reads(const POSITION *positions, uint idx, table_map found_ref);
void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist);
struct st_cond_statistic
1
0
[Commits] e1d8522e652: MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
by Varun 10 Sep '19
by Varun 10 Sep '19
10 Sep '19
revision-id: e1d8522e6529720cb6fea2d533d07871a3c06046 (mariadb-10.1.41-34-ge1d8522e652)
parent(s): 0e38cd37c7be46ac2b57344476d68a6dc59bc3a4
author: Varun Gupta
committer: Varun Gupta
timestamp: 2019-09-11 00:51:20 +0530
message:
MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
The issue here was that inside the function table_cond_selectivity we were trying to use
the ranges estimate for a key for which no sargable condition was present.
The fix for this issue is to make reset the quick structures when we read a TABLE structure
from the table cache. This would ensure that we don't read some estimate of previous queries
---
mysql-test/r/selectivity.result | 56 ++++++++++++++++++++++++++++++++++
mysql-test/r/selectivity_innodb.result | 47 ++++++++++++++++++++++++++++
mysql-test/t/selectivity.test | 32 +++++++++++++++++++
sql/sql_select.cc | 1 +
sql/table.cc | 20 ++++++++++++
sql/table.h | 1 +
6 files changed, 157 insertions(+)
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index 3f5db42d341..db8ff2c53c7 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -1668,4 +1668,60 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
drop table t1;
set use_stat_tables= @save_use_stat_tables;
set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
+# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
+#
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+set optimizer_switch='exists_to_in=off';
+set optimizer_use_condition_selectivity=2;
+SELECT * FROM t1
+WHERE
+EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id
+WHERE A.a=t1.a AND t2.b < 20);
+id a
+1 1
+2 2
+3 3
+4 4
+5 5
+6 6
+7 7
+8 8
+9 9
+10 10
+11 11
+12 12
+13 13
+14 14
+15 15
+16 16
+17 17
+18 18
+19 19
+explain SELECT * FROM t1
+WHERE
+EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id
+WHERE A.a=t1.a AND t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where
+2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where
+EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1
+1 SIMPLE B ref a a 5 const 1
+explain SELECT * FROM t1
+WHERE
+EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id
+WHERE A.a=t1.a AND t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where
+2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where
+drop table t1,t2;
+set optimizer_switch= @save_optimizer_switch;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
# End of 10.1 tests
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 1d73c2f5d50..f65eddf36ae 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -1678,6 +1678,53 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
drop table t1;
set use_stat_tables= @save_use_stat_tables;
set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
+# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
+#
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+set optimizer_switch='exists_to_in=off';
+set optimizer_use_condition_selectivity=2;
+select * from t1 where EXISTS (select * from t1 A INNER JOIN t2 ON t2.a = A.id where A.a=t1.a and t2.b < 20);
+id a
+1 1
+2 2
+3 3
+4 4
+5 5
+6 6
+7 7
+8 8
+9 9
+10 10
+11 11
+12 12
+13 13
+14 14
+15 15
+16 16
+17 17
+18 18
+19 19
+explain select * from t1 where EXISTS (select * from t1 A INNER JOIN t2 ON t2.a = A.id where A.a=t1.a and t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index
+2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 Using index
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where
+EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1
+1 SIMPLE B ref a a 5 const 1 Using index
+explain select * from t1 where EXISTS (select * from t1 A INNER JOIN t2 ON t2.a = A.id where A.a=t1.a and t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index
+2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 Using index
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where
+drop table t1,t2;
+set optimizer_switch= @save_optimizer_switch;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
# End of 10.1 tests
set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
set @tmp_ust= @@use_stat_tables;
diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test
index f1c9d6b31b8..6f79927e5d6 100644
--- a/mysql-test/t/selectivity.test
+++ b/mysql-test/t/selectivity.test
@@ -1124,5 +1124,37 @@ drop table t1;
set use_stat_tables= @save_use_stat_tables;
set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+--echo #
+--echo # MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
+--echo #
+
+--source include/have_sequence.inc
+
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+
+set optimizer_switch='exists_to_in=off';
+set optimizer_use_condition_selectivity=2;
+
+let $query= SELECT * FROM t1
+ WHERE
+ EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id
+ WHERE A.a=t1.a AND t2.b < 20);
+
+eval $query;
+eval explain $query;
+
+EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+
+eval explain $query;
+
+drop table t1,t2;
+set optimizer_switch= @save_optimizer_switch;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+
--echo # End of 10.1 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5b96c15bff5..73299b9cdc9 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7759,6 +7759,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s,
sel*= table_multi_eq_cond_selectivity(join, idx, s, rem_tables,
keyparts, ref_keyuse_steps);
+ DBUG_ASSERT(sel > 0 && sel <= 1);
return sel;
}
diff --git a/sql/table.cc b/sql/table.cc
index 94cd174ffd7..ac46ff6a42c 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -4162,6 +4162,8 @@ void TABLE::init(THD *thd, TABLE_LIST *tl)
created= TRUE;
cond_selectivity= 1.0;
cond_selectivity_sampling_explain= NULL;
+ quick_condition_rows= 0;
+ initialize_quick_structures();
#ifdef HAVE_REPLICATION
/* used in RBR Triggers */
master_had_triggers= 0;
@@ -7546,3 +7548,21 @@ bool fk_modifies_child(enum_fk_option opt)
static bool can_write[]= { false, false, true, true, false, true };
return can_write[opt];
}
+
+/*
+ @brief
+ Initialize all the quick structures that are used to stored the
+ estimates when the range optimizer is run.
+ @details
+ This is specifically needed when we read the TABLE structure from the
+ table cache. There can be some garbage data from previous queries
+ that need to be reset here.
+*/
+
+void TABLE::initialize_quick_structures()
+{
+ bzero(quick_rows, sizeof(quick_rows));
+ bzero(quick_key_parts, sizeof(quick_key_parts));
+ bzero(quick_costs, sizeof(quick_costs));
+ bzero(quick_n_ranges, sizeof(quick_n_ranges));
+}
diff --git a/sql/table.h b/sql/table.h
index 98ec9f005ea..44803b5aacd 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1450,6 +1450,7 @@ struct TABLE
}
bool update_const_key_parts(COND *conds);
+ void initialize_quick_structures();
my_ptrdiff_t default_values_offset() const
{ return (my_ptrdiff_t) (s->default_values - record[0]); }
1
0
[Commits] 4eb7a964f21: Issue #790: MultiGet-based MRR: Check if the query has been killed
by Sergei Petrunia 09 Sep '19
by Sergei Petrunia 09 Sep '19
09 Sep '19
revision-id: 4eb7a964f21d743d816d41870efd9133d4198469 (fb-prod201903-160-g4eb7a964f21)
parent(s): 0a669d663cffceabcfd63e0b45fef9338e198a26
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-09-09 17:56:03 +0300
message:
Issue #790: MultiGet-based MRR: Check if the query has been killed
The loop in mrr_fill_buffer() should have a check whether the query
has been KILL-ed.
---
.../suite/rocksdb/r/rocksdb_mrr_debug.result | 40 +++++++++++++++
mysql-test/suite/rocksdb/t/rocksdb_mrr_debug.test | 58 ++++++++++++++++++++++
storage/rocksdb/ha_rocksdb.cc | 19 +++++--
3 files changed, 113 insertions(+), 4 deletions(-)
diff --git a/mysql-test/suite/rocksdb/r/rocksdb_mrr_debug.result b/mysql-test/suite/rocksdb/r/rocksdb_mrr_debug.result
new file mode 100644
index 00000000000..ff321b5d269
--- /dev/null
+++ b/mysql-test/suite/rocksdb/r/rocksdb_mrr_debug.result
@@ -0,0 +1,40 @@
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int);
+insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t2 (
+pk int primary key,
+col1 int,
+filler char(32)
+) engine=rocksdb;
+insert into t2 select a,a,a from t1;
+set global rocksdb_force_flush_memtable_now=1;
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
+explain
+select * from t2,t0 where t2.pk=t0.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using join buffer (Batched Key Access)
+select variable_value into @n_multiget_orig
+from information_schema.global_status where variable_name='ROCKSDB_NUMBER_MULTIGET_GET';
+set debug_sync = 'rocksdb.mrr_fill_buffer.loop SIGNAL target_ready WAIT_FOR simulate_kill';
+select * from t2,t0 where t2.pk=t0.a;
+connect con1,localhost,root,,;
+set debug_sync = 'now WAIT_FOR target_ready';
+set @a= (select id from information_schema.processlist where state='debug sync point: rocksdb.mrr_fill_buffer.loop');
+kill query @a;
+connect con2,localhost,root,,;
+set debug_sync = 'now SIGNAL simulate_kill';
+connection con1;
+connection default;
+ERROR 70100: Query execution was interrupted
+# Check that we didn't continue till the MultiGet() call. The following will
+# return 0:
+select variable_value into @n_multiget_new
+from information_schema.global_status where variable_name='ROCKSDB_NUMBER_MULTIGET_GET';
+select @n_multiget_new - @n_multiget_orig;
+@n_multiget_new - @n_multiget_orig
+0
+set optimizer_switch=@save_optimizer_switch;
+drop table t0,t1,t2;
diff --git a/mysql-test/suite/rocksdb/t/rocksdb_mrr_debug.test b/mysql-test/suite/rocksdb/t/rocksdb_mrr_debug.test
new file mode 100644
index 00000000000..f35e57b43da
--- /dev/null
+++ b/mysql-test/suite/rocksdb/t/rocksdb_mrr_debug.test
@@ -0,0 +1,58 @@
+--source include/have_rocksdb.inc
+--source include/have_debug_sync.inc
+
+--enable_connect_log
+
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1(a int);
+insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+
+create table t2 (
+ pk int primary key,
+ col1 int,
+ filler char(32)
+) engine=rocksdb;
+
+insert into t2 select a,a,a from t1;
+set global rocksdb_force_flush_memtable_now=1;
+
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
+
+explain
+select * from t2,t0 where t2.pk=t0.a;
+
+select variable_value into @n_multiget_orig
+from information_schema.global_status where variable_name='ROCKSDB_NUMBER_MULTIGET_GET';
+
+set debug_sync = 'rocksdb.mrr_fill_buffer.loop SIGNAL target_ready WAIT_FOR simulate_kill';
+send select * from t2,t0 where t2.pk=t0.a;
+
+connect (con1,localhost,root,,);
+set debug_sync = 'now WAIT_FOR target_ready';
+set @a= (select id from information_schema.processlist where state='debug sync point: rocksdb.mrr_fill_buffer.loop');
+send kill query @a;
+
+# Note: it seems, just KILL QUERY will already cause debug sync point wait to
+# finish. Leave the signal anyway since it doesn't hurt
+connect (con2,localhost,root,,);
+set debug_sync = 'now SIGNAL simulate_kill';
+
+connection con1;
+reap;
+
+connection default;
+--error ER_QUERY_INTERRUPTED
+reap;
+
+--echo # Check that we didn't continue till the MultiGet() call. The following will
+--echo # return 0:
+select variable_value into @n_multiget_new
+from information_schema.global_status where variable_name='ROCKSDB_NUMBER_MULTIGET_GET';
+
+select @n_multiget_new - @n_multiget_orig;
+
+set optimizer_switch=@save_optimizer_switch;
+drop table t0,t1,t2;
diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc
index 3bc8b064e39..e490684e79b 100644
--- a/storage/rocksdb/ha_rocksdb.cc
+++ b/storage/rocksdb/ha_rocksdb.cc
@@ -15322,8 +15322,9 @@ int ha_rocksdb::multi_range_read_init(RANGE_SEQ_IF *seq, void *seq_init_param,
mrr_rowid_reader = reader;
mrr_n_rowids = SSIZE_MAX-1; // TODO: get rid of this
}
- mrr_fill_buffer();
- return 0;
+
+ res = mrr_fill_buffer();
+ return res;
}
uint ha_rocksdb::mrr_get_length_per_rec() {
@@ -15359,7 +15360,7 @@ int ha_rocksdb::mrr_fill_buffer() {
if (n_elements < 1) {
DBUG_ASSERT(0);
- return 1; // error
+ return HA_ERR_INTERNAL_ERROR; // error
}
// TODO: why are we allocating/de-allocating every time buffer is refilled?
char *buf = (char *)mrr_buf.buffer;
@@ -15380,6 +15381,11 @@ int ha_rocksdb::mrr_fill_buffer() {
char *range_ptr;
while ((key_size = mrr_rowid_reader->get_next_rowid((uchar*)buf, &range_ptr)) > 0 ) {
+ DEBUG_SYNC(table->in_use, "rocksdb.mrr_fill_buffer.loop");
+ if (table->in_use->killed) {
+ return HA_ERR_QUERY_INTERRUPTED;
+ }
+
elem++;
mrr_keys[elem] = rocksdb::Slice(buf, key_size);
mrr_range_ptrs[elem] = range_ptr;
@@ -15443,7 +15449,12 @@ int ha_rocksdb::multi_range_read_next(char **range_info) {
mrr_free_rows();
return HA_ERR_END_OF_FILE;
}
- mrr_fill_buffer();
+ int res;
+
+ if ((res = mrr_fill_buffer())) {
+ return res;
+ }
+
if (!mrr_n_elements) {
table->status = STATUS_NOT_FOUND; // not sure if this is necessary?
return HA_ERR_END_OF_FILE;
1
0
revision-id: 0a669d663cffceabcfd63e0b45fef9338e198a26 (fb-prod201903-159-g0a669d663cf)
parent(s): f3e2b2d34578fafa3fcb32d125ff702b10154bdd
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-09-09 17:21:15 +0300
message:
Post-rebase fixes
---
storage/rocksdb/ha_rocksdb.cc | 4 ----
1 file changed, 4 deletions(-)
diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc
index 52199cfbbcb..3bc8b064e39 100644
--- a/storage/rocksdb/ha_rocksdb.cc
+++ b/storage/rocksdb/ha_rocksdb.cc
@@ -3493,8 +3493,6 @@ class Rdb_transaction_impl : public Rdb_transaction {
statuses, sorted_input);
}
- }
-
rocksdb::Status get_for_update(
rocksdb::ColumnFamilyHandle *const column_family,
const rocksdb::Slice &key, rocksdb::PinnableSlice *const value,
@@ -3811,8 +3809,6 @@ class Rdb_writebatch_impl : public Rdb_transaction {
keys, values, statuses, sorted_input);
}
- }
-
rocksdb::Iterator *get_iterator(
const rocksdb::ReadOptions &options,
rocksdb::ColumnFamilyHandle *const /* column_family */) override {
1
0
[Commits] f98c6a9db8c: MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
by Varun 08 Sep '19
by Varun 08 Sep '19
08 Sep '19
revision-id: f98c6a9db8c1c8e40761141aadafe1a2ff579526 (mariadb-10.1.41-34-gf98c6a9db8c)
parent(s): 0e38cd37c7be46ac2b57344476d68a6dc59bc3a4
author: Varun Gupta
committer: Varun Gupta
timestamp: 2019-09-08 23:01:47 +0530
message:
MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
The issue here was that inside the function table_cond_selectivity we were trying to use
the ranges estimate for a key for which no sargable condition was present.
The fix for this issue is to make reset the quick structures when we read a TABLE structure
from the table cache. This would ensure that we don't read some estimate of previous queries
---
mysql-test/r/stat_tables.result | 45 ++++++++++++++++++++++++++++++++++
mysql-test/r/stat_tables_innodb.result | 45 ++++++++++++++++++++++++++++++++++
mysql-test/t/stat_tables.test | 26 ++++++++++++++++++++
sql/table.cc | 20 +++++++++++++++
sql/table.h | 1 +
5 files changed, 137 insertions(+)
diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index d26221b5f8d..81c93203b4a 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -739,6 +739,51 @@ db_name table_name index_name prefix_arity avg_frequency a b
test t1 k1 1 1.0000 2 2
test t1 k1 1 1.0000 3 3
drop table t1;
+#
+# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
+#
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+set optimizer_switch='exists_to_in=off';
+set optimizer_use_condition_selectivity=4;
+select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20);
+id a
+1 1
+2 2
+3 3
+4 4
+5 5
+6 6
+7 7
+8 8
+9 9
+10 10
+11 11
+12 12
+13 13
+14 14
+15 15
+16 16
+17 17
+18 18
+19 19
+explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where
+2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where
+EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1
+1 SIMPLE B ref a a 5 const 1
+explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where
+2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where
+drop table t1,t2;
set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
set @save_optimizer_switch=@@optimizer_switch;
set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index b8bed681465..227aada41fd 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -766,6 +766,51 @@ db_name table_name index_name prefix_arity avg_frequency a b
test t1 k1 1 1.0000 2 2
test t1 k1 1 1.0000 3 3
drop table t1;
+#
+# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
+#
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+set optimizer_switch='exists_to_in=off';
+set optimizer_use_condition_selectivity=4;
+select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20);
+id a
+1 1
+2 2
+3 3
+4 4
+5 5
+6 6
+7 7
+8 8
+9 9
+10 10
+11 11
+12 12
+13 13
+14 14
+15 15
+16 16
+17 17
+18 18
+19 19
+explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index
+2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1 Using index
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where
+EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1
+1 SIMPLE B ref a a 5 const 1 Using index
+explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index
+2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1 Using index
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where
+drop table t1,t2;
set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
set @save_optimizer_switch=@@optimizer_switch;
set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index e9f37698a73..2c6ea556e28 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -498,6 +498,32 @@ analyze table t1;
select * from mysql.index_stats, t1 where index_name='k1' and t1.a > 1 and t1.b > 1;
drop table t1;
+--echo #
+--echo # MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
+--echo #
+
+--source include/have_sequence.inc
+
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+
+set optimizer_switch='exists_to_in=off';
+set optimizer_use_condition_selectivity=4;
+
+let $query= select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20);
+
+eval $query;
+eval explain $query;
+
+EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+
+eval explain $query;
+
+drop table t1,t2;
+
set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
set @save_optimizer_switch=@@optimizer_switch;
diff --git a/sql/table.cc b/sql/table.cc
index 94cd174ffd7..ac46ff6a42c 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -4162,6 +4162,8 @@ void TABLE::init(THD *thd, TABLE_LIST *tl)
created= TRUE;
cond_selectivity= 1.0;
cond_selectivity_sampling_explain= NULL;
+ quick_condition_rows= 0;
+ initialize_quick_structures();
#ifdef HAVE_REPLICATION
/* used in RBR Triggers */
master_had_triggers= 0;
@@ -7546,3 +7548,21 @@ bool fk_modifies_child(enum_fk_option opt)
static bool can_write[]= { false, false, true, true, false, true };
return can_write[opt];
}
+
+/*
+ @brief
+ Initialize all the quick structures that are used to stored the
+ estimates when the range optimizer is run.
+ @details
+ This is specifically needed when we read the TABLE structure from the
+ table cache. There can be some garbage data from previous queries
+ that need to be reset here.
+*/
+
+void TABLE::initialize_quick_structures()
+{
+ bzero(quick_rows, sizeof(quick_rows));
+ bzero(quick_key_parts, sizeof(quick_key_parts));
+ bzero(quick_costs, sizeof(quick_costs));
+ bzero(quick_n_ranges, sizeof(quick_n_ranges));
+}
diff --git a/sql/table.h b/sql/table.h
index 98ec9f005ea..44803b5aacd 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1450,6 +1450,7 @@ struct TABLE
}
bool update_const_key_parts(COND *conds);
+ void initialize_quick_structures();
my_ptrdiff_t default_values_offset() const
{ return (my_ptrdiff_t) (s->default_values - record[0]); }
1
0
[Commits] 4b1ab57f1c5: MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
by Varun 07 Sep '19
by Varun 07 Sep '19
07 Sep '19
revision-id: 4b1ab57f1c5ec26503f2c7ebeaea8343fb424a23 (mariadb-10.1.41-34-g4b1ab57f1c5)
parent(s): 0e38cd37c7be46ac2b57344476d68a6dc59bc3a4
author: Varun Gupta
committer: Varun Gupta
timestamp: 2019-09-07 14:41:46 +0530
message:
MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
The issue here was that inside the function table_cond_selectivity we were trying to use
the ranges estimate for a key for which no sargable condition was present.
The fix for this issue is to make sure to first check if range access is possible on the key,
if yes only the use its estimate.
---
mysql-test/r/stat_tables.result | 45 ++++++++++++++++++++++++++++++++++
mysql-test/r/stat_tables_innodb.result | 45 ++++++++++++++++++++++++++++++++++
mysql-test/t/stat_tables.test | 26 ++++++++++++++++++++
sql/sql_select.cc | 2 +-
4 files changed, 117 insertions(+), 1 deletion(-)
diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index d26221b5f8d..81c93203b4a 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -739,6 +739,51 @@ db_name table_name index_name prefix_arity avg_frequency a b
test t1 k1 1 1.0000 2 2
test t1 k1 1 1.0000 3 3
drop table t1;
+#
+# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
+#
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+set optimizer_switch='exists_to_in=off';
+set optimizer_use_condition_selectivity=4;
+select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20);
+id a
+1 1
+2 2
+3 3
+4 4
+5 5
+6 6
+7 7
+8 8
+9 9
+10 10
+11 11
+12 12
+13 13
+14 14
+15 15
+16 16
+17 17
+18 18
+19 19
+explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where
+2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where
+EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1
+1 SIMPLE B ref a a 5 const 1
+explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where
+2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where
+drop table t1,t2;
set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
set @save_optimizer_switch=@@optimizer_switch;
set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index b8bed681465..227aada41fd 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -766,6 +766,51 @@ db_name table_name index_name prefix_arity avg_frequency a b
test t1 k1 1 1.0000 2 2
test t1 k1 1 1.0000 3 3
drop table t1;
+#
+# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
+#
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+set optimizer_switch='exists_to_in=off';
+set optimizer_use_condition_selectivity=4;
+select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20);
+id a
+1 1
+2 2
+3 3
+4 4
+5 5
+6 6
+7 7
+8 8
+9 9
+10 10
+11 11
+12 12
+13 13
+14 14
+15 15
+16 16
+17 17
+18 18
+19 19
+explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index
+2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1 Using index
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where
+EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1
+1 SIMPLE B ref a a 5 const 1 Using index
+explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index
+2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1 Using index
+2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where
+drop table t1,t2;
set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
set @save_optimizer_switch=@@optimizer_switch;
set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index e9f37698a73..2c6ea556e28 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -498,6 +498,32 @@ analyze table t1;
select * from mysql.index_stats, t1 where index_name='k1' and t1.a > 1 and t1.b > 1;
drop table t1;
+--echo #
+--echo # MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
+--echo #
+
+--source include/have_sequence.inc
+
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+
+set optimizer_switch='exists_to_in=off';
+set optimizer_use_condition_selectivity=4;
+
+let $query= select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20);
+
+eval $query;
+eval explain $query;
+
+EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+
+eval explain $query;
+
+drop table t1,t2;
+
set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
set @save_optimizer_switch=@@optimizer_switch;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5b96c15bff5..cc209c229a3 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7623,7 +7623,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s,
/*
Check if we have a prefix of key=const that matches a quick select.
*/
- if (!is_hash_join_key_no(key))
+ if (!is_hash_join_key_no(key) && table->quick_keys.is_set(key))
{
table_map quick_key_map= (table_map(1) << table->quick_key_parts[key]) - 1;
if (table->quick_rows[key] &&
1
0
[Commits] 0bc3958a30c: Support Create_time and Update_time in MyRocks table status
by psergey 07 Sep '19
by psergey 07 Sep '19
07 Sep '19
revision-id: 0bc3958a30c54d88585f890acee20d3908caa958 (fb-prod201903-144-g0bc3958a30c)
parent(s): d97c0c628e5dc60abd725f6a7120a8d87b09321e
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-09-07 10:43:08 +0300
message:
Support Create_time and Update_time in MyRocks table status
- Create_time is stored in the MyRocks' internal data dictionary.
- Update_time is in-memory only (like in InnoDB).
---
mysql-test/suite/rocksdb/include/bulk_load.inc | 4 +-
.../suite/rocksdb/include/bulk_load_unsorted.inc | 4 +-
mysql-test/suite/rocksdb/r/bulk_load.result | 12 ++--
mysql-test/suite/rocksdb/r/bulk_load_rev_cf.result | 12 ++--
.../rocksdb/r/bulk_load_rev_cf_and_data.result | 12 ++--
.../suite/rocksdb/r/bulk_load_rev_data.result | 12 ++--
.../suite/rocksdb/r/bulk_load_unsorted.result | 12 ++--
.../suite/rocksdb/r/bulk_load_unsorted_rev.result | 12 ++--
mysql-test/suite/rocksdb/r/issue255.result | 16 +++---
mysql-test/suite/rocksdb/r/rocksdb.result | 6 +-
.../suite/rocksdb/r/show_table_status.result | 44 ++++++++++++--
mysql-test/suite/rocksdb/r/truncate_table.result | 8 +--
mysql-test/suite/rocksdb/t/issue255.test | 17 +++---
mysql-test/suite/rocksdb/t/rocksdb.test | 4 +-
mysql-test/suite/rocksdb/t/show_table_status.test | 37 +++++++++++-
mysql-test/suite/rocksdb/t/truncate_table.test | 8 +--
storage/rocksdb/ha_rocksdb.cc | 37 ++++++++++++
storage/rocksdb/rdb_datadic.cc | 67 ++++++++++++++++++++++
storage/rocksdb/rdb_datadic.h | 25 ++++++++
storage/rocksdb/rdb_global.h | 2 +
20 files changed, 277 insertions(+), 74 deletions(-)
diff --git a/mysql-test/suite/rocksdb/include/bulk_load.inc b/mysql-test/suite/rocksdb/include/bulk_load.inc
index 1b79825e507..7e163602202 100644
--- a/mysql-test/suite/rocksdb/include/bulk_load.inc
+++ b/mysql-test/suite/rocksdb/include/bulk_load.inc
@@ -121,12 +121,12 @@ set rocksdb_bulk_load=0;
--remove_file $file
# Make sure row count index stats are correct
---replace_column 6 # 7 # 8 # 9 #
+--replace_column 6 # 7 # 8 # 9 # 12 # 13 #
SHOW TABLE STATUS WHERE name LIKE 't%';
ANALYZE TABLE t1, t2, t3;
---replace_column 6 # 7 # 8 # 9 #
+--replace_column 6 # 7 # 8 # 9 # 12 # 13 #
SHOW TABLE STATUS WHERE name LIKE 't%';
# Make sure all the data is there.
diff --git a/mysql-test/suite/rocksdb/include/bulk_load_unsorted.inc b/mysql-test/suite/rocksdb/include/bulk_load_unsorted.inc
index 5cdc76a32d4..812af0401aa 100644
--- a/mysql-test/suite/rocksdb/include/bulk_load_unsorted.inc
+++ b/mysql-test/suite/rocksdb/include/bulk_load_unsorted.inc
@@ -119,12 +119,12 @@ set rocksdb_bulk_load=0;
--remove_file $file
# Make sure row count index stats are correct
---replace_column 6 # 7 # 8 # 9 #
+--replace_column 6 # 7 # 8 # 9 # 12 # 13 #
SHOW TABLE STATUS WHERE name LIKE 't%';
ANALYZE TABLE t1, t2, t3;
---replace_column 6 # 7 # 8 # 9 #
+--replace_column 6 # 7 # 8 # 9 # 12 # 13 #
SHOW TABLE STATUS WHERE name LIKE 't%';
# Make sure all the data is there.
diff --git a/mysql-test/suite/rocksdb/r/bulk_load.result b/mysql-test/suite/rocksdb/r/bulk_load.result
index a36f99a7619..76db28e66bd 100644
--- a/mysql-test/suite/rocksdb/r/bulk_load.result
+++ b/mysql-test/suite/rocksdb/r/bulk_load.result
@@ -38,9 +38,9 @@ pk a b
set rocksdb_bulk_load=0;
SHOW TABLE STATUS WHERE name LIKE 't%';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned
+t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL partitioned
ANALYZE TABLE t1, t2, t3;
Table Op Msg_type Msg_text
test.t1 analyze status OK
@@ -48,9 +48,9 @@ test.t2 analyze status OK
test.t3 analyze status OK
SHOW TABLE STATUS WHERE name LIKE 't%';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned
+t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL partitioned
select count(pk) from t1;
count(pk)
5000000
diff --git a/mysql-test/suite/rocksdb/r/bulk_load_rev_cf.result b/mysql-test/suite/rocksdb/r/bulk_load_rev_cf.result
index b5d3e252c5d..ae363f7ec0c 100644
--- a/mysql-test/suite/rocksdb/r/bulk_load_rev_cf.result
+++ b/mysql-test/suite/rocksdb/r/bulk_load_rev_cf.result
@@ -38,9 +38,9 @@ pk a b
set rocksdb_bulk_load=0;
SHOW TABLE STATUS WHERE name LIKE 't%';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned
+t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL partitioned
ANALYZE TABLE t1, t2, t3;
Table Op Msg_type Msg_text
test.t1 analyze status OK
@@ -48,9 +48,9 @@ test.t2 analyze status OK
test.t3 analyze status OK
SHOW TABLE STATUS WHERE name LIKE 't%';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned
+t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL partitioned
select count(pk) from t1;
count(pk)
5000000
diff --git a/mysql-test/suite/rocksdb/r/bulk_load_rev_cf_and_data.result b/mysql-test/suite/rocksdb/r/bulk_load_rev_cf_and_data.result
index f46acd41080..dd8dd7e60a8 100644
--- a/mysql-test/suite/rocksdb/r/bulk_load_rev_cf_and_data.result
+++ b/mysql-test/suite/rocksdb/r/bulk_load_rev_cf_and_data.result
@@ -38,9 +38,9 @@ pk a b
set rocksdb_bulk_load=0;
SHOW TABLE STATUS WHERE name LIKE 't%';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned
+t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL partitioned
ANALYZE TABLE t1, t2, t3;
Table Op Msg_type Msg_text
test.t1 analyze status OK
@@ -48,9 +48,9 @@ test.t2 analyze status OK
test.t3 analyze status OK
SHOW TABLE STATUS WHERE name LIKE 't%';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned
+t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL partitioned
select count(pk) from t1;
count(pk)
5000000
diff --git a/mysql-test/suite/rocksdb/r/bulk_load_rev_data.result b/mysql-test/suite/rocksdb/r/bulk_load_rev_data.result
index 3389968ef37..96738ae62e2 100644
--- a/mysql-test/suite/rocksdb/r/bulk_load_rev_data.result
+++ b/mysql-test/suite/rocksdb/r/bulk_load_rev_data.result
@@ -38,9 +38,9 @@ pk a b
set rocksdb_bulk_load=0;
SHOW TABLE STATUS WHERE name LIKE 't%';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned
+t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL partitioned
ANALYZE TABLE t1, t2, t3;
Table Op Msg_type Msg_text
test.t1 analyze status OK
@@ -48,9 +48,9 @@ test.t2 analyze status OK
test.t3 analyze status OK
SHOW TABLE STATUS WHERE name LIKE 't%';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL
-t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned
+t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL
+t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_bin NULL partitioned
select count(pk) from t1;
count(pk)
5000000
diff --git a/mysql-test/suite/rocksdb/r/bulk_load_unsorted.result b/mysql-test/suite/rocksdb/r/bulk_load_unsorted.result
index 924032549ac..87fc63af2da 100644
--- a/mysql-test/suite/rocksdb/r/bulk_load_unsorted.result
+++ b/mysql-test/suite/rocksdb/r/bulk_load_unsorted.result
@@ -70,9 +70,9 @@ LOAD DATA INFILE <input_file> INTO TABLE t3;
set rocksdb_bulk_load=0;
SHOW TABLE STATUS WHERE name LIKE 't%';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL
-t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL
-t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned
+t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_swedish_ci NULL
+t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_swedish_ci NULL
+t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_swedish_ci NULL partitioned
ANALYZE TABLE t1, t2, t3;
Table Op Msg_type Msg_text
test.t1 analyze status OK
@@ -80,9 +80,9 @@ test.t2 analyze status OK
test.t3 analyze status OK
SHOW TABLE STATUS WHERE name LIKE 't%';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL
-t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL
-t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned
+t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_swedish_ci NULL
+t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_swedish_ci NULL
+t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_swedish_ci NULL partitioned
select count(a) from t1;
count(a)
5000000
diff --git a/mysql-test/suite/rocksdb/r/bulk_load_unsorted_rev.result b/mysql-test/suite/rocksdb/r/bulk_load_unsorted_rev.result
index 3cc9fb8e459..8e0914f0159 100644
--- a/mysql-test/suite/rocksdb/r/bulk_load_unsorted_rev.result
+++ b/mysql-test/suite/rocksdb/r/bulk_load_unsorted_rev.result
@@ -70,9 +70,9 @@ LOAD DATA INFILE <input_file> INTO TABLE t3;
set rocksdb_bulk_load=0;
SHOW TABLE STATUS WHERE name LIKE 't%';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL
-t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL
-t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned
+t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_swedish_ci NULL
+t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_swedish_ci NULL
+t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_swedish_ci NULL partitioned
ANALYZE TABLE t1, t2, t3;
Table Op Msg_type Msg_text
test.t1 analyze status OK
@@ -80,9 +80,9 @@ test.t2 analyze status OK
test.t3 analyze status OK
SHOW TABLE STATUS WHERE name LIKE 't%';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL
-t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL
-t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned
+t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_swedish_ci NULL
+t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_swedish_ci NULL
+t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL # # NULL latin1_swedish_ci NULL partitioned
select count(a) from t1;
count(a)
5000000
diff --git a/mysql-test/suite/rocksdb/r/issue255.result b/mysql-test/suite/rocksdb/r/issue255.result
index c1ce3be2276..b45b3b5afc7 100644
--- a/mysql-test/suite/rocksdb/r/issue255.result
+++ b/mysql-test/suite/rocksdb/r/issue255.result
@@ -2,7 +2,7 @@ CREATE TABLE t1 (pk BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT);
INSERT INTO t1 VALUES (5);
SHOW TABLE STATUS LIKE 't1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB # Fixed # # # # # # 6 NULL NULL NULL latin1_swedish_ci NULL
+t1 ROCKSDB # Fixed # # # # # # 6 # # NULL latin1_swedish_ci NULL
INSERT INTO t1 VALUES ('538647864786478647864');
Warnings:
Warning 1264 Out of range value for column 'pk' at row 1
@@ -12,7 +12,7 @@ pk
9223372036854775807
SHOW TABLE STATUS LIKE 't1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 2 22 44 0 0 0 9223372036854775807 NULL NULL NULL latin1_swedish_ci NULL
+t1 ROCKSDB 10 Fixed 2 22 44 0 0 0 9223372036854775807 # # NULL latin1_swedish_ci NULL
INSERT INTO t1 VALUES ();
ERROR 23000: Duplicate entry '9223372036854775807' for key 'PRIMARY'
SELECT * FROM t1;
@@ -21,7 +21,7 @@ pk
9223372036854775807
SHOW TABLE STATUS LIKE 't1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB # Fixed # # # # # # 9223372036854775807 NULL NULL NULL latin1_swedish_ci NULL
+t1 ROCKSDB # Fixed # # # # # # 9223372036854775807 # # NULL latin1_swedish_ci NULL
INSERT INTO t1 VALUES ();
ERROR 23000: Duplicate entry '9223372036854775807' for key 'PRIMARY'
SELECT * FROM t1;
@@ -30,13 +30,13 @@ pk
9223372036854775807
SHOW TABLE STATUS LIKE 't1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB # Fixed # # # # # # 9223372036854775807 NULL NULL NULL latin1_swedish_ci NULL
+t1 ROCKSDB # Fixed # # # # # # 9223372036854775807 # # NULL latin1_swedish_ci NULL
DROP TABLE t1;
CREATE TABLE t1 (pk TINYINT NOT NULL PRIMARY KEY AUTO_INCREMENT);
INSERT INTO t1 VALUES (5);
SHOW TABLE STATUS LIKE 't1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB # Fixed # # # # # # 6 NULL NULL NULL latin1_swedish_ci NULL
+t1 ROCKSDB # Fixed # # # # # # 6 # # NULL latin1_swedish_ci NULL
INSERT INTO t1 VALUES (1000);
Warnings:
Warning 1264 Out of range value for column 'pk' at row 1
@@ -46,7 +46,7 @@ pk
127
SHOW TABLE STATUS LIKE 't1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB # Fixed # # # # # # 127 NULL NULL NULL latin1_swedish_ci NULL
+t1 ROCKSDB # Fixed # # # # # # 127 # # NULL latin1_swedish_ci NULL
INSERT INTO t1 VALUES ();
ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
SELECT * FROM t1;
@@ -55,7 +55,7 @@ pk
127
SHOW TABLE STATUS LIKE 't1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB # Fixed # # # # # # 127 NULL NULL NULL latin1_swedish_ci NULL
+t1 ROCKSDB # Fixed # # # # # # 127 # # NULL latin1_swedish_ci NULL
INSERT INTO t1 VALUES ();
ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
SELECT * FROM t1;
@@ -64,5 +64,5 @@ pk
127
SHOW TABLE STATUS LIKE 't1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB # Fixed # # # # # # 127 NULL NULL NULL latin1_swedish_ci NULL
+t1 ROCKSDB # Fixed # # # # # # 127 # # NULL latin1_swedish_ci NULL
DROP TABLE t1;
diff --git a/mysql-test/suite/rocksdb/r/rocksdb.result b/mysql-test/suite/rocksdb/r/rocksdb.result
index 088eb050f6f..a631d58ac69 100644
--- a/mysql-test/suite/rocksdb/r/rocksdb.result
+++ b/mysql-test/suite/rocksdb/r/rocksdb.result
@@ -1417,7 +1417,7 @@ create table t1 (i int primary key auto_increment) engine=RocksDB;
insert into t1 values (null),(null);
show table status like 't1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 1000 0 # 0 0 0 3 NULL NULL NULL latin1_swedish_ci NULL
+t1 ROCKSDB 10 Fixed 1000 0 # 0 0 0 3 # # NULL latin1_swedish_ci NULL
drop table t1;
#
# Fix Issue #4: Crash when using pseudo-unique keys
@@ -2612,7 +2612,7 @@ CREATE TABLE t1(a INT AUTO_INCREMENT KEY);
INSERT INTO t1 VALUES(0),(-1),(0);
SHOW TABLE STATUS LIKE 't1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 1000 0 0 0 0 0 3 NULL NULL NULL latin1_swedish_ci NULL
+t1 ROCKSDB 10 Fixed 1000 0 0 0 0 0 3 # # NULL latin1_swedish_ci NULL
SELECT * FROM t1;
a
-1
@@ -2623,7 +2623,7 @@ CREATE TABLE t1(a INT AUTO_INCREMENT KEY);
INSERT INTO t1 VALUES(0),(10),(0);
SHOW TABLE STATUS LIKE 't1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 1000 0 0 0 0 0 12 NULL NULL NULL latin1_swedish_ci NULL
+t1 ROCKSDB 10 Fixed 1000 0 0 0 0 0 12 # # NULL latin1_swedish_ci NULL
SELECT * FROM t1;
a
1
diff --git a/mysql-test/suite/rocksdb/r/show_table_status.result b/mysql-test/suite/rocksdb/r/show_table_status.result
index 29140f045e4..1d24463837f 100644
--- a/mysql-test/suite/rocksdb/r/show_table_status.result
+++ b/mysql-test/suite/rocksdb/r/show_table_status.result
@@ -7,12 +7,12 @@ set global rocksdb_force_flush_memtable_now = true;
CREATE TABLE t3 (a INT, b CHAR(8), pk INT PRIMARY KEY) ENGINE=rocksdb CHARACTER SET utf8;
SHOW TABLE STATUS WHERE name IN ( 't1', 't2', 't3' );
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed 1000 # # 0 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL
-t2 ROCKSDB 10 Fixed 1000 # # 0 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL
-t3 ROCKSDB 10 Fixed 1000 # # 0 0 0 NULL NULL NULL NULL utf8_general_ci NULL
+t1 ROCKSDB 10 Fixed 1000 # # 0 0 0 NULL # # NULL latin1_swedish_ci NULL
+t2 ROCKSDB 10 Fixed 1000 # # 0 0 0 NULL # # NULL latin1_swedish_ci NULL
+t3 ROCKSDB 10 Fixed 1000 # # 0 0 0 NULL # # NULL utf8_general_ci NULL
SHOW TABLE STATUS WHERE name LIKE 't2';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t2 ROCKSDB 10 Fixed 1000 # # 0 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL
+t2 ROCKSDB 10 Fixed 1000 # # 0 0 0 NULL # # NULL latin1_swedish_ci NULL
DROP TABLE t1, t2, t3;
CREATE DATABASE `db_new..............................................end`;
USE `db_new..............................................end`;
@@ -22,3 +22,39 @@ SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.table_statistics WHERE T
TABLE_SCHEMA db_new..............................................end
TABLE_NAME t1_new..............................................end
DROP DATABASE `db_new..............................................end`;
+#
+# MDEV-17171: Bug: RocksDB Tables do not have "Creation Date"
+#
+use test;
+create table t1 (a int) engine=rocksdb;
+select create_time is not null, update_time, check_time
+from information_schema.tables where table_schema=database() and table_name='t1';
+create_time is not null update_time check_time
+1 NULL NULL
+insert into t1 values (1);
+select create_time is not null, update_time is not null, check_time
+from information_schema.tables where table_schema=database() and table_name='t1';
+create_time is not null update_time is not null check_time
+1 1 NULL
+flush tables;
+select create_time is not null, update_time is not null, check_time
+from information_schema.tables where table_schema=database() and table_name='t1';
+create_time is not null update_time is not null check_time
+1 1 NULL
+select create_time, update_time into @create_tm, @update_tm
+from information_schema.tables
+where table_schema=database() and table_name='t1';
+select sleep(3);
+sleep(3)
+0
+insert into t1 values (2);
+select
+create_time=@create_tm /* should not change */ ,
+timestampdiff(second, @update_tm, update_time) > 2,
+check_time
+from information_schema.tables
+where table_schema=database() and table_name='t1';
+create_time=@create_tm 1
+timestampdiff(second, @update_tm, update_time) > 2 1
+check_time NULL
+drop table t1;
diff --git a/mysql-test/suite/rocksdb/r/truncate_table.result b/mysql-test/suite/rocksdb/r/truncate_table.result
index 1544256f194..79b266a2453 100644
--- a/mysql-test/suite/rocksdb/r/truncate_table.result
+++ b/mysql-test/suite/rocksdb/r/truncate_table.result
@@ -9,19 +9,19 @@ DROP TABLE t1;
CREATE TABLE t1 (a INT KEY AUTO_INCREMENT, c CHAR(8)) ENGINE=rocksdb;
SHOW TABLE STATUS LIKE 't1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed # # # 0 0 0 1 NULL NULL NULL latin1_swedish_ci NULL
+t1 ROCKSDB 10 Fixed # # # 0 0 0 1 # # NULL latin1_swedish_ci NULL
INSERT INTO t1 (c) VALUES ('a'),('b'),('c');
SHOW TABLE STATUS LIKE 't1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed # # # 0 0 0 4 NULL NULL NULL latin1_swedish_ci NULL
+t1 ROCKSDB 10 Fixed # # # 0 0 0 4 # # NULL latin1_swedish_ci NULL
TRUNCATE TABLE t1;
SHOW TABLE STATUS LIKE 't1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed # # # 0 0 0 1 NULL NULL NULL latin1_swedish_ci NULL
+t1 ROCKSDB 10 Fixed # # # 0 0 0 1 # # NULL latin1_swedish_ci NULL
INSERT INTO t1 (c) VALUES ('d');
SHOW TABLE STATUS LIKE 't1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 ROCKSDB 10 Fixed # # # 0 0 0 2 NULL NULL NULL latin1_swedish_ci NULL
+t1 ROCKSDB 10 Fixed # # # 0 0 0 2 # # NULL latin1_swedish_ci NULL
SELECT a,c FROM t1;
a c
1 d
diff --git a/mysql-test/suite/rocksdb/t/issue255.test b/mysql-test/suite/rocksdb/t/issue255.test
index 370dece0c6c..686f45b4056 100644
--- a/mysql-test/suite/rocksdb/t/issue255.test
+++ b/mysql-test/suite/rocksdb/t/issue255.test
@@ -3,24 +3,25 @@
CREATE TABLE t1 (pk BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT);
INSERT INTO t1 VALUES (5);
---replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 #
+--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
SHOW TABLE STATUS LIKE 't1';
INSERT INTO t1 VALUES ('538647864786478647864');
---replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 #
+--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
SELECT * FROM t1;
+--replace_column 12 # 13 #
SHOW TABLE STATUS LIKE 't1';
--error ER_DUP_ENTRY
INSERT INTO t1 VALUES ();
SELECT * FROM t1;
---replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 #
+--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
SHOW TABLE STATUS LIKE 't1';
--error ER_DUP_ENTRY
INSERT INTO t1 VALUES ();
SELECT * FROM t1;
---replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 #
+--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
SHOW TABLE STATUS LIKE 't1';
DROP TABLE t1;
@@ -28,24 +29,24 @@ DROP TABLE t1;
CREATE TABLE t1 (pk TINYINT NOT NULL PRIMARY KEY AUTO_INCREMENT);
INSERT INTO t1 VALUES (5);
---replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 #
+--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
SHOW TABLE STATUS LIKE 't1';
INSERT INTO t1 VALUES (1000);
SELECT * FROM t1;
---replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 #
+--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
SHOW TABLE STATUS LIKE 't1';
--error ER_DUP_ENTRY
INSERT INTO t1 VALUES ();
SELECT * FROM t1;
---replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 #
+--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
SHOW TABLE STATUS LIKE 't1';
--error ER_DUP_ENTRY
INSERT INTO t1 VALUES ();
SELECT * FROM t1;
---replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 #
+--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 #
SHOW TABLE STATUS LIKE 't1';
DROP TABLE t1;
diff --git a/mysql-test/suite/rocksdb/t/rocksdb.test b/mysql-test/suite/rocksdb/t/rocksdb.test
index 5eff0fbf38f..7dcae569c92 100644
--- a/mysql-test/suite/rocksdb/t/rocksdb.test
+++ b/mysql-test/suite/rocksdb/t/rocksdb.test
@@ -1198,7 +1198,7 @@ drop table t1;
create table t1 (i int primary key auto_increment) engine=RocksDB;
insert into t1 values (null),(null);
---replace_column 7 #
+--replace_column 7 # 12 # 13 #
show table status like 't1';
drop table t1;
@@ -1903,11 +1903,13 @@ DROP TABLE t1;
# value is 4 while MyRocks will show it as 3.
CREATE TABLE t1(a INT AUTO_INCREMENT KEY);
INSERT INTO t1 VALUES(0),(-1),(0);
+--replace_column 12 # 13 #
SHOW TABLE STATUS LIKE 't1';
SELECT * FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a INT AUTO_INCREMENT KEY);
INSERT INTO t1 VALUES(0),(10),(0);
+--replace_column 12 # 13 #
SHOW TABLE STATUS LIKE 't1';
SELECT * FROM t1;
DROP TABLE t1;
diff --git a/mysql-test/suite/rocksdb/t/show_table_status.test b/mysql-test/suite/rocksdb/t/show_table_status.test
index 29cc2ccfb5e..4be2e82a72d 100644
--- a/mysql-test/suite/rocksdb/t/show_table_status.test
+++ b/mysql-test/suite/rocksdb/t/show_table_status.test
@@ -24,7 +24,7 @@ set global rocksdb_force_flush_memtable_now = true;
CREATE TABLE t3 (a INT, b CHAR(8), pk INT PRIMARY KEY) ENGINE=rocksdb CHARACTER SET utf8;
---replace_column 6 # 7 #
+--replace_column 6 # 7 # 12 # 13 #
SHOW TABLE STATUS WHERE name IN ( 't1', 't2', 't3' );
# Some statistics don't get updated as quickly. The Data_length and
@@ -48,7 +48,7 @@ set global rocksdb_force_flush_memtable_now = true;
# We expect the number of rows to be 10000. Data_len and Avg_row_len
# may vary, depending on built-in compression library.
---replace_column 6 # 7 #
+--replace_column 6 # 7 # 12 # 13 #
SHOW TABLE STATUS WHERE name LIKE 't2';
DROP TABLE t1, t2, t3;
@@ -62,3 +62,36 @@ CREATE TABLE `t1_new..............................................end`(a int) en
INSERT INTO `t1_new..............................................end` VALUES (1);
--query_vertical SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.table_statistics WHERE TABLE_NAME = 't1_new..............................................end'
DROP DATABASE `db_new..............................................end`;
+--echo #
+--echo # MDEV-17171: Bug: RocksDB Tables do not have "Creation Date"
+--echo #
+use test;
+create table t1 (a int) engine=rocksdb;
+
+select create_time is not null, update_time, check_time
+from information_schema.tables where table_schema=database() and table_name='t1';
+
+insert into t1 values (1);
+select create_time is not null, update_time is not null, check_time
+from information_schema.tables where table_schema=database() and table_name='t1';
+
+flush tables;
+select create_time is not null, update_time is not null, check_time
+from information_schema.tables where table_schema=database() and table_name='t1';
+
+select create_time, update_time into @create_tm, @update_tm
+from information_schema.tables
+where table_schema=database() and table_name='t1';
+
+select sleep(3);
+insert into t1 values (2);
+
+--vertical_results
+select
+ create_time=@create_tm /* should not change */ ,
+ timestampdiff(second, @update_tm, update_time) > 2,
+ check_time
+from information_schema.tables
+where table_schema=database() and table_name='t1';
+
+drop table t1;
diff --git a/mysql-test/suite/rocksdb/t/truncate_table.test b/mysql-test/suite/rocksdb/t/truncate_table.test
index a61488654a3..1001eeb6cde 100644
--- a/mysql-test/suite/rocksdb/t/truncate_table.test
+++ b/mysql-test/suite/rocksdb/t/truncate_table.test
@@ -29,22 +29,22 @@ DROP TABLE t1;
CREATE TABLE t1 (a INT KEY AUTO_INCREMENT, c CHAR(8)) ENGINE=rocksdb;
#--replace_column 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 # 14 # 15 # 16 # 17 # 18 #
---replace_column 5 # 6 # 7 #
+--replace_column 5 # 6 # 7 # 12 # 13 #
SHOW TABLE STATUS LIKE 't1';
INSERT INTO t1 (c) VALUES ('a'),('b'),('c');
#--replace_column 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 # 14 # 15 # 16 # 17 # 18 #
---replace_column 5 # 6 # 7 #
+--replace_column 5 # 6 # 7 # 12 # 13 #
SHOW TABLE STATUS LIKE 't1';
TRUNCATE TABLE t1;
#--replace_column 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 # 14 # 15 # 16 # 17 # 18 #
---replace_column 5 # 6 # 7 #
+--replace_column 5 # 6 # 7 # 12 # 13 #
SHOW TABLE STATUS LIKE 't1';
INSERT INTO t1 (c) VALUES ('d');
#--replace_column 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 12 # 13 # 14 # 15 # 16 # 17 # 18 #
---replace_column 5 # 6 # 7 #
+--replace_column 5 # 6 # 7 # 12 # 13 #
SHOW TABLE STATUS LIKE 't1';
--sorted_result
diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc
index d6c89087bea..f4f902a8caf 100644
--- a/storage/rocksdb/ha_rocksdb.cc
+++ b/storage/rocksdb/ha_rocksdb.cc
@@ -2456,6 +2456,8 @@ class Rdb_transaction {
bool m_is_delayed_snapshot = false;
bool m_is_two_phase = false;
+ std::unordered_set<Rdb_tbl_def*> modified_tables;
+
private:
/*
Number of write operations this transaction had when we took the last
@@ -3139,6 +3141,25 @@ class Rdb_transaction {
virtual void start_tx() = 0;
virtual void start_stmt() = 0;
+ protected:
+ // Non-virtual functions with actions to be done on transaction start and
+ // commit.
+ void on_commit() {
+ time_t tm;
+ tm = time(nullptr);
+ for (auto &it : modified_tables) {
+ it->update_time = tm;
+ }
+ modified_tables.clear();
+ }
+ void on_rollback() {
+ modified_tables.clear();
+ }
+ public:
+ void log_table_write_op(Rdb_tbl_def *tbl) {
+ modified_tables.insert(tbl);
+ }
+
void set_initial_savepoint() {
/*
Set the initial savepoint. If the first statement in the transaction
@@ -3322,7 +3343,9 @@ class Rdb_transaction_impl : public Rdb_transaction {
goto error;
}
+ on_commit();
error:
+ on_rollback();
/* Save the transaction object to be reused */
release_tx();
@@ -3338,6 +3361,7 @@ class Rdb_transaction_impl : public Rdb_transaction {
public:
void rollback() override {
+ on_rollback();
m_write_count = 0;
m_insert_count = 0;
m_update_count = 0;
@@ -3659,7 +3683,9 @@ class Rdb_writebatch_impl : public Rdb_transaction {
res = true;
goto error;
}
+ on_commit();
error:
+ on_rollback();
reset();
m_write_count = 0;
@@ -3691,6 +3717,7 @@ class Rdb_writebatch_impl : public Rdb_transaction {
}
void rollback() override {
+ on_rollback();
m_write_count = 0;
m_insert_count = 0;
m_update_count = 0;
@@ -7596,6 +7623,7 @@ int ha_rocksdb::create_table(const std::string &table_name,
DBUG_ASSERT(!debug_sync_set_action(thd, STRING_WITH_LEN(act)));
});
+ m_tbl_def->put_creation_time(&dict_manager, batch, time(nullptr));
{
std::lock_guard<Rdb_dict_manager> dm_lock(dict_manager);
err = ddl_manager.put_and_write(m_tbl_def, batch);
@@ -10177,6 +10205,8 @@ int ha_rocksdb::update_write_row(const uchar *const old_data,
row_info.tx->incr_insert_count();
}
+ row_info.tx->log_table_write_op(m_tbl_def);
+
if (do_bulk_commit(row_info.tx)) {
DBUG_RETURN(HA_ERR_ROCKSDB_BULK_LOAD);
}
@@ -10648,6 +10678,7 @@ int ha_rocksdb::delete_row(const uchar *const buf) {
}
tx->incr_delete_count();
+ tx->log_table_write_op(m_tbl_def);
if (do_bulk_commit(tx)) {
DBUG_RETURN(HA_ERR_ROCKSDB_BULK_LOAD);
@@ -10802,6 +10833,12 @@ int ha_rocksdb::info(uint flag) {
k->rec_per_key[j] = x;
}
}
+
+ stats.create_time = m_tbl_def->get_creation_time(&dict_manager);
+ }
+
+ if (flag & HA_STATUS_TIME) {
+ stats.update_time = m_tbl_def->update_time;
}
if (flag & HA_STATUS_ERRKEY) {
diff --git a/storage/rocksdb/rdb_datadic.cc b/storage/rocksdb/rdb_datadic.cc
index c0741a1ce9b..5d1d6f53980 100644
--- a/storage/rocksdb/rdb_datadic.cc
+++ b/storage/rocksdb/rdb_datadic.cc
@@ -3497,6 +3497,27 @@ Rdb_tbl_def::~Rdb_tbl_def() {
}
}
+void Rdb_tbl_def::put_creation_time(Rdb_dict_manager *dict_manager,
+ rocksdb::WriteBatchBase *batch,
+ time_t timeval) {
+ dict_manager->put_creation_time(batch,
+ m_key_descr_arr[0]->get_gl_index_id(),
+ timeval);
+ create_time = timeval;
+}
+
+time_t Rdb_tbl_def::get_creation_time(Rdb_dict_manager *dict_manager) {
+ time_t tm;
+ if (create_time == CREATE_TIME_UNKNOWN) {
+ if (dict_manager->get_creation_time(m_key_descr_arr[0]->get_gl_index_id(),
+ &tm))
+ create_time = tm;
+ else
+ create_time = 0; // Not available
+ }
+ return create_time;
+}
+
/*
Put table definition DDL entry. Actual write is done at
Rdb_dict_manager::commit.
@@ -5538,6 +5559,7 @@ rocksdb::Status Rdb_dict_manager::put_auto_incr_val(
value_writer.to_slice());
}
+
bool Rdb_dict_manager::get_auto_incr_val(const GL_INDEX_ID &gl_index_id,
ulonglong *new_val) const {
Rdb_buf_writer<Rdb_key_def::INDEX_NUMBER_SIZE * 3> key_writer;
@@ -5557,6 +5579,51 @@ bool Rdb_dict_manager::get_auto_incr_val(const GL_INDEX_ID &gl_index_id,
return false;
}
+
+rocksdb::Status
+Rdb_dict_manager::put_creation_time(rocksdb::WriteBatchBase *batch,
+ const GL_INDEX_ID &gl_index_id,
+ time_t timeval) const {
+ uchar key_buf[Rdb_key_def::INDEX_NUMBER_SIZE * 3] = {0};
+ dump_index_id(key_buf, Rdb_key_def::TABLE_CREATION_TS, gl_index_id);
+ const rocksdb::Slice key =
+ rocksdb::Slice(reinterpret_cast<char *>(key_buf), sizeof(key_buf));
+
+ // Value is constructed by storing the version and the value.
+ uchar value_buf[RDB_SIZEOF_TABLE_CREATION_TS_VERSION +
+ ROCKSDB_SIZEOF_TABLE_CREATION_TS] = {0};
+ uchar *ptr = value_buf;
+ rdb_netbuf_store_uint16(ptr, Rdb_key_def::TABLE_CREATION_TS_VERSION);
+ ptr += RDB_SIZEOF_TABLE_CREATION_TS_VERSION;
+ rdb_netbuf_store_uint64(ptr, timeval);
+ ptr += ROCKSDB_SIZEOF_TABLE_CREATION_TS;
+ const rocksdb::Slice value =
+ rocksdb::Slice(reinterpret_cast<char *>(value_buf), ptr - value_buf);
+
+ return batch->Put(m_system_cfh, key, value);
+}
+
+bool Rdb_dict_manager::get_creation_time(const GL_INDEX_ID &gl_index_id,
+ time_t *new_val) const {
+ uchar key_buf[Rdb_key_def::INDEX_NUMBER_SIZE * 3] = {0};
+ dump_index_id(key_buf, Rdb_key_def::TABLE_CREATION_TS, gl_index_id);
+
+ std::string value;
+ const rocksdb::Status status = get_value(
+ rocksdb::Slice(reinterpret_cast<char *>(key_buf), sizeof(key_buf)),
+ &value);
+
+ if (status.ok() && value.size() >= RDB_SIZEOF_TABLE_CREATION_TS_VERSION) {
+ const uchar *const val = reinterpret_cast<const uchar *>(value.data());
+
+ if (rdb_netbuf_to_uint16(val) <= Rdb_key_def::TABLE_CREATION_TS_VERSION) {
+ *new_val = rdb_netbuf_to_uint64(val + RDB_SIZEOF_TABLE_CREATION_TS_VERSION);
+ return true;
+ }
+ }
+ return false;
+}
+
uint Rdb_seq_generator::get_and_update_next_number(
Rdb_dict_manager *const dict) {
DBUG_ASSERT(dict != nullptr);
diff --git a/storage/rocksdb/rdb_datadic.h b/storage/rocksdb/rdb_datadic.h
index 416857cad38..cb256d6891b 100644
--- a/storage/rocksdb/rdb_datadic.h
+++ b/storage/rocksdb/rdb_datadic.h
@@ -196,6 +196,7 @@ const size_t RDB_SIZEOF_INDEX_TYPE = sizeof(uchar);
const size_t RDB_SIZEOF_KV_VERSION = sizeof(uint16);
const size_t RDB_SIZEOF_INDEX_FLAGS = sizeof(uint32);
const size_t RDB_SIZEOF_AUTO_INCREMENT_VERSION = sizeof(uint16);
+const size_t RDB_SIZEOF_TABLE_CREATION_TS_VERSION = sizeof(uint16);
// Possible return values for rdb_index_field_unpack_t functions.
enum {
@@ -500,6 +501,7 @@ class Rdb_key_def {
DDL_CREATE_INDEX_ONGOING = 8,
AUTO_INC = 9,
DROPPED_CF = 10,
+ TABLE_CREATION_TS = 11,
END_DICT_INDEX_ID = 255
};
@@ -514,6 +516,7 @@ class Rdb_key_def {
DDL_CREATE_INDEX_ONGOING_VERSION = 1,
AUTO_INCREMENT_VERSION = 1,
DROPPED_CF_VERSION = 1,
+ TABLE_CREATION_TS_VERSION = 1
// Version for index stats is stored in IndexStats struct
};
@@ -1116,6 +1119,13 @@ class Rdb_tbl_def {
~Rdb_tbl_def();
+ // time values are shown in SHOW TABLE STATUS
+ void put_creation_time(Rdb_dict_manager *dict_manager,
+ rocksdb::WriteBatchBase *batch, time_t timeval);
+ time_t get_creation_time(Rdb_dict_manager *dict_manager);
+
+ time_t update_time = 0; // in-memory only value, maintained right here
+
void check_and_set_read_free_rpl_table();
/* Number of indexes */
@@ -1161,6 +1171,12 @@ class Rdb_tbl_def {
const std::string &base_tablename() const { return m_tablename; }
const std::string &base_partition() const { return m_partition; }
GL_INDEX_ID get_autoincr_gl_index_id();
+
+ private:
+ const time_t CREATE_TIME_UNKNOWN= 1;
+ // CREATE_TIME_UNKNOWN means "didn't try to read, yet"
+ // 0 means "no data available" (and SQL layer shares this)
+ time_t create_time = CREATE_TIME_UNKNOWN;
};
/*
@@ -1386,6 +1402,10 @@ class Rdb_binlog_manager {
key: Rdb_key_def::DROPPED_CF(0xa) + cf_id
value: version
+ 10. Table creation timestamp
+ key: Rdb_key_def::TABLE_CREATION_TIMESTAMP + cf_id + index_id
+ value: timestamp
+
Data dictionary operations are atomic inside RocksDB. For example,
when creating a table with two indexes, it is necessary to call Put
three times. They have to be atomic. Rdb_dict_manager has a wrapper function
@@ -1565,6 +1585,11 @@ class Rdb_dict_manager {
bool overwrite = false) const;
bool get_auto_incr_val(const GL_INDEX_ID &gl_index_id,
ulonglong *new_val) const;
+ rocksdb::Status put_creation_time(rocksdb::WriteBatchBase *batch,
+ const GL_INDEX_ID &gl_index_id,
+ time_t timeval_arg) const;
+ bool get_creation_time(const GL_INDEX_ID &gl_index_id,
+ time_t *new_val) const;
private:
/* dropped cf flags */
diff --git a/storage/rocksdb/rdb_global.h b/storage/rocksdb/rdb_global.h
index d24160ab1c8..2de201db354 100644
--- a/storage/rocksdb/rdb_global.h
+++ b/storage/rocksdb/rdb_global.h
@@ -269,6 +269,8 @@ const char *const RDB_TTL_COL_QUALIFIER = "ttl_col";
#define ROCKSDB_SIZEOF_AUTOINC_VALUE sizeof(longlong)
+#define ROCKSDB_SIZEOF_TABLE_CREATION_TS sizeof(uint64_t)
+
/*
Maximum index prefix length in bytes.
*/
1
0