Re: [sqlite] speeding up an FTS5 search with a JOIN

2020-03-09 Thread Dan Kennedy
any different from your join query. What does EXPLAIN QUERY PLAN say for the version that takes a few hundred ms? Dan. You might notice that in my Q2 above I MATCHed for ‘bar’ instead of ‘foo’. That is because ‘bar’ returns fewer rows than ‘foo’ does. The problem remains when there are

Re: [sqlite] speeding up an FTS5 search with a JOIN

2020-03-07 Thread Dan Kennedy
;t1" with t1.deleted set to something other than 0? What does:   SELECT count(*) AS num FROM t1 JOIN vt1 ON t1.t1Id=vt1.t1Id WHERE vt1 MATCH 'foo'; return? Dan. Run Time : real 26.218 user 1.396376 sys 5.413630 ``` The answer is correct but the query takes more than 26 seconds

Re: [sqlite] Status of LSM1 extension

2020-03-05 Thread Dan Kennedy
is not impossible. I do intend to fix any reported bugs though. Dan. Thanks, --DD [1] [2] [3] [4]

Re: [sqlite] Issues with sqlite3session_attach(conn, NULL) w/ duplicate table in temp

2020-02-27 Thread Dan Kennedy
"*" or "main.*" or "main." avoids the issue. I think that's just because sqlite3session_attach() doesn't understand any of those shorthands. If you pass "*", it searches for a table named "*", not a table that matches the glob pattern "*&

Re: [sqlite] Trigger name missing

2020-02-26 Thread Dan Kennedy
he following creates a "BEFORE" trigger named "AFTER". Does that explain things?   CREATE TRIGGER AFTER INSERT ON t1 BEGIN     ...   END; I find I fall into this trap about once every 18 months... Dan. Not critical but annoying i

Re: [sqlite] wrong number of arguments to function rank()

2020-02-06 Thread Dan Kennedy
ach column in the table. So:   SELECT rank(matchinfo(event_search_test), 1.0, 1.0) AS rank, room_id... Dan. I'm running sqlite3 3.27.2 on a Debian 10 machine. I looked at and I can see that the error is somehow relate

Re: [sqlite] Virtual table OR constraint bug

2020-01-29 Thread Dan Kennedy
:// None of us saw your post last month. We think it must have been filtered as spam by gmail. Sorry about that. Dan. Thanks, Lalit On Thu, 2 Jan 2020 at 15:13, Lalit Maganti wrote: Hi all, I believe that I have found a bug in the virtual table bytecode

Re: [sqlite] Implementing xLock/xUnlock VFS methods...

2020-01-08 Thread Dan Kennedy
On 8/1/63 22:41, J Decker wrote: On Wed, Jan 8, 2020 at 7:10 AM Dan Kennedy wrote: On 8/1/63 20:29, J Decker wrote: The documentation isn't very clear on what the intent of an xUnlock( SQLITE_LOCK_NONE ) is intended to do. Is it unlock everything? Is it the same as remove a shared lock

Re: [sqlite] Implementing xLock/xUnlock VFS methods...

2020-01-08 Thread Dan Kennedy
(fd, SQLITE_LOCK_SHARED) should fall back to a SHARED lock from RESERVED/PENDING/EXCLUSIVE. Dan. The first few operations are xLock( SQLITE_LOCK_SHARED ) followed by xUnlock(SQLITE_LOCK_NONE)... where

Re: [sqlite] A hang in Sqlite

2020-01-05 Thread Dan Kennedy
_with.html Thanks for all the work you've been doing on SQLite! Dan. Yongheng & Rui ___ sqlite-users mailing list

Re: [sqlite] Heap Use After Free In sqlite.

2019-12-27 Thread Dan Kennedy
ile triggering an assert in the delevelopment code. Thanks for this. Looks like the asan error was fixed here: The assert() failure is now fixed here: Dan. Yongheng &a

Re: [sqlite] Sanitising user input for FTS5 MATCH parameter

2019-12-21 Thread Dan Kennedy
gle phrase, enclose it in double-quotes, doubling any embedded " characters SQL style. Or, if you wanted the input treated as a list of terms separated by implicit AND, split the input on whitespace and then enclose each term in double-quotes. Details here:

Re: [sqlite] What is the C language standard to which sqlite conforms ?

2019-11-24 Thread Dan Kennedy
On 24/11/62 06:18, Dennis Clarke wrote: On 11/23/19 4:46 PM, Dan Kennedy wrote: Some follow up and thank you all for looking at this. Using this mornings trunk/current/head I do see the tests running well  with these little exceptions : boe13$ pwd /opt/bw/build

Re: [sqlite] Concurrency Question

2019-11-24 Thread Dan Kennedy
On 24/11/62 00:05, Jens Alfke wrote: On Nov 23, 2019, at 7:17 AM, Dan Kennedy wrote: This should only happen if you are using shared-cache mode. Don't use shared-cache mode. Shared-cache mode also breaks Isolation between connections — during a transaction, other connections will see

Re: [sqlite] What is the C language standard to which sqlite conforms ?

2019-11-23 Thread Dan Kennedy
Some follow up and thank you all for looking at this. Using this mornings trunk/current/head I do see the tests running well  with these little exceptions : boe13$ pwd /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.006 ... build clean as usual :-) tests run nicely now

Re: [sqlite] Concurrency Question

2019-11-23 Thread Dan Kennedy
to another table? Is this the expected behavior or am I doing something stupid in my code. And if so, what to check? This should only happen if you are using shared-cache mode. Don't use shared-cache mode. Dan. ___ sqlite-users mailing list

Re: [sqlite] What is the C language standard to which sqlite conforms ?

2019-11-21 Thread Dan Kennedy
struct pointed to by p; the third line reads the pVfs field of the struct, which is now NULL, and then calls free() on that NULL pointer, which is a no-op. The net result is to leak the heap block pointed to by p->pVfs. Shouldn't the second and third lines be swapped? They should indeed. Tha

Re: [sqlite] [Makefile:1256: tcltest] Segmentation fault (core dumped)

2019-11-19 Thread Dan Kennedy
ut.txt" created by the [make tcltest] command, we should be able to figure out what is going on. Thanks, Dan. I feel as if I am going in circles here however the codebase seems to compile fine however the testsuite blows up in marvelous ways : . . . Time: walshared.test 26 ms # WARNING: This ne

Re: [sqlite] WAL2 mode

2019-11-12 Thread Dan Kennedy
On 12/11/62 19:00, Simon Slavin wrote: On 12 Nov 2019, at 10:06am, Dan Kennedy wrote: This branch might interest you: " In wal2 mode, the system uses two wal files instead of one. The files are named "-wal&qu

Re: [sqlite] Custom window functions vs builtin

2019-11-12 Thread Dan Kennedy
indow type. You can't implement percent_rank(), cume_dist() or ntile() without knowing the number of rows in the partition before returning any values, so that requires special handling as well. There are probably other things too... Dan. ___ sq

Re: [sqlite] proposal for write-lock on "commit" rather than "begin transaction"

2019-11-12 Thread Dan Kennedy
h might interest you: The "BEGIN CONCURRENT" idea is that two connections may concurrently have independent write transactions based on optimistic read/write page locking. Dan. ~Brannon

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

2019-11-11 Thread Dan Kennedy
e : Time: walshared.test 24 ms # WARNING: This next test takes around 12 seconds gmake: *** [Makefile:1256: tcltest] Segmentation fault (core dumped) This is almost certainly an issue with the test scripts, not the library. Can you post the last 100 lines or so of the file "test-out.txt&

Re: [sqlite] Prepared statements in FTS MATCH queries

2019-11-01 Thread Dan Kennedy
On 1/11/62 19:15, mailing lists wrote: Thanks. Is there a difference between these statements with respect results, performance etc. or are both statements describe the same thing? They're the same in all important respects. Dan. a) SELECT * FROM NamesFTS WHERE FTS MATCH

Re: [sqlite] Prepared statements in FTS MATCH queries

2019-11-01 Thread Dan Kennedy
On 1/11/62 03:03, mailing lists wrote: Hi Dan, I did not know that. What was the reason that it did not work before 3.30? The implementation of the xBestIndex method of fts3/4, and fts5 prior to 3.30.0, only allowed a single MATCH constraint to be processed and passed through to xFilter

Re: [sqlite] Prepared statements in FTS MATCH queries

2019-10-31 Thread Dan Kennedy
) Is there any possibility to use prepared statements for FTS tables with an AND condition? I like to prevent code injection. As of 3.30.0, should work with FTS5. Dan. Regards, Hartwig ___ sqlite-users mailing list

Re: [sqlite] Roadmap?

2019-10-20 Thread Dan Kennedy
estions. Which missing feature or features causes you the most bother? Dan. ___ sqlite-users mailing list

Re: [sqlite] Count Rows Inserted into FTS5 Index w/ External Content Table

2019-10-20 Thread Dan Kennedy
QLite. And if you can, build SQLite directly into the app (almost always a good idea), don't use the system version. Good luck, Dan. Thanks! Ben ___ sqlite-users mailing list sqlite-users@mailinglists

Re: [sqlite] An inconsistency between sqlite implementation and documentation of zlib API deflate

2019-10-11 Thread Dan Kennedy
e(). Still, to be correct, it's now changed to use deflateBound() to size the buffer here: Dan. The description of zlib API deflate is shown as bellow: If the parameter flush is set to Z_FINISH, pending input is processed, pending output

Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected

2019-09-21 Thread Dan Kennedy
the GroupBy sortOrder field if expressions are found equal. I see dan modifies the sortFlag. That sounds equivalent to me. The sortOrder/sortFlag thing is probably just because you patched the last release (3.29.0) or earlier. The field has changed names since then. Dan. Would be interesting to know

Re: [sqlite] Undo sqlite3_snapshot_open?

2019-09-19 Thread Dan Kennedy
ansaction, the new transaction accesses the latest database snapshot - just as if you had never used sqlite3_snapshot_open() with the connection at all. Dan. Thanks is advance, Gwendal Roué ___ sqlite-users mailing list sqlite-user

Re: [sqlite] Bug: WINDOW clause within a CTE

2019-09-15 Thread Dan Kennedy
sed, so this doesn't produce an error. There are few other scenarios SQLite does this too. The statement "SELECT (0 AND fake_column);", for example. Dan. ___ sqlite-users mailing list

Re: [sqlite] Window functions

2019-09-04 Thread Dan Kennedy
licate territoryid/customerid values, so running the query without the GROUP BY and replacing "sum(subtotal)" with "subtotal" would produce the same results. Dan -Original Message- From: sqlite-users On Behalf Of Simon Slavin Sent: Wednesday, September 04, 2019

Re: [sqlite] Window functions

2019-09-04 Thread Dan Kennedy
rame" differently to the way we do. Not that it matters too much, as the term only occurs once in each of the two blog entries anyway. Dan. ___ sqlite-users mailing list

Re: [sqlite] Query planner regression with FTS4: slower path is chosen

2019-09-04 Thread Dan Kennedy
to CROSS JOIN works to force SQLite to pick the plan you want. FTS5 does a little better with the query, but only because it runs faster - it still picks the slow plan. There might be room to improve this in FTS5, but probably not for FTS3/4, which are now focused very much on backwards

Re: [sqlite] Documentation misunderstanding or bug (FTS3 and "^" character)?

2019-08-30 Thread Dan Kennedy
- it's faster, has more features, is better tested and the query syntax is properly defined. Dan. Because it is regarded as a diacritical character? PS: I have to admit that I have overlooked the comment that '^' works only for FTS4 tables. Regards, Hardy Am

Re: [sqlite] Documentation misunderstanding or bug (FTS3 and "^" character)?

2019-08-30 Thread Dan Kennedy
; to "FTS4" and it will likely work. Dan. On 30/8/62 16:31, mailing lists wrote: Hi, I could not find an example showing a result in the documentation, therefore I created one by myself: CREATE VIRTUAL TABLE myData USING FTS3(content); INSERT INTO myData 'alpha beta'; 1) SELEC

Re: [sqlite] Inverted changesets and UNIQUE constraints

2019-08-26 Thread Dan Kennedy
here does exist an order in which the changes can be successfully applied without hitting constraints - it eventually succeeds in applying the entire changeset. But, as the sessions module works through the normal SQL interface, each time it hits an intermittent constraint, an error messag

Re: [sqlite] [FTS5] Potential table name escape issue with ORDER BY rank

2019-08-20 Thread Dan Kennedy
": syntax error Thanks for the thorough bug report. Now fixed here: Dan. As the comment indicates, this produces the message “near ‘Table’: syntax error”. My armchair debugging skills are telling me that it seems to be an issue with the

Re: [sqlite] escaping search terms in queries with bind params

2019-08-07 Thread Dan Kennedy
e part of fts5 barewords before passing the query to fts5: Or you could try the query as input first, then strip out the special characters and try again only if the first attempt failed - so that users could use advanced syntax if they get

Re: [sqlite] 3.29.0 .recover command

2019-08-06 Thread Dan Kennedy
quot; to the output to avoid this. .recover now does this too: Dan. sqlite> .q While doing the same kind of work around .dump worked nicely: sqlite> .once systemd.sql sqlite> .dump sqlite3 dump.db sqlite> .read systemd.sql sqlite

Re: [sqlite] mode insert dumps

2019-08-02 Thread Dan Kennedy
lso the columns I selected listed? I would like something like: INSERT INTO catadaemo( mude_anno, mude_numero ) VALUES(2019,1161); Try doing:   sqlite3> .headers on along with the ".mode insert" command. Dan. ___ sqlite-users mailing list sq

Re: [sqlite] Large database backup

2019-08-01 Thread Dan Kennedy
the size of the cache used by the destination db writing should begin in an earlier sqlite3_backup_step() call. Dan. Also, I'm open to other suggestions. I was contemplating to just copy the database file directly while WAL checkpoints are not done but I read some comments that suggested

Re: [sqlite] Bug report: crash when close blob handle after close_v2 db

