Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10902

Inconsistant date behaviour and wrong comparison with '0000-00-00' date

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.0, 10.1
    • Fix Version/s: 10.0, 10.1
    • Component/s: None
    • Labels:
      None

      Description

      Hi,

      While comparing the date behaviour between MySQL 5.5 and MariaDB 10.x, I found some strange behaviours with date :

      SET SQL_MODE='';
      SELECT CAST('' AS DATE);
      +------------------+
      | CAST('' AS DATE) |
      +------------------+
      | NULL             |
      +------------------+
       
      SELECT CAST('' AS DATE)='0000-00-00';
      +-------------------------------+
      | CAST('' AS DATE)='0000-00-00' |
      +-------------------------------+
      |                             1 |
      +-------------------------------+
      

      NULL = '0000-00-00' ??

      SET SQL_MODE='NO_ZERO_DATE';
      Query OK, 0 rows affected (0.00 sec)
       
      SELECT CAST('' AS DATE)='0000-00-00';
      +-------------------------------+
      | CAST('' AS DATE)='0000-00-00' |
      +-------------------------------+
      |                          NULL |
      +-------------------------------+
      1 row in set, 2 warnings (0.01 sec)
      

      Why SQL_MODE is impacting the result here ?

      SELECT NOW() - INTERVAL '736598' DAY;
      +-------------------------------+
      | NOW() - INTERVAL '736598' DAY |
      +-------------------------------+
      | NULL                          |
      +-------------------------------+
      1 row in set, 1 warning (0.00 sec)
       
      SHOW WARNINGS;
      +---------+------+--------------------------------------------+
      | Level   | Code | Message                                    |
      +---------+------+--------------------------------------------+
      | Warning | 1441 | Datetime function: datetime field overflow |
      +---------+------+--------------------------------------------+
      1 row in set (0.00 sec)
      

      => Ok, overflow

      SELECT NOW() - INTERVAL '3212121' DAY;
      +--------------------------------+
      | NOW() - INTERVAL '3212121' DAY |
      +--------------------------------+
      | 6777-10-01 04:01:55            |
      +--------------------------------+
      1 row in set (0.00 sec)
      

      => no overflow ??

      DROP TABLE IF EXISTS test_datetime;
      CREATE TABLE test_datetime (`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `last_open_date` date NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB;
      INSERT INTO test_datetime (last_open_date) VALUES ('0000-00-00');
      SELECT COUNT(*) FROM test_datetime WHERE last_open_date='121';
      +----------+
      | COUNT(*) |
      +----------+
      |        1 |
      +----------+
      1 row in set, 1 warning (0.00 sec)
      MariaDB [test]> SHOW WARNINGS;
      +---------+------+---------------------------------+
      | Level   | Code | Message                         |
      +---------+------+---------------------------------+
      | Warning | 1292 | Incorrect datetime value: '121' |
      +---------+------+---------------------------------+
      1 row in set (0.00 sec)
      MariaDB [test]> SELECT COUNT(*) FROM test_datetime WHERE last_open_date=CAST('121' AS date);
      +----------+
      | COUNT(*) |
      +----------+
      |        0 |
      +----------+
      1 row in set, 1 warning (0.00 sec)
       
      MariaDB [test]> SELECT CAST('121' AS date);
      +---------------------+
      | CAST('121' AS date) |
      +---------------------+
      | NULL                |
      +---------------------+
      1 row in set, 1 warning (0.00 sec)
       
      MariaDB [test]> SELECT * FROM test_datetime WHERE last_open_date IS NULL;
      +----+----------------+
      | id | last_open_date |
      +----+----------------+
      |  1 | 0000-00-00     |
      +----+----------------+
      1 row in set (0.00 sec)
      

      Note because of this issue, we had to switch back to percona 5.6 which doesn't have this behaviour.

      Thanks and regards,
      Jocelyn Fournier

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                joce jocelyn fournier
              • Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated: