Merge olga.mysql.com:/home/igor/mysql-5.0-opt

into  olga.mysql.com:/home/igor/dev-opt/mysql-5.0-opt-bug26963


mysql-test/r/select.result:
  Auto merged
mysql-test/t/select.test:
  Auto merged

2 files changed, 85 insertions, 0 deletions

diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index d5f07d0fc47..31d15981d93 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3942,4 +3942,42 @@ cc 	cc 	7
 aa  	aa	2
 aa	aa	2
 DROP TABLE t1,t2;
+CREATE TABLE t1 (
+access_id int NOT NULL default '0',
+name varchar(20) default NULL,
+rank int NOT NULL default '0',
+KEY idx (access_id)
+);
+CREATE TABLE t2 (
+faq_group_id int NOT NULL default '0',
+faq_id int NOT NULL default '0',
+access_id int default NULL,
+UNIQUE KEY idx1 (faq_id),
+KEY idx2 (faq_group_id,faq_id)
+);
+INSERT INTO t1 VALUES 
+(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
+INSERT INTO t2 VALUES
+(261,265,1),(490,494,1);
+SELECT t2.faq_id 
+FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
+ON (t1.access_id = t2.access_id)
+LEFT JOIN t2 t
+ON (t.faq_group_id = t2.faq_group_id AND
+find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+WHERE
+t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+faq_id
+265
+SELECT t2.faq_id 
+FROM t1 INNER JOIN t2
+ON (t1.access_id = t2.access_id)
+LEFT JOIN t2 t
+ON (t.faq_group_id = t2.faq_group_id AND
+find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+WHERE
+t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+faq_id
+265
+DROP TABLE t1,t2;
 End of 5.0 tests
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 39769dada60..883ea7bf0b0 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -3308,4 +3308,51 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 
 DROP TABLE t1,t2;
 
+
+#
+# Bug #26963: join with predicates that contain fields from equalities evaluated
+#             to constants after constant table substitution
+#
+
+CREATE TABLE t1 (
+ access_id int NOT NULL default '0',
+ name varchar(20) default NULL,
+ rank int NOT NULL default '0',
+ KEY idx (access_id)
+);
+
+CREATE TABLE t2 (
+  faq_group_id int NOT NULL default '0',
+  faq_id int NOT NULL default '0',
+  access_id int default NULL,
+  UNIQUE KEY idx1 (faq_id),
+  KEY idx2 (faq_group_id,faq_id)
+);
+
+INSERT INTO t1 VALUES 
+  (1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
+INSERT INTO t2 VALUES
+  (261,265,1),(490,494,1);
+
+
+SELECT t2.faq_id 
+  FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
+       ON (t1.access_id = t2.access_id)
+       LEFT JOIN t2 t
+       ON (t.faq_group_id = t2.faq_group_id AND
+           find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+   WHERE
+     t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+
+SELECT t2.faq_id 
+  FROM t1 INNER JOIN t2
+       ON (t1.access_id = t2.access_id)
+       LEFT JOIN t2 t
+       ON (t.faq_group_id = t2.faq_group_id AND
+           find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+   WHERE
+     t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests