Merge 5.5->10.0-base

19 files changed, 356 insertions, 40 deletions

diff --git a/cmake/os/FreeBSD.cmake b/cmake/os/FreeBSD.cmake
index 3a5a29f6de6..ffb89c81206 100644
--- a/cmake/os/FreeBSD.cmake
+++ b/cmake/os/FreeBSD.cmake
@@ -24,9 +24,10 @@
 # ADD_DEFINITIONS(-DHAVE_BROKEN_REALPATH)
 
 # Find libexecinfo (library that contains backtrace_symbols etc)
-INCLUDE_DIRECTORIES(/usr/local/include)
-SET(CMAKE_REQUIRED_INCLUDES ${CMAKE_REQUIRED_INCLUDES} /usr/local/include )
-SET(ENV{LIB} "$ENV{LIB}:/usr/local/lib")
+SET(EXECINFO_ROOT /usr/local CACHE INTERNAL "Where to find execinfo library and header")
+INCLUDE_DIRECTORIES(${EXECINFO_ROOT}/include)
+SET(CMAKE_REQUIRED_INCLUDES ${CMAKE_REQUIRED_INCLUDES} ${EXECINFO_ROOT}/include)
+SET(ENV{LIB} "$ENV{LIB}:${EXECINFO_ROOT}/lib")
 FIND_LIBRARY(EXECINFO NAMES execinfo)
 IF(EXECINFO)
  SET(LIBEXECINFO ${EXECINFO})
diff --git a/libmysqld/lib_sql.cc b/libmysqld/lib_sql.cc
index e025af0ac8f..0809e91d134 100644
--- a/libmysqld/lib_sql.cc
+++ b/libmysqld/lib_sql.cc
@@ -684,8 +684,10 @@ void *create_embedded_thd(int client_flag)
   thd->data_tail= &thd->first_data;
   bzero((char*) &thd->net, sizeof(thd->net));
 
+  mysql_mutex_lock(&LOCK_thread_count);
   thread_count++;
   threads.append(thd);
+  mysql_mutex_unlock(&LOCK_thread_count);
   thd->mysys_var= 0;
   return thd;
 err:
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 40420a15c25..3438579d97e 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2215,6 +2215,35 @@ DROP VIEW v1;
 DROP TABLE t1,t2;
 set optimizer_switch=@save_optimizer_switch;
 #
+# mdev-5078: sum over a view/derived table
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 (a) VALUES (1), (2);
+CREATE TABLE t2 (b int(11));
+INSERT INTO t2 (b) VALUES (1), (2);
+CREATE VIEW v AS SELECT b as c FROM t2;
+SELECT a, (SELECT SUM(a + c) FROM v) FROM t1;
+a	(SELECT SUM(a + c) FROM v)
+1	5
+2	7
+SELECT a, (SELECT SUM(a + c) FROM (SELECT b as c FROM t2) AS v1) FROM t1;
+a	(SELECT SUM(a + c) FROM (SELECT b as c FROM t2) AS v1)
+1	5
+2	7
+DROP VIEW v;
+DROP TABLE t1,t2;
+#
+# mdev-5105: memory overwrite in multi-table update 
+#            using natuaral join with a view
+#
+create table t1(a int,b tinyint,c tinyint)engine=myisam;
+create table t2(a tinyint,b float,c int, d int, e int, f int, key (b), key(c), key(d), key(e), key(f))engine=myisam;
+create table t3(a int,b int,c int, d int, e int, f int, key(a), key(b), key(c), key(d), key(e), key(f))engine=myisam;
+create view v1 as select t2.b a, t1.b b, t2.c c, t2.d d, t2.e e, t2.f f from t1,t2 where t1.a=t2.a;
+update t3 natural join v1 set a:=1;
+drop view v1;
+drop table t1,t2,t3;
+#
 # end of 5.3 tests
 #
 set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index d23a6706e04..019099bde14 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -879,6 +879,116 @@ NULL
 7
 drop view v;
 drop table t1, t2;
+CREATE TABLE t1 (
+id int, i1 int, i2 int DEFAULT 0,
+d1 date DEFAULT '2000-01-01', d2 date DEFAULT '2000-01-01',
+t1 time DEFAULT '00:00:00', t2 time DEFAULT '00:00:00',
+dt1 datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
+dt2 datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
+c1 varchar(1) NOT NULL, c2 varchar(1) NOT NULL
+) ENGINE=MyISAM;
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 (id,i1,c1,c2) VALUES 
+(1,7,'t','t'),(2,4,'k','k'),(3,2,'e','e'),(4,0,'i','i'),(5,1,'t','t'),(6,91,'m','m'),
+(7,6,'z','z'),(8,3,'c','c'),(9,6,'i','i'),(10,8,'v','v'), (11,1,'l','l'),(12,4,'j','j'),
+(13,5,'w','w'),(14,0,'r','r'),(15,7,'o','o'),(16,9,'o','o'),(17,7,'u','u'),(18,6,'f','f'),
+(19,0,'l','l'),(20,6,'g','g'),(21,1,'e','e'),(22,7,'y','y'),(23,0,'p','p'),(24,6,'v','v'),
+(25,5,'d','d'),(26,9,'i','i'),(27,5,'z','z'),(28,2,'q','q'),(29,4,'j','j'),(30,9,'m','m'),
+(31,8,'d','d'),(32,5,'r','r'),(33,1,'r','r'),(34,1,'k','k'),(35,4,'p','p'),(36,2,'x','x'),
+(37,5,'w','w'),(38,0,'k','k'),(39,7,'y','y'),(40,4,'p','p'),(41,9,'l','l'),(42,2,'u','u'),
+(43,3,'r','r'),(44,5,'y','y'),(45,3,'u','u'),(46,9,'t','t'),(47,8,'f','f'),(48,2,'f','f'),
+(49,2,'q','q'),(50,6,'v','v'),(51,6,'u','u'),(52,0,'b','b'),(53,1,'n','n'),(54,2,'p','p'),
+(55,0,'y','y'),(56,1,'l','l'),(57,1,'c','c'),(58,0,'d','d'),(59,2,'y','y'),(60,7,'l','l'),
+(61,6,'m','m'),(62,9,'q','q'),(63,0,'j','j'),(64,3,'u','u'),(65,4,'w','w'),(66,5,'p','p'),
+(67,8,'z','z'),(68,5,'u','u'),(69,7,'b','b'),(70,0,'f','f'),(71,6,'u','u'),(72,1,'i','i'),
+(73,9,'s','s'),(74,3,'y','y'),(75,5,'s','s'),(76,8,'x','x'),(77,3,'s','s'),(78,3,'l','l'),
+(79,8,'b','b'),(80,0,'p','p'),(81,9,'m','m'),(82,5,'k','k'),(83,7,'u','u'),(84,0,'y','y'),
+(85,2,'x','x'),(86,5,'h','h'),(87,5,'j','j'),(88,5,'o','o'),(89,9,'o','o'),(90,1,'c','c'),
+(91,7,'k','k'),(92,9,'t','t'),(93,3,'h','h'),(94,6,'g','g'),(95,9,'r','r'),(96,2,'i','i');
+CREATE TABLE t2 (i INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (7),(8);
+SELECT STRAIGHT_JOIN COUNT(DISTINCT t1.id)  FROM
+t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
+COUNT(DISTINCT t1.id)
+18
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN DISTINCT t1.id  FROM
+t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	96	100.00	Using where; Using temporary
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.i1	9	100.00	Using where; Distinct
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Distinct; Using join buffer (flat, BNL join)
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	96	100.00	
+Warnings:
+Note	1003	select straight_join distinct `test`.`t1`.`id` AS `id` from `test`.`t1` join `test`.`v1` join `test`.`t2` where ((`test`.`t2`.`i` = `v1`.`id`) and (`v1`.`i1` = `test`.`t1`.`i1`) and (`v1`.`id` <> 3))
+set join_buffer_size=1024;
+SELECT STRAIGHT_JOIN DISTINCT t1.id  FROM
+t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
+id
+7
+8
+9
+18
+20
+24
+43
+45
+50
+51
+61
+64
+71
+74
+77
+78
+93
+94
+set join_buffer_size=1024*16;
+SELECT STRAIGHT_JOIN DISTINCT t1.id  FROM
+t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
+id
+7
+9
+18
+20
+24
+8
+50
+51
+61
+43
+45
+71
+64
+74
+77
+78
+94
+93
+set join_buffer_size=default;
+SELECT STRAIGHT_JOIN DISTINCT t1.id  FROM
+t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
+id
+7
+9
+18
+20
+24
+50
+51
+61
+71
+94
+8
+43
+45
+64
+74
+77
+78
+93
+DROP VIEW v1;
+DROP TABLE t1,t2;
 #
 # Bug #11744875: 4082: integer lengths cause truncation with distinct concat and innodb
 #
diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index 836de3d5842..ce233e0db23 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -373,3 +373,12 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT * FROM t1 WHERE NOT (concat( dt, '1' ) IS NOT NULL);
 dt
 DROP TABLE t1;
+#
+# Bug mdev-5132: crash when exeicuting a join query
+#                with IS NULL and IS NOT NULL in where
+#
+CREATE TABLE t1 (a DATE, b INT, c INT, KEY(a), KEY(b), KEY(c)) ENGINE=MyISAM;
+CREATE TABLE t2 (d DATE) ENGINE=MyISAM;
+SELECT  * FROM t1,t2 WHERE  1 IS NOT NULL AND t1.b IS NULL;
+a	b	c	d
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 4b7e76e11ca..8d1b3109d20 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -1546,6 +1546,39 @@ set optimizer_switch=@save_optimizer_switch;
 
 
 --echo #
+--echo # mdev-5078: sum over a view/derived table
+--echo #
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 (a) VALUES (1), (2);
+
+CREATE TABLE t2 (b int(11));
+INSERT INTO t2 (b) VALUES (1), (2);
+
+CREATE VIEW v AS SELECT b as c FROM t2;
+
+SELECT a, (SELECT SUM(a + c) FROM v) FROM t1;
+
+SELECT a, (SELECT SUM(a + c) FROM (SELECT b as c FROM t2) AS v1) FROM t1;
+
+DROP VIEW v;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # mdev-5105: memory overwrite in multi-table update 
+--echo #            using natuaral join with a view
+--echo #
+
+create table t1(a int,b tinyint,c tinyint)engine=myisam; 
+create table t2(a tinyint,b float,c int, d int, e int, f int, key (b), key(c), key(d), key(e), key(f))engine=myisam; 
+create table t3(a int,b int,c int, d int, e int, f int, key(a), key(b), key(c), key(d), key(e), key(f))engine=myisam; 
+create view v1 as select t2.b a, t1.b b, t2.c c, t2.d d, t2.e e, t2.f f from t1,t2 where t1.a=t2.a; 
+
+update t3 natural join v1 set a:=1; 
+drop view v1; 
+drop table t1,t2,t3;
+
+--echo #
 --echo # end of 5.3 tests
 --echo #
 
diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
index f1c120a313d..71643a25c5a 100644
--- a/mysql-test/t/distinct.test
+++ b/mysql-test/t/distinct.test
@@ -683,6 +683,64 @@ select distinct (select max(a) from t1 where alias.b = a) as field1 from t2 as a
 drop view v;
 drop table t1, t2;
 
+#
+# Bug mdev-5028: invalid distinct optimization when join buffer is used
+#
+
+CREATE TABLE t1 (
+  id int, i1 int, i2 int DEFAULT 0,
+  d1 date DEFAULT '2000-01-01', d2 date DEFAULT '2000-01-01',
+  t1 time DEFAULT '00:00:00', t2 time DEFAULT '00:00:00',
+  dt1 datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
+  dt2 datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
+  c1 varchar(1) NOT NULL, c2 varchar(1) NOT NULL
+) ENGINE=MyISAM;
+
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+
+INSERT INTO t1 (id,i1,c1,c2) VALUES 
+(1,7,'t','t'),(2,4,'k','k'),(3,2,'e','e'),(4,0,'i','i'),(5,1,'t','t'),(6,91,'m','m'),
+(7,6,'z','z'),(8,3,'c','c'),(9,6,'i','i'),(10,8,'v','v'), (11,1,'l','l'),(12,4,'j','j'),
+(13,5,'w','w'),(14,0,'r','r'),(15,7,'o','o'),(16,9,'o','o'),(17,7,'u','u'),(18,6,'f','f'),
+(19,0,'l','l'),(20,6,'g','g'),(21,1,'e','e'),(22,7,'y','y'),(23,0,'p','p'),(24,6,'v','v'),
+(25,5,'d','d'),(26,9,'i','i'),(27,5,'z','z'),(28,2,'q','q'),(29,4,'j','j'),(30,9,'m','m'),
+(31,8,'d','d'),(32,5,'r','r'),(33,1,'r','r'),(34,1,'k','k'),(35,4,'p','p'),(36,2,'x','x'),
+(37,5,'w','w'),(38,0,'k','k'),(39,7,'y','y'),(40,4,'p','p'),(41,9,'l','l'),(42,2,'u','u'),
+(43,3,'r','r'),(44,5,'y','y'),(45,3,'u','u'),(46,9,'t','t'),(47,8,'f','f'),(48,2,'f','f'),
+(49,2,'q','q'),(50,6,'v','v'),(51,6,'u','u'),(52,0,'b','b'),(53,1,'n','n'),(54,2,'p','p'),
+(55,0,'y','y'),(56,1,'l','l'),(57,1,'c','c'),(58,0,'d','d'),(59,2,'y','y'),(60,7,'l','l'),
+(61,6,'m','m'),(62,9,'q','q'),(63,0,'j','j'),(64,3,'u','u'),(65,4,'w','w'),(66,5,'p','p'),
+(67,8,'z','z'),(68,5,'u','u'),(69,7,'b','b'),(70,0,'f','f'),(71,6,'u','u'),(72,1,'i','i'),
+(73,9,'s','s'),(74,3,'y','y'),(75,5,'s','s'),(76,8,'x','x'),(77,3,'s','s'),(78,3,'l','l'),
+(79,8,'b','b'),(80,0,'p','p'),(81,9,'m','m'),(82,5,'k','k'),(83,7,'u','u'),(84,0,'y','y'),
+(85,2,'x','x'),(86,5,'h','h'),(87,5,'j','j'),(88,5,'o','o'),(89,9,'o','o'),(90,1,'c','c'),
+(91,7,'k','k'),(92,9,'t','t'),(93,3,'h','h'),(94,6,'g','g'),(95,9,'r','r'),(96,2,'i','i');
+
+CREATE TABLE t2 (i INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (7),(8);
+
+SELECT STRAIGHT_JOIN COUNT(DISTINCT t1.id)  FROM
+  t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
+
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN DISTINCT t1.id  FROM
+  t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
+
+set join_buffer_size=1024;
+SELECT STRAIGHT_JOIN DISTINCT t1.id  FROM
+  t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
+
+set join_buffer_size=1024*16;
+SELECT STRAIGHT_JOIN DISTINCT t1.id  FROM
+  t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
+
+set join_buffer_size=default;
+SELECT STRAIGHT_JOIN DISTINCT t1.id  FROM
+  t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
+
+DROP VIEW v1;
+DROP TABLE t1,t2;
+
 --echo #
 --echo # Bug #11744875: 4082: integer lengths cause truncation with distinct concat and innodb
 --echo #
diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test
index 4a45240ec68..81951a9ce68 100644
--- a/mysql-test/t/null.test
+++ b/mysql-test/t/null.test
@@ -283,3 +283,15 @@ SELECT * FROM t1 WHERE NOT (concat( dt, '1' ) IS NOT NULL);
 SELECT * FROM t1 WHERE NOT (concat( dt, '1' ) IS NOT NULL); 
 
 DROP TABLE t1;
+
+--echo #
+--echo # Bug mdev-5132: crash when exeicuting a join query
+--echo #                with IS NULL and IS NOT NULL in where
+--echo #
+
+CREATE TABLE t1 (a DATE, b INT, c INT, KEY(a), KEY(b), KEY(c)) ENGINE=MyISAM; 
+CREATE TABLE t2 (d DATE) ENGINE=MyISAM; 
+
+SELECT  * FROM t1,t2 WHERE  1 IS NOT NULL AND t1.b IS NULL;
+
+DROP TABLE t1,t2;
diff --git a/sql/item.cc b/sql/item.cc
index fa4c4eb99b0..26c75ccfd40 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -5114,6 +5114,11 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
         goto mark_non_agg_field;
     }
 
+    if (thd->lex->in_sum_func &&
+        thd->lex->in_sum_func->nest_level == 
+        thd->lex->current_select->nest_level)
+      set_if_bigger(thd->lex->in_sum_func->max_arg_level,
+                    thd->lex->current_select->nest_level);
     /*
       if it is not expression from merged VIEW we will set this field.
 
@@ -5130,11 +5135,6 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
       return FALSE;
 
     set_field(from_field);
-    if (thd->lex->in_sum_func &&
-        thd->lex->in_sum_func->nest_level == 
-        thd->lex->current_select->nest_level)
-      set_if_bigger(thd->lex->in_sum_func->max_arg_level,
-                    thd->lex->current_select->nest_level);
   }
   else if (thd->mark_used_columns != MARK_COLUMNS_NONE)
   {
diff --git a/sql/item.h b/sql/item.h
index 67e2b1dd48c..2a4a7537a66 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1192,6 +1192,7 @@ public:
   virtual bool view_used_tables_processor(uchar *arg) { return 0; }
   virtual bool eval_not_null_tables(uchar *opt_arg) { return 0; }
   virtual bool is_subquery_processor (uchar *opt_arg) { return 0; }
+  virtual bool count_sargable_conds(uchar *arg) { return 0; }
   virtual bool limit_index_condition_pushdown_processor(uchar *opt_arg)
   {
     return FALSE;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 2f1592a5f3d..6814e8a8012 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1435,6 +1435,7 @@ bool Item_in_optimizer::eval_not_null_tables(uchar *opt_arg)
   return FALSE;
 }
 
+
 bool Item_in_optimizer::fix_left(THD *thd)
 {
   if ((!args[0]->fixed && args[0]->fix_fields(thd, args)) ||
@@ -2205,6 +2206,15 @@ bool Item_func_between::eval_not_null_tables(uchar *opt_arg)
 }  
 
 
+bool Item_func_between::count_sargable_conds(uchar *arg)
+{
+  SELECT_LEX *sel= (SELECT_LEX *) arg;
+  sel->cond_count++;
+  sel->between_count++;
+  return 0;
+}
+
+
 void Item_func_between::fix_after_pullout(st_select_lex *new_parent, Item **ref)
 {
   /* This will re-calculate attributes of the arguments */
@@ -4802,6 +4812,7 @@ longlong Item_func_isnull::val_int()
   return args[0]->is_null() ? 1: 0;
 }
 
