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

On 9/3/63 01:44, Puneet Kishor wrote: Update: so, after much hitting of my head against the wall of sql, I came up with the following – as noted above, I really have two distinct set of queries I can do separately like so Q1: (SELECT t1Id FROM t1 WHERE …) AS a Q2: (SELECT t1Id FROM vt1

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

On 7/3/63 14:58, P Kishor wrote: I asked this question on Stackoverflow with not much success, and a suggestion to ask it on the list. So here I am. I have two tables, t1(id, t1Id, … other cols …, fullText) and a FTS5 virtual table vt1(t1Id, fullText) ``` sqlite> EXPLAIN QUERY PLAN ...>

Re: [sqlite] Status of LSM1 extension

On 5/3/63 16:11, Dominique Devienne wrote: Hi, I'm interested in LSM1 [1] as an alternative to SQLite, since in a particular use-case, I'm using SQLite mostly as a key-value store, and write performance is particularly important, in addition to MVCC. Sounds like it could be an excellent fit

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

On 27/2/63 05:04, Adam Levy wrote: Hi all When I have a database connection with a temp table of the same name as a table in main, I am getting what I feel is unexpected behavior from the session extension. Most succinctly, I start a session on a connection on the main db with a table with an

Re: [sqlite] Trigger name missing

On 26/2/63 16:31, Jean-Luc Hainaut wrote: Hi all, It seems that SQLite (version 31.1) accepts a trigger declaration in which the name is missing. When fired, this trigger doesn't crashes but exhibits a strange behaviour. In particular, while expression "new." in an "insert" trigger returns

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

On 6/2/63 19:00, Andrea Spacca wrote: Hello, I experienced seeing no results for search in matrix-syanpse backed by sqlite3 storage. Tracking down the their source code I identified the query: https://github.com/matrix-org/synapse/blob/v1.9.1/synapse/storage/data_stores/main/search.py#L424

Re: [sqlite] Virtual table OR constraint bug

On 29/1/63 20:09, Lalit Maganti wrote: Hi folks, Just wanted to check up on this to see if this issue is something which is being tracked and if there was a potential fix in the works? Thanks for the excellent bug report and minimal reproduction case. Now fixed here:  

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

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...

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? That's right. xUnlock(fd, SQLITE_LOCK_NONE) should completely unlock the file.

Re: [sqlite] A hang in Sqlite

On 6/1/63 13:44, Yongheng Chen wrote: Hi, We found a test case that hangs Sqlite: — CREATE TEMPORARY TABLE v0 ( v1 INT UNIQUE ) ; WITH RECURSIVE v0 ( v1 ) AS ( SELECT -128 UNION SELECT v1 + 33 FROM v0 ) SELECT 'x' from v0; — This seems triggering a dead loop. However, since v0 is empty, it

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

On 28/12/62 01:58, Yongheng Chen wrote: Hi, We found a heap UAF bug in sqlite. Here’s the PoC: — CREATE TABLE v0 ( v1 CHECK( CASE v1 WHEN '13' THEN 10 ELSE 10 END ) ) ; CREATE TRIGGER x INSERT ON v0 BEGIN INSERT INTO v0 ( v1 , v1 ) SELECT v1 , v1 FROM v0 WHERE v1 < 10 ON CONFLICT DO NOTHING

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

On 20/12/62 22:03, test user wrote: Hello, I have a search box on a website that uses FTS5/MATCH. MATCH seems to take its own custom language for matching. 1. Is it safe to just pass the users query to MATCH ? via the SQLite bind FFI? Users could specify a query that uses excessive

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

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

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 ?

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

On 23/11/62 17:02, Mario M. Westphal wrote: I have an issue with concurrency. This may be the intended behavior of SQLite. Or I'm doing something wrong. If somebody could shed a light, I would be thankful. I compile and use SQLite on Windows with VStudio. I compile it with

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

On 22/11/62 00:06, Jens Alfke wrote: On Nov 21, 2019, at 7:01 AM, Richard Hipp wrote: The memset() just forces the bug to the surface in builds where the ckmalloc()/ckfree() routines of TCL are using caching that prevents valgrind/ASAN from seeing the use-after-free. The memset() is not

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

On 20/11/62 11:47, Dennis Clarke wrote: Still on Red Hat Enterprise Linux here and now using CFLAGS wherein there is no "std" specified. The crash below is in test code - it is almost certainly a problem with the test scripts. If you can you send me the "test-out.txt" created by the

Re: [sqlite] WAL2 mode

On 12/11/62 19:00, Simon Slavin wrote: On 12 Nov 2019, at 10:06am, Dan Kennedy wrote: This branch might interest you: https://www.sqlite.org/src/timeline?r=begin-concurrent-pnu-wal2 " 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

On 12/11/62 18:50, Merijn Verstraaten wrote: I already asked this question without an answer, but as it hidden somewhere nested deeply in another thread I think it may have simply gone under the radar. Apologies for the duplication if you already saw it! How is the behaviour of (custom)

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

On 25/10/62 23:07, Brannon King wrote: This is a request for a small change to the handling of multiple connections. I think it would significantly enhance the usefulness there via allowing multiple "views" of the data. Consider that I have two simultaneous connections to one file, named Con1

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

On 8/11/62 00:15, Dennis Clarke wrote: On 2019-11-07 11:44, Shawn Wagner wrote: ... Just don't use strict c99 mode when compiling with gcc? Drop the -std argument from your CFLAGS to use the default (gnu11 since gcc 5) or explicitly use gnu99, which gives you that version of the C standard +

Re: [sqlite] Prepared statements in FTS MATCH queries

'LastName:alpha FirstNames:beta'; b) SELECT * FROM NamesFTS WHERE LastName MATCH 'alpha' AND FirstNames MATCH 'beta'; Regards, Hartwig Am 2019-11-01 um 07:55 schrieb Dan Kennedy mailto:danielk1...@gmail.com>>: On 1/11/62 03:03, mailing lists wrote: Hi Dan, I did not know that. What was the

Re: [sqlite] Prepared statements in FTS MATCH queries

. Fts5 now uses a more complicated method to pass constraints between those two methods, which allows the details of multiple MATCH constraints to be passed through. Dan. Regards, Hartwig Am 2019-10-31 um 19:16 schrieb Dan Kennedy : On 1/11/62 00:32, mailing lists wrote: For normal

Re: [sqlite] Prepared statements in FTS MATCH queries

On 1/11/62 00:32, mailing lists wrote: For normal tables I can use something like: SELECT * FROM Names WHERE FirstNames=? AND or OR LastName=?; For FTS tables I can only use SELECT * FROM FTSNames WHERE FirstNames MATCH ? OR LastName MATCH ?; AND is not supported (still do not know why) Is

Re: [sqlite] Roadmap?

On 20/10/62 14:53, Thomas Kurz wrote: I'd kindly ask whether there is some sort of roadmap for SQLite development? Someone recently pointed out how much he loves the "lite" and well-thought features. I cannot see that: I observe that many "playground" gadgets keep being implemented (like

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

On 19/10/62 06:31, Ben Asher wrote: Hello! I'm trying to write some code to keep an external content table in sync with the index. To do this, I need to be able to get some state about the index: either how many rows have been inserted so far or the max rowid that has been inserted into the

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

On 11/10/62 06:18, 吕涛 wrote: Hi, there is an inconsistency between sqlite implementation and documentation of zlib API deflate in the file /ext/misc/zipfile.c:1003. 1000 deflateInit2(, 9, Z_DEFLATED, -15, 8, Z_DEFAULT_STRATEGY); 1001 res = deflate(, Z_FINISH); 1002 1003 if(

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

On 22/9/62 02:25, Fredrik Larsen wrote: Interesting, very similar change but not fully idenctial. In my patch, I created a sqlite3ExprListCompareIgnoreButUpdateSort, and used this function from line 6239. This function ignores the sort part when comparing expressions, but will update the

Re: [sqlite] Undo sqlite3_snapshot_open?

On 19/9/62 18:13, Gwendal Roué wrote: Hello, I am looking at the snapshot experimental APIs, and it looks like once a connection has been sent to an "historical snapshot" with sqlite3_snapshot_open (https://www.sqlite.org/c3ref/snapshot_open.html), the connection can never be restored back to

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

On 15/9/62 11:57, Jake Thaw wrote: The following examples demonstrate a possible bug when using a WINDOW clause within a CTE: SQLite version 3.30.0 2019-09-14 16:44:51 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent

Re: [sqlite] Window functions

On 5/9/62 00:13, David Raymond wrote: Kind of annoying that when the author shows a screenshot of the sample data he's using for his queries that he doesn't include 2 of the fields that are in the queries. Makes it harder to "play along at home" For their ntile example (on page2) I don't

Re: [sqlite] Window functions

On 4/9/62 23:14, Simon Slavin wrote: I ran into this two-part article, probably on Hacker News: I tried comparing it with but I don't know enough to be able to tell whether the

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

On 2/9/62 16:57, Paul wrote: I has been a while without response, so I just bumping this message. 19 July 2019, 14:21:27, by "Paul" : I have a test case when the regression can be observed in queries that use JOINs with FTS4 tables, somewhere in between 3.22.0 and 3.23.0. For some reason

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

2019-08-30 um 12:24 schrieb Dan Kennedy : The fts3/4 documentation says: "If the FTS table is an FTS4 table (not FTS3), a token may also be prefixed with a "^" character. In this case, in order to match the token must appear as the very first token in any column of the matching r

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

The fts3/4 documentation says: "If the FTS table is an FTS4 table (not FTS3), a token may also be prefixed with a "^" character. In this case, in order to match the token must appear as the very first token in any column of the matching row." So change "FTS3" to "FTS4" and it will likely

Re: [sqlite] Inverted changesets and UNIQUE constraints

On 26/8/62 15:12, Daniel Kraft wrote: Hi! I'm using the SQLite session extension to create changesets, invert them and apply them to undo previous changes in the database. (Essentially what I need to do is persistent savepoints.) This works well so far, but I recently wondered about the

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

On 21/8/62 01:06, Matt Haynie wrote: Hello sqlite-users, Apologies if this isn’t formatted correctly, I’m not used to submitting bug reports via mailing lists. Although I’m sure some people will be shaking their head, I chose to use periods between words in table names. I’ve been careful to

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

On 7/8/62 13:50, P Kishor wrote: Using FTS5 (sqlite3 3.29.x), the following works SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v MATCH 'Trematoda awaiting allocation’; but the following fails SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v MATCH 'Trematoda

Re: [sqlite] 3.29.0 .recover command

On 6/8/62 17:26, Olivier Mascia wrote: On one database instance, a .dump command gives me (among many other lines) things like: INSERT INTO STATISTICS VALUES(11237795927160,11868); while the output of .recover command gives me things this way: INSERT INTO 'STATISTICS'('_rowid_',

Re: [sqlite] mode insert dumps

On 2/8/62 15:04, Luca Ferrari wrote: Hi, I've got a lot of database files that I would like to dump using INSERT statements. unluckily, .mode insert does not work for me, or I'm not able to understand how it works. sqlite> select mude_anno, mude_numero from catdaemo; INSERT INTO table

Re: [sqlite] Large database backup

On 1/8/62 19:49, Tammisalo Toni wrote: Hi! I have an application which is using sqlite database in WAL mode. There is a need for periodic backups to a remote site without obstructing the normal operation. Both read and write access is required during the backup. At the moment I have system

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

On 14/7/62 17:18, Chaoji Li wrote: This problem is only present for 3.28+. A sample test case is attached. Thanks for reporting this. We think it's fixed here:   https://sqlite.org/src/info/52f463d29407fad6 The mailing list stripped off your test case, so if you could either run it with

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

On 15/7/62 00:05, ardi wrote: Hi! I'm going to use sqlite as means of the file format I/O for applications. One of the critical points in file I/O is saving the file in a safe way, so that data loss cannot happen (or at least the risk of happening is minimized as much as possible).

Re: [sqlite] Link errors with SQLITE_OMIT_VIRTUALTABLE

On 14/7/62 15:59, Orgad Shaneh wrote: Hi, In reply to https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg113512.html. Can you please accept this patch? The patch doesn't seem all that intrusive, but is there a reason you can't build from canonical sources instead of

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

On 11/7/62 23:07, Andreas Kretzer wrote: I'm using SQLITE3 (V3.29.0) on an arm embedded linux (2.6.39) on an ext3 filesystem. Several processes hold the DB open and the "-wal" and "-shm" files exist. if I use 'lsof | fgrep ' I can see all processes having all three files open. At least one of

Re: [sqlite] Incorrect results after upgrading to 3.28.0

Thanks for reporting this one. It is a bug. Now fixed here: https://sqlite.org/src/info/5fd20e09a522b62a Ticket: https://sqlite.org/src/info/9cdc5c46 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

On 18/6/62 19:48, Riccardo Schirone wrote: Hi, What is exactly the patch for CVE-2019-5018? I could not see it anywhere in the release changelog nor in the Cisco Talos report. It would be useful for distributions that need to backport the fix. It was this one:

Re: [sqlite] json path escaping with double quote

On 13/6/62 23:45, gwenn wrote: Hello, With the json1 extension, we can escape special characters like '[' from being interpreted as an array index by wrapping the path in double quotes. But sometimes, it does not work: Thanks for reporting this. Now fixed here:  

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

On 15/6/62 20:08, Dominique Pellé wrote: Hi SQLite page https://sqlite.org/c3ref/io_methods.html says at the bottom: === BEGIN QUOTE === If xRead() returns SQLITE_IOERR_SHORT_READ it must also fill in the unread portions of the buffer with zeros. A VFS that fails to zero-fill short reads

Re: [sqlite] configure failure for aarch64

On 7/6/62 20:17, Beat Küng wrote: Hi I'm trying to build sqlite (the latest release 3.28.0) for an aarch64 build target and get the following error: checking build system type... ./config.guess: unable to guess system type This script, last modified 2007-07-22, has failed to recognize the

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

On 7/6/62 20:23, Ulrich Telle wrote: When I append the source code of the SHA3 extension (shathree.c from ext/misc directory) to the SQLite3 amalgamation (sqlite3.c), and then try to compile it for Android, I get the following error message: shathree.c:83:7: error: expected identifier or '('

Re: [sqlite] Bug in sqlite3.c

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. And it had reported ok. I will

Re: [sqlite] Bug in sqlite3.c

On 3/6/62 12:51, bhandari_nikhil wrote: I am facing a crash in sqlite3fts5BufferAppendBlob. Following is the backtrace: #0 sqlite3Fts5BufferAppendBlob (pRc=pRc@entry=0xf54139c8, pBuf=pBuf@entry=0xf5afeb90, nData=4294967295, pData=0xf49fff76 "90246ture") at sqlite3.c:180474 #1 0xf717b6f8

Re: [sqlite] Session extension questions

On 31/5/62 04:54, Sebastien Boisvert wrote: I've got a couple of general questions about the session extension, specifically about applying changesets/patchsets: - Assuming little to no conflicts, what's the performance of applying a changeset to a database; let's say for 1 (or multiples

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

On 16/5/62 18:47, Amirouche Boubekki wrote: Hello, I am considering using lsm extension for a project. I a did a few benchmark with the following configuration: LSM_CONFIG_AUTOFLUSH 1048576 LSM_CONFIG_BLOCK_SIZE 65536 LSM_CONFIG_AUTOWORK 1 LSM_CONFIG_MMAP 0 LSM_CONFIG_MULTIPLE_PROCESS 0 I

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

On 11/5/62 16:54, Manuel Rigger wrote: Hi everyone, I found another test case that demonstrates a malfunctioning index: CREATE TABLE IF NOT EXISTS t0 (c0); CREATE INDEX IF NOT EXISTS i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0(c0) VALUES(NULL); SELECT * FROM t0 WHERE t0.c0 IS NOT 1; --

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

On 6/5/62 16:42, Manuel Rigger wrote: Hi everyone, the following example fails with an error "no such column: c0": CREATE TABLE t0 (c0 INTEGER, PRIMARY KEY (c0)) WITHOUT ROWID; ALTER TABLE t0 RENAME COLUMN c0 TO c1; Thanks again for the bug reports. This one is now fixed here:

Re: [sqlite] .expert output help

On 26/4/62 23:56, Jose Isaias Cabrera wrote: this query. If you try ".expert -verbose", it will tell you the other This is the output from --verbose sqlite> .expert --verbose sqlite> SELECT a.*,sum(b.AnnualDossier) as Dossier FROM Project_List AS a ...> LEFT JOIN Project_ABT_Budget AS b

Re: [sqlite] .expert output help

On 26/4/62 21:30, Jose Isaias Cabrera wrote: Greetings. sqlite> .expert sqlite> SELECT a.*,sum(b.AnnualDossier) as Dossier FROM Project_List AS a ...> LEFT JOIN Project_ABT_Budget AS b ON a.ProjID = b.ProjID ...> AND ...> b.InsertDate = ...> (SELECT MAX(InsertDate) FROM

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

On 25/4/62 07:58, paul tracy wrote: My question is whether or not I can rely on my use of non-unique rowids in FTS5 contentless tables to remain unenforced into the future of SQLite or am I exploiting an oversight that may be resolved in the future? Details ... I have a key-value pair SQLite

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

Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dan Kennedy Gesendet: Freitag, 29. März 2019 14:30 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] Query Regression IN and Virtual Tables On 29/3/62 14:32, Hick Gunter wro

Re: [sqlite] Row locking sqlite3

On 28/3/62 01:04, Thomas Kurz wrote: I wonder whether SQLite is treating each DELETE as a single transaction. Could you try wrapping the main delete in BEGIN ... END and see whether that speeds up the cascaded DELETE ? Would you be able to find timings (either in your code or in the

Re: [sqlite] is this possible

On 29/3/62 03:00, Igor Tandetnik wrote: On 3/28/2019 3:21 PM, Mark Wagner wrote: Imagine I have these two tables and one view defining a join. CREATE TABLE t (foo); CREATE TABLE s (bar); CREATE VIEW v as select * from t join s on (foo = q); Surprisingly, this last statement succeeds. But if

Re: [sqlite] Query Regression IN and Virtual Tables

On 29/3/62 14:32, Hick Gunter wrote: When upgrading from 3.7.14.1 to 3.24 I noticed the following problem Given a virtual table like CREATE VIRTUAL TABLE vt ( key1 INTEGER, key2 INTEGER, key3 INTEGER, attr1 INTEGER,...); whose xBestIndex function simulates (in unsupported syntax) CREATE

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

There's code here, if you want to experiment with it:   https://sqlite.org/src/timeline?r=wal2 Docs:   https://sqlite.org/src/artifact/a807405a05e19a49 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

That's the error I got too. Now fixed here:   https://sqlite.org/src/info/b3fa58dd7403dbd4 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

On 18/3/62 15:48, Chu wrote: The code: ``` CREATE VIRTUAL TABLE t1 USING fts5(content); INSERT INTO t1 VALUES(''); BEGIN ; DELETE FROM t1 WHERE rowid = 1; SELECT * FROM t1 WHERE content MATCH ''; INSERT INTO t1 VALUES('');

Re: [sqlite] picking random subset of rows

On 18/3/62 17:36, Kevin Martin wrote: Hi, I am trying to use a correlated subquery with an 'order by random() limit 2' to pick upto two random rows for each value in the outer query. I am not sure if I am doing this correctly, but the number of rows I am getting seems to vary randomly which

Re: [sqlite] Segmentation fault running a query

On 6/3/62 16:37, Peter Hardman wrote: So, I forgot I had emails from the list turned off Integrity check shows no results. The query fails from the CLI The schema (of a very much cut down database) is attached. Can you post the results of running the ".fullschema" command in the

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

On 27/2/62 05:47, Jake Thaw wrote: This may not strictly be a bug, but currently (3.27.2) a vtab cannot overload scalar functions in aggregate queries. Adding a check for TK_AGG_COLUMN in sqlite3VtabOverloadFunction makes my use case function as expected. I think it has always been this way.

Re: [sqlite] Disk I/O errors

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

On 22/2/62 17:17, Chris Locke wrote: This issue was found via DB Browser for SQLite, but relates to SQLite, not DB Browser for SQLite, so please bear with me If a table is part of a view, then SQLite complains that "Error: error in view view1: no such table: main.table11" ALTER TABLE ...

Re: [sqlite] Typo in FTS5 documentation

On 17/2/62 02:21, J. King wrote: states: the following three queries all specify the same phrase: Four queries are then listed. Presumably it should read: Thanks for pointing this out. Will be fixed next time the website is built and

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

On 9/2/62 03:31, Dominique Pellé wrote: David Raymond wrote: SQLite version 3.27.1 is now available on the SQLite website: https://sqlite.org/ https://sqlite.org/download.html https://sqlite.org/releaselog/3_27_1.html Release notes https://sqlite.org/releaselog/3_27_1.html say:

Re: [sqlite] SQLITE_DBCONFIG_DEFENSIVE documentation looks confusing

On 29/1/62 23:18, Chris Brody wrote: I think my sample code should have read as follows: To activate the "defensive" flag for a database connection: sqlite3_db_config(db, SQLITE_DBCONFIG_DEFENSIVE, 1, NULL); (I got the wrong prefix before, and I discovered that it crashes if I do not add the

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

On 01/06/2019 08:08 PM, Daniel Kraft wrote: Hi! It seems to me that the session extension is broken in a situation that involves a "WITHOUT ROWID" table and "INSERT OR REPLACE" statements (but not if only one of those is used). Note that I'm using SQLite version 3.26.0 (> 3.17.0), so the

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

On 12/30/2018 02:41 PM, Nicolas Roy-Renaud wrote: I've been dealing with occasional segfaults on one of my applications when running a similar query, except even on SQLite 3.26, the safeguard wouldn't trigger. Running the SQL query specified in the bug report description from the tracker

Re: [sqlite] Window functions in sqlite 3.26.0

On 12/24/2018 01:43 AM, Balaji Ramanathan wrote: Hi, Are window functions enabled by default in the sqlite command line shell program that is available for download on the sqlite website? I get the error message "no such function: rownumber()" when I try to use that window function.

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

On 12/19/2018 12:55 AM, Shlomi Fish wrote: Hi, With the attached bash script on Mageia Linux v7 x86-64 I consistently get the test failures here: https://www.shlomifish.org/Files/files/text/sqlite-mga7-rpm-build.txt.xz this is with sqlite 3.26.0. This affects our rpm %check phase. Can you

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

On 12/14/2018 07:22 AM, Jens Alfke wrote: We’ve found a bug where creating indexes on expressions causes a query to give incorrect results. Our indexes use some complex custom functions, but the bug is easy to reproduce just using the ‘abs’ function: SELECT * FROM docs WHERE abs(a)=2

Re: [sqlite] Minor glitch in FTS5 docs

On 12/15/2018 08:45 AM, Jens Alfke wrote: At https://sqlite.org/fts5.html#synonym_support : There are several ways to approach this in FTS5: 1. By mapping all synonyms to a single token. […] 2. By adding multiple synonyms for a single term to the

Re: [sqlite] Affinity of expression indexes

On 12/13/2018 07:41 AM, Jens Alfke wrote: Consider CREATE INDEX foo_idx ON tbl (myfunction(a)); where ‘myfunction’ is a deterministic C function I’ve registered with the SQLite connection (and ‘a’ is a column of ‘tbl’ of course.) SQLite has no idea what data type(s) ‘myfunction’ returns, and it

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

On 12/12/2018 03:37 AM, Jens Alfke wrote: Is there any way for a custom FTS4 tokenizer to know when it’s tokenizing a search string (the argument of a MATCH expression), as opposed to text to be indexed? Here’s my problem: I’ve implemented a custom tokenizer that skips “stop words” (noise

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

On 12/03/2018 10:37 AM, Philip Warner wrote: Tables with complex triggers (possibly limited to "Insert...With", though that is not clear), fail with "no such table". Thanks for reporting this. The bug was that table and column names within WITH clauses in the schema were not being

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

would be much easier if I could just use deferred foreign keys that don't let me commit if the database is in an inconsistent state. Máté Dan Kennedy ezt írta (időpont: 2018. nov. 23., P, 16:30): On 11/23/2018 09:54 PM, szmate1618 wrote: Dear list members, I have the following problem,

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

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: sqlite-users@mailinglists.sqlite.org Sent: Friday, November 23, 2018

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

On 11/23/2018 09:54 PM, szmate1618 wrote: Dear list members, I have the following problem, with which I'd like to request your aid: Currently, at version 3.25.2, SQLite only has a limited support for alter table. E.g. you cannot change the datatype (type affinity) of a column, or drop a

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

On 11/21/2018 11:01 PM, Benjamin Stadin wrote: Hi, I've forked libgpkg on Github to merge fixes from other repositories and update the embedded SQLite (was at 3.8.). Though in the RTREE tests I get a 'database table is locked' error for the update statements (see exact error message below).

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

On 11/16/2018 08:41 PM, Victor Costan wrote: The amalgamation build ships two definitions of sqlite3_complete(). Most builds use the one in sqlite3.c. If SQLITE_OMIT_COMPLETE is defined, shell.c supplies a stub definition -- thank you very much for adding that! Unfortunately, the stub

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

On 11/15/2018 02:56 AM, Douglas Hall wrote: From https://www.sqlite.org/windowfunctions.html: "It is a syntax error to specify a FILTER clause as part of a built-in window function invocation." I'm curious, why is this the case? Why is a FILTER clause only allowed when used with aggregate

Re: [sqlite] Documentation of SQLite

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. ___

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

On 11/10/2018 08:18 AM, Keith Medcalf wrote: On Friday, 9 November, 2018 16:20, J. King wrote: To: SQLite mailing list That could lead to loss of referential integrity when modifying a table in a way not supported by ALTER TABLE, I believe. One usual method is to turn foreign keys off,

Re: [sqlite] race condition?

On 10/29/2018 03:45 PM, Lei Chen wrote: Hi experts, I'm debugging a tricky issue related to sqlite(3.9.2) database access. This is on Linux 3.2 kernel. Basically, when the failure occurs, there are two processes accessing the same -journal file concurrently, see below log. When daemon scsitgtd

Re: [sqlite] Optmize queries on ranges

On 10/26/2018 02:27 PM, siscia wrote: Hi all, thanks for your suggestions, unfortunately, I already tried all of them, except for the rtrees. Actually, my request for help wasn't complete. The ranges I am storing in the table are not overlapping. To make an example in SQL. The following can

Re: [sqlite] Optmize queries on ranges

On 10/25/2018 11:13 PM, siscia wrote: Hi all, I am facing an interesting optimization problem. I have a table like this: CREATE TABLE ranges ( start int, end int, value int, ); The query that I am interested in optimizing is "select value from ranges where (? between start and

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

On 10/23/2018 03:13 AM, Peter Ďurica wrote: Table with sample data: *create table t(a int, b int);* *insert into t values(1,11);* *insert into t values(2,12);* now query using any window function (row_number, rank, ) after UNION or UNION ALL will cause sqlite.exe crash (no regular error)

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

On 10/19/2018 02:30 AM, Deon Brewis wrote: Hi, I seem to have run into a limit where SQLITE doesn't use an index correctly if an indexed column is over the 64th column in the table. It's a partial index like: CREATE INDEX idx ON table(A, B DESC, C, D) WHERE A > 0 Where A and B are

Re: [sqlite] [SQLITE]ignoring syntax errors during search for sth include "symbols"

On 10/18/2018 04:58 PM, Maziar Parsijani wrote: Hi, how could I ignore syntax errors like this? *SELECT *,* * highlight(searchsimpleenhanced, 2, '', '') text* * FROM searchsimpleenhanced* * WHERE searchsimpleenhanced MATCH 'sth][';* You can use double quotes to search for a token that

Re: [sqlite] Hot-Journal with VFS

On 10/18/2018 09:27 PM, Bob schwanzer wrote: Hi, I'm seeing hot journal frequently in our SQLite installation. We use VFS which does some checksum and other operations. There are no abnormal scenarios such as power off, process crash, abandoned transactions... DB is opened by 10-20 processes

  1   2   3   4   5   6   7   8   9   10   >