[sqlite] Contstant WHERE terms still require table scan?

[Dan Kennedy]

On 06/12/2015 05:45 PM, nomad at null.net wrote:
> On Fri Jun 12, 2015 at 09:49:29AM +, Hick Gunter wrote:
>> Seems the correct code is already generated...
> Thanks Hick, that shows a bit more detail I didn't think to look for.
> It seems that this only works for bind values, as the comparison and goto
> statements aren't present when the term is defined at prepare time:
>
>  .width 4 10 4 4 4 10 2 10
>  EXPLAIN SELECT
>  x.id
>  FROM
>  x
>  WHERE
>  1=0
>  ;
>
>  addr  opcode  p1p2p3p4  p5  comment
>    --        --  --  --
>  0 Init0 9 0 00  NULL
>  1 Ne  2 7 1 51  NULL
>  2 OpenRead0 2 0 0   00  NULL
>  3 Rewind  0 7 0 00  NULL
>  4 Rowid   0 3 0 00  NULL
>  5 ResultRow   3 1 0 00  NULL
>  6 Next0 4 0 01  NULL
>  7 Close   0 0 0 00  NULL
>  8 Halt0 0 0 00  NULL
>  9 Transactio  0 0 1 0   01  NULL
>  10TableLock   0 2 0 x   00  NULL
>  11Integer 1 1 0 00  NULL
>  12Integer 0 2 0 00  NULL
>  13Goto0 1 0 00  NULL
>
> That makes me think that for the 1=0 case the scan occurs anyway?

I think the "Ne" at address 1 is the test in this case.

Dan.

[sqlite] Is recursive CTE fully capable?

[da...@andl.org]

Interesting.

SQL has been Turing complete since PSM was added to the 1992 standard. (Not
SQLite). I guess they mean "Turing complete with respect to the relation
datatype".

Andl already supports windowing (but not on SQLite). The Andl implementation
of recursion queries is nearly done.

I read the paper. It should provide a good source of sample queries for
Andl. However, I was not impressed by the "proof". Maybe you had to be
there.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Igor
Tandetnik
Sent: Friday, 12 June 2015 1:49 PM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] Is recursive CTE fully capable?

On 6/11/2015 8:08 PM, david at andl.org wrote:
> The question I'm trying to ask is whether recursive CTE (either as 
> defined in the standard or as implemented in SQLite) carries the full 
> capability of evaluating recursive queries on appropriate data 
> structures, or are there queries that are beyond what it can do?

http://assets.en.oreilly.com/1/event/27/High%20Performance%20SQL%20with%20Po
stgreSQL%20Presentation.pdf
"With CTE and Windowing, SQL is Turing Complete."

--
Igor Tandetnik

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

[sqlite] Virtual tables/xBestIndex: Is this a bug?

[Eric Hill]

Thanks, Dan.  I grabbed the fix and tried it out - works great, and a much 
better fix than what I suggested (not shocking).  It really cleans up 
xBestIndex.  In my simple cases, it's like the usable flag is now superfluous; 
xBestIndex is only getting passed usable constraints (and not getting called it 
all when there are no usable constraints).  But perhaps there are still 
scenarios where an unusable constraint will show up.

Eric

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy
Sent: Thursday, June 11, 2015 1:14 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] Virtual tables/xBestIndex: Is this a bug?

On 06/11/2015 03:49 AM, Eric Hill wrote:
> Is it a bug that SQLite changes the order of the constraints passed to 
> xBestIndex based merely on the order of the ON clause, when SQL (AFAIK) says 
> nothing about what the order of the ON clause should be?

No. The order is undefined.

However, it is less than perfect that the constraints on "film_id=" are marked 
usable when they are really not. That's the root of your problem I think - 
SQLite is asking for the wrong thing. When it eventually figures out that it 
can't actually use the plan it requested from xBestIndex (because the film_id= 
constraint is not actually usable) it falls back to a linear scan.

There is now a change on the trunk that should fix this:

   http://www.sqlite.org/src/info/7b446771cadedafb

