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 5 5988
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
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
<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
<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
Yep. It actually works as I posted. It only breaks when you remove
the count(*) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
|
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...
|
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
|
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,
|
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...
| |
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...
|
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,...
|
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...
|
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: $!";
|
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...
|
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...
| |
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. ...
|
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
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...
| |