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

TIMESTAMP has 2-level logic for default values

    XMLWordPrintable

Details

    Description

      I don't know what's the expected behavior for co-existing old non-standard TIMESTAMP logic and new MDEV-10134 (full support for DEFAULTs). Maybe it's just a documentation issue, but if it is, it needs to be documented, because it has become even more confusing than it had been before.

      MariaDB [test]> show variables like '%explicit%';
      +---------------------------------+-------+
      | Variable_name                   | Value |
      +---------------------------------+-------+
      | explicit_defaults_for_timestamp | OFF   |
      +---------------------------------+-------+
      1 row in set (0.01 sec)
      

      MariaDB [test]> create table t1 (i int, t timestamp not null default adddate(current_timestamp, interval i day));
      Query OK, 0 rows affected (0.44 sec)
       
      MariaDB [test]> show create table t1 \G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `i` int(11) DEFAULT NULL,
        `t` timestamp NOT NULL DEFAULT (adddate(current_timestamp, interval i day))
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.00 sec)
      

      Normally, if interval is NULL, the function would return NULL:

      MariaDB [test]> select adddate(current_timestamp, interval NULL day);
      +-----------------------------------------------+
      | adddate(current_timestamp, interval NULL day) |
      +-----------------------------------------------+
      | NULL                                          |
      +-----------------------------------------------+
      1 row in set (0.00 sec)
      

      However, the timestamp gets a non-null default:

      MariaDB [test]> insert into t1 (i) values (null);
      Query OK, 1 row affected (0.09 sec)
       
      MariaDB [test]> select * from t1;
      +------+---------------------+
      | i    | t                   |
      +------+---------------------+
      | NULL | 2016-10-26 01:00:40 |
      +------+---------------------+
      1 row in set (0.00 sec)
      

      It does not happen with DATETIME, so I assume the reason is the old TIMESTAMP magic.

      MariaDB [test]> create table t1 (i int, t datetime not null default adddate(current_timestamp, interval i day));
      Query OK, 0 rows affected (0.41 sec)
       
      MariaDB [test]> insert into t1 (i) values (null);
      ERROR 1048 (23000): Column 't' cannot be null
      

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.