SQLite Forum

Timeline
Login

50 most recent forum posts by user Maxulite

2021-09-29
16:08 Edit: VACUUM INTO for vfs conversion (artifact: b810849b60 user: Maxulite)

I recently noticed that VACUUM INTO command has an "implicit" feature not mentioned in the docs. It can be used for conversion between VFSes. Since it support URI filenames the target might be different to the VFS the database is opened with. I, for example, for a long time had dedicated menu items in my admin for converting the current db to a specific VFS format and back. Now (actually since the day VACUUM INTO was introduced) one can also do this with an SQL construct

VACUUM INTO 'file:///c:/temp/mydb?vfs=myvfs'.

I think this feature is deserved to be added to the VACUUM doc page, maybe as a simple several words sentence.

Something to wish for is support for passing custom uri parameters to VFSes. With this it would be possible to VACUUM INTO with required parameters like for example passwords for encrypted databases and so on.

16:07 Post: VACUUM INTO for vfs conversion (artifact: d821cf67e5 user: Maxulite)

I recently noticed that VACUUM INTO command has an "implicit" feature not mentioned in the docs. It can be used for conversion between VFSes. Since it support URI filenames the target might be different to the VFS the database is opened with. I, for example, for a long time had dedicated menu items in my admin for converting the current db to a specific VFS format and back. Now (actually since the day VACUUM INTO was introduced) one can also do this with an SQL construct

VACUUM INTO 'file:///c:/temp/mydb?vfs=myvfs'.

I think this feature is deserved to be added to the VACUUM doc page, maybe as a simple several words sentence.

Something to wish for is support for passing custom uri parameters to VFSes. With this it would be possible to VACUUM INTO with required parameters like for example passwords for encrypted databases and so on.

2021-09-24
11:43 Edit reply: vfs write amount size without WAL (artifact: ca3358f9f2 user: Maxulite)

A good source of wisdom in this respect is The Checksum VFS Shim. Looking at the xRead and xWrite handlers one can notice that the check-summing logic will go completely out of sync if the requests are not equal to the page size. And for this to happen the implementation ignores (bypasses to the default implementation) anything but main and wal file

  if( (flags & (SQLITE_OPEN_MAIN_DB|SQLITE_OPEN_WAL))==0 ){
    return pSubVfs->xOpen(pSubVfs, zName, pFile, flags, pOutFlags);
  }
11:42 Reply: vfs write amount size without WAL (artifact: d25e37bacb user: Maxulite)

A good source of wisdom in this respect The Checksum VFS Shim. Looking at the xRead and xWrite handlers one can notice that the check-summing logic will go completely out of sync if the requests are not equal to the page size. And for this to happen the implementation ignores (bypasses to the default implementation) anything but main and wal file

  if( (flags & (SQLITE_OPEN_MAIN_DB|SQLITE_OPEN_WAL))==0 ){
    return pSubVfs->xOpen(pSubVfs, zName, pFile, flags, pOutFlags);
  }
2021-09-10
10:33 Reply: .expert via api (artifact: 4693110e39 user: Maxulite)

What I tried today:

The page about this extension contains all the necessary information if you want to compile and use this feature. Some minor points:

  • sqlite3expert.c compiles into linkable code (no need for expert.c) and can be statically linked with an executable. If you want to use a dynamic library, export necessary functions (mentioned in the docs) and be ready for a large executable (containing all the SQLite inside). So it's not an extension in the sense how loadable extensions are used. I personally only tried the statically linked approach.
  • Every successful call to sqlite3_expert_sql implicitly creates an auto-increment statement id that you should pass to sqlite3_expert_report in order to get the report (starting with 0)
  • The extension provides other information as well. But in order to get index suggestion, call sqlite3_expert_report with EXPERT_REPORT_INDEXES constant. You will get a multi-line suggestion with one or more indexes or Null if there's no need for new indexes
  • Don't forget to free error messages (if any) with sqlite3_free

I was a little late to know of the .expert feature (see my naive suggestion at the Simon's post). But thanks to the discussion, a new tool is added to the palette.

2021-09-09
10:42 Reply: Automatic indexing (idle question) (artifact: 952dbb45e8 user: Maxulite)

As a probable variation of creating indexes with a "based on ..." principle in mind, I wonder how hard it is (for majority of cases) to implement something like

SUGGEST INDEX { sql query}
command. So given a query, it suggest necessary indices if they don't exist already. Basically the code should parse the query and find all index-related pieces like ORDER BY, GROUP BY, WHERE in the query, identify fields and field sets and suggest new indexes if necessary.

I suspect that although SQLite itself is best fit for the task, a third party tool may also be a good candidate using the SQLite as a help. For example, since EXPLAIN QUERY PLAN output usually contains Scan {table} lines, it can be used for feedback loop in this tool, so the tool may create a memory-based db with the same schema (and no data) and iterate with different create index command analyzing whether scan {table} lines are gone or not.

2021-08-15
09:36 Reply: Unable to use FTS MATCH with OR condition (artifact: efbcf6b978 user: Maxulite)

The post is available at mail-archive. Nabble has probably got rid of most of the servers so the current non-availability of the posts is very likely permanent.

2021-07-09
11:49 Reply: Error message from table-valued function xConnect is ignored (artifact: 52288df1f1 user: Maxulite)

It looks like it's the same issue as I reported recently (https://sqlite.org/forum/forumpost/db63efd0d1). At least now when switching from 3.36.00 to the check-in version I see my message instead of "no such table ...". Thanks!

09:22 Reply: Custom error messages for required table-valued function parameter (artifact: 7cc2944edb user: Maxulite)
AFAICT SQLite builds an array of all possible constraints based on the query and will then set the "usable" field to reflect those available for the specific query plan.

Sounds reasonable.I just read the Dan's reply and one phrase in it

If the required parameter is not present in aConstraint[] at all, return SQLITE_ERROR and an error message.

indeed assumes that the array is fixed and only "usable" fields are changing. If it's the case then one really can compare the array set (not looking at the usable field) with the required set and return the error immediately if they don't match. But the docs also contains the phrase

While compiling a single SQL query, the SQLite core might call xBestIndex multiple times with different settings in sqlite3_index_info
and this implies (at least for me) that the column set may change during a single xBestIndex session (multiply calls), so one can not rely on this assumption
07:42 Edit reply: Custom error messages for required table-valued function parameter (artifact: a00514f753 user: Maxulite)

I always thought that the xBestIndex invocations are made in no particular order and sqlite even free to repeat them if it wishes so even if some constraint case was not sufficient for me, I still can wait for a better one following. That's why the answer in my tables always either SQLITE_OK or SQLITE_CONSTRAINT (after version 3.26.00) and no SQLITE_ERROR since the latter will interrupt the variants enumerations. But you explanations implicitly assumes some possibly documented ordering logic allowing one to detect in a xBestIndex invocation that there won't be better one for particular column set and one is safe to fire SQLITE_ERROR and break the loop. Can you confirm this?

07:41 Reply: Custom error messages for required table-valued function parameter (artifact: dde19cf395 user: Maxulite)

I always thought that the xBestIndex invocations are made in no particular order and sqlite even free to repeat them if it wishes so even if some constraint case was not sufficient for me, I still can wait for a better one following. That's why the answer in my tables always either SQLITE_OK or SQLITE_CONSTRAINT (after version 3.26.00) and no SQLITE_ERROR since the latter will interrupt the variants enumerations. But you explanations implicitly assumes some possibly documented ordering logic allowing one to detect in a xBestIndex invocation that there won't be better one for particular column set and one is safe to fire SQLITE_ERROR and breaks the loop. Can you confirm this?

2021-07-08
12:35 Reply: Custom error messages for required table-valued function parameter (artifact: 07157c4470 user: Maxulite)

I don't know whether the developers took your suggestion to the future features, but currently you may switch to a different method in order to see your messages.

the SQLITE_CONSTRAINT result as a possible outcome of xBestIndex appeared in Sqlite 3.26.00. But before that one only could provide a very expensive cost for any non-desirable constrained suggestion and report SQLITE_ERROR in the xFilter if no required constraints were provided. Actually I welcomed SQLITE_CONSTRAINT, but I already had virtual tables working only when the required constrained were provided so my current code checks for the version (3.26.00) and works either old or new way. And if you switch to this older method, you should see your error messages set with zErrMsg in the xFilter with SQLITE_ERROR result.

2021-07-02
10:08 Reply: fts5 rowid peculiarity in UPDATE FROM (artifact: b85f27ab7a user: Maxulite)

Great, the citizen is now less special again )

I checked with my data, everything works as expected, including UPDATE FROM and WITHOUT ROWID tables.

There's one thing I noticed though. Sometimes one can introduce his or here own rowid (plenty of ways to do this, cte, forced rowid in "without rowid" tables, etc). The specifics here is that to be ambiguous in two senses, the rowids should be by the same kind. So joining two tables with "user rowid" gives "ambiguous column name: rowid" error and joining two tables with "sqlite rowid" gives "no such column: rowid", but mixing them (one user, one sqlite) gives no error. I don't know whether this mixing was possible before this fix, but if not then this might be a reason for hiding rowids in joins.

The example of this when sqlite is ok with the mixed ambiguity (testtable is a general rowid table and according to my observation the rowid in ON clause is interpreted as coming from ctesource)

with ctesource(rowid, id) as
  (
     select 12324, 1
  )
select * from testtable, ctesource on rowid=ctesource.id

I'm not sure this is a big deal, just to take an additional look here.

2021-06-30
11:57 Edit reply: fts5 rowid peculiarity in UPDATE FROM (artifact: 02564bc926 user: Maxulite)

Thanks, I understand now. The situation is unusual in the sense that rowid is a special citizen for obvious reasons and anyone familiar with Sqlite is used to this. In other words it's always there (with exception of WITHOUT ROWID tables), but you don't deal with it unless you want to. But when using fts5, one is enforced to use it instead of a general (at least from the naming point of view) column docid. So the special life of this citizen comes to light unconditionally.

I tried to search in the timeline and it looks like not using an alias like docid was a very early design decision, the comments some time contained docid in descriptions and then were renamed overnight. Just for historical curiosity I wonder what was the objective of not using docid alias from the start?

11:56 Reply: fts5 rowid peculiarity in UPDATE FROM (artifact: e1962fd1d4 user: Maxulite)

Thanks, I understand now. The situation is unusual in the sense that rowid is a special citizen for obvious reasons and anyone familiar with Sqlite is used to this. In other words it's always there (with exception of WITHOUT ROWID tables), but you don't deal with it unless you want to. But when using fts5, one is enforced to use it instead of a general (at least from the naming point of view) column docid. So the special life of this citizen comes to light unconditionally.

I tried to search in the timeline and it looks like not using an alias like docid was a very early design decision, the comment some time contained docid in description and then were renamed overnight. Just for historical curiosity I wonder what was the objective of not using docid alias from the start?

2021-06-29
10:41 Post: fts5 rowid peculiarity in UPDATE FROM (artifact: ab5b077c32 user: Maxulite)

Sqlite version 3.36.0

For fts4 the following query works

update FtsIndex4 Set text="foo bar" from (select 123432545 as modified ) where docid=modified

but for fts5 the equivalent one (docid is replaced by rowid)

update FtsIndex5 Set text="foo bar" from (select 123432545 as modified ) where rowid=modified

gives an error (no such column rowid), but with the table name prefix "...where FtsIndex5.rowid=modified..." it works. Not a big deal, but might confuse working with drop-in replacement in mind when migrating from fts3/fts4 to fts5

Thanks

2021-06-10
12:06 Edit reply: Feature request: Stored Procedures (artifact: 84f6bb0ead user: Maxulite)

As a long time user of sqlite as a library and a data-mining tool, I'm always for parameterization and tried to add it in one or other way. Recently I suggested using views with parameters at the forum (https://sqlite.org/forum/forumpost/8f0351af38), but I doubt this will be implemented. My post also suggested the solution in a form of virtual table and I used it several times since. As I see, you're familiar with user-defined functions so probably with virtual tables too so you would implement something like this.

As a short description, you have a list of named queries in a table and all of them use named or unnamed parameters in the bodies (?1 , :myparameter). The name and the body are stored in the separated fields. The names are registered as eponymous-only modules and when sqlite meets something like

Select * from my_parameterized_query(123, 'foo bar')

the module responses with a schema containing the input field according to the parameters found in the bodies and the names of the output columns, makes the best cursor negotiating low cost when sqlite provides the required parameters.

Not surprisingly, this method also allows using parameterized quering "calls" inside other parameterized queries, so they sometimes can be used as "a library" of inter-calling table-valued functions.

Since new functions are created very easily, they sometimes helps in unexpected ways. I had a cte when I needed a join with the recursive part in a sub-query, which Sqlite currently forbids. But I made a parameterized query for the top-level join accepting the recursive "parameters" and making anything required inside the "body" (for example, group by).

12:06 Reply: Feature request: Stored Procedures (artifact: ced561df87 user: Maxulite)

As a long time user of sqlite as a library and a data-mining tool, I'm always for parameterization and implemented tried to add it in one or other way. Recently I suggested using views with parameters at the forum (https://sqlite.org/forum/forumpost/8f0351af38), but I doubt this will be implemented. My post also suggested the solution in a form of virtual table and I used it several times since. As I see, you're familiar with user-defined functions so probably with virtual tables too so you would implement something like this.

As a short description, you have a list of named queries in a table and all of them use named or unnamed parameters in the bodies (?1 , :myparameter). The name and the body are stored in the separated fields. The names are registered as eponymous-only modules and when sqlite meets something like

Select * from my_parameterized_query(123, 'foo bar')

the module responses with a schema containing the input field according to the parameters found in the bodies and the names of the output columns, makes the best cursor negotiating low cost when sqlite provides the required parameters.

Not surprisingly, this method also allows using parameterized quering "calls" inside other parameterized queries, so they sometimes can be used as "a library" of inter-calling table-valued functions.

Since new functions are created very easily, they sometimes helps in unexpected ways. I had a cte when I needed a join with the recursive part in a sub-query, which Sqlite currently forbids. But I made a parameterized query for the top-level join accepting the recursive "parameters" and making anything required inside the "body" (for example, group by).

2021-05-26
12:43 Reply: memory vs mmap (artifact: 657f352d4d user: Maxulite)

Great to hear you found the bottleneck. I admit that my test was not a very realistic in that it had a very high IO/CPU ratio. Just tried the one involving more or less the linear reading of the 100M file already in the system cache, the time of the disk db was almost as good as the equivalent one in the memory.

An interesting side point. I found that :memory: databases indeed have some special treatment inside sqlite. My tweaking for VFS statistics never worked for memory databases. I suspected that I should just find a correct vfs. The one that jumped in the sources was "memdb", but actually it's another vfs-semantic memory database interface probably for serialization (available for finding only when sqlite is compiled with SQLITE_ENABLE_DESERIALIZE flag) and it looked like the bases opened with it works slower than native ":memory:" bases and even slower than memory-mapped disk bases (when the system already cached the data). I suppose this is an illustration of the phrase from the docs "Skipping the copy step is what makes memory mapped I/O faster". Probably ":memory:" databases are also able skip the copy step.

09:14 Reply: Error message from xConnect of eponymous table (artifact: ce0c022872 user: Maxulite)

Some additional info to this issue.

I checked the source for pragma_table_info logic and apparrently pragmaVtabFilter transforms the table-valued originated data of pragma_table_info(table, schema) into PRAGMA shema.table_info(table) and just returns the error from the PRAGMA query as it is("unknown database..."). So it is very likely that no error reporting on xConnect level is involved here.

As for my instances of the issue, I made a test with one of my virtual tables that is eponymous (enabled) virtual table (xCreate = xConnect). A fresh (create virtual) table was created. Then I closed the file and made a temporal unconditional error in xConnect handler source with my error message returned. The results are

  • For select * from an_unique_virtual_table_name(... I got my error (expected)
  • For select * from module_name(... (eponymous mode) I got "no such table {module_name}" error, but in the debugger I saw that my message was indeed passed to sqlite.
2021-05-21
14:12 Post: Error message from xConnect of eponymous table (artifact: db63efd0d1 user: Maxulite)

I may be wrong, but it looks like pzErr message returned from xConnect call to an eponymous table is not propagated to the original caller, so the error message is "no such table: ..." even if my returned pzErr is not empty or null.

I'm not sure about my findings because pragma_table_info when called with invalid database (second parameter) reports another error ('unknown database') so this eponymous table is able somehow to report different errors.

I must admit that my wish to change it is a kind of exotic since from the very nature of eponymous tables (no parameters when created) they rarely should produce errors. But in my case I use the data from the database itself. Delaying errors for later (for example, cursor handling moment) is not possible because the schema depends on the data found.

09:37 Reply: memory vs mmap (artifact: 1769f8e8f2 user: Maxulite)

Sorry, I'm a little late to the post, I read it, but by thoughts kept accumulating until final actions. My assumptions that prevented me from just skipping this topic was

  • Working and knowning file-mapping for ages, I have an assumption that the mechanism used heavily by modern OSes to save memory space, more or less should work without any penalty. So if a file sector is loaded into memory after the first hardware exception, there should be almost no virtual penalty for the consequence acceses
  • Observing the sqlite team positive perfectionism, I don't expect any sqlite logical penalty unless it is absolutely necessary.

So I did a test (it's Windows, but I assume some minor hardware/system variations are possible with other desktop/server OSes)

create table testtable (id integer primary key autoincrement, value)
insert into testtable (value) values(zeroblob(10000))

The last one executed 10000 times, so we have a 100 MB database

And the test to observe the timing and reading is the cross join

select count(*) from testtable, testtable

I chose it because it doesn't consume dynamic memory, heavily resides on reading (since the 100 MB db doesn't fit in sqlite cache), consumes very little in CPU space and because of this is very prone to any delays at VFS reading level.

My initial tests showed more or less the same difference you mentioned. The memory query took 9 seconds, memory-mapped query took 21 seconds. For both times the cpu and total was almost the same, so more or less no waiting at the I/O level. And by the way, the full-scale file query took 2 minutes and more. Historically I have a "tweak" layer in my admin gui showing the total VFS read (xRead or xFetch) and the average speed of reading. On my machine (Windows 7, not so modern) the mem-mapped version read (fetched) 205 GB and the speed was 9GB/Sec

So something was not right. I looked at what requests came to my tweaked fetch routine, they were mostly equal to page size (4096). So naturally the next test was with the same database with the page increased to 65536. And this time the test performed much better. 10 Seconds, 1 TB total read with 117 GB/Sec fetch speed. So despite the fact that sqlite had to read more pages due to different structure mapping to bigger pages, the new x10 speed fully compensated this.

So, my version is that for default page size, the penalty is due to granularity of memory mapping view requests related to the page size. And you can probably easily overcome this with a bigger page size. I don't know though whether the improvements are possible for smaller page sizes without sacrificing the clarity of the sqlite VFS/IO level.

2021-05-17
14:30 Reply: Probable small leak with a specfic order of actions (artifact: 4e05a1db99 user: Maxulite)

I see no leaks with this check-in. Thanks

12:36 Edit reply: Nothing for several days from the forum (artifact: efd518ce98 user: Maxulite)

let me share my version

The page explains different details including that in this case the said ip is probably not responsible. So either the provider will naturally crosses the threshold back or will have to take some measures if the clients (mostly innocent) encourage it to do so.

12:00 Post: Probable small leak with a specfic order of actions (artifact: 726219164b user: Maxulite)

I have a minor memory leak with a specific order of actions, though I might miss something here. (Checked with version 3.35.5)

A virtual table implementation registers a user-defined function (doesn't matter before or after sqlite3_create_module_v2) with a sqlite3_create_function call. On xDestroy callback (when the db is being closed) it unregisters the function by calling sqlite3_create_function with xFunc parameter having NULL value. In this case my program detects a small leak (21-28 bytes), when there's no matching free for a malloc call. The program is in Delphi with sqlite static linking and malloc/free mapped to the Delphi rtl GetMem/FreeMem calls and detection was made by FastMM memory manager.

The conventional order of actions like registering and unregistering a function before the db is being closed or xDestroy is called doesn't produce the leak.

Also if I don't unregister the function (probably I can do this anyway, but currently it's irrelevant), there's also no leak.

2021-05-14
14:34 Reply: Discovered bug in xBestIndex of virtual tables (artifact: 2782d6a9b4 user: Maxulite)

I can confirm that there are some unexpected solutions on sqlite side

So I emulated these testes and for example #2 (with Join) xBestIndex indeed offers two suggestions, one of them containing 3 constraints, but the third constraint in this case is exactly the same as the first one ("foo" field). The second xBestIndex call offers three columns, but the third column has usable field set to 0, so it's effectively a 2-column option. And if I make the 3-column option cheap and 2-column undesirable (returning SQLITE_CONSTRAINT), I get "no query solution". But if 2-column option is ok on my side (SQLITE_OK with some cost), sqlite goes with it (and xFilter offers two values). So the 3-column index suggestion is probably some internal non-usable variant.

This 2-column result looks exactly like sqlite somehow resolves "bar" value transitively (I see real values in xFilter and real results when bind some actual values). And for example #4 when there's no join I also see only one xBestIndex suggestion with a single column constraint and no solution if I expect both values in xFilter.

So it looks like sqlite is able somehow transitively resolve values for xFilter, but probably this mechanics is not ideal at the moment. So in my opinion it's not a bug, but a case for improvement

2021-05-13
16:49 Reply: Nothing for several days from the forum (artifact: 3bdfaad40c user: Maxulite)

let me share my version

  • Online blacklist host checkers (like https://mxtoolbox.com/blacklists.aspx ) point to UCEPROTECT-Level3 list for sqlite.org ip (45.33.6.223)
  • Querying sqlite.org ip at https://www.uceprotect.net/en/rblcheck.php shows level3 listing and explanation that there are too many abuses from the ip range of sqlite.org provider AS63949 (Linode).

The page explains different details including that in this case the said ip is probably not responsible. So either the provider will naturally crosses the threshold back or will have to take some measures if the clients (mostly innocent) encourage it to do so.

2021-05-07
11:15 Reply: Opening a DB with SQLITE_OPEN_EXCLUSIVE (artifact: f9a5d70aaa user: Maxulite)

I suspect that you expect too much from this flag. According to the documentation

The SQLITE_OPEN_EXCLUSIVE flag, when paired with the SQLITE_OPEN_CREATE, is used to indicate that file should always be created, and that it is an error if it already exists. It is not used to indicate the file should be opened for exclusive access.

There's a (still) not documented Windows-only parameter when opening with SQLITE_OPEN_URI (starting version 3034000). This was mentioned here at the forum and implemented very quickly, but still not mentioned at the docs. You should format uri something like this

file:///c:/mydb.db3?exclusive=1
2021-04-20
12:04 Reply: Aliased function result with the same name issue (artifact: 17af9c8507 user: Maxulite)

Thanks, jake, this is a good source of information.

I also thought for a while and suspect that the issue I mentioned might be introduced intentionally.

So we have two variant of alias-enabled queries

  • The ones that work only in sqlite producing errors on other dbmses. Here sqlite is free to behave like it prefers
  • The ones that work both in sqlite and other dbmses because the names of alias(es) match(es) the names of columns. So in order for such queries to produce the same results in sqlite as everywhere, Sqlite must pick the column value, not the aliased value.

I also hope that this "feature" will finally get some attention in the documentation.

09:27 Reply: Why no release announcement in this Forum anymore? (artifact: c0a8074078 user: Maxulite)

As long as I understand, what you see here as "announcements" are actually pre-release information posted in order to discuss and test upcoming features and changes. The releases that contain fixes (especially critical ones) released simply as downloads with visible traces at different sqlite.org pages, for example at https://sqlite.org/changes.html and sure https://www.sqlite.org/download.html

09:01 Reply: Aliased function result with the same name issue (artifact: 6962362303 user: Maxulite)

I had a vague thought that this was discussed before and finally found that the referencing of aliases in where and join clauses is not in the SQL standard. Actually both PostgreSQL and MySQL doesn't like it. I was wrong about MySql since I didn't test a query with a changed alias name. So, current tests for

select trim(txt) as txtalias from testspaces where txtalias <> ''

MySql

Unknown column 'txtalias' in 'where clause'
PostgreSql
column "txtalias" does not exist

So, as long as it's an sqlite-only feature, it follows its own rules, no question asked.

My vague feeling was probably originated from an answer at the maillist from Igor Tandetnik many years ago that even quoted elsewhere sometimes without attribution

Per SQL standard, column aliases can be referenced in ORDER BY, GROUP BY and HAVING clauses. As an extension, SQLite also allows them in WHERE and JOIN ON clauses, but again, such usage is non-standard (though very convenient at times). Neither the standard nor SQLite implementation allow referencing aliases in the SELECT clause.

Still can't find anything related at sqlite.org (including requirements)

2021-04-19
16:37 Reply: Aliased function result with the same name issue (artifact: 36c281c5cc user: Maxulite)

Thanks for the info about PostgreSQL. Knowing how often PostgreSQL was mentioned as a point of reference It is more likely the things would probably stay as they are now.

Also in the following query Sqlite chooses the column value, not the function result (MySql chooses the Trim result)

select trim(txt) as txt from testspaces join (select '' as foo) on txt <> foo

Does it match PostgreSQL?

09:17 Post: Aliased function result with the same name issue (artifact: f2aa1d0cbf user: Maxulite)

Hi, I noticed a little unexpected way Sqlite treats field aliases and would like to make sure this is by design or not.

In a query like this

SELECT MyFunction(MyField) as MyField Where MyField<>''

(when the name of the field and the alias are the same) it looks like the condition is evaluated against the original MyField value, not the result of MyFunction. I checked this with the version 3.35.4. The simplest way to reproduce this is by using Trim function against several rows of repeated space symbols.

I also checked this in MySql (some old version) and seems like the condition of the query

SELECT Trim(Txt) as Txt FROM `testspaces` where Txt<>''

references the result of the function, not the original field.

A sidenote fun fact: recently I found the page with sqlite requirements (https://www.sqlite.org/requirements.html) is a very good source of Sqlite knowledge that can be queried alternatively to usual searches like web search or site search. This is due to the fact that it contains the facts about Sqlite (3243 as of today) in concentrated form so if you're lucky to formulate the query with the right terms, you will get a small set of rows that can be easily digested. I mention this because before posting I queried a table created from today requirements against "%alias%name%" and didn't find anything related so if the issue is by design then it would be great that a new requirement explaining this will appear in the requirements.

Thanks

2021-03-17
11:28 Post: Parametrized queries with table-valued syntax (suggestion and virtual tables solution) (artifact: 8f0351af38 user: Maxulite)

Hi,

I would like to share my recent experience with Sqlite. I mush admit that the trip with this great library sometimes leads to unexpected destinations.

So recently I had a suggestion for Sqlite and even started to write it down. But eventually I saw that almost the same can be achieved with the virtual tables so probably the suggestion is not so actual even for myself. But anyway, the idea was to allow parameters in views and allow such views to be used with a tabled-valued syntax to pass actual parameters. So having a view

Create view DateQuery as select * from SomeTable where Date between ?1 and ?2

would allow querying something like

Select * from DateQuery('2011-01-01', '2021-01-01')

But having later some negative arguments about this idea talking to myself, I saw that there's a probably a way to implement something like this with the existing virtual tables mechanism of Sqlite (using "eponymous-only" feature of them). In its simplest form, there's a two-column table having a name column and a parametrized query column. After the db is opened, the code scans this table and creates eponymous-only modules, one for every row of this table. Each module has the same name as the first column of this table and every schema for a single row is prepared using the information about the parameters of the query in the second column. The "columns"-parameters are hidden (for the sake of table-valued syntax), remaining columns are from the underlining query itself. So the example above migrates to a row in this table (instead of hypothetical parametrized view)

'DateQuery' | 'select * from SomeTable where Date between ?1 and ?2'

and the usage of this query stays the same

Select * from DateQuery('2011-01-01', '2021-01-01')

Now I can confirm that this method works, at least for several tests I made after implementing this meta-module. There are some obstacles though for the broad usage. For example, since Sqlite caches virtual table after the first use, in order to see instant changes, changing queries should avoid anything related to schema (or reopen database after such changes.) Also inserting into this table will require addtional sync operation and deleting from it might involve newer sqlite3_drop_modules api. But this also is fixable with the database reopening.

2020-12-04
11:29 Edit reply: Add option for a process exclusive mode to open files on Windows (artifact: a7fe6152f5 user: Maxulite)

Just checked sqlite 3.34.0. The exclusive parameter for URI filename mode seems to work on Windows, quite a nice addition. But both release history and the page about sqlite3_open_v2 probably lack this information. I hope this can be fixed for others to know. Thanks

11:29 Reply: Add option for a process exclusive mode to open files on Windows (artifact: b3dc51d7a4 user: Maxulite)

Just checked sqlite 3.34.0. The exclusive parameter for URI filename mode seems to work on Windows, quite a nice addition. But both release history and the page about sqlite3_open_v2 probably lacks this information. I hope this can be fixed for others to know. Thanks

2020-10-27
10:08 Reply: Multi-threaded non-serialization (artifact: 68a64442fa user: Maxulite)

This temporal pending in case of reading claim is explained in the docs ("No new SHARED locks are permitted against the database if a PENDING lock is active, though existing SHARED locks are allowed to continue. "). So if a reader can termporary aquire a PENDING lock, then it can be granted SHARED one. So this trying is necessary, I'd recommend not to modify it if you have plans for complex reading/writing logic.

But by implementing this hack, the developers probably focused on the Window-specific obscacles (anti-virus and indexing), but not on the concurrent lock requests as in your case. If the developer might invest some time in this windows-only workaround, it can be remade using event waiting functions with 2-3 ms time out. I doubt it will have high priority so in your case you might as well invest your time in a little modification to winlock* family of functions to support lpOverlapped.hEvent

2020-07-14
13:43 Reply: Two space separated fields in SELECT field list (artifact: 8e8ce8405e user: Maxulite)

Thanks, indeed it was due to the optional AS :). I suppose if my mistake was the omitting of the planned AS, I'd recognize it instantly. But both my identifiers were correct field names and the intention was to list them so I saw the requested/provided count mismatch and nothing more.

13:05 Post: Two space separated fields in SELECT field list (artifact: 6af78431b5 user: Maxulite)

Hi,

Just by mistake typed a space instead of the comma when entering a field list for a Select statement, but noticed that Sqlite accepted it, just ignored the first one of two, so

SELECT Field1, Field2 Field3, Field4

finished showing

Field1, Field3, Field4

result

Other observations:

  • No third or any following participant in this space separated exception is possible, Sqlite gives an error.
  • No arbitrary name possible for the first field, only a valid field name
  • Absolutely equivalent behavior noticed for MySql.

Does it have some semantic meaning in the SQL world?

Thanks

Max

2020-07-07
10:49 Reply: Possible freeze in the progress loop (artifact: 18ac58dbc0 user: Maxulite)

I also tried to make some simple test with a small memory/time footprint using CTE and noticed that due to the logic behind the progress callback, the unexpected outcome could also be a partial freeze of the progress callback while Sqlite still continues the execution and finishes it.

This is probably because not every nVmStep increase goes to progress handler, but probably only "Goto" vdbe instruction. So nProgressLimit may have some almost fully saturated value (0xFFFFFFFA) and nVmStep leaves the progress loop (for example with 0xFFFFFFF9 value) and wraps after that to zero (while not visiting the progress loop), so after that this progress loop is ignored until nVmStep reaches again an big value.

So some other users previously could have not a responsive-freeze symptom like mine, but one when sqlite becomes fully unresponsive starting some point during the execution, but finally finishing without an issue.

2020-07-06
14:42 Reply: Possible freeze in the progress loop (artifact: 1139d2b649 user: Maxulite)

Thanks,

everything is ok, the insert is completed without freezing.

I wonder whether the counter resolution increase to 64 bit can have performance penalty on some 32-bit compilers/architectures. I hope not given that a single vdbe instruction probably translates into multiply assembler instructions

10:22 Post: Possible freeze in the progress loop (artifact: d07949dc94 user: Maxulite)

Hi,

When I insert a comparatively large dataset into a table (about 600 millions records) I encounter a "freeze" at some time during the insert. Since I have a progress callback, the "freeze" does not make the program unresponsive, only nothing happens with the VFS traffic (I have a vfs read/write numbers constantly showing the data) and with memory allocations. Windows process info also shows "freezing" for different I/O numbers.

When I made some debugging, the code that waits "forever" is probably this ( from SQLITE_PRIVATE int sqlite3VdbeExec )


while( nVmStep>=nProgressLimit && db->xProgress!=0 ){ assert( db->nProgressOps!=0 ); nProgressLimit += db->nProgressOps; if( db->xProgress(db->pProgressArg) ){ nProgressLimit = 0xffffffff; rc = SQLITE_INTERRUPT; goto abort_due_to_error; } }

In my case I see that

  • nVmStep = 0xFFFFFFFE
  • nProgressLimit is increasing by db->nProgressOps = 0xA.

Looking at the condition and taking the 32-bit wrapping logic into account, nProgressLimit will probably never be greater than the current nVmStep value (the only greater value is the odd 0xFFFFFFFF and no even to even wrapping (+0xA) will lead to it)

Max

2020-06-03
20:58 Reply: Unexepected commit result with two threads application (artifact: cfbd2df7e3 user: Maxulite)

The Check-in seems to work as expected (as a double check, I see version 3.33.0)

After reading the ticket, I suspect that this issue is probably non-actual for those who does waiting the right way, so without draining the CPU by multiply calls to sqlite3_step, and by using Sqlite recommended features like busy handler.

Although I noticed that for my long query that triggered this, the usual time was about 10 seconds and if the sqlite3_busy_timeout was above this, everything was ok, but if sqlite3_busy_timeout was 3 seconds, and I tried to repeat the step, the issue seemed to return. So my guess is the issue could affect ones who does the waiting the right way, but rely on small sleeping (either by Sqlite means or not), waking multiply times up to check or make the interface responsive.

19:28 Reply: Unexepected commit result with two threads application (artifact: e071a20218 user: Maxulite)
Thanks,
I will try to find something additional, but at the current moment I made a following test with logging
  ....
  if (p->rc != SQLITE_OK)
    sqlite3_log(..., "dif code: %u", p->rc);
  p->iCurrentTime = 0;
  assert( p->explain==0 );
  p->pResultSet = 0;
  db->busyHandler.nBusy = 0;
  if( AtomicLoad(&db->u1.isInterrupted) ) {
    sqlite3_log(..., "to interrupt: %u", p->rc);
    goto abort_due_to_interrupt;
  }
  ....

and the first log call (dif code) is triggered in the Thread1 (MainUI, on Commit sqlite3_step, I see this at the stack trace) with p->rc:SQLITE_BUSY and the second one (to interrupt) never called. So it's probably somewhere in the following code when the p->rc values difference might matter
15:05 Reply: Unexepected commit result with two threads application (artifact: 160fa3b474 user: Maxulite)

Thanks, eventually not aware of the replies, made an inquiry and ended up with "manual" bisect by splitting sections at the timeline.

The change that triggers the difference is https://www.sqlite.org/cgi/src/info/2361b03b61311aab (Remove another unnecessary local variable initialization from sqlite3VdbeExec())

The code fragment in question is (the difference between versions is the line with my comment "THIS IS THE LINE ...". I looked at the latest sqlite, basically these particular lines are the same, only isInterrupted wrapped in AtomicLoad)

assert( p->rc==SQLITE_OK || (p->rc&0xff)==SQLITE_BUSY );
assert( p->bIsReader || p->readOnly!=0 );
p->rc = SQLITE_OK; // THIS IS THE LINE THAT TRIGGERS THE CHANGE
p->iCurrentTime = 0;
assert( p->explain==0 );
p->pResultSet = 0;
db->busyHandler.nBusy = 0;
if( db->u1.isInterrupted ) goto abort_due_to_interrupt;

I also noticed isInterrupted field here so I looked once more at my code and saw also an interrupt action so in order to clarify, the steps are

  • Thread1 (Main UI) asks the Thread2 Connection to sqlite3_interrupt, so the call is from Thread1, but for the connection of Thread2. This is because a new text is being entered so the previous query is no longer needed
  • Thread2 by reacting to an event triggered by the previous step starts a read-only query
  • Thread 1 begins deferred transaction, updates and calls Commit. If the line with p->rc = SQLITE_OK is omitted then sqlite3_step returns SQLITE_BUSY twice and returns SQLITE_DONE but failing to write. If the line is present then sqlite3_step allows calling it multiply times with SQLITE_BUSY result and correctly writes.

I'm not the developer so this is only my speculation. According to the first assert the p->rc can have different values (not only SQLITE_OK), but if the interruption (last line) is triggered its final state might be different in two Sqlite versions so this change may be an optimization with unnoticed side effects.

2020-06-02
16:50 Reply: Unexepected commit result with two threads application (artifact: 29a05d0d62 user: Maxulite)

As a last resort, I tried to make tests with different Sqlite versions (dll). And pinpointed the change to the version 3.17.0, so 3.16.2 and below correctly waits for the reading thread to finish, but 3.17.0 and above shows the symptoms I described elsewhere, including the win32 dll from the site.

The history information about 3.17.0 doesn't show anything interesting that might be related to locks or something, only "Perform some UPDATE operations in a single pass instead of in two passes" might be related. Also quickly scrolled through WinMerge difference between amalgamation sources, also didn't notice anything suspected given that I'm not the developer.

I wonder is there an easy way to download amalgamation betas chronologically between the releases? My "binary" search then will be able to find the change in question.

15:30 Reply: Unexepected commit result with two threads application (artifact: 0e88c45f45 user: Maxulite)

Thanks, indeed according to lang_transaction.html

"SQLite attempts to undo just the one statement it was working on and leave changes from prior statements within the same transaction intact ... However, ... it might be necessary for SQLite to rollback. An application can tell which course of action SQLite took by using the sqlite3_get_autocommit()... "

It's interesting that in my case the codes and states are strange or at least make no sense to me.

COMMIT sqlite3_step in the main thread when supposedly another thread is still reading.

  • SQLITE_BUSY, sqlite3_get_autocommit(): 0
  • SQLITE_BUSY, sqlite3_get_autocommit(): 0
  • SQLITE_DONE, sqlite3_get_autocommit(): 1

I could stop trying if the step 2 gave me autocommit: 1, but there's no one, so the next step looked like success, but actually according to the data, led to a rollback

I still suspect that there might be something wrong with my code.

13:36 Reply: Unexepected commit result with two threads application (artifact: 9d833bf6c0 user: Maxulite)

Well, it looks like I need to require some additional knowledge about locking, transaction kinds and deadlocks when db is busy. But after some reading I have a question regarding SQLITE_BUSY. Is it safe to assume that any Sqlite function that returned SQLITE_BUSY should not be called second time?

11:14 Reply: Unexepected commit result with two threads application (artifact: 9eca70676c user: Maxulite)

Thanks,

It looks like I have no busy handler, only greedy waiter (no sleeping), and I just looked several times, the main thread tried to do commit for update operations and got SQLITE_BUSY two times (sqlite3_step calls), after that SQLITE_DONE with the consequences I described. I checked this several times, no exception, every time the counter of SQLITE_BUSY results is always 2 before SQLITE_DONE. And very fast (subjectively)

I know that it's better to do a graceful waiting with my own sleep or little help from Sqlite, but I suppose the greedy waiter can't be the reason behind this issue.

09:59 Post: Unexepected commit result with two threads application (artifact: ba6f754fc2 user: Maxulite)
Hi

I met an unexpected Sqlite behavior in my note-taking application on Windows. But I may have wrong assumptions about how locking is working so maybe a fix in the code is needed.

The application basically uses the same db in two threads. But each one opens a separate db connection (the same db physical path). 
- Thread 1 is about all general information retrieval and change (UI thread).
- Thread 2 is for quick search in the search box in order to show a popup list of the phrases met a search criteria while the user is typing. 

The problem is when the application want to commit in the main thread (after several Update statements), but quick type search is still searching, the overall consequence looks like a rollback effect: commit returns SQLITE_OK, no new changes in the data, the rollback file is gone and the date of the main db file is the one that was before this change was tried.  

I checked this with 3.32.01 version, Windows 7, 32 bit app 

Do I expect too much about auto-locking handling and probably still need some inter-thread locking mechanism despite the fact that the connections are different? 

Max
More ↓