CREATE TABLE AS SELECT AVG(TO_DAYS()/TO_SECONDS()/PERIOD_ADD()/YEARWEEK()) truncates because max_length under-counts decimal precision

SET sql_mode='';

DROP DATABASE IF EXISTS mdev_avg_todays;

CREATE DATABASE mdev_avg_todays;

USE mdev_avg_todays;

CREATE TABLE t(a DATE);

INSERT INTO t VALUES ('9999-12-31'),('9999-12-30'),('9999-12-29'),('9999-12-28');

SELECT AVG(TO_DAYS(a)) AS live FROM t;

-- live: 3652422.5000

CREATE TABLE ct AS SELECT AVG(TO_DAYS(a)) AS v FROM t;

SHOW CREATE TABLE ct;

SELECT * FROM ct;

-- v decimal(10,4); v=999999.9999  (WRONG)

CREATE TABLE t2(a DATETIME);

INSERT INTO t2 VALUES

  ('9999-12-31 23:59:59'),('9999-12-30 00:00:00'),('9999-12-29 12:00:00');

SELECT AVG(TO_SECONDS(a)) AS live FROM t2;

CREATE TABLE cts AS SELECT AVG(TO_SECONDS(a)) AS v FROM t2;

SHOW CREATE TABLE cts; SELECT * FROM cts;

-- live ~315569390399.6667; CTAS v=99999999999.9999 (WRONG)

CREATE TABLE t3(a INT); INSERT INTO t3 VALUES (1),(2),(3);

SELECT AVG(PERIOD_ADD(200001,99999)) AS live FROM t3;

CREATE TABLE ctp AS SELECT AVG(PERIOD_ADD(200001,99999)) AS v FROM t3;

SHOW CREATE TABLE ctp; SELECT * FROM ctp;

-- live 1033304; CTAS 99999.9999 (WRONG)

CREATE TABLE d(a DATE);

INSERT INTO d VALUES ('9999-12-31'),('9999-12-30'),('9999-01-01');

SELECT AVG(YEARWEEK(a)) AS live FROM d;

CREATE TABLE cty AS SELECT AVG(YEARWEEK(a)) AS v FROM d;

SHOW CREATE TABLE cty; SELECT * FROM cty;

-- live ~999918.6667; CTAS 99999.9999 (WRONG)

-- Control: YEAR already fixed (MDEV-33496)

SELECT AVG(YEAR(a)) AS live FROM t;

CREATE TABLE ctyear AS SELECT AVG(YEAR(a)) AS v FROM t;

SHOW CREATE TABLE ctyear; SELECT * FROM ctyear;

-- decimal(8,4); 9999.0000 OK