MySQL Lists: commits: bk commit into 5.1 tree (gkodinov:1.2375)

List:	Commits	« Previous Message Next Message »
From:	gkodinov	Date:	November 29 2006 11:48am
Subject:	bk commit into 5.1 tree (gkodinov:1.2375)
View as plain text
Below is the list of changes that have just been committed into a local
5.1 repository of gkodinov. When gkodinov does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-11-29 11:48:44+01:00, gkodinov@stripped +15 -0
  Merge dl145s.mysql.com:/data0/bk/team_tree_merge/mysql-5.0-opt
  into  dl145s.mysql.com:/data0/bk/team_tree_merge/MERGE2/mysql-5.1-opt
  MERGE: 1.1810.1698.196

  mysql-test/r/func_gconcat.result@stripped, 2006-11-29 11:46:25+01:00,
gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.52.1.12

  mysql-test/r/func_group.result@stripped, 2006-11-29 11:46:25+01:00,
gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.49.1.6

  mysql-test/r/group_min_max.result@stripped, 2006-11-29 11:46:26+01:00,
gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.23.1.5

  mysql-test/r/type_varchar.result@stripped, 2006-11-29 11:46:26+01:00,
gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.8.1.2

  mysql-test/t/group_min_max.test@stripped, 2006-11-29 11:46:26+01:00,
gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.21.3.1

  mysql-test/t/type_varchar.test@stripped, 2006-11-29 11:46:26+01:00,
gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.8.1.2

  sql/field.cc@stripped, 2006-11-29 11:48:42+01:00, gkodinov@stripped +0 -0
    SCCS merged
    MERGE: 1.256.1.76

  sql/item.cc@stripped, 2006-11-29 11:46:26+01:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.113.1.130

  sql/item.h@stripped, 2006-11-29 11:46:26+01:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.183.1.30

  sql/item_func.h@stripped, 2006-11-29 11:46:26+01:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.136.2.23

  sql/item_subselect.h@stripped, 2006-11-29 11:46:26+01:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.77.1.6

  sql/item_sum.cc@stripped, 2006-11-29 11:46:26+01:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.170.1.20

  sql/item_sum.h@stripped, 2006-11-29 11:46:26+01:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.102.1.5

  sql/opt_range.cc@stripped, 2006-11-29 11:46:26+01:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.159.30.1

  sql/sql_string.h@stripped, 2006-11-29 11:46:26+01:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.60.1.3

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gkodinov
# Host:	dl145s.mysql.com
# Root:	/data0/bk/team_tree_merge/MERGE2/mysql-5.1-opt/RESYNC

--- 1.355/sql/field.cc	2006-11-28 17:14:05 +01:00
+++ 1.356/sql/field.cc	2006-11-29 11:48:42 +01:00
@@ -47,6 +47,8 @@ uchar Field_null::null[1]={1};
 const char field_separator=',';
 
 #define DOUBLE_TO_STRING_CONVERSION_BUFFER_SIZE 320
+#define LONGLONG_TO_STRING_CONVERSION_BUFFER_SIZE 128
+#define DECIMAL_TO_STRING_CONVERSION_BUFFER_SIZE 128
 #define BLOB_PACK_LENGTH_TO_MAX_LENGH(arg) \
 ((ulong) ((LL(1) << min(arg, 4) * 8) - LL(1)))
 
