1
/* Copyright (C) 2000-2006 MySQL AB
3
This program is free software; you can redistribute it and/or modify
4
it under the terms of the GNU General Public License as published by
5
the Free Software Foundation; version 2 of the License.
7
This program is distributed in the hope that it will be useful,
8
but WITHOUT ANY WARRANTY; without even the implied warranty of
9
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10
GNU General Public License for more details.
12
You should have received a copy of the GNU General Public License
13
along with this program; if not, write to the Free Software
14
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
17
/* classes for sum functions */
19
#ifdef USE_PRAGMA_INTERFACE
20
#pragma interface /* gcc class implementation */
26
Class Item_sum is the base class used for special expressions that SQL calls
27
'set functions'. These expressions are formed with the help of aggregate
28
functions such as SUM, MAX, GROUP_CONCAT etc.
32
A set function cannot be used in certain positions where expressions are
33
accepted. There are some quite explicable restrictions for the usage of
37
SELECT AVG(b) FROM t1 WHERE SUM(b) > 20 GROUP by a
38
the usage of the set function AVG(b) is legal, while the usage of SUM(b)
39
is illegal. A WHERE condition must contain expressions that can be
40
evaluated for each row of the table. Yet the expression SUM(b) can be
41
evaluated only for each group of rows with the same value of column a.
43
SELECT AVG(b) FROM t1 WHERE c > 30 GROUP BY a HAVING SUM(b) > 20
44
both set function expressions AVG(b) and SUM(b) are legal.
46
We can say that in a query without nested selects an occurrence of a
47
set function in an expression of the SELECT list or/and in the HAVING
48
clause is legal, while in the WHERE clause it's illegal.
50
The general rule to detect whether a set function is legal in a query with
51
nested subqueries is much more complicated.
53
Consider the the following query:
54
SELECT t1.a FROM t1 GROUP BY t1.a
55
HAVING t1.a > ALL (SELECT t2.c FROM t2 WHERE SUM(t1.b) < t2.c).
56
The set function SUM(b) is used here in the WHERE clause of the subquery.
57
Nevertheless it is legal since it is under the HAVING clause of the query
58
to which this function relates. The expression SUM(t1.b) is evaluated
59
for each group defined in the main query, not for groups of the subquery.
61
The problem of finding the query where to aggregate a particular
62
set function is not so simple as it seems to be.
65
SELECT t1.a FROM t1 GROUP BY t1.a
66
HAVING t1.a > ALL(SELECT t2.c FROM t2 GROUP BY t2.c
67
HAVING SUM(t1.a) < t2.c)
68
the set function can be evaluated for both outer and inner selects.
69
If we evaluate SUM(t1.a) for the outer query then we get the value of t1.a
70
multiplied by the cardinality of a group in table t1. In this case
71
in each correlated subquery SUM(t1.a) is used as a constant. But we also
72
can evaluate SUM(t1.a) for the inner query. In this case t1.a will be a
73
constant for each correlated subquery and summation is performed
74
for each group of table t2.
75
(Here it makes sense to remind that the query
76
SELECT c FROM t GROUP BY a HAVING SUM(1) < a
77
is quite legal in our SQL).
79
So depending on what query we assign the set function to we
80
can get different result sets.
82
The general rule to detect the query where a set function is to be
83
evaluated can be formulated as follows.
84
Consider a set function S(E) where E is an expression with occurrences
85
of column references C1, ..., CN. Resolve these column references against
86
subqueries that contain the set function S(E). Let Q be the innermost
87
subquery of those subqueries. (It should be noted here that S(E)
88
in no way can be evaluated in the subquery embedding the subquery Q,
89
otherwise S(E) would refer to at least one unbound column reference)
90
If S(E) is used in a construct of Q where set functions are allowed then
91
we evaluate S(E) in Q.
92
Otherwise we look for a innermost subquery containing S(E) of those where
93
usage of S(E) is allowed.
95
Let's demonstrate how this rule is applied to the following queries.
97
1. SELECT t1.a FROM t1 GROUP BY t1.a
98
HAVING t1.a > ALL(SELECT t2.b FROM t2 GROUP BY t2.b
99
HAVING t2.b > ALL(SELECT t3.c FROM t3 GROUP BY t3.c
100
HAVING SUM(t1.a+t2.b) < t3.c))
101
For this query the set function SUM(t1.a+t2.b) depends on t1.a and t2.b
102
with t1.a defined in the outermost query, and t2.b defined for its
103
subquery. The set function is in the HAVING clause of the subquery and can
104
be evaluated in this subquery.
106
2. SELECT t1.a FROM t1 GROUP BY t1.a
107
HAVING t1.a > ALL(SELECT t2.b FROM t2
108
WHERE t2.b > ALL (SELECT t3.c FROM t3 GROUP BY t3.c
109
HAVING SUM(t1.a+t2.b) < t3.c))
110
Here the set function SUM(t1.a+t2.b)is in the WHERE clause of the second
111
subquery - the most upper subquery where t1.a and t2.b are defined.
112
If we evaluate the function in this subquery we violate the context rules.
113
So we evaluate the function in the third subquery (over table t3) where it
114
is used under the HAVING clause.
116
3. SELECT t1.a FROM t1 GROUP BY t1.a
117
HAVING t1.a > ALL(SELECT t2.b FROM t2
118
WHERE t2.b > ALL (SELECT t3.c FROM t3
119
WHERE SUM(t1.a+t2.b) < t3.c))
120
In this query evaluation of SUM(t1.a+t2.b) is not legal neither in the second
121
nor in the third subqueries. So this query is invalid.
123
Mostly set functions cannot be nested. In the query
124
SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20
125
the expression SUM(b) is not acceptable, though it is under a HAVING clause.
126
Yet it is acceptable in the query:
127
SELECT t.1 FROM t1 GROUP BY t1.a HAVING SUM(t1.b) > 20.
129
An argument of a set function does not have to be a reference to a table
130
column as we saw it in examples above. This can be a more complex expression
131
SELECT t1.a FROM t1 GROUP BY t1.a HAVING SUM(t1.b+1) > 20.
132
The expression SUM(t1.b+1) has a very clear semantics in this context:
133
we sum up the values of t1.b+1 where t1.b varies for all values within a
134
group of rows that contain the same t1.a value.
136
A set function for an outer query yields a constant within a subquery. So
137
the semantics of the query
138
SELECT t1.a FROM t1 GROUP BY t1.a
139
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
140
HAVING AVG(t2.c+SUM(t1.b)) > 20)
141
is still clear. For a group of the rows with the same t1.a values we
142
calculate the value of SUM(t1.b). This value 's' is substituted in the
144
SELECT t2.c FROM t2 GROUP BY t2.c HAVING AVG(t2.c+s)
145
than returns some result set.
147
By the same reason the following query with a subquery
148
SELECT t1.a FROM t1 GROUP BY t1.a
149
HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
150
HAVING AVG(SUM(t1.b)) > 20)
155
Three methods were added to the class to check the constraints specified
156
in the previous section. These methods utilize several new members.
158
The field 'nest_level' contains the number of the level for the subquery
159
containing the set function. The main SELECT is of level 0, its subqueries
160
are of levels 1, the subqueries of the latter are of level 2 and so on.
162
The field 'aggr_level' is to contain the nest level of the subquery
163
where the set function is aggregated.
165
The field 'max_arg_level' is for the maximun of the nest levels of the
166
unbound column references occurred in the set function. A column reference
167
is unbound within a set function if it is not bound by any subquery
168
used as a subexpression in this function. A column reference is bound by
169
a subquery if it is a reference to the column by which the aggregation
170
of some set function that is used in the subquery is calculated.
171
For the set function used in the query
172
SELECT t1.a FROM t1 GROUP BY t1.a
173
HAVING t1.a > ALL(SELECT t2.b FROM t2 GROUP BY t2.b
174
HAVING t2.b > ALL(SELECT t3.c FROM t3 GROUP BY t3.c
175
HAVING SUM(t1.a+t2.b) < t3.c))
176
the value of max_arg_level is equal to 1 since t1.a is bound in the main
177
query, and t2.b is bound by the first subquery whose nest level is 1.
178
Obviously a set function cannot be aggregated in the subquery whose
179
nest level is less than max_arg_level. (Yet it can be aggregated in the
180
subqueries whose nest level is greater than max_arg_level.)
182
SELECT t.a FROM t1 HAVING AVG(t1.a+(SELECT MIN(t2.c) FROM t2))
183
the value of the max_arg_level for the AVG set function is 0 since
184
the reference t2.c is bound in the subquery.
186
The field 'max_sum_func_level' is to contain the maximum of the
187
nest levels of the set functions that are used as subexpressions of
188
the arguments of the given set function, but not aggregated in any
189
subquery within this set function. A nested set function s1 can be
190
used within set function s0 only if s1.max_sum_func_level <
191
s0.max_sum_func_level. Set function s1 is considered as nested
192
for set function s0 if s1 is not calculated in any subquery
195
A set function that is used as a subexpression in an argument of another
196
set function refers to the latter via the field 'in_sum_func'.
198
The condition imposed on the usage of set functions are checked when
199
we traverse query subexpressions with the help of the recursive method
200
fix_fields. When we apply this method to an object of the class
201
Item_sum, first, on the descent, we call the method init_sum_func_check
202
that initialize members used at checking. Then, on the ascent, we
203
call the method check_sum_func that validates the set function usage
204
and reports an error if it is illegal.
205
The method register_sum_func serves to link the items for the set functions
206
that are aggregated in the embedding (sub)queries. Circular chains of such
207
functions are attached to the corresponding st_select_lex structures
208
through the field inner_sum_func_list.
210
Exploiting the fact that the members mentioned above are used in one
211
recursive function we could have allocated them on the thread stack.
212
Yet we don't do it now.
214
We assume that the nesting level of subquries does not exceed 127.
215
TODO: to catch queries where the limit is exceeded to make the
222
class Item_sum :public Item_result_field
226
{ COUNT_FUNC, COUNT_DISTINCT_FUNC, SUM_FUNC, SUM_DISTINCT_FUNC, AVG_FUNC,
227
AVG_DISTINCT_FUNC, MIN_FUNC, MAX_FUNC, STD_FUNC,
228
VARIANCE_FUNC, SUM_BIT_FUNC, UDF_SUM_FUNC, GROUP_CONCAT_FUNC
231
Item **args, *tmp_args[2];
232
Item **ref_by; /* pointer to a ref to the object used to register it */
233
Item_sum *next; /* next in the circular chain of registered objects */
235
Item_sum *in_sum_func; /* embedding set function if any */
236
st_select_lex * aggr_sel; /* select where the function is aggregated */
237
int8 nest_level; /* number of the nesting level of the set function */
238
int8 aggr_level; /* nesting level of the aggregating subquery */
239
int8 max_arg_level; /* max level of unbound column references */
240
int8 max_sum_func_level;/* max level of aggregation for embedded functions */
241
bool quick_group; /* If incremental update of fields */
243
This list is used by the check for mixing non aggregated fields and
244
sum functions in the ONLY_FULL_GROUP_BY_MODE. We save all outer fields
245
directly or indirectly used under this function it as it's unclear
246
at the moment of fixing outer field whether it's aggregated or not.
248
List<Item_field> outer_fields;
251
table_map used_tables_cache;
256
void mark_as_sum_func();
257
Item_sum() :arg_count(0), quick_group(1), forced_const(FALSE)
261
Item_sum(Item *a) :args(tmp_args), arg_count(1), quick_group(1),
267
Item_sum( Item *a, Item *b ) :args(tmp_args), arg_count(2), quick_group(1),
270
args[0]=a; args[1]=b;
273
Item_sum(List<Item> &list);
274
//Copy constructor, need to perform subselects with temporary tables
275
Item_sum(THD *thd, Item_sum *item);
276
enum Type type() const { return SUM_FUNC_ITEM; }
277
virtual enum Sumfunctype sum_func () const=0;
280
This method is similar to add(), but it is called when the current
281
aggregation group changes. Thus it performs a combination of
284
inline bool reset() { clear(); return add(); };
287
Prepare this item for evaluation of an aggregate value. This is
288
called by reset() when a group changes, or, for correlated
289
subqueries, between subquery executions. E.g. for COUNT(), this
290
method should set count= 0;
292
virtual void clear()= 0;
295
This method is called for the next row in the same group. Its
296
purpose is to aggregate the new value to the previous values in
297
the group (i.e. since clear() was called last time). For example,
298
for COUNT(), do count++.
300
virtual bool add()=0;
303
Called when new group is started and results are being saved in
304
a temporary table. Similar to reset(), but must also store value in
305
result_field. Like reset() it is supposed to reset start value to
307
This set of methods (reult_field(), reset_field, update_field()) of
308
Item_sum is used only if quick_group is not null. Otherwise
309
copy_or_same() is used to obtain a copy of this item.
311
virtual void reset_field()=0;
313
Called for each new value in the group, when temporary table is in use.
314
Similar to add(), but uses temporary table field to obtain current value,
315
Updated value is then saved in the field.
317
virtual void update_field()=0;
318
virtual bool keep_field_type(void) const { return 0; }
319
virtual void fix_length_and_dec() { maybe_null=1; null_value=1; }
321
This method is used for debug purposes to print the name of an
322
item to the debug log. The second use of this method is as
323
a helper function of print(), where it is applicable.
324
To suit both goals it should return a meaningful,
325
distinguishable and sintactically correct string. This method
326
should not be used for runtime type identification, use enum
327
{Sum}Functype and Item_func::functype()/Item_sum::sum_func()
330
NOTE: for Items inherited from Item_sum, func_name() return part of
331
function name till first argument (including '(') to make difference in
332
names for functions with 'distinct' clause and without 'distinct' and
333
also to make printing of items inherited from Item_sum uniform.
335
virtual const char *func_name() const= 0;
336
virtual Item *result_item(Field *field)
337
{ return new Item_field(field); }
338
table_map used_tables() const { return used_tables_cache; }
339
void update_used_tables ();
345
bool is_null() { return null_value; }
348
used_tables_cache= 0;
351
virtual bool const_item() const { return forced_const; }
352
void make_field(Send_field *field);
353
virtual void print(String *str, enum_query_type query_type);
354
void fix_num_length_and_dec();
357
This function is called by the execution engine to assign 'NO ROWS
358
FOUND' value to an aggregate item, when the underlying result set
359
has no rows. Such value, in a general case, may be different from
360
the default value of the item after 'clear()': e.g. a numeric item
361
may be initialized to 0 by clear() and to NULL by
364
void no_rows_in_result() { clear(); }
366
virtual bool setup(THD *thd) {return 0;}
367
virtual void make_unique() {}
368
Item *get_tmp_table_item(THD *thd);
369
virtual Field *create_tmp_field(bool group, TABLE *table,
370
uint convert_blob_length);
371
bool walk(Item_processor processor, bool walk_subquery, uchar *argument);
372
bool init_sum_func_check(THD *thd);
373
bool check_sum_func(THD *thd, Item **ref);
374
bool register_sum_func(THD *thd, Item **ref);
375
st_select_lex *depended_from()
376
{ return (nest_level == aggr_level ? 0 : aggr_sel); }
380
class Item_sum_num :public Item_sum
384
val_xxx() functions may be called several times during the execution of a
385
query. Derived classes that require extensive calculation in val_xxx()
386
maintain cache of aggregate value. This variable governs the validity of
391
Item_sum_num() :Item_sum(),is_evaluated(FALSE) {}
392
Item_sum_num(Item *item_par)
393
:Item_sum(item_par), is_evaluated(FALSE) {}
394
Item_sum_num(Item *a, Item* b) :Item_sum(a,b),is_evaluated(FALSE) {}
395
Item_sum_num(List<Item> &list)
396
:Item_sum(list), is_evaluated(FALSE) {}
397
Item_sum_num(THD *thd, Item_sum_num *item)
398
:Item_sum(thd, item),is_evaluated(item->is_evaluated) {}
399
bool fix_fields(THD *, Item **);
402
DBUG_ASSERT(fixed == 1);
403
return (longlong) rint(val_real()); /* Real as default */
405
String *val_str(String*str);
406
my_decimal *val_decimal(my_decimal *);
411
class Item_sum_int :public Item_sum_num
414
Item_sum_int(Item *item_par) :Item_sum_num(item_par) {}
415
Item_sum_int(List<Item> &list) :Item_sum_num(list) {}
416
Item_sum_int(THD *thd, Item_sum_int *item) :Item_sum_num(thd, item) {}
417
double val_real() { DBUG_ASSERT(fixed == 1); return (double) val_int(); }
418
String *val_str(String*str);
419
my_decimal *val_decimal(my_decimal *);
420
enum Item_result result_type () const { return INT_RESULT; }
421
void fix_length_and_dec()
422
{ decimals=0; max_length=21; maybe_null=null_value=0; }
426
class Item_sum_sum :public Item_sum_num
429
Item_result hybrid_type;
431
my_decimal dec_buffs[2];
433
void fix_length_and_dec();
436
Item_sum_sum(Item *item_par) :Item_sum_num(item_par) {}
437
Item_sum_sum(THD *thd, Item_sum_sum *item);
438
enum Sumfunctype sum_func () const {return SUM_FUNC;}
443
String *val_str(String*str);
444
my_decimal *val_decimal(my_decimal *);
445
enum Item_result result_type () const { return hybrid_type; }
448
void no_rows_in_result() {}
449
const char *func_name() const { return "sum("; }
450
Item *copy_or_same(THD* thd);
455
/* Common class for SUM(DISTINCT), AVG(DISTINCT) */
459
class Item_sum_distinct :public Item_sum_num
462
/* storage for the summation result */
465
/* storage for unique elements */
468
enum enum_field_types table_field_type;
469
uint tree_key_length;
471
Item_sum_distinct(THD *thd, Item_sum_distinct *item);
473
Item_sum_distinct(Item *item_par);
474
~Item_sum_distinct();
476
bool setup(THD *thd);
481
my_decimal *val_decimal(my_decimal *);
483
String *val_str(String *str);
485
/* XXX: does it need make_unique? */
487
enum Sumfunctype sum_func () const { return SUM_DISTINCT_FUNC; }
488
void reset_field() {} // not used
489
void update_field() {} // not used
490
virtual void no_rows_in_result() {}
491
void fix_length_and_dec();
492
enum Item_result result_type () const { return val.traits->type(); }
493
virtual void calculate_val_and_count();
494
virtual bool unique_walk_function(void *elem);
499
Item_sum_sum_distinct - implementation of SUM(DISTINCT expr).
500
See also: MySQL manual, chapter 'Adding New Functions To MySQL'
501
and comments in item_sum.cc.
504
class Item_sum_sum_distinct :public Item_sum_distinct
507
Item_sum_sum_distinct(THD *thd, Item_sum_sum_distinct *item)
508
:Item_sum_distinct(thd, item) {}
510
Item_sum_sum_distinct(Item *item_arg) :Item_sum_distinct(item_arg) {}
512
enum Sumfunctype sum_func () const { return SUM_DISTINCT_FUNC; }
513
const char *func_name() const { return "sum(distinct "; }
514
Item *copy_or_same(THD* thd) { return new Item_sum_sum_distinct(thd, this); }
518
/* Item_sum_avg_distinct - SELECT AVG(DISTINCT expr) FROM ... */
520
class Item_sum_avg_distinct: public Item_sum_distinct
523
Item_sum_avg_distinct(THD *thd, Item_sum_avg_distinct *original)
524
:Item_sum_distinct(thd, original) {}
527
Item_sum_avg_distinct(Item *item_arg) : Item_sum_distinct(item_arg) {}
529
void fix_length_and_dec();
530
virtual void calculate_val_and_count();
531
enum Sumfunctype sum_func () const { return AVG_DISTINCT_FUNC; }
532
const char *func_name() const { return "avg(distinct "; }
533
Item *copy_or_same(THD* thd) { return new Item_sum_avg_distinct(thd, this); }
537
class Item_sum_count :public Item_sum_int
542
Item_sum_count(Item *item_par)
543
:Item_sum_int(item_par),count(0)
545
Item_sum_count(THD *thd, Item_sum_count *item)
546
:Item_sum_int(thd, item), count(item->count)
548
enum Sumfunctype sum_func () const { return COUNT_FUNC; }
550
void no_rows_in_result() { count=0; }
552
void make_const(longlong count_arg)
555
Item_sum::make_const();
561
const char *func_name() const { return "count("; }
562
Item *copy_or_same(THD* thd);
566
class TMP_TABLE_PARAM;
568
class Item_sum_count_distinct :public Item_sum_int
571
uint32 *field_lengths;
572
TMP_TABLE_PARAM *tmp_table_param;
573
bool force_copy_fields;
575
If there are no blobs, we can use a tree, which
576
is faster than heap table. In that case, we still use the table
577
to help get things set up, but we insert nothing in it
581
Storage for the value of count between calls to val_int() so val_int()
582
will not recalculate on each call. Validitiy of the value is stored in
587
Following is 0 normal object and pointer to original one for copy
588
(to correctly free resources)
590
Item_sum_count_distinct *original;
591
uint tree_key_length;
594
bool always_null; // Set to 1 if the result is always NULL
597
friend int composite_key_cmp(void* arg, uchar* key1, uchar* key2);
598
friend int simple_str_key_cmp(void* arg, uchar* key1, uchar* key2);
601
Item_sum_count_distinct(List<Item> &list)
602
:Item_sum_int(list), table(0), field_lengths(0), tmp_table_param(0),
603
force_copy_fields(0), tree(0), count(0),
604
original(0), always_null(FALSE)
606
Item_sum_count_distinct(THD *thd, Item_sum_count_distinct *item)
607
:Item_sum_int(thd, item), table(item->table),
608
field_lengths(item->field_lengths),
609
tmp_table_param(item->tmp_table_param),
610
force_copy_fields(0), tree(item->tree), count(item->count),
611
original(item), tree_key_length(item->tree_key_length),
612
always_null(item->always_null)
614
~Item_sum_count_distinct();
618
enum Sumfunctype sum_func () const { return COUNT_DISTINCT_FUNC; }
622
void reset_field() { return ;} // Never called
623
void update_field() { return ; } // Never called
624
const char *func_name() const { return "count(distinct "; }
625
bool setup(THD *thd);
627
Item *copy_or_same(THD* thd);
628
void no_rows_in_result() {}
632
/* Item to get the value of a stored sum function */
636
class Item_avg_field :public Item_result_field
640
Item_result hybrid_type;
641
uint f_precision, f_scale, dec_bin_size;
643
Item_avg_field(Item_result res_type, Item_sum_avg *item);
644
enum Type type() const { return FIELD_AVG_ITEM; }
647
my_decimal *val_decimal(my_decimal *);
648
bool is_null() { update_null_value(); return null_value; }
649
String *val_str(String*);
650
enum_field_types field_type() const
652
return hybrid_type == DECIMAL_RESULT ?
653
MYSQL_TYPE_NEWDECIMAL : MYSQL_TYPE_DOUBLE;
655
void fix_length_and_dec() {}
656
enum Item_result result_type () const { return hybrid_type; }
660
class Item_sum_avg :public Item_sum_sum
665
uint f_precision, f_scale, dec_bin_size;
667
Item_sum_avg(Item *item_par) :Item_sum_sum(item_par), count(0) {}
668
Item_sum_avg(THD *thd, Item_sum_avg *item)
669
:Item_sum_sum(thd, item), count(item->count),
670
prec_increment(item->prec_increment) {}
672
void fix_length_and_dec();
673
enum Sumfunctype sum_func () const {return AVG_FUNC;}
677
// In SPs we might force the "wrong" type with select into a declare variable
678
longlong val_int() { return (longlong) rint(val_real()); }
679
my_decimal *val_decimal(my_decimal *);
680
String *val_str(String *str);
683
Item *result_item(Field *field)
684
{ return new Item_avg_field(hybrid_type, this); }
685
void no_rows_in_result() {}
686
const char *func_name() const { return "avg("; }
687
Item *copy_or_same(THD* thd);
688
Field *create_tmp_field(bool group, TABLE *table, uint convert_blob_length);
692
Item_sum_sum::cleanup();
696
class Item_sum_variance;
698
class Item_variance_field :public Item_result_field
702
Item_result hybrid_type;
703
uint f_precision0, f_scale0;
704
uint f_precision1, f_scale1;
705
uint dec_bin_size0, dec_bin_size1;
708
Item_variance_field(Item_sum_variance *item);
709
enum Type type() const {return FIELD_VARIANCE_ITEM; }
712
{ /* can't be fix_fields()ed */ return (longlong) rint(val_real()); }
713
String *val_str(String *str)
714
{ return val_string_from_real(str); }
715
my_decimal *val_decimal(my_decimal *dec_buf)
716
{ return val_decimal_from_real(dec_buf); }
717
bool is_null() { update_null_value(); return null_value; }
718
enum_field_types field_type() const
720
return hybrid_type == DECIMAL_RESULT ?
721
MYSQL_TYPE_NEWDECIMAL : MYSQL_TYPE_DOUBLE;
723
void fix_length_and_dec() {}
724
enum Item_result result_type () const { return hybrid_type; }
731
= sum (ai - avg(a))^2 / count(a) )
732
= sum (ai^2 - 2*ai*avg(a) + avg(a)^2) / count(a)
733
= (sum(ai^2) - sum(2*ai*avg(a)) + sum(avg(a)^2))/count(a) =
734
= (sum(ai^2) - 2*avg(a)*sum(a) + count(a)*avg(a)^2)/count(a) =
735
= (sum(ai^2) - 2*sum(a)*sum(a)/count(a) + count(a)*sum(a)^2/count(a)^2 )/count(a) =
736
= (sum(ai^2) - 2*sum(a)^2/count(a) + sum(a)^2/count(a) )/count(a) =
737
= (sum(ai^2) - sum(a)^2/count(a))/count(a)
739
But, this falls prey to catastrophic cancellation. Instead, use the recurrence formulas
741
M_{1} = x_{1}, ~ M_{k} = M_{k-1} + (x_{k} - M_{k-1}) / k newline
742
S_{1} = 0, ~ S_{k} = S_{k-1} + (x_{k} - M_{k-1}) times (x_{k} - M_{k}) newline
743
for 2 <= k <= n newline
744
ital variance = S_{n} / (n-1)
748
class Item_sum_variance : public Item_sum_num
750
void fix_length_and_dec();
753
Item_result hybrid_type;
755
double recurrence_m, recurrence_s; /* Used in recurrence relation. */
757
uint f_precision0, f_scale0;
758
uint f_precision1, f_scale1;
759
uint dec_bin_size0, dec_bin_size1;
763
Item_sum_variance(Item *item_par, uint sample_arg) :Item_sum_num(item_par),
764
hybrid_type(REAL_RESULT), count(0), sample(sample_arg)
766
Item_sum_variance(THD *thd, Item_sum_variance *item);
767
enum Sumfunctype sum_func () const { return VARIANCE_FUNC; }
771
my_decimal *val_decimal(my_decimal *);
774
Item *result_item(Field *field)
775
{ return new Item_variance_field(this); }
776
void no_rows_in_result() {}
777
const char *func_name() const
778
{ return sample ? "var_samp(" : "variance("; }
779
Item *copy_or_same(THD* thd);
780
Field *create_tmp_field(bool group, TABLE *table, uint convert_blob_length);
781
enum Item_result result_type () const { return REAL_RESULT; }
785
Item_sum_num::cleanup();
791
class Item_std_field :public Item_variance_field
794
Item_std_field(Item_sum_std *item);
795
enum Type type() const { return FIELD_STD_ITEM; }
797
my_decimal *val_decimal(my_decimal *);
798
enum Item_result result_type () const { return REAL_RESULT; }
799
enum_field_types field_type() const { return MYSQL_TYPE_DOUBLE;}
803
standard_deviation(a) = sqrt(variance(a))
806
class Item_sum_std :public Item_sum_variance
809
Item_sum_std(Item *item_par, uint sample_arg)
810
:Item_sum_variance(item_par, sample_arg) {}
811
Item_sum_std(THD *thd, Item_sum_std *item)
812
:Item_sum_variance(thd, item)
814
enum Sumfunctype sum_func () const { return STD_FUNC; }
816
Item *result_item(Field *field)
817
{ return new Item_std_field(this); }
818
const char *func_name() const { return "std("; }
819
Item *copy_or_same(THD* thd);
820
enum Item_result result_type () const { return REAL_RESULT; }
821
enum_field_types field_type() const { return MYSQL_TYPE_DOUBLE;}
824
// This class is a string or number function depending on num_func
826
class Item_sum_hybrid :public Item_sum
829
String value,tmp_value;
833
Item_result hybrid_type;
834
enum_field_types hybrid_field_type;
836
bool was_values; // Set if we have found at least one row (for max/min only)
839
Item_sum_hybrid(Item *item_par,int sign)
840
:Item_sum(item_par), sum(0.0), sum_int(0),
841
hybrid_type(INT_RESULT), hybrid_field_type(MYSQL_TYPE_LONGLONG),
842
cmp_sign(sign), was_values(TRUE)
843
{ collation.set(&my_charset_bin); }
844
Item_sum_hybrid(THD *thd, Item_sum_hybrid *item);
845
bool fix_fields(THD *, Item **);
849
my_decimal *val_decimal(my_decimal *);
851
String *val_str(String *);
852
bool keep_field_type(void) const { return 1; }
853
enum Item_result result_type () const { return hybrid_type; }
854
enum enum_field_types field_type() const { return hybrid_field_type; }
856
void min_max_update_str_field();
857
void min_max_update_real_field();
858
void min_max_update_int_field();
859
void min_max_update_decimal_field();
861
bool any_value() { return was_values; }
862
void no_rows_in_result();
863
Field *create_tmp_field(bool group, TABLE *table,
864
uint convert_blob_length);
868
class Item_sum_min :public Item_sum_hybrid
871
Item_sum_min(Item *item_par) :Item_sum_hybrid(item_par,1) {}
872
Item_sum_min(THD *thd, Item_sum_min *item) :Item_sum_hybrid(thd, item) {}
873
enum Sumfunctype sum_func () const {return MIN_FUNC;}
876
const char *func_name() const { return "min("; }
877
Item *copy_or_same(THD* thd);
881
class Item_sum_max :public Item_sum_hybrid
884
Item_sum_max(Item *item_par) :Item_sum_hybrid(item_par,-1) {}
885
Item_sum_max(THD *thd, Item_sum_max *item) :Item_sum_hybrid(thd, item) {}
886
enum Sumfunctype sum_func () const {return MAX_FUNC;}
889
const char *func_name() const { return "max("; }
890
Item *copy_or_same(THD* thd);
894
class Item_sum_bit :public Item_sum_int
897
ulonglong reset_bits,bits;
900
Item_sum_bit(Item *item_par,ulonglong reset_arg)
901
:Item_sum_int(item_par),reset_bits(reset_arg),bits(reset_arg) {}
902
Item_sum_bit(THD *thd, Item_sum_bit *item):
903
Item_sum_int(thd, item), reset_bits(item->reset_bits), bits(item->bits) {}
904
enum Sumfunctype sum_func () const {return SUM_BIT_FUNC;}
909
void fix_length_and_dec()
910
{ decimals= 0; max_length=21; unsigned_flag= 1; maybe_null= null_value= 0; }
914
Item_sum_int::cleanup();
919
class Item_sum_or :public Item_sum_bit
922
Item_sum_or(Item *item_par) :Item_sum_bit(item_par,LL(0)) {}
923
Item_sum_or(THD *thd, Item_sum_or *item) :Item_sum_bit(thd, item) {}
925
const char *func_name() const { return "bit_or("; }
926
Item *copy_or_same(THD* thd);
930
class Item_sum_and :public Item_sum_bit
933
Item_sum_and(Item *item_par) :Item_sum_bit(item_par, ULONGLONG_MAX) {}
934
Item_sum_and(THD *thd, Item_sum_and *item) :Item_sum_bit(thd, item) {}
936
const char *func_name() const { return "bit_and("; }
937
Item *copy_or_same(THD* thd);
940
class Item_sum_xor :public Item_sum_bit
943
Item_sum_xor(Item *item_par) :Item_sum_bit(item_par,LL(0)) {}
944
Item_sum_xor(THD *thd, Item_sum_xor *item) :Item_sum_bit(thd, item) {}
946
const char *func_name() const { return "bit_xor("; }
947
Item *copy_or_same(THD* thd);
952
User defined aggregates
957
class Item_udf_sum : public Item_sum
963
Item_udf_sum(udf_func *udf_arg)
964
:Item_sum(), udf(udf_arg)
966
Item_udf_sum(udf_func *udf_arg, List<Item> &list)
967
:Item_sum(list), udf(udf_arg)
969
Item_udf_sum(THD *thd, Item_udf_sum *item)
970
:Item_sum(thd, item), udf(item->udf)
971
{ udf.not_original= TRUE; }
972
const char *func_name() const { return udf.name(); }
973
bool fix_fields(THD *thd, Item **ref)
975
DBUG_ASSERT(fixed == 0);
977
if (init_sum_func_check(thd))
981
if (udf.fix_fields(thd, this, this->arg_count, this->args))
984
return check_sum_func(thd, ref);
986
enum Sumfunctype sum_func () const { return UDF_SUM_FUNC; }
987
virtual bool have_field_update(void) const { return 0; }
991
void reset_field() {};
992
void update_field() {};
994
virtual void print(String *str, enum_query_type query_type);
998
class Item_sum_udf_float :public Item_udf_sum
1001
Item_sum_udf_float(udf_func *udf_arg)
1002
:Item_udf_sum(udf_arg) {}
1003
Item_sum_udf_float(udf_func *udf_arg, List<Item> &list)
1004
:Item_udf_sum(udf_arg, list) {}
1005
Item_sum_udf_float(THD *thd, Item_sum_udf_float *item)
1006
:Item_udf_sum(thd, item) {}
1009
DBUG_ASSERT(fixed == 1);
1010
return (longlong) rint(Item_sum_udf_float::val_real());
1013
String *val_str(String*str);
1014
my_decimal *val_decimal(my_decimal *);
1015
void fix_length_and_dec() { fix_num_length_and_dec(); }
1016
Item *copy_or_same(THD* thd);
1020
class Item_sum_udf_int :public Item_udf_sum
1023
Item_sum_udf_int(udf_func *udf_arg)
1024
:Item_udf_sum(udf_arg) {}
1025
Item_sum_udf_int(udf_func *udf_arg, List<Item> &list)
1026
:Item_udf_sum(udf_arg, list) {}
1027
Item_sum_udf_int(THD *thd, Item_sum_udf_int *item)
1028
:Item_udf_sum(thd, item) {}
1031
{ DBUG_ASSERT(fixed == 1); return (double) Item_sum_udf_int::val_int(); }
1032
String *val_str(String*str);
1033
my_decimal *val_decimal(my_decimal *);
1034
enum Item_result result_type () const { return INT_RESULT; }
1035
void fix_length_and_dec() { decimals=0; max_length=21; }
1036
Item *copy_or_same(THD* thd);
1040
class Item_sum_udf_str :public Item_udf_sum
1043
Item_sum_udf_str(udf_func *udf_arg)
1044
:Item_udf_sum(udf_arg) {}
1045
Item_sum_udf_str(udf_func *udf_arg, List<Item> &list)
1046
:Item_udf_sum(udf_arg,list) {}
1047
Item_sum_udf_str(THD *thd, Item_sum_udf_str *item)
1048
:Item_udf_sum(thd, item) {}
1049
String *val_str(String *);
1055
res=val_str(&str_value);
1056
return res ? my_strntod(res->charset(),(char*) res->ptr(),res->length(),
1057
&end_not_used, &err_not_used) : 0.0;
1066
if (!(res= val_str(&str_value)))
1067
return 0; /* Null value */
1069
end= (char*) res->ptr()+res->length();
1070
return cs->cset->strtoll10(cs, res->ptr(), &end, &err_not_used);
1072
my_decimal *val_decimal(my_decimal *dec);
1073
enum Item_result result_type () const { return STRING_RESULT; }
1074
void fix_length_and_dec();
1075
Item *copy_or_same(THD* thd);
1079
class Item_sum_udf_decimal :public Item_udf_sum
1082
Item_sum_udf_decimal(udf_func *udf_arg)
1083
:Item_udf_sum(udf_arg) {}
1084
Item_sum_udf_decimal(udf_func *udf_arg, List<Item> &list)
1085
:Item_udf_sum(udf_arg, list) {}
1086
Item_sum_udf_decimal(THD *thd, Item_sum_udf_decimal *item)
1087
:Item_udf_sum(thd, item) {}
1088
String *val_str(String *);
1091
my_decimal *val_decimal(my_decimal *);
1092
enum Item_result result_type () const { return DECIMAL_RESULT; }
1093
void fix_length_and_dec() { fix_num_length_and_dec(); }
1094
Item *copy_or_same(THD* thd);
1097
#else /* Dummy functions to get sql_yacc.cc compiled */
1099
class Item_sum_udf_float :public Item_sum_num
1102
Item_sum_udf_float(udf_func *udf_arg)
1104
Item_sum_udf_float(udf_func *udf_arg, List<Item> &list) :Item_sum_num() {}
1105
Item_sum_udf_float(THD *thd, Item_sum_udf_float *item)
1106
:Item_sum_num(thd, item) {}
1107
enum Sumfunctype sum_func () const { return UDF_SUM_FUNC; }
1108
double val_real() { DBUG_ASSERT(fixed == 1); return 0.0; }
1110
bool add() { return 0; }
1111
void update_field() {}
1115
class Item_sum_udf_int :public Item_sum_num
1118
Item_sum_udf_int(udf_func *udf_arg)
1120
Item_sum_udf_int(udf_func *udf_arg, List<Item> &list) :Item_sum_num() {}
1121
Item_sum_udf_int(THD *thd, Item_sum_udf_int *item)
1122
:Item_sum_num(thd, item) {}
1123
enum Sumfunctype sum_func () const { return UDF_SUM_FUNC; }
1124
longlong val_int() { DBUG_ASSERT(fixed == 1); return 0; }
1125
double val_real() { DBUG_ASSERT(fixed == 1); return 0; }
1127
bool add() { return 0; }
1128
void update_field() {}
1132
class Item_sum_udf_decimal :public Item_sum_num
1135
Item_sum_udf_decimal(udf_func *udf_arg)
1137
Item_sum_udf_decimal(udf_func *udf_arg, List<Item> &list)
1139
Item_sum_udf_decimal(THD *thd, Item_sum_udf_float *item)
1140
:Item_sum_num(thd, item) {}
1141
enum Sumfunctype sum_func () const { return UDF_SUM_FUNC; }
1142
double val_real() { DBUG_ASSERT(fixed == 1); return 0.0; }
1143
my_decimal *val_decimal(my_decimal *) { DBUG_ASSERT(fixed == 1); return 0; }
1145
bool add() { return 0; }
1146
void update_field() {}
1150
class Item_sum_udf_str :public Item_sum_num
1153
Item_sum_udf_str(udf_func *udf_arg)
1155
Item_sum_udf_str(udf_func *udf_arg, List<Item> &list)
1157
Item_sum_udf_str(THD *thd, Item_sum_udf_str *item)
1158
:Item_sum_num(thd, item) {}
1159
String *val_str(String *)
1160
{ DBUG_ASSERT(fixed == 1); null_value=1; return 0; }
1161
double val_real() { DBUG_ASSERT(fixed == 1); null_value=1; return 0.0; }
1162
longlong val_int() { DBUG_ASSERT(fixed == 1); null_value=1; return 0; }
1163
enum Item_result result_type () const { return STRING_RESULT; }
1164
void fix_length_and_dec() { maybe_null=1; max_length=0; }
1165
enum Sumfunctype sum_func () const { return UDF_SUM_FUNC; }
1167
bool add() { return 0; }
1168
void update_field() {}
1171
#endif /* HAVE_DLOPEN */
1175
class Item_func_group_concat : public Item_sum
1177
TMP_TABLE_PARAM *tmp_table_param;
1178
MYSQL_ERROR *warning;
1185
If DISTINCT is used with this GROUP_CONCAT, this member is used to filter
1187
@see Item_func_group_concat::setup
1188
@see Item_func_group_concat::add
1189
@see Item_func_group_concat::clear
1191
Unique *unique_filter;
1194
Name_resolution_context *context;
1195
/** The number of ORDER BY items. */
1196
uint arg_count_order;
1197
/** The number of selected items, aka the expr list. */
1198
uint arg_count_field;
1199
uint count_cut_values;
1201
bool warning_for_row;
1203
bool force_copy_fields;
1206
Following is 0 normal object and pointer to original one for copy
1207
(to correctly free resources)
1209
Item_func_group_concat *original;
1211
friend int group_concat_key_cmp_with_distinct(void* arg, const void* key1,
1213
friend int group_concat_key_cmp_with_order(void* arg, const void* key1,
1215
friend int dump_leaf_key(uchar* key,
1216
element_count count __attribute__((unused)),
1217
Item_func_group_concat *group_concat_item);
1220
Item_func_group_concat(Name_resolution_context *context_arg,
1221
bool is_distinct, List<Item> *is_select,
1222
SQL_LIST *is_order, String *is_separator);
1224
Item_func_group_concat(THD *thd, Item_func_group_concat *item);
1225
~Item_func_group_concat();
1228
enum Sumfunctype sum_func () const {return GROUP_CONCAT_FUNC;}
1229
const char *func_name() const { return "group_concat"; }
1230
virtual Item_result result_type () const { return STRING_RESULT; }
1231
enum_field_types field_type() const
1233
if (max_length/collation.collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB )
1234
return MYSQL_TYPE_BLOB;
1236
return MYSQL_TYPE_VARCHAR;
1240
void reset_field() { DBUG_ASSERT(0); } // not used
1241
void update_field() { DBUG_ASSERT(0); } // not used
1242
bool fix_fields(THD *,Item **);
1243
bool setup(THD *thd);
1247
String *res; res=val_str(&str_value);
1248
return res ? my_atof(res->c_ptr()) : 0.0;
1255
if (!(res= val_str(&str_value)))
1256
return (longlong) 0;
1257
end_ptr= (char*) res->ptr()+ res->length();
1258
return my_strtoll10(res->ptr(), &end_ptr, &error);
1260
my_decimal *val_decimal(my_decimal *decimal_value)
1262
return val_decimal_from_string(decimal_value);
1264
String* val_str(String* str);
1265
Item *copy_or_same(THD* thd);
1266
void no_rows_in_result() {}
1267
virtual void print(String *str, enum_query_type query_type);
1268
virtual bool change_context_processor(uchar *cntx)
1269
{ context= (Name_resolution_context *)cntx; return FALSE; }
added
removed
sql/item_sum.h