Dan.

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

[sqlite] Is recursive CTE fully capable?

[da...@andl.org]

Appropriate just means: set up the data structures any way you like in order
to capture the right info and support suitable queries.

I'm not trying to find a shortcut to solving NP complete problems. If I did,
I probably wouldn't post it here.

The question is: are there problems for which:
a) a recursive description and/or recursive data structure are a good match
b) interesting and/or useful queries can be formulated
c) those queries can be solved by general recursive solutions in other
programming languages
d) cannot be solved by the recursion provided by SQL CTE, because of
inherent limitations in the model/algorithm it supports?

There are lots of interesting things you can do with graphs (including
trees, lists, DAGs etc) including creating them, navigating them, modifying
them and measuring them. How far does this particular tool get you?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon
Slavin
Sent: Friday, 12 June 2015 10:46 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is recursive CTE fully capable?


On 12 Jun 2015, at 1:08am, david at andl.org wrote:

> The question I'm trying to ask is whether recursive CTE (either as 
> defined in the standard or as implemented in SQLite) carries the full 
> capability of evaluating recursive queries on appropriate data 
> structures, or are there queries that are beyond what it can do?

I think your question can only be answered with "What you mean by
"appropriate" ?".  CTE is part of the 1999 SQL standard and as good a way as
any to implement directed graphs in SQL.

There are plenty of queries which can be expressed in a SQL database but
can't be answered without a computer which can reprogram itself, e.g. The
Halting Problem



or without a ridiculously long processing time, e.g. The Travelling Salesman
Problem



.  CTE is only one type of meta-programming and is not all-powerful.

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

[sqlite] Contstant WHERE terms still require table scan?

[no...@null.net]

On Fri Jun 12, 2015 at 09:49:29AM +, Hick Gunter wrote:
> Seems the correct code is already generated...

Thanks Hick, that shows a bit more detail I didn't think to look for.
It seems that this only works for bind values, as the comparison and goto
statements aren't present when the term is defined at prepare time:

.width 4 10 4 4 4 10 2 10
EXPLAIN SELECT
x.id
FROM
x
WHERE
1=0
;

addr  opcode  p1p2p3p4  p5  comment   
  --        --  --  --
0 Init0 9 0 00  NULL  
1 Ne  2 7 1 51  NULL  
2 OpenRead0 2 0 0   00  NULL  
3 Rewind  0 7 0 00  NULL  
4 Rowid   0 3 0 00  NULL  
5 ResultRow   3 1 0 00  NULL  
6 Next0 4 0 01  NULL  
7 Close   0 0 0 00  NULL  
8 Halt0 0 0 00  NULL  
9 Transactio  0 0 1 0   01  NULL  
10TableLock   0 2 0 x   00  NULL  
11Integer 1 1 0 00  NULL  
12Integer 0 2 0 00  NULL  
13Goto0 1 0 00  NULL  

That makes me think that for the 1=0 case the scan occurs anyway?

-- 
Mark Lawrence

[sqlite] Contstant WHERE terms still require table scan?

[no...@null.net]

> This would potentially allow me to shortcut some largish UNION
> statements.

I should clarify: I don't want to have to force my callers to use their
own if/then/else statements in order to pick a specific query. I want a
single general-purpose query they can call that shortcuts based on a
bind value (or WHERE clause to a VIEW).

-- 
Mark Lawrence

[sqlite] Contstant WHERE terms still require table scan?

[Mark Lawrence]

I'm wondering if it would be possible to optimize the query planner for
situations where one of the WHERE clauses is a constant that evaluates
to false?

CREATE TABLE x(
id INTEGER PRIMARY KEY
);

EXPLAIN QUERY PLAN SELECT
x.id
FROM
x
WHERE
1=0
;

-- selectidorder   fromdetail  
-- --  --  --  
-- 0   0   0   SCAN TABLE x


This would potentially allow me to shortcut some largish UNION
statements.

Mark.
-- 
Mark Lawrence
Home:   +41 44 520 12 59
Mobile: +41 76 796 65 68

