Interesting... two possibilities:

1. The type conversion rules appear to have changed and the expression now seems to have INTEGER affinity, or
2. Type conversion rules haven't changed but the affinity of generate_series() as INTEGER is now being enforced where it wasn't previously

sqlite> select * from generate_series(1,10) where cast(value as real)<5.5;
┌───────┐
│ value │
├───────┤
│ 1     │
│ 2     │
│ 3     │
│ 4     │
│ 5     │
└───────┘

The documentation isn't explicit about the cases of INTEGER op REAL (and vice versa). It discusses TEXT/BLOB conversion to numeric, but

Otherwise, no affinity is applied and both operands are compared as is.

It's not at all clear what is meant by compared as is.

Neither of those. Check-in 2024-08-22T18:12Z attempted to enhance the generate_series virtual table to take advantage of "value<NUM" constraints. But when I implemented that, it never occurred to me that NUM might be a floating point value. After all, generate_series deals with integer. So I used "sqlite3_value_int64()" to extract the value of NUM, which rounds it down from 5.5 to 5, and because the comparison is less-than, not less-than-or-equal-to, it therefore omits the final number.

It's very easy to hide the constraint from generate_series.

    select * from generate_series(1,10) where not value>=5.5;

or even

    select * from generate_series(1,10) where +value<5.5;

A related question: What should be the correct output of

SELECT value FROM generate_series(11.5,14.25);

Possible answers:

1. 11, 12, 13, 14
2. 12, 13, 14
3. Raise an error complaining that the arguments must be integers.

The current implementation does option 1. Should I change it?

Showing the current mismatch between the constraints and the returned values more clearly:

sqlite> SELECT value, start, stop FROM generate_series WHERE start=11.5 AND stop=14.25;
+-------+-------+------+
| value | start | stop |
+-------+-------+------+
| 11    | 11    | 14   |
| 12    | 11    | 14   |
| 13    | 11    | 14   |
| 14    | 11    | 14   |
+-------+-------+------+

My opinion: either option 3, or option 2 but returning the original values instead of the cast-to-integer ones in the start, stop, and step columns.

I think 2) is wrong. Worst of both worlds.

I would be keen on 3) actually, as the issue is then explicit and less likely to lead to poor assumptions which underlie irritating bugs in the future.

1. at least works consistently with integer parsing.

I believe the author of sqlite has posted his regret about following Postel's law before.

Consider an optional parameter that selects the behavior.

This could get me stuck in the psych ward for a few months, but what about

SELECT value FROM generate_series(11.5, 14.25);
11.5
12.5
13.5

That would generate too many bug reports about missing the stop value (because of rounding errors).

Yeah, makes sense. Oh well, worth putting put there I guess