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

Expected see full definition of virtual column into EXTRA field

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Won't Fix
    • 5.3.12, 10.0.21, 5.5, 10.0, 10.1
    • N/A
    • Virtual Columns
    • None
    • Linux Fedora

    Description

      Hi!

      Expected see full definition for virtual column into extra field.

      SHOW CREATE TABLE crm_kladr

      CREATE TABLE `crm_kladr` (
        `name` varchar(40) NOT NULL,
        `socr` varchar(10) NOT NULL,
        `code` varchar(13) NOT NULL,
        `index` varchar(6) NOT NULL,
        `gninmb` varchar(4) NOT NULL,
        `uno` varchar(4) NOT NULL,
        `ocatd` varchar(11) NOT NULL,
        `status` varchar(1) NOT NULL,
        `regioncode` varchar(3) DEFAULT NULL,
        `raioncode` varchar(3) DEFAULT NULL,
        `citycode` varchar(3) DEFAULT NULL,
        `towncode` varchar(3) DEFAULT NULL,
        `actual` varchar(2) AS ( (SUBSTR(`code`, -2))) PERSISTENT,
        PRIMARY KEY (`code`),
        KEY `IX_kladr_name` (`name`),
        KEY `IX_kladr_regioncode` (`regioncode`),
        KEY `IX_kladr_raioncode` (`raioncode`),
        KEY `IX_kladr_citycode` (`citycode`),
        KEY `IX_kladr_towncode` (`towncode`),
        KEY `IX_kladr_postindex` (`index`),
        KEY `actual` (`actual`) USING BTREE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

      SHOW COLUMNS FROM crm_kladr FROM dev;

      Field       Type         Null    Key     Default  Extra       
      ----------  -----------  ------  ------  -------  ------------
      name        varchar(40)  NO      MUL     (NULL)               
      socr        varchar(10)  NO              (NULL)               
      code        varchar(13)  NO      PRI     (NULL)               
      index       varchar(6)   NO      MUL     (NULL)               
      gninmb      varchar(4)   NO              (NULL)               
      uno         varchar(4)   NO              (NULL)               
      ocatd       varchar(11)  NO              (NULL)               
      status      varchar(1)   NO              (NULL)               
      regioncode  varchar(3)   YES     MUL     (NULL)               
      raioncode   varchar(3)   YES     MUL     (NULL)               
      citycode    varchar(3)   YES     MUL     (NULL)               
      towncode    varchar(3)   YES     MUL     (NULL)               
      actual      varchar(2)   YES     MUL     (NULL)   PERSISTENT  

      SELECT * FROM information_schema.columns WHERE table_schema = 'dev' AND  table_name = 'crm_kladr'

      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            dev           crm_kladr   name                        1  (NULL)          NO           varchar                          40                     120             (NULL)         (NULL)              (NULL)  utf8                utf8_general_ci  varchar(40)  MUL                     select,insert,update,references                  
      def            dev           crm_kladr   socr                        2  (NULL)          NO           varchar                          10                      30             (NULL)         (NULL)              (NULL)  utf8                utf8_general_ci  varchar(10)                          select,insert,update,references                  
      def            dev           crm_kladr   code                        3  (NULL)          NO           varchar                          13                      39             (NULL)         (NULL)              (NULL)  utf8                utf8_general_ci  varchar(13)  PRI                     select,insert,update,references                  
      def            dev           crm_kladr   index                       4  (NULL)          NO           varchar                           6                      18             (NULL)         (NULL)              (NULL)  utf8                utf8_general_ci  varchar(6)   MUL                     select,insert,update,references                  
      def            dev           crm_kladr   gninmb                      5  (NULL)          NO           varchar                           4                      12             (NULL)         (NULL)              (NULL)  utf8                utf8_general_ci  varchar(4)                           select,insert,update,references                  
      def            dev           crm_kladr   uno                         6  (NULL)          NO           varchar                           4                      12             (NULL)         (NULL)              (NULL)  utf8                utf8_general_ci  varchar(4)                           select,insert,update,references                  
      def            dev           crm_kladr   ocatd                       7  (NULL)          NO           varchar                          11                      33             (NULL)         (NULL)              (NULL)  utf8                utf8_general_ci  varchar(11)                          select,insert,update,references                  
      def            dev           crm_kladr   status                      8  (NULL)          NO           varchar                           1                       3             (NULL)         (NULL)              (NULL)  utf8                utf8_general_ci  varchar(1)                           select,insert,update,references                  
      def            dev           crm_kladr   regioncode                  9  (NULL)          YES          varchar                           3                       9             (NULL)         (NULL)              (NULL)  utf8                utf8_general_ci  varchar(3)   MUL                     select,insert,update,references                  
      def            dev           crm_kladr   raioncode                  10  (NULL)          YES          varchar                           3                       9             (NULL)         (NULL)              (NULL)  utf8                utf8_general_ci  varchar(3)   MUL                     select,insert,update,references                  
      def            dev           crm_kladr   citycode                   11  (NULL)          YES          varchar                           3                       9             (NULL)         (NULL)              (NULL)  utf8                utf8_general_ci  varchar(3)   MUL                     select,insert,update,references                  
      def            dev           crm_kladr   towncode                   12  (NULL)          YES          varchar                           3                       9             (NULL)         (NULL)              (NULL)  utf8                utf8_general_ci  varchar(3)   MUL                     select,insert,update,references                  
      def            dev           crm_kladr   actual                     13  (NULL)          YES          varchar                           2                       6             (NULL)         (NULL)              (NULL)  utf8                utf8_general_ci  varchar(2)   MUL         PERSISTENT  select,insert,update,references                  

      In examples above we see only keyword "PERSISTENT", but be better if we see "AS ( (SUBSTR(`code`, -2))) PERSISTENT"

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              mikhail Mikhail Gavrilov
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.