2019-07-15 Thread Dan Kennedy
it with the latest SQLite from fossil or else post it inline here so that we can run it, that would be very helpful. Thanks, Dan. Basically, the flow is: 1. Open in-memory db A (we don't do anything about it). 2. Open db B from file test.db 3. Create a blob handle from B 4. close_v2 A 5. close_v2

Re: [sqlite] Safe saving of in-memory db to disk file

2019-07-15 Thread Dan Kennedy
using an SQLite transaction. So if your app or the system crashes before the backup is complete, the transaction is rolled back following recovery. Dan. Do you have any recommendation for saving the inmemory db in a safe way? (by "safe" I mean I don't want the to lose both the o

Re: [sqlite] Link errors with SQLITE_OMIT_VIRTUALTABLE

2019-07-15 Thread Dan Kennedy
of sqlite3.c? If you build from the full source package with SQLITE_OMIT_VIRTUALTABLE defined things work as is. And you will save a bit more code space than just building sqlite3.c with the same option. Dan. Thanks, - Orgad

Re: [sqlite] sqlite3_close() drops wal and shm files despite of other processes holding DB open

2019-07-12 Thread Dan Kennedy
tements in the app? "PRAGMA locking_mode=none" for example? Or are you opening/closing the database file directly at all (separate from SQLite), causing SQLite's locks to be dropped by this POSIX quirk?

Re: [sqlite] Incorrect results after upgrading to 3.28.0

2019-06-26 Thread Dan Kennedy
Thanks for reporting this one. It is a bug. Now fixed here: Ticket: Dan. > We recently updated from version 3.26.0 to version 3.28.0. Now we're seeing > different (incorrect) results for the following

Re: [sqlite] CVE-2019-5018 patch

2019-06-18 Thread Dan Kennedy
/1e16d3e8fc60d39c Dan. ___ sqlite-users mailing list

Re: [sqlite] json path escaping with double quote

2019-06-17 Thread Dan Kennedy
/45bfcb88e71451a6 Dan. sqlite> CREATE TABLE test (data TEXT); sqlite> INSERT INTO test (data) VALUES ('{}'); sqlite> UPDATE test SET data = json_set(data, '$."equity_spot[at_andr]"', json(3 2)); sqlite> SELECT json_each.key, json_each.value FROM test, json_each(

Re: [sqlite] SQLITE_MAX_COLUMN should be a runtime knob for Machine Learning

2019-06-16 Thread Dan Kaminsky
facebookresearch/InferSent On Sat, Jun 15, 2019 at 6:42 AM Dan Kaminsky wrote: > Sqlite3 has something of a normative declaration in its source code: > > * > ** This is the maximum number of > ** > *** Columns in a table > *** Columns in an index > *** Colu

[sqlite] SQLITE_MAX_COLUMN should be a runtime knob for Machine Learning

2019-06-15 Thread Dan Kaminsky
these user scenarios. I spent quite a bit of time hacking large column support into a working Python pipeline, and I'd prefer never to run that in production. Converting this compile time variable into a runtime knob would be appreciated. --Dan [1] [2] htt

Re: [sqlite] demoRead() function in test_demovfs.c not consistent with documentation

2019-06-15 Thread Dan Kennedy
. So I guess new VFS implementations should zero memory when returning SQLITE_IOERR_SHORT_READ. Regards, Dan. ___ sqlite-users mailing list

Re: [sqlite] configure failure for aarch64

2019-06-07 Thread Dan Kennedy
? Now updated here: Dan. ___ sqlite-users mailing list

Re: [sqlite] Compile time error when compiling extension shathree.c together with sqlite3.c for Android

2019-06-07 Thread Dan Kennedy
/3ec7371161 Dan. ^ As far as I can tell header file termbits.h is pulled in as a side effect of including (which is one of the "standard include files" used in sqlite3.c). As a workaround I could add a preprocessor check #ifdef B0 #undef B0 #endif in front of the s

Re: [sqlite] Bug in sqlite3.c

2019-06-04 Thread Dan Kennedy
On 4/6/62 12:11, bhandari_nikhil wrote: Thanks Dan. I had checked the database integrity using the following command: sqlite3 myfile.db "PRAGMA integrity_check;" Try "INSERT INTO ft(ft) VALUES('integrity-check')", where "ft" is the name of the fts5 table

Re: [sqlite] Bug in sqlite3.c

2019-06-03 Thread Dan Kennedy
ttps:// Have you demonstrated the crash with the latest version, or just eyeballed the code? Cheers then, Dan. Regards Nikhil Bhandari -- Sent from: ___ sqlite-users

Re: [sqlite] Session extension questions

2019-06-03 Thread Dan Kennedy
)? As each row is inserted/updated/deleted. Before or after the operation depending on whether the trigger is declared as BEFORE or AFTER. Dan. ___ sqlite-users mailing list

Re: [sqlite] LSM Extension Performance (from sqlite3 tree)

2019-05-16 Thread Dan Kennedy
the memory leak is the code you are using. If we can actually run it and reproduce the memory leak, so much the better. Dan. ___ sqlite-users mailing list

Re: [sqlite] Partial NOT NULL index malfunctions with IS NOT/!=

2019-05-11 Thread Dan Kennedy
; -- returns no row Thanks for this. Should be fixed now. Ticket here: Dan. If the index is created, no rows are fetched. Without the index, the NULL row is returned. I think that this looks like a rather general pattern that could be used in practice

Re: [sqlite] ALTER TABLE fails when renaming an INTEGER PRIMARY KEY column in a WITHOUT ROWID table

2019-05-06 Thread Dan Kennedy
re: Dan. However, specifying c0 as the PRIMARY KEY in the column definition rather than in a table constraint seems to work: CREATE TABLE t0 (c0 INTEGER PRIMARY KEY) WITHOUT ROWID; ALTER TABLE t0 RENAME COLUMN c0 TO c1; Be

Re: [sqlite] .expert output help

2019-04-26 Thread Dan Kennedy
r than (null). That means it couldn't even come up with anything to try - you already have indexes for all WHERE constraints an ORDER/GROUP BY terms in the query. It should probably say "(no candidates found)", or something along those lines. Dan.

Re: [sqlite] .expert output help

2019-04-26 Thread Dan Kennedy
to answer questions regarding specific queries and allows people to recreate your experiment themselves if they wish to investigate further. Dan. Thanks for your knowledge sharing. josé ___ sqlite-users mailing list sqlite-users@mailinglists.s

Re: [sqlite] Will ROWID be required to be unique in contentless Full text search

2019-04-25 Thread Dan Kennedy
of column 'ftiVal'. If you set detail=none, then FTS5 won't store any offset information and you won't have a problem. Like columnsize=0, this option saves disk space too. Good luck then, Dan. ___ sqlite-users mailing list sqlite-users@mailinglis

Re: [sqlite] Query Regression IN and Virtual Tables - followup

2019-04-05 Thread Dan Kennedy
he shell tool and it mysteriously omitted instruction 16 from the output? Are there any other problems? Does the SQL statement return the correct results if you execute it without the EXPLAIN? Dan. explain select lsn from atx_txlog where period_no between 7300 and 7313 and event_Type

Re: [sqlite] Row locking sqlite3

2019-03-29 Thread Dan Kennedy
is going on. Dan. ___ sqlite-users mailing list

Re: [sqlite] is this possible

2019-03-29 Thread Dan Kennedy
:06:06   Enter ".help" for usage hints.   sqlite> SELECT * FROM t;   sqlite> SELECT * FROM s;   sqlite> SELECT * FROM v;   Error: no such column: q Dan. ___ sqlite-users mailing list http://mail

Re: [sqlite] Query Regression IN and Virtual Tables

2019-03-29 Thread Dan Kennedy
plan to use. Dan. SQLite asks for (key1,'='), (key2,'>='), (key2,'<=') and xBestIndex accepts all 3 constraints yielding query plan - materialize IN as anonymous ephemeral table - search vt using index 1 (key1 = ? AND key2 >= ? AND key2 <= ?) - retrieve column attr1

Re: [sqlite] WAL grows without bounds, short concurrent writes & reads

2019-03-29 Thread Dan Kennedy
There's code here, if you want to experiment with it: Docs: Dan. On 29/3/62 01:33, Florian Uekermann wrote: Hi, A very simple reproducer bash script using the sqlite3 CLI is appended at the end

Re: [sqlite] FTS5 Transaction Leads to OOB Read

2019-03-19 Thread Dan Kennedy
That's the error I got too. Now fixed here: Dan. On 19/3/62 04:22, Dominique Pellé wrote: Chu wrote: The code: ``` CREATE VIRTUAL TABLE t1 USING fts5(content); BEGIN; INSERT INTO t1 (content) VALUES('

Re: [sqlite] FTS5 Transaction Leads to NULL Pointer

2019-03-19 Thread Dan Kennedy
'); SELECT * FROM t1 WHERE content MATCH ''; END; `` Thanks very much for isolating and reporting this problem, and the other one. Now fixed here: Dan. As you can see, it creates a virtual table with fts5, and run a transaction

Re: [sqlite] picking random subset of rows

2019-03-18 Thread Dan Kennedy
andom()" it might return a different result every time. Your words suggest that you are hoping it will be run once for each different value of "da.area", with different results each time. But it will not. Dan. -- In the real query, this order by is required to see a diffe

Re: [sqlite] Segmentation fault running a query

2019-03-06 Thread Dan Kennedy
t; command in the shell tool? Thanks, Dan. And if it does still crash, please provide the database schema to help with debugging. -Original Message- From: sqlite-users [mailto:sqlite-users-bounces at] On Behalf Of Simon Slavin Sent: Tuesday, March 05, 20

Re: [sqlite] Vtab scalar function overloading in aggregate queries

2019-02-27 Thread Dan Kennedy
the built-in ones). Dan. ___ sqlite-users mailing list

Re: [sqlite] Disk I/O errors

2019-02-23 Thread Dan Kennedy
On 24/2/62 00:55, Tim Streater wrote: (sorry for the duplicate - vibrating finger). I have a hosted web site using the SQLite functions from PHP. The page where PHP is used was failing, and on investigation this is because an SQLite function called from within PHP is now returning: Code: 10

Re: [sqlite] Cannot Modify Table if Part of a View

2019-02-22 Thread Dan Kennedy
ragma.html#pragma_legacy_alter_table Dan. The link to the full issue is here: Martin has followed the instructions here ( which raises the above error. (link to comment: https://

Re: [sqlite] Typo in FTS5 documentation

2019-02-18 Thread Dan Kennedy
built and uploaded. Dan. ___ sqlite-users mailing list

Re: [sqlite] Was there an announcement of 3.27?

2019-02-11 Thread Dan Kennedy
ly a bug, but cannot be fixed without creating backwards compatibility problems. If this option is set to "2", then diacritics are correctly removed from all Latin characters. Dan. I also use the opportunity to report a few typos in - the second

Re: [sqlite] SQLITE_DBCONFIG_DEFENSIVE documentation looks confusing

2019-01-29 Thread Dan Kennedy
connection is in DEFENSIVE mode or not following the call.   int bDefensive;   sqlite3_db_config(db, SQLITE_DBCONFIG_DEFENSIVE, 1, );   if( bDefensive ){     printf("connection in defensive mode!\n");   }else{     printf("connection not in defensive mode :(\n");   } Dan.

Re: [sqlite] Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"

2019-01-07 Thread Dan Kennedy
code) instead of one "INSERT OR REPLACE" statement. Am I doing something wrong here, or is this a bug? I think it was a bug. Thanks for reporting it. Now fixed here: Dan. Is there s

Re: [sqlite] Bug in 3.25.2 (RECURSIVE CTE + window function)

2018-12-31 Thread Dan Kennedy
ically there are window functions within a recursive sub-query, they don't operate on the recursively generated dataset (they're in a subquery) so I don't think the restriction applies. Dan. WITH RECURSIVE chain(link1, link2, n) AS (VALUES ('hello', 'world', 1), ('world', 'hello', 1)), mark

Re: [sqlite] Window functions in sqlite 3.26.0

2018-12-23 Thread Dan Kennedy
w function. They are in 3.26.0. Try "row_number", with an underscore. Or, if that's not the problem, please post the failing SQL statement. Thanks, Dan. The documentation only mentions that window functions were added in version 3.25.0, and does not give any indication as to whet

Re: [sqlite] "make test" fails on Mageia Linux v7 x86-64

2018-12-19 Thread Dan Kennedy
help? What is your Tcl version (run the "testfixture" binary and type "set tcl_patchLevel" to find out)? Upgrading Tcl will most likely fix this. Dan. ___ sqlite-users mailing list http://mai

Re: [sqlite] Expression-index bug with OR query

2018-12-15 Thread Dan Kennedy Dan. The transcript below comes from the SQLite 3.24 shell. ### Populate database: sqlite> create table docs (a int, b int); sqlite> insert into docs (a, b) values (2, 4); sqlite> insert into docs (a, b) values (3, 9); ### Simple

Re: [sqlite] Minor glitch in FTS5 docs

2018-12-14 Thread Dan Kennedy
elieve that the first sentence of item 2 should be changed to something like “By adding multiple synonyms for a single term while parsing the FTS query”. Thanks. Now fixed here: Dan. —Jens ___ sqlite-users m

Re: [sqlite] Affinity of expression indexes

2018-12-13 Thread Dan Kennedy
x could not be used, as the affinity of the comparison would be TEXT, not "no affinity" or BLOB. Dan. —Jens ___ sqlite-users mailing list

Re: [sqlite] How can custom tokenizer tell it's parsing a search string?

2018-12-12 Thread Dan Kennedy
he" in the index. And it won't be there if the tokenizer used for parsing documents stripped it out. I think your best options might be to switch to FTS5 or to write a tokenizer smart enough to remove the AND or other syntax tokens when required. Dan. The best workaround I can think o

Re: [sqlite] Failure to rename table in 3.25 and 3.26

2018-12-05 Thread Dan Kennedy
chema were not being updated correctly. Now fixed here: Dan. The following produces the error in 3.26; a much simpler trigger does not produce the error. |Create Table LOG_ENTRY(|| ||LOG_ENTRY_ID int primary key,|| ||TIME_START Long,|| ||

Re: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

2018-11-29 Thread Dan Kennedy
with: PRAGMA foreign_keys = 0; BEGIN; if( [PRAGMA foreign_key_check]=="ok" ) COMMIT else ROLLBACK; PRAGMA foreign_keys = 1; Dan. Thanks in advance, Máté szmate1618 ezt írta (időpont: 2018. nov. 23., P, 17:21): "PRAGMA foreign_keys = ?" is a property of

Re: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

2018-11-23 Thread Dan Kennedy
Affinity changes are applied before data is written to the database. As you say though, implementing ALTER TABLE to change the type would be easier if they were not. Dan. - Original Message ----- From: Dan Kennedy To: Sent: Friday, November 23, 2018

Re: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

2018-11-23 Thread Dan Kennedy
after). "PRAGMA foreign_keys = ?" is a property of the connection only, not the database file. So what advantage would there be in including the PRAGMA statements in the body of a transaction? Dan. I'd like to use deferred foreign keys instead. I have 3 queries, one of th

