BUG#998236: Assertion failure or valgrind errors at best_access_path ...

- Let fix_semijoin_strategies_for_picked_join_order() set 
  POSITION::prefix_record_count for POSITION records that it copies from
  SJ_MATERIALIZATION_INFO::tables. 
  (These records do not have prefix_record_count set, because they are optimized
   as joins-inside-semijoin-nests, without full advance_sj_state() processing).  
 

1 files changed, 46 insertions, 0 deletions

diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index 7f1181bb562..63bb29a2e13 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -2398,6 +2398,52 @@ DROP TABLE t1;
 set @@join_cache_level= @tmp_jcl_978479;
 set @@optimizer_switch= @tmp_os_978479;
 
+--echo #
+--echo # BUG#998236: Assertion failure or valgrind errors at best_access_path ...
+--echo #
+CREATE TABLE t1 (a1 VARCHAR(3), a2 VARCHAR(35), KEY(a1,a2)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('USA','Arvada'),('USA','Athens'),('USA','Atlanta'),('USA','Augusta'),
+('USA','Aurora'),('USA','Aurora'),('USA','Austin'),('USA','Bakersfield'),
+('USA','Baltimore'),('USA','Baton'),('USA','Beaumont'),('USA','Bellevue'),
+('USA','Berkeley'),('USA','Billings'),('USA','Birmingham'),('USA','Boise'),
+('USA','Boston'),('USA','Boulder'),('USA','Bridgeport'),('USA','Brockton'),
+('USA','Brownsville'),('USA','Buffalo'),('USA','Burbank'),('USA','Cambridge'),
+('USA','Cape'),('USA','Carrollton'),('USA','Carson'),('USA','Cary'),
+('USA','Cedar'),('USA','Chandler'),('USA','Charleston'),('USA','Charlotte'),
+('USA','Chattanooga'),('USA','Chesapeake'),('USA','Chicago'),('USA','Chula'),
+('USA','Cincinnati'),('USA','Citrus'),('USA','Clarksville'),('USA','Clearwater'),
+('USA','Cleveland'),('USA','Colorado'),('USA','Columbia'),('USA','Columbus'),
+('USA','Columbus'),('USA','Compton'),('USA','Concord'),('USA','Coral'),
+('USA','Corona'),('USA','Corpus'),('USA','Costa'),('USA','Dallas'),('USA','Daly'),
+('USA','Davenport'),('USA','Dayton'),('USA','Denver'),('USA','DesMoines'),
+('USA','Detroit'),('USA','Downey'),('USA','Durham'),('USA','East'),('USA','ElCajon'),
+('USA','ElMonte'),('USA','ElPaso'),('USA','Elgin'),('USA','Elizabeth'),
+('USA','Erie'),('USA','Escondido'),('USA','Eugene'),('USA','Evansville'),
+('USA','Fairfield'),('USA','Fall'),('USA','Fayetteville'),('USA','Flint'),
+('USA','Fontana'),('USA','FortCollins'),('USA','FortLauderdale'),('USA','FortWayne'),
+('USA','FortWorth'),('USA','Fremont'),('USA','Fresno'),('USA','Fullerton'),
+('USA','Gainesville'),('USA','GardenGrove'),('USA','Garland'),('USA','Gary'),
+('USA','Gilbert'),('USA','Glendale'),('USA','Glendale'),('USA','GrandPrairie'),
+('USA','GrandRapids'),('USA','Green Bay'),('USA','Greensboro'),('USA','Hampton'),
+('USA','Hartford'),('USA','Hayward'),('USA','Henderson'),('USA','Hialeah'),
+('USA','Inglewood'),('USA','Livonia');
+
+CREATE TABLE t3 (c1 VARCHAR(3), c2 VARCHAR(16), PRIMARY KEY (c1,c2)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('USA','Chinese'), ('USA','English');
+
+# Not reproducible with 2 rows
+CREATE TABLE t2 (b1 INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1);
+
+SELECT alias1.a1 AS field1
+FROM t1 AS alias1, t1 AS alias2
+WHERE alias1.a2 IN ( SELECT a1 FROM t1, t2 )
+AND alias1.a1 IS NULL
+AND ( alias1.a1, alias2.a1 ) IN ( SELECT c1, c1 FROM t3 )
+GROUP BY field1;
+
+DROP TABLE t1,t3,t2;
 
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;