The below worked fine so far, as expected, but starts to throw unexpected
ERROR 1292 (22007) at line 15: Truncated incorrect DECIMAL value: '#'
in most recent releases. I looked over the 10.3.37 (lowest version release I can reproduce this on) release notes and changelog page, but could not find any change that seemed to be related to this change in behavior ...
droptableif exists t1;
droptableif exists t2;
createtable t1(c1 varchar(1));
createtable t2(c1 varchar(1));
set session sql_mode = STRICT_TRANS_TABLES;
insertinto t1(c1) values('#');
insertinto t2(c1)
selectif(c1 = '#', c1 = 0, c1) as c1
from t1;
Attachments
Issue Links
is caused by
MDEV-29540Incorrect sequence values in INSERT SELECT
While definitely a regression, isn't the current behaviour correct in emitting an error on the if function returning two different types? So is this actually a bug?
Taking a CREATE TABLE .. SELECT case
createtable t2 asselectif(c1 = '#', c1 = 0, c1) as c1 from t1
: query 'create table t2 as select if(c1 = '#', c1 = 0, c1) as c1 from t1' failed: 1292: Truncated incorrect DECIMAL value: '#'
This produces the same error even before MDEV-29540.
Daniel Black
added a comment - - edited While definitely a regression, isn't the current behaviour correct in emitting an error on the if function returning two different types? So is this actually a bug?
Taking a CREATE TABLE .. SELECT case
create table t2 as select if (c1 = '#' , c1 = 0, c1) as c1 from t1
: query 'create table t2 as select if(c1 = ' # ', c1 = 0, c1) as c1 from t1' failed: 1292: Truncated incorrect DECIMAL value: '#'
This produces the same error even before MDEV-29540 .
No, the current behavior is wrong. There are few considerations here:
sql_mode is STRICT_TRANS_TABLES but the test has no transactional tables, so CREATE ... SELECT should only produce a warning
but the original meaning of the STRICT_TRANS_TABLES was kind of "be strict if the statement can be completely rolled back", and unlike INSERT ... SELECT a CREATE ... SELECT can be "completely rolled back" even for non-transactional tables. In that logic, CREATE ... SELECT behaves correctly and INSERT ... SELECT did behave correctly before 8c389393695.
still currently we mainly interpret STRICT_TRANS_TABLES as "modifying transactional tables" and here the warning happens not on INSERT as such, but in the comparison. In that logic, this warning should be never treated as an error in any sql_mode and any table, transactional or not.
This should be eventually solved in MDEV-19362 (see also the discussion in comments). For now let's just fix the regression and restore the old behavior.
Sergei Golubchik
added a comment - No, the current behavior is wrong. There are few considerations here:
sql_mode is STRICT_TRANS_TABLES but the test has no transactional tables, so CREATE ... SELECT should only produce a warning
but the original meaning of the STRICT_TRANS_TABLES was kind of "be strict if the statement can be completely rolled back", and unlike INSERT ... SELECT a CREATE ... SELECT can be "completely rolled back" even for non-transactional tables. In that logic, CREATE ... SELECT behaves correctly and INSERT ... SELECT did behave correctly before 8c389393695 .
still currently we mainly interpret STRICT_TRANS_TABLES as "modifying transactional tables" and here the warning happens not on INSERT as such, but in the comparison. In that logic, this warning should be never treated as an error in any sql_mode and any table, transactional or not.
This should be eventually solved in MDEV-19362 (see also the discussion in comments). For now let's just fix the regression and restore the old behavior.
People
Daniel Black
Hartmut Holzgraefe
Votes:
0Vote for this issue
Watchers:
6Start 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":1718.2999999970198,"ttfb":467,"pageVisibility":"visible","entityId":117995,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"fd325705-8f7a-4df4-a467-f3abfad33068","navigationType":0,"readyForUser":1793.5,"redirectCount":0,"resourceLoadedEnd":2078.699999988079,"resourceLoadedStart":474.19999998807907,"resourceTiming":[{"duration":786.4000000059605,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":474.19999998807907,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":474.19999998807907,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1260.5999999940395,"responseStart":0,"secureConnectionStart":0},{"duration":786.6000000089407,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/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":474.3999999910593,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":474.3999999910593,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1261,"responseStart":0,"secureConnectionStart":0},{"duration":798.5999999940395,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":474.59999999403954,"connectEnd":474.59999999403954,"connectStart":474.59999999403954,"domainLookupEnd":474.59999999403954,"domainLookupStart":474.59999999403954,"fetchStart":474.59999999403954,"redirectEnd":0,"redirectStart":0,"requestStart":474.59999999403954,"responseEnd":1273.199999988079,"responseStart":1273.199999988079,"secureConnectionStart":474.59999999403954},{"duration":853.7999999970198,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/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":474.8999999910593,"connectEnd":474.8999999910593,"connectStart":474.8999999910593,"domainLookupEnd":474.8999999910593,"domainLookupStart":474.8999999910593,"fetchStart":474.8999999910593,"redirectEnd":0,"redirectStart":0,"requestStart":474.8999999910593,"responseEnd":1328.699999988079,"responseStart":1328.699999988079,"secureConnectionStart":474.8999999910593},{"duration":857.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":475,"connectEnd":475,"connectStart":475,"domainLookupEnd":475,"domainLookupStart":475,"fetchStart":475,"redirectEnd":0,"redirectStart":0,"requestStart":475,"responseEnd":1332.3999999910593,"responseStart":1332.3999999910593,"secureConnectionStart":475},{"duration":857.7000000029802,"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":475.19999998807907,"connectEnd":475.19999998807907,"connectStart":475.19999998807907,"domainLookupEnd":475.19999998807907,"domainLookupStart":475.19999998807907,"fetchStart":475.19999998807907,"redirectEnd":0,"redirectStart":0,"requestStart":475.19999998807907,"responseEnd":1332.8999999910593,"responseStart":1332.8999999910593,"secureConnectionStart":475.19999998807907},{"duration":857.9000000059605,"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":475.3999999910593,"connectEnd":475.3999999910593,"connectStart":475.3999999910593,"domainLookupEnd":475.3999999910593,"domainLookupStart":475.3999999910593,"fetchStart":475.3999999910593,"redirectEnd":0,"redirectStart":0,"requestStart":475.3999999910593,"responseEnd":1333.2999999970198,"responseStart":1333.199999988079,"secureConnectionStart":475.3999999910593},{"duration":940.5,"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":475.59999999403954,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":475.59999999403954,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1416.0999999940395,"responseStart":0,"secureConnectionStart":0},{"duration":857.8999999910593,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":475.79999999701977,"connectEnd":475.79999999701977,"connectStart":475.79999999701977,"domainLookupEnd":475.79999999701977,"domainLookupStart":475.79999999701977,"fetchStart":475.79999999701977,"redirectEnd":0,"redirectStart":0,"requestStart":475.79999999701977,"responseEnd":1333.699999988079,"responseStart":1333.699999988079,"secureConnectionStart":475.79999999701977},{"duration":940.1999999880791,"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":476,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":476,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1416.199999988079,"responseStart":0,"secureConnectionStart":0},{"duration":858.2000000029802,"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":476.09999999403954,"connectEnd":476.09999999403954,"connectStart":476.09999999403954,"domainLookupEnd":476.09999999403954,"domainLookupStart":476.09999999403954,"fetchStart":476.09999999403954,"redirectEnd":0,"redirectStart":0,"requestStart":476.09999999403954,"responseEnd":1334.2999999970198,"responseStart":1334.2999999970198,"secureConnectionStart":476.09999999403954},{"duration":1598.2999999970198,"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":479.8999999910593,"connectEnd":479.8999999910593,"connectStart":479.8999999910593,"domainLookupEnd":479.8999999910593,"domainLookupStart":479.8999999910593,"fetchStart":479.8999999910593,"redirectEnd":0,"redirectStart":0,"requestStart":479.8999999910593,"responseEnd":2078.199999988079,"responseStart":2078.199999988079,"secureConnectionStart":479.8999999910593},{"duration":1598.699999988079,"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":480,"connectEnd":480,"connectStart":480,"domainLookupEnd":480,"domainLookupStart":480,"fetchStart":480,"redirectEnd":0,"redirectStart":0,"requestStart":480,"responseEnd":2078.699999988079,"responseStart":2078.699999988079,"secureConnectionStart":480},{"duration":514.1000000089407,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1428.199999988079,"connectEnd":1428.199999988079,"connectStart":1428.199999988079,"domainLookupEnd":1428.199999988079,"domainLookupStart":1428.199999988079,"fetchStart":1428.199999988079,"redirectEnd":0,"redirectStart":0,"requestStart":1428.199999988079,"responseEnd":1942.2999999970198,"responseStart":1942.199999988079,"secureConnectionStart":1428.199999988079},{"duration":405,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1712.3999999910593,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1712.3999999910593,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":2117.3999999910593,"responseStart":0,"secureConnectionStart":0}],"fetchStart":1,"domainLookupStart":1,"domainLookupEnd":1,"connectStart":1,"connectEnd":1,"requestStart":290,"responseStart":468,"responseEnd":480,"domLoading":471,"domInteractive":2120,"domContentLoadedEventStart":2120,"domContentLoadedEventEnd":2163,"domComplete":2663,"loadEventStart":2663,"loadEventEnd":2663,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":2081.699999988079},{"name":"bigPipe.sidebar-id.end","time":2082.3999999910593},{"name":"bigPipe.activity-panel-pipe-id.start","time":2082.5999999940395},{"name":"bigPipe.activity-panel-pipe-id.end","time":2084.199999988079},{"name":"activityTabFullyLoaded","time":2177.5999999940395}],"measures":[],"correlationId":"e62c430b71849","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":101,"dbReadsTimeInMs":11,"dbConnsTimeInMs":19,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
caused by https://github.com/MariaDB/server/commit/8c389393695