Has the evaluation of OLAP functions in recursive queries changed lately?

[VolkerBarth]

Just a question that has risen in this question:

The sample query from this answer does work with 12.0.1.3726 (on Windows) but raises error -921 ("Invalid recursive query") with 12.0.1.3769.

That error is documented with the following "Probable cause":

A recursive query must consist of the UNION ALL of an initial query and an iteration query. The only set operation allowed in the iteration query is UNION ALL. A recursive reference to the name of the common table expression can only appear in the iteration query, and must not be in a SELECT that uses DISTINCT, ORDER BY, or an aggregate function. A recursive reference also cannot appear on the NULL-supplying side of an outer join, or in a subquery.

The query does use UNION ALL etc.
The critical expression is the following from the iterative query (i.e. when omitting that expression, the query does work with 12.0.1.3769, too) - note that there is no reference to the recursive common table expression here but simply to the joined base table aliased as "e":

SELECT ...CAST (ROW_NUMBER() OVER (PARTITION BY e.manager_ID ORDER BY e.employee_id)

If my understanding of the restrictions for the references to the common table expression in the iterative query is correct, then I would think the query should be allowed.

So the question is: Is the new behaviour (i.e. raising -921) a bug fix or a bug?

[VolkerBarth]

To cite from my comment on Ani's comment on Ani's answer:

The SQL construct
ROW_NUMBER() OVER (PARTITION BY e.manager_ID ORDER BY e.employee_id)
is a window aggregate function.

@Ani: That does contrast with the 12.0.1 docs, which do not list ROW_NUMBER() in the list of aggregate functions (in constrast to MAX(), LIST(), REGR_R2(), STDDEV() and the like) nor as a "Ranking function" but as a "Miscellaneous function". - Do I have to conclude that this category is wrong?

I would still love to know whether that category is correct? IMVHO, ROW_NUMBER() does not do an aggregation/grouping at all - in contrast to MAX() etc. - it just imposes an ordering on an result set... could anybody give some more insight?

[VolkerBarth]

...and frankly, I'd also would suggest to re-think whether the fix of CR 711791 should really restrict the usage of ROW_NUMBER(), too...

[VolkerBarth]

...just a reminder: May I get any more insights from the QP experts? I'd appreciate that!

[VolkerBarth]

@Ivan: Just in case you have a moment to answer my question - Is ROW_NUMBER() considered an aggregate function? (I would still claim that the cited usage case for ROW_NUMBER() would be helpful, and I would not think it would lead to improper evaluation, but apparently, I'm no query processing expert at all...)

Aside: I have the impression that Ivan's current answer on this old OLAP FAQ may have to do with my issue here, too.

[Former Member]

The "QTS-711791 Recursive queries containing window aggregate functions were not prohibited" fixed a bug in the SQL Anywhere server which explained your issue:

Versions fixed:

SQL Anywhere EBF

SA 12.0.1 3758

SA 11.0.1 2836

Recursive queries can not contain aggregate functions. When written using a window (OVER clause), aggregates were improperly allowed. In some cases this could lead to a crash under certain system conditions. This has been fixed: queries with windowed aggregate functions now correctly give an error.

[MarkCulp]

Isn't that what I said?.... but as Volker has pointed out, his query does not (appear to) have an aggregate function. So perhaps you could explain?

[Former Member]

The SQL construct

ROW_NUMBER() OVER (PARTITION BY e.manager_ID ORDER BY e.employee_id)

is a window aggregate function.

[MarkCulp]

Thanks Ani.

[VolkerBarth]

@Ani: That does contrast with the 12.0.1 docs, which do not list ROW_NUMBER() in the list of aggregate functions (in constrast to MAX(), LIST(), REGR_R2(), STDDEV() and the like) nor as a "Ranking function" but as a "Miscellaneous function". - Do I have to conclude that this category is wrong?

[Former Member]

This is another way to achieve your sorting without using window aggregate functions. I assumed that sort-string is only used for sorting and it is not needed in the result set.

 WITH RECURSIVE employee_hierarchie
       ( level,
         sort_string,
         manager_id,
         employee_id,
         name )
    AS ( SELECT CAST ( 1 AS INTEGER ) AS level,
                CAST ( e.employee_id AS LONG VARCHAR) AS sort_string,
                e.manager_id     AS manager_id,
                e.employee_id    AS employee_id,
                e.name           AS name
           FROM employee e where e.employee_id = e.manager_id
         UNION ALL
         SELECT h.level + 1,
                h.sort_string || '-' || CAST ( e.employee_id AS LONG VARCHAR), 
                e.manager_id,
                e.employee_id,
                e.name
           FROM employee_hierarchie h
                INNER JOIN employee e
                        ON h.employee_id = e.manager_id
          WHERE e.employee_id <> e.manager_id)
    SELECT level, manager_id, employee_id, name
      FROM employee_hierarchie
     ORDER BY sort_string;

[VolkerBarth]

Well, the sort_string may also be used for filtering afterwards, say to restrict the result to just one particular range within the whole tree. But whether to include it in the final select list or not is no problem here IMHO.

---

Besides that, the solution does work when sorting by the id (and is somewhat identical to Markus' solution in my other question). - However, how would I use a different sort order, such as by name or by salary (in descending order) - attributes which do not fit as well to be put inside a concatenated string?

My impression is that here I would need (or at least highly prefer) to "number child nodes" within each parent node. If I can't use ROW_NUMBER() for that and can't use an ORDER BY within the iterative query, what other options do I have?

[VolkerBarth]

Just as a sample where ROW_NUMBER() is required IMHO - a similar sample query but here in an attempt to order by salary in descending order. It should still use a depth-first order but should list each nodes's children in order of descending salary.

For that, the mentioned method does not work as a shorter sort_string will inevitably be ordered after a longer one, making the sort order wrong:

WITH RECURSIVE employee_hierarchie
       ( level,
         sort_string,
         manager_id,
         employee_id,
         name,
         salary )
    AS ( SELECT CAST ( 1 AS INTEGER ) AS level,
                -- build a "salary sort part by padding it with leading zeroes 
                RIGHT(REPEAT('0', 10) || CAST ( e.salary AS LONG VARCHAR), 12) AS sort_string,
                e.manager_id     AS manager_id,
                e.employee_id    AS employee_id,
                e.name           AS name,
                e.salary         AS salary
           FROM employee e where e.employee_id = e.manager_id
         UNION ALL
         SELECT h.level + 1,
                h.sort_string || '-' || RIGHT(REPEAT('0', 10) || CAST ( e.salary AS LONG VARCHAR), 12), 
                e.manager_id,
                e.employee_id,
                e.name,
                e.salary
           FROM employee_hierarchie h
                INNER JOIN employee e
                        ON h.employee_id = e.manager_id
          WHERE e.employee_id <> e.manager_id)
    SELECT sort_string, level, manager_id, employee_id, name, salary
      FROM employee_hierarchie
     ORDER BY sort_string desc;

Undesired result (top node is listed as last row:

sort_string,level,manager_id,employee_id,name,salary
001000000.00-000900000.00-000800000.00-000500000.00,4,6,9,Inari,500000.000000
001000000.00-000900000.00-000800000.00-000100000.00-000100000.00,5,10,13,Marlon,100000.000000
001000000.00-000900000.00-000800000.00-000100000.00-000100000.00,5,10,14,Nissa,100000.000000
001000000.00-000900000.00-000800000.00-000100000.00,4,6,10,Jordan,100000.000000
001000000.00-000900000.00-000800000.00-000100000.00,4,8,11,Khalil,100000.000000
001000000.00-000900000.00-000800000.00-000100000.00,4,8,12,Lisette,100000.000000
001000000.00-000900000.00-000800000.00,3,3,6,Fabriane,800000.000000
001000000.00-000900000.00-000800000.00,3,4,8,Hunter,800000.000000
001000000.00-000900000.00-000750000.00,3,2,5,Electra,750000.000000
001000000.00-000900000.00-000750000.00,3,3,7,Genevieve,750000.000000
001000000.00-000900000.00,2,1,2,Briana,900000.000000
001000000.00-000900000.00,2,1,3,Calista,900000.000000
001000000.00-000900000.00,2,1,4,Delmar,900000.000000
001000000.00,1,1,1,Ainslie,1000000.000000

[VolkerBarth]

Further advice and/or explanation from the query processing experts like Ani and Ivan (and Glenn, of course) would be highly appreciated!

[MarkCulp]

Are you using an aggregate function in your query?

If so, they perhaps you are getting the error due to QTS 711791 that was made in 12.0.1.3758 and 11.0.1.2836:

Summary: Recursive queries containing window aggregate functions were not prohibited

Recursive queries can not contain aggregate functions. When written using a window (OVER clause), aggregates were improperly allowed. In some cases this could lead to a crash under certain system conditions. This has been fixed: queries with windowed aggregate functions now correctly give an error.

[VolkerBarth]

Well, as you can see from the query, I'm only using the ROW_NUMBER() function. AFAIK this is not considered an aggregate function. However, it always needs a WINDOW to operate on.

Therefore I would question if this usage of ROW_NUMBER() should be prevented. - Well, if it has to be prevented, please tell me why; and then I would really like to get to know another method to "number" the children of a node dynamically, as that is what ROW_NUMBER() seems so handy here.

WITH RECURSIVE employee_hierarchie
   ( level,
     sort_string,
     manager_id,
     employee_id,
     name )
AS ( SELECT CAST ( 1 AS INTEGER ) AS level,
            CAST (ROW_NUMBER() OVER (ORDER BY e.employee_id) AS LONG VARCHAR) AS sort_string,
            e.manager_id     AS manager_id,
            e.employee_id    AS employee_id,
            e.name           AS name
       FROM employee e where e.employee_id = e.manager_id
     UNION ALL
     SELECT h.level + 1,
            h.sort_string || '-' || CAST (ROW_NUMBER() OVER (PARTITION BY e.manager_ID ORDER BY e.employee_id) AS LONG VARCHAR),
            e.manager_id,
            e.employee_id,
            e.name
       FROM employee_hierarchie h
            INNER JOIN employee e
                    ON h.employee_id = e.manager_id
      WHERE e.employee_id <> e.manager_id)
SELECT sort_string, level, manager_id, employee_id, name
  FROM employee_hierarchie
 ORDER BY sort_string;

[MarkCulp]

I will need to ask the QP Guru (aka Ani) on Monday...