very weird SELECT error (MSDE)
-
This is the weirdest and most simple error I have ever seen in my 5+ years of SQL experience. Hopefully someone has run into this before. I am getting a query timeout when including a specific column in a select statement, but no timeout when I do "select *". I'll paste in queries that work, as well as queries that don't work. When I include by name the column "invoice_finalised" the query times out. When I include invoice_finalised and remove *any other column* it works fine (<0.1s). When I do "select * from invoice" it runs fine. The problem seems to be when I use the column by name. This is terribly confusing. Thanks for any help you guys can offer.
Works:
select invoice_id, customer_code, invoice_creation_ts, invoice_created_by, customer_invoice_code, total_items, total_weight, invoice_total from dbo.invoice
select * from dbo.invoice
select invoice_finalised, invoice_id, ((((customer_code removed)))) invoice_creation_ts, invoice_created_by, customer_invoice_code, total_items, total_weight, invoice_total from dbo.invoice
DOESN'T work (just times out):
select invoice_finalised, invoice_id, customer_code, invoice_creation_ts, invoice_created_by, customer_invoice_code, total_items, total_weight, invoice_total from dbo.invoice
Below is my table creation statement. There are only two rows in the table, and the offending bit column (invoice_finalised) is set to 1 for both rows. Does this make sense to anyone?
CREATE TABLE [invoice] (
[invoice_id] [int] NOT NULL ,
[customer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[invoice_creation_ts] [datetime] NULL ,
[invoice_created_by] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[current_gold_fix_gram] [decimal](6, 2) NULL ,
[invoice_processed_by] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[shipping] [decimal](6, 2) NULL ,
[customer_invoice_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[total_items] [int] NOT NULL CONSTRAINT [DF_invoice_total_items] DEFAULT (0),
[total_weight] [decimal](6, 2) NOT NULL CONSTRAINT [DF_invoice_total_weight] DEFAULT (0.0),
[invoice_total] [decimal](18, 2) NOT NULL CONSTRAINT [DF_invoice_invoice_total] DEFAULT (0.00),
[logged_in_user] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_invoice_logged_in_user] DEFAULT (''),
[invoice_notes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[invoice_finalised] [bit] NOT NULL CONSTRAINT [DF_invoice_invoice_finalised] DEFAULT (0),
[use_shipping_address] [bit] NOT NULL CONSTRAINT [DF_invoice_use_shipping_address] DEFAULT (0),
CONSTRAINT [PK_invoice] PRIMARY KEY CLUSTERED
(
[invoice_id]
) ON [PRIMARY]
) ON [PRIMARY]
-
After further testing, if I ADD a column to the select list it returns perfectly. It seems to be the unique combination of columns listed.
-
Can you send us the plans of each queries??
SET SHOWPLAN_TEXT ON
GO
Select 'query here'
GO
SET SHOWPLAN_TEXT OFF
-
Thanks for the reply! Unfortunately all queries show the same plan:
|--Clustered Index Scan(OBJECT
[testdb].[dbo].[invoice].[PK_invoice]))(edit: should be |--Clustered Index Scan(OBJECT: ([testdb].[dbo].[invoice].[PK_invoice]))
-
wow... tried rebooting, reinstalling??
-
So the sad face wasn't part of the plan then?
When things like this start happening, you start suspecting the integrity and consistency of the database and perhaps you should think about running some checks.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Ya I'd check that first now that it is suggested
. -
Tried rebooting. Not reinstalling. I'm running checks in a second. Thanks for the help guys
-
CHECKDB found 0 allocation errors and 0 consistency errors in database 'testdb'.
<-- the real sad face[1] Database testdb: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 3 secs **
Well I'm stumped. Looks like it's "select *" for now -
Getting weirder. Have you tried backing up the db and restoring it as another db, then running the query on that? If that fails, try restoring onto a different SQL Server/MSDE instance and executing again. If that fails again, suggest you post the DDL and I'll give it a go here.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I'll try copying the db and moving to another instance, but I'll have to do that later as I'm on a deadline. I'm just going to add the extra column until I get a proper fix implemented. Thanks for the help, I'll keep you guys posted on my results!
-
Dumb question:
What happens if you change the two bit fields to [tiny]int?
I think I'm working up an aversion to bit fields.
Regards
Otto
Best Regards,Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke) -
Why's that??
-
Why am I developing an aversion to bit fields?
They seem to be responsible for unexpected wierdness - see for example thread "Why does this scan an entire NC index?" in the performance section. After reading the posts there, they seem to work fine - once you work out how to use them properly.
Having said that - I've used them before without any problems.
Regards
Otto
Best Regards,Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke) -
Simple trick >> Where BitColum = CAST(0 AS BIT)
but they usually make poor index because of selectivity
.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply