[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 created issue - 2024-02-20 09:15

Alexander Barkov made changes - 2024-02-20 09:16

Field	Original Value	New Value
Description	I run this script: {code:sql} 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; {code} In unexpectedly returns this error: {noformat} ERROR 1264 (22003): Out of range value for column 'AVG(YEAR(a))' at row 2 {noformat} If I add LIMIT 0, the table gets created: {code:sql} CREATE OR REPLACE TABLE t2 AS SELECT AVG(YEAR(a)) FROM t1 LIMIT 0; SHOW CREATE TABLE t2; {code} {noformat} +-------+---------------------------------------------------------------------------------------------------------------------------------+ \| Table \| Create Table \| +-------+---------------------------------------------------------------------------------------------------------------------------------+ \| t2 \| CREATE TABLE `t2` ( `AVG(YEAR(a))` decimal(7,4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci \| +-------+---------------------------------------------------------------------------------------------------------------------------------+ {noformat} However, the column size is too small: a DECIMAL(7,4) can store up to 3 integer digits. The expected column type is DECIMAL(8,4).	I run this script: {code:sql} 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; {code} It unexpectedly returns this error: {noformat} ERROR 1264 (22003): Out of range value for column 'AVG(YEAR(a))' at row 2 {noformat} If I add LIMIT 0, the table gets created: {code:sql} CREATE OR REPLACE TABLE t2 AS SELECT AVG(YEAR(a)) FROM t1 LIMIT 0; SHOW CREATE TABLE t2; {code} {noformat} +-------+---------------------------------------------------------------------------------------------------------------------------------+ \| Table \| Create Table \| +-------+---------------------------------------------------------------------------------------------------------------------------------+ \| t2 \| CREATE TABLE `t2` ( `AVG(YEAR(a))` decimal(7,4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci \| +-------+---------------------------------------------------------------------------------------------------------------------------------+ {noformat} 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).

Leonid Fedorov made changes - 2024-02-20 09:35

Fix Version/s

10.6 [ 24028 ]

Leonid Fedorov made changes - 2024-02-20 09:36

Link

This issue blocks ~~MCOL-5241~~ [ ~~MCOL-5241~~ ]

Alexander Barkov made changes - 2024-02-20 09:55

Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.7 [ 24805 ]
Affects Version/s		10.8 [ 26121 ]
Affects Version/s		10.9 [ 26905 ]
Affects Version/s		10.10 [ 27530 ]
Affects Version/s		10.11 [ 27614 ]
Affects Version/s		11.0 [ 28320 ]
Affects Version/s		11.1 [ 28549 ]
Affects Version/s		11.2 [ 28603 ]
Affects Version/s		11.3 [ 28565 ]
Affects Version/s		11.4 [ 29301 ]

Alexander Barkov made changes - 2024-02-20 11:42

Summary

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

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

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' );

Alexander Barkov made changes - 2024-02-26 11:22

Fix Version/s		10.5.25 [ 29626 ]
Fix Version/s		10.6.18 [ 29627 ]
Fix Version/s		10.11.8 [ 29630 ]
Fix Version/s		11.0.6 [ 29628 ]
Fix Version/s		11.1.5 [ 29629 ]
Fix Version/s		11.2.4 [ 29631 ]
Fix Version/s		11.4.2 [ 29633 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2024-02-20 09:15

Updated:: 2024-02-26 11:22

Resolved:: 2024-02-26 11:22

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server