Testcase for: MDEV-7893 table_elimination works wrong ...

1 files changed, 31 insertions, 0 deletions

diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test
index 0d42dca57b7..24f48206013 100644
--- a/mysql-test/t/table_elim.test
+++ b/mysql-test/t/table_elim.test
@@ -610,3 +610,34 @@ SELECT t1.alpha3 FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON t2.t3_code  = t3.code )
 DROP TABLE t1, t2, t3;
 
 SET optimizer_switch=@save_optimizer_switch;
+
+--echo #
+--echo # MDEV-7893: table_elimination works wrong with on computed expression and compound unique key
+--echo #  (just a testcase)
+CREATE TABLE t1 (
+  PostID int(10) unsigned NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+INSERT INTO t1 (PostID) VALUES (1), (2);
+
+CREATE TABLE t2 (
+  VoteID int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
+  EntityID int(10) unsigned NOT NULL,
+  UserID int(10) unsigned NOT NULL,
+  UNIQUE KEY EntityID (EntityID,UserID)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+  
+INSERT INTO t2 (EntityID, UserID) VALUES (1,  30), (2, 30);
+
+SELECT t1.*, T.Voted as Voted
+FROM 
+t1 LEFT JOIN (
+  SELECT 1 AS Voted, EntityID 
+  FROM t2 
+  WHERE t2.UserID = '20' ) AS T 
+  ON T.EntityID = t1.PostID
+WHERE t1.PostID='1'
+LIMIT 1;
+
+DROP TABLE t1,t2;
+