Search code examples
sqldatemariadb

Column of "date" dataType, inserts null value, while inserting value between 0 to 1.. eg 0.08, 0.99 etc

CREATE TABLE `datesissue` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dates` date DEFAULT NULL,
  `user` varchar(64) COLLATE utf8mb3_bin NOT NULL,
  `modified_user` varchar(64) COLLATE utf8mb3_bin NOT NULL,
  `created_date` datetime NOT NULL DEFAULT current_timestamp(),
  `modified_date` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `system_data_source` varchar(512) COLLATE utf8mb3_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin



insert into datesissue (dates,user, modified_user) values(0.08,"abc","xyz")

update datesissue set dates = 0.00020596473883443116 where id = 1

I expect Database to throw me an error stating

Incorrect date value: for column datesissue.dates at row 1

Solution

  • MariaDB preserved the backwards compatibility by default which means invalid dates where accepted.

    If you set the SQL_MODE to contain NO_ZERO_DATE (and NO_ZERO_IN_DATE while you are at it), a more expected behaviour of an error will occur:

    MariaDB [test]> set statement sql_mode=concat(@@sql_mode, ",NO_ZERO_DATE,NO_ZERO_IN_DATE") for update datesissue set dates =
0.20596473883443116;
ERROR 1292 (22007): Incorrect date value: '0.20596473883443116' for column `test`.`datesissue`.`dates` at row 1