Merge branch '10.0' into 10.1

9 files changed, 167 insertions, 31 deletions

diff --git a/extra/mysqld_safe_helper.c b/extra/mysqld_safe_helper.c
index 09e507c6e1c..2ccff20b910 100644
--- a/extra/mysqld_safe_helper.c
+++ b/extra/mysqld_safe_helper.c
@@ -59,7 +59,7 @@ int main(int argc, char *argv[])
   MY_INIT(argv[0]);
 
   if (argc < 3)
-    do_usage(argv[0]);
+    do_usage();
 
   user_info= my_check_user(argv[1], MYF(0));
   if (user_info ? my_set_user(argv[1], user_info, MYF(MY_WME))
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index 24640154c59..9bfd5bd67b5 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -400,3 +400,45 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
 c1	c2
 drop table t1, t2;
+#
+# MDEV-12673: cost-based choice between materialization and in-to-exists
+#
+CREATE TABLE t1 (
+pk1 int, a1 varchar(3), b1 varchar(3), PRIMARY KEY (pk1), KEY(a1), KEY(b1)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'foo','bar'),(2,'bar','foo');
+CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 VARCHAR(3), KEY(a2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'abc'),(2,'xyz'),(3,'foo');
+SELECT 'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 )
+0
+SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 )
+1
+EXPLAIN 
+SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	t2	const	PRIMARY,a2	PRIMARY	4	const	1	
+2	SUBQUERY	t1	ref	a1,b1	b1	6	const	1	Using where
+DROP TABLE t1,t2;
+CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (i2 int, c2 varchar(3), KEY(i2,c2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'abc'),(2,'foo');
+CREATE TABLE t3 (pk3 int PRIMARY KEY, c3 varchar(3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1,'foo'),(2,'bar');
+SELECT * FROM t1 WHERE i1 NOT IN ( 
+SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 
+);
+i1
+1
+EXPLAIN
+SELECT * FROM t1 WHERE i1 NOT IN ( 
+SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t3	const	PRIMARY	PRIMARY	4	const	1	
+2	DEPENDENT SUBQUERY	t2	index	NULL	i2	11	NULL	2	Using where; Using index
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/suite/innodb/r/log_file_size.result b/mysql-test/suite/innodb/r/log_file_size.result
index 67794f293aa..621176d7879 100644
--- a/mysql-test/suite/innodb/r/log_file_size.result
+++ b/mysql-test/suite/innodb/r/log_file_size.result
@@ -1,3 +1,6 @@
+call mtr.add_suppression("mysqld got signal 11");
+call mtr.add_suppression("Attempting backtrace");
+FLUSH TABLES;
 CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB;
 BEGIN;
 INSERT INTO t1 VALUES (42);
diff --git a/mysql-test/suite/innodb/t/log_file_size.test b/mysql-test/suite/innodb/t/log_file_size.test
index bf307123734..4bae93957e8 100644
--- a/mysql-test/suite/innodb/t/log_file_size.test
+++ b/mysql-test/suite/innodb/t/log_file_size.test
@@ -31,6 +31,7 @@ call mtr.add_suppression("InnoDB: Only one log file found");
 call mtr.add_suppression("InnoDB: Log file .*ib_logfile[01].* size");
 call mtr.add_suppression("InnoDB: Unable to open .*ib_logfile0. to check native AIO read support");
 # InnoDB shutdown after refused startup is not clean in 10.0 or 10.1!
+--source include/not_valgrind.inc
 call mtr.add_suppression("mysqld got signal 11");
 call mtr.add_suppression("Attempting backtrace");
 FLUSH TABLES;
diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test
index 8205e94b203..316ac707bef 100644
--- a/mysql-test/t/subselect_mat_cost_bugs.test
+++ b/mysql-test/t/subselect_mat_cost_bugs.test
@@ -424,3 +424,42 @@ explain select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
 select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
 
 drop table t1, t2;
+
+--echo #
+--echo # MDEV-12673: cost-based choice between materialization and in-to-exists
+--echo #
+
+CREATE TABLE t1 (
+ pk1 int, a1 varchar(3), b1 varchar(3), PRIMARY KEY (pk1), KEY(a1), KEY(b1)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'foo','bar'),(2,'bar','foo');
+
+CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 VARCHAR(3), KEY(a2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'abc'),(2,'xyz'),(3,'foo');
+
+SELECT 'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+EXPLAIN 
+SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2 (i2 int, c2 varchar(3), KEY(i2,c2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'abc'),(2,'foo');
+
+CREATE TABLE t3 (pk3 int PRIMARY KEY, c3 varchar(3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1,'foo'),(2,'bar');
+
+SELECT * FROM t1 WHERE i1 NOT IN ( 
+  SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 
+);
+
+EXPLAIN
+SELECT * FROM t1 WHERE i1 NOT IN ( 
+  SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 
+);
+
+DROP TABLE t1,t2,t3;
diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt
index 7a42742a181..54f9da6dd38 100644
--- a/sql/CMakeLists.txt
+++ b/sql/CMakeLists.txt
@@ -68,6 +68,7 @@ ENDIF()
 ADD_CUSTOM_COMMAND(
   OUTPUT ${CMAKE_CURRENT_BINARY_DIR}/lex_token.h
   COMMAND gen_lex_token > lex_token.h
+  DEPENDS gen_lex_token
 )
 
 ADD_DEFINITIONS(-DMYSQL_SERVER -DHAVE_EVENT_SCHEDULER)
@@ -322,6 +323,7 @@ ENDIF()
 ADD_CUSTOM_COMMAND(
   OUTPUT ${CMAKE_CURRENT_BINARY_DIR}/lex_hash.h
   COMMAND gen_lex_hash > lex_hash.h
+  DEPENDS gen_lex_hash
 )
 
 MYSQL_ADD_EXECUTABLE(mysql_tzinfo_to_sql tztime.cc COMPONENT Server)
@@ -446,7 +448,7 @@ IF(WIN32)
     ${CMAKE_CURRENT_BINARY_DIR}/my_bootstrap.sql
     mysql_bootstrap_sql.c
     WORKING_DIRECTORY ${CMAKE_CURRENT_BINARY_DIR}
-    DEPENDS ${my_bootstrap_sql}
+    DEPENDS comp_sql ${my_bootstrap_sql}
   )
 
   MYSQL_ADD_EXECUTABLE(mysql_install_db 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 88426dcb8b0..0a282705e53 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8731,12 +8731,14 @@ get_best_combination(JOIN *join)
   {
     if (j->bush_children)
       j= j->bush_children->start;
-      
-    used_tables|= j->table->map;
-    if ((keyuse= join->best_positions[tablenr].key) &&
-        create_ref_for_key(join, j, keyuse, TRUE, used_tables))
-     DBUG_RETURN(TRUE);              // Something went wrong
 
+    used_tables|= j->table->map;
+    if (j->type != JT_CONST && j->type != JT_SYSTEM)
+    {
+      if ((keyuse= join->best_positions[tablenr].key) &&
+          create_ref_for_key(join, j, keyuse, TRUE, used_tables))
+        DBUG_RETURN(TRUE);              // Something went wrong
+    }
     if (j->last_leaf_in_bush)
       j= j->bush_root_tab;
   }
diff --git a/storage/maria/ma_pagecache.c b/storage/maria/ma_pagecache.c
index 39c6ece0615..b5fa9497065 100644
--- a/storage/maria/ma_pagecache.c
+++ b/storage/maria/ma_pagecache.c
@@ -493,7 +493,8 @@ error:
 
 #define FLUSH_CACHE         2000            /* sort this many blocks at once */
 
-static void free_block(PAGECACHE *pagecache, PAGECACHE_BLOCK_LINK *block);
+static my_bool free_block(PAGECACHE *pagecache, PAGECACHE_BLOCK_LINK *block,
+                          my_bool abort_if_pinned);
 static void unlink_hash(PAGECACHE *pagecache, PAGECACHE_HASH_LINK *hash_link);
 #ifndef DBUG_OFF
 static void test_key_cache(PAGECACHE *pagecache,
@@ -1943,7 +1944,7 @@ restart:
         removed from the cache as we set the PCBLOCK_REASSIGNED
         flag (see the code below that handles reading requests).
       */
-      free_block(pagecache, block);
+      free_block(pagecache, block, 0);
       return 0;
     }
     /* Wait until the page is flushed on disk */
@@ -1954,7 +1955,7 @@ restart:
     /* Invalidate page in the block if it has not been done yet */
     DBUG_ASSERT(block->status);                 /* Should always be true */
     if (block->status)
-      free_block(pagecache, block);
+      free_block(pagecache, block, 0);
     return 0;
   }
 
@@ -1979,8 +1980,13 @@ restart:
     }
     else
     {
-      DBUG_ASSERT(hash_link->requests > 0);
-      hash_link->requests--;
+      /*
+        When we come here either PCBLOCK_REASSIGNED or PCBLOCK_IN_SWITCH are
+        active. In both cases wqueue_release_queue() is called when the
+        state changes.
+      */
+      DBUG_ASSERT(block->hash_link == hash_link);
+      remove_reader(block);
       KEYCACHE_DBUG_PRINT("find_block",
                           ("request waiting for old page to be saved"));
       {
@@ -3648,7 +3654,7 @@ static my_bool pagecache_delete_internal(PAGECACHE *pagecache,
   DBUG_ASSERT(block->hash_link->requests > 0);
   page_link->requests--;
   /* See NOTE for pagecache_unlock() about registering requests. */
-  free_block(pagecache, block);
+  free_block(pagecache, block, 0);
   dec_counter_for_resize_op(pagecache);
   return 0;
 
@@ -4230,7 +4236,8 @@ end:
   and add it to the free list.
 */
 
-static void free_block(PAGECACHE *pagecache, PAGECACHE_BLOCK_LINK *block)
+static my_bool free_block(PAGECACHE *pagecache, PAGECACHE_BLOCK_LINK *block,
+                          my_bool abort_if_pinned)
 {
   uint status= block->status;
   KEYCACHE_THREAD_TRACE("free block");
@@ -4244,11 +4251,27 @@ static void free_block(PAGECACHE *pagecache, PAGECACHE_BLOCK_LINK *block)
     /*
       While waiting for readers to finish, new readers might request the
       block. But since we set block->status|= PCBLOCK_REASSIGNED, they
-      will wait on block->wqueue[COND_FOR_SAVED]. They must be signalled
+      will wait on block->wqueue[COND_FOR_SAVED]. They must be signaled
       later.
     */
     block->status|= PCBLOCK_REASSIGNED;
     wait_for_readers(pagecache, block);
+    if (unlikely(abort_if_pinned) && unlikely(block->pins))
+    {
+      /*
+        Block got pinned while waiting for readers.
+        This can only happens when called from flush_pagecache_blocks_int()
+        when flushing blocks as part of prepare for maria_close() or from
+        flush_cached_blocks()
+      */
+      block->status&= ~PCBLOCK_REASSIGNED;
+      unreg_request(pagecache, block, 0);
+
+      /* All pending requests for this page must be resubmitted. */
+      if (block->wqueue[COND_FOR_SAVED].last_thread)
+        wqueue_release_queue(&block->wqueue[COND_FOR_SAVED]);
+      return 1;
+    }
     unlink_hash(pagecache, block->hash_link);
   }
 
@@ -4299,6 +4322,8 @@ static void free_block(PAGECACHE *pagecache, PAGECACHE_BLOCK_LINK *block)
   /* All pending requests for this page must be resubmitted. */
   if (block->wqueue[COND_FOR_SAVED].last_thread)
     wqueue_release_queue(&block->wqueue[COND_FOR_SAVED]);
+
+  return 0;
 }
 
 
@@ -4434,9 +4459,16 @@ static int flush_cached_blocks(PAGECACHE *pagecache,
     if (! (type == FLUSH_KEEP || type == FLUSH_KEEP_LAZY ||
            type == FLUSH_FORCE_WRITE))
     {
-      pagecache->blocks_changed--;
-      pagecache->global_blocks_changed--;
-      free_block(pagecache, block);
+      if (!free_block(pagecache, block, 1))
+      {
+        pagecache->blocks_changed--;
+        pagecache->global_blocks_changed--;
+      }
+      else
+      {
+        block->status&= ~PCBLOCK_IN_FLUSH;
+        link_to_file_list(pagecache, block, file, 1);
+      }
     }
     else
     {
@@ -4674,7 +4706,7 @@ restart:
             /* It's a temporary file */
             pagecache->blocks_changed--;
 	    pagecache->global_blocks_changed--;
-            free_block(pagecache, block);
+            free_block(pagecache, block, 0);
           }
         }
         else if (type != FLUSH_KEEP_LAZY)
@@ -4744,11 +4776,12 @@ restart:
 #endif
         next= block->next_changed;
         if (block->hash_link->file.file == file->file &&
+            !block->pins &&
             (! (block->status & PCBLOCK_CHANGED)
              || type == FLUSH_IGNORE_CHANGED))
         {
           reg_requests(pagecache, block, 1);
-          free_block(pagecache, block);
+          free_block(pagecache, block, 1);
         }
       }
     }
diff --git a/tests/fork_big.pl b/tests/fork_big.pl
index 6e78e779d11..ec1f131d453 100755
--- a/tests/fork_big.pl
+++ b/tests/fork_big.pl
@@ -1,4 +1,5 @@
 #!/usr/bin/perl -w
+use strict;
 
 # Copyright (c) 2001, 2006 MySQL AB
 #   Use is subject to license terms
@@ -21,7 +22,7 @@
 #
 # Tested a lot with:  --threads=30
 
-$opt_loop_count=500000; # Change this to make test harder/easier
+my $opt_loop_count=500000; # Change this to make test harder/easier
 
 ##################### Standard benchmark inits ##############################
 
@@ -31,6 +32,13 @@ use Benchmark;
 
 package main;
 
+our ($opt_skip_create,$opt_skip_in,$opt_verbose,$opt_fast_insert);
+our ($opt_lock_tables,$opt_debug,$opt_skip_delete,$opt_fast,$opt_force);
+our ($opt_threads);
+our ($opt_host,$opt_user,$opt_password,$opt_db);
+my (@testtables, $abort_table, $numtables, $start_time, $end_time);
+my ($dbh);
+
 $opt_skip_create=$opt_skip_in=$opt_verbose=$opt_fast_insert=
 $opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=0;
 $opt_threads=5;
@@ -94,6 +102,8 @@ $|= 1;				# Autoflush
 #### Start the tests
 ####
 
+my ($i, $pid, %work);
+
 for ($i=0 ; $i < $opt_threads ; $i ++)
 {
   test_insert() if (($pid=fork()) == 0); $work{$pid}="insert";
@@ -118,10 +128,13 @@ test_alter() if (($pid=fork()) == 0); $work{$pid}="alter";
 
 print "Started " . ($opt_threads*2+4) . " threads\n";
 
+my ($errors, $running_insert_threads);
+
 $errors=0;
 $running_insert_threads=$opt_threads+$numtables;
 while (($pid=wait()) != -1)
 {
+  my ($ret);
   $ret=$?/256;
   print "thread '" . $work{$pid} . "' finished with exit code $ret\n";
   if ($work{$pid} =~ /^insert/)
@@ -203,7 +216,7 @@ sub test_insert
 
 sub test_select
 {
-  my ($dbh, $i, $j, $count, $loop);
+  my ($dbh, $i, $j, $count, $loop, $count_query, $row_counts);
 
   $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
 		      $opt_user, $opt_password,
@@ -270,7 +283,7 @@ sub test_select_count
 
 sub test_join
 {
-  my ($dbh, $i, $j, $count, $loop);
+  my ($dbh, $i, $j, $count, $loop, $count_query, $row_counts);
 
   $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
 		      $opt_user, $opt_password,
@@ -389,7 +402,7 @@ sub test_update
 
 sub test_check
 {
-  my ($dbh, $row, $i, $j, $type, $table);
+  my ($dbh, $sth, $row, $i, $j, $type, $table);
   $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
 		      $opt_user, $opt_password,
 		    { PrintError => 0}) || die $DBI::errstr;
@@ -397,7 +410,7 @@ sub test_check
   $type= "check";
   for ($i=$j=0 ; !test_if_abort($dbh) ; $i++)
   {
-    sleep(1000);
+    sleep(200);
     $table=$testtables[$j]->[0];
     $sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n";
     $sth->execute || die $DBI::errstr;
@@ -426,7 +439,7 @@ sub test_check
 
 sub test_repair
 {
-  my ($dbh, $row, $i, $type, $table);
+  my ($dbh, $sth, $row, $i, $type, $table);
   $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
 		      $opt_user, $opt_password,
 		    { PrintError => 0}) || die $DBI::errstr;
@@ -434,7 +447,7 @@ sub test_repair
   $type= "repair";
   for ($i=0 ; !test_if_abort($dbh) ; $i++)
   {
-    sleep(2000);
+    sleep(100);
     $table=$testtables[0]->[0];
     $sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n";
     $sth->execute || die $DBI::errstr;
@@ -470,7 +483,7 @@ sub test_flush
   $count=0;
   while (!test_if_abort($dbh))
   {
-    sleep(3000);
+    sleep(300);
     $dbh->do("flush tables $tables") ||
       die "Got error on flush $DBI::errstr\n";
     $count++;
@@ -488,7 +501,7 @@ sub test_flush
 sub test_database
 {
   my ($database) = @_;
-  my ($dbh, $row, $i, $type, $tables);
+  my ($dbh, $sth, $row, $i, $type, $tables);
   $dbh = DBI->connect("DBI:mysql:$database:$opt_host",
 		      $opt_user, $opt_password,
 		    { PrintError => 0}) || die $DBI::errstr;
@@ -521,7 +534,7 @@ sub test_database
 
 sub test_alter
 {
-  my ($dbh, $row, $i, $type, $table);
+  my ($dbh, $sth, $row, $i, $type, $table);
   $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
 		      $opt_user, $opt_password,
 		    { PrintError => 0}) || die $DBI::errstr;
@@ -559,6 +572,7 @@ sub signal_abort
 sub test_if_abort()
 {
   my ($dbh)=@_;
+  my ($row);
   $row=simple_query($dbh,"select * from $opt_db.$abort_table");
   return (defined($row) && defined($row->[0]) != 0) ? 1 : 0;
 }
@@ -567,7 +581,7 @@ sub test_if_abort()
 sub make_count_query
 {
   my ($table_count)= @_;
-  my ($tables, $count_query, $i, $tables_def);
+  my ($tables, $count_query, $i, $table_def);
   $tables="";
   $count_query="select high_priority ";
   $table_count--;