[Commits] 0aa2c0a1cf3: MDEV-19245: Impossible WHERE should be noticed earlier after HAVING pushdown
by Galina 15 Apr '19
by Galina 15 Apr '19
15 Apr '19
revision-id: 0aa2c0a1cf393d9cafc3504ea7f24be77aec0076 (mariadb-10.4.4-4-g0aa2c0a1cf3)
parent(s): 1e7ad5bb1c69dba8c7d721a2cfbbe98c7e900015
author: Galina Shalygina
committer: Galina Shalygina
timestamp: 2019-04-15 20:47:24 +0300
message:
MDEV-19245: Impossible WHERE should be noticed earlier after HAVING pushdown
The bug appears because not all conditions are found to be knowingly
true or false in WHERE after HAVING pushdown optimization.
Impossible WHERE can be found much earlier compared with how it is done now.
To fix it and_new_conditions_to_optimized_cond() is changed.
---
mysql-test/main/having_cond_pushdown.result | 43 +++++++++++++++++++++++++++++
mysql-test/main/having_cond_pushdown.test | 31 +++++++++++++++++++++
sql/item.h | 5 ++++
sql/item_func.h | 2 ++
sql/opt_subselect.cc | 18 ++++--------
5 files changed, 87 insertions(+), 12 deletions(-)
diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result
index 85ca0342dee..20e3567f8ee 100644
--- a/mysql-test/main/having_cond_pushdown.result
+++ b/mysql-test/main/having_cond_pushdown.result
@@ -4657,3 +4657,46 @@ GROUP BY v1.pk
HAVING (v1.pk = 1);
DROP TABLE t1,t2,tmp1;
DROP VIEW v1;
+#
+# MDEV-19245: Impossible WHERE should be noticed earlier
+# after HAVING pushdown
+#
+CREATE TABLE t1 (a INT, b INT, c INT);
+INSERT INTO t1 VALUES (1,2,1),(3,2,2),(5,6,4),(3,4,1);
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a > 3 GROUP BY t1.a HAVING t1.a = 3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a = 3 GROUP BY t1.a HAVING t1.a > 3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a > 3 AND t1.a = 3 GROUP BY t1.a ;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE (t1.a < 2 OR t1.c > 1) GROUP BY t1.a HAVING t1.a = 3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 OR t1.a > 3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a = 3 AND (t1.a < 2 OR t1.a > 3) GROUP BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
+WHERE (t1.a < 2 AND t1.c > 1) GROUP BY t1.a HAVING t1.a = 3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
+WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 AND t1.c > 1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
+WHERE t1.a = 3 AND (t1.a < 2 AND t1.b > 3) GROUP BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+DROP TABLE t1;
diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test
index 257e5cb4875..ed466455808 100644
--- a/mysql-test/main/having_cond_pushdown.test
+++ b/mysql-test/main/having_cond_pushdown.test
@@ -1340,3 +1340,34 @@ HAVING (v1.pk = 1);
DROP TABLE t1,t2,tmp1;
DROP VIEW v1;
+
+--echo #
+--echo # MDEV-19245: Impossible WHERE should be noticed earlier
+--echo # after HAVING pushdown
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, c INT);
+INSERT INTO t1 VALUES (1,2,1),(3,2,2),(5,6,4),(3,4,1);
+
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a > 3 GROUP BY t1.a HAVING t1.a = 3;
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a = 3 GROUP BY t1.a HAVING t1.a > 3;
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a > 3 AND t1.a = 3 GROUP BY t1.a ;
+
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE (t1.a < 2 OR t1.c > 1) GROUP BY t1.a HAVING t1.a = 3;
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 OR t1.a > 3);
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a = 3 AND (t1.a < 2 OR t1.a > 3) GROUP BY t1.a;
+
+EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
+WHERE (t1.a < 2 AND t1.c > 1) GROUP BY t1.a HAVING t1.a = 3;
+EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
+WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 AND t1.c > 1);
+EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
+WHERE t1.a = 3 AND (t1.a < 2 AND t1.b > 3) GROUP BY t1.a;
+
+DROP TABLE t1;
diff --git a/sql/item.h b/sql/item.h
index 97d31e6ba34..2d05f640a8d 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1943,6 +1943,11 @@ class Item: public Value_source,
virtual bool check_partition_func_processor(void *arg) { return 1;}
virtual bool post_fix_fields_part_expr_processor(void *arg) { return 0; }
virtual bool rename_fields_processor(void *arg) { return 0; }
+ /*
+ TRUE if the function is knowingly TRUE or FALSE.
+ Not to be used for AND/OR formulas.
+ */
+ virtual bool is_simplified_cond_processor(void *arg) { return false; }
/** Processor used to check acceptability of an item in the defining
expression for a virtual column
diff --git a/sql/item_func.h b/sql/item_func.h
index 44e9691c9df..27cb245db6b 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -398,6 +398,8 @@ class Item_func :public Item_func_or_sum,
bool with_sum_func() const { return m_with_sum_func; }
With_sum_func_cache* get_with_sum_func_cache() { return this; }
Item_func *get_item_func() { return this; }
+ bool is_simplified_cond_processor(void *arg)
+ { return const_item() && !val_int(); }
};
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 32b70b41eb3..4c912154e95 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -5723,12 +5723,6 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
&((Item_cond_and *) cond)->m_cond_equal,
false, NULL);
}
- /*
- Check if equalities that can't be transformed into multiple
- equalities are knowingly true or false.
- */
- if (item->const_item() && !item->val_int())
- is_simplified_cond= true;
and_args->push_back(item, thd->mem_root);
}
and_args->append((List<Item> *) cond_equalities);
@@ -5821,12 +5815,6 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
{
item= item->build_equal_items(thd, inherited, false, NULL);
}
- /*
- Check if equalities that can't be transformed into multiple
- equalities are knowingly true or false.
- */
- if (item->const_item() && !item->val_int())
- is_simplified_cond= true;
new_conds_list.push_back(item, thd->mem_root);
}
new_conds_list.append((List<Item> *)&new_cond_equal.current_level);
@@ -5870,7 +5858,13 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
cond= cond->propagate_equal_fields(thd,
Item::Context_boolean(),
*cond_eq);
+ cond->update_used_tables();
}
+ /* Check if conds has knowingly true or false parts. */
+ if (cond &&
+ !is_simplified_cond &&
+ cond->walk(&Item::is_simplified_cond_processor, 0, 0))
+ is_simplified_cond= true;
/*
If it was found that there are some knowingly true or false equalities
1
0
revision-id: 00b665ce76cf270c40bcd5eee926c3ced86becc6 (mariadb-10.2.23-34-g00b665ce76c)
parent(s): e3f44d8d0eb0277312391686672f81a460094917
author: Sujatha Sivakumar
committer: Sujatha Sivakumar
timestamp: 2019-04-13 10:06:58 +0300
message:
Version 1
---
client/mysqlbinlog.cc | 15 ++++++++-------
1 file changed, 8 insertions(+), 7 deletions(-)
diff --git a/client/mysqlbinlog.cc b/client/mysqlbinlog.cc
index cfc05cbf794..785d1b93255 100644
--- a/client/mysqlbinlog.cc
+++ b/client/mysqlbinlog.cc
@@ -189,7 +189,7 @@ Log_event* read_remote_annotate_event(uchar* net_buf, ulong event_len,
const char **error_msg)
{
uchar *event_buf;
- Log_event* event;
+ Log_event* event= NULL;
if (!(event_buf= (uchar*) my_malloc(event_len + 1, MYF(MY_WME))))
{
@@ -227,8 +227,7 @@ void print_annotate_event(PRINT_EVENT_INFO *print_event_info)
if (annotate_event)
{
annotate_event->print(result_file, print_event_info);
- delete annotate_event; // the event should not be printed more than once
- annotate_event= 0;
+ free_annotate_event();
}
}
@@ -995,6 +994,7 @@ static bool print_row_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev,
{
e= *(dynamic_element(&events_in_stmt, i, Log_event**));
delete e;
+ e= NULL;
}
reset_dynamic(&events_in_stmt);
@@ -1465,7 +1465,7 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev,
if (print_row_event(print_event_info, ev, e->get_table_id(),
e->get_flags(Rows_log_event::STMT_END_F)))
goto err;
- if (!is_stmt_end)
+ if (opt_flashback && !is_stmt_end)
destroy_evt= FALSE;
break;
}
@@ -1478,7 +1478,7 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev,
if (print_row_event(print_event_info, ev, e->get_table_id(),
e->get_flags(Old_rows_log_event::STMT_END_F)))
goto err;
- if (!is_stmt_end)
+ if (opt_flashback && !is_stmt_end)
destroy_evt= FALSE;
break;
}
@@ -1539,10 +1539,11 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev,
}
}
- if (remote_opt)
- ev->temp_buf= 0;
if (destroy_evt) /* destroy it later if not set (ignored table map) */
+ {
delete ev;
+ ev= NULL;
+ }
}
DBUG_RETURN(retval);
}
1
0
12 Apr '19
revision-id: 6484df9977f7799f0e33307f447c6598c2570614 (mariadb-10.4.3-168-g6484df9)
parent(s): 517a56c1884e74b7c7606b56b0884537e792089e
author: Varun Gupta
committer: Varun Gupta
timestamp: 2019-04-12 18:00:35 +0530
message:
MDEV-18880: Optimizer trace prints date in hexadecimal
Introducded a print_key_value function to makes sure that the trace prints data in readable format
for readable characters and the rest of the characters are printed as hexadecimal.
---
mysql-test/main/opt_trace.result | 442 +++++++++++++++++++++++++++++++++-
mysql-test/main/opt_trace.test | 55 +++++
mysql-test/main/opt_trace_ucs2.result | 54 +++++
mysql-test/main/opt_trace_ucs2.test | 10 +
sql/field.cc | 55 +++++
sql/field.h | 14 ++
sql/opt_range.cc | 34 +--
sql/sql_string.cc | 12 +
sql/sql_string.h | 3 +
9 files changed, 647 insertions(+), 32 deletions(-)
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index ed5881e..49f993f 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -1446,7 +1446,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id {
{
"index": "id",
"covering": true,
- "ranges": ["(0x24a20f) <= (a)"],
+ "ranges": ["(2001-01-04) <= (a)"],
"rows": 9,
"cost": 2.35
}
@@ -1462,7 +1462,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id {
"rows": 9,
"cost": 2.35,
"key_parts_used_for_access": ["id"],
- "ranges": ["(0x24a20f) <= (a)"],
+ "ranges": ["(2001-01-04) <= (a)"],
"chosen": false,
"cause": "cost"
},
@@ -1624,7 +1624,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id {
{
"index": "id",
"covering": true,
- "ranges": ["(0x24a20f) <= (a) <= (0x24a20f)"],
+ "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"],
"rows": 9,
"cost": 2.35
}
@@ -1640,7 +1640,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id {
"rows": 9,
"cost": 2.35,
"key_parts_used_for_access": ["id", "a"],
- "ranges": ["(0x24a20f) <= (a) <= (0x24a20f)"],
+ "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"],
"chosen": false,
"cause": "cost"
},
@@ -6130,7 +6130,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"index": "start_date",
"ranges":
[
- "(0x4ac60f,NULL) < (start_date,end_date)"
+ "(2019-02-10,NULL) < (start_date,end_date)"
],
"rowid_ordered": false,
"using_mrr": false,
@@ -6214,7 +6214,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"index": "i_b",
"ranges":
[
- "(0xd95b94336a9946a39cf5b58cfe772d8c) <= (b) <= (0xd95b94336a9946a39cf5b58cfe772d8c)"
+ "(\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C) <= (b) <= (\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C)"
],
"rowid_ordered": true,
"using_mrr": false,
@@ -6268,4 +6268,434 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
}
]
drop table t1;
+#
+# MDEV-18880: Optimizer trace prints date in hexadecimal
+#
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10) CHARSET BINARY , INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 13 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(ab\x0A) <= (b) <= (ab\x0A)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3787,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+ALTER TABLE t1 modify column b BINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 11 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(ab\x0A\x00\x00\x00\x00\x00\x00\x00) <= (b) <= (ab\x0A\x00\x00\x00\x00\x00\x00\x00)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3785,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+ALTER TABLE t1 modify column b VARBINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 13 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(ab\x0A) <= (b) <= (ab\x0A)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3787,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1;
+CREATE TABLE t1(i INT PRIMARY KEY, b CHAR(10), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 11 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(ab\n) <= (b) <= (ab\n)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3785,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1;
+CREATE TABLE t1(i INT PRIMARY KEY, b blob , INDEX i_b(b));
+Warnings:
+Note 1071 Specified key was too long; max key length is 1000 bytes
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b= 'ab\n';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 1003 const 1 Using where
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(ab\x0A) <= (b) <= (ab\x0A)"
+ ],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 3.5719,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1;
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, 'ab\n');
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 13 const 2 Using index condition
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n' {
+ "steps": [
+ {
+ "join_preparation": {
+ "select_id": 1,
+ "steps": [
+ {
+ "expanded_query": "select t1.i AS i,t1.b AS b from t1 where t1.b = 'ab\n'"
+ }
+ ]
+ }
+ },
+ {
+ "join_optimization": {
+ "select_id": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "t1.b = 'ab\n'",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "multiple equal('ab\n', t1.b)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "multiple equal('ab\n', t1.b)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "multiple equal('ab\n', t1.b)"
+ }
+ ]
+ }
+ },
+ {
+ "table_dependencies": [
+ {
+ "table": "t1",
+ "row_may_be_null": false,
+ "map_bit": 0,
+ "depends_on_map_bits": []
+ }
+ ]
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "table": "t1",
+ "field": "b",
+ "equals": "'ab\n'",
+ "null_rejecting": false
+ }
+ ]
+ },
+ {
+ "rows_estimation": [
+ {
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "rows": 2,
+ "cost": 4.5098
+ },
+ "potential_range_indexes": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not applicable"
+ },
+ {
+ "index": "i_b",
+ "usable": true,
+ "key_parts": ["b"]
+ }
+ ],
+ "setup_range_conditions": [],
+ "group_index_range": {
+ "chosen": false,
+ "cause": "no group by or distinct"
+ },
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "i_b",
+ "ranges": ["(ab\n) <= (b) <= (ab\n)"],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 2,
+ "cost": 3.6324,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect": {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union": []
+ },
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "i_b",
+ "rows": 2,
+ "ranges": ["(ab\n) <= (b) <= (ab\n)"]
+ },
+ "rows_for_plan": 2,
+ "cost_for_plan": 3.6324,
+ "chosen": true
+ }
+ }
+ },
+ {
+ "selectivity_for_indexes": [
+ {
+ "index_name": "i_b",
+ "selectivity_from_index": 1
+ }
+ ],
+ "selectivity_for_columns": [],
+ "cond_selectivity": 1
+ }
+ ]
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "plan_prefix": [],
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "i_b",
+ "used_range_estimates": true,
+ "rows": 2,
+ "cost": 3,
+ "chosen": true
+ },
+ {
+ "type": "scan",
+ "chosen": false,
+ "cause": "cost"
+ }
+ ]
+ }
+ }
+ ]
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "t1.b = 'ab\n'",
+ "attached_conditions_computation": [],
+ "attached_conditions_summary": [
+ {
+ "table": "t1",
+ "attached": "t1.b = 'ab\n'"
+ }
+ ]
+ }
+ }
+ ]
+ }
+ },
+ {
+ "join_execution": {
+ "select_id": 1,
+ "steps": []
+ }
+ }
+ ]
+} 0 0
+drop table t1;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k (a int);
+insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
+create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
+insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
+explain format=json select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t1",
+ "access_type": "range",
+ "possible_keys": ["start_date"],
+ "key": "start_date",
+ "key_length": "8",
+ "used_key_parts": ["start_date", "end_date"],
+ "rows": 1000,
+ "filtered": 100,
+ "index_condition": "t1.start_date >= '2019-02-10' and t1.end_date < '2019-04-01'"
+ }
+ }
+}
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "start_date",
+ "ranges":
+ [
+ "(2019-02-10,NULL) < (start_date,end_date)"
+ ],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1000,
+ "cost": 1282.2,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1, t0, one_k;
set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index 981a53a..a6aec8f 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -444,4 +444,59 @@ select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) fr
drop table t1;
+--echo #
+--echo # MDEV-18880: Optimizer trace prints date in hexadecimal
+--echo #
+
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10) CHARSET BINARY , INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+ALTER TABLE t1 modify column b BINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+ALTER TABLE t1 modify column b VARBINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+
+CREATE TABLE t1(i INT PRIMARY KEY, b CHAR(10), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+
+
+CREATE TABLE t1(i INT PRIMARY KEY, b blob , INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b= 'ab\n';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, 'ab\n');
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k (a int);
+insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
+create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
+--disable_warnings
+insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
+--enable_warnings
+explain format=json select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1, t0, one_k;
+
set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace_ucs2.result b/mysql-test/main/opt_trace_ucs2.result
new file mode 100644
index 0000000..306fdbf
--- /dev/null
+++ b/mysql-test/main/opt_trace_ucs2.result
@@ -0,0 +1,54 @@
+create or replace table t1 (col1 char(10) character set ucs2, filler char(100), key(col1)) ;
+insert into t1 values ('a', 'a');
+insert into t1 values ('a', 'a');
+set optimizer_trace=1;
+explain format=json select * from t1 force index(col1) where col1 >='a';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t1",
+ "access_type": "range",
+ "possible_keys": ["col1"],
+ "key": "col1",
+ "key_length": "21",
+ "used_key_parts": ["col1"],
+ "rows": 2,
+ "filtered": 100,
+ "index_condition": "t1.col1 >= 'a'"
+ }
+ }
+}
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "col1",
+ "ranges":
+ [
+ "(a) <= (col1)"
+ ],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 2,
+ "cost": 3.7609,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1;
diff --git a/mysql-test/main/opt_trace_ucs2.test b/mysql-test/main/opt_trace_ucs2.test
new file mode 100644
index 0000000..827dc40
--- /dev/null
+++ b/mysql-test/main/opt_trace_ucs2.test
@@ -0,0 +1,10 @@
+--source include/not_embedded.inc
+--source include/have_ucs2.inc
+
+create or replace table t1 (col1 char(10) character set ucs2, filler char(100), key(col1)) ;
+insert into t1 values ('a', 'a');
+insert into t1 values ('a', 'a');
+set optimizer_trace=1;
+explain format=json select * from t1 force index(col1) where col1 >='a';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
diff --git a/sql/field.cc b/sql/field.cc
index 4a93464..028d304 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -11228,3 +11228,58 @@ bool Field::val_str_nopad(MEM_ROOT *mem_root, LEX_CSTRING *to)
thd->variables.sql_mode= sql_mode_backup;
return rc;
}
+
+
+void Field::print_key_value(String *out, uint length)
+{
+ if (charset() == &my_charset_bin)
+ print_key_value_binary(out, ptr, length);
+ else
+ val_str(out);
+}
+
+
+void Field_string::print_key_value(String *out, uint length)
+{
+ if (charset() == &my_charset_bin)
+ {
+ length= field_charset->cset->lengthsp(field_charset, (const char*) ptr, length);
+ print_key_value_binary(out, ptr, length);
+ }
+ else
+ {
+ THD *thd= get_thd();
+ sql_mode_t sql_mode_backup= thd->variables.sql_mode;
+ thd->variables.sql_mode&= ~MODE_PAD_CHAR_TO_FULL_LENGTH;
+ val_str(out,out);
+ thd->variables.sql_mode= sql_mode_backup;
+ }
+}
+
+
+void Field_varstring::print_key_value(String *out, uint length)
+{
+ if (charset() == &my_charset_bin)
+ print_key_value_binary(out, get_data(), get_length());
+ else
+ val_str(out,out);
+}
+
+
+void Field_blob::print_key_value(String *out, uint length)
+{
+ if (charset() == &my_charset_bin)
+ {
+ uchar *blob;
+ memcpy(&blob, ptr+packlength, sizeof(uchar*));
+ print_key_value_binary(out, blob, get_length());
+ }
+ else
+ val_str(out, out);
+}
+
+
+void Field::print_key_value_binary(String *out, const uchar* key, uint length)
+{
+ out->append_semi_hex((const char*)key, length, charset());
+}
diff --git a/sql/field.h b/sql/field.h
index 8bd7343..0fbbc04 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -1380,6 +1380,8 @@ class Field: public Value_source
virtual int set_time() { return 1; }
bool set_warning(Sql_condition::enum_warning_level, unsigned int code,
int cuted_increment, ulong current_row=0) const;
+ virtual void print_key_value(String *out, uint length);
+ void print_key_value_binary(String *out, const uchar* key, uint length);
protected:
bool set_warning(unsigned int code, int cuted_increment) const
{
@@ -3587,6 +3589,7 @@ class Field_string :public Field_longstr {
{ return charset() == &my_charset_bin ? FALSE : TRUE; }
Field *make_new_field(MEM_ROOT *root, TABLE *new_table, bool keep_type);
virtual uint get_key_image(uchar *buff,uint length, imagetype type);
+ void print_key_value(String *out, uint length);
private:
int save_field_metadata(uchar *first_byte);
};
@@ -3692,6 +3695,8 @@ class Field_varstring :public Field_longstr {
uint is_equal(Create_field *new_field);
void hash(ulong *nr, ulong *nr2);
uint length_size() { return length_bytes; }
+ void print_key_value(String *out, uint length);
+
private:
int save_field_metadata(uchar *first_byte);
};
@@ -4029,6 +4034,7 @@ class Field_blob :public Field_longstr {
uint32 char_length() const;
uint32 character_octet_length() const;
uint is_equal(Create_field *new_field);
+ void print_key_value(String *out, uint length);
friend void TABLE::remember_blob_values(String *blob_storage);
friend void TABLE::restore_blob_values(String *blob_storage);
@@ -4153,6 +4159,10 @@ class Field_geom :public Field_blob {
geometry_type get_geometry_type() { return geom_type; };
static geometry_type geometry_type_merge(geometry_type, geometry_type);
uint get_srid() { return srid; }
+ void print_key_value(String *out, uint length)
+ {
+ out->append(STRING_WITH_LEN("unprintable_geometry_value"));
+ }
};
uint gis_field_options_image(uchar *buff, List<Create_field> &create_fields);
@@ -4445,6 +4455,10 @@ class Field_bit :public Field {
{
return get_mm_leaf_int(param, key_part, cond, op, value, true);
}
+ void print_key_value(String *out, uint length)
+ {
+ val_int_as_str(out, 1);
+ }
private:
virtual size_t do_last_null_byte() const;
int save_field_metadata(uchar *first_byte);
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 7c40a99..461df77 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -15785,7 +15785,7 @@ static void append_range_all_keyparts(Json_writer_array *range_trace,
uint n_key_parts= param.table->actual_n_key_parts(keyinfo);
seq.keyno= idx;
seq.real_keyno= param.real_keynr[idx];
- seq.param= ¶m;
+ seq.param= ¶m;
seq.start= keypart;
const KEY_PART_INFO *cur_key_part= key_parts + keypart->part;
seq_it= seq_if.init((void *) &seq, 0, flags);
@@ -15828,12 +15828,10 @@ static void print_key_value(String *out, const KEY_PART_INFO *key_part,
{
// Byte 0 of a nullable key is the null-byte. If set, key is NULL.
if (field->real_maybe_null() && *key)
+ {
out->append(STRING_WITH_LEN("NULL"));
- else
- (field->type() == MYSQL_TYPE_GEOMETRY)
- ? out->append(STRING_WITH_LEN("unprintable_geometry_value"))
- : out->append(STRING_WITH_LEN("unprintable_blob_value"));
- goto next;
+ goto next;
+ }
}
if (field->real_maybe_null())
@@ -15852,28 +15850,12 @@ static void print_key_value(String *out, const KEY_PART_INFO *key_part,
store_length--;
}
- /*
- Binary data cannot be converted to UTF8 which is what the
- optimizer trace expects. If the column is binary, the hex
- representation is printed to the trace instead.
- */
- if (field->flags & BINARY_FLAG)
- {
- out->append("0x");
- for (uint i = 0; i < store_length; i++)
- {
- out->append(_dig_vec_lower[*(key + i) >> 4]);
- out->append(_dig_vec_lower[*(key + i) & 0x0F]);
- }
- goto next;
- }
-
field->set_key_image(key, key_part->length);
- if (field->type() == MYSQL_TYPE_BIT)
- (void)field->val_int_as_str(&tmp, 1); // may change tmp's charset
+ field->print_key_value(&tmp, key_part->length);
+ if (field->charset() == &my_charset_bin)
+ out->append(tmp.ptr(), tmp.length(), tmp.charset());
else
- field->val_str(&tmp); // may change tmp's charset
- out->append(tmp.ptr(), tmp.length(), tmp.charset());
+ tmp.print(out, system_charset_info);
next:
if (key + store_length < key_end)
diff --git a/sql/sql_string.cc b/sql/sql_string.cc
index 45af08f..4e55510 100644
--- a/sql/sql_string.cc
+++ b/sql/sql_string.cc
@@ -1084,6 +1084,7 @@ bool String::append_for_single_quote(const char *st, size_t len)
case '\n': APPEND(STRING_WITH_LEN("\\n"));
case '\r': APPEND(STRING_WITH_LEN("\\r"));
case '\032': APPEND(STRING_WITH_LEN("\\Z"));
+ case '\t': APPEND(STRING_WITH_LEN("\\t"));
default: APPEND(c);
}
}
@@ -1175,3 +1176,14 @@ uint convert_to_printable(char *to, size_t to_len,
*t= '\0';
return (uint) (t - to);
}
+
+bool String::append_semi_hex(const char *s, uint len, CHARSET_INFO *cs)
+{
+ size_t dst_len= len * 4 + 1; //extra length for the '\0' character
+ if (reserve(dst_len))
+ return true;
+ uint nbytes= convert_to_printable(Ptr + str_length, dst_len, s, len, cs);
+ DBUG_ASSERT((ulonglong) str_length + nbytes < UINT32_MAX);
+ str_length+= nbytes;
+ return false;
+}
\ No newline at end of file
diff --git a/sql/sql_string.h b/sql/sql_string.h
index fa941c8..ae8e7ea 100644
--- a/sql/sql_string.h
+++ b/sql/sql_string.h
@@ -666,6 +666,7 @@ class Binary_string: public Static_binary_string
int reserve(size_t space_needed)
{
+ DBUG_ASSERT((ulonglong) str_length + space_needed < UINT32_MAX);
return realloc(str_length + space_needed);
}
int reserve(size_t space_needed, size_t grow_by);
@@ -951,6 +952,8 @@ class String: public Charset, public Binary_string
{
return !sortcmp(this, other, cs);
}
+private:
+ bool append_semi_hex(const char *s, uint len, CHARSET_INFO *cs);
};
1
0
[Commits] fc8caf1997b: Merge remote-tracking branch 'origin/10.3' into bb-10.4-supermergejan
by jan 12 Apr '19
by jan 12 Apr '19
12 Apr '19
revision-id: fc8caf1997ba6b0dc099103347b12e8bd264c340 (mariadb-10.4.4-7-gfc8caf1997b)
parent(s): 71848585f81690babb70cba345719a5d178b00d7 4dc10ec68d354241768ce4779c024b4bb2ca897b
author: Jan Lindström
committer: Jan Lindström
timestamp: 2019-04-12 12:37:27 +0300
message:
Merge remote-tracking branch 'origin/10.3' into bb-10.4-supermergejan
cmake/cpack_rpm.cmake | 2 +-
storage/spider/spd_db_mysql.cc | 3 +++
storage/spider/spd_environ.h | 1 +
3 files changed, 5 insertions(+), 1 deletion(-)
diff --cc storage/spider/spd_db_mysql.cc
index d744de02a9d,262e47120ce..18b66d33fbf
--- a/storage/spider/spd_db_mysql.cc
+++ b/storage/spider/spd_db_mysql.cc
@@@ -1948,6 -1948,6 +1948,9 @@@ int spider_db_mbase::connect
connect_retry_count--;
my_sleep((ulong) connect_retry_interval);
} else {
++#ifdef SPIDER_NET_HAS_THD
++ db_conn->net.thd = NULL;
++#endif
if (connect_mutex)
pthread_mutex_unlock(&spider_open_conn_mutex);
break;
diff --cc storage/spider/spd_environ.h
index 5e66a912582,5e66a912582..ded2927482b
--- a/storage/spider/spd_environ.h
+++ b/storage/spider/spd_environ.h
@@@ -25,6 -25,6 +25,7 @@@
#if defined(MARIADB_BASE_VERSION) && MYSQL_VERSION_ID >= 100100
#define SPIDER_SUPPORT_CREATE_OR_REPLACE_TABLE
++#define SPIDER_NET_HAS_THD
#endif
#if defined(MARIADB_BASE_VERSION) && MYSQL_VERSION_ID >= 100211
1
0
[Commits] 6a645dd02a0: Merge remote-tracking branch 'origin/10.3' into bb-10.4-janmerge
by jan 12 Apr '19
by jan 12 Apr '19
12 Apr '19
revision-id: 6a645dd02a0b88002d309043f39a62a641759621 (mariadb-10.4.4-7-g6a645dd02a0)
parent(s): 71848585f81690babb70cba345719a5d178b00d7 a05f423554d0825997c1ff3de7e49e981e84e882
author: Jan Lindström
committer: Jan Lindström
timestamp: 2019-04-12 11:55:18 +0300
message:
Merge remote-tracking branch 'origin/10.3' into bb-10.4-janmerge
cmake/cpack_rpm.cmake | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
1
0
[Commits] 9a749d1e36f: MDEV-17362: SIGSEGV in JOIN::optimize_inner or Assertion `fixed == 0'
by Galina 12 Apr '19
by Galina 12 Apr '19
12 Apr '19
revision-id: 9a749d1e36f0ec6d1af0cb302cf48164ccdd975e (mariadb-10.4.3-108-g9a749d1e36f)
parent(s): 11fa88ef67bea74bd13ad688938a03d5b266db64
author: Galina Shalygina
committer: Galina Shalygina
timestamp: 2019-04-12 10:58:02 +0300
message:
MDEV-17362: SIGSEGV in JOIN::optimize_inner or Assertion `fixed == 0'
failed in Item_equal::fix_fields, server crashes after 2nd execution of PS
Fixed the bug when pushdown into materialized IN subquery is made
only in the first execution of statetement.
---
mysql-test/main/in_subq_cond_pushdown.result | 18 ++++++++++++++++++
mysql-test/main/in_subq_cond_pushdown.test | 22 ++++++++++++++++++++++
mysql-test/main/subselect_innodb.result | 13 +++++++++++++
mysql-test/main/subselect_innodb.test | 16 ++++++++++++++++
sql/opt_subselect.cc | 1 +
5 files changed, 70 insertions(+)
diff --git a/mysql-test/main/in_subq_cond_pushdown.result b/mysql-test/main/in_subq_cond_pushdown.result
index eef320d2d04..eec85ca9c23 100644
--- a/mysql-test/main/in_subq_cond_pushdown.result
+++ b/mysql-test/main/in_subq_cond_pushdown.result
@@ -3887,3 +3887,21 @@ i1
2
1
DROP TABLE t1,t2,t3;
+#
+# MDEV-17362: SIGSEGV in JOIN::optimize_inner or Assertion `fixed == 0'
+# failed in Item_equal::fix_fields, server crashes after 2nd execution
+# of PS
+#
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (x int, y int);
+INSERT INTO t1 VALUES (1,1),(2,2);
+INSERT INTO t2 VALUES (1,1),(2,2),(2,3);
+PREPARE stmt FROM "
+SELECT * FROM t1
+WHERE a = b
+ AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);";
+EXECUTE stmt;
+a b
+EXECUTE stmt;
+a b
+DROP TABLE t1,t2;
diff --git a/mysql-test/main/in_subq_cond_pushdown.test b/mysql-test/main/in_subq_cond_pushdown.test
index 7763201cda1..3b78dd9461c 100644
--- a/mysql-test/main/in_subq_cond_pushdown.test
+++ b/mysql-test/main/in_subq_cond_pushdown.test
@@ -860,3 +860,25 @@ SELECT t3.i1 FROM t3
GROUP BY i1 HAVING t.i1 < 3));
DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # MDEV-17362: SIGSEGV in JOIN::optimize_inner or Assertion `fixed == 0'
+--echo # failed in Item_equal::fix_fields, server crashes after 2nd execution
+--echo # of PS
+--echo #
+
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (x int, y int);
+
+INSERT INTO t1 VALUES (1,1),(2,2);
+INSERT INTO t2 VALUES (1,1),(2,2),(2,3);
+
+PREPARE stmt FROM "
+SELECT * FROM t1
+WHERE a = b
+ AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);";
+
+EXECUTE stmt;
+EXECUTE stmt;
+
+DROP TABLE t1,t2;
diff --git a/mysql-test/main/subselect_innodb.result b/mysql-test/main/subselect_innodb.result
index 518158e3a04..474217c652a 100644
--- a/mysql-test/main/subselect_innodb.result
+++ b/mysql-test/main/subselect_innodb.result
@@ -636,3 +636,16 @@ execute stmt;
execute stmt;
1
drop table t1,t2,t3;
+CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB;
+CREATE TABLE t2 (x int, y int) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,1),(2,2);
+INSERT INTO t2 VALUES (1,1),(2,2),(2,3);
+PREPARE stmt FROM "
+SELECT * FROM t1
+WHERE a = b
+ AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);";
+EXECUTE stmt;
+a b
+EXECUTE stmt;
+a b
+DROP TABLE t1,t2;
diff --git a/mysql-test/main/subselect_innodb.test b/mysql-test/main/subselect_innodb.test
index 214d692e793..d76051cadfc 100644
--- a/mysql-test/main/subselect_innodb.test
+++ b/mysql-test/main/subselect_innodb.test
@@ -635,3 +635,19 @@ execute stmt;
execute stmt;
drop table t1,t2,t3;
+
+CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB;
+CREATE TABLE t2 (x int, y int) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (1,1),(2,2);
+INSERT INTO t2 VALUES (1,1),(2,2),(2,3);
+
+PREPARE stmt FROM "
+SELECT * FROM t1
+WHERE a = b
+ AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);";
+
+EXECUTE stmt;
+EXECUTE stmt;
+
+DROP TABLE t1,t2;
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index a1d60aff6ef..664a230c518 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -5954,6 +5954,7 @@ bool setup_degenerate_jtbm_semi_joins(JOIN *join,
if ((subq_pred= table->jtbm_subselect))
{
+ subq_pred->is_jtbm_const_tab= FALSE;
JOIN *subq_join= subq_pred->unit->first_select()->join;
if (!subq_join->tables_list || !subq_join->table_count)
1
0
10 Apr '19
revision-id: 3226c5083266291ad8ee932e467ebc9d80491b12 (mariadb-10.4.3-168-g3226c50)
parent(s): 517a56c1884e74b7c7606b56b0884537e792089e
author: Varun Gupta
committer: Varun Gupta
timestamp: 2019-04-10 15:34:03 +0530
message:
MDEV-18880: Optimizer trace prints date in hexadecimal
Introducded a print_key_value function to makes sure that the trace prints data in readable format
for readable characters and the rest of the characters are printed as hexadecimal.
---
mysql-test/main/opt_trace.result | 442 +++++++++++++++++++++++++++++++++-
mysql-test/main/opt_trace.test | 56 +++++
mysql-test/main/opt_trace_ucs2.result | 54 +++++
mysql-test/main/opt_trace_ucs2.test | 10 +
sql/field.cc | 55 +++++
sql/field.h | 14 ++
sql/opt_range.cc | 34 +--
sql/sql_string.cc | 11 +
sql/sql_string.h | 3 +
9 files changed, 647 insertions(+), 32 deletions(-)
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index ed5881e..3aec8aa 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -1446,7 +1446,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id {
{
"index": "id",
"covering": true,
- "ranges": ["(0x24a20f) <= (a)"],
+ "ranges": ["(2001-01-04) <= (a)"],
"rows": 9,
"cost": 2.35
}
@@ -1462,7 +1462,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id {
"rows": 9,
"cost": 2.35,
"key_parts_used_for_access": ["id"],
- "ranges": ["(0x24a20f) <= (a)"],
+ "ranges": ["(2001-01-04) <= (a)"],
"chosen": false,
"cause": "cost"
},
@@ -1624,7 +1624,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id {
{
"index": "id",
"covering": true,
- "ranges": ["(0x24a20f) <= (a) <= (0x24a20f)"],
+ "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"],
"rows": 9,
"cost": 2.35
}
@@ -1640,7 +1640,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id {
"rows": 9,
"cost": 2.35,
"key_parts_used_for_access": ["id", "a"],
- "ranges": ["(0x24a20f) <= (a) <= (0x24a20f)"],
+ "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"],
"chosen": false,
"cause": "cost"
},
@@ -6130,7 +6130,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"index": "start_date",
"ranges":
[
- "(0x4ac60f,NULL) < (start_date,end_date)"
+ "(2019-02-10,NULL) < (start_date,end_date)"
],
"rowid_ordered": false,
"using_mrr": false,
@@ -6214,7 +6214,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"index": "i_b",
"ranges":
[
- "(0xd95b94336a9946a39cf5b58cfe772d8c) <= (b) <= (0xd95b94336a9946a39cf5b58cfe772d8c)"
+ "(\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C) <= (b) <= (\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C)"
],
"rowid_ordered": true,
"using_mrr": false,
@@ -6268,4 +6268,434 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
}
]
drop table t1;
+#
+# MDEV-18880: Optimizer trace prints date in hexadecimal
+#
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10) CHARSET BINARY , INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 13 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(ab\x0A) <= (b) <= (ab\x0A)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3787,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+ALTER TABLE t1 modify column b BINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 11 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(ab\x0A\x00\x00\x00\x00\x00\x00\x00) <= (b) <= (ab\x0A\x00\x00\x00\x00\x00\x00\x00)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3785,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+ALTER TABLE t1 modify column b VARBINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 13 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(ab\x0A) <= (b) <= (ab\x0A)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3787,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1;
+CREATE TABLE t1(i INT PRIMARY KEY, b CHAR(10), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 11 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(ab\n) <= (b) <= (ab\n)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3785,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1;
+CREATE TABLE t1(i INT PRIMARY KEY, b blob , INDEX i_b(b));
+Warnings:
+Note 1071 Specified key was too long; max key length is 1000 bytes
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 1003 const 1 Using where
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(ab\x0A) <= (b) <= (ab\x0A)"
+ ],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 3.5719,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1;
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, 'ab\n');
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 13 const 2 Using index condition
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n') {
+ "steps": [
+ {
+ "join_preparation": {
+ "select_id": 1,
+ "steps": [
+ {
+ "expanded_query": "select t1.i AS i,t1.b AS b from t1 where t1.b = 'ab\n'"
+ }
+ ]
+ }
+ },
+ {
+ "join_optimization": {
+ "select_id": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "t1.b = 'ab\n'",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "multiple equal('ab\n', t1.b)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "multiple equal('ab\n', t1.b)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "multiple equal('ab\n', t1.b)"
+ }
+ ]
+ }
+ },
+ {
+ "table_dependencies": [
+ {
+ "table": "t1",
+ "row_may_be_null": false,
+ "map_bit": 0,
+ "depends_on_map_bits": []
+ }
+ ]
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "table": "t1",
+ "field": "b",
+ "equals": "'ab\n'",
+ "null_rejecting": false
+ }
+ ]
+ },
+ {
+ "rows_estimation": [
+ {
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "rows": 2,
+ "cost": 4.5098
+ },
+ "potential_range_indexes": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not applicable"
+ },
+ {
+ "index": "i_b",
+ "usable": true,
+ "key_parts": ["b"]
+ }
+ ],
+ "setup_range_conditions": [],
+ "group_index_range": {
+ "chosen": false,
+ "cause": "no group by or distinct"
+ },
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "i_b",
+ "ranges": ["(ab\n) <= (b) <= (ab\n)"],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 2,
+ "cost": 3.6324,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect": {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union": []
+ },
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "i_b",
+ "rows": 2,
+ "ranges": ["(ab\n) <= (b) <= (ab\n)"]
+ },
+ "rows_for_plan": 2,
+ "cost_for_plan": 3.6324,
+ "chosen": true
+ }
+ }
+ },
+ {
+ "selectivity_for_indexes": [
+ {
+ "index_name": "i_b",
+ "selectivity_from_index": 1
+ }
+ ],
+ "selectivity_for_columns": [],
+ "cond_selectivity": 1
+ }
+ ]
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "plan_prefix": [],
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "i_b",
+ "used_range_estimates": true,
+ "rows": 2,
+ "cost": 3,
+ "chosen": true
+ },
+ {
+ "type": "scan",
+ "chosen": false,
+ "cause": "cost"
+ }
+ ]
+ }
+ }
+ ]
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "t1.b = 'ab\n'",
+ "attached_conditions_computation": [],
+ "attached_conditions_summary": [
+ {
+ "table": "t1",
+ "attached": "t1.b = 'ab\n'"
+ }
+ ]
+ }
+ }
+ ]
+ }
+ },
+ {
+ "join_execution": {
+ "select_id": 1,
+ "steps": []
+ }
+ }
+ ]
+} 0 0
+drop table t1;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k (a int);
+insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
+create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
+insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
+explain format=json select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t1",
+ "access_type": "range",
+ "possible_keys": ["start_date"],
+ "key": "start_date",
+ "key_length": "8",
+ "used_key_parts": ["start_date", "end_date"],
+ "rows": 1000,
+ "filtered": 100,
+ "index_condition": "t1.start_date >= '2019-02-10' and t1.end_date < '2019-04-01'"
+ }
+ }
+}
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "start_date",
+ "ranges":
+ [
+ "(2019-02-10,NULL) < (start_date,end_date)"
+ ],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1000,
+ "cost": 1282.2,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1, t0, one_k;
set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index 981a53a..36bdc86 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -444,4 +444,60 @@ select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) fr
drop table t1;
+--echo #
+--echo # MDEV-18880: Optimizer trace prints date in hexadecimal
+--echo #
+
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10) CHARSET BINARY , INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+ALTER TABLE t1 modify column b BINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+ALTER TABLE t1 modify column b VARBINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+
+CREATE TABLE t1(i INT PRIMARY KEY, b CHAR(10), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+
+
+CREATE TABLE t1(i INT PRIMARY KEY, b blob , INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, 'ab\n');
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+#select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k (a int);
+insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
+create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
+--disable_warnings
+insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
+--enable_warnings
+explain format=json select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1, t0, one_k;
+
set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace_ucs2.result b/mysql-test/main/opt_trace_ucs2.result
new file mode 100644
index 0000000..306fdbf
--- /dev/null
+++ b/mysql-test/main/opt_trace_ucs2.result
@@ -0,0 +1,54 @@
+create or replace table t1 (col1 char(10) character set ucs2, filler char(100), key(col1)) ;
+insert into t1 values ('a', 'a');
+insert into t1 values ('a', 'a');
+set optimizer_trace=1;
+explain format=json select * from t1 force index(col1) where col1 >='a';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t1",
+ "access_type": "range",
+ "possible_keys": ["col1"],
+ "key": "col1",
+ "key_length": "21",
+ "used_key_parts": ["col1"],
+ "rows": 2,
+ "filtered": 100,
+ "index_condition": "t1.col1 >= 'a'"
+ }
+ }
+}
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "col1",
+ "ranges":
+ [
+ "(a) <= (col1)"
+ ],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 2,
+ "cost": 3.7609,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1;
diff --git a/mysql-test/main/opt_trace_ucs2.test b/mysql-test/main/opt_trace_ucs2.test
new file mode 100644
index 0000000..827dc40
--- /dev/null
+++ b/mysql-test/main/opt_trace_ucs2.test
@@ -0,0 +1,10 @@
+--source include/not_embedded.inc
+--source include/have_ucs2.inc
+
+create or replace table t1 (col1 char(10) character set ucs2, filler char(100), key(col1)) ;
+insert into t1 values ('a', 'a');
+insert into t1 values ('a', 'a');
+set optimizer_trace=1;
+explain format=json select * from t1 force index(col1) where col1 >='a';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
diff --git a/sql/field.cc b/sql/field.cc
index 4a93464..028d304 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -11228,3 +11228,58 @@ bool Field::val_str_nopad(MEM_ROOT *mem_root, LEX_CSTRING *to)
thd->variables.sql_mode= sql_mode_backup;
return rc;
}
+
+
+void Field::print_key_value(String *out, uint length)
+{
+ if (charset() == &my_charset_bin)
+ print_key_value_binary(out, ptr, length);
+ else
+ val_str(out);
+}
+
+
+void Field_string::print_key_value(String *out, uint length)
+{
+ if (charset() == &my_charset_bin)
+ {
+ length= field_charset->cset->lengthsp(field_charset, (const char*) ptr, length);
+ print_key_value_binary(out, ptr, length);
+ }
+ else
+ {
+ THD *thd= get_thd();
+ sql_mode_t sql_mode_backup= thd->variables.sql_mode;
+ thd->variables.sql_mode&= ~MODE_PAD_CHAR_TO_FULL_LENGTH;
+ val_str(out,out);
+ thd->variables.sql_mode= sql_mode_backup;
+ }
+}
+
+
+void Field_varstring::print_key_value(String *out, uint length)
+{
+ if (charset() == &my_charset_bin)
+ print_key_value_binary(out, get_data(), get_length());
+ else
+ val_str(out,out);
+}
+
+
+void Field_blob::print_key_value(String *out, uint length)
+{
+ if (charset() == &my_charset_bin)
+ {
+ uchar *blob;
+ memcpy(&blob, ptr+packlength, sizeof(uchar*));
+ print_key_value_binary(out, blob, get_length());
+ }
+ else
+ val_str(out, out);
+}
+
+
+void Field::print_key_value_binary(String *out, const uchar* key, uint length)
+{
+ out->append_semi_hex((const char*)key, length, charset());
+}
diff --git a/sql/field.h b/sql/field.h
index 8bd7343..0fbbc04 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -1380,6 +1380,8 @@ class Field: public Value_source
virtual int set_time() { return 1; }
bool set_warning(Sql_condition::enum_warning_level, unsigned int code,
int cuted_increment, ulong current_row=0) const;
+ virtual void print_key_value(String *out, uint length);
+ void print_key_value_binary(String *out, const uchar* key, uint length);
protected:
bool set_warning(unsigned int code, int cuted_increment) const
{
@@ -3587,6 +3589,7 @@ class Field_string :public Field_longstr {
{ return charset() == &my_charset_bin ? FALSE : TRUE; }
Field *make_new_field(MEM_ROOT *root, TABLE *new_table, bool keep_type);
virtual uint get_key_image(uchar *buff,uint length, imagetype type);
+ void print_key_value(String *out, uint length);
private:
int save_field_metadata(uchar *first_byte);
};
@@ -3692,6 +3695,8 @@ class Field_varstring :public Field_longstr {
uint is_equal(Create_field *new_field);
void hash(ulong *nr, ulong *nr2);
uint length_size() { return length_bytes; }
+ void print_key_value(String *out, uint length);
+
private:
int save_field_metadata(uchar *first_byte);
};
@@ -4029,6 +4034,7 @@ class Field_blob :public Field_longstr {
uint32 char_length() const;
uint32 character_octet_length() const;
uint is_equal(Create_field *new_field);
+ void print_key_value(String *out, uint length);
friend void TABLE::remember_blob_values(String *blob_storage);
friend void TABLE::restore_blob_values(String *blob_storage);
@@ -4153,6 +4159,10 @@ class Field_geom :public Field_blob {
geometry_type get_geometry_type() { return geom_type; };
static geometry_type geometry_type_merge(geometry_type, geometry_type);
uint get_srid() { return srid; }
+ void print_key_value(String *out, uint length)
+ {
+ out->append(STRING_WITH_LEN("unprintable_geometry_value"));
+ }
};
uint gis_field_options_image(uchar *buff, List<Create_field> &create_fields);
@@ -4445,6 +4455,10 @@ class Field_bit :public Field {
{
return get_mm_leaf_int(param, key_part, cond, op, value, true);
}
+ void print_key_value(String *out, uint length)
+ {
+ val_int_as_str(out, 1);
+ }
private:
virtual size_t do_last_null_byte() const;
int save_field_metadata(uchar *first_byte);
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 7c40a99..461df77 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -15785,7 +15785,7 @@ static void append_range_all_keyparts(Json_writer_array *range_trace,
uint n_key_parts= param.table->actual_n_key_parts(keyinfo);
seq.keyno= idx;
seq.real_keyno= param.real_keynr[idx];
- seq.param= ¶m;
+ seq.param= ¶m;
seq.start= keypart;
const KEY_PART_INFO *cur_key_part= key_parts + keypart->part;
seq_it= seq_if.init((void *) &seq, 0, flags);
@@ -15828,12 +15828,10 @@ static void print_key_value(String *out, const KEY_PART_INFO *key_part,
{
// Byte 0 of a nullable key is the null-byte. If set, key is NULL.
if (field->real_maybe_null() && *key)
+ {
out->append(STRING_WITH_LEN("NULL"));
- else
- (field->type() == MYSQL_TYPE_GEOMETRY)
- ? out->append(STRING_WITH_LEN("unprintable_geometry_value"))
- : out->append(STRING_WITH_LEN("unprintable_blob_value"));
- goto next;
+ goto next;
+ }
}
if (field->real_maybe_null())
@@ -15852,28 +15850,12 @@ static void print_key_value(String *out, const KEY_PART_INFO *key_part,
store_length--;
}
- /*
- Binary data cannot be converted to UTF8 which is what the
- optimizer trace expects. If the column is binary, the hex
- representation is printed to the trace instead.
- */
- if (field->flags & BINARY_FLAG)
- {
- out->append("0x");
- for (uint i = 0; i < store_length; i++)
- {
- out->append(_dig_vec_lower[*(key + i) >> 4]);
- out->append(_dig_vec_lower[*(key + i) & 0x0F]);
- }
- goto next;
- }
-
field->set_key_image(key, key_part->length);
- if (field->type() == MYSQL_TYPE_BIT)
- (void)field->val_int_as_str(&tmp, 1); // may change tmp's charset
+ field->print_key_value(&tmp, key_part->length);
+ if (field->charset() == &my_charset_bin)
+ out->append(tmp.ptr(), tmp.length(), tmp.charset());
else
- field->val_str(&tmp); // may change tmp's charset
- out->append(tmp.ptr(), tmp.length(), tmp.charset());
+ tmp.print(out, system_charset_info);
next:
if (key + store_length < key_end)
diff --git a/sql/sql_string.cc b/sql/sql_string.cc
index 45af08f..dbc5f4f 100644
--- a/sql/sql_string.cc
+++ b/sql/sql_string.cc
@@ -1175,3 +1175,14 @@ uint convert_to_printable(char *to, size_t to_len,
*t= '\0';
return (uint) (t - to);
}
+
+bool String::append_semi_hex(const char *s, uint len, CHARSET_INFO *cs)
+{
+ size_t dst_len= len * 4;
+ if (reserve(dst_len))
+ return true;
+ uint nbytes= convert_to_printable(Ptr + str_length, dst_len, s, len, cs);
+ DBUG_ASSERT((ulonglong) str_length + nbytes < UINT32_MAX);
+ str_length+= nbytes;
+ return false;
+}
\ No newline at end of file
diff --git a/sql/sql_string.h b/sql/sql_string.h
index fa941c8..ae8e7ea 100644
--- a/sql/sql_string.h
+++ b/sql/sql_string.h
@@ -666,6 +666,7 @@ class Binary_string: public Static_binary_string
int reserve(size_t space_needed)
{
+ DBUG_ASSERT((ulonglong) str_length + space_needed < UINT32_MAX);
return realloc(str_length + space_needed);
}
int reserve(size_t space_needed, size_t grow_by);
@@ -951,6 +952,8 @@ class String: public Charset, public Binary_string
{
return !sortcmp(this, other, cs);
}
+private:
+ bool append_semi_hex(const char *s, uint len, CHARSET_INFO *cs);
};
1
0
10 Apr '19
revision-id: 98189b6df7a7f88efc9b7a199a436eb3022fd157 (mariadb-10.4.3-168-g98189b6)
parent(s): 517a56c1884e74b7c7606b56b0884537e792089e
author: Varun Gupta
committer: Varun Gupta
timestamp: 2019-04-10 13:49:16 +0530
message:
MDEV-18880: Optimizer trace prints date in hexadecimal
Introducded a print_key_value function to makes sure that the trace prints data in readable format
for readable characters and the rest of the characters are printed as hexadecimal.
---
mysql-test/main/opt_trace.result | 442 ++++++++++++++++++++++++++++++++++++++-
mysql-test/main/opt_trace.test | 56 +++++
sql/field.cc | 59 ++++++
sql/field.h | 15 ++
sql/opt_range.cc | 34 +--
sql/sql_string.h | 5 +
6 files changed, 579 insertions(+), 32 deletions(-)
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index ed5881e..3aec8aa 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -1446,7 +1446,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id {
{
"index": "id",
"covering": true,
- "ranges": ["(0x24a20f) <= (a)"],
+ "ranges": ["(2001-01-04) <= (a)"],
"rows": 9,
"cost": 2.35
}
@@ -1462,7 +1462,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id {
"rows": 9,
"cost": 2.35,
"key_parts_used_for_access": ["id"],
- "ranges": ["(0x24a20f) <= (a)"],
+ "ranges": ["(2001-01-04) <= (a)"],
"chosen": false,
"cause": "cost"
},
@@ -1624,7 +1624,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id {
{
"index": "id",
"covering": true,
- "ranges": ["(0x24a20f) <= (a) <= (0x24a20f)"],
+ "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"],
"rows": 9,
"cost": 2.35
}
@@ -1640,7 +1640,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id {
"rows": 9,
"cost": 2.35,
"key_parts_used_for_access": ["id", "a"],
- "ranges": ["(0x24a20f) <= (a) <= (0x24a20f)"],
+ "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"],
"chosen": false,
"cause": "cost"
},
@@ -6130,7 +6130,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"index": "start_date",
"ranges":
[
- "(0x4ac60f,NULL) < (start_date,end_date)"
+ "(2019-02-10,NULL) < (start_date,end_date)"
],
"rowid_ordered": false,
"using_mrr": false,
@@ -6214,7 +6214,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"index": "i_b",
"ranges":
[
- "(0xd95b94336a9946a39cf5b58cfe772d8c) <= (b) <= (0xd95b94336a9946a39cf5b58cfe772d8c)"
+ "(\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C) <= (b) <= (\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C)"
],
"rowid_ordered": true,
"using_mrr": false,
@@ -6268,4 +6268,434 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
}
]
drop table t1;
+#
+# MDEV-18880: Optimizer trace prints date in hexadecimal
+#
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10) CHARSET BINARY , INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 13 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(ab\x0A) <= (b) <= (ab\x0A)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3787,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+ALTER TABLE t1 modify column b BINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 11 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(ab\x0A\x00\x00\x00\x00\x00\x00\x00) <= (b) <= (ab\x0A\x00\x00\x00\x00\x00\x00\x00)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3785,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+ALTER TABLE t1 modify column b VARBINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 13 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(ab\x0A) <= (b) <= (ab\x0A)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3787,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1;
+CREATE TABLE t1(i INT PRIMARY KEY, b CHAR(10), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 11 const 1 Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(ab\n) <= (b) <= (ab\n)"
+ ],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 2.3785,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1;
+CREATE TABLE t1(i INT PRIMARY KEY, b blob , INDEX i_b(b));
+Warnings:
+Note 1071 Specified key was too long; max key length is 1000 bytes
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 1003 const 1 Using where
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "i_b",
+ "ranges":
+ [
+ "(ab\x0A) <= (b) <= (ab\x0A)"
+ ],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1,
+ "cost": 3.5719,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1;
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, 'ab\n');
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i_b i_b 13 const 2 Using index condition
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n') {
+ "steps": [
+ {
+ "join_preparation": {
+ "select_id": 1,
+ "steps": [
+ {
+ "expanded_query": "select t1.i AS i,t1.b AS b from t1 where t1.b = 'ab\n'"
+ }
+ ]
+ }
+ },
+ {
+ "join_optimization": {
+ "select_id": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "t1.b = 'ab\n'",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "multiple equal('ab\n', t1.b)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "multiple equal('ab\n', t1.b)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "multiple equal('ab\n', t1.b)"
+ }
+ ]
+ }
+ },
+ {
+ "table_dependencies": [
+ {
+ "table": "t1",
+ "row_may_be_null": false,
+ "map_bit": 0,
+ "depends_on_map_bits": []
+ }
+ ]
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "table": "t1",
+ "field": "b",
+ "equals": "'ab\n'",
+ "null_rejecting": false
+ }
+ ]
+ },
+ {
+ "rows_estimation": [
+ {
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "rows": 2,
+ "cost": 4.5098
+ },
+ "potential_range_indexes": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not applicable"
+ },
+ {
+ "index": "i_b",
+ "usable": true,
+ "key_parts": ["b"]
+ }
+ ],
+ "setup_range_conditions": [],
+ "group_index_range": {
+ "chosen": false,
+ "cause": "no group by or distinct"
+ },
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "i_b",
+ "ranges": ["(ab\n) <= (b) <= (ab\n)"],
+ "rowid_ordered": true,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 2,
+ "cost": 3.6324,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect": {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union": []
+ },
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "i_b",
+ "rows": 2,
+ "ranges": ["(ab\n) <= (b) <= (ab\n)"]
+ },
+ "rows_for_plan": 2,
+ "cost_for_plan": 3.6324,
+ "chosen": true
+ }
+ }
+ },
+ {
+ "selectivity_for_indexes": [
+ {
+ "index_name": "i_b",
+ "selectivity_from_index": 1
+ }
+ ],
+ "selectivity_for_columns": [],
+ "cond_selectivity": 1
+ }
+ ]
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "plan_prefix": [],
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "i_b",
+ "used_range_estimates": true,
+ "rows": 2,
+ "cost": 3,
+ "chosen": true
+ },
+ {
+ "type": "scan",
+ "chosen": false,
+ "cause": "cost"
+ }
+ ]
+ }
+ }
+ ]
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "t1.b = 'ab\n'",
+ "attached_conditions_computation": [],
+ "attached_conditions_summary": [
+ {
+ "table": "t1",
+ "attached": "t1.b = 'ab\n'"
+ }
+ ]
+ }
+ }
+ ]
+ }
+ },
+ {
+ "join_execution": {
+ "select_id": 1,
+ "steps": []
+ }
+ }
+ ]
+} 0 0
+drop table t1;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k (a int);
+insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
+create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
+insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
+explain format=json select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t1",
+ "access_type": "range",
+ "possible_keys": ["start_date"],
+ "key": "start_date",
+ "key_length": "8",
+ "used_key_parts": ["start_date", "end_date"],
+ "rows": 1000,
+ "filtered": 100,
+ "index_condition": "t1.start_date >= '2019-02-10' and t1.end_date < '2019-04-01'"
+ }
+ }
+}
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+
+ {
+ "range_scan_alternatives":
+ [
+
+ {
+ "index": "start_date",
+ "ranges":
+ [
+ "(2019-02-10,NULL) < (start_date,end_date)"
+ ],
+ "rowid_ordered": false,
+ "using_mrr": false,
+ "index_only": false,
+ "rows": 1000,
+ "cost": 1282.2,
+ "chosen": true
+ }
+ ],
+ "analyzing_roworder_intersect":
+ {
+ "cause": "too few roworder scans"
+ },
+ "analyzing_index_merge_union":
+ [
+ ]
+ }
+]
+drop table t1, t0, one_k;
set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index 981a53a..36bdc86 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -444,4 +444,60 @@ select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) fr
drop table t1;
+--echo #
+--echo # MDEV-18880: Optimizer trace prints date in hexadecimal
+--echo #
+
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10) CHARSET BINARY , INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+ALTER TABLE t1 modify column b BINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+ALTER TABLE t1 modify column b VARBINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+
+CREATE TABLE t1(i INT PRIMARY KEY, b CHAR(10), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+
+
+CREATE TABLE t1(i INT PRIMARY KEY, b blob , INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, 'ab\n');
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+#select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k (a int);
+insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
+create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
+--disable_warnings
+insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
+--enable_warnings
+explain format=json select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1, t0, one_k;
+
set optimizer_trace='enabled=off';
diff --git a/sql/field.cc b/sql/field.cc
index 4a93464..12fd423 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -11228,3 +11228,62 @@ bool Field::val_str_nopad(MEM_ROOT *mem_root, LEX_CSTRING *to)
thd->variables.sql_mode= sql_mode_backup;
return rc;
}
+
+
+void Field::print_key_value(String *out, uint length)
+{
+ if (charset() == &my_charset_bin)
+ print_key_value_binary(out, ptr, length);
+ else
+ val_str(out);
+}
+
+
+void Field_string::print_key_value(String *out, uint length)
+{
+ if (charset() == &my_charset_bin)
+ {
+ length= field_charset->cset->lengthsp(field_charset, (const char*) ptr, length);
+ print_key_value_binary(out, ptr, length);
+ }
+ else
+ val_str(out,out);
+}
+
+
+void Field_varstring::print_key_value(String *out, uint length)
+{
+ if (charset() == &my_charset_bin)
+ print_key_value_binary(out, get_data(), get_length());
+ else
+ val_str(out,out);
+}
+
+
+void Field_blob::print_key_value(String *out, uint length)
+{
+ uchar *blob;
+ memcpy(&blob, ptr+packlength, sizeof(uchar*));
+ print_key_value_binary(out, blob, get_length());
+}
+
+
+void Field::print_key_value_binary(String *out, const uchar* key, uint length)
+{
+ size_t dst_len= length*4;
+ StringBuffer<128> tmp(system_charset_info);
+ tmp.reserve(dst_len);
+
+ tmp.copy_semi_hex((const char*) key, static_cast<size_t>(length), charset());
+ out->append(tmp.c_ptr_safe(), tmp.length(), charset());
+}
+
+
+void Field::print_key_value_helper(String *out, uint length)
+{
+ THD *thd= get_thd();
+ sql_mode_t sql_mode_backup= thd->variables.sql_mode;
+ thd->variables.sql_mode&= ~MODE_PAD_CHAR_TO_FULL_LENGTH;
+ print_key_value(out, length);
+ thd->variables.sql_mode= sql_mode_backup;
+}
diff --git a/sql/field.h b/sql/field.h
index 8bd7343..35ca939 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -1380,6 +1380,9 @@ class Field: public Value_source
virtual int set_time() { return 1; }
bool set_warning(Sql_condition::enum_warning_level, unsigned int code,
int cuted_increment, ulong current_row=0) const;
+ virtual void print_key_value(String *out, uint length);
+ virtual void print_key_value_helper(String *out, uint length);
+ void print_key_value_binary(String *out, const uchar* key, uint length);
protected:
bool set_warning(unsigned int code, int cuted_increment) const
{
@@ -3587,6 +3590,7 @@ class Field_string :public Field_longstr {
{ return charset() == &my_charset_bin ? FALSE : TRUE; }
Field *make_new_field(MEM_ROOT *root, TABLE *new_table, bool keep_type);
virtual uint get_key_image(uchar *buff,uint length, imagetype type);
+ void print_key_value(String *out, uint length);
private:
int save_field_metadata(uchar *first_byte);
};
@@ -3692,6 +3696,8 @@ class Field_varstring :public Field_longstr {
uint is_equal(Create_field *new_field);
void hash(ulong *nr, ulong *nr2);
uint length_size() { return length_bytes; }
+ void print_key_value(String *out, uint length);
+
private:
int save_field_metadata(uchar *first_byte);
};
@@ -4029,6 +4035,7 @@ class Field_blob :public Field_longstr {
uint32 char_length() const;
uint32 character_octet_length() const;
uint is_equal(Create_field *new_field);
+ void print_key_value(String *out, uint length);
friend void TABLE::remember_blob_values(String *blob_storage);
friend void TABLE::restore_blob_values(String *blob_storage);
@@ -4153,6 +4160,10 @@ class Field_geom :public Field_blob {
geometry_type get_geometry_type() { return geom_type; };
static geometry_type geometry_type_merge(geometry_type, geometry_type);
uint get_srid() { return srid; }
+ void print_key_value(String *out, uint length)
+ {
+ out->append(STRING_WITH_LEN("unprintable_geometry_value"));
+ }
};
uint gis_field_options_image(uchar *buff, List<Create_field> &create_fields);
@@ -4445,6 +4456,10 @@ class Field_bit :public Field {
{
return get_mm_leaf_int(param, key_part, cond, op, value, true);
}
+ void print_key_value(String *out, uint length)
+ {
+ val_int_as_str(out, 1);
+ }
private:
virtual size_t do_last_null_byte() const;
int save_field_metadata(uchar *first_byte);
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 7c40a99..3ceb484 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -15785,7 +15785,7 @@ static void append_range_all_keyparts(Json_writer_array *range_trace,
uint n_key_parts= param.table->actual_n_key_parts(keyinfo);
seq.keyno= idx;
seq.real_keyno= param.real_keynr[idx];
- seq.param= ¶m;
+ seq.param= ¶m;
seq.start= keypart;
const KEY_PART_INFO *cur_key_part= key_parts + keypart->part;
seq_it= seq_if.init((void *) &seq, 0, flags);
@@ -15828,12 +15828,10 @@ static void print_key_value(String *out, const KEY_PART_INFO *key_part,
{
// Byte 0 of a nullable key is the null-byte. If set, key is NULL.
if (field->real_maybe_null() && *key)
+ {
out->append(STRING_WITH_LEN("NULL"));
- else
- (field->type() == MYSQL_TYPE_GEOMETRY)
- ? out->append(STRING_WITH_LEN("unprintable_geometry_value"))
- : out->append(STRING_WITH_LEN("unprintable_blob_value"));
- goto next;
+ goto next;
+ }
}
if (field->real_maybe_null())
@@ -15852,28 +15850,12 @@ static void print_key_value(String *out, const KEY_PART_INFO *key_part,
store_length--;
}
- /*
- Binary data cannot be converted to UTF8 which is what the
- optimizer trace expects. If the column is binary, the hex
- representation is printed to the trace instead.
- */
- if (field->flags & BINARY_FLAG)
- {
- out->append("0x");
- for (uint i = 0; i < store_length; i++)
- {
- out->append(_dig_vec_lower[*(key + i) >> 4]);
- out->append(_dig_vec_lower[*(key + i) & 0x0F]);
- }
- goto next;
- }
-
field->set_key_image(key, key_part->length);
- if (field->type() == MYSQL_TYPE_BIT)
- (void)field->val_int_as_str(&tmp, 1); // may change tmp's charset
+ field->print_key_value_helper(&tmp, key_part->length);
+ if (field->charset() == &my_charset_bin)
+ out->append(tmp.ptr(), tmp.length(), tmp.charset());
else
- field->val_str(&tmp); // may change tmp's charset
- out->append(tmp.ptr(), tmp.length(), tmp.charset());
+ tmp.print(out, system_charset_info);
next:
if (key + store_length < key_end)
diff --git a/sql/sql_string.h b/sql/sql_string.h
index fa941c8..d5a795d 100644
--- a/sql/sql_string.h
+++ b/sql/sql_string.h
@@ -951,6 +951,11 @@ class String: public Charset, public Binary_string
{
return !sortcmp(this, other, cs);
}
+ void copy_semi_hex(const char *st, size_t len, CHARSET_INFO *cs)
+ {
+ uint length= convert_to_printable(Ptr, len*4, st, len, cs);
+ str_length+= length;
+ }
};
1
0
10 Apr '19
revision-id: 71848585f81690babb70cba345719a5d178b00d7 (mariadb-10.4.4-6-g71848585f81)
parent(s): 304ae942f78f231a1f382624cdef9be80b3f5b84
author: Jan Lindström
committer: Jan Lindström
timestamp: 2019-04-10 11:19:38 +0300
message:
Fix InnoDB dynamic plugin compile errors on wsrep patch.
---
include/mysql/service_wsrep.h | 5 ++++-
sql/sql_plugin_services.ic | 3 ++-
sql/wsrep_dummy.cc | 3 +++
3 files changed, 9 insertions(+), 2 deletions(-)
diff --git a/include/mysql/service_wsrep.h b/include/mysql/service_wsrep.h
index 8424689a219..d76643364b3 100644
--- a/include/mysql/service_wsrep.h
+++ b/include/mysql/service_wsrep.h
@@ -83,6 +83,7 @@ extern struct wsrep_service_st {
const char* (*wsrep_get_sr_table_name_func)();
my_bool (*wsrep_get_debug_func)();
void (*wsrep_commit_ordered_func)(MYSQL_THD thd);
+ my_bool (*wsrep_thd_is_applying_func)(const MYSQL_THD thd);
} *wsrep_service;
#define MYSQL_SERVICE_WSREP_INCLUDED
@@ -121,7 +122,8 @@ extern struct wsrep_service_st {
#define wsrep_thd_skip_locking(T) wsrep_service->wsrep_thd_skip_locking_func(T)
#define wsrep_get_sr_table_name() wsrep_service->wsrep_get_sr_table_name_func()
#define wsrep_get_debug() wsrep_service->wsrep_get_debug_func()
-#define wsrep_commit_ordered(T) wsrep_service->wsrep_commit_ordered(T)
+#define wsrep_commit_ordered(T) wsrep_service->wsrep_commit_ordered_func(T)
+#define wsrep_thd_is_applying(T) wsrep_service->wsrep_thd_is_applying_func(T)
#else
@@ -213,6 +215,7 @@ extern "C" const char* wsrep_get_sr_table_name();
extern "C" my_bool wsrep_get_debug();
extern "C" void wsrep_commit_ordered(MYSQL_THD thd);
+extern "C" my_bool wsrep_thd_is_applying(const MYSQL_THD thd);
#endif
#endif /* MYSQL_SERVICE_WSREP_INCLUDED */
diff --git a/sql/sql_plugin_services.ic b/sql/sql_plugin_services.ic
index 36748121360..955b9a0ce3a 100644
--- a/sql/sql_plugin_services.ic
+++ b/sql/sql_plugin_services.ic
@@ -172,7 +172,8 @@ static struct wsrep_service_st wsrep_handler = {
wsrep_thd_skip_locking,
wsrep_get_sr_table_name,
wsrep_get_debug,
- wsrep_commit_ordered
+ wsrep_commit_ordered,
+ wsrep_thd_is_applying
};
static struct thd_specifics_service_st thd_specifics_handler=
diff --git a/sql/wsrep_dummy.cc b/sql/wsrep_dummy.cc
index bd37042992a..01f2ad88ed5 100644
--- a/sql/wsrep_dummy.cc
+++ b/sql/wsrep_dummy.cc
@@ -135,3 +135,6 @@ my_bool wsrep_get_debug()
void wsrep_commit_ordered(THD* )
{ }
+
+my_bool wsrep_thd_is_applying(const THD*)
+{ return 0;}
1
0
[Commits] 03dcec9a9ae: Fix wsrep_thd_is_applier macro to point correct function name.
by jan 10 Apr '19
by jan 10 Apr '19
10 Apr '19
revision-id: 03dcec9a9ae0869b38518ec039f644e47850585d (mariadb-10.2.23-66-g03dcec9a9ae)
parent(s): 725579c0f47f424578520c7252680c53ed1583eb
author: Jan Lindström
committer: Jan Lindström
timestamp: 2019-04-10 10:13:42 +0300
message:
Fix wsrep_thd_is_applier macro to point correct function name.
---
include/mysql/service_wsrep.h | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/include/mysql/service_wsrep.h b/include/mysql/service_wsrep.h
index 7237cccfaeb..9ef47207a07 100644
--- a/include/mysql/service_wsrep.h
+++ b/include/mysql/service_wsrep.h
@@ -160,7 +160,7 @@ extern struct wsrep_service_st {
#define wsrep_trx_order_before(T1,T2) wsrep_service->wsrep_trx_order_before_func(T1,T2)
#define wsrep_unlock_rollback() wsrep_service->wsrep_unlock_rollback_func()
#define wsrep_set_data_home_dir(A) wsrep_service->wsrep_set_data_home_dir_func(A)
-#define wsrep_thd_is_applier(T) wsrep_service->wsrep_thd_is_applier(T)
+#define wsrep_thd_is_applier(T) wsrep_service->wsrep_thd_is_applier_func(T)
#define wsrep_debug get_wsrep_debug()
#define wsrep_log_conflicts get_wsrep_log_conflicts()
1
0