[MDEV-29075] Changing explicit_defaults_for_timestamp within stored procedure works inconsistently Created: 2022-07-09  Updated: 2022-08-02  Resolved: 2022-08-02

Status: Closed
Project: MariaDB Server
Component/s: Stored routines, Variables
Affects Version/s: N/A
Fix Version/s: 10.5.17, 10.6.9, 10.7.5, 10.8.4, 10.9.2

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: upstream

Issue Links:
Blocks
is blocked by MDEV-29206 DEFAULT NULL for NOT NULL columns is ... Closed
Problem/Incident
is caused by MDEV-28632 Change default of explicit_defaults_f... Closed

 Description    

--delimiter $
 
create procedure pr1()
 
begin
 
  set explicit_defaults_for_timestamp= off;
 
  create table t1 (ts timestamp);
 
end $
 
create procedure pr2()
 
begin
 
  set explicit_defaults_for_timestamp= on;
 
  create table t2 (ts timestamp);
 
end $
 
--delimiter ;
 
 
 
set explicit_defaults_for_timestamp= on;
 
call pr1();
 
select @@explicit_defaults_for_timestamp;
 
show create table t1;
 
 
 
set explicit_defaults_for_timestamp= off;
 
call pr2();
 
select @@explicit_defaults_for_timestamp;
 
show create table t2;
 
 
 
# Cleanup
 
drop procedure pr1;
 
drop procedure pr2;
 
drop table t1, t2;

bb-10.10-MDEV-28632 c4407143a2

 
set explicit_defaults_for_timestamp= on;
 
call pr1();
 
select @@explicit_defaults_for_timestamp;
 
@@explicit_defaults_for_timestamp
 
0
 
show create table t1;
 
Table	Create Table
 
t1	CREATE TABLE `t1` (
 
  `ts` timestamp NULL DEFAULT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
set explicit_defaults_for_timestamp= off;
 
call pr2();
 
select @@explicit_defaults_for_timestamp;
 
@@explicit_defaults_for_timestamp
 
1
 
show create table t2;
 
Table	Create Table
 
t2	CREATE TABLE `t2` (
 
  `ts` timestamp NOT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1

So, the procedures do change the variable value, but within the procedure it doesn't work the same way as outside.

If it's changed within the procedure from ON to OFF, then there seems to be no effect at all, the table is still created with TIMESTAMP NULL DEFAULT NULL.

If it's changed from OFF to ON, there is a partial effect, the column is created as NOT NULL, but without default/on update.

MySQL 8.0 behaves in a similar way, only it adds default zero date in the second case, which isn't what one would expect either.

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

bar, please, take a look at commits https://github.com/MariaDB/server/commit/5a362d486b30 and https://github.com/MariaDB/server/commit/7b8304045272

Comment by Alexander Barkov [ 2022-07-29 ]

replied by email

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