Re: [sqlite] here is my test case//Re: SQLITE3 FTS5 prefix query get mismatch result

2018-03-22 Thread Dan Kennedy
On 03/21/2018 03:16 PM, zheng xiaojin wrote: (Please add.the head file and sqlite lib yourself, Thank you very much) Hi, Thanks for doing this. I'm running this version of the test program modified for posix threads: https://pastebin.com/d1HCX2aJ but I haven't seen any errors yet. How l

Re: [sqlite] here is my test case//Re: SQLITE3 FTS5 prefix query get mismatch result

2018-03-22 Thread zheng xiaojin
Sorry, but testcase maybe have some format problem, changed belowed, I run this on SQLITE 3.15.2, both on Windows and linux. and only less than 2min can happen. It doesn't matter about threadsafe, because the write func is only used to protect the test data. It means you can stop the. write func

[sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Ron Watkins
I have a table which contains a datetime column: table|foo|foo|2|CREATE TABLE foo ( dttmdatetimenot null i int not null ) I want to select out the max(i) value for each day where there are multiple records per day. select date(dttm) dt,max(i) from

Re: [sqlite] here is my test case//Re: SQLITE3 FTS5 prefix query get mismatch result

2018-03-22 Thread Dan Kennedy
On 03/22/2018 05:38 PM, zheng xiaojin wrote: Sorry, but testcase maybe have some format problem, changed belowed, I run this on SQLITE 3.15.2, both on Windows and linux. and only less than 2min can happen. It doesn't matter about threadsafe, because the write func is only used to protect the te

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Paul Sanderson
how about select date(dttm) dt,max(i) from foo group by date(dttm) order by 1; Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a w

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread R Smith
On 2018/03/21 5:30 PM, Ron Watkins wrote: I have a table which contains a datetime column: table|foo|foo|2|CREATE TABLE foo ( dttmdatetimenot null i int not null ) I want to select out the max(i) value for each day where there are multiple recor

Re: [sqlite] here is my test case//Re: SQLITE3 FTS5 prefix query get mismatch result

2018-03-22 Thread Dan Kennedy
On 03/22/2018 06:05 PM, Dan Kennedy wrote: On 03/22/2018 05:38 PM, zheng xiaojin wrote: Sorry, but testcase maybe have some format problem, changed belowed, I run this on SQLITE 3.15.2, both on Windows and linux. and only less than 2min can happen. It doesn't matter about threadsafe, because th

Re: [sqlite] here is my test case//Re: SQLITE3 FTS5 prefix query get mismatch result

2018-03-22 Thread zheng xiaojin
OK,Thank you very much,I will have a try ?取 Outlook for Android From: sqlite-users on behalf of Dan Kennedy Sent: Thursday, March 22, 2018 7:26:29 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] here is my test case//Re:

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

2018-03-22 Thread R Smith
On 2018/03/21 9:58 PM, csanyipal wrote: I am really trying to understand how CTEs works and trying to achive my goal ( see bellow ) so I modified a little your code: ... As you can see I tried to add more CTEs into code out there but must these comment out because I get wrong Results. So for n

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread David Raymond
Your query is fine. If you're only getting 1 row, then there's only 1 date in your data. If you're not getting a date column, then there's something drastically wrong with whatever you're using, as you have it right there in your query. Are your datetimes not stored correctly perhaps? SQLite ve

Re: [sqlite] How to optimise a somewhat-recursive query?

2018-03-22 Thread Richard Hipp
On 3/21/18, Jonathan Moules wrote: > I've spent the last ~90 minutes trying to build this but to no avail The query planner enhancements are now available in the pre-release snapshot on the https://sqlite.org/download.html page. -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] [EXTERNAL] Lazy virtual table creation

2018-03-22 Thread Philippe Riand
Thanks for your responses. Actually, it is up to my code to find out if the table exists or not. But you also raised a good point, as it can disappear, so what I’m looking for is more something like: “this statement uses this set set of tables [x,y,z…], please prepare your environment according

Re: [sqlite] [EXTERNAL] How to convert a datetime column to a date?

2018-03-22 Thread Hick Gunter
Whatever is in your dttm column (there is no datetime type in sqlite) does not parse as a valid timestring, so date() returns NULL for each and every row, giving just 1 result row. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftr

[sqlite] Unexpected optimization

