The first column should return '2005-03-26' with a warning.
Alexander Barkov
added a comment - - edited Examples when trailing garbage behaviour depends on the data type
SELECT CAST ( '20050326 garbage' as date ), CAST ( '50326 garbage' as time );
SHOW WARNINGS;
+----------------------------------+-------------------------------+
| CAST('20050326 garbage' as date) | CAST('50326 garbage' as time) |
+----------------------------------+-------------------------------+
| NULL | 05:03:26 |
+----------------------------------+-------------------------------+
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '20050326 garbage' |
| Warning | 1292 | Truncated incorrect time value: '50326 garbage' |
+---------+------+-------------------------------------------------+
The first column should return '2005-03-26' with a warning.
'sign space hh:mm:ss' works fine, 'sign space DD hh:mm:ss' does not work well:
SELECTTIME('- 01:00:00'), TIME('- 1 01:00:00');
+--------------------+----------------------+
| TIME('- 01:00:00') | TIME('- 1 01:00:00') |
+--------------------+----------------------+
| -01:00:00 | -01:00:00 |
+--------------------+----------------------+
The expected result for the second column is '-25:00:00'.
Alexander Barkov
added a comment - - edited 'sign space hh:mm:ss' works fine, 'sign space DD hh:mm:ss' does not work well:
SELECT TIME ( '- 01:00:00' ), TIME ( '- 1 01:00:00' );
+--------------------+----------------------+
| TIME('- 01:00:00') | TIME('- 1 01:00:00') |
+--------------------+----------------------+
| -01:00:00 | -01:00:00 |
+--------------------+----------------------+
The expected result for the second column is '-25:00:00'.
People
Alexander Barkov
Alexander Barkov
Votes:
0Vote for this issue
Watchers:
1Start 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":721.3999998569489,"ttfb":162.09999990463257,"pageVisibility":"visible","entityId":70328,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":1,"journeyId":"0537e370-3ae2-4079-aab9-16e4865a3a63","navigationType":0,"readyForUser":832.2999999523163,"redirectCount":0,"resourceLoadedEnd":475.39999985694885,"resourceLoadedStart":169.59999990463257,"resourceTiming":[{"duration":3.5,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":169.59999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":169.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":173.09999990463257,"responseStart":0,"secureConnectionStart":0},{"duration":3.799999952316284,"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":169.79999995231628,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":169.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":173.59999990463257,"responseStart":0,"secureConnectionStart":0},{"duration":165.20000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":169.89999985694885,"connectEnd":175.69999980926514,"connectStart":175.69999980926514,"domainLookupEnd":175.69999980926514,"domainLookupStart":175.69999980926514,"fetchStart":169.89999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":175.79999995231628,"responseEnd":335.09999990463257,"responseStart":188.89999985694885,"secureConnectionStart":175.69999980926514},{"duration":278.5,"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":170,"connectEnd":170,"connectStart":170,"domainLookupEnd":170,"domainLookupStart":170,"fetchStart":170,"redirectEnd":0,"redirectStart":0,"requestStart":177.09999990463257,"responseEnd":448.5,"responseStart":196.59999990463257,"secureConnectionStart":170},{"duration":26.299999952316284,"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":170.09999990463257,"connectEnd":170.09999990463257,"connectStart":170.09999990463257,"domainLookupEnd":170.09999990463257,"domainLookupStart":170.09999990463257,"fetchStart":170.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":177.79999995231628,"responseEnd":196.39999985694885,"responseStart":194.59999990463257,"secureConnectionStart":170.09999990463257},{"duration":27.200000047683716,"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":170.09999990463257,"connectEnd":170.09999990463257,"connectStart":170.09999990463257,"domainLookupEnd":170.09999990463257,"domainLookupStart":170.09999990463257,"fetchStart":170.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":177.19999980926514,"responseEnd":197.29999995231628,"responseStart":195.39999985694885,"secureConnectionStart":170.09999990463257},{"duration":34,"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":170.29999995231628,"connectEnd":170.29999995231628,"connectStart":170.29999995231628,"domainLookupEnd":170.29999995231628,"domainLookupStart":170.29999995231628,"fetchStart":170.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":178.79999995231628,"responseEnd":204.29999995231628,"responseStart":203,"secureConnectionStart":170.29999995231628},{"duration":8,"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":170.39999985694885,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":170.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":178.39999985694885,"responseStart":0,"secureConnectionStart":0},{"duration":44.799999952316284,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":170.5,"connectEnd":170.5,"connectStart":170.5,"domainLookupEnd":170.5,"domainLookupStart":170.5,"fetchStart":170.5,"redirectEnd":0,"redirectStart":0,"requestStart":181.79999995231628,"responseEnd":215.29999995231628,"responseStart":207.29999995231628,"secureConnectionStart":170.5},{"duration":9.900000095367432,"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":170.59999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":170.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":180.5,"responseStart":0,"secureConnectionStart":0},{"duration":46.10000014305115,"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":170.69999980926514,"connectEnd":170.69999980926514,"connectStart":170.69999980926514,"domainLookupEnd":170.69999980926514,"domainLookupStart":170.69999980926514,"fetchStart":170.69999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":183.09999990463257,"responseEnd":216.79999995231628,"responseStart":208.19999980926514,"secureConnectionStart":170.69999980926514},{"duration":300.7999999523163,"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":174.59999990463257,"connectEnd":174.59999990463257,"connectStart":174.59999990463257,"domainLookupEnd":174.59999990463257,"domainLookupStart":174.59999990463257,"fetchStart":174.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":186.59999990463257,"responseEnd":475.39999985694885,"responseStart":471.89999985694885,"secureConnectionStart":174.59999990463257},{"duration":268.5,"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":205.29999995231628,"connectEnd":205.29999995231628,"connectStart":205.29999995231628,"domainLookupEnd":205.29999995231628,"domainLookupStart":205.29999995231628,"fetchStart":205.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":216.59999990463257,"responseEnd":473.7999999523163,"responseStart":468.69999980926514,"secureConnectionStart":205.29999995231628},{"duration":50.90000009536743,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":480.59999990463257,"connectEnd":480.59999990463257,"connectStart":480.59999990463257,"domainLookupEnd":480.59999990463257,"domainLookupStart":480.59999990463257,"fetchStart":480.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":499.5,"responseEnd":531.5,"responseStart":530.8999998569489,"secureConnectionStart":480.59999990463257},{"duration":90.10000014305115,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":714.8999998569489,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":714.8999998569489,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":805,"responseStart":0,"secureConnectionStart":0},{"duration":115.90000009536743,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":721.5999999046326,"connectEnd":721.5999999046326,"connectStart":721.5999999046326,"domainLookupEnd":721.5999999046326,"domainLookupStart":721.5999999046326,"fetchStart":721.5999999046326,"redirectEnd":0,"redirectStart":0,"requestStart":805.6999998092651,"responseEnd":837.5,"responseStart":836.3999998569489,"secureConnectionStart":721.5999999046326}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":20,"responseStart":162,"responseEnd":205,"domLoading":165,"domInteractive":909,"domContentLoadedEventStart":909,"domContentLoadedEventEnd":969,"domComplete":1105,"loadEventStart":1105,"loadEventEnd":1106,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":871.1999998092651},{"name":"bigPipe.sidebar-id.end","time":871.8999998569489},{"name":"bigPipe.activity-panel-pipe-id.start","time":872.0999999046326},{"name":"bigPipe.activity-panel-pipe-id.end","time":877.5},{"name":"activityTabFullyLoaded","time":984.6999998092651}],"measures":[],"correlationId":"e872227c9607a1","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":81,"dbReadsTimeInMs":11,"dbConnsTimeInMs":18,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Other related problems:
Trailing spaces change the result of TIME():
+----------+----------+
| c1 | c2 |
+----------+----------+
| 00:20:01 | 00:00:00 |
+----------+----------+
+---------------------+----------------------+
| TIME('0001:01:01 ') | TIME('0001:01:01 ') |
+---------------------+----------------------+
| 01:01:01 | 00:00:00 |
+---------------------+----------------------+
+-------------+--------------+
| TIME('1 2') | TIME('1 2 ') |
+-------------+--------------+
| 00:00:01 | 26:00:00 |
+-------------+--------------+
Trailing spaces following DATE + 'T' (date/time separator) change the meaning of TIME():
+---------------------+----------------------+
| TIME('2001-01-01T') | TIME('2001-01-01T ') |
+---------------------+----------------------+
| 00:20:01 | 00:00:00 |
+---------------------+----------------------+
+---------------------+----------------------+
| TIME('00901-01-01') | TIME('00901-01-01T') |
+---------------------+----------------------+
| 00:09:01 | 00:00:00 |
+---------------------+----------------------+
Different result for 'T' with trailing digits in TIME()
+---------------------+----------------------+
| TIME('901-01-01T1') | TIME('901-01-01T10') |
+---------------------+----------------------+
| 00:09:01 | 10:00:00 |
+---------------------+----------------------+
+---------------------+----------------------+
| TIME('091-01-01T1') | TIME('091-01-01T10') |
+---------------------+----------------------+
| NULL | 10:00:00 |
+---------------------+----------------------+
Trailing garbage change the result of TIME()
+---------------------+----------------------+
| TIME('0001:01:01x') | TIME('0001:01:01xx') |
+---------------------+----------------------+
| 01:01:01 | 00:00:00 |
+---------------------+----------------------+
but not if the garbage is punctuation:
+---------------------+----------------------+
| TIME('0001:01:01.') | TIME('0001:01:01..') |
+---------------------+----------------------+
| 01:01:01 | 01:01:01 |
+---------------------+----------------------+
+---------------------+----------------------+
| TIME('0001:01:01-') | TIME('0001:01:01--') |
+---------------------+----------------------+
| 01:01:01 | 01:01:01 |
+---------------------+----------------------+
+---------------------+----------------------+
| TIME('0001:01:01-') | TIME('0001:01:01--') |
+---------------------+----------------------+
| 01:01:01 | 01:01:01 |
+---------------------+----------------------+
TIME() produces negative zero for the minus followed by garbage
+--------------+-------------------------------+
| TIME('-xxx') | TIME('-xxxxxxxxxxxxxxxxxxxx') |
+--------------+-------------------------------+
| -00:00:00 | -00:00:00 |
+--------------+-------------------------------+
+-----------------+------------------------------------+
| TIME('- ') | TIME('- ') |
+-----------------+------------------------------------+
| -00:00:00 | -00:00:00 |
+-----------------+------------------------------------+
But TIME() with a sign only produces NULL
+-----------+-----------+
| TIME('-') | TIME('-') |
+-----------+-----------+
| NULL | NULL |
+-----------+-----------+
This is different comparing to sign followed by garbage and time followed by spaces (see above).
Examples when zero fractional digits change the meaning completely
+---------------------+-----------------------+
| TIME('1-1-1 1:1:1') | TIME('1-1-1 1:1:1.0') |
+---------------------+-----------------------+
| 00:00:01 | 01:01:01.0 |
+---------------------+-----------------------+