Re: [sqlite] 'Database table is locked' error with libgpkg and SQLite >= 3.24.0

2018-11-21 Thread Dan Kennedy
in this thread: Dan. To reproduce the issue: - Clone the repository at - Configure with tests and RTREE: cmake -DCMAKE_BUILD_TYPE:STRING=Release -DGPKG_TEST:BOOL

Re: [sqlite] Bug: SQLite shell does not build on Windows with SQLITE_OMIT_COMPLETE

2018-11-16 Thread Dan Kennedy
iling list Thanks for reporting this. Should now be fixed here: Dan. ___ sqlite-users mailing l

Re: [sqlite] Built-in Window Functions and Filter

2018-11-15 Thread Dan Kennedy
aggregate functions? One real reason is because postgres (and I think everybody else at the moment) disallows them. Another is that it's not always clear what FILTER expressions should do for built-in window functions. How does it affect row_number(), for example? Dan. Thanks, Do

Re: [sqlite] Documentation of SQLite

2018-11-10 Thread Dan Kennedy
On 11/10/2018 05:44 PM, Patrik Nilsson wrote: Dear All, Where can I find the downloadable HTML documentation? Not so long ago I could download it on the download page. It was forgotten for the 3.25.3 release. It's back up now. Dan. ___ sqlite

Re: [sqlite] [EXTERNAL] found a glitch in ALTER TABLE RENAME (3.25.x)

2018-11-09 Thread Dan Kennedy
hings. Which means doing whatever was done in legacy processing of the ALTER commands including the handling of foreign key constraints. I think this is a good answer. Now implemented here: Dan.

Re: [sqlite] race condition?

2018-10-29 Thread Dan Kennedy
known issue in the old sqlite version? Or how can I continue to debug the lock contention issue? Not a known issue. There are some common problems regarding locking enumerated here: In practice, the ones in sections 2.2 and 2.2.1 se

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread Dan Kennedy
OM ranges WHERE start <= ? ORDER BY start DESC LIMIT 1 ) WHERE end >= ? Dan. I am afraid that we are hitting the first case, which really is a pity. Is there a way to suggest to the index how to work on these cases? Cheers, Simone -- Sent from: http://sqlite.106534

Re: [sqlite] Optmize queries on ranges

2018-10-25 Thread Dan Kennedy
t be iterating through a very large set of records to extract the ones you want. R-tree might work for you: Dan. ___ sqlite-users mailing list

Re: [sqlite] union + window functions = sqlite crash (version 3.25.2)

2018-10-23 Thread Dan Kennedy
a bug in core sqlite, which should be fixed It is indeed, thanks for reporting it. Should now be fixed here: Dan. ___ sqlite-users mailing list http

Re: [sqlite] 64-column covering index limit clarification

2018-10-21 Thread Dan Kennedy
here, the partial index is used to optimize the ORDER BY in the query. Thanks, Dan. CREATE TABLE t1( c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35, c36, c37, c38, c3

  1   2   3   4   5   6   7   8   9   10   >