After upgrade MariaDB from 10.1.15 to 10.3.7 one view stop working:
CREATE OR REPLACE VIEW first_view AS SELECT o.order_pk FROM second_view AS o LEFT JOIN third_view AS d ON o.order_pk=d.order_pk;
Now return
Unknown column 'd.order_pk' in 'on clause'
If i change LEFT JOIN to RIGHT JOIN no errors.
I have 10.1.15 in the same cluster and this view works fine.
- is duplicated by
-
MDEV-16472
column aliases no longer available in joins
-
-
Closed
{"report":{"fcp":959.7000000476837,"ttfb":171.5,"pageVisibility":"visible","entityId":68018,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"a6f840fa-ecda-4e1f-9c29-24dc6b18ef10","navigationType":0,"readyForUser":1066.6000001430511,"redirectCount":0,"resourceLoadedEnd":689.6000001430511,"resourceLoadedStart":185,"resourceTiming":[{"duration":28,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":185,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":185,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":213,"responseStart":0,"secureConnectionStart":0},{"duration":32.200000047683716,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/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","startTime":185.40000009536743,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":185.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":217.60000014305115,"responseStart":0,"secureConnectionStart":0},{"duration":311.39999985694885,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":185.40000009536743,"connectEnd":218.90000009536743,"connectStart":218.90000009536743,"domainLookupEnd":218.90000009536743,"domainLookupStart":218.90000009536743,"fetchStart":185.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":219.20000004768372,"responseEnd":496.7999999523163,"responseStart":234.79999995231628,"secureConnectionStart":218.90000009536743},{"duration":466.7000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/099b33461394b8015fc36c0a4b96e19f-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/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","startTime":185.5,"connectEnd":185.5,"connectStart":185.5,"domainLookupEnd":185.5,"domainLookupStart":185.5,"fetchStart":185.5,"redirectEnd":0,"redirectStart":0,"requestStart":219.90000009536743,"responseEnd":652.2000000476837,"responseStart":248.5,"secureConnectionStart":185.5},{"duration":59.299999952316284,"initiatorType":"script","name":"https://jira.mariadb.org/s/94c15bff32baef80f4096a08aceae8bc-CDN/lu2bu7/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":185.60000014305115,"connectEnd":185.60000014305115,"connectStart":185.60000014305115,"domainLookupEnd":185.60000014305115,"domainLookupStart":185.60000014305115,"fetchStart":185.60000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":220.10000014305115,"responseEnd":244.90000009536743,"responseStart":238.90000009536743,"secureConnectionStart":185.60000014305115},{"duration":95.59999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":185.70000004768372,"connectEnd":185.70000004768372,"connectStart":185.70000004768372,"domainLookupEnd":185.70000004768372,"domainLookupStart":185.70000004768372,"fetchStart":185.70000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":247.10000014305115,"responseEnd":281.2999999523163,"responseStart":274.40000009536743,"secureConnectionStart":185.70000004768372},{"duration":54.799999952316284,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bu7/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":185.90000009536743,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":185.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":240.70000004768372,"responseStart":0,"secureConnectionStart":0},{"duration":95.70000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":185.90000009536743,"connectEnd":185.90000009536743,"connectStart":185.90000009536743,"domainLookupEnd":185.90000009536743,"domainLookupStart":185.90000009536743,"fetchStart":185.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":247.29999995231628,"responseEnd":281.60000014305115,"responseStart":275.40000009536743,"secureConnectionStart":185.90000009536743},{"duration":95.90000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":186,"connectEnd":186,"connectStart":186,"domainLookupEnd":186,"domainLookupStart":186,"fetchStart":186,"redirectEnd":0,"redirectStart":0,"requestStart":247.40000009536743,"responseEnd":281.90000009536743,"responseStart":276.2999999523163,"secureConnectionStart":186},{"duration":55.299999952316284,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bu7/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":186.10000014305115,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":186.10000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":241.40000009536743,"responseStart":0,"secureConnectionStart":0},{"duration":95.90000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/s/3339d87fa2538a859872f2df449bf8d0-CDN/lu2bu7/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":186.20000004768372,"connectEnd":186.20000004768372,"connectStart":186.20000004768372,"domainLookupEnd":186.20000004768372,"domainLookupStart":186.20000004768372,"fetchStart":186.20000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":247.60000014305115,"responseEnd":282.10000014305115,"responseStart":277.40000009536743,"secureConnectionStart":186.20000004768372},{"duration":491.09999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":186.90000009536743,"connectEnd":186.90000009536743,"connectStart":186.90000009536743,"domainLookupEnd":186.90000009536743,"domainLookupStart":186.90000009536743,"fetchStart":186.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":314.40000009536743,"responseEnd":678,"responseStart":664.5,"secureConnectionStart":186.90000009536743},{"duration":497.30000019073486,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":192.29999995231628,"connectEnd":192.29999995231628,"connectStart":192.29999995231628,"domainLookupEnd":192.29999995231628,"domainLookupStart":192.29999995231628,"fetchStart":192.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":340.90000009536743,"responseEnd":689.6000001430511,"responseStart":673.7000000476837,"secureConnectionStart":192.29999995231628},{"duration":130.90000009536743,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":656.2999999523163,"connectEnd":656.2999999523163,"connectStart":656.2999999523163,"domainLookupEnd":656.2999999523163,"domainLookupStart":656.2999999523163,"fetchStart":656.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":753.9000000953674,"responseEnd":787.2000000476837,"responseStart":785.7999999523163,"secureConnectionStart":656.2999999523163},{"duration":244.60000014305115,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":888.5,"connectEnd":888.5,"connectStart":888.5,"domainLookupEnd":888.5,"domainLookupStart":888.5,"fetchStart":888.5,"redirectEnd":0,"redirectStart":0,"requestStart":1100.7000000476837,"responseEnd":1133.1000001430511,"responseStart":1132.1000001430511,"secureConnectionStart":888.5}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":30,"responseStart":172,"responseEnd":185,"domLoading":176,"domInteractive":1161,"domContentLoadedEventStart":1161,"domContentLoadedEventEnd":1217,"domComplete":1423,"loadEventStart":1423,"loadEventEnd":1423,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1103},{"name":"bigPipe.sidebar-id.end","time":1103.7000000476837},{"name":"bigPipe.activity-panel-pipe-id.start","time":1103.9000000953674},{"name":"bigPipe.activity-panel-pipe-id.end","time":1106.6000001430511},{"name":"activityTabFullyLoaded","time":1234.9000000953674}],"measures":[],"correlationId":"82e823b06a53e1","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":76,"dbReadsTimeInMs":12,"dbConnsTimeInMs":19,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Strange, maybe that's happened because your select more simple that my view?
My full view looks like this:
CREATE OR REPLACE VIEW third_view AS
SELECT
o.PK AS order_pk,
MAX(IF(omc.ostatus = 'SHIPPED', omc.p_timestamp, null)) AS shipped_date,
MAX(IF(omc.ostatus = 'REALIZATION', omc.p_timestamp, null)) AS realization_date,
MAX(IF(omc.ostatus = 'RETURN', omc.p_timestamp, null)) AS return_date,
MAX(IF(omc.ostatus = 'RETURN_AND_REALIZATION', omc.p_timestamp, null)) AS return_and_realization_date,
MAX(IF(omc.ostatus = 'APPROVED', omc.p_timestamp, null)) AS approved_date,
MAX(IF(omc.ostatus = 'READY_TO_PACKING', omc.p_timestamp, null)) AS ready_to_packing_date,
MAX(IF(omc.ostatus = 'PACKED', omc.p_timestamp, null)) AS packed_date,
MAX(IF(omc.ostatus = 'READY_FOR_ISSUANCE', omc.p_timestamp, null)) AS ready_for_issurance_date,
MAX(IF(omc.ostatus = 'IN_STORE_PARTIALLY_COMPLETED', omc.p_timestamp, null)) AS in_store_partially_completed_date,
MAX(IF(omc.ostatus = 'DELIVERED', omc.p_timestamp, null)) AS delivered_date,
MAX(IF(omc.ostatus = 'CANCELLED', omc.p_timestamp, null)) AS cancelled_date,
MAX(IF(omc.ostatus = 'RETURNED_FOR_REVISION', omc.p_timestamp, null)) AS returned_for_revision_date,
MAX(IF(omc.ostatus = 'MOVED_TO_RETAIL', omc.p_timestamp, null)) AS moved_to_retail_date,
MAX(IF(omc.ostatus = 'PROCESSED', omc.p_timestamp, null)) AS processed_date,
MAX(IF(omc.ostatus = 'WAITING', omc.p_timestamp, null)) AS waiting_date,
MAX(IF(omc.ostatus = 'UNCALL', omc.p_timestamp, null)) AS uncall_date,
MAX(IF(omc.ostatus = 'PARTIALLY_READY_FOR_ISSUANCE', omc.p_timestamp, null)) AS partially_ready_for_issuance_date,
MAX(IF(omc.ostatus = 'PARTIAL_REALIZATION', omc.p_timestamp, null)) AS partial_realization_date,
MAX(IF(omc.ostatus = 'REALIZATION_AND_RETURN', omc.p_timestamp, null)) AS realization_and_return_date
FROM main_view AS o
JOIN ordermodifirecords AS omr
ON o.PK = omr.p_order
JOIN ordermodifirecentrs AS omc
ON omc.p_modificationrecord = omr.PK
WHERE omc.p_status IS NOT NULL
GROUP BY o.PK
ORDER BY NULL;
If i test your example, disable optimization option works good.