MDEV-12459 introduced table_type TEMPORARY for temporary tables. However, for temporary sequences I_S.TABLES shows the generic SEQUENCE, same as for normal sequences.
They are still distinguishable via TEMPORARY column in I_S.TABLES, but it appears inconsistent, and besides there is a discussion in MDEV-12459 about removing the column.
createsequence s;
createtemporarysequence s;
select table_name, table_type, temporaryfrom information_schema.tables where table_name = 's';
# Cleanup
dropsequence s;
dropsequence s;
preview-10.9-MDEV-20119-misc c906db30
select table_name, table_type, temporaryfrom information_schema.tables where table_name = 's';
table_name table_type temporary
s SEQUENCE Y
s SEQUENCE N
Attachments
Issue Links
is caused by
MDEV-12459The information_schema tables for getting temporary tables info is missing, at least for innodb there is no INNODB_TEMP_TABLE_INFO
It is still a bit of a problem (although a small one, considering the low probability of using temporary sequences in real life and even lower probability of overshadowing).
Suppose we have
createtable t (a int);
createtemporarysequence t;
The old version of sys.table_exists, even though it doesn't understand sequences at all (see MDEV-28340), would still return in this case a close-to-valid TEMPORARY.
However, the new version fails with
preview-10.9-MDEV-20119-misc c906db3033
mysqltest: At line 3: query 'call sys.table_exists('test','t',@a)' failed: ER_SUBQUERY_NO_1_ROW (1242): Subquery returns more than 1 row
– understandably, since the check for TABLE_TYPE = 'temporary' returns nothing, and the next SELECT returns both rows.
Elena Stepanova
added a comment - - edited It is still a bit of a problem (although a small one, considering the low probability of using temporary sequences in real life and even lower probability of overshadowing).
Suppose we have
create table t (a int );
create temporary sequence t;
The old version of sys.table_exists , even though it doesn't understand sequences at all (see MDEV-28340 ), would still return in this case a close-to-valid TEMPORARY .
However, the new version fails with
preview-10.9-MDEV-20119-misc c906db3033
mysqltest: At line 3: query 'call sys.table_exists(' test ',' t ',@a)' failed: ER_SUBQUERY_NO_1_ROW (1242): Subquery returns more than 1 row
– understandably, since the check for TABLE_TYPE = 'temporary' returns nothing, and the next SELECT returns both rows.
I don't know the sys schema code and I don't know how did you get result from above (I understand that is from the latest patch and why did it fail but don't know how sys schema should be tested)?
Looking on the number of MDEV's related to sys schema and my patch seems I need to learn it .
Note our sys schema documentation is currently incomplete.
Anel Husakovic
added a comment - I don't know the sys schema code and I don't know how did you get result from above (I understand that is from the latest patch and why did it fail but don't know how sys schema should be tested)?
Looking on the number of MDEV's related to sys schema and my patch seems I need to learn it .
Note our sys schema documentation is currently incomplete.
anel The commit you linked only seems to contain the test case, not the actual code changes and I can't figure out from the log which other commit I'm supposed to look at as it seems Monty reviewed the other ones. Can you please create a commit that covers the changes required by this MDEV and then send it for review?
The test case does show that having 2 tables (or rather sequences), one shadowing the other one makes sys.table_exists return the type of the one that's visible. That's ok (I think). We need to document this behavior properly.
Additionally, from the test case the output doesn't match what I would expect the output of sys.table_exists to be. Like elenst mentioned, when executing the stored procedure on a temporary sequence one gets a close-to-valid TEMPORARY. I would extend the behavior for it to properly say TEMPORARY SEQUENCE when we do pass it a temporary sequence, much like you extended it to say SEQUENCE.
greenman remember this MDEV when documenting sys_schema.
Vicențiu Ciorbaru
added a comment - anel The commit you linked only seems to contain the test case, not the actual code changes and I can't figure out from the log which other commit I'm supposed to look at as it seems Monty reviewed the other ones. Can you please create a commit that covers the changes required by this MDEV and then send it for review?
The test case does show that having 2 tables (or rather sequences), one shadowing the other one makes sys.table_exists return the type of the one that's visible. That's ok (I think). We need to document this behavior properly.
https://mariadb.com/kb/en/sys-schema/ has no info at all regarding this intended behaviour. https://dev.mysql.com/doc/refman/8.0/en/sys-table-exists.html does, but MySQL does not have any temporary sequences.
Additionally, from the test case the output doesn't match what I would expect the output of sys.table_exists to be. Like elenst mentioned, when executing the stored procedure on a temporary sequence one gets a close-to-valid TEMPORARY . I would extend the behavior for it to properly say TEMPORARY SEQUENCE when we do pass it a temporary sequence, much like you extended it to say SEQUENCE .
greenman remember this MDEV when documenting sys_schema.
Anel, please close this when that patch is pushed.
Vicențiu Ciorbaru
added a comment - This MDEV covers the last review comment from MDEV-12459 .
We will introduce TEMPORARY SEQUENCE as a string in I_S.tables.
create table t1 (a int);
create sequence s1;
create temporary table t1 (b int);
create temporary sequence s1;
select table_schema, table_name, table_type, temporary
from information_schema.tables
where table_schema = 'test';
table_schema table_name table_type temporary
test s1 TEMPORARY SEQUENCE Y
test t1 TEMPORARY Y
test t1 BASE TABLE N
test s1 SEQUENCE N
Anel, please close this when that patch is pushed.
People
Anel Husakovic
Elena Stepanova
Votes:
0Vote for this issue
Watchers:
5Start 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":949.2000000029802,"ttfb":182.09999999403954,"pageVisibility":"visible","entityId":110022,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"b2e5e24b-72d7-4576-bdc4-784383b6404b","navigationType":0,"readyForUser":1029.0999999940395,"redirectCount":0,"resourceLoadedEnd":1368.0999999940395,"resourceLoadedStart":194.79999999701977,"resourceTiming":[{"duration":239.29999999701977,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":194.79999999701977,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":194.79999999701977,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":434.09999999403954,"responseStart":0,"secureConnectionStart":0},{"duration":239.20000000298023,"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":195.09999999403954,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":195.09999999403954,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":434.29999999701977,"responseStart":0,"secureConnectionStart":0},{"duration":254.59999999403954,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":195.20000000298023,"connectEnd":195.20000000298023,"connectStart":195.20000000298023,"domainLookupEnd":195.20000000298023,"domainLookupStart":195.20000000298023,"fetchStart":195.20000000298023,"redirectEnd":0,"redirectStart":0,"requestStart":195.20000000298023,"responseEnd":449.79999999701977,"responseStart":449.79999999701977,"secureConnectionStart":195.20000000298023},{"duration":341.8999999910593,"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":195.5,"connectEnd":195.5,"connectStart":195.5,"domainLookupEnd":195.5,"domainLookupStart":195.5,"fetchStart":195.5,"redirectEnd":0,"redirectStart":0,"requestStart":195.5,"responseEnd":537.3999999910593,"responseStart":537.3999999910593,"secureConnectionStart":195.5},{"duration":345.3999999910593,"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":195.70000000298023,"connectEnd":195.70000000298023,"connectStart":195.70000000298023,"domainLookupEnd":195.70000000298023,"domainLookupStart":195.70000000298023,"fetchStart":195.70000000298023,"redirectEnd":0,"redirectStart":0,"requestStart":195.70000000298023,"responseEnd":541.0999999940395,"responseStart":541.0999999940395,"secureConnectionStart":195.70000000298023},{"duration":345.90000000596046,"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":195.8999999910593,"connectEnd":195.8999999910593,"connectStart":195.8999999910593,"domainLookupEnd":195.8999999910593,"domainLookupStart":195.8999999910593,"fetchStart":195.8999999910593,"redirectEnd":0,"redirectStart":0,"requestStart":195.8999999910593,"responseEnd":541.7999999970198,"responseStart":541.7999999970198,"secureConnectionStart":195.8999999910593},{"duration":346.59999999403954,"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":196,"connectEnd":196,"connectStart":196,"domainLookupEnd":196,"domainLookupStart":196,"fetchStart":196,"redirectEnd":0,"redirectStart":0,"requestStart":196,"responseEnd":542.5999999940395,"responseStart":542.5999999940395,"secureConnectionStart":196},{"duration":395.3999999910593,"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":196.20000000298023,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":196.20000000298023,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":591.5999999940395,"responseStart":0,"secureConnectionStart":0},{"duration":347.1000000089407,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":196.3999999910593,"connectEnd":196.3999999910593,"connectStart":196.3999999910593,"domainLookupEnd":196.3999999910593,"domainLookupStart":196.3999999910593,"fetchStart":196.3999999910593,"redirectEnd":0,"redirectStart":0,"requestStart":196.3999999910593,"responseEnd":543.5,"responseStart":543.5,"secureConnectionStart":196.3999999910593},{"duration":395.20000000298023,"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":196.59999999403954,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":196.59999999403954,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":591.7999999970198,"responseStart":0,"secureConnectionStart":0},{"duration":347.5,"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":196.70000000298023,"connectEnd":196.70000000298023,"connectStart":196.70000000298023,"domainLookupEnd":196.70000000298023,"domainLookupStart":196.70000000298023,"fetchStart":196.70000000298023,"redirectEnd":0,"redirectStart":0,"requestStart":196.70000000298023,"responseEnd":544.2000000029802,"responseStart":544.2000000029802,"secureConnectionStart":196.70000000298023},{"duration":477.59999999403954,"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":197.70000000298023,"connectEnd":197.70000000298023,"connectStart":197.70000000298023,"domainLookupEnd":197.70000000298023,"domainLookupStart":197.70000000298023,"fetchStart":197.70000000298023,"redirectEnd":0,"redirectStart":0,"requestStart":197.70000000298023,"responseEnd":675.2999999970198,"responseStart":675.2999999970198,"secureConnectionStart":197.70000000298023},{"duration":1168.7999999970198,"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":197.79999999701977,"connectEnd":197.79999999701977,"connectStart":197.79999999701977,"domainLookupEnd":197.79999999701977,"domainLookupStart":197.79999999701977,"fetchStart":197.79999999701977,"redirectEnd":0,"redirectStart":0,"requestStart":197.79999999701977,"responseEnd":1366.5999999940395,"responseStart":1366.5999999940395,"secureConnectionStart":197.79999999701977},{"duration":59.29999999701977,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":603.2999999970198,"connectEnd":603.2999999970198,"connectStart":603.2999999970198,"domainLookupEnd":603.2999999970198,"domainLookupStart":603.2999999970198,"fetchStart":603.2999999970198,"redirectEnd":0,"redirectStart":0,"requestStart":603.2999999970198,"responseEnd":662.5999999940395,"responseStart":662.5999999940395,"secureConnectionStart":603.2999999970198},{"duration":504,"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":863.8999999910593,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":863.8999999910593,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1367.8999999910593,"responseStart":0,"secureConnectionStart":0},{"duration":503.59999999403954,"initiatorType":"link","name":"https://jira.mariadb.org/s/50bc9be5bfead1a25e72c1a9338c94f6-CDN/lu2cib/820016/12ta74/e108c7645258ccb43280ed3404e3e949/_/download/contextbatch/css/com.atlassian.jira.plugins.jira-development-integration-plugin:0,-_super,-jira.view.issue,-jira.global,-jira.general,-jira.browse.project,-project.issue.navigator,-atl.general/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":864.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":864.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1368.0999999940395,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":15,"responseStart":182,"responseEnd":189,"domLoading":192,"domInteractive":1398,"domContentLoadedEventStart":1398,"domContentLoadedEventEnd":1443,"domComplete":2078,"loadEventStart":2078,"loadEventEnd":2079,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1371.5},{"name":"bigPipe.sidebar-id.end","time":1372.2999999970198},{"name":"bigPipe.activity-panel-pipe-id.start","time":1372.5},{"name":"bigPipe.activity-panel-pipe-id.end","time":1375.2999999970198},{"name":"activityTabFullyLoaded","time":1459.8999999910593}],"measures":[],"correlationId":"6d63e5b442226c","effectiveType":"4g","downlink":9.9,"rtt":0,"serverDuration":103,"dbReadsTimeInMs":12,"dbConnsTimeInMs":20,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
I have updated
MDEV-12459and I don't think this is a bug, two sequences are distinguished by the temporary column.