Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
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
|
Attachments
Issue Links
- relates to
-
MDEV-3929 Add system variable explicit_defaults_for_timestamp for compatibility with MySQL
-
- Closed
-
-
-
- Closed
-
- links to
Activity
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.
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.
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.
That's different. It's a timestamp behavior on NULL. If you'd try
you'd get "no default" error. If you'd set a default for your table, like
then NULL would still insert CURRENT_TIMESTAMP, while () would insert the default value.