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

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2, 10.3, 10.4, 10.5
    • 10.4, 10.5
    • None
    • 10.3.22-MariaDB-log

    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

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

            Dates

              Created:
              Updated:

              Git Integration

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