- commits - lists.mariadb.org

[Commits] fc8caf1997b: Merge remote-tracking branch 'origin/10.3' into bb-10.4-supermergejan
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

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

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

[Commits] 3226c50: MDEV-18880: Optimizer trace prints date in hexadecimal
by varun 10 Apr '19

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= &param; 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

[Commits] 98189b6: MDEV-18880: Optimizer trace prints date in hexadecimal
by varun 10 Apr '19

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= &param; 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

[Commits] 71848585f81: Fix InnoDB dynamic plugin compile errors on wsrep patch.
by jan 10 Apr '19

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

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

[Commits] 0998803: MDEV-19195 Active Record unit test fails with MariaDB 10.4.3
by IgorBabaev 08 Apr '19

08 Apr '19

revision-id: 0998803020fc9d48fd2aa8b5177f9431fbee99dd (mariadb-10.4.4-4-g0998803) parent(s): 1e7ad5bb1c69dba8c7d721a2cfbbe98c7e900015 author: Igor Babaev committer: Igor Babaev timestamp: 2019-04-08 11:21:53 -0700 message: MDEV-19195 Active Record unit test fails with MariaDB 10.4.3 Currently usage of range rowid filters can be combined only with ref access and single index range access. So if the optimizer has chosen some other quick select method to access a joined table then no range rowid filter can be used for this table. --- mysql-test/main/rowid_filter_innodb.result | 84 ++++++++++++++++++------------ mysql-test/main/rowid_filter_innodb.test | 21 ++++++++ sql/sql_select.cc | 4 ++ 3 files changed, 77 insertions(+), 32 deletions(-) diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index d19aca1..c877e5a 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -1977,8 +1977,8 @@ union ( select * from t1 where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref|filter f1,f2 f2|f1 33|13 const 1 (2%) Using index condition; Using where; Using rowid filter -2 UNION t1 ref|filter f1,f2 f2|f1 33|13 const 1 (2%) Using index condition; Using where; Using rowid filter +1 PRIMARY t1 index_merge f1,f2 f1,f2 13,33 NULL 1 Using intersect(f1,f2); Using where +2 UNION t1 index_merge f1,f2 f1,f2 13,33 NULL 1 Using intersect(f1,f2); Using where NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL explain format=json ( select * from t1 where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) @@ -1997,24 +1997,24 @@ EXPLAIN "select_id": 1, "table": { "table_name": "t1", - "access_type": "ref", + "access_type": "index_merge", "possible_keys": ["f1", "f2"], - "key": "f2", - "key_length": "33", - "used_key_parts": ["f2"], - "ref": ["const"], - "rowid_filter": { - "range": { - "key": "f1", - "used_key_parts": ["f1"] - }, - "rows": 1, - "selectivity_pct": 1.5873 + "key_length": "13,33", + "index_merge": { + "intersect": { + "range": { + "key": "f1", + "used_key_parts": ["f1"] + }, + "range": { + "key": "f2", + "used_key_parts": ["f2"] + } + } }, "rows": 1, - "filtered": 100, - "index_condition": "t1.f2 is null", - "attached_condition": "t1.f1 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" + "filtered": 1.5873, + "attached_condition": "t1.f1 is null and t1.f2 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" } } }, @@ -2024,24 +2024,24 @@ EXPLAIN "operation": "UNION", "table": { "table_name": "t1", - "access_type": "ref", + "access_type": "index_merge", "possible_keys": ["f1", "f2"], - "key": "f2", - "key_length": "33", - "used_key_parts": ["f2"], - "ref": ["const"], - "rowid_filter": { - "range": { - "key": "f1", - "used_key_parts": ["f1"] - }, - "rows": 1, - "selectivity_pct": 1.5873 + "key_length": "13,33", + "index_merge": { + "intersect": { + "range": { + "key": "f1", + "used_key_parts": ["f1"] + }, + "range": { + "key": "f2", + "used_key_parts": ["f2"] + } + } }, "rows": 1, - "filtered": 100, - "index_condition": "t1.f2 is null", - "attached_condition": "t1.f1 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" + "filtered": 1.5873, + "attached_condition": "t1.f1 is null and t1.f2 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" } } } @@ -2050,4 +2050,24 @@ EXPLAIN } } drop table t1; +# +# MDEV-19195: possible RORI-plan and possible plan with range filter +# for not first joined table +# +create table t1 (id int not null primary key) engine=innodb; +insert into t1 values (2),(1); +create table t2 (y int,x int,index (x),index (y)) engine=innodb; +insert into t2 values +(4,1),(4,777),(2,1),(2,888),(111,1),(222,1),(333,345),(444,1), +(555,555),(666,1); +select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; +id y x +1 2 1 +explain extended select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 Using index +1 SIMPLE t2 index_merge x,y y,x 5,5 NULL 1 100.00 Using intersect(y,x); Using where; Using index +Warnings: +Note 1003 select 1 AS `id`,`test`.`t2`.`y` AS `y`,`test`.`t2`.`x` AS `x` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`y` = 2 and `test`.`t2`.`x` = 1 +drop table t1, t2; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test index 803f284..173ba15 100644 --- a/mysql-test/main/rowid_filter_innodb.test +++ b/mysql-test/main/rowid_filter_innodb.test @@ -43,5 +43,26 @@ eval explain format=json $q; drop table t1; +--echo # +--echo # MDEV-19195: possible RORI-plan and possible plan with range filter +--echo # for not first joined table +--echo # + +create table t1 (id int not null primary key) engine=innodb; +insert into t1 values (2),(1); + +create table t2 (y int,x int,index (x),index (y)) engine=innodb; +insert into t2 values + (4,1),(4,777),(2,1),(2,888),(111,1),(222,1),(333,345),(444,1), + (555,555),(666,1); + +let $q= +select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; + +eval $q; +eval explain extended $q; + +drop table t1, t2; + SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f96b340..ba32b8d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7815,6 +7815,10 @@ best_access_path(JOIN *join, DBUG_ASSERT(tmp >= 0); } } + else + { + best_filter= 0; + } loose_scan_opt.check_range_access(join, idx, s->quick); }