[sqlite] Contstant WHERE terms still require table scan?

[Hick Gunter]

As Dan pointed out, the check is there in line 1.

I think it is a result of the query compiler very cleverly separating out the 
constant expressions and computing those once, before any table gets opened.

Im 3.7.14 (the version I am using), it also leaves in the check at each record, 
which is omitted in the code I suspect was generated by a newer version.

Maybe some time in the future , the unreachable sequence from lines 2-6 will be 
eliminated.
That wouldlleave the arena set to remove the TableLock (because the table is no 
longer referenced) and the Close and the Transaction (because no tables are 
used).
Eventually, with branch pruning we would be left with

0 Init 0 2 0 00 NULL
1 Halt 0 0 0 00 NULL
2 Goto 0 1 0 00 NULL

-Urspr?ngliche Nachricht-
Von: nomad at null.net [mailto:nomad at null.net]
Gesendet: Freitag, 12. Juni 2015 12:45
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Contstant WHERE terms still require table scan?

On Fri Jun 12, 2015 at 09:49:29AM +, Hick Gunter wrote:
> Seems the correct code is already generated...

Thanks Hick, that shows a bit more detail I didn't think to look for.
It seems that this only works for bind values, as the comparison and goto 
statements aren't present when the term is defined at prepare time:

.width 4 10 4 4 4 10 2 10
EXPLAIN SELECT
x.id
FROM
x
WHERE
1=0
;

addr  opcode  p1p2p3p4  p5  comment
  --        --  --  --
0 Init0 9 0 00  NULL
1 Ne  2 7 1 51  NULL
2 OpenRead0 2 0 0   00  NULL
3 Rewind  0 7 0 00  NULL
4 Rowid   0 3 0 00  NULL
5 ResultRow   3 1 0 00  NULL
6 Next0 4 0 01  NULL
7 Close   0 0 0 00  NULL
8 Halt0 0 0 00  NULL
9 Transactio  0 0 1 0   01  NULL
10TableLock   0 2 0 x   00  NULL
11Integer 1 1 0 00  NULL
12Integer 0 2 0 00  NULL
13Goto0 1 0 00  NULL

That makes me think that for the 1=0 case the scan occurs anyway?

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Is recursive CTE fully capable?

[Simon Slavin]

On 12 Jun 2015, at 4:48am, Igor Tandetnik  wrote:

> http://assets.en.oreilly.com/1/event/27/High%20Performance%20SQL%20with%20PostgreSQL%20Presentation.pdf
> "With CTE and Windowing, SQL is Turing Complete."

But SQLite doesn't have Windowing, right ?  Or does it ?

Simon.

[sqlite] confusing error msgs

[Simon Davies]

On 12 June 2015 at 06:42, Hick Gunter  wrote:
> You are creating each table in a separate file; a foreign key may only 
> reference a table in the same file.
>
> Your type declarations are faulty in that you are omitting an opening 
> parenthesis in a DECIMAL 4,3) declaration.
>
> SQLite does not constrain sizes, a TEXT(10) or a CHAR(1) variable my contain 
> arbitrary length strings; types only define affinities, the same fields could 
> be used to store integers, reals or BLOBS for that matter.
>
> SQLIte also does not implement fixed point numeric fields. DECIMAL (6,2) 
> defines a field of numeric affinity, i.e. if you provide a value that 
> contains a decimal point, it will be stored as a REAL.
>

And table constraints (foreign key in timeslip table) must follow the
column declarations
Regards,
Simon

[sqlite] Is recursive CTE fully capable?

[da...@andl.org]

The question I'm trying to ask is whether recursive CTE (either as defined
in the standard or as implemented in SQLite) carries the full capability of
evaluating recursive queries on appropriate data structures, or are there
queries that are beyond what it can do?

As far as I can see recursive CTE is very similar in capability (and
algorithm) to tail recursion. It's generally possible to code any loop and
most ordinary recursive functions in terms of tail recursion, which results
in highly efficient implementation. I'm interested to know where the limits
are, and what queries (if any) are beyond using this method.

If there is an academic paper or other reference that provides the answer
I'm happy to be pointed to that.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Contstant WHERE terms still require table scan?

[Hick Gunter]

Seems the correct code is already generated...

asql> explain select rowid from x where 1=?;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000  NULL
1 Integer1 2 000  NULL
2 Variable   1 3 000  NULL
3 Eq 3 1 272  NULL
4 IfNot  1 12100  NULL
5 Goto   0 14000  NULL
6 OpenRead   0 2 1 0  00  x
7 Rewind 0 12000  NULL
8 IfNot  1 11100  NULL
9 Rowid  0 4 000  NULL
10ResultRow  4 1 000  NULL
11Next   0 8 001  NULL
12Close  0 0 000  NULL
13Halt   0 0 000  NULL
14Transaction1 0 000  NULL
15VerifyCookie   1 1 100  NULL
16TableLock  1 2 0 x  00  NULL
17Goto   0 6 000  NULL

-Urspr?ngliche Nachricht-
Von: Mark Lawrence [mailto:nomad at null.net]
Gesendet: Freitag, 12. Juni 2015 11:37
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] Contstant WHERE terms still require table scan?

I'm wondering if it would be possible to optimize the query planner for 
situations where one of the WHERE clauses is a constant that evaluates to false?

CREATE TABLE x(
id INTEGER PRIMARY KEY
);

EXPLAIN QUERY PLAN SELECT
x.id
FROM
x
WHERE
1=0
;

-- selectidorder   fromdetail
-- --  --  --  
-- 0   0   0   SCAN TABLE x


This would potentially allow me to shortcut some largish UNION statements.

Mark.
--
Mark Lawrence
Home:   +41 44 520 12 59
Mobile: +41 76 796 65 68
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] SQLITE_THREADSAFE in VxWorks

[675079636]

Hello, I'm new.
If the compiler option SQLITE_THREADSAFE = 1 or 2 in VxWorks, what happens next?
If error: does this mean that does not support pthread? I think not.
If Success: how SQLITE design multicore multithreading by the Semaphore?
Well, do you successfully compiled it with SQLITE_THREADSAFE = 1 or 2  under 
VxWorks platform and it works?If there is an example, would be better.




Thanks for Andy Ling's help before

[sqlite] Is recursive CTE fully capable?

[Igor Tandetnik]

On 6/12/2015 5:33 AM, Simon Slavin wrote:
>
> On 12 Jun 2015, at 4:48am, Igor Tandetnik  wrote:
>
>> http://assets.en.oreilly.com/1/event/27/High%20Performance%20SQL%20with%20PostgreSQL%20Presentation.pdf
>> "With CTE and Windowing, SQL is Turing Complete."
>
> But SQLite doesn't have Windowing, right ?  Or does it ?

Not to my knowledge.
-- 
Igor Tandetnik

[sqlite] CTE/Sudoku talk (was User-defined types -- in Andl)

[Jeremy Sands]

Good evening gentlemen.   The talks are streaming DIRECTLY to YouTube.  So  
they will be available permanently, but maybe not immediately after the  
talk is over (YT transcode time, and they may not auto-publish until given  
direct blessing to do so).   But if you're in the area, I STRONGLY  
recommend stopping by.   It is free to attend, and the "hallway track" is  
of immense value.  And really, the strength of the DB talks in general  
this year is *insane*.

Cheers


On Thu, 11 Jun 2015 11:15:23 -0400, Richard Hipp  wrote:

> On 6/11/15, Drago, William @ CSG - NARDA-MITEQ  
>  wrote:
>> I'm in NY, but just by coincidence I'll be very close to Charlotte for a
>> graduation on the same darn day as your CTE/Sudoku talk. So close, yet  
>> so
>> far! I am also going to miss your Git: Just say "No!" talk which I would
>> have loved to see.
>>
>> So, if these talks are permanently available on YouTube (not just live
>> streamed) please let us know.
>>
>
> I'm not in charge of the conference so I don't know.  CCed this email
> to the people at SELF who are better able to answer.  Maybe they will
> reply.  If not, I'll probably find out next week.


-- 
Jeremy Sands
@jeremysands
Res publica non dominetur

Proprietor
Galt's Gulch Industries, LLC
P: 1-706-389-GALT
http://www.galtsgulchindustries.com

[sqlite] confusing error msgs

[Hick Gunter]

You are creating each table in a separate file; a foreign key may only 
reference a table in the same file.

Your type declarations are faulty in that you are omitting an opening 
parenthesis in a DECIMAL 4,3) declaration.

SQLite does not constrain sizes, a TEXT(10) or a CHAR(1) variable my contain 
arbitrary length strings; types only define affinities, the same fields could 
be used to store integers, reals or BLOBS for that matter.

SQLIte also does not implement fixed point numeric fields. DECIMAL (6,2) 
defines a field of numeric affinity, i.e. if you provide a value that contains 
a decimal point, it will be stored as a REAL.

-Urspr?ngliche Nachricht-
Von: Keller Racing [mailto:keller_racing at netscape.com]
Gesendet: Donnerstag, 11. Juni 2015 20:11
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] confusing error msgs

Hi all.  Yes, I'm a newby.  I have some past experience using dBASE III+ and I 
currently use freeBASIC for my programming needs.  I have a need to put a small 
database in one of my freeBASIC applications and SQLite seemed to fit the bill. 
 However I am having some problems.

I have 4 sql "CREATE TABLE" files: event.sql, timeslip.sql, baseline.sql and 
current.sql.  Here is the code in the various files:

CREATE TABLE event(
ev_rec_no INTEGER   PRIMARY KEY,
ev_date TEXT(10),
LOCATION TEXT(40),
SANCTION TEXT(10),
elevation DECIMAL(6, 2),
evNote_1 TEXT (76),
evNote_2 TEXT (76),
evNote_3 TEXT (76),
evNote_4 TEXT (76),
evNote_5 TEXT (76)
);

CREATE TABLE timeslip (
ts_rec_no INTEGER   PRIMARY KEY,
evnt_rec_no INTEGER,
FOREIGN KEY (evnt_rec_no) REFERENCES event(ev_rec_no),
stEvent_date TEXT (10),
stLocation TEXT (50),
stSanction TEXT (10),
sfElev DECIMAL (6, 2),
sfDist DECIMAL (5, 4),
sfWgt DECIMAL (5, 2),
usLaunch_rpm INTEGER,
usShift_rpm INTEGER,
stRun_time TEXT (5),
stAMPM CHAR (1),
stRun_type TEXT (2),
stLane CHAR (1),
stCarNum TEXT (5),
stClass TEXT (8),
sfCl_index DECIMAL (6, 4),
sfOvUn DECIMAL (6, 4),
sfDial DECIMAL (7,4),-- dial in
sfRt DECIMAL (6, 4), -- reaction time
sfTime_60_ft DECIMAL (7, 4),
sfTime_330_ft DECIMAL (7, 4),
sfTime_660_ft DECIMAL (7, 4),
sfMPH_660_ft DECIMAL (8, 4),
sfTIme_1000_ft DECIMAL (7, 4),
sfMPH_1000_ft DECIMAL (8, 4),
sfTime_1320_ft DECIMAL (7, 4),
sfMPH_1320_ft DECIMAL (8,4),
sfFirst DECIMAL (7,4),
sfMOV DECIMAL (6, 4),
stResult CHAR (1),   -- won or loss
sfTempFah DECIMAL(6,2),
sfRelHumid DECIMAL (4,3),
sfVapPress DECIMAL (4,3),
sfBaroPress DECIMAL 4,3),
stType CHAR(1),
stNote_1 TEXT (76),
stNote_2 TEXT (76),
stNote_3 TEXT (76),
stNote_4 TEXT (76),
stNote_5 TEXT (76)
);

