SQLite Forum

That would seem to indicate that each time on the merry-go-round you are consuming some resource and forgetting to release it.

So you are saying that the particular line you are attributing this to (the sqlite3_exec) "works for a while" and then it does not?

What do you mean "a few hours" afterwards?

Yes, exactly.

Note that the select ts, sum(size_t) over (order by ts desc from mqtt6 will output the ts and sum so far obtained in descending order by ts (and ascending sum). This is a subquery with a limit 1 and a condition on the sum(size_t) so the summing will run until it hits the first row (going backwards in time) where the size_t sum exceeds 1024000 and will return the ts for that row so the delete can delete all the rows less or equal to this ts.

The index on (ts, size_t) serves two purposes. (1) it can be used by the sum ... over process so that only the index is scanned without need to access or process the underlying table. (2) to find all the rows to be deleted without scanning the underlying table (thus rows can be inserted out of order if one wants and it will make no difference to the running time).

You can add an order by to the sum ... over query for the greater certainty, but at the moment it is not required (I suppose this is an implementation detail), that is:

create trigger less_den_ten_mb_delete after insert on mqtt6
begin
  delete from mqtt6
        where ts <= (
                     select ts 
                       from (
                                select ts, 
                                       sum(size_t) over (order by ts desc) as size
                                  from mqtt6
                              order by ts desc
                             ) 
                      where size >= 1024000 
                      limit 1
                    );
end;

What is a VDS? Venereal Disease Symptoms? Depending on the particular symptoms, penicillin may be curative.

You have provided insufficient information for meaningful diagnosis.

Note that many "virtualization emulators" do not actually provide the functional facilities required for SQLite3 to function correctly. Perhaps your VDS is defective as designed ...

SQLite version 3.36.0 2021-06-15 11:18:07
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> create table mqtt6
   ...> (
   ...>     ts integer not null,
   ...>     size_t integer not null
   ...> );
sqlite> create index mqtt6_ts_size_t on mqtt6 (ts, size_t);
sqlite> insert into mqtt6 select value, RandomV(1024) + 1 from generate_series where start == 1 and stop == 1000000;
sqlite> create trigger less_den_ten_mb_delete after insert on mqtt6 when (select sum(size_t) from mqtt6) > 1024000
   ...> begin
   ...>   delete from mqtt6
   ...>         where ts <= (
   ...>                        select ts
   ...>                          from (
   ...>                                select ts,
   ...>                                       sum(size_t) over (order by ts desc) as size
   ...>                                  from mqtt6
   ...>                               )
   ...>                         where size >= 1024000
   ...>                         limit 1
   ...>                     );
   ...> end;
sqlite> .timer on
sqlite> .eqp on
sqlite>
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 1000000  │ 512366354   │ 1       │ 1000000 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.147 user 0.140625 sys 0.000000
sqlite> insert into mqtt6 values (1000001, RandomV(1024)+1);
Run Time: real 0.504 user 0.500000 sys 0.015625
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2016     │ 1023824     │ 997986  │ 1000001 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.004 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000002, RandomV(1024)+1);
Run Time: real 0.002 user 0.000000 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2016     │ 1023316     │ 997987  │ 1000002 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.006 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000003, RandomV(1024)+1);
Run Time: real 0.002 user 0.000000 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2015     │ 1023479     │ 997989  │ 1000003 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000004, RandomV(1024)+1);
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2016     │ 1023841     │ 997989  │ 1000004 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000005, RandomV(1024)+1);
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2017     │ 1023939     │ 997989  │ 1000005 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.006 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000006, RandomV(1024)+1);
Run Time: real 0.002 user 0.015625 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2017     │ 1023846     │ 997990  │ 1000006 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000007, RandomV(1024)+1);
Run Time: real 0.002 user 0.000000 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2017     │ 1023853     │ 997991  │ 1000007 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000008, RandomV(1024)+1);
Run Time: real 0.001 user 0.015625 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2016     │ 1023964     │ 997993  │ 1000008 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.006 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000009, RandomV(1024)+1);
Run Time: real 0.002 user 0.015625 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2016     │ 1023726     │ 997994  │ 1000009 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite>

How about something like:

create trigger less_den_ten_mb_delete after insert on mqtt6 when (select sum(size_t) from mqtt6) > 1024000
begin
  delete from mqtt6
        where ts <= (
                       select ts 
                         from (
                               select ts, 
                                      sum(size_t) over (order by ts desc) as size
                                 from mqtt6
                              ) 
                        where size >= 1024000 
                        limit 1
                    );
end;

and get rid of the delete trigger. You can probably do away with the when clause since if size >= 1024000 is never met then the delete will do nothing and assuming that you have an covering index on (ts, size_t) it will be very fast.

There is nothing magical. The shell is processing the command (the entire command given to the shell) in accordance with the rules by which every other shell command is processed by that shell. The fact that the target executable word happens to be "sqlite3' is quite irrelevant to the process.

The quoting and escaping rules are exactly and precisely the same as if the target executable word was 'awk' or 'grep' or 'ls'.

You will note that pragma locking_mode=exclusive only works this way when the journal_mode is not wal. Setting journal_mode=wal and locking_mode=exclusive have an entirely different meaning.

You might, for example, create a crc32 aggregate function. Then you can set the user_version to the result of select crc32(sql) from (select sql from sqlite_master order by sql)) which will verify that the database schema is exactly what you expect it to be.

The only reason for setting the journal_mode to wal after setting locking_mode normal is that after being in locking_mode exclusive in a non-wal journal_mode, the change does not "become apparent" until the next time the commit machinery runs (so you could select count(*) from sqlite_master or any other command in its place).

If the database is "new" and "empty" the following conditions hold:

select count(*) from sqlite_master; will be 0
pragma journal_mode; will be 'delete'
pragma application_id; will be 0
pragma user_version; will be 0
pragma page_count; will be 0

If these conditions all hold then do:

pragma locking_mode=exclusive;
begin immediate;
pragma application_id=(some non-zero number)
pragma user_version=(some non-zero number)
commit;

If you get here, then you have exclusive access to the database so that you can do your initialization after which you do:

pragma locking_mode=normal;
pragma journal_mode=wal;

and you are good to go.

If any of the pre-conditions fail, then this is not a "new and empty database" and if the application_id/user_version are not what you want then you need to wait a bit and check again, eventually bailing because it is not a database you can work with.

Why are you checking if the database exists at all? You do not care if the database exists or not. You only care that you can open it.

You should be opening it and then checking to see if it contains what you expect it to contain, and if it does not, THEN you initialize the database.

If 700 copies of the application open the database at the SAME TIME, then all of them may notice (by testing) that the database does not contain anything (is empty) but ONLY ONE OF THEM will be able to begin a transaction to "initialize the database" and 699 of them will get SQLITE_BUSY when they try to start the transaction necessary to "initialize the database".

Where is the race condition?

To use the wonderful error messages from my favourite Operating System (TRSDOS):

HOW?

The Window Query that generates the new row_number excludes rows with strongs_no == 'punc2' from the Window Function. You could use a FILTER clause but that does not work with "built-in" window functions, only aggregate window functions, so since those rows are excluded, you have to add them back in with a union.

The union can be a union all because the rowid in the update table (src) is used to lookup the row to be updated in dst, so they do not need to be in order.

If you want this to be able to be run multiple times when the data changes, then use:

update x as dst
   set desired_new_index = ni
  from (
        select _rowid_,
               row_number() over (partition by book_no, chapter_no, verse_no order by index_no) as ni
          from x
         where strongs_no != 'punc2'
       union all
        select _rowid_,
               null as ni
         from x
        where strongs_no == 'punc2'
       ) as src
 where src._rowid_ == dst._rowid_
;

** No need to sort the update table, so it can use a UNION ALL **

The Window Function is probably more efficient.

In both cases you will need the appropriate indexes. (book_no, chapter_no, verse_no, index_no, strongs_no) for the Window Function, and (strongs_no) if you want to add the null values union.

You will also need the (book_no ...) index for your query as well.

If you really want the maximum speed for the Window Function )with the union) you probably want indexes thus:

create unique index xx1 on x (book_no, chapter_no, verse_no, index_no) where strongs_no != 'punc2';
create index xx2 on x (strongs_no) where strongs_no == 'punc2';

See https://sqlite.org/windowfunctions.html for details of the Window Functions.

Like this perhaps?

create table x
(
  book_no integer,
  chapter_no integer,
  verse_no integer,
  index_no integer,
  strongs_no text,
  desired_new_index integer
);

insert into x (book_no, chapter_no, verse_no, index_no, strongs_no) values
(1, 1, 2, 1, 'H776'),
(1, 1, 2, 2, 'H1961'),
(1, 1, 2, 3, 'H8414'),
(1, 1, 2, 4, 'punc2'),
(1, 1, 2, 5, 'H922'),
(1, 1, 2, 6, 'punc2'),
(1, 1, 2, 7, 'H2822'),
(1, 1, 2, 8, 'H5921'),
(1, 1, 2, 9, 'H6440'),
(1, 1, 2, 10, 'H8415'),
(1, 1, 2, 11, 'punc2'),
(1, 1, 2, 12, 'H7307'),
(1, 1, 2, 13, 'H430'),
(1, 1, 2, 14, 'H7363'),
(1, 1, 2, 15, 'H5921'),
(1, 1, 2, 16, 'H6440'),
(1, 1, 2, 17, 'H4325'),
(1, 1, 2, 18, 'punc2'),
(1, 1, 3, 1, 'H430');


update x as dst
   set desired_new_index = ni
  from (
        select _rowid_,
               row_number() over (partition by book_no, chapter_no, verse_no order by index_no) as ni
          from x
         where strongs_no != 'punc2'
       ) as src
 where src._rowid_ == dst._rowid_
;

select * from x;
┌─────────┬────────────┬──────────┬──────────┬────────────┬───────────────────┐
│ book_no │ chapter_no │ verse_no │ index_no │ strongs_no │ desired_new_index │
├─────────┼────────────┼──────────┼──────────┼────────────┼───────────────────┤
│ 1       │ 1          │ 2        │ 1        │ H776       │ 1                 │
│ 1       │ 1          │ 2        │ 2        │ H1961      │ 2                 │
│ 1       │ 1          │ 2        │ 3        │ H8414      │ 3                 │
│ 1       │ 1          │ 2        │ 4        │ punc2      │                   │
│ 1       │ 1          │ 2        │ 5        │ H922       │ 4                 │
│ 1       │ 1          │ 2        │ 6        │ punc2      │                   │
│ 1       │ 1          │ 2        │ 7        │ H2822      │ 5                 │
│ 1       │ 1          │ 2        │ 8        │ H5921      │ 6                 │
│ 1       │ 1          │ 2        │ 9        │ H6440      │ 7                 │
│ 1       │ 1          │ 2        │ 10       │ H8415      │ 8                 │
│ 1       │ 1          │ 2        │ 11       │ punc2      │                   │
│ 1       │ 1          │ 2        │ 12       │ H7307      │ 9                 │
│ 1       │ 1          │ 2        │ 13       │ H430       │ 10                │
│ 1       │ 1          │ 2        │ 14       │ H7363      │ 11                │
│ 1       │ 1          │ 2        │ 15       │ H5921      │ 12                │
│ 1       │ 1          │ 2        │ 16       │ H6440      │ 13                │
│ 1       │ 1          │ 2        │ 17       │ H4325      │ 14                │
│ 1       │ 1          │ 2        │ 18       │ punc2      │                   │
│ 1       │ 1          │ 3        │ 1        │ H430       │ 1                 │
└─────────┴────────────┴──────────┴──────────┴────────────┴───────────────────┘

The DISTINCT or ALL keyword works exactly the same as it does everywhere else that it can be used. The default is ALL, except in the case of UNION where the default is DISTINCT.

SELECT ALL ....

means to return ALL the rows (even duplicate results) when performing the projection.

SELECT DISTINCT ...

means to return only the DISTINCT rows (duplicates removed) when performing the projection.

UNION ALL

means to return all the rows, even duplicates, when computing the resulting union of two projections

UNION DISTINCT means remove duplicate rows from the result when computing the union of two projections.

The default for UNION is DISTINCT and you may not specify the DISTINCT keyword.

The default for SELECT and aggregate functions is ALL and you may specify either keyword or none at all. An aggregate function in which the DISTINCT keyword is specified may only specify one argument.

Note the railroad diagram is incorrect. You may specify:

function ( [ALL] arg, arg, arg, ... arg )
function ( DISTINCT arg )
function ( * )
function ( )

You cannot specify DISTINCT and more than one arg, although this does not apply to the optional ALL, which is silent.

Example:

sqlite> select count(ALL value % 10) from wholenumber where value between 1 and 100;
┌───────────────────────┐
│ count(ALL value % 10) │
├───────────────────────┤
│ 100                   │
└───────────────────────┘
sqlite> select count(DISTINCT value % 10) from wholenumber where value between 1 and 100;
┌────────────────────────────┐
│ count(DISTINCT value % 10) │
├────────────────────────────┤
│ 10                         │
└────────────────────────────┘
sqlite>

That code works fine for me. I would suspect that you are not linking against what you think you are linking against.

How about you add the following line:

        printf("sqlite3 libversion: %s\n", sqlite3_libversion());

after the line

        printf("bytes_written=%llu  %s\n",bytes_written,sql);

and see if that sheds any light ...

bytes_written=0  insert  INTO mqtt6 values ('abcd',1623066863471168257,1623066863471168257,35) RETURNING (SELECT sum(size_t) from mqtt6);
sqlite3 libversion: 3.36.0

That code fragment cannot possibly result in the text shown. The variable sql will point to a text string that contains:

insert or REPLACE INTO mqtt6 values.('abcd',something,something,35)

which looks absolutely nothing like the output you have provided.

What is the version of SQLite3 that you are linking with? Perhaps it does not understand the "returning" clause. (The sqlite3 CLI is an SQLite3 Application and does not have to be linked against the same version of SQLite3 as a.out is being linked to).

Why do you assume that a race condition exists (or can exist) that you need to (or can) mitigate in any way whatsoever?

You are not opening a file, you are opening a database. If in the process of opening a database there is a race condition against the underlying file, that would be a bug in the SQLite3 library.

Just because you want to your car to be yellow to avoid it being green, where the car manufacturer does not sell green cars, is not a valid reason for wanting a yellow car. No matter what you do, you will not get a green car, so why bother specifying a colour at all if all you want to do is avoid getting a green car, which the car manufacturer cannot sell to you anyway?

It sounds like you expending extra cycles in what is commonly called "premature optimization".

In other words, you are expending effort in avoidance of a problem which does not yet exist (and which cannot exist).

sqlite> create table x(x text);
sqlite> insert into x select value from wholenumber where value between 1 and 100000000;
sqlite> create index i on x(length(x));
sqlite> .eqp on
sqlite> .timer on
sqlite> select sum(length(x)) from x;
QUERY PLAN
`--SCAN x (~1048576 rows)
┌────────────────┐
│ sum(length(x)) │
├────────────────┤
│ 788888898      │
└────────────────┘
Run Time: real 9.542 user 9.531250 sys 0.000000
sqlite> select sum(length(x)) from x indexed by i;
QUERY PLAN
`--SCAN x USING INDEX i (~1048576 rows)
┌────────────────┐
│ sum(length(x)) │
├────────────────┤
│ 788888898      │
└────────────────┘
Run Time: real 12.403 user 12.375000 sys 0.000000
sqlite> create index j on x(length(x),x);
Run Time: real 30.600 user 61.453125 sys 9.109375
sqlite> select sum(length(x)) from x indexed by j;
QUERY PLAN
`--SCAN x USING COVERING INDEX j (~1048576 rows)
┌────────────────┐
│ sum(length(x)) │
├────────────────┤
│ 788888898      │
└────────────────┘
Run Time: real 9.536 user 9.531250 sys 0.000000
sqlite>

The fastest solution is to do a table scan and compute the result.

See https://sqlite.org/c3ref/txn_state.html which will tell you the transaction state of a particular database schema.

The CLI is an Application program that uses the SQLite3 library. It is no different from any other application that uses the SQLite3 library except that it happens to be written and supported by the authors of SQLite3.

Do you get the same result with the current versions of SQLite3?

How about:

create table tempunion as select * from tab1;
insert into tempunion select * from tab2 except select * from tab1;

If there were no duplicates (that is, the operation is UNION ALL) then you can omit the except select * from tab1.

If you mean that you want the affinity of test1111.fp2 to be real then you can declare the view as:

CREATE VIEW test1111 
AS SELECT *,
          cast(SUM(future_price) as REAL) as fp2
     FROM t_spotprice
    WHERE tradingday = 20200103;

The affinity of the result of an expression (which also includes functions) is NONE (No Affinity). This is quite separate from the datatype of the result value.

Using the current tip of trunk (my version of a8d921136f) I get the following results (this is after analyze being run).

Clearly the different spellings of the same request result in wildly different query plans and vdbe code being generated, the most efficient being the "direct join" where the planner has the most latitude to choose the nesting order.

The interesting thing is that the IN form should be no less efficient that the EXISTS form, but it is.

sqlite> SELECT count(_rowid_)
   ...>   FROM edge
   ...>  WHERE node_from IN sub_nodes
   ...>    AND node_to IN sub_nodes
   ...> ;
QUERY PLAN
|--SEARCH edge USING COVERING INDEX edge_to_from (node_to=? AND node_from=?) (~44 rows)
|--USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR
`--USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     31    0                    0   Start at 31
1     Null           0     1     2                    0   r[1..2]=NULL
2     OpenRead       3     8     0     k(3,,,)        0   root=8 iDb=0; edge_to_from
3     ColumnsUsed    3     0     0     3              0
4     Explain        4     0     0     SEARCH edge USING COVERING INDEX edge_to_from (node_to=? AND node_from=?) (~44 rows)  0
5     Noop           0     0     0                    0   Begin WHERE-loop0: edge
6     Once           0     8     0                    0
7     OpenRead       4     4     0     1              0   root=4 iDb=0; sub_nodes
8     Rewind         4     26    0                    0
9       Rowid          4     3     0                    0   r[3]=rowid
10      IsNull         3     25    0                    0   if r[3]==NULL goto 25
11      Once           0     13    0                    0
12      OpenRead       5     4     0     1              0   root=4 iDb=0; sub_nodes
13      Rewind         5     25    0                    0
14        Rowid          5     4     0                    0   r[4]=rowid
15        IsNull         4     24    0                    0   if r[4]==NULL goto 24
16        SeekScan       18    19    0                    0   Scan-ahead up to 18 rows
17        SeekGE         3     24    3     2              0   key=r[3..4]
18          IdxGT          3     24    3     2              0   key=r[3..4]
19          Noop           0     0     0                    0   Begin WHERE-core
20          IdxRowid       3     5     0                    0   r[5]=rowid
21          AggStep        0     5     1     count(1)       1   accum=r[1] step(r[5])
22          Noop           0     0     0                    0   End WHERE-core
23        Next           3     18    0                    0
24      Next           5     14    0                    0
25    Next           4     9     0                    0
26    Noop           0     0     0                    0   End WHERE-loop0: edge
27    AggFinal       1     1     0     count(1)       0   accum=r[1] N=1
28    Copy           1     6     0                    0   r[6]=r[1]
29    ResultRow      6     1     0                    0   output=r[6]
30    Halt           0     0     0                    0
31    Transaction    0     0     7     0              1   usesStmtJournal=0
32    Goto           0     1     0                    0
┌────────────────┐
│ count(_rowid_) │
├────────────────┤
│ 1929           │
└────────────────┘
Run Time: real 5.817 user 5.703125 sys 0.031250
sqlite> SELECT count(_rowid_)
   ...>   FROM edge
   ...>  WHERE +node_from IN sub_nodes
   ...>    AND node_to IN sub_nodes
   ...> ;
QUERY PLAN
|--SEARCH edge USING COVERING INDEX edge_to_from (node_to=?) (~44 rows)
|--USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR
`--USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     35    0                    0   Start at 35
1     Null           0     1     2                    0   r[1..2]=NULL
2     OpenRead       3     8     0     k(3,,,)        2   root=8 iDb=0; edge_to_from
3     ColumnsUsed    3     0     0     3              0
4     Explain        4     0     0     SEARCH edge USING COVERING INDEX edge_to_from (node_to=?) (~44 rows)  0
5     Noop           0     0     0                    0   Begin WHERE-loop0: edge
6     Once           0     8     0                    0
7     OpenRead       4     4     0     1              0   root=4 iDb=0; sub_nodes
8     Rewind         4     30    0                    0
9       Rowid          4     3     0                    0   r[3]=rowid
10      IsNull         3     29    0                    0   if r[3]==NULL goto 29
11      SeekGE         3     29    3     1              0   key=r[3]
12        IdxGT          3     29    3     1              0   key=r[3]
13        Noop           0     0     0                    0   begin IN expr
14        Once           0     16    0                    0
15        OpenRead       5     4     0     1              0   root=4 iDb=0; sub_nodes
16        Column         3     1     4                    0   r[4]=edge.node_from
17        SeekRowid      5     28    4                    0   intkey=r[4]
18        Goto           0     24    0                    0
19        Goto           0     28    0                    0
20        Rewind         5     28    0                    0
21        Column         5     0     5                    0   r[5]=
22        Ne             4     28    5     BINARY-8       0   if r[5]!=r[4] goto 28
23        Goto           0     28    0                    0   end IN expr
24        Noop           0     0     0                    0   Begin WHERE-core
25        IdxRowid       3     5     0                    0   r[5]=rowid
26        AggStep        0     5     1     count(1)       1   accum=r[1] step(r[5])
27        Noop           0     0     0                    0   End WHERE-core
28      Next           3     12    0                    0
29    Next           4     9     0                    0
30    Noop           0     0     0                    0   End WHERE-loop0: edge
31    AggFinal       1     1     0     count(1)       0   accum=r[1] N=1
32    Copy           1     6     0                    0   r[6]=r[1]
33    ResultRow      6     1     0                    0   output=r[6]
34    Halt           0     0     0                    0
35    Transaction    0     0     7     0              1   usesStmtJournal=0
36    Goto           0     1     0                    0
┌────────────────┐
│ count(_rowid_) │
├────────────────┤
│ 1929           │
└────────────────┘
Run Time: real 0.124 user 0.015625 sys 0.015625
sqlite> SELECT count(_rowid_)
   ...>   FROM edge
   ...>  WHERE +node_from IN sub_nodes
   ...>    AND +node_to IN sub_nodes
   ...> ;
QUERY PLAN
|--SCAN edge (~180224 rows)
|--USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR
`--USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     39    0                    0   Start at 39
1     Null           0     1     2                    0   r[1..2]=NULL
2     OpenRead       0     3     0     2              0   root=3 iDb=0; edge
3     ColumnsUsed    0     0     0     3              0
4     Explain        4     0     0     SCAN edge (~180224 rows)  0
5     Noop           0     0     0                    0   Begin WHERE-loop0: edge
6     Rewind         0     34    0                    0
7       Noop           0     0     0                    0   begin IN expr
8       Once           0     10    0                    0
9       OpenRead       3     4     0     1              0   root=4 iDb=0; sub_nodes
10      Column         0     0     3                    0   r[3]=edge.node_from
11      SeekRowid      3     33    3                    0   intkey=r[3]
12      Goto           0     18    0                    0
13      Goto           0     33    0                    0
14      Rewind         3     33    0                    0
15      Column         3     0     4                    0   r[4]=
16      Ne             3     33    4     BINARY-8       0   if r[4]!=r[3] goto 33
17      Goto           0     33    0                    0   end IN expr
18      Noop           0     0     0                    0   begin IN expr
19      Once           0     21    0                    0
20      OpenRead       4     4     0     1              0   root=4 iDb=0; sub_nodes
21      Column         0     1     4                    0   r[4]=edge.node_to
22      SeekRowid      4     33    4                    0   intkey=r[4]
23      Goto           0     29    0                    0
24      Goto           0     33    0                    0
25      Rewind         4     33    0                    0
26      Column         4     0     5                    0   r[5]=
27      Ne             4     33    5     BINARY-8       0   if r[5]!=r[4] goto 33
28      Goto           0     33    0                    0   end IN expr
29      Noop           0     0     0                    0   Begin WHERE-core
30      Rowid          0     5     0                    0   r[5]=rowid
31      AggStep        0     5     1     count(1)       1   accum=r[1] step(r[5])
32      Noop           0     0     0                    0   End WHERE-core
33    Next           0     7     0                    1
34    Noop           0     0     0                    0   End WHERE-loop0: edge
35    AggFinal       1     1     0     count(1)       0   accum=r[1] N=1
36    Copy           1     6     0                    0   r[6]=r[1]
37    ResultRow      6     1     0                    0   output=r[6]
38    Halt           0     0     0                    0
39    Transaction    0     0     7     0              1   usesStmtJournal=0
40    Goto           0     1     0                    0
┌────────────────┐
│ count(_rowid_) │
├────────────────┤
│ 1929           │
└────────────────┘
Run Time: real 0.151 user 0.031250 sys 0.046875
sqlite> SELECT count(_rowid_)
   ...>   FROM edge
   ...>  WHERE EXISTS (SELECT * FROM sub_nodes WHERE node_id == node_from)
   ...>    AND EXISTS (SELECT * FROM sub_nodes WHERE node_id == node_to)
   ...> ;
QUERY PLAN
|--SCAN edge (~180224 rows)
|--CORRELATED SCALAR SUBQUERY 1
|  `--SEARCH sub_nodes USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
`--CORRELATED SCALAR SUBQUERY 2
   `--SEARCH sub_nodes USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     49    0                    0   Start at 49
1     Null           0     1     2                    0   r[1..2]=NULL
2     OpenRead       0     3     0     2              0   root=3 iDb=0; edge
3     ColumnsUsed    0     0     0     3              0
4     Explain        4     0     0     SCAN edge (~180224 rows)  0
5     Noop           0     0     0                    0   Begin WHERE-loop0: edge
6     Rewind         0     44    0                    0
7       Integer        21    4     0                    0   r[4]=21; return address
8       Integer        0     5     0                    0   r[5]=0; Init EXISTS result
9       Integer        1     6     0                    0   r[6]=1; LIMIT counter
10      OpenRead       1     4     0     0              0   root=4 iDb=0; sub_nodes
11      ColumnsUsed    1     0     0     0              0
12      Explain        12    0     0     SEARCH sub_nodes USING INTEGER PRIMARY KEY (rowid=?) (~1 row)  0
13      Noop           0     0     0                    0   Begin WHERE-loop0: sub_nodes
14      Column         0     0     7                    0   r[7]=edge.node_from
15      SeekRowid      1     20    7                    0   intkey=r[7]
16      Noop           0     0     0                    0   Begin WHERE-core
17      Integer        1     5     0                    0   r[5]=1
18      DecrJumpZero   6     21    0                    0   if (--r[6])==0 goto 21
19      Noop           0     0     0                    0   End WHERE-core
20      Noop           0     0     0                    0   End WHERE-loop0: sub_nodes
21      Return         4     0     0                    0
22      IfNot          5     43    1                    0
23      Integer        37    9     0                    0   r[9]=37; return address
24      Integer        0     10    0                    0   r[10]=0; Init EXISTS result
25      Integer        1     11    0                    0   r[11]=1; LIMIT counter
26      OpenRead       2     4     0     0              0   root=4 iDb=0; sub_nodes
27      ColumnsUsed    2     0     0     0              0
28      Explain        28    0     0     SEARCH sub_nodes USING INTEGER PRIMARY KEY (rowid=?) (~1 row)  0
29      Noop           0     0     0                    0   Begin WHERE-loop0: sub_nodes
30      Column         0     1     12                   0   r[12]=edge.node_to
31      SeekRowid      2     36    12                   0   intkey=r[12]
32      Noop           0     0     0                    0   Begin WHERE-core
33      Integer        1     10    0                    0   r[10]=1
34      DecrJumpZero   11    37    0                    0   if (--r[11])==0 goto 37
35      Noop           0     0     0                    0   End WHERE-core
36      Noop           0     0     0                    0   End WHERE-loop0: sub_nodes
37      Return         9     0     0                    0
38      IfNot          10    43    1                    0
39      Noop           0     0     0                    0   Begin WHERE-core
40      Rowid          0     3     0                    0   r[3]=rowid
41      AggStep        0     3     1     count(1)       1   accum=r[1] step(r[3])
42      Noop           0     0     0                    0   End WHERE-core
43    Next           0     7     0                    1
44    Noop           0     0     0                    0   End WHERE-loop0: edge
45    AggFinal       1     1     0     count(1)       0   accum=r[1] N=1
46    Copy           1     14    0                    0   r[14]=r[1]
47    ResultRow      14    1     0                    0   output=r[14]
48    Halt           0     0     0                    0
49    Transaction    0     0     7     0              1   usesStmtJournal=0
50    Goto           0     1     0                    0
┌────────────────┐
│ count(_rowid_) │
├────────────────┤
│ 1929           │
└────────────────┘
Run Time: real 0.200 user 0.078125 sys 0.046875
sqlite> SELECT count(edge._rowid_)
   ...>  FROM edge
   ...>  JOIN sub_nodes as s1 ON s1.node_id == node_from
   ...>  JOIN sub_nodes as s2 ON s2.node_id == node_to
   ...> ;
QUERY PLAN
|--SCAN s1 (~9216 rows)
|--SEARCH edge USING COVERING INDEX edge_from_to (node_from=?) (~2 rows)
`--SEARCH s2 USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     35    0                    0   Start at 35
1     Null           0     1     2                    0   r[1..2]=NULL
2     OpenRead       1     4     0     0              0   root=4 iDb=0; sub_nodes
3     ColumnsUsed    1     0     0     0              0
4     OpenRead       3     7     0     k(3,,,)        2   root=7 iDb=0; edge_from_to
5     ColumnsUsed    3     0     0     3              0
6     OpenRead       2     4     0     0              0   root=4 iDb=0; sub_nodes
7     ColumnsUsed    2     0     0     0              0
8     Explain        8     0     0     SCAN s1 (~9216 rows)  0
9     Noop           0     0     0                    0   Begin WHERE-loop0: sub_nodes
10    Rewind         1     30    0                    0
11      Explain        11    0     0     SEARCH edge USING COVERING INDEX edge_from_to (node_from=?) (~2 rows)  0
12      Noop           0     0     0                    0   Begin WHERE-loop1: edge
13      Rowid          1     3     0                    0   r[3]=rowid
14      CursorHint     3     0     0     EQ(r[3],c0)    0
15      Rowid          1     4     0                    0   r[4]=rowid
16      SeekGE         3     28    4     1              0   key=r[4]
17        IdxGT          3     28    4     1              0   key=r[4]
18        Explain        18    0     0     SEARCH s2 USING INTEGER PRIMARY KEY (rowid=?) (~1 row)  0
19        Noop           0     0     0                    0   Begin WHERE-loop2: sub_nodes
20        Column         3     1     5                    0   r[5]=edge.node_to
21        SeekRowid      2     26    5                    0   intkey=r[5]
22        Noop           0     0     0                    0   Begin WHERE-core
23        IdxRowid       3     6     0                    0   r[6]=rowid
24        AggStep        0     6     1     count(1)       1   accum=r[1] step(r[6])
25        Noop           0     0     0                    0   End WHERE-core
26        Noop           0     0     0                    0   End WHERE-loop2: sub_nodes
27      Next           3     17    0                    0
28      Noop           0     0     0                    0   End WHERE-loop1: edge
29    Next           1     11    0                    1
30    Noop           0     0     0                    0   End WHERE-loop0: sub_nodes
31    AggFinal       1     1     0     count(1)       0   accum=r[1] N=1
32    Copy           1     7     0                    0   r[7]=r[1]
33    ResultRow      7     1     0                    0   output=r[7]
34    Halt           0     0     0                    0
35    Transaction    0     0     7     0              1   usesStmtJournal=0
36    Goto           0     1     0                    0
┌─────────────────────┐
│ count(edge._rowid_) │
├─────────────────────┤
│ 1929                │
└─────────────────────┘
Run Time: real 0.114 user 0.015625 sys 0.000000
sqlite> SELECT count(edge._rowid_)
   ...>  FROM edge
   ...>  JOIN sub_nodes as s1 ON s1.node_id == +node_from
   ...>  JOIN sub_nodes as s2 ON s2.node_id == +node_to
   ...> ;
QUERY PLAN
|--SCAN edge (~196608 rows)
|--SEARCH s1 USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
`--SEARCH s2 USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     31    0                    0   Start at 31
1     Null           0     1     2                    0   r[1..2]=NULL
2     OpenRead       0     3     0     2              0   root=3 iDb=0; edge
3     ColumnsUsed    0     0     0     3              0
4     OpenRead       1     4     0     0              0   root=4 iDb=0; sub_nodes
5     ColumnsUsed    1     0     0     0              0
6     OpenRead       2     4     0     0              0   root=4 iDb=0; sub_nodes
7     ColumnsUsed    2     0     0     0              0
8     Explain        8     0     0     SCAN edge (~196608 rows)  0
9     Noop           0     0     0                    0   Begin WHERE-loop0: edge
10    Rewind         0     26    0                    0
11      Explain        11    0     0     SEARCH s1 USING INTEGER PRIMARY KEY (rowid=?) (~1 row)  0
12      Noop           0     0     0                    0   Begin WHERE-loop1: sub_nodes
13      Column         0     0     3                    0   r[3]=edge.node_from
14      SeekRowid      1     24    3                    0   intkey=r[3]
15      Explain        15    0     0     SEARCH s2 USING INTEGER PRIMARY KEY (rowid=?) (~1 row)  0
16      Noop           0     0     0                    0   Begin WHERE-loop2: sub_nodes
17      Column         0     1     4                    0   r[4]=edge.node_to
18      SeekRowid      2     23    4                    0   intkey=r[4]
19      Noop           0     0     0                    0   Begin WHERE-core
20      Rowid          0     5     0                    0   r[5]=rowid
21      AggStep        0     5     1     count(1)       1   accum=r[1] step(r[5])
22      Noop           0     0     0                    0   End WHERE-core
23      Noop           0     0     0                    0   End WHERE-loop2: sub_nodes
24      Noop           0     0     0                    0   End WHERE-loop1: sub_nodes
25    Next           0     11    0                    1
26    Noop           0     0     0                    0   End WHERE-loop0: edge
27    AggFinal       1     1     0     count(1)       0   accum=r[1] N=1
28    Copy           1     6     0                    0   r[6]=r[1]
29    ResultRow      6     1     0                    0   output=r[6]
30    Halt           0     0     0                    0
31    Transaction    0     0     7     0              1   usesStmtJournal=0
32    Goto           0     1     0                    0
┌─────────────────────┐
│ count(edge._rowid_) │
├─────────────────────┤
│ 1929                │
└─────────────────────┘
Run Time: real 0.138 user 0.046875 sys 0.031250
sqlite>

Should not that query be exactly equal to:

SELECT count(_rowid_)
  FROM edge
 WHERE EXISTS (SELECT * FROM sub_nodes WHERE node_id == node_from)
   AND EXISTS (SELECT * FROM sub_nodes WHERE node_id == node_to)
;

since value IN table means where exists a row in table where the primary key of table == value?

It should, of course, be pointed out that your query can use the index for the purpose of the order by only. The LIKE expression is a substring search so a full table/index scan is required (it begins with a wildcard).

The index could only be used if you changed the operation of LIKE to be case sensitive OR declared the index as case insensitive and repaired your query:

create index i0 on names(transliterate_to_ascii(name) collate nocase);

SELECT * FROM names WHERE transliterate_to_ascii(name) LIKE transliterate_to_ascii('médéric%') ORDER BY transliterate_to_ascii(name) collate nocase;

My first solution was to use a custom function, eg. transliterate_to_ascii:

SELECT * FROM names WHERE transliterate_to_ascii(name) LIKE transliterate_to_ascii('%médéric%') ORDER BY transliterate_to_ascii(name);

But obviously this doesn't scale very well as that function would be called a lot, as I can't use indexes.

Well, actually you can. You can create an index on the function:

create index i0 on names(transliterate_to_ascii(name));

SQLite3 libraries compiled with SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION=1 will be able to open and read the database. However, they will not be allowed to update it because that requires that the unknown SQL function be actually present.

Integrity check works (for this purpose) by scanning the table and then doing a B-Tree traversal of the index to find the entry for that row. If the row is not found by the time the traversal is complete, then you get an error message that the row is missing from the index (it was not found where it was supposed to be -- it may indeed be in the index, just not where it was expected to be found though this is not checked).

This is because the "collation function" is used to traverse the BTree. That is, assuming a very simple B-Tree, the "collation function" is used to determine if the sought key is "greater than" or "less than" the value stored at the current node and thus whether you "go left" or "go right" or the "current node" is the one sought. If the "collation function" indicates that the "current node" is supposed to be the one sought, then the key and the rowid are checked against the key and rowid in the original table. If these do not match, then an error message is thrown.

If the function used to do the comparison when "searching" the B-Tree does not provide consistent results with the function used to construct the B-Tree in the first place, then you will go right when you should go left and will fail to find the entry sought and an error message will be produced when you eventually run out of nodes without finding the one for which you are looking.

There is no solution to this problem other than to make the necessary collation sequence consistently available when accessing the B-Tree.

Theoretically you do not need those on Windows (with a MinGW compiler) because those functions (dynamic loading and threading) are natively handled by the NT Kernel (KERNEL32.DLL trampoline) and thus do not need to be present in the link since the Kernel Runtime is always present and a compiler designed to produce "native" Win32 code does not require runtime support for those functions.

You will note that -mthreads (enables thread exception propagation to the main thread) does require runtime library support, but in some versions of MinGW that support code is hauled in automatically during linking and depending on the version of MinGW may always be present even if not specified (since such propagation is a native Win32 function to some extent).

On Linux however, the threading and dynamic linking routines are runtime functions and not native functions (as in the compiler does not generate the syscalls directly), therefore you must haul in the runtime support libraries.

Note that you can also request assignment of a dedicated (static) IPv6 /64 subnet from Linode rather than using the "default" IPv6 subnet assigned to the cluster (and thus other hosts). While this /64 is still assigned from the overall /32 delegation to the same Linode ASN from ARIN, it is not shared by other Linode customers.

This means that things which track access based on the network part of the IPv6 address (the /64) will see you only and not other Linode customers on this subnet -- this includes almost everything (although it is in the same ASN, only very limited DNSBL list entire ASNs, most work at the /64 (network) prefix level).

I did this way back because RIPE was returning unexpected access errors and it turns out they (and a lot of others) track by /64 and access was being impacted by other Linode customers on the shared network segment.

The parent of a foreign key relationship must be unique. That means that you must declare the parent foreign-key as UNIQUE (or PRIMARY KEY) in the table definition or you must declare a UNIQUE index on the parent key (which is the same thing). Also, if you are cascading parent to child, you probably want an index on the child key as well.

CREATE TABLE IF NOT EXISTS User
(
   ID        INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   UserName  TEXT NOT NULL,
   MashineID TEXT NOT NULL,
   UNIQUE (UserName, MashineID)
);

CREATE TABLE IF NOT EXISTS ProgramList
(
   ProgNumber INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   Program    TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS Programs
(
   ProgID     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   UserID     INTEGER,
   MashineID  TEXT,
   ProgNumber INTEGER, -- I assume this is what you meant
   FilePath   TEXT,
   Installed  BOOLEAN,
   FOREIGN KEY (UserID) REFERENCES User(ID) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY(Program) REFERENCES ProgramList(ProgNumber) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX Programs_UserID on Programs(UserID);
CREATE INDEX Programs_ProgNumber on Programs(ProgNumber);

Why are the pseudokeys NOT NULL AUTOINCREMENT? Is there a reason for this or just because it sounds cool?

There is already a compile-time option to fix quotes. However, it only (obviously) has the effect of requiring that double-quotes are identifier quotes only. It will not help necessarily help people who insist on using quotes improperly because there is no way to know that someone has used the wrong quote type when both are acceptable (but have different meanings) -- it only prevents (by spitting an error message) if someone uses identifier quotes when the identifier cannot be resolved.

Programs(UserName) is text but User(ID) is integer.

ProgramList(Program) is not unique.

Your schema is logically inconsistent.

Yes, though only adding the collate rtrim definition to the column definition in the table will make indexes including the column "work properly" without modifying all the table data to remove the trailing space(s) ...

I though trailing spaces may be the issue.

You can fix that by any one of
- adding the trailing space (as in WHEN 'ANALYST ' then)
- adding the builtin collation rtrim to the comparison (as in WHEN 'ANALYST' collate rtrim THEN)
- adding collate rtrim to the column definition
- updating the table to remove the trailing spaces

The built-in rtrim collation is specifically designed to ignore trailing spaces during text comparisons.

Create a unique index on the combination of columns in the join (snapshot_id, object_id) in that order so that the processing can find all the rows which have snapshot == 5 and use the (3, object_id) to find the row to update. Without that index one or the other (or both) devolve into a table scans.

Fixed on trunk https://www.sqlite.org/src/info/9be208a6d70582c6

The job of the query planner is to choose the appropriate index.

For example, if you have a table of 1 billion rows, a where condition which selects 5 rows, and a choice of whether to use (a) an index for the WHERE clause then sort the result or (b) scan the index in the result order and only output matching rows, then clearly one should choose the former (a) because is is more efficient to quickly locate the 5 rows and then sort them than to scan all 1 billion rows in the right order then only output 5 of them.

However, if the where clause selected all rows except for 5, then clearly the method chosen should be the latter as it is faster to output the rows in order after scanning them all, than it is to sort them.

However, there is a point at which it makes no difference which method is chosen.

So the question of how to do the unnecessary is of zero utility.

This is the same thing just spoken differently.

v1 has no affinity. the constant 10 has no affinity.

WHERE v1 LIKE 10 and v1 = 10

results propagates the integer constant 10 so the condition becomes:

WHERE 10 LIKE 10 AND v1 = 10

which is true for the row where v1 has the value 10 (when the real number 10.0 is compared to the integer number 10, the result is TRUE). Similary when the integer 10 is strigified it is like the integer 10 when stringified.

In the second case

WHERE v1 LIKE 10

v1 is a REAL number. When stringified the result is the string '10.0'. When the integer constant 10 is stringified, the result is '10'. '10.0' LIKE '10' is False. Hence the row does not satisfy the condition.

sqlite> select 10. like 10;
┌─────────────┐
│ 10. like 10 │
├─────────────┤
│ 0           │
└─────────────┘
sqlite> select cast(cast(10 as real) as text), cast(10 as text);
┌────────────────────────────────┬──────────────────┐
│ cast(cast(10 as real) as text) │ cast(10 as text) │
├────────────────────────────────┼──────────────────┤
│ 10.0                           │ 10               │
└────────────────────────────────┴──────────────────┘
sqlite> select '10.0' like '10';
┌──────────────────┐
│ '10.0' like '10' │
├──────────────────┤
│ 0                │
└──────────────────┘
sqlite>