[MDEV-22791] SHOW COLUMNS for TEXT NOT NULL DEFAULT differ from VARCHAR NOT NULL DEFAULT Created: 2020-06-03  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Admin statements, Data types
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Sergey Chernomorets Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None
Environment:

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           |
+-------------+----------------+


Generated at Thu Feb 08 09:17:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.