SELECT 1 FROM DUAL WHERE 1 GROUPBY 1 HAVING 1 ORDERBY 1
FORUPDATE;
select * from v1;
dropview v1;
Expected result:
1
1
Actual result:
query 'select * from v1' failed: ER_PARSE_ERROR (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax touse near 'order by 1'at line 1
Is it a good idea to allow locking reads in a view, or in joins whose execution order might not be deterministic, depending on the query plan? Note: there is also LOCK IN SHARE MODE.
Marko Mäkelä
added a comment - Is it a good idea to allow locking reads in a view, or in joins whose execution order might not be deterministic, depending on the query plan? Note: there is also LOCK IN SHARE MODE .
marko, I agree with you, but since the documentation does not prohibit this and in version 10.3 this test passes successfully, I was forced to create a bug.
Lena Startseva
added a comment - marko , I agree with you, but since the documentation does not prohibit this and in version 10.3 this test passes successfully, I was forced to create a bug.
select 1 AS `1` from `test`.`t1` union select 1 AS `1` from DUAL where 1 group by 1 having 1 for update order by 1
Oleksandr Byelkin
added a comment - It printed in view as:
select 1 AS `1` from `test`.`t1` union select 1 AS `1` from DUAL where 1 group by 1 having 1 for update order by 1
lock type should go to fake_select but somehow it appeared in the last select:
(gdb) p fake_select_lex->lock_type
$1 = TL_READ_DEFAULT
(gdb) p first_select()->lock_type
$2 = TL_READ_DEFAULT
(gdb) p first_select()->next_select()->lock_type
$3 = TL_WRITE
Oleksandr Byelkin
added a comment - - edited lock type should go to fake_select but somehow it appeared in the last select:
(gdb) p fake_select_lex->lock_type
$1 = TL_READ_DEFAULT
(gdb) p first_select()->lock_type
$2 = TL_READ_DEFAULT
(gdb) p first_select()->next_select()->lock_type
$3 = TL_WRITE
MDEV-29537 Creation of view with UNION and SELECT ... FOR UPDATE in definition is failed with error
lock_type writen in the last SELECT of the unit even it parsed last,
so it should be printed last from the last select of the unit.
Oleksandr Byelkin
added a comment -
commit 365e7db6990aa10f6137ce37d2e5251d6c64ad9f (HEAD -> bb-10.5-MDEV-29537, origin/bb-10.5-MDEV-29537)
Author: Oleksandr Byelkin <sanja@mariadb.com>
Date: Fri Sep 20 14:58:23 2024 +0200
MDEV-29537 Creation of view with UNION and SELECT ... FOR UPDATE in definition is failed with error
lock_type writen in the last SELECT of the unit even it parsed last,
so it should be printed last from the last select of the unit.
MDEV-29537 Creation of view with UNION and SELECT ... FOR UPDATE in definition is failed with error
lock_type writen in the last SELECT of the unit even it parsed last,
so it should be printed last from the last select of the unit.
Oleksandr Byelkin
added a comment -
commit 7043423f37d8a58fe9ac99a1b3ef25dd6f2d796c (HEAD -> bb-10.5-MDEV-29537, origin/bb-10.5-MDEV-29537)
Author: Oleksandr Byelkin <sanja@mariadb.com>
Date: Fri Sep 20 14:58:23 2024 +0200
MDEV-29537 Creation of view with UNION and SELECT ... FOR UPDATE in definition is failed with error
lock_type writen in the last SELECT of the unit even it parsed last,
so it should be printed last from the last select of the unit.
People
Nikita Malyavin
Lena Startseva
Votes:
0Vote for this issue
Watchers:
4Start 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.
{"report":{"fcp":954.3000001907349,"ttfb":225.5,"pageVisibility":"visible","entityId":114681,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":32,"apdex":0.5,"journeyId":"c3af8e15-7efd-4948-bb03-ba78162bacdd","navigationType":0,"readyForUser":1102,"redirectCount":0,"resourceLoadedEnd":1183.0999999046326,"resourceLoadedStart":230.2000002861023,"resourceTiming":[{"duration":123.2999997138977,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":230.2000002861023,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":230.2000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":353.5,"responseStart":0,"secureConnectionStart":0},{"duration":123.30000019073486,"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":230.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":230.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":353.80000019073486,"responseStart":0,"secureConnectionStart":0},{"duration":132.60000038146973,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":230.59999990463257,"connectEnd":230.59999990463257,"connectStart":230.59999990463257,"domainLookupEnd":230.59999990463257,"domainLookupStart":230.59999990463257,"fetchStart":230.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":230.59999990463257,"responseEnd":363.2000002861023,"responseStart":363.2000002861023,"secureConnectionStart":230.59999990463257},{"duration":199.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":230.80000019073486,"connectEnd":230.80000019073486,"connectStart":230.80000019073486,"domainLookupEnd":230.80000019073486,"domainLookupStart":230.80000019073486,"fetchStart":230.80000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":230.80000019073486,"responseEnd":429.90000009536743,"responseStart":429.90000009536743,"secureConnectionStart":230.80000019073486},{"duration":202.5,"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":231,"connectEnd":231,"connectStart":231,"domainLookupEnd":231,"domainLookupStart":231,"fetchStart":231,"redirectEnd":0,"redirectStart":0,"requestStart":231,"responseEnd":433.5,"responseStart":433.5,"secureConnectionStart":231},{"duration":203.39999961853027,"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":231.2000002861023,"connectEnd":231.2000002861023,"connectStart":231.2000002861023,"domainLookupEnd":231.2000002861023,"domainLookupStart":231.2000002861023,"fetchStart":231.2000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":231.2000002861023,"responseEnd":434.59999990463257,"responseStart":434.59999990463257,"secureConnectionStart":231.2000002861023},{"duration":203.69999980926514,"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":231.40000009536743,"connectEnd":231.40000009536743,"connectStart":231.40000009536743,"domainLookupEnd":231.40000009536743,"domainLookupStart":231.40000009536743,"fetchStart":231.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":231.40000009536743,"responseEnd":435.09999990463257,"responseStart":435.09999990463257,"secureConnectionStart":231.40000009536743},{"duration":290.90000009536743,"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":231.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":231.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":522.4000000953674,"responseStart":0,"secureConnectionStart":0},{"duration":203.7999997138977,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":231.7000002861023,"connectEnd":231.7000002861023,"connectStart":231.7000002861023,"domainLookupEnd":231.7000002861023,"domainLookupStart":231.7000002861023,"fetchStart":231.7000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":231.7000002861023,"responseEnd":435.5,"responseStart":435.5,"secureConnectionStart":231.7000002861023},{"duration":290.59999990463257,"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":231.90000009536743,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":231.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":522.5,"responseStart":0,"secureConnectionStart":0},{"duration":204.10000038146973,"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":232.09999990463257,"connectEnd":232.09999990463257,"connectStart":232.09999990463257,"domainLookupEnd":232.09999990463257,"domainLookupStart":232.09999990463257,"fetchStart":232.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":232.09999990463257,"responseEnd":436.2000002861023,"responseStart":436.09999990463257,"secureConnectionStart":232.09999990463257},{"duration":352.5,"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":245.90000009536743,"connectEnd":245.90000009536743,"connectStart":245.90000009536743,"domainLookupEnd":245.90000009536743,"domainLookupStart":245.90000009536743,"fetchStart":245.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":245.90000009536743,"responseEnd":598.4000000953674,"responseStart":598.4000000953674,"secureConnectionStart":245.90000009536743},{"duration":601.5999999046326,"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":252.40000009536743,"connectEnd":252.40000009536743,"connectStart":252.40000009536743,"domainLookupEnd":252.40000009536743,"domainLookupStart":252.40000009536743,"fetchStart":252.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":252.40000009536743,"responseEnd":854,"responseStart":854,"secureConnectionStart":252.40000009536743},{"duration":42.40000009536743,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":558.8000001907349,"connectEnd":558.8000001907349,"connectStart":558.8000001907349,"domainLookupEnd":558.8000001907349,"domainLookupStart":558.8000001907349,"fetchStart":558.8000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":558.8000001907349,"responseEnd":601.2000002861023,"responseStart":601.2000002861023,"secureConnectionStart":558.8000001907349},{"duration":247.90000009536743,"initiatorType":"link","name":"https://jira.mariadb.org/s/d5715adaadd168a9002b108b2b039b50-CDN/lu2cib/820016/12ta74/be4b45e9cec53099498fa61c8b7acba4/_/download/contextbatch/css/jira.project.sidebar,-_super,-project.issue.navigator,-jira.general,-jira.browse.project,-jira.view.issue,-jira.global,-atl.general,-com.atlassian.jira.projects.sidebar.init/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":614.8000001907349,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":614.8000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":862.7000002861023,"responseStart":0,"secureConnectionStart":0},{"duration":567.3999996185303,"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":615.7000002861023,"connectEnd":615.7000002861023,"connectStart":615.7000002861023,"domainLookupEnd":615.7000002861023,"domainLookupStart":615.7000002861023,"fetchStart":615.7000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":615.7000002861023,"responseEnd":1183.0999999046326,"responseStart":1183.0999999046326,"secureConnectionStart":615.7000002861023},{"duration":332.2000002861023,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":935.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":935.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1267.7000002861023,"responseStart":0,"secureConnectionStart":0},{"duration":300,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":953.4000000953674,"connectEnd":953.4000000953674,"connectStart":953.4000000953674,"domainLookupEnd":953.4000000953674,"domainLookupStart":953.4000000953674,"fetchStart":953.4000000953674,"redirectEnd":0,"redirectStart":0,"requestStart":953.4000000953674,"responseEnd":1253.4000000953674,"responseStart":1253.4000000953674,"secureConnectionStart":953.4000000953674},{"duration":590.2999997138977,"initiatorType":"script","name":"https://jira.mariadb.org/s/097ae97cb8fbec7d6ea4bbb1f26955b9-CDN/lu2cib/820016/12ta74/be4b45e9cec53099498fa61c8b7acba4/_/download/contextbatch/js/jira.project.sidebar,-_super,-project.issue.navigator,-jira.general,-jira.browse.project,-jira.view.issue,-jira.global,-atl.general,-com.atlassian.jira.projects.sidebar.init/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true&whisper-enabled=true","startTime":616.2000002861023,"connectEnd":616.2000002861023,"connectStart":616.2000002861023,"domainLookupEnd":616.2000002861023,"domainLookupStart":616.2000002861023,"fetchStart":616.2000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":616.2000002861023,"responseEnd":1206.5,"responseStart":1206.5,"secureConnectionStart":616.2000002861023}],"fetchStart":1,"domainLookupStart":1,"domainLookupEnd":1,"connectStart":1,"connectEnd":1,"requestStart":58,"responseStart":226,"responseEnd":252,"domLoading":229,"domInteractive":1196,"domContentLoadedEventStart":1196,"domContentLoadedEventEnd":1267,"domComplete":1563,"loadEventStart":1563,"loadEventEnd":1564,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1151.7000002861023},{"name":"bigPipe.sidebar-id.end","time":1152.5},{"name":"bigPipe.activity-panel-pipe-id.start","time":1152.7000002861023},{"name":"bigPipe.activity-panel-pipe-id.end","time":1155.2000002861023},{"name":"activityTabFullyLoaded","time":1288.2000002861023}],"measures":[],"correlationId":"1fe8f98192014","effectiveType":"4g","downlink":9.2,"rtt":0,"serverDuration":102,"dbReadsTimeInMs":13,"dbConnsTimeInMs":21,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Bug
Major
MDEV-27691
make working view-protocol
Is it a good idea to allow locking reads in a view, or in joins whose execution order might not be deterministic, depending on the query plan? Note: there is also LOCK IN SHARE MODE.