Can't use ORDER BY in subqueries

Kotschenreuther, Leo

27 Oct 2014 27 Oct '14

4:57 p.m.

Hi all, I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation: I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this: sql>select * from test join (select * from foo order by c limit 1); The result I receive is the following: syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order” On the other hand, I can run the subquery without the main query and I receive a valid result. Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries? Regards Leo

Attachments:

attachment.html (text/html — 2.3 KB)

Show replies by date

Martin Kersten

27 Oct 27 Oct

6:02 p.m.

Hi 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. See thread https://www.monetdb.org/pipermail/users-list/2013-October/006861.html regards, Martin On 27/10/14 17:57, Kotschenreuther, Leo wrote:

...

Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

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

Leo Kotschenreuther

5 Nov 5 Nov

3:49 p.m.

Hi, I understand why Monetdb does’t support ORDER BY in subqueries. An ORDER BY combined with a LIMIT does influence the size of the query result set. Is there a workaround to use such a query? Here’s my use case: I’m working with the spatial extension and have a table filled with polygons. Now I want to know the ids of all polygons that intersect the polygon with the biggest area. Is there a chance to answer this use case with only one query? Regards Leo

...

On 27 Oct 2014, at 19:02, Martin Kersten wrote:

Hi

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.

See thread https://www.monetdb.org/pipermail/users-list/2013-October/006861.html

regards, Martin

On 27/10/14 17:57, Kotschenreuther, Leo wrote:

...
Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

_______________________________________________ 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

Brandon Jackson

3:52 p.m.

This is off the cuff, but why not try some kind of TOP 1 query that would return the first result in your ordered query. That way you can quit using LIMIT within the subquery.

...

On Nov 5, 2014, at 9:49 AM, Leo Kotschenreuther wrote:

Hi,

I understand why Monetdb does’t support ORDER BY in subqueries.

An ORDER BY combined with a LIMIT does influence the size of the query result set. Is there a workaround to use such a query?

Here’s my use case: I’m working with the spatial extension and have a table filled with polygons. Now I want to know the ids of all polygons that intersect the polygon with the biggest area.

Is there a chance to answer this use case with only one query?

Regards Leo

...
On 27 Oct 2014, at 19:02, Martin Kersten wrote:

Hi

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.

See thread https://www.monetdb.org/pipermail/users-list/2013-October/006861.html

regards, Martin

On 27/10/14 17:57, Kotschenreuther, Leo wrote:

...
Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

_______________________________________________ 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

Martin Kersten

3:52 p.m.

Hi A possible workaround is to enclose the query in a table producing function. regards, Martin On 05/11/14 16:49, Leo Kotschenreuther wrote:

...

Hi,

I understand why Monetdb does’t support ORDER BY in subqueries.

An ORDER BY combined with a LIMIT does influence the size of the query result set. Is there a workaround to use such a query?

Here’s my use case: I’m working with the spatial extension and have a table filled with polygons. Now I want to know the ids of all polygons that intersect the polygon with the biggest area.

Is there a chance to answer this use case with only one query?

Regards Leo

...
On 27 Oct 2014, at 19:02, Martin Kersten wrote:

Hi

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.

See thread https://www.monetdb.org/pipermail/users-list/2013-October/006861.html

regards, Martin

On 27/10/14 17:57, Kotschenreuther, Leo wrote:

...
Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

_______________________________________________ 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

Martin Kersten

4:24 p.m.

Hi This recurring question has a simple solution in MonetDB for most of the cases considered. Simple rip out the subquery and encapsulate it in table producing function. The following example shows how: -- select * from tables where id in (select id from tables limit 1); create function limited() returns table (id integer) begin return select id from tables order by id desc limit 1; end; select * from tables where id in (select * from limited()); regards, Martin On 05/11/14 16:52, Martin Kersten wrote:

...

Hi

A possible workaround is to enclose the query in a table producing function.

regards, Martin

On 05/11/14 16:49, Leo Kotschenreuther wrote:

...
Hi,

I understand why Monetdb does’t support ORDER BY in subqueries.

An ORDER BY combined with a LIMIT does influence the size of the query result set. Is there a workaround to use such a query?

Here’s my use case: I’m working with the spatial extension and have a table filled with polygons. Now I want to know the ids of all polygons that intersect the polygon with the biggest area.

Is there a chance to answer this use case with only one query?

Regards Leo

...
On 27 Oct 2014, at 19:02, Martin Kersten wrote:

Hi

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.

See thread https://www.monetdb.org/pipermail/users-list/2013-October/006861.html

regards, Martin

On 27/10/14 17:57, Kotschenreuther, Leo wrote:

...
Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

_______________________________________________ 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

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

Roberto Cornacchia

5:42 p.m.

Martin, Your answer to this question is useful but triggers a feature request that I'm very interested in for long :) The major drawback of your workaround is that the name of the table to be limited is hard-coded inside the function. Which makes this solution impractical for real scenarios. What is much missed - and would make your solution very useful in practice - is to be able to pass table identifiers as function parameters. I could produce a long list of issues that would be solved by this ;) On 5 Nov 2014 17:24, "Martin Kersten" wrote:

...

Hi

This recurring question has a simple solution in MonetDB for most of the cases considered. Simple rip out the subquery and encapsulate it in table producing function. The following example shows how:

-- select * from tables where id in (select id from tables limit 1); create function limited() returns table (id integer) begin return select id from tables order by id desc limit 1; end; select * from tables where id in (select * from limited());

regards, Martin

On 05/11/14 16:52, Martin Kersten wrote:

...
Hi

A possible workaround is to enclose the query in a table producing function.

regards, Martin

On 05/11/14 16:49, Leo Kotschenreuther wrote:

...
Hi,

I understand why Monetdb does’t support ORDER BY in subqueries.

An ORDER BY combined with a LIMIT does influence the size of the query result set. Is there a workaround to use such a query?

Here’s my use case: I’m working with the spatial extension and have a table filled with polygons. Now I want to know the ids of all polygons that intersect the polygon with the biggest area.

Is there a chance to answer this use case with only one query?

Regards Leo

On 27 Oct 2014, at 19:02, Martin Kersten wrote:

...
Hi

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.

See thread https://www.monetdb.org/pipermail/users-list/2013-October/006861.html

regards, Martin

On 27/10/14 17:57, Kotschenreuther, Leo wrote:

...
Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

_______________________________________________ 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

_______________________________________________ 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

Stefan Manegold

5:48 p.m.

passing table identifiers IMHO clashes with SQL type system. passing table-typed arguments might work --- you still need one function per table signature, but no longer per table name; IMHO, there are plans to work on supporting something like that ... Stefan ----- Original Message -----

...

Martin,

Your answer to this question is useful but triggers a feature request that I'm very interested in for long :)

The major drawback of your workaround is that the name of the table to be limited is hard-coded inside the function. Which makes this solution impractical for real scenarios.

What is much missed - and would make your solution very useful in practice - is to be able to pass table identifiers as function parameters.

I could produce a long list of issues that would be solved by this ;) On 5 Nov 2014 17:24, "Martin Kersten" < Martin.Kersten@cwi.nl > wrote:

Hi

This recurring question has a simple solution in MonetDB for most of the cases considered. Simple rip out the subquery and encapsulate it in table producing function. The following example shows how:

-- select * from tables where id in (select id from tables limit 1); create function limited() returns table (id integer) begin return select id from tables order by id desc limit 1; end; select * from tables where id in (select * from limited());

regards, Martin

On 05/11/14 16:52, Martin Kersten wrote:

Hi

A possible workaround is to enclose the query in a table producing function.

regards, Martin

On 05/11/14 16:49, Leo Kotschenreuther wrote:

Hi,

I understand why Monetdb does’t support ORDER BY in subqueries.

An ORDER BY combined with a LIMIT does influence the size of the query result set. Is there a workaround to use such a query?

Here’s my use case: I’m working with the spatial extension and have a table filled with polygons. Now I want to know the ids of all polygons that intersect the polygon with the biggest area.

Is there a chance to answer this use case with only one query?

Regards Leo

On 27 Oct 2014, at 19:02, Martin Kersten < Martin.Kersten@cwi.nl > wrote:

Hi

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.

See thread https://www.monetdb.org/ pipermail/users-list/2013- October/006861.html

regards, Martin

On 27/10/14 17:57, Kotschenreuther, Leo wrote:

Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

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

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

Roberto Cornacchia

5:55 p.m.

That would already be useful! Thanks. On 5 Nov 2014 18:49, "Stefan Manegold" wrote:

...

passing table identifiers IMHO clashes with SQL type system.

passing table-typed arguments might work --- you still need one function per table signature, but no longer per table name; IMHO, there are plans to work on supporting something like that ...

Stefan

----- Original Message -----

...
Martin,

Your answer to this question is useful but triggers a feature request

...
I'm very interested in for long :)

The major drawback of your workaround is that the name of the table to be limited is hard-coded inside the function. Which makes this solution impractical for real scenarios.

What is much missed - and would make your solution very useful in

that practice -

...
is to be able to pass table identifiers as function parameters.

I could produce a long list of issues that would be solved by this ;) On 5 Nov 2014 17:24, "Martin Kersten" < Martin.Kersten@cwi.nl > wrote:

Hi

This recurring question has a simple solution in MonetDB for most of the cases considered. Simple rip out the subquery and encapsulate it in table producing function. The following example shows how:

-- select * from tables where id in (select id from tables limit 1); create function limited() returns table (id integer) begin return select id from tables order by id desc limit 1; end; select * from tables where id in (select * from limited());

regards, Martin

On 05/11/14 16:52, Martin Kersten wrote:

Hi

A possible workaround is to enclose the query in a table producing function.

regards, Martin

On 05/11/14 16:49, Leo Kotschenreuther wrote:

Hi,

I understand why Monetdb does’t support ORDER BY in subqueries.

An ORDER BY combined with a LIMIT does influence the size of the query result set. Is there a workaround to use such a query?

Here’s my use case: I’m working with the spatial extension and have a table filled with polygons. Now I want to know the ids of all polygons that intersect the polygon with the biggest area.

Is there a chance to answer this use case with only one query?

Regards Leo

On 27 Oct 2014, at 19:02, Martin Kersten < Martin.Kersten@cwi.nl > wrote:

Hi

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.

See thread https://www.monetdb.org/ pipermail/users-list/2013- October/006861.html

regards, Martin

On 27/10/14 17:57, Kotschenreuther, Leo wrote:

Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

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

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

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

Budulinku Dejmihrasku

6 Nov 6 Nov

8:54 a.m.

a dynamic sql execution would be nice solution to this - as for example the EXECUTE IMMEDIATE 'astringwithsqlstatement' [INTO ...] [USING ...] statement in Oracle milan On 5. 11. 2014 18:48, Stefan Manegold wrote:

...

passing table identifiers IMHO clashes with SQL type system.

passing table-typed arguments might work --- you still need one function per table signature, but no longer per table name; IMHO, there are plans to work on supporting something like that ...

Stefan

----- Original Message -----

...
Martin,

Your answer to this question is useful but triggers a feature request that I'm very interested in for long :)

The major drawback of your workaround is that the name of the table to be limited is hard-coded inside the function. Which makes this solution impractical for real scenarios.

What is much missed - and would make your solution very useful in practice - is to be able to pass table identifiers as function parameters.

I could produce a long list of issues that would be solved by this ;) On 5 Nov 2014 17:24, "Martin Kersten" < Martin.Kersten@cwi.nl > wrote:

Hi

This recurring question has a simple solution in MonetDB for most of the cases considered. Simple rip out the subquery and encapsulate it in table producing function. The following example shows how:

-- select * from tables where id in (select id from tables limit 1); create function limited() returns table (id integer) begin return select id from tables order by id desc limit 1; end; select * from tables where id in (select * from limited());

regards, Martin

On 05/11/14 16:52, Martin Kersten wrote:

Hi

A possible workaround is to enclose the query in a table producing function.

regards, Martin

On 05/11/14 16:49, Leo Kotschenreuther wrote:

Hi,

I understand why Monetdb does’t support ORDER BY in subqueries.

An ORDER BY combined with a LIMIT does influence the size of the query result set. Is there a workaround to use such a query?

Here’s my use case: I’m working with the spatial extension and have a table filled with polygons. Now I want to know the ids of all polygons that intersect the polygon with the biggest area.

Is there a chance to answer this use case with only one query?

Regards Leo

On 27 Oct 2014, at 19:02, Martin Kersten < Martin.Kersten@cwi.nl > wrote:

Hi

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.

See thread https://www.monetdb.org/ pipermail/users-list/2013- October/006861.html

regards, Martin

On 27/10/14 17:57, Kotschenreuther, Leo wrote:

Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

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

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

Stefan Manegold

5 Nov 5 Nov

4:48 p.m.

Hi Leo, could you give an SQL example of the very query you'd like to run but that MonetDB "rejects" due to order by and/or limit in a sub query? Thanks! Stefan ----- Original Message -----

...

Hi,

I understand why Monetdb does’t support ORDER BY in subqueries.

An ORDER BY combined with a LIMIT does influence the size of the query result set. Is there a workaround to use such a query?

Here’s my use case: I’m working with the spatial extension and have a table filled with polygons. Now I want to know the ids of all polygons that intersect the polygon with the biggest area.

Is there a chance to answer this use case with only one query?

Regards Leo

...
On 27 Oct 2014, at 19:02, Martin Kersten wrote:

Hi

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.

See thread https://www.monetdb.org/pipermail/users-list/2013-October/006861.html

regards, Martin

On 27/10/14 17:57, Kotschenreuther, Leo wrote:

...
Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

_______________________________________________ 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

Leo Kotschenreuther

5:01 p.m.

Hi, I have two tables: CREATE TABLE lines (ID INT, size INT, line GEOMETRY); CREATE TABLE polygons (ID INT, size INT, polygon GEOMETRY); The query I want to call is the following: SELECT COUNT(*) FROM POLYGONS one JOIN (SELECT LINE FROM LINES ORDER BY Length(line) DESC LIMIT 1) two ON "Intersect"(one.polygon, two.line) = TRUE; I tried out to create a function that replaces the use of the subquery: create function line_max() returns table (id integer) begin return select id from lines order by Length(line) desc limit 1; end; select count(*) From polygons one join lines two ON "Intersect"(one.polygon, two.line) = TRUE where two.id in (select id from line_max()); The query started without errors but it stops with the following error: HEAPalloc: Insufficient space for HEAP of 7200000000 bytes. Thanks Leo

...

On 05 Nov 2014, at 17:48, Stefan Manegold wrote:

Hi Leo,

could you give an SQL example of the very query you'd like to run but that MonetDB "rejects" due to order by and/or limit in a sub query?

Thanks!

Stefan

----- Original Message -----

...
Hi,

I understand why Monetdb does’t support ORDER BY in subqueries.

An ORDER BY combined with a LIMIT does influence the size of the query result set. Is there a workaround to use such a query?

Here’s my use case: I’m working with the spatial extension and have a table filled with polygons. Now I want to know the ids of all polygons that intersect the polygon with the biggest area.

Is there a chance to answer this use case with only one query?

Regards Leo

...
On 27 Oct 2014, at 19:02, Martin Kersten wrote:

Hi

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.

See thread https://www.monetdb.org/pipermail/users-list/2013-October/006861.html

regards, Martin

On 27/10/14 17:57, Kotschenreuther, Leo wrote:

...
Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

_______________________________________________ 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

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

Martin Kersten

5:09 p.m.

the pitfall of deciding on how queries executed. given the message it looks like a cartesian product. ;)

...

On 5 nov. 2014, at 18:01, Leo Kotschenreuther wrote:

Hi,

I have two tables: CREATE TABLE lines (ID INT, size INT, line GEOMETRY); CREATE TABLE polygons (ID INT, size INT, polygon GEOMETRY);

The query I want to call is the following: SELECT COUNT(*) FROM POLYGONS one JOIN (SELECT LINE FROM LINES ORDER BY Length(line) DESC LIMIT 1) two ON "Intersect"(one.polygon, two.line) = TRUE;

I tried out to create a function that replaces the use of the subquery: create function line_max() returns table (id integer) begin return select id from lines order by Length(line) desc limit 1; end;

select count(*) From polygons one join lines two ON "Intersect"(one.polygon, two.line) = TRUE where two.id in (select id from line_max());

The query started without errors but it stops with the following error: HEAPalloc: Insufficient space for HEAP of 7200000000 bytes.

Thanks Leo

...
On 05 Nov 2014, at 17:48, Stefan Manegold wrote:

Hi Leo,

could you give an SQL example of the very query you'd like to run but that MonetDB "rejects" due to order by and/or limit in a sub query?

Thanks!

Stefan

----- Original Message -----

...
Hi,

I understand why Monetdb does’t support ORDER BY in subqueries.

An ORDER BY combined with a LIMIT does influence the size of the query result set. Is there a workaround to use such a query?

Here’s my use case: I’m working with the spatial extension and have a table filled with polygons. Now I want to know the ids of all polygons that intersect the polygon with the biggest area.

Is there a chance to answer this use case with only one query?

Regards Leo

...
On 27 Oct 2014, at 19:02, Martin Kersten wrote:

Hi

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.

See thread https://www.monetdb.org/pipermail/users-list/2013-October/006861.html

regards, Martin

...
On 27/10/14 17:57, Kotschenreuther, Leo wrote: Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

_______________________________________________ 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

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

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

Stefan Manegold

5:31 p.m.

in case the ("our") optimizer indeed fails to push-down the selection, maybe giving a hint like this select count(*) From polygons one join (select line from lines where two.id in (select id from line_max())) two ON "Intersect"(one.polygon,two.line) = TRUE; or even with two as (select line from lines where two.id in (select id from line_max())) select count(*) From polygons one join two ON "Intersect"(one.polygon,two.line) = TRUE; helps? the (intermediate) result might still be as large as polygons in case the longest lines intersects with all polygons ... Stefan ----- Original Message -----

...

the pitfall of deciding on how queries executed. given the message it looks like a cartesian product. ;)

On 5 nov. 2014, at 18:01, Leo Kotschenreuther < leo.kotschenreuther@student.hpi.de > wrote:

Hi,

I have two tables: CREATE TABLE lines (ID INT, size INT, line GEOMETRY); CREATE TABLE polygons (ID INT, size INT, polygon GEOMETRY);

The query I want to call is the following: SELECT COUNT(*) FROM POLYGONS one JOIN (SELECT LINE FROM LINES ORDER BY Length(line) DESC LIMIT 1) two ON "Intersect"(one.polygon, two.line) = TRUE;

I tried out to create a function that replaces the use of the subquery: create function line_max() returns table (id integer) begin return select id from lines order by Length(line) desc limit 1; end;

select count(*) From polygons one join lines two ON "Intersect"(one.polygon, two.line) = TRUE where two.id in (select id from line_max());

The query started without errors but it stops with the following error: HEAPalloc: Insufficient space for HEAP of 7200000000 bytes.

Thanks Leo

On 05 Nov 2014, at 17:48, Stefan Manegold < Stefan.Manegold@cwi.nl > wrote:

Hi Leo,

could you give an SQL example of the very query you'd like to run but that MonetDB "rejects" due to order by and/or limit in a sub query?

Thanks!

Stefan

----- Original Message -----

Hi,

I understand why Monetdb does’t support ORDER BY in subqueries.

An ORDER BY combined with a LIMIT does influence the size of the query result set. Is there a workaround to use such a query?

Here’s my use case: I’m working with the spatial extension and have a table filled with polygons. Now I want to know the ids of all polygons that intersect the polygon with the biggest area.

Is there a chance to answer this use case with only one query?

Regards Leo

On 27 Oct 2014, at 19:02, Martin Kersten < Martin.Kersten@cwi.nl > wrote:

Hi

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.

See thread https://www.monetdb.org/pipermail/users-list/2013-October/006861.html

regards, Martin

On 27/10/14 17:57, Kotschenreuther, Leo wrote:

Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

_______________________________________________ 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

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

_______________________________________________ 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

Leo Kotschenreuther

5:44 p.m.

Thanks Stefan, the solution you proposed works out (the one without the function). I used the following to get what I want: SELECT COUNT(*) FROM POLYGONS one JOIN (SELECT LINE FROM LINES where length(line) = (select max(length(line)) from lines) ) two ON "Intersect"(one.polygon, two.line) = TRUE; Leo

...

On 05 Nov 2014, at 18:31, Stefan Manegold wrote:

in case the ("our") optimizer indeed fails to push-down the selection, maybe giving a hint like this

select count(*) From polygons one join (select line from lines where two.id in (select id from line_max())) two ON "Intersect"(one.polygon,two.line) = TRUE;

or even

with two as (select line from lines where two.id in (select id from line_max())) select count(*) From polygons one join two ON "Intersect"(one.polygon,two.line) = TRUE;

helps?

the (intermediate) result might still be as large as polygons in case the longest lines intersects with all polygons ...

Stefan

