I'm trying to add a GENERATED ALWAYS column to a table that will decode two characters into a DATE.

It contains a function (returning DATE), but it was misbehaving the same way when the expression was in-lined. The function works fine in a SELECT. I've checked my character set and collation for consistency.

sql_mode is "ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Here is the table schema. Note I have a Location generated column, which is working fine.

CREATE TABLE `Photo_Gear_Date_Codes` (
  `ID` varchar(4) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL DEFAULT '....',
  `Location` varchar(253) GENERATED ALWAYS AS (case left(`ID`,2) when 'TN' then 'Tatsuno, Hyogo' else 'Unknown' end) STORED,
  `Note` varchar(1022) COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Here is the function I defined to try to simplify things, although I got the same error when I in-lined this code:

CREATE DEFINER=`root`@`127.0.0.1`
FUNCTION `Date_from_OM_date_code`(id3 char(1), id4 char(1))
RETURNS date
DETERMINISTIC
COMMENT 'Decode the last two digits of a four-character OM date code.'
RETURN CONVERT(
    concat(
      case ID3
        when '1' then '1971'
        when '2' then '1972'
        when '3' then '1973'
        when '4' then '1974'
        when '5' then '1975'
        when '6' then '1976'
        when '7' then '1977'
        when '8' then '1978'
        when '9' then '1979'
        when '0' then '1980'
        when 'A' then '1981'
        when 'B' then '1982'
        when 'C' then '1983'
        when 'D' then '1984'
        when 'E' then '1985'
        when 'F' then '1986'
        when 'G' then '1987'
        when 'H' then '1988'
        when 'I' then '1989'
        when 'J' then '1990'
        when 'K' then '1991'
        when 'L' then '1992'
        when 'M' then '1993'
        when 'N' then '1994'
        when 'O' then '1995'
        when 'P' then '1996'
        when 'Q' then '1997'
        when 'R' then '1998'
        when 'S' then '1999'
        when 'T' then '2000'
        when 'U' then '2001'
        when 'V' then '2002'
        when 'W' then '2003'
        when 'X' then '2004'
        when 'Y' then '2005'
        when 'Z' then '2006'
        end,
      case ID4
        when '1' then '-01-00'
        when '2' then '-02-00'
        when '3' then '-03-00'
        when '4' then '-04-00'
        when '5' then '-05-00'
        when '6' then '-06-00'
        when '7' then '-07-00'
        when '8' then '-08-00'
        when '9' then '-09-00'
        when 'A' then '-10-00'
        when 'B' then '-11-00'
        when 'C' then '-12-00'
        when 'X' then '-10-00'
        when 'Y' then '-11-00'
        when 'Z' then '-12-00'
        end), DATE);

Here is how I'm trying to add the generated column:

USE `Personal`;
ALTER TABLE `Photo_Gear_Date_Codes` ADD COLUMN `Date` DATE AS 
  (Date_from_OM_date_code(SUBSTR(`ID`,3,1), RIGHT(`ID`,1))) STORED;
SELECT ID,
  `Location`,
  Date_from_OM_date_code(SUBSTR(`ID`,3,1), RIGHT(`ID`,1)) `Date`
  FROM Photo_Gear_Date_Codes

When I execute those three statements, the second throws the 1901 error code:

Function or expression 'Date_from_OM_date_code()' cannot be used in the GENERATED ALWAYS AS clause of Date

Error code 1901.

But I proceed through the error, and the SELECT works just fine!

It seems like it should work… I'm pulling my hair out over this one!

Thanks in advance for pointing out where I'm being stupid! :-)

CodePudding user response：

As mariadb's documentation on generated column says:

This expression might generate the value based on the values of other columns in the table, or it might generate the value by calling built-in functions or user-defined functions (UDFs).

UDFs are functions written in c , not in sql. As you can see, standard, sql-based user created functions are not included in the list, hence the error message.

Include your function's body as an expression as the generated column's code instead.