Sorry, this struck a bit of a sore spot with me, so I apologize for the small rant... Feel free to completely ignore it.
CTEs are important for two reasons: 1. Simplification of query syntax. One can argue that this isn't terribly important in a system designed as an embedded database, rather than a BI-grade data mining target. (though I'm sure many people are also using it as such). But, whatever. 2. Query optimization. If I have to use the same subselect more than once in a query, it is a good optimization to tell the query parser to take a certain set of results, store them in a temporary resultset for this single query, then use that as a target of the second query. So, yes, you could break out a CTE into create temporary table/do final query/drop temporary table, but that adds a layer of complexity that's not necessary in most database engines, and hence aren't found in most ORMs. Yes, you could add custom code to support this, but when it often makes sense to do exactly what CTEs are meant to do, it seems like a no-brainer from a theoretical support standpoint. While a query optimizer can potentially deduce the usefulness of the right order to do subqueries in, often times, as a programmer, I know that I need a single query that will reduce a large dataset to a very small one, and then I need a few nontrivial operations over the very small dataset. Just saying "use multiple subselects" doesn't give any useful feedback about whether that will be properly optimized or not, and what caveats there are to the optimization process. It also leaves you with a disgustingly long query in many cases. Non-bug-related posts to this list often take the form of one of the following few categories: 1. Underqualified programmers asking for query help to do their jobs that a qualified programmer could easily do. Doesn't belong on the list -- I'm sure there's a #sql-newbies list somewhere for things like this, and there should be a form-letter answer forwarding people to that. 2. Feature requests from underqualified programmers that don't realize the right way to do something. Doesn't really belong on the list, though they mostly get shot down pretty quick or someone points out the obvious answer. Whatever, doesn't take up much mental/email bandwidth. I can go either way on this. 3. True feature requests that are not implemented in SQLite and would be useful to a set of users/developers in some way/shape/form and is not directly workaroundable. What I don't like is how often #3 requests gets shot down as being stupid. Yes, often a feature request doesn't really fit with the general mantra of SQLite, and it can be easily described as such. However, many things are in a pretty grey area. For example, CTEs would fit fairly nicely with the general mantra of SQLite, since it allows for making things smaller/simpler/more explicit for the QO, but it's being shot down as a non-useful feature that can be worked around. Well, can it? Or does sqlite perform the subselect multiple times if you mention the same query a couple different times in subtly different ways (case sensitive, etc.)? There are important nuances here before completely dismissing something out of hand. Just because there is another way to do something doesn't mean it's not a valid feature request to be prioritized with the rest of the feature requests. Saying "this is a potential future feature someday, but due to [some architecture issue] it's actually quite complicated to implement, and, as such, is unlikely to ever be actually implemented" is a completely valid answer from a project management perspective. Simply dismissing something out of hand without a thorough explanation of why, however, isn't quite as valid. -David -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RSmith Sent: Thursday, December 26, 2013 5:37 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] "Common Table Expression" This reminds me of a plan to add RADAR dishes to cars to monitor other traffic and avoid collisions - brilliant idea but the detrimental effect on aerodynamics and limiting size-factor of already-built garages all over the world stifled enthusiasm. Probably "Temporary Views" would be the exact thing that can achieve the same as CTE. Further simplification might be implemented on your code, if in fact you are designing a system and not using some other SQLite-reliant system (in which case CTE might really help you). To emphasize what Simon said: SQLite does not support a full syntactic script engine with variables and the like and isn't likely to expand by doing it and/or include CTE for the simple reason that the cost tradeoff in DB-Engine size vs. added functionality is non-sensical. It has to function in many cases as a DB engine on embedded systems where space is a real concern, and those designers would dread the idea of devoting more memory in the name of readability - and it is probably fair to extend that sentiment to designers with non-embedded systems (I know it is true for me). One might probably add a compile-time switch enabling or disabling (or including) a CTE component so that the feature and related space-consumption might be optional, but if you prefer CTE for your SQL, I am confident that compile-time switching won't be your favourite thing either. Further to this, the effort / pleasure ratio of adding it would probably prove larger than comfortable. As an aside, proper use of comment-blocks and inline commenting (which both your code and SQLite allows) can make anything as clear you'd like. Have a great day! Ryan On 2013/12/26 21:05, Simon Slavin wrote: > On 26 Dec 2013, at 6:57pm, big stone <stonebi...@gmail.com> wrote: > >> "sub-select" method : >> - allows you to do only a part of what you can do in CTE, >> - becomes more and more difficult to read as you add tables and >> treatment in your sql. >> >> With CTE in SQLite, it would be possible to: >> - decompose your SQL treatment in clean intermediate steps, >> - make your global algorithm "visible", >> - do easily things currently impossible in 1 query with sub-selects. > It looks like you want VIEWs rather than sub-selects. VIEWs enable all the > things you listed above. > > <http://www.sqlite.org/lang_createview.html> > > They're a way of saving a SELECT command so it can be used as if the results > are a table. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
