SQLite Forum

Timeline
Login

50 events by user gunter_hick occurring around 2021-10-25 12:07:14.

More ↑
2021-11-23
15:51 Reply: How to free max heap size on execution of sqlite query using sqlite3_exec? artifact: b74cacb378 user: gunter_hick

Please do not edit your posts. It makes things much more complicated

15:47 Reply: How to free max heap size on execution of sqlite query using sqlite3_exec? artifact: fe367010c2 user: gunter_hick
Sorry but that opens up more questions than it answers.

Did you build SQLite for verix OS yourself?

Have you checked to see if each execution of the query adds about the same amount to heap memory?

Have you used valgrind or whatever tool there is available on verix OS to check for memory leaks?

Have you tried reproducing the error on a less obscure OS?

Can you provide a minimal example (schema and queries in plain SQL) that illustrates the problem?
08:13 Reply: How to free max heap size on execution of sqlite query using sqlite3_exec? artifact: c2b02ba891 user: gunter_hick
Which "device", which operating system, which release of sqlite, which schema, which query?

Can you replicate the problem using sqlite shell?

There are basically two possibilities:

a) There is an actual memory leak (indicated by heap memory increasing linearly each time you re-run the query), in which case you need to show that this is happening in sqlite and not in your own code.

b) What you are observing is heap memory increasing to a point where queries can be run by re-using memory space already acquired.
2021-11-18
13:34 Reply: VisualStudio 2022: C4746 concerning "volatile" triggers under ARM64 artifact: 20e5c0d362 user: gunter_hick
Maybe you will get more traffic if you include the exact version of SQLite you are attempting to compile and some of the actual warning messages.
2021-11-09
13:04 Reply: ExecuteNonQuery returns wrong number of affected elements artifact: 79c037887a user: gunter_hick
On rereading I found the following limitation: "Even though a DML statement with a RETURNING clause returns table content, it cannot be used as a subquery. The RETURNING clause can only return data to the application."
11:55 Reply: ExecuteNonQuery returns wrong number of affected elements artifact: 09e11be9a8 user: gunter_hick
IIRC system.data.sqlite is also supported here. It just helps to state the wrapper one is using.

I don't have access to an SQLite release that knows how to DELETE RETURNING; so either my syntax is off, or your SQLite doesn't know either. Or both.
2021-11-08
15:38 Reply: Hide rows in table1 if same value found in table2 ? artifact: 0ca2c2def3 user: gunter_hick
I am not sure that you are on the right forum. AFAIK DB4S is maintained by somebody completely different than the SQLite library, which is maintained here. Questions about the operation of the browser should be directed to the DB4S maintainers.
15:31 Reply: INTEGRITY OS support for WINDOWS artifact: 6144909e66 user: gunter_hick

I'm not quite sure what the connection to SQLite is here. Can you elaborate?

10:04 Reply: Hide rows in table1 if same value found in table2 ? artifact: 75640d44ab user: gunter_hick
Yes, you use your own table and column names. I suggest using something a little more descriptive than "table1" and "table2".

Currently you are limiting yourself to the schema of your data source (the csv file). This is keeping you from adopting the simple solution. I would suggest you do the following:

x) Keep a table (maybe call it import_data) that exactly matches the csv file.

x) Define a second table with a descriptive name that has the additional fields you may need, including the "deleted" field.

x) Transfer the imported data using your own dedicated "schema conversion" statement. This could probably be an INSERT INTO .. DELETE FROM import_data RETURNING <field list>;

x) Do all your programming against your own schema. Any changes in the csv file schema or your own schema are handled at exactly one point, the above schema conversion statement.
08:11 Reply: ExecuteNonQuery returns wrong number of affected elements artifact: 06b15fce9d user: gunter_hick
I'm guessing here that you are not calling SQLite directly but are using some kind of wrapper. Would you divulge which one? Depending in the answer, you would be better off asking in that wrappers' forum.

Can you replicate the problem using just the SQlite shell, e.g. 

BEGIN;
SELECT count() FROM table;
SELECT count() from (DELETE FROM table RETURNING 1);
COMMIT;

and checking that both counts match?
07:56 Edit reply: Hide rows in table1 if same value found in table2 ? artifact: 360779c42e user: gunter_hick
If you can change the schema, add a column to store the "deleted" attribute, so you can SELECT ... WHERE NOT deleted.

Otherwise

SELECT ... WHERE NOT EXISTS (SELECT 1 FROM table2 where table2.field == table1.field);

Or

SELECT ... WHERE table1.field NOT IN (SELECT DISTINCT field FROM table2);

Edit: added DISTINCT to above SELECT
07:54 Reply: Hide rows in table1 if same value found in table2 ? artifact: 07b0a1a362 user: gunter_hick
If you can change the schema, add a column to store the "deleted" attribute, so you can SELECT ... WHERE NOT deleted.

Otherwise

SELECT ... WHERE NOT EXISTS (SELECT 1 FROM table2 where table2.field == table1.field);

Or

SELECT ... WHERE table1.field NOT IN (SELECT field FROM table2);
2021-11-04
11:56 Reply: Is this inherent casting by design? artifact: 972249fe2a user: gunter_hick

See https://sqlite.org/datatype3.html

"Mathematical operators (+, -, *, /, %, <<, >>, &, and ) interpret both operands as if they were numbers."

And no, the string has to start with something that looks like a number (leading whitespace is allowed). So '10 EUR' converts to 10, whereas 'EUR 10' converts to 0.

This conversion is a convenience feature due to permissive typing; strictly typed engines may even throw a syntax error, telling you that multiplying a string and a number is unsupported.

08:17 Reply: Is the order conserved in a table from a VALUES clause? artifact: 19f5963a1d user: gunter_hick
In SQLite 3.24.0, both versions (inside/outside CTE) generate identical code. Maybe there is a difference if the CTE is used in a JOIN instead of directly, so the difference between "natural order" (inside cte) and "explicit order" (outside cte) becomes visible.

explain with t(ord, val) as (values (1, 'two'), (2, 'one'), (3, 'five')) select val from t order by ord;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  NULL
1     InitCoroutine  1     12    2                    00  NULL
2     Integer        1     2     0                    00  NULL
3     String8        0     3     0     two            00  NULL
4     Yield          1     0     0                    00  NULL
5     Integer        2     2     0                    00  NULL
6     String8        0     3     0     one            00  NULL
7     Yield          1     0     0                    00  NULL
8     Integer        3     2     0                    00  NULL
9     String8        0     3     0     five           00  NULL
10    Yield          1     0     0                    00  NULL
11    EndCoroutine   1     0     0                    00  NULL
12    SorterOpen     1     3     0     k(1,B)         00  NULL
13    InitCoroutine  1     0     2                    00  NULL
14      Yield          1     20    0                    00  NULL
15      Copy           3     7     0                    00  NULL
16      Copy           2     6     0                    00  NULL
17      MakeRecord     6     2     8                    00  NULL
18      SorterInsert   1     8     6     2              00  NULL
19    Goto           0     14    0                    00  NULL
20    OpenPseudo     2     9     3                    00  NULL
21    SorterSort     1     26    0                    00  NULL
22      SorterData     1     9     2                    00  NULL
23      Column         2     1     7                    00  NULL
24      ResultRow      7     1     0                    00  NULL
25    SorterNext     1     22    0                    00  NULL
26    Halt           0     0     0                    00  NULL
07:18 Reply: Is the order conserved in a table from a VALUES clause? artifact: 89e8c48d41 user: gunter_hick
This:

WITH ov(idx,val) AS (SELECT * FROM (VALUES (1,'Z'),(3,'M'),(2,'A')) ORDER BY 1) SELECT * FROM ov;
idx  val
---  ---
1    Z
2    A
3    M

still uses a coroutine, but returns the entries in the requested order. And is future proof, because it explicitly specifies an order.

explain with ov(idx,val) as (select * from (values (1,'Z'),(3,'M'),(2,'A')) order by 1) select * from ov;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  NULL
1     InitCoroutine  1     12    2                    00  NULL
2     Integer        1     2     0                    00  NULL
3     String8        0     3     0     Z              00  NULL
4     Yield          1     0     0                    00  NULL
5     Integer        3     2     0                    00  NULL
6     String8        0     3     0     M              00  NULL
7     Yield          1     0     0                    00  NULL
8     Integer        2     2     0                    00  NULL
9     String8        0     3     0     A              00  NULL
10    Yield          1     0     0                    00  NULL
11    EndCoroutine   1     0     0                    00  NULL
12    SorterOpen     2     4     0     k(1,B)         00  NULL
13    InitCoroutine  1     0     2                    00  NULL
14      Yield          1     21    0                    00  NULL
15      Copy           2     7     0                    00  NULL
16      Copy           3     8     0                    00  NULL
17      Copy           7     6     0                    00  NULL
18      MakeRecord     6     3     9                    00  NULL
19      SorterInsert   2     9     6     3              00  NULL
20    Goto           0     14    0                    00  NULL
21    OpenPseudo     3     10    4                    00  NULL
22    SorterSort     2     28    0                    00  NULL
23      SorterData     2     10    3                    00  NULL
24      Column         3     2     8                    00  NULL
25      Column         3     1     7                    00  NULL
26      ResultRow      7     2     0                    00  NULL
27    SorterNext     2     23    0                    00  NULL
28    Halt           0     0     0                    00  NULL
2021-11-03
06:47 Reply: Is the order conserved in a table from a VALUES clause? artifact: c96625304b user: gunter_hick
I cannot make assertions on behalf of the SQLite Dev team. I am just reporting the status for a specific version I am working with. We have SQLite integrated into our proprietary build environment with some customizations that require careful merging and testing when switching versions. We also use our own virtual table implementations extensively. So there is considerable work involved in switching SQLite releases, which is why we are currently using 3.24.0

Please do not construe my report as an endorsement to rely on current undocumented behaviour.
2021-11-02
15:24 Reply: Is the order conserved in a table from a VALUES clause? artifact: 3cd08ce4ad user: gunter_hick
The pragma will reverse the order (my guess here) if and when SQLite decides to implement VALUES as an internal table.

I'm also guessing that the pragma affects the execution of the SQL program and not the generated bytecode, so concievably you could prepare a statement, run it, change the pragma setting, and rerun it, yielding different ordering of unordered selects.

There is also this disclaimer, which IMHO covers the subject exception on VALUES: "The reverse_unordered_selects pragma works for most SELECT statements, however the query planner may sometimes choose an algorithm that is not easily reversed, in which case the output will appear in the same order regardless of the reverse_unordered_selects setting."
14:31 Reply: Is the order conserved in a table from a VALUES clause? artifact: 204d416ad6 user: gunter_hick
Neither. See also my comment below. This is the bytecode generated by SQLite 3.24.0:

asql> explain select * from (values  (1),(2),(3) );
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  NULL
1     InitCoroutine  1     9     2                    00  NULL
2     Integer        1     2     0                    00  NULL
3     Yield          1     0     0                    00  NULL
4     Integer        2     2     0                    00  NULL
5     Yield          1     0     0                    00  NULL
6     Integer        3     2     0                    00  NULL
7     Yield          1     0     0                    00  NULL
8     EndCoroutine   1     0     0                    00  NULL
9     InitCoroutine  1     0     2                    00  NULL
10      Yield          1     14    0                    00  NULL
11      Copy           2     4     0                    00  NULL
12      ResultRow      4     1     0                    00  NULL
13    Goto           0     10    0                    00  NULL
14    Halt           0     0     0                    00  NULL

The guarantee holds only as long as the code generator implements VALUES as a coroutine, and this prevents the pragma from affecting the order.
13:36 Reply: Is the order conserved in a table from a VALUES clause? artifact: bdf99f11ec user: gunter_hick
In SQLite 3.24.0 the VALUES clause is implemented as a coroutine, so the implicit order of the rows returned is that from the code generator, which currently happens to be the lexical order in the statement.

None of this is subject to any guarantees. If in the future SQLite decides to make an internal table out of the VALUES clause and the query planner thinks it expedient to access this table on the RHS side of a join and maybe with a temporary index, anything could happen with the visitation order.
2021-10-30
09:41 Reply: How to insert TEXT data from SQL server to SQLite with SQLite ODBC Driver artifact: cfb5e544b0 user: gunter_hick
This is most probably an issue with the ODBC driver. SQLite does not return German language error messages. The error message would indicate that INSERT ... INTO is not supported by the driver.

You have also failed to provide the schema of the table you are attempting to insert into, so maybe your assertion of target column type is mistaken.

Whart do you mean with "SQLite is as linked server"? SQLite is a library that is either statically or dynamically linked into your application. There is no "server" like in client/server database systems.
2021-10-28
14:05 Reply: --skip option does not work correctly. artifact: f41017c1ea user: gunter_hick
If you want anonymous columns, just leave out the header line.
09:42 Reply: Database on android SD CARD artifact: 458ca9b9bc user: gunter_hick

My guess is as good as yours. I'm inclined to think CLI executable because it does not have to have a file name suffix in Unix, whereas shareable images/libraries tend to end in .so

It may be irrelevant unless unix binaries can be run on android

09:21 Reply: --skip option does not work correctly. artifact: 588ce245b1 user: gunter_hick
Please be more specfic as to the meaning of "does not work correctly". Maybe stating what you expected and what you got my yield enough information to suggest a solution.
09:12 Reply: Database on android SD CARD artifact: 80644234f1 user: gunter_hick

The OP already stated thus: "So, instead of the andriod binaries, I downloaded Precompiled Binaries for Linux and was able to move the sqlite file to my sdcard"

2021-10-25
12:07 Delete reply: 100x jump in query time with IN operator artifact: 33515d88e3 user: gunter_hick
Deleted
12:06 Reply: 100x jump in query time with IN operator artifact: 5a04e3117b user: gunter_hick
SQLite NGQP is a cost based query planner. The IN () operator with a list of literal values gets implemented as a kind of temporary table; sometimes SQLite decides to create an index and do lookups, other times it decides to use that table as the outermost loop of the query.

EXPLAIN QUERY PLAN should show that in a more concise manner.

If compiled in DEBUG mode mith WHERETRACE enabled, the .wheretrace command will show how SQLite NGQP reaches its plan. Essential input is the return values from the xBestIndex method of your virtual table, especially the "number of rows" and the "estimated cost". It is paramount to deliver accurate estimates. Cost should reflect processing cost relative to SQLite native tables.

Note that you can name the IN table by making it a CTE and CROSS JOIN to force the query plan that works fast.
12:06 Reply: 100x jump in query time with IN operator artifact: a3d68ed8b4 user: gunter_hick
SQLite NGQP is a cost based query planner. The IN () operator with a list of literal values gets implemented as a kind of temporary table; sometimes SQLite decides to create an index and do lookups, other times it decides to use that table as the outermost loop of the query.

EXPLAIN QUERY PLAN should show that in a more concise manner.

If compiled in DEBUG mode mith WHERETRACE enabled, the .wheretrace command will show how SQLite NGQP reaches its plan. Essential input is the return values from the xBestIndex method of your virtual table, especially the "number of rows" and the "estimated cost". It is paramount to deliver accurate estimates. Cost should reflect processing cost relative to SQLite native tables.

Note that you can name the IN table by making it a CTE and CROSS JOIN to force the query plan that works fast.
2021-10-21
15:06 Reply: Inconsistency in BETWEEN Query artifact: 0eaf895da7 user: gunter_hick
You need to read up on data types is SQLite and the concept of "affinity".

See https://sqlite.org/datatype3.html#sorting_grouping_and_compound_selects

In section 4.2. Type Conversions Prior To Comparison, the second bullet is "If one operand has TEXT affinity and the other has no affinity, then TEXT affinity is applied to the other operand."

So you are performing a string (lexical) comparison of (duplicate digits removed for clarity) "40100" <= "4011" <= "40199", which is obviously true; because "4011" comes after any string beginning with "4010" and before any string beginning with "4019".
2021-10-15
10:24 Reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price artifact: c35c2625be user: gunter_hick
This will probably calculate the number of days: (julianday(date_out) - julianday(date_back) + 1)

You need to use
 WHERE rowid = NEW.ROWID
to restrict changes to the affected row. 

 WHERE rental_cost IS NULL
will update ALL of the rows that have a NULL rental cost

The price in the subselect will be the one from the first Rental record visited in the subselect. I don't think that is what you intend. If you want the price from the affected row, just use NEW.price instead.
09:04 Edit reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price artifact: 1785d2aef0 user: gunter_hick
Lots of misconceptions here.

x) SQLite trigger programs do not have variables. The result column price from the SELECT ist not available to the separate UPDATE statement. Use a subselect to retrieve the price that relates to the row being updated.

x) The UPDATE is going to affect every row of Rental that matches the WHERE clause, not just the NEW row. Use rowid = NEW.rowid instead

x) rental_cost = NULL is always FALSE; nothing is ever equal to NULL, not even NULL. Use IS NULL to check for NULL values.

x) I think you meant to compute (date_out - date_back), not subtract a date from a product of a price and a date.

Edit: Subtracting dates stored as TEXT will probably not yield the expected result.
09:01 Reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price artifact: b6c6137cdd user: gunter_hick
Lots of misconceptions here.

x) SQLite trigger programs do not have variables. The result column price from the SELECT ist not available to the separate UPDATE statement. Use a subselect to retrieve the price that relates to the row being updated.

x) The UPDATE is going to affect every row of Rental that matches the WHERE clause, not just the NEW row. Use rowid = NEW.rowid instead

x) rental_cost = NULL is always FALSE; nothing is ever equal to NULL, not even NULL. Use IS NULL to check for NULL values.

x) I think you meant to compute (date_out - date_back), not subtract a date from a product of a price and a date.
2021-10-13
15:10 Reply: Data Types artifact: 2aa9f14827 user: gunter_hick
Maybe you are confusing values returned from interfaces with serial type codes used in the internal record format.
2021-10-08
11:44 Reply: Updating in-process connections on write artifact: 16f6f9cace user: gunter_hick
Sharing a connection between threads that use implicit transactions means that none of the threads can be sure when a transaction begins or ends. They share a common transaction that begins when any thread starts reading and ends when all threads are done with reading simultaneously. This is not transparent to the individual threads. E.g. if two threads alternate in reading, each starting a new SELECT before the other one ends it's SELECT, the transaction never commits, and thus never sees the result of a write transaction on the other connection.

Using a separate connection for each thread gives each thread control over it's own transaction. No transaction can see "intermediate" values if you properly BEGIN and END any write transactions.
2021-10-04
09:18 Reply: Is the lifetime of argv in sqlite3_module::xConnect greater than sqlite3_module::xFilter? artifact: ba002147ae user: gunter_hick
There are at least two possible sources for the module name passed to the xConnect method.

- it could be the argument passed to the sqlite3_create_module[_v2] function, in which case it's lifetime is determined by you, the caller

- it could be a copy of the above, stored in SQLite internal structures associated with the module, which would then probably be valid until the module is dropped

- it could be the argument given in the CREATE VIRTUAL TABLE statement, in which case it could reside in the input buffer, or inside the SQL program created to process the statement, or somewhere else convenient for SQLite; all of which  probably expire after your xConnect function returns
2021-09-30
11:20 Reply: How to filter find duplicate and show row with same field using sqlite code? artifact: b9aad0d5dd user: gunter_hick
What you are looking for is an "autojoin", i.e. joining a table to itself.

SELECT a.* from person a join person b on (a.name = b.name and a.person_no <> b.person_no);
07:33 Reply: Multithread processing, storing in memory and writing to database artifact: c8a46bbb28 user: gunter_hick
The heretic's approach:

- have each thread write INSERT INTO <table> VALUES (<values>); statements into a text file 
- when triggered (and/or periodically), each thread closes and renames the text file, then opens a new file
- main thread processes the renamed text files into the DB and renames them again (or deletes them if you don't require rollforward capability)

No pesky synchronization of databases, predictable effort storing the metrics, just one connection doing all the writes in a batch transaction.

Alternatively, instead of full statements, have the metric threads write csv files containing just the data and import those either directly in the shell or via a CSV table valued function.

INSERT INTO <table> SELECT * FROM CSV('<filename>');
2021-09-28
08:58 Reply: Views, Casting vs and converters artifact: 44eaada7ec user: gunter_hick
I think you are not properly separating SQL issues from Python issues.

For SQLite data types see https://sqlite.org/datatype3.html. The builtin types are NULL, INTEGER, REAL, TEXT and BLOB. It is not possible to define additional types in SQLite. Any type name you provide is mapped to one of the builtin types.

For SQL Syntax see https://sqlite.org/lang.html, https://sqlite.org/lang_select.html and https://sqlite.org/lang_expr.html.

The CAST SQL function attempts to convert whatever actual value is provided to a value of the builtin types the provided type-name is mapped to.

An integer value of 1 stored in column my_col with a declared type of INTEGER is stored as an integer value of 1.

my_col thus returns the integer value 1,
CAST(my_col as FLOAT) returns real value 1.0,
CAST(my_col as BOOLEAN) leaves the integer value 1 as the integer value 1

SQLite does not attach any meaning to the column name. It seems you are attempting to convey information to Python through this channel. IMHO this violates the layer model. SQLite is in the data layer; SQL Queries should focus on retrieval of the data. Your Python script handles the presentation layer, so it should already know how the data is returned, and not rely on abusing column names as data.

Python questions are better posed on a Python orieanted plattform.
2021-09-14
06:11 Reply: integer overflow in pager.c artifact: 4c5e8c2fa9 user: gunter_hick
I asked my C compiler (GCC 4.8.5/RH7/IA64) about this:

    int32_t              a = 65536;
    int32_t              b = 0x7ffffff;
    int64_t              p;

    p = a*b;

and got this

32 bit init:   c7 45 fc 00 00 01 00    movl   $0x10000,-0x4(%rbp)
32 bit init:   c7 45 f8 ff ff ff 07    movl   $0x7ffffff,-0x8(%rbp)
32 bit load:   8b 45 fc                mov    -0x4(%rbp),%eax
32 bit mult:   0f af 45 f8             imul   -0x8(%rbp),%eax
32->64 conv:   48 98                   cltq
64 bit stor:   48 89 45 f0             mov    %rax,-0x10(%rbp)

and then about this:

    int32_t              a = 65536;
    int32_t              b = 0x7ffffff;
    int64_t              p;

    p = (int64_t)a*b;

32 bit init:   c7 45 fc 00 00 01 00    movl   $0x10000,-0x4(%rbp)
32 bit init:   c7 45 f8 ff ff ff 07    movl   $0x7ffffff,-0x8(%rbp)
32 bit load:   8b 45 fc                mov    -0x4(%rbp),%eax
64 bit cast:   48 63 d0                movslq %eax,%rdx
32 bit load:   8b 45 f8                mov    -0x8(%rbp),%eax
32->64 conv:   48 98                   cltq
64 bit mult:   48 0f af c2             imul   %rdx,%rax
64 bit stor:   48 89 45 f0             mov    %rax,-0x10(%rbp)

So yes, there may be truncation without a cast. Machine code is 6 bytes longer and 64 bit multiplication seems to require both operands in registers.
2021-09-13
13:10 Reply: integer overflow in pager.c artifact: 0a1b617041 user: gunter_hick
I don't think so.

Both statements multiply two 32 bit integers yielding a 64 bit result. The values you claim as problematic both use 16 or 17 bits, which would overflow a 32bit result, but come nowhere near overflowing 64 bit integers.

i64 offset = (i64)pSavepoint->iSubRec*(4+pPager->pageSize);

i64 sz = (pPager->pageSize+4)*pRel->iSubRec;

Even multiplying the maximum pagesize (17 bits) by maxint (31 bits) only yields 48 bits of product, well within the 63 bits available for positive integers.
2021-09-10
06:40 Reply: Method to temporarily disable Not Null constraint? artifact: a63d3daeea user: gunter_hick
Since you have foreign keys disabled, would it not be possible to insert a non-NULL token value for the relational properties? After all, the second pass is going to fix them anyway. And you can check against a suitably selected token value before enabling constraint checking.
2021-09-09
09:00 Reply: How to insert duplicate rows? artifact: 97d7f045e7 user: gunter_hick
What you are referring to is a formatting quirk. The OP is building the CREATE TABLE statement by concatenating string literals on continuation lines. The double quotes do not appear in the statement itself.

"fragment"<whitespace><backslash><newline>
"fragment" ...
06:51 Reply: How to insert duplicate rows? artifact: d76d0062ef user: gunter_hick
Can you replicate the issue using the SQLite shell alone? If that is the case, then please post the exact SQL used.

If not, then there is probably something wrong in what you are not showing. Like which release of SQLite you are working with, the programming language used (maybe C or similar from the obvious expectation that white space separated literal strings are concatenated and the extraneous backslash line continuation characters?), and the actual code.
2021-09-06
12:26 Reply: Automatic indexing (idle question) artifact: 02bba99d9b user: gunter_hick
Idle answer: Apart from indices required to fulfil constraints (e.g. UNIQUE and FOREIGN KEY), the utility of an index is exposed by it being used in the queries run against the database. This is by principle available only in retrospect. AFAIK SQLite does consider creating an index to run just one statement fast, but only if the cost of creating such an index is expected to be  amortised within that single statement.

Even if the application programmer has a defined set of queries, there is no guarantee that SQLite will have seen all of said set; and it may well be that the one index never before used is justified by a need for a blindingly fast response to an "emergency query" required to "save the planet from an asteroid strike" (gross exaggeration), in which case Murphy ensures that SQLite will have deleted exactly this index just 5 minutes before it is needed.

And then there is still the issue of how much time one is prepared to sacrifice in each and every query for an as yet unknown benefit that may arise from an automatically added/dropped ondex.
2021-09-03
06:58 Reply: Virtual table ORDER BY and GT/LT (GE/LE) constraints expected behavior - full table scan when not needed? artifact: bcdd9dfa97 user: gunter_hick
There are two separate things going on here.

SQLite is asking about a constraint AND about an ordering.

I guess you are already correctly setting an index number and the orderByComsumed flag (the query plan should reflect this) so that your xFilter and xNext functions return rows in the correct order.

What you are observing is consistent with telling SQLite that you can NOT handle the constraint; SQLite codes a check and discards any returned rows that do not match. It will not stop until you tell it that the EOF has been reached (xEOF returns true).

You can speed things up by handling the constraint (field,>) yourself. You need to assign an argv index value to receive the value to compare the field against in the xFilter function and code to return EOF when the constraint is no longer met.

You should also change your estimated cost and estimated rows return values to reflect that you will be retrieving, on average, half as many rows for a greater/less than scan.

If you set the omit flag, SQLite will trust your VT table implementation and not code a constraint check itself.
2021-09-02
14:01 Reply: Why data is lost in SQLite database with WAL mode on when connection is not closed properly? artifact: f77e1f9c55 user: gunter_hick

As confessed in another thread, the OP is running two different versions of SQLite within the same "app" and is unable to discern if he is doing so within different threads or different processes.

I guess it is just interference from using two different SQLite libraries, possibly compounded by loss of POSIX locks if runnign within the same process.

13:53 Edit reply: Why WAL file (-wal) may disappear? artifact: d73300eb0a user: gunter_hick
As confessed in a different thread, the OP is running different versions of SQLite while accessing the same db file. And probably also in threads of the same process, but is unable to determine if this is so or not.
13:51 Reply: Why WAL file (-wal) may disappear? artifact: 596160733a user: gunter_hick
The OP is running different versions of SQLite while accessing the same db file. And probably also in threads of the same process, but is unable to determine if this is so or not.
2021-09-01
13:48 Reply: Lua db:close deletes WAL file artifact: 6f803472eb user: gunter_hick
Then you already have the answer you are looking for and can stop repeat-posting the same problem over and over again.

There is no problem with SQLite, only with the way you are abusing it. The book 1984 is a warning, not an instruction manual. The same goes for https://sqlite.org/howtocorrupt.html
10:22 Reply: Lua db:close deletes WAL file artifact: 1f282de976 user: gunter_hick
Are you sure your "app" and your "lua script" are actually running in truly separate processes, and not just separate threads?

Are you sure that your "app" and your "lua script" are calling the same sqlite library? Maybe one is hard linked and the other is using the "installed" library.
2021-08-30
06:17 Edit reply: Can an application-defined function be used to convert one row of data to multiple rows and insert them into another table? artifact: 98819819db user: gunter_hick
We are using virtual tables for similar purposes since release 3.7.14. The mechanism has been expanded to allow table valued functions by declaring hidden columns in the internal table declaration passed back to SQLite, e.g.

CREATE TABLE split (word TEXT, list TEXT hidden)

which allows one to 

SELECT word FROM split WHERE list = 'list of words, with punctuation';

and also JOIN to other tables
More ↓