Can SQL get Corrupted - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Can SQL get Corrupted

Author

Topic

jsarraf
Starting Member

2 Posts

Posted - 2002-11-10 : 22:15:17

I'm helping a friend with one of his query. The problem is driving me crazy since I have never encountered anything like that before.

We have a select query from T1 which is outer joined to T2, T3 and T4

All Tables T1....T4 are derived tables.

The query looks OK. and was working fine until fine recentetly where it started to return different result for the same parameters. Is that Possible in SQL. And what would be the possible cause for it.

So the problem is that I have a DB that is not used by anyone else, I run a query in Query analyser but the query return DIFFERENT results for each run even If I have only few seconds between one and the other.

WWJD

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-11-11 : 03:30:46

Pretty obvious question, but do you have any time related constraints in your WHERE or JOIN clauses? :-)

If not, its possible that you may have encountered a bug in SQL Server. We can only say something concrete, if you post your create table structures, some sample data and your queries, so that we can reproduce the problem.

--
HTH,
Vyas
http://vyaskn.tripod.com

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-11 : 03:35:28

quote:

So the problem is that I have a DB that is not used by anyone else, I run a query in Query analyser but the query return DIFFERENT results for each run even If I have only few seconds between one and the other.

When you say different do you mean actually different data in each set, or the same set in a different order?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-11 : 04:54:41

Post the code working off temp tables together with code to populate the temp tables giving the feature.
You may find in doing this you find what causes the problem and at least other people can try the same thing on different systems to see if it is a bug that has been fixed.

Try running simpler queries to see if you can isolate the problem and turn off parallelism as that has had a lot of associated bugs.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-13 : 21:02:12

you can change each derived table to a table function and create a stored procedure to select on all functions. then you can debug the stored procedure in query analyzer.
In an interview with Jim Gray http://www.fawcette.com/interviews/gray/default.asp
in the reply to the last question he reveals dirty secret that there was a time (not so many times ago, surprisingly in 1996) that RDBMSes
like SQL Server, Oracle, DB2, Sybase, and Informix often gave wrong answers on complex queries. He adds that nowadays they all have good quality. So trusting this GURU, it is hard to believe that SQL Server
gives a wrong answer can you post me your table structures and some sample of your data in order give a better answer or who knows find another <dirty secret> about SQL Serevr.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-14 : 04:19:50

it is hard to believe that SQL Server gives a wrong answer.

There are still cases where it gives incorrect results.
the replace function will change the size and padding of fields.
the like statement sometimes misbehaves if there are no wildcard characters.

I wouldn't expect problems with simple things as they are well tested but if you do anything out of the ordinary (or use new features) always consider that they may not work.

Leaving a new version a year before using is always a good idea.
v7 didn't work until sp3.
v6 until sp2
i.e. could easily produce incorrect results for fairly simple processes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-11-14 : 06:16:49

See also my experience via the enclosed link....execution #n of the query would affect the results of execution #n+1...even though they were independent queries (just doing SELECT *)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=2144

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-14 : 06:43:55

That was v7sp0.
This query looks like it would create work tables which is where a lot of the problems lie.

This version didn't work properly and would probably be fixed with sp3 when the locking was corrected (and the system was slowed down a lot as a result).
(or was it sp2?)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 11/14/2002 10:13:07

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-14 : 09:46:53

There are still cases where it gives incorrect results.

I would be glad to see some examples of incorrect results using REPLACE, LIKE
or anything else. is it possible for you to do it nr?

thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-14 : 10:12:27

replace
try

select replace(convert(char(10),'QqQqqq '),'q','W') + 'z'
select replace(convert(varchar(10),'QqQqqq '),'q','W') + 'z'

you should get
QWQWWWz
QWQWWW z
may depend on version

replace - think it is a v2000 bug which I don't have here
something like
select *
from sysobjects
where 1 = case when name like 'sysobjects' then 1 else 0 end

doesn't get the match whereas this does
select *
from sysobjects
where 1 = case when name like 'sysobjects%' then 1 else 0 end

It is something to do with like and case statements trying to get an exact match.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-15 : 18:18:25

q and Q will be replaced with W possibly because we usually install
SQL Server in case insensitive mode. have you evere tested it in a
case sensitive installation of SQL Server?

for the two queries

select *
from sysobjects
where 1 = case when name like 'sysobjects' then 1 else 0 end

and

select *
from sysobjects
where 1 = case when name like 'sysobjects%' then 1 else 0 end

both got the match on my system. I tested it on Profesional Win2K and
personal SQL Server 2000 without any service pack.

can you tell me your system specifications?

Edited by - burbakei on 11/15/2002 18:27:26

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-18 : 08:51:34

In the replace it is the spaces that are trimmed from the first query that is the bug - happens on v7 and v2000, I think with all service packs. I've come accross another one where a replace decides to pad a field to 8000 bytes as well - but that was part of a large conatenation with decimal numbers and coalesces and powers so I wasn't sure what actually caused it.

The one with the like statement I think was introduced with v2000. I'll try to find the exact statement soon but it is to do with like and case and no wildcards.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-21 : 10:09:12

you are right. is it possible the next to write things such as this
like:

select '<' + replace(convert(char(100),'QqQqqq '),'q','W') + 'z' + '>'
select '<' + replace(convert(varchar(100),'QqQqqq '),'q','W') + 'z' + '>'

maybe somebody like me be a lazy-eyed person.

and i would be glad to see the like bug soon.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources