[MDEV-16352] Default values for text based fields contains a paired quotation marks Created: 2018-05-31  Updated: 2020-07-13  Resolved: 2020-07-13

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.3.7, 10.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Josep Sanz Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: describe, quotation_marks

Attachments: File error.sql    

 Description   

When I create a table, I get different presentations when I execute the DESCRIBE statement for the default values of the text based fields.

For example, If I create a varchar field, I get the default value without quotation marks when I execute the DESCRIBE statement, but when I create a text field (text, tinytext, mediumtext or longtext), I get the default value with quotation marks when I execute the DESCRIBE statement.

I have attached a test case file.

The actual result is:

+-------------+--------------+------+-----+---------+----------------+
 
| Field       | Type         | Null | Key | Default | Extra          |
 
+-------------+--------------+------+-----+---------+----------------+
 
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
 
| some_number | int(11)      | NO   |     | 0       |                |
 
| some_text1  | char(255)    | NO   |     |         |                |
 
| some_text2  | varchar(255) | NO   |     |         |                |
 
| some_text3  | text         | NO   |     | ''      |                |
 
| some_text4  | tinytext     | NO   |     | ''      |                |
 
| some_text5  | mediumtext   | NO   |     | ''      |                |
 
| some_text6  | longtext     | NO   |     | ''      |                |
 
+-------------+--------------+------+-----+---------+----------------+

The expected result is:

+-------------+--------------+------+-----+---------+----------------+
 
| Field       | Type         | Null | Key | Default | Extra          |
 
+-------------+--------------+------+-----+---------+----------------+
 
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
 
| some_number | int(11)      | NO   |     | 0       |                |
 
| some_text1  | char(255)    | NO   |     |         |                |
 
| some_text2  | varchar(255) | NO   |     |         |                |
 
| some_text3  | text         | NO   |     |         |                |
 
| some_text4  | tinytext     | NO   |     |         |                |
 
| some_text5  | mediumtext   | NO   |     |         |                |
 
| some_text6  | longtext     | NO   |     |         |                |
 
+-------------+--------------+------+-----+---------+----------------+



 Comments   
Comment by Sergei Golubchik [ 2020-07-13 ]

This is done for compatibility reasons. Before 10.2 TEXT columns could not have defaults at all and all defaults had to be literals. Since 10.2, one can use expressions as default values, and BLOB/TEXT can have defaults too. This feature made DESCRIBE output ambiguous. Consider

CREATE TABLE aaa (
 
  some_text1 char(255)        NOT NULL DEFAULT 'concat("a","b")',
 
  some_text2 varchar(255)     NOT NULL DEFAULT concat("a","b"),
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The output is

+-------------+--------------+------+-----+-----------------+----------------+
 
| Field       | Type         | Null | Key | Default         | Extra          |
 
+-------------+--------------+------+-----+-----------------+----------------+
 
| some_text1  | char(255)    | NO   |     | concat("a","b") |                |
 
| some_text2  | varchar(255) | NO   |     | concat('a','b') |                |
 
+-------------+--------------+------+-----+-----------------+----------------+

You can use INFORMATION_SCHEMA.COLUMNS to see the correct unambiguous values of DEFAULT, string literals will have quotes for CHAR and VARCHAR too.

But for compatibility with old applications we did not change the output of DESCRIBE, so it works as before, not quoting default values that were not quoted before 10.2.

Generated at Thu Feb 08 08:28:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.