SQLite Forum

My bet is that their dates are like 1/2/2022, and not like 01/02/2022, but as has been pointed out, they aren't showing us their data.

Not much there is valid SQL.

The format for a CASE statement is shown in the Expression page

Single quotes make string literals. You need double quotes to quote an identifier like a column name.

String concatenation is 2 pipes, not 1

Core Functions

There is no right() function, but you can use a negative second value to substr()

For the month lookup you'll use substr() and instr(), something like substr("accounting period", 1, instr("accounting period", '/') - 1)

SQLite can only ever have 1 writer at a time. In order to have concurrent readers while that 1 writer is active you have to use WAL mode

Did I misunderstand the query ?

Yes. It being a sub-query is part of whatever the bug is.

Can add that I'm seeing what they're seeing with the 3.37.2 CLI

I believe your understanding is correct.

My limited understanding of Postgres is that it keeps multiple copies of each record. Each copy has metadata that says I am valid from transaction A through transaction B. It does that because older transactions still need to see the table as it was when they started even if a newer transaction comes along and makes changes. So Postgres autovacuum comes along and says "ok, you were valid from A through D, and the oldest running transaction is F, so no one needs you any more, you can be removed from the file." Or "You were valid from D through G, but the oldest running transaction is F, so F might still need you and we have to leave you in the file."

The way SQLite handles that is that in rollback journal mode you can't make changes until all other transactions complete. In WAL mode you can't checkpoint a page in the WAL back to the main file until all transactions older than that change are complete. So in WAL mode you'll have your multiple copies of a page, but they'll be in the WAL and not the main file.

SQLite's autovacuum is pretty much what you said. It's there to reclaim unused pages and keep the file size smaller by shuffling pages from the end to free pages earlier in the file.

I can't answer all of of your questions, but in this case the blob size vs the covering index is important. In the main B tree each record has the rowid and the blob for every value in the tree. With a 1K blob then you're only gonna get 3 records per 4k page (or use overflow pages) for the main storage.

Conversely the index on id only stores id, it doesn't store the blob in the index. Since it's only storing one integer for each record then you're gonna store thousands per page.

Since your query doesn't use any of the other fields, it doesn't have to go to the main table to do the query, it can get everything it needs from the nice compact index, which in this specific case means it's fetching a thousand times fewer pages from the database to complete it. If your query involved any of the other fields in the table, then the extra index on ID is going to be slower and not of any use.

SELECT ..., SUM(o.quantity)*o.price

You've got an aggregate function in there, but no GROUP BY. So it's using all the rows as your group, and treating the rest of the columns are bare columns, which it's taking from one of the rows "at random" (not actually random, but that's another discussion)

You need to either group by something for which you want info on the groups... or get rid of the aggregate functions.

See this optimization

A LEFT JOIN can sometimes be converted into an ordinary JOIN if there are terms in the WHERE clause that guarantee that the two joins will give identical results. In particular, if any column in the right-hand table of the LEFT JOIN must be non-NULL in order for the WHERE clause to be true, then the LEFT JOIN is demoted to an ordinary JOIN.

In your case the ON condition only references the left table, so there's no point in it being an outer join, and thus it treats it as an inner join.

If I understand you correctly, you're saying you tried to commit your own code to the master SQLite repository?

SQLite is open source, but not open-contribute. You can suggest changes here in the forums for example, but only the select few actual members of the team can commit code.

If it's "just" spelling issues (in the code and not the docs) then don't fret if they say no. I remember someone submitted a thing to remove all the trailing white space for example, and the decision was that was going to explode and cause noise in deltas with no actual benefit to the binaries. So someone looking for what changed from version A to B would see 1,000 lines of code changed... but none of those code changes would actually effect how anything worked.

Most DBMS's implement immediate Foreign-Keys by default, unlike SQLite.

With deferred FKs, the only mode in SQLite...

No, immediate is the default and you have to specify "deferrable initially deferred" to get deferred FKs.

Deferred Foreign Key Constraints

(Or I'm just misunderstanding what you're saying)

So we're saying in the original version, that since v3 is calculated as NULL, that the "v3 NOT IN..." should therefore evaluate to NULL, and thus the record should not pass the WHERE clause. But somehow it is.

My hunch is that you'll find that exact case with group_concat() a lot in the wild because we can't do ordered aggregates.

Adding in the ability to do

group_concat(word, ',' order by word)

would allow people to fix it, but then they'd be fixing it because it broke in the meantime.

I know SQLite has logic where it can create Automatic Indexes to help with queries. Perhaps if that was combined with AS MATERIALIZED hints to let it know it's ok to consider making an index on the materialization of the with statement?

Or is that already the case?

Remember that you can make Indexes on expressions, so if the way you're pulling out stuff from the blob is something that can be expressed in SQL with deterministic functions, then you can index on it.

Compared to PRAGMA integrity_check, does PRAGMA quick_check skip checking the validity of the sort order per the collation? (Is skipping this step one of the things that makes it "quick"?)

Yes. From the docs https://www.sqlite.org/pragma.html#pragma_quick_check

The pragma is like integrity_check except that it does not verify UNIQUE constraints and does not verify that index content matches table content. By skipping UNIQUE and index consistency checks, quick_check is able to run faster. PRAGMA quick_check runs in O(N) time whereas PRAGMA integrity_check requires O(NlogN) time where N is the total number of rows in the database. Otherwise the two pragmas are the same.

I tested it by inserting into a text field all the capitalization variants of 'ABC', then using pragma writable_schema to change it to text collate nocase. After closing and re-opening the database quick_check returned ok, but integrity_check found errors.

I think the main reason changes() reports is for triggers. That way "on update" triggers will still fire even if all the old and new fields are the same for the record.

Granted it's not the most thorough of tests, but in Windows, when I run an update query that doesn't end up changing any values, then the Modified and Accessed times of the file are not updated, despite changes() saying something was changed. When I do an update that does change values, then the Modified and Accessed times on the file do get updated. So I'd say yes, there is some optimization there. Whether it depends on the VFS... I haven't a clue.

What does pragma integrity_check; return?

So I just need to do separate execs for BEGIN and END before and after, is it the right way to do it?

That's my understanding anyway. Give it a whirl and let us know if that does indeed solve your issue.

There are two functions below doing the same thing. On my machine Test1 takes microseconds to complete, Test2 ~ 17-20 seconds. Why is Test2 so painfully slow? I expected it to be faster than Test1.

1 transaction in the first vs 1,000 in the second?

I'll give you just one scenario to help you see that views and triggers are not always enough.

How are views and triggers not enough for this?

SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table people (pid, name, address);

sqlite> create table phones (pid, phone);

sqlite> create view add_person as select pid, name, address, phone from people inner join phones using (pid);

sqlite> create trigger add_person_trg instead of insert on add_person begin insert into people values (new.pid, new.name, new.address); insert into phones values (new.pid, new.phone); end;

sqlite> insert into add_person values (1, 'First Last', 'Home', '555-1212');

sqlite> select * from people;
pid|name|address
1|First Last|Home

sqlite> select * from phones;
pid|phone
1|555-1212

sqlite>

And which column format does SQLite not support?

Don't know about MYSQL, but in PostgreSQL for example you could have an array field to hold all the topics

create table quotes ( quote_id integer not null primary key, quote text not null, topic_ids integer[] ); insert into quotes values (1, 'A successful man is a fall in love man.', array[1, 2, 3]);

But the only advantage of that would be 1 less table and a lower overall record count, which doesn't really help anything. You couldn't make them official foreign keys, updates would be harder, indexing going from topics to quotes would be harder, etc.

So, as people have recommended, it would be a lot better for the normal

create table quotes_to_topics ( quote_id int not null references quotes, topic_id int not null references topics, primary key (quote_id, topic_id) ) without rowid; create index topics_to_quotes_idx on quotes_to_topics (topic_id, quote_id);

param_id doesn't seem to be showing up anywhere in your update query. Is that right, or should there also be a bit saying the param_id's are equal? If you've got more than one param_id per object_id then you're potentially messing things up I believe.

If the real schema is as simple as the example you could use SQLite's bare columns in aggregates to get the highest snapshot for each parameter.

Something like... select max(snapshot_id) as snapshot_id, param_id, param_value from SnapshotParameters group by param_id order by param_id;

I believe order by is supposed to look at the column aliases first, yes. I can't point you to anything specific that says it though.

Don't forget you can order by something that's not in the result columns. So you can explicitly say the column you want to order by.

sqlite> select printf('%8.2f', n) as n from t order by t.n desc;
┌──────────┐
│    n     │
├──────────┤
│   500.00 │
│  -574.41 │
└──────────┘

sqlite>

For the Python library, adding in "isolation_level = None" in the connection call gets rid of all the automatic transaction handling and leaves it in autocommit mode/full manual transaction control.

Not sure that it would help at all for SQLite, but one example from remote DBMS inserts is using multiple records in your values statement.

So instead of...

INSERT INTO user VALUES (NULL,?,?,?);

...binding 3 values for 1 record per INSERT statement

You might try doing 10 at a time:

INSERT INTO user VALUES (NULL,?,?,?), (NULL,?,?,?), (NULL,?,?,?), (NULL,?,?,?)... x10

...binding 30 values for 10 records per INSERT statement

Rowid tables use table b-trees. "without rowid" tables and all indexes use index b-trees.

From the Database File Format page

A b-tree page is either a table b-tree page or an index b-tree page. All pages within each complete b-tree are of the same type: either table or index. There is one table b-trees in the database file for each rowid table in the database schema, including system tables such as sqlite_schema. There is one index b-tree in the database file for each index in the schema, including implied indexes created by uniqueness constraints. There are no b-trees associated with virtual tables. Specific virtual table implementations might make use of shadow tables for storage, but those shadow tables will have separate entries in the database schema. WITHOUT ROWID tables use index b-trees rather than a table b-trees, so there is one index b-tree in the database file for each WITHOUT ROWID table. The b-tree corresponding to the sqlite_schema table is always a table b-tree and always has a root page of 1. The sqlite_schema table contains the root page number for every other table and index in the database file.

In the second schema the hash value of base32 is unique instead.

CREATE INDEX hash_base32 ON hash(base32)

There "hash" is the table name and "base32" is the field name.

It's not "hash" as a function name and "base32" as an argument.

CREATE INDEX

Temporary Files Used By SQLite Section 5 has the info I think you're looking for.

Temporary File Storage Locations

The directory or folder in which temporary files are created is determined by the OS-specific VFS.

On unix-like systems, directories are searched in the following order:

    1. The directory set by PRAGMA temp_store_directory or by the sqlite3_temp_directory global variable
    2. The SQLITE_TMPDIR environment variable
    3. The TMPDIR environment variable
    4. /var/tmp
    5. /usr/tmp
    6. /tmp
    7. The current working directory (".") 

The first of the above that is found to exist and have the write and execute bits set is used. The final "." fallback is important for some applications that use SQLite inside of chroot jails that do not have the standard temporary file locations available.

On Windows systems, folders are searched in the following order:

    1. The folder set by PRAGMA temp_store_directory or by the sqlite3_temp_directory global variable
    2. The folder returned by the GetTempPath() system interface. 

SQLite itself does not pay any attention to environment variables in this case, though presumably the GetTempPath() system call does. The search algorithm is different for CYGWIN builds. Check the source code for details.

How would an index be helpful in processing a "x & ? = 0" WHERE clause term? What algorithm do you propose?

Wouldn't you then only need to execute the test once per each unique value of x?

Go through the index in order, when x changes test the new x. Then you can either say "include every row until x changes" or "ignore every row until x changes"

On a simple test like this then it might not save much, but the more expensive the test, and the fewer distinct values of x then the more useful that would be.

(That or I'm missing something obvious)

In 2.4 of the SELECT documentation there's this:

4 . Removal of duplicate rows (DISTINCT processing).

One of the ALL or DISTINCT keywords may follow the SELECT keyword in a simple SELECT statement. If the simple SELECT is a SELECT ALL, then the entire set of result rows are returned by the SELECT. If neither ALL or DISTINCT are present, then the behavior is as if ALL were specified. If the simple SELECT is a SELECT DISTINCT, then duplicate rows are removed from the set of result rows before it is returned. For the purposes of detecting duplicate rows, two NULL values are considered to be equal. The usual rules apply for selecting a collation sequence to compare text values.

So it looks like it uses =/IS to compare them. And since 1 = 1.0 they're considered the same. Which one you get back is probably which one came first as it was traversing the values.

PRAGMA journal_mode = WAL;

The script gets called in a loop from just two different machines on the network.

Just to make sure I'm understanding this right, each of those two machines is accessing its own separate database. Right? Because WAL mode doesn't work across different machines.

Or is that perhaps part of your problem :)

Maybe "one index per table instance in the query" is more accurate?

What we're saying is that if you have a table t with fields a and b, with an index on a and an index on b, then in...

select * from t where a = 5 or b = 7;

Then SQLite with use only one of those two indexes, or do a full table scan. It will not use the A index for "where a = 5", use the B index for "or b = 7" and then de-duplicate the results.

If you have

select * from t as t1 join t as t2 on t1.id1 = t2.id2 where t1.a = 12 or t2.b = 42;

then again for t1 it will use one index or the full table, and for t2 it will use one index or the full table. It can't use an index on t2.id2 to speed up the ON clause and use an index on t2.b to speed up the WHERE and do some cross comparing. It can only pick one.

...or something like that.

but is there a way to rewrite the second without the the duplication of IN groups?

If you mean just without having to type/bind the values twice, then how about

with ProjIDs as (values ('PR0000014888'),
                        ('PR0000015204'),
                        ('PR0000016639'),
                        ('PR0000016678'),
                        ('PR0000016724'))
select ...
where b.ProjID in ProjIDs
...
where a.ProjID in ProjIDs
...