• Alexander Barkov's avatar
    MDEV-33496 Out of range error in AVG(YEAR(datetime)) due to a wrong data type · e63311c2
    Alexander Barkov authored
    Functions extracting non-negative datetime components:
    
    - YEAR(dt),        EXTRACT(YEAR FROM dt)
    - QUARTER(td),     EXTRACT(QUARTER FROM dt)
    - MONTH(dt),       EXTRACT(MONTH FROM dt)
    - WEEK(dt),        EXTRACT(WEEK FROM dt)
    - HOUR(dt),
    - MINUTE(dt),
    - SECOND(dt),
    - MICROSECOND(dt),
    - DAYOFYEAR(dt)
    - EXTRACT(YEAR_MONTH FROM dt)
    
    did not set their max_length properly, so in the DECIMAL
    context they created a too small DECIMAL column, which
    led to the 'Out of range value' error.
    
    The problem is that most of these functions historically
    returned the signed INT data type.
    
    There were two simple ways to fix these functions:
    1. Add +1 to max_length.
       But this would also change their size in the string context
       and create too long VARCHAR columns, with +1 excessive size.
    
    2. Preserve max_length, but change the data type from INT to INT UNSIGNED.
       But this would break backward compatibility.
       Also, using UNSIGNED is generally not desirable,
       it's better to stay with signed when possible.
    
    This fix implements another solution, which it makes all these functions
    work well in all contexts: int, decimal, string.
    
    Fix details:
    
    - Adding a new special class Type_handler_long_ge0 - the data type
      handler for expressions which:
      * should look like normal signed INT
      * but which known not to return negative values
      Expressions handled by Type_handler_long_ge0 store in Item::max_length
      only the number of digits, without adding +1 for the sign.
    
    - Fixing Item_extract to use Type_handler_long_ge0
      for non-negative datetime components:
       YEAR, YEAR_MONTH, QUARTER, MONTH, WEEK
    
    - Adding a new abstract class Item_long_ge0_func, for functions
      returning non-negative datetime components.
      Item_long_ge0_func uses Type_handler_long_ge0 as the type handler.
      The class hierarchy now looks as follows:
    
    Item_long_ge0_func
      Item_long_func_date_field
        Item_func_to_days
        Item_func_dayofmonth
        Item_func_dayofyear
        Item_func_quarter
        Item_func_year
      Item_long_func_time_field
        Item_func_hour
        Item_func_minute
        Item_func_second
        Item_func_microsecond
    
    - Cleanup: EXTRACT(QUARTER FROM dt) created an excessive VARCHAR column
      in string context. Changing its length from 2 to 1.
    e63311c2
func_extract.result 78.3 KB