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

View with a window function replaces column expression with its alias, making the view invalid

Details

    Description

      If one creates a view that 1) contains one or more window functions as column expression and 2) provides aliases for such columns, the column expressions get replaced by the aliases in the view definition, thus rendering the view invalid. An attempt to reference such view results in "ERROR 1356 (HY000): View 'XXX' references invalid table(s) or column(s) ..."

      To reproduce:

      MariaDB [onedb]> create or replace view v_test (tab_name, rnk) as select table_name, rank() over (order by table_name)  from information_schema.tables;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [onedb]> show create view v_test;
      		 
      +--------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
      		 
      | View   | Create View                                                                                                                               | character_set_client | collation_connection |
      		 
      +--------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
      		 
      | v_test | CREATE VIEW "v_test" AS select "information_schema"."tables"."TABLE_NAME" AS "tab_name","rnk" AS "rnk" from "information_schema"."tables" | utf8                 | utf8_general_ci      |
      		 
      +--------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
      		 
      1 row in set, 1 warning (0.00 sec)

      Notice how the view definition has `"rnk" AS "rnk"` as the second column – the alias "rnk" replaced the column expression.

      It happens when you provide a column list in the view DDL – "create view blah (<column list>)" and also when you supply individual column aliases – "create view blah as select rank() over () as <alias>"

      Attachments

        Activity

          People

            igor Igor Babaev (Inactive)
            nickivanov nick ivanov
            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.