2018-03-22 Thread Max Vlasov
Hi, I noticed an unexpected optimization at the sqlite side. Currently I can not reproduce this with some arbitrary test data (probably I will eventually). Anyway the logic behind this (pseudo-code query) Select , (Select count(*) from LookUpTable where LookUpTable.Value=TableValue) as StatC

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

2018-03-22 Thread Dominique Devienne
On Sat, Mar 17, 2018 at 1:28 AM, Richard Hipp wrote: > On 3/16/18, R Smith wrote: > > It's interesting to fathom what hypothesis is being tested with this > pole... > > INSERT operations on a table with AUTOINCREMENT do a full-table scan > against the sqlite_sequence table. I'm wondering if it

Re: [sqlite] [EXTERNAL] Unexpected optimization

2018-03-22 Thread Hick Gunter
SQLite uses the result of ANALYZE for computing costs of native tables and your reported costs for queries from virtual tables. You are expected to return estimated disk access counts for the constraints given (plus more detail, depending on SQLite version). Are you sure you need LEFT joins as

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Ron Watkins
It doesn't seem to work that way. I have 192330 distinct dttm entries, but I still only get 1 row. It seems as the “date(dttm)” result is either null or the empty string for all records. Not sure why. This works in other databases, so there must be something subtle about the “date()” function or

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Peter Da Silva
It might be helpful to provide some examples of what you have in those DATETIME columns. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Ron Watkins
The table defines “dttm” as a “datetime” datatype. sqlite> select distinct dttm from foo; … (lots of records) '2018-03-22 06:25:01' '2018-03-22 06:26:01' '2018-03-22 06:27:01' '2018-03-22 06:28:01' '2018-03-22 06:29:01' '2018-03-22 06:30:01' '2018-03-22 06:31:01' '2018-03-22 06:32:02'

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread R Smith
On 2018/03/22 3:21 PM, Ron Watkins wrote: It doesn't seem to work that way. I have 192330 distinct dttm entries, but I still only get 1 row. It seems as the “date(dttm)” result is either null or the empty string for all records. Not sure why. This works in other databases, so there must be s

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Peter Da Silva
1. It looks like you have superfluous quotes around the dates. That's probably your issue. 2. Try "select distinct dttm from foo LIMIT 10;" On 3/22/18, 8:47 AM, "sqlite-users on behalf of Ron Watkins" wrote: sqlite> select distinct dttm from foo; … (lots of records) '2

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Ron Watkins
sqlite> select '_' || dttm || '_' from foo limit 20; _'2017-11-08 07:00:01'_ _'2017-11-08 07:00:01'_ _'2017-11-08 07:00:01'_ _'2017-11-08 07:01:01'_ _'2017-11-08 07:01:01'_ _'2017-11-08 07:01:01'_ _'2017-11-08 07:02:01'_ _'2017-11-08 07:02:01'_ _'2017-11-08 07:02:01'_ _'2017-11-08 07:03:0

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Ron Watkins
The file that I use ".import" on contains records like this: '2018-03-22 07:01:01'|2533268 I had assumed the value was being treated as a datetime, but it looks like it may be treated as a string? Im a bit confused because the column definition sais "datetime", not "varchar", so if it's

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Peter Da Silva
SQLITE3 datetime fields are actually text. Fix the file that you're importing so it doesn't have quotes around the date. It looks like you're using some kind of modified CSV exporter to create the file. On 3/22/18, 9:06 AM, "sqlite-users on behalf of Ron Watkins" wrote: The file that I

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread R Smith
Ok, that tells us exactly what we needed to know, and Peter was right, those quotes are in the actual data, and shouldn't be. Try this query please: UPDATE foo SET dttm = date(trim(dttm,));  -- That's 4 single quotes in a row, which is a single quote, escaped with another single quote, in

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread David Raymond
Use datetime there instead of date or you'll lose the time part of it. UPDATE foo SET dttm = datetime(trim(dttm,)); http://www.sqlite.org/datatype3.html -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday,

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Ron Watkins
Is there an easy way to "fix" the data already in the table? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Peter Da Silva Sent: Thursday, March 22, 2018 7:09 AM To: SQLite mailing list Subject: Re: [sqlite] How to convert a date

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Peter Da Silva
Don't actually need to convert it to datettime if it's already in the right format, do you, or does datetime() do some grooming the source needs? On 3/22/18, 9:19 AM, "sqlite-users on behalf of David Raymond" wrote: Use datetime there instead of date or you'll lose the time part of it.

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