+
 longlong Item_is_not_null_test::val_int()
 {
   DBUG_ASSERT(fixed == 1);
@@ -5006,6 +5017,7 @@ bool Item_func_like::fix_fields(THD *thd, Item **ref)
   return FALSE;
 }
 
+
 void Item_func_like::cleanup()
 {
   canDoTurboBM= FALSE;
@@ -5696,7 +5708,8 @@ Item_equal::Item_equal(Item *f1, Item *f2, bool with_const_item)
   equal_items.push_back(f1);
   equal_items.push_back(f2);
   compare_as_dates= with_const_item && f2->cmp_type() == TIME_RESULT;
-  upper_levels= NULL;  
+  upper_levels= NULL;
+  sargable= TRUE; 
 }
 
 
@@ -5727,6 +5740,7 @@ Item_equal::Item_equal(Item_equal *item_equal)
   compare_as_dates= item_equal->compare_as_dates;
   cond_false= item_equal->cond_false;
   upper_levels= item_equal->upper_levels;
+  sargable= TRUE;
 }
 
 
@@ -6127,6 +6141,14 @@ void Item_equal::update_used_tables()
 }
 
 
+bool Item_equal::count_sargable_conds(uchar *arg)
+{
+  SELECT_LEX *sel= (SELECT_LEX *) arg;
+  uint m= equal_items.elements;
+  sel->cond_count+= m*(m-1);
+  return 0;
+}
+
 
 /**
   @brief
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 2d89b8d65db..50d1eb036ff 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -372,7 +372,8 @@ protected:
 
 public:
   Item_bool_func2(Item *a,Item *b)
-    :Item_int_func(a,b), cmp(tmp_arg, tmp_arg+1), abort_on_null(FALSE) {}
+    :Item_int_func(a,b), cmp(tmp_arg, tmp_arg+1),
+     abort_on_null(FALSE) { sargable= TRUE; }
   void fix_length_and_dec();
   int set_cmp_func()
   {
@@ -677,7 +678,7 @@ public:
   /* TRUE <=> arguments will be compared as dates. */
   Item *compare_as_dates;
   Item_func_between(Item *a, Item *b, Item *c)
