1 files changed, 68 insertions, 0 deletions
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 51bb72e1239..907d39e95fe 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -1067,4 +1067,72 @@ insert into t2 values ('1:1:1');
 select t2.i from t1 left join t2 on t2.i = t1.i where t1.i = '1:1:1';
 drop table t1,t2;
 
+--echo #
+--echo # BUG#954900: unexpected empty set due to an invalid build of key ref
+--echo #                   
+
+CREATE TABLE t1 (dog_id int(10), birthday date, PRIMARY KEY (dog_id,birthday));
+INSERT INTO t1 VALUES (5918,'2004-07-22');
+
+CREATE TABLE t2 (dog_id int(10) unsigned, t_id char(1), birthday date, a_id int(10),
+PRIMARY KEY (dog_id,t_id,birthday,a_id));
+INSERT INTO t2 VALUES 
+(5918,'N','2004-07-22',5216551), (5918,'N','2004-07-22',5223640),
+(5918,'N','2004-07-22',5389491), (5918,'N','2004-07-22',5749434),
+(5918,'N','2004-07-22',5992424), (5922,'N','2005-06-30',5076957),
+(5924,'N','2000-08-11',20264), (5924,'N','2000-08-11',64251),
+(5924,'N','2000-08-11',74748), (5924,'N','2000-08-11',87590),
+(5924,'N','2000-08-11',104695), (5924,'N','2000-08-11',133136),
+(5924,'N','2000-08-11',5027806), (5924,'N','2000-08-11',5076957),
+(5924,'N','2000-08-11',5166821), (5924,'N','2000-08-11',5181896),
+(5924,'N','2000-08-11',5217908), (5924,'N','2000-08-11',5220812),
+(5924,'N','2000-08-11',5226473), (5924,'N','2000-08-11',5339111),
+(5925,'N','2005-02-10',19227), (5925,'N','2005-02-10',74529),
+(5925,'N','2005-02-10',74748), (5927,'N','2005-08-18',20264),
+(5927,'N','2005-08-18',58364), (5929,'N','2005-01-19',58364),
+(5935,'N','2006-03-10',19227), (5935,'N','2006-03-10',64251),
+(5935,'N','2006-03-10',5222400), (5935,'N','2006-03-10',5226473),
+(5936,'N','2004-10-29',5015032), (5937,'N','2002-04-05',11237),
+(5937,'N','2002-04-05',23911), (5937,'N','2002-04-05',112133),
+(5937,'N','2002-04-05',169721), (5937,'N','2002-04-05',170650),
+(5937,'N','2002-04-05',5014494), (5937,'N','2002-04-05',5166009),
+(5937,'N','2002-04-05',5181871), (5937,'N','2002-04-05',5213380),
+(5937,'N','2002-04-05',5214875), (5937,'N','2002-04-05',5895062),
+(5938,'N','2006-03-24',11237), (5938,'N','2006-03-24',19227),
+(5938,'N','2006-03-24',23911), (5938,'N','2006-03-24',58364),
+(5938,'N','2006-03-24',64251), (5938,'N','2006-03-24',111716),
+(5938,'N','2006-03-24',112702), (5938,'N','2006-03-24',133136),
+(5938,'N','2006-03-24',168718), (5938,'N','2006-03-24',5137136),
+(5938,'N','2006-03-24',5161519), (5938,'N','2006-03-24',5168120),
+(5938,'N','2006-03-24',5219034), (6234,'N','2006-06-02',103058),
+(6234,'N','2006-06-02',5146844), (6235,'N','2006-06-01',12900),
+(6235,'N','2006-06-01',20264), (6235,'N','2006-06-01',64251),
+(6235,'N','2006-06-01',75160), (6235,'N','2006-06-01',5014494),
+(6235,'N','2006-06-01',5181638), (6236,'N','2006-06-06',112595),
+(6236,'N','2006-06-06',5219601), (6236,'N','2006-06-06',5808374);
+
+CREATE TABLE t3 (dog_id int(10) unsigned);
+INSERT INTO t3 VALUES (5918);
+
+CREATE TABLE t4 (dog_id int(10), t_id char(1), birthday date, KEY (t_id));
+INSERT INTO t4 VALUES (5918,'N','2004-07-22'), (5919,'N','2004-07-20');
+
+CREATE TABLE t5 (dog_id int(10) unsigned, UNIQUE KEY (dog_id));
+INSERT INTO t5 VALUES (5918);
+
+SET @tmp_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='index_condition_pushdown=off';
+
+EXPLAIN
+SELECT * FROM t5 DU, t1 D, t4 DT, t2 DSA, t3 DSAR
+WHERE DU.dog_id=D.dog_id AND D.dog_id=DT.dog_id AND D.birthday=DT.birthday AND
+      DT.t_id=DSA.t_id AND DT.birthday=DSA.birthday AND DSA.dog_id=DSAR.dog_id;
+SELECT * FROM t5 DU, t1 D, t4 DT, t2 DSA, t3 DSAR
+WHERE DU.dog_id=D.dog_id AND D.dog_id=DT.dog_id AND D.birthday=DT.birthday AND
+      DT.t_id=DSA.t_id AND DT.birthday=DSA.birthday AND DSA.dog_id=DSAR.dog_id;
+
+SET optimizer_switch=@tmp_optimizer_switch;
+
+DROP TABLE t1,t2,t3,t4,t5;
+
 SET optimizer_switch=@save_optimizer_switch;