On 13-01-2011 08:47:53 -0500, Brett Spurrier wrote:

And the following query works just fine: SELECT fid FROM signatures ti ORDER BY ti.D0 ASC LIMIT 1 OFFSET 0;

However when use this query within the WHERE clause of another query, it fails: SELECT fid, title FROM signatures WHERE fid = ( SELECT fid FROM signatures ti ORDER BY ti.D0 ASC LIMIT 1 OFFSET 0 );

With the error: "syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select fid, title from signatures where fid = ( select  fid from signatures ti order"

Can anyone tell why Monet can't process this, but MySQL does just fine? MS SQL works too as long as I use TOP instead of LIMIT, and keep the offset at 0 (which I would like to eventually change).

Cosmetical functions such as limit, offset and order by are considered useless by the SQL standard, and hence not supported by MonetDB. In your case it seems not necessary at all either: SELECT fid, title FROM signatures ORDER BY D0 ASC LIMIT 1 OFFSET 0;

Dear all, IMHO, ORDER BY, LIMIT & OFFSET are useful in SQL. However, they are not part of relational algebra, and hence have so semantical (from a relational algebra pint of view) meaning, but (indeed) purely "cosmetic" (as in result rendering) functionality in SQL. Given that, it does not make much sense for a SQL implementation that (tries to) closely adhere to relational algebra semantics (and the SQL standard) to support ORDER BY, LIMIT & OFFSET in subqueries. Hence, MonetDB support them only in the top-level SQL query block. ... just the way I like to see and phrase this ... Stefan On Fri, Jan 14, 2011 at 05:43:52PM +0100, Lefteris wrote:

I would not call limit offset and group by useless and for sure not according to sql standard. Moreover, I am sure they are supported by MonetDB. Unless your answer is sarcastic, which I don't understand it, and why it should be.

On Fri, Jan 14, 2011 at 9:02 AM, Fabian Groffen wrote:

...

On 13-01-2011 08:47:53 -0500, Brett Spurrier wrote:

...

And the following query works just fine: SELECT fid FROM signatures ti ORDER BY ti.D0 ASC LIMIT 1 OFFSET 0;

However when use this query within the WHERE clause of another query, it fails: SELECT fid, title FROM signatures WHERE fid = ( SELECT fid FROM signatures ti ORDER BY ti.D0 ASC LIMIT 1 OFFSET 0 );

With the error: "syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select fid, title from signatures where fid = ( select  fid from signatures ti order"

Can anyone tell why Monet can't process this, but MySQL does just fine? MS SQL works too as long as I use TOP instead of LIMIT, and keep the offset at 0 (which I would like to eventually change).

Cosmetical functions such as limit, offset and order by are considered useless by the SQL standard, and hence not supported by MonetDB.  In your case it seems not necessary at all either:

SELECT fid, title        FROM signatures        ORDER BY D0 ASC        LIMIT 1 OFFSET 0;

------------------------------------------------------------------------------ Protect Your Site and Customers from Malware Attacks Learn about various malware tactics and how to avoid them. Understand malware threats, the impact they can have on your business, and how you can protect your company and customers by using code signing. http://p.sf.net/sfu/oracle-sfdevnl _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ Protect Your Site and Customers from Malware Attacks Learn about various malware tactics and how to avoid them. Understand malware threats, the impact they can have on your business, and how you can protect your company and customers by using code signing. http://p.sf.net/sfu/oracle-sfdevnl _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

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