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, 2007-01-23 12:34:50+02:00, gkodinov@stripped +40 -0
Merge macbook.gmz:/Users/kgeorge/mysql/work/mysql-5.0-opt
into macbook.gmz:/Users/kgeorge/mysql/work/merge-5.1-opt
MERGE: 1.1810.2362.68
mysql-test/r/func_in.result@stripped, 2007-01-23 11:56:42+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.28.1.2
mysql-test/r/func_str.result@stripped, 2007-01-23 12:34:31+02:00, gkodinov@stripped +0 -1
merge 5.0-opt -> 5.1-opt
MERGE: 1.110.1.16
mysql-test/r/group_by.result@stripped, 2007-01-23 12:34:31+02:00, gkodinov@stripped +0 -0
merge 5.0-opt -> 5.1-opt
MERGE: 1.69.1.7
mysql-test/r/insert.result@stripped, 2007-01-23 12:34:32+02:00, gkodinov@stripped +0 -0
merge 5.0-opt -> 5.1-opt
MERGE: 1.22.1.5
mysql-test/r/range.result@stripped, 2007-01-23 11:56:43+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.44.1.9
mysql-test/r/select.result@stripped, 2007-01-23 11:56:43+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.121.1.23
mysql-test/r/sp-code.result@stripped, 2007-01-23 11:56:43+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.5.1.3
mysql-test/r/subselect.result@stripped, 2007-01-23 12:34:32+02:00, gkodinov@stripped +0 -0
merge 5.0-opt -> 5.1-opt
MERGE: 1.134.1.35
mysql-test/r/trigger.result@stripped, 2007-01-23 11:56:44+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.29.1.22
mysql-test/r/udf.result@stripped, 2007-01-23 11:56:44+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.3.1.8
mysql-test/r/view.result@stripped, 2007-01-23 11:56:45+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.138.1.48
mysql-test/t/func_in.test@stripped, 2007-01-23 11:56:45+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.22.1.2
mysql-test/t/func_str.test@stripped, 2007-01-23 11:56:45+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.92.1.7
mysql-test/t/group_by.test@stripped, 2007-01-23 12:34:33+02:00, gkodinov@stripped +0 -0
merge 5.0-opt -> 5.1-opt
MERGE: 1.59.1.3
mysql-test/t/insert.test@stripped, 2007-01-23 12:34:33+02:00, gkodinov@stripped +0 -1
merge 5.0-opt -> 5.1-opt
MERGE: 1.22.1.3
mysql-test/t/range.test@stripped, 2007-01-23 11:56:46+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.37.1.7
mysql-test/t/select.test@stripped, 2007-01-23 11:56:46+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.102.1.16
mysql-test/t/subselect.test@stripped, 2007-01-23 12:34:34+02:00, gkodinov@stripped +0 -0
merge 5.0-opt -> 5.1-opt
MERGE: 1.129.1.9
mysql-test/t/trigger.test@stripped, 2007-01-23 11:56:47+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.34.1.24
mysql-test/t/udf.test@stripped, 2007-01-23 11:56:47+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.8.1.4
mysql-test/t/view.test@stripped, 2007-01-23 12:34:34+02:00, gkodinov@stripped +41 -3
merge 5.0-opt -> 5.1-opt
MERGE: 1.126.1.45
sql/item.cc@stripped, 2007-01-23 11:56:48+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.113.1.136
sql/item.h@stripped, 2007-01-23 11:56:49+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.183.1.34
sql/item_cmpfunc.cc@stripped, 2007-01-23 11:56:49+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.187.1.42
sql/item_cmpfunc.h@stripped, 2007-01-23 12:34:34+02:00, gkodinov@stripped +2 -1
merge 5.0-opt -> 5.1-opt
MERGE: 1.122.2.14
sql/item_func.cc@stripped, 2007-01-23 11:56:50+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.270.1.52
sql/item_strfunc.cc@stripped, 2007-01-23 11:56:51+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.261.1.32
sql/item_strfunc.h@stripped, 2007-01-23 12:34:35+02:00, gkodinov@stripped +2 -6
merge 5.0-opt -> 5.1-opt
MERGE: 1.101.1.19
sql/mysql_priv.h@stripped, 2007-01-23 11:56:51+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.290.1.139
sql/opt_range.h@stripped, 2007-01-23 11:56:52+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.57.1.11
sql/sql_base.cc@stripped, 2007-01-23 11:56:52+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.235.1.129
sql/sql_delete.cc@stripped, 2007-01-23 11:56:53+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.144.1.46
sql/sql_insert.cc@stripped, 2007-01-23 11:56:54+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.146.1.66
sql/sql_lex.cc@stripped, 2007-01-23 11:56:54+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.142.1.69
sql/sql_lex.h@stripped, 2007-01-23 11:56:54+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.175.1.61
sql/sql_prepare.cc@stripped, 2007-01-23 11:56:55+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.142.1.48
sql/sql_select.cc@stripped, 2007-01-23 11:56:56+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.312.1.170
sql/sql_table.cc@stripped, 2007-01-23 11:56:57+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.239.1.91
sql/sql_union.cc@stripped, 2007-01-23 11:56:57+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.130.1.6
sql/sql_update.cc@stripped, 2007-01-23 11:56:58+02:00, gkodinov@stripped +0 -0
Auto merged
MERGE: 1.154.2.54
# 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/merge-5.1-opt/RESYNC
--- 1.228/sql/item.cc 2007-01-12 12:47:54 +02:00
+++ 1.229/sql/item.cc 2007-01-23 11:56:48 +02:00
@@ -3503,6 +3503,16 @@ Item_field::fix_outer_field(THD *thd, Fi
{
if (*from_field)
{
+ if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY &&
+ select->cur_pos_in_select_list != UNDEF_POS)
+ {
+ /*
+ As this is an outer field it should be added to the list of
+ non aggregated fields of the outer select.
+ */
+ marker= select->cur_pos_in_select_list;
+ select->non_agg_fields.push_back(this);
+ }
if (*from_field != view_ref_found)
{
prev_subselect_item->used_tables_cache|= (*from_field)->table->map;
@@ -3705,10 +3715,11 @@ Item_field::fix_outer_field(THD *thd, Fi
bool Item_field::fix_fields(THD *thd, Item **reference)
{
DBUG_ASSERT(fixed == 0);
+ Field *from_field= (Field *)not_found_field;
+ bool outer_fixed= false;
+
if (!field) // If field is not checked
{
- Field *from_field= (Field *)not_found_field;
- bool outer_fixed= false;
/*
In case of view, find_field_in_tables() write pointer to view field
expression to 'reference', i.e. it substitute that expression instead
@@ -3800,6 +3811,7 @@ bool Item_field::fix_fields(THD *thd, It
goto error;
if (!ret)
return FALSE;
+ outer_fixed= 1;
}
set_field(from_field);
@@ -3859,6 +3871,13 @@ bool Item_field::fix_fields(THD *thd, It
}
#endif
fixed= 1;
+ if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY &&
+ !outer_fixed && !thd->lex->in_sum_func &&
+ thd->lex->current_select->cur_pos_in_select_list != UNDEF_POS)
+ {
+ thd->lex->current_select->non_agg_fields.push_back(this);
+ marker= thd->lex->current_select->cur_pos_in_select_list;
+ }
return FALSE;
error:
--- 1.218/sql/item.h 2006-12-31 02:06:34 +02:00
+++ 1.219/sql/item.h 2007-01-23 11:56:49 +02:00
@@ -482,7 +482,8 @@ public:
Item *next;
uint32 max_length;
uint name_length; /* Length of name */
- uint8 marker, decimals;
+ int8 marker;
+ uint8 decimals;
my_bool maybe_null; /* If item may be null */
my_bool null_value; /* if item is null */
my_bool unsigned_flag;
@@ -2373,6 +2374,9 @@ public:
bool fix_fields(THD *, Item **);
void print(String *str);
table_map used_tables() const { return (table_map)0L; }
+ Field *get_tmp_table_field() { return 0; }
+ Item *copy_or_same(THD *thd) { return this; }
+ Item *get_tmp_table_item(THD *thd) { return copy_or_same(thd); }
void cleanup();
private:
--- 1.234/sql/item_cmpfunc.cc 2007-01-10 12:06:17 +02:00
+++ 1.235/sql/item_cmpfunc.cc 2007-01-23 11:56:49 +02:00
@@ -176,6 +176,22 @@ longlong Item_func_not::val_int()
}
/*
+ We put any NOT expression into parenthesis to avoid
+ possible problems with internal view representations where
+ any '!' is converted to NOT. It may cause a problem if
+ '!' is used in an expression together with other operators
+ whose precedence is lower than the precedence of '!' yet
+ higher than the precedence of NOT.
+*/
+
+void Item_func_not::print(String *str)
+{
+ str->append('(');
+ Item_func::print(str);
+ str->append(')');
+}
+
+/*
special NOT for ALL subquery
*/
--- 1.139/sql/item_cmpfunc.h 2007-01-10 12:06:17 +02:00
+++ 1.140/sql/item_cmpfunc.h 2007-01-23 12:34:34 +02:00
@@ -271,6 +271,7 @@ public:
const char *func_name() const { return "not"; }
Item *neg_transformer(THD *thd);
bool check_partition_func_processor(byte *int_arg) {return FALSE;}
+ void print(String *str);
};
class Item_maxmin_subselect;
@@ -1033,6 +1034,11 @@ public:
class Item_func_in :public Item_func_opt_neg
{
public:
+ Item_result cmp_type;
+ /*
+ an array of values when the right hand arguments of IN
+ are all SQL constant and there are no nulls
+ */
in_vector *array;
bool have_null;
Item_result left_result_type;
@@ -1064,7 +1070,7 @@ public:
DBUG_VOID_RETURN;
}
optimize_type select_optimize() const
- { return array ? OPTIMIZE_KEY : OPTIMIZE_NONE; }
+ { return OPTIMIZE_KEY; }
void print(String *str);
enum Functype functype() const { return IN_FUNC; }
const char *func_name() const { return " IN "; }
--- 1.345/sql/item_func.cc 2007-01-10 12:06:17 +02:00
+++ 1.346/sql/item_func.cc 2007-01-23 11:56:50 +02:00
@@ -2746,25 +2746,28 @@ udf_handler::fix_fields(THD *thd, Item_r
if (arguments[i]->const_item())
{
- if (arguments[i]->null_value)
- continue;
-
switch (arguments[i]->result_type())
{
case STRING_RESULT:
case DECIMAL_RESULT:
{
String *res= arguments[i]->val_str(&buffers[i]);
+ if (arguments[i]->null_value)
+ continue;
f_args.args[i]= (char*) res->ptr();
break;
}
case INT_RESULT:
*((longlong*) to)= arguments[i]->val_int();
+ if (arguments[i]->null_value)
+ continue;
f_args.args[i]= to;
to+= ALIGN_SIZE(sizeof(longlong));
break;
case REAL_RESULT:
*((double*) to)= arguments[i]->val_real();
+ if (arguments[i]->null_value)
+ continue;
f_args.args[i]= to;
to+= ALIGN_SIZE(sizeof(double));
break;
--- 1.306/sql/item_strfunc.cc 2007-01-02 15:55:15 +02:00
+++ 1.307/sql/item_strfunc.cc 2007-01-23 11:56:51 +02:00
@@ -1706,6 +1706,19 @@ String *Item_func_encode::val_str(String
return res;
}
+void Item_func_encode::print(String *str)
+{
+ str->append(func_name());
+ str->append('(');
+ args[0]->print(str);
+ str->append(',');
+ str->append('\'');
+ str->append(seed);
+ str->append('\'');
+ str->append(')');
+}
+
+
String *Item_func_decode::val_str(String *str)
{
String *res;
--- 1.123/sql/item_strfunc.h 2006-12-23 21:19:50 +02:00
+++ 1.124/sql/item_strfunc.h 2007-01-23 12:34:35 +02:00
@@ -360,12 +360,16 @@ public:
class Item_func_encode :public Item_str_func
{
+ protected:
+ SQL_CRYPT sql_crypt;
+ String seed;
public:
Item_func_encode(Item *a, Item *seed):
Item_str_func(a, seed) {}
String *val_str(String *);
void fix_length_and_dec();
const char *func_name() const { return "encode"; }
+ void print(String *str);
};
--- 1.467/sql/mysql_priv.h 2007-01-10 12:06:18 +02:00
+++ 1.468/sql/mysql_priv.h 2007-01-23 11:56:51 +02:00
@@ -415,7 +415,11 @@ MY_LOCALE *my_locale_by_name(const char
#define UNCACHEABLE_EXPLAIN 8
/* Don't evaluate subqueries in prepare even if they're not correlated */
#define UNCACHEABLE_PREPARE 16
+/* For uncorrelated SELECT in an UNION with some correlated SELECTs */
+#define UNCACHEABLE_UNITED 32
+/* Used to check GROUP BY list in the MODE_ONLY_FULL_GROUP_BY mode */
+#define UNDEF_POS (-1)
#ifdef EXTRA_DEBUG
/*
Sync points allow us to force the server to reach a certain line of code
--- 1.374/sql/sql_base.cc 2007-01-12 12:47:55 +02:00
+++ 1.375/sql/sql_base.cc 2007-01-23 11:56:52 +02:00
@@ -5327,6 +5327,7 @@ bool setup_fields(THD *thd, Item **ref_p
bzero(ref_pointer_array, sizeof(Item *) * fields.elements);
Item **ref= ref_pointer_array;
+ thd->lex->current_select->cur_pos_in_select_list= 0;
while ((item= it++))
{
if (!item->fixed && item->fix_fields(thd, it.ref()) ||
@@ -5343,7 +5344,10 @@ bool setup_fields(THD *thd, Item **ref_p
sum_func_list)
item->split_sum_func(thd, ref_pointer_array, *sum_func_list);
thd->used_tables|= item->used_tables();
+ thd->lex->current_select->cur_pos_in_select_list++;
}
+ thd->lex->current_select->cur_pos_in_select_list= UNDEF_POS;
+
thd->lex->allow_sum_func= save_allow_sum_func;
thd->mark_used_columns= save_mark_used_columns;
DBUG_PRINT("info", ("thd->mark_used_columns: %d", thd->mark_used_columns));
--- 1.205/sql/sql_delete.cc 2006-12-23 21:19:53 +02:00
+++ 1.206/sql/sql_delete.cc 2007-01-23 11:56:53 +02:00
@@ -153,7 +153,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
if (order && order->elements)
{
- uint length;
+ uint length= 0;
SORT_FIELD *sortorder;
TABLE_LIST tables;
List<Item> fields;
@@ -173,7 +173,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
DBUG_RETURN(TRUE);
}
- if (!select && limit != HA_POS_ERROR)
+ if ((!select || table->quick_keys.is_clear_all()) && limit != HA_POS_ERROR)
usable_index= get_index_for_order(table, (ORDER*)(order->first), limit);
if (usable_index == MAX_KEY)
--- 1.241/sql/sql_insert.cc 2006-12-31 10:41:37 +02:00
+++ 1.242/sql/sql_insert.cc 2007-01-23 11:56:54 +02:00
@@ -81,6 +81,65 @@ static bool check_view_insertability(THD
#define my_safe_afree(ptr, size, min_length) if (size > min_length) my_free(ptr,MYF(0))
#endif
+/*
+ Check that insert/update fields are from the same single table of a view.
+
+ SYNOPSIS
+ check_view_single_update()
+ fields The insert/update fields to be checked.
+ view The view for insert.
+ map [in/out] The insert table map.
+
+ DESCRIPTION
+ This function is called in 2 cases:
+ 1. to check insert fields. In this case *map will be set to 0.
+ Insert fields are checked to be all from the same single underlying
+ table of the given view. Otherwise the error is thrown. Found table
+ map is returned in the map parameter.
+ 2. to check update fields of the ON DUPLICATE KEY UPDATE clause.
+ In this case *map contains table_map found on the previous call of
+ the function to check insert fields. Update fields are checked to be
+ from the same table as the insert fields.
+
+ RETURN
+ 0 OK
+ 1 Error
+*/
+
+bool check_view_single_update(List<Item> &fields, TABLE_LIST *view,
+ table_map *map)
+{
+ /* it is join view => we need to find the table for update */
+ List_iterator_fast<Item> it(fields);
+ Item *item;
+ TABLE_LIST *tbl= 0; // reset for call to check_single_table()
+ table_map tables= 0;
+
+ while ((item= it++))
+ tables|= item->used_tables();
+
+ /* Check found map against provided map */
+ if (*map)
+ {
+ if (tables != *map)
+ goto error;
+ return FALSE;
+ }
+
+ if (view->check_single_table(&tbl, tables, view) || tbl == 0)
+ goto error;
+
+ view->table= tbl->table;
+ *map= tables;
+
+ return FALSE;
+
+error:
+ my_error(ER_VIEW_MULTIUPDATE, MYF(0),
+ view->view_db.str, view->view_name.str);
+ return TRUE;
+}
+
/*
Check if insert fields are correct.
@@ -105,7 +164,7 @@ static bool check_view_insertability(THD
static int check_insert_fields(THD *thd, TABLE_LIST *table_list,
List<Item> &fields, List<Item> &values,
- bool check_unique)
+ bool check_unique, table_map *map)
{
TABLE *table= table_list->table;
@@ -183,21 +242,9 @@ static int check_insert_fields(THD *thd,
if (table_list->effective_algorithm == VIEW_ALGORITHM_MERGE)
{
- /* it is join view => we need to find table for update */
- List_iterator_fast<Item> it(fields);
- Item *item;
- TABLE_LIST *tbl= 0; // reset for call to check_single_table()
- table_map map= 0;
-
- while ((item= it++))
- map|= item->used_tables();
- if (table_list->check_single_table(&tbl, map, table_list) || tbl == 0)
- {
- my_error(ER_VIEW_MULTIUPDATE, MYF(0),
- table_list->view_db.str, table_list->view_name.str);
+ if (check_view_single_update(fields, table_list, map))
return -1;
- }
- table_list->table= table= tbl->table;
+ table= table_list->table;
}
if (check_unique && thd->dup_field)
@@ -255,7 +302,7 @@ static int check_insert_fields(THD *thd,
*/
static int check_update_fields(THD *thd, TABLE_LIST *insert_table_list,
- List<Item> &update_fields)
+ List<Item> &update_fields, table_map *map)
{
TABLE *table= insert_table_list->table;
my_bool timestamp_mark;
@@ -276,6 +323,10 @@ static int check_update_fields(THD *thd,
if (setup_fields(thd, 0, update_fields, MARK_COLUMNS_WRITE, 0, 0))
return -1;
+ if (insert_table_list->effective_algorithm == VIEW_ALGORITHM_MERGE &&
+ check_view_single_update(update_fields, insert_table_list, map))
+ return -1;
+
if (table->timestamp_field)
{
/* Don't set timestamp column if this is modified. */
@@ -892,6 +943,7 @@ bool mysql_prepare_insert(THD *thd, TABL
Name_resolution_context_state ctx_state;
bool insert_into_view= (table_list->view != 0);
bool res= 0;
+ table_map map= 0;
DBUG_ENTER("mysql_prepare_insert");
DBUG_PRINT("enter", ("table_list 0x%lx, table 0x%lx, view %d",
(ulong)table_list, (ulong)table,
@@ -940,12 +992,12 @@ bool mysql_prepare_insert(THD *thd, TABL
/* Prepare the fields in the statement. */
if (values &&
!(res= check_insert_fields(thd, context->table_list, fields, *values,
- !insert_into_view) ||
+ !insert_into_view, &map) ||
setup_fields(thd, 0, *values, MARK_COLUMNS_READ, 0, 0)) &&
duplic == DUP_UPDATE)
{
select_lex->no_wrap_view_item= TRUE;
- res= check_update_fields(thd, context->table_list, update_fields);
+ res= check_update_fields(thd, context->table_list, update_fields, &map);
select_lex->no_wrap_view_item= FALSE;
/*
When we are not using GROUP BY we can refer to other tables in the
@@ -2406,6 +2458,7 @@ select_insert::prepare(List<Item> &value
{
LEX *lex= thd->lex;
int res;
+ table_map map= 0;
SELECT_LEX *lex_current_select_save= lex->current_select;
DBUG_ENTER("select_insert::prepare");
@@ -2418,7 +2471,7 @@ select_insert::prepare(List<Item> &value
*/
lex->current_select= &lex->select_lex;
res= check_insert_fields(thd, table_list, *fields, values,
- !insert_into_view) ||
+ !insert_into_view, &map) ||
setup_fields(thd, 0, values, MARK_COLUMNS_READ, 0, 0);
if (info.handle_duplicates == DUP_UPDATE)
@@ -2436,7 +2489,7 @@ select_insert::prepare(List<Item> &value
lex->select_lex.no_wrap_view_item= TRUE;
res= res || check_update_fields(thd, context->table_list,
- *info.update_fields);
+ *info.update_fields, &map);
lex->select_lex.no_wrap_view_item= FALSE;
/*
When we are not using GROUP BY we can refer to other tables in the
--- 1.216/sql/sql_lex.cc 2007-01-10 12:06:18 +02:00
+++ 1.217/sql/sql_lex.cc 2007-01-23 11:56:54 +02:00
@@ -1209,6 +1209,8 @@ void st_select_lex::init_select()
offset_limit= 0; /* denotes the default offset = 0 */
with_sum_func= 0;
is_correlated= 0;
+ cur_pos_in_select_list= UNDEF_POS;
+ non_agg_fields.empty();
}
/*
@@ -1398,9 +1400,17 @@ void st_select_lex::mark_as_dependent(SE
if (!(s->uncacheable & UNCACHEABLE_DEPENDENT))
{
// Select is dependent of outer select
- s->uncacheable|= UNCACHEABLE_DEPENDENT;
+ s->uncacheable= (s->uncacheable & ~UNCACHEABLE_UNITED) |
+ UNCACHEABLE_DEPENDENT;
SELECT_LEX_UNIT *munit= s->master_unit();
- munit->uncacheable|= UNCACHEABLE_DEPENDENT;
+ munit->uncacheable= (munit->uncacheable & ~UNCACHEABLE_UNITED) |
+ UNCACHEABLE_DEPENDENT;
+ for (SELECT_LEX *sl= munit->first_select(); sl ; sl= sl->next_select())
+ {
+ if (sl != s &&
+ !(sl->uncacheable & (UNCACHEABLE_DEPENDENT | UNCACHEABLE_UNITED)))
+ sl->uncacheable|= UNCACHEABLE_UNITED;
+ }
}
is_correlated= TRUE;
this->master_unit()->item->is_correlated= TRUE;
--- 1.255/sql/sql_lex.h 2006-12-31 02:06:36 +02:00
+++ 1.256/sql/sql_lex.h 2007-01-23 11:56:54 +02:00
@@ -616,6 +616,10 @@ public:
bool no_wrap_view_item;
/* exclude this select from check of unique_table() */
bool exclude_from_table_unique_test;
+ /* List of fields that aren't under an aggregate function */
+ List<Item_field> non_agg_fields;
+ /* index in the select list of the expression currently being fixed */
+ int cur_pos_in_select_list;
void init_query();
void init_select();
--- 1.479/sql/sql_select.cc 2007-01-22 12:58:08 +02:00
+++ 1.480/sql/sql_select.cc 2007-01-23 11:56:56 +02:00
@@ -8954,8 +8954,7 @@ Field *create_tmp_field(THD *thd, TABLE
if (type != Item::FIELD_ITEM &&
item->real_item()->type() == Item::FIELD_ITEM &&
- (item->type() != Item::REF_ITEM ||
- !((Item_ref *) item)->depended_from))
+ !((Item_ref *) item)->depended_from)
{
orig_item= item;
item= item->real_item();
@@ -12499,7 +12498,7 @@ static int
create_sort_index(THD *thd, JOIN *join, ORDER *order,
ha_rows filesort_limit, ha_rows select_limit)
{
- uint length;
+ uint length= 0;
ha_rows examined_rows;
TABLE *table;
SQL_SELECT *select;
@@ -12520,8 +12519,10 @@ create_sort_index(THD *thd, JOIN *join,
!(join->select_options & SELECT_BIG_RESULT)) &&
test_if_skip_sort_order(tab,order,select_limit,0))
DBUG_RETURN(0);
+ for (ORDER *ord= join->order; ord; ord= ord->next)
+ length++;
if (!(join->sortorder=
- make_unireg_sortorder(order,&length,join->sortorder)))
+ make_unireg_sortorder(order, &length, join->sortorder)))
goto err; /* purecov: inspected */
table->sort.io_cache=(IO_CACHE*) my_malloc(sizeof(IO_CACHE),
@@ -12929,8 +12930,10 @@ SORT_FIELD *make_unireg_sortorder(ORDER
for (ORDER *tmp = order; tmp; tmp=tmp->next)
count++;
if (!sortorder)
- sortorder= (SORT_FIELD*) sql_alloc(sizeof(SORT_FIELD)*(count+1));
- pos=sort=sortorder;
+ sortorder= (SORT_FIELD*) sql_alloc(sizeof(SORT_FIELD) *
+ (max(count, *length) + 1));
+ pos= sort= sortorder;
+
if (!pos)
return 0;
@@ -13458,49 +13461,83 @@ setup_group(THD *thd, Item **ref_pointer
bool *hidden_group_fields)
{
*hidden_group_fields=0;
+ ORDER *ord;
+
if (!order)
return 0; /* Everything is ok */
- if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
- {
- Item *item;
- List_iterator<Item> li(fields);
- while ((item=li++))
- item->marker=0; /* Marker that field is not used */
- }
uint org_fields=all_fields.elements;
thd->where="group statement";
- for (; order; order=order->next)
+ for (ord= order; ord; ord= ord->next)
{
- if (find_order_in_list(thd, ref_pointer_array, tables, order, fields,
+ if (find_order_in_list(thd, ref_pointer_array, tables, ord, fields,
all_fields, TRUE))
return 1;
- (*order->item)->marker=1; /* Mark found */
- if ((*order->item)->with_sum_func)
+ (*ord->item)->marker= UNDEF_POS; /* Mark found */
+ if ((*ord->item)->with_sum_func)
{
- my_error(ER_WRONG_GROUP_FIELD, MYF(0), (*order->item)->full_name());
+ my_error(ER_WRONG_GROUP_FIELD, MYF(0), (*ord->item)->full_name());
return 1;
}
}
if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
{
- /* Don't allow one to use fields that is not used in GROUP BY */
+ /*
+ Don't allow one to use fields that is not used in GROUP BY
+ For each select a list of field references that aren't under an
+ aggregate function is created. Each field in this list keeps the
+ position of the select list expression which it belongs to.
+
+ First we check an expression from the select list against the GROUP BY
+ list. If it's found there then it's ok. It's also ok if this expression
+ is a constant or an aggregate function. Otherwise we scan the list
+ of non-aggregated fields and if we'll find at least one field reference
+ that belongs to this expression and doesn't occur in the GROUP BY list
+ we throw an error. If there are no fields in the created list for a
+ select list expression this means that all fields in it are used under
+ aggregate functions.
+ */
Item *item;
+ Item_field *field;
+ int cur_pos_in_select_list= 0;
List_iterator<Item> li(fields);
+ List_iterator<Item_field> naf_it(thd->lex->current_select->non_agg_fields);
- while ((item=li++))
+ field= naf_it++;
+ while (field && (item=li++))
{
- if (item->type() != Item::SUM_FUNC_ITEM && !item->marker &&
- !item->const_item())
+ if (item->type() != Item::SUM_FUNC_ITEM && item->marker >= 0 &&
+ !item->const_item() &&
+ !(item->real_item()->type() == Item::FIELD_ITEM &&
+ item->used_tables() & OUTER_REF_TABLE_BIT))
{
- /*
- TODO: change ER_WRONG_FIELD_WITH_GROUP to more detailed
- ER_NON_GROUPING_FIELD_USED
- */
- my_error(ER_WRONG_FIELD_WITH_GROUP, MYF(0), item->full_name());
- return 1;
+ while (field)
+ {
+ /* Skip fields from previous expressions. */
+ if (field->marker < cur_pos_in_select_list)
+ goto next_field;
+ /* Found a field from the next expression. */
+ if (field->marker > cur_pos_in_select_list)
+ break;
+ /*
+ Check whether the field occur in the GROUP BY list.
+ Throw the error later if the field isn't found.
+ */
+ for (ord= order; ord; ord= ord->next)
+ if ((*ord->item)->eq((Item*)field, 0))
+ goto next_field;
+ /*
+ TODO: change ER_WRONG_FIELD_WITH_GROUP to more detailed
+ ER_NON_GROUPING_FIELD_USED
+ */
+ my_error(ER_WRONG_FIELD_WITH_GROUP, MYF(0), field->full_name());
+ return 1;
+next_field:
+ field= naf_it++;
+ }
}
+ cur_pos_in_select_list++;
}
}
if (org_fields != all_fields.elements)
@@ -13626,10 +13663,12 @@ count_field_types(TMP_TABLE_PARAM *param
param->quick_group=1;
while ((field=li++))
{
- Item::Type type=field->real_item()->type();
- if (type == Item::FIELD_ITEM)
+ Item::Type type=field->type();
+ Item::Type real_type= field->real_item()->type();
+ if (type == Item::FIELD_ITEM || (real_type == Item::FIELD_ITEM &&
+ !((Item_ref *) field)->depended_from))
param->field_count++;
- else if (type == Item::SUM_FUNC_ITEM)
+ else if (real_type == Item::SUM_FUNC_ITEM)
{
if (! field->const_item())
{
--- 1.379/sql/sql_table.cc 2006-12-27 11:58:01 +02:00
+++ 1.380/sql/sql_table.cc 2007-01-23 11:56:57 +02:00
@@ -6684,7 +6684,7 @@ copy_data_between_tables(TABLE *from,TAB
Copy_field *copy,*copy_end;
ulong found_count,delete_count;
THD *thd= current_thd;
- uint length;
+ uint length= 0;
SORT_FIELD *sortorder;
READ_RECORD info;
TABLE_LIST tables;
--- 1.217/sql/sql_update.cc 2006-12-31 02:06:37 +02:00
+++ 1.218/sql/sql_update.cc 2007-01-23 11:56:58 +02:00
@@ -320,7 +320,7 @@ int mysql_update(THD *thd,
to update
NOTE: filesort will call table->prepare_for_position()
*/
- uint length;
+ uint length= 0;
SORT_FIELD *sortorder;
ha_rows examined_rows;
--- 1.141/sql/sql_union.cc 2006-12-23 21:19:55 +02:00
+++ 1.142/sql/sql_union.cc 2007-01-23 11:56:57 +02:00
@@ -734,6 +734,7 @@ bool st_select_lex::cleanup()
{
error= (bool) ((uint) error | (uint) lex_unit->cleanup());
}
+ non_agg_fields.empty();
DBUG_RETURN(error);
}
--- 1.160/mysql-test/r/subselect.result 2007-01-08 12:30:58 +02:00
+++ 1.161/mysql-test/r/subselect.result 2007-01-23 12:34:32 +02:00
@@ -1464,7 +1464,7 @@ id select_type table type possible_keys
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index
Warnings:
-Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
@@ -1476,13 +1476,13 @@ id select_type table type possible_keys
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index
Warnings:
-Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where
Warnings:
-Note 1003 select `test`.`t1`.`s1` AS `s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2'))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
+Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2')))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
drop table t1,t2;
create table t2 (a int, b int);
create table t3 (a int);
@@ -1737,14 +1737,14 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where
Warnings:
-Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8)))))
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8))))))
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index
Warnings:
Note 1276 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))
+Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');
@@ -3605,6 +3605,39 @@ FROM t1) t;
COUNT(*)
3000
DROP TABLE t1,t2;
+CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
+CREATE TABLE t2 (c int);
+INSERT INTO t1 VALUES ('aa', 1);
+INSERT INTO t2 VALUES (1);
+SELECT * FROM t1
+WHERE EXISTS (SELECT c FROM t2 WHERE c=1
+UNION
+SELECT c from t2 WHERE c=t1.c);
+id c
+aa 1
+INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
+SELECT * FROM t1
+WHERE EXISTS (SELECT c FROM t2 WHERE c=1
+UNION
+SELECT c from t2 WHERE c=t1.c);
+id c
+aa 1
+bb 2
+cc 3
+dd 1
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (c int);
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1
+WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
+UNION
+SELECT c from t2 WHERE c=t1.c);
+id c
+aa 1
+bb 2
+cc 3
+dd 1
+DROP TABLE t1,t2,t3;
CREATE TABLE t1 (s1 char(1));
INSERT INTO t1 VALUES ('a');
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
--- 1.194/mysql-test/r/view.result 2007-01-02 12:06:13 +02:00
+++ 1.195/mysql-test/r/view.result 2007-01-23 11:56:45 +02:00
@@ -3014,6 +3014,17 @@ i j
6 3
DROP VIEW v1, v2;
DROP TABLE t1;
+CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL;
+SHOW CREATE VIEW v;
+View Create View
+v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select ((not(0)) * 5) AS `x`
+SELECT !0 * 5 AS x FROM DUAL;
+x
+5
+SELECT * FROM v;
+x
+5
+DROP VIEW v;
End of 5.0 tests.
DROP DATABASE IF EXISTS `d-1`;
CREATE DATABASE `d-1`;
--- 1.141/mysql-test/t/subselect.test 2007-01-10 12:06:17 +02:00
+++ 1.142/mysql-test/t/subselect.test 2007-01-23 12:34:34 +02:00
@@ -2510,6 +2510,40 @@ SELECT SQL_NO_CACHE COUNT(*)
DROP TABLE t1,t2;
#
+# Bug #25219: EXIST subquery with UNION over a mix of
+# correlated and uncorrelated selects
+#
+
+CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
+CREATE TABLE t2 (c int);
+
+INSERT INTO t1 VALUES ('aa', 1);
+INSERT INTO t2 VALUES (1);
+
+SELECT * FROM t1
+ WHERE EXISTS (SELECT c FROM t2 WHERE c=1
+ UNION
+ SELECT c from t2 WHERE c=t1.c);
+
+INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
+
+SELECT * FROM t1
+ WHERE EXISTS (SELECT c FROM t2 WHERE c=1
+ UNION
+ SELECT c from t2 WHERE c=t1.c);
+
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (c int);
+INSERT INTO t3 VALUES (1);
+
+SELECT * FROM t1
+ WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
+ UNION
+ SELECT c from t2 WHERE c=t1.c);
+
+DROP TABLE t1,t2,t3;
+
+#
# Bug#20835 (literal string with =any values)
#
CREATE TABLE t1 (s1 char(1));
--- 1.175/mysql-test/t/view.test 2006-11-13 13:45:26 +02:00
+++ 1.176/mysql-test/t/view.test 2007-01-23 12:34:34 +02:00
@@ -2906,6 +2906,58 @@ DROP FUNCTION f1;
DROP VIEW v1;
DROP TABLE t1;
+#
+# BUG#22584: last_insert_id not updated after inserting a record
+# through a updatable view
+#
+# We still do not update LAST_INSERT_ID if AUTO_INCREMENT column is
+# not accessible through a view. However, we do not reset the value
+# of LAST_INSERT_ID, but keep it unchanged.
+#
+--disable_warnings
+DROP VIEW IF EXISTS v1, v2;
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT);
+CREATE VIEW v1 AS SELECT j FROM t1;
+CREATE VIEW v2 AS SELECT * FROM t1;
+
+INSERT INTO t1 (j) VALUES (1);
+SELECT LAST_INSERT_ID();
+
+INSERT INTO v1 (j) VALUES (2);
+--echo # LAST_INSERT_ID() should not change.
+SELECT LAST_INSERT_ID();
+
+INSERT INTO v2 (j) VALUES (3);
+--echo # LAST_INSERT_ID() should be updated.
+SELECT LAST_INSERT_ID();
+
+INSERT INTO v1 (j) SELECT j FROM t1;
+--echo # LAST_INSERT_ID() should not change.
+SELECT LAST_INSERT_ID();
+
+SELECT * FROM t1;
+
+DROP VIEW v1, v2;
+DROP TABLE t1;
+
+#
+# Bug #25580: !0 as an operand in a select expression of a view
+#
+
+CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL;
+SHOW CREATE VIEW v;
+
+SELECT !0 * 5 AS x FROM DUAL;
+SELECT * FROM v;
+
+DROP VIEW v;
+
+
+--echo End of 5.0 tests.
+
# Bug #16813 (WITH CHECK OPTION doesn't work with UPDATE)
#
CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL);
@@ -2956,9 +3008,6 @@ SELECT * FROM t1;
DROP VIEW v1, v2;
DROP TABLE t1;
-
-
---echo End of 5.0 tests.
#
# Bug#21370 View renaming lacks tablename_to_filename encoding
--- 1.54/mysql-test/r/trigger.result 2006-11-30 01:10:44 +02:00
+++ 1.55/mysql-test/r/trigger.result 2007-01-23 11:56:44 +02:00
@@ -1278,4 +1278,36 @@ a b
2 b
3 c
drop table t1;
+CREATE TABLE t1 (
+id int NOT NULL DEFAULT '0',
+a varchar(10) NOT NULL,
+b varchar(10),
+c varchar(10),
+d timestamp NOT NULL,
+PRIMARY KEY (id, a)
+);
+CREATE TABLE t2 (
+fubar_id int unsigned NOT NULL DEFAULT '0',
+last_change_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+PRIMARY KEY (fubar_id)
+);
+CREATE TRIGGER fubar_change
+AFTER UPDATE ON t1
+FOR EACH ROW
+BEGIN
+INSERT INTO t2 (fubar_id, last_change_time)
+SELECT DISTINCT NEW.id AS fubar_id, NOW() AS last_change_time
+FROM t1 WHERE (id = NEW.id) AND (OLD.c != NEW.c)
+ON DUPLICATE KEY UPDATE
+last_change_time =
+IF((fubar_id = NEW.id)AND(OLD.c != NEW.c),NOW(),last_change_time);
+END
+|
+INSERT INTO t1 (id,a, b,c,d) VALUES
+(1,'a','b','c',now()),(2,'a','b','c',now());
+UPDATE t1 SET c='Bang!' WHERE id=1;
+SELECT fubar_id FROM t2;
+fubar_id
+1
+DROP TABLE t1,t2;
End of 5.0 tests
--- 1.61/mysql-test/t/trigger.test 2006-12-28 07:42:02 +02:00
+++ 1.62/mysql-test/t/trigger.test 2007-01-23 11:56:47 +02:00
@@ -1548,4 +1548,50 @@ select * from t1;
drop table t1;
+#
+# Bug#25398: crash when a trigger contains a SELECT with
+# trigger fields in the select list under DISTINCT
+#
+
+CREATE TABLE t1 (
+ id int NOT NULL DEFAULT '0',
+ a varchar(10) NOT NULL,
+ b varchar(10),
+ c varchar(10),
+ d timestamp NOT NULL,
+ PRIMARY KEY (id, a)
+);
+
+CREATE TABLE t2 (
+ fubar_id int unsigned NOT NULL DEFAULT '0',
+ last_change_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+ PRIMARY KEY (fubar_id)
+);
+
+DELIMITER |;
+
+CREATE TRIGGER fubar_change
+ AFTER UPDATE ON t1
+ FOR EACH ROW
+ BEGIN
+ INSERT INTO t2 (fubar_id, last_change_time)
+ SELECT DISTINCT NEW.id AS fubar_id, NOW() AS last_change_time
+ FROM t1 WHERE (id = NEW.id) AND (OLD.c != NEW.c)
+ ON DUPLICATE KEY UPDATE
+ last_change_time =
+ IF((fubar_id = NEW.id)AND(OLD.c != NEW.c),NOW(),last_change_time);
+ END
+|
+
+DELIMITER ;|
+
+INSERT INTO t1 (id,a, b,c,d) VALUES
+ (1,'a','b','c',now()),(2,'a','b','c',now());
+
+UPDATE t1 SET c='Bang!' WHERE id=1;
+
+SELECT fubar_id FROM t2;
+
+DROP TABLE t1,t2;
+
--echo End of 5.0 tests
--- 1.32/mysql-test/r/func_in.result 2006-11-30 13:26:58 +02:00
+++ 1.33/mysql-test/r/func_in.result 2007-01-23 11:56:42 +02:00
@@ -351,6 +351,49 @@ some_id
1
2
drop table t1;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);
+CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (3,2),(4,2);
+CREATE TABLE t3 (a int PRIMARY KEY);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+CREATE TABLE t4 (a int PRIMARY KEY);
+INSERT INTO t4 VALUES (1),(2);
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t3
+JOIN t1 ON t3.a=t1.a
+JOIN t2 ON t3.a=t2.a
+JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
+SELECT STRAIGHT_JOIN * FROM t3
+JOIN t1 ON t3.a=t1.a
+JOIN t2 ON t3.a=t2.a
+JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+a a b a b a
+3 3 1 3 2 1
+3 3 1 3 2 2
+4 4 1 4 2 1
+4 4 1 4 2 2
+EXPLAIN SELECT STRAIGHT_JOIN
+(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
+FROM t3, t1, t2
+WHERE t3.a=t1.a AND t3.a=t2.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
+1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 2 Using where; Using index
+SELECT STRAIGHT_JOIN
+(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
+FROM t3, t1, t2
+WHERE t3.a=t1.a AND t3.a=t2.a;
+(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
+3
+3
+DROP TABLE t1,t2,t3,t4;
End of 5.0 tests
create table t1(f1 char(1));
insert into t1 values ('a'),('b'),('1');
--- 1.76/mysql-test/r/group_by.result 2006-10-19 17:07:02 +03:00
+++ 1.77/mysql-test/r/group_by.result 2007-01-23 12:34:31 +02:00
@@ -933,6 +933,108 @@ b sum(1)
18 6
19 6
DROP TABLE t1;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
+INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
+SET SQL_MODE = 'ONLY_FULL_GROUP_BY';
+SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b;
+MAX(a)-MIN(a)
+1
+1
+1
+SELECT CEILING(MIN(a)) FROM t1 GROUP BY b;
+CEILING(MIN(a))
+1
+3
+5
+SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1
+GROUP BY b;
+CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END
+Positive
+Positive
+Positive
+SELECT a + 1 FROM t1 GROUP BY a;
+a + 1
+2
+3
+4
+5
+6
+7
+SELECT a + b FROM t1 GROUP BY b;
+ERROR 42000: 'test.t1.a' isn't in GROUP BY
+SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
+FROM t1 AS t1_outer;
+(SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
+1
+2
+3
+4
+5
+6
+SELECT 1 FROM t1 as t1_outer GROUP BY a
+HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
+1
+1
+1
+1
+1
+1
+1
+SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1)
+FROM t1 AS t1_outer GROUP BY t1_outer.b;
+ERROR 42000: 'test.t1_outer.a' isn't in GROUP BY
+SELECT 1 FROM t1 as t1_outer GROUP BY a
+HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
+ERROR 42S22: Unknown column 'test.t1_outer.b' in 'field list'
+SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
+FROM t1 AS t1_outer GROUP BY t1_outer.b;
+(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
+21
+21
+21
+SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
+FROM t1 AS t1_outer;
+(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
+3
+3
+3
+3
+3
+3
+SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1)
+FROM t1 AS t1_outer GROUP BY t1_outer.b;
+ERROR 42000: 'test.t1_outer.a' isn't in GROUP BY
+SELECT 1 FROM t1 as t1_outer
+WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1);
+1
+1
+1
+1
+1
+1
+1
+SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0;
+b
+1
+2
+3
+SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12;
+1
+1
+1
+SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1);
+1
+1
+SELECT 1 FROM t1 GROUP BY b HAVING a = 2;
+ERROR 42S22: Unknown column 'a' in 'having clause'
+SELECT 1 FROM t1 GROUP BY SUM(b);
+ERROR HY000: Invalid use of group function
+SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN
+(SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a
+HAVING SUM(t1_inner.b)+t1_outer.b > 5);
+ERROR 42000: 'test.t1_outer.b' isn't in GROUP BY
+DROP TABLE t1;
+SET SQL_MODE = '';
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.30/mysql-test/r/insert.result 2006-11-13 18:11:14 +02:00
+++ 1.31/mysql-test/r/insert.result 2007-01-23 12:34:32 +02:00
@@ -347,6 +347,27 @@ select row_count();
row_count()
1
drop table t1;
+create table t1 (f1 int unique, f2 int);
+create table t2 (f3 int, f4 int);
+create view v1 as select * from t1, t2 where f1= f3;
+insert into t1 values (1,11), (2,22);
+insert into t2 values (1,12), (2,24);
+insert into v1 (f1) values (3) on duplicate key update f3= f3 + 10;
+ERROR HY000: Can not modify more than one base table through a join view 'test.v1'
+insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10;
+select * from t1;
+f1 f2
+1 11
+2 22
+3 NULL
+insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10;
+select * from t1;
+f1 f2
+1 11
+2 22
+12 NULL
+drop view v1;
+drop table t1,t2;
create table t1 (id int primary key auto_increment, data int, unique(data));
insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120);
insert ignore into t1 values(NULL,10),(NULL,20),(NULL,110),(NULL,120),(NULL,100),(NULL,90);
--- 1.51/mysql-test/r/range.result 2006-10-23 08:29:01 +03:00
+++ 1.52/mysql-test/r/range.result 2007-01-23 11:56:43 +02:00
@@ -674,6 +674,49 @@ select a from t1 where a > 'x';
a
xx
drop table t1;
+CREATE TABLE t1 (
+OXID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
+OXPARENTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT 'oxrootid',
+OXLEFT int NOT NULL DEFAULT '0',
+OXRIGHT int NOT NULL DEFAULT '0',
+OXROOTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
+PRIMARY KEY (OXID),
+KEY OXNID (OXID),
+KEY OXLEFT (OXLEFT),
+KEY OXRIGHT (OXRIGHT),
+KEY OXROOTID (OXROOTID)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
+INSERT INTO t1 VALUES
+('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'),
+('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3,
+'d8c4177d09f8b11f5.52725521'),
+('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5,
+'d8c4177d09f8b11f5.52725521'),
+('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7,
+'d8c4177d09f8b11f5.52725521'),
+('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9,
+'d8c4177d09f8b11f5.52725521'),
+('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11,
+'d8c4177d09f8b11f5.52725521');
+EXPLAIN
+SELECT s.oxid FROM t1 v, t1 s
+WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
+s.oxleft > v.oxleft AND s.oxleft < v.oxright;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using where
+1 SIMPLE s ALL OXLEFT NULL NULL NULL 5 Range checked for each record (index map: 0x4)
+SELECT s.oxid FROM t1 v, t1 s
+WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
+s.oxleft > v.oxleft AND s.oxleft < v.oxright;
+oxid
+d8c4177d151affab2.81582770
+d8c4177d206a333d2.74422679
+d8c4177d225791924.30714720
+d8c4177d2380fc201.39666693
+d8c4177d24ccef970.14957924
+DROP TABLE t1;
End of 4.1 tests
CREATE TABLE t1 (
id int(11) NOT NULL auto_increment,
--- 1.139/mysql-test/r/select.result 2006-12-09 05:19:46 +02:00
+++ 1.140/mysql-test/r/select.result 2007-01-23 11:56:43 +02:00
@@ -3611,3 +3611,11 @@ id select_type table type possible_keys
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;
+CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
+CREATE TABLE t2 ( f11 int PRIMARY KEY );
+INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0);
+INSERT INTO t2 VALUES (62);
+SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1;
+f1 f2 f3 f4 f5 f6 checked_out f11
+1 1 1 0 0 0 0 NULL
+DROP TABLE t1, t2;
--- 1.24/mysql-test/t/func_in.test 2006-11-16 13:13:00 +02:00
+++ 1.25/mysql-test/t/func_in.test 2007-01-23 11:56:45 +02:00
@@ -254,6 +254,42 @@ select some_id from t1 where some_id not
select some_id from t1 where some_id not in(-4,-1,3423534,2342342);
drop table t1;
+#
+# BUG#20420: optimizer reports wrong keys on left join with IN
+#
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);
+
+CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (3,2),(4,2);
+
+CREATE TABLE t3 (a int PRIMARY KEY);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+
+CREATE TABLE t4 (a int PRIMARY KEY);
+INSERT INTO t4 VALUES (1),(2);
+
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t3
+ JOIN t1 ON t3.a=t1.a
+ JOIN t2 ON t3.a=t2.a
+ JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+
+SELECT STRAIGHT_JOIN * FROM t3
+ JOIN t1 ON t3.a=t1.a
+ JOIN t2 ON t3.a=t2.a
+ JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+
+EXPLAIN SELECT STRAIGHT_JOIN
+ (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
+ FROM t3, t1, t2
+ WHERE t3.a=t1.a AND t3.a=t2.a;
+
+SELECT STRAIGHT_JOIN
+ (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
+ FROM t3, t1, t2
+ WHERE t3.a=t1.a AND t3.a=t2.a;
+
+DROP TABLE t1,t2,t3,t4;
--echo End of 5.0 tests
--- 1.61/mysql-test/t/group_by.test 2006-10-19 17:16:35 +03:00
+++ 1.62/mysql-test/t/group_by.test 2007-01-23 12:34:33 +02:00
@@ -703,6 +703,57 @@ SELECT SQL_BIG_RESULT b, sum(1) FROM t1
DROP TABLE t1;
#
+# Bug #23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode
+#
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
+INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
+
+SET SQL_MODE = 'ONLY_FULL_GROUP_BY';
+SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b;
+SELECT CEILING(MIN(a)) FROM t1 GROUP BY b;
+SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1
+ GROUP BY b;
+SELECT a + 1 FROM t1 GROUP BY a;
+--error ER_WRONG_FIELD_WITH_GROUP
+SELECT a + b FROM t1 GROUP BY b;
+SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
+ FROM t1 AS t1_outer;
+SELECT 1 FROM t1 as t1_outer GROUP BY a
+ HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
+--error ER_WRONG_FIELD_WITH_GROUP
+SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1)
+ FROM t1 AS t1_outer GROUP BY t1_outer.b;
+--error ER_BAD_FIELD_ERROR
+SELECT 1 FROM t1 as t1_outer GROUP BY a
+ HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
+SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
+ FROM t1 AS t1_outer GROUP BY t1_outer.b;
+SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
+ FROM t1 AS t1_outer;
+--error ER_WRONG_FIELD_WITH_GROUP
+SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1)
+ FROM t1 AS t1_outer GROUP BY t1_outer.b;
+
+SELECT 1 FROM t1 as t1_outer
+ WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1);
+
+SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0;
+
+SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12;
+SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1);
+
+--error ER_BAD_FIELD_ERROR
+SELECT 1 FROM t1 GROUP BY b HAVING a = 2;
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT 1 FROM t1 GROUP BY SUM(b);
+--error ER_WRONG_FIELD_WITH_GROUP
+SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN
+ (SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a
+ HAVING SUM(t1_inner.b)+t1_outer.b > 5);
+DROP TABLE t1;
+SET SQL_MODE = '';
+
+#
# Bug #21174: Index degrades sort performance and
# optimizer does not honor IGNORE INDEX
#
--- 1.27/mysql-test/t/insert.test 2006-11-13 18:15:25 +02:00
+++ 1.28/mysql-test/t/insert.test 2007-01-23 12:34:33 +02:00
@@ -225,6 +225,23 @@ insert into t1 values (5, 5) on duplicat
select row_count();
drop table t1;
+#
+# Bug#25123: ON DUPLICATE KEY clause allows fields not from the insert table
+#
+create table t1 (f1 int unique, f2 int);
+create table t2 (f3 int, f4 int);
+create view v1 as select * from t1, t2 where f1= f3;
+insert into t1 values (1,11), (2,22);
+insert into t2 values (1,12), (2,24);
+--error 1393
+insert into v1 (f1) values (3) on duplicate key update f3= f3 + 10;
+insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10;
+select * from t1;
+insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10;
+select * from t1;
+drop view v1;
+drop table t1,t2;
+
# Test of INSERT IGNORE and re-using auto_increment values
create table t1 (id int primary key auto_increment, data int, unique(data));
insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120);
--- 1.43/mysql-test/t/range.test 2006-10-23 08:29:01 +03:00
+++ 1.44/mysql-test/t/range.test 2007-01-23 11:56:46 +02:00
@@ -525,6 +525,49 @@ explain select a from t1 where a > 'x';
select a from t1 where a > 'x';
drop table t1;
+#
+# Bug #24776: assertion abort for 'range checked for each record'
+#
+
+CREATE TABLE t1 (
+ OXID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
+ OXPARENTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT 'oxrootid',
+ OXLEFT int NOT NULL DEFAULT '0',
+ OXRIGHT int NOT NULL DEFAULT '0',
+ OXROOTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
+ PRIMARY KEY (OXID),
+ KEY OXNID (OXID),
+ KEY OXLEFT (OXLEFT),
+ KEY OXRIGHT (OXRIGHT),
+ KEY OXROOTID (OXROOTID)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
+
+INSERT INTO t1 VALUES
+('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'),
+('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3,
+ 'd8c4177d09f8b11f5.52725521'),
+('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5,
+ 'd8c4177d09f8b11f5.52725521'),
+('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7,
+ 'd8c4177d09f8b11f5.52725521'),
+('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9,
+ 'd8c4177d09f8b11f5.52725521'),
+('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11,
+ 'd8c4177d09f8b11f5.52725521');
+
+EXPLAIN
+SELECT s.oxid FROM t1 v, t1 s
+ WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+ v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
+ s.oxleft > v.oxleft AND s.oxleft < v.oxright;
+
+SELECT s.oxid FROM t1 v, t1 s
+ WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+ v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
+ s.oxleft > v.oxleft AND s.oxleft < v.oxright;
+
+DROP TABLE t1;
+
--echo End of 4.1 tests
#
--- 1.111/mysql-test/t/select.test 2006-11-21 23:15:30 +02:00
+++ 1.112/mysql-test/t/select.test 2007-01-23 11:56:46 +02:00
@@ -3093,3 +3093,14 @@ SELECT t3.a FROM t1,t2,t3
t3.c IN ('bb','ee');
DROP TABLE t1,t2,t3;
+
+#
+# Bug#25172: Not checked buffer size leads to a server crash
+#
+CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
+CREATE TABLE t2 ( f11 int PRIMARY KEY );
+INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0);
+INSERT INTO t2 VALUES (62);
+SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1;
+DROP TABLE t1, t2;
+
--- 1.134/mysql-test/r/func_str.result 2006-12-21 00:46:32 +02:00
+++ 1.135/mysql-test/r/func_str.result 2007-01-23 12:34:31 +02:00
@@ -1304,6 +1304,18 @@ id select_type table type possible_keys
Warnings:
Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both _latin1'y' from `test`.`t1`.`s`) > _latin1'ab')
DROP TABLE t1;
+create table t1(f1 varchar(4));
+explain extended select encode(f1,'zxcv') as 'enc' from t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found
+Warnings:
+Note 1003 select encode(`test`.`t1`.`f1`,'zxcv') AS `enc` from `test`.`t1`
+explain extended select decode(f1,'zxcv') as 'enc' from t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found
+Warnings:
+Note 1003 select decode(`test`.`t1`.`f1`,'zxcv') AS `enc` from `test`.`t1`
+drop table t1;
End of 4.1 tests
create table t1 (d decimal default null);
insert into t1 values (null);
--- 1.101/mysql-test/t/func_str.test 2006-12-21 00:46:32 +02:00
+++ 1.102/mysql-test/t/func_str.test 2007-01-23 11:56:45 +02:00
@@ -774,6 +774,14 @@ EXPLAIN EXTENDED SELECT s FROM t1 WHERE
DROP TABLE t1;
+#
+# Bug#23409: ENCODE() and DECODE() functions aren't printed correctly
+#
+create table t1(f1 varchar(4));
+explain extended select encode(f1,'zxcv') as 'enc' from t1;
+explain extended select decode(f1,'zxcv') as 'enc' from t1;
+drop table t1;
+
--echo End of 4.1 tests
#
--- 1.16/mysql-test/r/udf.result 2007-01-08 12:30:58 +02:00
+++ 1.17/mysql-test/r/udf.result 2007-01-23 11:56:44 +02:00
@@ -278,3 +278,37 @@ drop table bug18761;
select is_const((1,2,3));
ERROR 21000: Operand should contain 1 column(s)
drop function if exists is_const;
+CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
+CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
+CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
+create function f1(p1 varchar(255))
+returns varchar(255)
+begin
+return metaphon(p1);
+end//
+create function f2(p1 varchar(255))
+returns double
+begin
+return myfunc_double(p1);
+end//
+create function f3(p1 varchar(255))
+returns double
+begin
+return myfunc_int(p1);
+end//
+select f3(NULL);
+f3(NULL)
+0
+select f2(NULL);
+f2(NULL)
+NULL
+select f1(NULL);
+f1(NULL)
+NULL
+drop function f1;
+drop function f2;
+drop function f3;
+drop function metaphon;
+drop function myfunc_double;
+drop function myfunc_int;
+End of 5.0 tests.
--- 1.16/mysql-test/t/udf.test 2007-01-08 12:30:59 +02:00
+++ 1.17/mysql-test/t/udf.test 2007-01-23 11:56:47 +02:00
@@ -294,3 +294,50 @@ drop table bug18761;
select is_const((1,2,3));
drop function if exists is_const;
+
+#
+# Bug #25382: Passing NULL to an UDF called from stored procedures
+# crashes server
+#
+--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
+eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_LIB";
+
+--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
+eval CREATE FUNCTION myfunc_double RETURNS REAL SONAME "$UDF_EXAMPLE_LIB";
+
+--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
+eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB";
+
+delimiter //;
+create function f1(p1 varchar(255))
+returns varchar(255)
+begin
+ return metaphon(p1);
+end//
+
+create function f2(p1 varchar(255))
+returns double
+begin
+ return myfunc_double(p1);
+end//
+
+create function f3(p1 varchar(255))
+returns double
+begin
+ return myfunc_int(p1);
+end//
+
+delimiter ;//
+
+select f3(NULL);
+select f2(NULL);
+select f1(NULL);
+
+drop function f1;
+drop function f2;
+drop function f3;
+drop function metaphon;
+drop function myfunc_double;
+drop function myfunc_int;
+
+--echo End of 5.0 tests.
--- 1.10/mysql-test/r/sp-code.result 2006-08-12 18:32:44 +03:00
+++ 1.11/mysql-test/r/sp-code.result 2007-01-23 11:56:43 +02:00
@@ -187,7 +187,7 @@ Pos Instruction
32 set v_dig@4 (v_dig@4 + 1)
33 stmt 4 "update sudoku_work set dig = v_dig wh..."
34 set v_tcounter@6 (v_tcounter@6 + 1)
-35 jump_if_not 37(37) not(`test`.`sudoku_digit_ok`(v_row@7,v_col@8,v_dig@4))
+35 jump_if_not 37(37) (not(`test`.`sudoku_digit_ok`(v_row@7,v_col@8,v_dig@4)))
36 jump 15
37 set v_i@3 (v_i@3 + 1)
38 jump 15
--- 1.188/sql/sql_prepare.cc 2007-01-02 15:55:15 +02:00
+++ 1.189/sql/sql_prepare.cc 2007-01-23 11:56:55 +02:00
@@ -1677,7 +1677,7 @@ static bool check_prepared_statement(Pre
case SQLCOM_INSERT:
res= mysql_test_insert(stmt, tables, lex->field_list,
lex->many_values,
- select_lex->item_list, lex->value_list,
+ lex->update_list, lex->value_list,
lex->duplicates);
break;
| Thread |
|---|
| • bk commit into 5.1 tree (gkodinov:1.2393) | kgeorge | 23 Jan |
