Below is the list of changes that have just been committed into a local
5.1 repository of kgeorge. When kgeorge 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-09-27 13:03:41+03:00, gkodinov@stripped +21 -0
Merge macbook.gmz:/Users/kgeorge/mysql/work/B21174-5.0-opt
into macbook.gmz:/Users/kgeorge/mysql/work/B21174-5.1-opt
MERGE: 1.1810.1697.157
mysql-test/r/ctype_ucs.result@stripped, 2006-09-27 12:56:25+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.39.1.10
mysql-test/r/func_gconcat.result@stripped, 2006-09-27 12:56:25+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.52.1.9
mysql-test/r/func_group.result@stripped, 2006-09-27 12:56:26+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.49.1.4
mysql-test/r/group_by.result@stripped, 2006-09-27 12:56:26+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.69.1.3
mysql-test/r/ps.result@stripped, 2006-09-27 13:03:29+03:00, gkodinov@stripped +0 -47
merge 5.0-opt -> 5.1-opt
MERGE: 1.56.1.18
mysql-test/r/subselect.result@stripped, 2006-09-27 12:56:27+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.134.1.27
mysql-test/r/view.result@stripped, 2006-09-27 12:56:27+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.138.1.39
mysql-test/t/ctype_ucs.test@stripped, 2006-09-27 12:56:27+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.36.1.11
mysql-test/t/func_gconcat.test@stripped, 2006-09-27 12:56:28+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.44.1.2
mysql-test/t/ps.test@stripped, 2006-09-27 12:56:28+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.56.1.15
mysql-test/t/view.test@stripped, 2006-09-27 12:56:29+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.126.1.38
sql/field.h@stripped, 2006-09-27 12:56:29+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.153.12.4
sql/filesort.cc@stripped, 2006-09-27 12:56:29+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.105.1.1
sql/item.cc@stripped, 2006-09-27 12:56:30+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.113.1.122
sql/item_sum.cc@stripped, 2006-09-27 12:56:31+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.170.1.11
sql/mysql_priv.h@stripped, 2006-09-27 12:56:31+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.290.1.121
sql/sql_lex.cc@stripped, 2006-09-27 12:56:32+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.142.1.59
sql/sql_select.cc@stripped, 2006-09-27 12:56:33+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.312.1.144
sql/sql_yacc.yy@stripped, 2006-09-27 12:56:34+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.371.1.117
sql/unireg.cc@stripped, 2006-09-27 13:03:30+03:00, gkodinov@stripped +10 -12
merge 5.0-opt -> 5.1-opt
MERGE: 1.53.1.23
storage/myisam/mi_check.c@stripped, 2006-09-27 12:56:35+03:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.129.16.2
storage/myisam/mi_check.c@stripped, 2006-09-27 12:56:24+03:00, gkodinov@stripped +0 -0
Merge rename: myisam/mi_check.c -> storage/myisam/mi_check.c
# 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: macbook.gmz
# Root: /Users/kgeorge/mysql/work/B21174-5.1-opt/RESYNC
--- 1.129.16.1/myisam/mi_check.c 2006-09-27 13:04:08 +03:00
+++ 1.151/storage/myisam/mi_check.c 2006-09-27 13:04:08 +03:00
@@ -358,7 +358,7 @@ int chk_key(MI_CHECK *param, register MI
puts("- check key delete-chain");
param->key_file_blocks=info->s->base.keystart;
- for (key=0 ; key < info->s->state.header.max_block_size ; key++)
+ for (key=0 ; key < info->s->state.header.max_block_size_index ; key++)
if (check_k_link(param,info,key))
{
if (param->testflag & T_VERBOSE) puts("");
@@ -1412,7 +1412,7 @@ int mi_repair(MI_CHECK *param, register
share->state.key_root[i]= HA_OFFSET_ERROR;
/* Drop the delete chain. */
- for (i=0 ; i < share->state.header.max_block_size ; i++)
+ for (i=0 ; i < share->state.header.max_block_size_index ; i++)
share->state.key_del[i]= HA_OFFSET_ERROR;
/*
@@ -1796,7 +1796,7 @@ int mi_sort_index(MI_CHECK *param, regis
info->update= (short) (HA_STATE_CHANGED | HA_STATE_ROW_CHANGED);
for (key=0 ; key < info->s->base.keys ; key++)
info->s->state.key_root[key]=index_pos[key];
- for (key=0 ; key < info->s->state.header.max_block_size ; key++)
+ for (key=0 ; key < info->s->state.header.max_block_size_index ; key++)
info->s->state.key_del[key]= HA_OFFSET_ERROR;
info->s->state.changed&= ~STATE_NOT_SORTED_PAGES;
@@ -2097,7 +2097,7 @@ int mi_repair_by_sort(MI_CHECK *param, r
/* Clear the pointers to the given rows */
for (i=0 ; i < share->base.keys ; i++)
share->state.key_root[i]= HA_OFFSET_ERROR;
- for (i=0 ; i < share->state.header.max_block_size ; i++)
+ for (i=0 ; i < share->state.header.max_block_size_index ; i++)
share->state.key_del[i]= HA_OFFSET_ERROR;
info->state->key_file_length=share->base.keystart;
}
@@ -2119,6 +2119,7 @@ int mi_repair_by_sort(MI_CHECK *param, r
my_seek(param->read_cache.file,0L,MY_SEEK_END,MYF(0));
sort_param.wordlist=NULL;
+ init_alloc_root(&sort_param.wordroot, FTPARSER_MEMROOT_ALLOC_SIZE, 0);
if (share->data_file_type == DYNAMIC_RECORD)
length=max(share->base.min_pack_length+1,share->base.min_block_length);
@@ -2181,12 +2182,36 @@ int mi_repair_by_sort(MI_CHECK *param, r
{
uint ft_max_word_len_for_sort=FT_MAX_WORD_LEN_FOR_SORT*
sort_param.keyinfo->seg->charset->mbmaxlen;
- sort_info.max_records=
- (ha_rows) (sort_info.filelength/ft_min_word_len+1);
+ sort_param.key_length+=ft_max_word_len_for_sort-HA_FT_MAXBYTELEN;
+ /*
+ fulltext indexes may have much more entries than the
+ number of rows in the table. We estimate the number here.
+
+ Note, built-in parser is always nr. 0 - see ftparser_call_initializer()
+ */
+ if (sort_param.keyinfo->ftparser_nr == 0)
+ {
+ /*
+ for built-in parser the number of generated index entries
+ cannot be larger than the size of the data file divided
+ by the minimal word's length
+ */
+ sort_info.max_records=
+ (ha_rows) (sort_info.filelength/ft_min_word_len+1);
+ }
+ else
+ {
+ /*
+ for external plugin parser we cannot tell anything at all :(
+ so, we'll use all the sort memory and start from ~10 buffpeks.
+ (see _create_index_by_sort)
+ */
+ sort_info.max_records=
+ 10*param->sort_buffer_length/sort_param.key_length;
+ }
sort_param.key_read=sort_ft_key_read;
sort_param.key_write=sort_ft_key_write;
- sort_param.key_length+=ft_max_word_len_for_sort-HA_FT_MAXBYTELEN;
}
else
{
@@ -2202,6 +2227,7 @@ int mi_repair_by_sort(MI_CHECK *param, r
goto err;
}
param->calc_checksum=0; /* No need to calc glob_crc */
+ free_root(&sort_param.wordroot, MYF(0));
/* Set for next loop */
sort_info.max_records= (ha_rows) info->state->records;
@@ -2466,7 +2492,7 @@ int mi_repair_parallel(MI_CHECK *param,
/* Clear the pointers to the given rows */
for (i=0 ; i < share->base.keys ; i++)
share->state.key_root[i]= HA_OFFSET_ERROR;
- for (i=0 ; i < share->state.header.max_block_size ; i++)
+ for (i=0 ; i < share->state.header.max_block_size_index ; i++)
share->state.key_del[i]= HA_OFFSET_ERROR;
info->state->key_file_length=share->base.keystart;
}
@@ -2592,6 +2618,7 @@ int mi_repair_parallel(MI_CHECK *param,
uint ft_max_word_len_for_sort=FT_MAX_WORD_LEN_FOR_SORT*
sort_param[i].keyinfo->seg->charset->mbmaxlen;
sort_param[i].key_length+=ft_max_word_len_for_sort-HA_FT_MAXBYTELEN;
+ init_alloc_root(&sort_param[i].wordroot, FTPARSER_MEMROOT_ALLOC_SIZE, 0);
}
}
sort_info.total_keys=i;
@@ -2813,10 +2840,11 @@ static int sort_ft_key_read(MI_SORT_PARA
{
for (;;)
{
- my_free((char*) wptr, MYF(MY_ALLOW_ZERO_PTR));
+ free_root(&sort_param->wordroot, MYF(MY_MARK_BLOCKS_FREE));
if ((error=sort_get_next_record(sort_param)))
DBUG_RETURN(error);
- if (!(wptr=_mi_ft_parserecord(info,sort_param->key,sort_param->record)))
+ if (!(wptr=_mi_ft_parserecord(info,sort_param->key,sort_param->record,
+ &sort_param->wordroot)))
DBUG_RETURN(1);
if (wptr->pos)
break;
@@ -2840,7 +2868,7 @@ static int sort_ft_key_read(MI_SORT_PARA
#endif
if (!wptr->pos)
{
- my_free((char*) sort_param->wordlist, MYF(0));
+ free_root(&sort_param->wordroot, MYF(MY_MARK_BLOCKS_FREE));
sort_param->wordlist=0;
error=sort_write_record(sort_param);
}
@@ -3904,9 +3932,6 @@ int recreate_table(MI_CHECK *param, MI_I
create_info.data_file_length=file_length;
create_info.auto_increment=share.state.auto_increment;
- create_info.raid_type= share.base.raid_type;
- create_info.raid_chunks= share.base.raid_chunks;
- create_info.raid_chunksize= share.base.raid_chunksize;
create_info.language = (param->language ? param->language :
share.state.header.language);
create_info.key_file_length= status_info.key_file_length;
--- 1.201/sql/field.h 2006-09-27 13:04:08 +03:00
+++ 1.202/sql/field.h 2006-09-27 13:04:09 +03:00
@@ -1476,6 +1476,8 @@ public:
uint decimals, flags, pack_length, key_length;
Field::utype unireg_check;
TYPELIB *interval; // Which interval to use
+ TYPELIB *save_interval; // Temporary copy for the above
+ // Used only for UCS2 intervals
List<String> interval_list;
CHARSET_INFO *charset;
Field::geometry_type geom_type;
--- 1.110/sql/filesort.cc 2006-09-27 13:04:09 +03:00
+++ 1.111/sql/filesort.cc 2006-09-27 13:04:09 +03:00
@@ -1344,6 +1344,7 @@ sortlength(THD *thd, SORT_FIELD *sortord
switch ((sortorder->result_type=sortorder->item->result_type())) {
case STRING_RESULT:
sortorder->length=sortorder->item->max_length;
+ set_if_smaller(sortorder->length, thd->variables.max_sort_length);
if (use_strnxfrm((cs=sortorder->item->collation.collation)))
{
sortorder->length= cs->coll->strnxfrmlen(cs, sortorder->length);
--- 1.210/sql/item.cc 2006-09-27 13:04:09 +03:00
+++ 1.211/sql/item.cc 2006-09-27 13:04:09 +03:00
@@ -1210,6 +1210,7 @@ void Item::split_sum_func2(THD *thd, Ite
split_sum_func(thd, ref_pointer_array, fields);
}
else if ((type() == SUM_FUNC_ITEM || (used_tables() & ~PARAM_TABLE_BIT)) &&
+ type() != SUBSELECT_ITEM &&
(type() != REF_ITEM ||
((Item_ref*)this)->ref_type() == Item_ref::VIEW_REF))
{
--- 1.190/sql/item_sum.cc 2006-09-27 13:04:09 +03:00
+++ 1.191/sql/item_sum.cc 2006-09-27 13:04:09 +03:00
@@ -246,7 +246,27 @@ bool Item_sum::register_sum_func(THD *th
aggr_sl->inner_sum_func_list->next= this;
}
aggr_sl->inner_sum_func_list= this;
-
+ aggr_sl->with_sum_func= 1;
+
+ /*
+ Mark Item_subselect(s) as containing aggregate function all the way up
+ to aggregate function's calculation context.
+ Note that we must not mark the Item of calculation context itself
+ because with_sum_func on the calculation context st_select_lex is
+ already set above.
+
+ with_sum_func being set for an Item means that this Item refers
+ (somewhere in it, e.g. one of its arguments if it's a function) directly
+ or through intermediate items to an aggregate function that is calculated
+ in a context "outside" of the Item (e.g. in the current or outer select).
+
+ with_sum_func being set for an st_select_lex means that this st_select_lex
+ has aggregate functions directly referenced (i.e. not through a sub-select).
+ */
+ for (sl= thd->lex->current_select;
+ sl && sl != aggr_sl && sl->master_unit()->item;
+ sl= sl->master_unit()->outer_select() )
+ sl->master_unit()->item->with_sum_func= 1;
}
return FALSE;
}
--- 1.439/sql/mysql_priv.h 2006-09-27 13:04:09 +03:00
+++ 1.440/sql/mysql_priv.h 2006-09-27 13:04:09 +03:00
@@ -455,7 +455,8 @@ enum enum_parsing_place
NO_MATTER,
IN_HAVING,
SELECT_LIST,
- IN_WHERE
+ IN_WHERE,
+ IN_ON
};
struct st_table;
--- 1.198/sql/sql_lex.cc 2006-09-27 13:04:09 +03:00
+++ 1.199/sql/sql_lex.cc 2006-09-27 13:04:09 +03:00
@@ -1722,7 +1722,8 @@ bool st_lex::can_be_merged()
unit= unit->next_unit())
{
if (unit->first_select()->parent_lex == this &&
- (unit->item == 0 || unit->item->place() != IN_WHERE))
+ (unit->item == 0 ||
+ (unit->item->place() != IN_WHERE && unit->item->place() != IN_ON)))
{
selects_allow_merge= 0;
break;
--- 1.441/sql/sql_select.cc 2006-09-27 13:04:10 +03:00
+++ 1.442/sql/sql_select.cc 2006-09-27 13:04:10 +03:00
@@ -11875,8 +11875,6 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
We must not try to use disabled keys.
*/
usable_keys= table->s->keys_in_use;
- /* we must not consider keys that are disabled by IGNORE INDEX */
- usable_keys.intersect(table->keys_in_use_for_query);
for (ORDER *tmp_order=order; tmp_order ; tmp_order=tmp_order->next)
{
--- 1.499/sql/sql_yacc.yy 2006-09-27 13:04:10 +03:00
+++ 1.500/sql/sql_yacc.yy 2006-09-27 13:04:10 +03:00
@@ -6847,11 +6847,13 @@ join_table:
/* Change the current name resolution context to a local context. */
if (push_new_name_resolution_context(YYTHD, $1, $3))
YYABORT;
+ Select->parsing_place= IN_ON;
}
expr
{
add_join_on($3,$6);
Lex->pop_context();
+ Select->parsing_place= NO_MATTER;
}
| table_ref STRAIGHT_JOIN table_factor
ON
@@ -6860,12 +6862,14 @@ join_table:
/* Change the current name resolution context to a local context. */
if (push_new_name_resolution_context(YYTHD, $1, $3))
YYABORT;
+ Select->parsing_place= IN_ON;
}
expr
{
$3->straight=1;
add_join_on($3,$6);
Lex->pop_context();
+ Select->parsing_place= NO_MATTER;
}
| table_ref normal_join table_ref
USING
@@ -6889,6 +6893,7 @@ join_table:
/* Change the current name resolution context to a local context. */
if (push_new_name_resolution_context(YYTHD, $1, $5))
YYABORT;
+ Select->parsing_place= IN_ON;
}
expr
{
@@ -6896,6 +6901,7 @@ join_table:
Lex->pop_context();
$5->outer_join|=JOIN_TYPE_LEFT;
$$=$5;
+ Select->parsing_place= NO_MATTER;
}
| table_ref LEFT opt_outer JOIN_SYM table_factor
{
@@ -6920,6 +6926,7 @@ join_table:
/* Change the current name resolution context to a local context. */
if (push_new_name_resolution_context(YYTHD, $1, $5))
YYABORT;
+ Select->parsing_place= IN_ON;
}
expr
{
@@ -6928,6 +6935,7 @@ join_table:
YYABORT;
add_join_on($$, $8);
Lex->pop_context();
+ Select->parsing_place= NO_MATTER;
}
| table_ref RIGHT opt_outer JOIN_SYM table_factor
{
--- 1.84/sql/unireg.cc 2006-09-27 13:04:10 +03:00
+++ 1.85/sql/unireg.cc 2006-09-27 13:04:10 +03:00
@@ -292,13 +292,19 @@ bool mysql_create_frm(THD *thd, const ch
goto err3;
{
- /* Unescape all UCS2 intervals: were escaped in pack_headers */
+ /*
+ Restore all UCS2 intervals.
+ HEX representation of them is not needed anymore.
+ */
List_iterator<create_field> it(create_fields);
create_field *field;
while ((field=it++))
{
- if (field->interval && field->charset->mbminlen > 1)
- unhex_type2(field->interval);
+ if (field->save_interval)
+ {
+ field->interval= field->save_interval;
+ field->save_interval= 0;
+ }
}
}
DBUG_RETURN(0);
@@ -589,18 +595,36 @@ static bool pack_header(uchar *forminfo,
reclength=(uint) (field->offset+ data_offset + length);
n_length+= (ulong) strlen(field->field_name)+1;
field->interval_id=0;
+ field->save_interval= 0;
if (field->interval)
{
uint old_int_count=int_count;
if (field->charset->mbminlen > 1)
{
- /* Escape UCS2 intervals using HEX notation */
+ /*
+ Escape UCS2 intervals using HEX notation to avoid
+ problems with delimiters between enum elements.
+ As the original representation is still needed in
+ the function make_empty_rec to create a record of
+ filled with default values it is saved in save_interval
+ The HEX representation is created from this copy.
+ */
+ field->save_interval= field->interval;
+ field->interval= (TYPELIB*) sql_alloc(sizeof(TYPELIB));
+ *field->interval= *field->save_interval;
+ field->interval->type_names=
+ (const char **) sql_alloc(sizeof(char*) *
+ (field->interval->count+1));
+ field->interval->type_names[field->interval->count]= 0;
+ field->interval->type_lengths=
+ (uint *) sql_alloc(sizeof(uint) * field->interval->count);
+
for (uint pos= 0; pos < field->interval->count; pos++)
{
char *dst;
- uint length= field->interval->type_lengths[pos], hex_length;
- const char *src= field->interval->type_names[pos];
+ uint length= field->save_interval->type_lengths[pos], hex_length;
+ const char *src= field->save_interval->type_names[pos];
hex_length= length * 2;
field->interval->type_lengths[pos]= hex_length;
field->interval->type_names[pos]= dst= sql_alloc(hex_length + 1);
@@ -842,18 +866,18 @@ static bool make_empty_rec(THD *thd, Fil
/*
regfield don't have to be deleted as it's allocated with sql_alloc()
*/
- Field *regfield= make_field(&share,
- (char*) buff+field->offset + data_offset,
- field->length,
- null_pos + null_count / 8,
- null_count & 7,
- field->pack_flag,
- field->sql_type,
- field->charset,
- field->geom_type,
- field->unireg_check,
- field->interval,
- field->field_name);
+ Field *regfield=make_field((char*) buff+field->offset + data_offset,
+ field->length,
+ null_pos + null_count / 8,
+ null_count & 7,
+ field->pack_flag,
+ field->sql_type,
+ field->charset,
+ field->geom_type,
+ field->unireg_check,
+ field->save_interval ? field->save_interval :
+ field->interval,
+ field->field_name);
if (!regfield)
goto err; // End of memory
--- 1.50/mysql-test/r/ctype_ucs.result 2006-09-27 13:04:10 +03:00
+++ 1.51/mysql-test/r/ctype_ucs.result 2006-09-27 13:04:10 +03:00
@@ -755,6 +755,27 @@ select export_set(5, name, upper(name),
export_set(5, name, upper(name), ",", 5)
test1,TEST1,test1,TEST1,TEST1
'test\_2','TEST\_2','test\_2','TEST\_2','TEST\_2'
+CREATE TABLE t1 (
+status enum('active','passive') collate latin1_general_ci
+NOT NULL default 'passive'
+);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `status` enum('active','passive') character set latin1 collate latin1_general_ci NOT NULL default 'passive'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+ALTER TABLE t1 ADD a int NOT NULL AFTER status;
+CREATE TABLE t2 (
+status enum('active','passive') collate ucs2_turkish_ci
+NOT NULL default 'passive'
+);
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `status` enum('active','passive') character set ucs2 collate ucs2_turkish_ci NOT NULL default 'passive'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+ALTER TABLE t2 ADD a int NOT NULL AFTER status;
+DROP TABLE t1,t2;
select password(name) from bug20536;
password(name)
????????????????????
--- 1.46/mysql-test/t/ctype_ucs.test 2006-09-27 13:04:10 +03:00
+++ 1.47/mysql-test/t/ctype_ucs.test 2006-09-27 13:04:10 +03:00
@@ -484,6 +484,27 @@ select make_set(3, name, upper(name)) fr
select export_set(5, name, upper(name)) from bug20536;
select export_set(5, name, upper(name), ",", 5) from bug20536;
+#
+# Bug #20108: corrupted default enum value for a ucs2 field
+#
+
+CREATE TABLE t1 (
+ status enum('active','passive') collate latin1_general_ci
+ NOT NULL default 'passive'
+);
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 ADD a int NOT NULL AFTER status;
+
+CREATE TABLE t2 (
+ status enum('active','passive') collate ucs2_turkish_ci
+ NOT NULL default 'passive'
+);
+SHOW CREATE TABLE t2;
+ALTER TABLE t2 ADD a int NOT NULL AFTER status;
+
+DROP TABLE t1,t2;
+
+
# Some broken functions: add these tests just to document current behavior.
# PASSWORD and OLD_PASSWORD don't work with UCS2 strings, but to fix it would
--- 1.152/mysql-test/r/subselect.result 2006-09-27 13:04:11 +03:00
+++ 1.153/mysql-test/r/subselect.result 2006-09-27 13:04:11 +03:00
@@ -1160,7 +1160,7 @@ Code2 char(2) NOT NULL default '',
PRIMARY KEY (Code)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES ('AUS','Australia','Oceania','Australia and New Zealand',7741220.00,1901,18886000,79.8,351182.00,392911.00,'Australia','Constitutional Monarchy, Federation','Elisabeth II',135,'AU');
+INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ');
select t2.Continent, t1.Name, t1.Population from t2 LEFT JOIN t1 ON t2.Code = t1.t2 where t1.Population IN (select max(t1.Population) AS Population from t1, t2 where t1.t2 = t2.Code group by Continent);
Continent Name Population
Oceania Sydney 3276207
@@ -2512,7 +2512,7 @@ Code2 char(2) NOT NULL default ''
) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,68000,75.1,334.00,NULL,'Amerika Samoa','US Territory','George W. Bush',54,'AS');
+INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF');
INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM');
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
SELECT DISTINCT Continent AS c FROM t1 WHERE Code <> SOME ( SELECT Code FROM t1 WHERE Continent = c AND Population < 200);
@@ -2966,6 +2966,42 @@ ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a
a a b
10 1 359
drop table t1,t2;
+CREATE TABLE t1 (
+field1 int NOT NULL,
+field2 int NOT NULL,
+field3 int NOT NULL,
+PRIMARY KEY (field1,field2,field3)
+);
+CREATE TABLE t2 (
+fieldA int NOT NULL,
+fieldB int NOT NULL,
+PRIMARY KEY (fieldA,fieldB)
+);
+INSERT INTO t1 VALUES
+(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
+INSERT INTO t2 VALUES (1,1), (1,2), (1,3);
+SELECT field1, field2, COUNT(*)
+FROM t1 GROUP BY field1, field2;
+field1 field2 COUNT(*)
+1 1 2
+1 2 3
+1 3 1
+SELECT field1, field2
+FROM t1
+GROUP BY field1, field2
+HAVING COUNT(*) >= ALL (SELECT fieldB
+FROM t2 WHERE fieldA = field1);
+field1 field2
+1 2
+SELECT field1, field2
+FROM t1
+GROUP BY field1, field2
+HAVING COUNT(*) < ANY (SELECT fieldB
+FROM t2 WHERE fieldA = field1);
+field1 field2
+1 1
+1 3
+DROP TABLE t1, t2;
create table t1 (df decimal(5,1));
insert into t1 values(1.1);
insert into t1 values(2.2);
@@ -3422,3 +3458,64 @@ id select_type table type possible_keys
4 UNION t12 system NULL NULL NULL NULL 0 const row not found
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
DROP TABLE t1;
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 values (1);
+INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
+SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
+(SELECT COUNT(DISTINCT t1.b) from t2)
+2
+1
+1
+SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
+FROM t1 GROUP BY t1.a;
+(SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
+2
+1
+1
+SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
+COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b))
+2 2
+1 1
+1 1
+SELECT COUNT(DISTINCT t1.b),
+(SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
+FROM t1 GROUP BY t1.a;
+COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
+2 2
+1 1
+1 1
+SELECT (
+SELECT (
+SELECT COUNT(DISTINCT t1.b)
+)
+)
+FROM t1 GROUP BY t1.a;
+(
+SELECT (
+SELECT COUNT(DISTINCT t1.b)
+)
+)
+2
+1
+1
+SELECT (
+SELECT (
+SELECT (
+SELECT COUNT(DISTINCT t1.b)
+)
+)
+FROM t1 GROUP BY t1.a LIMIT 1)
+FROM t1 t2
+GROUP BY t2.a;
+(
+SELECT (
+SELECT (
+SELECT COUNT(DISTINCT t1.b)
+)
+)
+FROM t1 GROUP BY t1.a LIMIT 1)
+2
+2
+2
+DROP TABLE t1,t2;
--- 1.179/mysql-test/r/view.result 2006-09-27 13:04:11 +03:00
+++ 1.180/mysql-test/r/view.result 2006-09-27 13:04:11 +03:00
@@ -2935,4 +2935,18 @@ id select_type table type possible_keys
2 SUBQUERY t1 ALL NULL NULL NULL NULL 3
DROP VIEW v1;
DROP TABLE t1;
+CREATE TABLE t1(pk int PRIMARY KEY);
+CREATE TABLE t2(pk int PRIMARY KEY, fk int, ver int, org int);
+CREATE ALGORITHM=MERGE VIEW v1 AS
+SELECT t1.*
+FROM t1 JOIN t2
+ON t2.fk = t1.pk AND
+t2.ver = (SELECT MAX(t.ver) FROM t2 t WHERE t.org = t2.org);
+SHOW WARNINGS;
+Level Code Message
+SHOW CREATE VIEW v1;
+View Create View
+v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`pk` AS `pk` from (`t1` join `t2` on(((`t2`.`fk` = `t1`.`pk`) and (`t2`.`ver` = (select max(`t`.`ver`) AS `MAX(t.ver)` from `t2` `t` where (`t`.`org` = `t2`.`org`))))))
+DROP VIEW v1;
+DROP TABLE t1, t2;
End of 5.0 tests.
--- 1.160/mysql-test/t/view.test 2006-09-27 13:04:11 +03:00
+++ 1.161/mysql-test/t/view.test 2006-09-27 13:04:11 +03:00
@@ -2855,4 +2855,22 @@ EXPLAIN SELECT * FROM v1 t WHERE t.s1+1
DROP VIEW v1;
DROP TABLE t1;
+
+#
+# Bug #21646: view qith a subquery in ON expression
+#
+
+CREATE TABLE t1(pk int PRIMARY KEY);
+CREATE TABLE t2(pk int PRIMARY KEY, fk int, ver int, org int);
+
+CREATE ALGORITHM=MERGE VIEW v1 AS
+SELECT t1.*
+ FROM t1 JOIN t2
+ ON t2.fk = t1.pk AND
+ t2.ver = (SELECT MAX(t.ver) FROM t2 t WHERE t.org = t2.org);
+SHOW WARNINGS;
+SHOW CREATE VIEW v1;
+
+DROP VIEW v1;
+DROP TABLE t1, t2;
--echo End of 5.0 tests.
--- 1.52/mysql-test/r/func_group.result 2006-09-27 13:04:11 +03:00
+++ 1.53/mysql-test/r/func_group.result 2006-09-27 13:04:11 +03:00
@@ -851,8 +851,8 @@ CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1,1),(1,2),(2,3);
SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
(SELECT COUNT(DISTINCT t1.b))
-0
2
+1
SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a;
(SELECT COUNT(DISTINCT 12))
1
@@ -1004,7 +1004,7 @@ SELECT SQL_NO_CACHE
WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid
FROM t1 t, t2 c WHERE t.a = c.b;
minid
-NULL
+1
DROP TABLE t1,t2;
create table t1 select variance(0);
show create table t1;
--- 1.72/mysql-test/r/group_by.result 2006-09-27 13:04:11 +03:00
+++ 1.73/mysql-test/r/group_by.result 2006-09-27 13:04:11 +03:00
@@ -821,12 +821,3 @@ a b real_b
68 France France
DROP VIEW v1;
DROP TABLE t1,t2;
-CREATE TABLE t1 (a INT, b INT, KEY(a));
-INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);
-EXPLAIN SELECT a, SUM(b) FROM t1 GROUP BY a LIMIT 2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL a 5 NULL 4
-EXPLAIN SELECT a, SUM(b) FROM t1 IGNORE INDEX (a) GROUP BY a LIMIT 2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
-DROP TABLE t1;
--- 1.71/mysql-test/t/ps.test 2006-09-27 13:04:11 +03:00
+++ 1.72/mysql-test/t/ps.test 2006-09-27 13:04:11 +03:00
@@ -988,6 +988,58 @@ execute stmt;
drop temporary table t1;
deallocate prepare stmt;
+#
+# BUG#22085: Crash on the execution of a prepared statement that
+# uses an IN subquery with aggregate functions in HAVING
+#
+
+CREATE TABLE t1(
+ ID int(10) unsigned NOT NULL auto_increment,
+ Member_ID varchar(15) NOT NULL default '',
+ Action varchar(12) NOT NULL,
+ Action_Date datetime NOT NULL,
+ Track varchar(15) default NULL,
+ User varchar(12) default NULL,
+ Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update
+ CURRENT_TIMESTAMP,
+ PRIMARY KEY (ID),
+ KEY Action (Action),
+ KEY Action_Date (Action_Date)
+);
+
+INSERT INTO t1(Member_ID, Action, Action_Date, Track) VALUES
+ ('111111', 'Disenrolled', '2006-03-01', 'CAD' ),
+ ('111111', 'Enrolled', '2006-03-01', 'CAD' ),
+ ('111111', 'Disenrolled', '2006-07-03', 'CAD' ),
+ ('222222', 'Enrolled', '2006-03-07', 'CAD' ),
+ ('222222', 'Enrolled', '2006-03-07', 'CHF' ),
+ ('222222', 'Disenrolled', '2006-08-02', 'CHF' ),
+ ('333333', 'Enrolled', '2006-03-01', 'CAD' ),
+ ('333333', 'Disenrolled', '2006-03-01', 'CAD' ),
+ ('444444', 'Enrolled', '2006-03-01', 'CAD' ),
+ ('555555', 'Disenrolled', '2006-03-01', 'CAD' ),
+ ('555555', 'Enrolled', '2006-07-21', 'CAD' ),
+ ('555555', 'Disenrolled', '2006-03-01', 'CHF' ),
+ ('666666', 'Enrolled', '2006-02-09', 'CAD' ),
+ ('666666', 'Enrolled', '2006-05-12', 'CHF' ),
+ ('666666', 'Disenrolled', '2006-06-01', 'CAD' );
+
+PREPARE STMT FROM
+"SELECT GROUP_CONCAT(Track SEPARATOR ', ') FROM t1
+ WHERE Member_ID=? AND Action='Enrolled' AND
+ (Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t1
+ WHERE Member_ID=?
+ GROUP BY Track
+ HAVING Track>='CAD' AND
+ MAX(Action_Date)>'2006-03-01')";
+SET @id='111111';
+EXECUTE STMT USING @id,@id;
+SET @id='222222';
+EXECUTE STMT USING @id,@id;
+
+DEALLOCATE PREPARE STMT;
+DROP TABLE t1;
+
--echo End of 4.1 tests
############################# 5.0 tests start ################################
#
--- 1.60/mysql-test/r/func_gconcat.result 2006-09-27 13:04:12 +03:00
+++ 1.61/mysql-test/r/func_gconcat.result 2006-09-27 13:04:12 +03:00
@@ -660,3 +660,12 @@ CHAR_LENGTH( GROUP_CONCAT(b) )
240001
SET GROUP_CONCAT_MAX_LEN = 1024;
DROP TABLE t1;
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (2,1), (1,2), (2,2), (1,3);
+SELECT GROUP_CONCAT(a), x
+FROM (SELECT a, GROUP_CONCAT(b) x FROM t1 GROUP BY a) AS s
+GROUP BY x;
+GROUP_CONCAT(a) x
+2 1,2
+1 2,3
+DROP TABLE t1;
--- 1.46/mysql-test/t/func_gconcat.test 2006-09-27 13:04:12 +03:00
+++ 1.47/mysql-test/t/func_gconcat.test 2006-09-27 13:04:12 +03:00
@@ -447,3 +447,18 @@ SELECT a, CHAR_LENGTH(b) FROM t1;
SELECT CHAR_LENGTH( GROUP_CONCAT(b) ) FROM t1;
SET GROUP_CONCAT_MAX_LEN = 1024;
DROP TABLE t1;
+
+#
+# Bug #22015: crash with GROUP_CONCAT over a derived table that
+# returns the results of aggregation by GROUP_CONCAT
+#
+
+CREATE TABLE t1 (a int, b int);
+
+INSERT INTO t1 VALUES (2,1), (1,2), (2,2), (1,3);
+
+SELECT GROUP_CONCAT(a), x
+ FROM (SELECT a, GROUP_CONCAT(b) x FROM t1 GROUP BY a) AS s
+ GROUP BY x;
+
+DROP TABLE t1;
| Thread |
|---|
| • bk commit into 5.1 tree (gkodinov:1.2337) | kgeorge | 27 Sep |
