> >> >> 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?

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.

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.,


sql>prepare select * from tables;
execute prepared statement using: EXEC 1(...)
+----------+--------+-------+--------+--------+---------------+
| type     | digits | scale | schema | table  | column        |
+==========+========+=======+========+========+===============+
| int      |     32 |     0 |        | tables | id            |
| varchar  |   1024 |     0 |        | tables | name          |
| int      |     32 |     0 |        | tables | schema_id     |
| varchar  |   2048 |     0 |        | tables | query         |
| smallint |     16 |     0 |        | tables | type          |
| boolean  |      1 |     0 |        | tables | system        |
| smallint |     16 |     0 |        | tables | commit_action |
| boolean  |      1 |     0 |        | tables | readonly      |
| tinyint  |      8 |     0 |        | tables | temporary     |
+----------+--------+-------+--------+--------+---------------+
9 tuples (2.186ms)

> > 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.

Best,
Stefan

> Hadley
>
>
> --
> Chief Scientist, RStudio
> http://had.co.nz/
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
>

--
| Stefan.Manegold@CWI.nl | DB Architectures   (DA) |
| www.CWI.nl/~manegold/  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |

_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list