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

SQL mode MAXDB has undocumented side-effects

    XMLWordPrintable

Details

    Description

      SQL_MODE MAXDB is documented as an equivalent of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, MAXDB, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER. It's a recursive definition in itself, the only meaningful way to read it is that it's an equivalent of the combination of the other listed modes; but it is not. At least one extra effect is that MAXDB mode makes timestamp be converted into datetime, while the combination of the listed modes doesn't:

      set sql_mode= 'MAXDB';
      create table t1 (t timestamp);
      show create table t1;
      Table	Create Table
      t1	CREATE TABLE "t1" (
        "t" datetime DEFAULT NULL
      )
      drop table t1;
      set sql_mode= 'PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER';
      create table t1 (t timestamp);
      show create table t1;
      Table	Create Table
      t1	CREATE TABLE "t1" (
        "t" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
      )
      

      It was the same in MySQL up to an including 5.6; in 5.7 MAXDB mode was deprecated, and a warning about implicit conversion was returned. in 8.0, the mode is removed.

      At least the documentation needs to be updated, but maybe MariaDB doesn't need the mode either?

      Attachments

        Activity

          People

            greenman Ian Gilfillan
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.