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

inconsistent NOW behavior DEFAULT/ASOF

Details

    Description

      CREATE TABLE t1 (
      		 
        a TIMESTAMP(6) DEFAULT NOW(),
      		 
        b TIMESTAMP(6) DEFAULT NOW(0),
      		 
        c TIMESTAMP(6) DEFAULT (NOW() + INTERVAL 1 DAY)
      		 
      );
      		 
      SHOW CREATE TABLE t1;

      first column: NOW() becomes NOW(6), second column: NOW(0) becomes NOW(6), third column: NOW() becomes NOW(0). Same with CURRENT_TIMESTAMP, same in AS OF.

      Suggested solution: implement sql standard behavior, where CURRENT_TIMESTAMP() means CURRENT_TIMESTAMP(6). According to SQL:2016, Part 2, Section 6.1 <data type>, Syntax Rules:

      36) If <time precision> is not specified, then 0 (zero) is implicit. If <timestamp precision> is not specified, then 6 is implicit.

      Perhaps we'll do an old_mode setting of DEFAULT_NOW_0 to let users to revert to the old behavior temporarily.

      Attachments

        Issue Links

          is part of

          Task - A task that needs to be done. MDEV-12894 System-versioned tables

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            Fix Version/s 10.3 [ 22126 ]
            serg Sergei Golubchik made changes -
            Description {code:sql}
            CREATE TABLE t1 (
              a TIMESTAMP(6) DEFAULT NOW(),
              b TIMESTAMP(6) DEFAULT NOW(0),
              c TIMESTAMP(6) DEFAULT (NOW() + INTERVAL 1 DAY)
            );
            SHOW CREATE TABLE t1;
            {code}

            first column: {{NOW()}} becomes {{NOW(6)}}, second column: {{NOW(0)}} becomes {{NOW(6)}}, third column: {{NOW()}} becomes {{NOW(0)}}. Same with {{CURRENT_TIMESTAMP}}, same with {{AS OF}}.

            *Suggested solution:* implement sql standard behavior, where {{CURRENT_TIMESTAMP()}} means {{CURRENT_TIMESTAMP(6)}}. According to SQL:2016, Part 2, Section 6.1 <data type>, Syntax Rules:
            bq. 36) If <time precision> is not specified, then 0 (zero) is implicit. If <timestamp precision> is not specified, then 6 is implicit.

            Perhaps we'll do an {{old_mode}} setting of {{DEFAULT_NOW_0}} to let users to revert to the old behavior temporarily.
            serg Sergei Golubchik made changes -
            Description {code:sql}
            CREATE TABLE t1 (
              a TIMESTAMP(6) DEFAULT NOW(),
              b TIMESTAMP(6) DEFAULT NOW(0),
              c TIMESTAMP(6) DEFAULT (NOW() + INTERVAL 1 DAY)
            );
            SHOW CREATE TABLE t1;
            {code}

            first column: {{NOW()}} becomes {{NOW(6)}}, second column: {{NOW(0)}} becomes {{NOW(6)}}, third column: {{NOW()}} becomes {{NOW(0)}}. Same with {{CURRENT_TIMESTAMP}}, same with {{AS OF}}.

            *Suggested solution:* implement sql standard behavior, where {{CURRENT_TIMESTAMP()}} means {{CURRENT_TIMESTAMP(6)}}. According to SQL:2016, Part 2, Section 6.1 <data type>, Syntax Rules:
            bq. 36) If <time precision> is not specified, then 0 (zero) is implicit. If <timestamp precision> is not specified, then 6 is implicit.

            Perhaps we'll do an {{old_mode}} setting of {{DEFAULT_NOW_0}} to let users to revert to the old behavior temporarily.             		{code:sql}
            CREATE TABLE t1 (
              a TIMESTAMP(6) DEFAULT NOW(),
              b TIMESTAMP(6) DEFAULT NOW(0),
              c TIMESTAMP(6) DEFAULT (NOW() + INTERVAL 1 DAY)
            );
            SHOW CREATE TABLE t1;
            {code}

            first column: {{NOW()}} becomes {{NOW(6)}}, second column: {{NOW(0)}} becomes {{NOW(6)}}, third column: {{NOW()}} becomes {{NOW(0)}}. Same with {{CURRENT_TIMESTAMP}}, same in {{AS OF}}.

            *Suggested solution:* implement sql standard behavior, where {{CURRENT_TIMESTAMP()}} means {{CURRENT_TIMESTAMP(6)}}. According to SQL:2016, Part 2, Section 6.1 <data type>, Syntax Rules:
            bq. 36) If <time precision> is not specified, then 0 (zero) is implicit. If <timestamp precision> is not specified, then 6 is implicit.

            Perhaps we'll do an {{old_mode}} setting of {{DEFAULT_NOW_0}} to let users to revert to the old behavior temporarily.
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3 [ 22126 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.4 [ 22408 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 84245 ] MariaDB v4 [ 140554 ]

            People

              Unassigned Unassigned
              serg Sergei Golubchik
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.