10.2+ Integer DEFAULT values missing quotes (MDEV-15377)

[MDEV-15390] information_schema returns quoted strings for default textual columns Created: 2018-02-22  Updated: 2018-03-13  Resolved: 2018-03-13

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Server
Affects Version/s: 10.2
Fix Version/s: N/A

Type: Technical task Priority: Minor
Reporter: Nemanja Stambolic Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: None
Environment:

GNU/Linux

Issue Links:
PartOf
includes MDEV-15394 DEFAULT current_timestamp() discrepancy Closed
Relates
relates to MDEV-15407 DEFAULT NULL forced when no DEFAULT s... Closed

 Description   

Just when I thought that INFORMATION_SCHEMA returns proper default values which could be used across different MariaDB server versions....

  • Numbers are now just fine (e.g. not quoted) on both 10.0 and 10.2
  • But now textual columns (varchar, text...) are quoted and only IF they contain some default value (even empty string) on 10.2+ only

10.2:

+--------------+---------------+-------------+-------------+------------------+----------------+
 
| TABLE_SCHEMA | TABLE_NAME    | COLUMN_NAME | COLUMN_TYPE | ORDINAL_POSITION | COLUMN_DEFAULT |
 
+--------------+---------------+-------------+-------------+------------------+----------------+
 
| erschema     | partner_sites | er_order    | varchar(4)  |               70 | 'DESC'         |
 
| erschema     | partner_sites | er_limit    | tinyint(4)  |               71 | 10             |
 
+--------------+---------------+-------------+-------------+------------------+----------------+

10.0

+--------------+---------------+-------------+-------------+------------------+----------------+
 
| TABLE_SCHEMA | TABLE_NAME    | COLUMN_NAME | COLUMN_TYPE | ORDINAL_POSITION | COLUMN_DEFAULT |
 
+--------------+---------------+-------------+-------------+------------------+----------------+
 
| erschema     | partner_sites | er_order    | varchar(4)  |               70 | DESC           |
 
| erschema     | partner_sites | er_limit    | tinyint(4)  |               71 | 10             |
 
+--------------+---------------+-------------+-------------+------------------+----------------+

So we have different results depending on the context (INFORMATION_SCHEMA vs. SHOW CREATE TABLE...). What a mess.

 Comments   
Comment by Sergei Golubchik [ 2018-02-22 ]

Yes. We started looking at this because of the new support for arbitrary expressions in DEFAULT. Like

CREATE TABLE t1 (a CHAR(100) DEFAULT UUID(), b CHAR(100) DEFAULT 'UUID()')

Printing defaults without quotes in the INFORMATION_SCHEMA.COLUMNS is not able to distinguish between the two different defaults. So we looked at the SQL standard, and it turned out that it says that INFORMATION_SCHEMA.COLUMNS should print strings in default values quoted! Apparently when MySQL was implementing INFORMATION_SCHEMA.COLUMNS, someone misread the standard. We fixed that bug and it removed the ambiguity in UUID() vs 'UUID()'.

Comment by Nemanja Stambolic [ 2018-02-23 ]

Thanks for info.
I support the standardization - but in this case MariaDB 10.2 seems pretty much incompatible in some aspects comparing to 10.0 and/or plain MySQL servers.

I guess there's also no flag to get the old style back at least for INFORMATION_SCHEMA usage?

Comment by Ian Gilfillan [ 2018-03-13 ]

Documented in https://mariadb.com/kb/en/library/incompatibilities-and-feature-differences-between-mariadb-102-and-mysql-57/#incompatibilities, was already in https://mariadb.com/kb/en/library/information-schema-columns-table/

Comment by Nemanja Stambolic [ 2018-03-13 ]

Good. Thanks.

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