@@ -6211,20 +6213,50 @@ int Field_longstr::store_decimal(const m
 double Field_string::val_real(void)
 {
   ASSERT_COLUMN_MARKED_FOR_READ;
-  int not_used;
-  char *end_not_used;
+  int error;
+  char *end;
   CHARSET_INFO *cs= charset();
-  return my_strntod(cs,ptr,field_length,&end_not_used,&not_used);
+  double result;
+  
+  result=  my_strntod(cs,ptr,field_length,&end,&error);
+  if (!table->in_use->no_errors &&
+      (error || (field_length != (uint32)(end - ptr) && 
+                !check_if_only_end_space(cs, end, ptr + field_length))))
+  {
+    char buf[DOUBLE_TO_STRING_CONVERSION_BUFFER_SIZE];
+    String tmp(buf, sizeof(buf), cs);
+    tmp.copy(ptr, field_length, cs);
+    push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+                        ER_TRUNCATED_WRONG_VALUE, 
+                        ER(ER_TRUNCATED_WRONG_VALUE),
+                        "DOUBLE", tmp.c_ptr());
+  }
+  return result;
 }
 
 
 longlong Field_string::val_int(void)
 {
   ASSERT_COLUMN_MARKED_FOR_READ;
-  int not_used;
-  char *end_not_used;
-  CHARSET_INFO *cs=charset();
-  return my_strntoll(cs,ptr,field_length,10,&end_not_used,&not_used);
+  int error;
+  char *end;
+  CHARSET_INFO *cs= charset();
+  longlong result;
+
+  result= my_strntoll(cs,ptr,field_length,10,&end,&error);
+  if (!table->in_use->no_errors &&
+      (error || (field_length != (uint32)(end - ptr) && 
+                !check_if_only_end_space(cs, end, ptr + field_length))))
+  {
+    char buf[LONGLONG_TO_STRING_CONVERSION_BUFFER_SIZE];
+    String tmp(buf, sizeof(buf), cs);
+    tmp.copy(ptr, field_length, cs);
+    push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+                        ER_TRUNCATED_WRONG_VALUE, 
+                        ER(ER_TRUNCATED_WRONG_VALUE),
+                        "INTEGER", tmp.c_ptr());
+  }
+  return result;
 }
 
 
@@ -6243,8 +6275,20 @@ String *Field_string::val_str(String *va
 my_decimal *Field_string::val_decimal(my_decimal *decimal_value)
 {
   ASSERT_COLUMN_MARKED_FOR_READ;
-  str2my_decimal(E_DEC_FATAL_ERROR, ptr, field_length, charset(),
+  int err= str2my_decimal(E_DEC_FATAL_ERROR, ptr, field_length, charset(),
                  decimal_value);
+  if (!table->in_use->no_errors && err)
+  {
+    char buf[DECIMAL_TO_STRING_CONVERSION_BUFFER_SIZE];
+    CHARSET_INFO *cs= charset();
+    String tmp(buf, sizeof(buf), cs);
+    tmp.copy(ptr, field_length, cs);
+    push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+                        ER_TRUNCATED_WRONG_VALUE, 
+                        ER(ER_TRUNCATED_WRONG_VALUE),
+                        "DECIMAL", tmp.c_ptr());
+  }
+
   return decimal_value;
 }
 

--- 1.220/sql/item.cc	2006-11-19 19:19:50 +01:00
+++ 1.221/sql/item.cc	2006-11-29 11:46:26 +01:00
@@ -2208,12 +2208,6 @@ void Item_string::print(String *str)
 }
 
 
-inline bool check_if_only_end_space(CHARSET_INFO *cs, char *str, char *end)
-{
-  return str+ cs->cset->scan(cs, str, end, MY_SEQ_SPACES) == end;
-}
-
-
 double Item_string::val_real()
 {
   DBUG_ASSERT(fixed == 1);
@@ -4850,6 +4844,22 @@ bool Item_field::send(Protocol *protocol
 }
 
 
+void Item_field::update_null_value() 
+{ 
+  /* 
+    need to set no_errors to prevent warnings about type conversion 
+    popping up.
+  */
+  THD *thd= field->table->in_use;
+  int no_errors;
+
+  no_errors= thd->no_errors;
+  thd->no_errors= 1;
+  Item::update_null_value();
+  thd->no_errors= no_errors;
+}
+
+
 Item_ref::Item_ref(Name_resolution_context *context_arg,
                    Item **item, const char *table_name_arg,
                    const char *field_name_arg)
@@ -6208,7 +6218,7 @@ bool Item_cache_row::null_inside()
     }
     else
     {
-      values[i]->val_int();
+      values[i]->update_null_value();
       if (values[i]->null_value)
 	return 1;
     }

