Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2(EOL)
-
None
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
-
MDEV-12894 System-versioned tables
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 10.3 [ 22126 ] |
| 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. |
| 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. |
| Link |
This issue is part of |
| Fix Version/s | 10.3 [ 22126 ] |
| Fix Version/s | 10.4 [ 22408 ] |
| Workflow | MariaDB v3 [ 84245 ] | MariaDB v4 [ 140554 ] |