[MDEV-33496] Out of range error in AVG(YEAR(datetime)) due to a wrong data type - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4, 11.5(EOL)
Fix Version/s: 10.5.25, 10.11.8, 10.6.18, 11.0.6, 11.1.5, 11.2.4, 11.4.2
Component/s: Data types, Temporal Types
Labels:
None

Description

I run this script:

CREATE OR REPLACE TABLE t1 (a DATETIME);

INSERT INTO t1 VALUES ('2001-01-01 10:20:30');

CREATE OR REPLACE TABLE t2 AS SELECT AVG(YEAR(a)) FROM t1;

It unexpectedly returns this error:

ERROR 1264 (22003): Out of range value for column 'AVG(YEAR(a))' at row 2

If I add LIMIT 0, the table gets created:

CREATE OR REPLACE TABLE t2 AS SELECT AVG(YEAR(a)) FROM t1 LIMIT 0;

SHOW CREATE TABLE t2;

+-------+---------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                    |

+-------+---------------------------------------------------------------------------------------------------------------------------------+

| t2    | CREATE TABLE `t2` (

  `AVG(YEAR(a))` decimal(7,4) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |

+-------+---------------------------------------------------------------------------------------------------------------------------------+

However, the column size is too small: a DECIMAL(7,4) can store up to 3 integer digits only.
The expected column type is DECIMAL(8,4).

Attachments

Issue Links

blocks

MCOL-5241 MariaDB Columnstore produces wrong averages on extracted null-datetime fields (like year)

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov added a comment - 2024-02-20 12:07

A few other functions have the same problem:

CREATE OR REPLACE TABLE t1 (a DATETIME);

INSERT INTO t1 VALUES ('2001-12-31 10:20:30');

CREATE OR REPLACE TABLE t2 AS SELECT AVG(MONTH(a)) FROM t1;

ERROR 1264 (22003): Out of range value for column 'AVG(MONTH(a))' at row 2

CREATE OR REPLACE TABLE t1 (a DATETIME);

INSERT INTO t1 VALUES ('2001-12-31 10:20:30');

CREATE OR REPLACE TABLE t2 AS SELECT AVG(DAY(a)) FROM t1;

ERROR 1264 (22003): Out of range value for column 'AVG(DAY(a))' at row 2

CREATE OR REPLACE TABLE t1 (a DATETIME);

INSERT INTO t1 VALUES ('2001-12-31 10:20:30');

CREATE OR REPLACE TABLE t2 AS SELECT AVG(DAYOFYEAR(a)) FROM t1;

ERROR 1264 (22003): Out of range value for column 'AVG(DAYOFYEAR(a))' at row 2

CREATE OR REPLACE TABLE t1 (a DATETIME);

INSERT INTO t1 VALUES ('2001-12-31 10:20:30');

CREATE OR REPLACE TABLE t2 AS SELECT AVG(WEEK(a)) FROM t1;

ERROR 1264 (22003): Out of range value for column 'AVG(WEEK(a))' at row 2

CREATE OR REPLACE TABLE t1 (a DATETIME);

INSERT INTO t1 VALUES ('2001-12-31 10:20:30');

CREATE OR REPLACE TABLE t2 AS SELECT AVG(HOUR(a)) FROM t1;

ERROR 1264 (22003): Out of range value for column 'AVG(HOUR(a))' at row 2

CREATE OR REPLACE TABLE t1 (a DATETIME);

INSERT INTO t1 VALUES ('2001-12-31 10:20:30');

CREATE OR REPLACE TABLE t2 AS SELECT AVG(MINUTE(a)) FROM t1;

ERROR 1264 (22003): Out of range value for column 'AVG(MINUTE(a))' at row 2

CREATE OR REPLACE TABLE t1 (a DATETIME);

INSERT INTO t1 VALUES ('2001-12-31 10:20:30');

CREATE OR REPLACE TABLE t2 AS SELECT AVG(SECOND(a)) FROM t1;

ERROR 1264 (22003): Out of range value for column 'AVG(SECOND(a))' at row 2

CREATE OR REPLACE TABLE t1 (a DATETIME(6));

INSERT INTO t1 VALUES ('2001-12-31 10:20:30.999999');

CREATE OR REPLACE TABLE t2 AS SELECT AVG(MICROSECOND(a)) FROM t1;

ERROR 1264 (22003): Out of range value for column 'AVG(MICROSECOND(a))' at row 2

Alexander Barkov added a comment - 2024-02-20 12:07 A few other functions have the same problem: CREATE OR REPLACE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ( '2001-12-31 10:20:30' ); CREATE OR REPLACE TABLE t2 AS SELECT AVG ( MONTH (a)) FROM t1; ERROR 1264 (22003): Out of range value for column 'AVG(MONTH(a))' at row 2 CREATE OR REPLACE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ( '2001-12-31 10:20:30' ); CREATE OR REPLACE TABLE t2 AS SELECT AVG ( DAY (a)) FROM t1; ERROR 1264 (22003): Out of range value for column 'AVG(DAY(a))' at row 2 CREATE OR REPLACE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ( '2001-12-31 10:20:30' ); CREATE OR REPLACE TABLE t2 AS SELECT AVG (DAYOFYEAR(a)) FROM t1; ERROR 1264 (22003): Out of range value for column 'AVG(DAYOFYEAR(a))' at row 2 CREATE OR REPLACE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ( '2001-12-31 10:20:30' ); CREATE OR REPLACE TABLE t2 AS SELECT AVG (WEEK(a)) FROM t1; ERROR 1264 (22003): Out of range value for column 'AVG(WEEK(a))' at row 2 CREATE OR REPLACE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ( '2001-12-31 10:20:30' ); CREATE OR REPLACE TABLE t2 AS SELECT AVG ( HOUR (a)) FROM t1; ERROR 1264 (22003): Out of range value for column 'AVG(HOUR(a))' at row 2 CREATE OR REPLACE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ( '2001-12-31 10:20:30' ); CREATE OR REPLACE TABLE t2 AS SELECT AVG ( MINUTE (a)) FROM t1; ERROR 1264 (22003): Out of range value for column 'AVG(MINUTE(a))' at row 2 CREATE OR REPLACE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ( '2001-12-31 10:20:30' ); CREATE OR REPLACE TABLE t2 AS SELECT AVG ( SECOND (a)) FROM t1; ERROR 1264 (22003): Out of range value for column 'AVG(SECOND(a))' at row 2 CREATE OR REPLACE TABLE t1 (a DATETIME(6)); INSERT INTO t1 VALUES ( '2001-12-31 10:20:30.999999' ); CREATE OR REPLACE TABLE t2 AS SELECT AVG (MICROSECOND(a)) FROM t1; ERROR 1264 (22003): Out of range value for column 'AVG(MICROSECOND(a))' at row 2

Alexander Barkov added a comment - 2024-02-20 12:11

Some EXTRACT(unit FROM dt) have the same problem:

CREATE OR REPLACE TABLE t1 (a DATETIME);

INSERT INTO t1 VALUES ('2001-12-31 10:20:30');

CREATE OR REPLACE TABLE t2 AS SELECT AVG(EXTRACT(YEAR FROM a)) FROM t1;

ERROR 1264 (22003): Out of range value for column 'AVG(EXTRACT(YEAR FROM a))' at row 2

CREATE OR REPLACE TABLE t1 (a DATETIME);

INSERT INTO t1 VALUES ('2001-12-31 10:20:30');

CREATE OR REPLACE TABLE t2 AS SELECT AVG(EXTRACT(MONTH FROM a)) FROM t1;

ERROR 1264 (22003): Out of range value for column 'AVG(EXTRACT(MONTH FROM a))' at row 2

Alexander Barkov added a comment - 2024-02-20 12:11 Some EXTRACT(unit FROM dt) have the same problem: CREATE OR REPLACE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ( '2001-12-31 10:20:30' ); CREATE OR REPLACE TABLE t2 AS SELECT AVG (EXTRACT( YEAR FROM a)) FROM t1; ERROR 1264 (22003): Out of range value for column 'AVG(EXTRACT(YEAR FROM a))' at row 2 CREATE OR REPLACE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ( '2001-12-31 10:20:30' ); CREATE OR REPLACE TABLE t2 AS SELECT AVG (EXTRACT( MONTH FROM a)) FROM t1; ERROR 1264 (22003): Out of range value for column 'AVG(EXTRACT(MONTH FROM a))' at row 2

Alexander Barkov added a comment - 2024-02-22 02:27

The same problem is repeatable with all these statements:

EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING YEAR(TIMESTAMP'2001-12-31 10:20:30');

EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING QUARTER(TIMESTAMP'2001-12-31 10:20:30');

EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING MONTH(TIMESTAMP'2001-12-31 10:20:30');

EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING WEEK(TIMESTAMP'2001-12-31 10:20:30');

EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING DAY(TIMESTAMP'2001-12-31 10:20:30');

EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING DAYOFYEAR(TIMESTAMP'2001-12-31 10:20:30');

EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING HOUR(TIMESTAMP'2001-12-31 10:20:30');

EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING MINUTE(TIMESTAMP'2001-12-31 10:20:30');

EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING SECOND(TIMESTAMP'2001-12-31 10:20:30');

EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING MICROSECOND(TIMESTAMP'2001-12-31 10:20:30');

EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING MICROSECOND(TIMESTAMP'2001-12-31 10:20:30.999999');

Alexander Barkov added a comment - 2024-02-22 02:27 The same problem is repeatable with all these statements: EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING YEAR ( TIMESTAMP '2001-12-31 10:20:30' ); EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING QUARTER( TIMESTAMP '2001-12-31 10:20:30' ); EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING MONTH ( TIMESTAMP '2001-12-31 10:20:30' ); EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING WEEK( TIMESTAMP '2001-12-31 10:20:30' ); EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING DAY ( TIMESTAMP '2001-12-31 10:20:30' ); EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING DAYOFYEAR( TIMESTAMP '2001-12-31 10:20:30' ); EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING HOUR ( TIMESTAMP '2001-12-31 10:20:30' ); EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING MINUTE ( TIMESTAMP '2001-12-31 10:20:30' ); EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING SECOND ( TIMESTAMP '2001-12-31 10:20:30' ); EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING MICROSECOND( TIMESTAMP '2001-12-31 10:20:30' ); EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t2 AS SELECT AVG(?)' USING MICROSECOND( TIMESTAMP '2001-12-31 10:20:30.999999' );

MariaDB Server

Out of range error in AVG(YEAR(datetime)) due to a wrong data type

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration