MySQL表达式计算

MySQL表达式计算

代码位置：

mysql-5.6.26/sql/item_func.cc

样例驱动函数

mysql-5.6.26/sql/item.cc#resolve_const_item()

fix_length_and_dec

fix_length_and_dec函数负责计算出如下参数，供输出ResultMetaData及后继计算使用：
* max_length
* decimals
* maybe_null
* cmp_type
* collation
* cached_field_type
* compare_as_dates
* datetime_item

MySQL中greatest行为：
* cached_field_type是用标准逻辑agg_field_type()推导, 作为结果列类型。
* 计算过程却是根据get_cmp_type()来找到一个中间结果，然后所有数值转到中间结果上。 也就是说，比较过程与result_type类型无关。

例如下面的greateest函数，结果类型(cached_field_type)是var_string，但比较过程中会根据cmp_type的值将数据都转成数值类型进行比较，得到最大值后（数值类型），再将数值转化成字符串类型的数字输出。

mysql> select greatest(CharType, VarcharType, 0) g, t.* from matrix1 t; 
Field   1:  `g`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     23
Max_length: 4
Decimals:   31
Flags:      BINARY

Field   2:  `CharType`
Catalog:    `def`
Database:   `test`
Table:      `t`
Org_table:  `matrix1`
Type:       STRING
Collation:  ? (45)
Length:     12
Max_length: 1
Decimals:   0
Flags:

Field   3:  `VarcharType`
Catalog:    `def`
Database:   `test`
Table:      `t`
Org_table:  `matrix1`
Type:       VAR_STRING
Collation:  ? (45)
Length:     16
Max_length: 4
Decimals:   0
Flags:

Field   4:  `IntType`
Catalog:    `def`
Database:   `test`
Table:      `t`
Org_table:  `matrix1`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 2
Decimals:   0
Flags:      NUM

+------+----------+-------------+---------+
| g    | CharType | VarcharType | IntType |
+------+----------+-------------+---------+
| 1    | 1        | 1           |       0 |
| 1    | 1        | 1           |      99 |
| 1009 | 1        | 1009        |      99 |
| 1    | 1        | 1           |      99 |
| 1    | 1        | x99         |      99 |
| 9    | 3        | 9x1         |       8 |
+------+----------+-------------+---------+
6 rows in set, 1 warning (0.06 sec)

/*
@note called from Item::fix_fields.
*/
void Item_func_min_max::fix_length_and_dec()
{
  uint string_arg_count= 0;
  int max_int_part=0;
  bool datetime_found= FALSE;
  decimals=0;
  max_length=0;
  maybe_null=0;
  cmp_type= args[0]->temporal_with_date_as_number_result_type();

  for (uint i=0 ; i < arg_count ; i++)
  {
    set_if_bigger(max_length, args[i]->max_length);
    set_if_bigger(decimals, args[i]->decimals);
    set_if_bigger(max_int_part, args[i]->decimal_int_part());
    if (args[i]->maybe_null)
      maybe_null=1;
    cmp_type= item_cmp_type(cmp_type,
        args[i]->temporal_with_date_as_number_result_type());
    if (args[i]->result_type() == STRING_RESULT)
      string_arg_count++;
    if (args[i]->result_type() != ROW_RESULT &&
        args[i]->is_temporal_with_date())
    {
      datetime_found= TRUE;
      if (!datetime_item || args[i]->field_type() == MYSQL_TYPE_DATETIME)
        datetime_item= args[i];
    }
  }

  if (string_arg_count == arg_count)
  {
    // We compare as strings only if all arguments were strings.
    agg_arg_charsets_for_string_result_with_comparison(collation,
        args, arg_count);
    if (datetime_found)
    {
      thd= current_thd;
      compare_as_dates= TRUE;
      /*
         We should not do this:
         cached_field_type= datetime_item->field_type();
         count_datetime_length(args, arg_count);
         because compare_as_dates can be TRUE but
         result type can still be VARCHAR.
       */
    }
  }
  else if ((cmp_type == DECIMAL_RESULT) || (cmp_type == INT_RESULT))
  {
    collation.set_numeric();
    fix_char_length(my_decimal_precision_to_length_no_truncation(max_int_part +
          decimals,
          decimals,
          unsigned_flag));
  }
  else if (cmp_type == REAL_RESULT)
    fix_char_length(float_length(decimals));
  cached_field_type= agg_field_type(args, arg_count);
}

MySQL对datetime、date的处理比较特殊，感觉是打补丁打出来的，需要注意。

/*
   Compare item arguments in the DATETIME context.

   SYNOPSIS
   cmp_datetimes()
   value [out]   found least/greatest DATE/DATETIME value

   DESCRIPTION
   Compare item arguments as DATETIME values and return the index of the
   least/greatest argument in the arguments array.
   The correct integer DATE/DATETIME value of the found argument is
   stored to the value pointer, if latter is provided.

   RETURN
   0    If one of arguments is NULL or there was a execution error
#   index of the least/greatest argument
 */

uint Item_func_min_max::cmp_datetimes(longlong *value)
{
  longlong UNINIT_VAR(min_max);
  uint min_max_idx= 0;

  for (uint i=0; i < arg_count ; i++)
  {
    Item **arg= args + i;
    bool is_null;
    longlong res= get_datetime_value(thd, &arg, 0, datetime_item, &is_null);

    /* Check if we need to stop (because of error or KILL)  and stop the loop */
    if (thd->is_error())
    {
      null_value= 1;
      return 0;
    }

    if ((null_value= args[i]->null_value))
      return 0;
    if (i == 0 || (res < min_max ? cmp_sign : -cmp_sign) > 0)
    {
      min_max= res;
      min_max_idx= i;
    }
  }
  if (value)
    *value= min_max;
  return min_max_idx;
}


uint Item_func_min_max::cmp_times(longlong *value)
{
  longlong UNINIT_VAR(min_max);
  uint min_max_idx= 0;
  for (uint i=0; i < arg_count ; i++)
  {
    longlong res= args[i]->val_time_temporal();
    if ((null_value= args[i]->null_value))
      return 0;
    if (i == 0 || (res < min_max ? cmp_sign : -cmp_sign) > 0)
    {
      min_max= res;
      min_max_idx= i;
    }
  }
  if (value)
    *value= min_max;
  return min_max_idx;
}

val_str

当cmp_type为STRING的时候，会调用本函数。
但本函数内部

String *Item_func_min_max::val_str(String *str)
{
  DBUG_ASSERT(fixed == 1);
  if (compare_as_dates)
  {
    if (is_temporal())
    {
      /*
         In case of temporal data types, we always return
         string value according the format of the data type.
         For example, in case of LEAST(time_column, datetime_column)
         the result date type is DATETIME,
         so we return a 'YYYY-MM-DD hh:mm:ss' string even if time_column wins
         (conversion from TIME to DATETIME happens in this case).
       */
      longlong result;
      cmp_datetimes(&result);
      if (null_value)
        return 0;
      MYSQL_TIME ltime;
      TIME_from_longlong_packed(&ltime, field_type(), result);
      return (null_value= my_TIME_to_str(&ltime, str, decimals)) ?
        (String *) 0 : str;
    }
    else
    {
      /*
         In case of VARCHAR result type we just return val_str()
         value of the winning item AS IS, without conversion.
       */
      String *str_res;
      uint min_max_idx= cmp_datetimes(NULL);
      if (null_value)
        return 0;
      str_res= args[min_max_idx]->val_str(str);
      if (args[min_max_idx]->null_value)
      {
        // check if the call to val_str() above returns a NULL value
        null_value= 1;
        return NULL;
      }
      str_res->set_charset(collation.collation);
      return str_res;
    }
  }

  switch (cmp_type) {
    case INT_RESULT:
      {
        longlong nr=val_int();
        if (null_value)
          return 0;
        str->set_int(nr, unsigned_flag, collation.collation);
        return str;
      }
    case DECIMAL_RESULT:
      {
        my_decimal dec_buf, *dec_val= val_decimal(&dec_buf);
        if (null_value)
          return 0;
        my_decimal2string(E_DEC_FATAL_ERROR, dec_val, 0, 0, 0, str);
        return str;
      }
    case REAL_RESULT:
      {
        double nr= val_real();
        if (null_value)
          return 0; /* purecov: inspected */
        str->set_real(nr, decimals, collation.collation);
        return str;
      }
    case STRING_RESULT:
      {
        String *UNINIT_VAR(res);
        for (uint i=0; i < arg_count ; i++)
        {
          if (i == 0)
            res=args[i]->val_str(str);
          else
          {
            String *res2;
            res2= args[i]->val_str(res == str ? &tmp_value : str);
            if (res2)
            {
              int cmp= sortcmp(res,res2,collation.collation);
              if ((cmp_sign < 0 ? cmp : -cmp) < 0)
                res=res2;
            }
          }
          if ((null_value= args[i]->null_value))
            return 0;
        }
        res->set_charset(collation.collation);
        return res;
      }
    case ROW_RESULT:
    default:
      // This case should never be chosen
      DBUG_ASSERT(0);
      return 0;
  }
  return 0;                 // Keep compiler happy
}


bool Item_func_min_max::get_date(MYSQL_TIME *ltime, uint fuzzydate)
{
  DBUG_ASSERT(fixed == 1);
  if (compare_as_dates)
  {
    longlong result;
    cmp_datetimes(&result);
    if (null_value)
      return true;
    TIME_from_longlong_packed(ltime, datetime_item->field_type(), result);
    int warnings;
    return check_date(ltime, non_zero_date(ltime), fuzzydate, &warnings);
  }

  switch (field_type())
  {
    case MYSQL_TYPE_TIME:
      return get_date_from_time(ltime);
    case MYSQL_TYPE_DATETIME:
    case MYSQL_TYPE_TIMESTAMP:
    case MYSQL_TYPE_DATE:
      DBUG_ASSERT(0); // Should have been processed in "compare_as_dates" block.
    default:
      return get_date_from_non_temporal(ltime, fuzzydate);
  }
}


bool Item_func_min_max::get_time(MYSQL_TIME *ltime)
{
  DBUG_ASSERT(fixed == 1);
  if (compare_as_dates)
  {
    longlong result;
    cmp_datetimes(&result);
    if (null_value)
      return true;
    TIME_from_longlong_packed(ltime, datetime_item->field_type(), result);
    datetime_to_time(ltime);
    return false;
  }

  switch (field_type())
  {
    case MYSQL_TYPE_TIME:
      {
        longlong result;
        cmp_times(&result);
        if (null_value)
          return true;
        TIME_from_longlong_time_packed(ltime, result);
        return false;
      }
      break;
    case MYSQL_TYPE_DATE:
    case MYSQL_TYPE_TIMESTAMP:
    case MYSQL_TYPE_DATETIME:
      DBUG_ASSERT(0); // Should have been processed in "compare_as_dates" block.
    default:
      return get_time_from_non_temporal(ltime);
      break;
  }
}


double Item_func_min_max::val_real()
{
  DBUG_ASSERT(fixed == 1);
  double value=0.0;
  if (compare_as_dates)
  {
    longlong result= 0;
    (void)cmp_datetimes(&result);
    return double_from_datetime_packed(datetime_item->field_type(), result);
  }
  for (uint i=0; i < arg_count ; i++)
  {
    if (i == 0)
      value= args[i]->val_real();
    else
    {
      double tmp= args[i]->val_real();
      if (!args[i]->null_value && (tmp < value ? cmp_sign : -cmp_sign) > 0)
        value=tmp;
    }
    if ((null_value= args[i]->null_value))
      break;
  }
  return value;
}


longlong Item_func_min_max::val_int()
{
  DBUG_ASSERT(fixed == 1);
  longlong value=0;
  if (compare_as_dates)
  {
    longlong result= 0;
    (void)cmp_datetimes(&result);
    return longlong_from_datetime_packed(datetime_item->field_type(), result);
  }
  /*
     TS-TODO: val_str decides which type to use using cmp_type.
     val_int, val_decimal, val_real do not check cmp_type and
     decide data type according to the method type.
     This is probably not good:

     mysql> select least('11', '2'), least('11', '2')+0, concat(least(11,2));
     +------------------+--------------------+---------------------+
     | least('11', '2') | least('11', '2')+0 | concat(least(11,2)) |
     +------------------+--------------------+---------------------+
     | 11               |                  2 | 2                   |
     +------------------+--------------------+---------------------+
     1 row in set (0.00 sec)

     Should not the second column return 11?
     I.e. compare as strings and return '11', then convert to number.
   */
  for (uint i=0; i < arg_count ; i++)
  {
    if (i == 0)
      value=args[i]->val_int();
    else
    {
      longlong tmp=args[i]->val_int();
      if (!args[i]->null_value && (tmp < value ? cmp_sign : -cmp_sign) > 0)
        value=tmp;
    }
    if ((null_value= args[i]->null_value))
      break;
  }
  return value;
}


my_decimal *Item_func_min_max::val_decimal(my_decimal *dec)
{
  DBUG_ASSERT(fixed == 1);
  my_decimal tmp_buf, *tmp, *UNINIT_VAR(res);

  if (compare_as_dates)
  {
    longlong value= 0;
    (void)cmp_datetimes(&value);
    return my_decimal_from_datetime_packed(dec, datetime_item->field_type(),
        value);
  }
  for (uint i=0; i < arg_count ; i++)
  {
    if (i == 0)
      res= args[i]->val_decimal(dec);
    else
    {
      tmp= args[i]->val_decimal(&tmp_buf);      // Zero if NULL
      if (tmp && (my_decimal_cmp(tmp, res) * cmp_sign) < 0)
      {
        if (tmp == &tmp_buf)
        {
          /* Move value out of tmp_buf as this will be reused on next loop */
          my_decimal2decimal(tmp, dec);
          res= dec;
        }
        else
          res= tmp;
      }
    }
    if ((null_value= args[i]->null_value))
    {
      res= 0;
      break;
    }
  }

  if (res)
  {
    /*
       Need this to make val_str() always return fixed
       number of fractional digits, according to "decimals".
     */
    my_decimal_round(E_DEC_FATAL_ERROR, res, decimals, false, res);
  }
  return res;
}