SQLite User Forum

(minor) generate_series doc wrong parameter name
Login
Chronological

(minor) generate_series doc inconsistencies

(1.1) By Roger Binns (rogerbinns) on 2023-01-27 00:19:21 edited from 1.0 [source]

This page documents generate_series: https://sqlite.org/series.html

It keeps calling the second parameter END. However the parameter name is actually named STOP.

This fails:

    select value, start, end, step from generate_series(1,20,2);
    Parse error: no such column: end

This works:

    select value, start, stop, step from generate_series(1,20,2);

It is worth mentioning that page doesn't document the hidden parameters while this one does: https://sqlite.org/vtab.html#table_valued_functions

Edit: the page says that 9223372036854775807 (64 bit biggest value) is the default end. However it is actually 4294967295 (32 bit biggest value).

    select *,start,stop,step from generate_series(4294967294);
    ┌────────────┬────────────┬────────────┬──────┐
    │   value    │   start    │    stop    │ step │
    ├────────────┼────────────┼────────────┼──────┤
    │ 4294967294 │ 4294967294 │ 4294967295 │ 1    │
    │ 4294967295 │ 4294967294 │ 4294967295 │ 1    │
    └────────────┴────────────┴────────────┴──────┘

It also looks like generate_series could declare itself as WITHOUT ROWID. Is there a reason it isn't?

Roger

(2) By Larry Brasfield (larrybr) on 2023-01-27 02:23:13 in reply to 1.1 [link] [source]

Thanks, Roger. Most of your pickups are incorporated into a fix.

It also looks like generate_series could declare itself as WITHOUT ROWID. Is there a reason it isn't?

Yes. It has a rowid, or at least pretends to have one. As to the utility of that, I cannot say, but it is unlikely to vanish now.

(3.1) By Roger Binns (rogerbinns) on 2023-01-27 18:13:13 edited from 3.0 in reply to 2 [link] [source]

I've found several more issues with generate_series, mostly not doing error checking. The source code calls it a demonstration so that is fine. However the documentation page doesn't clearly state if you can expect the same robustness as the rest of SQLite.

I've been unable to get it to generate any errors, instead just falling back to using defaults or empty results. Here are some examples:

Works as expected:

    SELECT value FROM generate_series WHERE start=5 AND stop=50;

Empty results:

    SELECT value FROM generate_series WHERE start>5 AND stop=50; 
    SELECT value FROM generate_series WHERE start>=5 AND stop=50;

Treats start as zero:

    SELECT value FROM generate_series WHERE start!=5 AND stop=50;

These behave as though I provided 0, 10, 1:

    SELECT value FROM generate_series(0, 10, 0);
    SELECT value FROM generate_series('word', 10, 0.5);
    SELECT value FROM generate_series('0.999999999', 10, 'duck');
    SELECT value FROM generate_series(0.999999999, 10, 'duck');

If it is expected to be robust then my recommendations are:

  • BestIndex doing SQLITE_CONSTRAINT if the constraints are not SQLITE_INDEX_CONSTRAINT_EQ
  • Checking the type of parameters/values and erroring if they are not integers
  • Error if step value is zero

Other databases allow start being bigger than stop with a negative step. I found one page mentioning ORDER BY value DESC, but the doc page doesn't. (1, 10, -1) does produce values in descending order. This produces no results, but ideally something should be in the doc. 

    SELECT value FROM generate_series(10, 1, -1);

Edit This more due to the virtual table mechanism, but you can do a query like this:

    SELECT value FROM generate_series(1,10) WHERE start=3;

It is passing for 1 for start and separately 3 in the WHERE. Both are provided in the constraint list to BestIndex. generate_series produces no results.