revision-id: 47274d902e63663522176614157b4eace8a17b8e (mariadb-10.0.37-13-g47274d902e6)
parent(s): 6cecb10a2f8b6536bed78ab6d3791d8befc9d732
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-11-14 15:46:53 +0100
message:
fix of test suite
---
mysql-test/r/partition_innodb.result | 2 +-
mysql-test/t/partition_innodb.test | 2 +-
2 files changed, 2 insertions(+), 2 deletions(-)
diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result
index ee3525cb085..b1405be3f12 100644
--- a/mysql-test/r/partition_innodb.result
+++ b/mysql-test/r/partition_innodb.result
@@ -901,7 +901,7 @@ INSERT INTO t1 VALUES (1),(2);
INSERT IGNORE INTO t2 VALUES (2,2),('three',3),(4,4);
Warnings:
Warning 1366 Incorrect integer value: 'three' for column 'b' at row 2
-UPDATE v SET a = NULL ORDER BY a, b;
+UPDATE v SET a = NULL;
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'foo'
DROP view v;
diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test
index 5dbe9f467fa..5674a889023 100644
--- a/mysql-test/t/partition_innodb.test
+++ b/mysql-test/t/partition_innodb.test
@@ -1008,7 +1008,7 @@ CREATE ALGORITHM = MERGE VIEW v AS SELECT a, b FROM t1 STRAIGHT_JOIN t2 WHERE b
INSERT INTO t1 VALUES (1),(2);
INSERT IGNORE INTO t2 VALUES (2,2),('three',3),(4,4);
-UPDATE v SET a = NULL ORDER BY a, b;
+UPDATE v SET a = NULL;
DROP view v;
DROP TABLE t1, t2;
1
0
[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