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

"Data too long for column" instead of "Invalid default value for" upon ALTER in strict mode

    XMLWordPrintable

Details

    Description

      If we try to create a table with a too long default value for a varchar column, it produces ER_INVALID_DEFAULT "Invalid default value" error regardless the strict mode:

      MariaDB [test]> set sql_mode='';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> create table t1 (c varchar(1) default 'foo');
      ERROR 1067 (42000): Invalid default value for 'c'
      MariaDB [test]> 
      MariaDB [test]> set sql_mode='STRICT_ALL_TABLES';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> create table t1 (c varchar(1) default 'foo');
      ERROR 1067 (42000): Invalid default value for 'c'
      

      But if we set the default value via an ALTER statement, the error depends on the strict mode:

      MariaDB [test]> create table t1 (c varchar(1));
      Query OK, 0 rows affected (0.29 sec)
       
      MariaDB [test]> set sql_mode='';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> alter table t1 alter column c set default 'foo';
      ERROR 1067 (42000): Invalid default value for 'c'
      MariaDB [test]> 
      MariaDB [test]> set sql_mode='STRICT_ALL_TABLES';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> alter table t1 alter column c set default 'foo';
      ERROR 1406 (22001): Data too long for column 'c' at row 1
      

      It doesn't seem right.

      It started happening in 10.0.
      5.5 (and MySQL 5.6/5.7) produce the same ER_INVALID_DEFAULT consistently.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              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.