- commits - lists.mariadb.org

[Commits] 45e3dac: MDEV-17605 Statistics for InnoDB table is wrong if persistent statistics is used
by IgorBabaev 23 Apr '19

23 Apr '19

revision-id: 45e3dace5a7b25cdc55c6b5ddb3f26bb5576ea71 (mariadb-10.1.38-112-g45e3dac) parent(s): f4b27400185bab217da11f8781eebb09a8502304 author: Igor Babaev committer: Igor Babaev timestamp: 2019-04-22 17:10:42 -0700 message: MDEV-17605 Statistics for InnoDB table is wrong if persistent statistics is used The command SHOW INDEXES ignored setting of the system variable use_stat_tables to the value of 'preferably' and and showed statistical data received from the engine. Similarly queries over the table STATISTICS from INFORMATION_SCHEMA ignored this setting. It happened because the function fill_schema_table_by_open() did not read any data from statistical tables. --- mysql-test/r/stat_tables.result | 69 ++++++++++++++++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 69 ++++++++++++++++++++++++++++++++++ mysql-test/t/stat_tables.test | 54 ++++++++++++++++++++++++++ sql/sql_class.h | 3 ++ sql/sql_show.cc | 5 +++ sql/sql_statistics.cc | 5 ++- 6 files changed, 204 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index 3ebc3b4..bd3e9ed 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -625,3 +625,72 @@ MAX(pk) NULL DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-17605: SHOW INDEXES with use_stat_tables='preferably' +# +set use_stat_tables='preferably'; +CREATE DATABASE dbt3_s001; +use dbt3_s001; +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; +select * from mysql.table_stats; +db_name table_name cardinality +dbt3_s001 lineitem 6005 +select * from mysql.index_stats; +db_name table_name index_name prefix_arity avg_frequency +dbt3_s001 lineitem PRIMARY 1 4.0033 +dbt3_s001 lineitem PRIMARY 2 1.0000 +dbt3_s001 lineitem i_l_shipdate 1 2.6500 +dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0250 +dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5786 +dbt3_s001 lineitem i_l_partkey 1 30.0250 +dbt3_s001 lineitem i_l_suppkey 1 600.5000 +dbt3_s001 lineitem i_l_receiptdate 1 2.6477 +dbt3_s001 lineitem i_l_orderkey 1 4.0033 +dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033 +dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404 +dbt3_s001 lineitem i_l_commitdate 1 2.7160 +SHOW INDEXES FROM lineitem; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +lineitem 0 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE +lineitem 0 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE +lineitem 1 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE +lineitem 1 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE +lineitem 1 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE +lineitem 1 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE +lineitem 1 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE +lineitem 1 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE +lineitem 1 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE +lineitem 1 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE +lineitem 1 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE +lineitem 1 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE +SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='lineitem'; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT +def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE +def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE +SELECT +COUNT(DISTINCT l_orderkey), COUNT(DISTINCT l_orderkey,l_linenumber), +COUNT(DISTINCT l_shipDATE), +COUNT(DISTINCT l_partkey), COUNT(DISTINCT l_partkey,l_suppkey), +COUNT(DISTINCT l_suppkey), COUNT(DISTINCT l_receiptDATE), +COUNT(DISTINCT l_orderkey, l_quantity), COUNT(DISTINCT l_commitDATE) +FROM lineitem; +COUNT(DISTINCT l_orderkey) COUNT(DISTINCT l_orderkey,l_linenumber) COUNT(DISTINCT l_shipDATE) COUNT(DISTINCT l_partkey) COUNT(DISTINCT l_partkey,l_suppkey) COUNT(DISTINCT l_suppkey) COUNT(DISTINCT l_receiptDATE) COUNT(DISTINCT l_orderkey, l_quantity) COUNT(DISTINCT l_commitDATE) +1500 6005 2266 200 700 10 2268 5772 2211 +set optimizer_switch=@save_optimizer_switch; +DROP DATABASE dbt3_s001; +delete from mysql.table_stats; +delete from mysql.column_stats; +delete from mysql.index_stats; +set @save_optimizer_switch=@@optimizer_switch; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index b0f794c..579d194 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -652,5 +652,74 @@ MAX(pk) NULL DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-17605: SHOW INDEXES with use_stat_tables='preferably' +# +set use_stat_tables='preferably'; +CREATE DATABASE dbt3_s001; +use dbt3_s001; +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; +select * from mysql.table_stats; +db_name table_name cardinality +dbt3_s001 lineitem 6005 +select * from mysql.index_stats; +db_name table_name index_name prefix_arity avg_frequency +dbt3_s001 lineitem PRIMARY 1 4.0033 +dbt3_s001 lineitem PRIMARY 2 1.0000 +dbt3_s001 lineitem i_l_shipdate 1 2.6500 +dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0250 +dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5786 +dbt3_s001 lineitem i_l_partkey 1 30.0250 +dbt3_s001 lineitem i_l_suppkey 1 600.5000 +dbt3_s001 lineitem i_l_receiptdate 1 2.6477 +dbt3_s001 lineitem i_l_orderkey 1 4.0033 +dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033 +dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404 +dbt3_s001 lineitem i_l_commitdate 1 2.7160 +SHOW INDEXES FROM lineitem; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +lineitem 0 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE +lineitem 0 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE +lineitem 1 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE +lineitem 1 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE +lineitem 1 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE +lineitem 1 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE +lineitem 1 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE +lineitem 1 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE +lineitem 1 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE +lineitem 1 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE +lineitem 1 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE +lineitem 1 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE +SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='lineitem'; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT +def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE +def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE +SELECT +COUNT(DISTINCT l_orderkey), COUNT(DISTINCT l_orderkey,l_linenumber), +COUNT(DISTINCT l_shipDATE), +COUNT(DISTINCT l_partkey), COUNT(DISTINCT l_partkey,l_suppkey), +COUNT(DISTINCT l_suppkey), COUNT(DISTINCT l_receiptDATE), +COUNT(DISTINCT l_orderkey, l_quantity), COUNT(DISTINCT l_commitDATE) +FROM lineitem; +COUNT(DISTINCT l_orderkey) COUNT(DISTINCT l_orderkey,l_linenumber) COUNT(DISTINCT l_shipDATE) COUNT(DISTINCT l_partkey) COUNT(DISTINCT l_partkey,l_suppkey) COUNT(DISTINCT l_suppkey) COUNT(DISTINCT l_receiptDATE) COUNT(DISTINCT l_orderkey, l_quantity) COUNT(DISTINCT l_commitDATE) +1500 6005 2266 200 700 10 2268 5772 2211 +set optimizer_switch=@save_optimizer_switch; +DROP DATABASE dbt3_s001; +delete from mysql.table_stats; +delete from mysql.column_stats; +delete from mysql.index_stats; +set @save_optimizer_switch=@@optimizer_switch; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test index b89ab2b..97f9f08 100644 --- a/mysql-test/t/stat_tables.test +++ b/mysql-test/t/stat_tables.test @@ -402,3 +402,57 @@ SELECT MAX(pk) FROM t1; DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-17605: SHOW INDEXES with use_stat_tables='preferably' +--echo # + +set use_stat_tables='preferably'; + +CREATE DATABASE dbt3_s001; + +use dbt3_s001; + +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/dbt3_s001.inc +create index i_p_retailprice on part(p_retailprice); +delete from mysql.table_stats; +delete from mysql.column_stats; +delete from mysql.index_stats; +ANALYZE TABLE lineitem; +FLUSH TABLE mysql.table_stats, mysql.index_stats; +--enable_warnings +--enable_result_log +--enable_query_log + +select * from mysql.table_stats; +select * from mysql.index_stats; + +SHOW INDEXES FROM lineitem; + +SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='lineitem'; + +SELECT + COUNT(DISTINCT l_orderkey), COUNT(DISTINCT l_orderkey,l_linenumber), + COUNT(DISTINCT l_shipDATE), + COUNT(DISTINCT l_partkey), COUNT(DISTINCT l_partkey,l_suppkey), + COUNT(DISTINCT l_suppkey), COUNT(DISTINCT l_receiptDATE), + COUNT(DISTINCT l_orderkey, l_quantity), COUNT(DISTINCT l_commitDATE) +FROM lineitem; + +set optimizer_switch=@save_optimizer_switch; + +DROP DATABASE dbt3_s001; + +delete from mysql.table_stats; +delete from mysql.column_stats; +delete from mysql.index_stats; + +set @save_optimizer_switch=@@optimizer_switch; + +set use_stat_tables=@save_use_stat_tables; diff --git a/sql/sql_class.h b/sql/sql_class.h index 6640e02..2517f5c 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -2199,6 +2199,9 @@ class THD :public Statement, */ bool create_tmp_table_for_derived; + /* The flag to force reading statistics from EITS tables */ + bool force_read_stats; + bool save_prep_leaf_list; /* container for handler's private per-connection data */ diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 73f0f56..46914ea 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -4273,6 +4273,7 @@ fill_schema_table_by_open(THD *thd, bool is_show_fields_or_keys, 'only_view_structure()'. */ lex->sql_command= SQLCOM_SHOW_FIELDS; + thd->force_read_stats= get_schema_table_idx(schema_table) == SCH_STATISTICS; result= (open_temporary_tables(thd, table_list) || open_normal_and_derived_tables(thd, table_list, (MYSQL_OPEN_IGNORE_FLUSH | @@ -4280,6 +4281,10 @@ fill_schema_table_by_open(THD *thd, bool is_show_fields_or_keys, (can_deadlock ? MYSQL_OPEN_FAIL_ON_MDL_CONFLICT : 0)), DT_PREPARE | DT_CREATE)); + + (void) read_statistics_for_tables_if_needed(thd, table_list); + thd->force_read_stats= false; + /* Restore old value of sql_command back as it is being looked at in process_table() function. diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index d3a2094..b435971 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -2177,7 +2177,10 @@ inline bool statistics_for_command_is_needed(THD *thd) { if (thd->bootstrap || thd->variables.use_stat_tables == NEVER) return FALSE; - + + if (thd->force_read_stats) + return TRUE; + switch(thd->lex->sql_command) { case SQLCOM_SELECT: case SQLCOM_INSERT:

1 0

[Commits] a4f7d85: MDEV-18920 Prepared statements with st_convexhull hang and eat 100% cpu.
by holyfoot＠askmonty.org 22 Apr '19

22 Apr '19

revision-id: a4f7d859322ab771289abf13f50752266af43187 (mariadb-10.1.38-114-ga4f7d85) parent(s): 6c5e4c9bc0d9ac30f7ec7ee334630bacb58687ba committer: Alexey Botchkov timestamp: 2019-04-22 23:28:44 +0400 message: MDEV-18920 Prepared statements with st_convexhull hang and eat 100% cpu. In the case of error when object shapes are half-collected we need to set the NULL at the vertice's list. --- mysql-test/r/gis-precise.result | 4 ++++ mysql-test/t/gis-precise.test | 6 ++++++ sql/gcalc_slicescan.cc | 2 ++ 3 files changed, 12 insertions(+) diff --git a/mysql-test/r/gis-precise.result b/mysql-test/r/gis-precise.result index 89e5c23..76f72bb 100644 --- a/mysql-test/r/gis-precise.result +++ b/mysql-test/r/gis-precise.result @@ -505,6 +505,10 @@ GEOMETRYFROMTEXT('POINT(4599 60359)'), ) as relate_res; relate_res 0 +prepare s from 'do st_convexhull(st_aswkb(multipoint(point(-11702,15179),point(-5031,27960),point(-30557,11158),point(-27804,30314))))'; +execute s; +execute s; +deallocate prepare s; DROP TABLE IF EXISTS p1; CREATE PROCEDURE p1(dist DOUBLE, geom TEXT) BEGIN diff --git a/mysql-test/t/gis-precise.test b/mysql-test/t/gis-precise.test index 7391b21..7626da6 100644 --- a/mysql-test/t/gis-precise.test +++ b/mysql-test/t/gis-precise.test @@ -381,5 +381,11 @@ SELECT ST_RELATE( 'F*FFFF**F' ) as relate_res; +# MDEV-18920 Prepared statements with st_convexhull hang and eat 100% cpu. +prepare s from 'do st_convexhull(st_aswkb(multipoint(point(-11702,15179),point(-5031,27960),point(-30557,11158),point(-27804,30314))))'; +execute s; +execute s; +deallocate prepare s; + --source include/gis_debug.inc diff --git a/sql/gcalc_slicescan.cc b/sql/gcalc_slicescan.cc index ab48542..644ab4b 100644 --- a/sql/gcalc_slicescan.cc +++ b/sql/gcalc_slicescan.cc @@ -982,6 +982,8 @@ void Gcalc_heap::reset() { if (m_n_points) { + if (m_hook) + *m_hook= NULL; free_list(m_first); m_n_points= 0; }

1 0

[Commits] 9921dbd4542: Update rocksdb submodule (Range Locking: better comments)
by Sergei Petrunia 22 Apr '19

22 Apr '19

revision-id: 9921dbd4542c4fe1e5d28c611f48816e2568fffd (fb-prod201801-224-g9921dbd4542) parent(s): 7dca0d62c8ec62904f611aea81d593e4a74b7d55 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-04-22 14:26:21 +0300 message: Update rocksdb submodule (Range Locking: better comments) --- rocksdb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/rocksdb b/rocksdb index ded1df92d19..97b782b47ae 160000 --- a/rocksdb +++ b/rocksdb @@ -1 +1 @@ -Subproject commit ded1df92d1992b63f9db12e8757cb65ea711a4e3 +Subproject commit 97b782b47ae55675e2b0132d6332824343fe141e

1 0

[Commits] 97b782b47: Range Locking: better comments
by Sergei Petrunia 22 Apr '19

22 Apr '19

revision-id: 97b782b47ae55675e2b0132d6332824343fe141e (v5.8-1035-g97b782b47) parent(s): ded1df92d1992b63f9db12e8757cb65ea711a4e3 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-04-22 14:24:13 +0300 message: Range Locking: better comments --- include/rocksdb/utilities/transaction.h | 37 ++++++++++++++++++++------------- 1 file changed, 23 insertions(+), 14 deletions(-) diff --git a/include/rocksdb/utilities/transaction.h b/include/rocksdb/utilities/transaction.h index e980e032e..20f9b1bbd 100644 --- a/include/rocksdb/utilities/transaction.h +++ b/include/rocksdb/utilities/transaction.h @@ -26,15 +26,21 @@ using TransactionID = uint64_t; /* - A range endpoint. + class Endpoint allows to define prefix ranges. - Basic ranges can be defined over rowkeys. A Comparator function defines - ordering, a range endpoint is just a rowkey. + Prefix ranges are introduced below. - When one use lexicographic-like ordering, they may want to request "prefix - ranges". + == Basic Ranges == + + Basic ranges can be defined over rowkeys. Key Comparator defines ordering of + rowkeys, a finite closed range is the same as range over numbers: + + lower_endpoint <= X <= upper_endpoint + + The endpoints here are possible rowkey values. + + == Lexicographic-like ordering == - == Lexicographic ordering == A lexicographic-like ordering satisfies these criteria: 1.The ordering is prefix-based. If there are two keys in form @@ -49,21 +55,26 @@ using TransactionID = uint64_t; 2. An empty string is less than any other value. From this it follows that for any prefix and suffix, {prefix, suffix} > {prefix}. - 3. The row comparison function can compare key prefixes. If the data domain - includes keys A and B, then the comparison function is able to compare + 3. The row comparison function is able to compare key prefixes. If the data + domain includes keys A and B, then the comparison function is able to compare equal-length prefixes: min_len= min(byte_length(A), byte_length(B)); - cmp(Slice(A, min_len), Slice(B, min_len)) + cmp(Slice(A, min_len), Slice(B, min_len)) // this call is valid == Prefix ranges == - With lexicographic-like ordering, one may to construct ranges from a + + With lexicographic-like ordering, one may wish to construct ranges from a restriction in form prefix=P: - the left endpoint would would be {P, inf_suffix=false} - the right endpoint would be {P, inf_suffix=true}. - (TODO: or should we instead of the above just require that [Reverse]ByteWiseComparator - is used?) + == Supported comparators == + BytewiseComparator and ReverseBytweiseComparator meet the lexicographic-like + ordering requirements. + + TODO: RangeLocking will refuse to work if any other comparator is used, + although other comparators meeting this property could be used as well. */ class Endpoint { @@ -310,8 +321,6 @@ class Transaction { } // Get a range lock on [start_endpoint; end_endpoint]. - // Note: range endpoints generally a use a different data format than - // ranges. virtual Status GetRangeLock(ColumnFamilyHandle*, const Endpoint&, const Endpoint&) { return Status::NotSupported();

1 0

[Commits] 7dca0d62c8e: MyRocks part of: Remove TransactionDB::get_range_lock_manager()
by Sergei Petrunia 22 Apr '19

22 Apr '19

revision-id: 7dca0d62c8ec62904f611aea81d593e4a74b7d55 (fb-prod201801-223-g7dca0d62c8e) parent(s): 17d2b76267a967a5e8fbdf35330bc5ee25115cc6 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-04-22 13:21:35 +0300 message: MyRocks part of: Remove TransactionDB::get_range_lock_manager() --- rocksdb | 2 +- storage/rocksdb/ha_rocksdb.cc | 19 +++++++++++-------- 2 files changed, 12 insertions(+), 9 deletions(-) diff --git a/rocksdb b/rocksdb index 3b7567781e5..ded1df92d19 160000 --- a/rocksdb +++ b/rocksdb @@ -1 +1 @@ -Subproject commit 3b7567781e50b1ced3583a60fa7f1a568cc42d2e +Subproject commit ded1df92d1992b63f9db12e8757cb65ea711a4e3 diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index 5974cc2d0a0..dea7fb9e53f 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -598,6 +598,7 @@ static uint32_t rocksdb_max_manual_compactions = 0; static ulong rocksdb_max_lock_memory; static my_bool rocksdb_use_range_locking = 0; +static std::shared_ptr<rocksdb::RangeLockMgrHandle> range_lock_mgr; std::atomic<uint64_t> rocksdb_row_lock_deadlocks(0); std::atomic<uint64_t> rocksdb_row_lock_wait_timeouts(0); @@ -4973,7 +4974,12 @@ static int rocksdb_init_func(void *const p) { tx_db_options.custom_mutex_factory = std::make_shared<Rdb_mutex_factory>(); tx_db_options.write_policy = static_cast<rocksdb::TxnDBWritePolicy>(rocksdb_write_policy); - tx_db_options.use_range_locking = rocksdb_use_range_locking; + + if (rocksdb_use_range_locking) { + range_lock_mgr.reset( + rocksdb::NewRangeLockManager(tx_db_options.custom_mutex_factory)); + tx_db_options.range_lock_mgr = range_lock_mgr; + } status = check_rocksdb_options_compatibility(rocksdb_datadir, main_opts, cf_descr); @@ -4996,11 +5002,9 @@ static int rocksdb_init_func(void *const p) { DBUG_RETURN(HA_EXIT_FAILURE); } - if (rocksdb_use_range_locking) + if (range_lock_mgr) { - rocksdb::RangeLockMgrControl *mgr= rdb->get_range_lock_manager(); - - mgr->set_max_lock_memory(rocksdb_max_lock_memory); + range_lock_mgr->set_max_lock_memory(rocksdb_max_lock_memory); sql_print_information("RocksDB: USING NEW RANGE LOCKING"); sql_print_information("RocksDB: Max lock memory=%lu", rocksdb_max_lock_memory); } @@ -12903,10 +12907,9 @@ static SHOW_VAR rocksdb_empty_status_variables[] = { static void show_rocksdb_locktree_vars(THD *thd, SHOW_VAR *var, char *buff) { var->type = SHOW_ARRAY; - if (rocksdb_use_range_locking) + if (range_lock_mgr) { - //TODO: - rocksdb_locktree_escalation_count= rdb->get_range_lock_manager()->get_escalation_count(); + rocksdb_locktree_escalation_count= range_lock_mgr->get_escalation_count(); var->value = reinterpret_cast<char *>(&rocksdb_locktree_status_variables); } else

1 0

[Commits] ded1df92d: Remove TransactionDB::get_range_lock_manager()
by Sergei Petrunia 22 Apr '19

22 Apr '19

revision-id: ded1df92d1992b63f9db12e8757cb65ea711a4e3 (v5.8-1034-gded1df92d) parent(s): 3b7567781e50b1ced3583a60fa7f1a568cc42d2e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-04-22 13:18:22 +0300 message: Remove TransactionDB::get_range_lock_manager() This function was used by the user to get a handle to control the range lock manager. Now, the range lock manager is created by the user and then provided to TransactionDB::Open: mgr= rocksdb::NewRangeLockManager(...); TransactionDBOptions txn_db_opts; txn_db_opts.range_lock_mgr = mgr; TransactionDB::Open(... txn_db_opts ...); --- include/rocksdb/utilities/transaction_db.h | 31 +++++++++++--------- utilities/transactions/pessimistic_transaction.cc | 3 +- .../transactions/pessimistic_transaction_db.cc | 33 ++++++++++++---------- .../transactions/pessimistic_transaction_db.h | 8 ++---- utilities/transactions/range_locking_test.cc | 9 +++--- utilities/transactions/transaction_lock_mgr.cc | 18 ++++++++++-- utilities/transactions/transaction_lock_mgr.h | 13 +++++++-- 7 files changed, 69 insertions(+), 46 deletions(-) diff --git a/include/rocksdb/utilities/transaction_db.h b/include/rocksdb/utilities/transaction_db.h index b12c70785..8f0e018f2 100644 --- a/include/rocksdb/utilities/transaction_db.h +++ b/include/rocksdb/utilities/transaction_db.h @@ -33,6 +33,20 @@ enum TxnDBWritePolicy { const uint32_t kInitialMaxDeadlocks = 5; +// A handle to control RangeLockMgr +class RangeLockMgrHandle { + public: + virtual int set_max_lock_memory(size_t max_lock_memory) = 0; + virtual uint64_t get_escalation_count() = 0; + virtual ~RangeLockMgrHandle() {}; +}; + +// A factory function to create a Range Lock Manager +RangeLockMgrHandle* NewRangeLockManager( + std::shared_ptr<TransactionDBMutexFactory> mutex_factory +); + + struct TransactionDBOptions { // Specifies the maximum number of keys that can be locked at the same time // per column family. @@ -93,10 +107,9 @@ struct TransactionDBOptions { // logic in myrocks. This hack of simply not rolling back merge operands works // for the special way that myrocks uses this operands. bool rollback_merge_operands = false; - - // If true, range_locking_opts specifies options on range locking (filling - // the struct is mandatory) - bool use_range_locking = false; + + // If non-null, range locking should be used, and the lock manager is passed. + std::shared_ptr<RangeLockMgrHandle> range_lock_mgr; }; struct TransactionOptions { @@ -197,15 +210,6 @@ struct DeadlockPath { bool empty() { return path.empty() && !limit_exceeded; } }; -// Interface for controlling Range Locking manager -class RangeLockMgrControl { - public: - - virtual int set_max_lock_memory(size_t max_lock_memory) = 0; - virtual uint64_t get_escalation_count() = 0; - - virtual ~RangeLockMgrControl(){} -}; class TransactionDB : public StackableDB { public: @@ -281,7 +285,6 @@ class TransactionDB : public StackableDB { virtual std::vector<DeadlockPath> GetDeadlockInfoBuffer() = 0; virtual void SetDeadlockInfoBufferSize(uint32_t target_size) = 0; - virtual RangeLockMgrControl* get_range_lock_manager() { return nullptr; } protected: // To Create an TransactionDB, call Open() // The ownership of db is transferred to the base StackableDB diff --git a/utilities/transactions/pessimistic_transaction.cc b/utilities/transactions/pessimistic_transaction.cc index ca6bc27b6..ff653fa0e 100644 --- a/utilities/transactions/pessimistic_transaction.cc +++ b/utilities/transactions/pessimistic_transaction.cc @@ -50,7 +50,8 @@ PessimisticTransaction::PessimisticTransaction( skip_concurrency_control_(false) { txn_db_impl_ = static_cast_with_check<PessimisticTransactionDB, TransactionDB>(txn_db); - do_key_tracking_ = !txn_db_impl_->get_range_lock_manager(); + + do_key_tracking_ = txn_db_impl_->ShouldDoKeyTracking(); db_impl_ = static_cast_with_check<DBImpl, DB>(db_); Initialize(txn_options); } diff --git a/utilities/transactions/pessimistic_transaction_db.cc b/utilities/transactions/pessimistic_transaction_db.cc index 06981adf4..e50fb1dad 100644 --- a/utilities/transactions/pessimistic_transaction_db.cc +++ b/utilities/transactions/pessimistic_transaction_db.cc @@ -41,25 +41,28 @@ PessimisticTransactionDB::PessimisticTransactionDB( } void PessimisticTransactionDB::init_lock_manager() { - BaseLockMgr *lock_mgr; - std::shared_ptr<TransactionDBMutexFactory> mutex_factory = - txn_db_options_.custom_mutex_factory? - txn_db_options_.custom_mutex_factory : - std::shared_ptr<TransactionDBMutexFactory>( - new TransactionDBMutexFactoryImpl()); - - if (txn_db_options_.use_range_locking) { - range_lock_mgr_= new RangeLockMgr(this, mutex_factory); - lock_mgr = range_lock_mgr_; + if (txn_db_options_.range_lock_mgr) { + // A custom lock manager was provided in options + std::shared_ptr<RangeLockMgr> tmp; + tmp = std::static_pointer_cast<RangeLockMgr>(txn_db_options_.range_lock_mgr); + lock_mgr_= tmp; + range_lock_mgr_ = static_cast<RangeLockMgr*>(lock_mgr_.get()); } else { - lock_mgr = new TransactionLockMgr(this, txn_db_options_.num_stripes, - txn_db_options_.max_num_locks, - txn_db_options_.max_num_deadlocks, - mutex_factory); + // Use point lock manager by default + std::shared_ptr<TransactionDBMutexFactory> mutex_factory = + txn_db_options_.custom_mutex_factory? + txn_db_options_.custom_mutex_factory : + std::shared_ptr<TransactionDBMutexFactory>( + new TransactionDBMutexFactoryImpl()); + auto lock_mgr = new TransactionLockMgr(this, txn_db_options_.num_stripes, + txn_db_options_.max_num_locks, + txn_db_options_.max_num_deadlocks, + mutex_factory); + lock_mgr_.reset(lock_mgr); range_lock_mgr_ = nullptr; } - lock_mgr_ = std::shared_ptr<BaseLockMgr>(lock_mgr); + lock_mgr_->init(this); } // Support initiliazing PessimisticTransactionDB from a stackable db diff --git a/utilities/transactions/pessimistic_transaction_db.h b/utilities/transactions/pessimistic_transaction_db.h index 59da1634c..e88856342 100644 --- a/utilities/transactions/pessimistic_transaction_db.h +++ b/utilities/transactions/pessimistic_transaction_db.h @@ -127,6 +127,8 @@ class PessimisticTransactionDB : public TransactionDB { virtual void UpdateCFComparatorMap(const std::vector<ColumnFamilyHandle*>&) {} virtual void UpdateCFComparatorMap(ColumnFamilyHandle*) {} + // Key Tracking should be done only with point lock manager. + bool ShouldDoKeyTracking() const { return range_lock_mgr_ == nullptr; } protected: DBImpl* db_impl_; std::shared_ptr<Logger> info_log_; @@ -157,12 +159,6 @@ class PessimisticTransactionDB : public TransactionDB { // Non-null if we are using a lock manager that supports range locking. RangeLockMgr *range_lock_mgr_ = nullptr; - public: - // Return Range Lock Manager if we are actually using it - virtual RangeLockMgrControl* get_range_lock_manager() override { - return range_lock_mgr_; - } - private: void init_lock_manager(); // Must be held when adding/dropping column families. diff --git a/utilities/transactions/range_locking_test.cc b/utilities/transactions/range_locking_test.cc index aef345be5..df01573e4 100644 --- a/utilities/transactions/range_locking_test.cc +++ b/utilities/transactions/range_locking_test.cc @@ -42,6 +42,7 @@ class RangeLockingTest : public ::testing::Test { std::string dbname; Options options; + std::shared_ptr<RangeLockMgrHandle> range_lock_mgr; TransactionDBOptions txn_db_options; RangeLockingTest() @@ -51,13 +52,13 @@ class RangeLockingTest : public ::testing::Test { DestroyDB(dbname, options); Status s; - txn_db_options.use_range_locking = true; + + range_lock_mgr.reset(rocksdb::NewRangeLockManager(nullptr)); + txn_db_options.range_lock_mgr = range_lock_mgr; + s = TransactionDB::Open(options, txn_db_options, dbname, &db); assert(s.ok()); - rocksdb::RangeLockMgrControl *mgr= db->get_range_lock_manager(); - assert(mgr); - // can also: mgr->set_max_lock_memory(rocksdb_max_lock_memory); } ~RangeLockingTest() { diff --git a/utilities/transactions/transaction_lock_mgr.cc b/utilities/transactions/transaction_lock_mgr.cc index 8b5907bc5..edec58261 100644 --- a/utilities/transactions/transaction_lock_mgr.cc +++ b/utilities/transactions/transaction_lock_mgr.cc @@ -28,6 +28,7 @@ #include "util/sync_point.h" #include "util/thread_local.h" #include "utilities/transactions/pessimistic_transaction_db.h" +#include "utilities/transactions/transaction_db_mutex_impl.h" namespace rocksdb { @@ -753,6 +754,18 @@ void TransactionLockMgr::Resize(uint32_t target_size) { // RangeLockMgr - a lock manager that supports range locking ///////////////////////////////////////////////////////////////////////////// +RangeLockMgrHandle* NewRangeLockManager( + std::shared_ptr<TransactionDBMutexFactory> mutex_factory) { + std::shared_ptr<TransactionDBMutexFactory> use_factory; + + if (mutex_factory) + use_factory = mutex_factory; + else + use_factory.reset(new TransactionDBMutexFactoryImpl()); + + return new RangeLockMgr(use_factory); +} + /* Storage for locks that are currently held by a transaction. @@ -1070,9 +1083,8 @@ int RangeLockMgr::compare_dbt_endpoints(__toku_db*, void *arg, } -RangeLockMgr::RangeLockMgr(TransactionDB* txn_db, - std::shared_ptr<TransactionDBMutexFactory> mutex_factory) : - my_txn_db_(txn_db), mutex_factory_(mutex_factory) { +RangeLockMgr::RangeLockMgr(std::shared_ptr<TransactionDBMutexFactory> mutex_factory) : + mutex_factory_(mutex_factory) { ltm.create(on_create, on_destroy, on_escalate, NULL, mutex_factory_); cmp_.create(compare_dbt_endpoints, (void*)this, NULL); DICTIONARY_ID dict_id = { .dictid = 1 }; diff --git a/utilities/transactions/transaction_lock_mgr.h b/utilities/transactions/transaction_lock_mgr.h index 5e9ee851c..02c68964e 100644 --- a/utilities/transactions/transaction_lock_mgr.h +++ b/utilities/transactions/transaction_lock_mgr.h @@ -76,6 +76,9 @@ class BaseLockMgr { // Resize the deadlock info buffer virtual void Resize(uint32_t)=0; virtual std::vector<DeadlockPath> GetDeadlockInfoBuffer()= 0; + + // TransactionDB will call this at start + virtual void init(TransactionDB *db_arg) {}; virtual ~BaseLockMgr(){} using LockStatusData = std::unordered_multimap<uint32_t, KeyLockInfo>; @@ -194,7 +197,7 @@ using namespace toku; */ class RangeLockMgr : public BaseLockMgr, - public RangeLockMgrControl { + public RangeLockMgrHandle { public: void AddColumnFamily(uint32_t) override { /* do nothing */ } void RemoveColumnFamily(uint32_t) override { /* do nothing */ } @@ -227,8 +230,12 @@ class RangeLockMgr : void UnLock(PessimisticTransaction* txn, uint32_t column_family_id, const std::string& key, Env* env) override ; - RangeLockMgr(TransactionDB* txn_db, - std::shared_ptr<TransactionDBMutexFactory> mutex_factory); + RangeLockMgr(std::shared_ptr<TransactionDBMutexFactory> mutex_factory); + + void init(TransactionDB *db_arg) override { + my_txn_db_ = db_arg; + } + ~RangeLockMgr(); int set_max_lock_memory(size_t max_lock_memory) override

1 0

[Commits] 0693c8dd7: Remove TransactionDB::get_range_lock_manager()
by Sergei Petrunia 22 Apr '19

22 Apr '19

revision-id: 0693c8dd78bcbc82dba1867174b16bf3d4d7b67c (v5.8-1034-g0693c8dd7) parent(s): 3b7567781e50b1ced3583a60fa7f1a568cc42d2e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-04-22 12:31:32 +0300 message: Remove TransactionDB::get_range_lock_manager() This function was used by the user to get a handle to control the range lock manager. Now, the range lock manager is created by the user and then provided to TransactionDB::Open: mgr= rocksdb::NewRangeLockManager(...); TransactionDBOptions txn_db_opts; txn_db_opts.range_lock_mgr = mgr; TransactionDB::Open(... txn_db_opts ...); --- include/rocksdb/utilities/transaction_db.h | 30 +++++++++++--------- utilities/transactions/pessimistic_transaction.cc | 3 +- .../transactions/pessimistic_transaction_db.cc | 33 ++++++++++++---------- .../transactions/pessimistic_transaction_db.h | 8 ++---- utilities/transactions/transaction_lock_mgr.cc | 11 ++++++-- utilities/transactions/transaction_lock_mgr.h | 13 +++++++-- 6 files changed, 56 insertions(+), 42 deletions(-) diff --git a/include/rocksdb/utilities/transaction_db.h b/include/rocksdb/utilities/transaction_db.h index b12c70785..57cf219da 100644 --- a/include/rocksdb/utilities/transaction_db.h +++ b/include/rocksdb/utilities/transaction_db.h @@ -33,6 +33,19 @@ enum TxnDBWritePolicy { const uint32_t kInitialMaxDeadlocks = 5; +// A handle to control RangeLockMgr +class RangeLockMgrHandle { + public: + virtual int set_max_lock_memory(size_t max_lock_memory) = 0; + virtual uint64_t get_escalation_count() = 0; +}; + +// A factory function to create a Range Lock Manager +RangeLockMgrHandle* NewRangeLockManager( + std::shared_ptr<TransactionDBMutexFactory> mutex_factory +); + + struct TransactionDBOptions { // Specifies the maximum number of keys that can be locked at the same time // per column family. @@ -93,10 +106,9 @@ struct TransactionDBOptions { // logic in myrocks. This hack of simply not rolling back merge operands works // for the special way that myrocks uses this operands. bool rollback_merge_operands = false; - - // If true, range_locking_opts specifies options on range locking (filling - // the struct is mandatory) - bool use_range_locking = false; + + // If non-null, range locking should be used, and the lock manager is passed. + std::shared_ptr<RangeLockMgrHandle> range_lock_mgr; }; struct TransactionOptions { @@ -197,15 +209,6 @@ struct DeadlockPath { bool empty() { return path.empty() && !limit_exceeded; } }; -// Interface for controlling Range Locking manager -class RangeLockMgrControl { - public: - - virtual int set_max_lock_memory(size_t max_lock_memory) = 0; - virtual uint64_t get_escalation_count() = 0; - - virtual ~RangeLockMgrControl(){} -}; class TransactionDB : public StackableDB { public: @@ -281,7 +284,6 @@ class TransactionDB : public StackableDB { virtual std::vector<DeadlockPath> GetDeadlockInfoBuffer() = 0; virtual void SetDeadlockInfoBufferSize(uint32_t target_size) = 0; - virtual RangeLockMgrControl* get_range_lock_manager() { return nullptr; } protected: // To Create an TransactionDB, call Open() // The ownership of db is transferred to the base StackableDB diff --git a/utilities/transactions/pessimistic_transaction.cc b/utilities/transactions/pessimistic_transaction.cc index ca6bc27b6..ff653fa0e 100644 --- a/utilities/transactions/pessimistic_transaction.cc +++ b/utilities/transactions/pessimistic_transaction.cc @@ -50,7 +50,8 @@ PessimisticTransaction::PessimisticTransaction( skip_concurrency_control_(false) { txn_db_impl_ = static_cast_with_check<PessimisticTransactionDB, TransactionDB>(txn_db); - do_key_tracking_ = !txn_db_impl_->get_range_lock_manager(); + + do_key_tracking_ = txn_db_impl_->ShouldDoKeyTracking(); db_impl_ = static_cast_with_check<DBImpl, DB>(db_); Initialize(txn_options); } diff --git a/utilities/transactions/pessimistic_transaction_db.cc b/utilities/transactions/pessimistic_transaction_db.cc index 06981adf4..e50fb1dad 100644 --- a/utilities/transactions/pessimistic_transaction_db.cc +++ b/utilities/transactions/pessimistic_transaction_db.cc @@ -41,25 +41,28 @@ PessimisticTransactionDB::PessimisticTransactionDB( } void PessimisticTransactionDB::init_lock_manager() { - BaseLockMgr *lock_mgr; - std::shared_ptr<TransactionDBMutexFactory> mutex_factory = - txn_db_options_.custom_mutex_factory? - txn_db_options_.custom_mutex_factory : - std::shared_ptr<TransactionDBMutexFactory>( - new TransactionDBMutexFactoryImpl()); - - if (txn_db_options_.use_range_locking) { - range_lock_mgr_= new RangeLockMgr(this, mutex_factory); - lock_mgr = range_lock_mgr_; + if (txn_db_options_.range_lock_mgr) { + // A custom lock manager was provided in options + std::shared_ptr<RangeLockMgr> tmp; + tmp = std::static_pointer_cast<RangeLockMgr>(txn_db_options_.range_lock_mgr); + lock_mgr_= tmp; + range_lock_mgr_ = static_cast<RangeLockMgr*>(lock_mgr_.get()); } else { - lock_mgr = new TransactionLockMgr(this, txn_db_options_.num_stripes, - txn_db_options_.max_num_locks, - txn_db_options_.max_num_deadlocks, - mutex_factory); + // Use point lock manager by default + std::shared_ptr<TransactionDBMutexFactory> mutex_factory = + txn_db_options_.custom_mutex_factory? + txn_db_options_.custom_mutex_factory : + std::shared_ptr<TransactionDBMutexFactory>( + new TransactionDBMutexFactoryImpl()); + auto lock_mgr = new TransactionLockMgr(this, txn_db_options_.num_stripes, + txn_db_options_.max_num_locks, + txn_db_options_.max_num_deadlocks, + mutex_factory); + lock_mgr_.reset(lock_mgr); range_lock_mgr_ = nullptr; } - lock_mgr_ = std::shared_ptr<BaseLockMgr>(lock_mgr); + lock_mgr_->init(this); } // Support initiliazing PessimisticTransactionDB from a stackable db diff --git a/utilities/transactions/pessimistic_transaction_db.h b/utilities/transactions/pessimistic_transaction_db.h index 59da1634c..e88856342 100644 --- a/utilities/transactions/pessimistic_transaction_db.h +++ b/utilities/transactions/pessimistic_transaction_db.h @@ -127,6 +127,8 @@ class PessimisticTransactionDB : public TransactionDB { virtual void UpdateCFComparatorMap(const std::vector<ColumnFamilyHandle*>&) {} virtual void UpdateCFComparatorMap(ColumnFamilyHandle*) {} + // Key Tracking should be done only with point lock manager. + bool ShouldDoKeyTracking() const { return range_lock_mgr_ == nullptr; } protected: DBImpl* db_impl_; std::shared_ptr<Logger> info_log_; @@ -157,12 +159,6 @@ class PessimisticTransactionDB : public TransactionDB { // Non-null if we are using a lock manager that supports range locking. RangeLockMgr *range_lock_mgr_ = nullptr; - public: - // Return Range Lock Manager if we are actually using it - virtual RangeLockMgrControl* get_range_lock_manager() override { - return range_lock_mgr_; - } - private: void init_lock_manager(); // Must be held when adding/dropping column families. diff --git a/utilities/transactions/transaction_lock_mgr.cc b/utilities/transactions/transaction_lock_mgr.cc index 8b5907bc5..c3428e057 100644 --- a/utilities/transactions/transaction_lock_mgr.cc +++ b/utilities/transactions/transaction_lock_mgr.cc @@ -753,6 +753,12 @@ void TransactionLockMgr::Resize(uint32_t target_size) { // RangeLockMgr - a lock manager that supports range locking ///////////////////////////////////////////////////////////////////////////// +RangeLockMgrHandle* NewRangeLockManager( + std::shared_ptr<TransactionDBMutexFactory> mutex_factory +) { + return new RangeLockMgr(mutex_factory); +} + /* Storage for locks that are currently held by a transaction. @@ -1070,9 +1076,8 @@ int RangeLockMgr::compare_dbt_endpoints(__toku_db*, void *arg, } -RangeLockMgr::RangeLockMgr(TransactionDB* txn_db, - std::shared_ptr<TransactionDBMutexFactory> mutex_factory) : - my_txn_db_(txn_db), mutex_factory_(mutex_factory) { +RangeLockMgr::RangeLockMgr(std::shared_ptr<TransactionDBMutexFactory> mutex_factory) : + mutex_factory_(mutex_factory) { ltm.create(on_create, on_destroy, on_escalate, NULL, mutex_factory_); cmp_.create(compare_dbt_endpoints, (void*)this, NULL); DICTIONARY_ID dict_id = { .dictid = 1 }; diff --git a/utilities/transactions/transaction_lock_mgr.h b/utilities/transactions/transaction_lock_mgr.h index 5e9ee851c..02c68964e 100644 --- a/utilities/transactions/transaction_lock_mgr.h +++ b/utilities/transactions/transaction_lock_mgr.h @@ -76,6 +76,9 @@ class BaseLockMgr { // Resize the deadlock info buffer virtual void Resize(uint32_t)=0; virtual std::vector<DeadlockPath> GetDeadlockInfoBuffer()= 0; + + // TransactionDB will call this at start + virtual void init(TransactionDB *db_arg) {}; virtual ~BaseLockMgr(){} using LockStatusData = std::unordered_multimap<uint32_t, KeyLockInfo>; @@ -194,7 +197,7 @@ using namespace toku; */ class RangeLockMgr : public BaseLockMgr, - public RangeLockMgrControl { + public RangeLockMgrHandle { public: void AddColumnFamily(uint32_t) override { /* do nothing */ } void RemoveColumnFamily(uint32_t) override { /* do nothing */ } @@ -227,8 +230,12 @@ class RangeLockMgr : void UnLock(PessimisticTransaction* txn, uint32_t column_family_id, const std::string& key, Env* env) override ; - RangeLockMgr(TransactionDB* txn_db, - std::shared_ptr<TransactionDBMutexFactory> mutex_factory); + RangeLockMgr(std::shared_ptr<TransactionDBMutexFactory> mutex_factory); + + void init(TransactionDB *db_arg) override { + my_txn_db_ = db_arg; + } + ~RangeLockMgr(); int set_max_lock_memory(size_t max_lock_memory) override

1 0

[Commits] 61177f7: MDEV-19275 Provide SQL service to plugins.
by holyfoot＠askmonty.org 22 Apr '19

22 Apr '19

revision-id: 61177f75dc30403a5a6ced18ab069c1883952f5b (mariadb-10.4.4-15-g61177f7) parent(s): e4c5551964f398ebbe2b1b34fef028eff6f22fbe committer: Alexey Botchkov timestamp: 2019-04-22 11:36:37 +0400 message: MDEV-19275 Provide SQL service to plugins. Service SQL owerall design. The service_sql.h is not included in services.h, it's supposed to be included explicitly where the service is needed as it includes the mysql.h. --- include/mysql.h | 3 +- include/mysql/service_sql.h | 93 +++++++++++++++++++++++++++++++++++++++++++++ include/service_versions.h | 1 + libmysqld/libmysql.c | 5 --- libservices/CMakeLists.txt | 1 + libservices/sql_service.c | 19 +++++++++ sql-common/client.c | 17 +++++++++ sql/CMakeLists.txt | 1 + sql/service_sql.cc | 43 +++++++++++++++++++++ sql/sql_plugin_services.ic | 24 +++++++++++- 10 files changed, 199 insertions(+), 8 deletions(-) diff --git a/include/mysql.h b/include/mysql.h index 68b2e76..71c903e 100644 --- a/include/mysql.h +++ b/include/mysql.h @@ -193,7 +193,8 @@ enum mysql_option /* MariaDB options */ MYSQL_PROGRESS_CALLBACK=5999, MYSQL_OPT_NONBLOCK, - MYSQL_OPT_USE_THREAD_SPECIFIC_MEMORY + MYSQL_OPT_USE_THREAD_SPECIFIC_MEMORY, + MYSQL_OPT_USE_LOCAL_CONNECTION }; /** diff --git a/include/mysql/service_sql.h b/include/mysql/service_sql.h new file mode 100644 index 0000000..587ed77 --- /dev/null +++ b/include/mysql/service_sql.h @@ -0,0 +1,93 @@ +/* Copyright (C) 2019 MariaDB Corporation + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02111-1301 USA */ + +//#if (!defined (MYSQL_SERVICE_SQL) && defined (_mysql_h)) +#ifndef MYSQL_SERVICE_SQL +#define MYSQL_SERVICE_SQL + +#include "../mysql.h" + +/** + @file + sql service + + Provides interface for plugins to execute SQL queries. + +*/ + +#ifdef __cplusplus +extern "C" { +#endif + + +/* + TODO 5313 - decide if we should uncomment this istead of + the 'include "../mysql.h"' +typedef struct st_mysql MYSQL; +typedef struct st_mysql_res MYSQL_RES; +typedef char **MYSQL_ROW; +*/ + +extern struct sql_service_st { + MYSQL *(*init)(MYSQL *mysql); + MYSQL *(*real_connect)(MYSQL *mysql,const char *host, const char *user, + const char *passwd, const char *db, + uint port, const char *unix_socket,ulong client_flag); + int (*options)(MYSQL *mysql, enum mysql_option option, const void *arg); + void (*close)(MYSQL *mysql); + int (*real_query)(MYSQL *mysql, const char *q, + unsigned long length); + my_ulonglong (*affected_rows)(MYSQL *mysql); + unsigned int (*merrno)(MYSQL *mysql); + const char *(*error)(MYSQL *mysql); + MYSQL_RES *(*store_result)(MYSQL *mysql); + void (*free_result)(MYSQL_RES *result); + my_ulonglong (*num_rows)(MYSQL_RES *res); + unsigned int (*num_fields)(MYSQL_RES *res); + MYSQL_ROW (*fetch_row)(MYSQL_RES *result); + unsigned long * (*fetch_lengths)(MYSQL_RES *result); +} *sql_service; + +#ifdef MYSQL_DYNAMIC_PLUGIN + +#define mysql_init sql_service->init +#define mysql_real_connect sql_service->real_connect +#define mysql_local_connect sql_service->local_connect +#define mysql_close sql_service->close +#define mysql_real_query sql_service->real_query +#define mysql_affected_rows sql_service->affected_rows +#define mysql_warning_count sql_service->warning_count +#define mysql_errno sql_service->errno +#define mysql_sqlstate sql_service->sqlstate +#define mysql_error sql_service->error +#define mysql_store_result sql_service->store_result +#define mysql_free_result sql_service->free_result +#define mysql_num_rows sql_service->num_rows +#define mysql_num_fields sql_service->num_fields +#define mysql_fetch_field sql_service->fetch_field +#define mysql_field_seek sql_service->field_seek +#define mysql_fetch_row sql_service->fetch_row +#define mysql_fetch_lengths sql_service->fetch_lengths + +#endif /*MYSQL_DYNAMIC_PLUGIN*/ + + +#ifdef __cplusplus +} +#endif + +#endif /* MYSQL_SERVICE_SQL */ + + diff --git a/include/service_versions.h b/include/service_versions.h index 050012d..4cb4725 100644 --- a/include/service_versions.h +++ b/include/service_versions.h @@ -43,3 +43,4 @@ #define VERSION_thd_wait 0x0100 #define VERSION_wsrep 0x0202 #define VERSION_json 0x0100 +#define VERSION_sql 0x0100 diff --git a/libmysqld/libmysql.c b/libmysqld/libmysql.c index cd170b4..9c826a4 100644 --- a/libmysqld/libmysql.c +++ b/libmysqld/libmysql.c @@ -1062,11 +1062,6 @@ unsigned int STDCALL mysql_field_count(MYSQL *mysql) return mysql->field_count; } -my_ulonglong STDCALL mysql_affected_rows(MYSQL *mysql) -{ - return mysql->affected_rows; -} - my_ulonglong STDCALL mysql_insert_id(MYSQL *mysql) { return mysql->insert_id; diff --git a/libservices/CMakeLists.txt b/libservices/CMakeLists.txt index b99be71..0120648 100644 --- a/libservices/CMakeLists.txt +++ b/libservices/CMakeLists.txt @@ -38,6 +38,7 @@ SET(MYSQLSERVICES_SOURCES thd_wait_service.c wsrep_service.c json_service.c + sql_service.c ) ADD_CONVENIENCE_LIBRARY(mysqlservices ${MYSQLSERVICES_SOURCES}) diff --git a/libservices/sql_service.c b/libservices/sql_service.c new file mode 100644 index 0000000..39a9d0a --- /dev/null +++ b/libservices/sql_service.c @@ -0,0 +1,19 @@ + +/* Copyright (c) 2019, Monty Program Ab + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, but + WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA +*/ + +#include <service_versions.h> +SERVICE_VERSION sql_service= (void*)VERSION_sql; diff --git a/sql-common/client.c b/sql-common/client.c index c66cb1a..ace5630 100644 --- a/sql-common/client.c +++ b/sql-common/client.c @@ -2844,6 +2844,11 @@ set_connect_attributes(MYSQL *mysql, char *buff, size_t buf_len) } +#ifdef MYSQL_SERVER +extern MYSQL * STDCALL do_local_connect(MYSQL *mysql); +#endif /*MYSQL_SERVER*/ + + MYSQL * STDCALL CLI_MYSQL_REAL_CONNECT(MYSQL *mysql,const char *host, const char *user, const char *passwd, const char *db, @@ -2875,6 +2880,10 @@ CLI_MYSQL_REAL_CONNECT(MYSQL *mysql,const char *host, const char *user, set_mysql_error(mysql, CR_ALREADY_CONNECTED, unknown_sqlstate); DBUG_RETURN(0); } +#ifdef MYSQL_SERVER + if (mysql->options.methods_to_use == MYSQL_OPT_USE_LOCAL_CONNECTION) + DBUG_RETURN(do_local_connect(mysql)); +#endif mysql->methods= &client_methods; mysql->client_flag=0; /* For handshake */ @@ -3907,6 +3916,11 @@ static MYSQL_RES * cli_use_result(MYSQL *mysql) } +my_ulonglong STDCALL mysql_affected_rows(MYSQL *mysql) +{ + return mysql->affected_rows; +} + /************************************************************************** Return next row of the query results **************************************************************************/ @@ -4038,6 +4052,9 @@ mysql_options(MYSQL *mysql,enum mysql_option option, const void *arg) break; case MYSQL_OPT_USE_REMOTE_CONNECTION: case MYSQL_OPT_USE_EMBEDDED_CONNECTION: +#ifdef MYSQL_SERVER + case MYSQL_OPT_USE_LOCAL_CONNECTION: +#endif /*MYSQL_SERVER*/ case MYSQL_OPT_GUESS_CONNECTION: mysql->options.methods_to_use= option; break; diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt index ecca723..6a405c7 100644 --- a/sql/CMakeLists.txt +++ b/sql/CMakeLists.txt @@ -144,6 +144,7 @@ SET (SQL_SOURCE ${WSREP_SOURCES} table_cache.cc encryption.cc temporary_tables.cc proxy_protocol.cc backup.cc + service_sql.cc ${CMAKE_CURRENT_BINARY_DIR}/sql_builtin.cc ${CMAKE_CURRENT_BINARY_DIR}/sql_yacc.cc ${CMAKE_CURRENT_BINARY_DIR}/sql_yacc_ora.cc diff --git a/sql/service_sql.cc b/sql/service_sql.cc new file mode 100644 index 0000000..36f65b7 --- /dev/null +++ b/sql/service_sql.cc @@ -0,0 +1,43 @@ +/* Copyright 2018 Codership Oy <info(a)codership.com> + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */ +#include "mariadb.h" +#include "sql_prepare.h" +#include <sql_common.h> +#include <mysql.h> + +extern "C" +{ + +static MYSQL_METHODS connect_local_methods= +{ + NULL,//loc_read_query_result, + NULL,//loc_advanced_command, + NULL,//loc_read_rows, + NULL,//loc_use_result, + NULL,//loc_fetch_lengths, + NULL,//loc_flush_use_result, + NULL //loc_read_change_user_result +}; + + +MYSQL * STDCALL do_local_connect(MYSQL *mysql) +{ + mysql->methods= &connect_local_methods; + return 0; +} + + +} /* extern "C" */ + diff --git a/sql/sql_plugin_services.ic b/sql/sql_plugin_services.ic index 955b9a0..9e7a5b9 100644 --- a/sql/sql_plugin_services.ic +++ b/sql/sql_plugin_services.ic @@ -17,6 +17,7 @@ /* support for Services */ #include <service_versions.h> #include <mysql/service_wsrep.h> +#include <mysql/service_sql.h> struct st_service_ref { const char *name; @@ -208,7 +209,7 @@ static struct my_print_error_service_st my_print_error_handler= my_printv_error }; -struct json_service_st json_handler= +static struct json_service_st json_handler= { json_type, json_get_array_item, @@ -218,6 +219,24 @@ struct json_service_st json_handler= json_unescape_json }; +static struct sql_service_st sql_handler= +{ + mysql_init, + mysql_real_connect, + mysql_options, + mysql_close, + mysql_real_query, + mysql_affected_rows, + mysql_errno, + mysql_error, + mysql_store_result, + mysql_free_result, + mysql_num_rows, + mysql_num_fields, + mysql_fetch_row, + mysql_fetch_lengths +}; + static struct st_service_ref list_of_services[]= { { "base64_service", VERSION_base64, &base64_handler }, @@ -241,6 +260,7 @@ static struct st_service_ref list_of_services[]= { "thd_timezone_service", VERSION_thd_timezone, &thd_timezone_handler }, { "thd_wait_service", VERSION_thd_wait, &thd_wait_handler }, { "wsrep_service", VERSION_wsrep, &wsrep_handler }, - { "json_service", VERSION_json, &json_handler } + { "json_service", VERSION_json, &json_handler }, + { "sql_service", VERSION_sql, &sql_handler } };

1 0

[Commits] 167a96b5157: MDEV-9959: A serious MariaDB server performance bug
by varunraiko1803＠gmail.com 20 Apr '19

20 Apr '19

revision-id: 167a96b5157049408a6ad4bca7abcd376af93fb5 (mariadb-10.3.0-644-g167a96b5157) parent(s): 4359c6b4806605c78987e50cab3a6b42016b7603 author: Varun Gupta committer: Varun Gupta timestamp: 2018-03-27 16:45:46 +0530 message: MDEV-9959: A serious MariaDB server performance bug step#1: if a derived table has SELECT DISTINCT, provide index statistics for it so that the join optimizer in the upper select knows that ref access to the table will produce one row. Added handling for multiple selects in the derived table --- mysql-test/r/cte_nonrecursive.result | 8 +-- mysql-test/r/cte_recursive.result | 6 +- mysql-test/r/derived_cond_pushdown.result | 82 ++++++++++++------------- mysql-test/r/derived_view.result | 2 +- mysql-test/r/join_cache.result | 6 +- mysql-test/r/mdev9959.result | 46 ++++++++++++++ mysql-test/r/subselect_extra.result | 2 +- mysql-test/r/subselect_extra_no_semijoin.result | 2 +- mysql-test/t/mdev9959.test | 25 ++++++++ sql/sql_lex.h | 2 + sql/sql_union.cc | 60 ++++++++++++++++++ sql/sql_yacc.yy | 3 + sql/table.cc | 19 ++++++ 13 files changed, 209 insertions(+), 54 deletions(-) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index 53334512b20..a3eb81b0747 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -244,7 +244,7 @@ with t as (select distinct a from t1 where b >= 'c') select * from t as r1, t as r2 where r1.a=r2.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 8 Using where -1 PRIMARY <derived3> ref key0 key0 5 r1.a 2 +1 PRIMARY <derived3> ref key0 key0 5 r1.a 1 3 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary explain @@ -253,7 +253,7 @@ select * from (select distinct a from t1 where b >= 'c') as r1, where r1.a=r2.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 8 Using where -1 PRIMARY <derived3> ref key0 key0 5 r1.a 2 +1 PRIMARY <derived3> ref key0 key0 5 r1.a 1 3 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary # two references to t specified by a query @@ -369,7 +369,7 @@ select c as a from t2 where c < 4) select * from t2,t where t2.c=t.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 1 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where 3 UNION t2 ALL NULL NULL NULL NULL 4 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -381,7 +381,7 @@ select c as a from t2 where c < 4) as t where t2.c=t.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 1 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where 3 UNION t2 ALL NULL NULL NULL NULL 4 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 15d4fc1a01f..a7305046ba3 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -1238,9 +1238,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 2 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 3 RECURSIVE UNION p ALL PRIMARY NULL NULL NULL 12 -3 RECURSIVE UNION <derived2> ref key0 key0 5 test.p.id 2 +3 RECURSIVE UNION <derived2> ref key0 key0 5 test.p.id 1 4 RECURSIVE UNION p ALL PRIMARY NULL NULL NULL 12 -4 RECURSIVE UNION <derived2> ref key0 key0 5 test.p.id 2 +4 RECURSIVE UNION <derived2> ref key0 key0 5 test.p.id 1 NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL with recursive ancestors @@ -3049,7 +3049,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 16 100.00 2 DERIVED a ALL NULL NULL NULL NULL 16 100.00 Using where 3 RECURSIVE UNION b ALL NULL NULL NULL NULL 16 100.00 Using where -3 RECURSIVE UNION <derived2> ref key0 key0 35 test.b.departure 2 100.00 +3 RECURSIVE UNION <derived2> ref key0 key0 35 test.b.departure 1 100.00 4 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 16 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 32d3c88cc8d..3723e25a494 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -5187,7 +5187,7 @@ explain select * from v2_union as v,t2 where ((v.a=6) or (v.a=8)) and (v.c>200) and (v.a=t2.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 6 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort 4 UNION t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort @@ -5213,7 +5213,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 6, + "rows": 1, "filtered": 100, "attached_condition": "v.c > 200", "materialized": { @@ -5358,7 +5358,7 @@ a b c a b c d explain select * from v3_union as v,t2 where (v.a=t2.a) and (v.c>6); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 4 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -5382,7 +5382,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 4, + "rows": 1, "filtered": 100, "attached_condition": "v.c > 6", "materialized": { @@ -5476,7 +5476,7 @@ a b c a b c d explain select * from v3_union as v,t2 where (v.a=t2.a) and ((t2.a>1) or (v.b<20)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 4 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -5500,7 +5500,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 4, + "rows": 1, "filtered": 100, "attached_condition": "t2.a > 1 or v.b < 20", "materialized": { @@ -5561,7 +5561,7 @@ explain select * from v3_union as v,t2 where (v.a=t2.a) and ((v.b=19) or (v.b=21)) and ((v.c<3) or (v.c>600)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 4 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -5586,7 +5586,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 4, + "rows": 1, "filtered": 100, "attached_condition": "(v.b = 19 or v.b = 21) and (v.c < 3 or v.c > 600)", "materialized": { @@ -5645,7 +5645,7 @@ a b c a b c d explain select * from v4_union as v,t2 where (v.a=t2.a) and (v.b<20); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 4 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -5669,7 +5669,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 4, + "rows": 1, "filtered": 100, "attached_condition": "v.b < 20", "materialized": { @@ -5752,7 +5752,7 @@ explain select * from v4_union as v,t2 where (v.a=t2.a) and ((t2.a<3) or (v.b<40)) and (v.c>500); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 4 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -5777,7 +5777,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 4, + "rows": 1, "filtered": 100, "attached_condition": "(t2.a < 3 or v.b < 40) and v.c > 500", "materialized": { @@ -7952,7 +7952,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a<5); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -7976,7 +7976,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -8226,7 +8226,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.c>500); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -8250,7 +8250,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.c > 500", "materialized": { @@ -8317,7 +8317,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a<5) and (v1.c>500); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -8341,7 +8341,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.c > 500", "materialized": { @@ -8411,7 +8411,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and ((v1.b>27) or (v1.b<19)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -8435,7 +8435,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.b > 27 or v1.b < 19", "materialized": { @@ -8508,7 +8508,7 @@ explain select * from v1,t2 where (v1.a=t2.a) and ((v1.c<400) or (v1.c>800)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -8533,7 +8533,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.c < 400 or v1.c > 800", "materialized": { @@ -8762,7 +8762,7 @@ where ((d1.a>4) and (d1.c>500))); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.b 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.b 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -8796,7 +8796,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["b"], "ref": ["test.t2.b"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "t2.c = 988 and t2.b > 13 or d1.a > 4 and d1.c > 500", "materialized": { @@ -8872,7 +8872,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>5) and (v1.c>200); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 UNION <derived4> ALL NULL NULL NULL NULL 18 Using where 4 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort @@ -8899,7 +8899,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 3, + "rows": 1, "filtered": 100, "attached_condition": "v1.c > 200", "materialized": { @@ -9138,7 +9138,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>5) and (v1.c>200); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 UNION t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 4 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort @@ -9163,7 +9163,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 3, + "rows": 1, "filtered": 100, "attached_condition": "v1.c > 200", "materialized": { @@ -9260,7 +9260,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<200); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 4 UNION t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort @@ -9285,7 +9285,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 3, + "rows": 1, "filtered": 100, "attached_condition": "v1.c < 200", "materialized": { @@ -9498,7 +9498,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<130); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT <derived4> ALL NULL NULL NULL NULL 18 Using where 4 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort @@ -9525,7 +9525,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.c < 130", "materialized": { @@ -9643,7 +9643,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<130); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT <derived4> ALL NULL NULL NULL NULL 18 Using where 4 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort @@ -9671,7 +9671,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 3, + "rows": 1, "filtered": 100, "attached_condition": "v1.c < 130", "materialized": { @@ -10143,7 +10143,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a<2) and (v1.b<30) and (v1.c>450); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -10167,7 +10167,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.b < 30 and v1.c > 450", "materialized": { @@ -10243,7 +10243,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and ((v1.a<2) or (v1.a<5)) and (v1.c>450); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -10267,7 +10267,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.c > 450", "materialized": { @@ -10636,7 +10636,7 @@ a b c a b c explain select * from v1,t2 where (v1.b=t2.b) and (v1.a<3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.b 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.b 1 Using where 2 DERIVED t3 range i1 i1 5 NULL 1 Using index condition 3 UNION t3 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -10660,7 +10660,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["b"], "ref": ["test.t2.b"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.a < 3", "materialized": { @@ -12333,7 +12333,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["b"], "ref": ["test.t1.a"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 85e56ff176e..3a0fde7b053 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1525,7 +1525,7 @@ EXPLAIN SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 1 FirstMatch(t1) 3 DERIVED t2 ALL NULL NULL NULL NULL 6 4 UNION t3 ALL NULL NULL NULL NULL 4 NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index eea397402ad..cc185b640bc 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5197,7 +5197,7 @@ SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 End temporary +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 End temporary 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); @@ -5208,8 +5208,8 @@ EXPLAIN SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 -1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 End temporary 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); diff --git a/mysql-test/r/mdev9959.result b/mysql-test/r/mdev9959.result new file mode 100644 index 00000000000..049e0350cca --- /dev/null +++ b/mysql-test/r/mdev9959.result @@ -0,0 +1,46 @@ +create table t1(a int); +insert into t1 values (1),(2),(3),(4),(5),(6); +create table t2(a int, b int,c int); +insert into t2(a,b,c) values (1,1,2),(2,2,3),(3,1,4),(4,2,2),(5,1,1),(6,2,5); +create table t3(a int, b int); +insert into t3(a,b) values (1,1),(2,2),(2,1),(1,2),(5,1),(9,2); +table "<derived2>" should have type=ref and rows=1 +one select in derived table +with distinct +analyze select * from t1 , ((select distinct t2.a from t2 order by c))q where t1.a=q.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 1.00 100.00 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using temporary; Using filesort +# multiple selects in derived table +# NO UNION ALL +analyze select * from t1 , ( (select t2.a,t2.b from t2 order by c) union (select t2.a,t2.b from t2 order by c))q where t1.a=q.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 1.00 100.00 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 +3 UNION t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 6.00 NULL NULL +select * from t1 , ( (select t2.a from t2 order by c) union (select t2.a from t2 order by c))q where t1.a=q.a; +a a +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +# UNION ALL and EXCEPT +analyze select * from t1 , ( (select t2.a,t2.b from t2 order by c) union all (select t2.a,t2.b from t2 order by c) except(select t3.a, t3.b from t3 order by b))q where t1.a=q.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 0.50 100.00 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 +3 UNION t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 +4 EXCEPT t3 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 +NULL UNIT RESULT <unit2,3,4> ALL NULL NULL NULL NULL NULL 3.00 NULL NULL +select * from t1 , ( (select t2.a,t2.b from t2 order by c) union all (select t2.a,t2.b from t2 order by c) except(select t3.a, t3.b from t3 order by b))q where t1.a=q.a; +a a b +3 3 1 +4 4 2 +6 6 2 +drop table t1,t2,t3; diff --git a/mysql-test/r/subselect_extra.result b/mysql-test/r/subselect_extra.result index 73642c09324..a743f30d00a 100644 --- a/mysql-test/r/subselect_extra.result +++ b/mysql-test/r/subselect_extra.result @@ -409,7 +409,7 @@ EXPLAIN SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 1 FirstMatch(t1) 3 DERIVED t2 ALL NULL NULL NULL NULL 6 4 UNION t3 ALL NULL NULL NULL NULL 4 NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL diff --git a/mysql-test/r/subselect_extra_no_semijoin.result b/mysql-test/r/subselect_extra_no_semijoin.result index fc8d5759945..2646bc51bac 100644 --- a/mysql-test/r/subselect_extra_no_semijoin.result +++ b/mysql-test/r/subselect_extra_no_semijoin.result @@ -411,7 +411,7 @@ EXPLAIN SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 10 func,func 2 Using where +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 10 func,func 1 Using where 3 DERIVED t2 ALL NULL NULL NULL NULL 6 4 UNION t3 ALL NULL NULL NULL NULL 4 NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL diff --git a/mysql-test/t/mdev9959.test b/mysql-test/t/mdev9959.test new file mode 100644 index 00000000000..36f364261c9 --- /dev/null +++ b/mysql-test/t/mdev9959.test @@ -0,0 +1,25 @@ +create table t1(a int); +insert into t1 values (1),(2),(3),(4),(5),(6); +create table t2(a int, b int,c int); +insert into t2(a,b,c) values (1,1,2),(2,2,3),(3,1,4),(4,2,2),(5,1,1),(6,2,5); +create table t3(a int, b int); +insert into t3(a,b) values (1,1),(2,2),(2,1),(1,2),(5,1),(9,2); + +--echo table "<derived2>" should have type=ref and rows=1 +--echo one select in derived table + +--echo with distinct +analyze select * from t1 , ((select distinct t2.a from t2 order by c))q where t1.a=q.a; + +--echo # multiple selects in derived table +--echo # NO UNION ALL +analyze select * from t1 , ( (select t2.a,t2.b from t2 order by c) union (select t2.a,t2.b from t2 order by c))q where t1.a=q.a; +select * from t1 , ( (select t2.a from t2 order by c) union (select t2.a from t2 order by c))q where t1.a=q.a; + +--echo # UNION ALL and EXCEPT +analyze select * from t1 , ( (select t2.a,t2.b from t2 order by c) union all (select t2.a,t2.b from t2 order by c) except(select t3.a, t3.b from t3 order by b))q where t1.a=q.a; + +select * from t1 , ( (select t2.a,t2.b from t2 order by c) union all (select t2.a,t2.b from t2 order by c) except(select t3.a, t3.b from t3 order by b))q where t1.a=q.a; + +drop table t1,t2,t3; + diff --git a/sql/sql_lex.h b/sql/sql_lex.h index f0241a32acf..16d5e55d251 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -761,6 +761,7 @@ class st_select_lex_unit: public st_select_lex_node { Procedure *last_procedure; /* Pointer to procedure, if such exists */ bool columns_are_renamed; + bool union_all; /* TRUE if there is a UNION ALL operation */ void init_query(); st_select_lex* outer_select(); @@ -800,6 +801,7 @@ class st_select_lex_unit: public st_select_lex_node { bool union_needs_tmp_table(); void set_unique_exclude(); + bool check_distinct_in_union(); friend struct LEX; friend int subselect_union_engine::exec(); diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 857c9a117f5..6dac0cb0458 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1960,3 +1960,63 @@ void st_select_lex_unit::set_unique_exclude() } } } + +/* + Check if the selects in the derived table can give distinct rows irrespective + of the data given for the tables. + + for example: + select * from + ((select t1.a from t1) op (select t2.a from t2) op (select t3.a from t3)); + the op here being UNION/INTERSECT/EXCEPT + + so this function would check if the derived table like the case above + can give distinct rows or not irrespective of the data in the tables. + + So what the function is handling: + - If there is no UNION ALL, we are guarenteed distinct rows. + + Example + ((select t1.a from t1) UNION (select t2.a from t2); + + - If there is UNION ALL, we can still guarantee distinct rows if + the last operation of the selects does not involve UNION ALL or + INTERSECT is not there + + Example: + ((select t1.a from t1) UNION ALL (select t2.a from t2) UNION/EXCEPT + (select t3.a from t3)): guarantees distinct rows, UNION and EXCEPT + have the same precedence. + + Example: + ((select t1.a from t1) UNION ALL (select t2.a from t2) INTERSECT + (select t3.a from t3)): does not guarantee distinct rows because + INTERSECT has higher precedence than UNION so we would evaluate + the INTERSECT part first and then do UNION ALL, so there we can + end up with duplicates, so distinct rows are not guaranteed. + + @retval false Distinct rows are not guaranteed + @retval true Distinct rows are guanranteed irrespective of the data + in the tables + +*/ + +bool st_select_lex_unit::check_distinct_in_union() +{ + bool is_intersect_present=FALSE; + st_select_lex* first= first_select(); + for(st_select_lex *sl=first; sl ; sl=sl->next_select()) + is_intersect_present|= (sl->linkage == INTERSECT_TYPE); + + if (!union_all) + return true; + else + { + if (union_distinct) + { + if (!is_intersect_present && !union_distinct->next_select()) + return true; + } + } + return false; +} diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 35ec2d29d21..112873e8362 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -571,7 +571,10 @@ bool LEX::add_select_to_union_list(bool is_union_distinct, current_select; } else + { DBUG_ASSERT(type == UNION_TYPE); + current_select->master_unit()->union_all= true; + } return FALSE; } diff --git a/sql/table.cc b/sql/table.cc index 4f90d429ce5..d2938a29ea6 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -7113,6 +7113,25 @@ bool TABLE::add_tmp_key(uint key, uint key_parts, if (!keyinfo->rec_per_key) return TRUE; bzero(keyinfo->rec_per_key, sizeof(ulong)*key_parts); + /* + For the case when there is a derived table that would give distinct rows, + the index statistics are passed to the join optimizer to tell that + a ref access to the derived table will produce only one row. + */ + + st_select_lex_unit* derived= pos_in_table_list ? pos_in_table_list->derived: NULL; + if (derived) + { + /* + This handles the case when we have a single select in the derived table + */ + st_select_lex* first= derived->first_select(); + if ((first && !first->is_part_of_union() && + first->options & SELECT_DISTINCT) || + derived->check_distinct_in_union()) + keyinfo->rec_per_key[key_parts-1]=1; + } + keyinfo->read_stats= NULL; keyinfo->collected_stats= NULL;

2 2

[Commits] 793b74b: MDEV-19255 Server crash in st_join_table::save_explain_data or assertion
by IgorBabaev 19 Apr '19

19 Apr '19

revision-id: 793b74ba759845db885ceb1b9cbc5f8e99fd2cc3 (mariadb-10.4.4-30-g793b74b) parent(s): 878ca5ca4f8c5598f2e289a753327c87b5a8818c author: Igor Babaev committer: Igor Babaev timestamp: 2019-04-19 16:17:51 -0700 message: MDEV-19255 Server crash in st_join_table::save_explain_data or assertion `sel->quick' failure in JOIN::make_range_rowid_filters upon query with rowid_filter=ON Index ranges can be defined using conditions with inexpensive subqueries. Such a subquery is evaluated when some representation of a possible range sequence is built. After the evaluation the JOIN structure of the subsquery is distroyed. Any attempt to build the above representation may fail because the function that checks whether a subquery is inexpensive in some cases uses the join structure of the subquery. When a range rowid filter is built by a range sequence constructed out of a range condition that uses an inexpensive subquery the representation of the the sequence is built twice. Building the second representation fails due to the described problem with the execution of Item_subselect::is_expensive(). The function was corrected to return the result of the last its invocation if the Item_subselect object has been already evaluated. --- mysql-test/main/rowid_filter.result | 92 ++++++++++++++++++++++++++++++ mysql-test/main/rowid_filter.test | 39 +++++++++++++ mysql-test/main/rowid_filter_innodb.result | 92 ++++++++++++++++++++++++++++++ sql/item_subselect.cc | 20 ++++--- sql/item_subselect.h | 2 + 5 files changed, 237 insertions(+), 8 deletions(-) diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index 280ced7..efe914f 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -2012,4 +2012,96 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0 DROP TABLE t1,t2; +# +# MDEV-19255: rowid range filter built for range condition +# that uses in expensive subquery +# +CREATE TABLE t1 ( +pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'), +(17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'), +(24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'), +(31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'), +(38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'), +(45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'), +(52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'), +(59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'), +(66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'), +(73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'), +(80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'), +(87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL), +(94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'), +(101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'), +(107,8,'z'),(108,3,'k'),(109,65,NULL); +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1,'x'); +INSERT INTO t2 SELECT * FROM t1; +SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +pk1 a1 b1 pk2 a2 b2 +65 2 a 109 65 NULL +EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where +1 PRIMARY t1 eq_ref|filter PRIMARY,b1 PRIMARY|b1 4|4 test.t2.a2 1 (87%) 87.00 Using where; Using rowid filter +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2` +EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 101, + "filtered": 100, + "attached_condition": "t2.a2 <> t2.pk2 and t2.a2 is not null" + }, + "table": { + "table_name": "t1", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "b1"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk1"], + "ref": ["test.t2.a2"], + "rowid_filter": { + "range": { + "key": "b1", + "used_key_parts": ["b1"] + }, + "rows": 87, + "selectivity_pct": 87 + }, + "rows": 1, + "filtered": 87, + "attached_condition": "t1.b1 <= (subquery#2)" + }, + "subqueries": [ + { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t2", + "access_type": "range", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk2"], + "rows": 1, + "filtered": 100, + "index_condition": "t2.pk2 <= 1" + } + } + } + ] + } +} +DROP TABLE t1,t2; set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test index 9c53367..6f26e81 100644 --- a/mysql-test/main/rowid_filter.test +++ b/mysql-test/main/rowid_filter.test @@ -300,4 +300,43 @@ SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3); DROP TABLE t1,t2; +--echo # +--echo # MDEV-19255: rowid range filter built for range condition +--echo # that uses in expensive subquery +--echo # + +CREATE TABLE t1 ( + pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'), +(17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'), +(24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'), +(31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'), +(38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'), +(45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'), +(52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'), +(59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'), +(66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'), +(73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'), +(80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'), +(87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL), +(94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'), +(101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'), +(107,8,'z'),(108,3,'k'),(109,65,NULL); + +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1,'x'); +INSERT INTO t2 SELECT * FROM t1; + +let $q= +SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) + WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); + +eval $q; +eval EXPLAIN EXTENDED $q; +eval EXPLAIN FORMAT=JSON $q; + +DROP TABLE t1,t2; + set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index c877e5a..54c7e03 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -1941,6 +1941,98 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0 DROP TABLE t1,t2; +# +# MDEV-19255: rowid range filter built for range condition +# that uses in expensive subquery +# +CREATE TABLE t1 ( +pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'), +(17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'), +(24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'), +(31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'), +(38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'), +(45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'), +(52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'), +(59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'), +(66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'), +(73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'), +(80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'), +(87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL), +(94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'), +(101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'), +(107,8,'z'),(108,3,'k'),(109,65,NULL); +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1,'x'); +INSERT INTO t2 SELECT * FROM t1; +SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +pk1 a1 b1 pk2 a2 b2 +65 2 a 109 65 NULL +EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where +1 PRIMARY t1 eq_ref|filter PRIMARY,b1 PRIMARY|b1 4|4 test.t2.a2 1 (87%) 87.00 Using where; Using rowid filter +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2` +EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 101, + "filtered": 100, + "attached_condition": "t2.a2 <> t2.pk2 and t2.a2 is not null" + }, + "table": { + "table_name": "t1", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "b1"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk1"], + "ref": ["test.t2.a2"], + "rowid_filter": { + "range": { + "key": "b1", + "used_key_parts": ["b1"] + }, + "rows": 87, + "selectivity_pct": 87 + }, + "rows": 1, + "filtered": 87, + "attached_condition": "t1.b1 <= (subquery#2)" + }, + "subqueries": [ + { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t2", + "access_type": "range", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk2"], + "rows": 1, + "filtered": 100, + "index_condition": "t2.pk2 <= 1" + } + } + } + ] + } +} +DROP TABLE t1,t2; set @@use_stat_tables=@save_use_stat_tables; # # MDEV-18755: possible RORI-plan and possible plan with range filter diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 4fb9abc..2b1c4c1 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -54,7 +54,8 @@ Item_subselect::Item_subselect(THD *thd_arg): value_assigned(0), own_engine(0), thd(0), old_engine(0), have_to_be_excluded(0), inside_first_fix_fields(0), done_first_fix_fields(FALSE), - expr_cache(0), forced_const(FALSE), substitution(0), engine(0), eliminated(FALSE), + expr_cache(0), forced_const(FALSE), expensive_fl(FALSE), + substitution(0), engine(0), eliminated(FALSE), changed(0), is_correlated(FALSE), with_recursive_reference(0) { DBUG_ENTER("Item_subselect::Item_subselect"); @@ -585,6 +586,9 @@ bool Item_subselect::is_expensive() double examined_rows= 0; bool all_are_simple= true; + if (!expensive_fl && is_evaluated()) + return false; + /* check extremely simple select */ if (!unit->first_select()->next_select()) // no union { @@ -595,7 +599,7 @@ bool Item_subselect::is_expensive() SELECT_LEX *sl= unit->first_select(); JOIN *join = sl->join; if (join && !join->tables_list && !sl->first_inner_unit()) - return false; + return (expensive_fl= false); } @@ -605,14 +609,14 @@ bool Item_subselect::is_expensive() /* not optimized subquery */ if (!cur_join) - return true; + return (expensive_fl= true); /* If the subquery is not optimised or in the process of optimization it supposed to be expensive */ if (cur_join->optimization_state != JOIN::OPTIMIZATION_DONE) - return true; + return (expensive_fl= true); if (!cur_join->tables_list && !sl->first_inner_unit()) continue; @@ -634,7 +638,7 @@ bool Item_subselect::is_expensive() considered optimized if it has a join plan. */ if (!cur_join->join_tab) - return true; + return (expensive_fl= true); if (sl->first_inner_unit()) { @@ -642,15 +646,15 @@ bool Item_subselect::is_expensive() Subqueries that contain subqueries are considered expensive. @todo: accumulate the cost of subqueries. */ - return true; + return (expensive_fl= true); } examined_rows+= cur_join->get_examined_rows(); } // here we are sure that subquery is optimized so thd is set - return !all_are_simple && - (examined_rows > thd->variables.expensive_subquery_limit); + return (expensive_fl= !all_are_simple && + (examined_rows > thd->variables.expensive_subquery_limit)); } diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 0e771ba..bbc24d3 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -73,6 +73,8 @@ class Item_subselect :public Item_result_field, to substitute 'this' with a constant item. */ bool forced_const; + /* Set to the result of the last call of is_expensive() */ + bool expensive_fl; #ifndef DBUG_OFF /* Count the number of times this subquery predicate has been executed. */ uint exec_counter;

1 0