[MDEV-27324] Close-to-zero timestamps are converted into zero upon aggregation Created: 2021-12-20  Updated: 2023-03-03

Status: Open
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None


 Description   

The block below actually contains two test cases, with different engines and somewhat different queries. Choose whichever is more representative, or both.

For nearly-zero timestamps (1970-01-01 00:00:00 with milliseconds), certain queries return 0000-00-00 instead.

--source include/have_innodb.inc
 
 
 
SET TIME_ZONE='+00:00';
 
 
 
CREATE OR REPLACE TABLE t (b TIMESTAMP(2)) ENGINE=MyISAM;
 
INSERT INTO t VALUES
 
  ('1970-01-01 00:00:00.01'),
 
  ('1985-01-23 06:27:59.00');
 
 
 
SELECT MIN(b) FROM t;
 
 
 
CREATE OR REPLACE TABLE t (a INT, b TIMESTAMP(2), KEY idx(a)) ENGINE=InnoDB;
 
INSERT INTO t VALUES
 
  (1,'1970-01-01 00:00:00.01'),
 
  (2,'1985-01-23 06:27:59.00');
 
 
 
SELECT MIN(b), a FROM t GROUP BY a;
 
SELECT MIN(b), a FROM t IGNORE INDEX (idx) GROUP BY a;
 
 
 
DROP TABLE t;

10.4 20f22dfa

 
SELECT MIN(b) FROM t;
 
MIN(b)
 
0000-00-00 00:00:00.00


SELECT MIN(b), a FROM t GROUP BY a;
 
MIN(b)	a
 
0000-00-00 00:00:00.00	1
 
1985-01-23 06:27:59.00	2
 
SELECT MIN(b), a FROM t IGNORE INDEX (idx) GROUP BY a;
 
MIN(b)	a
 
1970-01-01 00:00:00.01	1
 
1985-01-23 06:27:59.00	2

Reproducible on 10.4+, with at least InnoDB and MyISAM, but it depends on the query/data.
Not reproducible on 102-10.3.
Generated at Thu Feb 08 09:52:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.