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,Tic ketDate) as HourOfDay,
IssuingOfficer,
count(*) as TicketsIssuedBy ThisOfficer,
TotalTicketsFor ThisHour_AllOff icers = (
select convert(real, count(*))/3
from ParkingTickets
where datepart(hh,Tic ketDate) = 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,Tic ketDate)
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 TicketsIssuedBy ThisOfficer,
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
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,Tic ketDate) as HourOfDay,
IssuingOfficer,
count(*) as TicketsIssuedBy ThisOfficer,
TotalTicketsFor ThisHour_AllOff icers = (
select convert(real, count(*))/3
from ParkingTickets
where datepart(hh,Tic ketDate) = 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,Tic ketDate)
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 TicketsIssuedBy ThisOfficer,
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
Comment