Below is the list of changes that have just been committed into a local
5.1 repository of hf. When hf does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2006-11-08 19:09:39+04:00, holyfoot@deer.(none) +43 -0
Merge mysql.com:/home/hf/work/mysql-5.0.clean
into mysql.com:/home/hf/work/mysql-5.1.clean
MERGE: 1.1810.2078.54
include/my_time.h@stripped, 2006-11-08 18:51:40+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.11.1.1
mysql-test/r/func_group.result@stripped, 2006-11-08 18:51:40+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.49.1.5
mysql-test/r/gis-rtree.result@stripped, 2006-11-08 18:51:40+04:00, holyfoot@deer.(none) +0 -8
Auto merged
MERGE: 1.14.1.7
mysql-test/r/order_by.result@stripped, 2006-11-08 18:51:40+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.55.1.1
mysql-test/r/subselect.result@stripped, 2006-11-08 18:51:40+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.134.1.33
mysql-test/r/type_datetime.result@stripped, 2006-11-08 18:51:40+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.30.1.3
mysql-test/r/type_newdecimal.result@stripped, 2006-11-08 18:52:55+04:00, holyfoot@deer.(none) +0 -0
SCCS merged
MERGE: 1.36.1.7
mysql-test/r/udf.result@stripped, 2006-11-08 18:51:40+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.3.1.5
mysql-test/t/func_group.test@stripped, 2006-11-08 18:51:40+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.48.1.2
mysql-test/t/gis-rtree.test@stripped, 2006-11-08 18:51:41+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.18.1.1
mysql-test/t/type_datetime.test@stripped, 2006-11-08 18:51:41+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.18.1.2
mysql-test/t/type_newdecimal.test@stripped, 2006-11-08 18:53:36+04:00, holyfoot@deer.(none) +0 -2
merging
MERGE: 1.33.1.6
mysql-test/t/udf.test@stripped, 2006-11-08 18:51:41+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.8.1.1
sql-common/my_time.c@stripped, 2006-11-08 18:51:45+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.19.1.2
sql/field.cc@stripped, 2006-11-08 18:51:41+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.256.1.73
sql/field.h@stripped, 2006-11-08 18:51:41+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.153.12.5
sql/filesort.cc@stripped, 2006-11-08 18:51:41+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.105.1.3
sql/item.cc@stripped, 2006-11-08 18:54:25+04:00, holyfoot@deer.(none) +0 -1
merging
MERGE: 1.113.1.127
sql/item.h@stripped, 2006-11-08 18:51:41+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.183.1.28
sql/item_cmpfunc.cc@stripped, 2006-11-08 18:51:41+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.187.1.37
sql/item_cmpfunc.h@stripped, 2006-11-08 18:51:42+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.122.2.10
sql/item_func.cc@stripped, 2006-11-08 18:51:42+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.270.1.39
sql/item_func.h@stripped, 2006-11-08 18:51:42+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.136.2.19
sql/item_subselect.cc@stripped, 2006-11-08 18:56:08+04:00, holyfoot@deer.(none) +0 -0
SCCS merged
MERGE: 1.113.1.27
sql/item_subselect.h@stripped, 2006-11-08 18:51:42+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.77.1.4
sql/item_sum.cc@stripped, 2006-11-08 18:51:42+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.170.1.12
sql/item_sum.h@stripped, 2006-11-08 18:51:42+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.102.1.2
sql/item_timefunc.cc@stripped, 2006-11-08 18:51:42+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.100.1.27
sql/item_timefunc.h@stripped, 2006-11-08 19:07:38+04:00, holyfoot@deer.(none) +6 -23
merging
MERGE: 1.63.1.6
sql/my_decimal.cc@stripped, 2006-11-08 18:51:42+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.11.1.1
sql/my_decimal.h@stripped, 2006-11-08 18:51:42+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.10.1.1
sql/mysql_priv.h@stripped, 2006-11-08 18:51:42+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.290.1.128
sql/records.cc@stripped, 2006-11-08 18:51:42+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.40.1.4
sql/sql_base.cc@stripped, 2006-11-08 18:51:43+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.235.66.11
sql/sql_lex.cc@stripped, 2006-11-08 19:08:41+04:00, holyfoot@deer.(none) +0 -0
SCCS merged
MERGE: 1.142.1.62
sql/sql_lex.h@stripped, 2006-11-08 18:51:43+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.175.20.6
sql/sql_select.cc@stripped, 2006-11-08 18:51:43+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.312.1.158
sql/sql_select.h@stripped, 2006-11-08 19:09:29+04:00, holyfoot@deer.(none) +2 -2
merging
MERGE: 1.92.1.20
sql/sql_show.cc@stripped, 2006-11-08 18:51:44+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.253.1.77
sql/sql_table.cc@stripped, 2006-11-08 18:51:44+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.239.1.85
sql/sql_yacc.yy@stripped, 2006-11-08 18:51:44+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.371.1.121
sql/table.cc@stripped, 2006-11-08 18:51:45+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.160.34.7
sql/table.h@stripped, 2006-11-08 18:51:45+04:00, holyfoot@deer.(none) +0 -0
Auto merged
MERGE: 1.102.1.33
# 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: holyfoot
# Host: deer.(none)
# Root: /home/hf/work/mysql-5.1.clean/RESYNC
--- 1.346/sql/field.cc 2006-11-08 19:09:51 +04:00
+++ 1.347/sql/field.cc 2006-11-08 19:09:51 +04:00
@@ -2477,6 +2477,13 @@ int Field_new_decimal::store_decimal(con
}
+int Field_new_decimal::store_time(TIME *ltime, timestamp_type t_type)
+{
+ my_decimal decimal_value;
+ return store_value(date2my_decimal(ltime, &decimal_value));
+}
+
+
double Field_new_decimal::val_real(void)
{
ASSERT_COLUMN_MARKED_FOR_READ;
@@ -5530,7 +5537,21 @@ int Field_newdate::store_time(TIME *ltim
long tmp;
int error= 0;
if (type == MYSQL_TIMESTAMP_DATE || type == MYSQL_TIMESTAMP_DATETIME)
+ {
tmp=ltime->year*16*32+ltime->month*32+ltime->day;
+ if ((my_bool)check_date(ltime, tmp,
+ (TIME_FUZZY_DATE |
+ (current_thd->variables.sql_mode &
+ (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE |
+ MODE_INVALID_DATES))), &error))
+ {
+ char buff[12];
+ String str(buff, sizeof(buff), &my_charset_latin1);
+ make_date((DATE_TIME_FORMAT *) 0, ltime, &str);
+ set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED,
+ str.ptr(), str.length(), MYSQL_TIMESTAMP_DATE, 1);
+ }
+ }
else
{
tmp=0;
@@ -5745,8 +5766,22 @@ int Field_datetime::store_time(TIME *lti
structure always fit into DATETIME range.
*/
if (type == MYSQL_TIMESTAMP_DATE || type == MYSQL_TIMESTAMP_DATETIME)
+ {
tmp=((ltime->year*10000L+ltime->month*100+ltime->day)*LL(1000000)+
(ltime->hour*10000L+ltime->minute*100+ltime->second));
+ if ((my_bool)check_date(ltime, tmp,
+ (TIME_FUZZY_DATE |
+ (current_thd->variables.sql_mode &
+ (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE |
+ MODE_INVALID_DATES))), &error))
+ {
+ char buff[19];
+ String str(buff, sizeof(buff), &my_charset_latin1);
+ make_datetime((DATE_TIME_FORMAT *) 0, ltime, &str);
+ set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED,
+ str.ptr(), str.length(), MYSQL_TIMESTAMP_DATETIME,1);
+ }
+ }
else
{
tmp=0;
--- 1.190/sql/field.h 2006-11-08 19:09:51 +04:00
+++ 1.191/sql/field.h 2006-11-08 19:09:51 +04:00
@@ -555,6 +555,7 @@ public:
int store(const char *to, uint length, CHARSET_INFO *charset);
int store(double nr);
int store(longlong nr, bool unsigned_val);
+ int store_time(TIME *ltime, timestamp_type t_type);
int store_decimal(const my_decimal *);
double val_real(void);
longlong val_int(void);
--- 1.114/sql/filesort.cc 2006-11-08 19:09:51 +04:00
+++ 1.115/sql/filesort.cc 2006-11-08 19:09:51 +04:00
@@ -115,6 +115,8 @@ ha_rows filesort(THD *thd, TABLE *table,
DBUG_PUSH(""); /* No DBUG here */
#endif
FILESORT_INFO table_sort;
+ TABLE_LIST *tab= table->pos_in_table_list;
+ Item_subselect *subselect= tab ? tab->containing_subselect() : 0;
/*
Don't use table->sort in filesort as it is also used by
QUICK_INDEX_MERGE_SELECT. Work with a copy and put it back at the end
@@ -127,7 +129,6 @@ ha_rows filesort(THD *thd, TABLE *table,
my_b_clear(&tempfile);
my_b_clear(&buffpek_pointers);
buffpek=0;
- sort_keys= (uchar **) NULL;
error= 1;
bzero((char*) ¶m,sizeof(param));
param.sort_length= sortlength(thd, sortorder, s_length, &multi_byte_charset);
@@ -208,13 +209,15 @@ ha_rows filesort(THD *thd, TABLE *table,
ulong old_memavl;
ulong keys= memavl/(param.rec_length+sizeof(char*));
param.keys=(uint) min(records+1, keys);
- if ((sort_keys= (uchar **) make_char_array(param.keys, param.rec_length,
- MYF(0))))
+ if (table_sort.sort_keys ||
+ (table_sort.sort_keys= (uchar **) make_char_array(param.keys, param.rec_length,
+ MYF(0))))
break;
old_memavl=memavl;
if ((memavl=memavl/4*3) < min_sort_memory && old_memavl > min_sort_memory)
memavl= min_sort_memory;
}
+ sort_keys= table_sort.sort_keys;
if (memavl < min_sort_memory)
{
my_error(ER_OUTOFMEMORY,MYF(ME_ERROR+ME_WAITTANG),
@@ -241,8 +244,12 @@ ha_rows filesort(THD *thd, TABLE *table,
}
else
{
- if (!(buffpek=read_buffpek_from_file(&buffpek_pointers, maxbuffer)))
+ if (!table_sort.buffpek && table_sort.buffpek_len < maxbuffer &&
+ !(table_sort.buffpek=
+ (byte *) read_buffpek_from_file(&buffpek_pointers, maxbuffer)))
goto err;
+ buffpek= (BUFFPEK *) table_sort.buffpek;
+ table_sort.buffpek_len= maxbuffer;
close_cached_file(&buffpek_pointers);
/* Open cached file if it isn't open */
if (! my_b_inited(outfile) &&
@@ -275,8 +282,14 @@ ha_rows filesort(THD *thd, TABLE *table,
err:
if (param.tmp_buffer)
x_free(param.tmp_buffer);
- x_free((gptr) sort_keys);
- x_free((gptr) buffpek);
+ if (!subselect || !subselect->is_uncacheable())
+ {
+ x_free((gptr) sort_keys);
+ table_sort.sort_keys= 0;
+ x_free((gptr) buffpek);
+ table_sort.buffpek= 0;
+ table_sort.buffpek_len= 0;
+ }
close_cached_file(&tempfile);
close_cached_file(&buffpek_pointers);
if (my_b_inited(outfile))
@@ -307,12 +320,26 @@ ha_rows filesort(THD *thd, TABLE *table,
} /* filesort */
-void filesort_free_buffers(TABLE *table)
+void filesort_free_buffers(TABLE *table, bool full)
{
if (table->sort.record_pointers)
{
my_free((gptr) table->sort.record_pointers,MYF(0));
table->sort.record_pointers=0;
+ }
+ if (full)
+ {
+ if (table->sort.sort_keys )
+ {
+ x_free((gptr) table->sort.sort_keys);
+ table->sort.sort_keys= 0;
+ }
+ if (table->sort.buffpek)
+ {
+ x_free((gptr) table->sort.buffpek);
+ table->sort.buffpek= 0;
+ table->sort.buffpek_len= 0;
+ }
}
if (table->sort.addon_buf)
{
--- 1.215/sql/item.cc 2006-11-08 19:09:51 +04:00
+++ 1.216/sql/item.cc 2006-11-08 19:09:51 +04:00
@@ -269,6 +269,34 @@ my_decimal *Item::val_decimal_from_strin
}
+my_decimal *Item::val_decimal_from_date(my_decimal *decimal_value)
+{
+ DBUG_ASSERT(fixed == 1);
+ TIME ltime;
+ longlong date;
+ if (get_date(<ime, TIME_FUZZY_DATE))
+ {
+ my_decimal_set_zero(decimal_value);
+ return 0;
+ }
+ return date2my_decimal(<ime, decimal_value);
+}
+
+
+my_decimal *Item::val_decimal_from_time(my_decimal *decimal_value)
+{
+ DBUG_ASSERT(fixed == 1);
+ TIME ltime;
+ longlong date;
+ if (get_time(<ime))
+ {
+ my_decimal_set_zero(decimal_value);
+ return 0;
+ }
+ return date2my_decimal(<ime, decimal_value);
+}
+
+
double Item::val_real_from_decimal()
{
/* Note that fix_fields may not be called for Item_avg_field items */
@@ -292,6 +320,25 @@ longlong Item::val_int_from_decimal()
return result;
}
+int Item::save_time_in_field(Field *field)
+{
+ TIME ltime;
+ if (get_time(<ime))
+ return set_field_to_null(field);
+ field->set_notnull();
+ return field->store_time(<ime, MYSQL_TIMESTAMP_TIME);
+}
+
+
+int Item::save_date_in_field(Field *field)
+{
+ TIME ltime;
+ if (get_date(<ime, TIME_FUZZY_DATE))
+ return set_field_to_null(field);
+ field->set_notnull();
+ return field->store_time(<ime, MYSQL_TIMESTAMP_DATETIME);
+}
+
Item::Item():
rsize(0), name(0), orig_name(0), name_length(0), fixed(0),
@@ -1176,6 +1223,28 @@ void Item_name_const::print(String *str)
/*
+ need a special class to adjust printing : references to aggregate functions
+ must not be printed as refs because the aggregate functions that are added to
+ the front of select list are not printed as well.
+*/
+class Item_aggregate_ref : public Item_ref
+{
+public:
+ Item_aggregate_ref(Name_resolution_context *context_arg, Item **item,
+ const char *table_name_arg, const char *field_name_arg)
+ :Item_ref(context_arg, item, table_name_arg, field_name_arg) {}
+
+ void print (String *str)
+ {
+ if (ref)
+ (*ref)->print(str);
+ else
+ Item_ident::print(str);
+ }
+};
+
+
+/*
Move SUM items out from item tree and replace with reference
SYNOPSIS
@@ -1228,8 +1297,8 @@ void Item::split_sum_func2(THD *thd, Ite
Item *new_item, *real_itm= real_item();
ref_pointer_array[el]= real_itm;
- if (!(new_item= new Item_ref(&thd->lex->current_select->context,
- ref_pointer_array + el, 0, name)))
+ if (!(new_item= new Item_aggregate_ref(&thd->lex->current_select->context,
+ ref_pointer_array + el, 0, name)))
return; // fatal_error is set
fields.push_front(real_itm);
thd->change_item_tree(ref, new_item);
@@ -3675,8 +3744,35 @@ bool Item_field::fix_fields(THD *thd, It
if (res != (Item **)not_found_item &&
(*res)->type() == Item::FIELD_ITEM)
{
- set_field((*((Item_field**)res))->field);
- return 0;
+ if ((*res)->type() == Item::FIELD_ITEM)
+ {
+ /*
+ It's an Item_field referencing another Item_field in the select
+ list.
+ use the field from the Item_field in the select list and leave
+ the Item_field instance in place.
+ */
+ set_field((*((Item_field**)res))->field);
+ return 0;
+ }
+ else
+ {
+ /*
+ It's not an Item_field in the select list so we must make a new
+ Item_ref to point to the Item in the select list and replace the
+ Item_field created by the parser with the new Item_ref.
+ */
+ Item_ref *rf= new Item_ref(context, db_name,table_name,field_name);
+ if (!rf)
+ return 1;
+ thd->change_item_tree(reference, rf);
+ /*
+ Because Item_ref never substitutes itself with other items
+ in Item_ref::fix_fields(), we can safely use the original
+ pointer to it even after fix_fields()
+ */
+ return rf->fix_fields(thd, reference) || rf->check_cols(1);
+ }
}
}
if ((ret= fix_outer_field(thd, &from_field, reference)) < 0)
--- 1.212/sql/item.h 2006-11-08 19:09:51 +04:00
+++ 1.213/sql/item.h 2006-11-08 19:09:51 +04:00
@@ -658,9 +658,14 @@ public:
my_decimal *val_decimal_from_real(my_decimal *decimal_value);
my_decimal *val_decimal_from_int(my_decimal *decimal_value);
my_decimal *val_decimal_from_string(my_decimal *decimal_value);
+ my_decimal *val_decimal_from_date(my_decimal *decimal_value);
+ my_decimal *val_decimal_from_time(my_decimal *decimal_value);
longlong val_int_from_decimal();
double val_real_from_decimal();
+ int save_time_in_field(Field *field);
+ int save_date_in_field(Field *field);
+
virtual Field *get_tmp_table_field() { return 0; }
/* This is also used to create fields in CREATE ... SELECT: */
virtual Field *tmp_table_field(TABLE *t_arg) { return 0; }
@@ -2060,6 +2065,16 @@ public:
class Item_in_subselect;
+
+
+/*
+ An object of this class:
+ - Converts val_XXX() calls to ref->val_XXX_result() calls, like Item_ref.
+ - Sets owner->was_null=TRUE if it has returned a NULL value from any
+ val_XXX() function. This allows to inject an Item_ref_null_helper
+ object into subquery and then check if the subquery has produced a row
+ with NULL value.
+*/
class Item_ref_null_helper: public Item_ref
{
--- 1.225/sql/item_cmpfunc.cc 2006-11-08 19:09:51 +04:00
+++ 1.226/sql/item_cmpfunc.cc 2006-11-08 19:09:51 +04:00
@@ -830,9 +830,41 @@ longlong Item_in_optimizer::val_int()
{
DBUG_ASSERT(fixed == 1);
cache->store(args[0]);
+
if (cache->null_value)
{
- null_value= 1;
+ if (((Item_in_subselect*)args[1])->is_top_level_item())
+ {
+ /*
+ We're evaluating "NULL IN (SELECT ...)". The result can be NULL or
+ FALSE, and we can return one instead of another. Just return NULL.
+ */
+ null_value= 1;
+ }
+ else
+ {
+ if (!((Item_in_subselect*)args[1])->is_correlated &&
+ result_for_null_param != UNKNOWN)
+ {
+ /* Use cached value from previous execution */
+ null_value= result_for_null_param;
+ }
+ else
+ {
+ /*
+ We're evaluating "NULL IN (SELECT ...)". The result is:
+ FALSE if SELECT produces an empty set, or
+ NULL otherwise.
+ We disable the predicates we've pushed down into subselect, run the
+ subselect and see if it has produced any rows.
+ */
+ ((Item_in_subselect*)args[1])->enable_pushed_conds= FALSE;
+ longlong tmp= args[1]->val_bool_result();
+ result_for_null_param= null_value=
+ !((Item_in_subselect*)args[1])->engine->no_rows();
+ ((Item_in_subselect*)args[1])->enable_pushed_conds= TRUE;
+ }
+ }
return 0;
}
bool tmp= args[1]->val_bool_result();
--- 1.136/sql/item_cmpfunc.h 2006-11-08 19:09:51 +04:00
+++ 1.137/sql/item_cmpfunc.h 2006-11-08 19:09:51 +04:00
@@ -100,25 +100,44 @@ public:
};
class Item_cache;
+#define UNKNOWN ((my_bool)-1)
+
+
+/*
+ Item_in_optimizer(left_expr, Item_in_subselect(...))
+
+ Item_in_optimizer is used to wrap an instance of Item_in_subselect. This
+ class does the following:
+ - Evaluate the left expression and store it in Item_cache_* object (to
+ avoid re-evaluating it many times during subquery execution)
+ - Shortcut the evaluation of "NULL IN (...)" to NULL in the cases where we
+ don't care if the result is NULL or FALSE.
+
+ NOTE
+ It is not quite clear why the above listed functionality should be
+ placed into a separate class called 'Item_in_optimizer'.
+*/
+
class Item_in_optimizer: public Item_bool_func
{
protected:
Item_cache *cache;
bool save_cache;
+ /*
+ Stores the value of "NULL IN (SELECT ...)" for uncorrelated subqueries:
+ UNKNOWN - "NULL in (SELECT ...)" has not yet been evaluated
+ FALSE - result is FALSE
+ TRUE - result is NULL
+ */
+ my_bool result_for_null_param;
public:
Item_in_optimizer(Item *a, Item_in_subselect *b):
- Item_bool_func(a, my_reinterpret_cast(Item *)(b)), cache(0), save_cache(0)
+ Item_bool_func(a, my_reinterpret_cast(Item *)(b)), cache(0),
+ save_cache(0), result_for_null_param(UNKNOWN)
{}
bool fix_fields(THD *, Item **);
bool fix_left(THD *thd, Item **ref);
bool is_null();
- /*
- Item_in_optimizer item is special boolean function. On value request
- (one of val, val_int or val_str methods) it evaluate left expression
- of IN by storing it value in cache item (one of Item_cache* items),
- then it test cache is it NULL. If left expression (cache) is NULL then
- Item_in_optimizer return NULL, else it evaluate Item_in_subselect.
- */
longlong val_int();
void cleanup();
const char *func_name() const { return "<in_optimizer>"; }
@@ -258,9 +277,11 @@ public:
class Item_maxmin_subselect;
/*
+ trigcond<param>(arg) ::= param? arg : TRUE
+
The class Item_func_trig_cond is used for guarded predicates
which are employed only for internal purposes.
- A guarded predicates is an object consisting of an a regular or
+ A guarded predicate is an object consisting of an a regular or
a guarded predicate P and a pointer to a boolean guard variable g.
A guarded predicate P/g is evaluated to true if the value of the
guard g is false, otherwise it is evaluated to the same value that
@@ -278,6 +299,10 @@ class Item_maxmin_subselect;
Objects of this class are built only for query execution after
the execution plan has been already selected. That's why this
class needs only val_int out of generic methods.
+
+ Current uses of Item_func_trig_cond objects:
+ - To wrap selection conditions when executing outer joins
+ - To wrap condition that is pushed down into subquery
*/
class Item_func_trig_cond: public Item_bool_func
@@ -1095,6 +1120,11 @@ public:
/* Functions used by HAVING for rewriting IN subquery */
class Item_in_subselect;
+
+/*
+ This is like IS NOT NULL but it also remembers if it ever has
+ encountered a NULL.
+*/
class Item_is_not_null_test :public Item_func_isnull
{
Item_in_subselect* owner;
--- 1.322/sql/item_func.cc 2006-11-08 19:09:51 +04:00
+++ 1.323/sql/item_func.cc 2006-11-08 19:09:51 +04:00
@@ -2886,6 +2886,20 @@ void Item_udf_func::cleanup()
}
+void Item_udf_func::print(String *str)
+{
+ str->append(func_name());
+ str->append('(');
+ for (uint i=0 ; i < arg_count ; i++)
+ {
+ if (i != 0)
+ str->append(',');
+ args[i]->print_item_w_name(str);
+ }
+ str->append(')');
+}
+
+
double Item_func_udf_float::val_real()
{
DBUG_ASSERT(fixed == 1);
--- 1.149/sql/item_func.h 2006-11-08 19:09:51 +04:00
+++ 1.150/sql/item_func.h 2006-11-08 19:09:51 +04:00
@@ -979,6 +979,7 @@ public:
Item_result result_type () const { return udf.result_type(); }
table_map not_null_tables() const { return 0; }
bool is_expensive() { return 1; }
+ void print(String *str);
};
--- 1.192/sql/item_sum.cc 2006-11-08 19:09:51 +04:00
+++ 1.193/sql/item_sum.cc 2006-11-08 19:09:51 +04:00
@@ -903,6 +903,7 @@ bool Item_sum_distinct::setup(THD *thd)
tree= new Unique(simple_raw_key_cmp, &tree_key_length, tree_key_length,
thd->variables.max_heap_table_size);
+ is_evaluated= FALSE;
DBUG_RETURN(tree == 0);
}
@@ -910,6 +911,7 @@ bool Item_sum_distinct::setup(THD *thd)
bool Item_sum_distinct::add()
{
args[0]->save_in_field(table->field[0], FALSE);
+ is_evaluated= FALSE;
if (!table->field[0]->is_null())
{
DBUG_ASSERT(tree);
@@ -939,6 +941,7 @@ void Item_sum_distinct::clear()
DBUG_ASSERT(tree != 0); /* we always have a tree */
null_value= 1;
tree->reset();
+ is_evaluated= FALSE;
DBUG_VOID_RETURN;
}
@@ -948,6 +951,7 @@ void Item_sum_distinct::cleanup()
delete tree;
tree= 0;
table= 0;
+ is_evaluated= FALSE;
}
Item_sum_distinct::~Item_sum_distinct()
@@ -959,16 +963,20 @@ Item_sum_distinct::~Item_sum_distinct()
void Item_sum_distinct::calculate_val_and_count()
{
- count= 0;
- val.traits->set_zero(&val);
- /*
- We don't have a tree only if 'setup()' hasn't been called;
- this is the case of sql_select.cc:return_zero_rows.
- */
- if (tree)
+ if (!is_evaluated)
{
- table->field[0]->set_notnull();
- tree->walk(item_sum_distinct_walk, (void*) this);
+ count= 0;
+ val.traits->set_zero(&val);
+ /*
+ We don't have a tree only if 'setup()' hasn't been called;
+ this is the case of sql_select.cc:return_zero_rows.
+ */
+ if (tree)
+ {
+ table->field[0]->set_notnull();
+ tree->walk(item_sum_distinct_walk, (void*) this);
+ }
+ is_evaluated= TRUE;
}
}
@@ -1024,9 +1032,13 @@ Item_sum_avg_distinct::fix_length_and_de
void
Item_sum_avg_distinct::calculate_val_and_count()
{
- Item_sum_distinct::calculate_val_and_count();
- if (count)
- val.traits->div(&val, count);
+ if (!is_evaluated)
+ {
+ Item_sum_distinct::calculate_val_and_count();
+ if (count)
+ val.traits->div(&val, count);
+ is_evaluated= TRUE;
+ }
}
@@ -2496,6 +2508,7 @@ void Item_sum_count_distinct::cleanup()
*/
delete tree;
tree= 0;
+ is_evaluated= FALSE;
if (table)
{
free_tmp_table(table->in_use, table);
@@ -2517,6 +2530,7 @@ void Item_sum_count_distinct::make_uniqu
original= 0;
force_copy_fields= 1;
tree= 0;
+ is_evaluated= FALSE;
tmp_table_param= 0;
always_null= FALSE;
}
@@ -2636,6 +2650,7 @@ bool Item_sum_count_distinct::setup(THD
but this has to be handled - otherwise someone can crash
the server with a DoS attack
*/
+ is_evaluated= FALSE;
if (! tree)
return TRUE;
}
@@ -2652,8 +2667,11 @@ Item *Item_sum_count_distinct::copy_or_s
void Item_sum_count_distinct::clear()
{
/* tree and table can be both null only if always_null */
+ is_evaluated= FALSE;
if (tree)
+ {
tree->reset();
+ }
else if (table)
{
table->file->extra(HA_EXTRA_NO_CACHE);
@@ -2674,6 +2692,7 @@ bool Item_sum_count_distinct::add()
if ((*field)->is_real_null(0))
return 0; // Don't count NULL
+ is_evaluated= FALSE;
if (tree)
{
/*
@@ -2698,12 +2717,14 @@ longlong Item_sum_count_distinct::val_in
return LL(0);
if (tree)
{
- ulonglong count;
+ if (is_evaluated)
+ return count;
if (tree->elements == 0)
return (longlong) tree->elements_in_tree(); // everything fits in memory
count= 0;
tree->walk(count_distinct_walk, (void*) &count);
+ is_evaluated= TRUE;
return (longlong) count;
}
table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
--- 1.104/sql/item_sum.h 2006-11-08 19:09:51 +04:00
+++ 1.105/sql/item_sum.h 2006-11-08 19:09:51 +04:00
@@ -321,12 +321,23 @@ public:
class Item_sum_num :public Item_sum
{
+protected:
+ /*
+ val_xxx() functions may be called several times during the execution of a
+ query. Derived classes that require extensive calculation in val_xxx()
+ maintain cache of aggregate value. This variable governs the validity of
+ that cache.
+ */
+ bool is_evaluated;
public:
- Item_sum_num() :Item_sum() {}
- Item_sum_num(Item *item_par) :Item_sum(item_par) {}
- Item_sum_num(Item *a, Item* b) :Item_sum(a,b) {}
- Item_sum_num(List<Item> &list) :Item_sum(list) {}
- Item_sum_num(THD *thd, Item_sum_num *item) :Item_sum(thd, item) {}
+ Item_sum_num() :Item_sum(),is_evaluated(FALSE) {}
+ Item_sum_num(Item *item_par)
+ :Item_sum(item_par), is_evaluated(FALSE) {}
+ Item_sum_num(Item *a, Item* b) :Item_sum(a,b),is_evaluated(FALSE) {}
+ Item_sum_num(List<Item> &list)
+ :Item_sum(list), is_evaluated(FALSE) {}
+ Item_sum_num(THD *thd, Item_sum_num *item)
+ :Item_sum(thd, item),is_evaluated(item->is_evaluated) {}
bool fix_fields(THD *, Item **);
longlong val_int()
{
@@ -509,6 +520,12 @@ class Item_sum_count_distinct :public It
*/
Unique *tree;
/*
+ Storage for the value of count between calls to val_int() so val_int()
+ will not recalculate on each call. Validitiy of the value is stored in
+ is_evaluated.
+ */
+ longlong count;
+ /*
Following is 0 normal object and pointer to original one for copy
(to correctly free resources)
*/
@@ -525,14 +542,15 @@ class Item_sum_count_distinct :public It
public:
Item_sum_count_distinct(List<Item> &list)
:Item_sum_int(list), table(0), field_lengths(0), tmp_table_param(0),
- force_copy_fields(0), tree(0), original(0), always_null(FALSE)
+ force_copy_fields(0), tree(0), count(0),
+ original(0), always_null(FALSE)
{ quick_group= 0; }
Item_sum_count_distinct(THD *thd, Item_sum_count_distinct *item)
:Item_sum_int(thd, item), table(item->table),
field_lengths(item->field_lengths),
tmp_table_param(item->tmp_table_param),
- force_copy_fields(0), tree(item->tree), original(item),
- tree_key_length(item->tree_key_length),
+ force_copy_fields(0), tree(item->tree), count(item->count),
+ original(item), tree_key_length(item->tree_key_length),
always_null(item->always_null)
{}
~Item_sum_count_distinct();
--- 1.137/sql/item_timefunc.cc 2006-11-08 19:09:51 +04:00
+++ 1.138/sql/item_timefunc.cc 2006-11-08 19:09:51 +04:00
@@ -1270,17 +1270,6 @@ String *Item_date::val_str(String *str)
}
-int Item_date::save_in_field(Field *field, bool no_conversions)
-{
- TIME ltime;
- if (get_date(<ime, TIME_FUZZY_DATE))
- return set_field_to_null(field);
- field->set_notnull();
- field->store_time(<ime, MYSQL_TIMESTAMP_DATE);
- return 0;
-}
-
-
longlong Item_date::val_int()
{
DBUG_ASSERT(fixed == 1);
--- 1.75/sql/item_timefunc.h 2006-11-08 19:09:51 +04:00
+++ 1.76/sql/item_timefunc.h 2006-11-08 19:09:51 +04:00
@@ -360,12 +360,20 @@ public:
decimals=0;
max_length=MAX_DATE_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
}
- int save_in_field(Field *to, bool no_conversions);
Field *tmp_table_field(TABLE *table)
{
return tmp_table_field_from_field_type(table, 0);
}
bool result_as_longlong() { return TRUE; }
+ my_decimal *val_decimal(my_decimal *decimal_value)
+ {
+ DBUG_ASSERT(fixed == 1);
+ return val_decimal_from_date(decimal_value);
+ }
+ int save_in_field(Field *field, bool no_conversions)
+ {
+ return save_date_in_field(field);
+ }
};
@@ -382,29 +390,61 @@ public:
return tmp_table_field_from_field_type(table, 0);
}
bool result_as_longlong() { return TRUE; }
+ my_decimal *val_decimal(my_decimal *decimal_value)
+ {
+ DBUG_ASSERT(fixed == 1);
+ return val_decimal_from_date(decimal_value);
+ }
+ int save_in_field(Field *field, bool no_conversions)
+ {
+ return save_date_in_field(field);
+ }
+};
+
+
+class Item_str_timefunc :public Item_str_func
+{
+public:
+ Item_str_timefunc() :Item_str_func() {}
+ Item_str_timefunc(Item *a) :Item_str_func(a) {}
+ Item_str_timefunc(Item *a,Item *b) :Item_str_func(a,b) {}
+ Item_str_timefunc(Item *a, Item *b, Item *c) :Item_str_func(a, b ,c) {}
+ enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
+ void fix_length_and_dec()
+ {
+ decimals=0;
+ max_length=MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
+ }
+ Field *tmp_table_field(TABLE *table)
+ {
+ return tmp_table_field_from_field_type(table, 0);
+ }
+ my_decimal *val_decimal(my_decimal *decimal_value)
+ {
+ DBUG_ASSERT(fixed == 1);
+ return val_decimal_from_time(decimal_value);
+ }
+ int save_in_field(Field *field, bool no_conversions)
+ {
+ return save_time_in_field(field);
+ }
};
/* Abstract CURTIME function. Children should define what time zone is used */
-class Item_func_curtime :public Item_func
+class Item_func_curtime :public Item_str_timefunc
{
longlong value;
char buff[9*2+32];
uint buff_length;
public:
- Item_func_curtime() :Item_func() {}
- Item_func_curtime(Item *a) :Item_func(a) {}
- enum Item_result result_type () const { return STRING_RESULT; }
- enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
+ Item_func_curtime() :Item_str_timefunc() {}
+ Item_func_curtime(Item *a) :Item_str_timefunc(a) {}
double val_real() { DBUG_ASSERT(fixed == 1); return (double) value; }
longlong val_int() { DBUG_ASSERT(fixed == 1); return value; }
String *val_str(String *str);
void fix_length_and_dec();
- Field *tmp_table_field(TABLE *table)
- {
- return tmp_table_field_from_field_type(table, 0);
- }
/*
Abstract method that defines which time zone is used for conversion.
Converts time current time in my_time_t representation to broken-down
@@ -626,10 +666,10 @@ class Item_func_convert_tz :public Item_
};
-class Item_func_sec_to_time :public Item_str_func
+class Item_func_sec_to_time :public Item_str_timefunc
{
public:
- Item_func_sec_to_time(Item *item) :Item_str_func(item) {}
+ Item_func_sec_to_time(Item *item) :Item_str_timefunc(item) {}
double val_real()
{
DBUG_ASSERT(fixed == 1);
@@ -639,17 +679,12 @@ public:
String *val_str(String *);
void fix_length_and_dec()
{
+ Item_str_timefunc::fix_length_and_dec();
collation.set(&my_charset_bin);
maybe_null=1;
decimals= DATETIME_DEC;
- max_length=MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
}
- enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
const char *func_name() const { return "sec_to_time"; }
- Field *tmp_table_field(TABLE *table)
- {
- return tmp_table_field_from_field_type(table, 0);
- }
bool result_as_longlong() { return TRUE; }
bool check_partition_func_processor(byte *int_arg) {return FALSE;}
};
@@ -774,6 +809,15 @@ public:
}
bool result_as_longlong() { return TRUE; }
longlong val_int();
+ my_decimal *val_decimal(my_decimal *decimal_value)
+ {
+ DBUG_ASSERT(fixed == 1);
+ return val_decimal_from_date(decimal_value);
+ }
+ int save_in_field(Field *field, bool no_conversions)
+ {
+ return save_date_in_field(field);
+ }
};
@@ -792,6 +836,15 @@ public:
}
bool result_as_longlong() { return TRUE; }
longlong val_int();
+ my_decimal *val_decimal(my_decimal *decimal_value)
+ {
+ DBUG_ASSERT(fixed == 1);
+ return val_decimal_from_time(decimal_value);
+ }
+ int save_in_field(Field *field, bool no_conversions)
+ {
+ return save_time_in_field(field);
+ }
};
@@ -809,12 +862,21 @@ public:
}
bool result_as_longlong() { return TRUE; }
longlong val_int();
+ my_decimal *val_decimal(my_decimal *decimal_value)
+ {
+ DBUG_ASSERT(fixed == 1);
+ return val_decimal_from_date(decimal_value);
+ }
+ int save_in_field(Field *field, bool no_conversions)
+ {
+ return save_date_in_field(field);
+ }
};
-class Item_func_makedate :public Item_str_func
+class Item_func_makedate :public Item_date_func
{
public:
- Item_func_makedate(Item *a,Item *b) :Item_str_func(a,b) {}
+ Item_func_makedate(Item *a,Item *b) :Item_date_func(a,b) {}
String *val_str(String *str);
const char *func_name() const { return "makedate"; }
enum_field_types field_type() const { return MYSQL_TYPE_DATE; }
@@ -823,11 +885,6 @@ public:
decimals=0;
max_length=MAX_DATE_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
}
- Field *tmp_table_field(TABLE *table)
- {
- return tmp_table_field_from_field_type(table, 0);
- }
- bool result_as_longlong() { return TRUE; }
longlong val_int();
bool check_partition_func_processor(byte *int_arg) {return FALSE;}
};
@@ -853,45 +910,46 @@ public:
void print(String *str);
const char *func_name() const { return "add_time"; }
bool check_partition_func_processor(byte *int_arg) {return FALSE;}
+ my_decimal *val_decimal(my_decimal *decimal_value)
+ {
+ DBUG_ASSERT(fixed == 1);
+ if (cached_field_type == MYSQL_TYPE_TIME)
+ return val_decimal_from_time(decimal_value);
+ if (cached_field_type == MYSQL_TYPE_DATETIME)
+ return val_decimal_from_date(decimal_value);
+ return Item_str_func::val_decimal(decimal_value);
+ }
+ int save_in_field(Field *field, bool no_conversions)
+ {
+ if (cached_field_type == MYSQL_TYPE_TIME)
+ return save_time_in_field(field);
+ if (cached_field_type == MYSQL_TYPE_DATETIME)
+ return save_date_in_field(field);
+ return Item_str_func::save_in_field(field, no_conversions);
+ }
};
-class Item_func_timediff :public Item_str_func
+class Item_func_timediff :public Item_str_timefunc
{
public:
Item_func_timediff(Item *a, Item *b)
- :Item_str_func(a, b) {}
+ :Item_str_timefunc(a, b) {}
String *val_str(String *str);
const char *func_name() const { return "timediff"; }
- enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
void fix_length_and_dec()
{
- decimals=0;
- max_length=MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
+ Item_str_timefunc::fix_length_and_dec();
maybe_null= 1;
}
- Field *tmp_table_field(TABLE *table)
- {
- return tmp_table_field_from_field_type(table, 0);
- }
};
-class Item_func_maketime :public Item_str_func
+class Item_func_maketime :public Item_str_timefunc
{
public:
Item_func_maketime(Item *a, Item *b, Item *c)
- :Item_str_func(a, b ,c) {}
+ :Item_str_timefunc(a, b ,c) {}
String *val_str(String *str);
const char *func_name() const { return "maketime"; }
- enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
- void fix_length_and_dec()
- {
- decimals=0;
- max_length=MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
- }
- Field *tmp_table_field(TABLE *table)
- {
- return tmp_table_field_from_field_type(table, 0);
- }
bool check_partition_func_processor(byte *int_arg) {return FALSE;}
};
--- 1.453/sql/mysql_priv.h 2006-11-08 19:09:51 +04:00
+++ 1.454/sql/mysql_priv.h 2006-11-08 19:09:51 +04:00
@@ -1798,7 +1798,7 @@ ha_rows filesort(THD *thd, TABLE *form,s
uint s_length, SQL_SELECT *select,
ha_rows max_rows, bool sort_positions,
ha_rows *examined_rows);
-void filesort_free_buffers(TABLE *table);
+void filesort_free_buffers(TABLE *table, bool full);
void change_double_for_sort(double nr,byte *to);
double my_double_round(double value, int dec, bool truncate);
int get_quick_record(SQL_SELECT *select);
--- 1.45/sql/records.cc 2006-11-08 19:09:51 +04:00
+++ 1.46/sql/records.cc 2006-11-08 19:09:51 +04:00
@@ -20,7 +20,7 @@
#include "mysql_priv.h"
static int rr_quick(READ_RECORD *info);
-static int rr_sequential(READ_RECORD *info);
+int rr_sequential(READ_RECORD *info);
static int rr_from_tempfile(READ_RECORD *info);
static int rr_unpack_from_tempfile(READ_RECORD *info);
static int rr_unpack_from_buffer(READ_RECORD *info);
@@ -251,6 +251,7 @@ void init_read_record(READ_RECORD *info,
} /* init_read_record */
+
void end_read_record(READ_RECORD *info)
{ /* free cache if used */
if (info->cache)
@@ -260,7 +261,7 @@ void end_read_record(READ_RECORD *info)
}
if (info->table)
{
- filesort_free_buffers(info->table);
+ filesort_free_buffers(info->table,0);
(void) info->file->extra(HA_EXTRA_NO_CACHE);
if (info->read_record != rr_quick) // otherwise quick_range does it
(void) info->file->ha_index_or_rnd_end();
@@ -356,7 +357,7 @@ static int rr_index(READ_RECORD *info)
}
-static int rr_sequential(READ_RECORD *info)
+int rr_sequential(READ_RECORD *info)
{
int tmp;
while ((tmp=info->file->rnd_next(info->record)))
--- 1.359/sql/sql_base.cc 2006-11-08 19:09:52 +04:00
+++ 1.360/sql/sql_base.cc 2006-11-08 19:09:52 +04:00
@@ -2105,6 +2105,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
table->file->ft_handler= 0;
if (table->timestamp_field)
table->timestamp_field_type= table->timestamp_field->get_auto_set_type();
+ table->pos_in_table_list= table_list;
table_list->updatable= 1; // It is not derived table nor non-updatable VIEW
table->clear_column_bitmaps();
DBUG_ASSERT(table->key_read == 0);
@@ -3548,6 +3549,7 @@ TABLE *open_temporary_table(THD *thd, co
if (thd->slave_thread)
slave_open_temp_tables++;
}
+ tmp_table->pos_in_table_list= 0;
DBUG_RETURN(tmp_table);
}
--- 1.201/sql/sql_lex.cc 2006-11-08 19:09:52 +04:00
+++ 1.202/sql/sql_lex.cc 2006-11-08 19:09:52 +04:00
@@ -165,6 +165,7 @@ void lex_start(THD *thd, const uchar *bu
lex->select_lex.ftfunc_list= &lex->select_lex.ftfunc_list_alloc;
lex->select_lex.group_list.empty();
lex->select_lex.order_list.empty();
+ lex->select_lex.udf_list.empty();
lex->ignore_space=test(thd->variables.sql_mode & MODE_IGNORE_SPACE);
lex->sql_command= SQLCOM_END;
lex->duplicates= DUP_ERROR;
@@ -1175,6 +1176,7 @@ void st_select_lex::init_select()
braces= 0;
when_list.empty();
expr_list.empty();
+ udf_list.empty();
interval_list.empty();
use_index.empty();
ftfunc_list_alloc.empty();
@@ -1188,7 +1190,7 @@ void st_select_lex::init_select()
select_limit= 0; /* denotes the default limit = HA_POS_ERROR */
offset_limit= 0; /* denotes the default offset = 0 */
with_sum_func= 0;
-
+ is_correlated= 0;
}
/*
@@ -1382,6 +1384,8 @@ void st_select_lex::mark_as_dependent(SE
SELECT_LEX_UNIT *munit= s->master_unit();
munit->uncacheable|= UNCACHEABLE_DEPENDENT;
}
+ is_correlated= TRUE;
+ this->master_unit()->item->is_correlated= TRUE;
}
bool st_select_lex_node::set_braces(bool value) { return 1; }
--- 1.246/sql/sql_lex.h 2006-11-08 19:09:52 +04:00
+++ 1.247/sql/sql_lex.h 2006-11-08 19:09:52 +04:00
@@ -496,7 +496,7 @@ public:
void set_thd(THD *thd_arg) { thd= thd_arg; }
friend void lex_start(THD *thd, const uchar *buf, uint length);
- friend int subselect_union_engine::exec();
+ friend int subselect_union_engine::exec(bool);
List<Item> *get_unit_column_types();
};
@@ -588,6 +588,8 @@ public:
query processing end even if we use temporary table
*/
bool subquery_in_having;
+ /* TRUE <=> this SELECT is correlated w.r.t. some ancestor select */
+ bool is_correlated;
/*
This variable is required to ensure proper work of subqueries and
stored procedures. Generally, one should use the states of
@@ -606,6 +608,8 @@ public:
bool no_wrap_view_item;
/* exclude this select from check of unique_table() */
bool exclude_from_table_unique_test;
+
+ List<udf_func> udf_list; /* udf function calls stack */
void init_query();
void init_select();
--- 1.458/sql/sql_select.cc 2006-11-08 19:09:52 +04:00
+++ 1.459/sql/sql_select.cc 2006-11-08 19:09:52 +04:00
@@ -158,8 +158,8 @@ static int join_read_prev_same(READ_RECO
static int join_read_prev(READ_RECORD *info);
static int join_ft_read_first(JOIN_TAB *tab);
static int join_ft_read_next(READ_RECORD *info);
-static int join_read_always_key_or_null(JOIN_TAB *tab);
-static int join_read_next_same_or_null(READ_RECORD *info);
+int join_read_always_key_or_null(JOIN_TAB *tab);
+int join_read_next_same_or_null(READ_RECORD *info);
static COND *make_cond_for_table(COND *cond,table_map table,
table_map used_table);
static Item* part_of_refkey(TABLE *form,Field *field);
@@ -505,11 +505,12 @@ err:
DBUG_RETURN(-1); /* purecov: inspected */
}
+
/*
test if it is known for optimisation IN subquery
- SYNOPSYS
- JOIN::test_in_subselect
+ SYNOPSIS
+ JOIN::test_in_subselect()
where - pointer for variable in which conditions should be
stored if subquery is known
@@ -544,6 +545,35 @@ bool JOIN::test_in_subselect(Item **wher
/*
+ Check if the passed HAVING clause is a clause added by subquery optimizer
+
+ SYNOPSIS
+ is_having_subq_predicates()
+ having Having clause
+
+ RETURN
+ TRUE The passed HAVING clause was added by the subquery optimizer
+ FALSE Otherwise
+*/
+
+bool is_having_subq_predicates(Item *having)
+{
+ if (having->type() == Item::FUNC_ITEM)
+ {
+ if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC)
+ return TRUE;
+ if (((Item_func *) having)->functype() == Item_func::TRIG_COND_FUNC)
+ {
+ having= ((Item_func*)having)->arguments()[0];
+ if (((Item_func *) having)->functype() == Item_func::ISNOTNULLTEST_FUNC)
+ return TRUE;
+ }
+ return TRUE;
+ }
+ return FALSE;
+}
+
+/*
global select optimisation.
return 0 - success
1 - error
@@ -1026,9 +1056,7 @@ JOIN::optimize()
}
} else if (join_tab[0].type == JT_REF_OR_NULL &&
join_tab[0].ref.items[0]->name == in_left_expr_name &&
- having->type() == Item::FUNC_ITEM &&
- ((Item_func *) having)->functype() ==
- Item_func::ISNOTNULLTEST_FUNC)
+ is_having_subq_predicates(having))
{
join_tab[0].type= JT_INDEX_SUBQUERY;
error= 0;
@@ -1274,14 +1302,14 @@ JOIN::reinit()
exec_tmp_table1->file->extra(HA_EXTRA_RESET_STATE);
exec_tmp_table1->file->delete_all_rows();
free_io_cache(exec_tmp_table1);
- filesort_free_buffers(exec_tmp_table1);
+ filesort_free_buffers(exec_tmp_table1,0);
}
if (exec_tmp_table2)
{
exec_tmp_table2->file->extra(HA_EXTRA_RESET_STATE);
exec_tmp_table2->file->delete_all_rows();
free_io_cache(exec_tmp_table2);
- filesort_free_buffers(exec_tmp_table2);
+ filesort_free_buffers(exec_tmp_table2,0);
}
if (items0)
set_items_ref_array(items0);
@@ -2540,6 +2568,9 @@ typedef struct key_field_t { // Used wh
when val IS NULL.
*/
bool null_rejecting;
+
+ /* TRUE<=> This ref access is an outer subquery reference access */
+ bool outer_ref;
} KEY_FIELD;
/* Values in optimize */
@@ -2841,6 +2872,7 @@ add_key_field(KEY_FIELD **key_fields,uin
cond->functype() == Item_func::MULT_EQUAL_FUNC) &&
((*value)->type() == Item::FIELD_ITEM) &&
((Item_field*)*value)->field->maybe_null());
+ (*key_fields)->outer_ref= FALSE;
(*key_fields)++;
}
@@ -2899,7 +2931,7 @@ add_key_equal_fields(KEY_FIELD **key_fie
}
static void
-add_key_fields(KEY_FIELD **key_fields,uint *and_level,
+add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
COND *cond, table_map usable_tables,
SARGABLE_PARAM **sargables)
{
@@ -2912,28 +2944,56 @@ add_key_fields(KEY_FIELD **key_fields,ui
{
Item *item;
while ((item=li++))
- add_key_fields(key_fields,and_level,item,usable_tables,sargables);
+ add_key_fields(join, 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,sargables);
+ add_key_fields(join, 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,sargables);
+ add_key_fields(join, key_fields, and_level, item, usable_tables,
+ sargables);
*key_fields=merge_key_fields(org_key_fields,start_key_fields,
*key_fields,++(*and_level));
}
}
return;
}
- /* If item is of type 'field op field/constant' add it to key_fields */
+ /*
+ Subquery optimization: check if the encountered condition is one
+ added by condition push down into subquery.
+ */
+ {
+ if (cond->type() == Item::FUNC_ITEM &&
+ ((Item_func*)cond)->functype() == Item_func::TRIG_COND_FUNC)
+ {
+ cond= ((Item_func*)cond)->arguments()[0];
+ if (!join->group_list && !join->order &&
+ join->unit->item &&
+ join->unit->item->substype() == Item_subselect::IN_SUBS &&
+ !join->unit->first_select()->next_select())
+ {
+ KEY_FIELD *save= *key_fields;
+ add_key_fields(join, key_fields, and_level, cond, usable_tables,
+ sargables);
+ // Indicate that this ref access candidate is for subquery lookup:
+ for (; save != *key_fields; save++)
+ save->outer_ref= TRUE;
+ }
+ return;
+ }
+ }
+
+ /* If item is of type 'field op field/constant' add it to key_fields */
if (cond->type() != Item::FUNC_ITEM)
return;
Item_func *cond_func= (Item_func*) cond;
@@ -3107,6 +3167,7 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array
keyuse.used_tables=key_field->val->used_tables();
keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL;
keyuse.null_rejecting= key_field->null_rejecting;
+ keyuse.outer_ref= key_field->outer_ref;
VOID(insert_dynamic(keyuse_array,(gptr) &keyuse));
}
}
@@ -3229,7 +3290,7 @@ sort_keyuse(KEYUSE *a,KEYUSE *b)
Here we can add 'ref' access candidates for t1 and t2, but not for t3.
*/
-static void add_key_fields_for_nj(TABLE_LIST *nested_join_table,
+static void add_key_fields_for_nj(JOIN *join, TABLE_LIST *nested_join_table,
KEY_FIELD **end, uint *and_level,
SARGABLE_PARAM **sargables)
{
@@ -3241,12 +3302,13 @@ static void add_key_fields_for_nj(TABLE_
while ((table= li++))
{
if (table->nested_join)
- add_key_fields_for_nj(table, end, and_level, sargables);
+ add_key_fields_for_nj(join, 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, sargables);
+ add_key_fields(join, end, and_level, nested_join_table->on_expr, tables,
+ sargables);
}
@@ -3321,7 +3383,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_AR
return TRUE;
if (cond)
{
- add_key_fields(&end,&and_level,cond,normal_tables,sargables);
+ add_key_fields(join_tab->join, &end, &and_level, cond, normal_tables,
+ sargables);
for (; field != end ; field++)
{
add_key_part(keyuse,field);
@@ -3343,8 +3406,9 @@ update_ref_and_keys(THD *thd, DYNAMIC_AR
into account as well.
*/
if (*join_tab[i].on_expr_ref)
- add_key_fields(&end,&and_level,*join_tab[i].on_expr_ref,
- join_tab[i].table->map,sargables);
+ add_key_fields(join_tab->join, &end, &and_level,
+ *join_tab[i].on_expr_ref,
+ join_tab[i].table->map, sargables);
}
/* Process ON conditions for the nested joins */
@@ -3354,7 +3418,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_AR
while ((table= li++))
{
if (table->nested_join)
- add_key_fields_for_nj(table, &end, &and_level, sargables);
+ add_key_fields_for_nj(join_tab->join, table, &end, &and_level,
+ sargables);
}
}
@@ -6250,7 +6315,7 @@ void JOIN::cleanup(bool full)
if (tables > const_tables) // Test for not-const tables
{
free_io_cache(table[const_tables]);
- filesort_free_buffers(table[const_tables]);
+ filesort_free_buffers(table[const_tables],full);
}
if (full)
@@ -11001,6 +11066,13 @@ join_init_quick_read_record(JOIN_TAB *ta
}
+int rr_sequential(READ_RECORD *info);
+int init_read_record_seq(JOIN_TAB *tab)
+{
+ tab->read_record.read_record= rr_sequential;
+ return tab->read_record.file->ha_rnd_init(1);
+}
+
static int
test_if_quick_select(JOIN_TAB *tab)
{
@@ -11130,7 +11202,7 @@ join_ft_read_next(READ_RECORD *info)
Reading of key with key reference and one part that may be NULL
*/
-static int
+int
join_read_always_key_or_null(JOIN_TAB *tab)
{
int res;
@@ -11146,7 +11218,7 @@ join_read_always_key_or_null(JOIN_TAB *t
}
-static int
+int
join_read_next_same_or_null(READ_RECORD *info)
{
int error;
--- 1.112/sql/sql_select.h 2006-11-08 19:09:52 +04:00
+++ 1.113/sql/sql_select.h 2006-11-08 19:09:52 +04:00
@@ -36,6 +36,8 @@ typedef struct keyuse_t {
satisfied if val has NULL 'value'.
*/
bool null_rejecting;
+ /* TRUE<=> This ref access is an outer subquery reference access */
+ bool outer_ref;
} KEYUSE;
class store_key;
@@ -494,10 +496,11 @@ class store_key :public Sql_alloc
Field *to_field; // Store data here
char *null_ptr;
char err;
- public:
+public:
+ bool null_key; /* TRUE <=> the value of the key has a null part */
enum store_key_result { STORE_KEY_OK, STORE_KEY_FATAL, STORE_KEY_CONV };
store_key(THD *thd, Field *field_arg, char *ptr, char *null, uint length)
- :null_ptr(null),err(0)
+ :null_ptr(null), err(0), null_key(0)
{
if (field_arg->type() == FIELD_TYPE_BLOB)
{
@@ -540,6 +543,7 @@ class store_key_field: public store_key
table->write_set);
copy_field.do_copy(©_field);
dbug_tmp_restore_column_map(table->write_set, old_map);
+ null_key= to_field->is_null();
return err != 0 ? STORE_KEY_FATAL : STORE_KEY_OK;
}
const char *name() const { return field_name; }
@@ -564,8 +568,8 @@ public:
table->write_set);
int res= item->save_in_field(to_field, 1);
dbug_tmp_restore_column_map(table->write_set, old_map);
+ null_key= to_field->is_null() || item->null_value;
return (err != 0 || res > 2 ? STORE_KEY_FATAL : (store_key_result) res);
-
}
const char *name() const { return "func"; }
};
@@ -595,6 +599,7 @@ public:
err= res;
}
}
+ null_key= to_field->is_null() || item->null_value;
return (err > 2 ? STORE_KEY_FATAL : (store_key_result) err);
}
const char *name() const { return "const"; }
--- 1.367/sql/sql_show.cc 2006-11-08 19:09:52 +04:00
+++ 1.368/sql/sql_show.cc 2006-11-08 19:09:52 +04:00
@@ -4981,7 +4981,7 @@ bool get_schema_tables_result(JOIN *join
table_list->table->file->extra(HA_EXTRA_RESET_STATE);
table_list->table->file->delete_all_rows();
free_io_cache(table_list->table);
- filesort_free_buffers(table_list->table);
+ filesort_free_buffers(table_list->table,1);
table_list->table->null_row= 0;
}
else
--- 1.368/sql/sql_table.cc 2006-11-08 19:09:52 +04:00
+++ 1.369/sql/sql_table.cc 2006-11-08 19:09:52 +04:00
@@ -4154,7 +4154,6 @@ static bool mysql_admin_table(THD* thd,
goto send_result;
}
- table->table->pos_in_table_list= table;
if ((table->table->db_stat & HA_READ_ONLY) && open_for_modify)
{
char buff[FN_REFLEN + MYSQL_ERRMSG_SIZE];
@@ -6737,8 +6736,6 @@ bool mysql_checksum_table(THD *thd, TABL
}
else
{
- t->pos_in_table_list= table;
-
if (t->file->ha_table_flags() & HA_HAS_CHECKSUM &&
!(check_opt->flags & T_EXTEND))
protocol->store((ulonglong)t->file->checksum());
--- 1.508/sql/sql_yacc.yy 2006-11-08 19:09:52 +04:00
+++ 1.509/sql/sql_yacc.yy 2006-11-08 19:09:52 +04:00
@@ -809,7 +809,7 @@ bool my_yyoverflow(short **a, YYSTYPE **
%type <item_list>
expr_list udf_expr_list udf_expr_list2 when_list
- ident_list ident_list_arg
+ ident_list ident_list_arg opt_expr_list
%type <var_type>
option_type opt_var_type opt_var_ident_type
@@ -6352,7 +6352,7 @@ simple_expr:
{ $$= new Item_func_trim($5,$3); }
| TRUNCATE_SYM '(' expr ',' expr ')'
{ $$= new Item_func_round($3,$5,1); }
- | ident '.' ident '(' udf_expr_list ')'
+ | ident '.' ident '(' opt_expr_list ')'
{
LEX *lex= Lex;
sp_name *name= new sp_name($1, $3);
@@ -6369,27 +6369,27 @@ simple_expr:
{
#ifdef HAVE_DLOPEN
udf_func *udf= 0;
+ LEX *lex= Lex;
if (using_udf_functions &&
(udf= find_udf($1.str, $1.length)) &&
udf->type == UDFTYPE_AGGREGATE)
{
- LEX *lex= Lex;
if (lex->current_select->inc_in_sum_expr())
{
yyerror(ER(ER_SYNTAX_ERROR));
YYABORT;
}
}
- $<udf>$= udf;
+ lex->current_select->udf_list.push_front(udf);
#endif
}
udf_expr_list ')'
{
#ifdef HAVE_DLOPEN
- udf_func *udf= $<udf>3;
- SELECT_LEX *sel= Select;
+ udf_func *udf;
+ LEX *lex= Lex;
- if (udf)
+ if (NULL != (udf= lex->current_select->udf_list.pop()))
{
if (udf->type == UDFTYPE_AGGREGATE)
Select->in_sum_expr--;
@@ -6629,12 +6629,29 @@ udf_expr_list3:
udf_expr:
remember_name expr remember_end select_alias
{
+ udf_func *udf= Select->udf_list.head();
+ /*
+ Use Item::name as a storage for the attribute value of user
+ defined function argument. It is safe to use Item::name
+ because the syntax will not allow having an explicit name here.
+ See WL#1017 re. udf attributes.
+ */
if ($4.str)
{
+ if (!udf)
+ {
+ /*
+ Disallow using AS to specify explicit names for the arguments
+ of stored routine calls
+ */
+ yyerror(ER(ER_SYNTAX_ERROR));
+ YYABORT;
+ }
+
$2->is_autogenerated_name= FALSE;
$2->set_name($4.str, $4.length, system_charset_info);
}
- else
+ else if (udf)
$2->set_name($1, (uint) ($3 - $1), YYTHD->charset());
$$= $2;
}
@@ -6753,6 +6770,11 @@ cast_type:
| TIME_SYM { $$=ITEM_CAST_TIME; Lex->charset= NULL; Lex->dec=Lex->length= (char*)0; }
| DATETIME { $$=ITEM_CAST_DATETIME; Lex->charset= NULL; Lex->dec=Lex->length= (char*)0; }
| DECIMAL_SYM float_options { $$=ITEM_CAST_DECIMAL; Lex->charset= NULL; }
+ ;
+
+opt_expr_list:
+ /* empty */ { $$= NULL; }
+ | expr_list { $$= $1;}
;
expr_list:
--- 1.246/sql/table.cc 2006-11-08 19:09:52 +04:00
+++ 1.247/sql/table.cc 2006-11-08 19:09:52 +04:00
@@ -4097,6 +4097,23 @@ void st_table_list::reinit_before_use(TH
embedding->nested_join->join_list.head() == embedded);
}
+/*
+ Return subselect that contains the FROM list this table is taken from
+
+ SYNOPSIS
+ st_table_list::containing_subselect()
+
+ RETURN
+ Subselect item for the subquery that contains the FROM list
+ this table is taken from if there is any
+ 0 - otherwise
+
+*/
+
+Item_subselect *st_table_list::containing_subselect()
+{
+ return (select_lex ? select_lex->master_unit()->item : 0);
+}
/*****************************************************************************
** Instansiate templates
--- 1.153/sql/table.h 2006-11-08 19:09:52 +04:00
+++ 1.154/sql/table.h 2006-11-08 19:09:52 +04:00
@@ -18,6 +18,7 @@
/* Structs that defines the TABLE */
class Item; /* Needed by ORDER */
+class Item_subselect;
class GRANT_TABLE;
class st_select_lex_unit;
class st_select_lex;
@@ -74,6 +75,9 @@ enum release_type { RELEASE_NORMAL, RELE
typedef struct st_filesort_info
{
IO_CACHE *io_cache; /* If sorted through filebyte */
+ uchar **sort_keys; /* Buffer for sorting keys */
+ byte *buffpek; /* Buffer for buffpek structures */
+ uint buffpek_len; /* Max number of buffpeks in the buffer */
byte *addon_buf; /* Pointer to a buffer if sorted with fields */
uint addon_length; /* Length of the buffer */
struct st_sort_addon_field *addon_field; /* Pointer to the fields info */
@@ -859,6 +863,7 @@ typedef struct st_table_list
procedure.
*/
void reinit_before_use(THD *thd);
+ Item_subselect *containing_subselect();
private:
bool prep_check_option(THD *thd, uint8 check_opt_type);
--- 1.155/mysql-test/r/subselect.result 2006-11-08 19:09:52 +04:00
+++ 1.156/mysql-test/r/subselect.result 2006-11-08 19:09:52 +04:00
@@ -744,7 +744,7 @@ id select_type table type possible_keys
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 AS `3` having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))
+Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1))) union select 3 AS `3` having trigcond((<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3)))))
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
id
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
@@ -907,7 +907,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
+Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and trigcond(((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
drop table t1,t2,t3;
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
@@ -2817,19 +2817,19 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and ((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) having (<is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`)))) AS `test` from `test`.`t1`
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having trigcond((<is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`))))
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`))))))
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and <is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`)))) AS `test` from `test`.`t1`
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having trigcond((((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and <is_not_null_test>(`test`.`t2`.`one`) and <is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
CREATE TABLE t1 (a char(5), b char(5));
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
@@ -3002,6 +3002,38 @@ field1 field2
1 1
1 3
DROP TABLE t1, t2;
+CREATE TABLE t1(a int, INDEX (a));
+INSERT INTO t1 VALUES (1), (3), (5), (7);
+INSERT INTO t1 VALUES (NULL);
+CREATE TABLE t2(a int);
+INSERT INTO t2 VALUES (1),(2),(3);
+EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index
+SELECT a, a IN (SELECT a FROM t1) FROM t2;
+a a IN (SELECT a FROM t1)
+1 1
+2 NULL
+3 1
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
+CREATE TABLE t2 AS SELECT
+(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a
+FROM t1 WHERE a > '2000-01-01';
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `sub_a` datetime default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');
+SHOW CREATE TABLE t3;
+Table Create Table
+t3 CREATE TABLE `t3` (
+ `a` datetime default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2,t3;
create table t1 (df decimal(5,1));
insert into t1 values(1.1);
insert into t1 values(2.2);
@@ -3544,4 +3576,20 @@ FROM t1 GROUP BY t1.a LIMIT 1)
2
2
2
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
+CREATE TABLE t2 (x int auto_increment, y int, z int,
+PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
+SET SESSION sort_buffer_size = 32 * 1024;
+SELECT SQL_NO_CACHE COUNT(*)
+FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
+FROM t1) t;
+COUNT(*)
+3000
+SET SESSION sort_buffer_size = 8 * 1024 * 1024;
+SELECT SQL_NO_CACHE COUNT(*)
+FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
+FROM t1) t;
+COUNT(*)
+3000
DROP TABLE t1,t2;
--- 1.131/sql/item_subselect.cc 2006-11-08 19:09:52 +04:00
+++ 1.132/sql/item_subselect.cc 2006-11-08 19:09:52 +04:00
@@ -37,7 +37,7 @@ inline Item * and_items(Item* cond, Item
Item_subselect::Item_subselect():
Item_result_field(), value_assigned(0), thd(0), substitution(0),
engine(0), old_engine(0), used_tables_cache(0), have_to_be_excluded(0),
- const_item_cache(1), engine_changed(0), changed(0)
+ const_item_cache(1), engine_changed(0), changed(0), is_correlated(FALSE)
{
with_subselect= 1;
reset();
@@ -235,16 +235,16 @@ bool Item_subselect::walk(Item_processor
}
-bool Item_subselect::exec()
+bool Item_subselect::exec(bool full_scan)
{
int res;
- res= engine->exec();
+ res= engine->exec(full_scan);
if (engine_changed)
{
engine_changed= 0;
- return exec();
+ return exec(full_scan);
}
return (res);
}
@@ -434,6 +434,15 @@ enum Item_result Item_singlerow_subselec
return engine->type();
}
+/*
+ Don't rely on the result type to calculate field type.
+ Ask the engine instead.
+*/
+enum_field_types Item_singlerow_subselect::field_type() const
+{
+ return engine->field_type();
+}
+
void Item_singlerow_subselect::fix_length_and_dec()
{
if ((max_columns= engine->cols()) == 1)
@@ -484,13 +493,13 @@ bool Item_singlerow_subselect::null_insi
void Item_singlerow_subselect::bring_value()
{
- exec();
+ exec(FALSE);
}
double Item_singlerow_subselect::val_real()
{
DBUG_ASSERT(fixed == 1);
- if (!exec() && !value->null_value)
+ if (!exec(FALSE) && !value->null_value)
{
null_value= 0;
return value->val_real();
@@ -505,7 +514,7 @@ double Item_singlerow_subselect::val_rea
longlong Item_singlerow_subselect::val_int()
{
DBUG_ASSERT(fixed == 1);
- if (!exec() && !value->null_value)
+ if (!exec(FALSE) && !value->null_value)
{
null_value= 0;
return value->val_int();
@@ -519,7 +528,7 @@ longlong Item_singlerow_subselect::val_i
String *Item_singlerow_subselect::val_str(String *str)
{
- if (!exec() && !value->null_value)
+ if (!exec(FALSE) && !value->null_value)
{
null_value= 0;
return value->val_str(str);
@@ -534,7 +543,7 @@ String *Item_singlerow_subselect::val_st
my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
{
- if (!exec() && !value->null_value)
+ if (!exec(FALSE) && !value->null_value)
{
null_value= 0;
return value->val_decimal(decimal_value);
@@ -549,7 +558,7 @@ my_decimal *Item_singlerow_subselect::va
bool Item_singlerow_subselect::val_bool()
{
- if (!exec() && !value->null_value)
+ if (!exec(FALSE) && !value->null_value)
{
null_value= 0;
return value->val_bool();
@@ -600,7 +609,8 @@ bool Item_in_subselect::test_limit(SELEC
Item_in_subselect::Item_in_subselect(Item * left_exp,
st_select_lex *select_lex):
- Item_exists_subselect(), optimizer(0), transformed(0), upper_item(0)
+ Item_exists_subselect(), optimizer(0), transformed(0),
+ enable_pushed_conds(TRUE), upper_item(0)
{
DBUG_ENTER("Item_in_subselect::Item_in_subselect");
left_expr= left_exp;
@@ -645,7 +655,7 @@ void Item_exists_subselect::fix_length_a
double Item_exists_subselect::val_real()
{
DBUG_ASSERT(fixed == 1);
- if (exec())
+ if (exec(FALSE))
{
reset();
return 0;
@@ -656,7 +666,7 @@ double Item_exists_subselect::val_real()
longlong Item_exists_subselect::val_int()
{
DBUG_ASSERT(fixed == 1);
- if (exec())
+ if (exec(FALSE))
{
reset();
return 0;
@@ -667,7 +677,7 @@ longlong Item_exists_subselect::val_int(
String *Item_exists_subselect::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
- if (exec())
+ if (exec(FALSE))
{
reset();
return 0;
@@ -680,7 +690,7 @@ String *Item_exists_subselect::val_str(S
my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value)
{
DBUG_ASSERT(fixed == 1);
- if (exec())
+ if (exec(FALSE))
{
reset();
return 0;
@@ -693,7 +703,7 @@ my_decimal *Item_exists_subselect::val_d
bool Item_exists_subselect::val_bool()
{
DBUG_ASSERT(fixed == 1);
- if (exec())
+ if (exec(FALSE))
{
reset();
return 0;
@@ -710,7 +720,8 @@ double Item_in_subselect::val_real()
*/
DBUG_ASSERT(0);
DBUG_ASSERT(fixed == 1);
- if (exec())
+ null_value= 0;
+ if (exec(!enable_pushed_conds))
{
reset();
null_value= 1;
@@ -730,7 +741,8 @@ longlong Item_in_subselect::val_int()
*/
DBUG_ASSERT(0);
DBUG_ASSERT(fixed == 1);
- if (exec())
+ null_value= 0;
+ if (exec(!enable_pushed_conds))
{
reset();
null_value= 1;
@@ -750,7 +762,8 @@ String *Item_in_subselect::val_str(Strin
*/
DBUG_ASSERT(0);
DBUG_ASSERT(fixed == 1);
- if (exec())
+ null_value= 0;
+ if (exec(!enable_pushed_conds))
{
reset();
null_value= 1;
@@ -769,7 +782,8 @@ String *Item_in_subselect::val_str(Strin
bool Item_in_subselect::val_bool()
{
DBUG_ASSERT(fixed == 1);
- if (exec())
+ null_value= 0;
+ if (exec(!enable_pushed_conds))
{
reset();
null_value= 1;
@@ -787,8 +801,9 @@ my_decimal *Item_in_subselect::val_decim
method should not be used
*/
DBUG_ASSERT(0);
+ null_value= 0;
DBUG_ASSERT(fixed == 1);
- if (exec())
+ if (exec(!enable_pushed_conds))
{
reset();
null_value= 1;
@@ -801,7 +816,55 @@ my_decimal *Item_in_subselect::val_decim
}
-/* Rewrite a single-column IN/ALL/ANY subselect. */
+/*
+ Rewrite a single-column IN/ALL/ANY subselect
+
+ SYNOPSIS
+ Item_in_subselect::single_value_transformer()
+ join Join object of the subquery (i.e. 'child' join).
+ func Subquery comparison creator
+
+ DESCRIPTION
+ Rewrite a single-column subquery using rule-based approach. The subquery
+
+ oe $cmp$ (SELECT ie FROM ... WHERE subq_where ... HAVING subq_having)
+
+ First, try to convert the subquery to scalar-result subquery in one of
+ the forms:
+
+ - oe $cmp$ (SELECT MAX(...) ) // handled by Item_singlerow_subselect
+ - oe $cmp$ <max>(SELECT ...) // handled by Item_maxmin_subselect
+
+ If that fails, the subquery will be handled with class Item_in_optimizer,
+ Inject the predicates into subquery, i.e. convert it to:
+
+ - If the subquery has aggregates, GROUP BY, or HAVING, convert to
+
+ SELECT ie FROM ... HAVING subq_having AND
+ trigcond(oe $cmp$ ref_or_null_helper<ie>)
+
+ the addition is wrapped into trigger only when we want to distinguish
+ between NULL and FALSE results.
+
+ - Otherwise (no aggregates/GROUP BY/HAVING) convert it to one of the
+ following:
+
+ = If we don't need to distinguish between NULL and FALSE subquery:
+
+ SELECT 1 FROM ... WHERE (oe $cmp$ ie) AND subq_where
+
+ = If we need to distinguish between those:
+
+ SELECT 1 FROM ...
+ WHERE subq_where AND trigcond((oe $cmp$ ie) OR (ie IS NULL))
+ HAVING trigcond(<is_not_null_test>(ie))
+
+ RETURN
+ RES_OK - OK, either subquery was transformed, or appopriate
+ predicates where injected into it.
+ RES_REDUCE - The subquery was reduced to non-subquery
+ RES_ERROR - Error
+*/
Item_subselect::trans_res
Item_in_subselect::single_value_transformer(JOIN *join,
@@ -934,8 +997,12 @@ Item_in_subselect::single_value_transfor
select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
/*
Add the left part of a subselect to a WHERE or HAVING clause of
- the right part, e.g. SELECT 1 IN (SELECT a FROM t1) =>
- SELECT Item_in_optimizer(1, SELECT a FROM t1 WHERE a=1)
+ the right part, e.g.
+
+ SELECT 1 IN (SELECT a FROM t1) =>
+
+ SELECT Item_in_optimizer(1, SELECT a FROM t1 WHERE a=1)
+
HAVING is used only if the right part contains a SUM function, a GROUP
BY or a HAVING clause.
*/
@@ -950,10 +1017,15 @@ Item_in_subselect::single_value_transfor
ref_pointer_array,
(char *)"<ref>",
this->full_name()));
-#ifdef CORRECT_BUT_TOO_SLOW_TO_BE_USABLE
- if (!abort_on_null && left_expr->maybe_null)
- item= new Item_cond_or(new Item_func_isnull(left_expr), item);
-#endif
+ if (!abort_on_null && ((Item*)select_lex->item_list.head())->maybe_null)
+ {
+ /*
+ We can encounter "NULL IN (SELECT ...)". Wrap the added condition
+ within a trigger.
+ */
+ item= new Item_func_trig_cond(item, &enable_pushed_conds);
+ }
+
/*
AND and comparison functions can't be changed during fix_fields()
we can assign select_lex->having here, and pass 0 as last
@@ -982,19 +1054,19 @@ Item_in_subselect::single_value_transfor
select_lex->item_list.push_back(new Item_int("Not_used",
(longlong) 1, 21));
select_lex->ref_pointer_array[0]= select_lex->item_list.head();
+
item= func->create(expr, item);
if (!abort_on_null && orig_item->maybe_null)
{
- having= new Item_is_not_null_test(this, having);
+ having=
+ new Item_func_trig_cond(new Item_is_not_null_test(this, having),
+ &enable_pushed_conds);
/*
Item_is_not_null_test can't be changed during fix_fields()
we can assign select_lex->having here, and pass 0 as last
argument (reference) to fix_fields()
*/
- select_lex->having=
- join->having= (join->having ?
- new Item_cond_and(having, join->having) :
- having);
+ select_lex->having= join->having= having;
select_lex->having_fix_field= 1;
/*
we do not check join->having->fixed, because Item_and (from
@@ -1005,12 +1077,15 @@ Item_in_subselect::single_value_transfor
select_lex->having_fix_field= 0;
if (tmp)
DBUG_RETURN(RES_ERROR);
+ /*
+ NOTE: It is important that we add this "IS NULL" here, even when
+ orig_item can't be NULL. This is needed so that this predicate is
+ only used by ref[_or_null] analyzer (and, e.g. is not used by const
+ propagation).
+ */
item= new Item_cond_or(item,
new Item_func_isnull(orig_item));
-#ifdef CORRECT_BUT_TOO_SLOW_TO_BE_USABLE
- if (left_expr->maybe_null)
- item= new Item_cond_or(new Item_func_isnull(left_expr), item);
-#endif
+ item= new Item_func_trig_cond(item, &enable_pushed_conds);
}
item->name= (char *)in_additional_cond;
/*
@@ -1037,13 +1112,14 @@ Item_in_subselect::single_value_transfor
we can assign select_lex->having here, and pass 0 as last
argument (reference) to fix_fields()
*/
- select_lex->having=
- join->having=
- func->create(expr,
+ Item *new_having=
+ func->create(expr,
new Item_ref_null_helper(&select_lex->context, this,
select_lex->ref_pointer_array,
(char *)"<no matter>",
(char *)"<result>"));
+ new_having= new Item_func_trig_cond(new_having, &enable_pushed_conds);
+ select_lex->having= join->having= new_having;
select_lex->having_fix_field= 1;
/*
@@ -1248,6 +1324,8 @@ Item_in_subselect::row_value_transformer
where_item= and_items(where_item, item);
}
+ if (where_item)
+ where_item= new Item_func_trig_cond(where_item, &enable_pushed_conds);
/*
AND can't be changed during fix_fields()
we can assign select_lex->where here, and pass 0 as last
@@ -1261,6 +1339,8 @@ Item_in_subselect::row_value_transformer
if (having_item)
{
bool res;
+ having_item= new Item_func_trig_cond(having_item, &enable_pushed_conds);
+
select_lex->having= join->having= and_items(join->having, having_item);
select_lex->having->top_level_item();
/*
@@ -1477,6 +1557,27 @@ bool subselect_union_engine::is_executed
}
+/*
+ Check if last execution of the subquery engine produced any rows
+
+ SYNOPSIS
+ subselect_union_engine::no_rows()
+
+ DESCRIPTION
+ Check if last execution of the subquery engine produced any rows. The
+ return value is undefined if last execution ended in an error.
+
+ RETURN
+ TRUE - Last subselect execution has produced no rows
+ FALSE - Otherwise
+*/
+
+bool subselect_union_engine::no_rows()
+{
+ /* Check if we got any rows when reading UNION result from temp. table: */
+ return test(!unit->fake_select_lex->join->send_records);
+}
+
void subselect_uniquesubquery_engine::cleanup()
{
DBUG_ENTER("subselect_uniquesubquery_engine::cleanup");
@@ -1542,32 +1643,58 @@ int subselect_uniquesubquery_engine::pre
return 1;
}
-static Item_result set_row(List<Item> &item_list, Item *item,
- Item_cache **row, bool *maybe_null)
+
+/*
+ Check if last execution of the subquery engine produced any rows
+
+ SYNOPSIS
+ subselect_single_select_engine::no_rows()
+
+ DESCRIPTION
+ Check if last execution of the subquery engine produced any rows. The
+ return value is undefined if last execution ended in an error.
+
+ RETURN
+ TRUE - Last subselect execution has produced no rows
+ FALSE - Otherwise
+*/
+
+bool subselect_single_select_engine::no_rows()
+{
+ return !item->assigned();
+}
+
+
+/*
+ makes storage for the output values for the subquery and calcuates
+ their data and column types and their nullability.
+*/
+void subselect_engine::set_row(List<Item> &item_list, Item_cache **row)
{
- Item_result res_type= STRING_RESULT;
Item *sel_item;
List_iterator_fast<Item> li(item_list);
+ res_type= STRING_RESULT;
+ res_field_type= FIELD_TYPE_VAR_STRING;
for (uint i= 0; (sel_item= li++); i++)
{
item->max_length= sel_item->max_length;
res_type= sel_item->result_type();
+ res_field_type= sel_item->field_type();
item->decimals= sel_item->decimals;
item->unsigned_flag= sel_item->unsigned_flag;
- *maybe_null= sel_item->maybe_null;
+ maybe_null= sel_item->maybe_null;
if (!(row[i]= Item_cache::get_cache(res_type)))
- return STRING_RESULT; // we should return something
+ return;
row[i]->setup(sel_item);
}
if (item_list.elements > 1)
res_type= ROW_RESULT;
- return res_type;
}
void subselect_single_select_engine::fix_length_and_dec(Item_cache **row)
{
DBUG_ASSERT(row || select_lex->item_list.elements==1);
- res_type= set_row(select_lex->item_list, item, row, &maybe_null);
+ set_row(select_lex->item_list, row);
item->collation.set(row[0]->collation);
if (cols() != 1)
maybe_null= 0;
@@ -1579,13 +1706,14 @@ void subselect_union_engine::fix_length_
if (unit->first_select()->item_list.elements == 1)
{
- res_type= set_row(unit->types, item, row, &maybe_null);
+ set_row(unit->types, row);
item->collation.set(row[0]->collation);
}
else
{
- bool fake= 0;
- res_type= set_row(unit->types, item, row, &fake);
+ bool maybe_null_saved= maybe_null;
+ set_row(unit->types, row);
+ maybe_null= maybe_null_saved;
}
}
@@ -1595,7 +1723,11 @@ void subselect_uniquesubquery_engine::fi
DBUG_ASSERT(0);
}
-int subselect_single_select_engine::exec()
+int init_read_record_seq(JOIN_TAB *tab);
+int join_read_always_key_or_null(JOIN_TAB *tab);
+int join_read_next_same_or_null(READ_RECORD *info);
+
+int subselect_single_select_engine::exec(bool full_scan)
{
DBUG_ENTER("subselect_single_select_engine::exec");
char const *save_where= thd->where;
@@ -1633,7 +1765,43 @@ int subselect_single_select_engine::exec
if (!executed)
{
item->reset_value_registration();
+ if (full_scan)
+ {
+ /*
+ We should not apply optimizations based on the condition that was
+ pushed down into the subquery. Those optimizations are ref[_or_null]
+ acceses. Change them to be full table scans.
+ */
+ for (uint i=join->const_tables ; i < join->tables ; i++)
+ {
+ JOIN_TAB *tab=join->join_tab+i;
+ if (tab->keyuse && tab->keyuse->outer_ref)
+ {
+ tab->read_first_record= init_read_record_seq;
+ tab->read_record.record= tab->table->record[0];
+ tab->read_record.thd= join->thd;
+ tab->read_record.ref_length= tab->table->file->ref_length;
+ }
+ }
+ }
+
join->exec();
+
+ if (full_scan)
+ {
+ /* Enable the optimizations back */
+ for (uint i=join->const_tables ; i < join->tables ; i++)
+ {
+ JOIN_TAB *tab=join->join_tab+i;
+ if (tab->keyuse && tab->keyuse->outer_ref)
+ {
+ tab->read_record.record= 0;
+ tab->read_record.ref_length= 0;
+ tab->read_first_record= join_read_always_key_or_null;
+ tab->read_record.read_record= join_read_next_same_or_null;
+ }
+ }
+ }
executed= 1;
thd->where= save_where;
thd->lex->current_select= save_select;
@@ -1644,29 +1812,159 @@ int subselect_single_select_engine::exec
DBUG_RETURN(0);
}
-int subselect_union_engine::exec()
+int subselect_union_engine::exec(bool full_scan)
{
char const *save_where= thd->where;
+ /*
+ Ignore the full_scan parameter: the pushed down predicates are only used
+ for filtering, and the caller has disabled them if necessary.
+ */
int res= unit->exec();
thd->where= save_where;
return res;
}
-int subselect_uniquesubquery_engine::exec()
+/*
+ Search for at least on row satisfying select condition
+
+ SYNOPSIS
+ subselect_uniquesubquery_engine::scan_table()
+
+ DESCRIPTION
+ Scan the table using sequential access until we find at least one row
+ satisfying select condition.
+
+ The result of this function (info about whether a row was found) is
+ stored in this->empty_result_set.
+
+ RETURN
+ FALSE - OK
+ TRUE - Error
+*/
+
+int subselect_uniquesubquery_engine::scan_table()
{
- DBUG_ENTER("subselect_uniquesubquery_engine::exec");
int error;
TABLE *table= tab->table;
- for (store_key **copy=tab->ref.key_copy ; *copy ; copy++)
+ DBUG_ENTER("subselect_uniquesubquery_engine::scan_table");
+ empty_result_set= TRUE;
+
+ if (table->file->inited)
+ table->file->ha_index_end();
+
+ table->file->ha_rnd_init(1);
+ table->file->extra_opt(HA_EXTRA_CACHE,
+ current_thd->variables.read_buff_size);
+ table->null_row= 0;
+ for (;;)
{
- if ((tab->ref.key_err= (*copy)->copy()) & 1)
+ error=table->file->rnd_next(table->record[0]);
+ if (error && error != HA_ERR_END_OF_FILE)
+ {
+ error= report_error(table, error);
+ break;
+ }
+ /* No more rows */
+ if (table->status)
+ break;
+
+ if (!cond || cond->val_int())
{
- table->status= STATUS_NOT_FOUND;
+ empty_result_set= FALSE;
+ break;
+ }
+ }
+
+ table->file->ha_rnd_end();
+ DBUG_RETURN(error != 0);
+}
+
+
+/*
+ Copy ref key and check for null parts in it
+
+ SYNOPSIS
+ subselect_uniquesubquery_engine::copy_ref_key()
+
+ DESCRIPTION
+ Copy ref key and check for null parts in it.
+
+ RETURN
+ FALSE - ok, index lookup key without keys copied.
+ TRUE - an error occured while copying the key
+*/
+
+bool subselect_uniquesubquery_engine::copy_ref_key()
+{
+ DBUG_ENTER("subselect_uniquesubquery_engine::copy_ref_key");
+
+ for (store_key **copy= tab->ref.key_copy ; *copy ; copy++)
+ {
+ tab->ref.key_err= (*copy)->copy();
+
+ /*
+ When there is a NULL part in the key we don't need to make index
+ lookup for such key thus we don't need to copy whole key.
+ If we later should do a sequential scan return OK. Fail otherwise.
+
+ See also the comment for the subselect_uniquesubquery_engine::exec()
+ function.
+ */
+ null_keypart= (*copy)->null_key;
+ bool top_level= ((Item_in_subselect *) item)->is_top_level_item();
+ if (null_keypart && !top_level)
+ break;
+ if ((tab->ref.key_err) & 1 || (null_keypart && top_level))
+ {
+ tab->table->status= STATUS_NOT_FOUND;
DBUG_RETURN(1);
}
}
+ DBUG_RETURN(0);
+}
+
+/*
+ Execute subselect
+
+ SYNOPSIS
+ subselect_uniquesubquery_engine::exec()
+
+ DESCRIPTION
+ Find rows corresponding to the ref key using index access.
+ If some part of the lookup key is NULL, then we're evaluating
+ NULL IN (SELECT ... )
+ This is a special case, we don't need to search for NULL in the table,
+ instead, the result value is
+ - NULL if select produces empty row set
+ - FALSE otherwise.
+
+ In some cases (IN subselect is a top level item, i.e. abort_on_null==TRUE)
+ the caller doesn't distinguish between NULL and FALSE result and we just
+ return FALSE.
+ Otherwise we make a full table scan to see if there is at least one matching row.
+
+ NOTE
+
+ RETURN
+ FALSE - ok
+ TRUE - an error occured while scanning
+*/
+
+int subselect_uniquesubquery_engine::exec(bool full_scan)
+{
+ DBUG_ENTER("subselect_uniquesubquery_engine::exec");
+ int error;
+ TABLE *table= tab->table;
+
+ /* TODO: change to use of 'full_scan' here? */
+ if (copy_ref_key())
+ DBUG_RETURN(1);
+
+ if (null_keypart)
+ DBUG_RETURN(scan_table());
+
if (!table->file->inited)
table->file->ha_index_init(tab->ref.key, 0);
error= table->file->index_read(table->record[0],
@@ -1695,14 +1993,68 @@ subselect_uniquesubquery_engine::~subsel
}
-int subselect_indexsubquery_engine::exec()
+/*
+ Index-lookup subselect 'engine' - run the subquery
+
+ SYNOPSIS
+ subselect_uniquesubquery_engine:exec()
+ full_scan
+
+ DESCRIPTION
+ The engine is used to resolve subqueries in form
+
+ oe IN (SELECT key FROM tbl WHERE subq_where)
+
+ The value of the predicate is calculated as follows:
+ 1. If oe IS NULL, this is a special case, do a full table scan on
+ table tbl and search for row that satisfies subq_where. If such
+ row is found, return NULL, otherwise return FALSE.
+ 2. Make an index lookup via key=oe, search for a row that satisfies
+ subq_where. If found, return TRUE.
+ 3. If check_null==TRUE, make another lookup via key=NULL, search for a
+ row that satisfies subq_where. If found, return NULL, otherwise
+ return FALSE.
+
+ TODO
+ The step #1 can be optimized further when the index has several key
+ parts. Consider a subquery:
+
+ (oe1, oe2) IN (SELECT keypart1, keypart2 FROM tbl WHERE subq_where)
+
+ and suppose we need to evaluate it for {oe1, oe2}=={const1, NULL}.
+ Current code will do a full table scan and obtain correct result. There
+ is a better option: instead of evaluating
+
+ SELECT keypart1, keypart2 FROM tbl WHERE subq_where (1)
+
+ and checking if it has produced any matching rows, evaluate
+
+ SELECT keypart2 FROM tbl WHERE subq_where AND keypart1=const1 (2)
+
+ If this query produces a row, the result is NULL (as we're evaluating
+ "(const1, NULL) IN { (const1, X), ... }", which has a value of UNKNOWN,
+ i.e. NULL). If the query produces no rows, the result is FALSE.
+
+ We currently evaluate (1) by doing a full table scan. (2) can be
+ evaluated by doing a "ref" scan on "keypart1=const1", which can be much
+ cheaper. We can use index statistics to quickly check whether "ref" scan
+ will be cheaper than full table scan.
+
+ RETURN
+ 0
+ 1
+*/
+
+int subselect_indexsubquery_engine::exec(bool full_scan)
{
- DBUG_ENTER("subselect_indexsubselect_engine::exec");
+ DBUG_ENTER("subselect_indexsubquery_engine::exec");
int error;
bool null_finding= 0;
TABLE *table= tab->table;
((Item_in_subselect *) item)->value= 0;
+ empty_result_set= TRUE;
+ null_keypart= 0;
if (check_null)
{
@@ -1711,14 +2063,12 @@ int subselect_indexsubquery_engine::exec
((Item_in_subselect *) item)->was_null= 0;
}
- for (store_key **copy=tab->ref.key_copy ; *copy ; copy++)
- {
- if ((tab->ref.key_err= (*copy)->copy()) & 1)
- {
- table->status= STATUS_NOT_FOUND;
- DBUG_RETURN(1);
- }
- }
+ /* Copy the ref key and check for nulls... */
+ if (copy_ref_key())
+ DBUG_RETURN(1);
+
+ if (null_keypart)
+ DBUG_RETURN(scan_table());
if (!table->file->inited)
table->file->ha_index_init(tab->ref.key, 1);
--- 1.79/sql/item_subselect.h 2006-11-08 19:09:52 +04:00
+++ 1.80/sql/item_subselect.h 2006-11-08 19:09:52 +04:00
@@ -60,6 +60,9 @@ public:
/* subquery is transformed */
bool changed;
+ /* TRUE <=> The underlying SELECT is correlated w.r.t some ancestor select */
+ bool is_correlated;
+
enum trans_res {RES_OK, RES_REDUCE, RES_ERROR};
enum subs_type {UNKNOWN_SUBS, SINGLEROW_SUBS,
EXISTS_SUBS, IN_SUBS, ALL_SUBS, ANY_SUBS};
@@ -92,7 +95,7 @@ public:
return null_value;
}
bool fix_fields(THD *thd, Item **ref);
- virtual bool exec();
+ virtual bool exec(bool full_scan);
virtual void fix_length_and_dec();
table_map used_tables() const;
table_map not_null_tables() const { return 0; }
@@ -114,6 +117,7 @@ public:
single select and union subqueries only.
*/
bool is_evaluated() const;
+ bool is_uncacheable() const;
/*
Used by max/min subquery to initialize value presence registration
@@ -156,6 +160,7 @@ public:
my_decimal *val_decimal(my_decimal *);
bool val_bool();
enum Item_result result_type() const;
+ enum_field_types field_type() const;
void fix_length_and_dec();
uint cols();
@@ -216,7 +221,20 @@ public:
friend class subselect_indexsubquery_engine;
};
-/* IN subselect */
+
+/*
+ IN subselect: this represents "left_exr IN (SELECT ...)"
+
+ This class has:
+ - (as a descendant of Item_subselect) a "subquery execution engine" which
+ allows it to evaluate subqueries. (and this class participates in
+ execution by having was_null variable where part of execution result
+ is stored.
+ - Transformation methods (todo: more on this).
+
+ This class is not used directly, it is "wrapped" into Item_in_optimizer
+ which provides some small bits of subquery evaluation.
+*/
class Item_in_subselect :public Item_exists_subselect
{
@@ -232,12 +250,14 @@ protected:
bool abort_on_null;
bool transformed;
public:
+ /* Used to trigger on/off conditions that were pushed down to subselect */
+ bool enable_pushed_conds;
Item_func_not_all *upper_item; // point on NOT/NOP before ALL/SOME subquery
Item_in_subselect(Item * left_expr, st_select_lex *select_lex);
Item_in_subselect()
:Item_exists_subselect(), optimizer(0), abort_on_null(0), transformed(0),
- upper_item(0)
+ enable_pushed_conds(TRUE), upper_item(0)
{}
subs_type substype() { return IN_SUBS; }
@@ -257,6 +277,7 @@ public:
my_decimal *val_decimal(my_decimal *);
bool val_bool();
void top_level_item() { abort_on_null=1; }
+ inline bool is_top_level_item() { return abort_on_null; }
bool test_limit(st_select_lex_unit *unit);
void print(String *str);
bool fix_fields(THD *thd, Item **ref);
@@ -292,6 +313,7 @@ protected:
THD *thd; /* pointer to current THD */
Item_subselect *item; /* item, that use this engine */
enum Item_result res_type; /* type of results */
+ enum_field_types res_field_type; /* column type of the results */
bool maybe_null; /* may be null (first item in select) */
public:
@@ -301,6 +323,7 @@ public:
result= res;
item= si;
res_type= STRING_RESULT;
+ res_field_type= FIELD_TYPE_VAR_STRING;
maybe_null= 0;
}
virtual ~subselect_engine() {}; // to satisfy compiler
@@ -314,10 +337,32 @@ public:
THD * get_thd() { return thd; }
virtual int prepare()= 0;
virtual void fix_length_and_dec(Item_cache** row)= 0;
- virtual int exec()= 0;
+ /*
+ Execute the engine
+
+ SYNOPSIS
+ exec()
+ full_scan TRUE - Pushed-down predicates are disabled, the engine
+ must disable made based on those predicates.
+ FALSE - Pushed-down predicates are in effect.
+ DESCRIPTION
+ Execute the engine. The result of execution is subquery value that is
+ either captured by previously set up select_result-based 'sink' or
+ stored somewhere by the exec() method itself.
+
+ A required side effect: if full_scan==TRUE, subselect_engine->no_rows()
+ should return correct result.
+
+ RETURN
+ 0 - OK
+ 1 - Either an execution error, or the engine was be "changed", and
+ caller should call exec() again for the new engine.
+ */
+ virtual int exec(bool full_scan)= 0;
virtual uint cols()= 0; /* return number of columns in select */
virtual uint8 uncacheable()= 0; /* query is uncacheable */
enum Item_result type() { return res_type; }
+ enum_field_types field_type() { return res_field_type; }
virtual void exclude()= 0;
bool may_be_null() { return maybe_null; };
virtual table_map upper_select_const_tables()= 0;
@@ -326,6 +371,11 @@ public:
virtual bool change_result(Item_subselect *si, select_subselect *result)= 0;
virtual bool no_tables()= 0;
virtual bool is_executed() const { return FALSE; }
+ /* Check if subquery produced any rows during last query execution */
+ virtual bool no_rows() = 0;
+
+protected:
+ void set_row(List<Item> &item_list, Item_cache **row);
};
@@ -343,7 +393,7 @@ public:
void cleanup();
int prepare();
void fix_length_and_dec(Item_cache** row);
- int exec();
+ int exec(bool full_scan);
uint cols();
uint8 uncacheable();
void exclude();
@@ -352,6 +402,7 @@ public:
bool change_result(Item_subselect *si, select_subselect *result);
bool no_tables();
bool is_executed() const { return executed; }
+ bool no_rows();
};
@@ -365,7 +416,7 @@ public:
void cleanup();
int prepare();
void fix_length_and_dec(Item_cache** row);
- int exec();
+ int exec(bool full_scan);
uint cols();
uint8 uncacheable();
void exclude();
@@ -374,6 +425,7 @@ public:
bool change_result(Item_subselect *si, select_subselect *result);
bool no_tables();
bool is_executed() const;
+ bool no_rows();
};
@@ -383,6 +435,12 @@ class subselect_uniquesubquery_engine: p
protected:
st_join_table *tab;
Item *cond;
+ /*
+ TRUE<=> last execution produced empty set. Valid only when left
+ expression is NULL.
+ */
+ bool empty_result_set;
+ bool null_keypart; /* TRUE <=> constructed search tuple has a NULL */
public:
// constructor can assign THD because it will be called after JOIN::prepare
@@ -396,7 +454,7 @@ public:
void cleanup();
int prepare();
void fix_length_and_dec(Item_cache** row);
- int exec();
+ int exec(bool full_scan);
uint cols() { return 1; }
uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; }
void exclude();
@@ -404,11 +462,15 @@ public:
void print (String *str);
bool change_result(Item_subselect *si, select_subselect *result);
bool no_tables();
+ int scan_table();
+ bool copy_ref_key();
+ bool no_rows() { return empty_result_set; }
};
class subselect_indexsubquery_engine: public subselect_uniquesubquery_engine
{
+ /* FALSE for 'ref', TRUE for 'ref-or-null'. */
bool check_null;
public:
@@ -419,7 +481,7 @@ public:
:subselect_uniquesubquery_engine(thd, tab_arg, subs, where),
check_null(chk_null)
{}
- int exec();
+ int exec(bool full_scan);
void print (String *str);
};
@@ -428,4 +490,10 @@ inline bool Item_subselect::is_evaluated
{
return engine->is_executed();
}
+
+inline bool Item_subselect::is_uncacheable() const
+{
+ return engine->uncacheable();
+}
+
--- 1.49/mysql-test/r/type_newdecimal.result 2006-11-08 19:09:52 +04:00
+++ 1.50/mysql-test/r/type_newdecimal.result 2006-11-08 19:09:52 +04:00
@@ -1422,6 +1422,14 @@ i2 count(distinct j)
1.0 2
2.0 2
drop table t1;
+create table t1(f1 decimal(20,6));
+insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond);
+insert into t1 values (CAST('10:11:12' AS time));
+select * from t1;
+f1
+20101112000000.000014
+101112.000000
+drop table t1;
select cast(143.481 as decimal(4,1));
cast(143.481 as decimal(4,1))
143.5
--- 1.42/mysql-test/t/type_newdecimal.test 2006-11-08 19:09:52 +04:00
+++ 1.43/mysql-test/t/type_newdecimal.test 2006-11-08 19:09:52 +04:00
@@ -1120,6 +1120,12 @@ select i, count(distinct j) from t1 grou
select i+0.0 as i2, count(distinct j) from t1 group by i2;
drop table t1;
+create table t1(f1 decimal(20,6));
+insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond);
+insert into t1 values (CAST('10:11:12' AS time));
+select * from t1;
+drop table t1;
+
#
# Bug#16172 DECIMAL data type processed incorrectly
#
--- 1.12/sql/my_decimal.cc 2006-11-08 19:09:52 +04:00
+++ 1.13/sql/my_decimal.cc 2006-11-08 19:09:52 +04:00
@@ -15,6 +15,8 @@
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
#include "mysql_priv.h"
+#include <time.h>
+
#ifndef MYSQL_CLIENT
/*
@@ -187,6 +189,23 @@ int str2my_decimal(uint mask, const char
}
check_result_and_overflow(mask, err, decimal_value);
return err;
+}
+
+
+my_decimal *date2my_decimal(TIME *ltime, my_decimal *dec)
+{
+ longlong date;
+ date = (ltime->year*100L + ltime->month)*100L + ltime->day;
+ if (ltime->time_type > MYSQL_TIMESTAMP_DATE)
+ date= ((date*100L + ltime->hour)*100L+ ltime->minute)*100L + ltime->second;
+ if (int2my_decimal(E_DEC_FATAL_ERROR, date, FALSE, dec))
+ return dec;
+ if (ltime->second_part)
+ {
+ dec->buf[(dec->intg-1) / 9 + 1]= ltime->second_part * 1000;
+ dec->frac= 6;
+ }
+ return dec;
}
--- 1.11/sql/my_decimal.h 2006-11-08 19:09:52 +04:00
+++ 1.12/sql/my_decimal.h 2006-11-08 19:09:52 +04:00
@@ -295,7 +295,12 @@ int string2my_decimal(uint mask, const S
{
return str2my_decimal(mask, str->ptr(), str->length(), str->charset(), d);
}
-#endif
+
+
+my_decimal *date2my_decimal(TIME *ltime, my_decimal *dec);
+
+
+#endif /*defined(MYSQL_SERVER) || defined(EMBEDDED_LIBRARY) */
inline
int double2my_decimal(uint mask, double val, my_decimal *d)
--- 1.15/include/my_time.h 2006-11-08 19:09:52 +04:00
+++ 1.16/include/my_time.h 2006-11-08 19:09:52 +04:00
@@ -57,6 +57,8 @@ typedef long my_time_t;
#define TIME_NO_ZERO_DATE (TIME_NO_ZERO_IN_DATE*2)
#define TIME_INVALID_DATES (TIME_NO_ZERO_DATE*2)
+my_bool check_date(const MYSQL_TIME *ltime, my_bool not_zero_date,
+ ulong flags, int *was_cut);
enum enum_mysql_timestamp_type
str_to_datetime(const char *str, uint length, MYSQL_TIME *l_time,
uint flags, int *was_cut);
--- 1.22/sql-common/my_time.c 2006-11-08 19:09:52 +04:00
+++ 1.23/sql-common/my_time.c 2006-11-08 19:09:52 +04:00
@@ -76,8 +76,8 @@ uint calc_days_in_year(uint year)
1 error
*/
-static my_bool check_date(const MYSQL_TIME *ltime, my_bool not_zero_date,
- ulong flags, int *was_cut)
+my_bool check_date(const MYSQL_TIME *ltime, my_bool not_zero_date,
+ ulong flags, int *was_cut)
{
if (not_zero_date)
{
--- 1.54/mysql-test/r/func_group.result 2006-11-08 19:09:52 +04:00
+++ 1.55/mysql-test/r/func_group.result 2006-11-08 19:09:52 +04:00
@@ -1020,3 +1020,29 @@ t1 CREATE TABLE `t1` (
`stddev(0)` double(8,4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
+INSERT INTO t1 SELECT a, b+8 FROM t1;
+INSERT INTO t1 SELECT a, b+16 FROM t1;
+INSERT INTO t1 SELECT a, b+32 FROM t1;
+INSERT INTO t1 SELECT a, b+64 FROM t1;
+INSERT INTO t1 SELECT a, b+128 FROM t1;
+INSERT INTO t1 SELECT a, b+256 FROM t1;
+INSERT INTO t1 SELECT a, b+512 FROM t1;
+INSERT INTO t1 SELECT a, b+1024 FROM t1;
+INSERT INTO t1 SELECT a, b+2048 FROM t1;
+INSERT INTO t1 SELECT a, b+4096 FROM t1;
+INSERT INTO t1 SELECT a, b+8192 FROM t1;
+INSERT INTO t1 SELECT a, b+16384 FROM t1;
+INSERT INTO t1 SELECT a, b+32768 FROM t1;
+SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50;
+a cnt
+1 65536
+SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50;
+a sumation
+1 2147516416
+SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
+a average
+1 32768.5000
+DROP TABLE t1;
+End of 5.0 tests
--- 1.56/mysql-test/r/order_by.result 2006-11-08 19:09:52 +04:00
+++ 1.57/mysql-test/r/order_by.result 2006-11-08 19:09:52 +04:00
@@ -854,6 +854,33 @@ b a
20 1
10 2
DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT a + 1 AS num FROM t1 ORDER BY 30 - num;
+num
+3
+2
+SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str);
+str
+test1
+test2
+SELECT a + 1 AS num FROM t1 GROUP BY 30 - num;
+num
+3
+2
+SELECT a + 1 AS num FROM t1 HAVING 30 - num;
+num
+2
+3
+SELECT a + 1 AS num, num + 1 FROM t1;
+ERROR 42S22: Unknown column 'num' in 'field list'
+SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1;
+num (select num + 2 FROM t1 LIMIT 1)
+2 4
+3 5
+SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a;
+ERROR 42S22: Unknown column 'num' in 'on clause'
+DROP TABLE t1;
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
explain SELECT t1.b as a, t2.b as c FROM
--- 1.35/mysql-test/r/type_datetime.result 2006-11-08 19:09:52 +04:00
+++ 1.36/mysql-test/r/type_datetime.result 2006-11-08 19:09:52 +04:00
@@ -179,3 +179,15 @@ a
2006-06-06 15:55:55
DROP PREPARE s;
DROP TABLE t1;
+SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6));
+CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6))
+20060810.000000
+SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6));
+CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6))
+20060810101112.000000
+SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6));
+CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6))
+20060810101112.000014
+SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
+CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6))
+101112.098700
--- 1.50/mysql-test/t/func_group.test 2006-11-08 19:09:52 +04:00
+++ 1.51/mysql-test/t/func_group.test 2006-11-08 19:09:52 +04:00
@@ -707,3 +707,28 @@ create table t1 select stddev(0);
show create table t1;
drop table t1;
+#
+# Bug #23184: SELECT causes server crash
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
+INSERT INTO t1 SELECT a, b+8 FROM t1;
+INSERT INTO t1 SELECT a, b+16 FROM t1;
+INSERT INTO t1 SELECT a, b+32 FROM t1;
+INSERT INTO t1 SELECT a, b+64 FROM t1;
+INSERT INTO t1 SELECT a, b+128 FROM t1;
+INSERT INTO t1 SELECT a, b+256 FROM t1;
+INSERT INTO t1 SELECT a, b+512 FROM t1;
+INSERT INTO t1 SELECT a, b+1024 FROM t1;
+INSERT INTO t1 SELECT a, b+2048 FROM t1;
+INSERT INTO t1 SELECT a, b+4096 FROM t1;
+INSERT INTO t1 SELECT a, b+8192 FROM t1;
+INSERT INTO t1 SELECT a, b+16384 FROM t1;
+INSERT INTO t1 SELECT a, b+32768 FROM t1;
+SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50;
+SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50;
+SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
+
+DROP TABLE t1;
+
+--echo End of 5.0 tests
--- 1.20/mysql-test/t/type_datetime.test 2006-11-08 19:09:52 +04:00
+++ 1.21/mysql-test/t/type_datetime.test 2006-11-08 19:09:52 +04:00
@@ -125,3 +125,13 @@ PREPARE s FROM 'SELECT a FROM t1 WHERE a
EXECUTE s;
DROP PREPARE s;
DROP TABLE t1;
+
+
+#
+# Bug 19491 (CAST DATE AS DECIMAL returns incorrect result
+#
+SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6));
+SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6));
+SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6));
+SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
+
--- 1.8/mysql-test/r/udf.result 2006-11-08 19:09:52 +04:00
+++ 1.9/mysql-test/r/udf.result 2006-11-08 19:09:52 +04:00
@@ -105,6 +105,85 @@ explain select myfunc_int(f1) from t1 or
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
drop table t1;
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 values (1,1),(2,2);
+CREATE FUNCTION fn(a int) RETURNS int DETERMINISTIC
+BEGIN
+RETURN a;
+END
+||
+CREATE VIEW v1 AS SELECT a, fn(MIN(b)) as c FROM t1 GROUP BY a;
+SELECT myfunc_int(a AS attr_name) FROM t1;
+myfunc_int(a AS attr_name)
+1
+2
+EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+Warnings:
+Note 1003 select myfunc_int(`test`.`t1`.`a` AS `attr_name`) AS `myfunc_int(a AS attr_name)` from `test`.`t1`
+EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+Warnings:
+Note 1003 select myfunc_int(`test`.`t1`.`a` AS `a`) AS `myfunc_int(a)` from `test`.`t1`
+SELECT a,c FROM v1;
+a c
+1 1
+2 2
+SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx) as c FROM t1 GROUP BY a' at line 1
+SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx)) as c FROM t1 GROUP BY a' at line 1
+SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx)) as c FROM t1 GROUP BY a' at line 1
+SELECT myfunc_int(fn(MIN(b)) xx) as c FROM t1 GROUP BY a;
+c
+1
+2
+SELECT myfunc_int(test.fn(MIN(b)) xx) as c FROM t1 GROUP BY a;
+c
+1
+2
+EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+Warnings:
+Note 1003 select myfunc_int(min(`test`.`t1`.`b`) AS `xx`) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
+EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+Warnings:
+Note 1003 select `test`.`fn`(min(`test`.`t1`.`b`)) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
+EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+Warnings:
+Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
+EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+Warnings:
+Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `test.fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
+SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a;
+c
+1
+2
+SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a;
+c
+1
+2
+SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a;
+c
+1
+2
+SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a;
+c
+1
+2
+DROP VIEW v1;
+DROP TABLE t1;
+DROP FUNCTION fn;
End of 5.0 tests.
DROP FUNCTION metaphon;
DROP FUNCTION myfunc_double;
--- 1.9/mysql-test/t/udf.test 2006-11-08 19:09:52 +04:00
+++ 1.10/mysql-test/t/udf.test 2006-11-08 19:09:52 +04:00
@@ -127,6 +127,50 @@ create table t1(f1 int);
insert into t1 values(1),(2);
explain select myfunc_int(f1) from t1 order by 1;
drop table t1;
+
+#
+# Bug #21809: Error 1356 while selecting from view with grouping though
+# underlying select OK.
+#
+CREATE TABLE t1(a INT, b INT); INSERT INTO t1 values (1,1),(2,2);
+
+DELIMITER ||;
+CREATE FUNCTION fn(a int) RETURNS int DETERMINISTIC
+BEGIN
+ RETURN a;
+END
+||
+DELIMITER ;||
+
+CREATE VIEW v1 AS SELECT a, fn(MIN(b)) as c FROM t1 GROUP BY a;
+
+SELECT myfunc_int(a AS attr_name) FROM t1;
+EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1;
+EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1;
+SELECT a,c FROM v1;
+
+--error ER_PARSE_ERROR
+SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a;
+--error ER_PARSE_ERROR
+SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a;
+--error ER_PARSE_ERROR
+SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a;
+
+SELECT myfunc_int(fn(MIN(b)) xx) as c FROM t1 GROUP BY a;
+SELECT myfunc_int(test.fn(MIN(b)) xx) as c FROM t1 GROUP BY a;
+
+EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a;
+EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a;
+EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a;
+EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a;
+SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a;
+SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a;
+SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a;
+SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a;
+DROP VIEW v1;
+DROP TABLE t1;
+DROP FUNCTION fn;
+
--echo End of 5.0 tests.
#
| Thread |
|---|
| • bk commit into 5.1 tree (holyfoot:1.2325) | holyfoot | 8 Nov |
