[Commits] 86b89b08368: MDEV-10564: Galera `wsrep_debug` patch logs MySQL user credentials
by jan 19 Jul '18
by jan 19 Jul '18
19 Jul '18
revision-id: 86b89b0836817ef18cd46489f3165c11d1b750d8 (mariadb-10.1.34-26-g86b89b08368)
parent(s): 4d06b7e1bd3b825da32c9200e6f5ca609add0e13
author: Jan Lindström
committer: Jan Lindström
timestamp: 2018-07-19 15:13:31 +0300
message:
MDEV-10564: Galera `wsrep_debug` patch logs MySQL user credentials
Restricted output for CREATE USER, GRANT, REVOKE and SET PASSWORD
so that it shows only above keywords but not rest of query i.e.
not user or password.
---
sql/wsrep_mysqld.cc | 26 +++++++++++++++++++++++---
1 file changed, 23 insertions(+), 3 deletions(-)
diff --git a/sql/wsrep_mysqld.cc b/sql/wsrep_mysqld.cc
index b127d3a8f00..a88713652ed 100644
--- a/sql/wsrep_mysqld.cc
+++ b/sql/wsrep_mysqld.cc
@@ -1450,6 +1450,25 @@ static bool wsrep_can_run_in_toi(THD *thd, const char *db, const char *table,
}
}
+static const char* wsrep_get_query_or_msg(const THD* thd)
+{
+ switch(thd->lex->sql_command)
+ {
+ case SQLCOM_CREATE_USER:
+ return "CREATE USER";
+ case SQLCOM_GRANT:
+ return "GRANT";
+ case SQLCOM_REVOKE:
+ return "REVOKE";
+ case SQLCOM_SET_OPTION:
+ if (thd->lex->definer)
+ return "SET PASSWORD";
+ /* fallthrough */
+ default:
+ return thd->query();
+ }
+}
+
/*
returns:
0: statement was replicated as TOI
@@ -1472,7 +1491,8 @@ static int wsrep_TOI_begin(THD *thd, char *db_, char *table_,
}
WSREP_DEBUG("TO BEGIN: %lld, %d : %s", (long long)wsrep_thd_trx_seqno(thd),
- thd->wsrep_exec_mode, thd->query() );
+ thd->wsrep_exec_mode, wsrep_get_query_or_msg(thd));
+
switch (thd->lex->sql_command)
{
case SQLCOM_CREATE_VIEW:
@@ -1546,8 +1566,8 @@ static void wsrep_TOI_end(THD *thd) {
wsrep_status_t ret;
wsrep_to_isolation--;
- WSREP_DEBUG("TO END: %lld, %d : %s", (long long)wsrep_thd_trx_seqno(thd),
- thd->wsrep_exec_mode, (thd->query()) ? thd->query() : "void");
+ WSREP_DEBUG("TO END: %lld, %d: %s", (long long)wsrep_thd_trx_seqno(thd),
+ thd->wsrep_exec_mode, wsrep_get_query_or_msg(thd));
wsrep_set_SE_checkpoint(thd->wsrep_trx_meta.gtid.uuid,
thd->wsrep_trx_meta.gtid.seqno);
1
0
19 Jul '18
revision-id: 0896d7ebc3698f898bd66544bbc0ba6e4a4fbe0e (mariadb-10.1.34-25-g0896d7ebc36)
parent(s): 312de43f40e221096b5565f6f4999eaadae09ef4 ada54101a7185782657813c553907f61f2a35faf
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-07-19 12:55:54 +0200
message:
Merge branch '10.0' into bb-10.1-merge
cmake/os/Windows.cmake | 2 +-
mysql-test/r/derived.result | 33 ++++++++++++
mysql-test/r/join.result | 40 +++++++++++++++
mysql-test/r/join_cache.result | 33 ++++++++++++
mysql-test/r/stat_tables.result | 60 ++++++++++++++++++++++
mysql-test/r/stat_tables_innodb.result | 60 ++++++++++++++++++++++
mysql-test/r/subselect_sj2_mat.result | 51 ++++++++++++++++++
.../suite/binlog/include/check_binlog_size.inc | 31 +++++++++++
.../suite/binlog/r/binlog_tmp_table_row.result | 7 +++
.../suite/binlog/t/binlog_tmp_table_row.test | 30 +++++++++++
mysql-test/suite/vcol/t/vcol_misc.test | 2 +
mysql-test/t/derived.test | 21 ++++++++
mysql-test/t/join.test | 53 +++++++++++++++++++
mysql-test/t/join_cache.test | 32 +++++++++++-
mysql-test/t/stat_tables.test | 50 ++++++++++++++++++
mysql-test/t/subselect_sj2_mat.test | 49 ++++++++++++++++++
mysql-test/valgrind.supp | 13 +++++
scripts/mysql_install_db.sh | 5 ++
sql/log.cc | 11 ++--
sql/mysqld.cc | 7 ++-
sql/opt_subselect.cc | 3 +-
sql/sql_base.cc | 12 +++--
sql/sql_parse.cc | 3 ++
sql/sql_select.cc | 4 +-
sql/table.cc | 19 +++++--
storage/xtradb/os/os0file.cc | 41 ++++++++++++++-
26 files changed, 649 insertions(+), 23 deletions(-)
diff --cc mysql-test/r/derived.result
index 687497ceb7e,889518343c0..6f786e34a9a
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@@ -1077,49 -1079,36 +1078,81 @@@
drop procedure pr;
drop view v1;
drop table t1;
+ set @@join_cache_level= @save_join_cache_level;
+ #
+ # MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views
+ #
+ CREATE TABLE t1 (c1 text, c2 int);
+ INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3);
+ CREATE TABLE t2 (c1 text, c2 int);
+ INSERT INTO t2 VALUES ('b',2), ('c',3);
+ CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+ explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ id select_type table type possible_keys key key_len ref rows Extra
+ 1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+ 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
+ 2 DERIVED t1 ALL NULL NULL NULL NULL 5
+ SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ c1 c2 c1 c2
+ c 3 c 3
+ c 3 c 3
+ set @save_join_cache_level= @@join_cache_level;
+ set @@join_cache_level=4;
+ explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ id select_type table type possible_keys key key_len ref rows Extra
+ 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+ 1 PRIMARY <derived2> hash_ALL NULL #hash#$hj 3 test.t2.c1 5 Using where; Using join buffer (flat, BNLH join)
+ 2 DERIVED t1 ALL NULL NULL NULL NULL 5
+ SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ c1 c2 c1 c2
+ c 3 c 3
+ c 3 c 3
+ drop table t1,t2;
+ drop view v1;
+ set @@join_cache_level= @save_join_cache_level;
# end of 5.5
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-8747 Wrong result for SELECT..WHERE derived_table_column='a' AND derived_table_column<>_latin1'A' COLLATE latin1_bin
+#
+CREATE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('a'),('A');
+SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
+a
+a
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
+a
+a
+DROP TABLE t1;
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
+a
+5
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
+a
+5
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
+a
+5
+DROP TABLE t1;
+#
+# MDEV-8749 Wrong result for SELECT..WHERE derived_table_enum_column='number' AND derived_table_enum_column OP number2
+#
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
+a
+5
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
+a
+5
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
+a
+5
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#
diff --cc mysql-test/r/stat_tables.result
index 279c09ff44f,c1457d5e91a..ceadb61feea
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@@ -516,73 -516,64 +516,133 @@@ use test
drop database db1;
drop database db2;
drop table t1;
+ #
+ # MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed
+ #
+ SET use_stat_tables = PREFERABLY;
+ SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' );
+ CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' )
+ NULL
+ set use_stat_tables=@save_use_stat_tables;
+ #
+ # MDEV-16757: manual addition of min/max statistics for BLOB
+ #
+ SET use_stat_tables= PREFERABLY;
+ CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+ INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ ANALYZE TABLE t1;
+ Table Op Msg_type Msg_text
+ test.t1 analyze status Engine-independent statistics collected
++test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
+ test.t1 analyze status OK
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 t NULL NULL 0.0000 3.0000 NULL NULL NULL NULL
+ DELETE FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='t';
+ INSERT INTO mysql.column_stats VALUES
+ ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+ test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+ SELECT pk FROM t1;
+ pk
+ 1
+ 2
+ DROP TABLE t1;
+ set use_stat_tables=@save_use_stat_tables;
+ #
+ # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+ #
+ SET use_stat_tables= PREFERABLY;
+ CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+ INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ ANALYZE TABLE t1;
+ Table Op Msg_type Msg_text
+ test.t1 analyze status Engine-independent statistics collected
+ test.t1 analyze status OK
+ SELECT * FROM t1;
+ pk c
+ 1 foo
+ 2 bar
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+ test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+ CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+ SELECT * FROM t1;
+ pk a
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16757: manual addition of min/max statistics for BLOB
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
+test.t1 analyze status OK
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+DELETE FROM mysql.column_stats
+WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+SELECT pk FROM t1;
+pk
+1
+2
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+SELECT * FROM t1;
+pk c
+1 foo
+2 bar
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+pk a
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+SELECT MAX(pk) FROM t1;
+MAX(pk)
+NULL
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
diff --cc mysql-test/r/stat_tables_innodb.result
index 1604c413743,2ac868e9341..c5e7309861c
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@@ -543,75 -543,66 +543,135 @@@ use test
drop database db1;
drop database db2;
drop table t1;
+ #
+ # MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed
+ #
+ SET use_stat_tables = PREFERABLY;
+ SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' );
+ CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' )
+ NULL
+ set use_stat_tables=@save_use_stat_tables;
+ #
+ # MDEV-16757: manual addition of min/max statistics for BLOB
+ #
+ SET use_stat_tables= PREFERABLY;
+ CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+ INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ ANALYZE TABLE t1;
+ Table Op Msg_type Msg_text
+ test.t1 analyze status Engine-independent statistics collected
++test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
+ test.t1 analyze status OK
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
-test t1 t NULL NULL 0.0000 3.0000 NULL NULL NULL NULL
+ DELETE FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='t';
+ INSERT INTO mysql.column_stats VALUES
+ ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+ test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+ SELECT pk FROM t1;
+ pk
+ 1
+ 2
+ DROP TABLE t1;
+ set use_stat_tables=@save_use_stat_tables;
+ #
+ # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+ #
+ SET use_stat_tables= PREFERABLY;
+ CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+ INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ ANALYZE TABLE t1;
+ Table Op Msg_type Msg_text
+ test.t1 analyze status Engine-independent statistics collected
+ test.t1 analyze status OK
+ SELECT * FROM t1;
+ pk c
+ 1 foo
+ 2 bar
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+ test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+ CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+ SELECT * FROM t1;
+ pk a
+ SELECT * FROM mysql.column_stats;
+ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+ DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16757: manual addition of min/max statistics for BLOB
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
+test.t1 analyze status OK
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+DELETE FROM mysql.column_stats
+WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+SELECT pk FROM t1;
+pk
+1
+2
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+SELECT * FROM t1;
+pk c
+1 foo
+2 bar
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+pk a
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+SELECT MAX(pk) FROM t1;
+MAX(pk)
+NULL
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
SET SESSION STORAGE_ENGINE=DEFAULT;
diff --cc mysql-test/t/derived.test
index b0415cf3b04,eeaf94adcee..778d141b80f
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@@ -935,39 -936,25 +936,59 @@@ call pr(2)
drop procedure pr;
drop view v1;
drop table t1;
+ set @@join_cache_level= @save_join_cache_level;
+ --echo #
+ --echo # MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views
+ --echo #
+
+ CREATE TABLE t1 (c1 text, c2 int);
+ INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3);
+ CREATE TABLE t2 (c1 text, c2 int);
+ INSERT INTO t2 VALUES ('b',2), ('c',3);
+ CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+
+ explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ set @save_join_cache_level= @@join_cache_level;
+ set @@join_cache_level=4;
+ explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
+ drop table t1,t2;
+ drop view v1;
+ set @@join_cache_level= @save_join_cache_level;
--echo # end of 5.5
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8747 Wrong result for SELECT..WHERE derived_table_column='a' AND derived_table_column<>_latin1'A' COLLATE latin1_bin
+--echo #
+CREATE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('a'),('A');
+SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-8749 Wrong result for SELECT..WHERE derived_table_enum_column='number' AND derived_table_enum_column OP number2
+--echo #
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
diff --cc mysql-test/t/stat_tables.test
index 51c9995b6fa,d69b00618ea..2c9c1eca7d3
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@@ -305,66 -305,54 +305,116 @@@ drop database db1
drop database db2;
drop table t1;
+ --echo #
+ --echo # MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed
+ --echo #
+
+ SET use_stat_tables = PREFERABLY;
+ SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' );
+ set use_stat_tables=@save_use_stat_tables;
+
+ --echo #
+ --echo # MDEV-16757: manual addition of min/max statistics for BLOB
+ --echo #
+
+ SET use_stat_tables= PREFERABLY;
+
+ CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+ INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ ANALYZE TABLE t1;
+ --sorted_result
+ SELECT * FROM mysql.column_stats;
+ DELETE FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='t';
+ INSERT INTO mysql.column_stats VALUES
+ ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+ --sorted_result
+ SELECT * FROM mysql.column_stats;
+
+ SELECT pk FROM t1;
+
+ DROP TABLE t1;
+
+ set use_stat_tables=@save_use_stat_tables;
+
+ --echo #
+ --echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+ --echo #
+
+ SET use_stat_tables= PREFERABLY;
+
+ CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+ INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ ANALYZE TABLE t1;
+ SELECT * FROM t1;
+ SELECT * FROM mysql.column_stats;
+
+ CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+ SELECT * FROM t1;
+ SELECT * FROM mysql.column_stats;
+
+ DROP TABLE t1;
+
set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # MDEV-16757: manual addition of min/max statistics for BLOB
+--echo #
+
+SET use_stat_tables= PREFERABLY;
+
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+DELETE FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+ ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+--sorted_result
+SELECT * FROM mysql.column_stats;
+
+SELECT pk FROM t1;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+--echo #
+
+SET use_stat_tables= PREFERABLY;
+
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+SELECT * FROM t1;
+SELECT * FROM mysql.column_stats;
+
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+SELECT * FROM mysql.column_stats;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
+
+
+--echo #
+--echo # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
+--echo #
+
+SET use_stat_tables= PREFERABLY;
+
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+
+SELECT MAX(pk) FROM t1;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
diff --cc sql/mysqld.cc
index 5f954f7576d,75d6cca7fda..0f5147c8cf1
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@@ -5889,14 -5497,10 +5894,14 @@@ int mysqld_main(int argc, char **argv
(char*) "" : mysqld_unix_port),
mysqld_port,
MYSQL_COMPILATION_COMMENT);
+
#ifndef _WIN32
- /* Only close stdin if it was open initinally. */
- if (please_close_stdin)
+ // try to keep fd=0 busy
- if (!freopen("/dev/null", "r", stdin))
++ if (please_close_stdin && !freopen("/dev/null", "r", stdin))
+ {
+ // fall back on failure
fclose(stdin);
+ }
#endif
#if defined(_WIN32) && !defined(EMBEDDED_LIBRARY)
diff --cc sql/sql_base.cc
index c2bf37a1064,1ed57a051d8..2a9b409dff5
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@@ -7451,7 -7405,7 +7452,7 @@@ store_natural_using_join_columns(THD *t
nj_col_1= it_1.get_natural_column_ref();
if (nj_col_1->is_common)
{
- natural_using_join->join_columns->push_back(nj_col_1, thd->mem_root);
- join_columns->push_back(nj_col_1);
++ join_columns->push_back(nj_col_1, thd->mem_root);
/* Reset the common columns for the next call to mark_common_columns. */
nj_col_1->is_common= FALSE;
}
@@@ -7505,7 -7459,8 +7506,8 @@@
}
if (non_join_columns->elements > 0)
- natural_using_join->join_columns->append(non_join_columns);
- join_columns->concat(non_join_columns);
++ join_columns->append(non_join_columns);
+ natural_using_join->join_columns= join_columns;
natural_using_join->is_join_columns_complete= TRUE;
result= FALSE;
1
0
[Commits] 98dd5324b6d: MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
by Varun 19 Jul '18
by Varun 19 Jul '18
19 Jul '18
revision-id: 98dd5324b6d6205549a4dd9b2f8358737f86fd23 (mariadb-5.5.60-44-g98dd5324b6d)
parent(s): 6bc722b85308709b1df647d97ee83247061c3aa2
author: Varun Gupta
committer: Varun Gupta
timestamp: 2018-07-19 15:47:32 +0530
message:
MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
So to push index condition for each join tab we have calculate the index condition that can be pushed and then
remove this index condition from the original condition. This is done through the function make_cond_remainder.
The problem is the function make_cond_remainder does not remove index condition when there is an OR operator.
Fixed this by making the function make_cond_remainder to keep in mind of the OR operator.
Also updated results for multipe test files which were incorrectly updated by the commit e0c1b3f24246d22e6785315f9a8448bd9a590422
code which was supposed to remove the conditon present in the index
condition was not getting executed when the condition had OR operator, with AND the pushed index conditon was getting removed from where.
---
mysql-test/r/innodb_icp.result | 2 +-
mysql-test/r/join_cache.result | 2 +-
mysql-test/r/mrr_icp_extra.result | 6 +++---
mysql-test/r/myisam_icp.result | 23 +++++++++++++++++++++--
mysql-test/r/myisam_mrr.result | 4 ++--
mysql-test/r/order_by.result | 4 ++--
mysql-test/r/range.result | 8 ++++----
mysql-test/r/range_mrr_icp.result | 8 ++++----
mysql-test/r/range_vs_index_merge.result | 10 +++++-----
mysql-test/r/range_vs_index_merge_innodb.result | 8 ++++----
mysql-test/r/select.result | 2 +-
mysql-test/r/select_jcl6.result | 2 +-
mysql-test/r/select_pkeycache.result | 2 +-
mysql-test/r/subselect_mat_cost.result | 4 ++--
mysql-test/r/xtradb_mrr.result | 4 ++--
mysql-test/t/myisam_icp.test | 15 +++++++++++++++
sql/opt_index_cond_pushdown.cc | 13 +++++--------
17 files changed, 74 insertions(+), 43 deletions(-)
diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index 9ca02595939..0b282b21ae0 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -649,7 +649,7 @@ SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort
+1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort
SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index f1e6fb577c8..2cd9d6311fb 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -4985,7 +4985,7 @@ EXPLAIN
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
diff --git a/mysql-test/r/mrr_icp_extra.result b/mysql-test/r/mrr_icp_extra.result
index f7adcfb19fd..b5d712c1def 100644
--- a/mysql-test/r/mrr_icp_extra.result
+++ b/mysql-test/r/mrr_icp_extra.result
@@ -72,7 +72,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
insert into t1 values (2, 11), (1, 11), (4, 14), (3, 14), (6, 12), (5, 12);
explain select * from t1 where b=1 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=1 or b is null order by a;
a b
1 1
@@ -81,7 +81,7 @@ a b
4 NULL
explain select * from t1 where b=2 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort
select * from t1 where b=2 or b is null order by a;
a b
3 NULL
@@ -123,7 +123,7 @@ Table Op Msg_type Msg_text
test.t1 optimize status OK
explain select * from t1 force index (a) where a=0 or a=2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Rowid-ordered scan
select * from t1 force index (a) where a=0 or a=2;
a b c
0 NULL 0
diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result
index 9b31bca7932..1ecd936c971 100644
--- a/mysql-test/r/myisam_icp.result
+++ b/mysql-test/r/myisam_icp.result
@@ -511,7 +511,7 @@ SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
@@ -653,7 +653,7 @@ SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort
+1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort
SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
@@ -976,4 +976,23 @@ explain select * from t1 where a=3 and col > 500 order by a desc, col desc;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 10 NULL 10 Using where
drop table t0, t1;
+#
+# MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
+#
+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 (key1 int not null, filler char(100));
+insert into t1 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B;
+alter table t1 add key(key1);
+explain select * from t1 where key1 < 3 or key1 > 99999;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range key1 key1 4 NULL 20 Using index condition; Rowid-ordered scan
+select * from t1 where key1 < 3 or key1 > 99999;
+key1 filler
+0 filler-data
+1 filler-data
+2 filler-data
+drop table ten,one_k,t1;
set optimizer_switch=@myisam_icp_tmp;
diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result
index bd50df7c40e..12954718973 100644
--- a/mysql-test/r/myisam_mrr.result
+++ b/mysql-test/r/myisam_mrr.result
@@ -188,7 +188,7 @@ explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 10 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 10 Using index condition; Rowid-ordered scan
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
a b c filler
@@ -210,7 +210,7 @@ NULL NULL NULL NULL-1
explain
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 21 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 21 Using index condition; Rowid-ordered scan
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
a b c filler
b-1 NULL c-1 NULL-15
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index d3f5cd89eee..8b0241d3f62 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -654,7 +654,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
insert into t1 values (12, 11), (11, 11), (14, 3), (13, 5), (16, 12), (15, 12);
explain select * from t1 where b=1 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=1 or b is null order by a;
a b
1 1
@@ -663,7 +663,7 @@ a b
4 NULL
explain select * from t1 where b=2 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort
select * from t1 where b=2 or b is null order by a;
a b
3 NULL
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 630a692cef6..84a074d614b 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -930,7 +930,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range status status 23 NULL 10 Using index condition
EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Using where
+1 SIMPLE t1 range status status 23 NULL 10 Using index condition
SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
id status
53 C
@@ -1034,13 +1034,13 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 13 NULL # Using index condition
explain select * from t1 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 13 NULL # Using index condition; Using where
+1 SIMPLE t1 range a a 13 NULL # Using index condition
explain select * from t2 where a between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ref a a 13 const # Using index condition
explain select * from t2 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref a a 13 const # Using index condition; Using where
+1 SIMPLE t2 ref a a 13 const # Using index condition
update t1 set a='b' where a<>'a';
explain select * from t1 where a not between 'b' and 'b';
id select_type table type possible_keys key key_len ref rows Extra
@@ -1972,7 +1972,7 @@ INSERT INTO t100(I,J) VALUES(8,26);
EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Using where
+1 SIMPLE t100 range I I 10 NULL 4 Using index condition
SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
K I J
diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result
index 3f5de5b0189..8b17afd2c51 100644
--- a/mysql-test/r/range_mrr_icp.result
+++ b/mysql-test/r/range_mrr_icp.result
@@ -932,7 +932,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Rowid-ordered scan
EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Rowid-ordered scan
SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
id status
53 C
@@ -1036,13 +1036,13 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 13 NULL # Using index condition; Rowid-ordered scan
explain select * from t1 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 13 NULL # Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range a a 13 NULL # Using index condition; Rowid-ordered scan
explain select * from t2 where a between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ref a a 13 const # Using index condition
explain select * from t2 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref a a 13 const # Using index condition; Using where
+1 SIMPLE t2 ref a a 13 const # Using index condition
update t1 set a='b' where a<>'a';
explain select * from t1 where a not between 'b' and 'b';
id select_type table type possible_keys key key_len ref rows Extra
@@ -1974,7 +1974,7 @@ INSERT INTO t100(I,J) VALUES(8,26);
EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Rowid-ordered scan
SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
K I J
diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result
index 0acaed37d22..649346b3ee3 100644
--- a/mysql-test/r/range_vs_index_merge.result
+++ b/mysql-test/r/range_vs_index_merge.result
@@ -327,11 +327,11 @@ ID Name Country Population
EXPLAIN
SELECT * FROM City WHERE (ID < 10) OR (ID BETWEEN 100 AND 110);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 21 Using index condition; Using where
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 21 Using index condition
EXPLAIN
SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 100 AND 200);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 201 Using index condition; Using where
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 201 Using index condition
EXPLAIN
SELECT * FROM City WHERE (ID < 600) OR (ID BETWEEN 900 AND 1500);
id select_type table type possible_keys key key_len ref rows Extra
@@ -343,11 +343,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 222 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 222 Using index condition
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 72 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 72 Using index condition
EXPLAIN
SELECT * FROM City
WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG')))
@@ -1163,7 +1163,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 29 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 29 Using index condition
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result
index ff4940281ce..f0af0fded0c 100644
--- a/mysql-test/r/range_vs_index_merge_innodb.result
+++ b/mysql-test/r/range_vs_index_merge_innodb.result
@@ -344,11 +344,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 394 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 394 Using index condition
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 133 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 133 Using index condition
EXPLAIN
SELECT * FROM City
WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG')))
@@ -1077,7 +1077,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
@@ -1164,7 +1164,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index cff5caf7b7a..781c26a4f17 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3422,7 +3422,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 359e7c8e2e7..f40354bbf14 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -3433,7 +3433,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index cff5caf7b7a..781c26a4f17 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -3422,7 +3422,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
diff --git a/mysql-test/r/subselect_mat_cost.result b/mysql-test/r/subselect_mat_cost.result
index 081196a227b..b7b25a001d9 100644
--- a/mysql-test/r/subselect_mat_cost.result
+++ b/mysql-test/r/subselect_mat_cost.result
@@ -285,7 +285,7 @@ WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English
AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish')
AND Code = Country;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 45 Using index condition; Using where; Rowid-ordered scan
+1 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 45 Using index condition; Rowid-ordered scan
1 PRIMARY Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where
2 MATERIALIZED CountryLanguage ref PRIMARY,Language Language 30 const 47 Using index condition
SELECT Country.Name
@@ -348,7 +348,7 @@ FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000))
AND Language IN ('English','Spanish');
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY CountryLanguage range Language Language 30 NULL 72 Using index condition; Using where; Rowid-ordered scan
-2 DEPENDENT SUBQUERY City ref CityName CityName 35 func 1 Using index condition; Using where
+2 DEPENDENT SUBQUERY City ref CityName CityName 35 func 1 Using index condition
2 DEPENDENT SUBQUERY Country eq_ref PRIMARY PRIMARY 3 world.City.Country 1 Using where; Using index
select count(*)
from CountryLanguage
diff --git a/mysql-test/r/xtradb_mrr.result b/mysql-test/r/xtradb_mrr.result
index c238d0530af..e679606c2ca 100644
--- a/mysql-test/r/xtradb_mrr.result
+++ b/mysql-test/r/xtradb_mrr.result
@@ -186,7 +186,7 @@ explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Rowid-ordered scan
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
a b c filler
@@ -208,7 +208,7 @@ NULL NULL NULL NULL-1
explain
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Rowid-ordered scan
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
a b c filler
b-1 NULL c-1 NULL-15
diff --git a/mysql-test/t/myisam_icp.test b/mysql-test/t/myisam_icp.test
index 508c282b1dc..b6d35968b1c 100644
--- a/mysql-test/t/myisam_icp.test
+++ b/mysql-test/t/myisam_icp.test
@@ -282,5 +282,20 @@ explain select * from t1 where a=3 and col > 500 order by a desc, col desc;
drop table t0, t1;
+--echo #
+--echo # MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
+--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 (key1 int not null, filler char(100));
+insert into t1 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B;
+alter table t1 add key(key1);
+explain select * from t1 where key1 < 3 or key1 > 99999;
+select * from t1 where key1 < 3 or key1 > 99999;
+drop table ten,one_k,t1;
+
set optimizer_switch=@myisam_icp_tmp;
diff --git a/sql/opt_index_cond_pushdown.cc b/sql/opt_index_cond_pushdown.cc
index df9dae8e442..35093dee235 100644
--- a/sql/opt_index_cond_pushdown.cc
+++ b/sql/opt_index_cond_pushdown.cc
@@ -263,6 +263,10 @@ Item *make_cond_for_index(Item *cond, TABLE *table, uint keyno,
Item *make_cond_remainder(Item *cond, TABLE *table, uint keyno,
bool other_tbls_ok, bool exclude_index)
{
+ if (exclude_index &&
+ uses_index_fields_only(cond, table, keyno, other_tbls_ok))
+ return NULL;
+
if (cond->type() == Item::COND_ITEM)
{
table_map tbl_map= 0;
@@ -317,14 +321,7 @@ Item *make_cond_remainder(Item *cond, TABLE *table, uint keyno,
return new_cond;
}
}
- else
- {
- if (exclude_index &&
- uses_index_fields_only(cond, table, keyno, other_tbls_ok))
- return 0;
- else
- return cond;
- }
+ return cond;
}
1
0
[Commits] 09f1476: MDEV-16777: galera.galera_gra_log fails with File ...GRA_*.log not found error
by psergeyï¼ askmonty.org 19 Jul '18
by psergeyï¼ askmonty.org 19 Jul '18
19 Jul '18
revision-id: 09f147659f36a30d7075885d88392c9a41244505
parent(s): 4d06b7e1bd3b825da32c9200e6f5ca609add0e13
committer: Sergei Petrunia
branch nick: 10.1-r2
timestamp: 2018-07-19 12:07:07 +0300
message:
MDEV-16777: galera.galera_gra_log fails with File ...GRA_*.log not found error
snprintf returns the number of bytes it wrote (or would have written) NOT
counting the \0 terminal character.
The buffer size it accepts as argument DOES COUNT the \0 character.
Pass the right parameter value.
---
sql/wsrep_binlog.cc | 14 +++++++++++---
1 file changed, 11 insertions(+), 3 deletions(-)
diff --git a/sql/wsrep_binlog.cc b/sql/wsrep_binlog.cc
index 902190d..c7674cd 100644
--- a/sql/wsrep_binlog.cc
+++ b/sql/wsrep_binlog.cc
@@ -326,11 +326,16 @@ void wsrep_dump_rbr_buf(THD *thd, const void* rbr_buf, size_t buf_len)
WSREP_ERROR("snprintf error: %d, skipping dump.", len);
return;
}
+ /*
+ len doesn't count the \0 end-of-string. Use len+1 below
+ to alloc and pass as an argument to snprintf.
+ */
char *filename= (char *)malloc(len+1);
- int len1= snprintf(filename, len, "%s/GRA_%ld_%lld.log",
+ int len1= snprintf(filename, len+1, "%s/GRA_%ld_%lld.log",
wsrep_data_home_dir, thd->thread_id,
(long long)wsrep_thd_trx_seqno(thd));
+
if (len > len1)
{
WSREP_ERROR("RBR dump path truncated: %d, skipping dump.", len);
@@ -469,7 +474,10 @@ void wsrep_dump_rbr_buf_with_header(THD *thd, const void *rbr_buf,
int len= snprintf(NULL, 0, "%s/GRA_%ld_%lld_v2.log",
wsrep_data_home_dir, thd->thread_id,
thd_trx_seqno);
-
+ /*
+ len doesn't count the \0 end-of-string. Use len+1 below
+ to alloc and pass as an argument to snprintf.
+ */
char *filename;
if (len < 0 || !(filename= (char*)malloc(len+1)))
{
@@ -477,7 +485,7 @@ void wsrep_dump_rbr_buf_with_header(THD *thd, const void *rbr_buf,
DBUG_VOID_RETURN;
}
- int len1= snprintf(filename, len, "%s/GRA_%ld_%lld_v2.log",
+ int len1= snprintf(filename, len+1, "%s/GRA_%ld_%lld_v2.log",
wsrep_data_home_dir, thd->thread_id,
thd_trx_seqno);
1
0
[Commits] c8499c0c580: MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
by Varun 19 Jul '18
by Varun 19 Jul '18
19 Jul '18
revision-id: c8499c0c580cbf3722770f9766af0bc47ac045a3 (mariadb-5.5.60-44-gc8499c0c580)
parent(s): 6bc722b85308709b1df647d97ee83247061c3aa2
author: Varun Gupta
committer: Varun Gupta
timestamp: 2018-07-19 14:22:30 +0530
message:
MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
So to push index condition for each join tab we have calculate the index condition that can be pushed and then
remove this index condition from the original condition. This is done through the function make_cond_remainder.
The problem is the function make_cond_remainder does not remove index condition when there is an OR operator.
Fixed this by making the function make_cond_remainder to keep in mind of the OR operator.
Also updated results for multipe test files which were incorrectly updated by the commit e0c1b3f24246d22e6785315f9a8448bd9a590422
code which was supposed to remove the conditon present in the index
condition was not getting executed when the condition had OR operator, with AND the pushed index conditon was getting removed from where.
---
mysql-test/r/innodb_icp.result | 2 +-
mysql-test/r/join_cache.result | 2 +-
mysql-test/r/mrr_icp_extra.result | 6 +++---
mysql-test/r/myisam_icp.result | 26 +++++++++++++++++++++++--
mysql-test/r/myisam_mrr.result | 4 ++--
mysql-test/r/order_by.result | 4 ++--
mysql-test/r/range.result | 8 ++++----
mysql-test/r/range_mrr_icp.result | 8 ++++----
mysql-test/r/range_vs_index_merge.result | 10 +++++-----
mysql-test/r/range_vs_index_merge_innodb.result | 8 ++++----
mysql-test/r/select.result | 2 +-
mysql-test/r/select_jcl6.result | 2 +-
mysql-test/r/select_pkeycache.result | 2 +-
mysql-test/r/subselect_mat_cost.result | 4 ++--
mysql-test/r/xtradb_mrr.result | 4 ++--
mysql-test/t/myisam_icp.test | 15 ++++++++++++++
sql/opt_index_cond_pushdown.cc | 13 +++++--------
17 files changed, 77 insertions(+), 43 deletions(-)
diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index 9ca02595939..0b282b21ae0 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -649,7 +649,7 @@ SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort
+1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort
SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index f1e6fb577c8..2cd9d6311fb 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -4985,7 +4985,7 @@ EXPLAIN
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
diff --git a/mysql-test/r/mrr_icp_extra.result b/mysql-test/r/mrr_icp_extra.result
index f7adcfb19fd..b5d712c1def 100644
--- a/mysql-test/r/mrr_icp_extra.result
+++ b/mysql-test/r/mrr_icp_extra.result
@@ -72,7 +72,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
insert into t1 values (2, 11), (1, 11), (4, 14), (3, 14), (6, 12), (5, 12);
explain select * from t1 where b=1 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=1 or b is null order by a;
a b
1 1
@@ -81,7 +81,7 @@ a b
4 NULL
explain select * from t1 where b=2 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort
select * from t1 where b=2 or b is null order by a;
a b
3 NULL
@@ -123,7 +123,7 @@ Table Op Msg_type Msg_text
test.t1 optimize status OK
explain select * from t1 force index (a) where a=0 or a=2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Rowid-ordered scan
select * from t1 force index (a) where a=0 or a=2;
a b c
0 NULL 0
diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result
index 9b31bca7932..2a3e9c9e0ba 100644
--- a/mysql-test/r/myisam_icp.result
+++ b/mysql-test/r/myisam_icp.result
@@ -511,7 +511,7 @@ SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
@@ -653,7 +653,7 @@ SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort
+1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort
SELECT * FROM t1
WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania'
ORDER BY a;
@@ -976,4 +976,26 @@ explain select * from t1 where a=3 and col > 500 order by a desc, col desc;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 10 NULL 10 Using where
drop table t0, t1;
+#
+# MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
+#
+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 (key1 int not null, filler char(100)) engine=rocksdb;
+Warnings:
+Warning 1286 Unknown storage engine 'rocksdb'
+Warning 1266 Using storage engine MyISAM for table 't1'
+insert into t1 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B;
+alter table t1 add key(key1);
+explain select * from t1 where key1 < 3 or key1 > 99999;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range key1 key1 4 NULL 20 Using index condition; Rowid-ordered scan
+select * from t1 where key1 < 3 or key1 > 99999;
+key1 filler
+0 filler-data
+1 filler-data
+2 filler-data
+drop table ten,one_k,t1;
set optimizer_switch=@myisam_icp_tmp;
diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result
index bd50df7c40e..12954718973 100644
--- a/mysql-test/r/myisam_mrr.result
+++ b/mysql-test/r/myisam_mrr.result
@@ -188,7 +188,7 @@ explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 10 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 10 Using index condition; Rowid-ordered scan
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
a b c filler
@@ -210,7 +210,7 @@ NULL NULL NULL NULL-1
explain
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 21 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 21 Using index condition; Rowid-ordered scan
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
a b c filler
b-1 NULL c-1 NULL-15
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index d3f5cd89eee..8b0241d3f62 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -654,7 +654,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
insert into t1 values (12, 11), (11, 11), (14, 3), (13, 5), (16, 12), (15, 12);
explain select * from t1 where b=1 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=1 or b is null order by a;
a b
1 1
@@ -663,7 +663,7 @@ a b
4 NULL
explain select * from t1 where b=2 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort
select * from t1 where b=2 or b is null order by a;
a b
3 NULL
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 630a692cef6..84a074d614b 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -930,7 +930,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range status status 23 NULL 10 Using index condition
EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Using where
+1 SIMPLE t1 range status status 23 NULL 10 Using index condition
SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
id status
53 C
@@ -1034,13 +1034,13 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 13 NULL # Using index condition
explain select * from t1 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 13 NULL # Using index condition; Using where
+1 SIMPLE t1 range a a 13 NULL # Using index condition
explain select * from t2 where a between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ref a a 13 const # Using index condition
explain select * from t2 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref a a 13 const # Using index condition; Using where
+1 SIMPLE t2 ref a a 13 const # Using index condition
update t1 set a='b' where a<>'a';
explain select * from t1 where a not between 'b' and 'b';
id select_type table type possible_keys key key_len ref rows Extra
@@ -1972,7 +1972,7 @@ INSERT INTO t100(I,J) VALUES(8,26);
EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Using where
+1 SIMPLE t100 range I I 10 NULL 4 Using index condition
SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
K I J
diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result
index 3f5de5b0189..8b17afd2c51 100644
--- a/mysql-test/r/range_mrr_icp.result
+++ b/mysql-test/r/range_mrr_icp.result
@@ -932,7 +932,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Rowid-ordered scan
EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Rowid-ordered scan
SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
id status
53 C
@@ -1036,13 +1036,13 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 13 NULL # Using index condition; Rowid-ordered scan
explain select * from t1 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 13 NULL # Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range a a 13 NULL # Using index condition; Rowid-ordered scan
explain select * from t2 where a between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ref a a 13 const # Using index condition
explain select * from t2 where a = 'a' or a='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref a a 13 const # Using index condition; Using where
+1 SIMPLE t2 ref a a 13 const # Using index condition
update t1 set a='b' where a<>'a';
explain select * from t1 where a not between 'b' and 'b';
id select_type table type possible_keys key key_len ref rows Extra
@@ -1974,7 +1974,7 @@ INSERT INTO t100(I,J) VALUES(8,26);
EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Rowid-ordered scan
SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
K I J
diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result
index 0acaed37d22..649346b3ee3 100644
--- a/mysql-test/r/range_vs_index_merge.result
+++ b/mysql-test/r/range_vs_index_merge.result
@@ -327,11 +327,11 @@ ID Name Country Population
EXPLAIN
SELECT * FROM City WHERE (ID < 10) OR (ID BETWEEN 100 AND 110);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 21 Using index condition; Using where
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 21 Using index condition
EXPLAIN
SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 100 AND 200);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 201 Using index condition; Using where
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 201 Using index condition
EXPLAIN
SELECT * FROM City WHERE (ID < 600) OR (ID BETWEEN 900 AND 1500);
id select_type table type possible_keys key key_len ref rows Extra
@@ -343,11 +343,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 222 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 222 Using index condition
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 72 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 72 Using index condition
EXPLAIN
SELECT * FROM City
WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG')))
@@ -1163,7 +1163,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 29 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 29 Using index condition
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result
index ff4940281ce..f0af0fded0c 100644
--- a/mysql-test/r/range_vs_index_merge_innodb.result
+++ b/mysql-test/r/range_vs_index_merge_innodb.result
@@ -344,11 +344,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 394 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 394 Using index condition
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 133 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 133 Using index condition
EXPLAIN
SELECT * FROM City
WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG')))
@@ -1077,7 +1077,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
@@ -1164,7 +1164,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index cff5caf7b7a..781c26a4f17 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3422,7 +3422,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 359e7c8e2e7..f40354bbf14 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -3433,7 +3433,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index cff5caf7b7a..781c26a4f17 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -3422,7 +3422,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition
DROP TABLE t1,t2;
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
diff --git a/mysql-test/r/subselect_mat_cost.result b/mysql-test/r/subselect_mat_cost.result
index 081196a227b..b7b25a001d9 100644
--- a/mysql-test/r/subselect_mat_cost.result
+++ b/mysql-test/r/subselect_mat_cost.result
@@ -285,7 +285,7 @@ WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English
AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish')
AND Code = Country;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 45 Using index condition; Using where; Rowid-ordered scan
+1 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 45 Using index condition; Rowid-ordered scan
1 PRIMARY Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where
2 MATERIALIZED CountryLanguage ref PRIMARY,Language Language 30 const 47 Using index condition
SELECT Country.Name
@@ -348,7 +348,7 @@ FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000))
AND Language IN ('English','Spanish');
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY CountryLanguage range Language Language 30 NULL 72 Using index condition; Using where; Rowid-ordered scan
-2 DEPENDENT SUBQUERY City ref CityName CityName 35 func 1 Using index condition; Using where
+2 DEPENDENT SUBQUERY City ref CityName CityName 35 func 1 Using index condition
2 DEPENDENT SUBQUERY Country eq_ref PRIMARY PRIMARY 3 world.City.Country 1 Using where; Using index
select count(*)
from CountryLanguage
diff --git a/mysql-test/r/xtradb_mrr.result b/mysql-test/r/xtradb_mrr.result
index c238d0530af..e679606c2ca 100644
--- a/mysql-test/r/xtradb_mrr.result
+++ b/mysql-test/r/xtradb_mrr.result
@@ -186,7 +186,7 @@ explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Rowid-ordered scan
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
a b c filler
@@ -208,7 +208,7 @@ NULL NULL NULL NULL-1
explain
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Rowid-ordered scan
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
a b c filler
b-1 NULL c-1 NULL-15
diff --git a/mysql-test/t/myisam_icp.test b/mysql-test/t/myisam_icp.test
index 508c282b1dc..c7dc90394be 100644
--- a/mysql-test/t/myisam_icp.test
+++ b/mysql-test/t/myisam_icp.test
@@ -282,5 +282,20 @@ explain select * from t1 where a=3 and col > 500 order by a desc, col desc;
drop table t0, t1;
+--echo #
+--echo # MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
+--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 (key1 int not null, filler char(100)) engine=rocksdb;
+insert into t1 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B;
+alter table t1 add key(key1);
+explain select * from t1 where key1 < 3 or key1 > 99999;
+select * from t1 where key1 < 3 or key1 > 99999;
+drop table ten,one_k,t1;
+
set optimizer_switch=@myisam_icp_tmp;
diff --git a/sql/opt_index_cond_pushdown.cc b/sql/opt_index_cond_pushdown.cc
index df9dae8e442..35093dee235 100644
--- a/sql/opt_index_cond_pushdown.cc
+++ b/sql/opt_index_cond_pushdown.cc
@@ -263,6 +263,10 @@ Item *make_cond_for_index(Item *cond, TABLE *table, uint keyno,
Item *make_cond_remainder(Item *cond, TABLE *table, uint keyno,
bool other_tbls_ok, bool exclude_index)
{
+ if (exclude_index &&
+ uses_index_fields_only(cond, table, keyno, other_tbls_ok))
+ return NULL;
+
if (cond->type() == Item::COND_ITEM)
{
table_map tbl_map= 0;
@@ -317,14 +321,7 @@ Item *make_cond_remainder(Item *cond, TABLE *table, uint keyno,
return new_cond;
}
}
- else
- {
- if (exclude_index &&
- uses_index_fields_only(cond, table, keyno, other_tbls_ok))
- return 0;
- else
- return cond;
- }
+ return cond;
}
1
0
[Commits] b8e75a2: Issue#857: MyRocks: Incorrect behavior when miltiple statements fail inside a transaction
by psergeyï¼ askmonty.org 18 Jul '18
by psergeyï¼ askmonty.org 18 Jul '18
18 Jul '18
revision-id: b8e75a29dd371913772a7eaaddb56430c9e8ac8e
parent(s): a47797f16671a44a92632935fc93e302e0c969ff
committer: Sergei Petrunia
branch nick: mysql-5.6-rocksdb-spetrunia
timestamp: 2018-07-18 12:26:31 +0300
message:
Issue#857: MyRocks: Incorrect behavior when miltiple statements fail inside a transaction
rollback_to_stmt_savepoint() calls do_rollback_to_savepoint(). This
removes the changes made by this statement, and also removes the last
set savepoint.
Before we start processing the next statement, we need to set the new
savepoint, so we will have something to rollback to if the next statement
fails.
Since rollback_to_stmt_savepoint always sets a new savepoint now,
m_n_savepoints is now redundant and is removed.
Squash with D7509380
---
mysql-test/suite/rocksdb/r/transaction.result | 17 +++++++++++++++++
mysql-test/suite/rocksdb/t/transaction.test | 23 +++++++++++++++++++++++
storage/rocksdb/ha_rocksdb.cc | 16 ++++++++--------
3 files changed, 48 insertions(+), 8 deletions(-)
diff --git a/mysql-test/suite/rocksdb/r/transaction.result b/mysql-test/suite/rocksdb/r/transaction.result
index 006baaf..8a5825b 100644
--- a/mysql-test/suite/rocksdb/r/transaction.result
+++ b/mysql-test/suite/rocksdb/r/transaction.result
@@ -958,3 +958,20 @@ a
rollback;
drop function func;
drop table t1,t2,t3;
+#
+# MDEV-16710: Slave SQL: Could not execute Update_rows_v1 event with RocksDB and triggers
+# Issue#857: MyRocks: Incorrect behavior when multiple statements fail inside a transaction
+#
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=RocksDB;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=RocksDB;
+CREATE TRIGGER tr AFTER INSERT ON t2 FOR EACH ROW INSERT INTO non_existing_table VALUES (NULL);
+BEGIN;
+DELETE FROM t1;
+INSERT INTO t2 VALUES (1);
+INSERT INTO t2 VALUES (2);
+# Must return empty result:
+SELECT * FROM t1;
+a
+COMMIT;
+drop table t1,t2;
diff --git a/mysql-test/suite/rocksdb/t/transaction.test b/mysql-test/suite/rocksdb/t/transaction.test
index 3350db9..129484b 100644
--- a/mysql-test/suite/rocksdb/t/transaction.test
+++ b/mysql-test/suite/rocksdb/t/transaction.test
@@ -133,3 +133,26 @@ rollback;
drop function func;
drop table t1,t2,t3;
+--echo #
+--echo # MDEV-16710: Slave SQL: Could not execute Update_rows_v1 event with RocksDB and triggers
+--echo # Issue#857: MyRocks: Incorrect behavior when multiple statements fail inside a transaction
+--echo #
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=RocksDB;
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=RocksDB;
+
+CREATE TRIGGER tr AFTER INSERT ON t2 FOR EACH ROW INSERT INTO non_existing_table VALUES (NULL);
+
+BEGIN;
+DELETE FROM t1;
+--error 0,ER_NO_SUCH_TABLE
+INSERT INTO t2 VALUES (1);
+--error 0,ER_NO_SUCH_TABLE
+INSERT INTO t2 VALUES (2);
+--echo # Must return empty result:
+SELECT * FROM t1;
+COMMIT;
+
+drop table t1,t2;
+
diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc
index 57eaa83..a71a5b3 100644
--- a/storage/rocksdb/ha_rocksdb.cc
+++ b/storage/rocksdb/ha_rocksdb.cc
@@ -1875,8 +1875,6 @@ protected:
bool m_is_two_phase = false;
private:
- /* Number of RockDB savepoints taken */
- int m_n_savepoints;
/*
Number of write operations this transaction had when we took the last
savepoint (the idea is not to take another savepoint if we haven't made
@@ -2436,7 +2434,6 @@ public:
entire transaction.
*/
do_set_savepoint();
- m_n_savepoints= 1;
m_writes_at_last_savepoint= m_write_count;
}
@@ -2453,7 +2450,6 @@ public:
{
do_set_savepoint();
m_writes_at_last_savepoint= m_write_count;
- m_n_savepoints++;
}
}
@@ -2464,10 +2460,14 @@ public:
void rollback_to_stmt_savepoint() {
if (m_writes_at_last_savepoint != m_write_count) {
do_rollback_to_savepoint();
- if (!--m_n_savepoints) {
- do_set_savepoint();
- m_n_savepoints= 1;
- }
+ /*
+ RollbackToSavePoint "removes the most recent SetSavePoint()", so
+ we need to set it again so that next statement can roll back to this
+ stage.
+ It's ok to do it here at statement end (instead of doing it at next
+ statement start) because setting a savepoint is cheap.
+ */
+ do_set_savepoint();
m_writes_at_last_savepoint= m_write_count;
}
}
1
0
[Commits] 4b026efd7ba: MDEV-16769: Notes "WSREP: Waiting for SST to complete" flood the error log
by jan 18 Jul '18
by jan 18 Jul '18
18 Jul '18
revision-id: 4b026efd7ba029fe27709088f65a17f7c585a9fa (mariadb-10.1.34-24-g4b026efd7ba)
parent(s): e08ddccc35f1c199f503861bb63c6f7bcef2a9f6
author: Jan Lindström
committer: Jan Lindström
timestamp: 2018-07-18 11:22:17 +0300
message:
MDEV-16769: Notes "WSREP: Waiting for SST to complete" flood the error log
Used wrong initialization for condition timeout, should have
used set_timespec.
---
sql/wsrep_sst.cc | 26 ++++++++++++++++----------
1 file changed, 16 insertions(+), 10 deletions(-)
diff --git a/sql/wsrep_sst.cc b/sql/wsrep_sst.cc
index 60683bf740c..155051bfe84 100644
--- a/sql/wsrep_sst.cc
+++ b/sql/wsrep_sst.cc
@@ -204,8 +204,7 @@ void wsrep_sst_grab ()
// Wait for end of SST
bool wsrep_sst_wait ()
{
- struct timespec wtime = {WSREP_TIMEDWAIT_SECONDS, 0};
- uint32 total_wtime = 0;
+ double total_wtime = 0;
if (mysql_mutex_lock (&LOCK_wsrep_sst))
abort();
@@ -214,14 +213,18 @@ bool wsrep_sst_wait ()
while (!sst_complete)
{
+ struct timespec wtime;
+ set_timespec(wtime, WSREP_TIMEDWAIT_SECONDS);
+ time_t start_time = time(NULL);
mysql_cond_timedwait (&COND_wsrep_sst, &LOCK_wsrep_sst, &wtime);
+ time_t end_time = time(NULL);
if (!sst_complete)
{
- total_wtime += wtime.tv_sec;
- WSREP_DEBUG("Waiting for SST to complete. waited %u secs.", total_wtime);
+ total_wtime += difftime(end_time, start_time);
+ WSREP_DEBUG("Waiting for SST to complete. current seqno: %ld waited %f secs.", local_seqno, total_wtime);
service_manager_extend_timeout(WSREP_EXTEND_TIMEOUT_INTERVAL,
- "WSREP state transfer ongoing, current seqno: %ld", local_seqno);
+ "WSREP state transfer ongoing, current seqno: %ld waited %f secs", local_seqno, total_wtime);
}
}
@@ -1319,19 +1322,22 @@ void wsrep_SE_init_grab()
void wsrep_SE_init_wait()
{
- struct timespec wtime = {WSREP_TIMEDWAIT_SECONDS, 0};
- uint32 total_wtime=0;
+ double total_wtime=0;
while (SE_initialized == false)
{
+ struct timespec wtime;
+ set_timespec(wtime, WSREP_TIMEDWAIT_SECONDS);
+ time_t start_time = time(NULL);
mysql_cond_timedwait (&COND_wsrep_sst_init, &LOCK_wsrep_sst_init, &wtime);
+ time_t end_time = time(NULL);
if (!SE_initialized)
{
- total_wtime += wtime.tv_sec;
- WSREP_DEBUG("Waiting for SST to complete. waited %u secs.", total_wtime);
+ total_wtime += difftime(end_time, start_time);
+ WSREP_DEBUG("Waiting for SST to complete. current seqno: %ld waited %f secs.", local_seqno, total_wtime);
service_manager_extend_timeout(WSREP_EXTEND_TIMEOUT_INTERVAL,
- "WSREP SE initialization ongoing.");
+ "WSREP state transfer ongoing, current seqno: %ld waited %f secs", local_seqno, total_wtime);
}
}
1
0
17 Jul '18
revision-id: e5c26fdfab3d43ad2b0524a3bf29f59f6fab37d8 (mariadb-10.0.35-58-ge5c26fdfab3)
parent(s): 1fd84f9129f2ed98706f6e225b06b16a13d0ebd0 2fbf2277ffec86d69f793534da7043b6dd540780
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-07-17 16:56:21 +0200
message:
Merge branch '5.5' into bb-10.0-merge
mysql-test/r/derived.result | 33 +++++++++++++++++++++
mysql-test/r/join.result | 40 +++++++++++++++++++++++++
mysql-test/r/join_cache.result | 33 +++++++++++++++++++++
mysql-test/r/subselect_sj2_mat.result | 51 ++++++++++++++++++++++++++++++++
mysql-test/suite/vcol/t/vcol_misc.test | 2 ++
mysql-test/t/derived.test | 21 ++++++++++++++
mysql-test/t/join.test | 53 ++++++++++++++++++++++++++++++++++
mysql-test/t/join_cache.test | 32 +++++++++++++++++++-
mysql-test/t/subselect_sj2_mat.test | 49 +++++++++++++++++++++++++++++++
mysql-test/valgrind.supp | 13 +++++++++
scripts/mysql_install_db.sh | 5 ++++
sql/log.cc | 10 +++----
sql/opt_subselect.cc | 3 +-
sql/sql_base.cc | 12 ++++----
sql/sql_select.cc | 4 +--
sql/table.cc | 19 ++++++++----
16 files changed, 361 insertions(+), 19 deletions(-)
diff --cc mysql-test/r/join.result
index d500b38a8dc,7b0e7807e39..d065403371d
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@@ -1500,54 -1498,42 +1500,94 @@@ DROP VIEW v2
DROP TABLE t1,t2;
SET optimizer_switch=@save_optimizer_switch;
#
+ # MDEV-16512
+ # Server crashes in find_field_in_table_ref on 2nd execution of SP referring to
+ # non-existing field
+ #
+ CREATE TABLE t (i INT);
+ CREATE PROCEDURE p() SELECT t1.f FROM t AS t1 JOIN t AS t2 USING (f);
+ CALL p;
+ ERROR 42S22: Unknown column 'f' in 'from clause'
+ CALL p;
+ ERROR 42S22: Unknown column 'f' in 'from clause'
+ FLUSH TABLES;
+ CALL p;
+ ERROR 42S22: Unknown column 'f' in 'from clause'
+ DROP TABLE t;
+ CREATE TABLE t (f INT);
+ CALL p;
+ ERROR 42S22: Unknown column 'f' in 'from clause'
+ DROP TABLE t;
+ CREATE TABLE t (i INT);
+ CALL p;
+ ERROR 42S22: Unknown column 'f' in 'from clause'
+ DROP PROCEDURE p;
+ DROP TABLE t;
+ CREATE TABLE t1 (a INT, b INT);
+ CREATE TABLE t2 (a INT);
+ CREATE TABLE t3 (a INT, c INT);
+ CREATE TABLE t4 (a INT, c INT);
+ CREATE TABLE t5 (a INT, c INT);
+ CREATE PROCEDURE p1() SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a)
+ LEFT JOIN t5 USING (a)) USING (a);
+ CALL p1;
+ ERROR 23000: Column 'c' in field list is ambiguous
+ CALL p1;
+ ERROR 23000: Column 'c' in field list is ambiguous
+ DROP PROCEDURE p1;
+ DROP TABLE t1,t2,t3,t4,t5;
+ #
+ # End of MariaDB 5.5 tests
+ #
++#
+# Bug #35268: Parser can't handle STRAIGHT_JOIN with USING
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+CREATE TABLE t2 (a int);
+INSERT INTO t2 (a) VALUES (1),(2),(3),(4);
+EXPLAIN
+SELECT t1.a FROM t1 NATURAL INNER JOIN t2 ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join)
+SELECT t1.a FROM t1 NATURAL INNER JOIN t2 ORDER BY t1.a;
+a
+1
+2
+3
+4
+EXPLAIN
+SELECT t1.a FROM t1 STRAIGHT_JOIN t2 USING(a) ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
+SELECT t1.a FROM t1 STRAIGHT_JOIN t2 USING(a) ORDER BY t1.a;
+a
+1
+2
+3
+4
+EXPLAIN
+SELECT t1.a FROM t1 NATURAL STRAIGHT_JOIN t2 ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
+SELECT t1.a FROM t1 NATURAL STRAIGHT_JOIN t2 ORDER BY t1.a;
+a
+1
+2
+3
+4
+DROP TABLE t1,t2;
+#
+# MDEV-5635: join of a const table with non-const tables
+#
+CREATE TABLE t1 (a varchar(3) NOT NULL) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo');
+CREATE TABLE t2 (b varchar(3), c varchar(3), INDEX(b)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('bar', 'bar'),( 'qux', 'qux');
+SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2
+WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a );
+a b c b c
+DROP TABLE t1,t2;
diff --cc mysql-test/r/join_cache.result
index f2383ce2681,f1e6fb577c8..18c3a057398
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@@ -5871,86 -5872,36 +5871,119 @@@ SET join_buffer_space_limit= default
set optimizer_switch=@save_optimizer_switch;
DROP TABLE t1,t4,t5,t2;
#
+ # MDEV-16603: BNLH for query with materialized semi-join
+ #
+ set join_cache_level=4;
+ CREATE TABLE t1 ( i1 int, v1 varchar(1)) ENGINE=InnoDB;
+ INSERT INTO t1 VALUES (7,'x');
+ CREATE TABLE t2 (i1 int, v1 varchar(1), KEY v1 (v1,i1)) ENGINE=InnoDB;
+ INSERT INTO t2 VALUES
+ (NULL,'x'),(1,'x'),(3,'x'),(5,'x'),(8,'x'),(48,'x'),
+ (228,'x'),(3,'y'),(1,'z'),(9,'z');
+ CREATE TABLE temp
+ SELECT t1.i1 AS f1, t1.v1 AS f2 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1));
+ SELECT * FROM temp
+ WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)));
+ f1 f2
+ 7 x
+ 7 x
+ 7 x
+ 7 x
+ 7 x
+ 7 x
+ 7 x
+ EXPLAIN EXTENDED SELECT * FROM temp
+ WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)));
+ id select_type table type possible_keys key key_len ref rows filtered Extra
+ 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 100.00
+ 1 PRIMARY temp hash_ALL NULL #hash#$hj 9 test.t1.i1,test.t1.v1 7 100.00 Using where; Using join buffer (flat, BNLH join)
+ 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 1 100.00 Using where
+ 2 MATERIALIZED t2 hash_index v1 #hash#v1:v1 4:9 test.t1.v1 10 10.00 Using join buffer (flat, BNLH join)
+ Warnings:
+ Note 1003 select `test`.`temp`.`f1` AS `f1`,`test`.`temp`.`f2` AS `f2` from `test`.`temp` semi join (`test`.`t2` join `test`.`t1`) where ((`test`.`temp`.`f1` = `test`.`t1`.`i1`) and (`test`.`t2`.`v1` = `test`.`t1`.`v1`) and (`test`.`temp`.`f2` = `test`.`t1`.`v1`))
+ DROP TABLE t1,t2,temp;
+ SET join_cache_level = default;
++#
+# MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins.
+#
+set join_cache_level=default;
+set expensive_subquery_limit=0;
+create table t1 (c1 int);
+create table t2 (c2 int);
+create table t3 (c3 int);
+insert into t1 values (1), (2);
+insert into t2 values (1), (2);
+insert into t3 values (2);
+explain
+select count(*) from t1 straight_join t2
+where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+2 UNCACHEABLE SUBQUERY t3 system NULL NULL NULL NULL 1
+set @counter=0;
+select count(*) from t1 straight_join t2
+where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1);
+count(*)
+2
+select @counter;
+@counter
+2
+explain
+select count(*) from t1 straight_join t2
+where c1 = c2-0 and
+c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and
+c2 / 2 = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+2 UNCACHEABLE SUBQUERY t3 system NULL NULL NULL NULL 1
+set @counter=0;
+select count(*) from t1 straight_join t2
+where c1 = c2-0 and
+c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and
+c2 / 2 = 1;
+count(*)
+1
+select @counter;
+@counter
+2
+drop table t1,t2,t3;
+set expensive_subquery_limit=default;
+#
+# MDEV-6071: EXPLAIN chooses to use join buffer while execution turns it down
+#
+create table t1 (a int);
+insert into t1 values
+(7), (9), (1), (4), (2), (3), (5), (8), (11), (6), (10);
+explain select count(*) from t1, t1 t2 where t1.a=t2.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 11
+1 SIMPLE t2 ALL NULL NULL NULL NULL 11 Using where; Using join buffer (flat, BNL join)
+set join_buffer_space_limit=1024*8;
+select @@join_buffer_space_limit;
+@@join_buffer_space_limit
+8192
+select @@join_buffer_size;
+@@join_buffer_size
+131072
+explain select count(*) from t1, t1 t2 where t1.a=t2.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 11
+1 SIMPLE t2 ALL NULL NULL NULL NULL 11 Using where
+set join_buffer_space_limit=default;
+drop table t1;
+#
+# MDEV-6687: Assertion `0' failed in Protocol::end_statement on query
+#
+SET join_cache_level = 3;
+# The following should have
+# - table order PROFILING,user,
+# - table user accessed with hash_ALL:
+explain
+SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user WHERE password_expired = PAGE_FAULTS_MINOR;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE PROFILING ALL NULL NULL NULL NULL NULL Using where
+1 SIMPLE user hash_ALL NULL #hash#$hj 1 information_schema.PROFILING.PAGE_FAULTS_MINOR 4 Using where; Using join buffer (flat, BNLH join)
+set join_cache_level=default;
set @@optimizer_switch=@save_optimizer_switch;
diff --cc mysql-test/r/subselect_sj2_mat.result
index c27beb295b8,7f97e1aabee..140739e5195
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@@ -1692,92 -1659,53 +1692,143 @@@ i
13
drop table t1;
#
+ # MDEV-15982: Incorrect results when subquery is materialized
+ #
+ CREATE TABLE `t1` (`id` int(32) NOT NULL primary key);
+ INSERT INTO `t1` VALUES
+ (45), (46), (47), (48), (49), (50), (51), (52), (53), (54), (55), (56), (57), (58), (59), (60), (61), (62),
+ (63), (64), (65), (66), (67), (68), (69), (70), (71), (72), (73), (74), (75), (76), (77), (78), (79), (80),
+ (81), (82), (83), (84), (85), (86), (87), (88), (89), (90), (91), (92),(93),(94),(95),(96), (97), (98),
+ (99), (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112), (113),
+ (114), (115), (116), (117), (118), (119), (120), (121), (122), (123), (124), (125), (126), (127), (128),
+ (129), (130), (131), (132), (133), (134), (135), (136), (137), (138), (139), (140), (141), (142), (143), (144), (145), (146),
+ (147), (148), (149), (150), (151), (152), (153), (154), (155), (156), (157), (158), (159), (160), (161),
+ (162), (163), (164), (165), (166), (167), (168), (169), (170), (171), (172), (173),
+ (174), (175), (176), (177), (178), (179), (180), (181), (182), (183), (2), (3), (4), (5), (6), (19), (35),
+ (7), (20), (8), (36), (219), (22), (10), (23), (37), (11), (24);
+ CREATE TABLE `t2` (`type` int , `id` int(32) NOT NULL primary key);
+ INSERT INTO `t2` VALUES
+ (2,2),(2,3),(1,4),(2,5),(1,6),(1,19),(5,7),(1,20),(1,8),(1,21),(1,9),
+ (1,22),(2,10),(1,23),(2,11),(1,24),(1,12),(1,25),(2,13),(2,26),(2,14),
+ (2,27),(1,15),(1,28),(3,16),(1,29),(2,17),(1,30),(5,18),(2,1);
+ CREATE TABLE `t3` (`ref_id` int(32) unsigned ,`type` varchar(80),`id` int(32) NOT NULL );
+ INSERT INTO `t3` VALUES
+ (1,'incident',31),(2,'faux pas',32),
+ (5,'oopsies',33),(3,'deniable',34),
+ (11,'wasntme',35),(10,'wasntme',36),
+ (17,'faux pas',37),(13,'unlikely',38),
+ (13,'improbable',39),(14,'incident',40),
+ (26,'problem',41),(14,'problem',42),
+ (26,'incident',43),(27,'incident',44);
+ explain
+ SELECT t2.id FROM t2,t1
+ WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id;
+ id select_type table type possible_keys key key_len ref rows Extra
+ 1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 30 Using index
+ 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where
+ 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 Using index
+ 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 14
+ 2 MATERIALIZED t1 eq_ref PRIMARY PRIMARY 4 test.t3.id 1 Using index
+ SELECT t2.id FROM t2,t1
+ WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id;
+ id
+ 10
+ 11
+ set optimizer_switch='materialization=off';
+ SELECT t2.id FROM t2,t1
+ WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id;
+ id
+ 11
+ 10
+ set optimizer_switch='materialization=on';
+ DROP TABLE t1,t2,t3;
++#
+# MDEV-15247: Crash when SET NAMES 'utf8' is set
+#
+CREATE TABLE t1 (
+id_category int unsigned,
+id_product int unsigned,
+PRIMARY KEY (id_category,id_product)
+) ENGINE=MyISAM;
+INSERT INTO `t1` VALUES (31,216), (31,215), (31,214), (31,213), (31,212), (32,211), (32,210), (32,209), (32,208), (29,207), (30,315372), (2,161), (2,132), (33,315380), (31,315371), (29,315370), (29,315373), (29,315369), (29,315374), (29,315368), (29,315375), (29,315367), (29,183), (29,182), (30,177), (29,315376), (13,315365), (2,167), (2,315357), (2,164), (2,159), (2,131), (2,127), (14,315364), (27,315363), (29,205), (29,204), (29,203), (29,202), (29,201), (29,200), (29,199), (29,198), (29,197), (29,196), (29,195), (29,194), (29,193), (29,192), (29,191), (29,190), (29,189), (14,188), (29,187), (29,186), (29,185), (29,184), (29,315377), (29,315378), (29,181), (33,315379), (29,179), (30,178), (29,180), (30,176), (30,175), (30,174), (30,173), (30,172), (11,171), (27,315357), (23,108), (23,102);
+CREATE TABLE t2 (
+id_product int,
+id_t2 int,
+KEY id_t2 (id_t2),
+KEY id_product (id_product)
+) ENGINE=MyISAM;
+INSERT INTO `t2` VALUES (11,31), (11,31), (11,31), (11,32), (11,32),
+(11,32), (10,26), (11,32), (10,28), (11,32), (10,29), (11,33), (10,26),
+(11,33), (10,27), (9,23), (11,32), (10,26), (8,18), (7,15), (11,32),
+(10,28), (11,32), (10,28), (11,32), (10,29), (11,32), (10,29), (8,19),
+(7,16), (8,18), (7,16), (8,20), (7,16), (11,32), (10,28), (8,19),
+(7,16), (8,20), (7,16), (11,32), (10,29), (8,19), (7,16), (8,20),
+(7,16), (10,27), (9,23), (10,27), (9,23), (10,27), (9,23), (11,32),
+(10,27), (11,32), (10,27), (8,18), (7,15), (10,26), (9,24), (8,19),
+(7,16), (10,26), (9,23), (8,19), (7,16), (8,18), (7,16), (8,18), (7,16),
+(9,23), (8,18), (9,23), (8,19), (7,16), (7,16), (8,19), (7,16), (11,31),
+(10,27), (9,24), (11,31), (10,27), (9,23), (8,19), (11,31), (10,26), (9,24),
+(8,19), (11,31), (10,26), (9,25), (8,18), (11,31), (10,26), (9,23), (8,19),
+(11,31), (10,26), (9,23), (8,18), (11,31), (10,30), (9,23), (8,18), (11,31),
+(10,30), (9,23), (8,19), (11,31), (10,26), (9,25), (8,19), (8,21), (11,32),
+(10,26), (9,22), (8,19), (11,32), (10,26), (9,22), (8,18), (11,32), (10,26),
+(9,22), (8,20), (11,33), (10,26), (9,22), (8,19), (11,33), (10,26), (9,22),
+(8,18), (11,33), (10,26), (9,22), (8,20), (11,32), (10,26), (9,24), (8,19),
+(11,32), (10,26), (9,25), (8,19), (11,32), (10,26), (9,25), (8,18), (11,32),
+(10,26), (9,23), (8,18), (11,32), (10,30), (9,23), (8,18), (11,32), (10,30),
+(9,23), (8,19), (11,32), (10,26), (9,23), (8,19), (11,32), (10,27), (9,23),
+(11,32), (10,27), (9,23), (11,32), (10,27), (9,23), (10,26), (9,22), (8,19),
+(7,15), (10,26), (9,22), (8,20), (7,15), (10,26), (9,22), (8,18), (7,15),
+(8,19), (10,26), (10,26), (11,33), (10,26), (11,33), (10,26), (11,33),
+(10,27), (11,33), (10,27), (11,31), (10,26), (11,31), (10,26), (8,18),
+(7,15), (9,23), (9,23), (9,24), (8,21), (7,15), (7,15), (7,15), (7,15),
+(7,15), (7,15), (7,15), (7,15), (7,15), (8,18), (7,17), (8,18), (7,17), (8,19), (8,19);
+CREATE TABLE t3 (
+id_product int unsigned,
+PRIMARY KEY (id_product)
+) ENGINE=MyISAM;
+INSERT INTO t3 VALUES
+(102),(103),(104),(105),(106),(107),(108),(109),(110),
+(315371),(315373),(315374),(315375),(315376),(315377),
+(315378),(315379),(315380);
+CREATE TABLE t4 (
+id_product int not null,
+id_shop int,
+PRIMARY KEY (id_product,id_shop)
+) ENGINE=MyISAM ;
+INSERT INTO t4 VALUES
+(202,1),(201,1),(200,1),(199,1),(198,1),(197,1),(196,1),(195,1),
+(194,1),(193,1),(192,1),(191,1),(190,1),(189,1),(188,1),(187,1),
+(186,1),(185,1),(184,1),(183,1),(182,1),(181,1),(179,1),(178,1),
+(177,1),(176,1),(126,1),(315380,1);
+CREATE TABLE t5 (id_product int) ENGINE=MyISAM;
+INSERT INTO `t5` VALUES
+(652),(668),(669),(670),(671),(673),(674),(675),(676),
+(677),(679),(680),(681),(682),(683),(684),(685),(686);
+explain
+SELECT * FROM t3
+JOIN t4 ON (t4.id_product = t3.id_product AND t4.id_shop = 1)
+JOIN t1 ON (t1.id_product = t3.id_product)
+LEFT JOIN t5 ON (t5.id_product = t3.id_product)
+WHERE 1=1
+AND t3.id_product IN (SELECT id_product FROM t2 t2_1 WHERE t2_1.id_t2 = 32)
+AND t3.id_product IN (SELECT id_product FROM t2 t2_2 WHERE t2_2.id_t2 = 15)
+AND t3.id_product IN (SELECT id_product FROM t2 t2_3 WHERE t2_3.id_t2 = 18 OR t2_3.id_t2 = 19)
+AND t3.id_product IN (SELECT id_product FROM t2 t2_4 WHERE t2_4.id_t2 = 34 OR t2_4.id_t2 = 23)
+AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2_5.id_t2 = 28 OR t2_5.id_t2 = 26);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL PRIMARY 8 NULL 73 Using index
+1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.id_product 1 Using index
+1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where
+1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 Using where
+1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where
+1 PRIMARY t4 eq_ref PRIMARY PRIMARY 8 test.t1.id_product,const 1 Using where; Using index
+1 PRIMARY <subquery6> eq_ref distinct_key distinct_key 4 func 1 Using where
+1 PRIMARY t5 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join)
+5 MATERIALIZED t2_4 range id_t2,id_product id_t2 5 NULL 18 Using index condition; Using where
+4 MATERIALIZED t2_3 range id_t2,id_product id_t2 5 NULL 32 Using index condition; Using where
+3 MATERIALIZED t2_2 ref id_t2,id_product id_t2 5 const 12
+2 MATERIALIZED t2_1 ref id_t2,id_product id_t2 5 const 50
+6 MATERIALIZED t2_5 range id_t2,id_product id_t2 5 NULL 30 Using index condition; Using where
+drop table t1,t2,t3,t4,t5;
diff --cc mysql-test/t/join.test
index e07a3665920,feafac57a7e..8a088de91cc
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@@ -1160,43 -1160,54 +1160,96 @@@ DROP TABLE t1,t2
SET optimizer_switch=@save_optimizer_switch;
+ --echo #
+ --echo # MDEV-16512
+ --echo # Server crashes in find_field_in_table_ref on 2nd execution of SP referring to
+ --echo # non-existing field
+ --echo #
+
+ CREATE TABLE t (i INT);
+ CREATE PROCEDURE p() SELECT t1.f FROM t AS t1 JOIN t AS t2 USING (f);
+ --error ER_BAD_FIELD_ERROR
+ CALL p;
+ --error ER_BAD_FIELD_ERROR
+ CALL p;
+ FLUSH TABLES;
+ --error ER_BAD_FIELD_ERROR
+ CALL p;
+ DROP TABLE t;
+
+ #
+ # Fix the table definition to match the using
+ #
+
+ CREATE TABLE t (f INT);
+ #
+ # The following shouldn't fail as the table is now matching the using
+ #
+ --error ER_BAD_FIELD_ERROR
+ CALL p;
+ DROP TABLE t;
+ CREATE TABLE t (i INT);
+ --error ER_BAD_FIELD_ERROR
+ CALL p;
+ DROP PROCEDURE p;
+ DROP TABLE t;
+
+ CREATE TABLE t1 (a INT, b INT);
+ CREATE TABLE t2 (a INT);
+ CREATE TABLE t3 (a INT, c INT);
+ CREATE TABLE t4 (a INT, c INT);
+ CREATE TABLE t5 (a INT, c INT);
+ CREATE PROCEDURE p1() SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a)
+ LEFT JOIN t5 USING (a)) USING (a);
+ --error ER_NON_UNIQ_ERROR
+ CALL p1;
+ --error ER_NON_UNIQ_ERROR
+ CALL p1;
+ DROP PROCEDURE p1;
+ DROP TABLE t1,t2,t3,t4,t5;
+
+ --echo #
+ --echo # End of MariaDB 5.5 tests
+ --echo #
++
++
+--echo #
+--echo # Bug #35268: Parser can't handle STRAIGHT_JOIN with USING
+--echo #
+
+CREATE TABLE t1 (a int);
+
+INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+
+CREATE TABLE t2 (a int);
+
+INSERT INTO t2 (a) VALUES (1),(2),(3),(4);
+
+EXPLAIN
+SELECT t1.a FROM t1 NATURAL INNER JOIN t2 ORDER BY t1.a;
+SELECT t1.a FROM t1 NATURAL INNER JOIN t2 ORDER BY t1.a;
+
+EXPLAIN
+SELECT t1.a FROM t1 STRAIGHT_JOIN t2 USING(a) ORDER BY t1.a;
+SELECT t1.a FROM t1 STRAIGHT_JOIN t2 USING(a) ORDER BY t1.a;
+
+EXPLAIN
+SELECT t1.a FROM t1 NATURAL STRAIGHT_JOIN t2 ORDER BY t1.a;
+SELECT t1.a FROM t1 NATURAL STRAIGHT_JOIN t2 ORDER BY t1.a;
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo # MDEV-5635: join of a const table with non-const tables
+--echo #
+
+CREATE TABLE t1 (a varchar(3) NOT NULL) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo');
+
+CREATE TABLE t2 (b varchar(3), c varchar(3), INDEX(b)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('bar', 'bar'),( 'qux', 'qux');
+
+SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2
+ WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a );
+
+DROP TABLE t1,t2;
diff --cc mysql-test/t/join_cache.test
index 8507d58eecd,d82b4fa6030..e095419e88d
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@@ -3835,82 -3836,36 +3835,112 @@@ set optimizer_switch=@save_optimizer_sw
DROP TABLE t1,t4,t5,t2;
+ --echo #
+ --echo # MDEV-16603: BNLH for query with materialized semi-join
+ --echo #
+
+ --source include/have_innodb.inc
+
+ set join_cache_level=4;
+
+ CREATE TABLE t1 ( i1 int, v1 varchar(1)) ENGINE=InnoDB;
+ INSERT INTO t1 VALUES (7,'x');
+
+ CREATE TABLE t2 (i1 int, v1 varchar(1), KEY v1 (v1,i1)) ENGINE=InnoDB;
+
+ INSERT INTO t2 VALUES
+ (NULL,'x'),(1,'x'),(3,'x'),(5,'x'),(8,'x'),(48,'x'),
+ (228,'x'),(3,'y'),(1,'z'),(9,'z');
+
+ CREATE TABLE temp
+ SELECT t1.i1 AS f1, t1.v1 AS f2 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1));
+
+ let $q =
+ SELECT * FROM temp
+ WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)));
+
+ eval $q;
+ eval EXPLAIN EXTENDED $q;
+
+ DROP TABLE t1,t2,temp;
+
+ SET join_cache_level = default;
+
-# this must be the last command in the file
+--echo #
+--echo # MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins.
+--echo #
+
+set join_cache_level=default;
+set expensive_subquery_limit=0;
+
+create table t1 (c1 int);
+create table t2 (c2 int);
+create table t3 (c3 int);
+
+insert into t1 values (1), (2);
+insert into t2 values (1), (2);
+insert into t3 values (2);
+
+explain
+select count(*) from t1 straight_join t2
+where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1);
+
+set @counter=0;
+
+select count(*) from t1 straight_join t2
+where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1);
+
+select @counter;
+
+explain
+select count(*) from t1 straight_join t2
+where c1 = c2-0 and
+ c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and
+ c2 / 2 = 1;
+
+set @counter=0;
+
+select count(*) from t1 straight_join t2
+where c1 = c2-0 and
+ c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and
+ c2 / 2 = 1;
+
+select @counter;
+
+drop table t1,t2,t3;
+set expensive_subquery_limit=default;
+
+--echo #
+--echo # MDEV-6071: EXPLAIN chooses to use join buffer while execution turns it down
+--echo #
+
+create table t1 (a int);
+insert into t1 values
+(7), (9), (1), (4), (2), (3), (5), (8), (11), (6), (10);
+
+explain select count(*) from t1, t1 t2 where t1.a=t2.a;
+
+set join_buffer_space_limit=1024*8;
+select @@join_buffer_space_limit;
+select @@join_buffer_size;
+
+explain select count(*) from t1, t1 t2 where t1.a=t2.a;
+
+set join_buffer_space_limit=default;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-6687: Assertion `0' failed in Protocol::end_statement on query
+--echo #
+SET join_cache_level = 3;
+--echo # The following should have
+--echo # - table order PROFILING,user,
+--echo # - table user accessed with hash_ALL:
+explain
+SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user WHERE password_expired = PAGE_FAULTS_MINOR;
+
+set join_cache_level=default;
+
+# The following command must be the last one the file
- # this must be the last command in the file
set @@optimizer_switch=@save_optimizer_switch;
diff --cc mysql-test/t/subselect_sj2_mat.test
index 68a888012f2,43b63459928..f54771856e7
--- a/mysql-test/t/subselect_sj2_mat.test
+++ b/mysql-test/t/subselect_sj2_mat.test
@@@ -345,90 -345,52 +345,139 @@@ WHERE
(t.id IN (0,4,12,13,1,10,3,11))
);
drop table t1;
+
+ --echo #
+ --echo # MDEV-15982: Incorrect results when subquery is materialized
+ --echo #
+
+ CREATE TABLE `t1` (`id` int(32) NOT NULL primary key);
+ INSERT INTO `t1` VALUES
+ (45), (46), (47), (48), (49), (50), (51), (52), (53), (54), (55), (56), (57), (58), (59), (60), (61), (62),
+ (63), (64), (65), (66), (67), (68), (69), (70), (71), (72), (73), (74), (75), (76), (77), (78), (79), (80),
+ (81), (82), (83), (84), (85), (86), (87), (88), (89), (90), (91), (92),(93),(94),(95),(96), (97), (98),
+ (99), (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112), (113),
+ (114), (115), (116), (117), (118), (119), (120), (121), (122), (123), (124), (125), (126), (127), (128),
+ (129), (130), (131), (132), (133), (134), (135), (136), (137), (138), (139), (140), (141), (142), (143), (144), (145), (146),
+ (147), (148), (149), (150), (151), (152), (153), (154), (155), (156), (157), (158), (159), (160), (161),
+ (162), (163), (164), (165), (166), (167), (168), (169), (170), (171), (172), (173),
+ (174), (175), (176), (177), (178), (179), (180), (181), (182), (183), (2), (3), (4), (5), (6), (19), (35),
+ (7), (20), (8), (36), (219), (22), (10), (23), (37), (11), (24);
+
+ CREATE TABLE `t2` (`type` int , `id` int(32) NOT NULL primary key);
+ INSERT INTO `t2` VALUES
+ (2,2),(2,3),(1,4),(2,5),(1,6),(1,19),(5,7),(1,20),(1,8),(1,21),(1,9),
+ (1,22),(2,10),(1,23),(2,11),(1,24),(1,12),(1,25),(2,13),(2,26),(2,14),
+ (2,27),(1,15),(1,28),(3,16),(1,29),(2,17),(1,30),(5,18),(2,1);
+
+ CREATE TABLE `t3` (`ref_id` int(32) unsigned ,`type` varchar(80),`id` int(32) NOT NULL );
+ INSERT INTO `t3` VALUES
+ (1,'incident',31),(2,'faux pas',32),
+ (5,'oopsies',33),(3,'deniable',34),
+ (11,'wasntme',35),(10,'wasntme',36),
+ (17,'faux pas',37),(13,'unlikely',38),
+ (13,'improbable',39),(14,'incident',40),
+ (26,'problem',41),(14,'problem',42),
+ (26,'incident',43),(27,'incident',44);
+
+ explain
+ SELECT t2.id FROM t2,t1
+ WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id;
+
+ SELECT t2.id FROM t2,t1
+ WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id;
+
+ set optimizer_switch='materialization=off';
+
+ SELECT t2.id FROM t2,t1
+ WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id;
+ set optimizer_switch='materialization=on';
+
+ DROP TABLE t1,t2,t3;
+
+
+--echo #
+--echo # MDEV-15247: Crash when SET NAMES 'utf8' is set
+--echo #
+
+CREATE TABLE t1 (
+ id_category int unsigned,
+ id_product int unsigned,
+ PRIMARY KEY (id_category,id_product)
+) ENGINE=MyISAM;
+
+INSERT INTO `t1` VALUES (31,216), (31,215), (31,214), (31,213), (31,212), (32,211), (32,210), (32,209), (32,208), (29,207), (30,315372), (2,161), (2,132), (33,315380), (31,315371), (29,315370), (29,315373), (29,315369), (29,315374), (29,315368), (29,315375), (29,315367), (29,183), (29,182), (30,177), (29,315376), (13,315365), (2,167), (2,315357), (2,164), (2,159), (2,131), (2,127), (14,315364), (27,315363), (29,205), (29,204), (29,203), (29,202), (29,201), (29,200), (29,199), (29,198), (29,197), (29,196), (29,195), (29,194), (29,193), (29,192), (29,191), (29,190), (29,189), (14,188), (29,187), (29,186), (29,185), (29,184), (29,315377), (29,315378), (29,181), (33,315379), (29,179), (30,178), (29,180), (30,176), (30,175), (30,174), (30,173), (30,172), (11,171), (27,315357), (23,108), (23,102);
+
+CREATE TABLE t2 (
+ id_product int,
+ id_t2 int,
+ KEY id_t2 (id_t2),
+ KEY id_product (id_product)
+) ENGINE=MyISAM;
+
+INSERT INTO `t2` VALUES (11,31), (11,31), (11,31), (11,32), (11,32),
+(11,32), (10,26), (11,32), (10,28), (11,32), (10,29), (11,33), (10,26),
+(11,33), (10,27), (9,23), (11,32), (10,26), (8,18), (7,15), (11,32),
+(10,28), (11,32), (10,28), (11,32), (10,29), (11,32), (10,29), (8,19),
+(7,16), (8,18), (7,16), (8,20), (7,16), (11,32), (10,28), (8,19),
+(7,16), (8,20), (7,16), (11,32), (10,29), (8,19), (7,16), (8,20),
+(7,16), (10,27), (9,23), (10,27), (9,23), (10,27), (9,23), (11,32),
+(10,27), (11,32), (10,27), (8,18), (7,15), (10,26), (9,24), (8,19),
+(7,16), (10,26), (9,23), (8,19), (7,16), (8,18), (7,16), (8,18), (7,16),
+(9,23), (8,18), (9,23), (8,19), (7,16), (7,16), (8,19), (7,16), (11,31),
+(10,27), (9,24), (11,31), (10,27), (9,23), (8,19), (11,31), (10,26), (9,24),
+(8,19), (11,31), (10,26), (9,25), (8,18), (11,31), (10,26), (9,23), (8,19),
+(11,31), (10,26), (9,23), (8,18), (11,31), (10,30), (9,23), (8,18), (11,31),
+(10,30), (9,23), (8,19), (11,31), (10,26), (9,25), (8,19), (8,21), (11,32),
+(10,26), (9,22), (8,19), (11,32), (10,26), (9,22), (8,18), (11,32), (10,26),
+(9,22), (8,20), (11,33), (10,26), (9,22), (8,19), (11,33), (10,26), (9,22),
+(8,18), (11,33), (10,26), (9,22), (8,20), (11,32), (10,26), (9,24), (8,19),
+(11,32), (10,26), (9,25), (8,19), (11,32), (10,26), (9,25), (8,18), (11,32),
+(10,26), (9,23), (8,18), (11,32), (10,30), (9,23), (8,18), (11,32), (10,30),
+(9,23), (8,19), (11,32), (10,26), (9,23), (8,19), (11,32), (10,27), (9,23),
+(11,32), (10,27), (9,23), (11,32), (10,27), (9,23), (10,26), (9,22), (8,19),
+(7,15), (10,26), (9,22), (8,20), (7,15), (10,26), (9,22), (8,18), (7,15),
+(8,19), (10,26), (10,26), (11,33), (10,26), (11,33), (10,26), (11,33),
+(10,27), (11,33), (10,27), (11,31), (10,26), (11,31), (10,26), (8,18),
+(7,15), (9,23), (9,23), (9,24), (8,21), (7,15), (7,15), (7,15), (7,15),
+(7,15), (7,15), (7,15), (7,15), (7,15), (8,18), (7,17), (8,18), (7,17), (8,19), (8,19);
+
+CREATE TABLE t3 (
+ id_product int unsigned,
+ PRIMARY KEY (id_product)
+) ENGINE=MyISAM;
+
+INSERT INTO t3 VALUES
+(102),(103),(104),(105),(106),(107),(108),(109),(110),
+(315371),(315373),(315374),(315375),(315376),(315377),
+(315378),(315379),(315380);
+
+CREATE TABLE t4 (
+ id_product int not null,
+ id_shop int,
+ PRIMARY KEY (id_product,id_shop)
+) ENGINE=MyISAM ;
+
+INSERT INTO t4 VALUES
+(202,1),(201,1),(200,1),(199,1),(198,1),(197,1),(196,1),(195,1),
+(194,1),(193,1),(192,1),(191,1),(190,1),(189,1),(188,1),(187,1),
+(186,1),(185,1),(184,1),(183,1),(182,1),(181,1),(179,1),(178,1),
+(177,1),(176,1),(126,1),(315380,1);
+
+CREATE TABLE t5 (id_product int) ENGINE=MyISAM;
+INSERT INTO `t5` VALUES
+(652),(668),(669),(670),(671),(673),(674),(675),(676),
+(677),(679),(680),(681),(682),(683),(684),(685),(686);
+
+explain
+SELECT * FROM t3
+ JOIN t4 ON (t4.id_product = t3.id_product AND t4.id_shop = 1)
+ JOIN t1 ON (t1.id_product = t3.id_product)
+LEFT JOIN t5 ON (t5.id_product = t3.id_product)
+WHERE 1=1
+AND t3.id_product IN (SELECT id_product FROM t2 t2_1 WHERE t2_1.id_t2 = 32)
+AND t3.id_product IN (SELECT id_product FROM t2 t2_2 WHERE t2_2.id_t2 = 15)
+AND t3.id_product IN (SELECT id_product FROM t2 t2_3 WHERE t2_3.id_t2 = 18 OR t2_3.id_t2 = 19)
+AND t3.id_product IN (SELECT id_product FROM t2 t2_4 WHERE t2_4.id_t2 = 34 OR t2_4.id_t2 = 23)
+AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2_5.id_t2 = 28 OR t2_5.id_t2 = 26);
+
+drop table t1,t2,t3,t4,t5;
diff --cc scripts/mysql_install_db.sh
index d04a6f497da,a8f32526a7e..5300036def5
--- a/scripts/mysql_install_db.sh
+++ b/scripts/mysql_install_db.sh
@@@ -208,11 -208,14 +208,16 @@@ cannot_find_file(
fi
echo
- echo "If you compiled from source, you need to run 'make install' to"
+ echo "If you compiled from source, you need to either run 'make install' to"
echo "copy the software into the correct location ready for operation."
+ echo "If you don't want to do a full install, you can use the --srcddir"
+ echo "option to only install the mysql database and privilege tables"
echo
+ echo "If you compiled from source, you need to either run 'make install' to"
+ echo "copy the software into the correct location ready for operation."
+ echo "If you don't want to do a full install, you can use the --srcdir"
+ echo "option to only install the mysql database and privilege tables"
+ echo
echo "If you are using a binary release, you must either be at the top"
echo "level of the extracted archive, or pass the --basedir option"
echo "pointing to that location."
1
0
[Commits] b75d819: MDEV-16711 Crash in Field_blob::store() while reading statistics
by IgorBabaev 16 Jul '18
by IgorBabaev 16 Jul '18
16 Jul '18
revision-id: b75d819604bbb6c461e6363e331bd6bd1ba7a787 (mariadb-10.1.34-21-gb75d819)
parent(s): ae0eb507bda858a13475faef4476dd496c4dbc01
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-07-15 18:40:25 -0700
message:
MDEV-16711 Crash in Field_blob::store() while reading statistics
for the small InnoDB table
This bug was introduced by the patch 6c414fcf89510215d6d3466eb9992d444eadae89.
The patch has not taken into account that some objects of the Field_* types
are created only for TABLE_SHARE and the field 'table' is set to NULL
for them. In particular such are objects created to store statistical
min/max values for columns.
---
mysql-test/r/stat_tables.result | 20 ++++++++++++++++++++
mysql-test/r/stat_tables_innodb.result | 20 ++++++++++++++++++++
mysql-test/t/stat_tables.test | 20 +++++++++++++++++++-
sql/field.cc | 8 +++++++-
4 files changed, 66 insertions(+), 2 deletions(-)
diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index 1f1cad1..279c09f 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -539,6 +539,10 @@ SELECT * FROM mysql.column_stats;
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+SELECT pk FROM t1;
+pk
+1
+2
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
#
@@ -566,3 +570,19 @@ SELECT * FROM mysql.column_stats;
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+SELECT MAX(pk) FROM t1;
+MAX(pk)
+NULL
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index fc1ecdf..1604c41 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -566,6 +566,10 @@ SELECT * FROM mysql.column_stats;
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+SELECT pk FROM t1;
+pk
+1
+2
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
#
@@ -593,5 +597,21 @@ SELECT * FROM mysql.column_stats;
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+SELECT MAX(pk) FROM t1;
+MAX(pk)
+NULL
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index b98ca64..51c9995 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -325,7 +325,7 @@ INSERT INTO mysql.column_stats VALUES
--sorted_result
SELECT * FROM mysql.column_stats;
-# SELECT pk FROM t1;
+SELECT pk FROM t1;
DROP TABLE t1;
@@ -350,3 +350,21 @@ SELECT * FROM mysql.column_stats;
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+
+
+--echo #
+--echo # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
+--echo #
+
+SET use_stat_tables= PREFERABLY;
+
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+
+SELECT MAX(pk) FROM t1;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
diff --git a/sql/field.cc b/sql/field.cc
index 64c5167..71fc568 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -7942,7 +7942,13 @@ int Field_blob::store(const char *from,uint length,CHARSET_INFO *cs)
return 0;
}
- if (table->blob_storage) // GROUP_CONCAT with ORDER BY | DISTINCT
+ /*
+ For min/max fields of statistical data 'table' is set to NULL.
+ It could not be otherwise as this data is shared by many instances
+ of the same base table.
+ */
+
+ if (table && table->blob_storage) // GROUP_CONCAT with ORDER BY | DISTINCT
{
DBUG_ASSERT(!f_is_hex_escape(flags));
DBUG_ASSERT(field_charset == cs);
1
0
[Commits] ae0eb50: MDEV-16760 CREATE OR REPLACE TABLE never updates statistical tables
by IgorBabaev 15 Jul '18
by IgorBabaev 15 Jul '18
15 Jul '18
revision-id: ae0eb507bda858a13475faef4476dd496c4dbc01 (mariadb-10.1.34-20-gae0eb50)
parent(s): 095dc81158902380b8618338efabb5ce480dbd79
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-07-15 16:28:39 -0700
message:
MDEV-16760 CREATE OR REPLACE TABLE never updates statistical tables
If the command CREATE OR REPLACE TABLE really replaces a table then
it should remove all data on this table from all statistical tables.
---
mysql-test/r/stat_tables.result | 25 +++++++++++++++++++++++++
mysql-test/r/stat_tables_innodb.result | 25 +++++++++++++++++++++++++
mysql-test/t/stat_tables.test | 20 ++++++++++++++++++++
sql/sql_table.cc | 4 ++++
4 files changed, 74 insertions(+)
diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index f299603..1f1cad1 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -541,3 +541,28 @@ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+SELECT * FROM t1;
+pk c
+1 foo
+2 bar
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+pk a
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index d2d9296..fc1ecdf 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -568,5 +568,30 @@ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+SELECT * FROM t1;
+pk c
+1 foo
+2 bar
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL
+test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+pk a
+SELECT * FROM mysql.column_stats;
+db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index 5bbd8ca..b98ca64 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -330,3 +330,23 @@ SELECT * FROM mysql.column_stats;
DROP TABLE t1;
set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+--echo #
+
+SET use_stat_tables= PREFERABLY;
+
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+SELECT * FROM t1;
+SELECT * FROM mysql.column_stats;
+
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+SELECT * FROM mysql.column_stats;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 3c91463..9a8c737 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -4770,6 +4770,10 @@ int create_table_impl(THD *thd,
{
if (options.or_replace())
{
+ LEX_STRING db_name= {(char *) db, strlen(db)};
+ LEX_STRING tab_name= {(char *) table_name, strlen(table_name)};
+ (void) delete_statistics_for_table(thd, &db_name, &tab_name);
+
TABLE_LIST table_list;
table_list.init_one_table(db, strlen(db), table_name,
strlen(table_name), table_name,
1
0