summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect.result26
-rw-r--r--mysql-test/r/subselect_no_exists_to_in.result26
-rw-r--r--mysql-test/r/subselect_no_mat.result26
-rw-r--r--mysql-test/r/subselect_no_opts.result26
-rw-r--r--mysql-test/r/subselect_no_scache.result26
-rw-r--r--mysql-test/r/subselect_no_semijoin.result26
-rw-r--r--mysql-test/suite/innodb/t/innodb-master.opt2
-rw-r--r--mysql-test/t/subselect.test27
-rw-r--r--sql/opt_subselect.cc64
-rw-r--r--storage/innobase/trx/trx0purge.cc26
-rw-r--r--storage/xtradb/trx/trx0purge.cc26
11 files changed, 233 insertions, 68 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index cf542a5913e..40f936fb3b4 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -7210,6 +7210,32 @@ NULL
# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
#
drop table t1, t2;
+#
+# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in
+# (5.5 test)
+#
+SET @optimiser_switch_save= @@optimizer_switch;
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(1),(1),(5),(5);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (5),(1);
+CREATE TABLE t3 (c INT, KEY(c));
+INSERT INTO t3 VALUES (5),(5);
+SET optimizer_switch='semijoin=on';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET optimizer_switch='semijoin=off';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET @@optimizer_switch= @optimiser_switch_save;
+DROP TABLE t1, t2, t3;
+End of 5.5 tests
# End of 10.0 tests
#
# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index a3a6e2ab4c0..a23820820ef 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -7210,6 +7210,32 @@ NULL
# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
#
drop table t1, t2;
+#
+# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in
+# (5.5 test)
+#
+SET @optimiser_switch_save= @@optimizer_switch;
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(1),(1),(5),(5);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (5),(1);
+CREATE TABLE t3 (c INT, KEY(c));
+INSERT INTO t3 VALUES (5),(5);
+SET optimizer_switch='semijoin=on';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET optimizer_switch='semijoin=off';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET @@optimizer_switch= @optimiser_switch_save;
+DROP TABLE t1, t2, t3;
+End of 5.5 tests
# End of 10.0 tests
#
# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 11457ccbb02..baa74307f89 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -7203,6 +7203,32 @@ NULL
# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
#
drop table t1, t2;
+#
+# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in
+# (5.5 test)
+#
+SET @optimiser_switch_save= @@optimizer_switch;
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(1),(1),(5),(5);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (5),(1);
+CREATE TABLE t3 (c INT, KEY(c));
+INSERT INTO t3 VALUES (5),(5);
+SET optimizer_switch='semijoin=on';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET optimizer_switch='semijoin=off';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET @@optimizer_switch= @optimiser_switch_save;
+DROP TABLE t1, t2, t3;
+End of 5.5 tests
# End of 10.0 tests
#
# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 076953c210f..039f2fe1a9e 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -7201,6 +7201,32 @@ NULL
# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
#
drop table t1, t2;
+#
+# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in
+# (5.5 test)
+#
+SET @optimiser_switch_save= @@optimizer_switch;
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(1),(1),(5),(5);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (5),(1);
+CREATE TABLE t3 (c INT, KEY(c));
+INSERT INTO t3 VALUES (5),(5);
+SET optimizer_switch='semijoin=on';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET optimizer_switch='semijoin=off';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET @@optimizer_switch= @optimiser_switch_save;
+DROP TABLE t1, t2, t3;
+End of 5.5 tests
# End of 10.0 tests
#
# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index b5c7e9bfdbd..0ce77bbb376 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -7216,6 +7216,32 @@ NULL
# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
#
drop table t1, t2;
+#
+# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in
+# (5.5 test)
+#
+SET @optimiser_switch_save= @@optimizer_switch;
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(1),(1),(5),(5);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (5),(1);
+CREATE TABLE t3 (c INT, KEY(c));
+INSERT INTO t3 VALUES (5),(5);
+SET optimizer_switch='semijoin=on';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET optimizer_switch='semijoin=off';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET @@optimizer_switch= @optimiser_switch_save;
+DROP TABLE t1, t2, t3;
+End of 5.5 tests
# End of 10.0 tests
#
# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 52da5f1cec3..574e78122f1 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -7201,6 +7201,32 @@ NULL
# SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
#
drop table t1, t2;
+#
+# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in
+# (5.5 test)
+#
+SET @optimiser_switch_save= @@optimizer_switch;
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(1),(1),(5),(5);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (5),(1);
+CREATE TABLE t3 (c INT, KEY(c));
+INSERT INTO t3 VALUES (5),(5);
+SET optimizer_switch='semijoin=on';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET optimizer_switch='semijoin=off';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET @@optimizer_switch= @optimiser_switch_save;
+DROP TABLE t1, t2, t3;
+End of 5.5 tests
# End of 10.0 tests
#
# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
diff --git a/mysql-test/suite/innodb/t/innodb-master.opt b/mysql-test/suite/innodb/t/innodb-master.opt
index 5266978e4f0..2e71d62206d 100644
--- a/mysql-test/suite/innodb/t/innodb-master.opt
+++ b/mysql-test/suite/innodb/t/innodb-master.opt
@@ -2,3 +2,5 @@
--default-storage-engine=MyISAM
--innodb-strict-mode=0
--innodb-file-per-table=0
+--loose-innodb-track-changed-pages
+--loose-innodb-log-archive
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index e0e3ed1a14c..717871db61d 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -6072,6 +6072,33 @@ SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
--echo #
drop table t1, t2;
+--echo #
+--echo # MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in
+--echo # (5.5 test)
+--echo #
+SET @optimiser_switch_save= @@optimizer_switch;
+
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(1),(1),(5),(5);
+
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (5),(1);
+
+CREATE TABLE t3 (c INT, KEY(c));
+INSERT INTO t3 VALUES (5),(5);
+
+SET optimizer_switch='semijoin=on';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+
+SET optimizer_switch='semijoin=off';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+
+SET @@optimizer_switch= @optimiser_switch_save;
+DROP TABLE t1, t2, t3;
+
+--echo End of 5.5 tests
--echo # End of 10.0 tests
--echo #
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 138af3f14b3..d7371fa7e3f 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -2704,7 +2704,8 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx,
LooseScan detector in best_access_path)
*/
remaining_tables &= ~new_join_tab->table->map;
- table_map dups_producing_tables;
+ table_map dups_producing_tables, prev_dups_producing_tables,
+ prev_sjm_lookup_tables;
if (idx == join->const_tables)
dups_producing_tables= 0;
@@ -2715,7 +2716,7 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx,
if ((emb_sj_nest= new_join_tab->emb_sj_nest))
dups_producing_tables |= emb_sj_nest->sj_inner_tables;
- Semi_join_strategy_picker **strategy;
+ Semi_join_strategy_picker **strategy, **prev_strategy;
if (idx == join->const_tables)
{
/* First table, initialize pickers */
@@ -2767,23 +2768,54 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx,
3. We have no clue what to do about fanount of semi-join Y.
*/
if ((dups_producing_tables & handled_fanout) ||
- (read_time < *current_read_time &&
+ (read_time < *current_read_time &&
!(handled_fanout & pos->inner_tables_handled_with_other_sjs)))
{
- /* Mark strategy as used */
- (*strategy)->mark_used();
- pos->sj_strategy= sj_strategy;
- if (sj_strategy == SJ_OPT_MATERIALIZE)
- join->sjm_lookup_tables |= handled_fanout;
+ DBUG_ASSERT(pos->sj_strategy != sj_strategy);
+ /*
+ If the strategy choosen first time or
+ the strategy replace strategy which was used to exectly the same
+ tables
+ */
+ if (pos->sj_strategy == SJ_OPT_NONE ||
+ handled_fanout ==
+ (prev_dups_producing_tables ^ dups_producing_tables))
+ {
+ prev_strategy= strategy;
+ if (pos->sj_strategy == SJ_OPT_NONE)
+ {
+ prev_dups_producing_tables= dups_producing_tables;
+ prev_sjm_lookup_tables= join->sjm_lookup_tables;
+ }
+ /* Mark strategy as used */
+ (*strategy)->mark_used();
+ pos->sj_strategy= sj_strategy;
+ if (sj_strategy == SJ_OPT_MATERIALIZE)
+ join->sjm_lookup_tables |= handled_fanout;
+ else
+ join->sjm_lookup_tables &= ~handled_fanout;
+ *current_read_time= read_time;
+ *current_record_count= rec_count;
+ dups_producing_tables &= ~handled_fanout;
+ //TODO: update bitmap of semi-joins that were handled together with
+ // others.
+ if (is_multiple_semi_joins(join, join->positions, idx,
+ handled_fanout))
+ pos->inner_tables_handled_with_other_sjs |= handled_fanout;
+ }
else
- join->sjm_lookup_tables &= ~handled_fanout;
- *current_read_time= read_time;
- *current_record_count= rec_count;
- dups_producing_tables &= ~handled_fanout;
- //TODO: update bitmap of semi-joins that were handled together with
- // others.
- if (is_multiple_semi_joins(join, join->positions, idx, handled_fanout))
- pos->inner_tables_handled_with_other_sjs |= handled_fanout;
+ {
+ /* Conflict fall to most general variant */
+ (*prev_strategy)->set_empty();
+ dups_producing_tables= prev_dups_producing_tables;
+ join->sjm_lookup_tables= prev_sjm_lookup_tables;
+ // mark it 'none' to avpoid loops
+ pos->sj_strategy= SJ_OPT_NONE;
+ // next skip to last;
+ strategy= pickers +
+ (sizeof(pickers)/sizeof(Semi_join_strategy_picker*) - 3);
+ continue;
+ }
}
else
{
diff --git a/storage/innobase/trx/trx0purge.cc b/storage/innobase/trx/trx0purge.cc
index 5553de4814f..9e5e90128cb 100644
--- a/storage/innobase/trx/trx0purge.cc
+++ b/storage/innobase/trx/trx0purge.cc
@@ -584,32 +584,6 @@ trx_purge_rseg_get_next_history_log(
mutex_exit(&(rseg->mutex));
mtr_commit(&mtr);
-
- mutex_enter(&trx_sys->mutex);
-
- /* Add debug code to track history list corruption reported
- on the MySQL mailing list on Nov 9, 2004. The fut0lst.cc
- file-based list was corrupt. The prev node pointer was
- FIL_NULL, even though the list length was over 8 million nodes!
- We assume that purge truncates the history list in large
- size pieces, and if we here reach the head of the list, the
- list cannot be longer than 2000 000 undo logs now. */
-
- if (trx_sys->rseg_history_len > 2000000) {
- ut_print_timestamp(stderr);
- fprintf(stderr,
- " InnoDB: Warning: purge reached the"
- " head of the history list,\n"
- "InnoDB: but its length is still"
- " reported as %lu! Make a detailed bug\n"
- "InnoDB: report, and submit it"
- " to https://jira.mariadb.org/\n",
- (ulong) trx_sys->rseg_history_len);
- ut_ad(0);
- }
-
- mutex_exit(&trx_sys->mutex);
-
return;
}
diff --git a/storage/xtradb/trx/trx0purge.cc b/storage/xtradb/trx/trx0purge.cc
index ff82bb2ad4e..cbf783628f9 100644
--- a/storage/xtradb/trx/trx0purge.cc
+++ b/storage/xtradb/trx/trx0purge.cc
@@ -584,32 +584,6 @@ trx_purge_rseg_get_next_history_log(
mutex_exit(&(rseg->mutex));
mtr_commit(&mtr);
-
- mutex_enter(&trx_sys->mutex);
-
- /* Add debug code to track history list corruption reported
- on the MySQL mailing list on Nov 9, 2004. The fut0lst.cc
- file-based list was corrupt. The prev node pointer was
- FIL_NULL, even though the list length was over 8 million nodes!
- We assume that purge truncates the history list in large
- size pieces, and if we here reach the head of the list, the
- list cannot be longer than 2000 000 undo logs now. */
-
- if (trx_sys->rseg_history_len > 2000000) {
- ut_print_timestamp(stderr);
- fprintf(stderr,
- " InnoDB: Warning: purge reached the"
- " head of the history list,\n"
- "InnoDB: but its length is still"
- " reported as %lu! Make a detailed bug\n"
- "InnoDB: report, and submit it"
- " to https://jira.mariadb.org/\n",
- (ulong) trx_sys->rseg_history_len);
- ut_ad(0);
- }
-
- mutex_exit(&trx_sys->mutex);
-
return;
}
View Lorry Controller Status