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>"
{"report":{"fcp":904.2000000476837,"ttfb":209.80000019073486,"pageVisibility":"visible","entityId":57783,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":1,"journeyId":"2e96b7ea-9873-4767-a2f2-a5c1158d5726","navigationType":0,"readyForUser":976.4000000953674,"redirectCount":0,"resourceLoadedEnd":984,"resourceLoadedStart":214.90000009536743,"resourceTiming":[{"duration":14.400000095367432,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":214.90000009536743,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":214.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":229.30000019073486,"responseStart":0,"secureConnectionStart":0},{"duration":14.599999904632568,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":215.10000014305115,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":215.10000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":229.70000004768372,"responseStart":0,"secureConnectionStart":0},{"duration":219.20000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":215.40000009536743,"connectEnd":215.40000009536743,"connectStart":215.40000009536743,"domainLookupEnd":215.40000009536743,"domainLookupStart":215.40000009536743,"fetchStart":215.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":215.40000009536743,"responseEnd":434.60000014305115,"responseStart":434.60000014305115,"secureConnectionStart":215.40000009536743},{"duration":207.09999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true&whisper-enabled=true","startTime":215.40000009536743,"connectEnd":215.40000009536743,"connectStart":215.40000009536743,"domainLookupEnd":215.40000009536743,"domainLookupStart":215.40000009536743,"fetchStart":215.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":215.40000009536743,"responseEnd":422.5,"responseStart":422.5,"secureConnectionStart":215.40000009536743},{"duration":19,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":215.70000004768372,"connectEnd":215.70000004768372,"connectStart":215.70000004768372,"domainLookupEnd":215.70000004768372,"domainLookupStart":215.70000004768372,"fetchStart":215.70000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":215.70000004768372,"responseEnd":234.70000004768372,"responseStart":234.70000004768372,"secureConnectionStart":215.70000004768372},{"duration":209.79999995231628,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":215.80000019073486,"connectEnd":215.80000019073486,"connectStart":215.80000019073486,"domainLookupEnd":215.80000019073486,"domainLookupStart":215.80000019073486,"fetchStart":215.80000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":215.80000019073486,"responseEnd":425.60000014305115,"responseStart":425.60000014305115,"secureConnectionStart":215.80000019073486},{"duration":220.40000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":215.90000009536743,"connectEnd":215.90000009536743,"connectStart":215.90000009536743,"domainLookupEnd":215.90000009536743,"domainLookupStart":215.90000009536743,"fetchStart":215.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":215.90000009536743,"responseEnd":436.30000019073486,"responseStart":436.30000019073486,"secureConnectionStart":215.90000009536743},{"duration":221.09999990463257,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":216.10000014305115,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":216.10000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":437.2000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":220.60000014305115,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":216.20000004768372,"connectEnd":216.20000004768372,"connectStart":216.20000004768372,"domainLookupEnd":216.20000004768372,"domainLookupStart":216.20000004768372,"fetchStart":216.20000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":216.20000004768372,"responseEnd":436.80000019073486,"responseStart":436.80000019073486,"secureConnectionStart":216.20000004768372},{"duration":221,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":216.40000009536743,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":216.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":437.40000009536743,"responseStart":0,"secureConnectionStart":0},{"duration":220.79999995231628,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":216.60000014305115,"connectEnd":216.60000014305115,"connectStart":216.60000014305115,"domainLookupEnd":216.60000014305115,"domainLookupStart":216.60000014305115,"fetchStart":216.60000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":216.60000014305115,"responseEnd":437.40000009536743,"responseStart":437.40000009536743,"secureConnectionStart":216.60000014305115},{"duration":296.2000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":226.90000009536743,"connectEnd":226.90000009536743,"connectStart":226.90000009536743,"domainLookupEnd":226.90000009536743,"domainLookupStart":226.90000009536743,"fetchStart":226.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":226.90000009536743,"responseEnd":523.1000001430511,"responseStart":523.1000001430511,"secureConnectionStart":226.90000009536743},{"duration":367.2000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":227.10000014305115,"connectEnd":227.10000014305115,"connectStart":227.10000014305115,"domainLookupEnd":227.10000014305115,"domainLookupStart":227.10000014305115,"fetchStart":227.10000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":227.10000014305115,"responseEnd":594.3000001907349,"responseStart":594.3000001907349,"secureConnectionStart":227.10000014305115},{"duration":131.39999985694885,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":661.8000001907349,"connectEnd":661.8000001907349,"connectStart":661.8000001907349,"domainLookupEnd":661.8000001907349,"domainLookupStart":661.8000001907349,"fetchStart":661.8000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":661.8000001907349,"responseEnd":793.2000000476837,"responseStart":793.2000000476837,"secureConnectionStart":661.8000001907349},{"duration":133.40000009536743,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":876.7000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":876.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1010.1000001430511,"responseStart":0,"secureConnectionStart":0},{"duration":95.19999980926514,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/e65b778d185daf5aee24936755b43da6/_/download/contextbatch/js/browser-metrics-plugin.contrib,-_super,-project.issue.navigator,-jira.view.issue,-atl.general/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":888.8000001907349,"connectEnd":888.8000001907349,"connectStart":888.8000001907349,"domainLookupEnd":888.8000001907349,"domainLookupStart":888.8000001907349,"fetchStart":888.8000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":888.8000001907349,"responseEnd":984,"responseStart":984,"secureConnectionStart":888.8000001907349}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":43,"responseStart":210,"responseEnd":227,"domLoading":214,"domInteractive":1034,"domContentLoadedEventStart":1034,"domContentLoadedEventEnd":1069,"domComplete":1453,"loadEventStart":1453,"loadEventEnd":1453,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1012.3000001907349},{"name":"bigPipe.sidebar-id.end","time":1013.1000001430511},{"name":"bigPipe.activity-panel-pipe-id.start","time":1013.3000001907349},{"name":"bigPipe.activity-panel-pipe-id.end","time":1015.8000001907349},{"name":"activityTabFullyLoaded","time":1077.2000000476837}],"measures":[],"correlationId":"634618b9772d0a","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":97,"dbReadsTimeInMs":10,"dbConnsTimeInMs":18,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}