1 /* Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights
4 This program is free software; you can redistribute it and/or modify
5 it under the terms of the GNU General Public License as published by
6 the Free Software Foundation; version 2 of the License.
8 This program is distributed in the hope that it will be useful,
9 but WITHOUT ANY WARRANTY; without even the implied warranty of
10 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 GNU General Public License for more details.
13 You should have received a copy of the GNU General Public License
14 along with this program; if not, write to the Free Software
15 Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
19 /* classes for sum functions */
21 #ifdef USE_PRAGMA_INTERFACE
22 #pragma interface /* gcc class implementation */
28 Class Item_sum is the base class used for special expressions that SQL calls
29 'set functions'. These expressions are formed with the help of aggregate
30 functions such as SUM, MAX, GROUP_CONCAT etc.
34 A set function cannot be used in certain positions where expressions are
35 accepted. There are some quite explicable restrictions for the usage of
39 SELECT AVG(b) FROM t1 WHERE SUM(b) > 20 GROUP by a
40 the usage of the set function AVG(b) is legal, while the usage of SUM(b)
41 is illegal. A WHERE condition must contain expressions that can be
42 evaluated for each row of the table. Yet the expression SUM(b) can be
43 evaluated only for each group of rows with the same value of column a.
45 SELECT AVG(b) FROM t1 WHERE c > 30 GROUP BY a HAVING SUM(b) > 20
46 both set function expressions AVG(b) and SUM(b) are legal.
48 We can say that in a query without nested selects an occurrence of a
49 set function in an expression of the SELECT list or/and in the HAVING
50 clause is legal, while in the WHERE clause it's illegal.
52 The general rule to detect whether a set function is legal in a query with
53 nested subqueries is much more complicated.
55 Consider the the following query:
56 SELECT t1.a FROM t1 GROUP BY t1.a
57 HAVING t1.a > ALL (SELECT t2.c FROM t2 WHERE SUM(t1.b) < t2.c).
58 The set function SUM(b) is used here in the WHERE clause of the subquery.
59 Nevertheless it is legal since it is under the HAVING clause of the query
60 to which this function relates. The expression SUM(t1.b) is evaluated
61 for each group defined in the main query, not for groups of the subquery.
63 The problem of finding the query where to aggregate a particular
64 set function is not so simple as it seems to be.
67 SELECT t1.a FROM t1 GROUP BY t1.a
68 HAVING t1.a > ALL(SELECT t2.c FROM t2 GROUP BY t2.c
69 HAVING SUM(t1.a) < t2.c)
70 the set function can be evaluated for both outer and inner selects.
71 If we evaluate SUM(t1.a) for the outer query then we get the value of t1.a
72 multiplied by the cardinality of a group in table t1. In this case
73 in each correlated subquery SUM(t1.a) is used as a constant. But we also
74 can evaluate SUM(t1.a) for the inner query. In this case t1.a will be a
75 constant for each correlated subquery and summation is performed
76 for each group of table t2.
77 (Here it makes sense to remind that the query
78 SELECT c FROM t GROUP BY a HAVING SUM(1) < a
79 is quite legal in our SQL).
81 So depending on what query we assign the set function to we
82 can get different result sets.
84 The general rule to detect the query where a set function is to be
85 evaluated can be formulated as follows.
86 Consider a set function S(E) where E is an expression with occurrences
87 of column references C1, ..., CN. Resolve these column references against
88 subqueries that contain the set function S(E). Let Q be the innermost
89 subquery of those subqueries. (It should be noted here that S(E)
90 in no way can be evaluated in the subquery embedding the subquery Q,
91 otherwise S(E) would refer to at least one unbound column reference)
92 If S(E) is used in a construct of Q where set functions are allowed then
93 we evaluate S(E) in Q.
94 Otherwise we look for a innermost subquery containing S(E) of those where
95 usage of S(E) is allowed.
97 Let's demonstrate how this rule is applied to the following queries.
99 1. SELECT t1.a FROM t1 GROUP BY t1.a
100 HAVING t1.a > ALL(SELECT t2.b FROM t2 GROUP BY t2.b
101 HAVING t2.b > ALL(SELECT t3.c FROM t3 GROUP BY t3.c
102 HAVING SUM(t1.a+t2.b) < t3.c))
103 For this query the set function SUM(t1.a+t2.b) depends on t1.a and t2.b
104 with t1.a defined in the outermost query, and t2.b defined for its
105 subquery. The set function is in the HAVING clause of the subquery and can
106 be evaluated in this subquery.
108 2. SELECT t1.a FROM t1 GROUP BY t1.a
109 HAVING t1.a > ALL(SELECT t2.b FROM t2
110 WHERE t2.b > ALL (SELECT t3.c FROM t3 GROUP BY t3.c
111 HAVING SUM(t1.a+t2.b) < t3.c))
112 Here the set function SUM(t1.a+t2.b)is in the WHERE clause of the second
113 subquery - the most upper subquery where t1.a and t2.b are defined.
114 If we evaluate the function in this subquery we violate the context rules.
115 So we evaluate the function in the third subquery (over table t3) where it
116 is used under the HAVING clause.
118 3. SELECT t1.a FROM t1 GROUP BY t1.a
119 HAVING t1.a > ALL(SELECT t2.b FROM t2
120 WHERE t2.b > ALL (SELECT t3.c FROM t3
121 WHERE SUM(t1.a+t2.b) < t3.c))
122 In this query evaluation of SUM(t1.a+t2.b) is not legal neither in the second
123 nor in the third subqueries. So this query is invalid.
125 Mostly set functions cannot be nested. In the query
126 SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20
127 the expression SUM(b) is not acceptable, though it is under a HAVING clause.
128 Yet it is acceptable in the query:
129 SELECT t.1 FROM t1 GROUP BY t1.a HAVING SUM(t1.b) > 20.
131 An argument of a set function does not have to be a reference to a table
132 column as we saw it in examples above. This can be a more complex expression
133 SELECT t1.a FROM t1 GROUP BY t1.a HAVING SUM(t1.b+1) > 20.
134 The expression SUM(t1.b+1) has a very clear semantics in this context:
135 we sum up the values of t1.b+1 where t1.b varies for all values within a
136 group of rows that contain the same t1.a value.
138 A set function for an outer query yields a constant within a subquery. So
139 the semantics of the query
140 SELECT t1.a FROM t1 GROUP BY t1.a
141 HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
142 HAVING AVG(t2.c+SUM(t1.b)) > 20)
143 is still clear. For a group of the rows with the same t1.a values we
144 calculate the value of SUM(t1.b). This value 's' is substituted in the
146 SELECT t2.c FROM t2 GROUP BY t2.c HAVING AVG(t2.c+s)
147 than returns some result set.
149 By the same reason the following query with a subquery
150 SELECT t1.a FROM t1 GROUP BY t1.a
151 HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c
152 HAVING AVG(SUM(t1.b)) > 20)
157 Three methods were added to the class to check the constraints specified
158 in the previous section. These methods utilize several new members.
160 The field 'nest_level' contains the number of the level for the subquery
161 containing the set function. The main SELECT is of level 0, its subqueries
162 are of levels 1, the subqueries of the latter are of level 2 and so on.
164 The field 'aggr_level' is to contain the nest level of the subquery
165 where the set function is aggregated.
167 The field 'max_arg_level' is for the maximun of the nest levels of the
168 unbound column references occurred in the set function. A column reference
169 is unbound within a set function if it is not bound by any subquery
170 used as a subexpression in this function. A column reference is bound by
171 a subquery if it is a reference to the column by which the aggregation
172 of some set function that is used in the subquery is calculated.
173 For the set function used in the query
174 SELECT t1.a FROM t1 GROUP BY t1.a
175 HAVING t1.a > ALL(SELECT t2.b FROM t2 GROUP BY t2.b
176 HAVING t2.b > ALL(SELECT t3.c FROM t3 GROUP BY t3.c
177 HAVING SUM(t1.a+t2.b) < t3.c))
178 the value of max_arg_level is equal to 1 since t1.a is bound in the main
179 query, and t2.b is bound by the first subquery whose nest level is 1.
180 Obviously a set function cannot be aggregated in the subquery whose
181 nest level is less than max_arg_level. (Yet it can be aggregated in the
182 subqueries whose nest level is greater than max_arg_level.)
184 SELECT t.a FROM t1 HAVING AVG(t1.a+(SELECT MIN(t2.c) FROM t2))
185 the value of the max_arg_level for the AVG set function is 0 since
186 the reference t2.c is bound in the subquery.
188 The field 'max_sum_func_level' is to contain the maximum of the
189 nest levels of the set functions that are used as subexpressions of
190 the arguments of the given set function, but not aggregated in any
191 subquery within this set function. A nested set function s1 can be
192 used within set function s0 only if s1.max_sum_func_level <
193 s0.max_sum_func_level. Set function s1 is considered as nested
194 for set function s0 if s1 is not calculated in any subquery
197 A set function that is used as a subexpression in an argument of another
198 set function refers to the latter via the field 'in_sum_func'.
200 The condition imposed on the usage of set functions are checked when
201 we traverse query subexpressions with the help of the recursive method
202 fix_fields. When we apply this method to an object of the class
203 Item_sum, first, on the descent, we call the method init_sum_func_check
204 that initialize members used at checking. Then, on the ascent, we
205 call the method check_sum_func that validates the set function usage
206 and reports an error if it is illegal.
207 The method register_sum_func serves to link the items for the set functions
208 that are aggregated in the embedding (sub)queries. Circular chains of such
209 functions are attached to the corresponding st_select_lex structures
210 through the field inner_sum_func_list.
212 Exploiting the fact that the members mentioned above are used in one
213 recursive function we could have allocated them on the thread stack.
214 Yet we don't do it now.
216 We assume that the nesting level of subquries does not exceed 127.
217 TODO: to catch queries where the limit is exceeded to make the
224 class Item_sum
:public Item_result_field
228 { COUNT_FUNC
, COUNT_DISTINCT_FUNC
, SUM_FUNC
, SUM_DISTINCT_FUNC
, AVG_FUNC
,
229 AVG_DISTINCT_FUNC
, MIN_FUNC
, MAX_FUNC
, STD_FUNC
,
230 VARIANCE_FUNC
, SUM_BIT_FUNC
, UDF_SUM_FUNC
, GROUP_CONCAT_FUNC
233 Item
**ref_by
; /* pointer to a ref to the object used to register it */
234 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
;
252 Item
**args
, *tmp_args
[2];
254 Copy of the arguments list to hold the original set of arguments.
255 Used in EXPLAIN EXTENDED instead of the current argument list because
256 the current argument list can be altered by usage of temporary tables.
258 Item
**orig_args
, *tmp_orig_args
[2];
259 table_map used_tables_cache
;
264 void mark_as_sum_func();
265 Item_sum() :quick_group(1), arg_count(0), forced_const(FALSE
)
269 Item_sum(Item
*a
) :quick_group(1), arg_count(1), args(tmp_args
),
270 orig_args(tmp_orig_args
), forced_const(FALSE
)
275 Item_sum( Item
*a
, Item
*b
) :quick_group(1), arg_count(2), args(tmp_args
),
276 orig_args(tmp_orig_args
), forced_const(FALSE
)
278 args
[0]=a
; args
[1]=b
;
281 Item_sum(List
<Item
> &list
);
282 //Copy constructor, need to perform subselects with temporary tables
283 Item_sum(THD
*thd
, Item_sum
*item
);
284 enum Type
type() const { return SUM_FUNC_ITEM
; }
285 virtual enum Sumfunctype
sum_func () const=0;
288 This method is similar to add(), but it is called when the current
289 aggregation group changes. Thus it performs a combination of
292 inline bool reset() { clear(); return add(); };
295 Prepare this item for evaluation of an aggregate value. This is
296 called by reset() when a group changes, or, for correlated
297 subqueries, between subquery executions. E.g. for COUNT(), this
298 method should set count= 0;
300 virtual void clear()= 0;
303 This method is called for the next row in the same group. Its
304 purpose is to aggregate the new value to the previous values in
305 the group (i.e. since clear() was called last time). For example,
306 for COUNT(), do count++.
308 virtual bool add()=0;
311 Called when new group is started and results are being saved in
312 a temporary table. Similar to reset(), but must also store value in
313 result_field. Like reset() it is supposed to reset start value to
315 This set of methods (reult_field(), reset_field, update_field()) of
316 Item_sum is used only if quick_group is not null. Otherwise
317 copy_or_same() is used to obtain a copy of this item.
319 virtual void reset_field()=0;
321 Called for each new value in the group, when temporary table is in use.
322 Similar to add(), but uses temporary table field to obtain current value,
323 Updated value is then saved in the field.
325 virtual void update_field()=0;
326 virtual bool keep_field_type(void) const { return 0; }
327 virtual void fix_length_and_dec() { maybe_null
=1; null_value
=1; }
328 virtual Item
*result_item(Field
*field
)
329 { return new Item_field(field
); }
330 table_map
used_tables() const { return used_tables_cache
; }
331 void update_used_tables ();
337 bool is_null() { return null_value
; }
340 used_tables_cache
= 0;
343 virtual bool const_item() const { return forced_const
; }
344 virtual bool const_during_execution() const { return false; }
345 virtual void print(String
*str
, enum_query_type query_type
);
346 void fix_num_length_and_dec();
349 This function is called by the execution engine to assign 'NO ROWS
350 FOUND' value to an aggregate item, when the underlying result set
351 has no rows. Such value, in a general case, may be different from
352 the default value of the item after 'clear()': e.g. a numeric item
353 may be initialized to 0 by clear() and to NULL by
356 void no_rows_in_result() { clear(); }
358 virtual bool setup(THD
* thd
) {return 0;}
359 virtual void make_unique() {}
360 Item
*get_tmp_table_item(THD
*thd
);
361 virtual Field
*create_tmp_field(bool group
, TABLE
*table
,
362 uint convert_blob_length
);
363 bool walk(Item_processor processor
, bool walk_subquery
, uchar
*argument
);
364 bool init_sum_func_check(THD
*thd
);
365 bool check_sum_func(THD
*thd
, Item
**ref
);
366 bool register_sum_func(THD
*thd
, Item
**ref
);
367 st_select_lex
*depended_from()
368 { return (nest_level
== aggr_level
? 0 : aggr_sel
); }
370 Item
*get_arg(int i
) { return args
[i
]; }
371 Item
*set_arg(int i
, THD
*thd
, Item
*new_val
);
372 uint
get_arg_count() { return arg_count
; }
376 class Item_sum_num
:public Item_sum
380 val_xxx() functions may be called several times during the execution of a
381 query. Derived classes that require extensive calculation in val_xxx()
382 maintain cache of aggregate value. This variable governs the validity of
387 Item_sum_num() :Item_sum(),is_evaluated(FALSE
) {}
388 Item_sum_num(Item
*item_par
)
389 :Item_sum(item_par
), is_evaluated(FALSE
) {}
390 Item_sum_num(Item
*a
, Item
* b
) :Item_sum(a
,b
),is_evaluated(FALSE
) {}
391 Item_sum_num(List
<Item
> &list
)
392 :Item_sum(list
), is_evaluated(FALSE
) {}
393 Item_sum_num(THD
*thd
, Item_sum_num
*item
)
394 :Item_sum(thd
, item
),is_evaluated(item
->is_evaluated
) {}
395 bool fix_fields(THD
*, Item
**);
398 DBUG_ASSERT(fixed
== 1);
399 return (longlong
) rint(val_real()); /* Real as default */
401 String
*val_str(String
*str
);
402 my_decimal
*val_decimal(my_decimal
*);
407 class Item_sum_int
:public Item_sum_num
410 Item_sum_int(Item
*item_par
) :Item_sum_num(item_par
) {}
411 Item_sum_int(List
<Item
> &list
) :Item_sum_num(list
) {}
412 Item_sum_int(THD
*thd
, Item_sum_int
*item
) :Item_sum_num(thd
, item
) {}
413 double val_real() { DBUG_ASSERT(fixed
== 1); return (double) val_int(); }
414 String
*val_str(String
*str
);
415 my_decimal
*val_decimal(my_decimal
*);
416 enum Item_result
result_type () const { return INT_RESULT
; }
417 void fix_length_and_dec()
418 { decimals
=0; max_length
=21; maybe_null
=null_value
=0; }
422 class Item_sum_sum
:public Item_sum_num
425 Item_result hybrid_type
;
427 my_decimal dec_buffs
[2];
429 void fix_length_and_dec();
432 Item_sum_sum(Item
*item_par
) :Item_sum_num(item_par
) {}
433 Item_sum_sum(THD
*thd
, Item_sum_sum
*item
);
434 enum Sumfunctype
sum_func () const {return SUM_FUNC
;}
439 String
*val_str(String
*str
);
440 my_decimal
*val_decimal(my_decimal
*);
441 enum Item_result
result_type () const { return hybrid_type
; }
444 void no_rows_in_result() {}
445 const char *func_name() const { return "sum("; }
446 Item
*copy_or_same(THD
* thd
);
451 /* Common class for SUM(DISTINCT), AVG(DISTINCT) */
455 class Item_sum_distinct
:public Item_sum_num
458 /* storage for the summation result */
461 /* storage for unique elements */
464 enum enum_field_types table_field_type
;
465 uint tree_key_length
;
467 Item_sum_distinct(THD
*thd
, Item_sum_distinct
*item
);
469 Item_sum_distinct(Item
*item_par
);
470 ~Item_sum_distinct();
472 bool setup(THD
*thd
);
477 my_decimal
*val_decimal(my_decimal
*);
479 String
*val_str(String
*str
);
481 /* XXX: does it need make_unique? */
483 enum Sumfunctype
sum_func () const { return SUM_DISTINCT_FUNC
; }
484 void reset_field() {} // not used
485 void update_field() {} // not used
486 virtual void no_rows_in_result() {}
487 void fix_length_and_dec();
488 enum Item_result
result_type () const { return val
.traits
->type(); }
489 virtual void calculate_val_and_count();
490 virtual bool unique_walk_function(void *elem
);
495 Item_sum_sum_distinct - implementation of SUM(DISTINCT expr).
496 See also: MySQL manual, chapter 'Adding New Functions To MySQL'
497 and comments in item_sum.cc.
500 class Item_sum_sum_distinct
:public Item_sum_distinct
503 Item_sum_sum_distinct(THD
*thd
, Item_sum_sum_distinct
*item
)
504 :Item_sum_distinct(thd
, item
) {}
506 Item_sum_sum_distinct(Item
*item_arg
) :Item_sum_distinct(item_arg
) {}
508 enum Sumfunctype
sum_func () const { return SUM_DISTINCT_FUNC
; }
509 const char *func_name() const { return "sum(distinct "; }
510 Item
*copy_or_same(THD
* thd
) { return new Item_sum_sum_distinct(thd
, this); }
514 /* Item_sum_avg_distinct - SELECT AVG(DISTINCT expr) FROM ... */
516 class Item_sum_avg_distinct
: public Item_sum_distinct
519 Item_sum_avg_distinct(THD
*thd
, Item_sum_avg_distinct
*original
)
520 :Item_sum_distinct(thd
, original
) {}
523 Item_sum_avg_distinct(Item
*item_arg
) : Item_sum_distinct(item_arg
) {}
525 void fix_length_and_dec();
526 virtual void calculate_val_and_count();
527 enum Sumfunctype
sum_func () const { return AVG_DISTINCT_FUNC
; }
528 const char *func_name() const { return "avg(distinct "; }
529 Item
*copy_or_same(THD
* thd
) { return new Item_sum_avg_distinct(thd
, this); }
533 class Item_sum_count
:public Item_sum_int
538 Item_sum_count(Item
*item_par
)
539 :Item_sum_int(item_par
),count(0)
541 Item_sum_count(THD
*thd
, Item_sum_count
*item
)
542 :Item_sum_int(thd
, item
), count(item
->count
)
544 enum Sumfunctype
sum_func () const { return COUNT_FUNC
; }
546 void no_rows_in_result() { count
=0; }
548 void make_const(longlong count_arg
)
551 Item_sum::make_const();
557 const char *func_name() const { return "count("; }
558 Item
*copy_or_same(THD
* thd
);
562 class TMP_TABLE_PARAM
;
564 class Item_sum_count_distinct
:public Item_sum_int
567 uint32
*field_lengths
;
568 TMP_TABLE_PARAM
*tmp_table_param
;
569 bool force_copy_fields
;
571 If there are no blobs, we can use a tree, which
572 is faster than heap table. In that case, we still use the table
573 to help get things set up, but we insert nothing in it
577 Storage for the value of count between calls to val_int() so val_int()
578 will not recalculate on each call. Validitiy of the value is stored in
583 Following is 0 normal object and pointer to original one for copy
584 (to correctly free resources)
586 Item_sum_count_distinct
*original
;
587 uint tree_key_length
;
590 bool always_null
; // Set to 1 if the result is always NULL
593 friend int composite_key_cmp(void* arg
, uchar
* key1
, uchar
* key2
);
594 friend int simple_str_key_cmp(void* arg
, uchar
* key1
, uchar
* key2
);
597 Item_sum_count_distinct(List
<Item
> &list
)
598 :Item_sum_int(list
), table(0), field_lengths(0), tmp_table_param(0),
599 force_copy_fields(0), tree(0), count(0),
600 original(0), always_null(FALSE
)
602 Item_sum_count_distinct(THD
*thd
, Item_sum_count_distinct
*item
)
603 :Item_sum_int(thd
, item
), table(item
->table
),
604 field_lengths(item
->field_lengths
),
605 tmp_table_param(item
->tmp_table_param
),
606 force_copy_fields(0), tree(item
->tree
), count(item
->count
),
607 original(item
), tree_key_length(item
->tree_key_length
),
608 always_null(item
->always_null
)
610 ~Item_sum_count_distinct();
614 enum Sumfunctype
sum_func () const { return COUNT_DISTINCT_FUNC
; }
618 void reset_field() { return ;} // Never called
619 void update_field() { return ; } // Never called
620 const char *func_name() const { return "count(distinct "; }
621 bool setup(THD
*thd
);
623 Item
*copy_or_same(THD
* thd
);
624 void no_rows_in_result() {}
628 /* Item to get the value of a stored sum function */
632 class Item_avg_field
:public Item_result_field
636 Item_result hybrid_type
;
637 uint f_precision
, f_scale
, dec_bin_size
;
639 Item_avg_field(Item_result res_type
, Item_sum_avg
*item
);
640 enum Type
type() const { return FIELD_AVG_ITEM
; }
643 my_decimal
*val_decimal(my_decimal
*);
644 bool is_null() { update_null_value(); return null_value
; }
645 String
*val_str(String
*);
646 enum_field_types
field_type() const
648 return hybrid_type
== DECIMAL_RESULT
?
649 MYSQL_TYPE_NEWDECIMAL
: MYSQL_TYPE_DOUBLE
;
651 void fix_length_and_dec() {}
652 enum Item_result
result_type () const { return hybrid_type
; }
653 const char *func_name() const { DBUG_ASSERT(0); return "avg_field"; }
657 class Item_sum_avg
:public Item_sum_sum
662 uint f_precision
, f_scale
, dec_bin_size
;
664 Item_sum_avg(Item
*item_par
) :Item_sum_sum(item_par
), count(0) {}
665 Item_sum_avg(THD
*thd
, Item_sum_avg
*item
)
666 :Item_sum_sum(thd
, item
), count(item
->count
),
667 prec_increment(item
->prec_increment
) {}
669 void fix_length_and_dec();
670 enum Sumfunctype
sum_func () const {return AVG_FUNC
;}
674 // In SPs we might force the "wrong" type with select into a declare variable
675 longlong
val_int() { return (longlong
) rint(val_real()); }
676 my_decimal
*val_decimal(my_decimal
*);
677 String
*val_str(String
*str
);
680 Item
*result_item(Field
*field
)
681 { return new Item_avg_field(hybrid_type
, this); }
682 void no_rows_in_result() {}
683 const char *func_name() const { return "avg("; }
684 Item
*copy_or_same(THD
* thd
);
685 Field
*create_tmp_field(bool group
, TABLE
*table
, uint convert_blob_length
);
689 Item_sum_sum::cleanup();
693 class Item_sum_variance
;
695 class Item_variance_field
:public Item_result_field
699 Item_result hybrid_type
;
700 uint f_precision0
, f_scale0
;
701 uint f_precision1
, f_scale1
;
702 uint dec_bin_size0
, dec_bin_size1
;
705 Item_variance_field(Item_sum_variance
*item
);
706 enum Type
type() const {return FIELD_VARIANCE_ITEM
; }
709 { /* can't be fix_fields()ed */ return (longlong
) rint(val_real()); }
710 String
*val_str(String
*str
)
711 { return val_string_from_real(str
); }
712 my_decimal
*val_decimal(my_decimal
*dec_buf
)
713 { return val_decimal_from_real(dec_buf
); }
714 bool is_null() { update_null_value(); return null_value
; }
715 enum_field_types
field_type() const
717 return hybrid_type
== DECIMAL_RESULT
?
718 MYSQL_TYPE_NEWDECIMAL
: MYSQL_TYPE_DOUBLE
;
720 void fix_length_and_dec() {}
721 enum Item_result
result_type () const { return hybrid_type
; }
722 const char *func_name() const { DBUG_ASSERT(0); return "variance_field"; }
729 = sum (ai - avg(a))^2 / count(a) )
730 = sum (ai^2 - 2*ai*avg(a) + avg(a)^2) / count(a)
731 = (sum(ai^2) - sum(2*ai*avg(a)) + sum(avg(a)^2))/count(a) =
732 = (sum(ai^2) - 2*avg(a)*sum(a) + count(a)*avg(a)^2)/count(a) =
733 = (sum(ai^2) - 2*sum(a)*sum(a)/count(a) + count(a)*sum(a)^2/count(a)^2 )/count(a) =
734 = (sum(ai^2) - 2*sum(a)^2/count(a) + sum(a)^2/count(a) )/count(a) =
735 = (sum(ai^2) - sum(a)^2/count(a))/count(a)
737 But, this falls prey to catastrophic cancellation. Instead, use the recurrence formulas
739 M_{1} = x_{1}, ~ M_{k} = M_{k-1} + (x_{k} - M_{k-1}) / k newline
740 S_{1} = 0, ~ S_{k} = S_{k-1} + (x_{k} - M_{k-1}) times (x_{k} - M_{k}) newline
741 for 2 <= k <= n newline
742 ital variance = S_{n} / (n-1)
746 class Item_sum_variance
: public Item_sum_num
748 void fix_length_and_dec();
751 Item_result hybrid_type
;
753 double recurrence_m
, recurrence_s
; /* Used in recurrence relation. */
755 uint f_precision0
, f_scale0
;
756 uint f_precision1
, f_scale1
;
757 uint dec_bin_size0
, dec_bin_size1
;
761 Item_sum_variance(Item
*item_par
, uint sample_arg
) :Item_sum_num(item_par
),
762 hybrid_type(REAL_RESULT
), count(0), sample(sample_arg
)
764 Item_sum_variance(THD
*thd
, Item_sum_variance
*item
);
765 enum Sumfunctype
sum_func () const { return VARIANCE_FUNC
; }
769 my_decimal
*val_decimal(my_decimal
*);
772 Item
*result_item(Field
*field
)
773 { return new Item_variance_field(this); }
774 void no_rows_in_result() {}
775 const char *func_name() const
776 { return sample
? "var_samp(" : "variance("; }
777 Item
*copy_or_same(THD
* thd
);
778 Field
*create_tmp_field(bool group
, TABLE
*table
, uint convert_blob_length
);
779 enum Item_result
result_type () const { return REAL_RESULT
; }
783 Item_sum_num::cleanup();
789 class Item_std_field
:public Item_variance_field
792 Item_std_field(Item_sum_std
*item
);
793 enum Type
type() const { return FIELD_STD_ITEM
; }
795 my_decimal
*val_decimal(my_decimal
*);
796 enum Item_result
result_type () const { return REAL_RESULT
; }
797 enum_field_types
field_type() const { return MYSQL_TYPE_DOUBLE
;}
798 const char *func_name() const { DBUG_ASSERT(0); return "std_field"; }
802 standard_deviation(a) = sqrt(variance(a))
805 class Item_sum_std
:public Item_sum_variance
808 Item_sum_std(Item
*item_par
, uint sample_arg
)
809 :Item_sum_variance(item_par
, sample_arg
) {}
810 Item_sum_std(THD
*thd
, Item_sum_std
*item
)
811 :Item_sum_variance(thd
, item
)
813 enum Sumfunctype
sum_func () const { return STD_FUNC
; }
815 Item
*result_item(Field
*field
)
816 { return new Item_std_field(this); }
817 const char *func_name() const { return "std("; }
818 Item
*copy_or_same(THD
* thd
);
819 enum Item_result
result_type () const { return REAL_RESULT
; }
820 enum_field_types
field_type() const { return MYSQL_TYPE_DOUBLE
;}
823 // This class is a string or number function depending on num_func
824 class Arg_comparator
;
826 class Item_sum_hybrid
:public Item_sum
829 Item_cache
*value
, *arg_cache
;
831 Item_result hybrid_type
;
832 enum_field_types hybrid_field_type
;
834 bool was_values
; // Set if we have found at least one row (for max/min only)
837 Item_sum_hybrid(Item
*item_par
,int sign
)
838 :Item_sum(item_par
), value(0), arg_cache(0), cmp(0),
839 hybrid_type(INT_RESULT
), hybrid_field_type(MYSQL_TYPE_LONGLONG
),
840 cmp_sign(sign
), was_values(TRUE
)
841 { collation
.set(&my_charset_bin
); }
842 Item_sum_hybrid(THD
*thd
, Item_sum_hybrid
*item
)
843 :Item_sum(thd
, item
), value(item
->value
), arg_cache(0),
844 hybrid_type(item
->hybrid_type
), hybrid_field_type(item
->hybrid_field_type
),
845 cmp_sign(item
->cmp_sign
), was_values(item
->was_values
)
847 bool fix_fields(THD
*, Item
**);
848 void setup_hybrid(Item
*item
, Item
*value_arg
);
852 my_decimal
*val_decimal(my_decimal
*);
854 String
*val_str(String
*);
855 bool keep_field_type(void) const { return 1; }
856 enum Item_result
result_type () const { return hybrid_type
; }
857 enum enum_field_types
field_type() const { return hybrid_field_type
; }
859 void min_max_update_str_field();
860 void min_max_update_real_field();
861 void min_max_update_int_field();
862 void min_max_update_decimal_field();
864 bool any_value() { return was_values
; }
865 void no_rows_in_result();
866 Field
*create_tmp_field(bool group
, TABLE
*table
,
867 uint convert_blob_length
);
871 class Item_sum_min
:public Item_sum_hybrid
874 Item_sum_min(Item
*item_par
) :Item_sum_hybrid(item_par
,1) {}
875 Item_sum_min(THD
*thd
, Item_sum_min
*item
) :Item_sum_hybrid(thd
, item
) {}
876 enum Sumfunctype
sum_func () const {return MIN_FUNC
;}
879 const char *func_name() const { return "min("; }
880 Item
*copy_or_same(THD
* thd
);
884 class Item_sum_max
:public Item_sum_hybrid
887 Item_sum_max(Item
*item_par
) :Item_sum_hybrid(item_par
,-1) {}
888 Item_sum_max(THD
*thd
, Item_sum_max
*item
) :Item_sum_hybrid(thd
, item
) {}
889 enum Sumfunctype
sum_func () const {return MAX_FUNC
;}
892 const char *func_name() const { return "max("; }
893 Item
*copy_or_same(THD
* thd
);
897 class Item_sum_bit
:public Item_sum_int
900 ulonglong reset_bits
,bits
;
903 Item_sum_bit(Item
*item_par
,ulonglong reset_arg
)
904 :Item_sum_int(item_par
),reset_bits(reset_arg
),bits(reset_arg
) {}
905 Item_sum_bit(THD
*thd
, Item_sum_bit
*item
):
906 Item_sum_int(thd
, item
), reset_bits(item
->reset_bits
), bits(item
->bits
) {}
907 enum Sumfunctype
sum_func () const {return SUM_BIT_FUNC
;}
912 void fix_length_and_dec()
913 { decimals
= 0; max_length
=21; unsigned_flag
= 1; maybe_null
= null_value
= 0; }
917 Item_sum_int::cleanup();
922 class Item_sum_or
:public Item_sum_bit
925 Item_sum_or(Item
*item_par
) :Item_sum_bit(item_par
,LL(0)) {}
926 Item_sum_or(THD
*thd
, Item_sum_or
*item
) :Item_sum_bit(thd
, item
) {}
928 const char *func_name() const { return "bit_or("; }
929 Item
*copy_or_same(THD
* thd
);
933 class Item_sum_and
:public Item_sum_bit
936 Item_sum_and(Item
*item_par
) :Item_sum_bit(item_par
, ULONGLONG_MAX
) {}
937 Item_sum_and(THD
*thd
, Item_sum_and
*item
) :Item_sum_bit(thd
, item
) {}
939 const char *func_name() const { return "bit_and("; }
940 Item
*copy_or_same(THD
* thd
);
943 class Item_sum_xor
:public Item_sum_bit
946 Item_sum_xor(Item
*item_par
) :Item_sum_bit(item_par
,LL(0)) {}
947 Item_sum_xor(THD
*thd
, Item_sum_xor
*item
) :Item_sum_bit(thd
, item
) {}
949 const char *func_name() const { return "bit_xor("; }
950 Item
*copy_or_same(THD
* thd
);
955 User defined aggregates
960 class Item_udf_sum
: public Item_sum
966 Item_udf_sum(udf_func
*udf_arg
)
967 :Item_sum(), udf(udf_arg
)
969 Item_udf_sum(udf_func
*udf_arg
, List
<Item
> &list
)
970 :Item_sum(list
), udf(udf_arg
)
972 Item_udf_sum(THD
*thd
, Item_udf_sum
*item
)
973 :Item_sum(thd
, item
), udf(item
->udf
)
974 { udf
.not_original
= TRUE
; }
975 const char *func_name() const { return udf
.name(); }
976 bool fix_fields(THD
*thd
, Item
**ref
)
978 DBUG_ASSERT(fixed
== 0);
980 if (init_sum_func_check(thd
))
984 if (udf
.fix_fields(thd
, this, this->arg_count
, this->args
))
987 memcpy (orig_args
, args
, sizeof (Item
*) * arg_count
);
988 return check_sum_func(thd
, ref
);
990 enum Sumfunctype
sum_func () const { return UDF_SUM_FUNC
; }
991 virtual bool have_field_update(void) const { return 0; }
995 void reset_field() {};
996 void update_field() {};
998 virtual void print(String
*str
, enum_query_type query_type
);
1002 class Item_sum_udf_float
:public Item_udf_sum
1005 Item_sum_udf_float(udf_func
*udf_arg
)
1006 :Item_udf_sum(udf_arg
) {}
1007 Item_sum_udf_float(udf_func
*udf_arg
, List
<Item
> &list
)
1008 :Item_udf_sum(udf_arg
, list
) {}
1009 Item_sum_udf_float(THD
*thd
, Item_sum_udf_float
*item
)
1010 :Item_udf_sum(thd
, item
) {}
1013 DBUG_ASSERT(fixed
== 1);
1014 return (longlong
) rint(Item_sum_udf_float::val_real());
1017 String
*val_str(String
*str
);
1018 my_decimal
*val_decimal(my_decimal
*);
1019 void fix_length_and_dec() { fix_num_length_and_dec(); }
1020 Item
*copy_or_same(THD
* thd
);
1024 class Item_sum_udf_int
:public Item_udf_sum
1027 Item_sum_udf_int(udf_func
*udf_arg
)
1028 :Item_udf_sum(udf_arg
) {}
1029 Item_sum_udf_int(udf_func
*udf_arg
, List
<Item
> &list
)
1030 :Item_udf_sum(udf_arg
, list
) {}
1031 Item_sum_udf_int(THD
*thd
, Item_sum_udf_int
*item
)
1032 :Item_udf_sum(thd
, item
) {}
1035 { DBUG_ASSERT(fixed
== 1); return (double) Item_sum_udf_int::val_int(); }
1036 String
*val_str(String
*str
);
1037 my_decimal
*val_decimal(my_decimal
*);
1038 enum Item_result
result_type () const { return INT_RESULT
; }
1039 void fix_length_and_dec() { decimals
=0; max_length
=21; }
1040 Item
*copy_or_same(THD
* thd
);
1044 class Item_sum_udf_str
:public Item_udf_sum
1047 Item_sum_udf_str(udf_func
*udf_arg
)
1048 :Item_udf_sum(udf_arg
) {}
1049 Item_sum_udf_str(udf_func
*udf_arg
, List
<Item
> &list
)
1050 :Item_udf_sum(udf_arg
,list
) {}
1051 Item_sum_udf_str(THD
*thd
, Item_sum_udf_str
*item
)
1052 :Item_udf_sum(thd
, item
) {}
1053 String
*val_str(String
*);
1059 res
=val_str(&str_value
);
1060 return res
? my_strntod(res
->charset(),(char*) res
->ptr(),res
->length(),
1061 &end_not_used
, &err_not_used
) : 0.0;
1070 if (!(res
= val_str(&str_value
)))
1071 return 0; /* Null value */
1073 end
= (char*) res
->ptr()+res
->length();
1074 return cs
->cset
->strtoll10(cs
, res
->ptr(), &end
, &err_not_used
);
1076 my_decimal
*val_decimal(my_decimal
*dec
);
1077 enum Item_result
result_type () const { return STRING_RESULT
; }
1078 void fix_length_and_dec();
1079 Item
*copy_or_same(THD
* thd
);
1083 class Item_sum_udf_decimal
:public Item_udf_sum
1086 Item_sum_udf_decimal(udf_func
*udf_arg
)
1087 :Item_udf_sum(udf_arg
) {}
1088 Item_sum_udf_decimal(udf_func
*udf_arg
, List
<Item
> &list
)
1089 :Item_udf_sum(udf_arg
, list
) {}
1090 Item_sum_udf_decimal(THD
*thd
, Item_sum_udf_decimal
*item
)
1091 :Item_udf_sum(thd
, item
) {}
1092 String
*val_str(String
*);
1095 my_decimal
*val_decimal(my_decimal
*);
1096 enum Item_result
result_type () const { return DECIMAL_RESULT
; }
1097 void fix_length_and_dec() { fix_num_length_and_dec(); }
1098 Item
*copy_or_same(THD
* thd
);
1101 #else /* Dummy functions to get sql_yacc.cc compiled */
1103 class Item_sum_udf_float
:public Item_sum_num
1106 Item_sum_udf_float(udf_func
*udf_arg
)
1108 Item_sum_udf_float(udf_func
*udf_arg
, List
<Item
> &list
) :Item_sum_num() {}
1109 Item_sum_udf_float(THD
*thd
, Item_sum_udf_float
*item
)
1110 :Item_sum_num(thd
, item
) {}
1111 enum Sumfunctype
sum_func () const { return UDF_SUM_FUNC
; }
1112 double val_real() { DBUG_ASSERT(fixed
== 1); return 0.0; }
1114 bool add() { return 0; }
1115 void update_field() {}
1119 class Item_sum_udf_int
:public Item_sum_num
1122 Item_sum_udf_int(udf_func
*udf_arg
)
1124 Item_sum_udf_int(udf_func
*udf_arg
, List
<Item
> &list
) :Item_sum_num() {}
1125 Item_sum_udf_int(THD
*thd
, Item_sum_udf_int
*item
)
1126 :Item_sum_num(thd
, item
) {}
1127 enum Sumfunctype
sum_func () const { return UDF_SUM_FUNC
; }
1128 longlong
val_int() { DBUG_ASSERT(fixed
== 1); return 0; }
1129 double val_real() { DBUG_ASSERT(fixed
== 1); return 0; }
1131 bool add() { return 0; }
1132 void update_field() {}
1136 class Item_sum_udf_decimal
:public Item_sum_num
1139 Item_sum_udf_decimal(udf_func
*udf_arg
)
1141 Item_sum_udf_decimal(udf_func
*udf_arg
, List
<Item
> &list
)
1143 Item_sum_udf_decimal(THD
*thd
, Item_sum_udf_float
*item
)
1144 :Item_sum_num(thd
, item
) {}
1145 enum Sumfunctype
sum_func () const { return UDF_SUM_FUNC
; }
1146 double val_real() { DBUG_ASSERT(fixed
== 1); return 0.0; }
1147 my_decimal
*val_decimal(my_decimal
*) { DBUG_ASSERT(fixed
== 1); return 0; }
1149 bool add() { return 0; }
1150 void update_field() {}
1154 class Item_sum_udf_str
:public Item_sum_num
1157 Item_sum_udf_str(udf_func
*udf_arg
)
1159 Item_sum_udf_str(udf_func
*udf_arg
, List
<Item
> &list
)
1161 Item_sum_udf_str(THD
*thd
, Item_sum_udf_str
*item
)
1162 :Item_sum_num(thd
, item
) {}
1163 String
*val_str(String
*)
1164 { DBUG_ASSERT(fixed
== 1); null_value
=1; return 0; }
1165 double val_real() { DBUG_ASSERT(fixed
== 1); null_value
=1; return 0.0; }
1166 longlong
val_int() { DBUG_ASSERT(fixed
== 1); null_value
=1; return 0; }
1167 enum Item_result
result_type () const { return STRING_RESULT
; }
1168 void fix_length_and_dec() { maybe_null
=1; max_length
=0; }
1169 enum Sumfunctype
sum_func () const { return UDF_SUM_FUNC
; }
1171 bool add() { return 0; }
1172 void update_field() {}
1175 #endif /* HAVE_DLOPEN */
1179 class Item_func_group_concat
: public Item_sum
1181 TMP_TABLE_PARAM
*tmp_table_param
;
1182 MYSQL_ERROR
*warning
;
1189 If DISTINCT is used with this GROUP_CONCAT, this member is used to filter
1191 @see Item_func_group_concat::setup
1192 @see Item_func_group_concat::add
1193 @see Item_func_group_concat::clear
1195 Unique
*unique_filter
;
1198 Name_resolution_context
*context
;
1199 /** The number of ORDER BY items. */
1200 uint arg_count_order
;
1201 /** The number of selected items, aka the expr list. */
1202 uint arg_count_field
;
1203 uint count_cut_values
;
1205 bool warning_for_row
;
1207 bool force_copy_fields
;
1210 Following is 0 normal object and pointer to original one for copy
1211 (to correctly free resources)
1213 Item_func_group_concat
*original
;
1215 friend int group_concat_key_cmp_with_distinct(void* arg
, const void* key1
,
1217 friend int group_concat_key_cmp_with_order(void* arg
, const void* key1
,
1219 friend int dump_leaf_key(uchar
* key
,
1220 element_count count
__attribute__((unused
)),
1221 Item_func_group_concat
*group_concat_item
);
1224 Item_func_group_concat(Name_resolution_context
*context_arg
,
1225 bool is_distinct
, List
<Item
> *is_select
,
1226 const SQL_I_List
<ORDER
> &is_order
, String
*is_separator
);
1228 Item_func_group_concat(THD
*thd
, Item_func_group_concat
*item
);
1229 ~Item_func_group_concat();
1232 enum Sumfunctype
sum_func () const {return GROUP_CONCAT_FUNC
;}
1233 const char *func_name() const { return "group_concat"; }
1234 virtual Item_result
result_type () const { return STRING_RESULT
; }
1235 enum_field_types
field_type() const
1237 if (max_length
/collation
.collation
->mbmaxlen
> CONVERT_IF_BIGGER_TO_BLOB
)
1238 return MYSQL_TYPE_BLOB
;
1240 return MYSQL_TYPE_VARCHAR
;
1244 void reset_field() { DBUG_ASSERT(0); } // not used
1245 void update_field() { DBUG_ASSERT(0); } // not used
1246 bool fix_fields(THD
*,Item
**);
1247 bool setup(THD
*thd
);
1251 String
*res
; res
=val_str(&str_value
);
1252 return res
? my_atof(res
->c_ptr()) : 0.0;
1259 if (!(res
= val_str(&str_value
)))
1260 return (longlong
) 0;
1261 end_ptr
= (char*) res
->ptr()+ res
->length();
1262 return my_strtoll10(res
->ptr(), &end_ptr
, &error
);
1264 my_decimal
*val_decimal(my_decimal
*decimal_value
)
1266 return val_decimal_from_string(decimal_value
);
1268 String
* val_str(String
* str
);
1269 Item
*copy_or_same(THD
* thd
);
1270 void no_rows_in_result() {}
1271 virtual void print(String
*str
, enum_query_type query_type
);
1272 virtual bool change_context_processor(uchar
*cntx
)
1273 { context
= (Name_resolution_context
*)cntx
; return FALSE
; }