2018-03-22 Thread Richard Hipp
On 3/22/18, Dominique Devienne wrote: > > Hi Richard. Is 8.d from https://www.sqlite.org/draft/releaselog/current.html > the result of this inquiry? No. I just happened to notice the inefficiency while I was working on 8d. > And is there a chance the "some kind of indexed lookup" you mention ab

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread R Smith
On 2018/03/22 4:19 PM, David Raymond wrote: Use datetime there instead of date or you'll lose the time part of it. UPDATE foo SET dttm = datetime(trim(dttm,)); Absolutely, thanks for catching that - I kind of assumed he meant to only have the date part, but that is of course in the final

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Peter Da Silva
On 3/22/18, 9:22 AM, "sqlite-users on behalf of Ron Watkins" wrote: > Is there an easy way to "fix" the data already in the table? The string trim trick David and Ryan suggested would work. To avoid banging on rows already converted, maybe: UPDATE foo SET dttm = trim(dttm,) WHERE dttm li

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Ron Watkins
Thanks, I was able to patch the table by removing the single tick marks using the trim and the group by query now works as expected. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday, March 22, 2018 7:23 AM To:

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

2018-03-22 Thread Dominique Devienne
On Thu, Mar 22, 2018 at 3:22 PM, Richard Hipp wrote: > On 3/22/18, Dominique Devienne wrote: > > > > Hi Richard. Is 8.d from https://www.sqlite.org/draft/ > releaselog/current.html > > the result of this inquiry? > > No. I just happened to notice the inefficiency while I was working on 8d. > >

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Donald Griggs
Hello, Ron, Regarding just these two sentences: I had assumed the value was being treated as a datetime, but it looks like it may be treated as a string? I'm a bit confused because the column definition sais "datetime", not "varchar", ... The info on this page is very important,

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread R Smith
On 2018/03/22 4:22 PM, Peter Da Silva wrote: Don't actually need to convert it to datettime if it's already in the right format, do you, or does datetime() do some grooming the source needs? Yes indeed, it's down to "vetting" and in no way needed for the conversion. If datetime(x) understand

Re: [sqlite] Unexpected optimization

2018-03-22 Thread Richard Hipp
Max, Since you appear to be writing your own virtual tables, you could probably benefit from becoming more familiar with the internal workings of SQLite, and especially the ".wheretrace" and ".selecttrace" commands of the CLI. To enable those commands, build a new copy of the CLI that includes -D

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Peter Da Silva
On 3/22/18, 9:43 AM, "sqlite-users on behalf of R Smith" wrote: > On 2018/03/22 4:22 PM, Peter Da Silva wrote: > > Don't actually need to convert it to datettime if it's already in the right > > format, do you, or does datetime() do some grooming the source needs? > > Yes indeed, it's down to

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

2018-03-22 Thread Chris Locke
I see - thanks Paul. I misunderstood. Thanks for your detailed explanation. Chris On Wed, Mar 21, 2018 at 9:13 AM, Paul Sanderson < sandersonforens...@gmail.com> wrote: > Actually it is totally different Chris > > > I read that - but my point was more that some people seem to think that > an

Re: [sqlite] How to optimise a somewhat-recursive query?

2018-03-22 Thread Jonathan Moules
On 2018-03-22 12:03, Richard Hipp wrote: On 3/21/18, Jonathan Moules wrote: I've spent the last ~90 minutes trying to build this but to no avail The query planner enhancements are now available in the pre-release snapshot on the https://sqlite.org/download.html page. Well, after a lot of fid

Re: [sqlite] How to convert a datetime column to a date?

2018-03-22 Thread Tim Streater
On 22 Mar 2018, at 14:05, Ron Watkins wrote: > The file that I use ".import" on contains records like this: > '2018-03-22 07:01:01'|2533268 > I had assumed the value was being treated as a datetime, but it looks like it > may be treated as a string? > > Im a bit confused because the column defi

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

2018-03-22 Thread jungle Boogie
On 16 March 2018 at 08:37, Richard Hipp wrote: > This is a survey, the results of which will help us to make SQLite faster. > > How many tables in your schema(s) use AUTOINCREMENT? > 0 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org h

[sqlite] The upcoming 3.23.0 release

2018-03-22 Thread Richard Hipp
SQLite version 3.23.0 will probably be released soon, in early April. For a summary of changes see https://sqlite.org/draft/releaselog/3_23_0.html Please download the latest Pre-release Snapshot (https://sqlite.org/download.html) and test out the latest SQLite in your applications. Report any iss

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