-    :Item_func_opt_neg(a, b, c), compare_as_dates(FALSE) {}
+    :Item_func_opt_neg(a, b, c), compare_as_dates(FALSE) { sargable= TRUE; }
   longlong val_int();
   optimize_type select_optimize() const { return OPTIMIZE_KEY; }
   enum Functype functype() const   { return BETWEEN; }
@@ -690,6 +691,7 @@ public:
   uint decimal_precision() const { return 1; }
   bool eval_not_null_tables(uchar *opt_arg);
   void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  bool count_sargable_conds(uchar *arg);
 };
 
 
@@ -1295,10 +1297,11 @@ public:
 
   Item_func_in(List<Item> &list)
     :Item_func_opt_neg(list), array(0), have_null(0),
-    arg_types_compatible(FALSE)
+     arg_types_compatible(FALSE)
   {
     bzero(&cmp_items, sizeof(cmp_items));
     allowed_arg_cols= 0;  // Fetch this value from first argument
+    sargable= TRUE;
   }
   longlong val_int();
   bool fix_fields(THD *, Item **);
@@ -1364,7 +1367,7 @@ public:
 class Item_func_isnull :public Item_bool_func
 {
 public:
-  Item_func_isnull(Item *a) :Item_bool_func(a) {}
+  Item_func_isnull(Item *a) :Item_bool_func(a) { sargable= TRUE; }
   longlong val_int();
   enum Functype functype() const { return ISNULL_FUNC; }
   void fix_length_and_dec()
@@ -1426,7 +1429,8 @@ class Item_func_isnotnull :public Item_bool_func
 {
   bool abort_on_null;
 public:
-  Item_func_isnotnull(Item *a) :Item_bool_func(a), abort_on_null(0) {}
+  Item_func_isnotnull(Item *a) :Item_bool_func(a), abort_on_null(0)
+  { sargable= TRUE; }
   longlong val_int();
   enum Functype functype() const { return ISNOTNULL_FUNC; }
   void fix_length_and_dec()
@@ -1817,7 +1821,7 @@ public:
   inline Item_equal()
     : Item_bool_func(), with_const(FALSE), eval_item(0), cond_false(0),
       context_field(NULL)
-  { const_item_cache=0 ;}
+  { const_item_cache=0; sargable= TRUE; }
   Item_equal(Item *f1, Item *f2, bool with_const_item);
   Item_equal(Item_equal *item_equal);
   /* Currently the const item is always the first in the list of equal items */
@@ -1850,6 +1854,7 @@ public:
   void set_context_field(Item_field *ctx_field) { context_field= ctx_field; }
   void set_link_equal_fields(bool flag) { link_equal_fields= flag; }
   friend class Item_equal_fields_iterator;
+  bool count_sargable_conds(uchar *arg);
   friend class Item_equal_iterator<List_iterator_fast,Item>;
   friend class Item_equal_iterator<List_iterator,Item>;
   friend Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels,
diff --git a/sql/item_func.cc b/sql/item_func.cc
index f93feb0fa91..ef436ef8dc9 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -761,6 +761,16 @@ double Item_int_func::val_real()
   return unsigned_flag ? (double) ((ulonglong) val_int()) : (double) val_int();
 }
 
+bool Item_int_func::count_sargable_conds(uchar *arg)
+{
+  if (sargable)
+  {
+    SELECT_LEX *sel= (SELECT_LEX *) arg;
+    sel->cond_count++;
+  }
+  return 0;
+}
+
 
 String *Item_int_func::val_str(String *str)
 {
diff --git a/sql/item_func.h b/sql/item_func.h
index e236882b615..f4be9a14fe3 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -575,25 +575,28 @@ class Item_num_op :public Item_func_numhybrid
 
 class Item_int_func :public Item_func
 {
+protected:
+  bool sargable;
 public:
   Item_int_func() :Item_func()
-  { collation.set_numeric(); fix_char_length(21); }
+  { collation.set_numeric(); fix_char_length(21); sargable= false; }
   Item_int_func(Item *a) :Item_func(a)
-  { collation.set_numeric(); fix_char_length(21); }
+  { collation.set_numeric(); fix_char_length(21); sargable= false; }
   Item_int_func(Item *a,Item *b) :Item_func(a,b)
-  { collation.set_numeric(); fix_char_length(21); }
+  { collation.set_numeric(); fix_char_length(21); sargable= false; }
   Item_int_func(Item *a,Item *b,Item *c) :Item_func(a,b,c)
-  { collation.set_numeric(); fix_char_length(21); }
+  { collation.set_numeric(); fix_char_length(21); sargable= false; }
   Item_int_func(Item *a,Item *b,Item *c, Item *d) :Item_func(a,b,c,d)
-  { collation.set_numeric(); fix_char_length(21); }
+  { collation.set_numeric(); fix_char_length(21); sargable= false; }
   Item_int_func(List<Item> &list) :Item_func(list)
-  { collation.set_numeric(); fix_char_length(21); }
+  { collation.set_numeric(); fix_char_length(21); sargable= false; }
   Item_int_func(THD *thd, Item_int_func *item) :Item_func(thd, item)
-  { collation.set_numeric(); }
+  { collation.set_numeric(); sargable= false; }
   double val_real();
   String *val_str(String*str);
   enum Item_result result_type () const { return INT_RESULT; }
   void fix_length_and_dec() {}
+  bool count_sargable_conds(uchar *arg);
 };
 
 
diff --git a/sql/spatial.cc b/sql/spatial.cc
index 32a2012a49d..b82e6977f8a 100644
--- a/sql/spatial.cc
+++ b/sql/spatial.cc
@@ -480,7 +480,7 @@ bool Gis_point::init_from_wkt(Gis_read_stream *trs, String *wkb)
 {
   double x, y;
   if (trs->get_next_number(&x) || trs->get_next_number(&y) ||
-      wkb->reserve(POINT_DATA_SIZE))
+      wkb->reserve(POINT_DATA_SIZE, 512))
     return 1;
   wkb->q_append(x);
   wkb->q_append(y);
@@ -793,7 +793,7 @@ int Gis_line_string::store_shapes(Gcalc_shape_transporter *trn) const
     return 1;
   n_points= uint4korr(data);
   data+= 4;
-  if (n_points < 1 || no_data(data, POINT_DATA_SIZE * n_points))
+  if (n_points < 1 || not_enough_points(data, n_points))
     return 1;
 
   trn->start_line();
@@ -1230,7 +1230,7 @@ int Gis_polygon::store_shapes(Gcalc_shape_transporter *trn) const
       return 1;
     n_points= uint4korr(data);
     data+= 4;
-    if (!n_points || no_data(data, POINT_DATA_SIZE * n_points))
+    if (!n_points || not_enough_points(data, n_points))
       return 1;
 
     trn->start_ring();
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 0f986e67828..bfafb0d4d9b 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -8697,7 +8697,6 @@ int setup_conds(THD *thd, TABLE_LIST *tables, List<TABLE_LIST> &leaves,
              embedded->on_expr->fix_fields(thd, &embedded->on_expr)) ||
 	    embedded->on_expr->check_cols(1))
 	  goto err_no_arena;
-        select_lex->cond_count++;
       }
       /*
         If it's a semi-join nest, fix its "left expression", as it is used by
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 44cb270c884..bb55451e9dc 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -395,8 +395,6 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived)
     if (dt_select->options & OPTION_SCHEMA_TABLE)
       parent_lex->options |= OPTION_SCHEMA_TABLE;
 
-    parent_lex->cond_count+= dt_select->cond_count;
-
     if (!derived->get_unit()->prepared)
     {
       dt_select->leaf_tables.empty();
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index f2ca7fb1c20..fdc52cb454a 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2009,9 +2009,10 @@ int JOIN::init_execution()
       JOIN_TAB *last_join_tab= join_tab + top_join_tab_count - 1;
       do
       {
-	if (used_tables & last_join_tab->table->map)
+	if (used_tables & last_join_tab->table->map ||
+            last_join_tab->use_join_cache)
 	  break;
-	last_join_tab->not_used_in_distinct=1;
+	last_join_tab->shortcut_for_distinct= true;
       } while (last_join_tab-- != join_tab);
       /* Optimize "select distinct b from t1 order by key_part_1 limit #" */
       if (order && skip_sort_order)
@@ -5009,6 +5010,32 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
   KEY_FIELD *key_fields, *end, *field;
   uint sz;
   uint m= max(select_lex->max_equal_elems,1);
+
+  SELECT_LEX *sel=thd->lex->current_select; 
+  sel->cond_count= 0;
+  sel->between_count= 0; 
+  if (cond)
+    cond->walk(&Item::count_sargable_conds, 0, (uchar*) sel);
+  for (i=0 ; i < tables ; i++)
+  {
+    if (*join_tab[i].on_expr_ref)
+      (*join_tab[i].on_expr_ref)->walk(&Item::count_sargable_conds,
+                                       0, (uchar*) sel);
+  }
+  {
+    List_iterator<TABLE_LIST> li(*join_tab->join->join_list);
+    TABLE_LIST *table;
+    while ((table= li++))
+    {
+      if (table->nested_join)
+      {
+        if (table->on_expr)
+          table->on_expr->walk(&Item::count_sargable_conds, 0, (uchar*) sel);
+        if (table->sj_on_expr)
+          table->sj_on_expr->walk(&Item::count_sargable_conds, 0, (uchar*) sel);
+      }
+    }
+  }
   
   /* 
     We use the same piece of memory to store both  KEY_FIELD 
@@ -5032,8 +5059,7 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
     substitutions.
   */ 
   sz= max(sizeof(KEY_FIELD),sizeof(SARGABLE_PARAM))*
