[MCOL-274] DATETIME field doesn't match with MySQL standard Created: 2016-08-25  Updated: 2017-12-01  Resolved: 2016-09-13

Status: Closed
Project: MariaDB ColumnStore
Component/s: DDLProc, DMLProc, PrimProc
Affects Version/s: 1.0.2
Fix Version/s: 1.0.3

Type: New Feature Priority: Minor
Reporter: Andrew Ernst Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
PartOf
is part of MCOL-280 Beta issues Closed
Problem/Incident
causes MCOL-283 calc_mysql_week causes PrimProc to se... Closed
Sprint: 1.0.3

 Description   

This isn't a dealbreaker, by any means – but I'm curious why ColumnStore restricts the range by 400 years?

ColumnStore supports:

A date and time combination. Supported range is 1400-01-01 00:00:00 to 9999-12-31 23:59:59.

That differs from MySQL 5.5+ which supports

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

ColumnStore reports (when attempting a value out of range):

ERROR 1815 (HY000) at line 306: Internal error: The default value is out of range for the specified data type.



 Comments   
Comment by Dipti Joshi (Inactive) [ 2016-08-25 ]

David.Hall, LinuxJedi Can you please see if this is something quick to fix ?

Comment by Andrew Hutchings (Inactive) [ 2016-08-25 ]

To answer ernstae's question: the date validation routine used is supplied by "Boost" which imposes those limits. I already have a fix I'm working on (as part of my MCOL-171 fix) that fixes this so that the limits are more in-line with MariaDB's

The reason why Boost uses year 1400 is because Gregorian calendar dates start at year 1582 and I believe year 1400 is an arbitrary date before that.

Comment by Andrew Hutchings (Inactive) [ 2016-08-25 ]

complete fix is now in https://github.com/LinuxJedi/mariadb-columnstore-engine/tree/MCOL-271. If I can't finish the fix for the finial MCOL-271 issue early tomorrow I'll split out the MCOL-274 fix and put that up for review separately.

Comment by David Hall (Inactive) [ 2016-08-26 ]

While desirable, this fix may break a number of our regression tests and could require a number of changes there. Some tests are testing the minimum date threshold.

Comment by Andrew Hutchings (Inactive) [ 2016-08-30 ]

Pull request created. Extracted from my other patches.

https://github.com/mariadb-corporation/mariadb-columnstore-engine/pull/4

Comment by Andrew Hutchings (Inactive) [ 2016-08-30 ]

Requesting review. I think Hall is right about the test suite.

Comment by David Hall (Inactive) [ 2016-09-02 ]

Test and close

Comment by Daniel Lee (Inactive) [ 2016-09-13 ]

Build verified:

Name : mariadb-columnstore-platform
Version : 1.0.3
Release : 1
Architecture: x86_64
Install Date: Tue 13 Sep 2016 09:26:32 AM CDT

Verified the following:
insert
update
LDI with cpimport
LDI without cpimport
cpimport

Generated at Thu Feb 08 02:19:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.