--- 1.215/sql/item.h	2006-11-19 19:19:50 +01:00
+++ 1.216/sql/item.h	2006-11-29 11:46:26 +01:00
@@ -745,6 +745,11 @@ public:
   virtual bool is_null() { return 0; }
 
   /*
+   Make sure the null_value member has a correct value.
+  */
+  virtual void update_null_value () { (void) val_int(); }
+
+  /*
     Inform the item that there will be no distinction between its result
     being FALSE or NULL.
 
@@ -1369,6 +1374,7 @@ public:
   bool get_date_result(TIME *ltime,uint fuzzydate);
   bool get_time(TIME *ltime);
   bool is_null() { return field->is_null(); }
+  void update_null_value();
   Item *get_tmp_table_item(THD *thd);
   bool collect_item_field_processor(byte * arg);
   bool find_item_in_field_list_processor(byte *arg);

--- 1.154/sql/item_func.h	2006-11-19 19:19:50 +01:00
+++ 1.155/sql/item_func.h	2006-11-29 11:46:26 +01:00
@@ -157,7 +157,7 @@ public:
     return (null_value=args[0]->get_time(ltime));
   }
   bool is_null() { 
-    (void) val_int();  /* Discard result. It sets null_value as side-effect. */ 
+    update_null_value();
     return null_value; 
   }
   void signal_divide_by_null();
@@ -241,7 +241,7 @@ public:
   virtual double real_op()= 0;
   virtual my_decimal *decimal_op(my_decimal *)= 0;
   virtual String *str_op(String *)= 0;
-  bool is_null() { (void) val_real(); return null_value; }
+  bool is_null() { update_null_value(); return null_value; }
 };
 
 /* function where type of result detected by first argument */

--- 1.201/sql/item_sum.cc	2006-11-28 17:14:09 +01:00
+++ 1.202/sql/item_sum.cc	2006-11-29 11:46:26 +01:00
@@ -1060,7 +1060,7 @@ bool Item_sum_count::add()
     count++;
   else
   {
-    (void) args[0]->val_int();
+    args[0]->update_null_value();
     if (!args[0]->null_value)
       count++;
   }
@@ -1976,7 +1976,7 @@ void Item_sum_count::reset_field()
     nr=1;
   else
   {
-    (void) args[0]->val_int();
+    args[0]->update_null_value();
     if (!args[0]->null_value)
       nr=1;
   }
@@ -2086,7 +2086,7 @@ void Item_sum_count::update_field()
     nr++;
   else
   {
-    (void) args[0]->val_int();
+    args[0]->update_null_value();
     if (!args[0]->null_value)
       nr++;
   }