----- Original Message -----

...
the pitfall of deciding on how queries executed. given the message it looks like a cartesian product. ;)

On 5 nov. 2014, at 18:01, Leo Kotschenreuther < leo.kotschenreuther@student.hpi.de > wrote:

Hi,

I have two tables: CREATE TABLE lines (ID INT, size INT, line GEOMETRY); CREATE TABLE polygons (ID INT, size INT, polygon GEOMETRY);

The query I want to call is the following: SELECT COUNT(*) FROM POLYGONS one JOIN (SELECT LINE FROM LINES ORDER BY Length(line) DESC LIMIT 1) two ON "Intersect"(one.polygon, two.line) = TRUE;

I tried out to create a function that replaces the use of the subquery: create function line_max() returns table (id integer) begin return select id from lines order by Length(line) desc limit 1; end;

select count(*) From polygons one join lines two ON "Intersect"(one.polygon, two.line) = TRUE where two.id in (select id from line_max());

The query started without errors but it stops with the following error: HEAPalloc: Insufficient space for HEAP of 7200000000 bytes.

Thanks Leo

On 05 Nov 2014, at 17:48, Stefan Manegold < Stefan.Manegold@cwi.nl > wrote:

Hi Leo,

could you give an SQL example of the very query you'd like to run but that MonetDB "rejects" due to order by and/or limit in a sub query?

Thanks!

Stefan

----- Original Message -----

Hi,

I understand why Monetdb does’t support ORDER BY in subqueries.

An ORDER BY combined with a LIMIT does influence the size of the query result set. Is there a workaround to use such a query?

Here’s my use case: I’m working with the spatial extension and have a table filled with polygons. Now I want to know the ids of all polygons that intersect the polygon with the biggest area.

Is there a chance to answer this use case with only one query?

Regards Leo

On 27 Oct 2014, at 19:02, Martin Kersten < Martin.Kersten@cwi.nl > wrote:

Hi

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.

See thread https://www.monetdb.org/pipermail/users-list/2013-October/006861.html

regards, Martin

On 27/10/14 17:57, Kotschenreuther, Leo wrote:

Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

_______________________________________________ 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

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

_______________________________________________ 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

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

Stefan Manegold

5:48 p.m.

great ;-) Stefan ----- Original Message -----

...

Thanks Stefan,

the solution you proposed works out (the one without the function). I used the following to get what I want: SELECT COUNT(*) FROM POLYGONS one JOIN (SELECT LINE FROM LINES where length(line) = (select max(length(line)) from lines) ) two ON "Intersect"(one.polygon, two.line) = TRUE;

Leo

...
On 05 Nov 2014, at 18:31, Stefan Manegold wrote:

in case the ("our") optimizer indeed fails to push-down the selection, maybe giving a hint like this

select count(*) From polygons one join (select line from lines where two.id in (select id from line_max())) two ON "Intersect"(one.polygon,two.line) = TRUE;

or even

with two as (select line from lines where two.id in (select id from line_max())) select count(*) From polygons one join two ON "Intersect"(one.polygon,two.line) = TRUE;

helps?

the (intermediate) result might still be as large as polygons in case the longest lines intersects with all polygons ...

Stefan

----- Original Message -----

...
the pitfall of deciding on how queries executed. given the message it looks like a cartesian product. ;)

On 5 nov. 2014, at 18:01, Leo Kotschenreuther < leo.kotschenreuther@student.hpi.de > wrote:

Hi,

I have two tables: CREATE TABLE lines (ID INT, size INT, line GEOMETRY); CREATE TABLE polygons (ID INT, size INT, polygon GEOMETRY);

The query I want to call is the following: SELECT COUNT(*) FROM POLYGONS one JOIN (SELECT LINE FROM LINES ORDER BY Length(line) DESC LIMIT 1) two ON "Intersect"(one.polygon, two.line) = TRUE;

I tried out to create a function that replaces the use of the subquery: create function line_max() returns table (id integer) begin return select id from lines order by Length(line) desc limit 1; end;

select count(*) From polygons one join lines two ON "Intersect"(one.polygon, two.line) = TRUE where two.id in (select id from line_max());

The query started without errors but it stops with the following error: HEAPalloc: Insufficient space for HEAP of 7200000000 bytes.

Thanks Leo

On 05 Nov 2014, at 17:48, Stefan Manegold < Stefan.Manegold@cwi.nl > wrote:

Hi Leo,

could you give an SQL example of the very query you'd like to run but that MonetDB "rejects" due to order by and/or limit in a sub query?

Thanks!

Stefan

----- Original Message -----

Hi,

I understand why Monetdb does’t support ORDER BY in subqueries.

An ORDER BY combined with a LIMIT does influence the size of the query result set. Is there a workaround to use such a query?

Here’s my use case: I’m working with the spatial extension and have a table filled with polygons. Now I want to know the ids of all polygons that intersect the polygon with the biggest area.

Is there a chance to answer this use case with only one query?

Regards Leo

On 27 Oct 2014, at 19:02, Martin Kersten < Martin.Kersten@cwi.nl > wrote:

Hi

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.

See thread https://www.monetdb.org/pipermail/users-list/2013-October/006861.html

regards, Martin

On 27/10/14 17:57, Kotschenreuther, Leo wrote:

Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

_______________________________________________ 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

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

_______________________________________________ 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

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

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

Stefan Manegold

5:26 p.m.

Hi, in case there is a single longest line in LINES, how about SELECT COUNT(*) FROM POLYGONS one JOIN (SELECT LINE FROM LINES where length(line) = (select max(length(line)) from lines) ) two ON "Intersect"(one.polygon, two.line) = TRUE; Or, if there is no single longest line in LINES, but ID is unique in lines (i.e., also LIMIT 1 would choose among all equally long longest lines one arbitrary, implementation dependent, possibly non-deterministic line) SELECT COUNT(*) FROM POLYGONS one JOIN (SELECT LINE FROM LINES where ID = (select max(ID) from LINES where length(line) = (select max(length(line)) from lines) ) ) two ON "Intersect"(one.polygon, two.line) = TRUE; ? Stefan ----- Original Message -----

...

Hi,

I have two tables: CREATE TABLE lines (ID INT, size INT, line GEOMETRY); CREATE TABLE polygons (ID INT, size INT, polygon GEOMETRY);

The query I want to call is the following: SELECT COUNT(*) FROM POLYGONS one JOIN (SELECT LINE FROM LINES ORDER BY Length(line) DESC LIMIT 1) two ON "Intersect"(one.polygon, two.line) = TRUE;

I tried out to create a function that replaces the use of the subquery: create function line_max() returns table (id integer) begin return select id from lines order by Length(line) desc limit 1; end;

select count(*) From polygons one join lines two ON "Intersect"(one.polygon, two.line) = TRUE where two.id in (select id from line_max());

The query started without errors but it stops with the following error: HEAPalloc: Insufficient space for HEAP of 7200000000 bytes.

Thanks Leo

On 05 Nov 2014, at 17:48, Stefan Manegold < Stefan.Manegold@cwi.nl > wrote:

Hi Leo,

could you give an SQL example of the very query you'd like to run but that MonetDB "rejects" due to order by and/or limit in a sub query?

Thanks!

Stefan

----- Original Message -----

Hi,

I understand why Monetdb does’t support ORDER BY in subqueries.

An ORDER BY combined with a LIMIT does influence the size of the query result set. Is there a workaround to use such a query?

Here’s my use case: I’m working with the spatial extension and have a table filled with polygons. Now I want to know the ids of all polygons that intersect the polygon with the biggest area.

Is there a chance to answer this use case with only one query?

Regards Leo

On 27 Oct 2014, at 19:02, Martin Kersten < Martin.Kersten@cwi.nl > wrote:

Hi

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.

See thread https://www.monetdb.org/pipermail/users-list/2013-October/006861.html

regards, Martin

On 27/10/14 17:57, Kotschenreuther, Leo wrote:

Hi all,

I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation:

I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this:

*sql>select * from test join (select * from foo order by c limit 1);* * * The result I receive is the following:

*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order**”* * * On the other hand, I can run the subquery without the main query and I receive a valid result.

Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries?

Regards Leo

_______________________________________________ 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

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

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

3704

Age (days ago)

3714

Last active (days ago)

List overview

Download

16 comments

7 participants

participants (7)

Brandon Jackson
Budulinku Dejmihrasku
Kotschenreuther, Leo
Leo Kotschenreuther
Martin Kersten
Roberto Cornacchia
Stefan Manegold