Re: [sqlite] Grammar of "X is Y"

John Crenshaw wrote: > Yeah, I tend to agree that null != null is confusing, But SQL doesn't have NULL != NULL. It has NULL != NULL IS NULL. That makes it even more confusing. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Grammar of "X is Y"

Meh, I don't want it THAT badly. I'm just saying that's how it should have been in the original design of the SQL language. In fact though, it probably wouldn't have mattered. Every different RDBMS seems to treat nulls differently in this regard, so I'm not even sure exactly what the spec says on

Re: [sqlite] Grammar of "X is Y"

On Tue, Oct 27, 2009 at 04:07:37PM -0700, Darren Duncan scratched on the wall: > Jay A. Kreibich wrote: > > On Tue, Oct 27, 2009 at 04:15:57PM +, Tom Sillence scratched on the > > wall: > >> because I really want to write neat queries like: > >> > >> select col1 is col2 from table > > > >

Re: [sqlite] Grammar of "X is Y"

John Crenshaw wrote: > Yeah, I tend to agree that null != null is confusing, however that is > the way it is "supposed" to behave, so changing that would break a lot > of code. If I had my way, and the behavior of NULL in operations COULD > be changed, I would opt for the following: > > NULL =

Re: [sqlite] Grammar of "X is Y"

Yeah, I tend to agree that null != null is confusing, however that is the way it is "supposed" to behave, so changing that would break a lot of code. If I had my way, and the behavior of NULL in operations COULD be changed, I would opt for the following: NULL = NULL -> true As a Boolean, NULL ->

Re: [sqlite] Conditional JOIN

On Tue, Oct 27, 2009 at 12:43:56PM -0700, Peter Haworth scratched on the wall: > It almost works but not quite. I believe that at least part of the > reason for this is that, as you commented, there can be multiple > entries in TableB that match any single ProdID/PriceTable in TableA.

[sqlite] Performance issues for "WITH x IN (y)" - fixed with "x = y"

While doing performance optimizations for queries on a table with 100k rows of email msg details and numerous indexes (Zimbra desktop email client), I found that select queries including x IN (y) can have a severe speed issue, especially when there is an ORDER by component as well. This is

[sqlite] Grammar of "X is Y"

The documentation, http://sqlite.org/lang_expr.html suggests that "is" behaves like "=" except that "null is null" returns true. However in practice the sqlite sql parser won't accept the word "is" followed by anything other than "null" (oh and I suppose "not" as in "is not"). sqlite> select 1 is

Re: [sqlite] Idea for improving page cache

I don't think ULL buys you as much extra cache space as you think, and it certainly isn't anywhere near as fast as LL. Consider the following problems that still remain: 1. If you need to point to an element (common with linked lists) it gets really messy. Sorted inserts and deletes will change

Re: [sqlite] Grammar of "X is Y"

John Crenshaw wrote: >> There's nothing wrong with that. > > Not unless style counts for something. X is Y looks far too much like X > as Y for my taste. I'd rather do a little extra typing to have clear > logic than to have clearly unclear code like that. My first thought when > I saw this was

Re: [sqlite] Grammar of "X is Y"

> There's nothing wrong with that. Not unless style counts for something. X is Y looks far too much like X as Y for my taste. I'd rather do a little extra typing to have clear logic than to have clearly unclear code like that. My first thought when I saw this was "doesn't he mean AS?" If I saw

Re: [sqlite] Grammar of "X is Y"

Jay A. Kreibich wrote: > On Tue, Oct 27, 2009 at 04:15:57PM +, Tom Sillence scratched on the wall: >> because I really want to write neat queries like: >> >> select col1 is col2 from table > > Are you sure? You just want a result set of true/false values? There's nothing wrong with that.

Re: [sqlite] Idea for improving page cache

On Tue, Oct 27, 2009 at 04:28:11PM -0400, John Crenshaw wrote: > "advantage" kind of depends. ULL is more specialized. You gain some > benefit, but also lose some as well. For example, consider what is > involved in doing a sorted insert into an ULL. On the other hand, you > can get all of the

Re: [sqlite] Deadlock with two local instances

> On Tue, 27 Oct 2009 21:50:12 +0100, "Marcus Grimm" > wrote: > >>PS: Does anybody know how I can edit this >>example code ? I recently attempted to add a clear >>PD statement and also add some comments but when >>I try to edit I allways end up in the wiki index page... >

Re: [sqlite] Deadlock with two local instances

On Tue, 27 Oct 2009 21:50:12 +0100, "Marcus Grimm" wrote: >PS: Does anybody know how I can edit this >example code ? I recently attempted to add a clear >PD statement and also add some comments but when >I try to edit I allways end up in the wiki index page... Last time

Re: [sqlite] Deadlock with two local instances

> Thanks! I ran the example code and it seems like every UPDATE fails > with errors like the following: > > SqlStep Timeout on handle: 8 (rc = 6) > SqlStep tries on handle 8: 200 > BeginTrans Timeout/Error on handle: 8, Errorcode = 6 > Write Thread: DB is busy! tries = 142 handle = 8 > > Looking

Re: [sqlite] Idea for improving page cache

"advantage" kind of depends. ULL is more specialized. You gain some benefit, but also lose some as well. For example, consider what is involved in doing a sorted insert into an ULL. On the other hand, you can get all of the same locality benefit with a pool allocation scheme. You don't reduce

Re: [sqlite] Idea for improving page cache

> Not really, just a reference to the ullNode that contains the page reference. > This ullNode can be searched quite quickly to find the referenced page, once > its on the CPU cache. ullNode can be merged with another node. And it will happen often in SQLite's use case. > Are you sure the list

Re: [sqlite] Idea for improving page cache

--- On Tue, 10/27/09, Pavel Ivanov wrote: > From: Pavel Ivanov > Subject: Re: [sqlite] Idea for improving page cache > To: kennethinbox-sql...@yahoo.com, "General Discussion of SQLite Database" > > Date: Tuesday, October 27,

Re: [sqlite] Idea for improving page cache

I really like the concept of ULL. Check this one out: http://blogs.msdn.com/devdev/archive/2005/08/22/454887.aspx Don't know if would be of any use for SQLite, but it does indeed provide an advantage compared to regular linked lists. > Date: Tue, 27 Oct 2009 14:59:36 -0400 > From:

Re: [sqlite] sqlite-users Digest, Vol 22, Issue 74

Thanks for this. I tried the CASE solution in preference to the coalesce solution since I'm more familiar with CASE than coalesce. Your statement that there must be matching TableB entries with PriceTable STANDARD for all values of TableA.ProdID is correct. It almost works but not quite.

Re: [sqlite] (no subject)

Indeed, stupid typo in my sql. Sorry for the noise and the lack of a subject on the subject line in my first email. David --- On Tue, 10/27/09, Pavel Ivanov wrote: > From: Pavel Ivanov > Subject: Re: [sqlite] (no subject) > To: "General Discussion of

Re: [sqlite] (no subject)

Something screwed up in your application: sqlite> create table assets (Code Text, Acct1 Text, Acct2 Text); sqlite> insert into assets values ('C0', 'name1', 'name2'); sqlite> select * from assets; C0|name1|name2 sqlite> insert into assets select 'C0', 'name1', 'name3' where not exists (select 1

Re: [sqlite] Idea for improving page cache

Supposing that the reduced cache misses are worth it, I think it would be better to simply allocate the nodes from a pool. Allocating from a pool maximizes locality and prevents the overhead involved in each allocation. Since the nodes have static size, pool allocation is easy. This doesn't

[sqlite] (no subject)

I have a table ASSETS with fields (Code Text, Acct1 Text, Acct2 Text). (There are other fields, and the primary key is a combination of 5 columns) For one code ('C0') I want to implement a rule that if I attempt to insert a combination of 'C0'/Acct1/Acct2 it will be ignored if the first two keys

Re: [sqlite] Idea for improving page cache

Ken, Kristoffer, are you talking about general ULL theory, game development or about development of page cache in SQLite? Pavel On Tue, Oct 27, 2009 at 2:31 PM, Ken wrote: > > > --- On Tue, 10/27/09, Kristoffer Danielsson > wrote:

Re: [sqlite] Idea for improving page cache

--- On Tue, 10/27/09, Kristoffer Danielsson wrote: > From: Kristoffer Danielsson > Subject: Re: [sqlite] Idea for improving page cache > To: sqlite-users@sqlite.org > Date: Tuesday, October 27, 2009, 1:03 PM > > In game

Re: [sqlite] Grammar of "X is Y"

On Tue, 27 Oct 2009, John Crenshaw wrote: > To: General Discussion of SQLite Database > From: John Crenshaw > Subject: Re: [sqlite] Grammar of "X is Y" > > ROFL > > -Original Message- > From: sqlite-users-boun...@sqlite.org >

Re: [sqlite] SQLite: question

On Tue, 27 Oct 2009, Ken wrote: > To: General Discussion of SQLite Database > From: Ken > Subject: Re: [sqlite] SQLite: question > > > Sql uses a single quote or a tick mark to delimit strings. > C, C++ and other languages use Dobule

Re: [sqlite] Idea for improving page cache

In game development you seldom use linked list altogether due to the increased rate of cache-misses. Why not use an array with some smart lookup-algorithm? > From: paiva...@gmail.com > Date: Tue, 27 Oct 2009 13:38:27 -0400 > To: kennethinbox-sql...@yahoo.com; sqlite-users@sqlite.org >

Re: [sqlite] Deadlock with two local instances

> Are these errors normal? If you're executing select statement, never get all rows from it and never reset/finalize it then yes, all updates from other thread will fail and it's normal. Pavel On Tue, Oct 27, 2009 at 1:42 PM, Chris T wrote: > Thanks!  I ran the example

Re: [sqlite] Deadlock with two local instances

Thanks! I ran the example code and it seems like every UPDATE fails with errors like the following: SqlStep Timeout on handle: 8 (rc = 6) SqlStep tries on handle 8: 200 BeginTrans Timeout/Error on handle: 8, Errorcode = 6 Write Thread: DB is busy! tries = 142 handle = 8 Looking at the database

Re: [sqlite] Idea for improving page cache

Are you sure that there will be improvement with ULL? If you're talking about improving due to CPU internal cache then first of all you have to store in the list pointers to pages, not pages themselves (you don't want to store several pages in one chunk of memory, do you?). So you're getting one

[sqlite] Idea for improving page cache

Hi All, I have an idea that could improve the page cache performance. Instead of using a regular linked list to connect pages that are on the cache use an "unrolled linked list". On some architectures due to the CPU caching the ULL is about 40 times faster. Still this is mostly

Re: [sqlite] Slow SELECTs in application

Yeah, you don't just need an index on all columns. You need the right indexes with the right combination of columns. Also, use prepared statements. If you don't use prepared statements SQLite will have to recompile those queries at every execution, which can take some time. John -Original

Re: [sqlite] Grammar of "X is Y"

ROFL -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Griggs, Donald Sent: Tuesday, October 27, 2009 12:31 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Grammar of "X is Y" Importance: Low Depends on what

Re: [sqlite] Grammar of "X is Y"

Depends on what your definition of "is" is. (Sorry, non-English speakers. This is a tiny joke based in American political history) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Grammar of "X is Y"

On Tue, Oct 27, 2009 at 04:15:57PM +, Tom Sillence scratched on the wall: > because I really want to write neat queries like: > > select col1 is col2 from table Are you sure? You just want a result set of true/false values? -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our

Re: [sqlite] Grammar of "X is Y"

On Oct 27, 2009, at 12:15 PM, Tom Sillence wrote: > The documentation, http://sqlite.org/lang_expr.html suggests that "is" > behaves like "=" except that "null is null" returns true. However in > practice the sqlite sql parser won't accept the word "is" followed by > anything other than "null"

Re: [sqlite] Grammar of "X is Y"

This behavior of "is" appeared in 3.6.19. What version of SQLite do you have? Pavel On Tue, Oct 27, 2009 at 12:15 PM, Tom Sillence wrote: > The documentation, http://sqlite.org/lang_expr.html suggests that "is" > behaves like "=" except that "null is null" returns true.

[sqlite] Grammar of "X is Y"

The documentation, http://sqlite.org/lang_expr.html suggests that "is" behaves like "=" except that "null is null" returns true. However in practice the sqlite sql parser won't accept the word "is" followed by anything other than "null" (oh and I suppose "not" as in "is not"). sqlite> select 1 is

Re: [sqlite] SQLite: question

Sql uses a single quote or a tick mark to delimit strings. C, C++ and other languages use Dobule quote to delimit strings. I think though that the column names may be double quoted to differentiate them from data. --- On Tue, 10/27/09, D. Richard Hipp wrote: > From: D.

Re: [sqlite] SQLITE_ENABLE_COLUMN_METADATA question ...

> As a matter of interest, how long does it take to > compile sqlite on your machine? Not long? About 5 seconds, though I didn't measure and this figure can be not exact enough. ;-) Pavel On Tue, Oct 27, 2009 at 5:19 AM, Keith Roberts wrote: > On Mon, 26 Oct 2009, Rob Sciuk

[sqlite] undocumented "ignore_check_constraints" pragma

Hi Everyone, I was just about to start investigating what was involved in implementing a pragma that did just this - only to find it exists already ! (ver 3.6.15) (and it does function as it says on the tin) (look at the flagPragma function "ignore_check_constraints", SQLITE_IgnoreChecks ) Any

Re: [sqlite] SQLite: question

Question forwarded to the sqlite-users mailing list. Quick answer: String are quoted in SQL using single quotes, not double-quotes. What you are seeing is not a bug. You are misusing the string quoting mechanism. On Oct 27, 2009, at 8:51 AM, Sergiu _ wrote: > Hello, > > I use SQLite in

Re: [sqlite] Slow SELECTs in application

On 27 Oct 2009, at 8:30am, Unabashed wrote: > I have two general types of selects for this table. First one is > "SELECT > word,wform,id_norm FROM mgWords WHERE id=" > and second is > "SELECT > id FROM mgWords WHERE (word='') AND (wform='') > AND > (id_norm=)". > So I tried to add indexes

Re: [sqlite] SQLITE_ENABLE_COLUMN_METADATA question ...

On Mon, 26 Oct 2009, Rob Sciuk wrote: > To: SQLite Users Digest > From: Rob Sciuk > Subject: [sqlite] SQLITE_ENABLE_COLUMN_METADATA question ... > > > I've just compiled the latest (3.6.19) with the I'm using a pre-compiled version shipped with

Re: [sqlite] Slow SELECTs in application

Thanks very much to all for answers! Sorry, I had some troubles with my internet connection, so I've read them just now. I need exactly unique value by set of this three columns in my table, that is correct. Application do not write temporary data to the disc, because it works in one transaction,

Re: [sqlite] Deadlock with two local instances

> Another odd thing is that when I call sqlite3_reset on the prepared > statement, it also returns SQLITE_BUSY. Should I only reset the > statement when it has been executed successfully? one possible approach when getting SQLITE_BUSY is to retry the sqlite3_step call until it finally gets thru.