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

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 >

Re: [sqlite] SQLITE_CANTOPEN_ISDIR and other extended error codes

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

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

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

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

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

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

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

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"

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

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

[sqlite] Index on expression goes corrupt from valid SQL

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

Re: [sqlite] The upcoming 3.23.0 release

> 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

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

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

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

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] How to get ORDER BY / LIMIT to stick to the fast-path?

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

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

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?

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

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

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

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

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

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

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

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

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

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

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 >

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

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

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

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

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

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] The upcoming 3.23.0 release

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] How many AUTOINCREMENT tables are in your schema?

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

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

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

[sqlite] The upcoming 3.23.0 release

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

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

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

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

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

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

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

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

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 >

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

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

Re: [sqlite] Unexpected optimization

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

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

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)

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

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 many AUTOINCREMENT tables are in your schema?

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

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

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

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

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

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

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

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

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

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

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"

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

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

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

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:

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

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,

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

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"

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

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?

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

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

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:

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

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

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

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

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

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?

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

Re: [sqlite] [EXTERNAL] Unexpected optimization

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 many AUTOINCREMENT tables are in your schema?

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] Unexpected optimization

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

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

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

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

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

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

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] How to convert a datetime column to a date?

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

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

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

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

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:

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

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

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

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

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

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

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

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

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

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)

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

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

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

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