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>"
Bug
Major