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

Information schema does not reflect virtual columns in views

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Trivial
    • Resolution: Unresolved
    • 10.5, 10.6, 10.11, 11.1(EOL), 10.4(EOL), 10.10(EOL), 11.0(EOL)
    • 11.2
    • Views, Virtual Columns
    • None

    Description

      I'm not really sure how columns for views should look at all in information_schema, but it seems that at least when a column is taken from a table "as is", its attributes such as type, default etc. are generally preserved; virtual columns are an exception, as neither EXTRA nor IS_GENERATED nor GENERATION_EXPRESSION are populated, even though the column is expectedly still treated as virtual upon DML on a view.

      create table t (a int, b int as (a));
      create view v as select * from t;
      select table_name, column_name, column_type, extra, is_generated, generation_expression from information_schema.columns where column_name = 'b';
      --error ER_WARNING_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
      insert into v (b) values (1);
      drop view v;
      drop table t;
      

      10.4 900c4d6920

      +------------+-------------+-------------+-------------------+--------------+-----------------------+
      | table_name | column_name | column_type | extra             | is_generated | generation_expression |
      +------------+-------------+-------------+-------------------+--------------+-----------------------+
      | v          | b           | int(11)     |                   | NEVER        | NULL                  |
      | t          | b           | int(11)     | VIRTUAL GENERATED | ALWAYS       | `a`                   |
      +------------+-------------+-------------+-------------------+--------------+-----------------------+
       
      MariaDB [test]> insert into v (b) values (1);
      ERROR 1906 (HY000): The value specified for generated column 'b' in table 't' has been ignored
      

      Attachments

        Activity

          People

            serg Sergei Golubchik
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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