Example of Internal SQL Server Error

[christopher.secord]

Here's a quick and dirty example of a legitimate bug in SQL Server.
I've seen other examples, but they were all very complex, some even
involving cursors and such. This one will produce the error with just
13 lines.

A police department has a database that contains, among other things,
information about parking tickets given out by the officers. Here is
the database and some sample data:

create table ParkingTickets (
TicketDate datetime,
IssuingOfficer int
)

insert into ParkingTickets values ('1/2/2005 12:31',1)
insert into ParkingTickets values ('1/2/2005 14:20',1)
insert into ParkingTickets values ('1/3/2005 12:05',1)
insert into ParkingTickets values ('1/3/2005 12:22',2)
insert into ParkingTickets values ('1/3/2005 14:01',2)
insert into ParkingTickets values ('1/4/2005 10:12',2)
insert into ParkingTickets values ('1/2/2005 12:10',3)
insert into ParkingTickets values ('1/4/2005 12:36',3)

So, as you can see, officer 1 gave out three tickets. Officer 2 gave
out three, etc. Management wants a report containing the following
information;

the Hour of Day,
the ID of the Issuing Officer,
the number of tickets issued by the officer during this hour during the
week,
and the average number of tickets issued by all officers during this
hour during the week

So, if the report is to cover 1/2/2005 through 1/5/2005, the report
would look like this:

select
datepart(hh,TicketDate) as HourOfDay,
IssuingOfficer,
count(*) as TicketsIssuedByThisOfficer,
TotalTicketsForThisHour_AllOfficers = (
select convert(real, count(*))/3
from ParkingTickets
where datepart(hh,TicketDate) = datepart(hh,PT.TicketDate)
and TicketDate between '1/2/2005' and '1/5/2005'
)
from ParkingTickets PT
where TicketDate between '1/2/2005' and '1/5/2005'
group by IssuingOfficer, datepart(hh,TicketDate)
order by HourOfDay, IssuingOfficer

drop table ParkingTickets

if you'll run that in Query Analyzer, you'll see that it works just
fine and produces the expected output. However, if you delete this
line:
count(*) as TicketsIssuedByThisOfficer,

the query errors out. It's almost like magic. With the count(*)
there, the query runs. Without it, the query fails. I can't see any
reason why deleting that line would produce an error. There are
multiple references to this on the microsoft kb, and many of them have
hotfixes. But none of them seems to fix this particular instance of
the bug - and like I said, this is the simplest incarnation that I've
seen.

Christopher Secord

[jamiemcc]

I changed it a bit and it works:

select count(*) as TicketsIssuedByThisOfficer,
datepart(hh,TicketDate) as HourOfDay, IssuingOfficer
, ( select convert(real, count(*))/3 from ParkingTickets where
datepart(hh,TicketDate) = datepart(hh,PT.TicketDate) and TicketDate
between '1/2/2005' and '1/5/2005' ) as
TotalTicketsForThisHour_AllOfficers
from ParkingTickets PT
where TicketDate between '1/2/2005' and '1/5/2005'
group by IssuingOfficer, datepart(hh,TicketDate)
order by HourOfDay, IssuingOfficer

FYI, I only got the error with the
'TotalTicketsForThisHour_AllOff*icers = (
select convert(real, count(*))/3
from ParkingTickets
where datepart(hh,TicketDate) =
datepart(hh,PT.TicketDate)
and TicketDate between '1/2/2005' and
'1/5/2005'
)
' line in the query.

Jamie

[Gang He [MSFT]]

Christopher,

Thanks for finding out the problem. I tried this on SQL Server 200 and
confirmed this is indeed an issue. I suggest you to contact Microsoft
Product Support to report it.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
<ch****************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Here's a quick and dirty example of a legitimate bug in SQL Server.
I've seen other examples, but they were all very complex, some even
involving cursors and such. This one will produce the error with just
13 lines.

A police department has a database that contains, among other things,
information about parking tickets given out by the officers. Here is
the database and some sample data:

create table ParkingTickets (
TicketDate datetime,
IssuingOfficer int
)

insert into ParkingTickets values ('1/2/2005 12:31',1)
insert into ParkingTickets values ('1/2/2005 14:20',1)
insert into ParkingTickets values ('1/3/2005 12:05',1)
insert into ParkingTickets values ('1/3/2005 12:22',2)
insert into ParkingTickets values ('1/3/2005 14:01',2)
insert into ParkingTickets values ('1/4/2005 10:12',2)
insert into ParkingTickets values ('1/2/2005 12:10',3)
insert into ParkingTickets values ('1/4/2005 12:36',3)

So, as you can see, officer 1 gave out three tickets. Officer 2 gave
out three, etc. Management wants a report containing the following
information;

the Hour of Day,
the ID of the Issuing Officer,
the number of tickets issued by the officer during this hour during the
week,
and the average number of tickets issued by all officers during this
hour during the week

So, if the report is to cover 1/2/2005 through 1/5/2005, the report
would look like this:

select
datepart(hh,TicketDate) as HourOfDay,
IssuingOfficer,
count(*) as TicketsIssuedByThisOfficer,
TotalTicketsForThisHour_AllOfficers = (
select convert(real, count(*))/3
from ParkingTickets
where datepart(hh,TicketDate) = datepart(hh,PT.TicketDate)
and TicketDate between '1/2/2005' and '1/5/2005'
)
from ParkingTickets PT
where TicketDate between '1/2/2005' and '1/5/2005'
group by IssuingOfficer, datepart(hh,TicketDate)
order by HourOfDay, IssuingOfficer

drop table ParkingTickets

if you'll run that in Query Analyzer, you'll see that it works just
fine and produces the expected output. However, if you delete this
line:
count(*) as TicketsIssuedByThisOfficer,

the query errors out. It's almost like magic. With the count(*)
there, the query runs. Without it, the query fails. I can't see any
reason why deleting that line would produce an error. There are
multiple references to this on the microsoft kb, and many of them have
hotfixes. But none of them seems to fix this particular instance of
the bug - and like I said, this is the simplest incarnation that I've
seen.

Christopher Secord

[Simon Hayes]

<ch****************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Here's a quick and dirty example of a legitimate bug in SQL Server.
I've seen other examples, but they were all very complex, some even
involving cursors and such. This one will produce the error with just
13 lines.

A police department has a database that contains, among other things,
information about parking tickets given out by the officers. Here is
the database and some sample data:

create table ParkingTickets (
TicketDate datetime,
IssuingOfficer int
)

insert into ParkingTickets values ('1/2/2005 12:31',1)
insert into ParkingTickets values ('1/2/2005 14:20',1)
insert into ParkingTickets values ('1/3/2005 12:05',1)
insert into ParkingTickets values ('1/3/2005 12:22',2)
insert into ParkingTickets values ('1/3/2005 14:01',2)
insert into ParkingTickets values ('1/4/2005 10:12',2)
insert into ParkingTickets values ('1/2/2005 12:10',3)
insert into ParkingTickets values ('1/4/2005 12:36',3)

So, as you can see, officer 1 gave out three tickets. Officer 2 gave
out three, etc. Management wants a report containing the following
information;

the Hour of Day,
the ID of the Issuing Officer,
the number of tickets issued by the officer during this hour during the
week,
and the average number of tickets issued by all officers during this
hour during the week

So, if the report is to cover 1/2/2005 through 1/5/2005, the report
would look like this:

select
datepart(hh,TicketDate) as HourOfDay,
IssuingOfficer,
count(*) as TicketsIssuedByThisOfficer,
TotalTicketsForThisHour_AllOfficers = (
select convert(real, count(*))/3
from ParkingTickets
where datepart(hh,TicketDate) = datepart(hh,PT.TicketDate)
and TicketDate between '1/2/2005' and '1/5/2005'
)
from ParkingTickets PT
where TicketDate between '1/2/2005' and '1/5/2005'
group by IssuingOfficer, datepart(hh,TicketDate)
order by HourOfDay, IssuingOfficer

drop table ParkingTickets

if you'll run that in Query Analyzer, you'll see that it works just
fine and produces the expected output. However, if you delete this
line:
count(*) as TicketsIssuedByThisOfficer,

the query errors out. It's almost like magic. With the count(*)
there, the query runs. Without it, the query fails. I can't see any
reason why deleting that line would produce an error. There are
multiple references to this on the microsoft kb, and many of them have
hotfixes. But none of them seems to fix this particular instance of
the bug - and like I said, this is the simplest incarnation that I've
seen.

Christopher Secord

The error seems to be described by this KB article (you didn't give any KB
article numbers, so I don't know if you've seen this one or not) - a
correlated subquery and GROUP BY:

http://support.microsoft.com/default...;en-us;Q274729

Unfortunately there is no solution given, only a workaround, but you could
open a case with PSS anyway - cases caused by bugs are free, and it should
mean that you get to know about any fix as soon as it's available.

Simon

[Craig Kelly]

<ch****************@gmail.com> wrote:

<snip>

if you'll run that in Query Analyzer, you'll see that it works just
fine and produces the expected output. However, if you delete this
line:
count(*) as TicketsIssuedByThisOfficer,

the query errors out. It's almost like magic. With the count(*)
there, the query runs. Without it, the query fails. I can't see any
reason why deleting that line would produce an error.

Christopher,

FWIW, when I played with your test (on SQL 2K) it appears that the problem
is the sub-query appearing before an aggregate function in the SELECT list:
moving count(*) to the end gives the same error. Adding another aggregate
like min(TicketDate) before the sub-query magically makes the query work
again.

Craig

[christopher.secord]

Yep. It actually works as I posted. It only breaks when you remove
the count(*)