Internal SQL Server error on UNION ALL

Internal SQL Server error on UNION ALL

Post by Ivan Arjentinsk » Wed, 29 Nov 2000 04:00:00



While solving another users problem I got this:

CREATE TABLE MaxCol(
PK INT PRIMARY KEY,
f1 INT,
f2 INT,
f3 INT,
f4 INT,
f5 INT,
mx INT
)
GO

INSERT INTO MaxCol VALUES(1,1,2,3,4,5,0)
INSERT INTO MaxCol VALUES(2,1,2,6,4,5,0)
INSERT INTO MaxCol VALUES(3,1,8,3,4,5,0)
INSERT INTO MaxCol VALUES(4,1,2,3,4,6,0)

UPDATE MaxCol
SET mx =
    (SELECT MAX(f)
    FROM
        (
        SELECT f1 AS f
        FROM MaxCol AS A
        WHERE A.PK=MaxCol.PK
        UNION ALL        -- Change this<<<
        SELECT f2
        FROM MaxCol AS B
        WHERE B.PK=MaxCol.PK
        UNION ALL
        SELECT f3
        FROM MaxCol AS C
        WHERE C.PK=MaxCol.PK
        UNION ALL
        SELECT F4
        FROM MAXCOL AS D
        WHERE D.PK=MAXCOL.PK
        UNION ALL
        SELECT F5
        FROM MAXCOL AS E
        WHERE E.PK=MAXCOL.PK
        ) AS TU
    )

RESULT:
Server: Msg 8624, Level 16, State 21, Line 1
Internal SQL Server error.
on the UPDATE.

When I changed the first UNION ALL to UNION it worked as expected.
Any ideas? Is it reproducing on other servers.


------
Microsoft SQL Server  2000 - 8.00.194 (Intel X86)
 Aug  6 2000 00:57:48
 Copyr

--
Ivan Arjentinski
-----------------------------------------------
Please answer only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
-----------------------------------------------

 
 
 

Internal SQL Server error on UNION ALL

Post by BP Margoli » Wed, 29 Nov 2000 04:00:00


Ivan,

Works on SQL Server 7.0 w/SP2 ... reproduced your bomb on SQL Server 2000
... will pass it on to Microsoft ...

Thanks, BPM

BTW, this simplification still bombs ...

CREATE TABLE MaxCol(
PK INT PRIMARY KEY,
f1 INT,
f2 INT,
mx INT
)
GO

UPDATE MaxCol
SET mx =
    (SELECT MAX(f)
    FROM
        (
        SELECT f1 AS f
        FROM MaxCol AS A
        WHERE A.PK=MaxCol.PK
        UNION ALL
        SELECT f2
        FROM MaxCol AS B
        WHERE B.PK=MaxCol.PK
        ) AS TU
    )


> While solving another users problem I got this:

> CREATE TABLE MaxCol(
> PK INT PRIMARY KEY,
> f1 INT,
> f2 INT,
> f3 INT,
> f4 INT,
> f5 INT,
> mx INT
> )
> GO

> INSERT INTO MaxCol VALUES(1,1,2,3,4,5,0)
> INSERT INTO MaxCol VALUES(2,1,2,6,4,5,0)
> INSERT INTO MaxCol VALUES(3,1,8,3,4,5,0)
> INSERT INTO MaxCol VALUES(4,1,2,3,4,6,0)

> UPDATE MaxCol
> SET mx =
>     (SELECT MAX(f)
>     FROM
>         (
>         SELECT f1 AS f
>         FROM MaxCol AS A
>         WHERE A.PK=MaxCol.PK
>         UNION ALL        -- Change this<<<
>         SELECT f2
>         FROM MaxCol AS B
>         WHERE B.PK=MaxCol.PK
>         UNION ALL
>         SELECT f3
>         FROM MaxCol AS C
>         WHERE C.PK=MaxCol.PK
>         UNION ALL
>         SELECT F4
>         FROM MAXCOL AS D
>         WHERE D.PK=MAXCOL.PK
>         UNION ALL
>         SELECT F5
>         FROM MAXCOL AS E
>         WHERE E.PK=MAXCOL.PK
>         ) AS TU
>     )

> RESULT:
> Server: Msg 8624, Level 16, State 21, Line 1
> Internal SQL Server error.
> on the UPDATE.

> When I changed the first UNION ALL to UNION it worked as expected.
> Any ideas? Is it reproducing on other servers.


> ------
> Microsoft SQL Server  2000 - 8.00.194 (Intel X86)
>  Aug  6 2000 00:57:48
>  Copyr

> --
> Ivan Arjentinski
> -----------------------------------------------
> Please answer only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.
> -----------------------------------------------


 
 
 

Internal SQL Server error on UNION ALL

Post by BP Margoli » Wed, 29 Nov 2000 04:00:00


Steve,

Even with a syntax error, SQL Server should not return a message of
"Internal SQL Error" ... and I have to disagree with you on the point of the
validity of the SQL itself ... the point of the code was to do a global
update of the mx column in the #MaxCol table.

BPM


> Greetings,

> I think the only bug here is in your sql logic <g>.It is an illegal
> outer reference.

> Try:

> UPDATE #MaxCol
> SET mx =
>        (SELECT max(f)
>        FROM
>        ( SELECT pk,f1 AS f
>         FROM #MaxCol AS A
>         UNION ALL
>         SELECT pk,f2
>         FROM #MaxCol AS B
>          ) AS TU
>        WHERE TU.pk=#maxcol.pk)

> :P

> Kindest regards,
> Sql humorist


> > Ivan,

> > Works on SQL Server 7.0 w/SP2 ... reproduced your bomb on SQL Server
> 2000
> > ... will pass it on to Microsoft ...

> > Thanks, BPM

> > BTW, this simplification still bombs ...

> > CREATE TABLE MaxCol(
> > PK INT PRIMARY KEY,
> > f1 INT,
> > f2 INT,
> > mx INT
> > )
> > GO

> > UPDATE MaxCol
> > SET mx =
> >     (SELECT MAX(f)
> >     FROM
> >         (
> >         SELECT f1 AS f
> >         FROM MaxCol AS A
> >         WHERE A.PK=MaxCol.PK
> >         UNION ALL
> >         SELECT f2
> >         FROM MaxCol AS B
> >         WHERE B.PK=MaxCol.PK
> >         ) AS TU
> >     )

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

Internal SQL Server error on UNION ALL

Post by Steve Dassi » Thu, 30 Nov 2000 12:15:16


Greetings,

I think the only bug here is in your sql logic <g>.It is an illegal
outer reference.

Try:

UPDATE #MaxCol
SET mx =
       (SELECT max(f)
       FROM
       ( SELECT pk,f1 AS f
        FROM #MaxCol AS A
        UNION ALL
        SELECT pk,f2
        FROM #MaxCol AS B
         ) AS TU
       WHERE TU.pk=#maxcol.pk)

:P

Kindest regards,
Sql humorist


Quote:

> Ivan,

> Works on SQL Server 7.0 w/SP2 ... reproduced your bomb on SQL Server
2000
> ... will pass it on to Microsoft ...

> Thanks, BPM

> BTW, this simplification still bombs ...

> CREATE TABLE MaxCol(
> PK INT PRIMARY KEY,
> f1 INT,
> f2 INT,
> mx INT
> )
> GO

> UPDATE MaxCol
> SET mx =
>     (SELECT MAX(f)
>     FROM
>         (
>         SELECT f1 AS f
>         FROM MaxCol AS A
>         WHERE A.PK=MaxCol.PK
>         UNION ALL
>         SELECT f2
>         FROM MaxCol AS B
>         WHERE B.PK=MaxCol.PK
>         ) AS TU
>     )

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

Internal SQL Server error on UNION ALL

Post by Steve Dassi » Thu, 30 Nov 2000 13:45:17


Server Guru BP,

I am suggesting that your query:

UPDATE MaxCol
 SET mx =
     (SELECT MAX(f)
    FROM
         (
         SELECT f1 AS f
         FROM MaxCol AS A
        WHERE A.PK=MaxCol.PK
         UNION ALL
         SELECT f2
         FROM MaxCol AS B
        WHERE B.PK=MaxCol.PK
         ) AS TU
     )

is ill formed because the inner scope:

(
SELECT f1 AS f
     FROM MaxCol AS A
        WHERE A.PK=MaxCol.PK
        UNION ALL
        SELECT f2
        FROM MaxCol AS B
        WHERE B.PK=MaxCol.PK
         ) AS TU

cannot see outside itself and thus A.PK cannot see/reference MaxCol.PK
and B.PK cannot see/reference MaxCol.PK.The PK's can see/reference each
other only in the outer scope as in my formulation.You can prove me
wrong by constructing any query in any version of ms server where a
relationship between 2 tables passes thru "2" scopes ie. an inner and
outer.This is what is probably contributing to the internal error.

Reasonable people can disagree :)

Steve



> Steve,

> Even with a syntax error, SQL Server should not return a message of
> "Internal SQL Error" ... and I have to disagree with you on the point
of the
> validity of the SQL itself ... the point of the code was to do a
global
> update of the mx column in the #MaxCol table.

> BPM



> > Greetings,

> > I think the only bug here is in your sql logic <g>.It is an illegal
> > outer reference.

> > Try:

> > UPDATE #MaxCol
> > SET mx =
> >        (SELECT max(f)
> >        FROM
> >        ( SELECT pk,f1 AS f
> >         FROM #MaxCol AS A
> >         UNION ALL
> >         SELECT pk,f2
> >         FROM #MaxCol AS B
> >          ) AS TU
> >        WHERE TU.pk=#maxcol.pk)

> > :P

> > Kindest regards,
> > Sql humorist


> > > Ivan,

> > > Works on SQL Server 7.0 w/SP2 ... reproduced your bomb on SQL
Server
> > 2000
> > > ... will pass it on to Microsoft ...

> > > Thanks, BPM

> > > BTW, this simplification still bombs ...

> > > CREATE TABLE MaxCol(
> > > PK INT PRIMARY KEY,
> > > f1 INT,
> > > f2 INT,
> > > mx INT
> > > )
> > > GO

> > > UPDATE MaxCol
> > > SET mx =
> > >     (SELECT MAX(f)
> > >     FROM
> > >         (
> > >         SELECT f1 AS f
> > >         FROM MaxCol AS A
> > >         WHERE A.PK=MaxCol.PK
> > >         UNION ALL
> > >         SELECT f2
> > >         FROM MaxCol AS B
> > >         WHERE B.PK=MaxCol.PK
> > >         ) AS TU
> > >     )

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

Internal SQL Server error on UNION ALL

Post by Steve Dassi » Thu, 30 Nov 2000 15:34:25


BP,
My apologies.I am wrong.The very fact it works with union is proof of
that.The update reformulated as a select also works with union and
gives an internal error with union all.My explanation is for an
entirely separate issue.It is a bug generated by silly query
language :).

Crow eater

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Internal SQL Server error on UNION ALL

Post by BP Margoli » Thu, 30 Nov 2000 04:00:00


So this makes the score something like 4,944 for you and 1 for me   :-)

BPM


Quote:> BP,
> My apologies.I am wrong.The very fact it works with union is proof of
> that.The update reformulated as a select also works with union and
> gives an internal error with union all.My explanation is for an
> entirely separate issue.It is a bug generated by silly query
> language :).

> Crow eater

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

1. SQL Server bug 2 - Internal server error

The error occurs when executing the statement below. If I change SELECT TOP
661 to SELECT TOP 660, the error does not occur.
Neither does it occur if I use inner joins instead of left outer joins.

INSERT INTO dw..Actual_curr(unit_id, account_id, business_structure_id
   , business_segment_id, period_id, frequency_id
   , ratetype_id, currency_id, loc_currency_code
   , opening_backlog, order_backlog_later, net_invoicing_YTD
   , orders_received_YTD)
 SELECT TOP 661
    unit_id, d_account, d_rel
  , d_segrel, d_period, 0 AS f
  , ratetype, d_curr, T210.curr_code
  , t1.m_value AS m1, t2.m_value AS m2, t3.m_value AS m3
  , t4.m_value AS m4
   FROM T212_md_temp AS T212
LEFT OUTER
JOIN T209_md_tmpcol AS t1 ON t1.d_id = T212.d_id AND t1.d_column = 0
LEFT OUTER
JOIN T209_md_tmpcol AS t2 ON t2.d_id = T212.d_id AND t2.d_column = 3
LEFT OUTER
JOIN T209_md_tmpcol AS t3 ON t3.d_id = T212.d_id AND t3.d_column = 16
LEFT OUTER
JOIN T209_md_tmpcol AS t4 ON t4.d_id = T212.d_id AND t4.d_column = 17
JOIN T210_md_curr AS T210 ON T210.m_curr = T212.m_curr
WHERE T212.table_id    = 100

The server is running SP2.

Thanks for your help
Lars Gramark

2. Installing OpenLink Beta

3. Internal SQL Error with SQL server 7

4. P.SQL 7.0 and Status 2

5. Error 2502 - INTERNAL USE ONLY - Internal error (SRV INE)

6. Using ct-lib to execute stored procedure, can't retrieve output parameters

7. Internal SQL Server Error

8. TM1 API and VB

9. Help - cannot start SQL server - Windows NT Internal error

10. SQL Server Internal Error

11. Inner Query returning Internal SQL Server error

12. SQL Server : Internal Error

13. Another Internal SQL Server Error