Documentation updated in an attempt to make this point clearer.
On Thu, Feb 6, 2014 at 11:36 AM, Richard Hipp <[email protected]> wrote: > > > > On Thu, Feb 6, 2014 at 6:19 AM, Stephan Beal <[email protected]>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 >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > [email protected] > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
