Fixed the cost for HASH join

Removed an old '* 2' from the HASH join cost. This was made obsolete by
a later patch that added cost for copying the data out from the join buffer
to table->record.

I also added some 'echo' to some test cases to make it easier to debug
test case changes.

Test case changes:
- subselect3_jcl6 and subselect_sj2_jcl6 result changes as materialized
  tables changed to hash join + first_match

4 files changed, 53 insertions, 16 deletions

diff --git a/mysql-test/main/subselect3.inc b/mysql-test/main/subselect3.inc
index fb330dfa3bc..c9d20800026 100644
--- a/mysql-test/main/subselect3.inc
+++ b/mysql-test/main/subselect3.inc
@@ -242,8 +242,9 @@ from t2;
 
 drop table t1,t2,t3,t4;
 
-# More tests for tricky multi-column cases, where some of pushed-down
-# equalities are used for index lookups and some arent.
+--echo # More tests for tricky multi-column cases, where some of pushed-down
+--echo # equalities are used for index lookups and some are not.
+
 create table t1 (oref char(4), grp int, ie1 int, ie2 int);
 insert into t1 (oref, grp, ie1, ie2) values
  ('aa', 10, 2, 1),
@@ -470,8 +471,11 @@ explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
 
 select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
 
+explain select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+
 select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
 
+explain select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
 select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
 
 explain 
@@ -1012,7 +1016,6 @@ explain select * from t1 where a in (select a from t1);
 drop table t1;
 set @@optimizer_switch=@save_optimizer_switch;
 
-set @@optimizer_switch=@save_optimizer_switch;
 set @@optimizer_switch='materialization=off';
 
 #
@@ -1034,9 +1037,9 @@ explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
 explain select straight_join * from t2 X, t2 Y 
 where X.a in (select straight_join A.a from t1 A, t1 B);
 
-#
-# SJ-Materialization scan + first table being system const table
-#
+--echo #
+--echo # SJ-Materialization scan + first table being system const table
+--echo #
 create table t0 (a int, b int);
 insert into t0 values(1,1);
 explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
@@ -1044,9 +1047,12 @@ create table t4 as select a as x, a as y from t1;
 explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30);
 drop table t0,t1,t2,t3,t4;
 
-#
-# LooseScan with ref access
-#
+--echo #
+--echo # LooseScan with ref access
+--echo #
+
+set @@optimizer_switch='join_cache_hashed=off';
+
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (a int, b int, filler char(100), key(a,b));
diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result
index 710aa00d5aa..c9a1ea6ca61 100644
--- a/mysql-test/main/subselect3.result
+++ b/mysql-test/main/subselect3.result
@@ -278,6 +278,8 @@ a	b	oref	Z
 NULL	1	100	0
 NULL	2	100	NULL
 drop table t1,t2,t3,t4;
+# More tests for tricky multi-column cases, where some of pushed-down
+# equalities are used for index lookups and some are not.
 create table t1 (oref char(4), grp int, ie1 int, ie2 int);
 insert into t1 (oref, grp, ie1, ie2) values
 ('aa', 10, 2, 1),
@@ -617,10 +619,18 @@ cc	2	0
 cc	NULL	NULL
 aa	1	1
 bb	NULL	NULL
+explain select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	Using where
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	Using where
 select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
 oref	a
 cc	5
 aa	1
+explain select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using where; Full scan on NULL key
 select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
 oref	a
 ee	NULL
@@ -1220,7 +1230,6 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
 drop table t1;
 set @@optimizer_switch=@save_optimizer_switch;
-set @@optimizer_switch=@save_optimizer_switch;
 set @@optimizer_switch='materialization=off';
 create table t1 (a int);
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -1254,6 +1263,9 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+#
+# SJ-Materialization scan + first table being system const table
+#
 create table t0 (a int, b int);
 insert into t0 values(1,1);
 explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
@@ -1268,6 +1280,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	10	Using where; Start temporary
 1	PRIMARY	t3	ref	a	a	5	test.t4.x	10	Using where; End temporary
 drop table t0,t1,t2,t3,t4;
+#
+# LooseScan with ref access
+#
+set @@optimizer_switch='join_cache_hashed=off';
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (a int, b int, filler char(100), key(a,b));
diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result
index 2bbc11863df..acd1269d875 100644
--- a/mysql-test/main/subselect3_jcl6.result
+++ b/mysql-test/main/subselect3_jcl6.result
@@ -281,6 +281,8 @@ a	b	oref	Z
 NULL	1	100	0
 NULL	2	100	NULL
 drop table t1,t2,t3,t4;
+# More tests for tricky multi-column cases, where some of pushed-down
+# equalities are used for index lookups and some are not.
 create table t1 (oref char(4), grp int, ie1 int, ie2 int);
 insert into t1 (oref, grp, ie1, ie2) values
 ('aa', 10, 2, 1),
@@ -620,10 +622,18 @@ cc	2	0
 cc	NULL	NULL
 aa	1	1
 bb	NULL	NULL
+explain select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	6	Using where
+1	PRIMARY	t2	hash_ALL	NULL	#hash#$hj	10	test.t1.oref,test.t1.ie	7	Using where; Using join buffer (flat, BNLH join)
 select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
 oref	a
-aa	1
 cc	5
+aa	1
+explain select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using where; Full scan on NULL key
 select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
 oref	a
 ee	NULL
@@ -1223,7 +1233,6 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	hash_ALL	NULL	#hash#$hj	6	test.t1.a	2	Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join)
 drop table t1;
 set @@optimizer_switch=@save_optimizer_switch;
-set @@optimizer_switch=@save_optimizer_switch;
 set @@optimizer_switch='materialization=off';
 create table t1 (a int);
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -1257,6 +1266,9 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+#
+# SJ-Materialization scan + first table being system const table
+#
 create table t0 (a int, b int);
 insert into t0 values(1,1);
 explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
@@ -1271,6 +1283,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	10	Using where; Start temporary
 1	PRIMARY	t3	ref	a	a	5	test.t4.x	10	Using where; End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 drop table t0,t1,t2,t3,t4;
+#
+# LooseScan with ref access
+#
+set @@optimizer_switch='join_cache_hashed=off';
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (a int, b int, filler char(100), key(a,b));
diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result
index 672b1654aa8..6ccec99ba5e 100644
--- a/mysql-test/main/subselect_sj2_jcl6.result
+++ b/mysql-test/main/subselect_sj2_jcl6.result
@@ -264,9 +264,8 @@ explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
 from t2 ot where a in (select a from t1 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	22	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	52	
+1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	22	Using where
+1	PRIMARY	it	hash_ALL	NULL	#hash#$hj	5	test.ot.a	52	Using where; FirstMatch(ot); Using join buffer (flat, BNLH join)
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
 from t2 ot where a in (select a from t1 it);
@@ -290,8 +289,8 @@ a	mid(filler1, 1,10)	length(filler1)=length(filler2)
 16	filler1234	1
 17	filler1234	1
 18	filler1234	1
-19	filler1234	1
 3	duplicate 	1
+19	filler1234	1
 19	duplicate 	1
 drop table t1, t2;
 create table t1 (a int, b int, key(a));