[#MDEV-4859] Wrong value and data type of "SELECT MAX(time

[MDEV-4859] Wrong value and data type of "SELECT MAX(time_column) + 1 FROM t1" Created: 2013-08-08 Updated: 2014-01-29 Resolved: 2013-11-26
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	10.0.3, 5.5.32, 5.3.12
Fix Version/s:	5.5.35, 10.0.7

Type:

Bug

Priority:

Minor

Reporter:

Alexander Barkov

Assignee:

Alexander Barkov

Resolution:

Fixed

Votes:

Labels:

None

Description

DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (t0 TIME);

INSERT INTO t1 VALUES ('10:10:10');

CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1; SELECT * FROM t2; SHOW COLUMNS FROM t2;

produces this output in MariaDB-5.5 and MariaDB-10.0:

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

| MAX(t0)+1   |

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

| 36610000001 |

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

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

| Field     | Type          | Null | Key | Default | Extra |

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

| MAX(t0)+1 | decimal(11,0) | YES  |     | NULL    |       |

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

36610000001 is a wrong value. The expected value is 101011.

DECIMAL(11,0) is a wrong type. The expected type is INT(9).

Note, MariaDB-5.3 returns 11 instead of 36610000001, which is also wrong.

Similar problem is observed with a DATETIME column.
This script:

CREATE TABLE t1 (t0 DATETIME);

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

CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1;

SELECT * FROM t2;

SHOW COLUMNS FROM t2;

produces this output:

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

| MAX(t0)+1         |

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

| 71923630210000001 |

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

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

| Field     | Type          | Null | Key | Default | Extra |

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

| MAX(t0)+1 | decimal(20,0) | YES  |     | NULL    |       |

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

Comments

Comment by Alexander Barkov [ 2013-11-26 ]

Pushed into 5.3, 5.5, 10.0-base.

Comment by Daniel Bartholomew [ 2014-01-29 ]

Generated at Thu Feb 08 06:59:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.