Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #2216

Re: Custom window functions

Show all headers | View raw

Anton Shepelev (anton.txt@g{oogle}mail.com) writes:
> Surprising indeed, nor do I think this unexpected difference
> is documented, seeming like a bug. 

The Docs are smart enough to be sufficiently vague:

   Depending on the ranking, aggregate, or analytic function used with the 
   OVER clause, <ORDER BY clause> and/or the <ROWS and RANGE clause> may not 
   be supported.

So it certainly permits for this exception.

But I could certainly argue that it is a bug in that the error 
message should be semantic, and not a parsing error.

But now for some interesting news. In SQL 2022, they introduced the 
WINDOW clause:

SELECT object_id, column_id, SUM(column_id) OVER MyWindow
FROM   sys.columns
WINDOW MyWindow AS (PARTITION BY object_id 
               ORDER BY column_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
ORDER  BY object_id, column_id

This is useful when you want to use the same Windows clause for multiple
columns in the same query, as you only have to define it in one place.

So what about this?

SELECT object_id, column_id, dbo.integerlist(column_id) OVER MyWindow
FROM   sys.columns
WINDOW MyWindow AS (PARTITION BY object_id 
                ORDER BY column_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
ORDER  BY object_id, column_id

Yes, it runs and returns the correct result.

However, I need to add a caveat here: The fact that this runs might 
be a bug. I seem to recall that there is something about CLR 
aggregates and ordering. That is, if this runs but produces an 
incorrect result, this is not good. Then again, my aggregate is
supposed to return an ordered result, and it seems to do with my
test query. But that may be due to chance.

I will need to bring this up with some people at Microsoft.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Custom window functions Anton Shepelev <anton.txt@g{oogle}mail.com> - 2023-05-22 12:32 +0300
  Re: Custom window functions Erland Sommarskog <esquel@sommarskog.se> - 2023-05-23 20:56 +0200
    Re: Custom window functions Anton Shepelev <anton.txt@g{oogle}mail.com> - 2023-05-25 18:38 +0300
      Re: Custom window functions Erland Sommarskog <esquel@sommarskog.se> - 2023-05-25 21:27 +0200
        Re: Custom window functions Erland Sommarskog <esquel@sommarskog.se> - 2023-05-26 21:00 +0200
          Re: Custom window functions Anton Shepelev <anton.txt@gmail.moc> - 2023-05-26 22:55 +0300

csiph-web