1 files changed, 44 insertions, 0 deletions
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 2d06c3e2a30..7859822bcad 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -3636,5 +3636,49 @@ set optimizer_switch=@tmp_optimizer_switch;
 
 DROP table t1,t2,t3;
 
+--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;
+
 # this must be the last command in the file
 set @@optimizer_switch=@save_optimizer_switch;