summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--Docs/manual.texi3
-rw-r--r--include/my_base.h2
-rw-r--r--include/mysql_com.h3
-rw-r--r--myisam/mi_search.c5
-rw-r--r--myisam/mi_write.c4
-rw-r--r--mysql-test/r/group_by.result16
-rw-r--r--mysql-test/t/group_by.test11
-rw-r--r--sql/sql_base.cc18
-rw-r--r--sql/sql_select.cc48
9 files changed, 83 insertions, 27 deletions
diff --git a/Docs/manual.texi b/Docs/manual.texi
index 5c871944d94..0d7604b436b 100644
--- a/Docs/manual.texi
+++ b/Docs/manual.texi
@@ -46928,6 +46928,9 @@ not yet 100% confident in this code.
@appendixsubsec Changes in release 3.23.52
@itemize @bullet
@item
+Fixed problem with @code{GROUP BY} on result with expression that created a
+@code{BLOB} field.
+@item
Fixed problem with privilege tables when downgrading from 4.0.2 to 3.23.
@item
Fixed thread bug in @code{SLAVE START} and @code{SLAVE STOP}.
diff --git a/include/my_base.h b/include/my_base.h
index 6275adab124..dff1553cf20 100644
--- a/include/my_base.h
+++ b/include/my_base.h
@@ -134,6 +134,7 @@ enum ha_base_keytype {
#define HA_BINARY_PACK_KEY 32 /* Packing of all keys to prev key */
#define HA_FULLTEXT 128 /* SerG: for full-text search */
#define HA_UNIQUE_CHECK 256 /* Check the key for uniqueness */
+#define HA_NULL_ARE_EQUAL 2048 /* NULL in key are cmp as equal */
/* Automatic bits in key-flag */
@@ -235,6 +236,7 @@ enum ha_base_keytype {
#define SEARCH_UPDATE 64
#define SEARCH_PREFIX 128
#define SEARCH_LAST 256
+#define SEARCH_NULL_ARE_EQUAL 32768 /* NULL in keys are equal */
/* bits in opt_flag */
#define QUICK_USED 1
diff --git a/include/mysql_com.h b/include/mysql_com.h
index 525a631caf6..fcc9abc5bcd 100644
--- a/include/mysql_com.h
+++ b/include/mysql_com.h
@@ -110,6 +110,9 @@ typedef struct st_vio Vio;
#endif
#endif
+#define MAX_CHAR_WIDTH 255 /* Max length for a CHAR colum */
+#define MAX_BLOB_WIDTH 8192 /* Default width for blob */
+
typedef struct st_net {
Vio* vio;
my_socket fd; /* For Perl DBI/dbd */
diff --git a/myisam/mi_search.c b/myisam/mi_search.c
index 2c04679ed4c..938062d977d 100644
--- a/myisam/mi_search.c
+++ b/myisam/mi_search.c
@@ -107,8 +107,9 @@ int _mi_search(register MI_INFO *info, register MI_KEYDEF *keyinfo,
}
else
{
- if (nextflag & SEARCH_FIND && (!(keyinfo->flag & HA_NOSAME)
- || key_len) && nod_flag)
+ if ((nextflag & SEARCH_FIND) && nod_flag &&
+ ((keyinfo->flag & (HA_NOSAME | HA_NULL_PART)) != HA_NOSAME ||
+ key_len))
{
if ((error=_mi_search(info,keyinfo,key,key_len,SEARCH_FIND,
_mi_kpos(nod_flag,keypos))) >= 0 ||
diff --git a/myisam/mi_write.c b/myisam/mi_write.c
index f31e43e52ab..7b468395166 100644
--- a/myisam/mi_write.c
+++ b/myisam/mi_write.c
@@ -263,7 +263,11 @@ static int w_search(register MI_INFO *info, register MI_KEYDEF *keyinfo,
if (keyinfo->flag & HA_SORT_ALLOWS_SAME)
comp_flag=SEARCH_BIGGER; /* Put after same key */
else if (keyinfo->flag & HA_NOSAME)
+ {
comp_flag=SEARCH_FIND | SEARCH_UPDATE; /* No dupplicates */
+ if (keyinfo->flag & HA_NULL_ARE_EQUAL)
+ comp_flag|= SEARCH_NULL_ARE_EQUAL;
+ }
else
comp_flag=SEARCH_SAME; /* Keys in rec-pos order */
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index aafeb64ee2a..200cbc29b08 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -68,3 +68,19 @@ One Two sum(Four)
1 1 16
1 2 16
1 3 16
+xID xID1
+1 1
+2 2
+2 2
+3 134
+3 134
+3 134
+4 185
+4 185
+4 185
+4 185
+xID xID1 Level
+1 1 *
+2 2 **
+3 134 ***
+4 185 ****
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 11408511e99..dfbb2f325a9 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -259,3 +259,14 @@ insert into t1 values (1,3,3,4);
insert into t1 values (1,3,4,4);
select One, Two, sum(Four) from t1 group by One,Two;
drop table if exists t1;
+
+#
+# The GROUP BY returned rows in wrong order in 3.23.51
+#
+
+CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID
+));
+insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL);
+select S.ID as xID, S.ID1 as xID1 from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2;
+select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2 group by xID order by xID1;
+drop table t1;
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index ee8c1097fb2..a40587bb8c9 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -2222,18 +2222,18 @@ int setup_ftfuncs(THD *thd)
return 0;
}
+
int init_ftfuncs(THD *thd, bool no_order)
{
- List_iterator<Item_func_match> li(thd->lex.ftfunc_list);
- Item_func_match *ifm;
- DBUG_PRINT("info",("Performing FULLTEXT search"));
- thd->proc_info="FULLTEXT initialization";
-
- while ((ifm=li++))
+ if (thd->lex.ftfunc_list.elements)
{
- ifm->init_search(no_order);
- }
+ List_iterator<Item_func_match> li(thd->lex.ftfunc_list);
+ Item_func_match *ifm;
+ DBUG_PRINT("info",("Performing FULLTEXT search"));
+ thd->proc_info="FULLTEXT initialization";
+ while ((ifm=li++))
+ ifm->init_search(no_order);
+ }
return 0;
}
-
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 939ec003aca..04368c016d1 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -153,7 +153,7 @@ mysql_select(THD *thd,TABLE_LIST *tables,List<Item> &fields,COND *conds,
uint select_options,select_result *result)
{
TABLE *tmp_table;
- int error,tmp;
+ int error, tmp_error;
bool need_tmp,hidden_group_fields;
bool simple_order,simple_group,no_order;
Item::cond_result cond_value;
@@ -380,9 +380,9 @@ mysql_select(THD *thd,TABLE_LIST *tables,List<Item> &fields,COND *conds,
thd->fatal_error)
goto err;
thd->proc_info="preparing";
- if ((tmp=join_read_const_tables(&join)) > 0)
+ if ((tmp_error=join_read_const_tables(&join)) > 0)
goto err;
- if (tmp && !(select_options & SELECT_DESCRIBE))
+ if (tmp_error && !(select_options & SELECT_DESCRIBE))
{
error=return_zero_rows(result,tables,fields,
join.tmp_table_param.sum_func_count != 0 &&
@@ -701,9 +701,11 @@ mysql_select(THD *thd,TABLE_LIST *tables,List<Item> &fields,COND *conds,
group=0;
}
thd->proc_info="Copying to group table";
+ tmp_error= -1;
if (make_sum_func_list(&join,all_fields) ||
- do_select(&join,(List<Item> *) 0,tmp_table2,0))
+ (tmp_error=do_select(&join,(List<Item> *) 0,tmp_table2,0)))
{
+ error=tmp_error;
free_tmp_table(thd,tmp_table2);
goto err; /* purecov: inspected */
}
@@ -3347,7 +3349,11 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
if (!param->quick_group)
group=0; // Can't use group key
else for (ORDER *tmp=group ; tmp ; tmp=tmp->next)
+ {
(*tmp->item)->marker=4; // Store null in key
+ if ((*tmp->item)->max_length >= MAX_CHAR_WIDTH)
+ using_unique_constraint=1;
+ }
if (param->group_length >= MAX_BLOB_WIDTH)
using_unique_constraint=1;
if (group)
@@ -3477,7 +3483,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
field_count= (uint) (reg_field - table->field);
/* If result table is small; use a heap */
- if (blob_count || using_unique_constraint ||
+ if (blob_count || using_unique_constraint || group_null_items ||
(select_options & (OPTION_BIG_TABLES | SELECT_SMALL_RESULT)) ==
OPTION_BIG_TABLES)
{
@@ -3499,7 +3505,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
if (blob_count == 0)
{
/* We need to ensure that first byte is not 0 for the delete link */
- if (hidden_null_count)
+ if (param->hidden_field_count)
hidden_null_count++;
else
null_count++;
@@ -3633,14 +3639,17 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
if (maybe_null)
{
/*
- To be able to group on NULL, we move the null bit to be
- just before the column and extend the key to cover the null bit
+ To be able to group on NULL, we reserve place in group_buff
+ for the NULL flag just before the column.
+ The field data is after this flag.
+ The NULL flag is updated by 'end_update()' and 'end_write()'
*/
- *group_buff= 0; // Init null byte
- key_part_info->offset--;
- key_part_info->length++;
- group->field->move_field((char*) group_buff+1, (uchar*) group_buff,
- 1);
+ keyinfo->flags|= HA_NULL_ARE_EQUAL; // def. that NULL == NULL
+ key_part_info->null_bit=field->null_bit;
+ key_part_info->null_offset= (uint) (field->null_ptr -
+ (uchar*) table->record[0]);
+ group->field->move_field((char*) ++group->buff);
+ group_buff++;
}
else
group->field->move_field((char*) group_buff);
@@ -3820,11 +3829,17 @@ static bool create_myisam_tmp_table(TABLE *table,TMP_TABLE_PARAM *param,
keyinfo->key_part[i].length > 4)
seg->flag|=HA_SPACE_PACK;
}
- if (using_unique_constraint &&
- !(field->flags & NOT_NULL_FLAG))
+ if (!(field->flags & NOT_NULL_FLAG))
{
seg->null_bit= field->null_bit;
seg->null_pos= (uint) (field->null_ptr - (uchar*) table->record[0]);
+ /*
+ We are using a GROUP BY on something that contains NULL
+ In this case we have to tell MyISAM that two NULL should
+ on INSERT be compared as equal
+ */
+ if (!using_unique_constraint)
+ keydef.flag|= HA_NULL_ARE_EQUAL;
}
}
}
@@ -4797,8 +4812,9 @@ end_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),
{
Item *item= *group->item;
item->save_org_in_field(group->field);
+ /* Store in the used key if the field was 0 */
if (item->maybe_null)
- group->buff[0]=item->null_value ? 0: 1; // Save reversed value
+ group->buff[-1]=item->null_value ? 1 : 0;
}
// table->file->index_init(0);
if (!table->file->index_read(table->record[1],
View Lorry Controller Status