-      (((thd->lex->current_select->cond_count+1)*2 +
-	thd->lex->current_select->between_count)*m+1);
+    ((sel->cond_count*2 + sel->between_count)*m+1);
   if (!(key_fields=(KEY_FIELD*)	thd->alloc(sz)))
     return TRUE; /* purecov: inspected */
   and_level= 0;
@@ -8660,6 +8686,7 @@ JOIN::make_simple_join(JOIN *parent, TABLE *temp_table)
   join_tab->keys.init();
   join_tab->keys.set_all();                     /* test everything in quick */
   join_tab->ref.key = -1;
+  join_tab->shortcut_for_distinct= false;
   join_tab->read_first_record= join_init_read_record;
   join_tab->join= this;
   join_tab->ref.key_parts= 0;
@@ -12014,13 +12041,10 @@ static bool check_row_equality(THD *thd, Item *left_row, Item_row *right_row,
                                        (Item_row *) left_item,
                                        (Item_row *) right_item,
 			               cond_equal, eq_list);
-      if (!is_converted)
-        thd->lex->current_select->cond_count++;      
     }
     else
     { 
       is_converted= check_simple_equality(left_item, right_item, 0, cond_equal);
-      thd->lex->current_select->cond_count++;
     }  
  
     if (!is_converted)
