[Commits] 3f4dab80912: MDEV-17032: Estimates are higher for partitions of a table with @@use_stat_tables= PREFERABLY
by varunraiko1803＠gmail.com 14 Nov '18
by varunraiko1803＠gmail.com 14 Nov '18
14 Nov '18
revision-id: 3f4dab80912682325b61b288178febe9ee55de49 (mariadb-10.0.36-78-g3f4dab80912)
parent(s): 6cecb10a2f8b6536bed78ab6d3791d8befc9d732
author: Varun Gupta
committer: Varun Gupta
timestamp: 2018-11-12 14:40:07 +0530
message:
MDEV-17032: Estimates are higher for partitions of a table with @@use_stat_tables= PREFERABLY
The problem here is EITS statistics does not calculate statistics for the partitions of the table.
So a temporary solution would be to not read EITS statistics for partitioned tables.
Also disabling reading of EITS for columns that participate in the partition list of a table.
---
mysql-test/r/partition.result | 37 +++++++++++++++++++++++++++++++++++++
mysql-test/t/partition.test | 35 +++++++++++++++++++++++++++++++++++
sql/opt_range.cc | 12 ++++++++++--
sql/partition_info.cc | 21 +++++++++++++++++++++
sql/partition_info.h | 1 +
sql/sql_statistics.cc | 6 ++++++
6 files changed, 110 insertions(+), 2 deletions(-)
diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index c6669176b3d..abfbc21dccb 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -2645,3 +2645,40 @@ Warnings:
Note 1517 Duplicate partition name p2
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
+#
+# MDEV-17032: Estimates are higher for partitions of a table with @@use_stat_tables= PREFERABLY
+#
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k(a int);
+insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
+create table t1 (
+part_key int,
+a int,
+b int
+) partition by list(part_key) (
+partition p0 values in (0),
+partition p1 values in (1),
+partition p2 values in (2),
+partition p3 values in (3),
+partition p4 values in (4)
+);
+insert into t1
+select mod(a,5), a/100, a from one_k;
+set @save_use_stat_tables= @@use_stat_tables;
+set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set @@use_stat_tables= PREFERABLY;
+set @@optimizer_use_condition_selectivity=4;
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+explain select * from t1 where part_key in (1,2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 400 Using where
+explain select * from t1 where part_key > 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 600 Using where
+set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set @@use_stat_tables= @save_use_stat_tables;
+drop table t1, one_k, ten;
diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test
index 1c8cd0375d6..7a5ac925491 100644
--- a/mysql-test/t/partition.test
+++ b/mysql-test/t/partition.test
@@ -2897,3 +2897,38 @@ EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
+--echo #
+--echo # MDEV-17032: Estimates are higher for partitions of a table with @@use_stat_tables= PREFERABLY
+--echo #
+
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table one_k(a int);
+insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
+
+
+create table t1 (
+ part_key int,
+ a int,
+ b int
+) partition by list(part_key) (
+ partition p0 values in (0),
+ partition p1 values in (1),
+ partition p2 values in (2),
+ partition p3 values in (3),
+ partition p4 values in (4)
+);
+insert into t1
+select mod(a,5), a/100, a from one_k;
+
+set @save_use_stat_tables= @@use_stat_tables;
+set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set @@use_stat_tables= PREFERABLY;
+set @@optimizer_use_condition_selectivity=4;
+analyze table t1;
+explain select * from t1 where part_key in (1,2);
+explain select * from t1 where part_key > 1;
+set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set @@use_stat_tables= @save_use_stat_tables;
+drop table t1, one_k, ten;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 3bcaa72e32f..993b7d57e0a 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -3322,6 +3322,10 @@ bool create_key_parts_for_pseudo_indexes(RANGE_OPT_PARAM *param,
{
Field **field_ptr;
TABLE *table= param->table;
+ partition_info *part_info= NULL;
+ #ifdef WITH_PARTITION_STORAGE_ENGINE
+ part_info= table->part_info;
+ #endif
uint parts= 0;
for (field_ptr= table->field; *field_ptr; field_ptr++)
@@ -3329,7 +3333,9 @@ bool create_key_parts_for_pseudo_indexes(RANGE_OPT_PARAM *param,
Column_statistics* col_stats= (*field_ptr)->read_stats;
if (bitmap_is_set(used_fields, (*field_ptr)->field_index)
&& col_stats && !col_stats->no_stat_values_provided()
- && !((*field_ptr)->type() == MYSQL_TYPE_GEOMETRY))
+ && !((*field_ptr)->type() == MYSQL_TYPE_GEOMETRY)
+ && (!part_info ||
+ part_info->disable_eits_for_partitioning_columns(*field_ptr)))
parts++;
}
@@ -3350,7 +3356,9 @@ bool create_key_parts_for_pseudo_indexes(RANGE_OPT_PARAM *param,
if (bitmap_is_set(used_fields, (*field_ptr)->field_index))
{
Field *field= *field_ptr;
- if (field->type() == MYSQL_TYPE_GEOMETRY)
+ if (field->type() == MYSQL_TYPE_GEOMETRY
+ && (!part_info ||
+ part_info->disable_eits_for_partitioning_columns(*field_ptr)))
continue;
uint16 store_length;
diff --git a/sql/partition_info.cc b/sql/partition_info.cc
index 52bda560c1c..91d74b28d0e 100644
--- a/sql/partition_info.cc
+++ b/sql/partition_info.cc
@@ -3164,6 +3164,27 @@ void partition_info::print_debug(const char *str, uint *value)
DBUG_PRINT("info", ("parser: %s", str));
DBUG_VOID_RETURN;
}
+
+/*
+
+ Disabling reading EITS statistics for columns involved in the
+ partition list of a table.
+ We assume the selecticivity for such columns would be handled
+ during partition pruning.
+
+*/
+
+bool partition_info::disable_eits_for_partitioning_columns(Field *field)
+{
+ uint i;
+ for (i= 0; i < num_part_fields; i++)
+ {
+ if (field->eq(part_field_array[i]))
+ return FALSE;
+ }
+ return TRUE;
+}
+
#else /* WITH_PARTITION_STORAGE_ENGINE */
/*
For builds without partitioning we need to define these functions
diff --git a/sql/partition_info.h b/sql/partition_info.h
index f250c5496bf..6aff636ae01 100644
--- a/sql/partition_info.h
+++ b/sql/partition_info.h
@@ -384,6 +384,7 @@ class partition_info : public Sql_alloc
bool is_full_part_expr_in_fields(List<Item> &fields);
public:
bool has_unique_name(partition_element *element);
+ bool disable_eits_for_partitioning_columns(Field *field);
};
uint32 get_next_partition_id_range(struct st_partition_iter* part_iter);
diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc
index cb75a5c2176..33a12588434 100644
--- a/sql/sql_statistics.cc
+++ b/sql/sql_statistics.cc
@@ -3589,6 +3589,12 @@ void set_statistics_for_table(THD *thd, TABLE *table)
(use_stat_table_mode <= COMPLEMENTARY ||
!table->stats_is_read || read_stats->cardinality_is_null) ?
table->file->stats.records : read_stats->cardinality;
+
+ #ifdef WITH_PARTITION_STORAGE_ENGINE
+ if (table->part_info)
+ table->used_stat_records= table->file->stats.records;
+ #endif
+
KEY *key_info, *key_info_end;
for (key_info= table->key_info, key_info_end= key_info+table->s->keys;
key_info < key_info_end; key_info++)
2
1
[Commits] a9c1420233f: MDEV-17589: Stack-buffer-overflow with indexed varchar (utf8) field
by varunraiko1803＠gmail.com 14 Nov '18
by varunraiko1803＠gmail.com 14 Nov '18
14 Nov '18
revision-id: a9c1420233f3f65aee00a8e7bdd1c8481b0bbc37 (mariadb-10.2.18-74-ga9c1420233f)
parent(s): b290ef8c76e2d7dfbae7a85766694a6fd4648eac
author: Varun Gupta
committer: Varun Gupta
timestamp: 2018-11-12 16:25:25 +0530
message:
MDEV-17589: Stack-buffer-overflow with indexed varchar (utf8) field
Create a new constant MAX_DATA_LENGTH_FOR_KEY.
Replace the value of MAX_KEY_LENGTH to also include the LENGTH and NULL BYTES.
---
mysql-test/r/func_group_innodb.result | 23 +++++++++++++++++++++++
mysql-test/t/func_group_innodb.test | 18 ++++++++++++++++++
sql/handler.h | 12 +++++++++---
sql/partition_info.cc | 4 ++--
sql/sql_const.h | 13 ++++++++++++-
sql/table.cc | 2 +-
6 files changed, 65 insertions(+), 7 deletions(-)
diff --git a/mysql-test/r/func_group_innodb.result b/mysql-test/r/func_group_innodb.result
index 52d5922df95..c2bfe9bf81c 100644
--- a/mysql-test/r/func_group_innodb.result
+++ b/mysql-test/r/func_group_innodb.result
@@ -246,4 +246,27 @@ EXPLAIN SELECT MIN(c) FROM t1 GROUP BY b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range NULL b 263 NULL 3 Using index for group-by
DROP TABLE t1;
+#
+# MDEV-17589: Stack-buffer-overflow with indexed varchar (utf8) field
+#
+CREATE TABLE t1 (v1 varchar(1020), v2 varchar(2), v3 varchar(2), KEY k1 (v3,v2,v1)) ENGINE=InnoDB CHARACTER SET=utf8;
+INSERT INTO t1 VALUES ('king', 'qu','qu'), ('bad','go','go');
+explain
+SELECT MIN(t1.v1) FROM t1 where t1.v2='qu' and t1.v3='qu';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+SELECT MIN(t1.v1) FROM t1 where t1.v2='qu' and t1.v3='qu';
+MIN(t1.v1)
+king
+drop table t1;
+CREATE TABLE t1 (v1 varchar(1024) CHARACTER SET utf8, KEY v1 (v1)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('king'), ('bad');
+explain
+SELECT MIN(x.v1) FROM (SELECT t1.* FROM t1 WHERE t1.v1 >= 'p') x;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No matching min/max row
+SELECT MIN(x.v1) FROM (SELECT t1.* FROM t1 WHERE t1.v1 >= 'p') x;
+MIN(x.v1)
+NULL
+drop table t1;
End of 5.5 tests
diff --git a/mysql-test/t/func_group_innodb.test b/mysql-test/t/func_group_innodb.test
index c62d3d08496..0d24f37363b 100644
--- a/mysql-test/t/func_group_innodb.test
+++ b/mysql-test/t/func_group_innodb.test
@@ -192,4 +192,22 @@ EXPLAIN SELECT MIN(c) FROM t1 GROUP BY b;
DROP TABLE t1;
+--echo #
+--echo # MDEV-17589: Stack-buffer-overflow with indexed varchar (utf8) field
+--echo #
+
+CREATE TABLE t1 (v1 varchar(1020), v2 varchar(2), v3 varchar(2), KEY k1 (v3,v2,v1)) ENGINE=InnoDB CHARACTER SET=utf8;
+INSERT INTO t1 VALUES ('king', 'qu','qu'), ('bad','go','go');
+explain
+SELECT MIN(t1.v1) FROM t1 where t1.v2='qu' and t1.v3='qu';
+SELECT MIN(t1.v1) FROM t1 where t1.v2='qu' and t1.v3='qu';
+drop table t1;
+
+CREATE TABLE t1 (v1 varchar(1024) CHARACTER SET utf8, KEY v1 (v1)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('king'), ('bad');
+explain
+SELECT MIN(x.v1) FROM (SELECT t1.* FROM t1 WHERE t1.v1 >= 'p') x;
+SELECT MIN(x.v1) FROM (SELECT t1.* FROM t1 WHERE t1.v1 >= 'p') x;
+drop table t1;
+
--echo End of 5.5 tests
diff --git a/sql/handler.h b/sql/handler.h
index ed2ef822c88..6745312a17d 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -378,6 +378,12 @@ enum enum_alter_inplace_result {
#define HA_KEY_NULL_LENGTH 1
#define HA_KEY_BLOB_LENGTH 2
+/* Maximum length of any index lookup key, in bytes */
+
+#define MAX_KEY_LENGTH (MAX_DATA_LENGTH_FOR_KEY \
+ +(MAX_REF_PARTS \
+ *(HA_KEY_NULL_LENGTH + HA_KEY_BLOB_LENGTH)))
+
#define HA_LEX_CREATE_TMP_TABLE 1U
#define HA_CREATE_TMP_ALTER 8U
@@ -3421,14 +3427,14 @@ class handler :public Sql_alloc
uint max_key_parts() const
{ return MY_MIN(MAX_REF_PARTS, max_supported_key_parts()); }
uint max_key_length() const
- { return MY_MIN(MAX_KEY_LENGTH, max_supported_key_length()); }
+ { return MY_MIN(MAX_DATA_LENGTH_FOR_KEY, max_supported_key_length()); }
uint max_key_part_length() const
- { return MY_MIN(MAX_KEY_LENGTH, max_supported_key_part_length()); }
+ { return MY_MIN(MAX_DATA_LENGTH_FOR_KEY, max_supported_key_part_length()); }
virtual uint max_supported_record_length() const { return HA_MAX_REC_LENGTH; }
virtual uint max_supported_keys() const { return 0; }
virtual uint max_supported_key_parts() const { return MAX_REF_PARTS; }
- virtual uint max_supported_key_length() const { return MAX_KEY_LENGTH; }
+ virtual uint max_supported_key_length() const { return MAX_DATA_LENGTH_FOR_KEY; }
virtual uint max_supported_key_part_length() const { return 255; }
virtual uint min_record_length(uint options) const { return 1; }
diff --git a/sql/partition_info.cc b/sql/partition_info.cc
index f96882552fa..8be3f878f8d 100644
--- a/sql/partition_info.cc
+++ b/sql/partition_info.cc
@@ -1713,12 +1713,12 @@ bool partition_info::check_partition_field_length()
for (i= 0; i < num_part_fields; i++)
store_length+= get_partition_field_store_length(part_field_array[i]);
- if (store_length > MAX_KEY_LENGTH)
+ if (store_length > MAX_DATA_LENGTH_FOR_KEY)
DBUG_RETURN(TRUE);
store_length= 0;
for (i= 0; i < num_subpart_fields; i++)
store_length+= get_partition_field_store_length(subpart_field_array[i]);
- if (store_length > MAX_KEY_LENGTH)
+ if (store_length > MAX_DATA_LENGTH_FOR_KEY)
DBUG_RETURN(TRUE);
DBUG_RETURN(FALSE);
}
diff --git a/sql/sql_const.h b/sql/sql_const.h
index 7395ae3c08a..0c781855302 100644
--- a/sql/sql_const.h
+++ b/sql/sql_const.h
@@ -33,7 +33,18 @@
#define MAX_SYS_VAR_LENGTH 32
#define MAX_KEY MAX_INDEXES /* Max used keys */
#define MAX_REF_PARTS 32 /* Max parts used as ref */
-#define MAX_KEY_LENGTH 3072 /* max possible key */
+
+/*
+ Maximum length of the data part of an index lookup key.
+
+ The "data part" is defined as the value itself, not including the
+ NULL-indicator bytes or varchar length bytes ("the Extras"). We need this
+ value because there was a bug where length of the Extras were not counted.
+
+ You probably need MAX_KEY_LENGTH, not this constant.
+*/
+
+#define MAX_DATA_LENGTH_FOR_KEY 3072
#if SIZEOF_OFF_T > 4
#define MAX_REFLENGTH 8 /* Max length for record ref */
#else
diff --git a/sql/table.cc b/sql/table.cc
index 2b278288784..5b163d501d1 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -2169,7 +2169,7 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
uint pk_part_length= key_first_info->key_part[i].store_length;
if (keyinfo->ext_key_part_map & 1<<i)
{
- if (ext_key_length + pk_part_length > MAX_KEY_LENGTH)
+ if (ext_key_length + pk_part_length > MAX_DATA_LENGTH_FOR_KEY)
{
add_keyparts_for_this_key= i;
break;
2
1
revision-id: 5421e1c42972cf80d6a6bdc7eba7f5c5a5dbb04e (mariadb-10.3.6-189-g5421e1c4297)
parent(s): 2b8888bed528b97b38580a6bcd8fb335e97e545a
author: Jan Lindström
committer: Jan Lindström
timestamp: 2018-11-14 11:27:28 +0200
message:
Fix compiler warning.
---
include/wsrep.h | 5 -----
sql/sql_parse.cc | 2 +-
2 files changed, 1 insertion(+), 6 deletions(-)
diff --git a/include/wsrep.h b/include/wsrep.h
index ce085ababb4..03dd8f4fd0e 100644
--- a/include/wsrep.h
+++ b/include/wsrep.h
@@ -47,11 +47,6 @@
if (WSREP(thd) && !thd->lex->no_write_to_binlog \
&& wsrep_to_isolation_begin(thd, db_, table_, table_list_)) goto wsrep_error_label;
-#define WSREP_SYNC_WAIT(thd_, before_) \
- { if (WSREP_CLIENT(thd_) && \
- wsrep_sync_wait(thd_, before_)) goto error; }
-
-
#define WSREP_DEBUG(...) \
if (wsrep_debug) WSREP_LOG(sql_print_information, ##__VA_ARGS__)
#define WSREP_INFO(...) WSREP_LOG(sql_print_information, ##__VA_ARGS__)
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index cde1af33d82..3de97c8d9d8 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -6653,7 +6653,7 @@ static bool execute_show_status(THD *thd, TABLE_LIST *all_tables)
mysql_mutex_unlock(&LOCK_status);
return res;
#ifdef WITH_WSREP
- error: /* see WSREP_SYNC_WAIT() macro above */
+wsrep_error_label: /* see WSREP_SYNC_WAIT() macro above */
return true;
#endif /* WITH_WSREP */
}
1
0
revision-id: c1bc173b6fa0e50134f52b84fa184e00b65fc748 (mariadb-10.1.37-8-gc1bc173b6fa)
parent(s): 9c46adbb7e093029c56dddd5dbbef407db4e8160
author: Jan Lindström
committer: Jan Lindström
timestamp: 2018-11-14 10:12:24 +0200
message:
After review fixes.
---
.../encryption/r/innodb-checksum-algorithm.result | 2 +-
.../encryption/r/innodb-compressed-blob.result | 2 --
.../encryption/r/innodb-encryption-alter.result | 4 +--
.../r/innodb_encryption_default_key.result | 19 ++++++++++++--
.../encryption/t/innodb-checksum-algorithm.test | 6 +----
.../encryption/t/innodb-encryption-alter.test | 3 +--
.../t/innodb_encryption_default_key.test | 15 ++++++++++-
storage/innobase/fil/fil0crypt.cc | 2 +-
storage/innobase/handler/ha_innodb.cc | 29 +++++++++++-----------
storage/innobase/include/ha_prototypes.h | 6 ++---
storage/xtradb/fil/fil0crypt.cc | 2 +-
storage/xtradb/handler/ha_innodb.cc | 29 +++++++++++-----------
storage/xtradb/include/ha_prototypes.h | 6 ++---
13 files changed, 72 insertions(+), 53 deletions(-)
diff --git a/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result b/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result
index b3deac98e3c..a853f3869a9 100644
--- a/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result
+++ b/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result
@@ -10,7 +10,7 @@ SET GLOBAL innodb_encrypt_tables = ON;
SET GLOBAL innodb_encryption_threads = 4;
call mtr.add_suppression("InnoDB: innodb_checksum_algorithm is set to \"strict_(crc32|none|innodb)\" but the page \\[page id: space=[0-9]+, page number=[0-9]+\\] contains a valid checksum \"(innodb|none|crc32)\"");
SET GLOBAL innodb_checksum_algorithm = innodb;
-SET GLOBAL innodb_default_encryption_key_id=1;
+SET GLOBAL innodb_default_encryption_key_id=4;
SET GLOBAL innodb_checksum_algorithm=crc32;
create table tce_crc32(a serial, b blob, index(b(10))) engine=innodb
ROW_FORMAT=COMPRESSED encrypted=yes;
diff --git a/mysql-test/suite/encryption/r/innodb-compressed-blob.result b/mysql-test/suite/encryption/r/innodb-compressed-blob.result
index a6f4856fdbb..ce73b80820f 100644
--- a/mysql-test/suite/encryption/r/innodb-compressed-blob.result
+++ b/mysql-test/suite/encryption/r/innodb-compressed-blob.result
@@ -7,8 +7,6 @@ set GLOBAL innodb_default_encryption_key_id=4;
create table t1(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed;
create table t2(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed encrypted=yes;
create table t3(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed encrypted=no;
-Warnings:
-Warning 140 InnoDB: Ignored ENCRYPTION_KEY_ID 4 when encryption is disabled
insert into t1 values (1, repeat('secret',6000));
insert into t2 values (1, repeat('secret',6000));
insert into t3 values (1, repeat('secret',6000));
diff --git a/mysql-test/suite/encryption/r/innodb-encryption-alter.result b/mysql-test/suite/encryption/r/innodb-encryption-alter.result
index 381f14c9e9e..e1ff9eacfbe 100644
--- a/mysql-test/suite/encryption/r/innodb-encryption-alter.result
+++ b/mysql-test/suite/encryption/r/innodb-encryption-alter.result
@@ -4,7 +4,7 @@ SET GLOBAL innodb_encrypt_tables = ON;
SET GLOBAL innodb_encryption_threads = 4;
CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=NO ENCRYPTION_KEY_ID=4;
Warnings:
-Warning 140 InnoDB: Ignored ENCRYPTION_KEY_ID 4 when encryption is disabled
+Warning 140 InnoDB: Ignored ENCRYPTION_KEY_ID=4 when encryption is disabled
DROP TABLE t1;
set innodb_default_encryption_key_id = 99;
ERROR 42000: Variable 'innodb_default_encryption_key_id' can't be set to the value of '99'
@@ -44,6 +44,7 @@ t1 CREATE TABLE `t1` (
`c` varchar(256) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTION_KEY_ID`=4
+set innodb_default_encryption_key_id = 1;
CREATE TABLE t2 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=NO ENCRYPTION_KEY_ID=1;
ALTER TABLE t1 ENCRYPTION_KEY_ID=99;
ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID'
@@ -51,7 +52,6 @@ SHOW WARNINGS;
Level Code Message
Warning 140 InnoDB: ENCRYPTION_KEY_ID 99 not available
Error 1478 Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID'
-set innodb_default_encryption_key_id = 1;
drop table t1,t2;
SET GLOBAL innodb_encrypt_tables=OFF;
CREATE TABLE t1 (a int not null primary key) engine=innodb;
diff --git a/mysql-test/suite/encryption/r/innodb_encryption_default_key.result b/mysql-test/suite/encryption/r/innodb_encryption_default_key.result
index 13754df0d8b..6ad1f621cb7 100644
--- a/mysql-test/suite/encryption/r/innodb_encryption_default_key.result
+++ b/mysql-test/suite/encryption/r/innodb_encryption_default_key.result
@@ -1,15 +1,30 @@
-call mtr.add_suppression("InnoDB: cannot use encryption as default encryption key_id 999 not found from encryption plugin.");
+call mtr.add_suppression("innodb_default_encryption_key_id=999 is unavailable in the encryption plugin");
call mtr.add_suppression("Plugin 'InnoDB' init function returned error.");
call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed.");
SET GLOBAL innodb_file_format = `Barracuda`;
SET GLOBAL innodb_file_per_table = ON;
create table t1 (a int not null primary key) engine=InnoDB;
create table t2 (a int not null primary key) encrypted=yes engine=InnoDB;
+SET GLOBAL innodb_default_encryption_key_id = -1;
+ERROR 42000: Variable 'innodb_default_encryption_key_id' can't be set to the value of '-1'
+SHOW WARNINGS;
+Level Code Message
+Error 1231 Variable 'innodb_default_encryption_key_id' can't be set to the value of '-1'
+SET GLOBAL innodb_default_encryption_key_id = 4294967296;
+ERROR 42000: Variable 'innodb_default_encryption_key_id' can't be set to the value of '4294967296'
+SHOW WARNINGS;
+Level Code Message
+Error 1231 Variable 'innodb_default_encryption_key_id' can't be set to the value of '4294967296'
+SET GLOBAL innodb_default_encryption_key_id = 'k';
+ERROR 42000: Incorrect argument type to variable 'innodb_default_encryption_key_id'
+SHOW WARNINGS;
+Level Code Message
+Error 1232 Incorrect argument type to variable 'innodb_default_encryption_key_id'
SET GLOBAL innodb_default_encryption_key_id = 999;
ERROR 42000: Variable 'innodb_default_encryption_key_id' can't be set to the value of '999'
SHOW WARNINGS;
Level Code Message
-Warning 1210 InnoDB: innodb_default_encryption_key_id=999 not available in encryption plugin
+Warning 1210 innodb_default_encryption_key_id=999 not available in the encryption plugin
Error 1231 Variable 'innodb_default_encryption_key_id' can't be set to the value of '999'
SET GLOBAL innodb_default_encryption_key_id = 4;
SET GLOBAL innodb_encryption_threads = 4;
diff --git a/mysql-test/suite/encryption/t/innodb-checksum-algorithm.test b/mysql-test/suite/encryption/t/innodb-checksum-algorithm.test
index 21ff07b5837..d0caed05006 100644
--- a/mysql-test/suite/encryption/t/innodb-checksum-algorithm.test
+++ b/mysql-test/suite/encryption/t/innodb-checksum-algorithm.test
@@ -16,11 +16,7 @@ SET GLOBAL innodb_encryption_threads = 4;
call mtr.add_suppression("InnoDB: innodb_checksum_algorithm is set to \"strict_(crc32|none|innodb)\" but the page \\[page id: space=[0-9]+, page number=[0-9]+\\] contains a valid checksum \"(innodb|none|crc32)\"");
SET GLOBAL innodb_checksum_algorithm = innodb;
-#
-# Need to use default encryption key_id as below creating tables
-# with encrypted=no where nondefault key_id is not allowed.
-#
-SET GLOBAL innodb_default_encryption_key_id=1;
+SET GLOBAL innodb_default_encryption_key_id=4;
let MYSQLD_DATADIR =`SELECT @@datadir`;
diff --git a/mysql-test/suite/encryption/t/innodb-encryption-alter.test b/mysql-test/suite/encryption/t/innodb-encryption-alter.test
index d79bdecfc1a..fdd2dd86805 100644
--- a/mysql-test/suite/encryption/t/innodb-encryption-alter.test
+++ b/mysql-test/suite/encryption/t/innodb-encryption-alter.test
@@ -33,12 +33,11 @@ SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB;
SHOW CREATE TABLE t1;
+set innodb_default_encryption_key_id = 1;
CREATE TABLE t2 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=NO ENCRYPTION_KEY_ID=1;
--error ER_ILLEGAL_HA_CREATE_OPTION
ALTER TABLE t1 ENCRYPTION_KEY_ID=99;
SHOW WARNINGS;
-set innodb_default_encryption_key_id = 1;
-
--disable_warnings
--disable_query_log
diff --git a/mysql-test/suite/encryption/t/innodb_encryption_default_key.test b/mysql-test/suite/encryption/t/innodb_encryption_default_key.test
index 61a9f973d20..0f077707f98 100644
--- a/mysql-test/suite/encryption/t/innodb_encryption_default_key.test
+++ b/mysql-test/suite/encryption/t/innodb_encryption_default_key.test
@@ -2,7 +2,7 @@
-- source include/have_file_key_management_plugin.inc
-- source include/not_embedded.inc
-call mtr.add_suppression("InnoDB: cannot use encryption as default encryption key_id 999 not found from encryption plugin.");
+call mtr.add_suppression("innodb_default_encryption_key_id=999 is unavailable in the encryption plugin");
call mtr.add_suppression("Plugin 'InnoDB' init function returned error.");
call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed.");
@@ -19,6 +19,19 @@ SET GLOBAL innodb_file_per_table = ON;
create table t1 (a int not null primary key) engine=InnoDB;
create table t2 (a int not null primary key) encrypted=yes engine=InnoDB;
+#
+# Test limits
+#
+--error ER_WRONG_VALUE_FOR_VAR
+SET GLOBAL innodb_default_encryption_key_id = -1;
+SHOW WARNINGS;
+--error ER_WRONG_VALUE_FOR_VAR
+SET GLOBAL innodb_default_encryption_key_id = 4294967296;
+SHOW WARNINGS;
+--error ER_WRONG_TYPE_FOR_VAR
+SET GLOBAL innodb_default_encryption_key_id = 'k';
+SHOW WARNINGS;
+
# Do not allow setting default key to key_id that is not found
--error ER_WRONG_VALUE_FOR_VAR
SET GLOBAL innodb_default_encryption_key_id = 999;
diff --git a/storage/innobase/fil/fil0crypt.cc b/storage/innobase/fil/fil0crypt.cc
index c618a520b40..3e66bfee884 100644
--- a/storage/innobase/fil/fil0crypt.cc
+++ b/storage/innobase/fil/fil0crypt.cc
@@ -1065,7 +1065,7 @@ fil_crypt_start_encrypting_space(
/* 1 - create crypt data using default encryption key_id */
crypt_data = fil_space_create_crypt_data(FIL_ENCRYPTION_DEFAULT,
- thd_default_encryption_key_id());
+ innodb_default_encryption_key_id());
if (crypt_data == NULL) {
mutex_exit(&fil_crypt_threads_mutex);
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 0d9beb452d9..a22a287ec67 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -706,7 +706,12 @@ innodb_default_encryption_key_id_validate(
if (value->val_int(value, &key_id_buf)) {
/* The value is NULL. That is invalid. */
- return(1);
+ return 1;
+ }
+
+ if (key_id_buf < 1 || key_id_buf > UINT_MAX32) {
+ /* Out of range */
+ return 1;
}
*reinterpret_cast<uint*>(save) = key_id = static_cast<uint>(key_id_buf);
@@ -718,12 +723,12 @@ innodb_default_encryption_key_id_validate(
push_warning_printf(
thd, Sql_condition::WARN_LEVEL_WARN,
ER_WRONG_ARGUMENTS,
- "InnoDB: innodb_default_encryption_key_id=%u not available in encryption plugin",
+ "innodb_default_encryption_key_id=%u not available in the encryption plugin",
key_id);
- return (1);
+ return 1;
}
- return(0);
+ return 0;
}
static MYSQL_THDVAR_UINT(default_encryption_key_id, PLUGIN_VAR_RQCMDARG,
@@ -732,10 +737,8 @@ static MYSQL_THDVAR_UINT(default_encryption_key_id, PLUGIN_VAR_RQCMDARG,
NULL,
FIL_DEFAULT_ENCRYPTION_KEY, 1, UINT_MAX32, 0);
-/** Get global default encryption key_id
-@return key_id */
-UNIV_INTERN
-uint thd_default_encryption_key_id(void)
+/** @return innodb_default_encryption_key_id */
+UNIV_INTERN uint innodb_default_encryption_key_id()
{
return(THDVAR(NULL, default_encryption_key_id));
}
@@ -3646,10 +3649,8 @@ innobase_init(
key_id = THDVAR(NULL, default_encryption_key_id);
if (key_id != FIL_DEFAULT_ENCRYPTION_KEY
&& !encryption_key_id_exists(key_id)) {
- sql_print_error("InnoDB: cannot use encryption as "
- " default encryption key_id %u"
- " not found from encryption plugin.",
- key_id);
+ sql_print_error("innodb_default_encryption_key_id=%u is "
+ "unavailable in the encryption plugin", key_id);
goto error;
}
@@ -12009,11 +12010,11 @@ ha_innobase::check_table_options(
/* Ignore nondefault key_id if encryption is set off */
if (encrypt == FIL_ENCRYPTION_OFF
- && options->encryption_key_id != FIL_DEFAULT_ENCRYPTION_KEY) {
+ && options->encryption_key_id != THDVAR(thd, default_encryption_key_id)) {
push_warning_printf(
thd, Sql_condition::WARN_LEVEL_WARN,
HA_WRONG_CREATE_OPTION,
- "InnoDB: Ignored ENCRYPTION_KEY_ID %u when encryption is disabled",
+ "InnoDB: Ignored ENCRYPTION_KEY_ID=%u when encryption is disabled",
(uint)options->encryption_key_id
);
options->encryption_key_id = FIL_DEFAULT_ENCRYPTION_KEY;
diff --git a/storage/innobase/include/ha_prototypes.h b/storage/innobase/include/ha_prototypes.h
index 06610dcc94b..84d5652ce64 100644
--- a/storage/innobase/include/ha_prototypes.h
+++ b/storage/innobase/include/ha_prototypes.h
@@ -348,10 +348,8 @@ thd_supports_xa(
THD* thd); /*!< in: thread handle, or NULL to query
the global innodb_supports_xa */
-/** Get global default encryption key_id
-@return key_id */
-UNIV_INTERN
-uint thd_default_encryption_key_id(void);
+/** @return innodb_default_encryption_key_id */
+UNIV_INTERN uint innodb_default_encryption_key_id();
/** Get status of innodb_tmpdir.
@param[in] thd thread handle, or NULL to query
diff --git a/storage/xtradb/fil/fil0crypt.cc b/storage/xtradb/fil/fil0crypt.cc
index c618a520b40..3e66bfee884 100644
--- a/storage/xtradb/fil/fil0crypt.cc
+++ b/storage/xtradb/fil/fil0crypt.cc
@@ -1065,7 +1065,7 @@ fil_crypt_start_encrypting_space(
/* 1 - create crypt data using default encryption key_id */
crypt_data = fil_space_create_crypt_data(FIL_ENCRYPTION_DEFAULT,
- thd_default_encryption_key_id());
+ innodb_default_encryption_key_id());
if (crypt_data == NULL) {
mutex_exit(&fil_crypt_threads_mutex);
diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc
index 704e3478195..2fdd03fbb6e 100644
--- a/storage/xtradb/handler/ha_innodb.cc
+++ b/storage/xtradb/handler/ha_innodb.cc
@@ -707,7 +707,12 @@ innodb_default_encryption_key_id_validate(
if (value->val_int(value, &key_id_buf)) {
/* The value is NULL. That is invalid. */
- return(1);
+ return 1;
+ }
+
+ if (key_id_buf < 1 || key_id_buf > UINT_MAX32) {
+ /* Out of range */
+ return 1;
}
*reinterpret_cast<uint*>(save) = key_id = static_cast<uint>(key_id_buf);
@@ -719,12 +724,12 @@ innodb_default_encryption_key_id_validate(
push_warning_printf(
thd, Sql_condition::WARN_LEVEL_WARN,
ER_WRONG_ARGUMENTS,
- "InnoDB: innodb_default_encryption_key_id=%u not available in encryption plugin",
+ "innodb_default_encryption_key_id=%u not available in the encryption plugin",
key_id);
- return (1);
+ return 1;
}
- return(0);
+ return 0;
}
static MYSQL_THDVAR_UINT(default_encryption_key_id, PLUGIN_VAR_RQCMDARG,
@@ -733,10 +738,8 @@ static MYSQL_THDVAR_UINT(default_encryption_key_id, PLUGIN_VAR_RQCMDARG,
NULL,
FIL_DEFAULT_ENCRYPTION_KEY, 1, UINT_MAX32, 0);
-/** Get global default encryption key_id
-@return key_id */
-UNIV_INTERN
-uint thd_default_encryption_key_id(void)
+/** @return innodb_default_encryption_key_id */
+UNIV_INTERN uint innodb_default_encryption_key_id()
{
return(THDVAR(NULL, default_encryption_key_id));
}
@@ -4096,10 +4099,8 @@ innobase_init(
key_id = THDVAR(NULL, default_encryption_key_id);
if (key_id != FIL_DEFAULT_ENCRYPTION_KEY
&& !encryption_key_id_exists(key_id)) {
- sql_print_error("InnoDB: cannot use encryption as "
- " default encryption key_id %u"
- " not found from encryption plugin.",
- key_id);
+ sql_print_error("innodb_default_encryption_key_id=%u is "
+ "unavailable in the encryption plugin", key_id);
goto error;
}
@@ -12576,11 +12577,11 @@ ha_innobase::check_table_options(
/* Ignore nondefault key_id if encryption is set off */
if (encrypt == FIL_ENCRYPTION_OFF
- && options->encryption_key_id != FIL_DEFAULT_ENCRYPTION_KEY) {
+ && options->encryption_key_id != THDVAR(thd, default_encryption_key_id)) {
push_warning_printf(
thd, Sql_condition::WARN_LEVEL_WARN,
HA_WRONG_CREATE_OPTION,
- "InnoDB: Ignored ENCRYPTION_KEY_ID %u when encryption is disabled",
+ "InnoDB: Ignored ENCRYPTION_KEY_ID=%u when encryption is disabled",
(uint)options->encryption_key_id
);
options->encryption_key_id = FIL_DEFAULT_ENCRYPTION_KEY;
diff --git a/storage/xtradb/include/ha_prototypes.h b/storage/xtradb/include/ha_prototypes.h
index ef91484817a..60b36440a8b 100644
--- a/storage/xtradb/include/ha_prototypes.h
+++ b/storage/xtradb/include/ha_prototypes.h
@@ -366,10 +366,8 @@ thd_supports_xa(
THD* thd); /*!< in: thread handle, or NULL to query
the global innodb_supports_xa */
-/** Get global default encryption key_id
-@return key_id */
-UNIV_INTERN
-uint thd_default_encryption_key_id(void);
+/** @return innodb_default_encryption_key_id */
+UNIV_INTERN uint innodb_default_encryption_key_id();
/** Get status of innodb_tmpdir.
@param[in] thd thread handle, or NULL to query
1
0
[Commits] 8328cf09425: MDEV-13170: Database service (MySQL) stops after update with trigger
by varunraiko1803＠gmail.com 14 Nov '18
by varunraiko1803＠gmail.com 14 Nov '18
14 Nov '18
revision-id: 8328cf09425bae0c10ebfc0eea68b5cbbb0e1870 (mariadb-10.2.18-75-g8328cf09425)
parent(s): a9c1420233f3f65aee00a8e7bdd1c8481b0bbc37
author: Varun Gupta
committer: Varun Gupta
timestamp: 2018-11-14 13:04:25 +0530
message:
MDEV-13170: Database service (MySQL) stops after update with trigger
For prepare statemtent/stored procedures we rollback the items to original
ones after prepare execution in the function reinit_stmt_before_use.
This rollback is done for group by, order by clauses but is not done for
the window specification containing the order by and partition by clause of the
window function.
---
mysql-test/r/win.result | 34 ++++++++++++++++++++++++++++++++++
mysql-test/t/win.test | 41 +++++++++++++++++++++++++++++++++++++++++
sql/sql_prepare.cc | 12 ++++++++++++
3 files changed, 87 insertions(+)
diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index c539ac4f252..4ffa9f34c1d 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -3423,3 +3423,37 @@ GROUP BY LEFT('2018-08-24', 100) having 1=1 limit 0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Zero limit
drop table t1;
+#
+# MDEV-13170: Database service (MySQL) stops after update with trigger
+#
+CREATE TABLE t1 ( t1_id int, point_id int, ml_id int, UNIQUE KEY t1_ml_u (ml_id,point_id)) ;
+INSERT INTO t1 VALUES (1,1,8884),(2,1,8885);
+CREATE TABLE t2 ( db_time datetime, au_nr int, col_id int, new_val int);
+CREATE TABLE t3 (id1 int, id2 int, d1 int);
+CREATE TRIGGER t1_aurtrg AFTER UPDATE ON t1 FOR EACH ROW begin
+CREATE OR REPLACE TEMPORARY TABLE trg_u AS
+WITH l AS
+(SELECT a.*,
+Max(t2.col_id) over (PARTITION BY a.d1),
+Max(t2.new_val) over (PARTITION BY a.d1)
+FROM
+(SELECT d1 , id1, id2 FROM t3) a
+JOIN t2 ON (a.d1=t2.db_time AND a.id1=t2.au_nr))
+SELECT 1;
+END;//
+update t1 set ml_id=8884 where point_id=1;
+ERROR 23000: Duplicate entry '8884-1' for key 't1_ml_u'
+update t1 set ml_id=8884 where point_id=1;
+ERROR 23000: Duplicate entry '8884-1' for key 't1_ml_u'
+drop table t1, t2,t3;
+CREATE TABLE t1 (i INT, a char);
+INSERT INTO t1 VALUES (1, 'a'),(2, 'b');
+create view v1 as select * from t1;
+PREPARE stmt FROM "SELECT i, row_number() over (partition by i order by i) FROM v1";
+execute stmt;
+i row_number() over (partition by i order by i)
+1 1
+2 1
+deallocate prepare stmt;
+drop table t1;
+drop view v1;
diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test
index 7dda2b6215f..807d394edd3 100644
--- a/mysql-test/t/win.test
+++ b/mysql-test/t/win.test
@@ -2175,3 +2175,44 @@ explain
SELECT DISTINCT BIT_OR(100) OVER () FROM t1
GROUP BY LEFT('2018-08-24', 100) having 1=1 limit 0;
drop table t1;
+
+--echo #
+--echo # MDEV-13170: Database service (MySQL) stops after update with trigger
+--echo #
+
+CREATE TABLE t1 ( t1_id int, point_id int, ml_id int, UNIQUE KEY t1_ml_u (ml_id,point_id)) ;
+INSERT INTO t1 VALUES (1,1,8884),(2,1,8885);
+
+CREATE TABLE t2 ( db_time datetime, au_nr int, col_id int, new_val int);
+CREATE TABLE t3 (id1 int, id2 int, d1 int);
+
+delimiter //;
+
+CREATE TRIGGER t1_aurtrg AFTER UPDATE ON t1 FOR EACH ROW begin
+CREATE OR REPLACE TEMPORARY TABLE trg_u AS
+WITH l AS
+ (SELECT a.*,
+ Max(t2.col_id) over (PARTITION BY a.d1),
+ Max(t2.new_val) over (PARTITION BY a.d1)
+ FROM
+ (SELECT d1 , id1, id2 FROM t3) a
+ JOIN t2 ON (a.d1=t2.db_time AND a.id1=t2.au_nr))
+SELECT 1;
+
+ END;//
+
+ delimiter ;//
+--error 1062
+update t1 set ml_id=8884 where point_id=1;
+--error 1062
+update t1 set ml_id=8884 where point_id=1;
+drop table t1, t2,t3;
+
+CREATE TABLE t1 (i INT, a char);
+INSERT INTO t1 VALUES (1, 'a'),(2, 'b');
+create view v1 as select * from t1;
+PREPARE stmt FROM "SELECT i, row_number() over (partition by i order by i) FROM v1";
+execute stmt;
+deallocate prepare stmt;
+drop table t1;
+drop view v1;
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 65f7c85b4ec..c3156ce1d5c 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -2927,6 +2927,7 @@ void reinit_stmt_before_use(THD *thd, LEX *lex)
{
SELECT_LEX *sl= lex->all_selects_list;
DBUG_ENTER("reinit_stmt_before_use");
+ Window_spec *win_spec;
/*
We have to update "thd" pointer in LEX, all its units and in LEX::result,
@@ -2995,6 +2996,17 @@ void reinit_stmt_before_use(THD *thd, LEX *lex)
/* Fix ORDER list */
for (order= sl->order_list.first; order; order= order->next)
order->item= &order->item_ptr;
+ /* Fix window functions too */
+ List_iterator<Window_spec> it(sl->window_specs);
+
+ while ((win_spec= it++))
+ {
+ for (order= win_spec->partition_list->first; order; order= order->next)
+ order->item= &order->item_ptr;
+ for (order= win_spec->order_list->first; order; order= order->next)
+ order->item= &order->item_ptr;
+ }
+
{
#ifndef DBUG_OFF
bool res=
1
0
[Commits] 6aaa69f: MDEV-16188 Use in-memory PK filters built from range index scans
by IgorBabaev 14 Nov '18
by IgorBabaev 14 Nov '18
14 Nov '18
revision-id: 6aaa69f37360200c4807282c8df1b2c21c707d2d (mariadb-10.3.6-97-g6aaa69f)
parent(s): 5f46670bd09babbee75a24ac82eb4ade0706da66
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-11-13 22:08:29 -0800
message:
MDEV-16188 Use in-memory PK filters built from range index scans
Fixed bugs for phase 1.
Also added quick objects for filters.
---
mysql-test/main/index_merge_myisam.result | 2 +-
mysql-test/main/join_cache.result | 2 +-
mysql-test/main/join_nested_jcl6.result | 2 +-
mysql-test/main/join_outer_innodb.result | 8 +-
mysql-test/main/mdev13607.result | 30 +++---
mysql-test/main/mysql_client_test.result | 16 +--
mysql-test/main/mysqld--help.result | 7 +-
mysql-test/main/null_key.result | 2 +-
mysql-test/main/order_by.result | 2 +-
mysql-test/main/ps_1general.result | 4 +-
mysql-test/main/ps_2myisam.result | 2 +-
mysql-test/main/ps_3innodb.result | 2 +-
mysql-test/main/ps_4heap.result | 2 +-
mysql-test/main/ps_5merge.result | 4 +-
mysql-test/main/range.result | 10 +-
mysql-test/main/range_mrr_icp.result | 10 +-
mysql-test/main/rowid_filter.result | 151 ++++++++++++++++++++++++++++
mysql-test/main/select.result | 16 +--
mysql-test/main/select_jcl6.result | 16 +--
mysql-test/main/select_pkeycache.result | 16 +--
mysql-test/main/stat_tables.result | 2 +-
mysql-test/main/stat_tables_disabled.result | 6 +-
mysql-test/main/stat_tables_innodb.result | 4 +-
mysql-test/main/subselect2.result | 2 +-
sql/opt_subselect.h | 1 +
sql/rowid_filter.cc | 3 +-
sql/rowid_filter.h | 3 +-
sql/sql_class.cc | 2 +-
sql/sql_explain.cc | 42 ++++++--
sql/sql_explain.h | 12 ++-
sql/sql_select.cc | 107 ++++++++++++++++----
sql/sql_select.h | 1 +
tests/mysql_client_test.c | 4 +-
33 files changed, 373 insertions(+), 120 deletions(-)
diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result
index 725a888..b0c3f76 100644
--- a/mysql-test/main/index_merge_myisam.result
+++ b/mysql-test/main/index_merge_myisam.result
@@ -272,7 +272,7 @@ explain
select * from t0,t1 where (t0.key1=t1.key1) and
(t0.key1=3 or t0.key2<4) and t1.key1=2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 ref i1,i2 i1 4 const 1 Using where
+1 SIMPLE t0 ref|filter i1,i2 i1|i2 4|4 const 1 (1%) Using where; Using filter
1 SIMPLE t1 ref i1 i1 4 const 1
explain select * from t0,t1 where t0.key1 = 5 and
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result
index fde6e0f..46891e9 100644
--- a/mysql-test/main/join_cache.result
+++ b/mysql-test/main/join_cache.result
@@ -1213,7 +1213,7 @@ ON City.Country=Country.Code AND City.Population > 5000000
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Country range Name Name 52 NULL # Using index condition; Using where; Rowid-ordered scan
-1 SIMPLE City hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code # Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join)
+1 SIMPLE City hash_range|filter Population,Country #hash#Country:Population|Population 3:4|4 world.Country.Code # Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join); Using filter
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND City.Population > 5000000
diff --git a/mysql-test/main/join_nested_jcl6.result b/mysql-test/main/join_nested_jcl6.result
index 6b1901d..cbd459e 100644
--- a/mysql-test/main/join_nested_jcl6.result
+++ b/mysql-test/main/join_nested_jcl6.result
@@ -2002,7 +2002,7 @@ ON t6.b >= 2 AND t5.b=t7.b AND
(t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t5 ALL NULL NULL NULL NULL 3
-1 SIMPLE t7 ref PRIMARY,b_i b_i 5 test.t5.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE t7 ref|filter PRIMARY,b_i b_i|PRIMARY 5|4 test.t5.b 2 (43%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter
1 SIMPLE t6 ALL PRIMARY,b_i NULL NULL NULL 7 Using where; Using join buffer (incremental, BNL join)
1 SIMPLE t8 ref b_i b_i 5 test.t5.b 2 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result
index 6f3fb09..f00a723 100644
--- a/mysql-test/main/join_outer_innodb.result
+++ b/mysql-test/main/join_outer_innodb.result
@@ -444,9 +444,9 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1
1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where
1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where
-1 SIMPLE t13 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index
+1 SIMPLE t13 ref|filter PRIMARY,m3 PRIMARY|m3 4|4 test.t1.a1 1 (33%) Using where; Using index; Using filter
1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index
-1 SIMPLE m2 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index
+1 SIMPLE m2 ref|filter PRIMARY,m3 PRIMARY|m3 4|4 test.t1.a1 1 (33%) Using where; Using index; Using filter
1 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where
1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where
1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index
@@ -466,9 +466,9 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1
1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where
1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where
-1 SIMPLE t13 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index
+1 SIMPLE t13 ref|filter PRIMARY,m3 PRIMARY|m3 4|4 test.t1.a1 1 (33%) Using where; Using index; Using filter
1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index
-1 SIMPLE m2 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index
+1 SIMPLE m2 ref|filter PRIMARY,m3 PRIMARY|m3 4|4 test.t1.a1 1 (33%) Using where; Using index; Using filter
1 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where
1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where
1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index
diff --git a/mysql-test/main/mdev13607.result b/mysql-test/main/mdev13607.result
index 08848bc..0d573b0 100644
--- a/mysql-test/main/mdev13607.result
+++ b/mysql-test/main/mdev13607.result
@@ -76,21 +76,21 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join)
1 PRIMARY p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join)
1 PRIMARY p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join)
-1 PRIMARY <derived3> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join)
-1 PRIMARY <derived4> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join)
-1 PRIMARY <derived5> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join)
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join)
-1 PRIMARY <derived7> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join)
-1 PRIMARY <derived8> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join)
-1 PRIMARY <derived9> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join)
-1 PRIMARY <derived10> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join)
-1 PRIMARY <derived11> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join)
-1 PRIMARY <derived12> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join)
-1 PRIMARY <derived13> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join)
-1 PRIMARY <derived14> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join)
-1 PRIMARY <derived15> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join)
-1 PRIMARY <derived16> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join)
-1 PRIMARY <derived17> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join)
+1 PRIMARY <derived3> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join)
+1 PRIMARY <derived4> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join)
+1 PRIMARY <derived5> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join)
+1 PRIMARY <derived6> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join)
+1 PRIMARY <derived7> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join)
+1 PRIMARY <derived8> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join)
+1 PRIMARY <derived9> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join)
+1 PRIMARY <derived10> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join)
+1 PRIMARY <derived11> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join)
+1 PRIMARY <derived12> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join)
+1 PRIMARY <derived13> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join)
+1 PRIMARY <derived14> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join)
+1 PRIMARY <derived15> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join)
+1 PRIMARY <derived16> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join)
+1 PRIMARY <derived17> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join)
17 DERIVED r1 ALL NULL NULL NULL NULL 2
17 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
17 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join)
diff --git a/mysql-test/main/mysql_client_test.result b/mysql-test/main/mysql_client_test.result
index 6f65979..b5100f9 100644
--- a/mysql-test/main/mysql_client_test.result
+++ b/mysql-test/main/mysql_client_test.result
@@ -135,7 +135,7 @@ EXPALIN number of fields: 10
- 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
- 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
- - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
+ - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 9: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39
EXPALIN JSON number of fields: 1
- 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39
@@ -148,7 +148,7 @@ ANALYZE number of fields: 13
- 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
- 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
- - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
+ - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10
- 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
- 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
@@ -164,7 +164,7 @@ EXPALIN INSERT number of fields: 10
- 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
- 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
- - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
+ - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 9: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39
EXPALIN JSON INSERT number of fields: 1
- 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39
@@ -177,7 +177,7 @@ ANALYZE INSERT number of fields: 13
- 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
- 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
- - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
+ - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10
- 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
- 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
@@ -193,7 +193,7 @@ EXPALIN UPDATE number of fields: 10
- 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
- 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
- - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
+ - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 9: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39
EXPALIN JSON UPDATE number of fields: 1
- 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39
@@ -206,7 +206,7 @@ ANALYZE UPDATE number of fields: 13
- 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
- 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
- - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
+ - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10
- 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
- 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
@@ -222,7 +222,7 @@ EXPALIN DELETE number of fields: 10
- 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
- 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
- - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
+ - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 9: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39
EXPALIN JSON DELETE number of fields: 1
- 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39
@@ -235,7 +235,7 @@ ANALYZE DELETE number of fields: 13
- 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39
- 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39
- - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0
+ - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39
- 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10
- 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
- 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2
diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result
index 5ee85ee..68ed55c 100644
--- a/mysql-test/main/mysqld--help.result
+++ b/mysql-test/main/mysqld--help.result
@@ -546,6 +546,8 @@ The following specify which files/extra groups are read (specified before remain
relay log will be rotated automatically when the size
exceeds this value. If 0 at startup, it's set to
max_binlog_size
+ --max-rowid-filter-size=#
+ The maximum number of rows that fit in memory
--max-seeks-for-key=#
Limit assumed max number of seeks when looking up rows
based on a key
@@ -679,7 +681,7 @@ The following specify which files/extra groups are read (specified before remain
optimize_join_buffer_size, table_elimination,
extended_keys, exists_to_in, orderby_uses_equalities,
condition_pushdown_for_derived, split_materialized,
- condition_pushdown_for_subquery
+ condition_pushdown_for_subquery, rowid_filter
--optimizer-use-condition-selectivity=#
Controls selectivity of which conditions the optimizer
takes into account to calculate cardinality of a partial
@@ -1509,6 +1511,7 @@ max-long-data-size 16777216
max-prepared-stmt-count 16382
max-recursive-iterations 18446744073709551615
max-relay-log-size 1073741824
+max-rowid-filter-size 131072
max-seeks-for-key 18446744073709551615
max-session-mem-used 9223372036854775807
max-sort-length 1024
@@ -1545,7 +1548,7 @@ old-style-user-limits FALSE
optimizer-prune-level 1
optimizer-search-depth 62
optimizer-selectivity-sampling-limit 100
-optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on
+optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on
optimizer-use-condition-selectivity 1
performance-schema FALSE
performance-schema-accounts-size -1
diff --git a/mysql-test/main/null_key.result b/mysql-test/main/null_key.result
index 4b4a4b8..e14fbe6 100644
--- a/mysql-test/main/null_key.result
+++ b/mysql-test/main/null_key.result
@@ -181,7 +181,7 @@ insert into t2 values (7),(8);
explain select * from t2 straight_join t1 where t1.a=t2.a and b is null;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
-1 SIMPLE t1 ref a,b a 10 test.t2.a,const 2 Using where; Using index
+1 SIMPLE t1 ref|filter a,b a|b 10|5 test.t2.a,const 2 (13%) Using where; Using index; Using filter
drop index b on t1;
explain select * from t2,t1 where t1.a=t2.a and b is null;
id select_type table type possible_keys key key_len ref rows Extra
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index e2b05cc..4eeb26e 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -1503,7 +1503,7 @@ WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using where; Using temporary; Using filesort
-1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using index condition
+1 SIMPLE t2 ref|filter a,b,c a|b 40|5 test.t1.a,const 11 (26%) Using index condition; Using filter
SELECT d FROM t1, t2
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 1;
diff --git a/mysql-test/main/ps_1general.result b/mysql-test/main/ps_1general.result
index 035372a..e8cd2ef 100644
--- a/mysql-test/main/ps_1general.result
+++ b/mysql-test/main/ps_1general.result
@@ -451,7 +451,7 @@ def possible_keys 253 4_OR_8_K 0 Y 0 39 8
def key 253 64 0 Y 0 39 8
def key_len 253 4_OR_8_K 0 Y 0 39 8
def ref 253 2048 0 Y 0 39 8
-def rows 8 10 1 Y 32928 0 63
+def rows 253 64 1 Y 0 39 8
def Extra 253 255 14 N 1 39 8
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort
@@ -467,7 +467,7 @@ def possible_keys 253 4_OR_8_K 7 Y 0 39 8
def key 253 64 7 Y 0 39 8
def key_len 253 4_OR_8_K 1 Y 0 39 8
def ref 253 2048 0 Y 0 39 8
-def rows 8 10 1 Y 32928 0 63
+def rows 253 64 1 Y 0 39 8
def Extra 253 255 37 N 1 39 8
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using index condition; Using filesort
diff --git a/mysql-test/main/ps_2myisam.result b/mysql-test/main/ps_2myisam.result
index 3906875..c85abaa 100644
--- a/mysql-test/main/ps_2myisam.result
+++ b/mysql-test/main/ps_2myisam.result
@@ -1161,7 +1161,7 @@ def possible_keys 253 4_OR_8_K 0 Y 0 39 8
def key 253 64 0 Y 0 39 8
def key_len 253 4_OR_8_K 0 Y 0 39 8
def ref 253 2048 0 Y 0 39 8
-def rows 8 10 1 Y 32928 0 63
+def rows 253 64 1 Y 0 39 8
def Extra 253 255 0 N 1 39 8
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t9 ALL NULL NULL NULL NULL 2
diff --git a/mysql-test/main/ps_3innodb.result b/mysql-test/main/ps_3innodb.result
index 9f5c895..53f736f 100644
--- a/mysql-test/main/ps_3innodb.result
+++ b/mysql-test/main/ps_3innodb.result
@@ -1161,7 +1161,7 @@ def possible_keys 253 4_OR_8_K 0 Y 0 39 8
def key 253 64 0 Y 0 39 8
def key_len 253 4_OR_8_K 0 Y 0 39 8
def ref 253 2048 0 Y 0 39 8
-def rows 8 10 1 Y 32928 0 63
+def rows 253 64 1 Y 0 39 8
def Extra 253 255 0 N 1 39 8
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t9 ALL NULL NULL NULL NULL 2
diff --git a/mysql-test/main/ps_4heap.result b/mysql-test/main/ps_4heap.result
index 46b4d9c..0cf1ed1 100644
--- a/mysql-test/main/ps_4heap.result
+++ b/mysql-test/main/ps_4heap.result
@@ -1162,7 +1162,7 @@ def possible_keys 253 4_OR_8_K 0 Y 0 39 8
def key 253 64 0 Y 0 39 8
def key_len 253 4_OR_8_K 0 Y 0 39 8
def ref 253 2048 0 Y 0 39 8
-def rows 8 10 1 Y 32928 0 63
+def rows 253 64 1 Y 0 39 8
def Extra 253 255 0 N 1 39 8
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t9 ALL NULL NULL NULL NULL 2
diff --git a/mysql-test/main/ps_5merge.result b/mysql-test/main/ps_5merge.result
index cc2f0f8..4915539 100644
--- a/mysql-test/main/ps_5merge.result
+++ b/mysql-test/main/ps_5merge.result
@@ -1205,7 +1205,7 @@ def possible_keys 253 4_OR_8_K 0 Y 0 39 8
def key 253 64 0 Y 0 39 8
def key_len 253 4_OR_8_K 0 Y 0 39 8
def ref 253 2048 0 Y 0 39 8
-def rows 8 10 1 Y 32928 0 63
+def rows 253 64 1 Y 0 39 8
def Extra 253 255 0 N 1 39 8
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t9 ALL NULL NULL NULL NULL 2
@@ -4573,7 +4573,7 @@ def possible_keys 253 4_OR_8_K 0 Y 0 39 8
def key 253 64 0 Y 0 39 8
def key_len 253 4_OR_8_K 0 Y 0 39 8
def ref 253 2048 0 Y 0 39 8
-def rows 8 10 1 Y 32928 0 63
+def rows 253 64 1 Y 0 39 8
def Extra 253 255 0 N 1 39 8
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t9 ALL NULL NULL NULL NULL 2
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index 50de308..44f7e51 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -1896,7 +1896,7 @@ explain
select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 64
-1 SIMPLE t1 range i1,i2 i1 4 NULL 78 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t1 range|filter i1,i2 i1|i2 4|4 NULL 78 (89%) Using where; Using join buffer (flat, BNL join); Using filter
select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000;
count(*)
128
@@ -2530,7 +2530,7 @@ explain select * from t1,t2
where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range idx idx 5 NULL 6 Using index condition
-1 SIMPLE t2 ref idx1,idx2 idx1 5 test.t1.a 12 Using where
+1 SIMPLE t2 ref|filter idx1,idx2 idx1|idx2 5|5 test.t1.a 12 (13%) Using where; Using filter
explain format=json select * from t1,t2
where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
EXPLAIN
@@ -2553,7 +2553,7 @@ EXPLAIN
"access_type": "ref",
"possible_keys": ["idx1", "idx2"],
"key": "idx1",
- "key_length": "5",
+ "key_length": "5|5",
"used_key_parts": ["d"],
"ref": ["test.t1.a"],
"rows": 12,
@@ -2626,7 +2626,7 @@ insert into t1 select * from t1;
explain select * from t1,t2
where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range idx1,idx2 idx2 5 NULL 6 Using where
+1 SIMPLE t2 range|filter idx1,idx2 idx2|idx1 5|5 NULL 6 (7%) Using where; Using filter
1 SIMPLE t1 ref idx idx 5 test.t2.d 11
explain format=json select * from t1,t2
where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
@@ -2639,7 +2639,7 @@ EXPLAIN
"access_type": "range",
"possible_keys": ["idx1", "idx2"],
"key": "idx2",
- "key_length": "5",
+ "key_length": "5|5",
"used_key_parts": ["e"],
"rows": 6,
"filtered": 100,
diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index a6c5737..94a2b5f 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -1898,7 +1898,7 @@ explain
select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 64
-1 SIMPLE t1 range i1,i2 i1 4 NULL 78 Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
+1 SIMPLE t1 range|filter i1,i2 i1|i2 4|4 NULL 78 (89%) Using where; Rowid-ordered scan; Using join buffer (flat, BNL join); Using filter
select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000;
count(*)
128
@@ -2536,7 +2536,7 @@ explain select * from t1,t2
where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range idx idx 5 NULL 6 Using index condition; Rowid-ordered scan
-1 SIMPLE t2 ref idx1,idx2 idx1 5 test.t1.a 12 Using where
+1 SIMPLE t2 ref|filter idx1,idx2 idx1|idx2 5|5 test.t1.a 12 (13%) Using where; Using filter
explain format=json select * from t1,t2
where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
EXPLAIN
@@ -2560,7 +2560,7 @@ EXPLAIN
"access_type": "ref",
"possible_keys": ["idx1", "idx2"],
"key": "idx1",
- "key_length": "5",
+ "key_length": "5|5",
"used_key_parts": ["d"],
"ref": ["test.t1.a"],
"rows": 12,
@@ -2633,7 +2633,7 @@ insert into t1 select * from t1;
explain select * from t1,t2
where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range idx1,idx2 idx2 5 NULL 6 Using where; Rowid-ordered scan
+1 SIMPLE t2 range|filter idx1,idx2 idx2|idx1 5|5 NULL 6 (7%) Using where; Rowid-ordered scan; Using filter
1 SIMPLE t1 ref idx idx 5 test.t2.d 11
explain format=json select * from t1,t2
where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1;
@@ -2646,7 +2646,7 @@ EXPLAIN
"access_type": "range",
"possible_keys": ["idx1", "idx2"],
"key": "idx2",
- "key_length": "5",
+ "key_length": "5|5",
"used_key_parts": ["e"],
"rows": 6,
"filtered": 100,
diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result
new file mode 100644
index 0000000..e1296c3
--- /dev/null
+++ b/mysql-test/main/rowid_filter.result
@@ -0,0 +1,151 @@
+DROP DATABASE IF EXISTS dbt3_s001;
+CREATE DATABASE dbt3_s001;
+use dbt3_s001;
+# lineitem : {i_l_receiptdate, i_l_shipdate} -> i_l_receiptdate
+EXPLAIN SELECT *
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-02-01' AND
+l_receiptdate BETWEEN '1997-01-10' AND '1997-01-25';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate|i_l_shipdate 4|4 NULL 59 (1%) Using index condition; Using where; Using filter
+1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
+# lineitem : {i_l_receiptdate, i_l_shipdate} -> i_l_receiptdate
+# orders : {i_o_orderdate} -> i_o_orderdate
+EXPLAIN SELECT *
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-02-01' AND
+l_receiptdate BETWEEN '1997-01-10' AND '1997-01-25' AND
+o_orderdate > '1997-01-15';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate|i_l_shipdate 4|4 NULL 59 (1%) Using index condition; Using where; Using filter
+1 SIMPLE orders eq_ref|filter PRIMARY,i_o_orderdate PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (26%) Using where; Using filter
+# lineitem : {i_l_receiptdate, i_l_shipdate,
+# i_l_commitdate} -> i_l_receiptdate
+EXPLAIN SELECT *
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-02-01' AND
+l_receiptdate BETWEEN '1997-01-10' AND '1997-01-25' AND
+l_commitdate BETWEEN '1997-01-05' AND '1997-01-25';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate i_l_receiptdate|i_l_commitdate 4|4 NULL 59 (1%) Using index condition; Using where; Using filter
+1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
+# lineitem : {i_l_receiptdate, i_l_shipdate,
+# i_l_commitdate} -> i_l_commitdate
+EXPLAIN SELECT *
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-02-01' AND
+l_receiptdate BETWEEN '1997-01-01' AND '1997-01-25' AND
+l_commitdate BETWEEN '1997-01-15' AND '1997-01-25';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate i_l_commitdate|i_l_shipdate 4|4 NULL 35 (1%) Using index condition; Using where; Using filter
+1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
+CREATE INDEX i_l_extendedprice ON lineitem(l_extendedprice);
+# lineitem : {i_l_receiptdate, i_l_shipdate, i_l_commitdate,
+# i_l_extendedprice} -> i_l_extendedprice
+EXPLAIN SELECT *
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1996-11-01' AND '1997-01-21' AND
+l_receiptdate BETWEEN '1996-11-21' AND '1997-01-25' AND
+l_commitdate BETWEEN '1996-11-25' AND '1997-01-20' AND
+l_extendedprice BETWEEN 26000 AND 27000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate,i_l_extendedprice i_l_extendedprice|i_l_commitdate 9|4 NULL 77 (3%) Using index condition; Using where; Using filter
+1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
+# lineitem : {i_l_shipdate, i_l_extendedprice} -> i_l_shipdate
+EXPLAIN SELECT *
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-11' AND '1997-01-21' AND
+l_extendedprice BETWEEN 26000 AND 27000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_extendedprice i_l_shipdate|i_l_extendedprice 4|9 NULL 29 (1%) Using index condition; Using where; Using filter
+1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
+# lineitem : {i_l_shipdate, i_l_extendedprice} -> i_l_extendedprice
+# intersection point in the I quadrant
+EXPLAIN SELECT *
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE (l_shipdate BETWEEN '1997-01-11' AND '1997-01-26' OR
+l_shipdate BETWEEN '1995-02-01' AND '1995-02-14' OR
+l_shipdate BETWEEN '1994-12-12' AND '1994-12-28'
+ ) AND l_extendedprice BETWEEN 26000 AND 27000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_extendedprice i_l_extendedprice|i_l_shipdate 9|4 NULL 77 (2%) Using index condition; Using where; Using filter
+1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
+# lineitem : {i_l_shipdate, i_l_extendedprice} -> i_l_shipdate
+# parallel lines
+EXPLAIN SELECT *
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE (l_shipdate BETWEEN '1997-01-11' AND '1997-01-26' OR
+l_shipdate BETWEEN '1995-02-01' AND '1995-02-16' OR
+l_shipdate BETWEEN '1994-12-12' AND '1994-12-27'
+ ) AND l_extendedprice BETWEEN 26000 AND 27000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_extendedprice i_l_extendedprice|i_l_shipdate 9|4 NULL 77 (2%) Using index condition; Using where; Using filter
+1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
+CREATE INDEX i_l_discount ON lineitem(l_discount);
+CREATE INDEX i_l_tax ON lineitem(l_tax);
+# lineitem : {i_l_receiptdate, i_l_shipdate, i_l_commitdate,
+# i_l_extendedprice, i_l_discount, i_l_tax}
+# -> {i_l_extendedprice}
+EXPLAIN SELECT *
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1996-11-01' AND '1997-01-21' AND
+l_receiptdate BETWEEN '1996-11-21' AND '1997-01-25' AND
+l_commitdate BETWEEN '1996-11-25' AND '1997-01-20' AND
+l_extendedprice BETWEEN 26000 AND 27000 AND
+l_discount BETWEEN 0 AND 0.01 AND
+l_tax BETWEEN 0.03 AND 0.04;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate,i_l_extendedprice,i_l_discount,i_l_tax i_l_extendedprice|i_l_commitdate 9|4 NULL 77 (3%) Using index condition; Using where; Using filter
+1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
+DROP INDEX i_l_extendedprice on lineitem;
+DROP INDEX i_l_discount on lineitem;
+DROP INDEX i_l_tax on lineitem;
+SET max_rowid_filter_size= 1024;
+# lineitem : {i_l_shipdate, i_l_receiptdate, i_l_commitdate}
+# -> i_l_shipdate
+# i_l_commitdate isn't in-memory -> isn't used
+EXPLAIN SELECT *
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1996-12-28' AND '1997-01-20' AND
+l_receiptdate BETWEEN '1996-12-21' AND '1997-01-25' AND
+l_commitdate BETWEEN '1996-12-01' AND '1997-01-25';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate i_l_shipdate|i_l_receiptdate 4|4 NULL 72 (2%) Using index condition; Using where; Using filter
+1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
+SET max_rowid_filter_size= DEFAULT;
+# lineitem : {i_l_shipdate, i_l_commitdate} -> i_l_commitdate
+EXPLAIN SELECT *
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1993-01-01' AND '1997-01-30' AND
+l_commitdate BETWEEN '1997-01-10' AND '1997-01-12';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate i_l_commitdate 4 NULL 6 Using index condition; Using where
+1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
+# lineitem : {i_l_shipdate, i_l_commitdate} -> i_l_commitdate
+EXPLAIN SELECT *
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1993-01-01' AND '1997-01-30' AND
+l_commitdate BETWEEN '1993-01-10' AND '1997-01-12';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem ALL PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate NULL NULL NULL 6005 Using where
+1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
+# lineitem : {i_l_shipdate, i_l_commitdate, i_l_receiptdate}
+# -> i_l_receiptdate
+EXPLAIN SELECT *
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1993-01-01' AND '1997-01-30' AND
+l_commitdate BETWEEN '1993-01-10' AND '1997-01-12' AND
+l_receiptdate BETWEEN '1997-01-10' AND '1997-01-12';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate i_l_receiptdate 4 NULL 10 Using index condition; Using where
+1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
+# lineitem : {i_l_shipdate, i_l_receiptdate} -> i_l_receiptdate
+# indexes with high selectivity
+EXPLAIN SELECT *
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-09' AND '1997-01-10' AND
+l_receiptdate BETWEEN '1997-01-09' AND '1997-01-10';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate|i_l_shipdate 4|4 NULL 6 (1%) Using index condition; Using where; Using filter
+1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
+DROP DATABASE dbt3_s001;
diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result
index 94a9231..a03dbe4 100644
--- a/mysql-test/main/select.result
+++ b/mysql-test/main/select.result
@@ -3474,13 +3474,13 @@ INSERT INTO t2 VALUES
EXPLAIN
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition
-1 SIMPLE t2 ref c c 5 test.t1.a 2
+1 SIMPLE t2 ALL c NULL NULL NULL 18 Using where
+1 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using filter
EXPLAIN
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where
-1 SIMPLE t2 ref c c 5 test.t1.a 2
+1 SIMPLE t2 ALL c NULL NULL NULL 18 Using where
+1 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using filter
DROP TABLE t1, t2;
create table t1 (
a int unsigned not null auto_increment primary key,
@@ -3616,7 +3616,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where
-1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter
EXPLAIN
SELECT t3.a FROM t1,t2,t3
WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
@@ -3624,7 +3624,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee') ;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where
-1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter
EXPLAIN
SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
@@ -3632,7 +3632,7 @@ t3.c IN ('bb','ee');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where
-1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter
EXPLAIN
SELECT t3.a FROM t1,t2,t3
WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
@@ -3640,7 +3640,7 @@ t3.c IN ('bb','ee');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where
-1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter
DROP TABLE t1,t2,t3;
CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
CREATE TABLE t2 ( f11 int PRIMARY KEY );
diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result
index b45fb8c..45adc5a 100644
--- a/mysql-test/main/select_jcl6.result
+++ b/mysql-test/main/select_jcl6.result
@@ -3485,13 +3485,13 @@ INSERT INTO t2 VALUES
EXPLAIN
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Rowid-ordered scan
-1 SIMPLE t2 ref c c 5 test.t1.a 2 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE t2 ALL c NULL NULL NULL 18 Using where
+1 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter
EXPLAIN
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where; Rowid-ordered scan
-1 SIMPLE t2 ref c c 5 test.t1.a 2 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE t2 ALL c NULL NULL NULL 18 Using where
+1 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter
DROP TABLE t1, t2;
create table t1 (
a int unsigned not null auto_increment primary key,
@@ -3627,7 +3627,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where; Rowid-ordered scan
-1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter
EXPLAIN
SELECT t3.a FROM t1,t2,t3
WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
@@ -3635,7 +3635,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee') ;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where; Rowid-ordered scan
-1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter
EXPLAIN
SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
@@ -3643,7 +3643,7 @@ t3.c IN ('bb','ee');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where; Rowid-ordered scan
-1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter
EXPLAIN
SELECT t3.a FROM t1,t2,t3
WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
@@ -3651,7 +3651,7 @@ t3.c IN ('bb','ee');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where; Rowid-ordered scan
-1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter
DROP TABLE t1,t2,t3;
CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
CREATE TABLE t2 ( f11 int PRIMARY KEY );
diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result
index 94a9231..a03dbe4 100644
--- a/mysql-test/main/select_pkeycache.result
+++ b/mysql-test/main/select_pkeycache.result
@@ -3474,13 +3474,13 @@ INSERT INTO t2 VALUES
EXPLAIN
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition
-1 SIMPLE t2 ref c c 5 test.t1.a 2
+1 SIMPLE t2 ALL c NULL NULL NULL 18 Using where
+1 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using filter
EXPLAIN
SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where
-1 SIMPLE t2 ref c c 5 test.t1.a 2
+1 SIMPLE t2 ALL c NULL NULL NULL 18 Using where
+1 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using filter
DROP TABLE t1, t2;
create table t1 (
a int unsigned not null auto_increment primary key,
@@ -3616,7 +3616,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where
-1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter
EXPLAIN
SELECT t3.a FROM t1,t2,t3
WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
@@ -3624,7 +3624,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee') ;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where
-1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter
EXPLAIN
SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
@@ -3632,7 +3632,7 @@ t3.c IN ('bb','ee');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where
-1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter
EXPLAIN
SELECT t3.a FROM t1,t2,t3
WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
@@ -3640,7 +3640,7 @@ t3.c IN ('bb','ee');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where
-1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter
DROP TABLE t1,t2,t3;
CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
CREATE TABLE t2 ( f11 int PRIMARY KEY );
diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result
index 40290ca..9a5c023 100644
--- a/mysql-test/main/stat_tables.result
+++ b/mysql-test/main/stat_tables.result
@@ -332,7 +332,7 @@ and o_orderkey=l_orderkey and p_partkey=l_partkey;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const 1
1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
-1 SIMPLE part eq_ref PRIMARY,i_p_retailprice PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where
+1 SIMPLE part eq_ref|filter PRIMARY,i_p_retailprice PRIMARY|i_p_retailprice 4|9 dbt3_s001.lineitem.l_partkey 1 (1%) Using where; Using filter
select o_orderkey, p_partkey
from part, lineitem, orders
where p_retailprice > 1100 and o_orderdate='1997-01-01'
diff --git a/mysql-test/main/stat_tables_disabled.result b/mysql-test/main/stat_tables_disabled.result
index f57abc3..6faa562 100644
--- a/mysql-test/main/stat_tables_disabled.result
+++ b/mysql-test/main/stat_tables_disabled.result
@@ -31,7 +31,7 @@ limit 10;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE orders ALL PRIMARY,i_o_orderdate,i_o_custkey NULL NULL NULL # Using where; Using temporary; Using filesort
1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey # Using where
-1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey # Using where
+1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey # Using where; Using filter
set use_stat_tables='preferably';
EXPLAIN select sql_calc_found_rows straight_join
l_orderkey, sum(l_extendedprice*(1-l_discount)) as revenue,
@@ -46,7 +46,7 @@ limit 10;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE orders ALL PRIMARY,i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where; Using temporary; Using filesort
1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where
-1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
+1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (54%) Using where; Using filter
flush tables customer, orders, lineitem;
EXPLAIN select sql_calc_found_rows straight_join
l_orderkey, sum(l_extendedprice*(1-l_discount)) as revenue,
@@ -61,7 +61,7 @@ limit 10;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE orders ALL PRIMARY,i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where; Using temporary; Using filesort
1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where
-1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
+1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (54%) Using where; Using filter
# End of the test case for mdev-503
set optimizer_switch=@save_optimizer_switch;
DROP DATABASE dbt3_s001;
diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result
index 070d13d..ce41412 100644
--- a/mysql-test/main/stat_tables_innodb.result
+++ b/mysql-test/main/stat_tables_innodb.result
@@ -357,9 +357,9 @@ from part, lineitem, orders
where p_retailprice > 1100 and o_orderdate='1997-01-01'
and o_orderkey=l_orderkey and p_partkey=l_partkey;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE part range PRIMARY,i_p_retailprice i_p_retailprice 9 NULL 1 Using where; Using index
1 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const 1 Using index
-1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity i_l_partkey 9 dbt3_s001.part.p_partkey,dbt3_s001.orders.o_orderkey 1 Using index
+1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
+1 SIMPLE part eq_ref|filter PRIMARY,i_p_retailprice PRIMARY|i_p_retailprice 4|9 dbt3_s001.lineitem.l_partkey 1 (1%) Using where; Using filter
select o_orderkey, p_partkey
from part, lineitem, orders
where p_retailprice > 1100 and o_orderdate='1997-01-01'
diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result
index 31e7774..517d834 100644
--- a/mysql-test/main/subselect2.result
+++ b/mysql-test/main/subselect2.result
@@ -132,7 +132,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where
1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where
1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where
-1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where
+1 PRIMARY t3 eq_ref|filter PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY|CMFLDRPARNT_IDX 34|35 test.t3.PARENTID 1 (29%) Using where; Using filter
drop table t1, t2, t3, t4;
CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
INSERT INTO t1 VALUES (1),(2);
diff --git a/sql/opt_subselect.h b/sql/opt_subselect.h
index 0311182..846add7 100644
--- a/sql/opt_subselect.h
+++ b/sql/opt_subselect.h
@@ -303,6 +303,7 @@ class Loose_scan_opt
pos->loosescan_picker.loosescan_parts= best_max_loose_keypart + 1;
pos->use_join_buffer= FALSE;
pos->table= tab;
+ pos->filter= tab->filter;
// todo need ref_depend_map ?
DBUG_PRINT("info", ("Produced a LooseScan plan, key %s, %s",
tab->table->key_info[best_loose_scan_key].name.str,
diff --git a/sql/rowid_filter.cc b/sql/rowid_filter.cc
index bcca9a0..aa8194d 100644
--- a/sql/rowid_filter.cc
+++ b/sql/rowid_filter.cc
@@ -35,7 +35,7 @@ void Range_filter_cost_info::init(TABLE *tab, uint key_numb)
void TABLE::sort_range_filter_cost_info_array()
{
- if (best_filter_count == 2)
+ if (best_filter_count <= 2)
return;
for (uint i= best_filter_count; i < range_filter_cost_info_elements-1; i++)
@@ -216,3 +216,4 @@ Range_filter_cost_info
}
return best_filter;
}
+
diff --git a/sql/rowid_filter.h b/sql/rowid_filter.h
index 0d8520f..3097522 100644
--- a/sql/rowid_filter.h
+++ b/sql/rowid_filter.h
@@ -130,6 +130,7 @@ class Range_filter_cost_info : public Sql_alloc
double a; // slope of the linear function
double selectivity;
double intersect_x_axis_abcissa;
+ SQL_SELECT *select;
/**
Filter cost functions
@@ -155,7 +156,7 @@ class Range_filter_cost_info : public Sql_alloc
}
/* End of filter cost functions */
- Range_filter_cost_info() : table(0), key_no(0) {}
+ Range_filter_cost_info() : table(0), key_no(0), select(0) {}
void init(TABLE *tab, uint key_numb);
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 0dd8235..eae7d98 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -2673,7 +2673,7 @@ void THD::make_explain_field_list(List<Item> &field_list, uint8 explain_flags,
mem_root);
item->maybe_null=1;
field_list.push_back(item=new (mem_root)
- Item_empty_string(this, "ref|filter",
+ Item_empty_string(this, "ref",
NAME_CHAR_LEN*MAX_REF_PARTS, cs),
mem_root);
item->maybe_null=1;
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index 23bc1e9..c7e9d6c 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -380,11 +380,13 @@ int print_explain_row(select_result_sink *result,
item_list.push_back(item, mem_root);
/* 'rows' */
+ StringBuffer<64> rows_str;
if (rows)
{
+ rows_str.append_ulonglong((ulonglong)(*rows));
item_list.push_back(new (mem_root)
- Item_int(thd, *rows, MY_INT64_NUM_DECIMAL_DIGITS),
- mem_root);
+ Item_string_sys(thd, rows_str.ptr(),
+ rows_str.length()), mem_root);
}
else
item_list.push_back(item_null, mem_root);
@@ -1147,12 +1149,12 @@ void Explain_table_access::fill_key_len_str(String *key_len_str) const
key_len_str->append(buf, length);
}
- if (key.get_filter_key_length() != (uint)-1)
+ if (key.get_filter_len() != (uint)-1)
{
char buf[64];
size_t length;
- key_len_str->append(',');
- length= longlong10_to_str(key.get_filter_key_length(), buf, 10) - buf;
+ key_len_str->append('|');
+ length= longlong10_to_str(key.get_filter_len(), buf, 10) - buf;
key_len_str->append(buf, length);
}
}
@@ -1177,6 +1179,20 @@ bool Explain_index_use::set(MEM_ROOT *mem_root, KEY *key, uint key_len_arg)
return 0;
}
+bool Explain_index_use::set_filter(MEM_ROOT *mem_root, KEY *key, uint key_len_arg)
+{
+ if (!(filter_name= strdup_root(mem_root, key->name.str)))
+ return 1;
+ filter_len= key_len_arg;
+ uint len= 0;
+ for (uint i= 0; i < key->usable_key_parts; i++)
+ {
+ len += key->key_part[i].store_length;
+ if (len >= key_len_arg)
+ break;
+ }
+ return 0;
+}
bool Explain_index_use::set_pseudo_key(MEM_ROOT *root, const char* key_name_arg)
{
@@ -1240,7 +1256,16 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai
}
/* `type` column */
- push_str(thd, &item_list, join_type_str[type]);
+ StringBuffer<64> join_type_buf;
+ if (!is_filter_set())
+ push_str(thd, &item_list, join_type_str[type]);
+ else
+ {
+ join_type_buf.append(join_type_str[type]);
+ join_type_buf.append("|filter");
+ item_list.push_back(new (mem_root)
+ Item_string_sys(thd, join_type_buf.ptr(), join_type_buf.length()), mem_root);
+ }
/* `possible_keys` column */
StringBuffer<64> possible_keys_buf;
@@ -1252,6 +1277,11 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai
/* `key` */
StringBuffer<64> key_str;
fill_key_str(&key_str, false);
+ if (key.get_filter_name())
+ {
+ key_str.append("|");
+ key_str.append(key.get_filter_name());
+ }
if (key_str.length() > 0)
push_string(thd, &item_list, &key_str);
diff --git a/sql/sql_explain.h b/sql/sql_explain.h
index 71f9047..fc4e346 100644
--- a/sql/sql_explain.h
+++ b/sql/sql_explain.h
@@ -583,7 +583,8 @@ class Explain_index_use : public Sql_alloc
{
char *key_name;
uint key_len;
- uint key_len_for_filter;
+ char *filter_name;
+ uint filter_len;
public:
String_list key_parts_list;
@@ -596,16 +597,17 @@ class Explain_index_use : public Sql_alloc
{
key_name= NULL;
key_len= (uint)-1;
- key_len_for_filter= (uint)-1;
+ filter_name= NULL;
+ filter_len= (uint)-1;
}
bool set(MEM_ROOT *root, KEY *key_name, uint key_len_arg);
bool set_pseudo_key(MEM_ROOT *root, const char *key_name);
- void set_filter_key_length(uint key_length_arg)
- { key_len_for_filter= key_length_arg; }
+ bool set_filter(MEM_ROOT *root, KEY *key, uint key_len_arg);
inline const char *get_key_name() const { return key_name; }
inline uint get_key_len() const { return key_len; }
- inline uint get_filter_key_length() const { return key_len_for_filter; }
+ inline const char *get_filter_name() const { return filter_name; }
+ inline uint get_filter_len() const { return filter_len; }
};
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 8f55497..f23a6b4 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -101,6 +101,9 @@ static int sort_keyuse(KEYUSE *a,KEYUSE *b);
static bool are_tables_local(JOIN_TAB *jtab, table_map used_tables);
static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
bool allow_full_scan, table_map used_tables);
+static ha_rows get_quick_record_count(THD *thd, SQL_SELECT *select,
+ TABLE *table,
+ const key_map *keys,ha_rows limit);
void best_access_path(JOIN *join, JOIN_TAB *s,
table_map remaining_tables, uint idx,
bool disable_jbuf, double record_count,
@@ -1462,6 +1465,45 @@ int JOIN::optimize()
}
+bool
+JOIN::make_range_filter_select(SQL_SELECT *select)
+{
+ DBUG_ENTER("make_range_filter_selects");
+
+ JOIN_TAB *tab;
+
+ for (tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES);
+ tab;
+ tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS))
+ {
+ if (tab->filter)
+ {
+ int err;
+ SQL_SELECT *sel;
+ Item **sargable_cond= get_sargable_cond(this, tab->table);
+ sel= make_select(tab->table, const_table_map, const_table_map,
+ *sargable_cond, (SORT_INFO*) 0, 1, &err);
+ if (!sel)
+ DBUG_RETURN(1);
+ tab->filter->select= sel;
+
+ key_map filter_map;
+ filter_map.clear_all();
+ filter_map.set_bit(tab->filter->key_no);
+ bool force_index_save= tab->table->force_index;
+ tab->table->force_index= true;
+ (void) sel->test_quick_select(thd, filter_map, (table_map) 0,
+ (ha_rows) HA_POS_ERROR,
+ true, false, true);
+ tab->table->force_index= force_index_save;
+ if (thd->is_error())
+ DBUG_RETURN(1);
+ DBUG_ASSERT(sel->quick);
+ }
+ }
+ DBUG_RETURN(0);
+}
+
int JOIN::init_join_caches()
{
JOIN_TAB *tab;
@@ -1980,6 +2022,9 @@ int JOIN::optimize_stage2()
if (get_best_combination())
DBUG_RETURN(1);
+ if (make_range_filter_select(select))
+ DBUG_RETURN(1);
+
if (select_lex->handle_derived(thd->lex, DT_OPTIMIZE))
DBUG_RETURN(1);
@@ -7243,11 +7288,14 @@ best_access_path(JOIN *join,
loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp);
} /* not ft_key */
- filter= table->best_filter_for_current_join_order(start_key->key,
+ if (records < DBL_MAX)
+ {
+ filter= table->best_filter_for_current_join_order(start_key->key,
records,
record_count);
- if (filter && (filter->get_filter_gain(record_count*records) < tmp))
- tmp= tmp - filter->get_filter_gain(record_count*records);
+ if (filter && (filter->get_filter_gain(record_count*records) < tmp))
+ tmp= tmp - filter->get_filter_gain(record_count*records);
+ }
if (tmp + 0.0001 < best_time - records/(double) TIME_FOR_COMPARE)
{
@@ -7412,11 +7460,14 @@ best_access_path(JOIN *join,
else
tmp+= s->startup_cost;
- filter= s->table->best_filter_for_current_join_order(MAX_KEY,
- rnd_records,
- record_count);
- if (filter && (filter->get_filter_gain(record_count*rnd_records) < tmp))
- tmp= tmp - filter->get_filter_gain(record_count*rnd_records);
+ if (s->quick && s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE)
+ {
+ filter= s->table->best_filter_for_current_join_order(s->quick->index,
+ rnd_records,
+ record_count);
+ if (filter && (filter->get_filter_gain(record_count*rnd_records) < tmp))
+ tmp= tmp - filter->get_filter_gain(record_count*rnd_records);
+ }
/*
We estimate the cost of evaluating WHERE clause for found records
@@ -7435,7 +7486,9 @@ best_access_path(JOIN *join,
best= tmp;
records= best_records;
best_key= 0;
- best_filter= filter;
+ best_filter= 0;
+ if (s->quick && s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE)
+ best_filter= filter;
/* range/index_merge/ALL/index access method are "independent", so: */
best_ref_depends_map= 0;
best_uses_jbuf= MY_TEST(!disable_jbuf && !((s->table->map &
@@ -12478,6 +12531,16 @@ void JOIN_TAB::cleanup()
select= 0;
delete quick;
quick= 0;
+ if (filter && filter->select)
+ {
+ if (filter->select->quick)
+ {
+ delete filter->select->quick;
+ filter->select->quick= 0;
+ }
+ delete filter->select;
+ filter->select= 0;
+ }
if (cache)
{
cache->free();
@@ -24977,11 +25040,13 @@ int print_explain_message_line(select_result_sink *result,
item_list.push_back(item_null, mem_root);
/* `rows` */
+ StringBuffer<64> rows_str;
if (rows)
{
- item_list.push_back(new (mem_root) Item_int(thd, *rows,
- MY_INT64_NUM_DECIMAL_DIGITS),
- mem_root);
+ rows_str.append_ulonglong((ulonglong)(*rows));
+ item_list.push_back(new (mem_root)
+ Item_string_sys(thd, rows_str.ptr(),
+ rows_str.length()), mem_root);
}
else
item_list.push_back(item_null, mem_root);
@@ -25055,16 +25120,6 @@ bool JOIN_TAB::save_filter_explain_data(Explain_table_access *eta)
{
if (!filter)
return 0;
- KEY *pk_key= get_keyinfo_by_key_no(filter->key_no);
- StringBuffer<64> buff_for_pk;
- const char *tmp_buff;
- buff_for_pk.append("filter:");
- tmp_buff= pk_key->name.str;
- buff_for_pk.append(tmp_buff, strlen(tmp_buff), system_charset_info);
- if (!(eta->ref_list.append_str(join->thd->mem_root,
- buff_for_pk.c_ptr_safe())))
- return 1;
- eta->key.set_filter_key_length(pk_key->key_length);
(filter->selectivity*100 >= 1) ? eta->filter_perc= round(filter->selectivity*100) :
eta->filter_perc= 1;
return 0;
@@ -25207,6 +25262,14 @@ bool JOIN_TAB::save_explain_data(Explain_table_access *eta,
// psergey-todo: ^ check for error return code
/* Build "key", "key_len", and "ref" */
+
+ if (filter)
+ {
+ eta->key.set_filter(thd->mem_root,
+ &filter->table->key_info[filter->key_no],
+ filter->select->quick->max_used_key_length);
+ }
+
if (tab_type == JT_NEXT)
{
key_info= table->key_info+index;
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 1d08b74..cb0b7c3 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1625,6 +1625,7 @@ class JOIN :public Sql_alloc
bool optimize_unflattened_subqueries();
bool optimize_constant_subqueries();
int init_join_caches();
+ bool make_range_filter_select(SQL_SELECT *select);
bool make_sum_func_list(List<Item> &all_fields, List<Item> &send_fields,
bool before_group_by, bool recompute= FALSE);
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index cbf3dad..90c8b50 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -7606,8 +7606,8 @@ static void test_explain_bug()
verify_prepare_field(result, 7, "ref", "", MYSQL_TYPE_VAR_STRING,
"", "", "", NAME_CHAR_LEN * HA_MAX_KEY_SEG, 0);
- verify_prepare_field(result, 8, "rows", "", MYSQL_TYPE_LONGLONG,
- "", "", "", 10, 0);
+ verify_prepare_field(result, 8, "rows", "", MYSQL_TYPE_VAR_STRING,
+ "", "", "", NAME_CHAR_LEN, 0);
verify_prepare_field(result, 9, "Extra", "", MYSQL_TYPE_VAR_STRING,
"", "", "", 255, 0);
1
0
revision-id: d71574654386c6c33e05abb8fd337404c14587bd (mariadb-25.3.19-10-gd7157465)
parent(s): a37784dc48c4614226de30a48807a3e00cc1f429
author: Jan Lindström
committer: Jan Lindström
timestamp: 2018-11-13 15:54:14 +0200
message:
Fix Galera VERSION.
---
scripts/build.sh | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/scripts/build.sh b/scripts/build.sh
index c07a8bba..ac8eccdf 100755
--- a/scripts/build.sh
+++ b/scripts/build.sh
@@ -5,7 +5,7 @@ set -eu
# $Id$
# Galera library version
-VERSION="25.3.22"
+VERSION="26.4.0"
get_cores()
{
1
0
[Commits] 10807fe95db: MDEV-16217: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed in Field_num::get_date
by Oleksandr Byelkin 13 Nov '18
by Oleksandr Byelkin 13 Nov '18
13 Nov '18
revision-id: 10807fe95db791b6e1ac0f14e945de080f24d108 (mariadb-10.2.18-65-g10807fe95db)
parent(s): 89f948c766721a26e110bc9da0ca5ebc20f65112
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-11-13 11:15:20 +0100
message:
MDEV-16217: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed in Field_num::get_date
- clean up DEFAULT() to work only with default value and correctly print
itself.
- fix of DBUG_ASSERT about fields read/write
- fix of field marking for write based really on the thd->mark_used_columns flag
---
mysql-test/r/func_default.result | 10 ++++++-
mysql-test/r/func_time.result | 57 ++++++++++++++++++++++++++++++++++++++++
mysql-test/t/func_default.test | 5 ++++
mysql-test/t/func_time.test | 32 ++++++++++++++++++++++
sql/field.cc | 17 ++++++++++--
sql/field.h | 1 +
sql/item.cc | 25 ++++++++++++++++--
sql/item.h | 5 ++++
8 files changed, 147 insertions(+), 5 deletions(-)
diff --git a/mysql-test/r/func_default.result b/mysql-test/r/func_default.result
index 535be10da86..9699f0795e3 100644
--- a/mysql-test/r/func_default.result
+++ b/mysql-test/r/func_default.result
@@ -8,13 +8,21 @@ explain extended select default(str), default(strnull), default(intg), default(r
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 select default('') AS `default(str)`,default('') AS `default(strnull)`,default(0) AS `default(intg)`,default(0) AS `default(rel)` from dual
+Note 1003 select default(`test`.`t1`.`str`) AS `default(str)`,default(`test`.`t1`.`strnull`) AS `default(strnull)`,default(`test`.`t1`.`intg`) AS `default(intg)`,default(`test`.`t1`.`rel`) AS `default(rel)` from dual
select * from t1 where str <> default(str);
str strnull intg rel
0 0
explain select * from t1 where str <> default(str);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1
+create view v1 as select default(str), default(strnull), default(intg), default(rel) from t1;
+select * from v1;
+default(str) default(strnull) default(intg) default(rel)
+def NULL 10 3.14
+show create view v1;
+View Create View character_set_client collation_connection
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select default(`t1`.`str`) AS `default(str)`,default(`t1`.`strnull`) AS `default(strnull)`,default(`t1`.`intg`) AS `default(intg)`,default(`t1`.`rel`) AS `default(rel)` from `t1` latin1 latin1_swedish_ci
+drop view v1;
drop table t1;
CREATE TABLE t1 (id int(11), s varchar(20));
INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 5ca9cf5228f..16acf3fcbea 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -3423,3 +3423,60 @@ DROP TABLE t1,t2;
#
# End of 10.1 tests
#
+#
+# MDEV-16217: Assertion `!table || (!table->read_set ||
+# bitmap_is_set(table->read_set, field_index))'
+# failed in Field_num::get_date
+#
+CREATE TABLE t1 (pk int default 0, a1 date);
+INSERT INTO t1 VALUES (1,'1900-01-01'),(2,NULL),(3,NULL),(4,NULL);
+CREATE VIEW v1 AS
+SELECT t1.pk AS pk, t1.a1 AS a1 FROM t1;
+SELECT a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk)) FROM v1;
+a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk))
+0
+NULL
+NULL
+NULL
+SELECT a1 BETWEEN (('2018-08-24')) AND (~ DEFAULT(pk)) FROM v1;
+a1 BETWEEN (('2018-08-24')) AND (~ DEFAULT(pk))
+0
+NULL
+NULL
+NULL
+Warnings:
+Warning 1292 Incorrect datetime value: '18446744073709551615'
+CREATE TABLE t2 (pk int default 1, a1 date);
+INSERT INTO t2 VALUES (4,NULL);
+CREATE view v2 as SELECT default(t1.pk), default(t2.pk), t1.pk from t1,t2;
+select * from v2;
+default(t1.pk) default(t2.pk) pk
+0 1 1
+0 1 2
+0 1 3
+0 1 4
+show create view v2;
+View Create View character_set_client collation_connection
+v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select default(`t1`.`pk`) AS `default(t1.pk)`,default(`t2`.`pk`) AS `default(t2.pk)`,`t1`.`pk` AS `pk` from (`t1` join `t2`) latin1 latin1_swedish_ci
+CREATE view v3 as SELECT default(pk) from t2;
+select * from v3;
+default(pk)
+1
+explain extended select * from v3;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00
+Warnings:
+Note 1003 select default(`test`.`t2`.`pk`) AS `default(pk)` from dual
+explain extended select default(pk) from t2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00
+Warnings:
+Note 1003 select default(`test`.`t2`.`pk`) AS `default(pk)` from dual
+show create view v3;
+View Create View character_set_client collation_connection
+v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select default(`t2`.`pk`) AS `default(pk)` from `t2` latin1 latin1_swedish_ci
+DROP VIEW v1,v2,v3;
+DROP TABLE t1,t2;
+#
+# End of 10.2 tests
+#
diff --git a/mysql-test/t/func_default.test b/mysql-test/t/func_default.test
index fbd73990297..332bfca021f 100644
--- a/mysql-test/t/func_default.test
+++ b/mysql-test/t/func_default.test
@@ -11,6 +11,11 @@ explain extended select default(str), default(strnull), default(intg), default(r
select * from t1 where str <> default(str);
explain select * from t1 where str <> default(str);
+create view v1 as select default(str), default(strnull), default(intg), default(rel) from t1;
+select * from v1;
+show create view v1;
+drop view v1;
+
#TODO: uncomment when bug will be fixed
#create table t2 select default(str), default(strnull), default(intg), default(rel) from t1;
#show create table from t1;
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 73f91bb90a0..d391e5f5059 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -1910,3 +1910,35 @@ DROP TABLE t1,t2;
--echo #
--echo # End of 10.1 tests
--echo #
+
+--echo #
+--echo # MDEV-16217: Assertion `!table || (!table->read_set ||
+--echo # bitmap_is_set(table->read_set, field_index))'
+--echo # failed in Field_num::get_date
+--echo #
+CREATE TABLE t1 (pk int default 0, a1 date);
+INSERT INTO t1 VALUES (1,'1900-01-01'),(2,NULL),(3,NULL),(4,NULL);
+
+CREATE VIEW v1 AS
+SELECT t1.pk AS pk, t1.a1 AS a1 FROM t1;
+
+SELECT a1 BETWEEN (('2018-08-24')) AND (DEFAULT(pk)) FROM v1;
+SELECT a1 BETWEEN (('2018-08-24')) AND (~ DEFAULT(pk)) FROM v1;
+
+CREATE TABLE t2 (pk int default 1, a1 date);
+INSERT INTO t2 VALUES (4,NULL);
+CREATE view v2 as SELECT default(t1.pk), default(t2.pk), t1.pk from t1,t2;
+select * from v2;
+show create view v2;
+CREATE view v3 as SELECT default(pk) from t2;
+select * from v3;
+explain extended select * from v3;
+explain extended select default(pk) from t2;
+show create view v3;
+
+DROP VIEW v1,v2,v3;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
diff --git a/sql/field.cc b/sql/field.cc
index caa84dc9932..6cd8940a893 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -70,8 +70,21 @@ const char field_separator=',';
#define BLOB_PACK_LENGTH_TO_MAX_LENGH(arg) \
((ulong) ((1LL << MY_MIN(arg, 4) * 8) - 1))
-#define ASSERT_COLUMN_MARKED_FOR_READ DBUG_ASSERT(!table || (!table->read_set || bitmap_is_set(table->read_set, field_index)))
-#define ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED DBUG_ASSERT(is_stat_field || !table || (!table->write_set || bitmap_is_set(table->write_set, field_index) || (table->vcol_set && bitmap_is_set(table->vcol_set, field_index))))
+// Column marked for read or the field set to read out or record[0] or [1]
+#define ASSERT_COLUMN_MARKED_FOR_READ \
+ DBUG_ASSERT(!table || \
+ (!table->read_set || \
+ bitmap_is_set(table->read_set, field_index) || \
+ (!(ptr >= table->record[0] && \
+ ptr < table->record[0] + table->s->reclength))))
+
+#define ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED \
+ DBUG_ASSERT(is_stat_field || !table || \
+ (!table->write_set || \
+ bitmap_is_set(table->write_set, field_index) || \
+ (!(ptr >= table->record[0] && \
+ ptr < table->record[0] + table->s->reclength))) || \
+ (table->vcol_set && bitmap_is_set(table->vcol_set, field_index)))
#define FLAGSTR(S,F) ((S) & (F) ? #F " " : "")
diff --git a/sql/field.h b/sql/field.h
index 22c276478b6..55c3ed4c4bd 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -630,6 +630,7 @@ class Virtual_column_info: public Sql_alloc
bool utf8; /* Already in utf8 */
Item *expr;
LEX_STRING name; /* Name of constraint */
+ /* see VCOL_* (VCOL_FIELD_REF, ...) */
uint flags;
Virtual_column_info()
diff --git a/sql/item.cc b/sql/item.cc
index 2adec33491b..6828a74f9ff 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -8792,8 +8792,19 @@ bool Item_default_value::fix_fields(THD *thd, Item **items)
fixed= 1;
return FALSE;
}
+
+ /*
+ DEFAULT() do not need table field so should not ask handler to bring
+ field value (mark column for read)
+ */
+ enum_mark_columns save_mark_used_columns= thd->mark_used_columns;
+ thd->mark_used_columns= MARK_COLUMNS_NONE;
if (!arg->fixed && arg->fix_fields(thd, &arg))
+ {
+ thd->mark_used_columns= save_mark_used_columns;
goto error;
+ }
+ thd->mark_used_columns= save_mark_used_columns;
real_arg= arg->real_item();
@@ -8813,15 +8824,19 @@ bool Item_default_value::fix_fields(THD *thd, Item **items)
goto error;
memcpy((void *)def_field, (void *)field_arg->field,
field_arg->field->size_of());
- IF_DBUG(def_field->is_stat_field=1,); // a hack to fool ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED
+ // If non-constant default value expression
if (def_field->default_value && def_field->default_value->flags)
{
uchar *newptr= (uchar*) thd->alloc(1+def_field->pack_length());
if (!newptr)
goto error;
+ /*
+ Even if DEFAULT() do not read tables fields, the default value
+ expression can do it.
+ */
fix_session_vcol_expr_for_read(thd, def_field, def_field->default_value);
if (thd->mark_used_columns != MARK_COLUMNS_NONE)
- def_field->default_value->expr->walk(&Item::register_field_in_read_map, 1, 0);
+ def_field->default_value->expr->update_used_tables();
def_field->move_field(newptr+1, def_field->maybe_null() ? newptr : 0, 1);
}
else
@@ -8845,6 +8860,12 @@ void Item_default_value::print(String *str, enum_query_type query_type)
return;
}
str->append(STRING_WITH_LEN("default("));
+ /*
+ We take DEFAULT from a field so do not need it value in case of const
+ tables but its name so we set QT_NO_DATA_EXPANSION (as we print for
+ table definition, also we do not need table and database name)
+ */
+ query_type= (enum_query_type) (query_type | QT_NO_DATA_EXPANSION);
arg->print(str, query_type);
str->append(')');
}
diff --git a/sql/item.h b/sql/item.h
index 8d02d981d38..e7bf9285131 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -5229,6 +5229,11 @@ class Item_default_value : public Item_field
return false;
}
table_map used_tables() const;
+ virtual void update_used_tables()
+ {
+ if (field && field->default_value)
+ field->default_value->expr->update_used_tables();
+ }
Field *get_tmp_table_field() { return 0; }
Item *get_tmp_table_item(THD *thd) { return this; }
Item_field *field_for_view_update() { return 0; }
1
0
revision-id: 988d6d30a5ac41f00e54a72f0ba08301afab4fe7 (mariadb-10.2.18-66-g988d6d30a5a)
parent(s): 031efde365c674dbdbaada95aa6d42a4274db438
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-11-13 11:15:01 +0100
message:
postreview
---
sql/item.h | 2 +-
sql/sql_base.cc | 2 +-
2 files changed, 2 insertions(+), 2 deletions(-)
diff --git a/sql/item.h b/sql/item.h
index 5866f328f38..e7bf9285131 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -5232,7 +5232,7 @@ class Item_default_value : public Item_field
virtual void update_used_tables()
{
if (field && field->default_value)
- field->default_value->expr->walk(&Item::register_field_in_read_map, 1, 0);
+ field->default_value->expr->update_used_tables();
}
Field *get_tmp_table_field() { return 0; }
Item *get_tmp_table_item(THD *thd) { return this; }
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 0deb5ec1362..c282db42fdd 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -5737,7 +5737,7 @@ find_field_in_table_ref(THD *thd, TABLE_LIST *table_list,
TABLE *table= field_to_set->table;
if (thd->mark_used_columns == MARK_COLUMNS_READ)
bitmap_set_bit(table->read_set, field_to_set->field_index);
- else if (thd->mark_used_columns == MARK_COLUMNS_WRITE)
+ else
bitmap_set_bit(table->write_set, field_to_set->field_index);
}
}
1
0
[Commits] 5e63f616a7e: MDEV-17124: mariadb 10.1.34, views and prepared statements: ERROR 1615 (HY000): Prepared statement needs to be re-prepared
by Oleksandr Byelkin 13 Nov '18
by Oleksandr Byelkin 13 Nov '18
13 Nov '18
revision-id: 5e63f616a7ed511e6e69581f6659f60f1ec8633b (mariadb-10.1.37-9-g5e63f616a7e)
parent(s): 1368a63589d0b4900f7d7efb57444c4ea34e6c26
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-11-13 10:10:09 +0100
message:
MDEV-17124: mariadb 10.1.34, views and prepared statements: ERROR 1615 (HY000): Prepared statement needs to be re-prepared
The problem is that if table definition cache (TDC) is full of real tables which are in tables cache, view definition can not stay there so will me removed by its own underlying tables.
In situation above old mechanism of detection matching definition in PS and current version always require reprepare and so prevent executing the PS.
One work arount is to increase TDC, other - improve version check for views (which is done here).
Now in suspiciouse cases we check MD5 of the view to be sure that version really have chenged.
---
mysql-test/r/view.result | 28 ++++++++++++++++++++++++++++
mysql-test/t/view.test | 39 +++++++++++++++++++++++++++++++++++++++
sql/parse_file.cc | 17 +++++++++++++++++
sql/parse_file.h | 4 +++-
sql/sql_view.cc | 41 +++++++++++++++++++++++++++++++++++++++++
sql/sql_view.h | 1 +
sql/table.cc | 26 ++++++++++++++++++++++++++
sql/table.h | 8 +-------
8 files changed, 156 insertions(+), 8 deletions(-)
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index e8c96e49977..030d0f0c520 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -6259,5 +6259,33 @@ t1col1 t1col2 t1col3
drop view v1;
drop table t1,t2;
#
+# MDEV-17124: mariadb 10.1.34, views and prepared statements:
+# ERROR 1615 (HY000): Prepared statement needs to be re-prepared
+#
+set @tdc= @@table_definition_cache, @tc= @@table_open_cache;
+set global table_definition_cache= 400, table_open_cache= 400;
+create table tt (a int, primary key(a)) engine=MyISAM;
+create view v as select * from tt;
+insert into tt values(1),(2),(3),(4);
+prepare stmt from 'select * from tt';
+#fill table definition cache
+execute stmt;
+a
+1
+2
+3
+4
+prepare stmt from 'select * from v';
+execute stmt;
+a
+1
+2
+3
+4
+drop database db;
+drop view v;
+drop table tt;
+set global table_definition_cache= @tdc, table_open_cache= @tc;
+#
# End of 10.1 tests
#
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 6ff226d738f..ee587181aa4 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -6080,6 +6080,45 @@ select * from v1;
drop view v1;
drop table t1,t2;
+--echo #
+--echo # MDEV-17124: mariadb 10.1.34, views and prepared statements:
+--echo # ERROR 1615 (HY000): Prepared statement needs to be re-prepared
+--echo #
+
+set @tdc= @@table_definition_cache, @tc= @@table_open_cache;
+set global table_definition_cache= 400, table_open_cache= 400;
+
+create table tt (a int, primary key(a)) engine=MyISAM;
+create view v as select * from tt;
+insert into tt values(1),(2),(3),(4);
+
+prepare stmt from 'select * from tt';
+--echo #fill table definition cache
+--disable_query_log
+--disable_result_log
+create database db;
+use db;
+--let $tables=401
+while ($tables)
+{
+ --eval create table t$tables (i int) engine=MyISAM
+ --eval select * from t$tables
+ --dec $tables
+}
+
+use test;
+
+--enable_query_log
+--enable_result_log
+execute stmt;
+prepare stmt from 'select * from v';
+execute stmt;
+
+# Cleanup
+drop database db;
+drop view v;
+drop table tt;
+set global table_definition_cache= @tdc, table_open_cache= @tc;
--echo #
--echo # End of 10.1 tests
--echo #
diff --git a/sql/parse_file.cc b/sql/parse_file.cc
index f3dab4f7b2f..6f188660407 100644
--- a/sql/parse_file.cc
+++ b/sql/parse_file.cc
@@ -145,6 +145,7 @@ write_parameter(IO_CACHE *file, uchar* base, File_option *parameter)
switch (parameter->type) {
case FILE_OPTIONS_STRING:
+ case FILE_OPTIONS_FIXSTRING:
{
LEX_STRING *val_s= (LEX_STRING *)(base + parameter->offset);
if (my_b_write(file, (const uchar *)val_s->str, val_s->length))
@@ -830,6 +831,22 @@ File_parser::parse(uchar* base, MEM_ROOT *mem_root,
}
ptr= eol+1;
break;
+ case FILE_OPTIONS_FIXSTRING:
+ {
+ /* string have to be allocated already and length set */
+ LEX_STRING *val= (LEX_STRING *)(base + parameter->offset);
+ DBUG_ASSERT(val->length != 0);
+ if (ptr[val->length] != '\n')
+ {
+ my_error(ER_FPARSER_ERROR_IN_PARAMETER, MYF(0),
+ parameter->name.str, line);
+ DBUG_RETURN(TRUE);
+ }
+ memcpy(val->str, ptr, val->length);
+ val->str[val->length]= '\0';
+ ptr+= (val->length + 1);
+ break;
+ }
case FILE_OPTIONS_TIMESTAMP:
{
/* string have to be allocated already */
diff --git a/sql/parse_file.h b/sql/parse_file.h
index 87917dbd71b..28f4070b437 100644
--- a/sql/parse_file.h
+++ b/sql/parse_file.h
@@ -36,8 +36,10 @@ enum file_opt_type {
allocated with length 20 (19+1) */
FILE_OPTIONS_STRLIST, /**< list of escaped strings
(List<LEX_STRING>) */
- FILE_OPTIONS_ULLLIST /**< list of ulonglong values
+ FILE_OPTIONS_ULLLIST, /**< list of ulonglong values
(List<ulonglong>) */
+
+ FILE_OPTIONS_FIXSTRING /**< fixed length String (LEX_STRING) */
};
struct File_option
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 9a9309a133b..ee169de4c93 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -782,6 +782,16 @@ static File_option view_parameters[]=
FILE_OPTIONS_STRING}
};
+
+static File_option view_md5_parameters[]=
+{
+
+ {{ C_STRING_WITH_LEN("md5")}, 0, FILE_OPTIONS_FIXSTRING},
+ {{NullS, 0}, 0, FILE_OPTIONS_STRING}
+};
+
+
+
static LEX_STRING view_file_type[]= {{(char*) STRING_WITH_LEN("VIEW") }};
@@ -1125,7 +1135,38 @@ static int mysql_register_view(THD *thd, TABLE_LIST *view,
DBUG_RETURN(error);
}
+#define MD5_LEN 32
+/**
+ Check is TABLE_LEST and SHARE match
+ @param[in] view TABLE_LIST of the view
+ @param[in] share Share object of view
+
+ @return false on error or misspatch
+*/
+bool mariadb_view_version_check(TABLE_LIST *view, TABLE_SHARE *share)
+{
+ LEX_STRING md5;
+ char md5_buffer[MD5_LEN + 1];
+ md5.str= md5_buffer;
+ md5.length= MD5_LEN;
+
+ /*
+ Check that both were views (view->is_view() could not be checked
+ because it is not opened).
+ */
+ if (!share->is_view || view->md5.length != MD5_LEN)
+ return FALSE;
+
+ DBUG_ASSERT(share->view_def != NULL);
+ if (share->view_def->parse((uchar*)&md5, NULL,
+ view_md5_parameters,
+ 1,
+ &file_parser_dummy_hook))
+ return FALSE;
+ DBUG_ASSERT(md5.length == MD5_LEN);
+ return (strncmp(md5.str, view->md5.str, MD5_LEN) == 0);
+}
/**
read VIEW .frm and create structures
diff --git a/sql/sql_view.h b/sql/sql_view.h
index ce83dc656ad..1685169420d 100644
--- a/sql/sql_view.h
+++ b/sql/sql_view.h
@@ -37,6 +37,7 @@ bool mysql_create_view(THD *thd, TABLE_LIST *view,
bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table,
bool open_view_no_parse);
+bool mariadb_view_version_check(TABLE_LIST *view, TABLE_SHARE *share);
bool mysql_drop_view(THD *thd, TABLE_LIST *view, enum_drop_mode drop_mode);
diff --git a/sql/table.cc b/sql/table.cc
index e1edcc0b407..a6fbce9f3dd 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -7495,6 +7495,32 @@ void TABLE_LIST::set_lock_type(THD *thd, enum thr_lock_type lock)
}
}
+
+bool TABLE_LIST::is_table_ref_id_equal(TABLE_SHARE *s)
+{
+ enum enum_table_ref_type tp= s->get_table_ref_type();
+ if (m_table_ref_type == tp)
+ {
+ bool res= m_table_ref_version == s->get_table_ref_version();
+
+ /*
+ If definition is different object with view we can check MD5 in frm
+ to check if the same view got into table definition cache again.
+ */
+ if (!res &&
+ tp == TABLE_REF_VIEW &&
+ mariadb_view_version_check(this, s))
+ {
+ // to avoid relatively expensive parsing of frm next time
+ set_table_ref_id(s);
+ return TRUE;
+ }
+ return res;
+ }
+ return FALSE;
+}
+
+
uint TABLE_SHARE::actual_n_key_parts(THD *thd)
{
return use_ext_keys &&
diff --git a/sql/table.h b/sql/table.h
index a7913844e9d..9e1a061e606 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -2271,13 +2271,7 @@ struct TABLE_LIST
@sa check_and_update_table_version()
*/
- inline
- bool is_table_ref_id_equal(TABLE_SHARE *s) const
- {
- return (m_table_ref_type == s->get_table_ref_type() &&
- m_table_ref_version == s->get_table_ref_version());
- }
-
+ bool is_table_ref_id_equal(TABLE_SHARE *s);
/**
Record the value of metadata version of the corresponding
table definition cache element in this parse tree node.
1
0