|
Just a slight issue: I'm building ranges of dates via the (really helpful!) sa_rowgenerator system procedure and left-join them to other dates. This works great. However, sometimes the procedure should return no rows. I would expect it to do that when both its rstart and rend arguments are NULL. However does return one single row with row_num = 0. While I can certainly restrict the output in that cases with a WHERE clause (or could explicitly use a rstart value > rend value or a NULL as rstep value to get an empty result set), I do not really understand the procedure's behaviour here. Just for comparison: If I omit the first and/or second parameter, they are set to their default values (0 resp. 100) as expected. So an explicit NULL value is not treated as default value, again as expected. Does the procedure treat NULL = NULL and therefore count that as one initial step? (As stated, I'm not really asking for a solution but wanted to share another pitfall I've found...) |
|
I had a look at the code and this procedure treats NULL as 0 which is perhaps not the best behavior ... and I would not recommend that you rely on this behaviour since it could change in the future. sa_rowgenerator() will currently return no rows iff
HTH
Interestingly. I had expected that the rule should go
but I'm wrong - so it's not possible to use negative rstep values to step down (besides the special case "call sa_rowgenerator(n, n, -1)" which returns one row)? That being said, I'll follow your "Undocumented behaviour" warning and will restrict the result set by a further WHERE clause, as expressed in my question.
(27 Jan '16, 10:38)
Volker Barth
Replies hidden
Yes, I agree that it would have been nice to be able to use a negative step to get a descending list but alas that is not the case. :-(
(27 Jan '16, 10:47)
Mark Culp
Well, I guess decreasing values are easily created by an "ORDER BY row_num DESC", so that's not really an issue. However, I would suggest to declare rstep as UNSIGNED INT then to clariy its restriction - just in case the implementation of that procedure "could change in the future"...:) - or at least to document the value as non-negative.
(28 Jan '16, 05:34)
Volker Barth
|