SQLite Forum

Timeline
Login

50 events by user gunter_hick occurring on or before 2021-08-30 06:17:47.

More ↑
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
06:17 Reply: Can an application-defined function be used to convert one row of data to multiple rows and insert them into another table? artifact: cd7cc9fe98 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.

CREAT 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
2021-08-18
07:05 Reply: SIGBUS in sqlite artifact: 9c1225e45f user: gunter_hick
This is exactly what one would expect if one is bypassing the SQLite library to access an SQLite db file directly. SQLite Linux drivers know about this and take care to only ever use one file descriptor, even for multiple connections, and resolve symbolic links to the canonical file name.
05:58 Reply: SIGBUS in sqlite artifact: a65cd5e0a2 user: gunter_hick

Not familiar with android.

On linux, the documented "broken POSIX advisory lock" scenario relates to events within a single process. If two file handles point to the same file, closing one will release all locks on the file held by the process, regardless of which file handle was used to obtain them and which thread issued the request.

Maybe your "processes" are really threads? Or maybe you are using a separate file handle to "check on the shm file" for debugging purposes in process A?

2021-08-16
14:30 Reply: Download .TAB file into SQLITE3 Table artifact: 276fa9a058 user: gunter_hick
This appears to be a limit imposed by the sender of the data, not related to anything in SQLite.
2021-08-12
11:21 Reply: Test ACID properties SQLITE artifact: 0086fcd1e9 user: gunter_hick

Impressive. That would be about 125 copies per person alive today. Or over 200 times the total COVID vaccine shots administered worldwide.

11:08 Reply: Increasing insert performance of an in-memory db artifact: abb64f38e2 user: gunter_hick
Be sure to keep TEXT and BLOB fields near the end of the row, in decreasing access frequency, and fields used in the indices near the begining of the row. This minimizes the amount of decoding, as accessing any fields located after a TEXT/BLOB with sizeable data will require access to overflow page(s).
11:03 Reply: Increasing insert performance of an in-memory db artifact: 219fff0ae3 user: gunter_hick

It may be faster to create indices (apart from the one corresponding to insert order) after populating the tables.

10:58 Reply: help! decimal field multiplication error? artifact: 3827a278eb user: gunter_hick
There is no DECIMAL type in SQLite, thus you are using float arithmetic operations to process "money" (which is never a good idea) instead of integers with appropriate rounding rules.
2021-08-11
10:18 Reply: Test ACID properties SQLITE artifact: 8f494daa0b user: gunter_hick
Check the definition and test the property behind each letter separately.

Atomic: Try making two changes and then either ROLLBACK or COMMIT. Look for not-ROLLBACKED and not-COMMITED changes. Check combinations of rows changed by 1 UPDATE, rows changed by 2 UPDATEs in the same table, different tables or different attached databases.

Consistency: Try inserting/updating rows that violate constraints (remember to enable foreign key constraints). What those are is up to the author of the schema.

Independance: Try performing two transactions on the same row/table/database/across multiple attached databases in independent threads/processes. Check that data reread after both transactions are complete conform to expecations. In SQLite you will have to set a busy timeout to avoid the second transaction failing with "database is locked". Try to start the transactions simultaneously, so the changes occur in both orders.

Durability: Try killing threads/processes/power while transactions are in all states of progress (before/after first/last change, during commit), check to see if all comitted transactions are still present after the journal/WAL file has been processed.
2021-08-03
09:32 Reply: null character sorts greater than 0x7f and less than 0x80 artifact: 2fcca78e65 user: gunter_hick
TEXT means you are supposed to store valid utf-whatever and SQLite will return exactly what you stored, up to and including the first NUL character (in the original meaning of a single byte with value zero). You can also request translation to some other utf encoding based on the premise that what you stored corresponds to the label you provided.

BLOB means you can store anything and SQLite will return exactly what you stored.

If you store something as BLOB and request a cast to TEXT, SQLite will return everything up to and including the first NUL character (see above), or, if there is no NUL character, append one.

You are storing something that is NOT VALID utf-8, but MUTF-8 - probably from Java - in a box labeled utf-8 and then complaining that it does not do what you expect.

