Fixed LP bug #954900.

If the first component of a ref key happened to be a constant appeared
after constant row substitution then no store_key element should be
created for such a component. Yet create_ref_for_key() erroneously could
create such an element that caused construction of invalid ref keys and
wrong results for some joins.

1 files changed, 70 insertions, 0 deletions

diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index e196cb8170b..5185c9dcb2b 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -1400,4 +1400,74 @@ select t2.i from t1 left join t2 on t2.i = t1.i where t1.i = '1:1:1';
 i
 01:01:01
 drop table t1,t2;
+#
+# BUG#954900: unexpected empty set due to an invalid build of key ref
+#                   
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	DU	system	dog_id	NULL	NULL	NULL	1	
+1	SIMPLE	D	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	DSAR	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	DT	range	t_id	t_id	2	NULL	2	Using where
+1	SIMPLE	DSA	ref	PRIMARY	PRIMARY	8	const,test.DT.t_id,test.D.birthday	1	Using index
+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;
+dog_id	dog_id	birthday	dog_id	t_id	birthday	dog_id	t_id	birthday	a_id	dog_id
+5918	5918	2004-07-22	5918	N	2004-07-22	5918	N	2004-07-22	5216551	5918
+5918	5918	2004-07-22	5918	N	2004-07-22	5918	N	2004-07-22	5223640	5918
+5918	5918	2004-07-22	5918	N	2004-07-22	5918	N	2004-07-22	5389491	5918
+5918	5918	2004-07-22	5918	N	2004-07-22	5918	N	2004-07-22	5749434	5918
+5918	5918	2004-07-22	5918	N	2004-07-22	5918	N	2004-07-22	5992424	5918
+SET optimizer_switch=@tmp_optimizer_switch;
+DROP TABLE t1,t2,t3,t4,t5;
 SET optimizer_switch=@save_optimizer_switch;