> > >> >> 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)
>
>
>
> Unfortunately this won't work either, as what Hadley want is to be free to
> use limit/order by in his query.
>
> But if you do:
> sql>prepare select * from tables order by tables.id limit 2;
>
> Then the order by and limit will be assigned to the outer "prepare query",
> the one that returns the (type,digits,scale,schema,table,column) schema.

Good point --- not sure, though, whether that's a feature or a bug.

Either way, I am tempted to declare the actual behavior that I observe a bug:

sql>prepare select * from tables order by tables.id limit 99;
execute prepared statement using: EXEC 6(...)
+------+--------+-------+--------+-------+--------+

| type | digits | scale | schema | table | column |

+======+========+=======+========+=======+========+
+------+--------+-------+--------+-------+--------+
0 tuples (2.943ms)


Stefan

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

--

| 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