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

Window functions: Support for aggregate_func(DISTINCT ...) OVER (...)

    XMLWordPrintable

Details

    Description

      Should we support DISTINCT for aggregate functions that are computed over a window?

      PostgreSQL doesn't support this:

      test=#   select a, count(distinct b) over (order by a) from t1 ;
      ERROR:  DISTINCT is not implemented for window functions
      LINE 1: select a, count(distinct b) over (order by a) from t1 ;
                        ^
      

      http://www.postgresql.org/docs/9.5/static/sql-expressions.html says "Aggregate window functions, unlike normal aggregate functions, do not allow DISTINCT or ORDER BY to be used within the function argument list"

      SQL Server doesn't support it:
      https://connect.microsoft.com/SQLServer/feedback/details/254393/over-clause-enhancement-request-distinct-clause-for-aggregate-functions
      (verified with SQL Server 2008 and 2014)
      but one can find many requests for them to support this.

      Sybase documentation says:

      No aggregation functions in the rank query are allowed to specify DISTINCT

      http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1510/html/iqrefbb/BCGEEBHA.htm
      Does this mean they don't support it?

      Oracle 12.1 gives this error

      ORA-30487: ORDER BY not allowed here
      

      Apache Drill DOESNT support it:
      https://issues.apache.org/jira/browse/DRILL-3182

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.