Error 8623 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error 8623

Author

Topic

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-12 : 18:14:54

Error 8623 is "Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information."
Is this always an indication of an error in SQL Server? Or does it depend on the complexity of the query?

SQL Server 2000 sp2.


CREATE VIEW nbr
AS
SELECT b0 + b1 + b2 + b3 + b4 + b5 AS n
FROM (SELECT 0 AS b0 UNION ALL SELECT 1) AS t0,
     (SELECT 0 AS b1 UNION ALL SELECT 2) AS t1,
     (SELECT 0 AS b2 UNION ALL SELECT 4) AS t2,
     (SELECT 0 AS b3 UNION ALL SELECT 8) AS t3,
     (SELECT 0 AS b4 UNION ALL SELECT 16) AS t4,
     (SELECT 0 AS b5 UNION ALL SELECT 32) AS t5

GO


SELECT n INTO #nbr FROM nbr
GO


-- fails:
SELECT n1.n, SUM(n2.n)
FROM nbr n1
INNER JOIN #nbr n2 ON n1.n >= n2.n
GROUP BY n1.n
GO


-- fails:
SELECT n1.n, SUM(n2.n)
FROM #nbr n1
INNER JOIN nbr n2 ON n1.n >= n2.n
GROUP BY n1.n
GO


-- succeeds:
SELECT n1.n, SUM(n2.n)
FROM nbr n1
INNER JOIN nbr n2 ON n1.n >= n2.n
GROUP BY n1.n
GO


-- succeeds:
SELECT n1.n, SUM(n2.n)
FROM #nbr n1
INNER JOIN #nbr n2 ON n1.n >= n2.n
GROUP BY n1.n
GO


DROP TABLE #nbr
DROP VIEW nbr
GO

[Edit: I've changed the subqueries table aliases in the view at Rob's suggestion to make it clearer. It doesn't change the behaviour at all, though.]

Edited by - Arnold Fribble on 03/12/2002 18:30:56

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-12 : 18:18:52

Have you tried changing the column names so that they don't match the sub-query alias. When you look at this:

(SELECT 0 AS b0 UNION ALL SELECT 1) AS b0

The "SELECT 0 AS b0" could be interpreted as a table alias, which would conflict with the outer alias.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-12 : 18:26:07

Rob, thanks, I didn't spot that I'd left the names the same, so I've edited the original post. But no, it doesn't make a difference to the behaviour.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-12 : 19:08:35

Well, it doesn't seem to be a column/table alias confusion, because I tried "SELECT b0=0 UNION ALL SELECT 1" and still the problem arises. I looked at the estimated plans for the ones that succeed, and the only thing I can think of is that the view cannot be properly materialized when joining to another object. Joining to itself works because (I'm REALLY guessing here) once a plan is calculated for it, the optimizer simply reuses it. If you look at the plan that's generated for the view-view join you'll get the same idea I think.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-13 : 03:03:21

It was really only written as a view to make it clearer. Expanding the view in-place doesn't make a difference. Got to dash...


SELECT b0 + b1 + b2 + b3 + b4 + b5 AS n
INTO #nbr
FROM (SELECT 0 AS b0 UNION ALL SELECT 1) AS t0,
     (SELECT 0 AS b1 UNION ALL SELECT 2) AS t1,
     (SELECT 0 AS b2 UNION ALL SELECT 4) AS t2,
     (SELECT 0 AS b3 UNION ALL SELECT 8) AS t3,
     (SELECT 0 AS b4 UNION ALL SELECT 16) AS t4,
     (SELECT 0 AS b5 UNION ALL SELECT 32) AS t5
GO


-- fails:
SELECT n1.n, SUM(n2.n)
FROM (
    SELECT b0 + b1 + b2 + b3 + b4 + b5 AS n
    FROM (SELECT 0 AS b0 UNION ALL SELECT 1) AS t0,
         (SELECT 0 AS b1 UNION ALL SELECT 2) AS t1,
         (SELECT 0 AS b2 UNION ALL SELECT 4) AS t2,
         (SELECT 0 AS b3 UNION ALL SELECT 8) AS t3,
         (SELECT 0 AS b4 UNION ALL SELECT 16) AS t4,
         (SELECT 0 AS b5 UNION ALL SELECT 32) AS t5) n1
INNER JOIN #nbr n2 ON n1.n >= n2.n
GROUP BY n1.n
GO


-- fails:
SELECT n1.n, SUM(n2.n)
FROM #nbr n1
INNER JOIN (
    SELECT b0 + b1 + b2 + b3 + b4 + b5 AS n
    FROM (SELECT 0 AS b0 UNION ALL SELECT 1) AS t0,
         (SELECT 0 AS b1 UNION ALL SELECT 2) AS t1,
         (SELECT 0 AS b2 UNION ALL SELECT 4) AS t2,
         (SELECT 0 AS b3 UNION ALL SELECT 8) AS t3,
         (SELECT 0 AS b4 UNION ALL SELECT 16) AS t4,
         (SELECT 0 AS b5 UNION ALL SELECT 32) AS t5) n2 ON n1.n >= n2.n
GROUP BY n1.n
GO


-- succeeds:
SELECT n1.n, SUM(n2.n)
FROM (
    SELECT b0 + b1 + b2 + b3 + b4 + b5 AS n
    FROM (SELECT 0 AS b0 UNION ALL SELECT 1) AS t0,
         (SELECT 0 AS b1 UNION ALL SELECT 2) AS t1,
         (SELECT 0 AS b2 UNION ALL SELECT 4) AS t2,
         (SELECT 0 AS b3 UNION ALL SELECT 8) AS t3,
         (SELECT 0 AS b4 UNION ALL SELECT 16) AS t4,
         (SELECT 0 AS b5 UNION ALL SELECT 32) AS t5) n1
INNER JOIN (
    SELECT b0 + b1 + b2 + b3 + b4 + b5 AS n
    FROM (SELECT 0 AS b0 UNION ALL SELECT 1) AS t0,
         (SELECT 0 AS b1 UNION ALL SELECT 2) AS t1,
         (SELECT 0 AS b2 UNION ALL SELECT 4) AS t2,
         (SELECT 0 AS b3 UNION ALL SELECT 8) AS t3,
         (SELECT 0 AS b4 UNION ALL SELECT 16) AS t4,
         (SELECT 0 AS b5 UNION ALL SELECT 32) AS t5) n2 ON n1.n >= n2.n
GROUP BY n1.n
GO


-- succeeds:
SELECT n1.n, SUM(n2.n)
FROM #nbr n1
INNER JOIN #nbr n2 ON n1.n >= n2.n
GROUP BY n1.n
GO


DROP TABLE #nbr
GO

Nazim
A custom title

1408 Posts

Posted - 2002-03-13 : 08:07:34

Arnold this works in my system.

i have tested it using Sql 7.0 Service Pack 4 and Sql 2000 Service pack 2.

SELECT b0 + b1 + b2 + b3 + b4 + b5 AS n
INTO #nbr
FROM
(SELECT 0 AS b0 UNION ALL SELECT 1) AS t0,
(SELECT 0 AS b1 UNION ALL SELECT 2) AS t1,
(SELECT 0 AS b2 UNION ALL SELECT 4) AS t2,
(SELECT 0 AS b3 UNION ALL SELECT 8) AS t3,
(SELECT 0 AS b4 UNION ALL SELECT 16) AS t4,
(SELECT 0 AS b5 UNION ALL SELECT 32) AS t5
GO

--------------------------------------------------------------

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-13 : 08:14:01

The problem Nazim is when you join the temp table to the view, it throws the Internal Query Processor error. Mixing the two sources doesn't work for some reason. The non-join and self-join options do work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources