inconsistent result sets

Question

inconsistent result sets

stewart noble

SSCrazy

Points: 2009
More actions
January 22, 2007 at 2:29 am

#153395

Hi all,

I've never come across this problem before. I have a 5.5 million row table in a SQL Server 2000 database. I am trying to run a query for rows where a column is null:
select Id,Column1, column2, column3
from <table> where <column1> is null
and substring(column2,4,2) = 'HS'
I had a feeling the data was incorrect so I ran a different query:
select column1,count(*)
from o_transfer
where column1 is null
and substring(column2,4,2) = 'HS'
group by trn_ext_bat_id
and got the following results:
column1
-------------- -----------
4194           370349
I ran it again but got:
column1
-------------- -----------
4248           370349
and again:
column1
-------------- -----------
4186           370349
I suspectd it may be an index problem so I dropped and recreated all of the indexes on the table but the results were the same.
Can anyone shed any light on this?
Thanks

S

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

Stewart

That query should produce an error since you're grouping by something that isn't in the select list. Are you doing this in Query Analyzer? Can you post the DDL for your table?

John

stewart noble SSCrazy Points: 2009 More actions · Answer 2

sorry, I cut and pasted from QA but tried to remove the table specific data. The query itself is

select trn_ext_bat_id,count(*)

from o_transfer

where trn_ext_bat_id is null

and substring(trn_task_code,4,2) = 'HS'

group by trn_ext_bat_id

S

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 3

Stewart

That's what I suspected. Please will you post the DDL... you can change the column names if you wish. Some sample data would be helpful, too.

John

tnk SSCrazy Points: 2613 More actions · Answer 4

Forgive me if this is a stupid suggestion but are you certain that there are zero applications inserting, updating and deleting on that table? Is there replication configured on that database? Perhaps each minute the transactions from another database are synced up with this one.

stewart noble SSCrazy Points: 2009 More actions · Answer 5

The are no stupid suggestions. However, in this case I am sure that the data is static between queries. I am struggling to understand how a row with data in the "trn_ext_bat_id" column can appear in a query for all rows where that column is null.

S

Sergiy SSC Guru Points: 110192 More actions · Answer 6

Is there only one trn_ext_bat_id value in the table?

Why does you resultset display only one row?

Is it like this:

select @trn_ext_bat_id = trn_ext_bat_id,

@count = count(*)

from o_transfer

where trn_ext_bat_id is null

and substring(trn_task_code,4,2) = 'HS'

group by trn_ext_bat_id

select @trn_ext_bat_id , @count

Then you don't have any criteria for selecting trn_ext_bat_id, so it returns any one.

_____________
Code for TallyGenerator

stewart noble SSCrazy Points: 2009 More actions · Answer 7

No, as far as I can tell there are 147k rows which have a trn_ext_bat_id value. I have set rowcount 0.

The result set itself is reasonable (i.e. I am asking it to count the number of rows where trn_ext_bat_id is null which is 370349 rows. I would however expect the trn_ext_bat_id to be NULL).

I am almost positive this is an index problem - it reminds of the times that good old DBASE IV needed a reindex.

I am showing you exactly the query as it is:

select trn_ext_bat_id,count(*) as NullRecs

from o_transfer

where trn_ext_bat_id is null

and substring(trn_task_code,4,2) = 'HS'

group by trn_ext_bat_id

I would expect to see the following result set:

trn_ext_bat_id NullRecs

-------------- -----------

NULL 370349

But it don't. And worse, if the query were changed to:

select trn_ext_bat_id, trn_task_code

from o_transfer

where trn_ext_bat_id is null

and substring(trn_task_code,4,2) = 'HS'

then I get a result set like this

trn_ext_bat_id trn_task_code

-------------- -----------

NULL HSHSHSHS

4012 HSHSHSHS

...

NULL HSHSHSHS

5463 HSHSHSHS

Which is definitely wrong.

Very odd.

S

Sergiy SSC Guru Points: 110192 More actions · Answer 8

What if you query this:

select trn_ext_bat_id, trn_task_code, CONVERT(varbinary(8000), trn_task_code)

_____________
Code for TallyGenerator

stewart noble SSCrazy Points: 2009 More actions · Answer 9

Well, I tried that but this is part of the result set:

trn_ext_bat_id trn_task_code

-------- ------------- ------------------------

NULL OAEHSGCO 0x4F4145485347434F

3939 OAEHSGCO 0x4F4145485347434F

As you can see it make no difference at all.

S

Sergiy SSC Guru Points: 110192 More actions · Answer 10

If you really expose everything and nothing left beyond the scope I would suggest to leave everything and try to save your database.

_____________
Code for TallyGenerator