If you insist on storing MUTF-8 then use a blob field, although I don't expect it will change the sort order - BLOB fields sort by the byte representation, so since (UTF8 for ASCII 0x7F) 0x7Fnn < 0xC080 < 0xC280 (UTF-8 for ASCII 0x80) the sort order will not change.

Solving the ordering problem will require writing and loading your own collation function.
2021-08-02
06:15 Reply: Feature Request: Allow REAL to serve as rowid by having the B-Tree interpret its byte signature as a 64bit INTEGER. artifact: 774319c618 user: gunter_hick
Being able to compare values is central to BTree. Or any other method of retrieving records by key.

Leaf nodes contain records in sorted order and are linked together in sort order.

Internal nodes contain key values in sort order, separeted by node references to the next level node that pertains to that key range.

An ordering function needs to be tri-valued (return exactly one of "less", "equal" and "greater") and transitive ("a <op> b" and "b <op> c" implies "a <op> c").

Trying to compare fp64 NaN bit patterns will not conform, and pretending that all NaN bit patterns are equal is going to disrupt operations considerably, because inserting a records with rowid of "NaN++" is going to overwrite the record with rowid NaN.
2021-07-30
19:49 Reply: AUTOINCREMENT by value artifact: 4bc3c1241c user: gunter_hick

If you are picky about the exact values of rowids, you should be assigning them yourself.

Also note that AUTOINCREMENT does not guarantee that successive rowids will be adjacent, only monotonously ascending; the increment of a rowid may be committed, even if the record to be inserted is rolled back for any reason, including a constraint violation.

19:41 Reply: Feature Request: Allow REAL to serve as rowid by having the B-Tree interpret its byte signature as a 64bit INTEGER. artifact: 7ae29588f1 user: gunter_hick
The sort order of 64 bit patterns is different if they are interpreted as 2s complement integers (int64) than if they are interpreteed as fp64 values.

E.g. 0.0 and -0.0 compare as equal in fp64, but not as int64. Then you have inf and -inf and a whole bunch of NaN that you cannot compare as fp64 at all, but have different values as int64.

So a function for comparing fp64 by using int64 comparisons would have to check for the special cases first, and a function generating rowids would have to avoid anything beginning with 0xFFF.
2021-07-19
07:01 Reply: Clear shared memory DB and backup problem artifact: b581b6b536 user: gunter_hick
"I assume that should be no memory increase because I backup again same data."

Your assumption is wrong. What you are seeing is called "fragmentation". This is characteristic of every storage scheme for variable size objects in spaces of fixed capacity, even for the stuff in your garage.

The space used will increase more and more slowly until a balance point is reached, where "deleted" space ist being re-used at the same rate as it is being created. This is charaterized by the increase of space usage declining over iterations. I.e. asymptotic space usage.

A true leak would manifest itself in a constant increase of space usage for each iteration. I.e. linear space usage
2021-07-16
05:58 Reply: Should SQLite be able to optimize this query? artifact: 445f8007b4 user: gunter_hick

"Ordinary tables (and probably also virtual tables) are always unordered".

Nope. Virtual tables can implement indexed access. That is what the xBestIndex method is for, which is required to set the "orderByConsumed" Flag if the order of rows is guaranteed to be the requested order.

2021-07-14
12:26 Reply: Help with a query artifact: e5b60e7cf2 user: gunter_hick
Possibly what you are looking for is "GROUP BY aDate", which would return one row for each distinct aDate and do the computation within each group of records.

Also, try "sum(column)/2 as c" if you want half of the groups' sum; the value of v is only known at the end of the group and would be hard to determine during processing of rows.
2021-07-09
08:48 Reply: Custom error messages for required table-valued function parameter artifact: f3c176c621 user: gunter_hick

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.

There is no specific order documented, so even if a heuristic can be applied, it may well become unusable due to a changes in the implementation. the xBestIndex function therefore cannot "wait for a better opportunity".

Consider:

SELECT .. FROM a JOIN b ON (a.x == b.x) WHERE a.x == 5;

The constraint array for table a will contain 2 identical constraints for equality on field x, one from the JOIN condition and one from the WHERE clause.

For table a on the LHS, one of them will not be usable (which one is determined by implemenation details); for table a on the RHS, BOTH will be usable and the BestIndex function cannot tell which is which.

For table b, there is only one entry, which is usable only for the RHS.

Assuming both virtual tables implement indices starting with field x, this will strongly favor table a on the LHS. (search X search vs. scan X search)

Consider the logically equivalent:

SELECT .. FROM a JOIN b ON (a.x == b.x) WHERE b.x == 5;

For the reasons stated above, this will strongly favor table b on the LHS.

SQLite currently does not recognize that a.x == b.x AND a.x == 5 implies b.x == 5, so the optimal comparison (search a X search b vs. search b X search a) is never made. It is up to the programmer to guess which plan is actually superior and formulate the constraints accordingly

In the case of multiple identical usable constraints, I am not sure if it is actually irrelevant which one of them is marked as suitable; nor if marking both/all of them can possibly lead to conflicting constraints in a three or more way join.

2021-07-08
06:35 Reply: Repeat values n times artifact: b8a1c3b882 user: gunter_hick

That now makes sense. Stating the business case "I want to print labels for the items of an order" woudl have provided the necessary context.

2021-07-07
09:10 Reply: Repeat values n times artifact: 4c76418041 user: gunter_hick

What makes you think you need repeated identical records in a table? What is the difference between individual repeated records?

2021-06-21
15:08 Reply: Connection Pool always empty? artifact: ef586152fa user: gunter_hick
Obviously you are using a wrapper and some kind of object oriented language to call the SQLite library. Maybe you could provide a little more information on what exactly you are using.
2021-06-18
06:22 Reply: How to ReadUncommitted for c# .net framework 4.5 artifact: 8a3150c082 user: gunter_hick
That is NOT what I suggested, and it obviously does not change what is going on.

To be more explicit: Be sure to COMMIT the CREATE TABLE statement on the first connection BEFORE attempting to open the second connection.
2021-06-17
14:29 Reply: How to ReadUncommitted for c# .net framework 4.5 artifact: fe2090433d user: gunter_hick
Try creating the table in a separate transaction. I have no idea what the wrapper you are using may be doing transactionwise if you have multiple statements in one command.
2021-06-14
14:17 Reply: xBestIndex constraints: discern between JOIN and regular constraints artifact: 09893e4d3b user: gunter_hick

A "result set limiting" constraint associates a field with a constant (or a variable), whereas a "join constraint" defines a relation between two fields of the tables being joined.

There are three (not including syntax variations) logically equivalent ways to express an equijoin between two tables limited to a certain value of the field used:

  1. T1.a == x AND T1.a == T2.a
  2. T2.a == x AND T1.a == T2.a
  3. T1.a == x AND T2.a == x

Form 1 favors T1 in the outer loop Form 2 favors T2 in the outer loop Form 3 is indifferent

Combining Form 1 with a cross join with T2 in the outer loop makes SQLite check T2.a == x inside the full table scan loop instead of asking for a partial key scan of the same condition.

11:12 Post: xBestIndex constraints: discern between JOIN and regular constraints artifact: 57f4cb4d40 user: gunter_hick
with SQLite 3.24.0 (sorry, no newer version in production here yet) I have two virtual tables corresponding to the following table definitions:

CREATE TABLE privilege (
module_def_no INTEGER,
user_person_id INTEGER,
module_action_no INTEGER,
read_access_allowed INTEGER,
write_access_allowed INTEGER,
UNIQUE (module_def_no, user_person_id, module action no);


CREATE TABLE assmnt(
module_def_no INTEGER,
state_def_no INTEGER,
module_action_no INTEGER,
state_transition_no INTEGER,
entity_id INTEGER,
UNIQUE (module_def_no, module_action_no,state_def_no,state_transition_no));


When preparing the following statement


SELECT ... FROM privilege p, assmnt a
<C1> WHERE a.state_def_no=13
<C2>  AND a.module_def_no=14076
<C3>  AND p.module_def_no=a.module_def_no
<C4>  AND p.user_person_id=1002
<C5>  AND p.module_action_no=a.module_action_no
<C6>  AND p.write_access_allowed = 1
<C7>  AND a.module_action_no NOT IN (12);


the BestIndex function are called thus (with answers shown):

Note: Constraint information is formatted "C" for constraint, field name, operation : argvIndex, "O" if Omit flags is set
Note: Return information is formatted "R" for result, index name, (index number), cost, rows, "Unique" if Unique flag is set

Call #1

with all four fields of privilege table available, i.e. RHS of the JOIN,
Bestindex responds with the fields of the index in index order and result is unique

2021-06-14 11:19:19.215: B  BestIndex for table privilege
2021-06-14 11:19:19.215: Constraints
2021-06-14 11:19:19.216: C  module_def_no                   EQ  : 1 O
2021-06-14 11:19:19.216: C  user_person_id                  EQ  : 2 O
2021-06-14 11:19:19.216: C  module_action_no                EQ  : 3 O
2021-06-14 11:19:19.216: C  write_access_allowed            EQ  : 0
2021-06-14 11:19:19.216: R  privilege_idx    (1) Cost   15.39 Rows 1 Unique

Call #2

with only the fields available to the LHS of the JOIN being usable,
BestIndex responds with a full table scan returning all rows

2021-06-14 11:19:19.216: B  BestIndex for table privilege
2021-06-14 11:19:19.216: Constraints
2021-06-14 11:19:19.216: C (module_def_no                   EQ) : 0
2021-06-14 11:19:19.216: C  user_person_id                  EQ  : 0
2021-06-14 11:19:19.216: C (module_action_no                EQ) : 0
2021-06-14 11:19:19.216: C  write_access_allowed            EQ  : 0
2021-06-14 11:19:19.216: R  privilege_idx    (1) Cost 43061.39 Rows 43046

Call #3

with all 5 fields available, i.e. the RHS of the JOIN,
BestIndex responds with a partial key scan using the first three index fields

     B  BestIndex for table assmnt
     Constraints
<C1> C  state_def_no                    EQ  : 3 O
<C2> C  module_def_no                   EQ  : 1 O
<C7> C  module_action_no                NE  : 0
<C5> C  module_action_no                EQ  : 2 O
<C3> C  module_def_no                   EQ  : 0
     R  assmnt_idx (1) Cost   14.34 Rows 5

Call #4

with only the fields available to the LHS of the JOIN being usable,
BestINdex responds with a partial key scan using the first field

2021-06-14 11:19:19.216: B  BestIndex for table assmnt
2021-06-14 11:19:19.216: Constraints
2021-06-14 11:19:19.216: C  state_def_no                    EQ  : 0
2021-06-14 11:19:19.216: C  module_def_no                   EQ  : 1 O
2021-06-14 11:19:19.216: C  module_action_no                NE  : 0
2021-06-14 11:19:19.216: C (module_action_no                EQ) : 0
2021-06-14 11:19:19.216: C (module_def_no                   EQ) : 0
2021-06-14 11:19:19.216: R  assmnt_idx (1) Cost  137.34 Rows 128

this resulta in the

QUERY PLAN
|--SCAN TABLE assmnt VIRTUAL TABLE INDEX 1:
`--SCAN TABLE privilege VIRTUAL TABLE INDEX 1:

How can the BestIndex function determine if a given constraint comes from a "result set limiting" constraint or from a "join constraint"?
In the case of module_action_no, looking at the operation (EQ vs. NE) is sufficient to ensure that the value passed to the Filter function comes from the JOIN.
In the case of module_def_no, the case is not so clear; indeed, if forced to use the "wrong" query plan, I can see that SQLite is checking p.module_def_no against the constant  (addr 37,10,11) and passing a copy of the constant to the Filter function (addr 12..17) instead of a copy of the field value. 

addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     35    0                    00  NULL
1     VOpen          0     0     0     vtab:DD205A0   00  NULL
2     VOpen          1     0     0     vtab:DD221F0   00  NULL
3     Integer        1     1     0                    00  NULL
4     Integer        0     2     0                    00  NULL
5     VFilter        0     34    1                    00  NULL
6       VColumn        0     1     3                    00  NULL
7       Ne             4     33    3     (BINARY)       53  NULL
8       VColumn        0     4     5                    00  NULL
9       Ne             6     33    5     (BINARY)       54  NULL
10      VColumn        0     0     7                    00  NULL
11      Ne             8     33    7     (BINARY)       53  NULL
12      Integer        14076  11    0                    00  NULL
13      VColumn        0     2     12                   00  NULL
14      Integer        13    13    0                    00  NULL
15      Integer        1     9     0                    00  NULL
16      Integer        3     10    0                    00  NULL
17      VFilter        1     33    9                   00  NULL
18        VColumn        1     0     14                   00  NULL
19        Ne             14    32    7     (BINARY)       53  NULL
20        VColumn        1     2     15                   00  NULL
21        Eq             16    32    15    (BINARY)       53  NULL
22        Concat         15    22    21                   00  NULL
23        Concat         22    21    17                   00  NULL
24        Copy           15    21    0                    00  NULL
25        Function0      0     21    18    hex(1)         01  NULL
26        VColumn        0     2     23                   00  NULL
27        Concat         23    22    21                   00  NULL
28        Concat         22    21    19                   00  NULL
29        Copy           23    21    0                    00  NULL
30        Function0      0     21    20    hex(1)         01  NULL
31        ResultRow      17    4     0                    00  NULL
32      VNext          1     18    0                    00  NULL
33    VNext          0     6     0                    00  NULL
34    Halt           0     0     0                    00  NULL
35    Integer        1002  4     0                    00  NULL
36    Integer        1     6     0                    00  NULL
37    Integer        14076  8     0                    00  NULL
38    Integer        12    16    0                    00  NULL
39    String8        0     22    0     *              00  NULL
40    Goto           0     1     0                    00  NULL
10:28 Reply: create table artifact: 1c472938e8 user: gunter_hick

Try forEIgn instead of forIEgn

2021-06-01
06:54 Reply: What am I doing wrong to get errors when trying to compile amalgamation file in Linux artifact: 39470049fc user: gunter_hick
You are missing the pthread and the dynamic load libraries required by SQLite in the link phase of the second command. Add -lpthread and -ldl
06:22 Reply: Error when disable SQLITE_SimplifyJoin artifact: ff44af3331 user: gunter_hick
SQLite has become picky about the terms in the ON clause of joins some time ago.

The fragment "t0 LEFT JOIN t2 ON t0.c0=t1.c1" does not actually constrain t2 at all, but references an as yet unknown table. I would consider this a semantic error and SQLite is quite right for telling you off on it.

I suspect with optimization turned on, the statement is interpreted as

t0 LEFT JOIN (t2 JOIN t1 ON t1.c0>t2.c0) ON t0.c0=t1.c1

which "shifts" the offending ON clause to a position where it can be checked
2021-05-31
09:41 Reply: Question on internals: Sync between WAL and B Tree artifact: 54743e904b user: gunter_hick
WAL means Write Ahead Log. This is performed on page level, not on record level.

Any pages changed by the current write transaction are written to the WAL file. If the transaction commits, a commit record is written; if it rolls back, the entire transaction is deleted from the WAL file

If multiple transactions modify any given page, there may be several copies of that page corresponding to the states created by each of the committed transactions.

A read transaction will always continue to see the last commited state of any page as it was at the beginning of the read transaction.

Eventually, pages from committed transactions are written back to the db file. The delay also depends on read transactions still requiring outdated versions of modified pages.
05:34 Reply: nested case statement not working artifact: c29aeac335 user: gunter_hick
SQLite has no char(n) type, only text. Any trailing spaces in text field are there by design, not by any actions of SQLite.
2021-05-26
10:01 Reply: SQLite interface to a clangd indexed C/C++ code base artifact: 90620e14db user: gunter_hick
For Multi-Column-Indexes in virtual tables, our xBestIndex function

a) chooses the suported index with the largest number of prefix fields (stopping at the first field with a not-equal constraint), storing the internal index number in the idxNum field

b) assigns argvIndex values based on the order of the fields in the selected index and sets the omit flag

c) assigns both cost and row estimates based in the selectivity of the selected index as determined by the number of constrained index fields

d) encodes the selection in the idxStr field (as per recent clarification, this will need base64 encoding/decoding of the current binary structure).

The VFilter function will:

a) decode the idxNum and idxStr fields

b) generate one or two full key strings

c) set up the range scan on the base data store

The xNext function just steps to the next record until the key string(s) are no longer fulfilled or EOF is reached
09:17 Reply: Any examples of virtual tables that use idxInfo->idxStr? artifact: 2c81de1503 user: gunter_hick
Is there a specific set of circumstances where SQLite chooses to pass a "text copy" of idxStr to vFilter instead of the verbatim pointer? Seems we have been lucky so far...
2021-05-21
08:03 Reply: Column name includes quotes used in RETURNING expr artifact: 948bb2bdc9 user: gunter_hick

It seems that SQLite is trying hard to resolve the expressions to column names in the RETURNING clause.

't' is actually a string literal, whereas "t" is a quoted identifier that sometimes gets interpreted as a string literal, and I suspect you want only t without any quotes.

Please note that column labels not set by an AS clause are generated by SQLite and are subject to change without notice.

2021-05-14
06:03 Edit reply: Discovered bug in xBestIndex of virtual tables artifact: 3c417d5774 user: gunter_hick
In example #2 there is a JOIN and a constraint that resembles a JOIN constraint, so SQLite generates a constraint entry for both sides of foo == bar without further checks.

In example #4 there is no join, so SQLite correctly concludes that checking two fields of the same row for equality does not constitute a checkable constraint.

EDIT: Example #2 should have only 1 constraint, just like #4.
05:54 Reply: Discovered bug in xBestIndex of virtual tables artifact: 7767de74c8 user: gunter_hick
In example #2 there is a JOIN and a constraint that resembles a JOIN constraint, so SQLite generates a constraint entry for both sides of foo == bar without further checks.

In example #4 there is no join, so SQLite correctly concludes that checking two fields of the same row for equaity does not constitute a checkable constraint.
2021-05-10
06:56 Reply: Get column rank from values in selected row artifact: 8ced9b87eb user: gunter_hick
The general problem is "interpolation" and "extrapolation".

You need the two data points left and right of the given weight for the specified age. You then assume that the graph is a straight line in between the two data points and compute the percentile coordinate from the weight coordinate.

E.g. for age 3 and weight 8.75 you find (25,8) and (50,9); since 8.75 is 3/4 the way from 8 to 9, the result should be 3/4 of the way from 25 to 50 which is 43.75

In the case of an outlier, you would need to extrapolate using the two closest entries (which both lie on the same side of the given weight) and pretend the graph is a straight line there too.

E.g. for age 3 and weight 11.25 you find (75,10) and (90,11); since 11.25 is 1/4 past 11, the result should be 1/4 the difference between 75 and 90 higher, yielding 93.75.

This would be "linear interpolation" and "linear extrapolation". Adding more data points improves the accuracy of the result. Or you can take advantage of the fact that you are dealing with statistics, which means that the age/weight distribution follows the classical "bell curve". The difference is that it makes the math of interpolation more difficult, but yields better results for a smaller number of input points (median and standard deviation suffice).
2021-05-07
11:55 Reply: Get column rank from values in selected row artifact: 97c272ef42 user: gunter_hick
That would be "spreadsheet thinking". Doable but arduous. Try some "database thinking" instead. There are good reasons for normalizing tables in databases. They just work so much better when used as designed.
11:51 Reply: Get column rank from values in selected row artifact: 9ec3fe43c1 user: gunter_hick
What is preventing you from loading the non-normalized data into normalized tables? That would be the classic job of the "input" phase: convert data to the form best suited to the actual processing.

What if some day the input format is changed to a different number of precentiles or the borders change? You would have to rewrite all of the queries, as opposed to adjusting only the input conversion.
06:04 Edit reply: Get column rank from values in selected row artifact: f975ba3db7 user: gunter_hick
The reason for your problem is that you are using column names as data.

Your centiles need to go into a separate table.

CREATE TABLE centiles (ID INTEGER PRIMARY KEY, cent TEXT);
CREATE TABLE weights  (ID INTEGER PRIMARY KEY, age INTEGER, WEIGHT INTEGER, CENT_ID INTEGER REFERENCES centiles(ID), UNIQUE(age,weight));

SELECT c.cent FROM weights w JOIN centiles c ON (c.ID = w.cent_id) WHERE w.age = 3 and w.weight = 9;
06:02 Reply: Get column rank from values in selected row artifact: 56152d5471 user: gunter_hick
The reason for your problem is that you are using column names as data.

Your centiles need to go into a separate table.

CREATE TABLE centiles (ID INTEGER PRIMARY KEY, cent TEXT);
CREATE TABLE weights  (ID INTEGER PRAMIRY KEY, age INTEGER, WEIGHT INTEGER, CENT_ID INTEGER REFERENCES centiles(ID), UNIQUE(age,weight));

SELECT c.cent FROM weights w JOIN centiles c ON (c.ID = w.cent_id) WHERE w.age = 3 and w.weight = 9;
2021-05-06
09:47 Reply: order of INSERT affects the output artifact: 953c5d1055 user: gunter_hick
Don't do processing that requires a specific visitation order or output order without making sure that the required order is actually guaranteed. Which usually means adding an ORDER BY clause to uniquely determine said order.
2021-05-04
06:32 Reply: cli: .mode insert - use current table name artifact: e761fd095a user: gunter_hick
What if the SELECT is a join? Which table should be chosen?

I find it very practical when performing an INSERT INTO ... SELECT where source and destination tables reside on different machines or even distinct DBMS.

.mode insert oracletable
.out ORA_IMPORT.SQL
SELECT ...


sqlplus ... <ORA_IMPORT.SQL
2021-05-03
07:27 Reply: Is there a way to mark an index 'explicit' only? artifact: 37ddbd0802 user: gunter_hick
See also https://sqlite.org/queryplanner-ng.html Section 5
07:16 Reply: Is there a way to mark an index 'explicit' only? artifact: d791eb1678 user: gunter_hick
See https://sqlite.org/optoverview.html

- Run ANALYZE to create STAT table entries.
- Fudge STAT table entries to convince the QP
- Use CROSS JOIN to force table order
- Use unary + to disqualify columns
2021-04-29
09:34 Reply: bug report: an assert() in zipfile.c that may fail artifact: cc0318451d user: gunter_hick
I think it will be handled in a timely manner. There is about 20 times the age of the universe to resolve this problem.

What is the reason for creating an archive with the timestamp so far into the future?
2021-04-27
15:39 Reply: Database is locked when "upgrading" from read to write artifact: 8b3452e6de user: gunter_hick

AFAIK (pertaining to non-WAL mode), one cannot upgrade a read transaction to a write transaction until all other read transactions have finished.

In your case I suspect the following:

Connection A starts as a reader and does some processing.

Connection B starts as a reader and wants to upgrade to a writer; it needs to wait for connectionA to finish.

Connection A now wants to upgrade too. This is a deadlock, because it would need connection B to finish first - which is waitng for connection A. Since no progress can be made, connection A is notfied of the deadlock.

2021-04-12
11:13 Reply: SQLite3 extensions: extension-functions.c in Windows doesn't load artifact: 97c23b4cef user: gunter_hick
Are you sure that the dll file is in the working directory of the sqlite shell? 

Are you sure that there is an entrypoint with the name sqlite is using to initialize the extension?

Are you sure you are running the same sqlite.dll in both cases? Or rather a custom sqlite.dll with "included" uuid.c in one case and whatever windows regards as sqlite.dll in the other?

If you need to have a specially built sqlite library, you should statically link it to your application.
10:33 Edit reply: UNION Sorts on First Field artifact: 06c02cf9a3 user: gunter_hick
Not "any time", only when the visitation order changes. UNION ALL evaluates/groups from left to right.

You could use a "sort order" table

CREATE TABLE sort_order (Id INTEGER, name TEXT UNIQUE);
INSERT INTO sort_order VALUES (1, 'Minimum'),(2, 'Maximum'), (3, 'Today');

and then SELECT name FROM sort_order ORDER BY Id;
10:32 Reply: UNION Sorts on First Field artifact: 171c6dd697 user: gunter_hick
Not "any time", only when the visitation order changes. UNION ALL groups from left to right.

You could use a "sort order" table

CREATE TABLE sort_order (Id INTEGER, name TEXT UNIQUE);
INSERT INTO sort_order VALUES (1, 'Minimum'),(2, 'Maximum'), (3, 'Today');

and then SELECT name FROM sort_order ORDER BY Id;
09:54 Reply: UNION Sorts on First Field artifact: 1e4f183293 user: gunter_hick
UNION requires that duplicate records be omitted, which implies a sort. UNION ALL does not require a sort and will return records in visitation order.

It is wrong to assume a specific order of records returned by a SELECT. If you require a specific order, you must provide an ORDER BY clause.
More ↓