Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-1676

AVG as Window function with OVER(sort by) gives bad answer.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.1.5
    • Fix Version/s: 1.1.7
    • Component/s: ExeMgr
    • Labels:
      None
    • Sprint:
      2018-16, 2018-17, 2018-18, 2018-19, 2018-20, 2018-21

      Description

      Consider the following from a ref database:
      MariaDB [tpch1]> SELECT start_ts, Query_time_sum,avg(Query_time_sum) OVER(order by start_ts) prev_sum from query_class_metrics_float where agent_id=1 and query_class=1563 limit 50;
      ------------------------------------------------------

      start_ts Query_time_sum prev_sum

      ------------------------------------------------------

      2013-06-18 22:55:00 0.281992 0.28199198842048645
      2013-06-18 23:00:00 0.292305 0.28714849054813385
      2013-06-18 23:05:00 0.215658 0.26331832508246106
      2013-06-18 23:10:00 0.243597 0.2583879940211773
      2013-06-18 23:15:00 0.35189 0.2770883947610855
      2013-06-18 23:20:00 0.543079 0.32142016539971036
      2013-06-18 23:25:00 0.495779 0.34632857143878937
      2013-06-18 23:30:00 0.589326 0.3767032530158758
      2013-06-18 23:35:00 0.210907 0.3582814468277825
      2013-06-18 23:40:00 0.167944 0.33924770206212995
      2013-06-18 23:45:00 0.202401 0.32680709253657947

      ------------------------------------------------------
      11 rows in set (0.001 sec)

      From Columnstore 1.2:
      MariaDB [tpch1]> SELECT start_ts, Query_time_sum,avg(Query_time_sum) OVER(order by start_ts) prev_sum from query_class_metrics_float where agent_id=1 and query_class=1563 limit 50;
      -------------------------------------------------------------

      start_ts Query_time_sum prev_sum

      -------------------------------------------------------------

      2013-06-18 22:55:00 0.281992 2.6405915357e-314
      2013-06-18 23:00:00 0.292305 3.6530706201403676e176
      2013-06-18 23:05:00 0.215658 4.8888005904394483e238
      2013-06-18 23:10:00 0.243597 4.405801788445726e-156
      2013-06-18 23:15:00 0.35189 8.516974811510197e-309
      2013-06-18 23:20:00 0.543079 1.056589308839876e270
      2013-06-18 23:25:00 0.495779 8.693343355840886e-271
      2013-06-18 23:30:00 0.589326 0.000000003256678917883161
      2013-06-18 23:35:00 0.210907 2.1947538168970816e-186
      2013-06-18 23:40:00 0.167944 1.4295192705171712e-185
      2013-06-18 23:45:00 0.202401 9.965502693831758e-185

      -------------------------------------------------------------
      11 rows in set (7.36 sec)

      From Columnstore 1.1:
      MariaDB [tpch1]> SELECT start_ts, Query_time_sum,avg(Query_time_sum) OVER(order by start_ts) prev_sum from query_class_metrics_float where agent_id=1 and query_class=1563 limit 50;
      -----------------------------------------------------------

      start_ts Query_time_sum prev_sum

      -----------------------------------------------------------

      2013-06-18 22:55:00 0.281992 5.18595745e-315
      2013-06-18 23:00:00 0.292305 5.186812295e-315
      2013-06-18 23:05:00 0.215658 2.3860239872e-313
      2013-06-18 23:10:00 0.243597 3.618444363391428e176
      2013-06-18 23:15:00 0.35189 -3.3329179963442704e-111
      2013-06-18 23:20:00 0.543079 3.38760600563583e-310
      2013-06-18 23:25:00 0.495779 5.562689842891227e-309
      2013-06-18 23:30:00 0.589326 2.3158423261314417e77
      2013-06-18 23:35:00 0.210907 8.693343356262318e-271
      2013-06-18 23:40:00 0.167944 0.0000872324596181205
      2013-06-18 23:45:00 0.202401 5.193386935e-315

      -----------------------------------------------------------
      11 rows in set (13.16 sec)

        Attachments

          Activity

            People

            Assignee:
            pleblanc Patrick LeBlanc (Inactive)
            Reporter:
            David.Hall David Hall
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration