Hi,
Some time ago I created a complete parser for SELECT statements in SQLite
(based on the grammar found in the source code). Sample code below!
Understanding how the Lemon parser works, creating the abstract syntax tree
(AST), and implementing the parser classes turned out to be extraordinary
created when upgrading?
>
> On Tue, Nov 29, 2011 at 7:00 AM, Kristoffer Danielsson <
> kristoffer.daniels...@live.se> wrote:
>
> >
> >
> >
> >
> > Consider an SQLite database created with the SQLITE_ENABLE_STAT2 flag set.
> > After updating
Consider an SQLite database created with the SQLITE_ENABLE_STAT2 flag set.
After updating my software, the new SQLite version seems to use another flag;
SQLITE_ENABLE_STAT3. Will these stat3 optimizations be available when opening
and old database created with stat2? Or is it perhaps
qlite.org
> Subject: Re: [sqlite] ANALYZE necessary after database upgrade?
>
> On Thu, Aug 18, 2011 at 8:50 AM, Kristoffer Danielsson <
> kristoffer.daniels...@live.se> wrote:
>
> >
> >
> >
> >
> > I have noticed that certain (complex) select quer
the database and
send it to the sqlite team for analysis? > From: slav...@bigfraud.org
> Date: Thu, 18 Aug 2011 13:57:31 +0100
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ANALYZE necessary after database upgrade?
>
>
> On 18 Aug 2011, at 1:50pm, Kristoffer Danielsson
I have noticed that certain (complex) select queries return unexpected data
(missing rows) on my upgraded SQLite databases.My guess is that the optimizer
makes an erroneous decision on some index (I'm using both sqlite_stat1 and
sqlite_stat2). Is this a reasonable guess? Is it necessary to
Another solution is to transfer ownership of the handle to the new object.
E.g.: // Note that this requires the m_sqlite variable to be mutuable (for
modification of const-object).DatabaseRecord::DatabaseRecord(const
DatabaseRecord ){ m_sqlite = rhs.m_sqlite; ...
rhs.m_sqlite
Sqlite 3.7.5. Possible NATURAL JOIN bug.
I have a ~100 MB database which gives me odd results when running two similar
queries. I'm currently trying to create a minimal test case, but the error
seems to go away when I try to narrow it down.
In short, the queries look like this (scrambled
Nice :)
> To: sqlite-users@sqlite.org
> From: itandet...@mvps.org
> Date: Sat, 25 Sep 2010 10:21:18 -0400
> Subject: Re: [sqlite] Need help with self-join (I think)
>
> Kristoffer Danielsson <kristoffer.daniels...@live.se> wrote:
> >> From: oliver@web.
ot;fail", which increases the chances of doing mistakes.
> Date: Sat, 25 Sep 2010 02:47:05 +0200
> From: oliver@web.de
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Need help with self-join (I think)
>
> Am 25.09.2010 01:47, schrieb Kristoffer Danielsson:
>
&g
Yeah. USING is good to have :)
And yes, 123 might very well be a random number. I should have made that clear!
> To: sqlite-users@sqlite.org
> From: oliver@web.de
> Date: Sat, 25 Sep 2010 00:19:44 +
> Subject: Re: [sqlite] Need help with self-join (I think)
>
Ah, this one's easier to follow. What do you mean by "in this case"? What was
the condition that made it possible to remove the rest?
Again, thanks!
> To: sqlite-users@sqlite.org
> From: oliver@web.de
> Date: Sat, 25 Sep 2010 00:05:11 +
> Subject: Re: [sqlite] Need help with
sqlite] Need help with self-join (I think)
>
> Kristoffer Danielsson <kristoffer.daniels...@...> writes:
>
> SELECT DISTINCT t.Year, b.Name,
> CASE
> WHEN (SELECT TestID FROM Test WHERE Year = t.Year AND Name = b.Name) IS NULL
> THEN NULL
> ELSE (SELECT SomeValue FR
ith self-join (I think)
>
> On Sat, Sep 25, 2010 at 01:01:36AM +0200, Kristoffer Danielsson wrote:
> > CREATE TABLE Test (TestID INTEGER PRIMARY KEY, Year INT NOT NULL, Name TEXT
> > NOT NULL);
> > INSERT INTO Test (Year, Name) VALUES (2007, 'A');
> > INSERT INTO Test (
Hi,
I have an interesting SQL problem where I want certain rows to always be
represented. It feels like a self-join, but I'm not sure. Please help!
Create a database as follows:
CREATE TABLE Test (TestID
Definitely #1 if iOS supports 64-bit unix timestamps. You also store the time
of the day, which may prove useful later on.
#2 is non-standard. No time value.
#3 has no time value. '2010-09-03T01:23:45' (ISO 8601) would be an option
though.
#4 gives room for mistakes; the year may be
gt; ____
>
> From: sqlite-users-boun...@sqlite.org on behalf of Kristoffer Danielsson
> Sent: Mon 7/12/2010 6:39 AM
> To: sqlite-users@sqlite.org
> Subject: EXTERNAL:[sqlite] Terminate blocking SELECT-operation?
>
>
>
>
> I would like to
I would like to perform an asynchronous SELECT, terminatable by a semaphore or
equivalent. The problem I'm trying to solve is to allow termination of a
blocking SELECT-operation that take too long.
Does SQLite support that?
ite-users@sqlite.org
> Subject: Re: [sqlite] How to determine when to VACUUM?
>
>
> On 6 Jul 2010, at 10:45pm, Kristoffer Danielsson wrote:
>
> > Q: How do I programmatically (through sqlite-APIs?) determine if it's time
> > to VACUUM a database?
>
> It's never tim
I've been reading the documentation. I've been googling and thinking.
Still, I can't figure out the best way to determine when to run the
VACUUM-command. Note that I do NOT want to enable "auto vacuum".
I do remember reading something about calculating empty space, used pages etc
etc.
I solved my "inner/outer" problems by compiling Sqlite with
SQLITE_ENABLE_STAT2=1.
That flag makes it better at choosing the inner table!
> To: sqlite-users@sqlite.org
> From: itandet...@mvps.org
> Date: Wed, 11 Nov 2009 12:03:06 -0500
> Subject: Re: [sqlite] optimization question
>
> Tim
way to avoid column conflicts.
Thanks for your reply.
> From: slav...@bigfraud.org
> Date: Sat, 7 Nov 2009 23:55:52 +
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Avoiding "Ambigious column"?
>
>
> On 7 Nov 2009, at 10:56pm, Kristoffer Danielsson wrote
SQLite 3.6.20.
SELECT SUM(Salary - TotalSpent) / 100 FROM (T2 NATURAL JOIN T4) NATURAL JOIN
(T2 NATURAL JOIN T3);
Error: Ambigious column name: Salary
However, if I add two parentheses around "T2 NATURAL JOIN T4", the error goes
away:
SELECT SUM(Salary - TotalSpent) / 100 FROM
> On Nov 7, 2009, at 10:59 AM, Kristoffer Danielsson wrote:
> >
> > Present in both 3.6.19 and 3.6.20 (where it was fixed, according to
> > the ticket).
>
>
> In whatever environment you are running your experiment, please also
> run the following queries and
Like I said, I'm running 3.6.20 now. Same problem.
> From: d...@hwaci.com
> To: sqlite-users@sqlite.org
> Date: Sat, 7 Nov 2009 11:04:16 -0500
> Subject: Re: [sqlite] 3.6.20 NATURAL self-join still not fixed
>
>
> On Nov 7, 2009, at 10:54 AM, Simon Davies wrote:
> >
> > I used 10,000
still not fixed
>
> 2009/11/7 Kristoffer Danielsson <kristoffer.daniels...@live.se>:
> >
> > Using SQLite 3.6.20 (SQLITE_ENABLE_STAT2=1).
> >
> > PRAGMA foreign_keys=OFF;
> >
> > BEGIN TRANSACTION;
> >
> > CREATE TABLE Test
> > (
&g
Never" terminates
I have over 50,000 entries...
Thanks.
> From: danielk1...@gmail.com
> To: sqlite-users@sqlite.org
> Date: Sat, 7 Nov 2009 11:36:52 +0700
> Subject: Re: [sqlite] 3.6.20 NATURAL self-join still not fixed
>
>
> On Nov 7, 2009, at 5:25 AM, Kristoffer Da
ery so that it
> > contains joins with explicitly pointed condition which columns join
> > should be made on.
> >
> > BTW, why are you putting parenthesis there? Maybe it makes less
> > freedom to choose for SQLite's optimizer?
> >
> > Pavel
> >
>
ted condition which columns join
> should be made on.
>
> BTW, why are you putting parenthesis there? Maybe it makes less
> freedom to choose for SQLite's optimizer?
>
> Pavel
>
> On Sat, Oct 31, 2009 at 7:27 PM, Kristoffer Danielsson
> <kristoffer.daniels...@live.se>
; bigger table. And in this case there's no performance hit in such
> decision, only benefit.
>
> Pavel
>
> On Fri, Oct 30, 2009 at 12:07 PM, Kristoffer Danielsson
> <kristoffer.daniels...@live.se> wrote:
> >
> > Quote from: http://sqlite.phxsoftware.com/forums/p/1495
Quote from: http://sqlite.phxsoftware.com/forums/p/1495/6629.aspx
SQLite uses only nested loops to implement joins. Given a query like the
following:
SELECT ...
FROM OuterTable O INNER JOIN InnerTable I ON O.Id = I.Id
SQlite does something like this:
for each row in OuterTable
Seek
I'm not sure if the bug-fix for "natural self-join" solves this, but here we go.
This query yields a cartesian product:
SELECT COUNT(X) FROM A NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN D);
However, if I rearrange the tables I get the desired result:
SELECT COUNT(X) FROM (B NATURAL
I don't know about SQLite, but in all SQL courses you learn that you should
NEVER use the asterisk.
The asterisk is merely there to let you quickly view data _manually_.
> Date: Wed, 28 Oct 2009 16:02:01 +0200
> From: mi...@limbasan.ro
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite]
I've seen this too!
Had to refactor my "x IN (y)" code...
Perhaps the optimizer can be improved for this particular case?
> Date: Tue, 27 Oct 2009 11:06:14 -0700
> From: t...@zimbra.com
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Performance issues for "WITH x IN (y)" - fixed with "x
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:
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
>
the inner
workings of SQLite - would suffice?
> Date: Sun, 25 Oct 2009 10:08:01 -0500
> From: punk.k...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How does SQLite treat repeated expressions?
>
> On Sun, Oct 25, 2009 at 9:49 AM, Igor Tandetnik <itandet...@m
Consider the two statements below.
A: SELECT COUNT(DataID) FROM Data GROUP BY DataID ORDER BY COUNT(DataID);
B: SELECT COUNT(DataID) AS X FROM Data GROUP BY DataID ORDER BY X;
Is statement B faster than A? Why?
I want to my SQLite database to NEVER accept cartesian products. That is, if
the user makes a query that results in a cartesian product (explicit or
implicit), the execution of it must fail!
Is this possible?
Is this the right behavior?
CREATE TABLE t1(x INTEGER NOT NULL, y INTEGER);
INSERT INTO t1(x, y) VALUES (1, 0);
INSERT INTO t1(x, y) VALUES (2, 1);
INSERT INTO t1(x, y) VALUES (3, NULL);
sqlite> SELECT * FROM t1 A NATURAL JOIN t1 B;
1|0
2|1
I sense this could be right, but anyway... why
ate: Mon, 19 Oct 2009 12:19:02 -0700
> From: rog...@rogerbinns.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "x NATURAL JOIN x" BUG
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Kristoffer Danielsson wrote:
> > When can we expect a bu
?
Thanks.
> From: danielk1...@gmail.com
> To: sqlite-users@sqlite.org
> Date: Mon, 19 Oct 2009 14:44:27 +0700
> Subject: Re: [sqlite] "x NATURAL JOIN x" BUG
>
>
> On Oct 19, 2009, at 2:05 PM, Kristoffer Danielsson wrote:
>
> >
> > I did provi
; From: dar...@darrenduncan.net
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "x NATURAL JOIN x" BUG
>
> Kristoffer Danielsson wrote:
> > NATURAL INNER JOIN yields a cartesian product too. Just tried it!
>
> Well then, *surely* that at least has to b
, Oct 18, 2009 at 02:17:42PM +0200, Kristoffer Danielsson scratched
> > on the wall:
> >> Clearly, SQLite executes a cartesian product!
> >
> > Look at the output. It does not produce a Cartesian product. All
> > the rows are valid:
> >
> > SQLite versio
file.close();
return 0;
}
//
> Date: Sat, 17 Oct 2009 23:02:10 -0700
> From: dar...@darrenduncan.net
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Foreign keys + NATURAL JOIN
>
sers@sqlite.org
> Subject: Re: [sqlite] Foreign keys + NATURAL JOIN
>
> Kristoffer Danielsson wrote:
> > Q: Does foreign keys affect NATURAL JOINs?
> >
> > I haven't tested this. Does this NATURAL JOIN produce an inner join or a
> > cartesian product?
>
> The pre
Q: Does foreign keys affect NATURAL JOINs?
SQLite 3.6.18 sample (NO foreign keys):
CREATE TABLE t1 (a INTEGER PRIMARY KEY, b INTEGER NOT NULL);
CREATE TABLE t2 (x INTEGER PRIMARY KEY, y INTEGER NOT NULL);
SELECT * FROM a NATURAL JOIN b; -- Cartesian product!
SQLite 3.6.19 sample (using
e will be better.
>
> All these are pretty simple theoretical examples but I believe they
> all are implemented in SQLite optimizer.
>
>
> Pavel
>
> On Wed, Oct 7, 2009 at 9:08 AM, Kristoffer Danielsson
> <kristoffer.daniels...@live.se> wrote:
> >
> >
gain.
Thanks.
> Date: Tue, 6 Oct 2009 20:33:17 -0700
> From: dar...@darrenduncan.net
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Multiple NATURAL JOIN precedence?
>
> Kristoffer Danielsson wrote:
> > All my tables have unique column names, so I tend to u
All my tables have unique column names, so I tend to use "NATURAL JOIN" for my
joins (all join-columns are indexed). When I have multiple tables, I sometimes
get incredibly slow queries (not sure if they even terminate).
E.g.
SELECT SUM(x) FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 NATURAL
case is to add column to the table which
> will store the result of your function, create an index on that
> additional column and then use that column in the query. In this case
> SQLite will understand that it's better to use index and will use it.
>
> Pavel
>
> On Mon, Oct 5,
When I create my own "stored procedures" using sqlite3_create_function, I get
horrible performance (which I expected) even though the column of interest is
INDEXED.
Consider this sample (it's stupid, but it shows my problem):
SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate);
Consider this simple database:
CREATE TABLE T1
(TestID INTEGER PRIMARY KEY, X INTEGER NOT NULL, Y NOT NULL, Z NOT NULL,
UNIQUE(X, Y));
CREATE TABLE T2
(TestID INTEGER NOT NULL, X2 INTEGER NOT NULL, Y2 NOT NULL, Z2 NOT NULL);
What would be the most efficient way of copying all T1
I have made a topdown parser of a subset of the SQLite syntax. Now I need to
verify its correctness.
Does the SQLite developer team make use of some kind of regression tests where
complex statements are being tested?
It would really be great if I could find some 100 SELECT-statements for
Kristoffer Danielsson wrote:> Consider an application where the user enters a
search string:> SELECT Year FROM Car WHERE Brand = 'Audi'> > Now I want to add
some conditions, depending on settings in the application. For instance, I
would like to modify the above SQL string to get t
Consider an application where the user enters a search string:
SELECT Year FROM Car WHERE Brand = 'Audi'
Now I want to add some conditions, depending on settings in the application.
For instance, I would like to modify the above SQL string to get this:
SELECT Year, Color FROM Car WHERE Brand =
56 matches
Mail list logo
