Re: ORDER BY in sub-query

Filed as Bug http://bugs.monetdb.org/show_bug.cgi?id=3382 On 10/07/2013 04:55 PM, Roberto Cornacchia wrote:

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.id http://tables.id limit 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 mailto:Stefan.Manegold@cwi.nl> 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 http://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 http://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 http://iso.org . > > > You can find reliable information on this matter from other sources, > > > included www.w3schools.com http://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/ http://www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 tel:%2B31%20%280%2920%20592-4212 | 1098 XG Amsterdam (NL) |

_______________________________________________ users-list mailing list users-list@monetdb.org mailto: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