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

[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 can't really imagine how. We use sqlite3_result_*
> functions to report results, and even if we read invalid memory or pass
> invalid memory to SQLITE, it should crash - not corrupt.
>

Do you have an sqlite logging callback installed
(sqlite3_config(SQLITE_CONFIG_LOG, ...))?
The messages on that channel include more details (eg. the line number of
the sqlite3 source where the corruption was first noticed) for some
instances of corruption. Might help to pin down the issue.

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_CANTOPEN_ISDIR and other extended error codes

[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 that, but check the docs:

https://www.sqlite.org/capi3ref.html#sqlite3_open

> A database connection handle is usually returned in *ppDb, __even if an
error occurs__.
> The only exception is that if SQLite is unable to allocate memory to hold
the sqlite3 object,
> a NULL will be written into *ppDb instead of a pointer to the sqlite3
object.

So we're not looking at an "either an error or sqlite3* is returned" - you
can get both. The other thing to takeaway:

> Whether or not an error occurs when it is opened, resources associated
with the
> database connection handle should be released by passing it to
sqlite3_close()
> when it is no longer required.

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 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.
> If I put a LIMIT 1 on the end, the speed remains at 0.15s, but the query
> plan is different again. The fast EXPLAIN and the ORDER BY/LIMIT EXPLAIN
> are also here (they start changing at item 36).
>
> Any suggestions for what's going on here and how to coerce the planner to
> stick to the fast-path and then do a simple order by on those 86 (or
> however many - it'll always be a low number) results?
> (ANALYZE has been run)
>

Does it help if you move the ORDER BY to an outer select? ie:

SELECT id, u, err FROM (
SELECT u.url_id, u.url, l.error_code ...
) ORDER BY id;

If the query planner flattens the subquery this probably won't make a
difference though...
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 an allocation/free 
bug. I know this is the most likely cause of corruption, and that's in our own 
code, but so far I can't find it.

Bad hardware is extremely unlikely since we've hit this hundreds of times over 
the last couple of months on 100s of different physical hardware and on 
different hardware platforms (PC, MAC, iOS, Android).

I sleep with "How To Corrupt An SQLite Database File" under my pillow. I'd be 
able to tell if you moved a comma on that page from one day to the next... I've 
looked for every one of those items multiple times over.

PS: I did send an example corrupted file to Richard - I can send it to you as 
well if you like?

- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Simon Slavin
Sent: Thursday, March 22, 2018 7:20 PM
To: SQLite mailing list 
Subject: 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"
> 
> Can it still be related to the same issue?

I don't think so.  That particular error was not at all caught by the 
'malformed' error you're seeing.  It was reported as a corrupt index.

> 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?
> 
> 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 can't really imagine how.

Is the function deterministic ?  Is it marked as deterministic when you define 
it ?



> We use sqlite3_result_* functions to report results, and even if we read 
> invalid memory or pass invalid memory to SQLITE, it should crash - not 
> corrupt.

None of this should be happening unless -- please excuse my boldness -- the 
fault is in your own code.  Can you run your code under a tool which checks 
memory allocation and release, just to see if memory really is your problem ?

> We use sqlite3_result_* functions to report results, and even if we read 
> invalid memory or pass invalid memory to SQLITE, it should crash - not 
> corrupt.

To keep things fast, SQLite tends to assume that it itself is sane and doesn't 
do all the checking it could do.  But errors of this sort aren't usually 
difficult to track down.

A possibility you haven't mentioned is bad hardware.  It possible you have no 
software problems at all and your corruption is purely a bad sector in your 
storage medium.  Other than that, you might want to read



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7C%7C47b2838879894d62813408d59064aa7d%7C84df9e7fe9f640afb435%7C1%7C0%7C636573684383940382=4kf58cJlAUKQJeXAIT7CMXP8mLhhqxYWzoCx%2F7mjCRk%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 related to the same issue?

I don't think so.  That particular error was not at all caught by the 
'malformed' error you're seeing.  It was reported as a corrupt index.

> 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?
> 
> 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 can't really imagine how.

Is the function deterministic ?  Is it marked as deterministic when you define 
it ?



> We use sqlite3_result_* functions to report results, and even if we read 
> invalid memory or pass invalid memory to SQLITE, it should crash - not 
> corrupt.

None of this should be happening unless -- please excuse my boldness -- the 
fault is in your own code.  Can you run your code under a tool which checks 
memory allocation and release, just to see if memory really is your problem ?

> We use sqlite3_result_* functions to report results, and even if we read 
> invalid memory or pass invalid memory to SQLITE, it should crash - not 
> corrupt.

To keep things fast, SQLite tends to assume that it itself is sane and doesn't 
do all the checking it could do.  But errors of this sort aren't usually 
difficult to track down.

A possibility you haven't mentioned is bad hardware.  It possible you have no 
software problems at all and your corruption is purely a bad sector in your 
storage medium.  Other than that, you might want to read



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 "pure") then the
index can go corrupt.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index on expression goes corrupt from valid SQL

[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 (written in custom C++ code), 
which now sounds suspicious.

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 related to the same issue?


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?

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 can't really imagine how. We use sqlite3_result_* functions to 
report results, and even if we read invalid memory or pass invalid memory to 
SQLITE, it should crash - not corrupt.

- Deon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The upcoming 3.23.0 release

[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 course not really much concerned by this detail — yet I'm writing an 
email about it :).  Since 3.22 and now with 3.23 snapshot as of today, latest 
updates of Visual Studio 2017 report this warning:

"shell.c(2377): warning C4996: 'chmod': The POSIX name for this item is 
deprecated. Instead, use the ISO C and C++ conformant name: _chmod."

It occurs at two places in shell.c, and the same thing occurs once for unlink 
(_unlink).

In their latest versions of the documentation about their UCRT they say:

> The C++ standard reserves names that begin with an underscore in the global 
> namespace to the implementation. Because the POSIX functions are in the 
> global namespace, but are not part of the standard C runtime library, the 
> Microsoft-specific implementations of these functions have a leading 
> underscore. For portability, the UCRT also supports the default names, but 
> the Visual C++ compiler issues a deprecation warning when code that uses them 
> is compiled. Only the default POSIX names are deprecated, not the functions. 
> To suppress the warning, define _CRT_NONSTDC_NO_WARNINGS before including any 
> headers in code that uses the original POSIX names.

See: https://docs.microsoft.com/en-us/cpp/c-runtime-library/compatibility

Adding a #define _CRT_NONSTDC_NO_WARNINGS to shell.c might indeed be a nice way 
to go.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 initial query.  
Presumably, there is buffering that occurs both in SQLite and my file system to 
assist in future queries.  I've changed some buffer parameters in my file 
system initialization and was able to reduce the initial query time from 1.6 
seconds to 1.1 seconds.  I've also asked Segger file system tech support for 
some help understanding the initial query time as it applies to file 
reads/buffering.  Using sqlite3_config() I've changed some SQLite page cache 
and heap parameters, and disabled a few unwanted #defines which has decreased 
initial query time.  Do the above times give you any clues about the initial 
query time?  Any other ideas would be helpful.
Thanks again.


  From: Hick Gunter 
 To: 'SQLite mailing list'  
 Sent: Wednesday, March 21, 2018 1:29 AM
 Subject: Re: [sqlite] [EXTERNAL] R*Trees query data cached?
  
The .describe is just to make sure the SQLite has loaded the table definition. 
Try pragma table_info();

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von David Ashman - Zone 7 Engineering, LLC
Gesendet: Dienstag, 20. März 2018 22:45
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] R*Trees query data cached?

Thank you for the quick reply Hick.  I've implemented your script file in C 
since I'm running this application in the embedded world with no OS.  I don't 
see a .describe in the SQLite documentation.  I've tried to use .schema but 
that returns an error.  Do you have another suggestion to obtain the table 
information you had in mind?





      From: Hick Gunter 
 To: 'SQLite mailing list' 
 Sent: Tuesday, March 20, 2018 9:04 AM
 Subject: Re: [sqlite] [EXTERNAL] R*Trees query data cached?

SQLite does not have "query caching". It does have a "page cache" that will 
keep heavily used pages iin memory. There is also the possibility of a 
file-system/os-level cache. To break down the 1.6 seconds required for the 
first query, try executing an sql script. In linux this would be along the 
lines of:

> date; sqlite3 file.db < script.sql;date

With script.sql containing:

Select strftime('%Y-%m-%d %H:%M:%S.%f'); Select strftime('%Y-%m-%d 
%H:%M:%S.%f'); .describe adas_link_geometry Select strftime('%Y-%m-%d 
%H:%M:%S.%f'); .describe idx_adas_link_geometry Select strftime('%Y-%m-%d 
%H:%M:%S.%f'); Select ; Select strftime('%Y-%m-%d 
%H:%M:%S.%f'); Select ; Select strftime('%Y-%m-%d 
%H:%M:%S.%f'); Select ; Select strftime('%Y-%m-%d %H:%M:%S.%f');

This should give you an idea of where the time is being spent.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von David Ashman - Zone 7 Engineering, LLC
Gesendet: Dienstag, 20. März 2018 16:34
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] R*Trees query data cached?

Hello -
I have a question on SQLite query data buffering.
I'm successfully using SQLite v3.22.0 on an embedded ARM processor from ST with 
SD card.  The database file size is about 750MB.  The file system is Segger 
emFile FAT32.  I've configured SQLite to use 6MB RAM for heap.  I've done some 
query time benchmarking and found that the very first R*Trees query takes about 
1.6 seconds to complete.  Each successive R*Trees query (same query string with 
slightly different search parameters) takes about 11ms to complete.  Being new 
to SQLite and spatial queries, I'm trying to understand the substantial query 
time differences... does SQLite cache data from each query for future queries? 
The initial query:SELECT LINK_ID, FROM_REF_ELEVATION, TO_REF_ELEVATION FROM 
adas_link_geometry, idx_adas_link_geometry WHERE adas_link_geometry.ROWID = 
idx_adas_link_geometry.id AND minLat > 454760320 AND maxLat < 454800320 AND 
minLong > -1226807072 AND maxLong < -1226767072;

Thanks in advance,Dave
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 the data 
fields.  It does the "upsert" and returns the associated rowid of the record 
... and it only needs to be written once.  Of course, some source languages are 
not so nice and require far more complicated "stored procedures", however, you 
only have to design and write them once.


from __future__ import print_function

def upsert(db=None, table=None, pk=None, data=None, debug=False):
if not (db and table and pk):
raise ValueError('must specify db, table, pk')
if not all(pk.values()):
raise ValueError('PrimaryKeys must not be null')
alldata = dict()
alldata.update(pk)
sqlUpdate = ''
if data:
alldata.update(data)
sqlUpdate = ''.join(['UPDATE ',
 table,
 ' SET (',
 ', '.join(data.keys()),
 ') = (:',
 ', :'.join(data.keys()),
 ') WHERE (',
 ', '.join(pk.keys()),
 ') == (:',
 ', :'.join(pk.keys()),
 ');'
])
sqlInsert = ''.join(['INSERT OR IGNORE INTO ',
 table,
 ' (',
 ', '.join(alldata.keys()),
 ') values (:',
 ', :'.join(alldata.keys()),
 ');'
])
sqlSelect = ''.join(['SELECT id ',
 'FROM ',
 table,
 ' WHERE (',
 ', '.join(pk.keys()),
 ') == (:',
 ', :'.join(pk.keys()),
 ');'
])
if debug:
if sqlUpdate:
print(sqlUpdate)
print(sqlInsert)
print(sqlSelect)
cr = db.cursor()
cr.execute('SAVEPOINT %s%s' % ('UpSert', table))
if data:
cr.execute(sqlUpdate, alldata)
cr.execute(sqlInsert, alldata)
id = None
try:
for row in cr.execute(sqlSelect):
id = row[0]
except:
pass
cr.execute('RELEASE %s%s;' % ('UpSert', table))
cr.close()
return id

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux
>Sent: Thursday, 22 March, 2018 13:18
>To: SQLite mailing list
>Subject: 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 database.
>
>The second is that it seems so inefficient in the case of a new row
>being
>inserted. The row is inserted and then immediately updated. Why do
>both
>operations when only one is needed?
>
>Is it possible to write a stored procedure that checks a result of
>the
>INSERT OR IGNORE and only attempts the UPDATE if the row already
>existed?
>That would at least move the bulky code out of the application and
>into the
>database. Also it seems it would be more efficient.
>
>Thanks.
>
>Peter
>
>
>Peter
>
>
>
>On Wed, Mar 21, 2018 at 6:15 PM, Kees Nuyt  wrote:
>
>> On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux
>>  wrote:
>>
>> > You are right that my purpose does seem kind of confusing.
>> >
>> > What I really want is UPSERT to avoid doing an UPDATE, checking
>if the
>> > number of rows affected is zero, then doing an INSERT. The lack
>of UPSERT
>> > leads to a lot more application code using the UPDATE/INSERT
>combination.
>> > UPSERT doesn't exist in SQLite so I was trying to work around
>that by
>> using
>> > INSERT OR REPLACE which is not the same thing. I can see from
>another
>> > recent thread that some others also think that UPSERT would be a
>valuable
>> > addition to SQLite.
>>
>> I fail to see the problem in
>> BEGIN;
>> INSERT OR IGNORE ... ;
>> UPDATE  ;
>> COMMIT;
>> Simple code, no need to test number of affected rows, and pretty
>> fast because the relevant pages will be in cache.
>>
>> Or use an updatable view with an INSTEAD OF INSERT trigger.
>>
>> I did notice that attempts to define a proper UPSERT syntax
>> opened a can of worms by itself because it (also) has to provide

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

[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 bit less in 3.23.0) when I add 
the ORDER which seems quite a lot for ordering 86 items. I have no idea 
what the EXPLAIN /says/, but I can see it's very different between the 
two of them so this suggested the QP might be going astray.


Even if I delete all the indexes it's using, it still evidences, though 
the timings are higher (0.1s without, 0.2s with ORDER BY/LIMIT).

Cheers,
Jonathan

On 2018-03-22 22:13, Simon Slavin wrote:

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 read (probably unlike the development team 
who understand things at the level of EXPLAIN).

The situation as you describe it in the above post is that your query gets 
/slower/ when you add an ORDER BY clause.  This is not expected, and does not 
suggest anything wrong with SQLite.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 ORDER BY/LIMIT one posted in the 
question is this line:


59Copy2523000

Where the number 25 becomes the number 19.
(Note: The EXPLAINs are from 3.15)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 spots that the index is useful.  However

SELECT * FROM members ORDER BY +score DESC

has SQLite looking for "+score" but find "score", and they are clearly 
different, so SQLite will not use index m_s.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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; 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 read (probably unlike the development
> team who understand things at the level of EXPLAIN).
>
> The situation as you describe it in the above post is that your query gets
> /slower/ when you add an ORDER BY clause.  This is not expected, and does
> not suggest anything wrong with SQLite.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 read (probably unlike the development team 
who understand things at the level of EXPLAIN).

The situation as you describe it in the above post is that your query gets 
/slower/ when you add an ORDER BY clause.  This is not expected, and does not 
suggest anything wrong with SQLite.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 INDEX sgdsfweer 
(url_id=?)

100EXECUTE LIST SUBQUERY 2
200SEARCH TABLE urls USING COVERING INDEX 
sqlite_autoindex_urls_1 (url=?)

002SCAN SUBQUERY 1 AS recent
011SCAN TABLE lookups AS l
020SEARCH TABLE urls AS u USING INTEGER PRIMARY KEY (rowid=?)


Slow version (Order by and LIMIT):
100SEARCH TABLE lookups USING COVERING INDEX sgdsfweer 
(url_id=?)

100EXECUTE LIST SUBQUERY 2
200SEARCH TABLE urls USING COVERING INDEX 
sqlite_autoindex_urls_1 (url=?)

002SCAN SUBQUERY 1 AS recent
011SCAN TABLE lookups AS l
020SEARCH TABLE urls AS u USING INTEGER PRIMARY KEY (rowid=?)
000USE TEMP B-TREE FOR ORDER BY

On 2018-03-22 22:01, Simon Slavin wrote:

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

Could you post details similar to the ones in that post, but instead of using 
EXPLAIN use EXPLAIN QUERY PLAN ?  This will give results on a scale which is 
easier to understand in the context of your queries and indexes.



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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
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
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.
> If I put a LIMIT 1 on the end, the speed remains at 0.15s, but the query
plan is different again. The fast EXPLAIN and the ORDER BY/LIMIT EXPLAIN
are also here (they start changing at item 36).
>
> Any suggestions for what's going on here and how to coerce the planner to
stick to the fast-path and then do a simple order by on those 86 (or
however many - it'll always be a low number) results?
> (ANALYZE has been run)
>
> Thanks,
> Jonathan
>
> SELECT
> u.url_id, u.url, l.error_code
> FROM
> urls u
> JOIN
> lookups l
> USING(url_id)
> JOIN (
> SELECT
> url_id,
> MAX(retrieval_datetime) AS retrieval_datetime
> FROM lookups
> WHERE
> url_id IN (
> SELECT url_id FROM urls WHERE url = 'example.com'
> )
> ) recent
>
> ON u.source_seed_id = recent.url_id
> OR u.url_id = recent.url_id
> WHERE
> l.is_generic_flag = 1
> AND
> l.retrieval_datetime >= recent.retrieval_datetime
> AND
> DATETIME(recent.retrieval_datetime) > DATETIME('now', '-14 days',
'start of day')
>
>
> 
> Fast EXPLAIN:
>
> 0Init063000
> 1Integer351000
> 2Once035000
> 3OpenEphemeral22000
> 4Null02400
> 5OpenRead5503150k(4,-,,,)02
> 6Once016000
> 7OpenEphemeral710k(1,B)00
> 8OpenRead880k(2,,)02
> 9String8060example.com00
> 10SeekGE8156100
> 11IdxGT8156100
> 12IdxRowid87000
> 13MakeRecord718C00
> 14IdxInsert78000
> 15Close80000
> 16Rewind728000
> 17Column70500
> 18IsNull527000
> 19SeekLE5275100
> 20IdxLT5275100
> 21Column51800
> 22CollSeq900(BINARY)00
> 23AggStep0083max(1)01
> 24If926000
> 25Column50200
> 26Prev520000
> 27NextIfOpen717000
> 28Close50000
> 29AggFinal310max(1)00
> 30SCopy210000
> 31SCopy311000
> 32MakeRecord102800
> 33NewRowid212000
> 34Insert281208
> 35Return10000
> 36OpenRead190800
> 37OpenRead070500
> 38Rewind260000
> 39Column211400
> 40Function001413datetime(-1)01
> 41Le15591351
> 42Rewind159000
> 43Column171300
> 44Ne165813(BINARY)53
> 45Column131400
> 46Column211700
> 47Lt175814(BINARY)53
> 48Column111800
> 49SeekRowid0581800
> 50Column041900
> 51Column202000
> 52Eq205419(BINARY)43
> 53Ne205818(BINARY)53
> 54Copy1822000
> 55Column012300
> 56Column162400
> 57ResultRow223000
> 58Next143001
> 59Next239001
> 60Close10000
> 61Close00000
> 62Halt00000
> 63Transaction00496001
> 64TableLock090lookups00
> 65TableLock070urls00
> 66String80250now00
> 67String80260-14 days00
> 68String80270start of day00
> 69Function072515datetime(-1)03
> 70Integer116000
> 71Goto01000
>
>
> ---
> ORDER BY and LIMIT Explain:
> 0   

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

[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, Jonathan.

Could you post details similar to the ones in that post, but instead of using 
EXPLAIN use EXPLAIN QUERY PLAN ?  This will give results on a scale which is 
easier to understand in the context of your queries and indexes.



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 
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.
If I put a LIMIT 1 on the end, the speed remains at 0.15s, but the query 
plan is different again. The fast EXPLAIN and the ORDER BY/LIMIT EXPLAIN 
are also here (they start changing at item 36).


Any suggestions for what's going on here and how to coerce the planner 
to stick to the fast-path and then do a simple order by on those 86 (or 
however many - it'll always be a low number) results?

(ANALYZE has been run)

Thanks,
Jonathan

SELECT
u.url_id, u.url, l.error_code
FROM
urls u
JOIN
lookups l
USING(url_id)
JOIN (
SELECT
url_id,
MAX(retrieval_datetime) AS retrieval_datetime
FROM lookups
WHERE
url_id IN (
SELECT url_id FROM urls WHERE url = 'example.com'
)
) recent

ON u.source_seed_id = recent.url_id
OR u.url_id = recent.url_id
WHERE
l.is_generic_flag = 1
AND
l.retrieval_datetime >= recent.retrieval_datetime
AND
DATETIME(recent.retrieval_datetime) > DATETIME('now', '-14 
days', 'start of day')




Fast EXPLAIN:

0Init063000
1Integer351000
2Once035000
3OpenEphemeral22000
4Null02400
5OpenRead5503150k(4,-,,,)02
6Once016000
7OpenEphemeral710k(1,B)00
8OpenRead880k(2,,)02
9String8060example.com00
10SeekGE8156100
11IdxGT8156100
12IdxRowid87000
13MakeRecord718C00
14IdxInsert78000
15Close80000
16Rewind728000
17Column70500
18IsNull527000
19SeekLE5275100
20IdxLT5275100
21Column51800
22CollSeq900(BINARY)00
23AggStep0083max(1)01
24If926000
25Column50200
26Prev520000
27NextIfOpen717000
28Close50000
29AggFinal310max(1)00
30SCopy210000
31SCopy311000
32MakeRecord102800
33NewRowid212000
34Insert281208
35Return10000
36OpenRead190800
37OpenRead070500
38Rewind260000
39Column211400
40Function001413datetime(-1)01
41Le15591351
42Rewind159000
43Column171300
44Ne165813(BINARY)53
45Column131400
46Column211700
47Lt175814(BINARY)53
48Column111800
49SeekRowid0581800
50Column041900
51Column202000
52Eq205419(BINARY)43
53Ne205818(BINARY)53
54Copy1822000
55Column012300
56Column162400
57ResultRow223000
58Next143001
59Next239001
60Close10000
61Close00000
62Halt00000
63Transaction00496001
64TableLock090lookups00
65TableLock070urls00
66String80250now00
67String80260-14 days00
68String80270start of day00
69Function072515datetime(-1)03
70Integer116000
71Goto01000


---
ORDER BY and LIMIT Explain:
0Init077000
1Integer351000
2Once035000
3OpenEphemeral22000
4Null02400
5OpenRead5503150k(4,-,,,)02
6Once016000
7OpenEphemeral710k(1,B)00
8OpenRead880k(2,,)02
9

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

[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 it has to be repeated in the
> code of each application that uses the database.

From what I can remember, most of the suggested formats for an UPSERT
command (except perhaps R. Smith's "NOT"/"KEEP" idea) involve two
lists of fields/values, so would be similarly "bulky" as separate
INSERT and UPDATE commands, and need similar amounts of application
code.

> The second is that it seems so inefficient in the case of a new row being
> inserted. The row is inserted and then immediately updated. Why do both
> operations when only one is needed?

There are at least two alternatives (hopefully I've given correct
attribution): 

  o  "INSERT OR IGNORE" the "key" fields (ensures they now exist)
 followed by an UPDATE for the remaining fields (Simon Slavin).

  o  "INSERT OR IGNORE" all values; if sqlite3_changes() indicates
 nothing changed (because the record is already present), perform
 the UPDATE (Olivier Mascia).

> Is it possible to write a stored procedure that checks a result of the
> INSERT OR IGNORE and only attempts the UPDATE if the row already existed?
> That would at least move the bulky code out of the application and into the
> database. Also it seems it would be more efficient.

In some cases, e.g. where the data to be UPSERTed is in a (possibly
transient) table you can use a TRIGGER to perform the UPDATE part. (At
the risk of blowing my own trumpet, see an answer of mine on
StackOverflow: https://stackoverflow.com/a/22481731/2096401).


Regards,
Graham



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 because I get wrong Results.
>> So for now, with this code above I get followings ( for three school
>> weeks
>> ):
>>
>> 2017-09-01|F|1-2
>> 2017-09-01|F|3-4
>> 2017-09-01|F|5-6
>> 2017-09-04|M|1-2
>> 
>>
>> but I want followings ( for three school weeks ):
>>
>> 2017-09-01|F|1-2|5|b
>> 2017-09-01|F|1-2|7|c
>> 2017-09-04|M|1-2|7|b
>> 2017-09-04|M|1-2|5|a
>> 
>>
>> So what SQL command should I use for this?
> 
> I'm not exactly sure how the classes work, it's hard to establish from 
> your explanation and SQL example output, for instance, in the output, 
> why does Friday 1 September 2017 only have lesson-block 1-2 for grades 
> 5b and 7c... why not lesson blocks 3-4 or 5-6? ARe the lesson blocks 
> only active on certain days? If so, we need a table to specify it so.
> 
> I like your table specifying the TimeTables. It needed fixing because 
> the VALUES mechanism works a bit differently than you think, but you can 
> compare between your and my version to see exactly how that works.
> Like I said, it's a bit unclear, but I'm going to assume some things and 
> suggest this:
> 
> WITH PAR(calStartDate, calEndDate) AS (
>      SELECT '2017-09-01', '2017-09-21'
> ),DoW(dayId,dayName) AS (
>      VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),(4,'Th'),
> (5,'F'),(6,'Sa')
> ),LBs(lessonBlock) AS (
>      VALUES ('1-2'), ('3-4'), ('5-6')/*, ('7-8'),('9-10'), ('11-12'), 
> ('13-14'), ('15-16'), ('17-18'), ('19-20'),
>     ('21-22'), ('23-24'), ('25-26'), ('27-28'), ('29-30'), 
> ('31-32'), ('33-34'), ('35-36'), ('37-38'), ('39-40'),
>     ('41-42'), ('43-44'), ('45-46'), ('47-48'), ('49-50'), 
> ('51-52'), ('53-54'), ('55-56'), ('57-58'), ('59-60'),
>     ('61-62'), ('63-64'), ('65-66'), ('67-68'), ('69-70'), 
> ('71-72')  */
> ), TimeTable(DoWeek,Grade,Class) AS (
>   VALUES ('M',7,'b'),('M',5,'a'),
>      ('Tu',8,'c'),('Tu',8,'b'),
>      ('W',8,'a'),('W',7,'a'),
>      ('Th',6,'a'),('Th',5,'c'),
>      ('F',5,'b'),('F',7,'c')
> ), CAL(dayDate,nextDay,dayId) AS (
>      SELECT date(calStartDate,'-1 day'), date(calStartDate), -1 FROM PAR
>   UNION ALL
>      SELECT nextDay, date(nextDay,'+1 day'), CAST(STRFTIME('%w',nextDay) 
> AS INT) FROM CAL,PAR WHERE nextDay <= calEndDate
> ), RES(dayDate, dayName, lessonBlock, Grade, Class) AS (
>      SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock, TimeTable.Grade, 
> TimeTable.Class
>    FROM CAL
>   CROSS JOIN LBs
>    JOIN DoW ON DoW.dayID = CAL.dayId
>    JOIN TimeTable ON TimeTable.DoWeek = DoW.dayName
>   WHERE CAL.dayId > 0 AND CAL.dayId < 6  -- No Sundays and No
> Saturdays
> )
> SELECT *
>      FROM RES;
> 
> 
> I kept the majority of lesson blocks commented out for brevity, but if 
> it works, you can uncomment them and everything should work.
> 
> I hope it works, but if not, feel free to ask again, perhaps with a more 
> exact explanation of what the output should contain and how the lesson 
> blocks work.
> 
> 
> Cheers!
> Ryan

I tried it out, but it not gives the expected result.
I modified the 'Class' column name to 'ClassName' everywhere where it
occures, eg. like this:
TimeTable(DoWeek,Grade,ClassName)
and
TimeTable.ClassName

in order to make it more understandable.
Then I run it but it gives not the expected results.
This is because I was not clear enough about how the lesson blocks works.
But now this is what I'm supposed to do. My first language is not English,
so sorry if I fail in my attempt to explain this.
So in a school year on grade/class name ( like 5/a, 5/b, 5/c or 6/b, etc.)
must take 36 lesson blocks where a lesson block is built up of two lessons -
school hours - and lasts twice for 45 minutes. So in a school year there is
72 lessons, so 72/2=36 lesson blocks out there.
The lesson blocks I call like this: 1-2, 3-4, etc.
But there are school weeks too. In a school year there are 36 school weeks
out there.
In a school year there are 36 lesson blocks too. We numbering lesson blocks
in a school week, like this:
in the 1. school week (SW) the lesson block is called 1-2,
in the 2. SW 3-4,
...
in the 35. SW 69-70, and finally
in the 36. SW 71-72.

Now about the TimeTable.
On Friday 2017-09-01 I had one lesson block ( the 1-2 ) with 5/b Class and
one lesson block ( the 1-2 ) with 7/c Class.
On the next Friday I had one lesson block ( the 3-4 ) with 5/b Class and one
lesson block ( the 3-4 ) with 7/c Class.
And so on.
Say on Friday 2018-06-08 I will have one lesson block ( the 71-72 ) with 5/b
Class and one lesson block ( the 71-72 ) with 7/c Class.

So the records from the
SELECT *
 FROM RES;

should gives the followings ( see the Fridays how changes the 

Re: [sqlite] The upcoming 3.23.0 release

[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"



Wording changes case from "is" in item 1 to "must" in items 2 and 3.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
> 
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement |
> wc -l
> 
> Private email to me is fine.  Thanks for participating in this survey!
> -- 
> D. Richard Hipp
> d...@sqlite.org


---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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

The second is that it seems so inefficient in the case of a new row being
inserted. The row is inserted and then immediately updated. Why do both
operations when only one is needed?

Is it possible to write a stored procedure that checks a result of the
INSERT OR IGNORE and only attempts the UPDATE if the row already existed?
That would at least move the bulky code out of the application and into the
database. Also it seems it would be more efficient.

Thanks.

Peter


Peter



On Wed, Mar 21, 2018 at 6:15 PM, Kees Nuyt  wrote:

> On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux
>  wrote:
>
> > You are right that my purpose does seem kind of confusing.
> >
> > What I really want is UPSERT to avoid doing an UPDATE, checking if the
> > number of rows affected is zero, then doing an INSERT. The lack of UPSERT
> > leads to a lot more application code using the UPDATE/INSERT combination.
> > UPSERT doesn't exist in SQLite so I was trying to work around that by
> using
> > INSERT OR REPLACE which is not the same thing. I can see from another
> > recent thread that some others also think that UPSERT would be a valuable
> > addition to SQLite.
>
> I fail to see the problem in
> BEGIN;
> INSERT OR IGNORE ... ;
> UPDATE  ;
> COMMIT;
> Simple code, no need to test number of affected rows, and pretty
> fast because the relevant pages will be in cache.
>
> Or use an updatable view with an INSTEAD OF INSERT trigger.
>
> I did notice that attempts to define a proper UPSERT syntax
> opened a can of worms by itself because it (also) has to provide
> two colum lists, one for a full INSERT if the row with that PK
> doesn't exist, and another one for the columns to be updated
> when the row already exists. So, I don't see a big advantage in
> UPSERT.
>
> My humble two cents,
>
> --
> Regards,
> Kees Nuyt
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] The upcoming 3.23.0 release

[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 issues, either to this mailing list, or
directly to me at d...@sqlite.org.

All of our regression tests are passing with 100% branch coverage.
However, passing over 100 million test cases do not guarantee that all
is well, since developers who use SQLite tend to be very creative and
wind up using SQLite in ways that we never thought to test.  Hence,
your independent verification is helpful to us and much appreciated.

The release checklist for 3.23.0 is found at

https://www.sqlite.org/checklists/323/index

We have not yet frozen the code nor started the release checklist.
But we will do that soon. You can follow our progress on the
checklist.  The release will occur when the checklist goes all-green.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 definition sais "datetime", not
> "varchar", so if it's being treated as a string what can I do to fix it to be
> treated as a actual datetime datatype?

SQLite has no DATETIME or BOOLEAN datatype, see:



Personally I store all date/times as seconds since the epoch; it simplifies 
life a lot.



-- 
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 fiddling, I finally got something that seems to work.

One observation that's relevant to you; the suggested:

./configure; make sqlite3.c

results in a make error (I'm running it in a Linux Mint VM):

config.status: executing libtool commands
make: Warning: File 'Makefile' has modification time 2.4 s in the future
make: Nothing to be done for 'sqlite3.c'.
make: warning: Clock skew detected. Your build may be incomplete.

No idea what it's on about - I have no clock issues on the host machine 
and the time appears to be correct in the VM. Got around it in the end 
by running it as two commands.


--

Back to the issue at hand using my modestly populated test database - 
all six variations of the query (Order BY ASC; ORDER BY DESC; No Order 
By or LIMIT; - each of those twice, once with JOINs once with LEFT 
JOINs) take the same amount of time in the 3.23.0 build - 0.33seconds. 
(I used the built in ".timer on").


This is the same speed as three of the queries, and faster than one of 
them in 3.15, BUT this is still several times slower than 3.15 on the 
same database (but in Windows) for two of the queries.


The below query, along with the no ORDER BY / LIMIT variant both take 
just 0.08s to run in 3.15.


SELECT
u.url_id, u.url, l.error_code
FROM
urls u
left JOIN
lookups l
USING(url_id)
left JOIN
(select * from v_most_recent_lookup_per_url where url_id in (
select url_id from urls where url = 
'http://catalogue.beta.data.wa.gov.au/api/3/action/resource_search?limit=100=0=format%3AWFS'

)) recent
   -- By definition url's can) recent
-- This is the important bit
-- Here we use the most recent lookup url_id to link to the 
source_seed_id, so we only find its children

-- Or alternatively itself
ON u.source_seed_id = recent.url_id
OR u.url_id = recent.url_id
WHERE
-- For JSON-spider at least, Generic's are guaranteed to be 
generic pages.

l.is_generic_flag = 1
AND
-- Must be "or equal to" so we can get the lookup of the very 
base url.

l.retrieval_datetime >= recent.retrieval_datetime
AND
DATETIME(recent.retrieval_datetime) > DATETIME('now', '-14 
days', 'start of day')

ORDER BY
u.url_id aSC
LIMIT 1;


Although the two "fast" queries are much slower if a non-existent url is 
used (goes up to about 0.5s in 3.15 for the same query)


Cheers,
Jonathan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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
> > int primary key can be auto incrementing, it can't.
>
> an INT primary key cannot be autoincrementing
>
> An INTEGER primary key and an INTEGER primary key autoincrement work in
> essentially the same way. i.e. if you insert a row and do not specifically
> assign a value to the pk (i.e. you assign NULL) the value assigned will
> usually be one more than last pk used.
>
> if you have an INT primary key and add a new row with no value assigned to
> the PK then null will be stored (all null values are treated as unique in
> SQLite and so as far as the PK is concerned all rows are different).
>
> SQLite version 3.18.0 2017-03-28 18:48:43
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table test (id int primary key, data text);
> sqlite> insert into test (data) values('row 1');
> sqlite> insert into test (data) values('row 2');
> sqlite> select id, data from test;
>   |row 1
>   |row 2
>
> of course the rowid is still there hidden behind the scenes and you can
> access it with
>
> sqlite> select rowid, id, data from test;
> 1|  |row 1
> 2|  |row 2
>
> but if you want to use the rowid as the PK then you should probably use an
> INTEGER pk so it becomes an alias for the rowid in the first place.
>
> 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 work address for a fully functional demo licence
>
> On 20 March 2018 at 16:44, Chris Locke  wrote:
>
> > >  some people seem to think that an int primary key can be auto
> > incrementing, it can't
> >
> > But it works in the same way  sort of.  Its auto incrementing, with
> the
> > caveat that if the last row is deleted, the previous number will be used
> > again.  Depending on the database schema, this may or may not cause
> issues.
> >
> >
> > Thanks,
> > Chris
> >
> >
> > On Tue, Mar 20, 2018 at 9:45 AM, Paul Sanderson <
> > sandersonforens...@gmail.com> wrote:
> >
> > >  I read that - but my point was more that some people seem to think
> that
> > an
> > > int primary key can be auto incrementing, it can't.
> > >
> > >
> > > SQLite version 3.18.0 2017-03-28 18:48:43
> > > Enter ".help" for usage hints.
> > > Connected to a transient in-memory database.
> > > Use ".open FILENAME" to reopen on a persistent database.
> > > sqlite> create table test (id integer primary key autoincrement);
> > > sqlite> create table test2 (id int primary key autoincrement);
> > > Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
> > > sqlite>
> > >
> > > 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 work address for a fully functional demo licence
> > >
> > > On 20 March 2018 at 08:48, R Smith  wrote:
> > >
> > > >
> > > > On 2018/03/20 10:24 AM, Paul Sanderson wrote:
> > > >
> > > >> Autoincrement can ONLY be used with an integer primary key
> > > >>
> > > >
> > > > I think Peter's shouting is more about the inability to distinguish
> via
> > > > SQL or Pragma between an INTEGER PRIMARY KEY and an INT PRIMARY KEY,
> > both
> > > > of which are of course integer and can be auto-incrementing, but only
> > one
> > > > of which is an alias for rowid.
> > > >
> > > >
> > > >
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@mailinglists.sqlite.org
> > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
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?

[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 "vetting" and in no way needed for the  conversion. 
> If datetime(x) understand x and doesn't produce any NULLs,  then x is a valid 
> ISO8601 date (or valid-enough for SQLite) and all is fine.

Sure, but should you do that there? Because it won't tell you you had bad data, 
it'll just lose it.
 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexpected optimization

[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 -DSQLITE_ENABLE_SELECTTRACE and
-DSQLITE_ENABLE_WHERETRACE.

The command ".selecttrace 0x" will print out the parse tree of
your SQL statement at various points as it is transformed by the query
optimizer.  By viewing this parse tree, you might be better able to
understand the transformations that are taking place, which might give
additional insights into what it going astray for you.

The latest pre-release snapshot for 3.23.0 contains a couple of new
optimizations related to LEFT JOIN.  Please also try your code with
the pre-release snapshot to see if it helps or hurts or makes no
difference.  And please report back what you find, regardless of your
findings.

The ".wheretrace 0xfff" command prints out the steps used by the query
planner as it scores various execution strategies looking for the
fastest way to run your query.  It should clearly show the costs
returned by your xBestIndex implementation, how decisions are made
based on those costs, and help you to see how changing those costs
might result in better plans.

The outputs from .selecttrace and .wheretrace are undocumented.  They
are subject to change.  And they do change in incompatible ways from
time to time.  You'll need to look at the source code to fully
understand what the outputs mean.

Note that the cost numbers printed by .wheretrace are logarithmic.
See the description of the LogEst numbers at
https://www.sqlite.org/src/artifact?ln=755-778=7e9deb145c110289
for additional information. There is a simple command-line program at
https://www.sqlite.org/src/artifact/11346aa019e2e77a that you can
compile and use to convert values between LogEst and traditional
base-10 decimal numbers.

There is older debugging documentation at
https://www.sqlite.org/debugging.html that might give additional
hints.

As you work through this problem, please provide feedback so that we
can improve the documentation for the next person who faces similar
issues.

On 3/22/18, Max Vlasov  wrote:
> 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 StatCount from
> (
>   ... Select TableValue, ... left join  ... left join
>   where 
> )
>where StatCount  = ..
>
> The aggregate lookup (Select count()) is relatively expensive to perform
> and involves a virtual table on my side (LookUpTable). So the goal of
>  is also to narrow the output of the data for this lookup.
> Most of the time (including my synthetic tests) the filter indeed works the
> expected way (Filtering with  then performing the aggregate
> only for the suitable), but for some of queries where there are several
> joins sqlite starts to perform the lookup before applying 
> so I get my expensive calculations used for all rows of the inner joined
> table and then filtering with . I double checked this since
> the LookUpTable is my virtual table so I can set a breakpoint and inspect
> the passed value. Ironically, when I remove the outer condition ( where
> StatCount ..  ) from the query in question, it starts to work much faster.
>
> I suspect this might be related to how I respond to the constraint cost
> requests from sqlite. For this virtual table the possible results might be
> 1 or a very big value. I see that the value 1 is indeed visited for this
> query and probably sqlite might assume some absolute minimum cost for this
> look-up. But when I change it to a bigger value (still lower than "a very
> big value" also used), the query plan will not change.
>
> Here are summary of Explain Query Plan (rea is my virtual table, there are
> 4 joins in this query, sqlite 3.21.0).
>
> The Query with Outer condition "where StatCount  = .."
> SCAN TABLE
> SEARCH TABLE (2 times)
> EXECUTE CORRELATED SCALAR SUBQUERY 1
> SCAN TABLE Rea VIRTUAL TABLE INDEX 2:
> SEARCH TABLE (2 times)
> EXECUTE CORRELATED SCALAR SUBQUERY 2
> SCAN TABLE Rea VIRTUAL TABLE INDEX 2:
>
> The same query when I just removed the outer "where StatCount  = .."
> SCAN TABLE...
> SEARCH TABLE (4 times)
> EXECUTE CORRELATED SCALAR SUBQUERY 1
> SCAN TABLE Rea VIRTUAL TABLE INDEX 2:
>
>
> Can I manually affect the plan for this query or probably by further
> tweaking the virtual table costs?
>
> Thanks
>
>
>
> Thanks
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org

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

[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 x and doesn't produce any NULLs, 
then x is a valid ISO8601 date (or valid-enough for SQLite) and all is fine.


Vetting can however be achieved more simply by a query that doesn't 
alter the data, to allow research into the origin data and finding the 
cause.


I never trust the program making the data to avoid the obvious stray 
character, unneeded quotes, and also things like a date showing leap 
seconds (which might be understood by the target system) or an invalid 
day for a month etc. The OP may however know/trust his source data. I'm 
just overly paranoid. :)




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

[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, particularly section 2.2:
 https://www.sqlite.org/datatype3.html


Sqlite does not use a specific "datetime" type.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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.
>
> > And is there a chance the "some kind of indexed lookup" you mention above
> > is likely to land in the future?
>
> That is still unclear, but probably not.
>
> > If we insert 1M rows in that schema.i DB with 117 AUTOINCREMENT tables,
> are
> > we really full-scanning
> > up to 117M rows just to get the ROWIDs of those 1M rows? Thanks for your
> > insights into this. --DD
>
> No.  You are only doing a full table scan on the 117 entries of the
> sqlite_sequence table.  And as all 117 entries probably all fit on the
> same page, adding an index probably won't speed things up any.
>

What I meant was that each row-insert incurs a sqlite_sequence full-scan,
and thus inserting 1M rows logically incurs the multiplication of both
cardinality.

I already guessed sqlite_sequence is cached, since "extremely hot", but
that's
still a lot of rows even if cached, even more so since sqlite_sequence also
needs
to be *written* to, not just read from, to record the new max rowid.


> Another idea is to implement an in-memory cache so that if you do
> multiple inserts into an AUTOINCREMENT table, it only does the scan of
> sqlite_sequence once.
>
> In private communications with some groups that have a lot of
> AUTOINCREMENT tables and who performance sensitive and who heavily
> instrument their code, nobody has noticed any performance issues
> associated with scanning the sqlite_sequence table.  For that reason,
> we probably are not going to take any action on this right now.  But I
> will hold open the possibility of enhancing the sqlite_sequence lookup
> at some point in the future if somebody (especially a client) notices
> the potential for a performance benefit.


Thanks, this is reassuring. Many thanks for the quick update. --DD
___
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?

[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: sqlite-users@mailinglists.sqlite.org
Subject: 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 final query and not in the data.

Final query should then shorten to the actual date part to be:

SELECT date(dttm) AS dt, max(i) AS max_i FROM foo GROUP BY date(dttm) ORDER BY 
1;



>
>
> 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, March 22, 2018 10:12 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: 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, inside two single quotes.
>
> Followed by your original:
>
> SELECT dttm, max(i) AS max_i FROM foo GROUP BY dttm ORDER BY dttm;
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 like '''%''';

Note that's 4 single quotes in the first string, and two sets of three single 
quotes in the second.

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

[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 query and 
not in the data.


Final query should then shorten to the actual date part to be:

SELECT date(dttm) AS dt, max(i) AS max_i FROM foo GROUP BY date(dttm) ORDER BY 
1;






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, March 22, 2018 10:12 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: 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,
inside two single quotes.

Followed by your original:

SELECT dttm, max(i) AS max_i FROM foo GROUP BY dttm ORDER BY dttm;


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 above
> is likely to land in the future?

That is still unclear, but probably not.

>
> If we insert 1M rows in that schema.i DB with 117 AUTOINCREMENT tables, are
> we really full-scanning
> up to 117M rows just to get the ROWIDs of those 1M rows? Thanks for your
> insights into this. --DD

No.  You are only doing a full table scan on the 117 entries of the
sqlite_sequence table.  And as all 117 entries probably all fit on the
same page, adding an index probably won't speed things up any.

Another idea is to implement an in-memory cache so that if you do
multiple inserts into an AUTOINCREMENT table, it only does the scan of
sqlite_sequence once.

In private communications with some groups that have a lot of
AUTOINCREMENT tables and who performance sensitive and who heavily
instrument their code, nobody has noticed any performance issues
associated with scanning the sqlite_sequence table.  For that reason,
we probably are not going to take any action on this right now.  But I
will hold open the possibility of enhancing the sqlite_sequence lookup
at some point in the future if somebody (especially a client) notices
the potential for a performance benefit.

-- 
D. Richard Hipp
d...@sqlite.org
___
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?

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

UPDATE foo SET dttm = datetime(trim(dttm,));


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

[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 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" 
 
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 definition sais "datetime", not 
"varchar", so if it's being treated as a string what can I do to fix it to be 
treated as a actual datetime datatype?

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Thursday, March 22, 2018 6:49 AM
To: SQLite mailing list
Subject: 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" < 

 sqlite-users-boun...@mailinglists.sqlite.org on behalf of rwa...@gmail.com> 
wrote:

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'



'2018-03-22 06:33:01'



'2018-03-22 06:34:01'



'2018-03-22 06:35:01'



'2018-03-22 06:36:01'



'2018-03-22 06:37:01'



'2018-03-22 06:38:01'



'2018-03-22 06:39:01'



'2018-03-22 06:40:01'



'2018-03-22 06:41:01'



'2018-03-22 06:42:01'



'2018-03-22 06:43:01'



'2018-03-22 06:44:01'



sqlite>



 



BTW, is there some equilivant to “select top 10 * from foo;” style to 
reduce the number of records? I tried this “top 10” but it’s apparently  not 
supported.



 



-Original Message-

From: sqlite-users [ 
 
mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Peter Da Silva

Sent: Thursday, March 22, 2018 6:33 AM

To: SQLite mailing list

Subject: 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



 <  
mailto:sqlite-users@mailinglists.sqlite.org>  
 
sqlite-users@mailinglists.sqlite.org



 < 
 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>  
 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___

sqlite-users mailing list

  
sqlite-users@mailinglists.sqlite.org

  
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



 

___

sqlite-users mailing list

  

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

[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, March 22, 2018 10:12 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: 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, 
inside two single quotes.

Followed by your original:

SELECT dttm, max(i) AS max_i FROM foo GROUP BY dttm ORDER BY dttm;


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
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?

[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, 
inside two single quotes.


Followed by your original:

SELECT dttm, max(i) AS max_i FROM foo GROUP BY dttm ORDER BY dttm;


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

[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 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 being treated as a string what can I do to fix it to be 
treated as a actual datetime datatype?

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Thursday, March 22, 2018 6:49 AM
To: SQLite mailing list
Subject: 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" < 

 sqlite-users-boun...@mailinglists.sqlite.org on behalf of rwa...@gmail.com> 
wrote:

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'



'2018-03-22 06:33:01'



'2018-03-22 06:34:01'



'2018-03-22 06:35:01'



'2018-03-22 06:36:01'



'2018-03-22 06:37:01'



'2018-03-22 06:38:01'



'2018-03-22 06:39:01'



'2018-03-22 06:40:01'



'2018-03-22 06:41:01'



'2018-03-22 06:42:01'



'2018-03-22 06:43:01'



'2018-03-22 06:44:01'



sqlite>



 



BTW, is there some equilivant to “select top 10 * from foo;” style to 
reduce the number of records? I tried this “top 10” but it’s apparently  not 
supported.



 



-Original Message-

From: sqlite-users [ 
 
mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Peter Da Silva

Sent: Thursday, March 22, 2018 6:33 AM

To: SQLite mailing list

Subject: 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



 <  
mailto:sqlite-users@mailinglists.sqlite.org>  
 
sqlite-users@mailinglists.sqlite.org



 < 
 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>  
 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___

sqlite-users mailing list

  
sqlite-users@mailinglists.sqlite.org

  
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



 

___

sqlite-users mailing list

  
sqlite-users@mailinglists.sqlite.org

  
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org

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

[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 being treated as a string what can I do to fix it to be treated as a 
actual datetime datatype?

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Thursday, March 22, 2018 6:49 AM
To: SQLite mailing list
Subject: 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" < 

 sqlite-users-boun...@mailinglists.sqlite.org on behalf of rwa...@gmail.com> 
wrote:

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'



'2018-03-22 06:33:01'



'2018-03-22 06:34:01'



'2018-03-22 06:35:01'



'2018-03-22 06:36:01'



'2018-03-22 06:37:01'



'2018-03-22 06:38:01'



'2018-03-22 06:39:01'



'2018-03-22 06:40:01'



'2018-03-22 06:41:01'



'2018-03-22 06:42:01'



'2018-03-22 06:43:01'



'2018-03-22 06:44:01'



sqlite>



 



BTW, is there some equilivant to “select top 10 * from foo;” style to 
reduce the number of records? I tried this “top 10” but it’s apparently  not 
supported.



 



-Original Message-

From: sqlite-users [  
mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Peter Da Silva

Sent: Thursday, March 22, 2018 6:33 AM

To: SQLite mailing list

Subject: 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



 <  
mailto:sqlite-users@mailinglists.sqlite.org>  
 
sqlite-users@mailinglists.sqlite.org



 <  
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>  
 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___

sqlite-users mailing list

  
sqlite-users@mailinglists.sqlite.org

  
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



 

___

sqlite-users mailing list

  
sqlite-users@mailinglists.sqlite.org

  
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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:01'_

_'2017-11-08 07:03:01'_

_'2017-11-08 07:03:01'_

_'2017-11-08 07:04:01'_

_'2017-11-08 07:04:01'_

_'2017-11-08 07:04:01'_

_'2017-11-08 07:05:01'_

_'2017-11-08 07:05:01'_

_'2017-11-08 07:05:01'_

_'2017-11-08 07:06:01'_

_'2017-11-08 07:06:01'_

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Thursday, March 22, 2018 6:48 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: 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 something subtle about the “date()” function or 
> the “dttm” column that im missing here.

> 

>   

> 

> sqlite> select date(dttm) as dt,max(used_kb)/1024.0/1024.0 from foo 

> sqlite> group by dt order by 1;

> 

> |5038.83195495606

> 

> sqlite> select count(distinct dttm) from foo;

> 

> 192330

> 

> sqlite>

 

Could you post the results of:

 

SELECT '_' || dttm || '_' FROM foo LIMIT 20;

 

The underscores are just so I can tell if there are any weird leading/trailing 
spaces in your data.

___

sqlite-users mailing list

  
sqlite-users@mailinglists.sqlite.org

  
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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)

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

'2018-03-22 06:33:01'

'2018-03-22 06:34:01'

'2018-03-22 06:35:01'

'2018-03-22 06:36:01'

'2018-03-22 06:37:01'

'2018-03-22 06:38:01'

'2018-03-22 06:39:01'

'2018-03-22 06:40:01'

'2018-03-22 06:41:01'

'2018-03-22 06:42:01'

'2018-03-22 06:43:01'

'2018-03-22 06:44:01'

sqlite>

 

BTW, is there some equilivant to “select top 10 * from foo;” style to 
reduce the number of records? I tried this “top 10” but it’s apparently  not 
supported.

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Thursday, March 22, 2018 6:33 AM
To: SQLite mailing list
Subject: 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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

[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 something subtle about the “date()” function or the “dttm” column 
that im missing here.

  


sqlite> select date(dttm) as dt,max(used_kb)/1024.0/1024.0 from foo group by dt 
order by 1;

|5038.83195495606

sqlite> select count(distinct dttm) from foo;

192330

sqlite>


Could you post the results of:

SELECT '_' || dttm || '_' FROM foo LIMIT 20;

The underscores are just so I can tell if there are any weird 
leading/trailing spaces in your data.

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

[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'

'2018-03-22 06:33:01'

'2018-03-22 06:34:01'

'2018-03-22 06:35:01'

'2018-03-22 06:36:01'

'2018-03-22 06:37:01'

'2018-03-22 06:38:01'

'2018-03-22 06:39:01'

'2018-03-22 06:40:01'

'2018-03-22 06:41:01'

'2018-03-22 06:42:01'

'2018-03-22 06:43:01'

'2018-03-22 06:44:01'

sqlite>

 

BTW, is there some equilivant to “select top 10 * from foo;” style to reduce 
the number of records? I tried this “top 10” but it’s apparently  not supported.

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Thursday, March 22, 2018 6:33 AM
To: SQLite mailing list
Subject: 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

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

[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?

[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 the “dttm” column 
that im missing here.

 

sqlite> select date(dttm) as dt,max(used_kb)/1024.0/1024.0 from foo group by dt 
order by 1;

|5038.83195495606

sqlite> select count(distinct dttm) from foo;

192330

sqlite>

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Thursday, March 22, 2018 4:21 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: 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 records per day.

> 

> 

> 

> select date(dttm) dt,max(i) from foo group by dt order by 1;

> 

> 

> 

> However, it’s returning only 1 row, with no date column shown. How can 

> I get an actual “date” listed in the first column, and also get 1 row 

> per “date” value.

 

I fear I might be missing something obvious, but wouldn't this just do the job?:

SELECT date(dttm) AS dt, max(i)

  FROM foo

 GROUP BY date(dttm)

 

If you still get only 1 row, it means there is only 1 date value in all the 
rows in your table.

 

 

___

sqlite-users mailing list

  
sqlite-users@mailinglists.sqlite.org

  
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Unexpected optimization

[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 opposed to inner joins?

You can also use CROSS JOIN to force the QP to not reorder the LHS and RHS 
tables

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Max Vlasov
Gesendet: Donnerstag, 22. März 2018 13:44
An: SQLite mailing list 
Betreff: [EXTERNAL] [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 StatCount from (
  ... Select TableValue, ... left join  ... left join
  where 
)
   where StatCount  = ..

The aggregate lookup (Select count()) is relatively expensive to perform and 
involves a virtual table on my side (LookUpTable). So the goal of 
 is also to narrow the output of the data for this lookup.
Most of the time (including my synthetic tests) the filter indeed works the 
expected way (Filtering with  then performing the aggregate 
only for the suitable), but for some of queries where there are several joins 
sqlite starts to perform the lookup before applying  so I get 
my expensive calculations used for all rows of the inner joined table and then 
filtering with . I double checked this since the LookUpTable is 
my virtual table so I can set a breakpoint and inspect the passed value. 
Ironically, when I remove the outer condition ( where StatCount ..  ) from the 
query in question, it starts to work much faster.

I suspect this might be related to how I respond to the constraint cost 
requests from sqlite. For this virtual table the possible results might be
1 or a very big value. I see that the value 1 is indeed visited for this query 
and probably sqlite might assume some absolute minimum cost for this look-up. 
But when I change it to a bigger value (still lower than "a very big value" 
also used), the query plan will not change.

Here are summary of Explain Query Plan (rea is my virtual table, there are
4 joins in this query, sqlite 3.21.0).

The Query with Outer condition "where StatCount  = .."
SCAN TABLE
SEARCH TABLE (2 times)
EXECUTE CORRELATED SCALAR SUBQUERY 1
SCAN TABLE Rea VIRTUAL TABLE INDEX 2:
SEARCH TABLE (2 times)
EXECUTE CORRELATED SCALAR SUBQUERY 2
SCAN TABLE Rea VIRTUAL TABLE INDEX 2:

The same query when I just removed the outer "where StatCount  = .."
SCAN TABLE...
SEARCH TABLE (4 times)
EXECUTE CORRELATED SCALAR SUBQUERY 1
SCAN TABLE Rea VIRTUAL TABLE INDEX 2:


Can I manually affect the plan for this query or probably by further tweaking 
the virtual table costs?

Thanks



Thanks
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 is worthing
> adding extra logic to do some kind of indexed lookup.  For a schema
> with just a few AUTOINCREMENT tables, there is really no point in
> trying to use an index.  But if you have hundreds of AUTOINCREMENT
> tables, some kind of index might be worthwhile.
>

Hi Richard. Is 8.d from https://www.sqlite.org/draft/releaselog/current.html
the result of this inquiry?
And is there a chance the "some kind of indexed lookup" you mention above
is likely to land in the future?

I'm obviously asking because we (my employer) is the heaviest (publicly
reported) user of AUTOINCREMENT,
and I wonder if we're not incurring unbeknownst until now insert penalties
from those full scans.

What's the threshold you estimate (or measured) in AUTOINCREMENT table
count for the full-scan O(N)
to be slower than an hypothetical indexed-lookup O(log N) of
sqlite_sequence?

If we insert 1M rows in that schema.i DB with 117 AUTOINCREMENT tables, are
we really full-scanning
up to 117M rows just to get the ROWIDs of those 1M rows? Thanks for your
insights into this. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unexpected optimization

[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 StatCount from
(
  ... Select TableValue, ... left join  ... left join
  where 
)
   where StatCount  = ..

The aggregate lookup (Select count()) is relatively expensive to perform
and involves a virtual table on my side (LookUpTable). So the goal of
 is also to narrow the output of the data for this lookup.
Most of the time (including my synthetic tests) the filter indeed works the
expected way (Filtering with  then performing the aggregate
only for the suitable), but for some of queries where there are several
joins sqlite starts to perform the lookup before applying 
so I get my expensive calculations used for all rows of the inner joined
table and then filtering with . I double checked this since
the LookUpTable is my virtual table so I can set a breakpoint and inspect
the passed value. Ironically, when I remove the outer condition ( where
StatCount ..  ) from the query in question, it starts to work much faster.

I suspect this might be related to how I respond to the constraint cost
requests from sqlite. For this virtual table the possible results might be
1 or a very big value. I see that the value 1 is indeed visited for this
query and probably sqlite might assume some absolute minimum cost for this
look-up. But when I change it to a bigger value (still lower than "a very
big value" also used), the query plan will not change.

Here are summary of Explain Query Plan (rea is my virtual table, there are
4 joins in this query, sqlite 3.21.0).

The Query with Outer condition "where StatCount  = .."
SCAN TABLE
SEARCH TABLE (2 times)
EXECUTE CORRELATED SCALAR SUBQUERY 1
SCAN TABLE Rea VIRTUAL TABLE INDEX 2:
SEARCH TABLE (2 times)
EXECUTE CORRELATED SCALAR SUBQUERY 2
SCAN TABLE Rea VIRTUAL TABLE INDEX 2:

The same query when I just removed the outer "where StatCount  = .."
SCAN TABLE...
SEARCH TABLE (4 times)
EXECUTE CORRELATED SCALAR SUBQUERY 1
SCAN TABLE Rea VIRTUAL TABLE INDEX 2:


Can I manually affect the plan for this query or probably by further
tweaking the virtual table costs?

Thanks



Thanks
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 
Auftrag von Ron Watkins
Gesendet: Mittwoch, 21. März 2018 16:30
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [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) from foo group by dt order by 1;



However, it’s returning only 1 row, with no date column shown. How can I get an 
actual “date” listed in the first column, and also get 1 row per “date” value.



|5283598256
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 accordingly”. This is what I’m currently doing by 
pre-eanalyzing the SQL statement and extract the tables matching a identifiable 
pattern.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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
___
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?

[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 version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table foo (dttm datetime not null, i int not null);
--EQP-- 0,0,0,SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)

sqlite> insert into foo values
   ...> ('2018-03-22 07:00:00', 12),
   ...> ('2018-02-01 10:00:00', 1),
   ...> ('2018-03-22 05:00:00', 20),
   ...> ('2018-02-01 12:00:00', 2);

sqlite> select date(dttm) as dt, max(i) from foo group by dt order by 1;
--EQP-- 0,0,0,SCAN TABLE foo
--EQP-- 0,0,0,USE TEMP B-TREE FOR GROUP BY
dt|max(i)
2018-02-01|2
2018-03-22|20


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Ron Watkins
Sent: Wednesday, March 21, 2018 11:30 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [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) from foo group by dt order by 1;



However, it’s returning only 1 row, with no date column shown. How can I
get an actual “date” listed in the first column, and also get 1 row per
“date” value.



|5283598256
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 now, with this code above I get followings ( for three school weeks
):

2017-09-01|F|1-2
2017-09-01|F|3-4
2017-09-01|F|5-6
2017-09-04|M|1-2


but I want followings ( for three school weeks ):

2017-09-01|F|1-2|5|b
2017-09-01|F|1-2|7|c
2017-09-04|M|1-2|7|b
2017-09-04|M|1-2|5|a


So what SQL command should I use for this?


I'm not exactly sure how the classes work, it's hard to establish from 
your explanation and SQL example output, for instance, in the output, 
why does Friday 1 September 2017 only have lesson-block 1-2 for grades 
5b and 7c... why not lesson blocks 3-4 or 5-6? ARe the lesson blocks 
only active on certain days? If so, we need a table to specify it so.


I like your table specifying the TimeTables. It needed fixing because 
the VALUES mechanism works a bit differently than you think, but you can 
compare between your and my version to see exactly how that works.
Like I said, it's a bit unclear, but I'm going to assume some things and 
suggest this:


WITH PAR(calStartDate, calEndDate) AS (
    SELECT '2017-09-01', '2017-09-21'
),DoW(dayId,dayName) AS (
    VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),(4,'Th'), (5,'F'),(6,'Sa')
),LBs(lessonBlock) AS (
    VALUES ('1-2'), ('3-4'), ('5-6')/*, ('7-8'),('9-10'), ('11-12'), 
('13-14'), ('15-16'), ('17-18'), ('19-20'),
   ('21-22'), ('23-24'), ('25-26'), ('27-28'), ('29-30'), 
('31-32'), ('33-34'), ('35-36'), ('37-38'), ('39-40'),
   ('41-42'), ('43-44'), ('45-46'), ('47-48'), ('49-50'), 
('51-52'), ('53-54'), ('55-56'), ('57-58'), ('59-60'),
   ('61-62'), ('63-64'), ('65-66'), ('67-68'), ('69-70'), 
('71-72')  */

), TimeTable(DoWeek,Grade,Class) AS (
 VALUES ('M',7,'b'),('M',5,'a'),
    ('Tu',8,'c'),('Tu',8,'b'),
    ('W',8,'a'),('W',7,'a'),
    ('Th',6,'a'),('Th',5,'c'),
    ('F',5,'b'),('F',7,'c')
), CAL(dayDate,nextDay,dayId) AS (
    SELECT date(calStartDate,'-1 day'), date(calStartDate), -1 FROM PAR
 UNION ALL
    SELECT nextDay, date(nextDay,'+1 day'), CAST(STRFTIME('%w',nextDay) 
AS INT) FROM CAL,PAR WHERE nextDay <= calEndDate

), RES(dayDate, dayName, lessonBlock, Grade, Class) AS (
    SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock, TimeTable.Grade, 
TimeTable.Class

  FROM CAL
 CROSS JOIN LBs
  JOIN DoW ON DoW.dayID = CAL.dayId
  JOIN TimeTable ON TimeTable.DoWeek = DoW.dayName
 WHERE CAL.dayId > 0 AND CAL.dayId < 6  -- No Sundays and No Saturdays
)
SELECT *
    FROM RES;


I kept the majority of lesson blocks commented out for brevity, but if 
it works, you can uncomment them and everything should work.


I hope it works, but if not, feel free to ask again, perhaps with a more 
exact explanation of what the output should contain and how the lesson 
blocks work.



Cheers!
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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: 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 the
>> write func is only used to protect the test data. It means you can
>> stop the. write func when problem happen, and then you can just shell
>> to the db and run the same query then problem happen definitely.
>
>
> Can you do that (create a db that exhibits the problem) and upload it
> somewhere or email it directly to me? Then I'll be able to demonstrate
> the problem using just the shell.

Actually don't worry. I finally got it to work. I think it's the bug
fixed exactly a year and a day ago here:

   http://www.sqlite.org/src/info/840042cb2bed2924

Fix was published in 3.19.0. Can you confirm that 3.19.0 do not produce
the problem for you?

Thanks,
Dan.





>
> Thanks,
> Dan.
>
>
>
>>
>>
>>
>>> "create trigger if not exists trigger_insert_tbl after insert on tbl \
>>> begin \
>>> replace into tbl_fts(rowid, name, uid)
>>> values(new.rowid,new.name,,new.uid);
>>> \
>>> end;",
>>> "create trigger if not exists trigger_update_tbl after update on tbl \
>>> begin \
>>> replace into tbl_fts(rowid, name, uid)
>>> values(new.rowid,new.name,new.uid);\
>>> end;"
>>
>> ?取 Outlook for Android
>>
>> 
>> From: sqlite-users  on
>> behalf of Dan Kennedy 
>> Sent: Thursday, March 22, 2018 4:56:15 PM
>> To: sqlite-users@mailinglists.sqlite.org
>> Subject: 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 long do you usually have to run
>> it for before seeing the mismatch error?
>>
>> Which version of SQLite are you seeing the errors with?
>>
>> Are you building SQLite with SQLITE_THREADSAFE=0?
>>
>> Do you have access to a Linux or similar system on which you can run the
>> pthreads version above? Does it produce errors for you?
>>
>> Thanks,
>> Dan Kennedy.
>>
>>
>>
>>
>>
>>> char *orgName[] = {
>>> "yun j ji yunji yunj yji yj z zh zha zhao q qi qia qian d du dua
>>> duan zhaoqian zhaoq zhqian zhq zqian zq qianduan qiand qduan qd
>>> zhaoqianduan zhaoqiand zhaoqduan zhaoqd zhqianduan zhqiand zhqduan
>>> zhqd zqianduan zqiand zqduan zqd",
>>> "murren m mu l lu lun mulun mul mlun ml",
>>> "l lu m ma man luman lum lman lm",
>>> "d   di   din   ding   d   di   din   ding   f   fu   w   wu
>>> dingding   dingd   dding   dd   dingfu   dingf   dfu   df fuwu
>>> fuw   fwu   fw   dingdingfu   dingdingf   dingdfu dingdf   ddingfu
>>> ddingf   ddfu   ddf   dingfuwu   dingfuw dingfwu   dingfw   dfuwu
>>> dfuw   dfwu   dfw   dingdingfuwu dingdingfuw   dingdingfwu
>>> dingdingfw   dingdfuwu dingdfuw   dingdfwu   dingdfw   ddingfuwu
>>> ddingfuw ddingfwu   ddingfw   ddfuwu   ddfuw   ddfwu   ddfw   l   li
>>> x   xi   xia   lixia   lix   lxia   lx",
>>> "lucy y ya yan x xi yanxi yanx yxi yx",
>>> "p pe pen peng l le lei penglei pengl plei pl lucy"
>>> };
>>> char *nickName[] = {
>>> "ab",
>>> "bc",
>>> "cd",
>>> "de",
>>> "ef",
>>> "fg"
>>> };
>>> int id[] = { 21078,21218,21125,53234,40824,164873 };
>>>
>>> void prepareSchema_prefix(sqlite3 *db) {
>>> char *schemas[] = {
>>> "PRAGMA journal_mode=WAL;",
>>> "drop table if exists tbl;",
>>> "drop table if exists tbl_fts;",
>>> "create table if not exists tbl(name text, uid int primary key, nick
>>> text);",
>>> "create virtual table if not exists tbl_fts USING fts5(name, uid);",
>>> "create trigger if not exists trigger_insert_tbl after insert on tbl \
>>> begin \
>>> replace into tbl_fts(rowid, name, uid)
>>> values(new.rowid,new.name,new.uid);
>>> \
>>> end;",
>>> "create trigger if not exists trigger_update_tbl after update on tbl \
>>> begin \
>>> replace into tbl_fts(rowid, name, uid)
>>> values(new.rowid,new.name,new.uid);\
>>> end;"
>>> };
>>> int i, j, rc, cnt;
>>> cnt = 

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

[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 the 
write func is only used to protect the test data. It means you can 
stop the. write func when problem happen, and then you can just shell 
to the db and run the same query then problem happen definitely.



Can you do that (create a db that exhibits the problem) and upload it 
somewhere or email it directly to me? Then I'll be able to demonstrate 
the problem using just the shell.


Actually don't worry. I finally got it to work. I think it's the bug 
fixed exactly a year and a day ago here:


  http://www.sqlite.org/src/info/840042cb2bed2924

Fix was published in 3.19.0. Can you confirm that 3.19.0 do not produce 
the problem for you?


Thanks,
Dan.







Thanks,
Dan.








"create trigger if not exists trigger_insert_tbl after insert on tbl \
begin \
replace into tbl_fts(rowid, name, uid) 
values(new.rowid,new.name,,new.uid); 
\

end;",
"create trigger if not exists trigger_update_tbl after update on tbl \
begin \
replace into tbl_fts(rowid, name, uid) 
values(new.rowid,new.name,new.uid);\

end;"


?取 Outlook for Android


From: sqlite-users  on 
behalf of Dan Kennedy 

Sent: Thursday, March 22, 2018 4:56:15 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: 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 long do you usually have to run
it for before seeing the mismatch error?

Which version of SQLite are you seeing the errors with?

Are you building SQLite with SQLITE_THREADSAFE=0?

Do you have access to a Linux or similar system on which you can run the
pthreads version above? Does it produce errors for you?

Thanks,
Dan Kennedy.






char *orgName[] = {
"yun j ji yunji yunj yji yj z zh zha zhao q qi qia qian d du dua 
duan zhaoqian zhaoq zhqian zhq zqian zq qianduan qiand qduan qd 
zhaoqianduan zhaoqiand zhaoqduan zhaoqd zhqianduan zhqiand zhqduan 
zhqd zqianduan zqiand zqduan zqd",

"murren m mu l lu lun mulun mul mlun ml",
"l lu m ma man luman lum lman lm",
"d   di   din   ding   d   di   din   ding   f   fu   w   wu 
dingding   dingd   dding   dd   dingfu   dingf   dfu   df fuwu   
fuw   fwu   fw   dingdingfu   dingdingf   dingdfu dingdf   ddingfu   
ddingf   ddfu   ddf   dingfuwu   dingfuw dingfwu   dingfw   dfuwu   
dfuw   dfwu   dfw   dingdingfuwu dingdingfuw   dingdingfwu   
dingdingfw   dingdfuwu dingdfuw   dingdfwu   dingdfw   ddingfuwu   
ddingfuw ddingfwu   ddingfw   ddfuwu   ddfuw   ddfwu   ddfw   l   li 
x   xi   xia   lixia   lix   lxia   lx",

"lucy y ya yan x xi yanxi yanx yxi yx",
"p pe pen peng l le lei penglei pengl plei pl lucy"
};
char *nickName[] = {
"ab",
"bc",
"cd",
"de",
"ef",
"fg"
};
int id[] = { 21078,21218,21125,53234,40824,164873 };

void prepareSchema_prefix(sqlite3 *db) {
char *schemas[] = {
"PRAGMA journal_mode=WAL;",
"drop table if exists tbl;",
"drop table if exists tbl_fts;",
"create table if not exists tbl(name text, uid int primary key, nick 
text);",

"create virtual table if not exists tbl_fts USING fts5(name, uid);",
"create trigger if not exists trigger_insert_tbl after insert on tbl \
begin \
replace into tbl_fts(rowid, name, uid) 
values(new.rowid,new.name,new.uid); 
\

end;",
"create trigger if not exists trigger_update_tbl after update on tbl \
begin \
replace into tbl_fts(rowid, name, uid) 
values(new.rowid,new.name,new.uid);\

end;"
};
int i, j, rc, cnt;
cnt = sizeof(schemas) / sizeof(char*);
char *errMsg;
for (i = 0; i < cnt; i++) {
rc = sqlite3_exec(db, schemas[i], NULL, NULL, );
if (rc != SQLITE_OK& != SQLITE_DONE) {
printf("prepareSchema error: %s\n", errMsg);
return;
}
}
printf("prepareSchema over\n");
}

void fts5_prefix_match_test() {
sqlite3 *db;
int i, j, k, rc;
char *errMsg;
char *append = "abcdefghijklmnopqrstuvwxyz";
sqlite3_open("a.db", );
prepareSchema_prefix(db);
int test_cnt = 300, rc_copy = 0;
char zSql[2048];
char *tmp;
const int len_zSql = 2047;
int len_left;
int nRow = 0, nCol = 0;
char **pazResult;
const int trx_cnt = 1;
int trx_idx;
for (trx_idx = 0; trx_idx < 1; trx_idx++) {
for (i = 0; i < test_cnt; i++) {
j = random(6);
len_left = len_zSql;
tmp = zSql;
if 

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

[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
records per day.



select date(dttm) dt,max(i) from foo group by dt order by 1;



However, it’s returning only 1 row, with no date column shown. How can I
get an actual “date” listed in the first column, and also get 1 row per
“date” value.


I fear I might be missing something obvious, but wouldn't this just do 
the job?:

SELECT date(dttm) AS dt, max(i)
  FROM foo
 GROUP BY date(dttm)

If you still get only 1 row, it means there is only 1 date value in all 
the rows in your table.



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

[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 work address for a fully functional demo licence

On 21 March 2018 at 15:30, 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
> records per day.
>
>
>
> select date(dttm) dt,max(i) from foo group by dt order by 1;
>
>
>
> However, it’s returning only 1 row, with no date column shown. How can I
> get an actual “date” listed in the first column, and also get 1 row per
> “date” value.
>
>
>
> |5283598256
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 test data. It means you can stop the. write func when 
problem happen, and then you can just shell to the db and run the same query 
then problem happen definitely.



Can you do that (create a db that exhibits the problem) and upload it 
somewhere or email it directly to me? Then I'll be able to demonstrate 
the problem using just the shell.


Thanks,
Dan.








"create trigger if not exists trigger_insert_tbl after insert on tbl \
begin \
replace into tbl_fts(rowid, name, uid) 
values(new.rowid,new.name,,new.uid);
 \
end;",
"create trigger if not exists trigger_update_tbl after update on tbl \
begin \
replace into tbl_fts(rowid, name, uid) 
values(new.rowid,new.name,new.uid);\
end;"


?取 Outlook for Android


From: sqlite-users  on behalf of Dan 
Kennedy 
Sent: Thursday, March 22, 2018 4:56:15 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: 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 long do you usually have to run
it for before seeing the mismatch error?

Which version of SQLite are you seeing the errors with?

Are you building SQLite with SQLITE_THREADSAFE=0?

Do you have access to a Linux or similar system on which you can run the
pthreads version above? Does it produce errors for you?

Thanks,
Dan Kennedy.






char *orgName[] = {
"yun j ji yunji yunj yji yj z zh zha zhao q qi qia qian d du dua duan zhaoqian zhaoq 
zhqian zhq zqian zq qianduan qiand qduan qd zhaoqianduan zhaoqiand zhaoqduan zhaoqd 
zhqianduan zhqiand zhqduan zhqd zqianduan zqiand zqduan zqd",
"murren m mu l lu lun mulun mul mlun ml",
"l lu m ma man luman lum lman lm",
"d   di   din   ding   d   di   din   ding   f   fu   w   wu   dingding   dingd   
dding   dd   dingfu   dingf   dfu   df   fuwu   fuw   fwu   fw   dingdingfu   dingdingf   
dingdfu   dingdf   ddingfu   ddingf   ddfu   ddf   dingfuwu   dingfuw   dingfwu   dingfw  
 dfuwu   dfuw   dfwu   dfw   dingdingfuwu   dingdingfuw   dingdingfwu   dingdingfw   
dingdfuwu   dingdfuw   dingdfwu   dingdfw   ddingfuwu   ddingfuw   ddingfwu   ddingfw   
ddfuwu   ddfuw   ddfwu   ddfw   l   li   x   xi   xia   lixia   lix   lxia   lx",
"lucy y ya yan x xi yanxi yanx yxi yx",
"p pe pen peng l le lei penglei pengl plei pl lucy"
};
char *nickName[] = {
"ab",
"bc",
"cd",
"de",
"ef",
"fg"
};
int id[] = { 21078,21218,21125,53234,40824,164873 };

void prepareSchema_prefix(sqlite3 *db) {
char *schemas[] = {
"PRAGMA journal_mode=WAL;",
"drop table if exists tbl;",
"drop table if exists tbl_fts;",
"create table if not exists tbl(name text, uid int primary key, nick text);",
"create virtual table if not exists tbl_fts USING fts5(name, uid);",
"create trigger if not exists trigger_insert_tbl after insert on tbl \
begin \
replace into tbl_fts(rowid, name, uid) 
values(new.rowid,new.name,new.uid);
 \
end;",
"create trigger if not exists trigger_update_tbl after update on tbl \
begin \
replace into tbl_fts(rowid, name, uid) 
values(new.rowid,new.name,new.uid);\
end;"
};
int i, j, rc, cnt;
cnt = sizeof(schemas) / sizeof(char*);
char *errMsg;
for (i = 0; i < cnt; i++) {
rc = sqlite3_exec(db, schemas[i], NULL, NULL, );
if (rc != SQLITE_OK& != SQLITE_DONE) {
printf("prepareSchema error: %s\n", errMsg);
return;
}
}
printf("prepareSchema over\n");
}

void fts5_prefix_match_test() {
sqlite3 *db;
int i, j, k, rc;
char *errMsg;
char *append = "abcdefghijklmnopqrstuvwxyz";
sqlite3_open("a.db", );
prepareSchema_prefix(db);
int test_cnt = 300, rc_copy = 0;
char zSql[2048];
char *tmp;
const int len_zSql = 2047;
int len_left;
int nRow = 0, nCol = 0;
char **pazResult;
const int trx_cnt = 1;
int trx_idx;
for (trx_idx = 0; trx_idx < 1; trx_idx++) {
for (i = 0; i < test_cnt; i++) {
j = random(6);
len_left = len_zSql;
tmp = zSql;
if (j < 4) {
int uid = id[j] + random(300);
rc_copy = snprintf(tmp, len_left, "insert into tbl 
values(\"%s%c\",%d,\"%s%c\");",
orgName[j], append[random(26)], uid, nickName[j], append[random(26)]);
//printf("%s\n", zSql);
rc = sqlite3_exec(db, zSql, NULL, NULL, );
if (rc != SQLITE_OK& != SQLITE_DONE) {
if (rc != 

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

[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 foo group by dt order by 1;



However, it’s returning only 1 row, with no date column shown. How can I
get an actual “date” listed in the first column, and also get 1 row per
“date” value.



|5283598256
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 when 
problem happen, and then you can just shell to the db and run the same query 
then problem happen definitely.



> "create trigger if not exists trigger_insert_tbl after insert on tbl \
> begin \
> replace into tbl_fts(rowid, name, uid) 
> values(new.rowid,new.name,,new.uid);
>  \
> end;",
> "create trigger if not exists trigger_update_tbl after update on tbl \
> begin \
> replace into tbl_fts(rowid, name, uid) 
> values(new.rowid,new.name,new.uid);\
> end;"


?取 Outlook for Android


From: sqlite-users  on behalf of 
Dan Kennedy 
Sent: Thursday, March 22, 2018 4:56:15 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: 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 long do you usually have to run
it for before seeing the mismatch error?

Which version of SQLite are you seeing the errors with?

Are you building SQLite with SQLITE_THREADSAFE=0?

Do you have access to a Linux or similar system on which you can run the
pthreads version above? Does it produce errors for you?

Thanks,
Dan Kennedy.





>
> char *orgName[] = {
> "yun j ji yunji yunj yji yj z zh zha zhao q qi qia qian d du dua duan 
> zhaoqian zhaoq zhqian zhq zqian zq qianduan qiand qduan qd zhaoqianduan 
> zhaoqiand zhaoqduan zhaoqd zhqianduan zhqiand zhqduan zhqd zqianduan zqiand 
> zqduan zqd",
> "murren m mu l lu lun mulun mul mlun ml",
> "l lu m ma man luman lum lman lm",
> "d   di   din   ding   d   di   din   ding   f   fu   w   wu   dingding   
> dingd   dding   dd   dingfu   dingf   dfu   df   fuwu   fuw   fwu   fw   
> dingdingfu   dingdingf   dingdfu   dingdf   ddingfu   ddingf   ddfu   ddf   
> dingfuwu   dingfuw   dingfwu   dingfw   dfuwu   dfuw   dfwu   dfw   
> dingdingfuwu   dingdingfuw   dingdingfwu   dingdingfw   dingdfuwu   dingdfuw  
>  dingdfwu   dingdfw   ddingfuwu   ddingfuw   ddingfwu   ddingfw   ddfuwu   
> ddfuw   ddfwu   ddfw   l   li   x   xi   xia   lixia   lix   lxia   lx",
> "lucy y ya yan x xi yanxi yanx yxi yx",
> "p pe pen peng l le lei penglei pengl plei pl lucy"
> };
> char *nickName[] = {
> "ab",
> "bc",
> "cd",
> "de",
> "ef",
> "fg"
> };
> int id[] = { 21078,21218,21125,53234,40824,164873 };
>
> void prepareSchema_prefix(sqlite3 *db) {
> char *schemas[] = {
> "PRAGMA journal_mode=WAL;",
> "drop table if exists tbl;",
> "drop table if exists tbl_fts;",
> "create table if not exists tbl(name text, uid int primary key, nick text);",
> "create virtual table if not exists tbl_fts USING fts5(name, uid);",
> "create trigger if not exists trigger_insert_tbl after insert on tbl \
> begin \
> replace into tbl_fts(rowid, name, uid) 
> values(new.rowid,new.name,new.uid);
>  \
> end;",
> "create trigger if not exists trigger_update_tbl after update on tbl \
> begin \
> replace into tbl_fts(rowid, name, uid) 
> values(new.rowid,new.name,new.uid);\
> end;"
> };
> int i, j, rc, cnt;
> cnt = sizeof(schemas) / sizeof(char*);
> char *errMsg;
> for (i = 0; i < cnt; i++) {
> rc = sqlite3_exec(db, schemas[i], NULL, NULL, );
> if (rc != SQLITE_OK& != SQLITE_DONE) {
> printf("prepareSchema error: %s\n", errMsg);
> return;
> }
> }
> printf("prepareSchema over\n");
> }
>
> void fts5_prefix_match_test() {
> sqlite3 *db;
> int i, j, k, rc;
> char *errMsg;
> char *append = "abcdefghijklmnopqrstuvwxyz";
> sqlite3_open("a.db", );
> prepareSchema_prefix(db);
> int test_cnt = 300, rc_copy = 0;
> char zSql[2048];
> char *tmp;
> const int len_zSql = 2047;
> int len_left;
> int nRow = 0, nCol = 0;
> char **pazResult;
> const int trx_cnt = 1;
> int trx_idx;
> for (trx_idx = 0; trx_idx < 1; trx_idx++) {
> for (i = 0; i < test_cnt; i++) {
> j = random(6);
> len_left = len_zSql;
> tmp = zSql;
> if (j < 4) {
> int uid = id[j] + random(300);
> rc_copy = snprintf(tmp, len_left, "insert into tbl 
> values(\"%s%c\",%d,\"%s%c\");",
> orgName[j], append[random(26)], uid, nickName[j], append[random(26)]);
> //printf("%s\n", zSql);
> rc = sqlite3_exec(db, zSql, NULL, NULL, );
> if (rc != SQLITE_OK& != SQLITE_DONE) {
> if (rc != SQLITE_CONSTRAINT) {
> printf("error %s: %s\n", zSql, 

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

[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 long do you usually have to run 
it for before seeing the mismatch error?


Which version of SQLite are you seeing the errors with?

Are you building SQLite with SQLITE_THREADSAFE=0?

Do you have access to a Linux or similar system on which you can run the 
pthreads version above? Does it produce errors for you?


Thanks,
Dan Kennedy.







char *orgName[] = {
"yun j ji yunji yunj yji yj z zh zha zhao q qi qia qian d du dua duan zhaoqian zhaoq 
zhqian zhq zqian zq qianduan qiand qduan qd zhaoqianduan zhaoqiand zhaoqduan zhaoqd 
zhqianduan zhqiand zhqduan zhqd zqianduan zqiand zqduan zqd",
"murren m mu l lu lun mulun mul mlun ml",
"l lu m ma man luman lum lman lm",
"d   di   din   ding   d   di   din   ding   f   fu   w   wu   dingding   dingd   
dding   dd   dingfu   dingf   dfu   df   fuwu   fuw   fwu   fw   dingdingfu   dingdingf   
dingdfu   dingdf   ddingfu   ddingf   ddfu   ddf   dingfuwu   dingfuw   dingfwu   dingfw  
 dfuwu   dfuw   dfwu   dfw   dingdingfuwu   dingdingfuw   dingdingfwu   dingdingfw   
dingdfuwu   dingdfuw   dingdfwu   dingdfw   ddingfuwu   ddingfuw   ddingfwu   ddingfw   
ddfuwu   ddfuw   ddfwu   ddfw   l   li   x   xi   xia   lixia   lix   lxia   lx",
"lucy y ya yan x xi yanxi yanx yxi yx",
"p pe pen peng l le lei penglei pengl plei pl lucy"
};
char *nickName[] = {
"ab",
"bc",
"cd",
"de",
"ef",
"fg"
};
int id[] = { 21078,21218,21125,53234,40824,164873 };

void prepareSchema_prefix(sqlite3 *db) {
char *schemas[] = {
"PRAGMA journal_mode=WAL;",
"drop table if exists tbl;",
"drop table if exists tbl_fts;",
"create table if not exists tbl(name text, uid int primary key, nick text);",
"create virtual table if not exists tbl_fts USING fts5(name, uid);",
"create trigger if not exists trigger_insert_tbl after insert on tbl \
begin \
replace into tbl_fts(rowid, name, uid) 
values(new.rowid,new.name,new.uid);
 \
end;",
"create trigger if not exists trigger_update_tbl after update on tbl \
begin \
replace into tbl_fts(rowid, name, uid) 
values(new.rowid,new.name,new.uid);\
end;"
};
int i, j, rc, cnt;
cnt = sizeof(schemas) / sizeof(char*);
char *errMsg;
for (i = 0; i < cnt; i++) {
rc = sqlite3_exec(db, schemas[i], NULL, NULL, );
if (rc != SQLITE_OK& != SQLITE_DONE) {
printf("prepareSchema error: %s\n", errMsg);
return;
}
}
printf("prepareSchema over\n");
}

void fts5_prefix_match_test() {
sqlite3 *db;
int i, j, k, rc;
char *errMsg;
char *append = "abcdefghijklmnopqrstuvwxyz";
sqlite3_open("a.db", );
prepareSchema_prefix(db);
int test_cnt = 300, rc_copy = 0;
char zSql[2048];
char *tmp;
const int len_zSql = 2047;
int len_left;
int nRow = 0, nCol = 0;
char **pazResult;
const int trx_cnt = 1;
int trx_idx;
for (trx_idx = 0; trx_idx < 1; trx_idx++) {
for (i = 0; i < test_cnt; i++) {
j = random(6);
len_left = len_zSql;
tmp = zSql;
if (j < 4) {
int uid = id[j] + random(300);
rc_copy = snprintf(tmp, len_left, "insert into tbl 
values(\"%s%c\",%d,\"%s%c\");",
orgName[j], append[random(26)], uid, nickName[j], append[random(26)]);
//printf("%s\n", zSql);
rc = sqlite3_exec(db, zSql, NULL, NULL, );
if (rc != SQLITE_OK& != SQLITE_DONE) {
if (rc != SQLITE_CONSTRAINT) {
printf("error %s: %s\n", zSql, errMsg);
continue;
}
else {
rc_copy = snprintf(tmp, len_left, "update tbl set name=\"%s%c\" where uid=%d\n",
orgName[j], append[random(26)], uid);
rc = sqlite3_exec(db, zSql, NULL, NULL, );
if (rc != SQLITE_OK& != SQLITE_DONE) {
printf("error %s: %s\n", zSql, errMsg);
continue;
}
}
}
}
else {
rc_copy = snprintf(zSql, len_zSql, "select * from tbl where uid=%d;", id[j]);
rc = sqlite3_get_table(db, zSql, , , , );
if (nRow == 0) {
rc_copy = snprintf(tmp, len_left, "insert into tbl(name, uid, nick) 
values(\"%s\",%d,\"%s\");",
orgName[j], id[j], nickName[j]);
}
else {
rc_copy = snprintf(zSql, len_zSql, "update tbl set name=\"%s\" where uid=%d;",
orgName[j], id[j]);
}
rc = sqlite3_exec(db, zSql, NULL, NULL, );
if (rc != SQLITE_OK& != SQLITE_DONE) {
printf("error %s: %s\n", zSql, errMsg);
continue;
}
}
}
printf("insert 300 records\n");
sqlite3_sleep(200);
}
}


unsigned __stdcall writeFunc(void *pIn) {
fts5_prefix_match_test();
printf("write over\n");
return 0;
}
unsigned __stdcall readFunc(void *pIn) {
sqlite3 *db;
int i, j, rc;
char *errMsg;
char **pazResult;
int nRow, nCol;
sqlite3_sleep(2000);
rc = sqlite3_open("a.db", );
if (rc != SQLITE_OK) {
printf("open db error\n");
return 0;
}
char *zSql = "select rowid, * from tbl_fts where tbl_fts match \'lucy*\';";
while (1) {
rc = sqlite3_get_table(db, zSql, , , , );
if (nRow > 2) {
printf("mis-match\n");
for (i = 0; i <= nRow; i++) {
for (j = 0;