1
/* Copyright (C) 2000-2003 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 */
21
Sum functions (COUNT, MIN...)
24
#ifdef USE_PRAGMA_IMPLEMENTATION
25
#pragma implementation // gcc: Class implementation
28
#include "mysql_priv.h"
29
#include "sql_select.h"
32
Prepare an aggregate function item for checking context conditions.
34
The function initializes the members of the Item_sum object created
35
for a set function that are used to check validity of the set function
37
If the set function is not allowed in any subquery where it occurs
38
an error is reported immediately.
40
@param thd reference to the thread context info
43
This function is to be called for any item created for a set function
44
object when the traversal of trees built for expressions used in the query
45
is performed at the phase of context analysis. This function is to
46
be invoked at the descent of this traversal.
48
TRUE if an error is reported
53
bool Item_sum::init_sum_func_check(THD *thd)
55
if (!thd->lex->allow_sum_func)
57
my_message(ER_INVALID_GROUP_FUNC_USE, ER(ER_INVALID_GROUP_FUNC_USE),
61
/* Set a reference to the nesting set function if there is any */
62
in_sum_func= thd->lex->in_sum_func;
63
/* Save a pointer to object to be used in items for nested set functions */
64
thd->lex->in_sum_func= this;
65
nest_level= thd->lex->current_select->nest_level;
70
max_sum_func_level= -1;
76
Check constraints imposed on a usage of a set function.
78
The method verifies whether context conditions imposed on a usage
79
of any set function are met for this occurrence.
80
It checks whether the set function occurs in the position where it
81
can be aggregated and, when it happens to occur in argument of another
82
set function, the method checks that these two functions are aggregated in
84
If the context conditions are not met the method reports an error.
85
If the set function is aggregated in some outer subquery the method
86
adds it to the chain of items for such set functions that is attached
87
to the the st_select_lex structure for this subquery.
89
A number of designated members of the object are used to check the
90
conditions. They are specified in the comment before the Item_sum
92
Additionally a bitmap variable called allow_sum_func is employed.
93
It is included into the thd->lex structure.
94
The bitmap contains 1 at n-th position if the set function happens
95
to occur under a construct of the n-th level subquery where usage
96
of set functions are allowed (i.e either in the SELECT list or
97
in the HAVING clause of the corresponding subquery)
100
SELECT SUM(t1.b) FROM t1 GROUP BY t1.a
101
HAVING t1.a IN (SELECT t2.c FROM t2 WHERE AVG(t1.b) > 20) AND
102
t1.a > (SELECT MIN(t2.d) FROM t2);
104
allow_sum_func will contain:
105
- for SUM(t1.b) - 1 at the first position
106
- for AVG(t1.b) - 1 at the first position, 0 at the second position
107
- for MIN(t2.d) - 1 at the first position, 1 at the second position.
109
@param thd reference to the thread context info
110
@param ref location of the pointer to this item in the embedding expression
113
This function is to be called for any item created for a set function
114
object when the traversal of trees built for expressions used in the query
115
is performed at the phase of context analysis. This function is to
116
be invoked at the ascent of this traversal.
119
TRUE if an error is reported
124
bool Item_sum::check_sum_func(THD *thd, Item **ref)
127
nesting_map allow_sum_func= thd->lex->allow_sum_func;
129
The value of max_arg_level is updated if an argument of the set function
130
contains a column reference resolved against a subquery whose level is
131
greater than the current value of max_arg_level.
132
max_arg_level cannot be greater than nest level.
133
nest level is always >= 0
135
if (nest_level == max_arg_level)
138
The function must be aggregated in the current subquery,
139
If it is there under a construct where it is not allowed
142
invalid= !(allow_sum_func & (1 << max_arg_level));
144
else if (max_arg_level >= 0 || !(allow_sum_func & (1 << nest_level)))
147
The set function can be aggregated only in outer subqueries.
148
Try to find a subquery where it can be aggregated;
149
If we fail to find such a subquery report an error.
151
if (register_sum_func(thd, ref))
153
invalid= aggr_level < 0 && !(allow_sum_func & (1 << nest_level));
154
if (!invalid && thd->variables.sql_mode & MODE_ANSI)
155
invalid= aggr_level < 0 && max_arg_level < nest_level;
157
if (!invalid && aggr_level < 0)
159
aggr_level= nest_level;
160
aggr_sel= thd->lex->current_select;
163
By this moment we either found a subquery where the set function is
164
to be aggregated and assigned a value that is >= 0 to aggr_level,
165
or set the value of 'invalid' to TRUE to report later an error.
168
Additionally we have to check whether possible nested set functions
169
are acceptable here: they are not, if the level of aggregation of
170
some of them is less than aggr_level.
173
invalid= aggr_level <= max_sum_func_level;
176
my_message(ER_INVALID_GROUP_FUNC_USE, ER(ER_INVALID_GROUP_FUNC_USE),
184
If the set function is nested adjust the value of
185
max_sum_func_level for the nesting set function.
186
We take into account only enclosed set functions that are to be
187
aggregated on the same level or above of the nest level of
188
the enclosing set function.
189
But we must always pass up the max_sum_func_level because it is
190
the maximum nested level of all directly and indirectly enclosed
191
set functions. We must do that even for set functions that are
192
aggregated inside of their enclosing set function's nest level
193
because the enclosing function may contain another enclosing
194
function that is to be aggregated outside or on the same level
195
as its parent's nest level.
197
if (in_sum_func->nest_level >= aggr_level)
198
set_if_bigger(in_sum_func->max_sum_func_level, aggr_level);
199
set_if_bigger(in_sum_func->max_sum_func_level, max_sum_func_level);
203
Check that non-aggregated fields and sum functions aren't mixed in the
204
same select in the ONLY_FULL_GROUP_BY mode.
206
if (outer_fields.elements)
210
Here we compare the nesting level of the select to which an outer field
211
belongs to with the aggregation level of the sum function. All fields in
212
the outer_fields list are checked.
214
If the nesting level is equal to the aggregation level then the field is
215
aggregated by this sum function.
216
If the nesting level is less than the aggregation level then the field
217
belongs to an outer select. In this case if there is an embedding sum
218
function add current field to functions outer_fields list. If there is
219
no embedding function then the current field treated as non aggregated
220
and the select it belongs to is marked accordingly.
221
If the nesting level is greater than the aggregation level then it means
222
that this field was added by an inner sum function.
225
select avg ( <-- we are here, checking outer.f1
227
select sum(outer.f1 + inner.f1) from inner
231
In this case we check that no aggregate functions are used in the
232
select the field belongs to. If there are some then an error is
235
List_iterator<Item_field> of(outer_fields);
236
while ((field= of++))
238
SELECT_LEX *sel= field->cached_table->select_lex;
239
if (sel->nest_level < aggr_level)
244
Let upper function decide whether this field is a non
247
in_sum_func->outer_fields.push_back(field);
250
sel->full_group_by_flag|= NON_AGG_FIELD_USED;
252
if (sel->nest_level > aggr_level &&
253
(sel->full_group_by_flag & SUM_FUNC_USED) &&
254
!sel->group_list.elements)
256
my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
257
ER(ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));
262
aggr_sel->full_group_by_flag|= SUM_FUNC_USED;
263
update_used_tables();
264
thd->lex->in_sum_func= in_sum_func;
269
Attach a set function to the subquery where it must be aggregated.
271
The function looks for an outer subquery where the set function must be
272
aggregated. If it finds such a subquery then aggr_level is set to
273
the nest level of this subquery and the item for the set function
274
is added to the list of set functions used in nested subqueries
275
inner_sum_func_list defined for each subquery. When the item is placed
276
there the field 'ref_by' is set to ref.
279
Now we 'register' only set functions that are aggregated in outer
280
subqueries. Actually it makes sense to link all set function for
281
a subquery in one chain. It would simplify the process of 'splitting'
284
@param thd reference to the thread context info
285
@param ref location of the pointer to this item in the embedding expression
288
FALSE if the executes without failures (currently always)
293
bool Item_sum::register_sum_func(THD *thd, Item **ref)
296
nesting_map allow_sum_func= thd->lex->allow_sum_func;
297
for (sl= thd->lex->current_select->master_unit()->outer_select() ;
298
sl && sl->nest_level > max_arg_level;
299
sl= sl->master_unit()->outer_select() )
301
if (aggr_level < 0 && (allow_sum_func & (1 << sl->nest_level)))
303
/* Found the most nested subquery where the function can be aggregated */
304
aggr_level= sl->nest_level;
308
if (sl && (allow_sum_func & (1 << sl->nest_level)))
311
We reached the subquery of level max_arg_level and checked
312
that the function can be aggregated here.
313
The set function will be aggregated in this subquery.
315
aggr_level= sl->nest_level;
322
/* Add the object to the list of registered objects assigned to aggr_sel */
323
if (!aggr_sel->inner_sum_func_list)
327
next= aggr_sel->inner_sum_func_list->next;
328
aggr_sel->inner_sum_func_list->next= this;
330
aggr_sel->inner_sum_func_list= this;
331
aggr_sel->with_sum_func= 1;
334
Mark Item_subselect(s) as containing aggregate function all the way up
335
to aggregate function's calculation context.
336
Note that we must not mark the Item of calculation context itself
337
because with_sum_func on the calculation context st_select_lex is
340
with_sum_func being set for an Item means that this Item refers
341
(somewhere in it, e.g. one of its arguments if it's a function) directly
342
or through intermediate items to an aggregate function that is calculated
343
in a context "outside" of the Item (e.g. in the current or outer select).
345
with_sum_func being set for an st_select_lex means that this st_select_lex
346
has aggregate functions directly referenced (i.e. not through a sub-select).
348
for (sl= thd->lex->current_select;
349
sl && sl != aggr_sel && sl->master_unit()->item;
350
sl= sl->master_unit()->outer_select() )
351
sl->master_unit()->item->with_sum_func= 1;
353
thd->lex->current_select->mark_as_dependent(aggr_sel);
358
Item_sum::Item_sum(List<Item> &list) :arg_count(list.elements),
361
if ((args=(Item**) sql_alloc(sizeof(Item*)*arg_count)))
364
List_iterator_fast<Item> li(list);
372
if (!(orig_args= (Item **) sql_alloc(sizeof(Item *) * arg_count)))
377
list.empty(); // Fields are used
382
Constructor used in processing select with temporary tebles.
385
Item_sum::Item_sum(THD *thd, Item_sum *item):
386
Item_result_field(thd, item),
387
aggr_sel(item->aggr_sel),
388
nest_level(item->nest_level), aggr_level(item->aggr_level),
389
quick_group(item->quick_group),
390
arg_count(item->arg_count), orig_args(NULL),
391
used_tables_cache(item->used_tables_cache),
392
forced_const(item->forced_const)
397
orig_args=tmp_orig_args;
401
if (!(args= (Item**) thd->alloc(sizeof(Item*)*arg_count)))
403
if (!(orig_args= (Item**) thd->alloc(sizeof(Item*)*arg_count)))
406
memcpy(args, item->args, sizeof(Item*)*arg_count);
407
memcpy(orig_args, item->orig_args, sizeof(Item*)*arg_count);
411
void Item_sum::mark_as_sum_func()
413
SELECT_LEX *cur_select= current_thd->lex->current_select;
414
cur_select->n_sum_items++;
415
cur_select->with_sum_func= 1;
420
void Item_sum::make_field(Send_field *tmp_field)
422
if (args[0]->type() == Item::FIELD_ITEM && keep_field_type())
424
((Item_field*) args[0])->field->make_field(tmp_field);
425
/* For expressions only col_name should be non-empty string. */
426
char *empty_string= (char*)"";
427
tmp_field->db_name= empty_string;
428
tmp_field->org_table_name= empty_string;
429
tmp_field->table_name= empty_string;
430
tmp_field->org_col_name= empty_string;
431
tmp_field->col_name= name;
433
tmp_field->flags&= ~NOT_NULL_FLAG;
436
init_make_field(tmp_field, field_type());
440
void Item_sum::print(String *str, enum_query_type query_type)
442
/* orig_args is not filled with valid values until fix_fields() */
443
Item **pargs= fixed ? orig_args : args;
444
str->append(func_name());
445
for (uint i=0 ; i < arg_count ; i++)
449
pargs[i]->print(str, query_type);
454
void Item_sum::fix_num_length_and_dec()
457
for (uint i=0 ; i < arg_count ; i++)
458
set_if_bigger(decimals,args[i]->decimals);
459
max_length=float_length(decimals);
462
Item *Item_sum::get_tmp_table_item(THD *thd)
464
Item_sum* sum_item= (Item_sum *) copy_or_same(thd);
465
if (sum_item && sum_item->result_field) // If not a const sum func
467
Field *result_field_tmp= sum_item->result_field;
468
for (uint i=0 ; i < sum_item->arg_count ; i++)
470
Item *arg= sum_item->args[i];
471
if (!arg->const_item())
473
if (arg->type() == Item::FIELD_ITEM)
474
((Item_field*) arg)->field= result_field_tmp++;
476
sum_item->args[i]= new Item_field(result_field_tmp++);
484
bool Item_sum::walk (Item_processor processor, bool walk_subquery,
489
Item **arg,**arg_end;
490
for (arg= args, arg_end= args+arg_count; arg != arg_end; arg++)
492
if ((*arg)->walk(processor, walk_subquery, argument))
496
return (this->*processor)(argument);
500
Field *Item_sum::create_tmp_field(bool group, TABLE *table,
501
uint convert_blob_length)
504
switch (result_type()) {
506
field= new Field_double(max_length, maybe_null, name, decimals, TRUE);
509
field= new Field_longlong(max_length, maybe_null, name, unsigned_flag);
512
if (max_length/collation.collation->mbmaxlen <= 255 ||
513
convert_blob_length > Field_varstring::MAX_SIZE ||
514
!convert_blob_length)
515
return make_string_field(table);
516
field= new Field_varstring(convert_blob_length, maybe_null,
517
name, table->s, collation.collation);
520
field= Field_new_decimal::create_from_item(this);
524
// This case should never be choosen
534
void Item_sum::update_used_tables ()
538
used_tables_cache= 0;
539
for (uint i=0 ; i < arg_count ; i++)
541
args[i]->update_used_tables();
542
used_tables_cache|= args[i]->used_tables();
545
used_tables_cache&= PSEUDO_TABLE_BITS;
547
/* the aggregate function is aggregated into its local context */
548
used_tables_cache |= (1 << aggr_sel->join->tables) - 1;
553
Item *Item_sum::set_arg(int i, THD *thd, Item *new_val)
555
thd->change_item_tree(args + i, new_val);
561
Item_sum_num::val_str(String *str)
563
return val_string_from_real(str);
567
my_decimal *Item_sum_num::val_decimal(my_decimal *decimal_value)
569
return val_decimal_from_real(decimal_value);
574
Item_sum_int::val_str(String *str)
576
return val_string_from_int(str);
580
my_decimal *Item_sum_int::val_decimal(my_decimal *decimal_value)
582
return val_decimal_from_int(decimal_value);
587
Item_sum_num::fix_fields(THD *thd, Item **ref)
589
DBUG_ASSERT(fixed == 0);
591
if (init_sum_func_check(thd))
596
for (uint i=0 ; i < arg_count ; i++)
598
if (args[i]->fix_fields(thd, args + i) || args[i]->check_cols(1))
600
set_if_bigger(decimals, args[i]->decimals);
601
maybe_null |= args[i]->maybe_null;
604
max_length=float_length(decimals);
606
fix_length_and_dec();
608
if (check_sum_func(thd, ref))
611
memcpy (orig_args, args, sizeof (Item *) * arg_count);
618
Item_sum_hybrid::fix_fields(THD *thd, Item **ref)
620
DBUG_ASSERT(fixed == 0);
624
if (init_sum_func_check(thd))
627
// 'item' can be changed during fix_fields
628
if ((!item->fixed && item->fix_fields(thd, args)) ||
629
(item= args[0])->check_cols(1))
631
decimals=item->decimals;
633
switch (hybrid_type= item->result_type()) {
638
max_length= item->max_length;
641
max_length= float_length(decimals);
644
max_length= item->max_length;
650
setup(args[0], NULL);
651
/* MIN/MAX can return NULL for empty set indepedent of the used column */
653
unsigned_flag=item->unsigned_flag;
656
fix_length_and_dec();
657
item= item->real_item();
658
if (item->type() == Item::FIELD_ITEM)
659
hybrid_field_type= ((Item_field*) item)->field->type();
661
hybrid_field_type= Item::field_type();
663
if (check_sum_func(thd, ref))
666
orig_args[0]= args[0];
673
MIN/MAX function setup.
675
@param item argument of MIN/MAX function
676
@param value_arg calculated value of MIN/MAX function
679
Setup cache/comparator of MIN/MAX functions. When called by the
680
copy_or_same function value_arg parameter contains calculated value
681
of the original MIN/MAX object and it is saved in this object's cache.
684
void Item_sum_hybrid::setup(Item *item, Item *value_arg)
686
value= Item_cache::get_cache(item);
688
value->store(value_arg);
689
cmp= new Arg_comparator();
690
cmp->set_cmp_func(this, args, (Item**)&value, FALSE);
691
collation.set(item->collation);
695
Field *Item_sum_hybrid::create_tmp_field(bool group, TABLE *table,
696
uint convert_blob_length)
699
if (args[0]->type() == Item::FIELD_ITEM)
701
field= ((Item_field*) args[0])->field;
703
if ((field= create_tmp_field_from_field(current_thd, field, name, table,
704
NULL, convert_blob_length)))
705
field->flags&= ~NOT_NULL_FLAG;
709
DATE/TIME fields have STRING_RESULT result types.
710
In order to preserve field type, it's needed to handle DATE/TIME
711
fields creations separately.
713
switch (args[0]->field_type()) {
714
case MYSQL_TYPE_DATE:
715
field= new Field_newdate(maybe_null, name, collation.collation);
717
case MYSQL_TYPE_TIME:
718
field= new Field_time(maybe_null, name, collation.collation);
720
case MYSQL_TYPE_TIMESTAMP:
721
case MYSQL_TYPE_DATETIME:
722
field= new Field_datetime(maybe_null, name, collation.collation);
725
return Item_sum::create_tmp_field(group, table, convert_blob_length);
733
/***********************************************************************
734
** reset and add of sum_func
735
***********************************************************************/
739
check if the following assignments are really needed
741
Item_sum_sum::Item_sum_sum(THD *thd, Item_sum_sum *item)
742
:Item_sum_num(thd, item), hybrid_type(item->hybrid_type),
743
curr_dec_buff(item->curr_dec_buff)
745
/* TODO: check if the following assignments are really needed */
746
if (hybrid_type == DECIMAL_RESULT)
748
my_decimal2decimal(item->dec_buffs, dec_buffs);
749
my_decimal2decimal(item->dec_buffs + 1, dec_buffs + 1);
755
Item *Item_sum_sum::copy_or_same(THD* thd)
757
return new (thd->mem_root) Item_sum_sum(thd, this);
761
void Item_sum_sum::clear()
763
DBUG_ENTER("Item_sum_sum::clear");
765
if (hybrid_type == DECIMAL_RESULT)
768
my_decimal_set_zero(dec_buffs);
776
void Item_sum_sum::fix_length_and_dec()
778
DBUG_ENTER("Item_sum_sum::fix_length_and_dec");
779
maybe_null=null_value=1;
780
decimals= args[0]->decimals;
781
switch (args[0]->result_type()) {
784
hybrid_type= REAL_RESULT;
790
/* SUM result can't be longer than length(arg) + length(MAX_ROWS) */
791
int precision= args[0]->decimal_precision() + DECIMAL_LONGLONG_DIGITS;
792
max_length= my_decimal_precision_to_length_no_truncation(precision,
796
hybrid_type= DECIMAL_RESULT;
797
my_decimal_set_zero(dec_buffs);
804
DBUG_PRINT("info", ("Type: %s (%d, %d)",
805
(hybrid_type == REAL_RESULT ? "REAL_RESULT" :
806
hybrid_type == DECIMAL_RESULT ? "DECIMAL_RESULT" :
807
hybrid_type == INT_RESULT ? "INT_RESULT" :
815
bool Item_sum_sum::add()
817
DBUG_ENTER("Item_sum_sum::add");
818
if (hybrid_type == DECIMAL_RESULT)
820
my_decimal value, *val= args[0]->val_decimal(&value);
821
if (!args[0]->null_value)
823
my_decimal_add(E_DEC_FATAL_ERROR, dec_buffs + (curr_dec_buff^1),
824
val, dec_buffs + curr_dec_buff);
831
sum+= args[0]->val_real();
832
if (!args[0]->null_value)
839
longlong Item_sum_sum::val_int()
841
DBUG_ASSERT(fixed == 1);
842
if (hybrid_type == DECIMAL_RESULT)
845
my_decimal2int(E_DEC_FATAL_ERROR, dec_buffs + curr_dec_buff, unsigned_flag,
849
return (longlong) rint(val_real());
853
double Item_sum_sum::val_real()
855
DBUG_ASSERT(fixed == 1);
856
if (hybrid_type == DECIMAL_RESULT)
857
my_decimal2double(E_DEC_FATAL_ERROR, dec_buffs + curr_dec_buff, &sum);
862
String *Item_sum_sum::val_str(String *str)
864
if (hybrid_type == DECIMAL_RESULT)
865
return val_string_from_decimal(str);
866
return val_string_from_real(str);
870
my_decimal *Item_sum_sum::val_decimal(my_decimal *val)
872
if (hybrid_type == DECIMAL_RESULT)
873
return (dec_buffs + curr_dec_buff);
874
return val_decimal_from_real(val);
877
/***************************************************************************/
881
/* Declarations for auxilary C-callbacks */
883
static int simple_raw_key_cmp(void* arg, const void* key1, const void* key2)
885
return memcmp(key1, key2, *(uint *) arg);
889
static int item_sum_distinct_walk(void *element, element_count num_of_dups,
892
return ((Item_sum_distinct*) (item))->unique_walk_function(element);
897
/* Item_sum_distinct */
899
Item_sum_distinct::Item_sum_distinct(Item *item_arg)
900
:Item_sum_num(item_arg), tree(0)
903
quick_group is an optimizer hint, which means that GROUP BY can be
904
handled with help of index on grouped columns.
905
By setting quick_group to zero we force creation of temporary table
912
Item_sum_distinct::Item_sum_distinct(THD *thd, Item_sum_distinct *original)
913
:Item_sum_num(thd, original), val(original->val), tree(0),
914
table_field_type(original->table_field_type)
921
Behaves like an Integer except to fix_length_and_dec().
922
Additionally div() converts val with this traits to a val with true
923
decimal traits along with conversion of integer value to decimal value.
924
This is to speedup SUM/AVG(DISTINCT) evaluation for 8-32 bit integer
927
struct Hybrid_type_traits_fast_decimal: public
928
Hybrid_type_traits_integer
930
virtual Item_result type() const { return DECIMAL_RESULT; }
931
virtual void fix_length_and_dec(Item *item, Item *arg) const
932
{ Hybrid_type_traits_decimal::instance()->fix_length_and_dec(item, arg); }
934
virtual void div(Hybrid_type *val, ulonglong u) const
936
int2my_decimal(E_DEC_FATAL_ERROR, val->integer, 0, val->dec_buf);
937
val->used_dec_buf_no= 0;
938
val->traits= Hybrid_type_traits_decimal::instance();
939
val->traits->div(val, u);
941
static const Hybrid_type_traits_fast_decimal *instance();
942
Hybrid_type_traits_fast_decimal() {};
945
static const Hybrid_type_traits_fast_decimal fast_decimal_traits_instance;
947
const Hybrid_type_traits_fast_decimal
948
*Hybrid_type_traits_fast_decimal::instance()
950
return &fast_decimal_traits_instance;
953
void Item_sum_distinct::fix_length_and_dec()
955
DBUG_ASSERT(args[0]->fixed);
957
table_field_type= args[0]->field_type();
959
/* Adjust tmp table type according to the chosen aggregation type */
960
switch (args[0]->result_type()) {
963
val.traits= Hybrid_type_traits::instance();
964
if (table_field_type != MYSQL_TYPE_FLOAT)
965
table_field_type= MYSQL_TYPE_DOUBLE;
969
Preserving int8, int16, int32 field types gives ~10% performance boost
970
as the size of result tree becomes significantly smaller.
971
Another speed up we gain by using longlong for intermediate
972
calculations. The range of int64 is enough to hold sum 2^32 distinct
973
integers each <= 2^32.
975
if (table_field_type == MYSQL_TYPE_INT24 ||
976
(table_field_type >= MYSQL_TYPE_TINY &&
977
table_field_type <= MYSQL_TYPE_LONG))
979
val.traits= Hybrid_type_traits_fast_decimal::instance();
982
table_field_type= MYSQL_TYPE_LONGLONG;
985
val.traits= Hybrid_type_traits_decimal::instance();
986
if (table_field_type != MYSQL_TYPE_LONGLONG)
987
table_field_type= MYSQL_TYPE_NEWDECIMAL;
993
val.traits->fix_length_and_dec(this, args[0]);
999
check that the case of CHAR(0) works OK
1001
bool Item_sum_distinct::setup(THD *thd)
1003
List<Create_field> field_list;
1004
Create_field field_def; /* field definition */
1005
DBUG_ENTER("Item_sum_distinct::setup");
1006
/* It's legal to call setup() more than once when in a subquery */
1011
Virtual table and the tree are created anew on each re-execution of
1012
PS/SP. Hence all further allocations are performed in the runtime
1015
if (field_list.push_back(&field_def))
1018
null_value= maybe_null= 1;
1021
DBUG_ASSERT(args[0]->fixed);
1023
field_def.init_for_tmp_table(table_field_type, args[0]->max_length,
1024
args[0]->decimals, args[0]->maybe_null,
1025
args[0]->unsigned_flag);
1027
if (! (table= create_virtual_tmp_table(thd, field_list)))
1030
/* XXX: check that the case of CHAR(0) works OK */
1031
tree_key_length= table->s->reclength - table->s->null_bytes;
1034
Unique handles all unique elements in a tree until they can't fit
1035
in. Then the tree is dumped to the temporary file. We can use
1036
simple_raw_key_cmp because the table contains numbers only; decimals
1037
are converted to binary representation as well.
1039
tree= new Unique(simple_raw_key_cmp, &tree_key_length, tree_key_length,
1040
thd->variables.max_heap_table_size);
1042
is_evaluated= FALSE;
1043
DBUG_RETURN(tree == 0);
1047
bool Item_sum_distinct::add()
1049
args[0]->save_in_field(table->field[0], FALSE);
1050
is_evaluated= FALSE;
1051
if (!table->field[0]->is_null())
1056
'0' values are also stored in the tree. This doesn't matter
1057
for SUM(DISTINCT), but is important for AVG(DISTINCT)
1059
return tree->unique_add(table->field[0]->ptr);
1065
bool Item_sum_distinct::unique_walk_function(void *element)
1067
memcpy(table->field[0]->ptr, element, tree_key_length);
1069
val.traits->add(&val, table->field[0]);
1074
void Item_sum_distinct::clear()
1076
DBUG_ENTER("Item_sum_distinct::clear");
1077
DBUG_ASSERT(tree != 0); /* we always have a tree */
1080
is_evaluated= FALSE;
1084
void Item_sum_distinct::cleanup()
1086
Item_sum_num::cleanup();
1090
is_evaluated= FALSE;
1093
Item_sum_distinct::~Item_sum_distinct()
1096
/* no need to free the table */
1100
void Item_sum_distinct::calculate_val_and_count()
1105
val.traits->set_zero(&val);
1107
We don't have a tree only if 'setup()' hasn't been called;
1108
this is the case of sql_select.cc:return_zero_rows.
1112
table->field[0]->set_notnull();
1113
tree->walk(item_sum_distinct_walk, (void*) this);
1120
double Item_sum_distinct::val_real()
1122
calculate_val_and_count();
1123
return val.traits->val_real(&val);
1127
my_decimal *Item_sum_distinct::val_decimal(my_decimal *to)
1129
calculate_val_and_count();
1132
return val.traits->val_decimal(&val, to);
1136
longlong Item_sum_distinct::val_int()
1138
calculate_val_and_count();
1139
return val.traits->val_int(&val, unsigned_flag);
1143
String *Item_sum_distinct::val_str(String *str)
1145
calculate_val_and_count();
1148
return val.traits->val_str(&val, str, decimals);
1151
/* end of Item_sum_distinct */
1153
/* Item_sum_avg_distinct */
1156
Item_sum_avg_distinct::fix_length_and_dec()
1158
Item_sum_distinct::fix_length_and_dec();
1159
prec_increment= current_thd->variables.div_precincrement;
1161
AVG() will divide val by count. We need to reserve digits
1162
after decimal point as the result can be fractional.
1164
decimals= min(decimals + prec_increment, NOT_FIXED_DEC);
1169
Item_sum_avg_distinct::calculate_val_and_count()
1173
Item_sum_distinct::calculate_val_and_count();
1175
val.traits->div(&val, count);
1181
Item *Item_sum_count::copy_or_same(THD* thd)
1183
return new (thd->mem_root) Item_sum_count(thd, this);
1187
void Item_sum_count::clear()
1193
bool Item_sum_count::add()
1195
if (!args[0]->maybe_null || !args[0]->is_null())
1200
longlong Item_sum_count::val_int()
1202
DBUG_ASSERT(fixed == 1);
1203
return (longlong) count;
1207
void Item_sum_count::cleanup()
1209
DBUG_ENTER("Item_sum_count::cleanup");
1211
Item_sum_int::cleanup();
1219
void Item_sum_avg::fix_length_and_dec()
1221
Item_sum_sum::fix_length_and_dec();
1222
maybe_null=null_value=1;
1223
prec_increment= current_thd->variables.div_precincrement;
1224
if (hybrid_type == DECIMAL_RESULT)
1226
int precision= args[0]->decimal_precision() + prec_increment;
1227
decimals= min(args[0]->decimals + prec_increment, DECIMAL_MAX_SCALE);
1228
max_length= my_decimal_precision_to_length_no_truncation(precision,
1231
f_precision= min(precision+DECIMAL_LONGLONG_DIGITS, DECIMAL_MAX_PRECISION);
1232
f_scale= args[0]->decimals;
1233
dec_bin_size= my_decimal_get_binary_size(f_precision, f_scale);
1236
decimals= min(args[0]->decimals + prec_increment, NOT_FIXED_DEC);
1237
max_length= args[0]->max_length + prec_increment;
1242
Item *Item_sum_avg::copy_or_same(THD* thd)
1244
return new (thd->mem_root) Item_sum_avg(thd, this);
1248
Field *Item_sum_avg::create_tmp_field(bool group, TABLE *table,
1249
uint convert_blob_len)
1255
We must store both value and counter in the temporary table in one field.
1256
The easiest way is to do this is to store both value in a string
1257
and unpack on access.
1259
field= new Field_string(((hybrid_type == DECIMAL_RESULT) ?
1260
dec_bin_size : sizeof(double)) + sizeof(longlong),
1261
0, name, &my_charset_bin);
1263
else if (hybrid_type == DECIMAL_RESULT)
1264
field= Field_new_decimal::create_from_item(this);
1266
field= new Field_double(max_length, maybe_null, name, decimals, TRUE);
1273
void Item_sum_avg::clear()
1275
Item_sum_sum::clear();
1280
bool Item_sum_avg::add()
1282
if (Item_sum_sum::add())
1284
if (!args[0]->null_value)
1289
double Item_sum_avg::val_real()
1291
DBUG_ASSERT(fixed == 1);
1297
return Item_sum_sum::val_real() / ulonglong2double(count);
1301
my_decimal *Item_sum_avg::val_decimal(my_decimal *val)
1303
my_decimal sum_buff, cnt;
1304
const my_decimal *sum_dec;
1305
DBUG_ASSERT(fixed == 1);
1313
For non-DECIMAL hybrid_type the division will be done in
1314
Item_sum_avg::val_real().
1316
if (hybrid_type != DECIMAL_RESULT)
1317
return val_decimal_from_real(val);
1319
sum_dec= dec_buffs + curr_dec_buff;
1320
int2my_decimal(E_DEC_FATAL_ERROR, count, 0, &cnt);
1321
my_decimal_div(E_DEC_FATAL_ERROR, val, sum_dec, &cnt, prec_increment);
1326
String *Item_sum_avg::val_str(String *str)
1328
if (hybrid_type == DECIMAL_RESULT)
1329
return val_string_from_decimal(str);
1330
return val_string_from_real(str);
1338
double Item_sum_std::val_real()
1340
DBUG_ASSERT(fixed == 1);
1341
double nr= Item_sum_variance::val_real();
1342
DBUG_ASSERT(nr >= 0.0);
1346
Item *Item_sum_std::copy_or_same(THD* thd)
1348
return new (thd->mem_root) Item_sum_std(thd, this);
1358
Variance implementation for floating-point implementations, without
1359
catastrophic cancellation, from Knuth's _TAoCP_, 3rd ed, volume 2, pg232.
1360
This alters the value at m, s, and increments count.
1364
These two functions are used by the Item_sum_variance and the
1365
Item_variance_field classes, which are unrelated, and each need to calculate
1366
variance. The difference between the two classes is that the first is used
1367
for a mundane SELECT, while the latter is used in a GROUPing SELECT.
1369
static void variance_fp_recurrence_next(double *m, double *s, ulonglong *count, double nr)
1380
double m_kminusone= *m;
1381
*m= m_kminusone + (nr - m_kminusone) / (double) *count;
1382
*s= *s + (nr - m_kminusone) * (nr - *m);
1387
static double variance_fp_recurrence_result(double s, ulonglong count, bool is_sample_variance)
1392
if (is_sample_variance)
1393
return s / (count - 1);
1395
/* else, is a population variance */
1400
Item_sum_variance::Item_sum_variance(THD *thd, Item_sum_variance *item):
1401
Item_sum_num(thd, item), hybrid_type(item->hybrid_type),
1402
count(item->count), sample(item->sample),
1403
prec_increment(item->prec_increment)
1405
recurrence_m= item->recurrence_m;
1406
recurrence_s= item->recurrence_s;
1410
void Item_sum_variance::fix_length_and_dec()
1412
DBUG_ENTER("Item_sum_variance::fix_length_and_dec");
1413
maybe_null= null_value= 1;
1414
prec_increment= current_thd->variables.div_precincrement;
1417
According to the SQL2003 standard (Part 2, Foundations; sec 10.9,
1418
aggregate function; paragraph 7h of Syntax Rules), "the declared
1419
type of the result is an implementation-defined aproximate numeric
1422
hybrid_type= REAL_RESULT;
1424
switch (args[0]->result_type()) {
1427
decimals= min(args[0]->decimals + 4, NOT_FIXED_DEC);
1430
case DECIMAL_RESULT:
1432
int precision= args[0]->decimal_precision()*2 + prec_increment;
1433
decimals= min(args[0]->decimals + prec_increment, DECIMAL_MAX_SCALE);
1434
max_length= my_decimal_precision_to_length_no_truncation(precision,
1444
DBUG_PRINT("info", ("Type: REAL_RESULT (%d, %d)", max_length, (int)decimals));
1449
Item *Item_sum_variance::copy_or_same(THD* thd)
1451
return new (thd->mem_root) Item_sum_variance(thd, this);
1456
Create a new field to match the type of value we're expected to yield.
1457
If we're grouping, then we need some space to serialize variables into, to
1460
Field *Item_sum_variance::create_tmp_field(bool group, TABLE *table,
1461
uint convert_blob_len)
1467
We must store both value and counter in the temporary table in one field.
1468
The easiest way is to do this is to store both value in a string
1469
and unpack on access.
1471
field= new Field_string(sizeof(double)*2 + sizeof(longlong), 0, name, &my_charset_bin);
1474
field= new Field_double(max_length, maybe_null, name, decimals, TRUE);
1483
void Item_sum_variance::clear()
1488
bool Item_sum_variance::add()
1491
Why use a temporary variable? We don't know if it is null until we
1492
evaluate it, which has the side-effect of setting null_value .
1494
double nr= args[0]->val_real();
1496
if (!args[0]->null_value)
1497
variance_fp_recurrence_next(&recurrence_m, &recurrence_s, &count, nr);
1501
double Item_sum_variance::val_real()
1503
DBUG_ASSERT(fixed == 1);
1506
'sample' is a 1/0 boolean value. If it is 1/true, id est this is a sample
1507
variance call, then we should set nullness when the count of the items
1508
is one or zero. If it's zero, i.e. a population variance, then we only
1509
set nullness when the count is zero.
1511
Another way to read it is that 'sample' is the numerical threshhold, at and
1512
below which a 'count' number of items is called NULL.
1514
DBUG_ASSERT((sample == 0) || (sample == 1));
1515
if (count <= sample)
1522
return variance_fp_recurrence_result(recurrence_s, count, sample);
1526
my_decimal *Item_sum_variance::val_decimal(my_decimal *dec_buf)
1528
DBUG_ASSERT(fixed == 1);
1529
return val_decimal_from_real(dec_buf);
1533
void Item_sum_variance::reset_field()
1536
uchar *res= result_field->ptr;
1538
nr= args[0]->val_real(); /* sets null_value as side-effect */
1540
if (args[0]->null_value)
1541
bzero(res,sizeof(double)*2+sizeof(longlong));
1544
/* Serialize format is (double)m, (double)s, (longlong)count */
1545
ulonglong tmp_count;
1547
float8store(res, nr); /* recurrence variable m */
1549
float8store(res + sizeof(double), tmp_s);
1551
int8store(res + sizeof(double)*2, tmp_count);
1556
void Item_sum_variance::update_field()
1558
ulonglong field_count;
1559
uchar *res=result_field->ptr;
1561
double nr= args[0]->val_real(); /* sets null_value as side-effect */
1563
if (args[0]->null_value)
1566
/* Serialize format is (double)m, (double)s, (longlong)count */
1567
double field_recurrence_m, field_recurrence_s;
1568
float8get(field_recurrence_m, res);
1569
float8get(field_recurrence_s, res + sizeof(double));
1570
field_count=sint8korr(res+sizeof(double)*2);
1572
variance_fp_recurrence_next(&field_recurrence_m, &field_recurrence_s, &field_count, nr);
1574
float8store(res, field_recurrence_m);
1575
float8store(res + sizeof(double), field_recurrence_s);
1576
res+= sizeof(double)*2;
1577
int8store(res,field_count);
1583
void Item_sum_hybrid::clear()
1585
value->null_value= 1;
1589
double Item_sum_hybrid::val_real()
1591
DBUG_ASSERT(fixed == 1);
1594
return value->val_real();
1597
longlong Item_sum_hybrid::val_int()
1599
DBUG_ASSERT(fixed == 1);
1602
return value->val_int();
1606
my_decimal *Item_sum_hybrid::val_decimal(my_decimal *val)
1608
DBUG_ASSERT(fixed == 1);
1611
return value->val_decimal(val);
1616
Item_sum_hybrid::val_str(String *str)
1618
DBUG_ASSERT(fixed == 1);
1621
return value->val_str(str);
1625
void Item_sum_hybrid::cleanup()
1627
DBUG_ENTER("Item_sum_hybrid::cleanup");
1628
Item_sum::cleanup();
1629
forced_const= FALSE;
1634
by default it is TRUE to avoid TRUE reporting by
1635
Item_func_not_all/Item_func_nop_all if this item was never called.
1637
no_rows_in_result() set it to FALSE if was not results found.
1638
If some results found it will be left unchanged.
1644
void Item_sum_hybrid::no_rows_in_result()
1651
Item *Item_sum_min::copy_or_same(THD* thd)
1653
Item_sum_min *item= new (thd->mem_root) Item_sum_min(thd, this);
1654
item->setup(args[0], value);
1659
bool Item_sum_min::add()
1661
/* args[0] < value */
1662
int res= cmp->compare();
1663
if (!args[0]->null_value &&
1664
(null_value || res < 0))
1666
value->store(args[0]);
1667
value->cache_value();
1674
Item *Item_sum_max::copy_or_same(THD* thd)
1676
Item_sum_max *item= new (thd->mem_root) Item_sum_max(thd, this);
1677
item->setup(args[0], value);
1682
bool Item_sum_max::add()
1684
/* args[0] > value */
1685
int res= cmp->compare();
1686
if (!args[0]->null_value &&
1687
(null_value || res > 0))
1689
value->store(args[0]);
1690
value->cache_value();
1697
/* bit_or and bit_and */
1699
longlong Item_sum_bit::val_int()
1701
DBUG_ASSERT(fixed == 1);
1702
return (longlong) bits;
1706
void Item_sum_bit::clear()
1711
Item *Item_sum_or::copy_or_same(THD* thd)
1713
return new (thd->mem_root) Item_sum_or(thd, this);
1717
bool Item_sum_or::add()
1719
ulonglong value= (ulonglong) args[0]->val_int();
1720
if (!args[0]->null_value)
1725
Item *Item_sum_xor::copy_or_same(THD* thd)
1727
return new (thd->mem_root) Item_sum_xor(thd, this);
1731
bool Item_sum_xor::add()
1733
ulonglong value= (ulonglong) args[0]->val_int();
1734
if (!args[0]->null_value)
1739
Item *Item_sum_and::copy_or_same(THD* thd)
1741
return new (thd->mem_root) Item_sum_and(thd, this);
1745
bool Item_sum_and::add()
1747
ulonglong value= (ulonglong) args[0]->val_int();
1748
if (!args[0]->null_value)
1753
/************************************************************************
1754
** reset result of a Item_sum with is saved in a tmp_table
1755
*************************************************************************/
1757
void Item_sum_num::reset_field()
1759
double nr= args[0]->val_real();
1760
uchar *res=result_field->ptr;
1764
if (args[0]->null_value)
1767
result_field->set_null();
1770
result_field->set_notnull();
1772
float8store(res,nr);
1776
void Item_sum_hybrid::reset_field()
1778
switch(hybrid_type) {
1781
char buff[MAX_FIELD_WIDTH];
1782
String tmp(buff,sizeof(buff),result_field->charset()),*res;
1784
res=args[0]->val_str(&tmp);
1785
if (args[0]->null_value)
1787
result_field->set_null();
1788
result_field->reset();
1792
result_field->set_notnull();
1793
result_field->store(res->ptr(),res->length(),tmp.charset());
1799
longlong nr=args[0]->val_int();
1803
if (args[0]->null_value)
1806
result_field->set_null();
1809
result_field->set_notnull();
1811
result_field->store(nr, unsigned_flag);
1816
double nr= args[0]->val_real();
1820
if (args[0]->null_value)
1823
result_field->set_null();
1826
result_field->set_notnull();
1828
result_field->store(nr);
1831
case DECIMAL_RESULT:
1833
my_decimal value_buff, *arg_dec= args[0]->val_decimal(&value_buff);
1837
if (args[0]->null_value)
1838
result_field->set_null();
1840
result_field->set_notnull();
1843
We must store zero in the field as we will use the field value in
1846
if (!arg_dec) // Null
1847
arg_dec= &decimal_zero;
1848
result_field->store_decimal(arg_dec);
1858
void Item_sum_sum::reset_field()
1860
if (hybrid_type == DECIMAL_RESULT)
1862
my_decimal value, *arg_val= args[0]->val_decimal(&value);
1863
if (!arg_val) // Null
1864
arg_val= &decimal_zero;
1865
result_field->store_decimal(arg_val);
1869
DBUG_ASSERT(hybrid_type == REAL_RESULT);
1870
double nr= args[0]->val_real(); // Nulls also return 0
1871
float8store(result_field->ptr, nr);
1873
if (args[0]->null_value)
1874
result_field->set_null();
1876
result_field->set_notnull();
1880
void Item_sum_count::reset_field()
1882
uchar *res=result_field->ptr;
1885
if (!args[0]->maybe_null || !args[0]->is_null())
1891
void Item_sum_avg::reset_field()
1893
uchar *res=result_field->ptr;
1894
if (hybrid_type == DECIMAL_RESULT)
1897
my_decimal value, *arg_dec= args[0]->val_decimal(&value);
1898
if (args[0]->null_value)
1900
arg_dec= &decimal_zero;
1905
my_decimal2binary(E_DEC_FATAL_ERROR, arg_dec, res, f_precision, f_scale);
1907
int8store(res, tmp);
1911
double nr= args[0]->val_real();
1913
if (args[0]->null_value)
1914
bzero(res,sizeof(double)+sizeof(longlong));
1918
float8store(res,nr);
1919
res+=sizeof(double);
1926
void Item_sum_bit::reset_field()
1929
int8store(result_field->ptr, bits);
1932
void Item_sum_bit::update_field()
1934
uchar *res=result_field->ptr;
1935
bits= uint8korr(res);
1937
int8store(res, bits);
1942
calc next value and merge it with field_value.
1945
void Item_sum_sum::update_field()
1947
if (hybrid_type == DECIMAL_RESULT)
1949
my_decimal value, *arg_val= args[0]->val_decimal(&value);
1950
if (!args[0]->null_value)
1952
if (!result_field->is_null())
1954
my_decimal field_value,
1955
*field_val= result_field->val_decimal(&field_value);
1956
my_decimal_add(E_DEC_FATAL_ERROR, dec_buffs, arg_val, field_val);
1957
result_field->store_decimal(dec_buffs);
1961
result_field->store_decimal(arg_val);
1962
result_field->set_notnull();
1969
uchar *res=result_field->ptr;
1971
float8get(old_nr,res);
1972
nr= args[0]->val_real();
1973
if (!args[0]->null_value)
1976
result_field->set_notnull();
1978
float8store(res,old_nr);
1983
void Item_sum_count::update_field()
1986
uchar *res=result_field->ptr;
1989
if (!args[0]->maybe_null || !args[0]->is_null())
1995
void Item_sum_avg::update_field()
1997
longlong field_count;
1998
uchar *res=result_field->ptr;
1999
if (hybrid_type == DECIMAL_RESULT)
2001
my_decimal value, *arg_val= args[0]->val_decimal(&value);
2002
if (!args[0]->null_value)
2004
binary2my_decimal(E_DEC_FATAL_ERROR, res,
2005
dec_buffs + 1, f_precision, f_scale);
2006
field_count= sint8korr(res + dec_bin_size);
2007
my_decimal_add(E_DEC_FATAL_ERROR, dec_buffs, arg_val, dec_buffs + 1);
2008
my_decimal2binary(E_DEC_FATAL_ERROR, dec_buffs,
2009
res, f_precision, f_scale);
2012
int8store(res, field_count);
2019
nr= args[0]->val_real();
2020
if (!args[0]->null_value)
2023
float8get(old_nr, res);
2024
field_count= sint8korr(res + sizeof(double));
2026
float8store(res,old_nr);
2027
res+= sizeof(double);
2029
int8store(res, field_count);
2035
void Item_sum_hybrid::update_field()
2037
switch (hybrid_type) {
2039
min_max_update_str_field();
2042
min_max_update_int_field();
2044
case DECIMAL_RESULT:
2045
min_max_update_decimal_field();
2048
min_max_update_real_field();
2054
Item_sum_hybrid::min_max_update_str_field()
2057
String *res_str=args[0]->val_str(&cmp->value1);
2059
if (!args[0]->null_value)
2061
result_field->val_str(&cmp->value2);
2063
if (result_field->is_null() ||
2064
(cmp_sign * sortcmp(res_str,&cmp->value2,collation.collation)) < 0)
2065
result_field->store(res_str->ptr(),res_str->length(),res_str->charset());
2066
result_field->set_notnull();
2072
Item_sum_hybrid::min_max_update_real_field()
2076
old_nr=result_field->val_real();
2077
nr= args[0]->val_real();
2078
if (!args[0]->null_value)
2080
if (result_field->is_null(0) ||
2081
(cmp_sign > 0 ? old_nr > nr : old_nr < nr))
2083
result_field->set_notnull();
2085
else if (result_field->is_null(0))
2086
result_field->set_null();
2087
result_field->store(old_nr);
2092
Item_sum_hybrid::min_max_update_int_field()
2096
old_nr=result_field->val_int();
2097
nr=args[0]->val_int();
2098
if (!args[0]->null_value)
2100
if (result_field->is_null(0))
2104
bool res=(unsigned_flag ?
2105
(ulonglong) old_nr > (ulonglong) nr :
2107
/* (cmp_sign > 0 && res) || (!(cmp_sign > 0) && !res) */
2108
if ((cmp_sign > 0) ^ (!res))
2111
result_field->set_notnull();
2113
else if (result_field->is_null(0))
2114
result_field->set_null();
2115
result_field->store(old_nr, unsigned_flag);
2121
optimize: do not get result_field in case of args[0] is NULL
2124
Item_sum_hybrid::min_max_update_decimal_field()
2126
/* TODO: optimize: do not get result_field in case of args[0] is NULL */
2127
my_decimal old_val, nr_val;
2128
const my_decimal *old_nr= result_field->val_decimal(&old_val);
2129
const my_decimal *nr= args[0]->val_decimal(&nr_val);
2130
if (!args[0]->null_value)
2132
if (result_field->is_null(0))
2136
bool res= my_decimal_cmp(old_nr, nr) > 0;
2137
/* (cmp_sign > 0 && res) || (!(cmp_sign > 0) && !res) */
2138
if ((cmp_sign > 0) ^ (!res))
2141
result_field->set_notnull();
2143
else if (result_field->is_null(0))
2144
result_field->set_null();
2145
result_field->store_decimal(old_nr);
2149
Item_avg_field::Item_avg_field(Item_result res_type, Item_sum_avg *item)
2152
decimals=item->decimals;
2153
max_length= item->max_length;
2154
unsigned_flag= item->unsigned_flag;
2155
field=item->result_field;
2157
hybrid_type= res_type;
2158
prec_increment= item->prec_increment;
2159
if (hybrid_type == DECIMAL_RESULT)
2161
f_scale= item->f_scale;
2162
f_precision= item->f_precision;
2163
dec_bin_size= item->dec_bin_size;
2167
double Item_avg_field::val_real()
2169
// fix_fields() never calls for this Item
2174
if (hybrid_type == DECIMAL_RESULT)
2175
return val_real_from_decimal();
2177
float8get(nr,field->ptr);
2178
res= (field->ptr+sizeof(double));
2179
count= sint8korr(res);
2181
if ((null_value= !count))
2183
return nr/(double) count;
2187
longlong Item_avg_field::val_int()
2189
return (longlong) rint(val_real());
2193
my_decimal *Item_avg_field::val_decimal(my_decimal *dec_buf)
2195
// fix_fields() never calls for this Item
2196
if (hybrid_type == REAL_RESULT)
2197
return val_decimal_from_real(dec_buf);
2199
longlong count= sint8korr(field->ptr + dec_bin_size);
2200
if ((null_value= !count))
2203
my_decimal dec_count, dec_field;
2204
binary2my_decimal(E_DEC_FATAL_ERROR,
2205
field->ptr, &dec_field, f_precision, f_scale);
2206
int2my_decimal(E_DEC_FATAL_ERROR, count, 0, &dec_count);
2207
my_decimal_div(E_DEC_FATAL_ERROR, dec_buf,
2208
&dec_field, &dec_count, prec_increment);
2213
String *Item_avg_field::val_str(String *str)
2215
// fix_fields() never calls for this Item
2216
if (hybrid_type == DECIMAL_RESULT)
2217
return val_string_from_decimal(str);
2218
return val_string_from_real(str);
2222
Item_std_field::Item_std_field(Item_sum_std *item)
2223
: Item_variance_field(item)
2228
double Item_std_field::val_real()
2231
// fix_fields() never calls for this Item
2232
nr= Item_variance_field::val_real();
2233
DBUG_ASSERT(nr >= 0.0);
2238
my_decimal *Item_std_field::val_decimal(my_decimal *dec_buf)
2241
We can't call val_decimal_from_real() for DECIMAL_RESULT as
2242
Item_variance_field::val_real() would cause an infinite loop
2244
my_decimal tmp_dec, *dec;
2246
if (hybrid_type == REAL_RESULT)
2247
return val_decimal_from_real(dec_buf);
2249
dec= Item_variance_field::val_decimal(dec_buf);
2252
my_decimal2double(E_DEC_FATAL_ERROR, dec, &nr);
2253
DBUG_ASSERT(nr >= 0.0);
2255
double2my_decimal(E_DEC_FATAL_ERROR, nr, &tmp_dec);
2256
my_decimal_round(E_DEC_FATAL_ERROR, &tmp_dec, decimals, FALSE, dec_buf);
2261
Item_variance_field::Item_variance_field(Item_sum_variance *item)
2264
decimals=item->decimals;
2265
max_length=item->max_length;
2266
unsigned_flag= item->unsigned_flag;
2267
field=item->result_field;
2269
sample= item->sample;
2270
prec_increment= item->prec_increment;
2271
if ((hybrid_type= item->hybrid_type) == DECIMAL_RESULT)
2273
f_scale0= item->f_scale0;
2274
f_precision0= item->f_precision0;
2275
dec_bin_size0= item->dec_bin_size0;
2276
f_scale1= item->f_scale1;
2277
f_precision1= item->f_precision1;
2278
dec_bin_size1= item->dec_bin_size1;
2283
double Item_variance_field::val_real()
2285
// fix_fields() never calls for this Item
2286
if (hybrid_type == DECIMAL_RESULT)
2287
return val_real_from_decimal();
2289
double recurrence_s;
2291
float8get(recurrence_s, (field->ptr + sizeof(double)));
2292
count=sint8korr(field->ptr+sizeof(double)*2);
2294
if ((null_value= (count <= sample)))
2297
return variance_fp_recurrence_result(recurrence_s, count, sample);
2301
/****************************************************************************
2302
** COUNT(DISTINCT ...)
2303
****************************************************************************/
2305
int simple_str_key_cmp(void* arg, uchar* key1, uchar* key2)
2307
Field *f= (Field*) arg;
2308
return f->cmp(key1, key2);
2312
Did not make this one static - at least gcc gets confused when
2313
I try to declare a static function as a friend. If you can figure
2314
out the syntax to make a static function a friend, make this one
2318
int composite_key_cmp(void* arg, uchar* key1, uchar* key2)
2320
Item_sum_count_distinct* item = (Item_sum_count_distinct*)arg;
2321
Field **field = item->table->field;
2322
Field **field_end= field + item->table->s->fields;
2323
uint32 *lengths=item->field_lengths;
2324
for (; field < field_end; ++field)
2327
int len = *lengths++;
2328
int res = f->cmp(key1, key2);
2340
static int count_distinct_walk(void *elem, element_count count, void *arg)
2342
(*((ulonglong*)arg))++;
2349
void Item_sum_count_distinct::cleanup()
2351
DBUG_ENTER("Item_sum_count_distinct::cleanup");
2352
Item_sum_int::cleanup();
2354
/* Free objects only if we own them. */
2358
We need to delete the table and the tree in cleanup() as
2359
they were allocated in the runtime memroot. Using the runtime
2360
memroot reduces memory footprint for PS/SP and simplifies setup().
2364
is_evaluated= FALSE;
2367
free_tmp_table(table->in_use, table);
2370
delete tmp_table_param;
2379
This is used by rollup to create a separate usable copy of
2383
void Item_sum_count_distinct::make_unique()
2387
force_copy_fields= 1;
2389
is_evaluated= FALSE;
2395
Item_sum_count_distinct::~Item_sum_count_distinct()
2401
bool Item_sum_count_distinct::setup(THD *thd)
2404
SELECT_LEX *select_lex= thd->lex->current_select;
2407
Setup can be called twice for ROLLUP items. This is a bug.
2408
Please add DBUG_ASSERT(tree == 0) here when it's fixed.
2409
It's legal to call setup() more than once when in a subquery
2411
if (tree || table || tmp_table_param)
2414
if (!(tmp_table_param= new TMP_TABLE_PARAM))
2417
/* Create a table with an unique key over all parameters */
2418
for (uint i=0; i < arg_count ; i++)
2421
if (list.push_back(item))
2422
return TRUE; // End of memory
2423
if (item->const_item() && item->is_null())
2428
count_field_types(select_lex, tmp_table_param, list, 0);
2429
tmp_table_param->force_copy_fields= force_copy_fields;
2430
DBUG_ASSERT(table == 0);
2432
Make create_tmp_table() convert BIT columns to BIGINT.
2433
This is needed because BIT fields store parts of their data in table's
2434
null bits, and we don't have methods to compare two table records, which
2435
is needed by Unique which is used when HEAP table is used.
2438
List_iterator_fast<Item> li(list);
2440
while ((item= li++))
2442
if (item->type() == Item::FIELD_ITEM &&
2443
((Item_field*)item)->field->type() == FIELD_TYPE_BIT)
2448
if (!(table= create_tmp_table(thd, tmp_table_param, list, (ORDER*) 0, 1,
2450
(select_lex->options | thd->options),
2451
HA_POS_ERROR, (char*)"")))
2453
table->file->extra(HA_EXTRA_NO_ROWS); // Don't update rows
2456
if (table->s->db_type() == heap_hton)
2459
No blobs, otherwise it would have been MyISAM: set up a compare
2460
function and its arguments to use with Unique.
2462
qsort_cmp2 compare_key;
2464
Field **field= table->field;
2465
Field **field_end= field + table->s->fields;
2466
bool all_binary= TRUE;
2468
for (tree_key_length= 0; field < field_end; ++field)
2471
enum enum_field_types f_type= f->type();
2472
tree_key_length+= f->pack_length();
2473
if ((f_type == MYSQL_TYPE_VARCHAR) ||
2474
(!f->binary() && (f_type == MYSQL_TYPE_STRING ||
2475
f_type == MYSQL_TYPE_VAR_STRING)))
2483
cmp_arg= (void*) &tree_key_length;
2484
compare_key= (qsort_cmp2) simple_raw_key_cmp;
2488
if (table->s->fields == 1)
2491
If we have only one field, which is the most common use of
2492
count(distinct), it is much faster to use a simpler key
2493
compare method that can take advantage of not having to worry
2496
compare_key= (qsort_cmp2) simple_str_key_cmp;
2497
cmp_arg= (void*) table->field[0];
2498
/* tree_key_length has been set already */
2503
compare_key= (qsort_cmp2) composite_key_cmp;
2504
cmp_arg= (void*) this;
2505
field_lengths= (uint32*) thd->alloc(table->s->fields * sizeof(uint32));
2506
for (tree_key_length= 0, length= field_lengths, field= table->field;
2507
field < field_end; ++field, ++length)
2509
*length= (*field)->pack_length();
2510
tree_key_length+= *length;
2514
DBUG_ASSERT(tree == 0);
2515
tree= new Unique(compare_key, cmp_arg, tree_key_length,
2516
thd->variables.max_heap_table_size);
2518
The only time tree_key_length could be 0 is if someone does
2519
count(distinct) on a char(0) field - stupid thing to do,
2520
but this has to be handled - otherwise someone can crash
2521
the server with a DoS attack
2523
is_evaluated= FALSE;
2531
Item *Item_sum_count_distinct::copy_or_same(THD* thd)
2533
return new (thd->mem_root) Item_sum_count_distinct(thd, this);
2537
void Item_sum_count_distinct::clear()
2539
/* tree and table can be both null only if always_null */
2540
is_evaluated= FALSE;
2547
table->file->extra(HA_EXTRA_NO_CACHE);
2548
table->file->ha_delete_all_rows();
2549
table->file->extra(HA_EXTRA_WRITE_CACHE);
2553
bool Item_sum_count_distinct::add()
2558
copy_fields(tmp_table_param);
2559
copy_funcs(tmp_table_param->items_to_copy);
2561
for (Field **field=table->field ; *field ; field++)
2562
if ((*field)->is_real_null(0))
2563
return 0; // Don't count NULL
2565
is_evaluated= FALSE;
2569
The first few bytes of record (at least one) are just markers
2570
for deleted and NULLs. We want to skip them since they will
2571
bloat the tree without providing any valuable info. Besides,
2572
key_length used to initialize the tree didn't include space for them.
2574
return tree->unique_add(table->record[0] + table->s->null_bytes);
2576
if ((error= table->file->ha_write_row(table->record[0])) &&
2577
table->file->is_fatal_error(error, HA_CHECK_DUP))
2583
longlong Item_sum_count_distinct::val_int()
2586
DBUG_ASSERT(fixed == 1);
2587
if (!table) // Empty query
2594
if (tree->elements == 0)
2595
return (longlong) tree->elements_in_tree(); // everything fits in memory
2597
tree->walk(count_distinct_walk, (void*) &count);
2599
return (longlong) count;
2602
error= table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
2606
table->file->print_error(error, MYF(0));
2609
return table->file->stats.records;
2613
/****************************************************************************
2614
** Functions to handle dynamic loadable aggregates
2615
** Original source by: Alexis Mikhailov <root@medinf.chuvashia.su>
2616
** Adapted for UDAs by: Andreas F. Bobak <bobak@relog.ch>.
2617
** Rewritten by: Monty.
2618
****************************************************************************/
2622
void Item_udf_sum::clear()
2624
DBUG_ENTER("Item_udf_sum::clear");
2629
bool Item_udf_sum::add()
2631
DBUG_ENTER("Item_udf_sum::add");
2632
udf.add(&null_value);
2636
void Item_udf_sum::cleanup()
2639
udf_handler::cleanup() nicely handles case when we have not
2640
original item but one created by copy_or_same() method.
2643
Item_sum::cleanup();
2647
void Item_udf_sum::print(String *str, enum_query_type query_type)
2649
str->append(func_name());
2651
for (uint i=0 ; i < arg_count ; i++)
2655
args[i]->print(str, query_type);
2661
Item *Item_sum_udf_float::copy_or_same(THD* thd)
2663
return new (thd->mem_root) Item_sum_udf_float(thd, this);
2666
double Item_sum_udf_float::val_real()
2668
DBUG_ASSERT(fixed == 1);
2669
DBUG_ENTER("Item_sum_udf_float::val");
2670
DBUG_PRINT("info",("result_type: %d arg_count: %d",
2671
args[0]->result_type(), arg_count));
2672
DBUG_RETURN(udf.val(&null_value));
2676
String *Item_sum_udf_float::val_str(String *str)
2678
return val_string_from_real(str);
2682
my_decimal *Item_sum_udf_float::val_decimal(my_decimal *dec)
2684
return val_decimal_from_real(dec);
2688
String *Item_sum_udf_decimal::val_str(String *str)
2690
return val_string_from_decimal(str);
2694
double Item_sum_udf_decimal::val_real()
2696
return val_real_from_decimal();
2700
longlong Item_sum_udf_decimal::val_int()
2702
return val_int_from_decimal();
2706
my_decimal *Item_sum_udf_decimal::val_decimal(my_decimal *dec_buf)
2708
DBUG_ASSERT(fixed == 1);
2709
DBUG_ENTER("Item_func_udf_decimal::val_decimal");
2710
DBUG_PRINT("info",("result_type: %d arg_count: %d",
2711
args[0]->result_type(), arg_count));
2713
DBUG_RETURN(udf.val_decimal(&null_value, dec_buf));
2717
Item *Item_sum_udf_decimal::copy_or_same(THD* thd)
2719
return new (thd->mem_root) Item_sum_udf_decimal(thd, this);
2723
Item *Item_sum_udf_int::copy_or_same(THD* thd)
2725
return new (thd->mem_root) Item_sum_udf_int(thd, this);
2728
longlong Item_sum_udf_int::val_int()
2730
DBUG_ASSERT(fixed == 1);
2731
DBUG_ENTER("Item_sum_udf_int::val_int");
2732
DBUG_PRINT("info",("result_type: %d arg_count: %d",
2733
args[0]->result_type(), arg_count));
2734
DBUG_RETURN(udf.val_int(&null_value));
2738
String *Item_sum_udf_int::val_str(String *str)
2740
return val_string_from_int(str);
2743
my_decimal *Item_sum_udf_int::val_decimal(my_decimal *dec)
2745
return val_decimal_from_int(dec);
2749
/** Default max_length is max argument length. */
2751
void Item_sum_udf_str::fix_length_and_dec()
2753
DBUG_ENTER("Item_sum_udf_str::fix_length_and_dec");
2755
for (uint i = 0; i < arg_count; i++)
2756
set_if_bigger(max_length,args[i]->max_length);
2761
Item *Item_sum_udf_str::copy_or_same(THD* thd)
2763
return new (thd->mem_root) Item_sum_udf_str(thd, this);
2767
my_decimal *Item_sum_udf_str::val_decimal(my_decimal *dec)
2769
return val_decimal_from_string(dec);
2772
String *Item_sum_udf_str::val_str(String *str)
2774
DBUG_ASSERT(fixed == 1);
2775
DBUG_ENTER("Item_sum_udf_str::str");
2776
String *res=udf.val_str(str,&str_value);
2781
#endif /* HAVE_DLOPEN */
2784
/*****************************************************************************
2785
GROUP_CONCAT function
2788
GROUP_CONCAT([DISTINCT] expr,... [ORDER BY col [ASC|DESC],...]
2789
[SEPARATOR str_const])
2791
concat of values from "group by" operation
2794
Blobs doesn't work with DISTINCT or ORDER BY
2795
*****************************************************************************/
2800
Compares the values for fields in expr list of GROUP_CONCAT.
2803
GROUP_CONCAT([DISTINCT] expr [,expr ...]
2804
[ORDER BY {unsigned_integer | col_name | expr}
2805
[ASC | DESC] [,col_name ...]]
2806
[SEPARATOR str_val])
2809
@retval -1 : key1 < key2
2810
@retval 0 : key1 = key2
2811
@retval 1 : key1 > key2
2814
int group_concat_key_cmp_with_distinct(void* arg, const void* key1,
2817
Item_func_group_concat *item_func= (Item_func_group_concat*)arg;
2818
TABLE *table= item_func->table;
2820
for (uint i= 0; i < item_func->arg_count_field; i++)
2822
Item *item= item_func->args[i];
2824
If field_item is a const item then either get_tp_table_field returns 0
2825
or it is an item over a const table.
2827
if (item->const_item())
2830
We have to use get_tmp_table_field() instead of
2831
real_item()->get_tmp_table_field() because we want the field in
2832
the temporary table, not the original field
2834
Field *field= item->get_tmp_table_field();
2836
uint offset= field->offset(field->table->record[0])-table->s->null_bytes;
2837
if((res= field->cmp((uchar*)key1 + offset, (uchar*)key2 + offset)))
2845
function of sort for syntax: GROUP_CONCAT(expr,... ORDER BY col,... )
2848
int group_concat_key_cmp_with_order(void* arg, const void* key1,
2851
Item_func_group_concat* grp_item= (Item_func_group_concat*) arg;
2852
ORDER **order_item, **end;
2853
TABLE *table= grp_item->table;
2855
for (order_item= grp_item->order, end=order_item+ grp_item->arg_count_order;
2859
Item *item= *(*order_item)->item;
2861
We have to use get_tmp_table_field() instead of
2862
real_item()->get_tmp_table_field() because we want the field in
2863
the temporary table, not the original field
2865
Field *field= item->get_tmp_table_field();
2867
If item is a const item then either get_tp_table_field returns 0
2868
or it is an item over a const table.
2870
if (field && !item->const_item())
2873
uint offset= (field->offset(field->table->record[0]) -
2874
table->s->null_bytes);
2875
if ((res= field->cmp((uchar*)key1 + offset, (uchar*)key2 + offset)))
2876
return (*order_item)->asc ? res : -res;
2880
We can't return 0 because in that case the tree class would remove this
2881
item as double value. This would cause problems for case-changes and
2882
if the returned values are not the same we do the sort on.
2889
Append data from current leaf to item->result.
2892
int dump_leaf_key(uchar* key, element_count count __attribute__((unused)),
2893
Item_func_group_concat *item)
2895
TABLE *table= item->table;
2896
String tmp((char *)table->record[1], table->s->reclength,
2897
default_charset_info);
2899
String *result= &item->result;
2900
Item **arg= item->args, **arg_end= item->args + item->arg_count_field;
2901
uint old_length= result->length();
2903
if (item->no_appended)
2904
item->no_appended= FALSE;
2906
result->append(*item->separator);
2910
for (; arg < arg_end; arg++)
2913
if (! (*arg)->const_item())
2916
We have to use get_tmp_table_field() instead of
2917
real_item()->get_tmp_table_field() because we want the field in
2918
the temporary table, not the original field
2919
We also can't use table->field array to access the fields
2920
because it contains both order and arg list fields.
2922
Field *field= (*arg)->get_tmp_table_field();
2923
uint offset= (field->offset(field->table->record[0]) -
2924
table->s->null_bytes);
2925
DBUG_ASSERT(offset < table->s->reclength);
2926
res= field->val_str(&tmp, key + offset);
2929
res= (*arg)->val_str(&tmp);
2931
result->append(*res);
2934
/* stop if length of result more than max_length */
2935
if (result->length() > item->max_length)
2937
int well_formed_error;
2938
CHARSET_INFO *cs= item->collation.collation;
2939
const char *ptr= result->ptr();
2942
It's ok to use item->result.length() as the fourth argument
2943
as this is never used to limit the length of the data.
2944
Cut is done with the third argument.
2946
add_length= cs->cset->well_formed_len(cs,
2948
ptr + item->max_length,
2950
&well_formed_error);
2951
result->length(old_length + add_length);
2952
item->count_cut_values++;
2953
item->warning_for_row= TRUE;
2961
Constructor of Item_func_group_concat.
2963
@param distinct_arg distinct
2964
@param select_list list of expression for show values
2965
@param order_list list of sort columns
2966
@param separator_arg string value of separator.
2969
Item_func_group_concat::
2970
Item_func_group_concat(Name_resolution_context *context_arg,
2971
bool distinct_arg, List<Item> *select_list,
2972
SQL_LIST *order_list, String *separator_arg)
2973
:tmp_table_param(0), warning(0),
2974
separator(separator_arg), tree(0), unique_filter(NULL), table(0),
2975
order(0), context(context_arg),
2976
arg_count_order(order_list ? order_list->elements : 0),
2977
arg_count_field(select_list->elements),
2978
count_cut_values(0),
2979
distinct(distinct_arg),
2980
warning_for_row(FALSE),
2981
force_copy_fields(0), original(0)
2987
arg_count= arg_count_field + arg_count_order;
2990
We need to allocate:
2991
args - arg_count_field+arg_count_order
2992
(for possible order items in temporare tables)
2993
order - arg_count_order
2995
if (!(args= (Item**) sql_alloc(sizeof(Item*) * arg_count +
2996
sizeof(ORDER*)*arg_count_order)))
2999
if (!(orig_args= (Item **) sql_alloc(sizeof(Item *) * arg_count)))
3005
order= (ORDER**)(args + arg_count);
3007
/* fill args items of show and sort */
3008
List_iterator_fast<Item> li(*select_list);
3010
for (arg_ptr=args ; (item_select= li++) ; arg_ptr++)
3011
*arg_ptr= item_select;
3013
if (arg_count_order)
3015
ORDER **order_ptr= order;
3016
for (ORDER *order_item= (ORDER*) order_list->first;
3018
order_item= order_item->next)
3020
(*order_ptr++)= order_item;
3021
*arg_ptr= *order_item->item;
3022
order_item->item= arg_ptr++;
3028
Item_func_group_concat::Item_func_group_concat(THD *thd,
3029
Item_func_group_concat *item)
3030
:Item_sum(thd, item),
3031
tmp_table_param(item->tmp_table_param),
3032
warning(item->warning),
3033
separator(item->separator),
3035
unique_filter(item->unique_filter),
3038
context(item->context),
3039
arg_count_order(item->arg_count_order),
3040
arg_count_field(item->arg_count_field),
3041
count_cut_values(item->count_cut_values),
3042
distinct(item->distinct),
3043
warning_for_row(item->warning_for_row),
3044
always_null(item->always_null),
3045
force_copy_fields(item->force_copy_fields),
3048
quick_group= item->quick_group;
3049
result.set_charset(collation.collation);
3054
void Item_func_group_concat::cleanup()
3056
DBUG_ENTER("Item_func_group_concat::cleanup");
3057
Item_sum::cleanup();
3059
/* Adjust warning message to include total number of cut values */
3062
char warn_buff[MYSQL_ERRMSG_SIZE];
3063
sprintf(warn_buff, ER(ER_CUT_VALUE_GROUP_CONCAT), count_cut_values);
3064
warning->set_msg(current_thd, warn_buff);
3069
Free table and tree if they belong to this item (if item have not pointer
3070
to original item from which was made copy => it own its objects )
3074
delete tmp_table_param;
3078
THD *thd= table->in_use;
3079
free_tmp_table(thd, table);
3088
delete unique_filter;
3089
unique_filter= NULL;
3093
char warn_buff[MYSQL_ERRMSG_SIZE];
3094
sprintf(warn_buff, ER(ER_CUT_VALUE_GROUP_CONCAT), count_cut_values);
3095
warning->set_msg(thd, warn_buff);
3099
DBUG_ASSERT(tree == 0 && warning == 0);
3105
Item *Item_func_group_concat::copy_or_same(THD* thd)
3107
return new (thd->mem_root) Item_func_group_concat(thd, this);
3111
void Item_func_group_concat::clear()
3116
warning_for_row= FALSE;
3121
unique_filter->reset();
3122
/* No need to reset the table as we never call write_row */
3126
bool Item_func_group_concat::add()
3130
copy_fields(tmp_table_param);
3131
copy_funcs(tmp_table_param->items_to_copy);
3133
for (uint i= 0; i < arg_count_field; i++)
3135
Item *show_item= args[i];
3136
if (!show_item->const_item())
3138
Field *f= show_item->get_tmp_table_field();
3139
if (f->is_null_in_record((const uchar*) table->record[0]))
3140
return 0; // Skip row if it contains null
3145
bool row_eligible= TRUE;
3149
/* Filter out duplicate rows. */
3150
uint count= unique_filter->elements_in_tree();
3151
unique_filter->unique_add(table->record[0] + table->s->null_bytes);
3152
if (count == unique_filter->elements_in_tree())
3153
row_eligible= FALSE;
3156
TREE_ELEMENT *el= 0; // Only for safety
3157
if (row_eligible && tree)
3159
el= tree_insert(tree, table->record[0] + table->s->null_bytes, 0,
3161
/* check if there was enough memory to insert the row */
3166
If the row is not a duplicate (el->count == 1)
3167
we can dump the row here in case of GROUP_CONCAT(DISTINCT...)
3168
instead of doing tree traverse later.
3170
if (row_eligible && !warning_for_row &&
3171
(!tree || (el->count == 1 && distinct && !arg_count_order)))
3172
dump_leaf_key(table->record[0] + table->s->null_bytes, 1, this);
3179
Item_func_group_concat::fix_fields(THD *thd, Item **ref)
3181
uint i; /* for loop variable */
3182
DBUG_ASSERT(fixed == 0);
3184
if (init_sum_func_check(thd))
3190
Fix fields for select list and ORDER clause
3193
for (i=0 ; i < arg_count ; i++)
3195
if ((!args[i]->fixed &&
3196
args[i]->fix_fields(thd, args + i)) ||
3197
args[i]->check_cols(1))
3201
if (agg_item_charsets(collation, func_name(),
3203
/* skip charset aggregation for order columns */
3204
arg_count - arg_count_order,
3205
MY_COLL_ALLOW_CONV, 1))
3208
result.set_charset(collation.collation);
3211
max_length= thd->variables.group_concat_max_len;
3214
if (separator->needs_conversion(separator->length(), separator->charset(),
3215
collation.collation, &offset))
3217
uint32 buflen= collation.collation->mbmaxlen * separator->length();
3218
uint errors, conv_length;
3220
String *new_separator;
3222
if (!(buf= (char*) thd->stmt_arena->alloc(buflen)) ||
3223
!(new_separator= new(thd->stmt_arena->mem_root)
3224
String(buf, buflen, collation.collation)))
3227
conv_length= copy_and_convert(buf, buflen, collation.collation,
3228
separator->ptr(), separator->length(),
3229
separator->charset(), &errors);
3230
new_separator->length(conv_length);
3231
separator= new_separator;
3234
if (check_sum_func(thd, ref))
3237
memcpy (orig_args, args, sizeof (Item *) * arg_count);
3243
bool Item_func_group_concat::setup(THD *thd)
3246
SELECT_LEX *select_lex= thd->lex->current_select;
3247
DBUG_ENTER("Item_func_group_concat::setup");
3250
Currently setup() can be called twice. Please add
3251
assertion here when this is fixed.
3256
if (!(tmp_table_param= new TMP_TABLE_PARAM))
3259
/* We'll convert all blobs to varchar fields in the temporary table */
3260
tmp_table_param->convert_blob_length= max_length *
3261
collation.collation->mbmaxlen;
3262
/* Push all not constant fields to the list and create a temp table */
3264
for (uint i= 0; i < arg_count_field; i++)
3266
Item *item= args[i];
3267
if (list.push_back(item))
3269
if (item->const_item())
3271
if (item->is_null())
3279
List<Item> all_fields(list);
3281
Try to find every ORDER expression in the list of GROUP_CONCAT
3282
arguments. If an expression is not found, prepend it to
3283
"all_fields". The resulting field list is used as input to create
3286
if (arg_count_order &&
3287
setup_order(thd, args, context->table_list, list, all_fields, *order))
3290
count_field_types(select_lex, tmp_table_param, all_fields, 0);
3291
tmp_table_param->force_copy_fields= force_copy_fields;
3292
DBUG_ASSERT(table == 0);
3293
if (arg_count_order > 0 || distinct)
3296
Currently we have to force conversion of BLOB values to VARCHAR's
3297
if we are to store them in TREE objects used for ORDER BY and
3298
DISTINCT. This leads to truncation if the BLOB's size exceeds
3299
Field_varstring::MAX_SIZE.
3301
set_if_smaller(tmp_table_param->convert_blob_length,
3302
Field_varstring::MAX_SIZE);
3305
Force the create_tmp_table() to convert BIT columns to INT
3306
as we cannot compare two table records containg BIT fields
3307
stored in the the tree used for distinct/order by.
3308
Moreover we don't even save in the tree record null bits
3309
where BIT fields store parts of their data.
3311
List_iterator_fast<Item> li(all_fields);
3313
while ((item= li++))
3315
if (item->type() == Item::FIELD_ITEM &&
3316
((Item_field*) item)->field->type() == FIELD_TYPE_BIT)
3322
We have to create a temporary table to get descriptions of fields
3323
(types, sizes and so on).
3325
Note that in the table, we first have the ORDER BY fields, then the
3328
if (!(table= create_tmp_table(thd, tmp_table_param, all_fields,
3329
(ORDER*) 0, 0, TRUE,
3330
(select_lex->options | thd->options),
3331
HA_POS_ERROR, (char*) "")))
3333
table->file->extra(HA_EXTRA_NO_ROWS);
3337
Need sorting or uniqueness: init tree and choose a function to sort.
3338
Don't reserve space for NULLs: if any of gconcat arguments is NULL,
3339
the row is not added to the result.
3341
uint tree_key_length= table->s->reclength - table->s->null_bytes;
3343
if (arg_count_order)
3347
Create a tree for sorting. The tree is used to sort (according to the
3348
syntax of this function). If there is no ORDER BY clause, we don't
3351
init_tree(tree, (uint) min(thd->variables.max_heap_table_size,
3352
thd->variables.sortbuff_size/16), 0,
3354
group_concat_key_cmp_with_order , 0, NULL, (void*) this);
3358
unique_filter= new Unique(group_concat_key_cmp_with_distinct,
3361
thd->variables.max_heap_table_size);
3367
/* This is used by rollup to create a separate usable copy of the function */
3369
void Item_func_group_concat::make_unique()
3374
force_copy_fields= 1;
3379
String* Item_func_group_concat::val_str(String* str)
3381
DBUG_ASSERT(fixed == 1);
3384
if (no_appended && tree)
3385
/* Tree is used for sorting as in ORDER BY */
3386
tree_walk(tree, (tree_walk_action)&dump_leaf_key, (void*)this,
3388
if (count_cut_values && !warning)
3391
ER_CUT_VALUE_GROUP_CONCAT needs an argument, but this gets set in
3392
Item_func_group_concat::cleanup().
3395
warning= push_warning(table->in_use, MYSQL_ERROR::WARN_LEVEL_WARN,
3396
ER_CUT_VALUE_GROUP_CONCAT,
3397
ER(ER_CUT_VALUE_GROUP_CONCAT));
3403
void Item_func_group_concat::print(String *str, enum_query_type query_type)
3405
/* orig_args is not filled with valid values until fix_fields() */
3406
Item **pargs= fixed ? orig_args : args;
3407
str->append(STRING_WITH_LEN("group_concat("));
3409
str->append(STRING_WITH_LEN("distinct "));
3410
for (uint i= 0; i < arg_count_field; i++)
3414
pargs[i]->print(str, query_type);
3416
if (arg_count_order)
3418
str->append(STRING_WITH_LEN(" order by "));
3419
for (uint i= 0 ; i < arg_count_order ; i++)
3423
(*order[i]->item)->print(str, query_type);
3425
str->append(STRING_WITH_LEN(" ASC"));
3427
str->append(STRING_WITH_LEN(" DESC"));
3430
str->append(STRING_WITH_LEN(" separator \'"));
3431
str->append(*separator);
3432
str->append(STRING_WITH_LEN("\')"));
3436
Item_func_group_concat::~Item_func_group_concat()
3438
if (!original && unique_filter)
3439
delete unique_filter;