Bug found in SQL Server 2000 - 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)

Bug found in SQL Server 2000

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 11:16:54

I have found a strange behaviour in SQL Server 2000 Service Pack 4.

Consider ths query

-- Query 1
SELECT	Number,
	LEN(Pattern) AS Len,
	DATALENGTH(Pattern) AS Datalength,
	'_' + Pattern + '_' AS Output,
	Pattern,
	CHARINDEX(Pattern, 'This is the beginning of the alphabet "abcd"') AS Position
FROM	(
		SELECT	Number,
			CASE Number
				WHEN 5 THEN '<unknown>'
				WHEN 1 THEN '1'
				WHEN 3 THEN '0'
				WHEN 2 THEN 'ab'
				ELSE 'abcd'
			END AS Pattern
		FROM	master..spt_values
		WHERE	Type = 'p'
			AND Number < 4
	) AS d

This trivial query will yield same result both for SQL Server 2000 and SQL Server 2005

Number	Len	Datalen	Output	Pattern	Pos
------	------	------	------	------	------	
0	4	4	_abcd_	abcd	40
1	1	1	_1_	1	0
2	2	2	_ab_	ab	34
3	1	1	_0_	0	0

Then I decided to replace the 0 (zero) replacement with a CHAR statement like this

-- Query 2
SELECT	Number,
	LEN(Pattern) AS Len,
	DATALENGTH(Pattern) AS Datalen,
	'_' + Pattern + '_' AS Output,
	Pattern,
	CHARINDEX(Pattern, 'This is the beginning of the alphabet "abcd"') AS Pos
FROM	(
		SELECT	Number,
			CASE Number
				WHEN 5 THEN '<unknown>'
				WHEN 1 THEN '1'
				WHEN 3 THEN CHAR(48)
				WHEN 2 THEN 'ab'
				ELSE 'abcd'
			END AS Pattern
		FROM	master..spt_values
		WHERE	Type = 'p'
			AND Number < 4
	) AS d

When run on SQL Server 2005, the output is identical so the bug is fixed there.
But... When run under SQL Server 2000, the output looks like

Number	Len	Datalen	Output		Pattern	Pos
------	------	------	------------	------	------	
0	4	9	_abcd     _	abcd     0
1	1	9	_1        _	1        0
2	2	9	_ab       _	ab       0
3	1	9	_0        _	0        0

Now the Pattern is a CHAR datatype!

I can verify this by setting the current SQL Server 2005 database in compatibility mode 80, and the results are as run on SQL Server 2000.

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-14 : 13:48:17

Yeah I get the same result on my 2000 box. I would assume it is because the return type of the CHAR function is CHAR(1) so everything gets converted to a CHAR(9) so it can hold "<unknown>". A bit funky though. :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-15 : 03:22:45

I reported it to Connect here https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=344019
and got a workaround.

In our live code (which is not this simple as the example above), we just added

SET @Pattern = RTRIM(@Pattern)

after the CASE statement.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources