generate_series does not support negative step
(1.1) By luuk on 2020-12-03 07:10:02 edited from 1.0 [source]
It does not seems to work with negative step:
sqlite> select * from generate_series(1,3,1);
value
-----
1
2
3
sqlite> select * from generate_series(3,1,-1);
sqlite>
OK, i know i can do it with a recursive with:
sqlite> with recursive s(v) as (select 3 union all select v-1 from s where v-1>=1) select * from s;
v
-
3
2
1
sqlite>
It would be nice if a negative step is supported too, or if the docs state that only positive step values should be used.
(2) By Gunter Hick (gunter_hick) on 2020-12-03 07:36:43 in reply to 1.1 [link] [source]
Try select 4-value from generate_series(1,3,1);
(3) By Richard Hipp (drh) on 2020-12-03 14:29:43 in reply to 1.1 [link] [source]
It is the nature of SQL (SQL in general, not just SQLite) that the order of values is arbitrary unless you include an ORDER BY clause in the query. So if you say:
SELECT * FROM generate_series(1,3,1);
Then the system is free to return 1,2,3 or 3,2,1 or 2,1,3 or any of the other three possibilities, as it sees fit. And it might return a difference order each time. If you want the values in a specific order, you must use an ORDER BY clause.
And, as it happens, the generate_series() table-valued function is implemented so that it knows about the ORDER BY clause and will generate the numbers in the specific order you request. So if you say:
SELECT * FROM generate_series(1,3,1) ORDER BY value DESC;
Then SQLite will generate 3,2,1 and it will do so natively - that is to say without having to actually do any sorting. In other words, the generate_series() table counts down rather than counts up.
Nevertheless, I have just now enhanced the generate_series() table-valued function so that it will accept a negative step value. And if you give it a negative step value and you omit the ORDER BY clause, then it assumes an "ORDER BY value DESC" clause and counts down rather than up. I'm not yet 100% convinced that this is the right thing to do, so I may yet revert that enhancement before the next release, but it is in the code for now.
(4) By luuk on 2020-12-04 07:28:42 in reply to 3 [link] [source]
OK,
I should have explained, in more detail, that I was not concerned about the order by.
It was more a suggestion if START is higher than END, and STEP is NEGATIVE, i would like it when results are returned.
Thanks for the 'fix', and I hope to see them implemented 😉