1 0

[Commits] 17d2b76267a: Update rocksdb revision (only code cleanup changes)
by Sergei Petrunia 08 Apr '19

08 Apr '19

revision-id: 17d2b76267a967a5e8fbdf35330bc5ee25115cc6 (fb-prod201801-222-g17d2b76267a) parent(s): a8a4635b2544b73a8e76f24202889b20ff8c9dca author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-04-08 16:33:01 +0300 message: Update rocksdb revision (only code cleanup changes) --- rocksdb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/rocksdb b/rocksdb index cc3ead4e33a..3b7567781e5 160000 --- a/rocksdb +++ b/rocksdb @@ -1 +1 @@ -Subproject commit cc3ead4e33a8637e51c72ecb91256d52285979f5 +Subproject commit 3b7567781e50b1ced3583a60fa7f1a568cc42d2e

1 0

[Commits] 3b7567781: Range Locking: More comments
by Sergei Petrunia 08 Apr '19

08 Apr '19

revision-id: 3b7567781e50b1ced3583a60fa7f1a568cc42d2e (v5.8-1033-g3b7567781) parent(s): 0ebc41a1c459263a30e2d2bc68348ed5bcdd59bd author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-04-08 16:29:58 +0300 message: Range Locking: More comments --- include/rocksdb/utilities/transaction.h | 11 +++++++++-- 1 file changed, 9 insertions(+), 2 deletions(-) diff --git a/include/rocksdb/utilities/transaction.h b/include/rocksdb/utilities/transaction.h index 9dbba3df7..e980e032e 100644 --- a/include/rocksdb/utilities/transaction.h +++ b/include/rocksdb/utilities/transaction.h @@ -46,8 +46,15 @@ using TransactionID = uint64_t; then key_a < key_b. - An empty string is less than any other constant (from this it follows that - for any prefix and suffix, {prefix, suffix} > {prefix}) + 2. An empty string is less than any other value. From this it follows that + for any prefix and suffix, {prefix, suffix} > {prefix}. + + 3. The row comparison function can compare key prefixes. If the data domain + includes keys A and B, then the comparison function is able to compare + equal-length prefixes: + + min_len= min(byte_length(A), byte_length(B)); + cmp(Slice(A, min_len), Slice(B, min_len)) == Prefix ranges == With lexicographic-like ordering, one may to construct ranges from a

1 0