[#MDEV-13750] Document recent changes to information

As part of ~~MDEV-13132~~, the information_schema.COLUMNS table was changed quite a bit in MariaDB 10.2. However, the documentation page does not explain any information about that.

https://mariadb.com/kb/en/the-mariadb-library/information-schema-columns-table/

We can determine some of the changes by reading the comments of ~~MDEV-13132~~, but here is a quick test that can be used to show some of the differences:

DROP TABLE IF EXISTS mytest;

CREATE TABLE mytest (

mystr_1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,

mystr_2 VARCHAR(10),

mystr_3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT 'ABC',

mystr_4 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT 'NULL',

myint_1 INT DEFAULT NULL,

myint_2 INT,

myint_3 INT DEFAULT 1

) ENGINE=MyISAM;

SELECT

table_name

,column_name

,ordinal_position

,column_default

FROM information_schema.COLUMNS

WHERE table_schema=DATABASE()

AND TABLE_NAME='mytest';

This gives much different results between 10.1 and 10.2.

10.1.26:

+------------+-------------+------------------+----------------+

| table_name | column_name | ordinal_position | column_default |

+------------+-------------+------------------+----------------+

| mytest     | mystr_1     |                1 | NULL           |

| mytest     | mystr_2     |                2 | NULL           |

| mytest     | mystr_3     |                3 | ABC            |

| mytest     | mystr_4     |                4 | NULL           |

| mytest     | myint_1     |                5 | NULL           |

| mytest     | myint_2     |                6 | NULL           |

| mytest     | myint_3     |                7 | 1              |

+------------+-------------+------------------+----------------+

7 rows in set (0.00 sec)

10.2.7:

+------------+-------------+------------------+----------------+

| table_name | column_name | ordinal_position | column_default |

+------------+-------------+------------------+----------------+

| mytest     | mystr_1     |                1 | NULL           |

| mytest     | mystr_2     |                2 | NULL           |

| mytest     | mystr_3     |                3 | 'ABC'          |

| mytest     | mystr_4     |                4 | 'NULL'         |

| mytest     | myint_1     |                5 | NULL           |

| mytest     | myint_2     |                6 | NULL           |

| mytest     | myint_3     |                7 | 1              |

+------------+-------------+------------------+----------------+

7 rows in set (0.00 sec)

[MDEV-13750] Document recent changes to information_schema.COLUMNS table Created: 2017-09-06 Updated: 2020-08-25 Resolved: 2017-09-07
Status:	Closed
Project:	MariaDB Server
Component/s:	Documentation
Fix Version/s:	N/A

[MDEV-13750] Document recent changes to information_schema.COLUMNS table Created: 2017-09-06 Updated: 2020-08-25 Resolved: 2017-09-07