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-10-19 16:43:46+02:00, gkodinov@stripped +36 -0
Merge dl145s.mysql.com:/data/bk/team_tree_merge/MERGE/mysql-5.0-opt
into dl145s.mysql.com:/data/bk/team_tree_merge/MERGE/mysql-5.1-opt
MERGE: 1.1810.2078.35
BitKeeper/deleted/.del-bdb.result@stripped, 2006-10-19 16:01:34+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.48.1.5
BitKeeper/deleted/.del-bdb.result@stripped, 2006-10-19 16:01:33+02:00, gkodinov@stripped +0 -0
Merge rename: mysql-test/r/bdb.result -> BitKeeper/deleted/.del-bdb.result
include/my_base.h@stripped, 2006-10-19 16:02:14+02:00, gkodinov@stripped +0 -0
SCCS merged
MERGE: 1.70.1.11
mysql-test/include/mix1.inc@stripped, 2006-10-19 16:03:11+02:00, gkodinov@stripped +0 -0
SCCS merged
MERGE: 1.3.4.5
mysql-test/include/mix1.inc@stripped, 2006-10-19 16:01:34+02:00, gkodinov@stripped +0 -0
Merge rename: mysql-test/t/innodb_mysql.test -> mysql-test/include/mix1.inc
mysql-test/r/func_gconcat.result@stripped, 2006-10-19 16:01:34+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.52.1.10
mysql-test/r/func_str.result@stripped, 2006-10-19 16:04:42+02:00, gkodinov@stripped +1 -2
MERGE: 1.110.1.11
mysql-test/r/group_by.result@stripped, 2006-10-19 16:07:02+02:00, gkodinov@stripped +0 -0
SCCS merged
MERGE: 1.69.1.6
mysql-test/r/group_min_max.result@stripped, 2006-10-19 16:01:34+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.23.1.4
mysql-test/r/innodb.result@stripped, 2006-10-19 16:01:34+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.145.1.18
mysql-test/r/innodb_mysql.result@stripped, 2006-10-19 16:11:55+02:00, gkodinov@stripped +0 -0
SCCS merged
MERGE: 1.3.4.5
mysql-test/r/merge.result@stripped, 2006-10-19 16:01:34+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.43.1.12
mysql-test/r/myisam.result@stripped, 2006-10-19 16:01:34+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.71.1.17
mysql-test/r/olap.result@stripped, 2006-10-19 16:01:34+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.33.1.2
mysql-test/r/select.result@stripped, 2006-10-19 16:01:34+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.121.1.22
mysql-test/r/subselect.result@stripped, 2006-10-19 16:14:55+02:00, gkodinov@stripped +4 -8
MERGE: 1.134.1.29
mysql-test/r/type_decimal.result@stripped, 2006-10-19 16:01:34+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.38.1.4
mysql-test/r/view.result@stripped, 2006-10-19 16:01:34+02:00, gkodinov@stripped +0 -10
Auto merged
MERGE: 1.138.1.42
mysql-test/t/func_gconcat.test@stripped, 2006-10-19 16:01:35+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.44.1.3
mysql-test/t/group_by.test@stripped, 2006-10-19 16:16:35+02:00, gkodinov@stripped +0 -0
SCCS merged
MERGE: 1.59.1.2
mysql-test/t/group_min_max.test@stripped, 2006-10-19 16:01:35+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.21.1.4
mysql-test/t/select.test@stripped, 2006-10-19 16:01:35+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.102.1.14
mysql-test/t/view.test@stripped, 2006-10-19 16:17:33+02:00, gkodinov@stripped +1 -15
MERGE: 1.126.1.41
sql/item_cmpfunc.cc@stripped, 2006-10-19 16:01:35+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.187.1.36
sql/item_cmpfunc.h@stripped, 2006-10-19 16:01:35+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.122.2.9
sql/mysql_priv.h@stripped, 2006-10-19 16:18:27+02:00, gkodinov@stripped +1 -1
MERGE: 1.290.1.126
sql/opt_range.cc@stripped, 2006-10-19 16:01:35+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.159.1.71
sql/opt_range.h@stripped, 2006-10-19 16:01:35+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.57.1.7
sql/sql_base.cc@stripped, 2006-10-19 16:01:35+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.235.66.8
sql/sql_delete.cc@stripped, 2006-10-19 16:01:35+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.144.1.36
sql/sql_lex.cc@stripped, 2006-10-19 16:01:35+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.142.1.60
sql/sql_lex.h@stripped, 2006-10-19 16:01:35+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.175.20.4
sql/sql_rename.cc@stripped, 2006-10-19 16:40:53+02:00, gkodinov@stripped +9 -17
MERGE: 1.31.1.3
sql/sql_select.cc@stripped, 2006-10-19 16:42:02+02:00, gkodinov@stripped +0 -0
SCCS merged
MERGE: 1.312.1.153
sql/sql_select.h@stripped, 2006-10-19 16:01:36+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.92.1.19
sql/sql_table.cc@stripped, 2006-10-19 16:43:38+02:00, gkodinov@stripped +1 -2
MERGE: 1.239.1.84
sql/sql_update.cc@stripped, 2006-10-19 16:01:36+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.154.2.47
sql/table.cc@stripped, 2006-10-19 16:01:36+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.160.34.6
# 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: /data/bk/team_tree_merge/MERGE/mysql-5.1-opt/RESYNC
--- 1.92/include/my_base.h 2006-10-19 16:43:54 +02:00
+++ 1.93/include/my_base.h 2006-10-19 16:43:54 +02:00
@@ -229,12 +229,17 @@
#define HA_USES_PARSER 16384 /* Fulltext index uses [pre]parser */
#define HA_USES_BLOCK_SIZE ((uint) 32768)
#define HA_SORT_ALLOWS_SAME 512 /* Intern bit when sorting records */
+#if MYSQL_VERSION_ID < 0x50200
/*
Key has a part that can have end space. If this is an unique key
we have to handle it differently from other unique keys as we can find
many matching rows for one key (because end space are not compared)
*/
-#define HA_END_SPACE_KEY 4096
+#define HA_END_SPACE_KEY 0 /* was: 4096 */
+#else
+#error HA_END_SPACE_KEY is obsolete, please remove it
+#endif
+
/* These flags can be added to key-seg-flag */
--- 1.224/sql/item_cmpfunc.cc 2006-10-19 16:43:54 +02:00
+++ 1.225/sql/item_cmpfunc.cc 2006-10-19 16:43:54 +02:00
@@ -1122,6 +1122,8 @@
if (Item_func_opt_neg::fix_fields(thd, ref))
return 1;
+ thd->lex->current_select->between_count++;
+
/* not_null_tables_cache == union(T1(e),T1(e1),T1(e2)) */
if (pred_level && !negated)
return 0;
--- 1.135/sql/item_cmpfunc.h 2006-10-19 16:43:54 +02:00
+++ 1.136/sql/item_cmpfunc.h 2006-10-19 16:43:54 +02:00
@@ -448,6 +448,7 @@
negated= !negated;
return this;
}
+ bool subst_argument_checker(byte **arg) { return TRUE; }
};
--- 1.449/sql/mysql_priv.h 2006-10-19 16:43:54 +02:00
+++ 1.450/sql/mysql_priv.h 2006-10-19 16:43:54 +02:00
@@ -784,6 +784,9 @@
const char *table_name, uint flags);
void close_cached_table(THD *thd, TABLE *table);
bool mysql_rename_tables(THD *thd, TABLE_LIST *table_list, bool silent);
+bool do_rename(THD *thd, TABLE_LIST *ren_table, char *new_db,
+ char *new_table_name, char *new_table_alias,
+ bool skip_error);
bool mysql_change_db(THD *thd,const char *name,bool no_access_check);
void mysql_parse(THD *thd,char *inBuf,uint length);
bool mysql_test_parse_for_slave(THD *thd,char *inBuf,uint length);
@@ -870,7 +873,8 @@
bool check_simple_select();
int mysql_alter_tablespace(THD* thd, st_alter_tablespace *ts_info);
-SORT_FIELD * make_unireg_sortorder(ORDER *order, uint *length);
+SORT_FIELD * make_unireg_sortorder(ORDER *order, uint *length,
+ SORT_FIELD *sortorder);
int setup_order(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
List<Item> &fields, List <Item> &all_fields, ORDER *order);
int setup_group(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
--- 1.240/sql/opt_range.cc 2006-10-19 16:43:54 +02:00
+++ 1.241/sql/opt_range.cc 2006-10-19 16:43:54 +02:00
@@ -9787,6 +9787,7 @@
quick->quick_prefix_select= NULL;
quick->update_key_stat();
+ quick->adjust_prefix_ranges();
DBUG_RETURN(quick);
}
@@ -10013,6 +10014,42 @@
if (insert_dynamic(&min_max_ranges, (gptr)&range))
return TRUE;
return FALSE;
+}
+
+
+/*
+ Opens the ranges if there are more conditions in quick_prefix_select than
+ the ones used for jumping through the prefixes.
+
+ SYNOPSIS
+ QUICK_GROUP_MIN_MAX_SELECT::adjust_prefix_ranges()
+
+ NOTES
+ quick_prefix_select is made over the conditions on the whole key.
+ It defines a number of ranges of length x.
+ However when jumping through the prefixes we use only the the first
+ few most significant keyparts in the range key. However if there
+ are more keyparts to follow the ones we are using we must make the
+ condition on the key inclusive (because x < "ab" means
+ x[0] < 'a' OR (x[0] == 'a' AND x[1] < 'b').
+ To achive the above we must turn off the NEAR_MIN/NEAR_MAX
+*/
+void QUICK_GROUP_MIN_MAX_SELECT::adjust_prefix_ranges ()
+{
+ if (quick_prefix_select &&
+ group_prefix_len < quick_prefix_select->max_used_key_length)
+ {
+ DYNAMIC_ARRAY *arr;
+ uint inx;
+
+ for (inx= 0, arr= &quick_prefix_select->ranges; inx < arr->elements; inx++)
+ {
+ QUICK_RANGE *range;
+
+ get_dynamic(arr, (gptr)&range, inx);
+ range->flag &= ~(NEAR_MIN | NEAR_MAX);
+ }
+ }
}
--- 1.66/sql/opt_range.h 2006-10-19 16:43:54 +02:00
+++ 1.67/sql/opt_range.h 2006-10-19 16:43:54 +02:00
@@ -295,6 +295,7 @@
friend class QUICK_SELECT_DESC;
friend class QUICK_INDEX_MERGE_SELECT;
friend class QUICK_ROR_INTERSECT_SELECT;
+ friend class QUICK_GROUP_MIN_MAX_SELECT;
DYNAMIC_ARRAY ranges; /* ordered array of range ptrs */
QUICK_RANGE **cur_range; /* current element in ranges */
@@ -643,6 +644,7 @@
~QUICK_GROUP_MIN_MAX_SELECT();
bool add_range(SEL_ARG *sel_range);
void update_key_stat();
+ void adjust_prefix_ranges();
bool alloc_buffers();
int init();
int reset();
--- 1.356/sql/sql_base.cc 2006-10-19 16:43:54 +02:00
+++ 1.357/sql/sql_base.cc 2006-10-19 16:43:54 +02:00
@@ -4362,13 +4362,20 @@
const char *field_name=0;
const char *table_name=0;
bool found_unaliased_non_uniq= 0;
+ /*
+ true if the item that we search for is a valid name reference
+ (and not an item that happens to have a name).
+ */
+ bool is_ref_by_name= 0;
uint unaliased_counter;
LINT_INIT(unaliased_counter); // Dependent on found_unaliased
*unaliased= FALSE;
- if (find->type() == Item::FIELD_ITEM || find->type() == Item::REF_ITEM)
+ is_ref_by_name= (find->type() == Item::FIELD_ITEM ||
+ find->type() == Item::REF_ITEM);
+ if (is_ref_by_name)
{
field_name= ((Item_ident*) find)->field_name;
table_name= ((Item_ident*) find)->table_name;
@@ -4480,7 +4487,7 @@
}
}
else if (!table_name && (find->eq(item,0) ||
- find->name && item->name &&
+ is_ref_by_name && find->name && item->name &&
!my_strcasecmp(system_charset_info,
item->name,find->name)))
{
@@ -5810,6 +5817,7 @@
thd->mark_used_columns= MARK_COLUMNS_READ;
DBUG_PRINT("info", ("thd->mark_used_columns: %d", thd->mark_used_columns));
select_lex->cond_count= 0;
+ select_lex->between_count= 0;
for (table= tables; table; table= table->next_local)
{
--- 1.194/sql/sql_delete.cc 2006-10-19 16:43:54 +02:00
+++ 1.195/sql/sql_delete.cc 2006-10-19 16:43:54 +02:00
@@ -183,7 +183,7 @@
MYF(MY_FAE | MY_ZEROFILL));
if (!(sortorder= make_unireg_sortorder((ORDER*) order->first,
- &length)) ||
+ &length, NULL)) ||
(table->sort.found_records = filesort(thd, table, sortorder, length,
select, HA_POS_ERROR, 1,
&examined_rows))
--- 1.200/sql/sql_lex.cc 2006-10-19 16:43:54 +02:00
+++ 1.201/sql/sql_lex.cc 2006-10-19 16:43:54 +02:00
@@ -1147,7 +1147,7 @@
initialization is checked for failure.
*/
parent_lex->push_context(&context);
- cond_count= with_wild= 0;
+ cond_count= between_count= with_wild= 0;
conds_processed_with_permanent_arena= 0;
ref_pointer_array= 0;
select_n_having_items= 0;
--- 1.245/sql/sql_lex.h 2006-10-19 16:43:54 +02:00
+++ 1.246/sql/sql_lex.h 2006-10-19 16:43:54 +02:00
@@ -556,7 +556,8 @@
list during split_sum_func
*/
uint select_n_having_items;
- uint cond_count; /* number of arguments of and/or/xor in where/having */
+ uint cond_count; /* number of arguments of and/or/xor in where/having/on */
+ uint between_count; /* number of between predicates in where/having/on */
enum_parsing_place parsing_place; /* where we are parsing expression */
bool with_sum_func; /* sum function indicator */
/*
--- 1.454/sql/sql_select.cc 2006-10-19 16:43:54 +02:00
+++ 1.455/sql/sql_select.cc 2006-10-19 16:43:54 +02:00
@@ -35,14 +35,17 @@
"index_merge"
};
+struct st_sargable_param;
+
static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array);
static bool make_join_statistics(JOIN *join, TABLE_LIST *leaves, COND *conds,
DYNAMIC_ARRAY *keyuse);
static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,
- JOIN_TAB *join_tab,
+ JOIN_TAB *join_tab,
uint tables, COND *conds,
COND_EQUAL *cond_equal,
- table_map table_map, SELECT_LEX *select_lex);
+ table_map table_map, SELECT_LEX *select_lex,
+ st_sargable_param **sargables);
static int sort_keyuse(KEYUSE *a,KEYUSE *b);
static void set_position(JOIN *join,uint index,JOIN_TAB *table,KEYUSE *key);
static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
@@ -1413,7 +1416,8 @@
simple_order= simple_group;
skip_sort_order= 0;
}
- if (order &&
+ if (order &&
+ (order != group_list || !(select_options & SELECT_BIG_RESULT)) &&
(const_tables == tables ||
((simple_order || skip_sort_order) &&
test_if_skip_sort_order(&join_tab[const_tables], order,
@@ -1591,6 +1595,7 @@
{
DBUG_VOID_RETURN;
}
+ sortorder= curr_join->sortorder;
}
thd->proc_info="Copying to group table";
@@ -1803,6 +1808,7 @@
(select_options & OPTION_FOUND_ROWS ?
HA_POS_ERROR : unit->select_limit_cnt)))
DBUG_VOID_RETURN;
+ sortorder= curr_join->sortorder;
if (curr_join->const_tables != curr_join->tables &&
!curr_join->join_tab[curr_join->const_tables].table->sort.io_cache)
{
@@ -2069,6 +2075,19 @@
DBUG_RETURN(HA_POS_ERROR); /* This shouldn't happend */
}
+/*
+ This structure is used to collect info on potentially sargable
+ predicates in order to check whether they become sargable after
+ reading const tables.
+ We form a bitmap of indexes that can be used for sargable predicates.
+ Only such indexes are involved in range analysis.
+*/
+typedef struct st_sargable_param
+{
+ Field *field; /* field against which to check sargability */
+ Item **arg_value; /* values of potential keys for lookups */
+ uint num_values; /* number of values in the above array */
+} SARGABLE_PARAM;
/*
Calculate the best possible join and initialize the join structure
@@ -2091,6 +2110,7 @@
JOIN_TAB *stat,*stat_end,*s,**stat_ref;
KEYUSE *keyuse,*start_keyuse;
table_map outer_join=0;
+ SARGABLE_PARAM *sargables= 0;
JOIN_TAB *stat_vector[MAX_TABLES+1];
DBUG_ENTER("make_join_statistics");
@@ -2223,7 +2243,7 @@
if (conds || outer_join)
if (update_ref_and_keys(join->thd, keyuse_array, stat, join->tables,
conds, join->cond_equal,
- ~outer_join, join->select_lex))
+ ~outer_join, join->select_lex, &sargables))
DBUG_RETURN(1);
/* Read tables with 0 or 1 rows (system tables) */
@@ -2373,6 +2393,26 @@
}
} while (join->const_table_map & found_ref && ref_changed);
+ /*
+ Update info on indexes that can be used for search lookups as
+ reading const tables may has added new sargable predicates.
+ */
+ if (const_count && sargables)
+ {
+ for( ; sargables->field ; sargables++)
+ {
+ Field *field= sargables->field;
+ JOIN_TAB *stat= field->table->reginfo.join_tab;
+ key_map possible_keys= field->key_start;
+ possible_keys.intersect(field->table->keys_in_use_for_query);
+ bool is_const= 1;
+ for (uint i=0; i< sargables->num_values; i++)
+ is_const&= sargables->arg_value[i]->const_item();
+ if (is_const)
+ stat[0].const_keys.merge(possible_keys);
+ }
+ }
+
/* Calc how many (possible) matched records in each table */
for (s=stat ; s < stat_end ; s++)
@@ -2632,6 +2672,7 @@
eq_func True if we used =, <=> or IS NULL
value Value used for comparison with field
usable_tables Tables which can be used for key optimization
+ sargables IN/OUT Array of found sargable candidates
NOTES
If we are doing a NOT NULL comparison on a NOT NULL field in a outer join
@@ -2643,8 +2684,8 @@
static void
add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond,
- Field *field, bool eq_func, Item **value, uint num_values,
- table_map usable_tables)
+ Field *field, bool eq_func, Item **value, uint num_values,
+ table_map usable_tables, SARGABLE_PARAM **sargables)
{
uint exists_optimize= 0;
if (!(field->flags & PART_KEY_FLAG))
@@ -2703,6 +2744,19 @@
}
if (is_const)
stat[0].const_keys.merge(possible_keys);
+ else if (!eq_func)
+ {
+ /*
+ Save info to be able check whether this predicate can be
+ considered as sargable for range analisis after reading const tables.
+ We do not save info about equalities as update_const_equal_items
+ will take care of updating info on keys from sargable equalities.
+ */
+ (*sargables)--;
+ (*sargables)->field= field;
+ (*sargables)->arg_value= value;
+ (*sargables)->num_values= num_values;
+ }
/*
We can't always use indexes when comparing a string index to a
number. cmp_type() is checked to allow compare of dates to numbers.
@@ -2793,6 +2847,7 @@
value Value used for comparison with field
Is NULL for BETWEEN and IN
usable_tables Tables which can be used for key optimization
+ sargables IN/OUT Array of found sargable candidates
NOTES
If field items f1 and f2 belong to the same multiple equality and
@@ -2806,11 +2861,12 @@
add_key_equal_fields(KEY_FIELD **key_fields, uint and_level,
Item_func *cond, Item_field *field_item,
bool eq_func, Item **val,
- uint num_values, table_map usable_tables)
+ uint num_values, table_map usable_tables,
+ SARGABLE_PARAM **sargables)
{
Field *field= field_item->field;
add_key_field(key_fields, and_level, cond, field,
- eq_func, val, num_values, usable_tables);
+ eq_func, val, num_values, usable_tables, sargables);
Item_equal *item_equal= field_item->item_equal;
if (item_equal)
{
@@ -2825,7 +2881,8 @@
if (!field->eq(item->field))
{
add_key_field(key_fields, and_level, cond, item->field,
- eq_func, val, num_values, usable_tables);
+ eq_func, val, num_values, usable_tables,
+ sargables);
}
}
}
@@ -2833,7 +2890,8 @@
static void
add_key_fields(KEY_FIELD **key_fields,uint *and_level,
- COND *cond, table_map usable_tables)
+ COND *cond, table_map usable_tables,
+ SARGABLE_PARAM **sargables)
{
if (cond->type() == Item_func::COND_ITEM)
{
@@ -2844,20 +2902,20 @@
{
Item *item;
while ((item=li++))
- add_key_fields(key_fields,and_level,item,usable_tables);
+ add_key_fields(key_fields,and_level,item,usable_tables,sargables);
for (; org_key_fields != *key_fields ; org_key_fields++)
org_key_fields->level= *and_level;
}
else
{
(*and_level)++;
- add_key_fields(key_fields,and_level,li++,usable_tables);
+ add_key_fields(key_fields,and_level,li++,usable_tables,sargables);
Item *item;
while ((item=li++))
{
KEY_FIELD *start_key_fields= *key_fields;
(*and_level)++;
- add_key_fields(key_fields,and_level,item,usable_tables);
+ add_key_fields(key_fields,and_level,item,usable_tables,sargables);
*key_fields=merge_key_fields(org_key_fields,start_key_fields,
*key_fields,++(*and_level));
}
@@ -2888,9 +2946,9 @@
cond_func->argument_count() != 2);
add_key_equal_fields(key_fields, *and_level, cond_func,
(Item_field*) (cond_func->key_item()->real_item()),
- 0, values,
+ 0, values,
cond_func->argument_count()-1,
- usable_tables);
+ usable_tables, sargables);
}
if (cond_func->functype() == Item_func::BETWEEN)
{
@@ -2904,7 +2962,8 @@
{
field_item= (Item_field *) (cond_func->arguments()[i]->real_item());
add_key_equal_fields(key_fields, *and_level, cond_func,
- field_item, 0, values, 1, usable_tables);
+ field_item, 0, values, 1, usable_tables,
+ sargables);
}
}
}
@@ -2921,7 +2980,8 @@
add_key_equal_fields(key_fields, *and_level, cond_func,
(Item_field*) (cond_func->arguments()[0])->real_item(),
equal_func,
- cond_func->arguments()+1, 1, usable_tables);
+ cond_func->arguments()+1, 1, usable_tables,
+ sargables);
}
if (cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM &&
cond_func->functype() != Item_func::LIKE_FUNC &&
@@ -2930,7 +2990,8 @@
add_key_equal_fields(key_fields, *and_level, cond_func,
(Item_field*) (cond_func->arguments()[1])->real_item(),
equal_func,
- cond_func->arguments(),1,usable_tables);
+ cond_func->arguments(),1,usable_tables,
+ sargables);
}
break;
}
@@ -2945,7 +3006,7 @@
add_key_equal_fields(key_fields, *and_level, cond_func,
(Item_field*) (cond_func->arguments()[0])->real_item(),
cond_func->functype() == Item_func::ISNULL_FUNC,
- &tmp, 1, usable_tables);
+ &tmp, 1, usable_tables, sargables);
}
break;
case Item_func::OPTIMIZE_EQUAL:
@@ -2963,7 +3024,7 @@
while ((item= it++))
{
add_key_field(key_fields, *and_level, cond_func, item->field,
- TRUE, &const_item, 1, usable_tables);
+ TRUE, &const_item, 1, usable_tables, sargables);
}
}
else
@@ -2983,7 +3044,8 @@
if (!field->eq(item->field))
{
add_key_field(key_fields, *and_level, cond_func, field,
- TRUE, (Item **) &item, 1, usable_tables);
+ TRUE, (Item **) &item, 1, usable_tables,
+ sargables);
}
}
it.rewind();
@@ -3134,6 +3196,7 @@
nested_join_table IN Nested join pseudo-table to process
end INOUT End of the key field array
and_level INOUT And-level
+ sargables IN/OUT Array of found sargable candidates
DESCRIPTION
This function populates KEY_FIELD array with entries generated from the
@@ -3157,7 +3220,8 @@
*/
static void add_key_fields_for_nj(TABLE_LIST *nested_join_table,
- KEY_FIELD **end, uint *and_level)
+ KEY_FIELD **end, uint *and_level,
+ SARGABLE_PARAM **sargables)
{
List_iterator<TABLE_LIST> li(nested_join_table->nested_join->join_list);
table_map tables= 0;
@@ -3167,12 +3231,12 @@
while ((table= li++))
{
if (table->nested_join)
- add_key_fields_for_nj(table, end, and_level);
+ add_key_fields_for_nj(table, end, and_level, sargables);
else
if (!table->on_expr)
tables |= table->table->map;
}
- add_key_fields(end, and_level, nested_join_table->on_expr, tables);
+ add_key_fields(end, and_level, nested_join_table->on_expr, tables, sargables);
}
@@ -3187,9 +3251,10 @@
tables Number of tables in join
cond WHERE condition (note that the function analyzes
join_tab[i]->on_expr too)
- normal_tables tables not inner w.r.t some outer join (ones for which
+ normal_tables Tables not inner w.r.t some outer join (ones for which
we can make ref access based the WHERE clause)
select_lex current SELECT
+ sargables OUT Array of found sargable candidates
RETURN
0 - OK
@@ -3198,27 +3263,55 @@
static bool
update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
- uint tables, COND *cond, COND_EQUAL *cond_equal,
- table_map normal_tables, SELECT_LEX *select_lex)
+ uint tables, COND *cond, COND_EQUAL *cond_equal,
+ table_map normal_tables, SELECT_LEX *select_lex,
+ SARGABLE_PARAM **sargables)
{
uint and_level,i,found_eq_constant;
KEY_FIELD *key_fields, *end, *field;
+ uint sz;
uint m= 1;
if (cond_equal && cond_equal->max_members)
m= cond_equal->max_members;
-
- if (!(key_fields=(KEY_FIELD*)
- thd->alloc(sizeof(key_fields[0])*
- (thd->lex->current_select->cond_count+1)*2*m)))
+
+ /*
+ We use the same piece of memory to store both KEY_FIELD
+ and SARGABLE_PARAM structure.
+ KEY_FIELD values are placed at the beginning this memory
+ while SARGABLE_PARAM values are put at the end.
+ All predicates that are used to fill arrays of KEY_FIELD
+ and SARGABLE_PARAM structures have at most 2 arguments
+ except BETWEEN predicates that have 3 arguments and
+ IN predicates.
+ This any predicate if it's not BETWEEN/IN can be used
+ directly to fill at most 2 array elements, either of KEY_FIELD
+ or SARGABLE_PARAM type. For a BETWEEN predicate 3 elements
+ can be filled as this predicate is considered as
+ saragable with respect to each of its argument.
+ An IN predicate can require at most 1 element as currently
+ it is considered as sargable only for its first argument.
+ Multiple equality can add elements that are filled after
+ substitution of field arguments by equal fields. There
+ can be not more than cond_equal->max_members such 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);
+ if (!(key_fields=(KEY_FIELD*) thd->alloc(sz)))
return TRUE; /* purecov: inspected */
and_level= 0;
field= end= key_fields;
+ *sargables= (SARGABLE_PARAM *) key_fields +
+ (sz - sizeof((*sargables)[0].field))/sizeof(SARGABLE_PARAM);
+ /* set a barrier for the array of SARGABLE_PARAM */
+ (*sargables)[0].field= 0;
+
if (my_init_dynamic_array(keyuse,sizeof(KEYUSE),20,64))
return TRUE;
if (cond)
{
- add_key_fields(&end,&and_level,cond,normal_tables);
+ add_key_fields(&end,&and_level,cond,normal_tables,sargables);
for (; field != end ; field++)
{
add_key_part(keyuse,field);
@@ -3241,7 +3334,7 @@
*/
if (*join_tab[i].on_expr_ref)
add_key_fields(&end,&and_level,*join_tab[i].on_expr_ref,
- join_tab[i].table->map);
+ join_tab[i].table->map,sargables);
}
/* Process ON conditions for the nested joins */
@@ -3251,7 +3344,7 @@
while ((table= li++))
{
if (table->nested_join)
- add_key_fields_for_nj(table, &end, &and_level);
+ add_key_fields_for_nj(table, &end, &and_level, sargables);
}
}
@@ -5117,9 +5210,28 @@
JOIN_TAB *join_tab;
DBUG_ENTER("make_simple_join");
- if (!(tableptr=(TABLE**) join->thd->alloc(sizeof(TABLE*))) ||
- !(join_tab=(JOIN_TAB*) join->thd->alloc(sizeof(JOIN_TAB))))
- DBUG_RETURN(TRUE);
+ /*
+ Reuse TABLE * and JOIN_TAB if already allocated by a previous call
+ to this function through JOIN::exec (may happen for sub-queries).
+ */
+ if (!join->table_reexec)
+ {
+ if (!(join->table_reexec= (TABLE**) join->thd->alloc(sizeof(TABLE*))))
+ DBUG_RETURN(TRUE); /* purecov: inspected */
+ if (join->tmp_join)
+ join->tmp_join->table_reexec= join->table_reexec;
+ }
+ if (!join->join_tab_reexec)
+ {
+ if (!(join->join_tab_reexec=
+ (JOIN_TAB*) join->thd->alloc(sizeof(JOIN_TAB))))
+ DBUG_RETURN(TRUE); /* purecov: inspected */
+ if (join->tmp_join)
+ join->tmp_join->join_tab_reexec= join->join_tab_reexec;
+ }
+ tableptr= join->table_reexec;
+ join_tab= join->join_tab_reexec;
+
join->join_tab=join_tab;
join->table=tableptr; tableptr[0]=tmp_table;
join->tables=1;
@@ -7507,7 +7619,22 @@
((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC)
{
Item_equal *item_equal= (Item_equal *) cond;
+ bool contained_const= item_equal->get_const() != NULL;
item_equal->update_const();
+ if (!contained_const && item_equal->get_const())
+ {
+ /* Update keys for range analysis */
+ Item_equal_iterator it(*item_equal);
+ Item_field *item_field;
+ while ((item_field= it++))
+ {
+ Field *field= item_field->field;
+ JOIN_TAB *stat= field->table->reginfo.join_tab;
+ key_map possible_keys= field->key_start;
+ possible_keys.intersect(field->table->keys_in_use_for_query);
+ stat[0].const_keys.merge(possible_keys);
+ }
+ }
}
}
@@ -12213,7 +12340,6 @@
create_sort_index(THD *thd, JOIN *join, ORDER *order,
ha_rows filesort_limit, ha_rows select_limit)
{
- SORT_FIELD *sortorder;
uint length;
ha_rows examined_rows;
TABLE *table;
@@ -12227,11 +12353,18 @@
table= tab->table;
select= tab->select;
- if (test_if_skip_sort_order(tab,order,select_limit,0))
+ /*
+ When there is SQL_BIG_RESULT do not sort using index for GROUP BY,
+ and thus force sorting on disk.
+ */
+ if ((order != join->group_list ||
+ !(join->select_options & SELECT_BIG_RESULT)) &&
+ test_if_skip_sort_order(tab,order,select_limit,0))
DBUG_RETURN(0);
- if (!(sortorder=make_unireg_sortorder(order,&length)))
+ if (!(join->sortorder=
+ make_unireg_sortorder(order,&length,join->sortorder)))
goto err; /* purecov: inspected */
- /* It's not fatal if the following alloc fails */
+
table->sort.io_cache=(IO_CACHE*) my_malloc(sizeof(IO_CACHE),
MYF(MY_WME | MY_ZEROFILL));
table->status=0; // May be wrong if quick_select
@@ -12276,7 +12409,7 @@
if (table->s->tmp_table)
table->file->info(HA_STATUS_VARIABLE); // Get record count
- table->sort.found_records=filesort(thd, table,sortorder, length,
+ table->sort.found_records=filesort(thd, table,join->sortorder, length,
select, filesort_limit, 0,
&examined_rows);
tab->records= table->sort.found_records; // For SQL_CALC_ROWS
@@ -12624,7 +12757,8 @@
}
-SORT_FIELD *make_unireg_sortorder(ORDER *order, uint *length)
+SORT_FIELD *make_unireg_sortorder(ORDER *order, uint *length,
+ SORT_FIELD *sortorder)
{
uint count;
SORT_FIELD *sort,*pos;
@@ -12633,7 +12767,9 @@
count=0;
for (ORDER *tmp = order; tmp; tmp=tmp->next)
count++;
- pos=sort=(SORT_FIELD*) sql_alloc(sizeof(SORT_FIELD)*(count+1));
+ if (!sortorder)
+ sortorder= (SORT_FIELD*) sql_alloc(sizeof(SORT_FIELD)*(count+1));
+ pos=sort=sortorder;
if (!pos)
return 0;
@@ -13751,7 +13887,19 @@
disctinct->group_by optimization
*/
if (select_distinct)
+ {
group_parts+= fields_list.elements;
+ /*
+ If the ORDER clause is specified then it's possible that
+ it also will be optimized, so reserve space for it too
+ */
+ if (order)
+ {
+ ORDER *ord;
+ for (ord= order; ord; ord= ord->next)
+ group_parts++;
+ }
+ }
/* This must use calloc() as rollup_make_fields depends on this */
sum_funcs= (Item_sum**) thd->calloc(sizeof(Item_sum**) * (func_count+1) +
@@ -14234,12 +14382,17 @@
while ((item= it++))
{
ORDER *group_tmp;
+ bool found_in_group= 0;
+
for (group_tmp= group_list; group_tmp; group_tmp= group_tmp->next)
{
if (*group_tmp->item == item)
+ {
item->maybe_null= 1;
+ found_in_group= 1;
+ }
}
- if (item->type() == Item::FUNC_ITEM)
+ if (item->type() == Item::FUNC_ITEM && !found_in_group)
{
bool changed= FALSE;
if (change_group_ref(thd, (Item_func *) item, group_list, &changed))
--- 1.111/sql/sql_select.h 2006-10-19 16:43:54 +02:00
+++ 1.112/sql/sql_select.h 2006-10-19 16:43:54 +02:00
@@ -321,6 +321,18 @@
bool union_part; // this subselect is part of union
bool optimized; // flag to avoid double optimization in EXPLAIN
+ /*
+ storage for caching buffers allocated during query execution.
+ These buffers allocations need to be cached as the thread memory pool is
+ cleared only at the end of the execution of the whole query and not caching
+ allocations that occur in repetition at execution time will result in
+ excessive memory usage.
+ */
+ SORT_FIELD *sortorder; // make_unireg_sortorder()
+ TABLE **table_reexec; // make_simple_join()
+ JOIN_TAB *join_tab_reexec; // make_simple_join()
+ /* end of allocation caching storage */
+
JOIN(THD *thd_arg, List<Item> &fields_arg, ulonglong select_options_arg,
select_result *result_arg)
:fields_list(fields_arg)
@@ -346,6 +358,9 @@
examined_rows= 0;
exec_tmp_table1= 0;
exec_tmp_table2= 0;
+ sortorder= 0;
+ table_reexec= 0;
+ join_tab_reexec= 0;
thd= thd_arg;
sum_funcs= sum_funcs2= 0;
procedure= 0;
--- 1.366/sql/sql_table.cc 2006-10-19 16:43:54 +02:00
+++ 1.367/sql/sql_table.cc 2006-10-19 16:43:54 +02:00
@@ -5151,8 +5151,9 @@
char reg_path[FN_REFLEN+1];
ha_rows copied,deleted;
uint db_create_options, used_fields;
- handlerton *old_db_type, *new_db_type;
+ handlerton *old_db_type, *new_db_type, table_type;
HA_CREATE_INFO *create_info;
+ frm_type_enum frm_type;
uint need_copy_table= 0;
bool no_table_reopen= FALSE, varchar= FALSE;
#ifdef WITH_PARTITION_STORAGE_ENGINE
@@ -5234,6 +5235,51 @@
if (alter_info->tablespace_op != NO_TABLESPACE_OP)
DBUG_RETURN(mysql_discard_or_import_tablespace(thd,table_list,
alter_info->tablespace_op));
+ sprintf(new_name_buff,"%s/%s/%s%s",mysql_data_home, db, table_name, reg_ext);
+ unpack_filename(new_name_buff, new_name_buff);
+ if (lower_case_table_names != 2)
+ my_casedn_str(files_charset_info, new_name_buff);
+ frm_type= mysql_frm_type(thd, new_name_buff, &table_type);
+ /* Rename a view */
+ if (frm_type == FRMTYPE_VIEW && !(alter_info->flags & ~ALTER_RENAME))
+ {
+ /*
+ Avoid problems with a rename on a table that we have locked or
+ if the user is trying to to do this in a transcation context
+ */
+
+ if (thd->locked_tables || thd->active_transaction())
+ {
+ my_message(ER_LOCK_OR_ACTIVE_TRANSACTION,
+ ER(ER_LOCK_OR_ACTIVE_TRANSACTION), MYF(0));
+ DBUG_RETURN(1);
+ }
+
+ if (wait_if_global_read_lock(thd,0,1))
+ DBUG_RETURN(1);
+ VOID(pthread_mutex_lock(&LOCK_open));
+ if (lock_table_names(thd, table_list))
+ goto view_err;
+
+ error=0;
+ if (!do_rename(thd, table_list, new_db, new_name, new_name, 1))
+ {
+ if (mysql_bin_log.is_open())
+ {
+ thd->clear_error();
+ Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE);
+ mysql_bin_log.write(&qinfo);
+ }
+ send_ok(thd);
+ }
+
+ unlock_table_names(thd, table_list, (TABLE_LIST*) 0);
+
+view_err:
+ pthread_mutex_unlock(&LOCK_open);
+ start_waiting_global_read_lock(thd);
+ DBUG_RETURN(error);
+ }
if (!(table=open_ltable(thd,table_list,TL_WRITE_ALLOW_READ)))
DBUG_RETURN(TRUE);
table->use_all_columns();
@@ -6515,7 +6561,7 @@
if (thd->lex->select_lex.setup_ref_array(thd, order_num) ||
setup_order(thd, thd->lex->select_lex.ref_pointer_array,
&tables, fields, all_fields, order) ||
- !(sortorder=make_unireg_sortorder(order, &length)) ||
+ !(sortorder=make_unireg_sortorder(order, &length, NULL)) ||
(from->sort.found_records = filesort(thd, from, sortorder, length,
(SQL_SELECT *) 0, HA_POS_ERROR, 1,
&examined_rows)) ==
--- 1.210/sql/sql_update.cc 2006-10-19 16:43:54 +02:00
+++ 1.211/sql/sql_update.cc 2006-10-19 16:43:54 +02:00
@@ -327,7 +327,7 @@
table->sort.io_cache = (IO_CACHE *) my_malloc(sizeof(IO_CACHE),
MYF(MY_FAE | MY_ZEROFILL));
- if (!(sortorder=make_unireg_sortorder(order, &length)) ||
+ if (!(sortorder=make_unireg_sortorder(order, &length, NULL)) ||
(table->sort.found_records= filesort(thd, table, sortorder, length,
select, limit, 1,
&examined_rows))
--- 1.35/mysql-test/r/olap.result 2006-10-19 16:43:55 +02:00
+++ 1.36/mysql-test/r/olap.result 2006-10-19 16:43:55 +02:00
@@ -592,6 +592,21 @@
NULL 2
a 1
drop table t1;
+create table t1 (a varchar(22) not null , b int);
+insert into t1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10);
+select left(a,10), a, sum(b) from t1 group by 1,2 with rollup;
+left(a,10) a sum(b)
+2006-07-01 2006-07-01 21:30 1
+2006-07-01 2006-07-01 23:30 10
+2006-07-01 NULL 11
+NULL NULL 11
+select left(a,10) x, a, sum(b) from t1 group by x,a with rollup;
+x a sum(b)
+2006-07-01 2006-07-01 21:30 1
+2006-07-01 2006-07-01 23:30 10
+2006-07-01 NULL 11
+NULL NULL 11
+drop table t1;
CREATE TABLE t1(id int, type char(1));
INSERT INTO t1 VALUES
(1,"A"),(2,"C"),(3,"A"),(4,"A"),(5,"B"),
--- 1.12/mysql-test/r/innodb_mysql.result 2006-10-19 16:43:55 +02:00
+++ 1.13/mysql-test/r/innodb_mysql.result 2006-10-19 16:43:55 +02:00
@@ -290,6 +290,22 @@
1 SIMPLE t2 index NULL fkey 5 NULL 5 Using index
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where
DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
+INSERT INTO t1 VALUES ( 1 , 1 , 1);
+INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1;
+EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 5 NULL 128
+EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort
+DROP TABLE t1;
CREATE TABLE t1 (a int, b int);
insert into t1 values (1,1),(1,2);
CREATE TABLE t2 (primary key (a)) select * from t1;
--- 1.3.4.4/mysql-test/t/innodb_mysql.test 2006-10-19 16:43:55 +02:00
+++ 1.13/mysql-test/include/mix1.inc 2006-10-19 16:43:55 +02:00
@@ -1,9 +1,44 @@
--- source include/have_innodb.inc
+# include/mix1.inc
+#
+# The variables
+# $engine_type -- storage engine to be tested
+# $other_engine_type -- storage engine <> $engine_type
+# $other_engine_type must point to an all
+# time available storage engine
+# 2006-08 MySQL 5.1 MyISAM and MEMORY only
+# $test_foreign_keys -- 0, skip foreign key tests
+# -- 1, do not skip foreign key tests
+# have to be set before sourcing this script.
+#
+# Note: The comments/expectations refer to InnoDB.
+# They might be not valid for other storage engines.
+#
+# Last update:
+# 2006-08-15 ML refactoring of t/innodb_mysql.test
+# - shift main code of t/innodb_mysql.test to include/mix1.inc
+# - replace hardcoded assignment of storage engine by
+# use of $engine_type and $other_engine_type variables
+# - remove redundant replay testcase of
+# Bug#12882 min/max inconsistent on empty table
+# - corrected analyze table t1; to analyze table t4;
+# Much older versions of this test show that the table
+# where just some indexes have been created must be used.
+#
+
+eval SET SESSION STORAGE_ENGINE = $engine_type;
--disable_warnings
-drop table if exists t1,t2;
+drop table if exists t1,t2,t1m,t1i,t2m,t2i,t4;
--enable_warnings
+#
+# Bug#17530: Incorrect key truncation on table creation caused server crash.
+#
+create table t1(f1 varchar(800) binary not null, key(f1))
+ character set utf8 collate utf8_general_ci;
+insert into t1 values('aaa');
+drop table t1;
+
# BUG#16798: Uninitialized row buffer reads in ref-or-null optimizer
# (repeatable only w/innodb).
create table t1 (
@@ -11,8 +46,8 @@
org_id int(11) default null,
unique key contacts$c_id (c_id),
key contacts$org_id (org_id)
-) engine=innodb;
-insert into t1 values
+);
+insert into t1 values
(2,null),(120,null),(141,null),(218,7), (128,1),
(151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3),
(246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4);
@@ -35,7 +70,7 @@
unique key t2$slai_id (slai_id),
key t2$owner_id (owner_id),
key t2$sla_id (sla_id)
-) engine=innodb;
+);
insert into t2(slai_id, owner_tbl, owner_id, sla_id) values
(1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7),
(8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12);
@@ -43,17 +78,17 @@
flush tables;
select si.slai_id
from t1 c join t2 si on
- ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
- ( si.owner_tbl = 2 and si.owner_id = c.c_id))
-where
+ ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
+ ( si.owner_tbl = 2 and si.owner_id = c.c_id))
+where
c.c_id = 218 and expiredate is null;
-
+
select * from t1 where org_id is null;
select si.slai_id
from t1 c join t2 si on
- ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
- ( si.owner_tbl = 2 and si.owner_id = c.c_id))
-where
+ ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
+ ( si.owner_tbl = 2 and si.owner_id = c.c_id))
+where
c.c_id = 218 and expiredate is null;
drop table t1, t2;
@@ -62,14 +97,14 @@
# Bug#17212: results not sorted correctly by ORDER BY when using index
# (repeatable only w/innodb because of index props)
#
-CREATE TABLE t1 (a int, b int, KEY b (b)) Engine=InnoDB;
-CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b)) Engine=InnoDB;
-CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a),
- UNIQUE KEY b (b,c), KEY a (a,b,c)) Engine=InnoDB;
+CREATE TABLE t1 (a int, b int, KEY b (b));
+CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b));
+CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a),
+ UNIQUE KEY b (b,c), KEY a (a,b,c));
INSERT INTO t1 VALUES (1, 1);
-INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
-INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
+INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
+INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
INSERT INTO t2 SELECT a + 1, b FROM t2;
@@ -81,13 +116,13 @@
# demonstrate a problem when a must-use-sort table flag
# (sort_by_table=1) is being neglected.
-SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
- t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
+SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
+ t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
ORDER BY t1.b LIMIT 2;
# demonstrate the problem described in the bug report
-SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
- t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
+SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
+ t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
ORDER BY t1.b LIMIT 5;
DROP TABLE t1, t2, t3;
@@ -105,9 +140,9 @@
`id4` INT NOT NULL,
UNIQUE (`id2`,`id4`),
KEY (`id1`)
-) ENGINE=InnoDB;
+);
-INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES
+INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES
(1,1,1,0),
(1,1,2,1),
(5,1,2,2),
@@ -117,43 +152,21 @@
SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2);
DROP TABLE t1, t2;
+
#
# Bug #12882 min/max inconsistent on empty table
#
--disable_warnings
-create table t1m (a int) engine=myisam;
-create table t1i (a int) engine=innodb;
-create table t2m (a int) engine=myisam;
-create table t2i (a int) engine=innodb;
+eval create table t1m (a int) engine = $other_engine_type;
+create table t1i (a int);
+eval create table t2m (a int) engine = $other_engine_type;
+create table t2i (a int);
--enable_warnings
insert into t2m values (5);
insert into t2i values (5);
-# test with MyISAM
-select min(a) from t1m;
-select min(7) from t1m;
-select min(7) from DUAL;
-explain select min(7) from t2m join t1m;
-select min(7) from t2m join t1m;
-
-select max(a) from t1m;
-select max(7) from t1m;
-select max(7) from DUAL;
-explain select max(7) from t2m join t1m;
-select max(7) from t2m join t1m;
-
-select 1, min(a) from t1m where a=99;
-select 1, min(a) from t1m where 1=99;
-select 1, min(1) from t1m where a=99;
-select 1, min(1) from t1m where 1=99;
-
-select 1, max(a) from t1m where a=99;
-select 1, max(a) from t1m where 1=99;
-select 1, max(1) from t1m where a=99;
-select 1, max(1) from t1m where 1=99;
-
-# test with InnoDB
+# test with $engine_type
select min(a) from t1i;
select min(7) from t1i;
select min(7) from DUAL;
@@ -176,7 +189,7 @@
select 1, max(1) from t1i where a=99;
select 1, max(1) from t1i where 1=99;
-# mixed MyISAM/InnoDB test
+# mixed $engine_type/$other_engine_type test
explain select count(*), min(7), max(7) from t1m, t1i;
select count(*), min(7), max(7) from t1m, t1i;
@@ -189,13 +202,13 @@
drop table t1m, t1i, t2m, t2i;
#
-# Bug #12672: primary key implcitly included in every innodb index
+# Bug #12882: primary key implcitly included in every innodb index
# (was part of group_min_max.test)
#
-create table t1 (
+eval create table t1 (
a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
-);
+) ENGINE = $other_engine_type;
insert into t1 (a1, a2, b, c, d) values
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
@@ -233,49 +246,49 @@
--disable_warnings
create table t4 (
pk_col int auto_increment primary key, a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
-) engine=innodb;
+);
--enable_warnings
insert into t4 (a1, a2, b, c, d, dummy) select * from t1;
create index idx12672_0 on t4 (a1);
create index idx12672_1 on t4 (a1,a2,b,c);
create index idx12672_2 on t4 (a1,a2,b);
-analyze table t1;
+analyze table t4;
select distinct a1 from t4 where pk_col not in (1,2,3,4);
drop table t1,t4;
#
-# Bug #6142: a problem with the empty innodb table
+# Bug #6142: a problem with the empty innodb table
# (was part of group_min_max.test)
#
--disable_warnings
create table t1 (
a varchar(30), b varchar(30), primary key(a), key(b)
-) engine=innodb;
+);
--enable_warnings
select distinct a from t1;
drop table t1;
#
-# Bug #9798: group by with rollup
+# Bug #9798: group by with rollup
# (was part of group_min_max.test)
#
--disable_warnings
-create table t1(a int, key(a)) engine=innodb;
+create table t1(a int, key(a));
--enable_warnings
insert into t1 values(1);
select a, count(a) from t1 group by a with rollup;
drop table t1;
#
-# Bug #13293 Wrongly used index results in endless loop.
+# Bug #13293 Wrongly used index results in endless loop.
# (was part of group_min_max.test)
#
-create table t1 (f1 int, f2 char(1), primary key(f1,f2)) engine=innodb;
+create table t1 (f1 int, f2 char(1), primary key(f1,f2));
insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d");
alter table t1 drop primary key, add primary key (f2, f1);
explain select distinct f1 a, f1 b from t1;
@@ -284,21 +297,26 @@
#
# Test for bug #17164: ORed FALSE blocked conversion of outer join into join
-#
+#
CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
- INDEX (name)) ENGINE=InnoDB;
-CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11),
- FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB;
+ INDEX (name));
+CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11));
+# CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11),
+# FOREIGN KEY (fkey) REFERENCES t2(id));
+if ($test_foreign_keys)
+{
+ ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id);
+}
INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
EXPLAIN
-SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
+SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%';
EXPLAIN
-SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
+SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%' OR FALSE;
DROP TABLE t1,t2;
@@ -320,3 +338,111 @@
EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
DROP TABLE t1;
+
+#
+# Test of behaviour with CREATE ... SELECT
+#
+
+CREATE TABLE t1 (a int, b int);
+insert into t1 values (1,1),(1,2);
+--error 1062
+CREATE TABLE t2 (primary key (a)) select * from t1;
+# This should give warning
+drop table if exists t2;
+--error 1062
+CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
+# This should give warning
+drop table if exists t2;
+CREATE TABLE t2 (a int, b int, primary key (a));
+BEGIN;
+INSERT INTO t2 values(100,100);
+--error 1062
+CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+ROLLBACK;
+SELECT * from t2;
+TRUNCATE table t2;
+--error 1062
+INSERT INTO t2 select * from t1;
+SELECT * from t2;
+drop table t2;
+
+CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
+BEGIN;
+INSERT INTO t2 values(100,100);
+--error 1062
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+COMMIT;
+BEGIN;
+INSERT INTO t2 values(101,101);
+--error 1062
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+ROLLBACK;
+SELECT * from t2;
+TRUNCATE table t2;
+--error 1062
+INSERT INTO t2 select * from t1;
+SELECT * from t2;
+drop table t1,t2;
+
+#
+# Bug#17530: Incorrect key truncation on table creation caused server crash.
+#
+create table t1(f1 varchar(800) binary not null, key(f1))
+ character set utf8 collate utf8_general_ci;
+insert into t1 values('aaa');
+drop table t1;
+
+# Fix for BUG#19243 "wrong LAST_INSERT_ID() after ON DUPLICATE KEY
+# UPDATE": if the row is updated, it's like a regular UPDATE:
+# LAST_INSERT_ID() is not affected.
+CREATE TABLE `t2` (
+ `k` int(11) NOT NULL auto_increment,
+ `a` int(11) default NULL,
+ `c` int(11) default NULL,
+ PRIMARY KEY (`k`),
+ UNIQUE KEY `idx_1` (`a`)
+);
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+insert into t2 ( a ) values ( 7 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+select * from t2;
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+# test again when last_insert_id() is 0 initially
+select last_insert_id(0);
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+select * from t2;
+
+# Test of LAST_INSERT_ID() when autogenerated will fail:
+# last_insert_id() should not change
+insert ignore into t2 values (null,6,1),(10,8,1);
+select last_insert_id();
+# First and second autogenerated will fail, last_insert_id() should
+# point to third
+insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
+select last_insert_id();
+select * from t2;
+
+# Test of the workaround which enables people to know the id of the
+# updated row in INSERT ON DUPLICATE KEY UPDATE, by using
+# LAST_INSERT_ID(autoinc_col) in the UPDATE clause.
+
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1, k=last_insert_id(k);
+select last_insert_id();
+select * from t2;
+
+drop table t2;
--- 1.154/mysql-test/r/subselect.result 2006-10-19 16:43:55 +02:00
+++ 1.155/mysql-test/r/subselect.result 2006-10-19 16:43:55 +02:00
@@ -363,12 +363,12 @@
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t8 ref PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index
-4 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 100.00 Using where; Using index
-2 SUBQUERY t8 ref PRIMARY PRIMARY 37 const 1 100.00 Using where
-3 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 100.00 Using where; Using index
+1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index
+4 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index
+2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00
+3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index
Warnings:
-Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` AS `email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select `test`.`t8`.`pseudo` AS `pseudo` from `test`.`t8` where (`test`.`t8`.`pseudo` = _latin1'joce')))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select `test`.`t8`.`pseudo` AS `pseudo` from `test`.`t8` where (`test`.`t8`.`pseudo` = _latin1'joce')))
+Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` AS `email` from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
t8 WHERE pseudo='joce');
ERROR 21000: Operand should contain 1 column(s)
@@ -3457,6 +3457,32 @@
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
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 VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
+insert into t1 (a) values (FLOOR(rand() * 100));
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+SELECT a,
+(SELECT REPEAT(' ',250) FROM t1 i1
+WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a
+FROM t1 ORDER BY a LIMIT 5;
+a a
+0 NULL
+0 NULL
+0 NULL
+0 NULL
+0 NULL
DROP TABLE t1;
CREATE TABLE t1 (a INT, b INT);
CREATE TABLE t2 (a INT);
--- 1.167/mysql-test/t/view.test 2006-10-19 16:43:55 +02:00
+++ 1.168/mysql-test/t/view.test 2006-10-19 16:43:55 +02:00
@@ -2856,6 +2856,7 @@
DROP VIEW v1;
DROP TABLE t1;
+#
# Bug #5505: Wrong error message on INSERT into a view
#
create table t1 (s1 int);
--- 1.75/mysql-test/r/group_by.result 2006-10-19 16:43:55 +02:00
+++ 1.76/mysql-test/r/group_by.result 2006-10-19 16:43:55 +02:00
@@ -303,10 +303,10 @@
1 1
explain select sql_big_result score,count(*) from t1 group by score desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL score 3 NULL 8 Using index
+1 SIMPLE t1 index NULL score 3 NULL 8 Using index; Using filesort
explain select sql_big_result score,count(*) from t1 group by score desc order by null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL score 3 NULL 8 Using index
+1 SIMPLE t1 index NULL score 3 NULL 8 Using index; Using filesort
select sql_big_result score,count(*) from t1 group by score desc;
score count(*)
3 5
@@ -775,6 +775,55 @@
max(f1)+1
3
drop table t1;
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT a FROM t1 GROUP BY 'a';
+a
+1
+SELECT a FROM t1 GROUP BY "a";
+a
+1
+SELECT a FROM t1 GROUP BY `a`;
+a
+1
+2
+set sql_mode=ANSI_QUOTES;
+SELECT a FROM t1 GROUP BY "a";
+a
+1
+2
+SELECT a FROM t1 GROUP BY 'a';
+a
+1
+SELECT a FROM t1 GROUP BY `a`;
+a
+1
+2
+set sql_mode='';
+SELECT a FROM t1 HAVING 'a' > 1;
+a
+Warnings:
+Warning 1292 Truncated incorrect DOUBLE value: 'a'
+SELECT a FROM t1 HAVING "a" > 1;
+a
+Warnings:
+Warning 1292 Truncated incorrect DOUBLE value: 'a'
+SELECT a FROM t1 HAVING `a` > 1;
+a
+2
+SELECT a FROM t1 ORDER BY 'a' DESC;
+a
+1
+2
+SELECT a FROM t1 ORDER BY "a" DESC;
+a
+1
+2
+SELECT a FROM t1 ORDER BY `a` DESC;
+a
+2
+1
+DROP TABLE t1;
create table t1 (c1 char(3), c2 char(3));
create table t2 (c3 char(3), c4 char(3));
insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
@@ -821,6 +870,69 @@
68 France France
DROP VIEW v1;
DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1;
+INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1;
+INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1;
+INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1;
+INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1;
+INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1;
+INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1;
+SELECT MIN(b), MAX(b) from t1;
+MIN(b) MAX(b)
+0 19
+EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 5 NULL 128 Using index
+EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 5 NULL 128 Using index; Using filesort
+SELECT b, sum(1) FROM t1 GROUP BY b;
+b sum(1)
+0 6
+1 7
+2 7
+3 7
+4 7
+5 7
+6 7
+7 7
+8 7
+9 6
+10 6
+11 6
+12 6
+13 6
+14 6
+15 6
+16 6
+17 6
+18 6
+19 6
+SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
+b sum(1)
+0 6
+1 7
+2 7
+3 7
+4 7
+5 7
+6 7
+7 7
+8 7
+9 6
+10 6
+11 6
+12 6
+13 6
+14 6
+15 6
+16 6
+17 6
+18 6
+19 6
+DROP TABLE t1;
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;
--- 1.182/mysql-test/r/innodb.result 2006-10-19 16:43:55 +02:00
+++ 1.183/mysql-test/r/innodb.result 2006-10-19 16:43:55 +02:00
@@ -2063,15 +2063,15 @@
select sql_big_result v,count(c) from t1 group by v limit 10;
v count(c)
a 1
-a 10
-b 10
-c 10
-d 10
-e 10
-f 10
-g 10
+a 10
+b 10
+c 10
+d 10
+e 10
+f 10
+g 10
h 10
-i 10
+i 10
select c,count(*) from t1 group by c limit 10;
c count(*)
a 1
--- 1.58/mysql-test/r/merge.result 2006-10-19 16:43:55 +02:00
+++ 1.59/mysql-test/r/merge.result 2006-10-19 16:43:55 +02:00
@@ -629,7 +629,7 @@
EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
AND file_code = '0000000115' LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref PRIMARY,files PRIMARY 35 const,const 1 Using where
+1 SIMPLE t2 const PRIMARY,files PRIMARY 35 const,const 1
DROP TABLE t2, t1;
create table t1 (x int, y int, index xy(x, y));
create table t2 (x int, y int, index xy(x, y));
--- 1.137/mysql-test/r/select.result 2006-10-19 16:43:55 +02:00
+++ 1.138/mysql-test/r/select.result 2006-10-19 16:43:55 +02:00
@@ -3517,3 +3517,97 @@
2 NULL NULL NULL 2 40
2 NULL NULL NULL 2 50
DROP TABLE t1,t2,t3;
+create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int,
+c7 int, c8 int, c9 int, fulltext key (`c1`));
+select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8
+from t1 where c9=1 order by c2, c2;
+match (`c1`) against ('z') c2 c3 c4 c5 c6 c7 c8
+drop table t1;
+CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16));
+CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10));
+INSERT INTO t1 VALUES
+('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'),
+('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff');
+INSERT INTO t2 VALUES
+('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'),
+('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'),
+('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'),
+('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h');
+EXPLAIN SELECT t2.*
+FROM t1 JOIN t2 ON t2.fk=t1.pk
+WHERE t2.fk < 'c' AND t2.pk=t1.fk;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
+EXPLAIN SELECT t2.*
+FROM t1 JOIN t2 ON t2.fk=t1.pk
+WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
+EXPLAIN SELECT t2.*
+FROM t1 JOIN t2 ON t2.fk=t1.pk
+WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
+CREATE TABLE t2 (a int, b varchar(20) NOT NULL,
+PRIMARY KEY (a), UNIQUE KEY (b));
+INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
+INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
+EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t2 const b b 22 const 1 Using index
+DROP TABLE t1,t2;
+CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
+CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
+CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c));
+INSERT INTO t1 VALUES
+(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79),
+(6,63,67), (5,55,58), (3,38,39), (8,81,89);
+INSERT INTO t2 VALUES
+(21,210), (41,410), (82,820), (83,830), (84,840),
+(65,650), (51,510), (37,370), (94,940), (76,760),
+(22,220), (33,330), (40,400), (95,950), (38,380),
+(67,670), (88,880), (57,570), (96,960), (97,970);
+INSERT INTO t3 VALUES
+(210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'),
+(440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'),
+(230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'),
+(450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff');
+EXPLAIN
+SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
+WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
+t3.a=t2.a AND t3.c IN ('bb','ee');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t2 range si si 5 NULL 4 Using where
+1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+EXPLAIN
+SELECT t3.a FROM t1,t2,t3
+WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
+t3.a=t2.a AND t3.c IN ('bb','ee') ;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t2 range si,ai si 5 NULL 4 Using where
+1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+EXPLAIN
+SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
+WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
+t3.c IN ('bb','ee');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t2 range si si 5 NULL 2 Using where
+1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+EXPLAIN
+SELECT t3.a FROM t1,t2,t3
+WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
+t3.c IN ('bb','ee');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t2 range si,ai si 5 NULL 2 Using where
+1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+DROP TABLE t1,t2,t3;
--- 1.43/mysql-test/r/type_decimal.result 2006-10-19 16:43:55 +02:00
+++ 1.44/mysql-test/r/type_decimal.result 2006-10-19 16:43:55 +02:00
@@ -779,3 +779,14 @@
f1
40
drop table t1;
+create table t1 as
+select from_days(s) as date,t
+from (select 1 as s,'t' as t union select null, null ) as sub1;
+select group_concat(t) from t1 group by week(date)/10;
+group_concat(t)
+t
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '0000-00-00'
+Warning 1292 Truncated incorrect datetime value: '0000-00-00'
+Warning 1292 Truncated incorrect datetime value: '0000-00-00'
+drop table t1;
--- 1.60/mysql-test/t/group_by.test 2006-10-19 16:43:55 +02:00
+++ 1.61/mysql-test/t/group_by.test 2006-10-19 16:43:55 +02:00
@@ -609,6 +609,30 @@
select sql_buffer_result max(f1)+1 from t1;
drop table t1;
+#
+# BUG#14019-4.1-opt
+#
+CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1),(2);
+
+SELECT a FROM t1 GROUP BY 'a';
+SELECT a FROM t1 GROUP BY "a";
+SELECT a FROM t1 GROUP BY `a`;
+
+set sql_mode=ANSI_QUOTES;
+SELECT a FROM t1 GROUP BY "a";
+SELECT a FROM t1 GROUP BY 'a';
+SELECT a FROM t1 GROUP BY `a`;
+set sql_mode='';
+
+SELECT a FROM t1 HAVING 'a' > 1;
+SELECT a FROM t1 HAVING "a" > 1;
+SELECT a FROM t1 HAVING `a` > 1;
+
+SELECT a FROM t1 ORDER BY 'a' DESC;
+SELECT a FROM t1 ORDER BY "a" DESC;
+SELECT a FROM t1 ORDER BY `a` DESC;
+DROP TABLE t1;
+
# End of 4.1 tests
#
@@ -655,6 +679,28 @@
DROP VIEW v1;
DROP TABLE t1,t2;
+
+#
+# Bug#22781: SQL_BIG_RESULT fails to influence sort plan
+#
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));
+
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1;
+INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1;
+INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1;
+INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1;
+INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1;
+INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1;
+INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1;
+
+SELECT MIN(b), MAX(b) from t1;
+
+EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;
+EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
+SELECT b, sum(1) FROM t1 GROUP BY b;
+SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
+DROP TABLE t1;
#
# Bug #21174: Index degrades sort performance and
--- 1.109/mysql-test/t/select.test 2006-10-19 16:43:55 +02:00
+++ 1.110/mysql-test/t/select.test 2006-10-19 16:43:55 +02:00
@@ -2997,5 +2997,99 @@
SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
WHERE t1.id=2;
+DROP TABLE t1,t2,t3;
+
+#
+# Bug#20503: Server crash due to the ORDER clause isn't taken into account
+# while space allocation
+#
+create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int,
+c7 int, c8 int, c9 int, fulltext key (`c1`));
+select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8
+ from t1 where c9=1 order by c2, c2;
+drop table t1;
+
+#
+# Bug #22735: no equality propagation for BETWEEN and IN with STRING arguments
+#
+
+CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16));
+CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10));
+
+INSERT INTO t1 VALUES
+ ('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'),
+ ('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff');
+INSERT INTO t2 VALUES
+ ('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'),
+ ('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'),
+ ('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'),
+ ('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h');
+
+EXPLAIN SELECT t2.*
+ FROM t1 JOIN t2 ON t2.fk=t1.pk
+ WHERE t2.fk < 'c' AND t2.pk=t1.fk;
+EXPLAIN SELECT t2.*
+ FROM t1 JOIN t2 ON t2.fk=t1.pk
+ WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;
+EXPLAIN SELECT t2.*
+ FROM t1 JOIN t2 ON t2.fk=t1.pk
+ WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;
+
+DROP TABLE t1,t2;
+
+#
+# Bug #22367: Optimizer uses ref join type instead of eq_ref for simple
+# join on strings
+#
+CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
+CREATE TABLE t2 (a int, b varchar(20) NOT NULL,
+ PRIMARY KEY (a), UNIQUE KEY (b));
+INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
+INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
+
+EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
+
+DROP TABLE t1,t2;
+
+#
+# Bug #19579: predicates that become sargable after reading const tables
+# are not taken into account by optimizer
+#
+
+CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
+CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
+CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c));
+
+INSERT INTO t1 VALUES
+ (1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79),
+ (6,63,67), (5,55,58), (3,38,39), (8,81,89);
+INSERT INTO t2 VALUES
+ (21,210), (41,410), (82,820), (83,830), (84,840),
+ (65,650), (51,510), (37,370), (94,940), (76,760),
+ (22,220), (33,330), (40,400), (95,950), (38,380),
+ (67,670), (88,880), (57,570), (96,960), (97,970);
+INSERT INTO t3 VALUES
+ (210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'),
+ (440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'),
+ (230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'),
+ (450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff');
+
+EXPLAIN
+SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
+ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
+ t3.a=t2.a AND t3.c IN ('bb','ee');
+EXPLAIN
+SELECT t3.a FROM t1,t2,t3
+ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
+ t3.a=t2.a AND t3.c IN ('bb','ee') ;
+
+EXPLAIN
+SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
+ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
+ t3.c IN ('bb','ee');
+EXPLAIN
+SELECT t3.a FROM t1,t2,t3
+ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
+ t3.c IN ('bb','ee');
DROP TABLE t1,t2,t3;
--- 1.37/sql/sql_rename.cc 2006-10-19 16:43:55 +02:00
+++ 1.38/sql/sql_rename.cc 2006-10-19 16:43:55 +02:00
@@ -126,95 +126,141 @@
/*
- Rename all tables in list; Return pointer to wrong entry if something goes
- wrong. Note that the table_list may be empty!
+ Rename a single table or a view
+
+ SYNPOSIS
+ do_rename()
+ thd Thread handle
+ ren_table A table/view to be renamed
+ new_db The database to which the table to be moved to
+ new_table_name The new table/view name
+ new_table_alias The new table/view alias
+ skip_error Whether to skip error
+
+ DESCRIPTION
+ Rename a single table or a view.
+
+ RETURN
+ false Ok
+ true rename failed
*/
-static TABLE_LIST *
-rename_tables(THD *thd, TABLE_LIST *table_list, bool skip_error)
+bool
+do_rename(THD *thd, TABLE_LIST *ren_table, char *new_db, char *new_table_name,
+ char *new_table_alias, bool skip_error)
{
- TABLE_LIST *ren_table,*new_table;
+ int rc= 1;
+ char name[FN_REFLEN];
+ const char *new_alias, *old_alias;
frm_type_enum frm_type;
enum legacy_db_type table_type;
- DBUG_ENTER("rename_tables");
+ DBUG_ENTER("do_rename");
- for (ren_table= table_list; ren_table; ren_table= new_table->next_local)
+ if (lower_case_table_names == 2)
{
- int rc= 1;
- char name[FN_REFLEN];
- const char *new_alias, *old_alias;
-
- new_table= ren_table->next_local;
- if (lower_case_table_names == 2)
- {
- old_alias= ren_table->alias;
- new_alias= new_table->alias;
- }
- else
- {
- old_alias= ren_table->table_name;
- new_alias= new_table->table_name;
- }
- build_table_filename(name, sizeof(name),
- new_table->db, new_alias, reg_ext, 0);
- if (!access(name,F_OK))
- {
- my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_alias);
- DBUG_RETURN(ren_table); // This can't be skipped
- }
- build_table_filename(name, sizeof(name),
- ren_table->db, old_alias, reg_ext, 0);
+ old_alias= ren_table->alias;
+ new_alias= new_table_alias;
+ }
+ else
+ {
+ old_alias= ren_table->table_name;
+ new_alias= new_table_table_name;
+ }
+ build_table_filename(name, sizeof(name),
+ new_db, new_alias, reg_ext, 0);
+ if (!access(name,F_OK))
+ {
+ my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_alias);
+ DBUG_RETURN(1); // This can't be skipped
+ }
+ build_table_filename(name, sizeof(name),
+ ren_table->db, old_alias, reg_ext, 0);
- frm_type= mysql_frm_type(thd, name, &table_type);
- switch (frm_type)
- {
- case FRMTYPE_TABLE:
+ frm_type= mysql_frm_type(thd, name, &table_type);
+ switch (frm_type)
+ {
+ case FRMTYPE_TABLE:
{
- if (table_type == DB_TYPE_UNKNOWN)
- my_error(ER_FILE_NOT_FOUND, MYF(0), name, my_errno);
- else
+ if (!(rc= mysql_rename_table(table_type, ren_table->db, old_alias,
+ new_db, new_alias)))
{
- if (!(rc= mysql_rename_table(ha_resolve_by_legacy_type(thd,
- table_type),
- ren_table->db, old_alias,
- new_table->db, new_alias, 0)))
+ if ((rc= Table_triggers_list::change_table_name(thd, ren_table->db,
+ old_alias,
+ new_db,
+ new_alias)))
{
- if ((rc= Table_triggers_list::change_table_name(thd, ren_table->db,
- old_alias,
- new_table->db,
- new_alias)))
- {
- /*
- We've succeeded in renaming table's .frm and in updating
- corresponding handler data, but have failed to update table's
- triggers appropriately. So let us revert operations on .frm
- and handler's data and report about failure to rename table.
- */
- (void) mysql_rename_table(ha_resolve_by_legacy_type(thd,
- table_type),
- new_table->db, new_alias,
- ren_table->db, old_alias, 0);
- }
+ /*
+ We've succeeded in renaming table's .frm and in updating
+ corresponding handler data, but have failed to update table's
+ triggers appropriately. So let us revert operations on .frm
+ and handler's data and report about failure to rename table.
+ */
+ (void) mysql_rename_table(ha_resolve_by_legacy_type(thd,
+ table_type),
+ new_db, new_alias,
+ ren_table->db, old_alias, 0);
}
}
- break;
}
- case FRMTYPE_VIEW:
- /* change of schema is not allowed */
- if (strcmp(ren_table->db, new_table->db))
- my_error(ER_FORBID_SCHEMA_CHANGE, MYF(0), ren_table->db,
- new_table->db);
- else
- rc= mysql_rename_view(thd, new_alias, ren_table);
- break;
- default:
- DBUG_ASSERT(0); // should never happen
- case FRMTYPE_ERROR:
- my_error(ER_FILE_NOT_FOUND, MYF(0), name, my_errno);
- break;
+ break;
}
- if (rc && !skip_error)
+ case FRMTYPE_VIEW:
+ /* change of schema is not allowed */
+ if (strcmp(ren_table->db, new_db))
+ my_error(ER_FORBID_SCHEMA_CHANGE, MYF(0), ren_table->db,
+ new_db);
+ else
+ rc= mysql_rename_view(thd, new_alias, ren_table);
+ break;
+ default:
+ DBUG_ASSERT(0); // should never happen
+ case FRMTYPE_ERROR:
+ my_error(ER_FILE_NOT_FOUND, MYF(0), name, my_errno);
+ break;
+ }
+ if (rc && !skip_error)
+ DBUG_RETURN(1);
+
+ DBUG_RETURN(0);
+
+}
+/*
+ Rename all tables in list; Return pointer to wrong entry if something goes
+ wrong. Note that the table_list may be empty!
+*/
+
+/*
+ Rename tables/views in the list
+
+ SYNPOSIS
+ rename_tables()
+ thd Thread handle
+ table_list List of tables to rename
+ skip_error Whether to skip errors
+
+ DESCRIPTION
+ Take a table/view name from and odd list element and rename it to a
+ the name taken from list element+1. Note that the table_list may be
+ empty.
+
+ RETURN
+ false Ok
+ true rename failed
+*/
+
+static TABLE_LIST *
+rename_tables(THD *thd, TABLE_LIST *table_list, bool skip_error)
+{
+ TABLE_LIST *ren_table,*new_table, *tmp_table;
+
+ DBUG_ENTER("rename_tables");
+
+ for (ren_table= table_list; ren_table; ren_table= new_table->next_local)
+ {
+ new_table= ren_table->next_local;
+ if (do_rename(thd, ren_table, new_table->db, new_table->table_name,
+ new_table->alias, skip_error))
DBUG_RETURN(ren_table);
}
DBUG_RETURN(0);
--- 1.95/mysql-test/r/myisam.result 2006-10-19 16:43:55 +02:00
+++ 1.96/mysql-test/r/myisam.result 2006-10-19 16:43:55 +02:00
@@ -1002,15 +1002,15 @@
select sql_big_result v,count(c) from t1 group by v limit 10;
v count(c)
a 1
-a 10
-b 10
-c 10
-d 10
-e 10
-f 10
-g 10
+a 10
+b 10
+c 10
+d 10
+e 10
+f 10
+g 10
h 10
-i 10
+i 10
select c,count(*) from t1 group by c limit 10;
c count(*)
a 1
--- 1.126/mysql-test/r/func_str.result 2006-10-19 16:43:55 +02:00
+++ 1.127/mysql-test/r/func_str.result 2006-10-19 16:43:55 +02:00
@@ -1143,9 +1143,9 @@
SELECT * FROM t1 INNER JOIN t2 ON code=id
WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00');
id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 const PRIMARY PRIMARY 12 const 1 100.00 Using index
1 SIMPLE t1 ref code code 13 const 3 100.00 Using where; Using index
-1 SIMPLE t2 ref PRIMARY PRIMARY 12 const 1 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t1`.`code` AS `code`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = _latin1'a12') and (`test`.`t2`.`id` = _latin1'a12') and (length(`test`.`t1`.`code`) = 5))
+Note 1003 select `test`.`t1`.`code` AS `code`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = _latin1'a12') and (length(`test`.`t1`.`code`) = 5))
DROP TABLE t1,t2;
End of 5.0 tests
--- 1.48.1.4/mysql-test/r/bdb.result 2006-10-19 16:43:55 +02:00
+++ 1.59/BitKeeper/deleted/.del-bdb.result 2006-10-19 16:43:55 +02:00
@@ -48,7 +48,7 @@
15 102 2
update t1 set id=id+1000;
update t1 set id=1024 where id=1009;
-ERROR 23000: Duplicate entry '1024' for key 1
+ERROR 23000: Duplicate entry '1024' for key 'PRIMARY'
select * from t1;
id parent_id level
1001 100 0
@@ -270,7 +270,7 @@
commit;
insert into t1 values (5);
insert into t1 values (4);
-ERROR 23000: Duplicate entry '4' for key 1
+ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
commit;
select n, "after commit" from t1;
n after commit
@@ -279,12 +279,46 @@
set autocommit=1;
insert into t1 values (6);
insert into t1 values (4);
-ERROR 23000: Duplicate entry '4' for key 1
+ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
select n from t1;
n
4
5
6
+set autocommit=0;
+begin;
+savepoint `my_savepoint`;
+insert into t1 values (7);
+savepoint `savept2`;
+insert into t1 values (3);
+select n from t1;
+n
+3
+4
+5
+6
+7
+savepoint savept3;
+rollback to savepoint savept2;
+rollback to savepoint savept3;
+ERROR 42000: SAVEPOINT savept3 does not exist
+rollback to savepoint savept2;
+release savepoint `my_savepoint`;
+select n from t1;
+n
+4
+5
+6
+7
+rollback to savepoint `my_savepoint`;
+ERROR 42000: SAVEPOINT my_savepoint does not exist
+rollback to savepoint savept2;
+ERROR 42000: SAVEPOINT savept2 does not exist
+insert into t1 values (8);
+savepoint sv;
+commit;
+savepoint sv;
+set autocommit=1;
rollback;
drop table t1;
create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=BDB;
@@ -309,7 +343,7 @@
CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=bdb;
insert into t1 values ('pippo', 12);
insert into t1 values ('pippo', 12);
-ERROR 23000: Duplicate entry 'pippo' for key 1
+ERROR 23000: Duplicate entry 'pippo' for key 'PRIMARY'
delete from t1;
delete from t1 where id = 'pippo';
select * from t1;
@@ -464,9 +498,9 @@
insert into t1 (ggid,passwd) values ('test1','xxx');
insert into t1 (ggid,passwd) values ('test2','yyy');
insert into t1 (ggid,passwd) values ('test2','this will fail');
-ERROR 23000: Duplicate entry 'test2' for key 2
+ERROR 23000: Duplicate entry 'test2' for key 'ggid'
insert into t1 (ggid,id) values ('this will fail',1);
-ERROR 23000: Duplicate entry '1' for key 1
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
select * from t1 where ggid='test1';
id ggid email passwd
1 test1 xxx
@@ -479,7 +513,7 @@
replace into t1 (ggid,id) values ('this will work',1);
replace into t1 (ggid,passwd) values ('test2','this will work');
update t1 set id=100,ggid='test2' where id=1;
-ERROR 23000: Duplicate entry 'test2' for key 2
+ERROR 23000: Duplicate entry 'test2' for key 'ggid'
select * from t1;
id ggid email passwd
1 this will work
@@ -1013,7 +1047,7 @@
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
LOCK TABLES t1 WRITE;
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
-ERROR 23000: Duplicate entry '1-1' for key 1
+ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
select id from t1;
id
0
@@ -1031,7 +1065,7 @@
LOCK TABLES t1 WRITE;
begin;
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
-ERROR 23000: Duplicate entry '1-1' for key 1
+ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
select id from t1;
id
0
@@ -1332,40 +1366,40 @@
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(10) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(10) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
create table t2 like t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
- `v` varchar(10) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(10) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
create table t3 select * from t1;
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
- `v` varchar(10) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(10) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
alter table t1 modify c varchar(10);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(10) default NULL,
- `c` varchar(10) default NULL,
+ `v` varchar(10) DEFAULT NULL,
+ `c` varchar(10) DEFAULT NULL,
`t` text
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
alter table t1 modify v char(10);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` char(10) default NULL,
- `c` varchar(10) default NULL,
+ `v` char(10) DEFAULT NULL,
+ `c` varchar(10) DEFAULT NULL,
`t` text
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
alter table t1 modify t varchar(10);
@@ -1374,9 +1408,9 @@
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` char(10) default NULL,
- `c` varchar(10) default NULL,
- `t` varchar(10) default NULL
+ `v` char(10) DEFAULT NULL,
+ `c` varchar(10) DEFAULT NULL,
+ `t` varchar(10) DEFAULT NULL
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
select concat('*',v,'*',c,'*',t,'*') from t1;
concat('*',v,'*',c,'*',t,'*')
@@ -1387,8 +1421,8 @@
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(10) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(10) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text,
KEY `v` (`v`),
KEY `c` (`c`),
@@ -1453,7 +1487,7 @@
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 13 const # Using where
alter table t1 add unique(v);
-ERROR 23000: Duplicate entry '{ ' for key 1
+ERROR 23000: Duplicate entry '{ ' for key 'v_2'
alter table t1 add key(v);
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
qq
@@ -1606,8 +1640,8 @@
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(300) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(300) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text,
KEY `c` (`c`),
KEY `t` (`t`(10)),
@@ -1686,8 +1720,8 @@
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(300) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(300) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text,
KEY `c` (`c`),
KEY `t` (`t`(10)),
@@ -1766,8 +1800,8 @@
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(600) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(600) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text,
KEY `c` (`c`),
KEY `t` (`t`(10)),
@@ -1813,16 +1847,16 @@
create table t1 (a char(10), unique (a));
insert into t1 values ('a ');
insert into t1 values ('a ');
-ERROR 23000: Duplicate entry 'a' for key 1
+ERROR 23000: Duplicate entry 'a' for key 'a'
alter table t1 modify a varchar(10);
insert into t1 values ('a '),('a '),('a '),('a ');
-ERROR 23000: Duplicate entry 'a ' for key 1
+ERROR 23000: Duplicate entry 'a ' for key 'a'
insert into t1 values ('a ');
-ERROR 23000: Duplicate entry 'a ' for key 1
+ERROR 23000: Duplicate entry 'a ' for key 'a'
insert into t1 values ('a ');
-ERROR 23000: Duplicate entry 'a ' for key 1
+ERROR 23000: Duplicate entry 'a ' for key 'a'
insert into t1 values ('a ');
-ERROR 23000: Duplicate entry 'a ' for key 1
+ERROR 23000: Duplicate entry 'a ' for key 'a'
update t1 set a='a ' where a like 'a%';
select concat(a,'.') from t1;
concat(a,'.')
@@ -1844,8 +1878,8 @@
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(10) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(10) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text,
KEY `v` (`v`(5)),
KEY `c` (`c`(5)),
@@ -1856,15 +1890,15 @@
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` char(10) character set utf8 default NULL
+ `v` char(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 (v varchar(10), c char(10)) row_format=fixed;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(10) default NULL,
- `c` char(10) default NULL
+ `v` varchar(10) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
insert into t1 values('a','a'),('a ','a ');
select concat('*',v,'*',c,'*') from t1;
@@ -1906,7 +1940,7 @@
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` mediumtext character set utf8
+ `v` mediumtext CHARACTER SET utf8
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
drop table t1;
set storage_engine=MyISAM;
@@ -1928,38 +1962,23 @@
commit;
alter table t1 add primary key(a);
drop table t1;
-set autocommit=1;
-reset master;
-create table bug16206 (a int);
-insert into bug16206 values(1);
-start transaction;
-insert into bug16206 values(2);
-commit;
-show binlog events;
-Log_name Pos Event_type Server_id End_log_pos Info
-f n Format_desc 1 n Server ver: VERSION, Binlog ver: 4
-f n Query 1 n use `test`; create table bug16206 (a int)
-f n Query 1 n use `test`; insert into bug16206 values(1)
-f n Query 1 n use `test`; insert into bug16206 values(2)
-drop table bug16206;
-reset master;
-create table bug16206 (a int) engine= bdb;
-insert into bug16206 values(0);
-insert into bug16206 values(1);
-start transaction;
-insert into bug16206 values(2);
-commit;
-insert into bug16206 values(3);
-show binlog events;
-Log_name Pos Event_type Server_id End_log_pos Info
-f n Format_desc 1 n Server ver: VERSION, Binlog ver: 4
-f n Query 1 n use `test`; create table bug16206 (a int) engine= bdb
-f n Query 1 n use `test`; insert into bug16206 values(0)
-f n Query 1 n use `test`; insert into bug16206 values(1)
-f n Query 1 n use `test`; BEGIN
-f n Query 1 n use `test`; insert into bug16206 values(2)
-f n Query 1 n use `test`; COMMIT
-f n Query 1 n use `test`; insert into bug16206 values(3)
-drop table bug16206;
-set autocommit=0;
End of 5.0 tests
+create table t1 (a int) engine=bdb;
+set session transaction isolation level repeatable read;
+set transaction isolation level serializable;
+begin;
+select @@tx_isolation;
+@@tx_isolation
+SERIALIZABLE
+insert into t1 values (1);
+set transaction isolation level read committed;
+ERROR 25001: Transaction isolation level can't be changed while a transaction is in progress
+rollback;
+begin;
+select @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+insert into t1 values (1);
+rollback;
+drop table t1;
+End of 5.1 tests
--- 1.27/mysql-test/r/group_min_max.result 2006-10-19 16:43:55 +02:00
+++ 1.28/mysql-test/r/group_min_max.result 2006-10-19 16:43:55 +02:00
@@ -2162,3 +2162,23 @@
id2 id3 id5 id4 id3 id6 id5 id1
1 1 1 1 1 1 1 1
DROP TABLE t1,t2,t3,t4,t5,t6;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
+INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
+explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range PRIMARY,b PRIMARY 8 NULL 1 Using where; Using index for group-by
+SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
+MAX(b) a
+1 1
+SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
+MIN(b) a
+2 1
+CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
+INSERT INTO t2 SELECT a,b,b FROM t1;
+explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range PRIMARY PRIMARY 12 NULL 1 Using where; Using index for group-by
+SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
+MIN(c)
+2
+DROP TABLE t1,t2;
--- 1.27/mysql-test/t/group_min_max.test 2006-10-19 16:43:55 +02:00
+++ 1.28/mysql-test/t/group_min_max.test 2006-10-19 16:43:55 +02:00
@@ -819,3 +819,19 @@
t1;
DROP TABLE t1,t2,t3,t4,t5,t6;
+
+#
+# Bug#22342: No results returned for query using max and group by
+#
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
+INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
+
+explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
+SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
+SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
+CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
+INSERT INTO t2 SELECT a,b,b FROM t1;
+explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
+SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
+
+DROP TABLE t1,t2;
--- 1.62/mysql-test/r/func_gconcat.result 2006-10-19 16:43:55 +02:00
+++ 1.63/mysql-test/r/func_gconcat.result 2006-10-19 16:43:55 +02:00
@@ -74,11 +74,6 @@
1 a
2 b,c
3 C,D,d,d,D,E
-select grp,group_concat(c order by "c") from t1 group by grp;
-grp group_concat(c order by "c")
-1 a
-2 b,c
-3 C,D,d,d,D,E
select grp,group_concat(distinct c order by c) from t1 group by grp;
grp group_concat(distinct c order by c)
1 a
--- 1.48/mysql-test/t/func_gconcat.test 2006-10-19 16:43:55 +02:00
+++ 1.49/mysql-test/t/func_gconcat.test 2006-10-19 16:43:55 +02:00
@@ -32,7 +32,6 @@
select grp,group_concat(a order by a,d+c-ascii(c)-a) from t1 group by grp;
select grp,group_concat(a order by d+c-ascii(c),a) from t1 group by grp;
select grp,group_concat(c order by 1) from t1 group by grp;
-select grp,group_concat(c order by "c") from t1 group by grp;
select grp,group_concat(distinct c order by c) from t1 group by grp;
select grp,group_concat(distinct c order by c desc) from t1 group by grp;
explain extended select grp,group_concat(distinct c order by c desc) from t1 group by grp;
| Thread |
|---|
| • bk commit into 5.1 tree (gkodinov:1.2315) | gkodinov | 19 Oct |
