The query you wrote above IS valid and does work in MonetDB, provided that Q is valid. But if you want to include order by and limit in Q, then Q isn't valid :-) Possible solutions: remove order by/limit from Q (the schema doesn't change), or use the where clause on Q, without making it a subquery.
So what you seem to be telling me, is that if I have an arbitrary query Q that works as a query, there is no way in monetdb to efficiently determine the types of its columns, without inspecting and potentially modifying Q?
SELECT * FROM (Q) WHERE 0=1; is not guaranteed to be an efficient way to do this in the first place, as this requires the query to be executed, which might in general be expensive, and only "happens to be" cheap in case a smart optimizer recognizes the FALSE predicate and thus recognizes the empty results.
I agree, but most rdbms do recognise that optimisation.
An alternative that merely checks the syntactic and semantic correctness of the query and statically (i.e., without accessing any data) derives the column types is to "PREPARE" the query as prepared statement. As side-effect, this reports (a.o.) the column types, e.g.,
Thanks, that's very useful (modulo the bugs below)
Actually I had assumed it is available online, but apparently if you need to order a copy at iso.org. You can find reliable information on this matter from other sources, included www.w3schools.com, http://en.wikipedia.org/wiki/Order_by (I know, wikipedia is not so official, but it does contain references and it is reviewed, so it usually is rather accurate). What you should not look at is vendor-specific documentation, as long as you want to know what the standard is.
This is frustrating, especially given that the majority of monetdb's sql documentation is just pointers to the standard.
It's unfortunately beyond our control that the SQL standard is not freely available.
Also, with research being the primary work to earn our living, we unfortunately have limited resources to spend on developing. maintaining and documenting MonetDB. While doing our best, we so far did not manage to copy the entire SQL standard (or subset that MonetDB supports) into our website/documentation (in fact, we'd have to carefully study the respective licensees, first), focusing our scarce resources on development, maintenance, and documenting the most prominent / important deviations from the standard. We are constantly working on acquiring more resources to (also) improve and extend our documentation.
I appreciate that good documentation is hard, and making MonetDB easy to use may not be your main priority. But if you do want to have an impact on the world, and you do want to people to use your work, then documentation is incredibly important. MonetDB is currently considerably behind its competitors in the quality of its documentation and it makes it difficult to try it out and see if the performance improvements are worth it for a given problem. To be more concrete, here are a few small changes that would make the documentation more useful: * On http://www.monetdb.org/Documentation, link the headings in the body of the page to the respective documentation pages. * In the auto-generated documentation, e.g. http://www.monetdb.org/Documentation/SQLreference, have fewer big pages rather than many small pages. The only way I use the documentation is to go to the print-friendly view and then use the in page search. * Convert the supported and unsupported features to a table, so when you've found a feature, you can easily tell whether it's supported or not. * If you wanted to spend a little more time, you could: * update the list of built-in functions * update the grammar for SELECT - it currently has [ FROM table_name ',' ... ], but I think that should be (at least) [ FROM table_reference ',' ...], although that doesn't capture the restriction that subqueries can't have ORDER BY/LIMIT statements. Hadley -- Chief Scientist, RStudio http://had.co.nz/