- commits - lists.mariadb.org

[Commits] 1a2748c: MDEV-15473 Isolate/sandbox PAM modules, so that they can't crash the server.
by holyfoot＠askmonty.org 09 Jul '18

09 Jul '18

revision-id: 1a2748ceb0e30770eebc97d82d7baa885b332c49 (mariadb-10.3.6-45-g1a2748c) parent(s): aa01f51bdef9cc38d8e0a75ea9e2788651e41d16 committer: Alexey Botchkov timestamp: 2018-07-09 13:20:42 +0400 message: MDEV-15473 Isolate/sandbox PAM modules, so that they can't crash the server. Scripts added to set safe permissions for the auth_pam_tool and it's directory. --- plugin/auth_pam/CMakeLists.txt | 7 ++++++- scripts/mysql_install_db.sh | 10 ++++++++++ 2 files changed, 16 insertions(+), 1 deletion(-) diff --git a/plugin/auth_pam/CMakeLists.txt b/plugin/auth_pam/CMakeLists.txt index 4943d57..fbf0979 100644 --- a/plugin/auth_pam/CMakeLists.txt +++ b/plugin/auth_pam/CMakeLists.txt @@ -11,7 +11,12 @@ IF(HAVE_PAM_APPL_H) ADD_DEFINITIONS(-D_GNU_SOURCE) MYSQL_ADD_PLUGIN(auth_pam_v1 auth_pam_v1.c LINK_LIBRARIES pam MODULE_ONLY) MYSQL_ADD_PLUGIN(auth_pam auth_pam.c LINK_LIBRARIES pam dl MODULE_ONLY) - MYSQL_ADD_EXECUTABLE(auth_pam_tool auth_pam_tool.c DESTINATION ${INSTALL_PLUGINDIR}/auth_pam_tool_dir COMPONENT Server) + MYSQL_ADD_EXECUTABLE(auth_pam_tool auth_pam_tool.c DESTINATION ${INSTALL_PLUGINDIR}/auth_pam_tool_dir COMPONENT Server) TARGET_LINK_LIBRARIES(auth_pam_tool pam) + INSTALL(CODE "EXECUTE_PROCESS( + COMMAND chmod u=rwx,g=,o= auth_pam_tool_dir + COMMAND chmod u=rwxs,g=rx,o=rx auth_pam_tool_dir/auth_pam_tool + WORKING_DIRECTORY \$ENV{DESTDIR}\${CMAKE_INSTALL_PREFIX}/${INSTALL_PLUGINDIR}/)" + COMPONENT Server) ENDIF(HAVE_PAM_APPL_H) diff --git a/scripts/mysql_install_db.sh b/scripts/mysql_install_db.sh index ad7c028..fd26228 100644 --- a/scripts/mysql_install_db.sh +++ b/scripts/mysql_install_db.sh @@ -308,6 +308,7 @@ then srcpkgdatadir="$srcdir/scripts" buildpkgdatadir="$builddir/scripts" plugindir="$builddir/plugin/auth_socket" + pamtooldir="$builddir/plugin/auth_pam" elif test -n "$basedir" then bindir="$basedir/bin" # only used in the help text @@ -337,6 +338,7 @@ then exit 1 fi plugindir=`find_in_dirs --dir auth_socket.so $basedir/lib*/plugin $basedir/lib*/mysql/plugin` + pamtooldir=`find_in_dirs --dir auth_pam.so $basedir/lib*/plugin $basedir/lib*/mysql/plugin` else basedir="@prefix@" bindir="@bindir@" @@ -345,6 +347,7 @@ else srcpkgdatadir="@pkgdatadir@" buildpkgdatadir="@pkgdatadir@" plugindir="@pkgplugindir@" + pamtooldir="@pkgplugindir@" fi # Set up paths to SQL scripts required for bootstrap @@ -445,6 +448,13 @@ done if test -n "$user" then + chown $user "$pamtooldir/auth_pam_tool_dir" + if test $? -ne 0 + then + echo "Cannot change ownership of the '$pamtooldir\auth_pam_tool_dir' directory" + echo " to the '$user' user. Check that you have the necessary permissions and try again." + exit 1 + fi args="$args --user=$user" fi

1 0

[Commits] 6bc722b8530: MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views
by Varun 09 Jul '18

09 Jul '18

revision-id: 6bc722b85308709b1df647d97ee83247061c3aa2 (mariadb-5.5.56-247-g6bc722b8530) parent(s): 90cb7212742e9ae3a63bd183e171c95bd12d559f author: Varun Gupta committer: Varun Gupta timestamp: 2018-07-09 13:11:35 +0530 message: MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views In this issue we are using derived_with_keys optimization and we are using these keys to do a hash join which is incorrect. We cannot create keys for dervied tables whose keyparts have types are of BLOB or TEXT type. TEXT or BLOB columns can only be indexed over a specified length. --- mysql-test/r/derived.result | 33 +++++++++++++++++++++++++++++++++ mysql-test/t/derived.test | 21 +++++++++++++++++++++ sql/table.cc | 13 +++++++++++-- 3 files changed, 65 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 54c78dc9f6f..5debfaed5e3 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1023,6 +1023,7 @@ INSERT INTO t2 VALUES (NULL),(NULL); CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; INSERT INTO t3 VALUES ('abc',NULL),('def',4); +set @save_join_cache_level= @@join_cache_level; SET join_cache_level= 8; explain SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; @@ -1052,4 +1053,36 @@ i drop procedure pr; drop view v1; drop table t1; +set @@join_cache_level= @save_join_cache_level; +# +# MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views +# +CREATE TABLE t1 (c1 text, c2 int); +INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3); +CREATE TABLE t2 (c1 text, c2 int); +INSERT INTO t2 VALUES ('b',2), ('c',3); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +c1 c2 c1 c2 +c 3 c 3 +c 3 c 3 +set @save_join_cache_level= @@join_cache_level; +set @@join_cache_level=4; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY <derived2> hash_ALL NULL #hash#$hj 3 test.t2.c1 5 Using where; Using join buffer (flat, BNLH join) +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +c1 c2 c1 c2 +c 3 c 3 +c 3 c 3 +drop table t1,t2; +drop view v1; +set @@join_cache_level= @save_join_cache_level; # end of 5.5 diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index c5b792c8d4d..217203422e0 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -880,6 +880,7 @@ CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; INSERT INTO t3 VALUES ('abc',NULL),('def',4); +set @save_join_cache_level= @@join_cache_level; SET join_cache_level= 8; explain SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; @@ -902,5 +903,25 @@ call pr(2); drop procedure pr; drop view v1; drop table t1; +set @@join_cache_level= @save_join_cache_level; +--echo # +--echo # MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views +--echo # + +CREATE TABLE t1 (c1 text, c2 int); +INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3); +CREATE TABLE t2 (c1 text, c2 int); +INSERT INTO t2 VALUES ('b',2), ('c',3); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; + +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +set @save_join_cache_level= @@join_cache_level; +set @@join_cache_level=4; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +drop table t1,t2; +drop view v1; +set @@join_cache_level= @save_join_cache_level; --echo # end of 5.5 diff --git a/sql/table.cc b/sql/table.cc index 48aa445e1aa..f6152a36eef 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -6060,6 +6060,14 @@ void TABLE::create_key_part_by_field(KEY_PART_INFO *key_part_info, The function checks whether a possible key satisfies the constraints imposed on the keys of any temporary table. + We need to filter out BLOB columns here, because ref access optimizer creates + KEYUSE objects for equalities for non-key columns for two puproses: + 1. To discover possible keys for derived_with_keys optimization + 2. To do hash joins + For the purpose of #1, KEYUSE objects are not created for "blob_column=..." . + However, they might be created for #2. In order to catch that case, we filter + them out here. + @return TRUE if the key is valid @return FALSE otherwise */ @@ -6075,11 +6083,12 @@ bool TABLE::check_tmp_key(uint key, uint key_parts, { uint fld_idx= next_field_no(arg); reg_field= field + fld_idx; + if ((*reg_field)->type() == MYSQL_TYPE_BLOB) + return FALSE; uint fld_store_len= (uint16) (*reg_field)->key_length(); if ((*reg_field)->real_maybe_null()) fld_store_len+= HA_KEY_NULL_LENGTH; - if ((*reg_field)->type() == MYSQL_TYPE_BLOB || - (*reg_field)->real_type() == MYSQL_TYPE_VARCHAR || + if ((*reg_field)->real_type() == MYSQL_TYPE_VARCHAR || (*reg_field)->type() == MYSQL_TYPE_GEOMETRY) fld_store_len+= HA_KEY_BLOB_LENGTH; key_len+= fld_store_len;

1 0

[Commits] 6bc722b8530: MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views
by Varun 09 Jul '18

09 Jul '18

revision-id: 6bc722b85308709b1df647d97ee83247061c3aa2 (mariadb-5.5.56-247-g6bc722b8530) parent(s): 90cb7212742e9ae3a63bd183e171c95bd12d559f author: Varun Gupta committer: Varun Gupta timestamp: 2018-07-09 13:11:35 +0530 message: MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views In this issue we are using derived_with_keys optimization and we are using these keys to do a hash join which is incorrect. We cannot create keys for dervied tables whose keyparts have types are of BLOB or TEXT type. TEXT or BLOB columns can only be indexed over a specified length. --- mysql-test/r/derived.result | 33 +++++++++++++++++++++++++++++++++ mysql-test/t/derived.test | 21 +++++++++++++++++++++ sql/table.cc | 13 +++++++++++-- 3 files changed, 65 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 54c78dc9f6f..5debfaed5e3 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1023,6 +1023,7 @@ INSERT INTO t2 VALUES (NULL),(NULL); CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; INSERT INTO t3 VALUES ('abc',NULL),('def',4); +set @save_join_cache_level= @@join_cache_level; SET join_cache_level= 8; explain SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; @@ -1052,4 +1053,36 @@ i drop procedure pr; drop view v1; drop table t1; +set @@join_cache_level= @save_join_cache_level; +# +# MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views +# +CREATE TABLE t1 (c1 text, c2 int); +INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3); +CREATE TABLE t2 (c1 text, c2 int); +INSERT INTO t2 VALUES ('b',2), ('c',3); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +c1 c2 c1 c2 +c 3 c 3 +c 3 c 3 +set @save_join_cache_level= @@join_cache_level; +set @@join_cache_level=4; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY <derived2> hash_ALL NULL #hash#$hj 3 test.t2.c1 5 Using where; Using join buffer (flat, BNLH join) +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +c1 c2 c1 c2 +c 3 c 3 +c 3 c 3 +drop table t1,t2; +drop view v1; +set @@join_cache_level= @save_join_cache_level; # end of 5.5 diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index c5b792c8d4d..217203422e0 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -880,6 +880,7 @@ CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; INSERT INTO t3 VALUES ('abc',NULL),('def',4); +set @save_join_cache_level= @@join_cache_level; SET join_cache_level= 8; explain SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; @@ -902,5 +903,25 @@ call pr(2); drop procedure pr; drop view v1; drop table t1; +set @@join_cache_level= @save_join_cache_level; +--echo # +--echo # MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views +--echo # + +CREATE TABLE t1 (c1 text, c2 int); +INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3); +CREATE TABLE t2 (c1 text, c2 int); +INSERT INTO t2 VALUES ('b',2), ('c',3); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; + +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +set @save_join_cache_level= @@join_cache_level; +set @@join_cache_level=4; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +drop table t1,t2; +drop view v1; +set @@join_cache_level= @save_join_cache_level; --echo # end of 5.5 diff --git a/sql/table.cc b/sql/table.cc index 48aa445e1aa..f6152a36eef 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -6060,6 +6060,14 @@ void TABLE::create_key_part_by_field(KEY_PART_INFO *key_part_info, The function checks whether a possible key satisfies the constraints imposed on the keys of any temporary table. + We need to filter out BLOB columns here, because ref access optimizer creates + KEYUSE objects for equalities for non-key columns for two puproses: + 1. To discover possible keys for derived_with_keys optimization + 2. To do hash joins + For the purpose of #1, KEYUSE objects are not created for "blob_column=..." . + However, they might be created for #2. In order to catch that case, we filter + them out here. + @return TRUE if the key is valid @return FALSE otherwise */ @@ -6075,11 +6083,12 @@ bool TABLE::check_tmp_key(uint key, uint key_parts, { uint fld_idx= next_field_no(arg); reg_field= field + fld_idx; + if ((*reg_field)->type() == MYSQL_TYPE_BLOB) + return FALSE; uint fld_store_len= (uint16) (*reg_field)->key_length(); if ((*reg_field)->real_maybe_null()) fld_store_len+= HA_KEY_NULL_LENGTH; - if ((*reg_field)->type() == MYSQL_TYPE_BLOB || - (*reg_field)->real_type() == MYSQL_TYPE_VARCHAR || + if ((*reg_field)->real_type() == MYSQL_TYPE_VARCHAR || (*reg_field)->type() == MYSQL_TYPE_GEOMETRY) fld_store_len+= HA_KEY_BLOB_LENGTH; key_len+= fld_store_len;

1 0

[Commits] f7c182924bf: MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union
by Varun 09 Jul '18

09 Jul '18

revision-id: f7c182924bfa680db2cbc1eeafca6da5debea948 (mariadb-10.0.30-395-gf7c182924bf) parent(s): a2c0376e08d80d7b7dad8713d1df334b2b81eff9 author: Varun Gupta committer: Varun Gupta timestamp: 2018-07-09 12:59:21 +0530 message: MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union For index merge union[or sort union], the estimates are not taken into account while calculating the selectivity of a condition. So instead of showing the estimates of the index merge union[or sort union], it shows estimates equal to all the records of the table. The fix for the issue is to include the selectivity of index merge union[or sort union] while calculating the selectivity of a condition --- mysql-test/r/index_merge_myisam.result | 50 ++++++++++++++++++++++++++++++++++ mysql-test/t/index_merge_myisam.test | 35 ++++++++++++++++++++++++ sql/opt_range.cc | 8 +++++- sql/opt_range.h | 2 +- sql/sql_select.cc | 2 +- 5 files changed, 94 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index b3beff5a967..6021011f27a 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -1712,3 +1712,53 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY,c1,i,c2 NULL NULL NULL 69 Using where DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; +# +# MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union +# +create table t0 +( +key1 int not null, +INDEX i1(key1) +); +insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); +set @d=8; +insert into t0 select key1+@d from t0; +set @d=@d*2; +insert into t0 select key1+@d from t0; +set @d=@d*2; +insert into t0 select key1+@d from t0; +set @d=@d*2; +insert into t0 select key1+@d from t0; +set @d=@d*2; +insert into t0 select key1+@d from t0; +set @d=@d*2; +insert into t0 select key1+@d from t0; +set @d=@d*2; +insert into t0 select key1+@d from t0; +set @d=@d*2; +alter table t0 add key2 int not null, add index i2(key2); +alter table t0 add key3 int not null, add index i3(key3); +alter table t0 add key8 int not null, add index i8(key8); +update t0 set key2=key1,key3=key1,key8=1024-key1; +analyze table t0; +Table Op Msg_type Msg_text +test.t0 analyze status OK +set @optimizer_switch_save=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; +explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where +select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +key1 key2 key3 key8 +3 3 3 1021 +set optimizer_use_condition_selectivity=2; +explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where +select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +key1 key2 key3 key8 +3 3 3 1021 +set @@optimizer_switch= @optimizer_switch_save; +drop table t0; diff --git a/mysql-test/t/index_merge_myisam.test b/mysql-test/t/index_merge_myisam.test index d265007431e..e345723a1fd 100644 --- a/mysql-test/t/index_merge_myisam.test +++ b/mysql-test/t/index_merge_myisam.test @@ -243,3 +243,38 @@ DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; +--echo # +--echo # MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union +--echo # + +create table t0 +( + key1 int not null, + INDEX i1(key1) +); + +insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); +let $1=7; +set @d=8; +while ($1) +{ + eval insert into t0 select key1+@d from t0; + eval set @d=@d*2; + dec $1; +} +alter table t0 add key2 int not null, add index i2(key2); +alter table t0 add key3 int not null, add index i3(key3); +alter table t0 add key8 int not null, add index i8(key8); + +update t0 set key2=key1,key3=key1,key8=1024-key1; +analyze table t0; + +set @optimizer_switch_save=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; +explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +set optimizer_use_condition_selectivity=2; +explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +set @@optimizer_switch= @optimizer_switch_save; +drop table t0; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index f1d84e5c623..63874dd26ea 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3491,7 +3491,7 @@ double records_in_column_ranges(PARAM *param, uint idx, TRUE otherwise */ -bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond) +bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond, QUICK_SELECT_I *quick) { uint keynr; uint max_quick_key_parts= 0; @@ -3704,6 +3704,12 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond) } + if (quick && (quick->get_type() == QUICK_SELECT_I::QS_TYPE_ROR_UNION || + quick->get_type() == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE)) + { + table->cond_selectivity*= (quick->records/table_records); + } + bitmap_union(used_fields, &handled_columns); /* Check if we can improve selectivity estimates by using sampling */ diff --git a/sql/opt_range.h b/sql/opt_range.h index 54b15826d1b..bca446d5b9a 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -1051,7 +1051,7 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables, table_map read_tables, COND *conds, bool allow_null_cond, int *error); -bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond); +bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond, QUICK_SELECT_I *quick); #ifdef WITH_PARTITION_STORAGE_ENGINE bool prune_partitions(THD *thd, TABLE *table, Item *pprune_cond); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1ec6cca7c5b..7ef5cd6c77b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -3994,7 +3994,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, if (join->thd->variables.optimizer_use_condition_selectivity > 1) calculate_cond_selectivity_for_table(join->thd, s->table, *s->on_expr_ref ? - *s->on_expr_ref : conds); + *s->on_expr_ref : conds, s->quick); if (s->table->reginfo.impossible_range) { impossible_range= TRUE;

1 0

[Commits] aa01f51bdef: Fix of feedback plugin.
by Oleksandr Byelkin 06 Jul '18

06 Jul '18

revision-id: aa01f51bdef9cc38d8e0a75ea9e2788651e41d16 (mariadb-10.3.6-44-gaa01f51bdef) parent(s): 0e531581e01a493a767a233992d6c1c9b5597c8b author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-07-06 16:26:19 +0200 message: Fix of feedback plugin. Assign "SELECT" to the table before it usage. --- plugin/feedback/sender_thread.cc | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/plugin/feedback/sender_thread.cc b/plugin/feedback/sender_thread.cc index 92f186a1e2a..b025879b6ee 100644 --- a/plugin/feedback/sender_thread.cc +++ b/plugin/feedback/sender_thread.cc @@ -117,11 +117,12 @@ static int prepare_for_fill(TABLE_LIST *tables) tables->init_one_table(&INFORMATION_SCHEMA_NAME, &tbl_name, 0, TL_READ); tables->schema_table= i_s_feedback; + tables->select_lex= thd->lex->first_select_lex(); + DBUG_ASSERT(tables->select_lex); tables->table= create_schema_table(thd, tables); if (!tables->table) return 1; - tables->select_lex= thd->lex->current_select; tables->table->pos_in_table_list= tables; return 0;

1 0

[Commits] 0e531581e01: A fix moved from sql_yacc.yy to sql_yacc_ora.yy.
by Oleksandr Byelkin 06 Jul '18

06 Jul '18

revision-id: 0e531581e01a493a767a233992d6c1c9b5597c8b (mariadb-10.3.6-43-g0e531581e01) parent(s): 6fae04ea7d17aab3e3ef856c9f6a2d45051b426e author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-07-06 10:33:00 +0200 message: A fix moved from sql_yacc.yy to sql_yacc_ora.yy. --- mysql-test/main/set_statement.result | 7 +++++++ mysql-test/main/set_statement.test | 7 +++++++ sql/sql_yacc_ora.yy | 9 +++++---- 3 files changed, 19 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/set_statement.result b/mysql-test/main/set_statement.result index c34e1171899..a12954927b6 100644 --- a/mysql-test/main/set_statement.result +++ b/mysql-test/main/set_statement.result @@ -1225,3 +1225,10 @@ set @rnd=1; select @rnd; @rnd 0 +create table t (a int); +SET sql_mode=ORACLE; +SET STATEMENT myisam_sort_buffer_size=800000 FOR OPTIMIZE TABLE t; +Table Op Msg_type Msg_text +test.t optimize status Table is already up to date +SET sql_mode=default; +drop table t; diff --git a/mysql-test/main/set_statement.test b/mysql-test/main/set_statement.test index cc361553cfd..e2a3288106b 100644 --- a/mysql-test/main/set_statement.test +++ b/mysql-test/main/set_statement.test @@ -1130,3 +1130,10 @@ while ($1) --enable_query_log --echo # @rnd should be 0 select @rnd; + +create table t (a int); +SET sql_mode=ORACLE; +SET STATEMENT myisam_sort_buffer_size=800000 FOR OPTIMIZE TABLE t; +SET sql_mode=default; +drop table t; + diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 28a93806469..8cd2ad598ea 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -13176,10 +13176,11 @@ table_list: table_name: table_ident { - if (unlikely(!Select->add_table_to_list(thd, $1, NULL, - TL_OPTION_UPDATING, - YYPS->m_lock_type, - YYPS->m_mdl_type))) + if (!thd->lex->current_select_or_default()-> + add_table_to_list(thd, $1, NULL, + TL_OPTION_UPDATING, + YYPS->m_lock_type, + YYPS->m_mdl_type)) MYSQL_YYABORT; } ;

1 0

[Commits] 6fae04ea7d1: Fixed compile warning of windows.
by Oleksandr Byelkin 06 Jul '18

06 Jul '18

revision-id: 6fae04ea7d17aab3e3ef856c9f6a2d45051b426e (mariadb-10.3.6-42-g6fae04ea7d1) parent(s): f9849766b340ddadfc77e2f6cec9d8424202d5d3 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-07-06 09:03:13 +0200 message: Fixed compile warning of windows. --- sql/item_subselect.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 7af5472d408..eef6755bc98 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -126,7 +126,7 @@ void Item_subselect::init(st_select_lex *select_lex, else engine= new subselect_single_select_engine(select_lex, result, this); } - DBUG_PRINT("info", ("engine: 0x%lx", (ulong)engine)); + DBUG_PRINT("info", ("engine: %p", engine)); DBUG_VOID_RETURN; }

1 0

[Commits] f9849766b34: Added new warnings for windows specific test.
by Oleksandr Byelkin 06 Jul '18

06 Jul '18

revision-id: f9849766b340ddadfc77e2f6cec9d8424202d5d3 (mariadb-10.3.6-41-gf9849766b34) parent(s): 1f8328fed076c9189792a335a1cc38ad7ad3a042 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-07-06 09:02:40 +0200 message: Added new warnings for windows specific test. --- mysql-test/main/secure_file_priv_win.result | 10 ++++++++++ 1 file changed, 10 insertions(+) diff --git a/mysql-test/main/secure_file_priv_win.result b/mysql-test/main/secure_file_priv_win.result index d456c24d20c..af402ae9537 100644 --- a/mysql-test/main/secure_file_priv_win.result +++ b/mysql-test/main/secure_file_priv_win.result @@ -1,6 +1,8 @@ CREATE TABLE t1 (c1 longtext); INSERT INTO t1 values ('a'); SELECT * FROM t1 INTO OUTFILE 'MYSQL_TMP_DIR/B11764517.tmp'; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead show global variables like 'secure_file_priv'; Variable_name Value secure_file_priv MYSQL_TMP_DIR/ @@ -32,7 +34,15 @@ ERROR HY000: The MariaDB server is running with the --secure-file-priv option so SELECT * FROM t1 INTO OUTFILE 'MYSQL_TMP_DIR\\..a..\\..\\..\\B11764517-2.tmp'; ERROR HY000: The MariaDB server is running with the --secure-file-priv option so it cannot execute this statement SELECT * FROM t1 INTO OUTFILE 'MYSQL_TMP_DIR\\B11764517-2.tmp'; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead SELECT * FROM t1 INTO OUTFILE 'MYSQL_TMP_DIR/B11764517-3.tmp'; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead SELECT * FROM t1 INTO OUTFILE 'MYSQL_TMP_DIR_UCASE/B11764517-4.tmp'; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead SELECT * FROM t1 INTO OUTFILE 'MYSQL_TMP_DIR_LCASE/B11764517-5.tmp'; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead DROP TABLE t1;

1 0

[Commits] e9b78a1: Removed the test case for MDEV-15151 for the following reasons:
by IgorBabaev 05 Jul '18

05 Jul '18

revision-id: e9b78a1055c2b21fac1fc43dc8d660778d493fa6 (mariadb-10.2.16-26-ge9b78a1) parent(s): 1b335a74b4ea1944d6ef91113b1a510c0f11c557 author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-05 15:10:47 -0700 message: Removed the test case for MDEV-15151 for the following reasons: 1. The changed variant did not fail without the patch for MDEV-16629 while the original test case did fail. 2. In any case the test case should go to cte_recursive_not_embedded.test that was not created yet. --- mysql-test/r/cte_recursive.result | 21 --------------------- mysql-test/t/cte_recursive.test | 31 ------------------------------- 2 files changed, 52 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index d715fa2..c892c76 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -3300,24 +3300,3 @@ SELECT func(); func() 1 DROP FUNCTION func; -# -# MDEV-15151: function with recursive CTE using no base tables -# (duplicate of MDEV-16661) -# -connection default; -CREATE TABLE t1 (id int KEY); -INSERT INTO t1 VALUES (0), (1),(2); -CREATE OR REPLACE FUNCTION func() RETURNS int -RETURN -( -WITH recursive cte AS -(SELECT 1 a UNION SELECT cte.* FROM cte natural join t1) -SELECT * FROM cte limit 1 -); -connect con1,localhost,root,,; -SELECT func(); -connection default; -KILL QUERY 5; -DROP FUNCTION func; -DROP TABLE t1; -disconnect con1; diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 227ccd0..4eee9ef 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -1,4 +1,3 @@ ---source include/not_embedded.inc create table t1 (a int, b varchar(32)); insert into t1 values (4,'aaaa' ), (7,'bb'), (1,'ccc'), (4,'dd'); @@ -2324,33 +2323,3 @@ RETURN SELECT func(); DROP FUNCTION func; - ---echo # ---echo # MDEV-15151: function with recursive CTE using no base tables ---echo # (duplicate of MDEV-16661) ---echo # - ---connection default - -CREATE TABLE t1 (id int KEY); -INSERT INTO t1 VALUES (0), (1),(2); - -CREATE OR REPLACE FUNCTION func() RETURNS int -RETURN -( - WITH recursive cte AS - (SELECT 1 a UNION SELECT cte.* FROM cte natural join t1) - SELECT * FROM cte limit 1 -); - ---connect (con1,localhost,root,,) ---let $conid= `SELECT CONNECTION_ID()` ---send SELECT func() - ---connection default ---eval KILL QUERY $conid ---source include/restart_mysqld.inc - -DROP FUNCTION func; -DROP TABLE t1; ---disconnect con1

1 0

[Commits] 1f8328fed07: fix typo noticed by windows compiler
by Oleksandr Byelkin 05 Jul '18

05 Jul '18

revision-id: 1f8328fed076c9189792a335a1cc38ad7ad3a042 (mariadb-10.3.6-40-g1f8328fed07) parent(s): 7e704a2308e25953b5f8fb154eb325df3e25c2ec author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-07-05 17:58:11 +0200 message: fix typo noticed by windows compiler --- sql/sql_parse.cc | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 0509fdbfb4e..95456c03554 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -10078,7 +10078,8 @@ bool parse_sql(THD *thd, Parser_state *parser_state, ((thd->variables.sql_mode & MODE_ORACLE) ? ORAparse(thd) : MYSQLparse(thd)) != 0; - DBUG_ASSERT(opt_bootstrap | mysql_parse_status || thd->lex->select_stack_top == 0); + DBUG_ASSERT(opt_bootstrap || mysql_parse_status || + thd->lex->select_stack_top == 0); thd->lex->current_select= thd->lex->first_select_lex(); /*

1 0