How to make sa_rowgenerator() return an empty result set when rstart is NULL?

[VolkerBarth]

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

call sa_rowgenerator(null, null, 1)

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...)

[MarkCulp · Accepted solution]

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

• rstep = 0, or
• rstart > rstop, or
• rstart < rstop and rstep < 0

HTH

[VolkerBarth · Accepted solution]

rstart > rstop

Interestingly. I had expected that the rule should go

rstart > rstop and rstep > 0

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.

[MarkCulp · Accepted solution]

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. 😞

[VolkerBarth · Accepted solution]

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.