Hi Hadley,
On 07.10.2013, at 15:49, Hadley Wickham
2) in other SQL databases you can often efficiently find out the column types of a query Q by running SELECT * FROM (Q) WHERE 0=1 is there a similar idiom for monetdb?
This is a valid empty query that will show the header (when the standard result format is selected), with data types, and no tuple. So yes, I guess this would give you what you want.
Except it doesn't, because monetdb doesn't supported limit/order by in subqueries...
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?
There is a way of finding this out the response to the prepare command will contain the schema of the response without actually querying anything. If you like, I can give you an example how to do this from R.
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. Sorry for this frustration, of course this documentation could be more extensive.
Best, Hannes