2018-03-22 Thread Peter Michaux
I think there are a couple main offenders with > BEGIN; > INSERT OR IGNORE ... ; > UPDATE ; > COMMIT; The first is that it is bulky. If this is in the application code then it has to be repeated for each desired UPSERT and it has to be repeated in the code of each application that uses the d

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

2018-03-22 Thread Eduardo Morras
On Fri, 16 Mar 2018 11:37:24 -0400 Richard Hipp wrote: > This is a survey, the results of which will help us to make SQLite > faster. > > How many tables in your schema(s) use AUTOINCREMENT? Within all my projects 4 (very old projects) I don't use it on current projects. > I just need a singl

Re: [sqlite] The upcoming 3.23.0 release

2018-03-22 Thread Simon Slavin
On 22 Mar 2018, at 7:09pm, Richard Hipp wrote: > For a summary of changes see > https://sqlite.org/draft/releaselog/3_23_0.html "causes the database connection D to disconnection from database S"

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

2018-03-22 Thread csanyipal
R Smith-2 wrote > On 2018/03/21 9:58 PM, csanyipal wrote: >> >> I am really trying to understand how CTEs works and trying to achive my >> goal >> ( see bellow ) so I modified a little your code: >> ... >> As you can see I tried to add more CTEs into code out there but must >> these >> comment out

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

2018-03-22 Thread Graham Holden
Thursday, March 22, 2018, 7:18:08 PM, Peter Michaux wrote: > I think there are a couple main offenders with >> BEGIN; >> INSERT OR IGNORE ... ; >> UPDATE ; >> COMMIT; > The first is that it is bulky. If this is in the application code then it > has to be repeated for each desired UPSERT and

[sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

2018-03-22 Thread Jonathan Moules
Hi List, The below query takes just 0.002 seconds to run (sqlite 3.15.0 and 3.23.0 (preview)) despite looking through hundreds of thousands of records in each table, and it returns 86 records in all. This is great! But when I stick an "ORDER BY" on the end (either ASC or DESC), the processin

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

2018-03-22 Thread Simon Slavin
On 22 Mar 2018, at 9:24pm, Jonathan Moules wrote: > But when I stick an "ORDER BY" on the end (either ASC or DESC), the > processing time shoots up to 0.15s. The EXPLAIN between the two is > considerably different so it seems the ORDER BY is getting it to use a > sub-optimal query plan. Hi, J

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

2018-03-22 Thread Richard Hipp
Quick workaround: put a "+" on front of the first term of your ORDER BY clause. On Thursday, March 22, 2018, Jonathan Moules wrote: > Hi List, > > The below query takes just 0.002 seconds to run (sqlite 3.15.0 and 3.23.0 (preview)) despite looking through hundreds of thousands of records in each

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

2018-03-22 Thread Jonathan Moules
Hi Simon, Sure; I didn't include them because the only difference is the last line, and that just seems to be the standard "ordering" line. I figured the explain was more useful as a lot has changed in that. Cheers, Jonathan Fast version: 100SEARCH TABLE lookups USING COVERING IND

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

2018-03-22 Thread Simon Slavin
On 22 Mar 2018, at 10:09pm, Jonathan Moules wrote: > Sure; I didn't include them because the only difference is the last line, and > that just seems to be the standard "ordering" line. I figured the explain was > more useful as a lot has changed in that. I find EXPLAIN QUERY PLANs easier to r

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

2018-03-22 Thread Mark Wagner
When I saw this post I just assumed there wasn't a sufficient index to handle the select and the order by. Curious about the suggestion of adding + to the order by first term. On Thu, Mar 22, 2018 at 3:14 PM Simon Slavin wrote: > On 22 Mar 2018, at 10:09pm, Jonathan Moules > wrote: > > > Sure

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

2018-03-22 Thread Simon Slavin
On 22 Mar 2018, at 10:16pm, Mark Wagner wrote: > Curious about the suggestion of adding + to the order by first term. This stops SQLite from realising it can use an existing index. If you do CREATE INDEX m_s ON members (score) SELECT * FROM members ORDER BY score DESC SQLite cleverly

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

2018-03-22 Thread Jonathan Moules
On 2018-03-22 22:08, Richard Hipp wrote: Quick workaround: put a "+" on front of the first term of your ORDER BY clause. This gives me an ending of: ORDER BY +u.url_id ASC LIMIT 1; Alas it makes no difference to the speed. The sole difference in the EXPLAIN plan when that's added from the

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

2018-03-22 Thread Jonathan Moules
Hi Simon, Yep, I too find the Query Plan's easier to read (sometimes I even think I understand bits of them!) I do expect SQLite to get slower with an Order By - it has more work to do after all, but I thought I'd ask for this one because it's slowing down by almost two orders of magnitude (a

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

2018-03-22 Thread Keith Medcalf
Yes. Here is a stored procedure written in Python that implements a "stored procedure" (that is, it is a procedure and it is indeed stored) that does an "upsert" operation. You pass it the db connection object, the name of the table, a dictionary of the PrimaryKey fields, and a dictionary of

Re: [sqlite] [EXTERNAL] R*Trees query data cached?

2018-03-22 Thread David Ashman - Zone 7 Engineering, LLC
Thanks Gunter.  Below are the execution times: pragma table_value(pragma table_info(adas_link_geometry) 2mspragma table_value(pragma table_info(idx_adas_link_geometry) 1msSELECT 1.1 secondsSELECT 9msSELECT 9msSELECT 9msSELECT 9ms It appears that the bulk of the time is taken up in the initia

Re: [sqlite] The upcoming 3.23.0 release

2018-03-22 Thread Olivier Mascia
> Le 22 mars 2018 à 20:09, Richard Hipp a écrit : > > ... > Please download the latest Pre-release Snapshot > (https://sqlite.org/download.html) and test out the latest SQLite in > your applications. Report any issues, either to this mailing list, or > directly to me at d...@sqlite.org. I am of

[sqlite] Index on expression goes corrupt from valid SQL

2018-03-22 Thread Deon Brewis
I was just reading through this issue: https://www.sqlite.org/src/info/343634942dd54ab Does this bug have any other symptoms other than as specified in the report above? Reason I'm asking is that we are facing quite a bit of database corruption right now. We use a lot of expression indexes (wri

Re: [sqlite] Index on expression goes corrupt from valid SQL

2018-03-22 Thread Richard Hipp
On 3/22/18, Deon Brewis wrote: > > In general, is there anything dangerous that you can do in a custom function > inside an indexed expression that we need to watch out for? Yes: the UDF must give the same answer from the same inputs every single time. If it does not (if the function is not "pu

Re: [sqlite] Index on expression goes corrupt from valid SQL

2018-03-22 Thread Simon Slavin
On 23 Mar 2018, at 12:54am, Deon Brewis wrote: > However, what we see doesn't generally exhibit like the bug describes. The > bug as reported gives errors like this: > "row 1 missing from index idx1" > > Where we instead see things like: > "database disk image is malformed" > > Can it still be

Re: [sqlite] Index on expression goes corrupt from valid SQL

2018-03-22 Thread Deon Brewis
The expression index functions behave deterministic and are marked as such. We run clean under both AVRF and Sanitizer. We also allocate our own objects on our own separate heap (Windows) or zone (MAC) - so we're unlikely to ever get a pointer that points into the SQLITE memory space, even with

Re: [sqlite] Index on expression goes corrupt from valid SQL

2018-03-22 Thread Simon Slavin
On 23 Mar 2018, at 3:02am, Deon Brewis wrote: > PS: I did send an example corrupted file to Richard - I can send it to you as > well if you like? Richard will find anything there is to find. I do hope the problem gets solved since you seem to have put so much work into it and done everything

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

2018-03-22 Thread Rowan Worth
On 23 March 2018 at 05:24, Jonathan Moules wrote: > Hi List, > > The below query takes just 0.002 seconds to run (sqlite 3.15.0 and 3.23.0 > (preview)) despite looking through hundreds of thousands of records in each > table, and it returns 86 records in all. This is great! > > But when I stick a

Re: [sqlite] SQLITE_CANTOPEN_ISDIR and other extended error codes

2018-03-22 Thread Rowan Worth
On 20 March 2018 at 22:33, Deon Brewis wrote: > How do you actually get a SQLITE_CANTOPEN_ISDIR error? > > In order to get an extended result code, we need to pass a sqlite3* > connection, but you don't have that if the file can't be opened in the > first place. > I understand why you'd think th

Re: [sqlite] Index on expression goes corrupt from valid SQL

2018-03-22 Thread Rowan Worth
On 23 March 2018 at 08:54, Deon Brewis wrote: > Most of the time when the database gets corrupted, we don't crash, it > corrupts midway through valid SQL (no pattern to it - completely unrelated > SQL). I was thinking if the expression functions have bugs in them it could > cause corruption, but