@@ -12079,7 +12103,6 @@ static bool check_equality(THD *thd, Item *item, COND_EQUAL *cond_equal,
     if (left_item->type() == Item::ROW_ITEM &&
         right_item->type() == Item::ROW_ITEM)
     {
-      thd->lex->current_select->cond_count--;
       return check_row_equality(thd,
                                 (Item_row *) left_item,
                                 (Item_row *) right_item,
@@ -17208,7 +17231,7 @@ static enum_nested_loop_state
 evaluate_join_record(JOIN *join, JOIN_TAB *join_tab,
                      int error)
 {
-  bool not_used_in_distinct=join_tab->not_used_in_distinct;
+  bool shortcut_for_distinct= join_tab->shortcut_for_distinct;
   ha_rows found_records=join->found_records;
   COND *select_cond= join_tab->select_cond;
   bool select_cond_result= TRUE;
@@ -17373,7 +17396,7 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab,
         was not in the field list;  In this case we can abort if
         we found a row, as no new rows can be added to the result.
       */
-      if (not_used_in_distinct && found_records != join->found_records)
+      if (shortcut_for_distinct && found_records != join->found_records)
         DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS);
     }
     else
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 686f159fabf..e52e321650d 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -319,8 +319,9 @@ typedef struct st_join_table {
   uint          used_null_fields;
   uint          used_uneven_bit_fields;
   enum join_type type;
-  bool		cached_eq_ref_table,eq_ref_table,not_used_in_distinct;
-  bool		sorted;
+  bool          cached_eq_ref_table,eq_ref_table;
+  bool          shortcut_for_distinct;
+  bool          sorted;
   /* 
     If it's not 0 the number stored this field indicates that the index
     scan has been chosen to access the table data and we expect to scan