Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13750

Document recent changes to information_schema.COLUMNS table

    Details

      Description

      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)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                greenman Ian Gilfillan
                Reporter:
                GeoffMontee Geoff Montee
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: