Re: [sqlite] LEFT JOIN: ON vs. WHERE

the lookup on S if R.col != 'foo' and still return that R record. Marc Marc L. Allen | Software Engineer IV, PDI Marketing Cloud Solutions 2551 Eltham Ave. Suite N, Norfolk, VA 23513 Office 757.853.3000 x6015 Direct 757.605.6015 mlal...@pdisoftware.com www.pdisoftware.com <http://www.pdisoftware.

Re: [sqlite] TCL API: substitution of NULL for non-existent variables

error would occur at runtime. /Don > > > From: sqlite-users on behalf of Donald Allen > Sent: Thursday, February 28, 2019 08:35 AM > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] TCL API: substitution of NULL for non-existent variables > > Is there a way to

[sqlite] TCL API: substitution of NULL for non-existent variables

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

[sqlite] Email bounce

Everyone, I apologize for the email bounce notifications. My company was acquired a few months back and they’re trying to move us to their email system, so they’re trying to support two email addresses. I hope it’s only for the day, but if it becomes a burden, I can unsubscribe and come back

Re: [sqlite] Index help...

I’m not the expert here, but it appears that the cause is that your looking for things greater than some confidence. This forces an index scan. There’s nothing that gives a list of different confidences greater than, in this case .8, but even if it did, an index scan might be faster than

Re: [sqlite] unsubscribe

Because it knows you don't really want to leave. -Original Message- From: sqlite-users On Behalf Of Rob Dixon Sent: Tuesday, October 23, 2018 12:41 PM To: SQLite mailing list Subject: Re: [sqlite] unsubscribe I did that yesterday 3 times, got 3 confirmations and yet.. On Tue, Oct 23,

Re: [sqlite] insert or replace performance with self "references" column

lf > "references" column > Message-ID: > Content-Type: text/plain; charset=us-ascii > > On 27 Jun 2018, at 11:20pm, Allen wrote: > >> I have a table with an additional index and a query: >> >> "create table Transactions (Id integer primary

Re: [sqlite] sqlite-users Digest, Vol 126, Issue 28

do you get > "happiness making" results? > > --- > 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- >>bo

[sqlite] insert or replace performance with self "references" column

I have a table with an additional index and a query: "create table Transactions (Id integer primary key not null, Parent references Transactions(id), Body varchar);" "create index Parent_Index on Transactions (Parent);" EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent, Body)

Re: [sqlite] Can this be done with SQLite

Two options (one based on Igor's answer) update playYouTubVideo set speed = case when key = 1 then (select speed from playYouTubeVIdeo when key = 2) when key = 2 then (select ... when key = 3) ... when key = 5 then (select ...

Re: [sqlite] Is this a bug with expression evaluation?

I just multiply by 1.0 Select column1*(column2 * 1.0 / column3)... Removing the parentheses only provide the correct results in your example. It's still using integer math, it's just performing the multiply first, as per order of operations. -Original Message- From: sqlite-users

Re: [sqlite] finding the number of records until a value is different

Ok I'm on this list because I love reading all the comments and discussion about sqlite and DBs in general. I haven't used sqlite in quite awhile, so I don't know how well this will work, but... Assuming you have a row number as well, such as CREATE TABLE x ( row_number int,

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

that the other. I didn't realize (or had forgotten) that integers could be stored with variable length. Naturally, that has additional impact. > On Jul 14, 2017, at 9:37 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > On Friday, 14 July, 2017 18:20, Marc L. Allen <mlal...@o

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

canning the table, assuming that the where condition > does not match all rows. > > -- > ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > >> -Original Message- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf Of Ma

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

Generally, but not always, WHERE elements using a column in an expression are unsuitable to using an index. For instance: WHERE y + 2 = 4 generally can't use an index, whereas WHERE y = 2 can. That's not to say a smart optimizer won't convert the former to the latter. But, what's the

Re: [sqlite] Version 3.20.0 coming soon...

If it's going to change in future releases, then perhaps your "key point" is the main takeaway that should be documented? Something like: SQLITE_PREPARE_PERSISTENT is a hint to SQLite that the prepared statement is going to linger for a long time and that SQLite should take that into account

Re: [sqlite] Reducing the output of a query

Assuming each column is either NULL or a distinct item for each group, just use MAX() of each column adding the necessary group bys. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Joseph L. Casale Sent: Tuesday, May 2, 2017 3:55

Re: [sqlite] Problem compiling 3.17.0 in MSVS 12

This is a total shot in the dark... but something with line endings (\r, \n, \r\n, etc.) messing up the #define blah \ Or other things? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Drago, William @ CSG - NARDA-MITEQ Sent:

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

ers@mailinglists.sqlite.org> Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint On 11/17/16, Marc L. Allen <mlal...@outsitenetworks.com> wrote: > I'm not sure that's a valid trick, as bar.foo can be NULL, in which > case the LEFT

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

Of Richard Hipp Sent: Thursday, November 17, 2016 9:32 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint On 11/17/16, Marc L. Allen <mlal...@outsitenetworks.c

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

Maybe I'm missing something, but... ORDER BY id Is ordering by the ID the right-hand side of a LEFT join. As such, it depends on how NULL factors into an ORDER BY. If NULL comes first, it has to find enough records where the LEFT join fails. Yeah.. I'm probably missing something.

Re: [sqlite] bug in SQLITE_CHECKPOINT_PASSIVE?

> I double-checked and it is possible that the checkpoint thread is > sharing an sqlite3* with a writer thread. That was the problem. I gave the SQLITE_CHECKPOINT_PASSIVE it's own sqlite3*, and it is now working fine. Thanks much. ___ sqlite-users

Re: [sqlite] bug in SQLITE_CHECKPOINT_PASSIVE?

> Are each of your threads using their own handles or do they all share the > same handle? I double-checked and it is possible that the checkpoint thread is sharing an sqlite3* with a writer thread. If so, that would account for the problem, I think. I'll look into this further and post a

Re: [sqlite] bug in SQLITE_CHECKPOINT_PASSIVE?

> > sqlite3_busy_timeout(db, 0x7000) > If you want to turn timeouts off, specify zero or a negative number. My understanding is that timeouts off means the functions immediately return BUSY. I don't want timeouts off, I want them set to infinity, so the functions never in my lifetime return

[sqlite] bug in SQLITE_CHECKPOINT_PASSIVE?

It was that anomalous behavior that led me to try putting SQLITE_CHECKPOINT_PASSIVE inside the application-level write mutex to see what would happen. Any thoughts on what the problem might be? Thanks much, Allen ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Illegal SQL not rejected

That error is saying that you can't using HAVING on a column unless it's in a group by or it's referenced in an aggregate in the HAVING clause You could say, HAVING SUM(A) <> 0 or something. The query as stated SELECT SUM(A) ... HAVING A<>0 makes no sense because A is not in the select list.

[sqlite] Article about pointer abuse in SQLite

With everything that routine does, the extra initialization negatively impacts operation? -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, March 24, 2016 10:50 AM To:

[sqlite] Article about pointer abuse in SQLite

I don't think compilers "run" your code. When looking for uninitialized variables, it simply looks for a potential path through the code that uses a variable without it being initialized. The fact that the code never actually allows that path to occur is beyond the scope of most compilers,

[sqlite] Article about pointer abuse in SQLite

I had a long response to this, but it occurs to me that you're just being pedantic for fun. Am I wrong? -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf Sent: Friday, March 18, 2016

[sqlite] SQLite Pronunciation

? To me, it falls off the tongue easier to say Sequel instead of Ess-Queue-Ell. 1- http://acronyms.thefreedictionary.com/RADAR On Wed, Mar 16, 2016 at 2:55 PM, Marc L. Allen wrote: > That sounds like someone that comes from the land of Sequel. ;) > > I realize there *is* an official pron

[sqlite] SQLite Pronunciation

That sounds like someone that comes from the land of Sequel. ;) I realize there *is* an official pronunciation, but I will probably forever pronounce it as S-Q-L-light, regardless of what it really is. :) -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org

[sqlite] Using sqlite3.exe as a subprocess

On 16 January 2016 at 07:16, Roger Binns wrote: > > > while p.poll() == None: resp = p.communicate() print len(resp[0]), > > resp[0] > > That code doesn't make sense. communicate waits until the process > terminates. The SQLite shell won't terminate unless it gets a quit > command, or EOF on

[sqlite] Using sqlite3.exe as a subprocess

It seems that sqlite3.exe (console) doesn't work as a subprocess with pipes. I've tried it with both C++ code calling the CreateProcessW win32 API and with python and both resulted in the same behaviour. Which is the sub-process doesn't return anything when I try and read it's output (just

[sqlite] i think this is a bug in sqlite? thanks

I was about the suggest the same thing that was suggest in that thread. Verify the types of those values. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Anthony Damico Sent: Thursday, December

[sqlite] Alexa has been banished. Was: Mailing list policy change

I save one. I can forward it to you, if you'd like. > On Oct 29, 2015, at 5:37 PM, Nicolas J?ger wrote: > > damn! I wanted a date! > >>> On 10/29/15, Simon Slavin wrote: >>> >>> You don't even need to mess with the genuine list server. Just grab all the >>> addresses it sends to and use

[sqlite] Simple Math Question

I didn't see this go through, so I'm sending it again. I got an Alexa spam, so maybe that's part of it. I ran the following code on my ARM processor: double c25 = 0.0; c25 += 9.2; c25 += 7.9; c25 += 0.0; c25 += 4.0; c25 += 2.6; c25 += 1.3; double n25 = 25.0; double c23 = 0.0; c23 +=

[sqlite] Simple Math Question

I ram the following code on my ARM processor: double c25 = 0.0; c25 += 9.2; c25 += 7.9; c25 += 0.0; c25 += 4.0; c25 += 2.6; c25 += 1.3; double n25 = 25.0; double c23 = 0.0; c23 += 9.2; c23 += 7.8; c23 += 0.0; c23 += 3.0; c23 += 1.3; c23 += 1.7; double n23 = 23.0;

[sqlite] Simple Math Question

If I recall, he had a printout of the state at each time unit. The state was represented as a set of numbers out to 6 decimal points. He wanted to rerun part of the simulation, so he entered in those super-precise numbers and let them run, but the model quick diverged because those numbers

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

of SQLite Database Subject: Re: [sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested On 10/8/15, Marc L. Allen wrote: > However, CTE is a functionality enhancement that, I believe, does not > impact the ability of previous SQLite versions t

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

L. Allen Sent: Thursday, October 08, 2015 11:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested Just my $0.02... In the proposed new versioning system: Partial Indexes is clearly something

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

Just my $0.02... In the proposed new versioning system: Partial Indexes is clearly something that requires Y to be incremented as Y-1 won't be able to handle a database with partial indexes. However, CTE is a functionality enhancement that, I believe, does not impact the ability of previous

[sqlite] Feature Suggestions

> Don't forget to have each connection set a timeout This makes SQLite > automatically retry (using monotonic backoff) if the database is locked. I had that set to 10 seconds using sqlite3_busy_timeout(db, 1). That doesn't work in shared cache mode--in that mode, you immediately get an

[sqlite] Feature Suggestions

> The problem with going to EXCLUSIVE manually is that you lock out other > connections for longer than necessary. I have only one connection that does writes. In any event, I wanted to confirm the readers would work correctly while another connection held an EXCLUSIVE lock, because if they

[sqlite] Feature Suggestions

> Why on earth are you using BEGIN EXCLUSIVE? If WAL works "as advertised", I might as well go straight to EXCLUSIVE so I don't have to later escalate the lock from RESERVED to EXCLUSIVE. And it does work just fine, once I turned off the shared cache mode.

[sqlite] Feature Suggestions

> I was under the apparently mistaken impression that starting any transaction would block writes, even in WAL mode. My problem was that I was using sqlite3_enable_shared_cache(true). I enabled this because I was under the impression that is required for multiple connections to share an

[sqlite] Feature Suggestions

> WAL does this already. You just need to BEGIN a transaction when you want it to BEGIN and COMMIT or ROLLBACK when you are done with it. I was under the apparently mistaken impression that starting any transaction would block writes, even in WAL mode. Just to be clear, this would work: On

[sqlite] Feature Suggestions

Suggestions for SQLite features: 1. It would be nice to have read-only transactions for use with WAL databases. When a read-only transaction was started, it would take a "lock" on the WAL and then not advance further into the WAL until the transaction ended. Thus, all select statements issued

[sqlite] bug in query optimizer

Dear Dr. Hipps My bug report could not have been more clear. (And so is your response, BTW--I will not waste my time sending you bug reports in the future). Best Regards.

[sqlite] bug in query optimizer

I have a table, a partial index and a query, which (somewhat simplified) are: create table Objs (Seqnum int primary key, Status, Timeout int) without rowid; create index DIndex on Objs (Seqnum) where Status == 0; select Seqnum from Objs where Status == 0 and strftime('%s','now') >= Timeout

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

sqlite.org] On Behalf Of Reinhard Max Sent: Monday, July 27, 2015 12:04 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines On Mon, 27 Jul 2015 at 17:48, Marc L. Allen wrote: > When would that speci

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

on other machines On Mon, 27 Jul 2015 at 17:34, Marc L. Allen wrote: > Sorry.. confused does sqlite allow comparison between NULLs? No. > LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL > > Should never match anything... it checks to see if work.f

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

Sorry.. confused does sqlite allow comparison between NULLs? I mean... LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL Should never match anything... it checks to see if work.fpath IS NULL and if the same work.fpath is equal to something. I get the weird feeling that

[sqlite] Mozilla wiki 'avoid SQLite'

- A New Database Language - andl.org -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Marc L. Allen Sent: Thursday, 18 June 2015 11:51 PM To: General Discussion of SQLite Database Subject: Re: [sqlite

[sqlite] Mozilla wiki 'avoid SQLite'

I don't understand all the issues with SQL as it is today. I doubt I'm at the level of most of the posters in this group. However, I do write a mess of it so I thought I'd give my two cents as a programmer. For what it does, SQL does it really well. That is, it takes a couple of sets of

[sqlite] Mozilla wiki 'avoid SQLite'

I don't know. Back in the day, assembly was low-level because it was directly converted to machine code. C was high level because you could express more complex structures without worrying about the underlying architecture. I still like that distinction. I think people are trying to call C low

[sqlite] Mozilla wiki 'avoid SQLite'

Perhaps you might want to consider an apology and check your headers next time. I'm pretty sure the comment you quoted from this morning was written by Scott Doctor, not Scott Robinson. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org

[sqlite] saving :memory:database to disk

Don't you already have to have a DB initialization script to create the in-memory database? Couldn't you use that to create the on-disk database, and then have a separate script to copy all the data over? Or is this for more of a dynamic database whereby the users can add/remove tables and

Re: [sqlite] Select count(*)

I believe that when NULLs are allowed as PKs, they are all distinct. So, you can multiple rows with a NULL value as the PK. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dominique Devienne Sent: Thursday, December 11,

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

Doesn't that code risk being broken in a later version that doesn't update in the order provided by the sub-query? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J T Sent: Monday, December 08, 2014 9:23 AM To:

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

: Monday, December 08, 2014 9:18 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen <mlal...@outsitenetworks.com> wrote: > I am like you, Gwendal, in that I d

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

I am like you, Gwendal, in that I don't like that behavior in SQLite; however, not liking it doesn't make it a bug. The constraint-checking algorithm was defined to work exactly the way it's working. When designed, the fact that your type of insert would fail was known and understood. Hence,

Re: [sqlite] insert or ignore with foreign keys

I think INSERT OR IGNORE is designed to insert a record into a table if a record with its primary key doesn't already exist. It's not an INSERT AND IGNORE ON ANY ERROR. So: INSERT OR IGNORE INTO t2 VALUES (1,1) INSERT OR IGNORE INTO t2 VALUES (1,1) The above would not cause an error where,

Re: [sqlite] Bug

I think attachments are dropped. If the SQL is reasonable size, just post it. Otherwise, you'll need to host the screen shot somewhere and link to it. > On Nov 19, 2014, at 10:00 PM, Josef Handsuch wrote: > > Dear developer, I'd like to thank you for you brilliant

Re: [sqlite] select 1 where 1 - 1;

I think everyone agrees that SQLite does not strictly follow the SQL standards for WHERE clause expressions. The question is... should it? One must ask, "what makes SQLite lite?" I think this kind of simplification is of them. However, I can understand that it might rankle some people.

Re: [sqlite] select 1 where 1 - 1;

Really? Interesting. So... Select 1 Where 1 < inf; ? Or is it just when taking inf by itself? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Monday, May 05, 2014 11:32 AM To: General Discussion of

Re: [sqlite] Bug in division?

Not an error. Int/Int uses integer division and results in an integer number. When one number is a float, the result becomes a float. I don't know about all SQL varieties, but MSSQL is the same. > On Apr 30, 2014, at 8:04 AM, "Gene Connor" wrote: > > > SELECT

Re: [sqlite] Tcl variable substitution issue

I apologize for not replying directly to your messages. My account on this mailing list is not set up to send me individual messages. Richard Hipp wrote: You should do what you want, of course. But this statement is surprising since SQLite is really just a TCL extension that has "escaped" into

Re: [sqlite] Tcl variable substitution issue

be a good fit. I was wrong. But thanks for trying to help. /Don On Sun, Jan 26, 2014 at 11:33 AM, Donald Allen <donaldcal...@gmail.com> wrote: > There's a several-year-old discussion of this issue here: > > http://wiki.tcl.tk/19627 > > It looks like 'impedance mi

Re: [sqlite] Tcl variable substitution issue

the type of the substitution. I'm going to use something other than tcl for what I'm doing. On Sun, Jan 26, 2014 at 10:37 AM, Donald Allen <donaldcal...@gmail.com> wrote: > This script > > #!/usr/bin/env tclsh > > package require sqlite3 > > set x 1 > > sqlite3 db /tm

[sqlite] Tcl variable substitution issue

(2 > '1') as foo} { produces foo was 0 Looks like a bug to me, but perhaps I'm missing something. Comments? /Don Allen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

Sent: Tuesday, September 24, 2013 2:14 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement On Sep 24, 2013, at 8:09 PM, Marc L. Allen <mlal...@outsitenetworks.com> wrote: > Not complaining, mind you. MS SQL doesn'

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

NSERT OR REPLACE" statement On 24 Sep 2013, at 7:09pm, Marc L. Allen <mlal...@outsitenetworks.com> wrote: > Also, there are times when you do a bulk insert, so you have to structure the > query to not fail on records that are already present. Yeah. Actually I got what I posted wrong.

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

Discussion of SQLite Database Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement On 24 Sep 2013, at 6:58pm, Petite Abeille <petite.abei...@gmail.com> wrote: > On Sep 24, 2013, at 7:54 PM, "Marc L. Allen" <mlal...@outsitenetworks.com> > wr

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

of SQLite Database Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement On Sep 24, 2013, at 7:54 PM, "Marc L. Allen" <mlal...@outsitenetworks.com> wrote: > Yep. What most people want is an INSERT OR UPDATE. Yep. Which is what one usually calls 'MER

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

Yep. What most people want is an INSERT OR UPDATE. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille Sent: Tuesday, September 24, 2013 1:48 PM To: General Discussion of SQLite Database Subject: Re: [sqlite]

Re: [sqlite] Hints for the query planner

As I was reading this, I said to myself, "what they really need is a confidence value." Then I read the end and, there it was! A confidence value. Ok.. not exactly confidence, but I think you get my meaning. It seems to me that you're allowing the query writer to substitute personal

Re: [sqlite] UPDATE question

Yes, thanks. I was mistaken. On Sep 6, 2013, at 9:27 PM, "James K. Lowden" <jklow...@schemamania.org> wrote: > On Fri, 6 Sep 2013 07:56:53 -0500 > "Marc L. Allen" <mlal...@outsitenetworks.com> wrote: > >> I don't think it's a bug. > >

Re: [sqlite] UPDATE question

MySQL also uses this implementation. They acknowledge that it is not SQL compliant and that (I never thought of this), you cannot delete a record that has a foreign key link to itself. Postgres apparently has the ability to have deferred checking as of V9, but not before then. Please see:

Re: [sqlite] UPDATE question

Nice, but that still requires extra work. 1) Determine if row is already in table. 2) Determine next lower value. 3) Split difference and insert. There's also the possibility that the higher level APP expects the new row to have a sequence number of 3. -Original Message- From:

Re: [sqlite] UPDATE question

I don't think it's a bug. I don't believe there's any defined rule for how SQL should behave, is there? The updates are done serially not atomically. If the rows happen to be processed in reverse order, then no constraint is violated. In fact, if there was a way to define the order the

Re: [sqlite] UPDATE question

No one commented on my second thread (written after I actually understood the problem!). But, I proposed a two update sequence to do it. UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert AND Name = name_to_insert UPDATE table SET Sequence = -Sequence WHERE Sequence

Re: [sqlite] UPDATE question

Oops.. sorry.. I missed the last paragraph. If you're essentially single threaded.. I can do it in two updates... UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert AND Name = name_to_insert UPDATE table SET Sequence = -Sequence WHERE Sequence < 0 AND Name =

Re: [sqlite] UPDATE question

How about... ? UPDATE table SET Sequence = Sequence + 1 WHERE Sequence >= seq_to_insert AND Name = name_to_insert -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Peter Haworth Sent: Thursday, September 05, 2013 2:21 PM To:

Re: [sqlite] Collating sequences. Was: Query problems

The left-most of the first select? Or the second? Maybe I don't understand 'left-most?' -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, September 05, 2013 9:36 AM To: General Discussion of

Re: [sqlite] Table name length in sqlite affects performance. Why?

Not to mention having to check each new table to see if it's already in the database and the associated physical reads that might be associated with that. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich Sent:

Re: [sqlite] Inefficient covering index used for Subversion with SQLite 3.8.0

Am I understanding that, in this example, the I_NODES_PARENT is being chosen as the search index because... it's smaller and therefore faster to find initial qualifying rows that you can then use in some sort of ordered lookup in another index/table? I'm always in awe of some of the plans a

Re: [sqlite] segmentation fault with 3.8.0

Database Subject: Re: [sqlite] segmentation fault with 3.8.0 On Thu, Aug 29, 2013 at 11:47 AM, Marc L. Allen <mlal...@outsitenetworks.com > wrote: > Silly question.. I looked at the fix. Why ignore indexes with greater > than 4 fields? Isn't that a bit risky? Wouldn't it be better

Re: [sqlite] segmentation fault with 3.8.0

Silly question.. I looked at the fix. Why ignore indexes with greater than 4 fields? Isn't that a bit risky? Wouldn't it be better to ignore the fields after the 4th one for planning? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]

Re: [sqlite] Different result from experimental query

Looks like that should return one row, yes? I wonder if operator precedence is broken for that query and the OR is binding higher than the AND. Also possible is that the NOT NULL for id in table t is messing up some query optimization with t2.id NOT NULL. -Original Message- From:

Re: [sqlite] name resolutionn in GROUP BY

, 14 Aug 2013 14:57:19 -0500 "Marc L. Allen" <mlal...@outsitenetworks.com> wrote: > I'd actually like a compromise. Allow GROUP BY to accept a derived > name if no base name exists. I realize that's against spec, but > there's no ambiguity (as it otherwise errors

Re: [sqlite] name resolutionn in GROUP BY

I'd actually like a compromise. Allow GROUP BY to accept a derived name if no base name exists. I realize that's against spec, but there's no ambiguity (as it otherwise errors out), and does make it much nicer when the derived column is a hairy expression that I end up needing to replicate

Re: [sqlite] name resolution in GROUP BY

strictly on the derived table.   Peter From: Marc L. Allen <mlal...@outsitenetworks.com> >To: General Discussion of SQLite Database <sqlite-users@sqlite.org> >Sent: Wednesday, August 14, 2013 11:28 AM >Subject: Re: [sqlite] name resolution in GROUP BY > > >I understand.  My

Re: [sqlite] name resolutionn in GROUP BY

This appears to be how MS SQL handles it... looking at the definitions below, MS SQL uses the base value in GROUP BY and the derived value in ORDER BY. That said, 'lower(m)' referenced the base m, not the derived m in the ORDER BY. I'm afraid I don't understand enough about COLLATE to get

