Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24937

window functions over unbounded RANGE fails on not numeric type

    XMLWordPrintable

    Details

      Description

      create table ticks 
      (SYMBOL varchar(50), `DATE` Date, PRICE int);
      insert into ticks values
      ("AAPL","2021-02-21",10),
      ("TSLA","2021-02-21",800),
      ("AMZN","2021-02-21",90),
      ("AAPL","2021-02-20",17),
      ("TSLA","2021-02-20",900),
      ("AMZN","2021-02-20",105),
      ("AAPL","2021-02-19",5),
      ("TSLA","2021-02-19",960),
      ("AMZN","2021-02-19",80);
       
      select distinct symbol, first_value(price) over w as first, last_value(price) over w as last
      from ticks
      window w as (partition by symbol order by date range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING)
      

      "Numeric datatype is required for RANGE-type frame"

      https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=b5818fc67035bb95bf7ad49663f456b4

      Works in MySQL-8.0.23 as if RANGE was replace with ROWS.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              cvicentiu Vicențiu Ciorbaru
              Reporter:
              danblack Daniel Black
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration