Merge

4 files changed, 67 insertions, 13 deletions

diff --git a/mysql-test/include/world_schema.inc b/mysql-test/include/world_schema.inc
index bd7b768e83b..b2161afbb0a 100644
--- a/mysql-test/include/world_schema.inc
+++ b/mysql-test/include/world_schema.inc
@@ -6,7 +6,7 @@ CREATE TABLE Country (
   Capital int(11) default NULL,
   PRIMARY KEY  (Code),
   UNIQUE INDEX (Name)
-) COLLATE latin1_bin;
+);
 CREATE TABLE City (
   ID int(11) NOT NULL auto_increment,
   Name char(35) NOT NULL default '',
@@ -15,11 +15,11 @@ CREATE TABLE City (
   PRIMARY KEY  (ID),
   INDEX (Population),
   INDEX (Country) 
-) COLLATE latin1_bin;
+);
 CREATE TABLE CountryLanguage (
   Country char(3) NOT NULL default '',
   Language char(30) NOT NULL default '',
   Percentage float(3,1) NOT NULL default '0.0',
   PRIMARY KEY  (Country, Language),
   INDEX (Percentage)
-) COLLATE latin1_bin; 
+); 
diff --git a/mysql-test/include/world_schema1.inc b/mysql-test/include/world_schema1.inc
index 48448b5c1d8..172e79a7c8c 100644
--- a/mysql-test/include/world_schema1.inc
+++ b/mysql-test/include/world_schema1.inc
@@ -4,15 +4,15 @@ CREATE TABLE Country (
   SurfaceArea float(10,2) NOT NULL default '0.00',
   Population int(11) NOT NULL default '0',
   Capital int(11) default NULL
-) COLLATE latin1_bin;
+);
 CREATE TABLE City (
   ID int(11) NOT NULL,
   Name char(35) NOT NULL default '',
   Country char(3) NOT NULL default '',
   Population int(11) NOT NULL default '0'
-) COLLATE latin1_bin;
+);
 CREATE TABLE CountryLanguage (
   Country char(3) NOT NULL default '',
   Language char(30) NOT NULL default '',
   Percentage float(3,1) NOT NULL default '0.0'
-) COLLATE latin1_bin;
+);
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 0d2706c7739..d26ad9bf5ce 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -12,18 +12,18 @@ Name char(52) NOT NULL default '',
 SurfaceArea float(10,2) NOT NULL default '0.00',
 Population int(11) NOT NULL default '0',
 Capital int(11) default NULL
-) COLLATE latin1_bin;
+);
 CREATE TABLE City (
 ID int(11) NOT NULL,
 Name char(35) NOT NULL default '',
 Country char(3) NOT NULL default '',
 Population int(11) NOT NULL default '0'
-) COLLATE latin1_bin;
+);
 CREATE TABLE CountryLanguage (
 Country char(3) NOT NULL default '',
 Language char(30) NOT NULL default '',
 Percentage float(3,1) NOT NULL default '0.0'
-) COLLATE latin1_bin;
+);
 SELECT COUNT(*) FROM Country;
 COUNT(*)
 239
@@ -810,7 +810,7 @@ Population int(11) NOT NULL default '0',
 Capital int(11) default NULL,
 PRIMARY KEY  (Code),
 UNIQUE INDEX (Name)
-) COLLATE latin1_bin;
+);
 CREATE TABLE City (
 ID int(11) NOT NULL auto_increment,
 Name char(35) NOT NULL default '',
@@ -819,14 +819,14 @@ Population int(11) NOT NULL default '0',
 PRIMARY KEY  (ID),
 INDEX (Population),
 INDEX (Country) 
-) COLLATE latin1_bin;
+);
 CREATE TABLE CountryLanguage (
 Country char(3) NOT NULL default '',
 Language char(30) NOT NULL default '',
 Percentage float(3,1) NOT NULL default '0.0',
 PRIMARY KEY  (Country, Language),
 INDEX (Percentage)
-) COLLATE latin1_bin;
+);
 show variables like 'join_buffer_size';
 Variable_name	Value
 join_buffer_size	131072
@@ -5582,7 +5582,7 @@ EXPLAIN
 SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	
-1	SIMPLE	t1	ref	cu	cu	33	func	2	Using where; Using index
+1	SIMPLE	t1	ref	cu	cu	33	func	2	Using where; Using index; Using join buffer (flat, BNLH join)
 SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ;
 i
 6
@@ -6056,4 +6056,35 @@ a4	b5
 SET SESSION optimizer_switch = 'outer_join_with_cache=off';
 SET SESSION join_cache_level = DEFAULT;
 DROP TABLE t1,t2,t3,t4,t5;
+#
+# Bug #670380: hash join for non-binary collation
+#
+CREATE TABLE t1 (pk int PRIMARY KEY, a varchar(32));
+CREATE TABLE t2 (pk int PRIMARY KEY, a varchar(32), INDEX idx(a));
+INSERT INTO t1 VALUES
+(10,'AAA'), (20,'BBBB'), (30,'Cc'), (40,'DD'), (50,'ee');
+INSERT INTO t2 VALUES
+(1,'Bbbb'), (2,'BBB'), (3,'bbbb'), (4,'AaA'), (5,'CC'),
+(6,'cC'), (7,'CCC'), (8,'AAA'), (9,'bBbB'), (10,'aaaa'),
+(11,'a'), (12,'dd'), (13,'EE'), (14,'ee'), (15,'D');
+SET SESSION join_cache_level = 4;
+EXPLAIN
+SELECT * FROM t1,t2 WHERE t1.a=t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	SIMPLE	t2	ref	idx	idx	35	test.t1.a	2	Using join buffer (flat, BNLH join)
+SELECT * FROM t1,t2 WHERE t1.a=t2.a;
+pk	a	pk	a
+20	BBBB	1	Bbbb
+20	BBBB	3	bbbb
+10	AAA	4	AaA
+30	Cc	5	CC
+30	Cc	6	cC
+10	AAA	8	AAA
+20	BBBB	9	bBbB
+40	DD	12	dd
+50	ee	13	EE
+50	ee	14	ee
+SET SESSION join_cache_level = DEFAULT;
+DROP TABLE t1,t2;
 set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 1905297b25b..b7c275af821 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -2700,5 +2700,28 @@ SET SESSION join_cache_level = DEFAULT;
 
 DROP TABLE t1,t2,t3,t4,t5;
 
+--echo #
+--echo # Bug #670380: hash join for non-binary collation
+--echo #
+
+CREATE TABLE t1 (pk int PRIMARY KEY, a varchar(32));
+CREATE TABLE t2 (pk int PRIMARY KEY, a varchar(32), INDEX idx(a));
+INSERT INTO t1 VALUES
+  (10,'AAA'), (20,'BBBB'), (30,'Cc'), (40,'DD'), (50,'ee');
+INSERT INTO t2 VALUES
+  (1,'Bbbb'), (2,'BBB'), (3,'bbbb'), (4,'AaA'), (5,'CC'),
+  (6,'cC'), (7,'CCC'), (8,'AAA'), (9,'bBbB'), (10,'aaaa'),
+  (11,'a'), (12,'dd'), (13,'EE'), (14,'ee'), (15,'D');
+
+SET SESSION join_cache_level = 4;
+
+EXPLAIN
+SELECT * FROM t1,t2 WHERE t1.a=t2.a;
+SELECT * FROM t1,t2 WHERE t1.a=t2.a;
+
+SET SESSION join_cache_level = DEFAULT;
+
+DROP TABLE t1,t2;
+ 
 # this must be the last command in the file
 set @@optimizer_switch=@save_optimizer_switch;