Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Won't Fix
-
None
-
None
Description
In the following output, NULL can mean three things:
1) No default value
2) A NULL default
3) A string, 'NULL'
CREATE TABLE t (c1 VARCHAR(10) NOT NULL, c2 VARCHAR(10) DEFAULT NULL, c3 VARCHAR(10) NOT NULL DEFAULT 'NULL');
|
Query OK, 0 rows affected (0.06 sec)
|
|
|
mysql [localhost] {msandbox} (test) > DESC t;
|
+-------+-------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------+-------------+------+-----+---------+-------+
|
| c1 | varchar(10) | NO | | NULL | |
|
| c2 | varchar(10) | YES | | NULL | |
|
| c3 | varchar(10) | NO | | NULL | |
|
+-------+-------------+------+-----+---------+-------+
|
In MDEV-13132 this has been differentiated in INFORMATION_SCHEMA.COLUMNS, is it desirable to differentiate here as well?
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
This was intentional. In INFORMATION_SCHEMA.COLUMNS we followed the standard (and, still, got quite a few complains about breaking some applications). As there's no standard for DESC, we kept it unchanged for backward compatibility reasons. So that users would only need to change select * from information_schema.columns to describe and got back old historical (and ambiguous) behavior.
Better example including expressions would be the one from https://mariadb.com/kb/en/library/information-schema-columns-table/:
CREATE TABLE t (s1 VARCHAR(20) DEFAULT 'ABC',s2 VARCHAR(20) DEFAULT (concat('A','B')),s3 VARCHAR(20) DEFAULT ("concat('A','B')"),s4 VARCHAR(20),s5 VARCHAR(20) DEFAULT NULL,s6 VARCHAR(20) NOT NULL,s7 VARCHAR(20) DEFAULT 'NULL' NULL,s8 VARCHAR(20) DEFAULT 'NULL' NOT NULL);SELECTtable_name,column_name,ordinal_position,column_default,column_default IS NULLFROM information_schema.COLUMNSWHERE table_schema=DATABASE()AND TABLE_NAME='t';+------------+-------------+------------------+-----------------------+------------------------+| table_name | column_name | ordinal_position | column_default | column_default IS NULL |+------------+-------------+------------------+-----------------------+------------------------+| t | s1 | 1 | 'ABC' | 0 || t | s2 | 2 | concat('A','B') | 0 || t | s3 | 3 | 'concat(''A'',''B'')' | 0 || t | s4 | 4 | NULL | 0 || t | s5 | 5 | NULL | 0 || t | s6 | 6 | NULL | 1 || t | s7 | 7 | 'NULL' | 0 || t | s8 | 8 | 'NULL' | 0 |+------------+-------------+------------------+-----------------------+------------------------+8 rows in set (0.001 sec)DESC t;+-------+-------------+------+-----+-----------------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+-----------------+-------+| s1 | varchar(20) | YES | | ABC | || s2 | varchar(20) | YES | | concat('A','B') | || s3 | varchar(20) | YES | | concat('A','B') | || s4 | varchar(20) | YES | | NULL | || s5 | varchar(20) | YES | | NULL | || s6 | varchar(20) | NO | | NULL | || s7 | varchar(20) | YES | | NULL | || s8 | varchar(20) | NO | | NULL | |+-------+-------------+------+-----+-----------------+-------+