- commits - lists.mariadb.org

[Commits] MDEV-20591 Wrong Number of rows in mysqlbinlog output
by Sachin Setiya 14 Sep '19

14 Sep '19

commit 211b7884ae572c87754a080b26858d2901006804 Author: Sachin <sachin.setiya(a)mariadb.com> Date: Sat Sep 14 12:53:36 2019 +0530 MDEV-20591 Wrong Number of rows in mysqlbinlog output calc_field_event_length should accurately calculate the size of BLOB type fields, Instead of returning just the bytes taken by length it should return length bytes + actual length. diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result index 2af8b805be8..0cc1805eadc 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result @@ -327,6 +327,14 @@ INSERT INTO t2 SET a=1; INSERT INTO t2 SET b=1; UPDATE t1, t2 SET t1.a=10, t2.a=20; DROP TABLE t1,t2; +# +# MDEV-20591 Wrong Number of rows in mysqlbinlog output. +# +CREATE TABLE t1(c_char_utf8 CHAR(10) , +c_varchar_utf8 char(10) , +c_text_utf8 blob ); +INSERT into t1 values("B", "B", REPEAT("#", 2000)); +drop table t1; flush logs; INSERT INTO t1dec102 VALUES (-999.99); INSERT INTO t1dec102 VALUES (0); @@ -5274,6 +5282,47 @@ SET TIMESTAMP=1000000000/*!*/; DROP TABLE `t1`,`t2` /* generated by server */ /*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # CRC32 XXX GTID 0-1-317 ddl +/*!100001 SET @@session.gtid_seq_no=317*//*!*/; +# at # +#010909 4:46:40 server id 1 end_log_pos # CRC32 XXX Query thread_id=# exec_time=# error_code=0 +SET TIMESTAMP=1000000000/*!*/; +CREATE TABLE t1(c_char_utf8 CHAR(10) , +c_varchar_utf8 char(10) , +c_text_utf8 blob ) +/*!*/; +# at # +#010909 4:46:40 server id 1 end_log_pos # CRC32 XXX GTID 0-1-318 +/*!100001 SET @@session.gtid_seq_no=318*//*!*/; +BEGIN +/*!*/; +# at # +# at # +#010909 4:46:40 server id 1 end_log_pos # CRC32 XXX Annotate_rows: +#Q> INSERT into t1 values("B", "B", REPEAT("#", 2000)) +#010909 4:46:40 server id 1 end_log_pos # CRC32 XXX Table_map: `test`.`t1` mapped to number # +# at # +#010909 4:46:40 server id 1 end_log_pos # CRC32 XXX Write_rows: table id # flags: STMT_END_F +### INSERT INTO `test`.`t1` +### SET +### @1='B' /* STRING(10) meta=65034 nullable=1 is_null=0 */ +### @2='B' /* STRING(10) meta=65034 nullable=1 is_null=0 */ +### @3='################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */ +# Number of rows: 1 +# at # +#010909 4:46:40 server id 1 end_log_pos # CRC32 XXX Query thread_id=# exec_time=# error_code=0 +SET TIMESTAMP=1000000000/*!*/; +COMMIT +/*!*/; +# at # +#010909 4:46:40 server id 1 end_log_pos # CRC32 XXX GTID 0-1-319 ddl +/*!100001 SET @@session.gtid_seq_no=319*//*!*/; +# at # +#010909 4:46:40 server id 1 end_log_pos # CRC32 XXX Query thread_id=# exec_time=# error_code=0 +SET TIMESTAMP=1000000000/*!*/; +DROP TABLE `t1` /* generated by server */ +/*!*/; +# at # #010909 4:46:40 server id 1 end_log_pos # CRC32 XXX Rotate to master-bin.000002 pos: 4 DELIMITER ; # End of log file @@ -5287,17 +5336,17 @@ DELIMITER /*!*/; # at # #010909 4:46:40 server id 1 end_log_pos # CRC32 XXX Start: binlog v 4, server v #.##.## created 010909 4:46:40 # at # -#010909 4:46:40 server id 1 end_log_pos # CRC32 XXX Gtid list [0-1-316] +#010909 4:46:40 server id 1 end_log_pos # CRC32 XXX Gtid list [0-1-319] # at # #010909 4:46:40 server id 1 end_log_pos # CRC32 XXX Binlog checkpoint master-bin.000001 # at # #010909 4:46:40 server id 1 end_log_pos # CRC32 XXX Binlog checkpoint master-bin.000002 # at # -#010909 4:46:40 server id 1 end_log_pos # CRC32 XXX GTID 0-1-317 +#010909 4:46:40 server id 1 end_log_pos # CRC32 XXX GTID 0-1-320 /*!100101 SET @@session.skip_parallel_replication=0*//*!*/; /*!100001 SET @@session.gtid_domain_id=0*//*!*/; /*!100001 SET @@session.server_id=1*//*!*/; -/*!100001 SET @@session.gtid_seq_no=317*//*!*/; +/*!100001 SET @@session.gtid_seq_no=320*//*!*/; BEGIN /*!*/; # at # diff --git a/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row.test b/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row.test index 3146330f0cd..add977bf07a 100644 --- a/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row.test +++ b/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row.test @@ -438,6 +438,18 @@ INSERT INTO t2 SET b=1; UPDATE t1, t2 SET t1.a=10, t2.a=20; DROP TABLE t1,t2; +--echo # +--echo # MDEV-20591 Wrong Number of rows in mysqlbinlog output. +--echo # + +CREATE TABLE t1(c_char_utf8 CHAR(10) , + c_varchar_utf8 char(10) , + c_text_utf8 blob ); + +INSERT into t1 values("B", "B", REPEAT("#", 2000)); + +drop table t1; + flush logs; let $MYSQLD_DATADIR= `select @@datadir`; @@ -462,3 +474,4 @@ flush logs; --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /((a)[0-9]*=[0-9]*[.][0-9]{1,3})[0-9e+-]*[^ ]*(.*(FLOAT|DOUBLE).*[*].)/\1...\2/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ --error 1 --exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000002 2>&1 + diff --git a/sql/log_event.cc b/sql/log_event.cc index ce4d57329c7..369e9229d03 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -3423,7 +3423,16 @@ static size_t calc_field_event_length(const uchar *ptr, uint type, uint meta) case MYSQL_TYPE_SET: return meta & 0xFF; case MYSQL_TYPE_BLOB: - return (meta <= 4 ? meta : 0); + if (meta > 4 ) + return 0; + if (meta == 1) + return *ptr + 1; + if (meta == 2) + return uint2korr(ptr) + 2; + if (meta == 3) + return uint3korr(ptr) + 3; + if (meta == 4) + return uint4korr(ptr) + 4; case MYSQL_TYPE_VARCHAR: case MYSQL_TYPE_VAR_STRING: length= meta; -- Regards Sachin Setiya Software Engineer at MariaDB

1 0

[Commits] ae2b88f: Adjusted test results after the change of a test case
by IgorBabaev 14 Sep '19

14 Sep '19

revision-id: ae2b88ff3f94253921fed5c48422adeebe7e623d (mariadb-10.1.41-38-gae2b88f) parent(s): 0954bcb6639af47a8b57eb426aee9bba4036e5f1 author: Igor Babaev committer: Igor Babaev timestamp: 2019-09-13 21:10:52 -0700 message: Adjusted test results after the change of a test case --- mysql-test/r/selectivity.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index d8b2d46..d0bbb46 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -1716,7 +1716,7 @@ b INT NOT NULL, c char(100), KEY (b, c), KEY (b, a, c) -) +) ENGINE=MyISAM DEFAULT CHARSET = utf8; INSERT INTO t1 VALUES (1, 1, 1),

1 0

[Commits] 0954bcb: Post fix after the patch for MDEV-20576.
by IgorBabaev 13 Sep '19

13 Sep '19

revision-id: 0954bcb6639af47a8b57eb426aee9bba4036e5f1 (mariadb-10.1.41-37-g0954bcb) parent(s): deb9121fdf2152752346c767321e6e01aa5d6c69 author: Igor Babaev committer: Igor Babaev timestamp: 2019-09-13 15:09:28 -0700 message: Post fix after the patch for MDEV-20576. Adjusted test results. --- mysql-test/r/selectivity_innodb.result | 20 ++++++++++---------- mysql-test/t/selectivity.test | 2 +- storage/tokudb/mysql-test/tokudb/r/mvcc-29.result | 4 ++-- storage/tokudb/mysql-test/tokudb/r/mvcc-30.result | 4 ++-- storage/tokudb/mysql-test/tokudb/r/mvcc-31.result | 4 ++-- 5 files changed, 17 insertions(+), 17 deletions(-) diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index 56a7900..719156a 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -1726,7 +1726,7 @@ b INT NOT NULL, c char(100), KEY (b, c), KEY (b, a, c) -) +) ENGINE=MyISAM DEFAULT CHARSET = utf8; INSERT INTO t1 VALUES (1, 1, 1), @@ -1749,18 +1749,18 @@ INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; EXPLAIN SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref b,b_2 b_2 4 const 207 Using where; Using index; Using filesort +1 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; a 2071 -81 -71 -61 -51 -41 -31 -21 -11 +2061 +2051 +2041 +2031 +2021 +2011 +2001 +1991 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1; # End of 10.1 tests diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index 6e93e60..0deacc3 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -1174,7 +1174,7 @@ CREATE TABLE t1 ( c char(100), KEY (b, c), KEY (b, a, c) -) +) ENGINE=MyISAM DEFAULT CHARSET = utf8; INSERT INTO t1 VALUES diff --git a/storage/tokudb/mysql-test/tokudb/r/mvcc-29.result b/storage/tokudb/mysql-test/tokudb/r/mvcc-29.result index 5c02bab..b532eab 100644 --- a/storage/tokudb/mysql-test/tokudb/r/mvcc-29.result +++ b/storage/tokudb/mysql-test/tokudb/r/mvcc-29.result @@ -26,7 +26,7 @@ delete from foo where a > 5; # number of rows should be 9 explain select * from foo where a > 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE foo range PRIMARY PRIMARY 4 NULL 9 Using where +1 SIMPLE foo range PRIMARY PRIMARY 4 NULL 5 Using where # should have just 4 values select * from foo where a > 1; a b @@ -37,7 +37,7 @@ a b # number of rows should be 9 explain select * from foo where a > 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE foo range PRIMARY PRIMARY 4 NULL 9 Using where +1 SIMPLE foo range PRIMARY PRIMARY 4 NULL 5 Using where # 9 values select * From foo where a > 1; a b diff --git a/storage/tokudb/mysql-test/tokudb/r/mvcc-30.result b/storage/tokudb/mysql-test/tokudb/r/mvcc-30.result index c57787f..f293fe9 100644 --- a/storage/tokudb/mysql-test/tokudb/r/mvcc-30.result +++ b/storage/tokudb/mysql-test/tokudb/r/mvcc-30.result @@ -26,7 +26,7 @@ delete from foo where a < 10; # number of rows should be 9 explain select * from foo where a < 50; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE foo range PRIMARY PRIMARY 4 NULL 9 Using where +1 SIMPLE foo range PRIMARY PRIMARY 4 NULL 5 Using where # should have just 4 values select * from foo where a < 50; a b @@ -37,7 +37,7 @@ a b # number of rows should be 9 explain select * from foo where a < 50; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE foo range PRIMARY PRIMARY 4 NULL 9 Using where +1 SIMPLE foo range PRIMARY PRIMARY 4 NULL 5 Using where # 9 values select * From foo where a < 50; a b diff --git a/storage/tokudb/mysql-test/tokudb/r/mvcc-31.result b/storage/tokudb/mysql-test/tokudb/r/mvcc-31.result index ebc5ae49..cb55f67 100644 --- a/storage/tokudb/mysql-test/tokudb/r/mvcc-31.result +++ b/storage/tokudb/mysql-test/tokudb/r/mvcc-31.result @@ -26,7 +26,7 @@ delete from foo where a = 2 or a = 4 or a = 10 or a = 30 or a = 50; # number of rows should be 8 explain select * from foo where a > 1 and a < 50; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE foo range PRIMARY PRIMARY 4 NULL 8 Using where +1 SIMPLE foo range PRIMARY PRIMARY 4 NULL 5 Using where # should have just 4 values select * from foo where a > 1 and a < 50; a b @@ -37,7 +37,7 @@ a b # number of rows should be 8 explain select * from foo where a > 1 and a < 50; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE foo range PRIMARY PRIMARY 4 NULL 8 Using where +1 SIMPLE foo range PRIMARY PRIMARY 4 NULL 5 Using where # 8 values select * from foo where a > 1 and a < 50; a b

1 0

[Commits] 51d01dc: MDEV-20576 A new assertion added to check validity of calculated
by IgorBabaev 13 Sep '19

13 Sep '19

revision-id: 51d01dc578f3d3ab0ccf0dda72bd500c80484766 (mariadb-10.1.41-35-g51d01dc) parent(s): 031c695b8c865e5eb6c4c09ced404ae08f98430f author: Igor Babaev committer: Igor Babaev timestamp: 2019-09-12 23:00:49 -0700 message: MDEV-20576 A new assertion added to check validity of calculated selectivity values fails After having set the assertion that checks validity of selectivity values returned by the function table_cond_selectivity() a test case from order_by.tesst failed. The failure occurred because range optimizer could return as an estimate of the cardinality of the ranges built for an index a number exceeding the total number of records in the table. The second bug is more subtle. It may happen when there are several indexes with same prefix defined on the first joined table t accessed by a constant ref access. In this case the range optimizer estimates the number of accessed records of t for each usable index and these estimates can be different. Only the first of these estimates is taken into account when the selectivity of the ref access is calculated. However the optimizer later can choose a different index that provides a different estimate. The function table_condition_selectivity() could use this estimate to discount the selectivity of the ref access. This could lead to an selectivity value returned by this function that was greater that 1. --- mysql-test/r/innodb_icp.result | 4 +- mysql-test/r/range_vs_index_merge.result | 2 +- mysql-test/r/range_vs_index_merge_innodb.result | 2 +- mysql-test/r/selectivity.result | 85 +++++++++++++++++++++++++ mysql-test/r/selectivity_innodb.result | 85 +++++++++++++++++++++++++ mysql-test/t/selectivity.test | 81 +++++++++++++++++++++++ sql/opt_range.cc | 10 +++ sql/sql_select.cc | 15 +++++ 8 files changed, 280 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result index a5215bf..0c95f31 100644 --- a/mysql-test/r/innodb_icp.result +++ b/mysql-test/r/innodb_icp.result @@ -679,7 +679,7 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; @@ -690,7 +690,7 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result index bc46a4f..4f3c36b 100644 --- a/mysql-test/r/range_vs_index_merge.result +++ b/mysql-test/r/range_vs_index_merge.result @@ -1795,7 +1795,7 @@ SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range state,capital state 71 NULL 12 Using index condition; Using where +1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result index a6ec200..08b7df6 100644 --- a/mysql-test/r/range_vs_index_merge_innodb.result +++ b/mysql-test/r/range_vs_index_merge_innodb.result @@ -1796,7 +1796,7 @@ SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range state,capital state 71 NULL 10 Using index condition; Using where +1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 3f5db42..d8b2d46 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -1668,4 +1668,89 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` drop table t1; set use_stat_tables= @save_use_stat_tables; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# +# MDEV-20576: failing assertion DBUG_ASSERT(0.0 < sel && sel <= 1) +# +set @@optimizer_use_condition_selectivity=2; +set names utf8; +CREATE DATABASE world; +use world; +CREATE TABLE Country ( +Code char(3) NOT NULL default '', +Name char(52) NOT NULL default '', +SurfaceArea float(10,2) NOT NULL default '0.00', +Population int(11) NOT NULL default '0', +Capital int(11) default NULL, +PRIMARY KEY (Code), +UNIQUE INDEX (Name) +); +CREATE TABLE City ( +ID int(11) NOT NULL auto_increment, +Name char(35) NOT NULL default '', +Country char(3) NOT NULL default '', +Population int(11) NOT NULL default '0', +PRIMARY KEY (ID), +INDEX (Population), +INDEX (Country) +); +CREATE TABLE CountryLanguage ( +Country char(3) NOT NULL default '', +Language char(30) NOT NULL default '', +Percentage float(3,1) NOT NULL default '0.0', +PRIMARY KEY (Country, Language), +INDEX (Percentage) +); +CREATE INDEX Name ON City(Name); +CREATE INDEX CountryPopulation ON City(Country,Population); +CREATE INDEX CountryName ON City(Country,Name); +set @@optimizer_use_condition_selectivity=2; +EXPLAIN +SELECT * FROM City WHERE Country='FIN'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE City ref Country,CountryPopulation,CountryName CountryName 3 const 5 Using index condition +DROP DATABASE world; +use test; +CREATE TABLE t1 ( +a INT, +b INT NOT NULL, +c char(100), +KEY (b, c), +KEY (b, a, c) +) +DEFAULT CHARSET = utf8; +INSERT INTO t1 VALUES +(1, 1, 1), +(2, 2, 2), +(3, 3, 3), +(4, 4, 4), +(5, 5, 5), +(6, 6, 6), +(7, 7, 7), +(8, 8, 8), +(9, 9, 9); +INSERT INTO t1 SELECT a + 10, b, c FROM t1; +INSERT INTO t1 SELECT a + 20, b, c FROM t1; +INSERT INTO t1 SELECT a + 40, b, c FROM t1; +INSERT INTO t1 SELECT a + 80, b, c FROM t1; +INSERT INTO t1 SELECT a + 160, b, c FROM t1; +INSERT INTO t1 SELECT a + 320, b, c FROM t1; +INSERT INTO t1 SELECT a + 640, b, c FROM t1; +INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; +EXPLAIN +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; +a +2071 +2061 +2051 +2041 +2031 +2021 +2011 +2001 +1991 +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +DROP TABLE t1; # End of 10.1 tests diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index 1d73c2f..56a7900 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -1678,6 +1678,91 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` drop table t1; set use_stat_tables= @save_use_stat_tables; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# +# MDEV-20576: failing assertion DBUG_ASSERT(0.0 < sel && sel <= 1) +# +set @@optimizer_use_condition_selectivity=2; +set names utf8; +CREATE DATABASE world; +use world; +CREATE TABLE Country ( +Code char(3) NOT NULL default '', +Name char(52) NOT NULL default '', +SurfaceArea float(10,2) NOT NULL default '0.00', +Population int(11) NOT NULL default '0', +Capital int(11) default NULL, +PRIMARY KEY (Code), +UNIQUE INDEX (Name) +); +CREATE TABLE City ( +ID int(11) NOT NULL auto_increment, +Name char(35) NOT NULL default '', +Country char(3) NOT NULL default '', +Population int(11) NOT NULL default '0', +PRIMARY KEY (ID), +INDEX (Population), +INDEX (Country) +); +CREATE TABLE CountryLanguage ( +Country char(3) NOT NULL default '', +Language char(30) NOT NULL default '', +Percentage float(3,1) NOT NULL default '0.0', +PRIMARY KEY (Country, Language), +INDEX (Percentage) +); +CREATE INDEX Name ON City(Name); +CREATE INDEX CountryPopulation ON City(Country,Population); +CREATE INDEX CountryName ON City(Country,Name); +set @@optimizer_use_condition_selectivity=2; +EXPLAIN +SELECT * FROM City WHERE Country='FIN'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 7 Using index condition +DROP DATABASE world; +use test; +CREATE TABLE t1 ( +a INT, +b INT NOT NULL, +c char(100), +KEY (b, c), +KEY (b, a, c) +) +DEFAULT CHARSET = utf8; +INSERT INTO t1 VALUES +(1, 1, 1), +(2, 2, 2), +(3, 3, 3), +(4, 4, 4), +(5, 5, 5), +(6, 6, 6), +(7, 7, 7), +(8, 8, 8), +(9, 9, 9); +INSERT INTO t1 SELECT a + 10, b, c FROM t1; +INSERT INTO t1 SELECT a + 20, b, c FROM t1; +INSERT INTO t1 SELECT a + 40, b, c FROM t1; +INSERT INTO t1 SELECT a + 80, b, c FROM t1; +INSERT INTO t1 SELECT a + 160, b, c FROM t1; +INSERT INTO t1 SELECT a + 320, b, c FROM t1; +INSERT INTO t1 SELECT a + 640, b, c FROM t1; +INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; +EXPLAIN +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref b,b_2 b_2 4 const 207 Using where; Using index; Using filesort +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; +a +2071 +81 +71 +61 +51 +41 +31 +21 +11 +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +DROP TABLE t1; # End of 10.1 tests set optimizer_switch=@save_optimizer_switch_for_selectivity_test; set @tmp_ust= @@use_stat_tables; diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index f1c9d6b..6e93e60 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -1124,5 +1124,86 @@ drop table t1; set use_stat_tables= @save_use_stat_tables; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +--echo # +--echo # MDEV-20576: failing assertion DBUG_ASSERT(0.0 < sel && sel <= 1) +--echo # + +set @@optimizer_use_condition_selectivity=2; + +set names utf8; + +CREATE DATABASE world; + +use world; + +--source include/world_schema.inc + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/world.inc +--enable_warnings +--enable_result_log +--enable_query_log + +CREATE INDEX Name ON City(Name); +CREATE INDEX CountryPopulation ON City(Country,Population); +CREATE INDEX CountryName ON City(Country,Name); + +--disable_query_log +--disable_result_log +--disable_warnings +ANALYZE TABLE City; +--enable_warnings +--enable_result_log +--enable_query_log + +set @@optimizer_use_condition_selectivity=2; + +EXPLAIN +SELECT * FROM City WHERE Country='FIN'; + +DROP DATABASE world; + +use test; + +CREATE TABLE t1 ( + a INT, + b INT NOT NULL, + c char(100), + KEY (b, c), + KEY (b, a, c) +) +DEFAULT CHARSET = utf8; + +INSERT INTO t1 VALUES +(1, 1, 1), +(2, 2, 2), +(3, 3, 3), +(4, 4, 4), +(5, 5, 5), +(6, 6, 6), +(7, 7, 7), +(8, 8, 8), +(9, 9, 9); + +INSERT INTO t1 SELECT a + 10, b, c FROM t1; +INSERT INTO t1 SELECT a + 20, b, c FROM t1; +INSERT INTO t1 SELECT a + 40, b, c FROM t1; +INSERT INTO t1 SELECT a + 80, b, c FROM t1; +INSERT INTO t1 SELECT a + 160, b, c FROM t1; +INSERT INTO t1 SELECT a + 320, b, c FROM t1; +INSERT INTO t1 SELECT a + 640, b, c FROM t1; +INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; + +EXPLAIN +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; + +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +DROP TABLE t1; + --echo # End of 10.1 tests diff --git a/sql/opt_range.cc b/sql/opt_range.cc index e8421ad..45dad88 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -10179,6 +10179,16 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, bufsize, mrr_flags, cost); if (rows != HA_POS_ERROR) { + ha_rows table_records= param->table->stat_records(); + if (rows > table_records) + { + /* + For any index the total number of records within all ranges + cannot be be bigger than the number of records in the table + */ + rows= table_records; + set_if_bigger(rows, 1); + } param->quick_rows[keynr]= rows; param->possible_keys.set_bit(keynr); if (update_tbl_stats) diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5b96c15..c6e70c2 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7643,7 +7643,19 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, } keyparts++; } + /* + Here we discount selectivity of the constant range CR. To calculate + this selectivity we use elements from the quick_rows[] array. + If we have indexes i1,...,ik with the same prefix compatible + with CR any of the estimate quick_rows[i1], ... quick_rows[ik] could + be used for this calculation but here we don't know which one was + actually used. So sel could be greater than 1 and we have to cap it. + However if sel becomes greater than 2 then with high probability + something went wrong. + */ sel /= (double)table->quick_rows[key] / (double) table->stat_records(); + DBUG_ASSERT(0 < sel && sel <= 2.0); + set_if_smaller(sel, 1.0); used_range_selectivity= true; } } @@ -7691,6 +7703,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, if (table->field[fldno]->cond_selectivity > 0) { sel /= table->field[fldno]->cond_selectivity; + DBUG_ASSERT(0 < sel && sel <= 2.0); set_if_smaller(sel, 1.0); } /* @@ -7748,6 +7761,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, if (field->cond_selectivity > 0) { sel/= field->cond_selectivity; + DBUG_ASSERT(0 < sel && sel <= 2.0); set_if_smaller(sel, 1.0); } break; @@ -7759,6 +7773,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, sel*= table_multi_eq_cond_selectivity(join, idx, s, rem_tables, keyparts, ref_keyuse_steps); + DBUG_ASSERT(0.0 < sel && sel <= 1.0); return sel; }

1 0

[Commits] c11e269: Fixed a typo in the patch for mdev-15777
by IgorBabaev 13 Sep '19

13 Sep '19

revision-id: c11e26946f1742f8fdb71aab7c6887983dfdaf3a (mariadb-10.4.4-325-gc11e269) parent(s): 61bbf39915476ba7f9abe5cb6f2ddd4f704b9fbd author: Igor Babaev committer: Igor Babaev timestamp: 2019-09-12 21:30:02 -0700 message: Fixed a typo in the patch for mdev-15777 --- sql/item_cmpfunc.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 71bf5f4..907480a 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -2096,7 +2096,7 @@ bool Item_func_between::find_not_null_fields(table_map allowed) return false; return args[0]->find_not_null_fields(allowed) || args[1]->find_not_null_fields(allowed) || - args[3]->find_not_null_fields(allowed); + args[2]->find_not_null_fields(allowed); }

1 0

[Commits] 1966b71bfb2: MDEV-20371: Invalid reads at plan refinement stage: join->positions...
by psergey 12 Sep '19

12 Sep '19

revision-id: 1966b71bfb250d551d53daf7f6606337900bbb0f (mariadb-10.4.7-64-g1966b71bfb2) parent(s): 5c5452a5a086a9584efb2255059da671fff6e484 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-09-12 19:29:07 +0300 message: MDEV-20371: Invalid reads at plan refinement stage: join->positions... (re-committing in 10.4) best_access_path() is called from two optimization phases: 1. Plan choice phase, in choose_plan(). Here, the join prefix being considered is in join->positions[] 2. Plan refinement stage, in fix_semijoin_strategies_for_picked_join_order Here, the join prefix is in join->best_positions[] It used to access join->positions[] from stage #2. This didnt cause any valgrind or asan failures (as join->positions[] has been written-to before) but the effect was similar to that of reading the random data: The join prefix we've picked (in join->best_positions) could have nothing in common with the join prefix that was last to be considered (in join->positions). --- sql/opt_subselect.cc | 26 +++++++++++++++++--------- sql/opt_subselect.h | 8 ++++++-- sql/sql_select.cc | 37 ++++++++++++++++++++++--------------- sql/sql_select.h | 9 ++++++++- 4 files changed, 53 insertions(+), 27 deletions(-) diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index c4cb9b81170..22367ef85e4 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -453,10 +453,6 @@ bool find_eq_ref_candidate(TABLE *table, table_map sj_inner_tables); static SJ_MATERIALIZATION_INFO * at_sjmat_pos(const JOIN *join, table_map remaining_tables, const JOIN_TAB *tab, uint idx, bool *loose_scan); -void best_access_path(JOIN *join, JOIN_TAB *s, - table_map remaining_tables, uint idx, - bool disable_jbuf, double record_count, - POSITION *pos, POSITION *loose_scan_pos); static Item *create_subq_in_equalities(THD *thd, SJ_MATERIALIZATION_INFO *sjm, Item_in_subselect *subq_pred); @@ -2804,6 +2800,14 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx, &pos->dups_weedout_picker, NULL, }; + +#ifdef HAVE_valgrind + new (&pos->firstmatch_picker) Firstmatch_picker; + new (&pos->loosescan_picker) LooseScan_picker; + new (&pos->sjmat_picker) Sj_materialization_picker; + new (&pos->dups_weedout_picker) Duplicate_weedout_picker; +#endif + Json_writer_array trace_steps(join->thd, "semijoin_strategy_choice"); /* Update join->cur_sj_inner_tables (Used by FirstMatch in this function and @@ -3121,7 +3125,8 @@ bool Sj_materialization_picker::check_qep(JOIN *join, Json_writer_temp_disable trace_semijoin_mat_scan(thd); for (i= first_tab + mat_info->tables; i <= idx; i++) { - best_access_path(join, join->positions[i].table, rem_tables, i, + best_access_path(join, join->positions[i].table, rem_tables, + join->positions, i, disable_jbuf, prefix_rec_count, &curpos, &dummy); prefix_rec_count= COST_MULT(prefix_rec_count, curpos.records_read); prefix_cost= COST_ADD(prefix_cost, curpos.read_time); @@ -3790,7 +3795,8 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) Json_writer_object trace_one_table(thd); trace_one_table.add_table_name(join->best_positions[i].table); } - best_access_path(join, join->best_positions[i].table, rem_tables, i, + best_access_path(join, join->best_positions[i].table, rem_tables, + join->best_positions, i, FALSE, prefix_rec_count, join->best_positions + i, &dummy); prefix_rec_count *= join->best_positions[i].records_read; @@ -3830,8 +3836,9 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) } if (join->best_positions[idx].use_join_buffer) { - best_access_path(join, join->best_positions[idx].table, - rem_tables, idx, TRUE /* no jbuf */, + best_access_path(join, join->best_positions[idx].table, + rem_tables, join->best_positions, idx, + TRUE /* no jbuf */, record_count, join->best_positions + idx, &dummy); } record_count *= join->best_positions[idx].records_read; @@ -3869,7 +3876,8 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) if (join->best_positions[idx].use_join_buffer || (idx == first)) { best_access_path(join, join->best_positions[idx].table, - rem_tables, idx, TRUE /* no jbuf */, + rem_tables, join->best_positions, idx, + TRUE /* no jbuf */, record_count, join->best_positions + idx, &loose_scan_pos); if (idx==first) diff --git a/sql/opt_subselect.h b/sql/opt_subselect.h index 6210fc972c8..d1dd8e3e135 100644 --- a/sql/opt_subselect.h +++ b/sql/opt_subselect.h @@ -91,6 +91,7 @@ class Loose_scan_opt KEYUSE *best_loose_scan_start_key; uint best_max_loose_keypart; + table_map best_ref_depend_map; public: Loose_scan_opt(): @@ -252,13 +253,14 @@ class Loose_scan_opt best_loose_scan_records= records; best_max_loose_keypart= max_loose_keypart; best_loose_scan_start_key= start_key; + best_ref_depend_map= 0; } } } } void check_ref_access_part2(uint key, KEYUSE *start_key, double records, - double read_time) + double read_time, table_map ref_depend_map_arg) { if (part1_conds_met && read_time < best_loose_scan_cost) { @@ -268,6 +270,7 @@ class Loose_scan_opt best_loose_scan_records= records; best_max_loose_keypart= max_loose_keypart; best_loose_scan_start_key= start_key; + best_ref_depend_map= ref_depend_map_arg; } } @@ -283,6 +286,7 @@ class Loose_scan_opt best_loose_scan_records= rows2double(quick->records); best_max_loose_keypart= quick_max_loose_keypart; best_loose_scan_start_key= NULL; + best_ref_depend_map= 0; } } @@ -299,7 +303,7 @@ class Loose_scan_opt pos->use_join_buffer= FALSE; pos->table= tab; pos->range_rowid_filter_info= tab->range_rowid_filter_info; - // todo need ref_depend_map ? + pos->ref_depend_map= best_ref_depend_map; DBUG_PRINT("info", ("Produced a LooseScan plan, key %s, %s", tab->table->key_info[best_loose_scan_key].name.str, best_loose_scan_start_key? "(ref access)": diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 3dae58a78e2..641a398cbc6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -107,10 +107,6 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, static ha_rows get_quick_record_count(THD *thd, SQL_SELECT *select, TABLE *table, const key_map *keys,ha_rows limit); -void best_access_path(JOIN *join, JOIN_TAB *s, - table_map remaining_tables, uint idx, - bool disable_jbuf, double record_count, - POSITION *pos, POSITION *loose_scan_pos); static void optimize_straight_join(JOIN *join, table_map join_tables); static bool greedy_search(JOIN *join, table_map remaining_tables, uint depth, uint prune_level, @@ -5479,6 +5475,13 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, { if (choose_plan(join, all_table_map & ~join->const_table_map)) goto error; + +#ifdef HAVE_valgrind + // JOIN::positions holds the current query plan. We've already + // made the plan choice, so we should only use JOIN::best_positions + for (uint k=join->const_tables; k < join->table_count; k++) + MEM_UNDEFINED(&join->positions[k], sizeof(join->positions[k])); +#endif } else { @@ -7178,6 +7181,7 @@ void best_access_path(JOIN *join, JOIN_TAB *s, table_map remaining_tables, + const POSITION *join_positions, uint idx, bool disable_jbuf, double record_count, @@ -7298,7 +7302,7 @@ best_access_path(JOIN *join, if (!keyuse->val->maybe_null || keyuse->null_rejecting) notnull_part|=keyuse->keypart_map; - double tmp2= prev_record_reads(join->positions, idx, + double tmp2= prev_record_reads(join_positions, idx, (found_ref | keyuse->used_tables)); if (tmp2 < best_prev_record_reads) { @@ -7340,7 +7344,7 @@ best_access_path(JOIN *join, Really, there should be records=0.0 (yes!) but 1.0 would be probably safer */ - tmp= prev_record_reads(join->positions, idx, found_ref); + tmp= prev_record_reads(join_positions, idx, found_ref); records= 1.0; type= JT_FT; trace_access_idx.add("access_type", join_type_str[type]) @@ -7369,7 +7373,7 @@ best_access_path(JOIN *join, type= JT_EQ_REF; trace_access_idx.add("access_type", join_type_str[type]) .add("index", keyinfo->name); - tmp = prev_record_reads(join->positions, idx, found_ref); + tmp = prev_record_reads(join_positions, idx, found_ref); records=1.0; } else @@ -7657,7 +7661,8 @@ best_access_path(JOIN *join, } tmp= COST_ADD(tmp, s->startup_cost); - loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp); + loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp, + found_ref); } /* not ft_key */ if (records < DBL_MAX) @@ -8447,7 +8452,8 @@ optimize_straight_join(JOIN *join, table_map join_tables) trace_one_table.add_table_name(s); } /* Find the best access method from 's' to the current partial plan */ - best_access_path(join, s, join_tables, idx, disable_jbuf, record_count, + best_access_path(join, s, join_tables, join->positions, idx, + disable_jbuf, record_count, position, &loose_scan_pos); /* compute the cost of the new plan extended with 's' */ @@ -9376,8 +9382,8 @@ best_extension_by_limited_search(JOIN *join, /* Find the best access method from 's' to the current partial plan */ POSITION loose_scan_pos; - best_access_path(join, s, remaining_tables, idx, disable_jbuf, - record_count, position, &loose_scan_pos); + best_access_path(join, s, remaining_tables, join->positions, idx, + disable_jbuf, record_count, position, &loose_scan_pos); /* Compute the cost of extending the plan with 's' */ current_record_count= COST_MULT(record_count, position->records_read); @@ -9781,11 +9787,11 @@ cache_record_length(JOIN *join,uint idx) */ double -prev_record_reads(POSITION *positions, uint idx, table_map found_ref) +prev_record_reads(const POSITION *positions, uint idx, table_map found_ref) { double found=1.0; - POSITION *pos_end= positions - 1; - for (POSITION *pos= positions + idx - 1; pos != pos_end; pos--) + const POSITION *pos_end= positions - 1; + for (const POSITION *pos= positions + idx - 1; pos != pos_end; pos--) { if (pos->table->table->map & found_ref) { @@ -16675,7 +16681,8 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab, if ((i == first_tab && first_alt) || join->positions[i].use_join_buffer) { /* Find the best access method that would not use join buffering */ - best_access_path(join, rs, reopt_remaining_tables, i, + best_access_path(join, rs, reopt_remaining_tables, + join->positions, i, TRUE, rec_count, &pos, &loose_scan_pos); } diff --git a/sql/sql_select.h b/sql/sql_select.h index 4ec258f3653..b6359307215 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -854,6 +854,7 @@ class LooseScan_picker : public Semi_join_strategy_picker friend void best_access_path(JOIN *join, JOIN_TAB *s, table_map remaining_tables, + const struct st_position *join_positions, uint idx, bool disable_jbuf, double record_count, @@ -2071,6 +2072,12 @@ class store_key_const_item :public store_key_item } }; +void best_access_path(JOIN *join, JOIN_TAB *s, + table_map remaining_tables, + const POSITION *join_positions, uint idx, + bool disable_jbuf, double record_count, + POSITION *pos, POSITION *loose_scan_pos); + bool cp_buffer_from_ref(THD *thd, TABLE *table, TABLE_REF *ref); bool error_if_full_join(JOIN *join); int report_error(TABLE *table, int error); @@ -2435,7 +2442,7 @@ bool instantiate_tmp_table(TABLE *table, KEY *keyinfo, ulonglong options); bool open_tmp_table(TABLE *table); void setup_tmp_table_column_bitmaps(TABLE *table, uchar *bitmaps); -double prev_record_reads(POSITION *positions, uint idx, table_map found_ref); +double prev_record_reads(const POSITION *positions, uint idx, table_map found_ref); void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist); double get_tmp_table_lookup_cost(THD *thd, double row_count, uint row_size); double get_tmp_table_write_cost(THD *thd, double row_count, uint row_size);

1 0

[Commits] d0b74bbacc5: MDEV-20440: Optimizer trace: print more details about semi-join optimization
by psergey 12 Sep '19

12 Sep '19

revision-id: d0b74bbacc584e4879a29ae4277f6ce4f9145a60 (mariadb-10.4.7-67-gd0b74bbacc5) parent(s): 60c04be6599597548ad07ce11e1d7d4004a7cc9c author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-09-12 19:07:56 +0300 message: MDEV-20440: Optimizer trace: print more details about semi-join optimization Followup patch: fix typos --- mysql-test/main/opt_trace.result | 17 ++++++++++++----- sql/opt_subselect.cc | 8 +++++--- 2 files changed, 17 insertions(+), 8 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 3438714fd28..63a86615186 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -2970,7 +2970,7 @@ explain extended select * from t1 where a in (select pk from t10) { "read_time": 27.129 }, { - "chosen_strategy": "SJ-Materialize" + "chosen_strategy": "SJ-Materialization" } ], "estimated_join_cardinality": 3 @@ -4609,7 +4609,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "read_time": 18.315 }, { - "chosen_strategy": "SJ-Materialize" + "chosen_strategy": "SJ-Materialization" } ], "estimated_join_cardinality": 3 @@ -6309,7 +6309,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "fix_semijoin_strategies_for_picked_join_order": [] + "fix_semijoin_strategies_for_picked_join_order": [ + { + "semi_join_strategy": "DuplicateWeedout" + }, + { + "semi_join_strategy": "DuplicateWeedout" + } + ] }, { "best_join_order": [ @@ -6833,7 +6840,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "read_time": 37.226 }, { - "chosen_strategy": "SJ-Materialize" + "chosen_strategy": "SJ-Materialization" } ], "rest_of_plan": [ @@ -6935,7 +6942,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "read_time": 294.96 }, { - "chosen_strategy": "SJ-Materialize" + "chosen_strategy": "SJ-Materialization" } ], "estimated_join_cardinality": 27 diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index c4cb9b81170..f8284ac8b1a 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -2938,10 +2938,10 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx, const char *sname; switch (pos->sj_strategy) { case SJ_OPT_MATERIALIZE: - sname= "SJ-Materialize"; + sname= "SJ-Materialization"; break; case SJ_OPT_MATERIALIZE_SCAN: - sname= "SJ-Materialize-Scan"; + sname= "SJ-Materialization-Scan"; break; case SJ_OPT_FIRST_MATCH: sname= "FirstMatch"; @@ -3203,7 +3203,7 @@ bool LooseScan_picker::check_qep(JOIN *join, (new_join_tab->table->map & loosescan_need_tables)) { Json_writer_object trace(join->thd); - trace.add("strategy", "SJ-Materialization-Scan"); + trace.add("strategy", "LooseScan"); /* Ok we have LooseScan plan and also have all LooseScan sj-nest's inner tables and outer correlated tables into the prefix. @@ -3899,6 +3899,8 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) if (pos->sj_strategy == SJ_OPT_DUPS_WEEDOUT) { + Json_writer_object semijoin_strategy(thd); + semijoin_strategy.add("semi_join_strategy","DuplicateWeedout"); /* Duplicate Weedout starting at pos->first_dupsweedout_table, ending at this table.

1 0

[Commits] be6beb73e9e: MDEV-16560: [counter] rocksdb.ttl_secondary_read_filtering fail in buildbot
by Sergei Petrunia 11 Sep '19

11 Sep '19

revision-id: be6beb73e9e6adf2ebd69354a2496817f03ae6ff (mariadb-10.2.27-20-gbe6beb73e9e) parent(s): c8dc866fdeee551993ef91fb321135f9106ea00e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-09-11 23:05:12 +0300 message: MDEV-16560: [counter] rocksdb.ttl_secondary_read_filtering fail in buildbot It is not reproducible, but the issue seems to be the same as with MDEV-20490 and rocksdb.ttl_primary_read_filtering - a compaction caused by DROP TABLE gets behind and compacts away the expired rows for the next test. Fix this in the same way. --- .../rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering.result | 1 + storage/rocksdb/mysql-test/rocksdb/t/ttl_secondary_read_filtering.test | 3 +++ 2 files changed, 4 insertions(+) diff --git a/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering.result b/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering.result index 90de5447891..395c84edfe9 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/ttl_secondary_read_filtering.result @@ -101,6 +101,7 @@ a b SELECT * FROM t1 FORCE INDEX (kb); a b DROP TABLE t1; +set global rocksdb_compact_cf= 'default'; # Read filtering index scan tests (None of these queries should return any results) CREATE TABLE t1 ( a int, diff --git a/storage/rocksdb/mysql-test/rocksdb/t/ttl_secondary_read_filtering.test b/storage/rocksdb/mysql-test/rocksdb/t/ttl_secondary_read_filtering.test index d6be7d95f8d..f6042cc517e 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/ttl_secondary_read_filtering.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/ttl_secondary_read_filtering.test @@ -121,6 +121,9 @@ SELECT * FROM t1 FORCE INDEX (kb); DROP TABLE t1; +# Compact away the dropped data +set global rocksdb_compact_cf= 'default'; + --echo # Read filtering index scan tests (None of these queries should return any results) CREATE TABLE t1 ( a int,

1 0

[Commits] d8b28f78535: MDEV-20371: Invalid reads at plan refinement stage: join->positions...
by psergey 11 Sep '19

11 Sep '19

revision-id: d8b28f78535064c6505a8e355a04d2cef90f81f0 (mariadb-10.4.7-7-gd8b28f78535) parent(s): 13f36fffeaecf316435fc497b0f3ae2a5d58d749 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-09-11 20:22:08 +0300 message: MDEV-20371: Invalid reads at plan refinement stage: join->positions... (re-committing in 10.4) best_access_path() is called from two optimization phases: 1. Plan choice phase, in choose_plan(). Here, the join prefix being considered is in join->positions[] 2. Plan refinement stage, in fix_semijoin_strategies_for_picked_join_order Here, the join prefix is in join->best_positions[] It used to access join->positions[] from stage #2. This didnt cause any valgrind or asan failures (as join->positions[] has been written-to before) but the effect was similar to that of reading the random data: The join prefix we've picked (in join->best_positions) could have nothing in common with the join prefix that was last to be considered (in join->positions). --- sql/opt_subselect.cc | 27 +++++++++++++++++---------- sql/opt_subselect.h | 8 ++++++-- sql/sql_select.cc | 37 ++++++++++++++++++++++--------------- sql/sql_select.h | 9 ++++++++- 4 files changed, 53 insertions(+), 28 deletions(-) diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 599642b3a26..d9172d7f13f 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -453,10 +453,6 @@ bool find_eq_ref_candidate(TABLE *table, table_map sj_inner_tables); static SJ_MATERIALIZATION_INFO * at_sjmat_pos(const JOIN *join, table_map remaining_tables, const JOIN_TAB *tab, uint idx, bool *loose_scan); -void best_access_path(JOIN *join, JOIN_TAB *s, - table_map remaining_tables, uint idx, - bool disable_jbuf, double record_count, - POSITION *pos, POSITION *loose_scan_pos); void trace_plan_prefix(JOIN *join, uint idx, table_map remaining_tables); static Item *create_subq_in_equalities(THD *thd, SJ_MATERIALIZATION_INFO *sjm, @@ -2787,6 +2783,13 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx, NULL, }; +#ifdef HAVE_valgrind + new (&pos->firstmatch_picker) Firstmatch_picker; + new (&pos->loosescan_picker) LooseScan_picker; + new (&pos->sjmat_picker) Sj_materialization_picker; + new (&pos->dups_weedout_picker) Duplicate_weedout_picker; +#endif + if (join->emb_sjm_nest) { /* @@ -3078,7 +3081,8 @@ bool Sj_materialization_picker::check_qep(JOIN *join, Json_writer_temp_disable trace_semijoin_mat_scan(thd); for (i= first_tab + mat_info->tables; i <= idx; i++) { - best_access_path(join, join->positions[i].table, rem_tables, i, + best_access_path(join, join->positions[i].table, rem_tables, + join->positions, i, disable_jbuf, prefix_rec_count, &curpos, &dummy); prefix_rec_count= COST_MULT(prefix_rec_count, curpos.records_read); prefix_cost= COST_ADD(prefix_cost, curpos.read_time); @@ -3718,7 +3722,8 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) Json_writer_object trace_one_table(thd); trace_one_table.add_table_name(join->best_positions[i].table); } - best_access_path(join, join->best_positions[i].table, rem_tables, i, + best_access_path(join, join->best_positions[i].table, rem_tables, + join->best_positions, i, FALSE, prefix_rec_count, join->best_positions + i, &dummy); prefix_rec_count *= join->best_positions[i].records_read; @@ -3758,8 +3763,9 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) } if (join->best_positions[idx].use_join_buffer) { - best_access_path(join, join->best_positions[idx].table, - rem_tables, idx, TRUE /* no jbuf */, + best_access_path(join, join->best_positions[idx].table, + rem_tables, join->best_positions, idx, + TRUE /* no jbuf */, record_count, join->best_positions + idx, &dummy); } record_count *= join->best_positions[idx].records_read; @@ -3785,7 +3791,7 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) */ join->cur_sj_inner_tables= 0; Json_writer_object semijoin_strategy(thd); - semijoin_strategy.add("semi_join_strategy","sj_materialize"); + semijoin_strategy.add("semi_join_strategy","loose-scan"); Json_writer_array semijoin_plan(thd, "join_order"); for (idx= first; idx <= tablenr; idx++) { @@ -3797,7 +3803,8 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) if (join->best_positions[idx].use_join_buffer || (idx == first)) { best_access_path(join, join->best_positions[idx].table, - rem_tables, idx, TRUE /* no jbuf */, + rem_tables, join->best_positions, idx, + TRUE /* no jbuf */, record_count, join->best_positions + idx, &loose_scan_pos); if (idx==first) diff --git a/sql/opt_subselect.h b/sql/opt_subselect.h index 65131f6bc89..0799402f146 100644 --- a/sql/opt_subselect.h +++ b/sql/opt_subselect.h @@ -91,6 +91,7 @@ class Loose_scan_opt KEYUSE *best_loose_scan_start_key; uint best_max_loose_keypart; + table_map best_ref_depend_map; public: Loose_scan_opt(): @@ -253,13 +254,14 @@ class Loose_scan_opt best_loose_scan_records= records; best_max_loose_keypart= max_loose_keypart; best_loose_scan_start_key= start_key; + best_ref_depend_map= 0; } } } } void check_ref_access_part2(uint key, KEYUSE *start_key, double records, - double read_time) + double read_time, table_map ref_depend_map_arg) { if (part1_conds_met && read_time < best_loose_scan_cost) { @@ -269,6 +271,7 @@ class Loose_scan_opt best_loose_scan_records= records; best_max_loose_keypart= max_loose_keypart; best_loose_scan_start_key= start_key; + best_ref_depend_map= ref_depend_map_arg; } } @@ -284,6 +287,7 @@ class Loose_scan_opt best_loose_scan_records= rows2double(quick->records); best_max_loose_keypart= quick_max_loose_keypart; best_loose_scan_start_key= NULL; + best_ref_depend_map= 0; } } @@ -300,7 +304,7 @@ class Loose_scan_opt pos->use_join_buffer= FALSE; pos->table= tab; pos->range_rowid_filter_info= tab->range_rowid_filter_info; - // todo need ref_depend_map ? + pos->ref_depend_map= best_ref_depend_map; DBUG_PRINT("info", ("Produced a LooseScan plan, key %s, %s", tab->table->key_info[best_loose_scan_key].name.str, best_loose_scan_start_key? "(ref access)": diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ff07a7aea89..1b09449779e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -107,10 +107,6 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, static ha_rows get_quick_record_count(THD *thd, SQL_SELECT *select, TABLE *table, const key_map *keys,ha_rows limit); -void best_access_path(JOIN *join, JOIN_TAB *s, - table_map remaining_tables, uint idx, - bool disable_jbuf, double record_count, - POSITION *pos, POSITION *loose_scan_pos); static void optimize_straight_join(JOIN *join, table_map join_tables); static bool greedy_search(JOIN *join, table_map remaining_tables, uint depth, uint prune_level, @@ -5481,6 +5477,13 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, { if (choose_plan(join, all_table_map & ~join->const_table_map)) goto error; + +#ifdef HAVE_valgrind + // JOIN::positions holds the current query plan. We've already + // made the plan choice, so we should only use JOIN::best_positions + for (uint k=join->const_tables; k < join->table_count; k++) + MEM_UNDEFINED(&join->positions[k], sizeof(join->positions[k])); +#endif } else { @@ -7180,6 +7183,7 @@ void best_access_path(JOIN *join, JOIN_TAB *s, table_map remaining_tables, + const POSITION *join_positions, uint idx, bool disable_jbuf, double record_count, @@ -7299,7 +7303,7 @@ best_access_path(JOIN *join, if (!keyuse->val->maybe_null || keyuse->null_rejecting) notnull_part|=keyuse->keypart_map; - double tmp2= prev_record_reads(join->positions, idx, + double tmp2= prev_record_reads(join_positions, idx, (found_ref | keyuse->used_tables)); if (tmp2 < best_prev_record_reads) { @@ -7341,7 +7345,7 @@ best_access_path(JOIN *join, Really, there should be records=0.0 (yes!) but 1.0 would be probably safer */ - tmp= prev_record_reads(join->positions, idx, found_ref); + tmp= prev_record_reads(join_positions, idx, found_ref); records= 1.0; trace_access_idx.add("access_type", "fulltext") .add("index", keyinfo->name); @@ -7368,7 +7372,7 @@ best_access_path(JOIN *join, { trace_access_idx.add("access_type", "eq_ref") .add("index", keyinfo->name); - tmp = prev_record_reads(join->positions, idx, found_ref); + tmp = prev_record_reads(join_positions, idx, found_ref); records=1.0; } else @@ -7655,7 +7659,8 @@ best_access_path(JOIN *join, } tmp= COST_ADD(tmp, s->startup_cost); - loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp); + loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp, + found_ref); } /* not ft_key */ if (records < DBL_MAX) @@ -8432,7 +8437,8 @@ optimize_straight_join(JOIN *join, table_map join_tables) trace_one_table.add_table_name(s); } /* Find the best access method from 's' to the current partial plan */ - best_access_path(join, s, join_tables, idx, disable_jbuf, record_count, + best_access_path(join, s, join_tables, join->positions, idx, + disable_jbuf, record_count, position, &loose_scan_pos); /* compute the cost of the new plan extended with 's' */ @@ -9369,8 +9375,8 @@ best_extension_by_limited_search(JOIN *join, /* Find the best access method from 's' to the current partial plan */ POSITION loose_scan_pos; - best_access_path(join, s, remaining_tables, idx, disable_jbuf, - record_count, position, &loose_scan_pos); + best_access_path(join, s, remaining_tables, join->positions, idx, + disable_jbuf, record_count, position, &loose_scan_pos); /* Compute the cost of extending the plan with 's' */ current_record_count= COST_MULT(record_count, position->records_read); @@ -9763,11 +9769,11 @@ cache_record_length(JOIN *join,uint idx) */ double -prev_record_reads(POSITION *positions, uint idx, table_map found_ref) +prev_record_reads(const POSITION *positions, uint idx, table_map found_ref) { double found=1.0; - POSITION *pos_end= positions - 1; - for (POSITION *pos= positions + idx - 1; pos != pos_end; pos--) + const POSITION *pos_end= positions - 1; + for (const POSITION *pos= positions + idx - 1; pos != pos_end; pos--) { if (pos->table->table->map & found_ref) { @@ -16654,7 +16660,8 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab, if ((i == first_tab && first_alt) || join->positions[i].use_join_buffer) { /* Find the best access method that would not use join buffering */ - best_access_path(join, rs, reopt_remaining_tables, i, + best_access_path(join, rs, reopt_remaining_tables, + join->positions, i, TRUE, rec_count, &pos, &loose_scan_pos); } diff --git a/sql/sql_select.h b/sql/sql_select.h index b7f870bf797..416c2d35c07 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -854,6 +854,7 @@ class LooseScan_picker : public Semi_join_strategy_picker friend void best_access_path(JOIN *join, JOIN_TAB *s, table_map remaining_tables, + const struct st_position *join_positions, uint idx, bool disable_jbuf, double record_count, @@ -2071,6 +2072,12 @@ class store_key_const_item :public store_key_item } }; +void best_access_path(JOIN *join, JOIN_TAB *s, + table_map remaining_tables, + const POSITION *join_positions, uint idx, + bool disable_jbuf, double record_count, + POSITION *pos, POSITION *loose_scan_pos); + bool cp_buffer_from_ref(THD *thd, TABLE *table, TABLE_REF *ref); bool error_if_full_join(JOIN *join); int report_error(TABLE *table, int error); @@ -2435,7 +2442,7 @@ bool instantiate_tmp_table(TABLE *table, KEY *keyinfo, ulonglong options); bool open_tmp_table(TABLE *table); void setup_tmp_table_column_bitmaps(TABLE *table, uchar *bitmaps); -double prev_record_reads(POSITION *positions, uint idx, table_map found_ref); +double prev_record_reads(const POSITION *positions, uint idx, table_map found_ref); void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist); double get_tmp_table_lookup_cost(THD *thd, double row_count, uint row_size); double get_tmp_table_write_cost(THD *thd, double row_count, uint row_size);

1 0

[Commits] 7310eb64d8d: MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4
by Varun 10 Sep '19

10 Sep '19

revision-id: 7310eb64d8d3e1431fc14341415cf17682a5cf42 (mariadb-10.1.41-35-g7310eb64d8d) parent(s): 031c695b8c865e5eb6c4c09ced404ae08f98430f author: Varun Gupta committer: Varun Gupta timestamp: 2019-09-11 04:12:42 +0530 message: MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 The issue here was that inside the function table_cond_selectivity we were trying to use the ranges estimate for a key for which no sargable condition was present. The fix for this issue is to make reset the quick structures when we read a TABLE structure from the table cache. This would ensure that we don't read some estimate of previous queries --- mysql-test/r/innodb_icp.result | 4 +- mysql-test/r/range_vs_index_merge.result | 2 +- mysql-test/r/range_vs_index_merge_innodb.result | 2 +- mysql-test/r/selectivity.result | 56 +++++++++++++++++++++++++ mysql-test/r/selectivity_innodb.result | 56 +++++++++++++++++++++++++ mysql-test/t/selectivity.test | 32 ++++++++++++++ sql/opt_range.cc | 11 +++++ sql/sql_select.cc | 2 + sql/table.cc | 20 +++++++++ sql/table.h | 1 + 10 files changed, 182 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result index a5215bf9f0d..0c95f31ae95 100644 --- a/mysql-test/r/innodb_icp.result +++ b/mysql-test/r/innodb_icp.result @@ -679,7 +679,7 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; @@ -690,7 +690,7 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result index bc46a4fdd0b..4f3c36b7660 100644 --- a/mysql-test/r/range_vs_index_merge.result +++ b/mysql-test/r/range_vs_index_merge.result @@ -1795,7 +1795,7 @@ SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range state,capital state 71 NULL 12 Using index condition; Using where +1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result index a6ec200538d..08b7df66c67 100644 --- a/mysql-test/r/range_vs_index_merge_innodb.result +++ b/mysql-test/r/range_vs_index_merge_innodb.result @@ -1796,7 +1796,7 @@ SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range state,capital state 71 NULL 10 Using index condition; Using where +1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 3f5db42d341..db8ff2c53c7 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -1668,4 +1668,60 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` drop table t1; set use_stat_tables= @save_use_stat_tables; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# +# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 +# +create table t1 (id int, a int, PRIMARY KEY(id), key(a)); +insert into t1 select seq,seq from seq_1_to_100; +create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b)); +insert into t2 select seq,seq,seq from seq_1_to_100; +set optimizer_switch='exists_to_in=off'; +set optimizer_use_condition_selectivity=2; +SELECT * FROM t1 +WHERE +EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id +WHERE A.a=t1.a AND t2.b < 20); +id a +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +16 16 +17 17 +18 18 +19 19 +explain SELECT * FROM t1 +WHERE +EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id +WHERE A.a=t1.a AND t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where +2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where +EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1 +1 SIMPLE B ref a a 5 const 1 +explain SELECT * FROM t1 +WHERE +EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id +WHERE A.a=t1.a AND t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where +2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where +drop table t1,t2; +set optimizer_switch= @save_optimizer_switch; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; # End of 10.1 tests diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index 1d73c2f5d50..87c47434b5e 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -1678,6 +1678,62 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` drop table t1; set use_stat_tables= @save_use_stat_tables; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# +# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 +# +create table t1 (id int, a int, PRIMARY KEY(id), key(a)); +insert into t1 select seq,seq from seq_1_to_100; +create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b)); +insert into t2 select seq,seq,seq from seq_1_to_100; +set optimizer_switch='exists_to_in=off'; +set optimizer_use_condition_selectivity=2; +SELECT * FROM t1 +WHERE +EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id +WHERE A.a=t1.a AND t2.b < 20); +id a +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +16 16 +17 17 +18 18 +19 19 +explain SELECT * FROM t1 +WHERE +EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id +WHERE A.a=t1.a AND t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index +2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 Using index +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where +EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1 +1 SIMPLE B ref a a 5 const 1 Using index +explain SELECT * FROM t1 +WHERE +EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id +WHERE A.a=t1.a AND t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index +2 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 Using index +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where +drop table t1,t2; +set optimizer_switch= @save_optimizer_switch; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; # End of 10.1 tests set optimizer_switch=@save_optimizer_switch_for_selectivity_test; set @tmp_ust= @@use_stat_tables; diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index f1c9d6b31b8..6f79927e5d6 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -1124,5 +1124,37 @@ drop table t1; set use_stat_tables= @save_use_stat_tables; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +--echo # +--echo # MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 +--echo # + +--source include/have_sequence.inc + +create table t1 (id int, a int, PRIMARY KEY(id), key(a)); +insert into t1 select seq,seq from seq_1_to_100; + +create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b)); +insert into t2 select seq,seq,seq from seq_1_to_100; + +set optimizer_switch='exists_to_in=off'; +set optimizer_use_condition_selectivity=2; + +let $query= SELECT * FROM t1 + WHERE + EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id + WHERE A.a=t1.a AND t2.b < 20); + +eval $query; +eval explain $query; + +EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; + +eval explain $query; + +drop table t1,t2; +set optimizer_switch= @save_optimizer_switch; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; + --echo # End of 10.1 tests diff --git a/sql/opt_range.cc b/sql/opt_range.cc index e8421ad052a..118e063e487 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -10131,6 +10131,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, RANGE_SEQ_IF seq_if = {NULL, sel_arg_range_seq_init, sel_arg_range_seq_next, 0, 0}; handler *file= param->table->file; ha_rows rows= HA_POS_ERROR; + ha_rows table_records= param->table->stat_records(); uint keynr= param->real_keynr[idx]; DBUG_ENTER("check_quick_select"); @@ -10179,6 +10180,16 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, bufsize, mrr_flags, cost); if (rows != HA_POS_ERROR) { + /* + Capping the estimates of range access to atmost total records of table. + Also the estimate of range access is always atleast 1. + */ + if (rows > table_records) + { + rows= table_records; + set_if_bigger(rows, 1); + } + param->quick_rows[keynr]= rows; param->possible_keys.set_bit(keynr); if (update_tbl_stats) diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5b96c15bff5..089c8e96e49 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7644,6 +7644,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, keyparts++; } sel /= (double)table->quick_rows[key] / (double) table->stat_records(); + set_if_smaller(sel, 1.0); used_range_selectivity= true; } } @@ -7759,6 +7760,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, sel*= table_multi_eq_cond_selectivity(join, idx, s, rem_tables, keyparts, ref_keyuse_steps); + DBUG_ASSERT(sel > 0 && sel <= 1); return sel; } diff --git a/sql/table.cc b/sql/table.cc index 94cd174ffd7..ac46ff6a42c 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -4162,6 +4162,8 @@ void TABLE::init(THD *thd, TABLE_LIST *tl) created= TRUE; cond_selectivity= 1.0; cond_selectivity_sampling_explain= NULL; + quick_condition_rows= 0; + initialize_quick_structures(); #ifdef HAVE_REPLICATION /* used in RBR Triggers */ master_had_triggers= 0; @@ -7546,3 +7548,21 @@ bool fk_modifies_child(enum_fk_option opt) static bool can_write[]= { false, false, true, true, false, true }; return can_write[opt]; } + +/* + @brief + Initialize all the quick structures that are used to stored the + estimates when the range optimizer is run. + @details + This is specifically needed when we read the TABLE structure from the + table cache. There can be some garbage data from previous queries + that need to be reset here. +*/ + +void TABLE::initialize_quick_structures() +{ + bzero(quick_rows, sizeof(quick_rows)); + bzero(quick_key_parts, sizeof(quick_key_parts)); + bzero(quick_costs, sizeof(quick_costs)); + bzero(quick_n_ranges, sizeof(quick_n_ranges)); +} diff --git a/sql/table.h b/sql/table.h index 98ec9f005ea..44803b5aacd 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1450,6 +1450,7 @@ struct TABLE } bool update_const_key_parts(COND *conds); + void initialize_quick_structures(); my_ptrdiff_t default_values_offset() const { return (my_ptrdiff_t) (s->default_values - record[0]); }

1 0