The UNION code attempts to figure a common data type and translate the data to that for comparison. In this case, the common data type is UNSIGNED, so the result is unsigned. Any result column can only have one data type. Our code will not support a different type for different rows. If we choose INT as the common type, the result would be incorrect for a different data set.
A potential fix is to choose a larger data type like BIGINT to hold the result. This would be large enough to handle the largest UNSIGNED and still handle negative numbers. It becomes a little more problematic if one of the types is UNSIGNED BIGINT. Perhaps we could do something with int128_t.
David Hall (Inactive)
added a comment - - edited The UNION code attempts to figure a common data type and translate the data to that for comparison. In this case, the common data type is UNSIGNED, so the result is unsigned. Any result column can only have one data type. Our code will not support a different type for different rows. If we choose INT as the common type, the result would be incorrect for a different data set.
A potential fix is to choose a larger data type like BIGINT to hold the result. This would be large enough to handle the largest UNSIGNED and still handle negative numbers. It becomes a little more problematic if one of the types is UNSIGNED BIGINT. Perhaps we could do something with int128_t.
Alexander Barkov
added a comment - - edited MariaDB chooses:
DECIMAL(11,0) to mix INT andd INT UNSIGNED.
DECIMAL(21,0) to mix BIGINT and BIGINT UNSIGNED.
I think CS should choose the same data types.
MariaDB [mytest]> SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t1) tu;
------
a
------
1
-1
------
2 rows in set (0.070 sec)
Daniel Lee (Inactive)
added a comment - Build verified: 6.1.1 ( Drone #2585)
Reproduced the issue in build 2584 and verified the fix in build 2585.
MariaDB [mytest] > DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.116 sec)
MariaDB [mytest] > CREATE TABLE t1 (a INT, b INT UNSIGNED) ENGINE=ColumnStore;
Query OK, 0 rows affected (0.637 sec)
MariaDB [mytest] > INSERT INTO t1 VALUES (-1,1);
Query OK, 1 row affected (0.317 sec)
MariaDB [mytest] > SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t1) tu;
------
a
------
1
-1
------
2 rows in set (0.070 sec)
People
Daniel Lee (Inactive)
Alexander Barkov
Votes:
0Vote for this issue
Watchers:
3Start 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":1272.7000000476837,"ttfb":251.5,"pageVisibility":"visible","entityId":99358,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"4d14d6df-db06-45f5-a625-c19f7a0f9f40","navigationType":0,"readyForUser":1297.2999999523163,"redirectCount":0,"resourceLoadedEnd":1491.2000000476837,"resourceLoadedStart":273,"resourceTiming":[{"duration":371.09999990463257,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":273,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":273,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":644.0999999046326,"responseStart":0,"secureConnectionStart":0},{"duration":371.2000000476837,"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":273.2999999523163,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":273.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":644.5,"responseStart":0,"secureConnectionStart":0},{"duration":403.39999985694885,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":273.5,"connectEnd":273.5,"connectStart":273.5,"domainLookupEnd":273.5,"domainLookupStart":273.5,"fetchStart":273.5,"redirectEnd":0,"redirectStart":0,"requestStart":273.5,"responseEnd":676.8999998569489,"responseStart":676.7999999523163,"secureConnectionStart":273.5},{"duration":501.89999985694885,"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":273.7000000476837,"connectEnd":273.7000000476837,"connectStart":273.7000000476837,"domainLookupEnd":273.7000000476837,"domainLookupStart":273.7000000476837,"fetchStart":273.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":273.7000000476837,"responseEnd":775.5999999046326,"responseStart":775.5999999046326,"secureConnectionStart":273.7000000476837},{"duration":505.7000000476837,"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":273.7999999523163,"connectEnd":273.7999999523163,"connectStart":273.7999999523163,"domainLookupEnd":273.7999999523163,"domainLookupStart":273.7999999523163,"fetchStart":273.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":273.7999999523163,"responseEnd":779.5,"responseStart":779.5,"secureConnectionStart":273.7999999523163},{"duration":516,"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":274,"connectEnd":274,"connectStart":274,"domainLookupEnd":274,"domainLookupStart":274,"fetchStart":274,"redirectEnd":0,"redirectStart":0,"requestStart":274,"responseEnd":790,"responseStart":790,"secureConnectionStart":274},{"duration":516.7999999523163,"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":274.2000000476837,"connectEnd":274.2000000476837,"connectStart":274.2000000476837,"domainLookupEnd":274.2000000476837,"domainLookupStart":274.2000000476837,"fetchStart":274.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":274.2000000476837,"responseEnd":791,"responseStart":791,"secureConnectionStart":274.2000000476837},{"duration":548.4000000953674,"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":274.39999985694885,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":274.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":822.7999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":517.1000001430511,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":274.59999990463257,"connectEnd":274.59999990463257,"connectStart":274.59999990463257,"domainLookupEnd":274.59999990463257,"domainLookupStart":274.59999990463257,"fetchStart":274.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":274.59999990463257,"responseEnd":791.7000000476837,"responseStart":791.7000000476837,"secureConnectionStart":274.59999990463257},{"duration":548.0999999046326,"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":274.7999999523163,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":274.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":822.8999998569489,"responseStart":0,"secureConnectionStart":0},{"duration":544.3000001907349,"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":274.89999985694885,"connectEnd":274.89999985694885,"connectStart":274.89999985694885,"domainLookupEnd":274.89999985694885,"domainLookupStart":274.89999985694885,"fetchStart":274.89999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":274.89999985694885,"responseEnd":819.2000000476837,"responseStart":819.2000000476837,"secureConnectionStart":274.89999985694885},{"duration":1214.6000001430511,"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":276.09999990463257,"connectEnd":276.09999990463257,"connectStart":276.09999990463257,"domainLookupEnd":276.09999990463257,"domainLookupStart":276.09999990463257,"fetchStart":276.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":276.09999990463257,"responseEnd":1490.7000000476837,"responseStart":1490.7000000476837,"secureConnectionStart":276.09999990463257},{"duration":1213.1000001430511,"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":278.09999990463257,"connectEnd":278.09999990463257,"connectStart":278.09999990463257,"domainLookupEnd":278.09999990463257,"domainLookupStart":278.09999990463257,"fetchStart":278.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":278.09999990463257,"responseEnd":1491.2000000476837,"responseStart":1491.2000000476837,"secureConnectionStart":278.09999990463257},{"duration":322.80000019073486,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":833.3999998569489,"connectEnd":833.3999998569489,"connectStart":833.3999998569489,"domainLookupEnd":833.3999998569489,"domainLookupStart":833.3999998569489,"fetchStart":833.3999998569489,"redirectEnd":0,"redirectStart":0,"requestStart":833.3999998569489,"responseEnd":1156.2000000476837,"responseStart":1156.2000000476837,"secureConnectionStart":833.3999998569489},{"duration":417,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1189.2000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1189.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1606.2000000476837,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":59,"responseStart":251,"responseEnd":272,"domLoading":255,"domInteractive":1553,"domContentLoadedEventStart":1553,"domContentLoadedEventEnd":1602,"domComplete":2075,"loadEventStart":2075,"loadEventEnd":2076,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1495.2999999523163},{"name":"bigPipe.sidebar-id.end","time":1496.2000000476837},{"name":"bigPipe.activity-panel-pipe-id.start","time":1496.3999998569489},{"name":"bigPipe.activity-panel-pipe-id.end","time":1497.7999999523163},{"name":"activityTabFullyLoaded","time":1618.3999998569489}],"measures":[],"correlationId":"b005bed5fe616b","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":101,"dbReadsTimeInMs":13,"dbConnsTimeInMs":22,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
The UNION code attempts to figure a common data type and translate the data to that for comparison. In this case, the common data type is UNSIGNED, so the result is unsigned. Any result column can only have one data type. Our code will not support a different type for different rows. If we choose INT as the common type, the result would be incorrect for a different data set.
A potential fix is to choose a larger data type like BIGINT to hold the result. This would be large enough to handle the largest UNSIGNED and still handle negative numbers. It becomes a little more problematic if one of the types is UNSIGNED BIGINT. Perhaps we could do something with int128_t.