Description:
When using the LAG() window function in an arithmetic expression, the result may be wrong
How to repeat:
This statement:
WITH tab(t, company, quote) AS (
|
SELECT 1 AS t, 'G' AS company, 40 AS quote
|
UNION SELECT 2 , 'G', 60
|
UNION SELECT 3 , 'S', 60
|
UNION SELECT 4, 'S', 20
|
)
|
SELECT DISTINCT company, quote - LAG(quote) OVER(PARTITION BY company ORDER BY t)
|
FROM tab;
|
produces wrong output:
company e
|
--------------
|
G (null)
|
S (null)
|
Remove the DISTINCT keyword....
WITH tab(t, company, quote) AS (
|
SELECT 1 AS t, 'G' AS company, 40 AS quote
|
UNION SELECT 2 , 'G', 60
|
UNION SELECT 3 , 'S', 60
|
UNION SELECT 4, 'S', 20
|
)
|
SELECT company, quote - LAG(quote) OVER(PARTITION BY company ORDER BY t) e
|
FROM tab;
|
to get this:
company e
|
--------------
|
G (null)
|
G 20
|
S (null)
|
S -40
|
As can be seen, the DISTINCT keyword should have no effect on this query.
This is also described in this stack overflow question:
https://stackoverflow.com/q/49700278/521799
The same bug appears also in MySQL:
https://bugs.mysql.com/bug.php?id=92503
- is duplicated by
-
MDEV-14791
Crash with order by expression containing window functions
-
-
Closed
{"report":{"fcp":1699.5999999996275,"ttfb":438.5,"pageVisibility":"visible","entityId":69866,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"9c73b578-5e1d-4bb7-951d-191dac49ba2b","navigationType":0,"readyForUser":1804.5,"redirectCount":0,"resourceLoadedEnd":1338.2999999998137,"resourceLoadedStart":567,"resourceTiming":[{"duration":62.39999999990687,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":567,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":567,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":629.3999999999069,"responseStart":0,"secureConnectionStart":0},{"duration":142.10000000009313,"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":567.1999999997206,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":567.1999999997206,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":709.2999999998137,"responseStart":0,"secureConnectionStart":0},{"duration":518.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":567.3999999999069,"connectEnd":567.3999999999069,"connectStart":567.3999999999069,"domainLookupEnd":567.3999999999069,"domainLookupStart":567.3999999999069,"fetchStart":567.3999999999069,"redirectEnd":0,"redirectStart":0,"requestStart":715.2999999998137,"responseEnd":1085.8999999999069,"responseStart":739.3999999999069,"secureConnectionStart":567.3999999999069},{"duration":672.9000000003725,"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":567.5999999996275,"connectEnd":567.5999999996275,"connectStart":567.5999999996275,"domainLookupEnd":567.5999999996275,"domainLookupStart":567.5999999996275,"fetchStart":567.5999999996275,"redirectEnd":0,"redirectStart":0,"requestStart":715.8999999999069,"responseEnd":1240.5,"responseStart":756.5,"secureConnectionStart":567.5999999996275},{"duration":191.89999999990687,"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":567.7999999998137,"connectEnd":567.7999999998137,"connectStart":567.7999999998137,"domainLookupEnd":567.7999999998137,"domainLookupStart":567.7999999998137,"fetchStart":567.7999999998137,"redirectEnd":0,"redirectStart":0,"requestStart":716.0999999996275,"responseEnd":759.6999999997206,"responseStart":757.1999999997206,"secureConnectionStart":567.7999999998137},{"duration":191.89999999990687,"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":567.8999999999069,"connectEnd":567.8999999999069,"connectStart":567.8999999999069,"domainLookupEnd":567.8999999999069,"domainLookupStart":567.8999999999069,"fetchStart":567.8999999999069,"redirectEnd":0,"redirectStart":0,"requestStart":716.1999999997206,"responseEnd":759.7999999998137,"responseStart":757.7999999998137,"secureConnectionStart":567.8999999999069},{"duration":253.3000000002794,"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":568.0999999996275,"connectEnd":568.0999999996275,"connectStart":568.0999999996275,"domainLookupEnd":568.0999999996275,"domainLookupStart":568.0999999996275,"fetchStart":568.0999999996275,"redirectEnd":0,"redirectStart":0,"requestStart":793,"responseEnd":821.3999999999069,"responseStart":805.2999999998137,"secureConnectionStart":568.0999999996275},{"duration":223.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":568.2999999998137,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":568.2999999998137,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":791.7999999998137,"responseStart":0,"secureConnectionStart":0},{"duration":286.59999999962747,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":568.5,"connectEnd":568.5,"connectStart":568.5,"domainLookupEnd":568.5,"domainLookupStart":568.5,"fetchStart":568.5,"redirectEnd":0,"redirectStart":0,"requestStart":801.1999999997206,"responseEnd":855.0999999996275,"responseStart":853.2999999998137,"secureConnectionStart":568.5},{"duration":225.8000000002794,"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":568.6999999997206,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":568.6999999997206,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":794.5,"responseStart":0,"secureConnectionStart":0},{"duration":286.29999999981374,"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":568.8999999999069,"connectEnd":568.8999999999069,"connectStart":568.8999999999069,"domainLookupEnd":568.8999999999069,"domainLookupStart":568.8999999999069,"fetchStart":568.8999999999069,"redirectEnd":0,"redirectStart":0,"requestStart":801.2999999998137,"responseEnd":855.1999999997206,"responseStart":853.8999999999069,"secureConnectionStart":568.8999999999069},{"duration":764.7000000001863,"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":572.7999999998137,"connectEnd":572.7999999998137,"connectStart":572.7999999998137,"domainLookupEnd":572.7999999998137,"domainLookupStart":572.7999999998137,"fetchStart":572.7999999998137,"redirectEnd":0,"redirectStart":0,"requestStart":1009.7999999998137,"responseEnd":1337.5,"responseStart":1288.7999999998137,"secureConnectionStart":572.7999999998137},{"duration":765.3999999999069,"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":572.8999999999069,"connectEnd":572.8999999999069,"connectStart":572.8999999999069,"domainLookupEnd":572.8999999999069,"domainLookupStart":572.8999999999069,"fetchStart":572.8999999999069,"redirectEnd":0,"redirectStart":0,"requestStart":1153.5,"responseEnd":1338.2999999998137,"responseStart":1313.5999999996275,"secureConnectionStart":572.8999999999069},{"duration":241.89999999990687,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1243.3999999999069,"connectEnd":1243.3999999999069,"connectStart":1243.3999999999069,"domainLookupEnd":1243.3999999999069,"domainLookupStart":1243.3999999999069,"fetchStart":1243.3999999999069,"redirectEnd":0,"redirectStart":0,"requestStart":1444.6999999997206,"responseEnd":1485.2999999998137,"responseStart":1483.7999999998137,"secureConnectionStart":1243.3999999999069}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":150,"responseStart":438,"responseEnd":607,"domLoading":478,"domInteractive":2011,"domContentLoadedEventStart":2011,"domContentLoadedEventEnd":2069,"domComplete":2880,"loadEventStart":2880,"loadEventEnd":2881,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1967.0999999996275},{"name":"bigPipe.sidebar-id.end","time":1967.8999999999069},{"name":"bigPipe.activity-panel-pipe-id.start","time":1968.0999999996275},{"name":"bigPipe.activity-panel-pipe-id.end","time":1975.0999999996275},{"name":"activityTabFullyLoaded","time":2083.899999999907}],"measures":[],"correlationId":"5b7aa47c5342c9","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":97,"dbReadsTimeInMs":10,"dbConnsTimeInMs":22,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Thanks for the report! Reproducible on MariaDB 10.2,10.3
create table t1 (i1 int, i2 int);
insert into t1 values (1,40), (2,60), (3,60),(4,20);
MariaDB [test]> SELECT 1+LAG(i2) OVER(ORDER BY i1) FROM t1;
+-----------------------------+
| 1+LAG(i2) OVER(ORDER BY i1) |
+-----------------------------+
| NULL |
| 41 |
| 61 |
| 61 |
+-----------------------------+
4 rows in set (0.001 sec)
MariaDB [test]> SELECT distinct 1+LAG(i2) OVER(ORDER BY i1) FROM t1;
+-----------------------------+
| 1+LAG(i2) OVER(ORDER BY i1) |
+-----------------------------+
| NULL |
+-----------------------------+
1 row in set (0.000 sec)
MariaDB [test]> SELECT distinct 1+LAG(i2,0) OVER(ORDER BY i1) FROM t1;
+-------------------------------+
| 1+LAG(i2,0) OVER(ORDER BY i1) |
+-------------------------------+
| 41 |
+-------------------------------+
1 row in set (0.000 sec)
MariaDB [test]> SELECT distinct 1+LEAD(i2) OVER(ORDER BY i1) FROM t1;
+------------------------------+
| 1+LEAD(i2) OVER(ORDER BY i1) |
+------------------------------+
| 61 |
+------------------------------+
1 row in set (0.002 sec)
MariaDB [test]> SELECT distinct 1+rank() OVER(ORDER BY i1) FROM t1;
+----------------------------+
| 1+rank() OVER(ORDER BY i1) |
+----------------------------+
| 2 |
+----------------------------+
1 row in set (0.001 sec)
As a temporary workaround, it is possible to use derived table or CTE table
MariaDB [test]> select distinct * from (SELECT 1+LAG(i2) OVER(ORDER BY i1) FROM t1) tmp;
+-----------------------------+
| 1+LAG(i2) OVER(ORDER BY i1) |
+-----------------------------+
| NULL |
| 41 |
| 61 |
+-----------------------------+
3 rows in set (0.003 sec)