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

Information Schema does not show virtual / persisted column defintions

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.2.3
    • N/A
    • Virtual Columns
    • 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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.