473,837 Members | 1,498 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Example of Internal SQL Server Error

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

Jul 23 '05 #1
5 5997
I changed it a bit and it works:

select count(*) as TicketsIssuedBy ThisOfficer,
datepart(hh,Tic ketDate) as HourOfDay, IssuingOfficer
, ( 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' ) as
TotalTicketsFor ThisHour_AllOff icers
from ParkingTickets PT
where TicketDate between '1/2/2005' and '1/5/2005'
group by IssuingOfficer, datepart(hh,Tic ketDate)
order by HourOfDay, IssuingOfficer

FYI, I only got the error with the
'TotalTicketsFo rThisHour_AllOf f*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'
)
' line in the query.

Jamie

Jul 23 '05 #2
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.goo glegroups.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,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

Jul 23 '05 #3

<ch************ ****@gmail.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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,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


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
Jul 23 '05 #4
<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 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.


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
Jul 23 '05 #5
Yep. It actually works as I posted. It only breaks when you remove
the count(*)

Jul 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
9297
by: Michael Foord | last post by:
#!/usr/bin/python -u # 15-09-04 # v1.0.0 # auth_example.py # A simple script manually demonstrating basic authentication. # Copyright Michael Foord # Free to use, modify and relicense. # No warranty express or implied for the accuracy, fitness to purpose
10
10830
by: | last post by:
I am accessing the same error-containing ASP page on an ISP server using w2k IE6 but with different effect. On the first computer I get several line of HTML outputed by ASP, shown correctly by the browser, followed by a descriptive error message: Microsoft VBScript runtime error '800a000b' Division by zero followed by the number of the error-making line
5
8484
by: Ben | last post by:
hi when I try to excecute an ASP (either JS or VB) script to say, access a database record, I get an Internal Server Error HTTP 500.100 Why? and HOW CAN I FIX THIS? Thanks
4
3729
by: Patrick Masson | last post by:
Hello, Our configuration : Apache 2.0.53 PHP 5.0.4 PC Windows 2000 MATLAB 6.1 We work on a consulting project in France which involves MATLAB Web server,
8
10027
by: Rod | last post by:
I have been working with ASP.NET 1.1 for quite a while now. For some reason, opening some ASP.NET applications we wrote is producing the following error message: "The Web server reported the following error when attempting to create or open the Web project located at the following URL: 'http://localhost/WebApplication1'. 'HTTP/1.1 500 Internal Server Error'."
1
4240
by: sianan | last post by:
I tried to use the following example, to add a checkbox column to a DataGrid in an ASP.NET application: http://www.codeproject.com/aspnet/datagridcheckbox.asp For some reason, I simply CAN'T get the example to work. I created the following two classes, provided with the example: *-*-**-*-*-*-*-*-*-*-*-*-**-*-*-*-*-CheckBoxColumn Class:-*-*-*-*-*-*-*-*-*-*-**-*-*-*-*-**-*-*-*
4
6215
by: jf li | last post by:
I have a Asp.net web application and a Asp.net Web service application. The Web application is using HtmlInputFile to get a 50M size of file selected by end user, read the data of this file and pass the data to the web service. I already modified both web.config files and changed maxRequestLength to 60000(kb). When I debug the upload process, it seems the Web application can get the 50M file and read the data without problem, but when the...
11
12320
by: Lieven | last post by:
Hey, I had a hard disc problem last week on my server. I replaced the disc and copied al the files to the new hard disc, everything works fine again except some php scripts that are using the mail() function. When executing these scripts I get this error: "500 Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request.
23
24581
by: ticfranca | last post by:
Hi, I'm getting this error in the code below: sub Pega_recorde { $database = 'bundinha'; $host = 'localhost'; $usuario = 'myhumoradm'; $senha = 'my8xr2d2'; $dbh=DBI->connect("DBI:mysql:database=$database;host=$host","$usuario","$senha") or die "Can't open DB: $!";
3
5961
by: guillaume.braux | last post by:
Hello, I am running WS2008 + IIS7 + FASTCGI + ZendCore. I have not modified the default ZendCore php.ini configuration file. Actualy, any kind of PHP error, warning or notice gives me immediately a IIS 500 Error (Internal Server Error). It is a good thing in production environnement. For debuging purposes, I want temporary to be able to see PHP warnings and errors embedded on the html page.
0
9843
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9682
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10881
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10575
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9406
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5670
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5850
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4043
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3126
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.