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

SHOW COLUMNS for TEXT NOT NULL DEFAULT differ from VARCHAR NOT NULL DEFAULT

    XMLWordPrintable

    Details

      Description

      SHOW COLUMNS show '' as default value for TEXT and empty field for VARCHAR

      MariaDB [tmp]> create table a ( c text not null default '', b int  ) engine = innodb;
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [tmp]> show  columns from a;
      +-------+---------+------+-----+---------+-------+
      | Field | Type    | Null | Key | Default | Extra |
      +-------+---------+------+-----+---------+-------+
      | c     | text    | NO   |     | ''      |       |
      | b     | int(11) | YES  |     | NULL    |       |
      +-------+---------+------+-----+---------+-------+
      2 rows in set (0.00 sec)
       
      MariaDB [tmp]> 
      MariaDB [tmp]> drop table a;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [tmp]> create table a ( c varchar(3) not null default '', b int  ) engine = innodb;
      Query OK, 0 rows affected (0.02 sec)
       
      MariaDB [tmp]> show  columns from a;
      +-------+------------+------+-----+---------+-------+
      | Field | Type       | Null | Key | Default | Extra |
      +-------+------------+------+-----+---------+-------+
      | c     | varchar(3) | NO   |     |         |       |
      | b     | int(11)    | YES  |     | NULL    |       |
      +-------+------------+------+-----+---------+-------+
      2 rows in set (0.00 sec)
      
      

      In both cases information_schema show same value:

      select column_name, column_default from information_schema.columns where table_name = 'a';
      +-------------+----------------+
      | column_name | column_default |
      +-------------+----------------+
      | c           | ''             |
      | b           | NULL           |
      +-------------+----------------+
      

        Attachments

          Activity

            People

            Assignee:
            bar Alexander Barkov
            Reporter:
            chernomorets Sergey Chernomorets
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: