Bugs item #1898089, was opened at 2008-02-20 18:56 Message generated for change (Comment added) made by nielsnes You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1898089&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: MonetDB5 5.4 Status: Open Resolution: None Priority: 5 Private: No Submitted By: Michael Yang (msyang) Assigned to: Niels Nes (nielsnes) Summary: row_number() failing when used in join Initial Comment: Using the newest x64 Feb2008 release of monetDB/SQL on Windows server 2003 x64. This is a follow-up to issue [ 1892413 ] wrong or unexpected results in subquery - I'm instead attempting to use the row_number() over() to retrieve the next consecutive date. sql>CREATE TABLE tradeDate(date int not null constraint pk_tradeDate primary key); Timer 15.000 msec 0 rows sql>COPY INTO tradeDate FROM 'T:/temp/dt.txt' USING DELIMITERS '\t'; Rows affected 32605 Timer 110.000 msec 0 rows sql>SELECT COUNT(*) AS cnt from tradeDate; +-------+ | cnt | +=======+ | 32605 | +-------+ Timer 0.000 msec 1 rows sql>WITH e as (SELECT date, row_number() over (order by date) as rn from tradeDate) SELECT date, rn from e ORDER BY date limit 5; +----------+---+ | date |rn | +==========+===+ | 19251231 | 0 | | 19260102 | 1 | | 19260104 | 2 | | 19260105 | 3 | | 19260106 | 4 | +----------+---+ Timer 0.000 msec 5 rows sql>WITH e as (SELECT date, row_number() over (order by date) as rn from tradeDate) SELECT e1.date, e1.rn, e2.date, e2.rn from e as e1 inner join e as e2 ON e2. rn = e1.rn limit 5; +----------+---+---------+---+ | date |rn |date |rn | +==========+===+=========+===+ | 19251231 | 0 |19251231 | 0 | | 19260102 | 1 |19260102 | 1 | | 19260104 | 2 |19260104 | 2 | | 19260105 | 3 |19260105 | 3 | | 19260106 | 4 |19260106 | 4 | +----------+---+---------+---+ Timer 32.000 msec 5 rows -------------------------------------------- At this point, everything is OK. I've loaded the dates, demonstrated that row_number() is working. However, when attempting to join the row-numbered table 'e' with itself based on getting the next day, the query returns no results: sql>WITH e as (SELECT date, row_number() over (order by date) as rn from tradeDate) SELECT e1.date, e1.rn, e2.date, e2.rn from e as e1 inner join e as e2 ON e2. rn + 1 = e1.rn limit 5; Timer 0.000 msec 0 rows Still doesn't work if I take the 'limit' clause off: sql>WITH e as (SELECT date, row_number() over (order by date) as rn from tradeDa te) SELECT e1.date, e1.rn, e2.date, e2.rn from e as e1 inner join e as e2 ON e2. rn + 1 = e1.rn; Timer 0.000 msec 0 rows [data file dt.txt is attached] ---------------------------------------------------------------------- >Comment By: Niels Nes (nielsnes) Date: 2008-02-21 08:09 Message: Logged In: YES user_id=43556 Originator: NO The bug seems to be related to the WITH statement. When we use a real view for e it gives the correct output. ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1898089&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
