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

Information Schema does not show virtual / persisted column defintions

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.2.3
    • Fix Version/s: N/A
    • Component/s: Virtual Columns
    • Labels:
      None

      Description

      Problem

      For computed columns, SHOW CREATE TABLE shows the expression used to compute the virtual or persisted column. However, the information_schema.COLUMNS does not show this information. The EXTRA column just has the values

      • VIRTUAL GENERATED
      • STORED GENERATED

      Solution

      Extend the Information schema to show the expression for the computed columns, just in the same way that you can see the DEFAULT clause.

      Reproduce

      create or replace table gold_stars (
        emp_id int(10),
        count int(10),
        ts DATETIME DEFAULT NOW(),
        expires DATETIME as (DATE_ADD(ts, interval 1 year)) virtual,
        rolling DATETIME as (DATE_ADD(now(), interval 3 month)) virtual,
        sbox DATETIME as (DATE_ADD(ts, interval 6 year)) persistent
      )
      

      The computed columns (and default) can be seen thus

      MariaDB [test]> show create table gold_stars;
      +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                 |
      +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | gold_stars | CREATE TABLE `gold_stars` (
        `emp_id` int(10) DEFAULT NULL,
        `count` int(10) DEFAULT NULL,
        `ts` datetime DEFAULT current_timestamp(),
        `expires` datetime GENERATED ALWAYS AS (`ts` + interval 1 year) VIRTUAL,
        `rolling` datetime GENERATED ALWAYS AS (current_timestamp() + interval 3 month) VIRTUAL,
        `sbox` datetime GENERATED ALWAYS AS (`ts` + interval 6 year) STORED
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      

      Looking at the information_schema.COLUMNS, the expression cannot be seen

      MariaDB [test]> select * from information_schema.COLUMNS     where table_name = "gold_stars";
      +---------------+--------------+------------+-------------+------------------+---------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------------------+---------------------------------+----------------+
      | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT      | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA             | PRIVILEGES                      | COLUMN_COMMENT |
      +---------------+--------------+------------+-------------+------------------+---------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------------------+---------------------------------+----------------+
      | def           | test         | gold_stars | emp_id      |                1 | NULL                | YES         | int       |                     NULL |                   NULL |                10 |             0 |               NULL | NULL               | NULL           | int(10)     |            |                   | select,insert,update,references |                |
      | def           | test         | gold_stars | count       |                2 | NULL                | YES         | int       |                     NULL |                   NULL |                10 |             0 |               NULL | NULL               | NULL           | int(10)     |            |                   | select,insert,update,references |                |
      | def           | test         | gold_stars | ts          |                3 | current_timestamp() | YES         | datetime  |                     NULL |                   NULL |              NULL |          NULL |                  0 | NULL               | NULL           | datetime    |            |                   | select,insert,update,references |                |
      | def           | test         | gold_stars | expires     |                4 | NULL                | YES         | datetime  |                     NULL |                   NULL |              NULL |          NULL |                  0 | NULL               | NULL           | datetime    |            | VIRTUAL GENERATED | select,insert,update,references |                |
      | def           | test         | gold_stars | rolling     |                5 | NULL                | YES         | datetime  |                     NULL |                   NULL |              NULL |          NULL |                  0 | NULL               | NULL           | datetime    |            | VIRTUAL GENERATED | select,insert,update,references |                |
      | def           | test         | gold_stars | sbox        |                6 | NULL                | YES         | datetime  |                     NULL |                   NULL |              NULL |          NULL |                  0 | NULL               | NULL           | datetime    |            | STORED GENERATED  | select,insert,update,references |                |
      +---------------+--------------+------------+-------------+------------------+---------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------------------+---------------------------------+----------------+
      6 rows in set (0.00 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              alvinr Alvin Richards (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: