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

asked 27 Jan '16, 08:46

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

edited 27 Jan '16, 10:06

Mark%20Culp's gravatar image

Mark Culp
23.2k9132272


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

permanent link

answered 27 Jan '16, 10:19

Mark%20Culp's gravatar image

Mark Culp
23.2k9132272
accept rate: 40%

edited 27 Jan '16, 10:19

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.

(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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×412
×18

question asked: 27 Jan '16, 08:46

question was seen: 253 times

last updated: 28 Jan '16, 05:34