Re: [sqlite] name resolution in GROUP BY

resolution in GROUP BY On Wed, Aug 14, 2013 at 2:18 PM, Marc L. Allen <mlal...@outsitenetworks.com>wrote: > Heh... I forgot.. both selects below are identical, as 'lower(m1)' is > incorrect. MS SQL does not permit further operations on the derived value. > I think you also

Re: [sqlite] name resolution in GROUP BY

] On Behalf Of Richard Hipp Sent: Wednesday, August 14, 2013 2:26 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] name resolution in GROUP BY On Wed, Aug 14, 2013 at 2:18 PM, Marc L. Allen <mlal...@outsitenetworks.com>wrote: > Heh... I forgot.. both selects below are

Re: [sqlite] name resolution in GROUP BY

Heh... I forgot.. both selects below are identical, as 'lower(m1)' is incorrect. MS SQL does not permit further operations on the derived value. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marc L. Allen Sent: Wednesday

Re: [sqlite] name resolution in GROUP BY

I seem to recall having read that as well. I believe, however, that MySQL does allow it, but I think it defaults to base table when available. Also, a modified form of the test case: DROP TABLE #t1 CREATE TABLE #t1(m VARCHAR(4)); INSERT INTO #t1 VALUES('az'); INSERT INTO #t1 VALUES('by');

Re: [sqlite] name resolutionn in GROUP BY

As does MS SQL 2008 R2 DROP TABLE #Test CREATE TABLE #Test ( Val int ) INSERT INTO [#Test] ([Val]) VALUES (-2), (2) SELECT Val FROM #Test GROUP BY Val SELECT ABS(Val) AS Val FROM #Test GROUP BY Val Val --- -2 2 Val --- 2 2 Your requested test case: Untitled1 m - - 1 x 1

Re: [sqlite] attaching databases programmatically

Perhaps I misunderstood the question. It sounds like he has the sqlite* objects for the databases, but wants to be able to determine the database/filename associated with them so he can construct an ATTACH statement in another query. So.. the question is.. given an sqlite*, can you determine

Re: [sqlite] attaching databases programmatically

, July 22, 2013 10:51 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] attaching databases programmatically Marc L. Allen wrote on Monday, July 22, 2013 10:47 AM > Nelson, Erik wrote: > > I've got an application that allows the user to create an arbitrary > > numb

  1   2   3   >