Bugs item #1892413, was opened at 2008-02-13 05:04 Message generated for change (Comment added) made by nielsnes You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1892413&group_id=56967
Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: SQL/Core Group: SQL 2.22 Status: Open Resolution: None Priority: 7 Private: No Submitted By: Michael Yang (msyang) Assigned to: Niels Nes (nielsnes) Summary: wrong or unexpected results in subquery Initial Comment: Using MonetDB/SQL v5 on Linux. I'm getting simply wrong results or a !MALException:algebra.thetajoin:GDKerror when I try to run a subquery involving a self-join. Here's a small example: sql>CREATE TABLE test(date int not null constraint pk_test primary key); sql>INSERT into test values (19251231); sql>INSERT into test values (19260102); sql>INSERT into test values (19260104); sql>select * from test; +----------+ | date | +==========+ | 19251231 | | 19260102 | | 19260104 | +----------+ Timer 0.930 msec 3 rows sql>SELECT date, (SELECT date from test where date > t1.date limit 1) as dtNext from test t1; +----------+---------+ | date |dtnext | +==========+=========+ | 19251231 |19260102 | +----------+---------+ OK - not what I expected - since limit 1 is in the subquery, not the outer query. I was expecting that the query would at least return the next date as dtnext for each date in 'test'. I realize you can't specify an order by clause in the inner subquery - you should be able to. now, without the limit clause: sql>SELECT date, (SELECT date from test where date > t1.date) as dtNext from test t1; +----------+---------+ | date |dtnext | +==========+=========+ | 19251231 |19260102 | | 19260102 |19260104 | +----------+---------+ Timer 1.673 msec 3 rows OK, that's a little better. So I guess, unlike MS SQL Server where you must explicitly specify TOP(1) in a subquery to limit the subquery to returning only 1 value, monetDB/SQL implicitly does this, and orders by the default primary key ordering. Fine. However, when I try running this on a table with about 32605 dates, I get a GDKerror [the attached file of dates is an example]. sql>DELETE FROM test; sql>COPY INTO test FROM '/home/michael/tmp/dt.txt' USING DELIMITERS '\t'; sql>select count(*) as cnt from test; +-------+ | cnt | +=======+ | 32605 | +-------+ Timer 0.807 msec 1 rows sql>select date, (select date from test where date > t1.date) as dtNext from test t1; !MALException:algebra.thetajoin:GDKerror Timer 1.142 msec 0 rows ---------------------------------------------------------------------- >Comment By: Niels Nes (nielsnes) Date: 2008-02-20 09:40 Message: Logged In: YES user_id=43556 Originator: NO This bug report is a combination of problems. 1 we don't support limit in sub queries. 2 sub queries should return zero or one result. This wasn't checked correctly at runtime. 3 these queries need an outer join as some sub query results maybe null 4 there is a problem with the theta join, it claims to much memory (which on memory tight systems may well fail) So far I fixed 2 and 3. 1 is at best a feature request, work around it will a aggregate (such as max and min) or use a row_number() over() solution. Fixing 4 is on the list (at least for this data it should behave much better), but the proper fix would be to use a windowing like solution which we still need to implement. For 2 and 3 a test was added (src/tests/BugTracker/Tests/Attic/bug_in_selection.SF-1892413) ---------------------------------------------------------------------- Comment By: Michael Yang (msyang) Date: 2008-02-13 05:12 Message: Logged In: YES user_id=1952137 Originator: YES furthermore, a simple self-join with GROUP BY fails as well sql>select t1.date, min(t2.date) as dtNext from test t1, test t2 where t2.date > t1.date group by t1.date; !MALException:algebra.thetajoin:GDKerror Timer 1.141 msec 0 rows ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1892413&group_id=56967 ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Monetdb-bugs mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
