[sqlite] Virtual Table query - why isn't SQLite using my indexes?

Hey, Gunter, Thanks for taking the time to make such a thorough response to my question. Yes, your query is arguably more correct, though our queries do produce identical results. The SQL I included was generated by a general-purpose query tool that automatically joins tables based on key

[sqlite] Creating an index seems to break table logic

Hi Dan, Thank you for responding. The integrity check result is OK: sqlite> pragma integrity_check; integrity_check -- ok Here is the session copied. I had to obfuscate some values though? I don?t think this should prevent investigating this.

[sqlite] REGEXP pcre DLL for Windows

Yes of course - the answer is: didn't even know that a default do exist. Sorry! Grabbed the regexp.c compiled it - and yes - it works - plain and simple! Thanks a lot! For the archive if someone else try to compile it: gcc -s -O4 -I c:\sqlite\src\ -shared -o c:\sqlite\bin\regexp.dll

[sqlite] index broken by insert

Dear Richard, Dear Simon, Dear SQLiters, It is such a pleasure to deal with smart people. Pure joy. How quickly Simon figured out the problem and how Richard narrowed it down and fixing it. Thank you for the work around. I implemented my own, stupid, work around: execute offending insert

[sqlite] Creating an index seems to break table logic

Hi all, I have this schema: CREATE TABLE list_of_numbers ( nn TEXT, astart INT, aend INT, alength INT, usesflag TEXT, blength INT, coolflag NUM, alphaid ); Some example data: nn astart aendalength usesflag blength coolflag

[sqlite] REGEXP pcre DLL for Windows

Hello Coder, yes that is working fine! Thank you! As I'm always in struggle when I try to compile such things for sqlite. Do you have a small step by step HOWTO compiled this? Especially the used gcc parameters and so ... Would be great for the future but anyway - the provided DLL is working.

[sqlite] Creating an index seems to break table logic

On 19 May 2015, at 6:39pm, Adam Podstawczy?ski wrote: > Now, the above query is expected to return one record only Please check your post. The line you say is returned is not in the data you included in your post. Here's your data in a readable form to work with: CREATE TABLE

[sqlite] Segfault during FTS index creation from huge data

On 05/19/2015 05:54 PM, Artem wrote: >> On 05/19/2015 03:35 PM, Artem wrote: >>> Hi! >>> >>> And what about result of our conversation? >>> Can developers increase this limitations for using all memory that user >>> have? >> Hi Artem, >> The conclusion was that although the first problem

[sqlite] index broken by insert

-- Dear SQLiters, -- Here is schema first, table is below, -- followed by offending statement. -- you can copy and paste the entire body -- my comments are SQL compatible - -- STEP 1 -- -- create gender and handedness tables to fix possible values -- then

[sqlite] index broken by insert

Yes, I can provide full schema and data set. As far as I remember this mailing list does not accept attachments. Would that be OK to send in body of email? It is not that big. Roman From: sqlite-users-bounces at mailinglists.sqlite.org

[sqlite] index broken by insert

"Confirmation" of a bug: When I use old SQLite version 3.7.2, the offending INSERT does NOT lead to integrity failure. This is indicative of either bug in old integrity check or new insert. Please let me know what useful info/contribution I can make. Another addition. The output of PRAGMA

[sqlite] about query of sqlite fts4 model

hello, I have a question about fts4 model , could you help me please ? I have two virtual tables , these are created by fts4 , I have no idea how to improve the query efficiency when i execute a nested query ,for example: I found that the first case is better than another one.

[sqlite] select * where is/like ?

On 19 May 2015, at 4:23pm, Roman Fleysher wrote: > I guess not good: > > sqlite> PRAGMA integrity_check; > integrity_check > - > row 37 missing from index sqlite_autoindex_Exam_1 There you go. Problem

[sqlite] cannot read sqlite 3.x DB-File with SQLIte Browser 3.6 (can't either with 2.0b1)

Am 19.05.2015 um 16:23 schrieb Richard Hipp: > On 5/19/15, Christoph P.U. Kukulies wrote: >> Some time ago I wrote a little app in C# WPF (using sqlite .Net) and >> today I wanted to use it again. >> The app writes an SQLITE 3.x database file out to disk (extension >> .s3db). The file icon seems

[sqlite] Segfault during FTS index creation from huge data

On 05/19/2015 03:35 PM, Artem wrote: > Hi! > > And what about result of our conversation? > Can developers increase this limitations for using all memory that user > have? Hi Artem, The conclusion was that although the first problem encountered is the massive allocation that FTS tries to make,

[sqlite] index broken by insert

Dear SQLiters, I do not really know what info to provide for sufficient information. I use SQLite shell only for all create/insert manipulations. This insert below causes PRAGMA integrity_check; to report missing index (what appears to be on every inserted row): SQLite version 3.8.8.3

[sqlite] select * where is/like ?

On 19 May 2015, at 4:07pm, Roman Fleysher wrote: > CREATE TABLE Exam( > examID TEXT PRIMARY KEY NOT NULL Works fine for me. I tested inserting as text, integer and real in case they did something weird to LIKE. SQLite version 3.8.5 2014-08-15 22:37:57 Enter ".help" for usage hints.

[sqlite] index broken by insert

On 5/19/15, Roman Fleysher wrote: > > How will i know the patch/new version is available? By checking ticket > link? > Version 3.8.10.2 will appear on https://www.sqlite.org/download.html It takes about 16 hours to run a full test cycle of SQLite on Linux and Windows, which is the minimum

[sqlite] cannot read sqlite 3.x DB-File with SQLIte Browser 3.6 (can't either with 2.0b1)

Am 19.05.2015 um 16:08 schrieb Christoph P.U. Kukulies: > Some time ago I wrote a little app in C# WPF (using sqlite .Net) and > today I wanted to use it again. > The app writes an SQLITE 3.x database file out to disk (extension > .s3db). The file icon seems to be not bound to any application >

[sqlite] cannot read sqlite 3.x DB-File with SQLIte Browser 3.6 (can't either with 2.0b1)

Some time ago I wrote a little app in C# WPF (using sqlite .Net) and today I wanted to use it again. The app writes an SQLITE 3.x database file out to disk (extension .s3db). The file icon seems to be not bound to any application so maybe the extension was chosen deliberately by myself at the

[sqlite] select * where is/like ?

On 19 May 2015, at 4:43pm, Roman Fleysher wrote: > Now I have two questions: > > 1. I created database from scratch using new version of SQLITE and PRAGMA > integrity_check; produces "missing index" as before. Are you telling us that you have a sequence of commands which, done entirely

[sqlite] select * where is/like ?

Now I have two questions: 1. I created database from scratch using new version of SQLITE and PRAGMA integrity_check; produces "missing index" as before. How could I broke it if all indexing is done on insertion? 2. Replacing "=" with "IS" in the original WHERE solved the problem. However,

[sqlite] index broken by insert

On 5/19/15, Roman Fleysher wrote: > CREATE TRIGGER demographicInsert AFTER INSERT ON subject FOR EACH ROW BEGIN > INSERT INTO Exam (subjectID, examID, examType) VALUES (NEW.subjectID, > NEW.subjectID, 'demographicExam'); Your workaround is to change the insert into examID to cast it to type

[sqlite] select * where is/like ?

I guess not good: sqlite> PRAGMA integrity_check; integrity_check - row 37 missing from index sqlite_autoindex_Exam_1 row 38 missing from index sqlite_autoindex_Exam_1 row 39 missing from index

[sqlite] index broken by insert

Trouble ticket is here: https://www.sqlite.org/src/tktview/34cd55d68e On 5/19/15, Roman Fleysher wrote: > -- Dear SQLiters, > > -- Here is schema first, table is below, > -- followed by offending statement. > -- you can copy and paste the entire body > -- my comments are SQL compatible > >

[sqlite] select * where is/like ?

CREATE TABLE Exam( examID TEXT PRIMARY KEY NOT NULL I will also add, as most inexperienced programmers do, "but it worked before". The database was created with older version of SQLITE and the code worked with the same older version. Could it matter? Thank you, Roman

[sqlite] index broken by insert

On 5/19/15, Roman Fleysher wrote: > Yes, I can provide full schema and data set. As far as I remember this > mailing list does not accept attachments. Would that be OK to send in body > of email? It is not that big. > Yes, please. Send in the body of the email. -- D. Richard Hipp drh at

[sqlite] Segfault during FTS index creation from huge data

> On 05/19/2015 03:35 PM, Artem wrote: >> Hi! >> >> And what about result of our conversation? >> Can developers increase this limitations for using all memory that user >> have? > Hi Artem, > The conclusion was that although the first problem encountered is the > massive allocation that FTS

[sqlite] index broken by insert

Hi, Roman, On Tue, May 19, 2015 at 1:23 PM, Roman Fleysher wrote: > "Confirmation" of a bug: > > When I use old SQLite version 3.7.2, the offending INSERT does NOT lead to > integrity failure. This is indicative of either bug in old integrity check or > new insert. Please let me know what

[sqlite] select * where is/like ?

On 5/19/15, Roman Fleysher wrote: > Now I have two questions: > > 1. I created database from scratch using new version of SQLITE and PRAGMA > integrity_check; produces "missing index" as before. That sounds like a bug. Can you please send in the sequence of SQL commands that you used to

[sqlite] Segfault during FTS index creation from huge data

> On 19 May 2015, at 9:35am, Artem wrote: >> Can developers increase this limitations for using all memory that user >> have? > The answer to this depends on which operating system you are > running and how it is configured. The answers cannot be changed by changing > a setting in SQLite.

[sqlite] Final preparations for the release of System.Data.SQLite v1.0.97.0 have begun...

If you have any issues with the current code, please report them via this mailing list (and/or by creating a ticket on "https://system.data.sqlite.org/;) prior to Monday, May 25th. Thanks. -- Joe Mistachkin

[sqlite] Segfault during FTS index creation from huge data

Hi! And what about result of our conversation? Can developers increase this limitations for using all memory that user have? > One, you should remove sqlite-users at sqlite.org from your To list. I keep > bouncing email when I reply to you. Not a big deal, just an FYI. > Two: > On Sun, May 3,

[sqlite] SQLite not using primary key index anymore

> The SQLite I have here uses the primary key. It works now after another round of drop index/create index/analyze. The problem is/was related to the DB, I have multiple databases with the same schema, only the largest one had the issue (38 GB), the smaller ones did not. > Which version? While

[sqlite] SQLite not using primary key index anymore

Eric Grange wrote: > I recently added a field and index to an existing table, and now SQLite > seems to be using that index in place of the primary key, even on simple > queries > > CREATE TABLE vin ( >id INTEGER PRIMARY KEY AUTOINCREMENT, >tx_id INTEGER NOT NULL, >from_vout_id

[sqlite] SQLite not using primary key index anymore

Also while using select id from vin NOT INDEXED order by id desc limit 1 works around the problem, this is just one query of many (and a simplified one at that), and I have other less trivial queries where the inefficient default query plan has the same catastrophic effect on performance, so

[sqlite] cannot read sqlite 3.x DB-File with SQLIte Browser 3.6 (can't either with 2.0b1)

On 5/19/15, Christoph P.U. Kukulies wrote: > Some time ago I wrote a little app in C# WPF (using sqlite .Net) and > today I wanted to use it again. > The app writes an SQLITE 3.x database file out to disk (extension > .s3db). The file icon seems to be not bound to any application > so maybe the

[sqlite] SQLite not using primary key index anymore

Hi, I recently added a field and index to an existing table, and now SQLite seems to be using that index in place of the primary key, even on simple queries Table declaration: CREATE TABLE vin ( id INTEGER PRIMARY KEY AUTOINCREMENT, tx_id INTEGER NOT NULL, from_vout_id INTEGER,

[sqlite] Segfault during FTS index creation from huge data

On 19 May 2015, at 9:35am, Artem wrote: > Can developers increase this limitations for using all memory that user > have? The answer to this depends on which operating system you are running and how it is configured. The answers cannot be changed by changing a setting in SQLite. Simon.

[sqlite] Limit size of write-ahead log file

On Mon, 18 May 2015 19:00:28 +0200, Zsb?n Ambrus wrote: >The PRAGMA max_page_count statement lets me set a limit on the size >the database is allowed to grow. This is useful to protect myself >against accidental errors in my program, where I fill the file system >with a huge database file,

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

Eric Working backwards from your query, I think your schema would be similar to (foreign keys omitted) CREATE TABLE rental ( rentalID INTEGER PRIMARY KEY NOT NULL, rental_date TEXT, inventory_id INTEGER, customer_id INTEGER, ...); CREATE TABLE inventory ( inventory_id INTEGER PRIMARY KEY NOT

[sqlite] select * where is/like ?

On 19 May 2015, at 5:51am, Roman Fleysher wrote: > Why does not last SQL query produce nothing? What is the schema (the CREATE TABLE command) for your Exam table ? Simon.

[sqlite] select * where is/like ?

Dear SQLiters, I have a table with examID column. When I try to select specific ID I have to use "like" instead of "=". Why is that? Why does not last SQL query produce nothing? SQLite version 3.8.8.3 2015-02-25 13:29:11 sqlite> select examID, typeof(examID) from mainDB.Exam where