[MDEV-28983] TIMESTAMP logic remains partly non-standard with explicit_defaults_for_timestamp Created: 2022-06-29  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: upstream-fixed

Issue Links:
Relates
relates to MDEV-3929 Add system variable explicit_defaults... Closed
relates to MDEV-28632 Change default of explicit_defaults_f... Closed

 Description   

Note: Even though this is an old known issue not specific to 10.10, I think it becomes much more important now when MDEV-28632 is about to be pushed into 10.10 main; so I'm marking it as 10.10V1 against the standard procedure. Please feel free to adjust both the marking and the priority after deciding what to do about it.

With explicit_defaults_for_timestamp, if a TIMESTAMP column is defined as NOT NULL without a default value, it still partially retains an implicit (only undeclared) logic and gets a current timestamp value if NULL is attempted to be inserted.

Run with --mysqld=--explicit-defaults-for-timestamp=on

CREATE TABLE t (a TIMESTAMP NOT NULL);
 
SHOW CREATE TABLE t;
 
 
 
INSERT INTO t VALUES (NULL);
 
SELECT * FROM t;
 
 
 
# Cleanup
 
DROP TABLE t;

10.3 efdbb3cf

 
CREATE TABLE t (a TIMESTAMP NOT NULL);
 
SHOW CREATE TABLE t;
 
Table	Create Table
 
t	CREATE TABLE `t` (
 
  `a` timestamp NOT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
INSERT INTO t VALUES (NULL);
 
SELECT * FROM t;
 
a
 
2022-06-30 00:34:21



 Comments   
Comment by Sergei Golubchik [ 2022-07-12 ]

That's different. It's a timestamp behavior on NULL. If you'd try

INSERT t1 () VALUES ();

you'd get "no default" error. If you'd set a default for your table, like

CREATE TABLE t (a TIMESTAMP NOT NULL DEFAULT 20201010020304);

then NULL would still insert CURRENT_TIMESTAMP, while () would insert the default value.

Comment by Elena Stepanova [ 2022-07-12 ]

Yes, I know. I didn't say it was a default, it's non-standard timestamp logic, while explicit_defaults_for_timestamp presumes making timestamps behave the same as other column types. Of course, in MariaDB KB the variable is barely documented at all, so one can argue that we didn't promise anything other than the change NULL and DEFAULT clauses, but in reality, the variable was introduced for compatibility with MySQL, and MySQL documentation is quite specific about this:

This system variable determines whether the server enables certain nonstandard behaviors for default values and NULL-value handling in TIMESTAMP columns.

The description shouldn't have had the "without a default value" note. It was meant to emphasize that it doesn't have DEFAULT current_timestamp(), but I see how it could have been misinterpreted.

Comment by Sergei Golubchik [ 2022-07-12 ]

I'd prefer explicit_defaults_for_timestamp to mean that one needs to specify DEFAULT clause for TIMESTAMP columns explicitly, otherwise they'll have no default value.

As such, it does not affect behavior on NULL in NOT NULL columns. One can even argue that it's a bug in MySQL that explicit_defaults_for_timestamp affects behavior completely unrelated to defaults. It should've been standard_timestamp or no_implicit_timestamp_magic or something.

Comment by Elena Stepanova [ 2022-07-12 ]

One can argue (and more than one argued in the past) that the original idea of weird incomprehensible non-default timestamp behavior – all aspects of it – is a bug in MySQL, which wasn't easy to fix due to years of legacy, so the variable was introduced as a temporary measure to make the transition smoother. Introducing two different variables for this would have been clearly an overkill.
Although one can't squeeze all subtleties into a variable name, yes, in the hindsight probably a better name would have been standard_behavior_for_timestamp or something, but it didn't happen. I don't think an imperfect variable name is a good enough reason to start another 10+ years of legacy with "almost standard behavior but not quite". Better to make it sensible once for all.

Generated at Thu Feb 08 10:04:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.