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

Changing explicit_defaults_for_timestamp within stored procedure works inconsistently

    XMLWordPrintable

Details

    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.

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.