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

[MDEV-15407] DEFAULT NULL forced when no DEFAULT set on NULL-able column Created: 2018-02-24  Updated: 2018-03-14  Resolved: 2018-03-14

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:
Relates
relates to MDEV-15519 CHECKSUM TABLE gives different result... Confirmed
relates to MDEV-15377 10.2+ Integer DEFAULT values missing ... Closed
relates to MDEV-15390 information_schema returns quoted str... Closed
relates to MDEV-15394 DEFAULT current_timestamp() discrepancy Closed

 Description   

Now this starts to smell.
If a column is defined as NULL-able, 10.2 will automatically append DEFAULT NULL to create statements.

10.2

  `description` text COLLATE utf8_bin DEFAULT NULL

10.0

 `description` text COLLATE utf8_bin

Originally the column does not have DEFAULT value set at all!

Basically table exported from 10.2 will not work in a same way on MariaDB 10.0 or MySQL servers.

 Comments   
Comment by Nemanja Stambolic [ 2018-02-24 ]

Furthermore, this only seems to apply for text columns which are defined NULL-able, while NOT-NULL-able text columns and varchar columns share same behavior with 10.0 series.

Same on both 10.2 and 10.0

author varchar(100) CHARACTER SET utf8 DEFAULT NULL

Same on both 10.2 and 10.0

article text CHARACTER SET utf8 NOT NULL

Now that's a mess.

Comment by Ian Gilfillan [ 2018-02-24 ]

From MariaDB 10.2.1, BLOB and TEXT fields can have a DEFAULT value. The behaviour is unchanged, but the DEFAULT is now explicitly specified, as is preferable. The specification is now the same as for all other types. NOT NULL fields without a default will obviously fail or give a warning, depending on the strict mode, as they always have. In an unrelated change, 10.2 has also changed to strict mode by default.

Comment by Nemanja Stambolic [ 2018-03-13 ]

Thanks for clarification. I think this deserves to be documented.

Comment by Ian Gilfillan [ 2018-03-14 ]

Added to https://mariadb.com/kb/en/library/show-create-table/ and https://mariadb.com/kb/en/library/incompatibilities-and-feature-differences-between-mariadb-102-and-mysql-57/

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