CREATE TABLE baseline(
bl_rec_no INTEGER PRIMARY KEY NOT NULL,
stTestDate TEXT (10),
sfElev DECIMAL(6, 2),
sfTempFah DECIMAL(6,2),
sfRelHumid DECIMAL (4,3),
sfVapPress DECIMAL (4,3),
sfBaroPress DECIMAL 4,3),
stType CHAR(1),
sfMaxTrq DECIMAL (7,3),
sfMaxHP DECIMAL (7, 3),
sfCorrecFact DECIMAL (4,3),
stCorrecType TEXT (3),
sfWgt DECIMAL (5, 2),
stNote_1 TEXT (76),
stNote_2 TEXT (76),
stNote_3 TEXT (76),
stNote_4 TEXT (76),
stNote_5 TEXT (76)
);

CREATE TABLE current(
cr_rec_no INTEGER PRIMARY KEY,
stCurDate TEXT (12),
sfElev DECIMAL(6, 2),
sfTempFah DECIMAL(6,2),
sfRelHumid DECIMAL (4,3),
sfVapPress DECIMAL (4,3),
sfBaroPress DECIMAL 4,3),
stType CHAR(1),
stWgt DECIMAL (5,2),
sfMPHcf DECIMAL (5, 4),
sfETcf DECMIAL (5, 4),

stNote_1 TEXT (76),
stNote_2 TEXT (76),
stNote_3 TEXT (76),
stNote_4 TEXT (76),
stNote_5 TEXT (76)
);

Now I have a batch file that I use to construct the actual databases.  Here is 
that batch file:

REM **
REM db.bat - this batch file creats database for use with Performance Tuner REM 
**

del *.bak

REM sqlite3 -init event.sql pt__x3xx.db

REM sqlite3 -init event.sql event.db
REM C:\Documents and Settings\All 
Users\Documents\src\perftune\sqlite_test>sqlite3 -
REM init event.sql event.db
REM -- Loading resources from event.sql
REM SQLite version 3.7.5
REM Enter ".help" for instructions
REM Enter SQL statements terminated with a ";"
REM sqlite> .exit

REM sqlite3 -init timeslip.sql timeslip.db
REM C:\Documents and Settings\All 
Users\Documents\src\perftune\sqlite_test>sqlite3 -
REM init timeslip.sql timeslip.db
REM -- Loading resources from timeslip.sql
REM Error: near line 3: near "stEvent_date": syntax error

REM sqlite3 -init baseline.sql baseline.db
REM C:\Documents and Settings\All 
Users\Documents\src\perftune\sqlite_test>sqlite3 -
REM init baseline.sql baseline.db
REM -- Loading resources from baseline.sql
REM Error: near line 2: near "4": 

[sqlite] Is recursive CTE fully capable?

[Simon Slavin]

On 12 Jun 2015, at 1:08am, david at andl.org wrote:

> The question I'm trying to ask is whether recursive CTE (either as defined
> in the standard or as implemented in SQLite) carries the full capability of
> evaluating recursive queries on appropriate data structures, or are there
> queries that are beyond what it can do?

I think your question can only be answered with "What you mean by "appropriate" 
?".  CTE is part of the 1999 SQL standard and as good a way as any to implement 
directed graphs in SQL.

There are plenty of queries which can be expressed in a SQL database but can't 
be answered without a computer which can reprogram itself, e.g. The Halting 
Problem



or without a ridiculously long processing time, e.g. The Travelling Salesman 
Problem



.  CTE is only one type of meta-programming and is not all-powerful.

Simon.

[sqlite] Is recursive CTE fully capable?

[Igor Tandetnik]

On 6/11/2015 8:08 PM, david at andl.org wrote:
> The question I'm trying to ask is whether recursive CTE (either as defined
> in the standard or as implemented in SQLite) carries the full capability of
> evaluating recursive queries on appropriate data structures, or are there
> queries that are beyond what it can do?

http://assets.en.oreilly.com/1/event/27/High%20Performance%20SQL%20with%20PostgreSQL%20Presentation.pdf
"With CTE and Windowing, SQL is Turing Complete."

-- 
Igor Tandetnik