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":
The query does use UNION ALL etc. 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? asked 06 Oct '12, 05:13 Volker Barth |
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. answered 09 Oct '12, 14:06 Nica _SAP 1
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?
(09 Oct '12, 14:14)
Mark Culp
2
The SQL construct ROW_NUMBER() OVER (PARTITION BY e.manager_ID ORDER BY e.employee_id) is a window aggregate function.
(09 Oct '12, 14:20)
Nica _SAP
Replies hidden
Thanks Ani.
(09 Oct '12, 14:23)
Mark Culp
1
@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?
(10 Oct '12, 03:50)
Volker Barth
|
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:
answered 06 Oct '12, 06:35 Mark Culp 1
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;
(06 Oct '12, 09:46)
Volker Barth
Replies hidden
|
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; answered 09 Oct '12, 14:42 Nica _SAP 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?
(10 Oct '12, 03:59)
Volker Barth
Replies hidden
1
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
(10 Oct '12, 04:17)
Volker Barth
Further advice and/or explanation from the query processing experts like Ani and Ivan (and Glenn, of course) would be highly appreciated!
(12 Oct '12, 04:31)
Volker Barth
|
To cite from my comment on Ani's comment on Ani's answer:
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?
...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...
...just a reminder: May I get any more insights from the QP experts? I'd appreciate that!
@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.