@@ -2566,7 +2566,7 @@ bool Item_sum_count_distinct::setup(THD 
       return TRUE;                              // End of memory
     if (item->const_item())
     {
-      (void) item->val_int();
+      item->update_null_value();
       if (item->null_value)
 	always_null=1;
     }

--- 1.108/sql/item_sum.h	2006-11-19 19:19:51 +01:00
+++ 1.109/sql/item_sum.h	2006-11-29 11:46:26 +01:00
@@ -618,7 +618,7 @@ public:
   double val_real();
   longlong val_int();
   my_decimal *val_decimal(my_decimal *);
-  bool is_null() { (void) val_int(); return null_value; }
+  bool is_null() { update_null_value(); return null_value; }
   String *val_str(String*);
   enum_field_types field_type() const
   {
@@ -685,7 +685,7 @@ public:
   { /* can't be fix_fields()ed */ return (longlong) rint(val_real()); }
   String *val_str(String*);
   my_decimal *val_decimal(my_decimal *);
-  bool is_null() { (void) val_int(); return null_value; }
+  bool is_null() { update_null_value(); return null_value; }
   enum_field_types field_type() const
   {
     return hybrid_type == DECIMAL_RESULT ?

--- 1.248/sql/opt_range.cc	2006-11-28 17:14:10 +01:00
+++ 1.249/sql/opt_range.cc	2006-11-29 11:46:26 +01:00
@@ -8874,7 +8874,7 @@ static TRP_GROUP_MIN_MAX *
 get_best_group_min_max(PARAM *param, SEL_TREE *tree)
 {
   THD *thd= param->thd;
-  JOIN *join= thd->lex->select_lex.join;
+  JOIN *join= thd->lex->current_select->join;
   TABLE *table= param->table;
   bool have_min= FALSE;              /* TRUE if there is a MIN function. */
   bool have_max= FALSE;              /* TRUE if there is a MAX function. */
@@ -8895,7 +8895,7 @@ get_best_group_min_max(PARAM *param, SEL
   DBUG_ENTER("get_best_group_min_max");
 
   /* Perform few 'cheap' tests whether this access method is applicable. */
-  if (!join || (thd->lex->sql_command != SQLCOM_SELECT))
+  if (!join)
     DBUG_RETURN(NULL);        /* This is not a select statement. */
   if ((join->tables != 1) ||  /* The query must reference one table. */
       ((!join->group_list) && /* Neither GROUP BY nor a DISTINCT query. */
@@ -9736,7 +9736,7 @@ TRP_GROUP_MIN_MAX::make_quick(PARAM *par
   DBUG_ENTER("TRP_GROUP_MIN_MAX::make_quick");
 
   quick= new QUICK_GROUP_MIN_MAX_SELECT(param->table,
-                                        param->thd->lex->select_lex.join,
+                                       
param->thd->lex->current_select->join,
                                         have_min, have_max, min_max_arg_part,
                                         group_prefix_len, used_key_parts,
                                         index_info, index, read_cost, records,

--- 1.65/sql/sql_string.h	2006-11-01 13:44:42 +01:00
+++ 1.66/sql/sql_string.h	2006-11-29 11:46:26 +01:00
@@ -364,3 +364,9 @@ public:
     return (s->alloced && Ptr >= s->Ptr && Ptr < s->Ptr +
s->str_length);
   }
 };
+
+static inline bool check_if_only_end_space(CHARSET_INFO *cs, char *str, 
+                                           char *end)
+{
+  return str+ cs->cset->scan(cs, str, end, MY_SEQ_SPACES) == end;
+}

--- 1.82/sql/item_subselect.h	2006-11-19 19:19:50 +01:00
+++ 1.83/sql/item_subselect.h	2006-11-29 11:46:26 +01:00
@@ -91,7 +91,7 @@ public:
   enum Type type() const;
   bool is_null()
   {
-    val_int();
+    update_null_value();
     return null_value;
   }
   bool fix_fields(THD *thd, Item **ref);

--- 1.55/mysql-test/r/func_group.result	2006-11-08 15:51:40 +01:00
+++ 1.56/mysql-test/r/func_group.result	2006-11-29 11:46:25 +01:00
@@ -62,6 +62,13 @@ NULL	NULL
 1	7
 2	20.25
 3	45.483163247594
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a         '
+Warning	1292	Truncated incorrect DOUBLE value: 'a         '
+Warning	1292	Truncated incorrect DOUBLE value: 'b         '
+Warning	1292	Truncated incorrect DOUBLE value: 'c         '
+Warning	1292	Truncated incorrect DOUBLE value: 'C         '
+Warning	1292	Truncated incorrect DOUBLE value: 'E         '
 create table t2 (grp int, a bigint unsigned, c char(10));
 insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp;
 replace into t2 select grp, a, c from t1 limit 2,1;

--- 1.11/mysql-test/r/type_varchar.result	2006-08-30 00:36:46 +02:00
+++ 1.12/mysql-test/r/type_varchar.result	2006-11-29 11:46:26 +01:00
@@ -453,3 +453,38 @@ id	name_id	id	en	cz
 2	3	2	en string 2	cz string 2
 3	3	3	en string 3	cz string 3
 drop table t1, t2, t3;
+CREATE TABLE t1 (a CHAR(2));
+INSERT INTO t1 VALUES (10), (50), (30), ('1a'), (60), ('t');
+SELECT a,(a + 0) FROM t1 ORDER BY a;
+a	(a + 0)
+10	10
+1a	1
+30	30
+50	50
+60	60
+t	0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: '1a'
+Warning	1292	Truncated incorrect DOUBLE value: 't '
+SELECT a,(a DIV 2) FROM t1 ORDER BY a;
+a	(a DIV 2)
+10	5
+1a	0
+30	15
+50	25
+60	30
+t	0
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: '1a'
+Warning	1292	Truncated incorrect INTEGER value: 't '
+SELECT a,CAST(a AS SIGNED) FROM t1 ORDER BY a;
+a	CAST(a AS SIGNED)
+10	10
+1a	1
+30	30
+50	50
+60	60
+t	0
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: '1a'
+Warning	1292	Truncated incorrect INTEGER value: 't'

--- 1.10/mysql-test/t/type_varchar.test	2006-08-30 00:36:48 +02:00
+++ 1.11/mysql-test/t/type_varchar.test	2006-11-29 11:46:26 +01:00
@@ -187,3 +187,12 @@ left join t3 on t1.id=t3.id order by t3.
 --disable_metadata
 --enable_ps_protocol
 drop table t1, t2, t3;
+
+#
+# Bug #11927: Warnings shown for CAST( chr as signed) but not (chr + 0)
+#
+CREATE TABLE t1 (a CHAR(2));
+INSERT INTO t1 VALUES (10), (50), (30), ('1a'), (60), ('t');
+SELECT a,(a + 0) FROM t1 ORDER BY a;
+SELECT a,(a DIV 2) FROM t1 ORDER BY a;
+SELECT a,CAST(a AS SIGNED) FROM t1 ORDER BY a;

--- 1.28/mysql-test/r/group_min_max.result	2006-10-19 16:01:34 +02:00
+++ 1.29/mysql-test/r/group_min_max.result	2006-11-29 11:46:26 +01:00
@@ -2182,3 +2182,127 @@ SELECT MIN(c) FROM t2 WHERE b = 2 and a 
 MIN(c)
 2
 DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
+INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
+(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
+EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	NULL	a	5	NULL	8	Using index for group-by
+FLUSH STATUS;
+SELECT max(b), a FROM t1 GROUP BY a;
+max(b)	a
+5	1
+3	2
+1	3
+6	4
+SHOW STATUS LIKE 'handler_read__e%';
+Variable_name	Value
+Handler_read_key	8
+Handler_read_next	0
+EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	NULL	a	5	NULL	8	Using index for group-by
+FLUSH STATUS;
+CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a;
+SHOW STATUS LIKE 'handler_read__e%';
+Variable_name	Value
+Handler_read_key	8
+Handler_read_next	0
+FLUSH STATUS;
+SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
+max(b)	a
+5	1
+3	2
+1	3
+6	4
+SHOW STATUS LIKE 'handler_read__e%';
+Variable_name	Value
+Handler_read_key	8
+Handler_read_next	0
+FLUSH STATUS;
+(SELECT max(b), a FROM t1 GROUP BY a) UNION 
+(SELECT max(b), a FROM t1 GROUP BY a);
+max(b)	a
+5	1
+3	2
+1	3
+6	4
+SHOW STATUS LIKE 'handler_read__e%';
+Variable_name	Value
+Handler_read_key	16
+Handler_read_next	0
+EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION 
+(SELECT max(b), a FROM t1 GROUP BY a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	NULL	a	5	NULL	8	Using index for group-by
+2	UNION	t1	range	NULL	a	5	NULL	8	Using index for group-by
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
+FROM t1 AS t1_outer;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_outer	index	NULL	a	10	NULL	15	Using index
+2	SUBQUERY	t1	range	NULL	a	5	NULL	8	Using index for group-by
+EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS 
+(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_outer	index	NULL	a	10	NULL	15	Using index
+2	SUBQUERY	t1	index	NULL	a	10	NULL	8	Using index
+EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
+(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	range	NULL	a	5	NULL	8	Using index for group-by
+EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
+a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_outer	index	NULL	a	10	NULL	15	Using where; Using index
+2	DEPENDENT SUBQUERY	t1	index	NULL	a	10	NULL	8	Using index
+EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING 
+a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_outer	range	NULL	a	5	NULL	8	Using index for group-by
+2	SUBQUERY	t1	range	NULL	a	5	NULL	8	Using index for group-by
+EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 
+ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) 
+AND t1_outer1.b = t1_outer2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_outer1	ref	a	a	5	const	1	Using where; Using index
+1	PRIMARY	t1_outer2	index	NULL	a	10	NULL	15	Using where; Using index
+2	SUBQUERY	t1	range	NULL	a	5	NULL	8	Using index for group-by
+EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
+FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_outer2	index	NULL	a	10	NULL	15	Using index
+2	SUBQUERY	t1_outer	index	NULL	a	10	NULL	15	Using index
+3	SUBQUERY	t1	range	NULL	a	5	NULL	8	Using index for group-by
+CREATE TABLE t3 LIKE t1;
+FLUSH STATUS;
+INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;
+SHOW STATUS LIKE 'handler_read__e%';
+Variable_name	Value
+Handler_read_key	8
+Handler_read_next	0
+DELETE FROM t3;
+FLUSH STATUS;
+INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) 
+FROM t1 LIMIT 1;
+SHOW STATUS LIKE 'handler_read__e%';
+Variable_name	Value
+Handler_read_key	8
+Handler_read_next	0
+FLUSH STATUS;
+DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
+SHOW STATUS LIKE 'handler_read__e%';
+Variable_name	Value
+Handler_read_key	8
+Handler_read_next	0
+FLUSH STATUS;
+DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x 
+FROM t1) > 10000;
+Warnings:
+Error	1242	Subquery returns more than 1 row
+SHOW STATUS LIKE 'handler_read__e%';
+Variable_name	Value
+Handler_read_key	8
+Handler_read_next	1
+DROP TABLE t1,t2,t3;

--- 1.29/mysql-test/t/group_min_max.test	2006-11-21 22:15:29 +01:00
+++ 1.30/mysql-test/t/group_min_max.test	2006-11-29 11:46:26 +01:00
@@ -835,3 +835,63 @@ explain SELECT MIN(c) FROM t2 WHERE b = 
 SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
 
 DROP TABLE t1,t2;
+
+#
+# Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar statements
+#
+
+CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
+INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
+       (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
+EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
+FLUSH STATUS;
+SELECT max(b), a FROM t1 GROUP BY a;
+SHOW STATUS LIKE 'handler_read__e%';
+EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
+FLUSH STATUS;
+CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a;
+SHOW STATUS LIKE 'handler_read__e%';
+FLUSH STATUS;
+SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
+SHOW STATUS LIKE 'handler_read__e%';
+FLUSH STATUS;
+(SELECT max(b), a FROM t1 GROUP BY a) UNION 
+ (SELECT max(b), a FROM t1 GROUP BY a);
+SHOW STATUS LIKE 'handler_read__e%';
+EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION 
+ (SELECT max(b), a FROM t1 GROUP BY a);
+
+EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
+  FROM t1 AS t1_outer;
+EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS 
+  (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
+  (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
+EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
+  a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING 
+  a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
+EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 
+   ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) 
+   AND t1_outer1.b = t1_outer2.b;
+EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
+  FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
+
+CREATE TABLE t3 LIKE t1;
+FLUSH STATUS;
+INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;
+SHOW STATUS LIKE 'handler_read__e%';
+DELETE FROM t3;
+FLUSH STATUS;
+INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) 
+  FROM t1 LIMIT 1;
+SHOW STATUS LIKE 'handler_read__e%';
+FLUSH STATUS;
+DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
+SHOW STATUS LIKE 'handler_read__e%';
+FLUSH STATUS;
+DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x 
+                      FROM t1) > 10000;
+SHOW STATUS LIKE 'handler_read__e%';
+
+DROP TABLE t1,t2,t3;

--- 1.64/mysql-test/r/func_gconcat.result	2006-11-08 19:14:29 +01:00
+++ 1.65/mysql-test/r/func_gconcat.result	2006-11-29 11:46:25 +01:00
@@ -64,11 +64,49 @@ grp	group_concat(a order by a,d+c-ascii(
 1	1
 2	2,3
 3	4,5,6,7,8,9
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a         '
+Warning	1292	Truncated incorrect DOUBLE value: 'a         '
+Warning	1292	Truncated incorrect DOUBLE value: 'a         '
+Warning	1292	Truncated incorrect DOUBLE value: 'b         '
+Warning	1292	Truncated incorrect DOUBLE value: 'b         '
+Warning	1292	Truncated incorrect DOUBLE value: 'c         '
+Warning	1292	Truncated incorrect DOUBLE value: 'a         '
+Warning	1292	Truncated incorrect DOUBLE value: 'E         '
+Warning	1292	Truncated incorrect DOUBLE value: 'b         '
+Warning	1292	Truncated incorrect DOUBLE value: 'C         '
+Warning	1292	Truncated incorrect DOUBLE value: 'b         '
+Warning	1292	Truncated incorrect DOUBLE value: 'D         '
+Warning	1292	Truncated incorrect DOUBLE value: 'd         '
+Warning	1292	Truncated incorrect DOUBLE value: 'd         '
+Warning	1292	Truncated incorrect DOUBLE value: 'd         '
+Warning	1292	Truncated incorrect DOUBLE value: 'd         '
+Warning	1292	Truncated incorrect DOUBLE value: 'c         '
+Warning	1292	Truncated incorrect DOUBLE value: 'D         '
 select grp,group_concat(a order by d+c-ascii(c),a) from t1 group by grp;
 grp	group_concat(a order by d+c-ascii(c),a)
 1	1
 2	3,2
 3	7,8,4,6,9,5
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a         '
+Warning	1292	Truncated incorrect DOUBLE value: 'a         '
+Warning	1292	Truncated incorrect DOUBLE value: 'a         '
+Warning	1292	Truncated incorrect DOUBLE value: 'b         '
+Warning	1292	Truncated incorrect DOUBLE value: 'b         '
+Warning	1292	Truncated incorrect DOUBLE value: 'c         '
+Warning	1292	Truncated incorrect DOUBLE value: 'a         '
+Warning	1292	Truncated incorrect DOUBLE value: 'E         '
+Warning	1292	Truncated incorrect DOUBLE value: 'b         '
+Warning	1292	Truncated incorrect DOUBLE value: 'C         '
+Warning	1292	Truncated incorrect DOUBLE value: 'b         '
+Warning	1292	Truncated incorrect DOUBLE value: 'D         '
+Warning	1292	Truncated incorrect DOUBLE value: 'd         '
+Warning	1292	Truncated incorrect DOUBLE value: 'd         '
+Warning	1292	Truncated incorrect DOUBLE value: 'd         '
+Warning	1292	Truncated incorrect DOUBLE value: 'd         '
+Warning	1292	Truncated incorrect DOUBLE value: 'c         '
+Warning	1292	Truncated incorrect DOUBLE value: 'D         '
 select grp,group_concat(c order by 1) from t1 group by grp;
 grp	group_concat(c order by 1)
 1	a
Thread
• bk commit into 5.1 tree (gkodinov:1.2375)	gkodinov	29 Nov