Why does this crash SQL7 ?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Lisa Pearlson (no_at_spam.plz)
Date: 08/13/04 

Date: Fri, 13 Aug 2004 02:09:31 +0200

I got the latest SP (4) and MDAC, still this causes a crash..
Anyone know why, and how to circumvent this bug?

CREATE TABLE #test (
[year] smallint,
[month] tinyint,
[product] varchar(100),
[value] money
)
GO

INSERT INTO #test VALUES (2000, 2, 'p-01', 1.00)
INSERT INTO #test VALUES (2000, 2, 'p-02', 2.00)
INSERT INTO #test VALUES (2001, 2, 'p-01', 1.00)
INSERT INTO #test VALUES (2001, 2, 'p-03', 3.00)
INSERT INTO #test VALUES (2002, 2, 'p-01', 1.00)
INSERT INTO #test VALUES (2002, 2, 'p-01', 1.00)
GO

SELECT
 a.year,
 a.product,
 a.month,
 ISNULL(o.value, 0) value,
 ISNULL((SELECT SUM(value) FROM #test
  WHERE year=a.year AND product=a.product AND month<=a.month), 0)
cumul_artikel,
 ISNULL((SELECT SUM(value) FROM #test
  WHERE year=a.year AND month=a.month), 0) value_month,
 ISNULL((SELECT SUM(value) FROM #test
  WHERE year=a.year AND month<=a.month), 0) cumul_month
FROM (SELECT
 j.year,
 j.product,
 m.month
 FROM
 (SELECT DISTINCT [year], [product] FROM #test) j,
 (SELECT 1 AS [month]
 UNION ALL SELECT 2
 UNION ALL SELECT 3
 UNION ALL SELECT 4
 UNION ALL SELECT 5
 UNION ALL SELECT 6
 UNION ALL SELECT 7
 UNION ALL SELECT 8
 UNION ALL SELECT 9
 UNION ALL SELECT 10
 UNION ALL SELECT 11
 UNION ALL SELECT 12) m) AS a
LEFT OUTER JOIN #test o
 ON o.year = a.year AND o.month=a.month AND o.product=a.product
GO

DROP TABLE #test
GO

Lisa

Relevant Pages

  • Re: Why is C Standard Code Example Invalid?
    ... declaration of the complete type of the union is visible. ... visible union that contains them both, the compiler is at liberty to ... The C Standard says this a bug in the program rather than ...
    (comp.std.c)
  • very nasty problem .. please help
    ... I believe this is a bug in SQL7, ... The big problem is that the reporting tool used to get data from this table, ... UNION ALL SELECT 2 ...
    (microsoft.public.sqlserver.programming)
  • SQL Injection in phpBT (bug.php)
    ... ('binary' encoding is not supported, ... For viewing the votes placed on a bug, the bug_id variable is left open. ... bug.php?op=viewvotes&bugid=1 union select 1,2,3/*%20 XSS here ...
    (Bugtraq)
  • Re: Urgent: Strange Exception on SQL7 but not on SQL2000
    ... Either data corruptions or bug. ... > ISNULLFROM Sales ... > UNION ALL SELECT 2 ... > SELECT boekjr, artcode, periode, omzet, cumul_artikel ...
    (microsoft.public.sqlserver.server)
  • Re: Here is the query
    ... Please disregard my earlier posting asking for the repro. ... using had a bug that we fixed in at least SQL Server 2005, ... I have started using UNION ...
    (microsoft.public.sqlserver.xml)