[MCOL-5035] MariaDB Columnstore all versions: update and inserts behave differently on 0000-00-00 00:00:00 datetime Created: 2022-03-29  Updated: 2024-01-12

Status: Confirmed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 23.02.3, 23.10.0
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: andreas eschbacher Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: dates
Environment:

Tested on 2 Systems:
1.) MariaDB 10.5.13 RHEL 8.5
2.) MariaDB 10.3.16 CentOS 7.6

Attachments: File zero_dates_columnstore_innodb.sql    
Issue Links:
Problem/Incident
is caused by MCOL-271 Improved support for NULL for Varchar... Closed

 Description   

Both on EBI03 (MariaDB 10.5.13) and EBI01 (MariaDB 10.3.16) the following bug was found on Columnstore tables:

an insert into a datetime field of '0000-00-00 00:00:00' stores it this way,
BUT an UPDATE on datetime field of '0000-00-00 00:00:00' stores it as <null>

whereas on InnoDB tables in both cases '0000-00-00 00:00:00' is stored.

See the attached SQL.

Note: the SQL_MODE options STRICT_TRANS_TABLES, ALLOW_INVALID_DATES, NO_ZERO_DATE, NO_ZERO_IN_DATE might have an impact.
However, on the same session with the same set of SQL_MODE settings, in any case both engines should behave the same.

 Comments   
Comment by David Hall (Inactive) [ 2022-10-13 ]

It is possible that this behavior will be affected by MCOL-271 which is in progress.
This test case should be run after MCOL-271 is complete to see if it fixed it.

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