Bugs item #1892413, was opened at 2008-02-13 00:04 Message generated for change (Tracker Item Submitted) made by Item Submitter 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: 5 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 ---------------------------------------------------------------------- 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
