sql: windowed aggregate functions for decimals return unexpected results

[mneverov]

Consider the following sql:

CREATE TABLE t
(
    yf float,
    yd decimal
);

INSERT INTO t (yf,yd)
VALUES (1.0, 1.0),
       (20.0, 20.0),
       (25.0, 25.0),
       (41.0, 41.0),
       (55.5, 55.5),
       (60.9, 60.9),
       (72.0, 72.0),
       (88.0, 88.0),
       (88.0, 88.0),
       (89.0, 89.0);

select sqrdiff(yf)     as sqrdiff_float,
       sqrdiff(yd)     as sqrdiff_decimal,
       var_pop(yf)     as var_pop_float,
       var_pop(yd)     as var_pop_decimal,
       var_samp(yf)    as var_samp_float,
       var_samp(yd)    as var_samp_decimal,
       stddev_pop(yf)  as stddev_pop_float,
       stddev_pop(yd)  as stddev_pop_decimal,
       stddev_samp(yf) as stddev_samp_float,
       stddev_samp(yd) as stddev_samp_decimal
from t;

-- returns

    sqrdiff_float   | sqrdiff_decimal |   var_pop_float   | var_pop_decimal |  var_samp_float   | var_samp_decimal | stddev_pop_float  |  stddev_pop_decimal   | stddev_samp_float |  stddev_samp_decimal
--------------------+-----------------+-------------------+-----------------+-------------------+------------------+-------------------+-----------------------+-------------------+------------------------
  8885.844000000001 |        8885.844 | 888.5844000000001 |        888.5844 | 987.3160000000001 |          987.316 | 29.80913282871543 | 29.809132828715430446 | 31.42158493774622 | 31.421584937746218024
(1 row)

-- Note that the results for floats and decimals are the same and these values correspond to what PG returns.

select sqrdiff(yf) over (order by yf)     as sqrdiff_float,
       sqrdiff(yd) over (order by yd)     as sqrdiff_decimal,
       var_pop(yf) over (order by yf)     as var_pop_float,
       var_pop(yd) over (order by yd)     as var_pop_decimal,
       var_samp(yf) over (order by yf)    as var_samp_float,
       var_samp(yd) over (order by yd)    as var_samp_decimal,
       stddev_pop(yf) over (order by yf)  as stddev_pop_float,
       stddev_pop(yd) over (order by yd)  as stddev_pop_decimal,
       stddev_samp(yf) over (order by yf) as stddev_samp_float,
       stddev_samp(yd) over (order by yd) as stddev_samp_decimal
from t;

-- returns:

sqrdiff_float    |       sqrdiff_decimal       |   var_pop_float   |    var_pop_decimal    |   var_samp_float   |   var_samp_decimal    |  stddev_pop_float  |  stddev_pop_decimal   | stddev_samp_float  |  stddev_samp_decimal
---------------------+-----------------------------+-------------------+-----------------------+--------------------+-----------------------+--------------------+-----------------------+--------------------+------------------------
                   0 |                           0 |                 0 |                     0 | NULL               | NULL                  |                  0 |                     0 | NULL               | NULL
               180.5 |                       180.5 |             90.25 |                 90.25 |              180.5 |                 180.5 |                9.5 |                   9.5 | 13.435028842544403 | 13.435028842544402964
   320.6666666666667 | 1563.5500000000000000000001 | 106.8888888888889 | 52.738888888888888889 | 160.33333333333334 | 79.108333333333333333 | 10.338708279513883 | 7.2621545624483157849 | 12.662279942148386 | 8.8942865556116041596
              814.75 | 1563.5500000000000000000001 |          203.6875 |               163.075 |  271.5833333333333 | 217.43333333333333333 | 14.271912976192084 | 12.770082223697700217 | 16.479785597310826 | 14.745620818851043999
                1726 |        57131586605212041.25 |             345.2 |                312.71 |              431.5 |              390.8875 | 18.579558659989747 | 17.683608229091708760 |  20.77257807784099 | 19.770875043861867471
              2600.8 | 13317.319002524018247997853 | 433.4666666666667 | 145.82459565876494609 |  520.1600000000001 | 174.98951479051793531 | 20.819862311424316 | 12.075785508974765907 | 22.807016464237492 | 13.228360245718965727
  3845.0371428571434 |  1859.455475739525896765693 | 549.2910204081634 | 190.24741432177168926 |  640.8395238095239 | 221.95531670873363747 | 23.436958429117105 | 13.793020493052697892 | 25.314808389745398 | 14.898164877216711669
   7527.842222222223 |  18594.55475739525896765693 | 836.4269135802471 | 557.17077551305346554 |  940.9802777777779 | 626.81712245218514873 | 28.921046204801222 | 23.604465160495661304 |  30.67540183563661 | 25.036316071902134954
   7527.842222222223 |  18594.55475739525896765693 | 836.4269135802471 | 557.17077551305346554 |  940.9802777777779 | 626.81712245218514873 | 28.921046204801222 | 23.604465160495661304 |  30.67540183563661 | 25.036316071902134954
   8885.844000000001 |  1859.455475739525896765693 | 888.5844000000001 | 185.94554757395258968 |  987.3160000000001 | 206.60616397105843297 |  29.80913282871543 | 13.636185228059663963 |  31.42158493774622 | 14.373801305537044085
(10 rows)

The results for windowed aggregate functions with ordering for decimals differ from the results for the same functions without using over. The results for floats are the same.

I reproduced this on master branch, commit 7fa301f.

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I have CC'd a few people who may be able to assist you:

• @mattcrdb (member of the technical support engineering team)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}