Re: [sqlite] 0x11C: automatic index warning for CTEs ?

Howard Kapustein wrote: > I'm setting SQLite logging a warning > Warning 0x11C: automatic index on PackageIdByAll(_PackageID) > > WITH ... > PackageIdByAll(_PackageID) AS (...) > SELECT * > FROM ... > INNER JOIN PackageIdByAll AS cte_p ON cte_p._PackageID=pkg._PackageID > > Why? > > Package

[sqlite] 0x11C: automatic index warning for CTEs ?

I'm setting SQLite logging a warning Warning 0x11C: automatic index on PackageIdByAll(_PackageID) Whenever I run this SQL WITH PackageIdByUser(_PackageID) AS ( SELECT _PackageID FROM Package AS p INNER JOIN PackageUser AS pu ON pu.Package=p._PackageID WHERE pu.User=?1 AND p._W

Re: [sqlite] json_tree virtual table -- on disk or in memory?

> On Apr 20, 2017, at 11:02 AM, Akshat Jiwan Sharma > wrote: > > Does that mean that the json_tree table is stored in memory after the json > is parsed by sqlite? Yes, but only for the row being evaluated. It sounds like you’re thinking of json_tree creating a database table that contains al

Re: [sqlite] Difference between localtime and utc is 8 hours, but should be 4

That makes sense. Thank you very much. RobR -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski Sent: Friday, April 21, 2017 4:29 PM To: SQLite mailing list Subject: Re: [sqlite] Difference between localtime and ut

Re: [sqlite] Difference between localtime and utc is 8 hours, but should be 4

Let me clarify; What you store in the database is just a number. There is no indication to what timezone that references. So when you convert UTC to UTC, you're taking a time already set for UTC and converting it to another 4 hours earlier (Or later? -- i hate time math). When I say "UTC to UTC

Re: [sqlite] Difference between localtime and utc is 8 hours, but should be 4

Because you're converting your UTC time to UTC. On Fri, Apr 21, 2017 at 4:03 PM, Rob Richardson wrote: > Hello! > > I'm in the Eastern US time zone, in daylight savings time. I am four > hours earlier than UTC time. I have a column that stores UTC times as > Julian times (floating-point number

Re: [sqlite] Multiple sessions, page sizes and cache

On 21 Apr 2017, at 8:57pm, Kim Gräsman wrote: > There is a WHERE clause, but EXPLAIN QUERY PLAN does not mention any indexes: > >sele order from deta >- >0 00 SCAN TABLE mytable > > Also, this is just one of

[sqlite] Difference between localtime and utc is 8 hours, but should be 4

Hello! I'm in the Eastern US time zone, in daylight savings time. I am four hours earlier than UTC time. I have a column that stores UTC times as Julian times (floating-point numbers). The latest data point in the table was stored at about 8:41 this morning (4/21). I am getting strange re

Re: [sqlite] Multiple sessions, page sizes and cache

On Fri, Apr 21, 2017 at 6:21 PM, Simon Slavin wrote: > > On 21 Apr 2017, at 5:16pm, Kim Gräsman wrote: > >> Could be. Would that show up in EXPLAIN somehow? > > You would find it easier to spot using EXPLAIN QUERY PLAN. 'scan' means it’s > just looking through the entire table, but you’ll see m

Re: [sqlite] Database disk in malformed.

On 21 Apr 2017, at 8:30am, Shubham Agrawal wrote: > I am using Sqlite3 (version 3.7.9) to store some stats. I am getting > database disk is the malformed issue. Can someone suggest what could be > wrong because I tried everything what i got on google but no luck. Are you starting a new database

Re: [sqlite] Which pragmas are persistent?

I agree that something like that would be nice, at this point I think it's just backwards compatability issues preventing that. The best you can do at the moment is to compile anything you use/write yourself with... SQLITE_DEFAULT_FOREIGN_KEYS=1 ...but if you're not in a situation where you ha

Re: [sqlite] Database disk in malformed.

On 4/21/17, Shubham Agrawal wrote: > > I am using Sqlite3 (version 3.7.9) to store some stats. I am getting > database disk is the malformed issue. You will need to provide enough information so that we can reproduce your problem. Your current problem description is too vague for us to assist yo

Re: [sqlite] json_tree virtual table -- on disk or in memory?

On 4/20/17, Akshat Jiwan Sharma wrote: > Does that mean that the json_tree table is stored in memory after the json > is parsed by sqlite? Yes. > > If so, for querying json data will I be limited by the memory of my > machine? How big are your JSON strings that storing the entire string in memo

Re: [sqlite] Which pragmas are persistent?

PRAGMA foreign_keys=1 is transient, but it would be nice if it were persistent. John G On 13 April 2017 at 12:35, Tony Papadimitriou wrote: > -Original Message- From: no...@null.net > >> What would be useful (at least via the shell CLI) is a "list_pragmas" >> pragma that shows for ex

Re: [sqlite] Increasing performance of query

Thanks for your answers! First of all, my apologies with ANALYZE I meant that it increased performance (decreased query time) by orders of magnitude. Before ANALYZE the query was in the order of seconds (about 2 seconds). After ANALYZE the query was in the order of milliseconds. I tried VACUUM t

[sqlite] Database disk in malformed.

HI All, I am using Sqlite3 (version 3.7.9) to store some stats. I am getting database disk is the malformed issue. Can someone suggest what could be wrong because I tried everything what i got on google but no luck. Please suggest some idea to get rid of this issue. Regards, Shubham Agrawal ___

[sqlite] json_tree virtual table -- on disk or in memory?

Hi sqlite-users community, I was looking at the json1 extension and came to know that json_tree is implemented as a virtual table. Does that mean that the json_tree table is stored in memory after the json is parsed by sqlite? If so, for querying json data wi

Re: [sqlite] Multiple sessions, page sizes and cache

On 21 Apr 2017, at 5:16pm, Kim Gräsman wrote: > Could be. Would that show up in EXPLAIN somehow? You would find it easier to spot using EXPLAIN QUERY PLAN. 'scan' means it’s just looking through the entire table, but you’ll see mentions of indexes there. > The statement I've > had trouble wi

Re: [sqlite] Multiple sessions, page sizes and cache

On Fri, Apr 21, 2017 at 6:05 PM, Simon Slavin wrote: > > On 21 Apr 2017, at 4:18pm, Kim Gräsman wrote: > >> Unfortunately, we're getting occasional out-of-memory errors on >> statement execution. > > Is there a chance that your statements are making up temporary indexes ? > They can be arbitrar

Re: [sqlite] FTS5 query results different before/after MERGE command.

On 04/21/2017 02:36 AM, Jonathan Gaillard wrote: Here is the db https://ufile.io/q0314 Thanks for reporting this. It was a bug in the fts5 prefix query code. Now fixed here: http://www.sqlite.org/src/info/840042cb2bed2924 Dan. If you do: select rowid, name from filesfts where fil

Re: [sqlite] Multiple sessions, page sizes and cache

On 21 Apr 2017, at 4:18pm, Kim Gräsman wrote: > Unfortunately, we're getting occasional out-of-memory errors on > statement execution. Is there a chance that your statements are making up temporary indexes ? They can be arbitrarily large depending on how many rows you have in a table. Simon.

Re: [sqlite] Multiple sessions, page sizes and cache

Hi all, On Sun, Jan 15, 2017 at 5:50 PM, Richard Hipp wrote: > On 1/15/17, Kim Gräsman wrote: >> >> 1) If I configure a global SQLite heap with SQLITE_CONFIG_HEAP, won't >> I just trade malloc heap fragmentation for SQLite private heap >> fragmentation? Or does SQLite's fragmentation-prevention

Re: [sqlite] How difficult for bindings to support API 14 or below?

Have sent a patch file direct to you (Dan). On 21/04/2017 1:43 AM, Dan Kennedy wrote: Sure, that sounds great! I don't think there are any license issues, as all that code inherits the Apache 2.0 license from Android. Are you fossil-literate? ___