Hi Pete, Hannes is correct. The query you posted here overloads the limit and the order by clauses to realise a topn operation. Although many SQL vendors decided to implement such extensions, they are not compliant to the standards, for which both limit and order by are defined as affecting the *presentation* of results and not necessarily their computation. Conceptually, you always have to think that they compute the whole result, and then present a (sorted) portion to the user, regardless of how the query can then be optimised. That's why they are not defined for subqueries. A way to obtain what you want is: with sub as (select salesman_id, row_number() over(order by order_count desc) as r from order_counts) select salesman_id from sub where r <= 5 Cheres, Roberto On 4 Oct 2013 17:08, "Pete Hollobon" wrote:

Hi Hannes,

That's not always the case; how about something like getting the names of all order items sold by the top 5 salesmen by order count, using a query like this:

select item_name from orders where salesman_id in ( select salesman_id from order_counts order by order_count desc limit 5)

Or do you mean that it's a non-standard SQL extension to allow order by / limit in subqueries?

thanks, Pete.

On 4 October 2013 15:33, Hannes Mühleisen wrote:

...

Hello Hadley and list,

ORDER BY (and LIMIT/OFFSET for that matter) only control the overall shape of the query result set. Hence, they are indeed not supported within subqueries in MonetDB.

Best,

Hannes

On 10/04/2013 04:22 PM, Hadley Wickham wrote:

...

Currently, monetdb fails on this query:

SELECT * FROM (SELECT * FROM "Batting" ORDER BY "playerID")

with

syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from (select *

(obviously that query isn't very useful, but it makes it harder to automatically generate sql)

In case it matters, that query (with minor escaping variations) works in sqlite, postgres and mysql.

Hadley

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

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

Thank you Roberto, I hadn't realised that was the case, I am used to PostgreSQL's behaviour. Thanks for the alternative. On 5 October 2013 13:34, Roberto Cornacchia wrote:

.. and then of course w=you can use that within your outer query, joining with orders.

On Sat, Oct 5, 2013 at 2:32 PM, Roberto Cornacchia < roberto.cornacchia@gmail.com> wrote:

...

Hi Pete, Hannes is correct. The query you posted here overloads the limit and the order by clauses to realise a topn operation. Although many SQL vendors decided to implement such extensions, they are not compliant to the standards, for which both limit and order by are defined as affecting the *presentation* of results and not necessarily their computation. Conceptually, you always have to think that they compute the whole result, and then present a (sorted) portion to the user, regardless of how the query can then be optimised. That's why they are not defined for subqueries.

A way to obtain what you want is: with sub as (select salesman_id, row_number() over(order by order_count desc) as r from order_counts) select salesman_id from sub where r <= 5

Cheres, Roberto

On 4 Oct 2013 17:08, "Pete Hollobon" wrote:

...

Hi Hannes,

That's not always the case; how about something like getting the names of all order items sold by the top 5 salesmen by order count, using a query like this:

select item_name from orders where salesman_id in ( select salesman_id from order_counts order by order_count desc limit 5)

Or do you mean that it's a non-standard SQL extension to allow order by / limit in subqueries?

thanks, Pete.

On 4 October 2013 15:33, Hannes Mühleisen wrote:

...

Hello Hadley and list,

ORDER BY (and LIMIT/OFFSET for that matter) only control the overall shape of the query result set. Hence, they are indeed not supported within subqueries in MonetDB.

Best,

Hannes

On 10/04/2013 04:22 PM, Hadley Wickham wrote:

...

Currently, monetdb fails on this query:

SELECT * FROM (SELECT * FROM "Batting" ORDER BY "playerID")

with

syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from (select *

(obviously that query isn't very useful, but it makes it harder to automatically generate sql)

In case it matters, that query (with minor escaping variations) works in sqlite, postgres and mysql.

Hadley

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

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

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

On 5 Oct 2013 15:24, "Hadley Wickham" wrote:

Hi Roberto,

A couple of follow up questions:

1) is row_number() documented somewhere? What other window functions does

monetdb implement?

If I'm not mistaken, the online documentation doesn't actually include these functions yet. MonetDB supports the standard syntax, that's easy to find, just google for it.

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.

3) where is a good place to read the ANSI SQL standards?

Hadley

On Saturday, October 5, 2013, Roberto Cornacchia wrote:

...

Hi Pete, Hannes is correct. The query you posted here overloads the limit and the

order by clauses to realise a topn operation.

...

Although many SQL vendors decided to implement such extensions, they are not compliant to the standards, for which both limit and order by are defined as affecting the *presentation* of results and not necessarily

...

A way to obtain what you want is:

with sub as (select salesman_id, row_number() over(order by order_count

desc) as r from order_counts)

...

select salesman_id from sub where r <= 5

Cheres, Roberto

On 4 Oct 2013 17:08, "Pete Hollobon" wrote:

...

Hi Hannes,

That's not always the case; how about something like getting the names

of all order items sold by the top 5 salesmen by order count, using a query

Easiest is to paste your exact question into the google box ;) their computation. Conceptually, you always have to think that they compute the whole result, and then present a (sorted) portion to the user, regardless of how the query can then be optimised. That's why they are not defined for subqueries. like this:

...

...

select item_name from orders where salesman_id in ( select salesman_id from order_counts order by order_count desc limit 5)

Or do you mean that it's a non-standard SQL extension to allow order by

/ limit in subqueries?

...

thanks, Pete.

On 4 October 2013 15:33, Hannes Mühleisen

wrote:

...

...

Hello Hadley and list,

ORDER BY (and LIMIT/OFFSET for that matter) only control the overall

shape of the query result set. Hence, they are indeed not supported within subqueries in MonetDB.

...

Best,

Hannes

On 10/04/2013 04:22 PM, Hadley Wickham wrote:

...

Currently, monetdb fails on this query:

SELECT * FROM (SELECT * FROM "Batting" ORDER BY "playerID")

with

syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from (select *

(obviously that query isn't very useful, but it makes it harder to automatically generate sql)

In case it matters, that query (with minor escaping variations) works in sqlite, postgres and mysql.

Hadley

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

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

-- Chief Scientist, RStudio http://had.co.nz/

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

On Mon, Oct 7, 2013 at 3:13 PM, Hadley Wickham wrote:

...

...

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. However, I was incorrect about the format. The default format (\f sql) will give you the schema without datatypes. To get datatypes as well you will need to set "\f raw"

...

...

3) where is a good place to read the ANSI SQL standards?

Easiest is to paste your exact question into the google box ;)

I have done that. But e.g. "ansi sql subquery order by" does not return any pointers to the spec.

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

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

Hi Hadley, On 07.10.2013, at 15:49, Hadley Wickham wrote:

...

...

...

...

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

On Mon, Oct 7, 2013 at 4:14 PM, Stefan Manegold wrote:

...

...

...

...

...

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.

...

...

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

I agree - bug. Also because what I said about the order by / limit being linked to the schema of the prepare table turns out not to be true: sql>prepare select * from tables order by digits limit 2; SELECT: identifier 'digits' unknown So my guess is: order by / limit do not work. The silent fail produces an empty table for prepare. I actually think the syntax prepare "select * from tables order by tables.idlimit 99;" should not be allowed in the parser. Again, order by / limit are semantically about the presentation of the final result set, so in my view they don't makes sense within a prepare statement. On Mon, Oct 7, 2013 at 4:49 PM, Stefan Manegold wrote:

...

...

...

...

...

> 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

...

...

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/