Type:
Priority:
Resolution:
Unresolved
Affects Version/s:
10.1.37 , 10.11.1 , 10.3(EOL) , 10.4(EOL) , 10.5 , 10.6 , 10.7(EOL) , 10.8(EOL) , 10.9(EOL) , 10.10(EOL) , 10.11
Environment:
ubuntu 18.04
Description:
( SELECT (c1 DIV 1)*0.1 FROM t) UNION ALL ( SELECT '1' ); -- sql1
( SELECT DISTINCT (c1 DIV 1)*0.1 FROM t) UNION ALL ( SELECT '1' ); -- sql2
However, the value 0 changed to 0.0 after adding DISTINCT, seems like a logical bug:
mysql> select version();
+ -----------------------------------------+
| version() |
+ -----------------------------------------+
| 10.11.1-MariaDB-1:10.11.1+maria~ubu2204 |
+ -----------------------------------------+
1 row in set (0.00 sec)
mysql> ( SELECT (c1 DIV 1)*0.1 FROM t) UNION ALL ( SELECT '1' );
+ ----------------+
| (c1 DIV 1)*0.1 |
+ ----------------+
| 0 |
| 1 |
+ ----------------+
2 rows in set (0.00 sec)
mysql> ( SELECT DISTINCT (c1 DIV 1)*0.1 FROM t) UNION ALL ( SELECT '1' );
+ ----------------+
| (c1 DIV 1)*0.1 |
+ ----------------+
| 0.0 |
| 1 |
+ ----------------+
2 rows in set (0.00 sec)
How to repeat:
drop table if exists t;
create table t (c1 double );
insert into t values (0.1);
( SELECT (c1 DIV 1)*0.1 FROM t) UNION ALL ( SELECT '1' );
( SELECT DISTINCT (c1 DIV 1)*0.1 FROM t) UNION ALL ( SELECT '1' );
Hope these can be helpful for your debugging:https://hub.docker.com/_/mariadb/tags
MariaDB [TEST]> select version();
+ --------------------------+
| version() |
+ --------------------------+
| 10.1.37-MariaDB-1~bionic |
+ --------------------------+
1 row in set (0.00 sec)
MariaDB [TEST]> ( SELECT (c1 DIV 1)*0.1 FROM t) UNION ALL ( SELECT '1' );
+ ----------------+
| (c1 DIV 1)*0.1 |
+ ----------------+
| 0 |
| 1 |
+ ----------------+
2 rows in set (0.00 sec)
MariaDB [TEST]> ( SELECT DISTINCT (c1 DIV 1)*0.1 FROM t) UNION ALL ( SELECT '1' );
+ ----------------+
| (c1 DIV 1)*0.1 |
+ ----------------+
| 0.0 |
| 1 |
+ ----------------+
2 rows in set (0.00 sec)
MariaDB [TEST]> select version();
+ --------------------------+
| version() |
+ --------------------------+
| 10.1.36-MariaDB-1~bionic |
+ --------------------------+
1 row in set (0.00 sec)
MariaDB [TEST]> ( SELECT (c1 DIV 1)*0.1 FROM t) UNION ALL ( SELECT '1' );
+ ----------------+
| (c1 DIV 1)*0.1 |
+ ----------------+
| 0.0 |
| 1 |
+ ----------------+
2 rows in set (0.00 sec)
MariaDB [TEST]> ( SELECT DISTINCT (c1 DIV 1)*0.1 FROM t) UNION ALL ( SELECT '1' );
+ ----------------+
| (c1 DIV 1)*0.1 |
+ ----------------+
| 0.0 |
| 1 |
+ ----------------+
2 rows in set (0.00 sec)
created issue -
2022-12-17 08:01
made changes -
2022-12-18 14:37
Field
Original Value
New Value
Summary
0 changed to 0.0 caused by DISTINCT and UNION ALL
0 changed to 0.0 caused by DISTINCT and UNION ALL [10.1.37, 10.11.1]
made changes -
2022-12-19 09:14
Labels
distinct-optimization union
distinct-optimization innodb union
made changes -
2022-12-19 09:16
Affects Version/s
10.3
[ 22126
]
Affects Version/s
10.4
[ 22408
]
Affects Version/s
10.5
[ 23123
]
Affects Version/s
10.6
[ 24028
]
Affects Version/s
10.7
[ 24805
]
Affects Version/s
10.8
[ 26121
]
Affects Version/s
10.9
[ 26905
]
Affects Version/s
10.10
[ 27530
]
Affects Version/s
10.11
[ 27614
]
made changes -
2022-12-19 09:16
Fix Version/s
10.3
[ 22126
]
Fix Version/s
10.4
[ 22408
]
Fix Version/s
10.5
[ 23123
]
Fix Version/s
10.6
[ 24028
]
Fix Version/s
10.7
[ 24805
]
Fix Version/s
10.8
[ 26121
]
Fix Version/s
10.9
[ 26905
]
made changes -
2022-12-19 09:18
Assignee
Oleksandr Byelkin
[ sanja
]
made changes -
2022-12-19 09:19
Labels
distinct-optimization innodb union
distinct-optimization innodb union upstream
made changes -
2023-01-09 18:57
Status
Open
[ 1
]
Confirmed
[ 10101
]
made changes -
2023-03-03 17:33
Fix Version/s
10.7
[ 24805
]
made changes -
2023-04-27 13:53
Fix Version/s
10.3
[ 22126
]
made changes -
2023-04-27 14:50
Fix Version/s
10.8
[ 26121
]
made changes -
2023-11-28 10:39
Fix Version/s
10.9
[ 26905
]
made changes -
2024-09-10 14:56
Fix Version/s
10.4
[ 22408
]
{"report":{"fcp":3138.399999976158,"ttfb":450.5,"pageVisibility":"visible","entityId":117762,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":32,"apdex":0.5,"journeyId":"3270ddc4-0df2-45f9-a1c2-a21619186b05","navigationType":0,"readyForUser":3203.300000011921,"redirectCount":0,"resourceLoadedEnd":2525.899999976158,"resourceLoadedStart":557.8000000119209,"resourceTiming":[{"duration":118.59999996423721,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":557.8000000119209,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":557.8000000119209,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":676.3999999761581,"responseStart":0,"secureConnectionStart":0},{"duration":118.69999998807907,"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":558,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":558,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":676.6999999880791,"responseStart":0,"secureConnectionStart":0},{"duration":1547.300000011921,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":558.1999999880791,"connectEnd":558.1999999880791,"connectStart":558.1999999880791,"domainLookupEnd":558.1999999880791,"domainLookupStart":558.1999999880791,"fetchStart":558.1999999880791,"redirectEnd":0,"redirectStart":0,"requestStart":861.3000000119209,"responseEnd":2105.5,"responseStart":1204.0999999642372,"secureConnectionStart":558.1999999880791},{"duration":1967.5999999642372,"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":558.3000000119209,"connectEnd":558.3000000119209,"connectStart":558.3000000119209,"domainLookupEnd":558.3000000119209,"domainLookupStart":558.3000000119209,"fetchStart":558.3000000119209,"redirectEnd":0,"redirectStart":0,"requestStart":861.5,"responseEnd":2525.899999976158,"responseStart":1130.3999999761581,"secureConnectionStart":558.3000000119209},{"duration":571.8000000119209,"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":558.5999999642372,"connectEnd":558.5999999642372,"connectStart":558.5999999642372,"domainLookupEnd":558.5999999642372,"domainLookupStart":558.5999999642372,"fetchStart":558.5999999642372,"redirectEnd":0,"redirectStart":0,"requestStart":861.5,"responseEnd":1130.3999999761581,"responseStart":1061.199999988079,"secureConnectionStart":558.5999999642372},{"duration":648.8000000119209,"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":558.6999999880791,"connectEnd":558.6999999880791,"connectStart":558.6999999880791,"domainLookupEnd":558.6999999880791,"domainLookupStart":558.6999999880791,"fetchStart":558.6999999880791,"redirectEnd":0,"redirectStart":0,"requestStart":861.5999999642372,"responseEnd":1207.5,"responseStart":1204.800000011921,"secureConnectionStart":558.6999999880791},{"duration":648.6999999880791,"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":558.8999999761581,"connectEnd":558.8999999761581,"connectStart":558.8999999761581,"domainLookupEnd":558.8999999761581,"domainLookupStart":558.8999999761581,"fetchStart":558.8999999761581,"redirectEnd":0,"redirectStart":0,"requestStart":861.6999999880791,"responseEnd":1207.5999999642372,"responseStart":1205.800000011921,"secureConnectionStart":558.8999999761581},{"duration":118.5,"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":559.0999999642372,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":559.0999999642372,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":677.5999999642372,"responseStart":0,"secureConnectionStart":0},{"duration":648.5,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":559.1999999880791,"connectEnd":559.1999999880791,"connectStart":559.1999999880791,"domainLookupEnd":559.1999999880791,"domainLookupStart":559.1999999880791,"fetchStart":559.1999999880791,"redirectEnd":0,"redirectStart":0,"requestStart":861.6999999880791,"responseEnd":1207.699999988079,"responseStart":1206.0999999642372,"secureConnectionStart":559.1999999880791},{"duration":166.60000002384186,"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":559.3999999761581,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":559.3999999761581,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":726,"responseStart":0,"secureConnectionStart":0},{"duration":680.9000000357628,"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":559.5999999642372,"connectEnd":559.5999999642372,"connectStart":559.5999999642372,"domainLookupEnd":559.5999999642372,"domainLookupStart":559.5999999642372,"fetchStart":559.5999999642372,"redirectEnd":0,"redirectStart":0,"requestStart":861.8999999761581,"responseEnd":1240.5,"responseStart":1206.5999999642372,"secureConnectionStart":559.5999999642372},{"duration":1883.199999988079,"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":631.6999999880791,"connectEnd":631.6999999880791,"connectStart":631.6999999880791,"domainLookupEnd":631.6999999880791,"domainLookupStart":631.6999999880791,"fetchStart":631.6999999880791,"redirectEnd":0,"redirectStart":0,"requestStart":1060.199999988079,"responseEnd":2514.899999976158,"responseStart":2449.399999976158,"secureConnectionStart":631.6999999880791},{"duration":1731.3999999761581,"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":774.8000000119209,"connectEnd":774.8000000119209,"connectStart":774.8000000119209,"domainLookupEnd":774.8000000119209,"domainLookupStart":774.8000000119209,"fetchStart":774.8000000119209,"redirectEnd":0,"redirectStart":0,"requestStart":1060.3999999761581,"responseEnd":2506.199999988079,"responseStart":2448.899999976158,"secureConnectionStart":774.8000000119209},{"duration":325.5,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":2373.199999988079,"connectEnd":2373.199999988079,"connectStart":2373.199999988079,"domainLookupEnd":2373.199999988079,"domainLookupStart":2373.199999988079,"fetchStart":2373.199999988079,"redirectEnd":0,"redirectStart":0,"requestStart":2657.399999976158,"responseEnd":2698.699999988079,"responseStart":2690.099999964237,"secureConnectionStart":2373.199999988079}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":162,"responseStart":451,"responseEnd":948,"domLoading":465,"domInteractive":3347,"domContentLoadedEventStart":3347,"domContentLoadedEventEnd":3416,"domComplete":4421,"loadEventStart":4421,"loadEventEnd":4422,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":3315.199999988079},{"name":"bigPipe.sidebar-id.end","time":3316},{"name":"bigPipe.activity-panel-pipe-id.start","time":3316.099999964237},{"name":"bigPipe.activity-panel-pipe-id.end","time":3320.5},{"name":"activityTabFullyLoaded","time":3431}],"measures":[],"correlationId":"5e2d4a1c38b008","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":191,"dbReadsTimeInMs":26,"dbConnsTimeInMs":39,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Bug
Minor
Thank you!
I repeated as described, on 10.3-10.11 with Innodb engine, also on Mysql (5.7.38, 8.0.30).
MyIsam and Aria returned the expected results.