On Thu, Feb 6, 2014 at 6:19 AM, Stephan Beal <sgb...@googlemail.com> wrote:
> Hi, all, > > these docs: > > http://www.sqlite.org/draft/lang_with.html > > state that a CTE select must be two UNION'd selections, It says that a *recursive* CTE must have a UNION or UNION ALL. An ordinary CTE is not subject to that requirement. According to SQL:1999 the "RECURSIVE" keyword does not make a CTE recursive. The RECURSIVE keyword really means "potentially recursive". So the CTE still must meet the other requirements to be recursive: (1) it is a UNION or UNION ALL and (2) there is exactly one self-reference in the right-hand SELECT of the UNION or UNION ALL. Note that for compatibility with SQL Server and Oracle (and at variance with SQL:1999 and PostgreSQL) SQLite makes RECURSIVE optional in all cases. So in SQLite, a CTE is recursive if it meets conditions (1) and (2) above and it is an ordinary CTE if it does not. The RECURSIVE keyword is just noise that the parser silently ignores. > but the following > query seems to work regardless of whether or not i've got one or two > SELECTs: > > (requires a Fossil repo database and one or more valid wiki page names from > that fossil db.) > > WITH RECURSIVE > page_name(name) AS( > -- SELECT 'home' -- long history > -- UNION ALL > SELECT 'HackersGuide' -- short history > UNION ALL > SELECT 'building' -- moderate history > ), > wiki_tagids(name, rid,mtime) AS ( > SELECT page_name.name, x.rid AS rid, x.mtime AS mtime > FROM tag t, tagxref x, page_name > WHERE x.tagid=t.tagid > AND t.tagname='wiki-'||page_name.name > -- ORDER BY mtime DESC > ), > wiki_lineage(name, rid,uuid, mtime, size, user) AS( > -- docs say that this must be two UNION'd selects, > -- but it seems to work without two... > SELECT wt.name, wt.rid as rid, > b.uuid as uuid, > wt.mtime as mtime, > b.size as size, > coalesce(e.euser,e.user) as user > FROM wiki_tagids wt, > blob b, > event e > WHERE wt.rid=b.rid > AND e.objid=b.rid > ) > SELECT name, rid,uuid,datetime(mtime,'localtime'),size,user > FROM wiki_lineage > ORDER BY mtime DESC; > > > Or am i misunderstanding what the docs intend to say? > > -- > ----- stephan beal > http://wanderinghorse.net/home/stephan/ > http://gplus.to/sgbeal > "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of > those who insist on a perfect world, freedom will have to do." -- Bigby > Wolf > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
