SQL "bug"
-
I was having a hard time figuring out why my T-SQL query wasn't working.
DECLARE @billid int, @cli varchar;
SET @billid =12345;
SET @cli = '02077778888';SELECT COUNT(*) FROM Records WHERE BillID = @billid AND CLI = @cli;
This query consistently returned a result of zero records, despite my knowing that records existed for this combination, e.g.:
SELECT COUNT(*) FROM Records WHERE BillID = 12345
AND CLI = '02077778888';would return the correct number. Solution: The length of the varchar had not been specified. Although it parses correctly it doesn't actually work without a length being specified. This simple change fixed things:
DECLARE @billid int, @cli varchar(14);
'Howard
-
I was having a hard time figuring out why my T-SQL query wasn't working.
DECLARE @billid int, @cli varchar;
SET @billid =12345;
SET @cli = '02077778888';SELECT COUNT(*) FROM Records WHERE BillID = @billid AND CLI = @cli;
This query consistently returned a result of zero records, despite my knowing that records existed for this combination, e.g.:
SELECT COUNT(*) FROM Records WHERE BillID = 12345
AND CLI = '02077778888';would return the correct number. Solution: The length of the varchar had not been specified. Although it parses correctly it doesn't actually work without a length being specified. This simple change fixed things:
DECLARE @billid int, @cli varchar(14);
'Howard
I've had something similar happen with some user authentication work I was doing. I was generating an MD5 password hash and sending it to SQL Server 2000 for verification but even though I checked it for two hours, the application would never verify the password correctly even though it worked when I wrote out the SQL statement in Enterprise Manager. It turned out to be essentially a cast error - string literals in SQL statements are considered by SQL2k to be varchars, while my application was passing in an nvarchar field, and the blank Unicode bytes were throwing off the hash comparison.
Imagine that you are hired to build a bridge over a river which gets slightly wider every day; sometimes it shrinks but nobody can predict when. Your client provides no concrete or steel, only timber and cut stone (but they won't tell you what kind). The coefficient of gravity changes randomly from hour to hour, as does the viscosity of air. Your only tools are a hacksaw, a chainsaw, a rubber mallet, and a length of rope. Welcome to my world. -Me explaining my job to an engineer
-
I've had something similar happen with some user authentication work I was doing. I was generating an MD5 password hash and sending it to SQL Server 2000 for verification but even though I checked it for two hours, the application would never verify the password correctly even though it worked when I wrote out the SQL statement in Enterprise Manager. It turned out to be essentially a cast error - string literals in SQL statements are considered by SQL2k to be varchars, while my application was passing in an nvarchar field, and the blank Unicode bytes were throwing off the hash comparison.
Imagine that you are hired to build a bridge over a river which gets slightly wider every day; sometimes it shrinks but nobody can predict when. Your client provides no concrete or steel, only timber and cut stone (but they won't tell you what kind). The coefficient of gravity changes randomly from hour to hour, as does the viscosity of air. Your only tools are a hacksaw, a chainsaw, a rubber mallet, and a length of rope. Welcome to my world. -Me explaining my job to an engineer
Ouch.. at least mine were both varchars. It would be nice if the T-SQL parser would throw out a warning. PS. like the sig.
'Howard
-
Ouch.. at least mine were both varchars. It would be nice if the T-SQL parser would throw out a warning. PS. like the sig.
'Howard
Howard Richards wrote:
PS. like the sig.
I agree. It is right on with what I have to deal with sometimes :)
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
I've had something similar happen with some user authentication work I was doing. I was generating an MD5 password hash and sending it to SQL Server 2000 for verification but even though I checked it for two hours, the application would never verify the password correctly even though it worked when I wrote out the SQL statement in Enterprise Manager. It turned out to be essentially a cast error - string literals in SQL statements are considered by SQL2k to be varchars, while my application was passing in an nvarchar field, and the blank Unicode bytes were throwing off the hash comparison.
Imagine that you are hired to build a bridge over a river which gets slightly wider every day; sometimes it shrinks but nobody can predict when. Your client provides no concrete or steel, only timber and cut stone (but they won't tell you what kind). The coefficient of gravity changes randomly from hour to hour, as does the viscosity of air. Your only tools are a hacksaw, a chainsaw, a rubber mallet, and a length of rope. Welcome to my world. -Me explaining my job to an engineer
Robert Royall wrote:
Imagine that you are hired to build a bridge over a river which gets slightly wider every day; sometimes it shrinks but nobody can predict when. Your client provides no concrete or steel, only timber and cut stone (but they won't tell you what kind). The coefficient of gravity changes randomly from hour to hour, as does the viscosity of air. Your only tools are a hacksaw, a chainsaw, a rubber mallet, and a length of rope. Welcome to my world.
wow man, I do not want to be you :laugh: :laugh:
-
I was having a hard time figuring out why my T-SQL query wasn't working.
DECLARE @billid int, @cli varchar;
SET @billid =12345;
SET @cli = '02077778888';SELECT COUNT(*) FROM Records WHERE BillID = @billid AND CLI = @cli;
This query consistently returned a result of zero records, despite my knowing that records existed for this combination, e.g.:
SELECT COUNT(*) FROM Records WHERE BillID = 12345
AND CLI = '02077778888';would return the correct number. Solution: The length of the varchar had not been specified. Although it parses correctly it doesn't actually work without a length being specified. This simple change fixed things:
DECLARE @billid int, @cli varchar(14);
'Howard
It's not so much the length of the @cli varchar not being implicitly specified, it's the fact that @cli and CLI are different lengths. If you'd changed your second line to SET @cli = '02077778888vvv' (where v=space) it would have worked. I bet your second query wouldn't have worked as written (...WHERE BillID = 12345 AND CLI = '02077778888'). But I bet a generic SELECT (with no WHERE clauses) would have shown a row with (apparently) BillID = 12345 and CLI = '02077778888'. Actually, CLI would have been '020777788888 '
-
It's not so much the length of the @cli varchar not being implicitly specified, it's the fact that @cli and CLI are different lengths. If you'd changed your second line to SET @cli = '02077778888vvv' (where v=space) it would have worked. I bet your second query wouldn't have worked as written (...WHERE BillID = 12345 AND CLI = '02077778888'). But I bet a generic SELECT (with no WHERE clauses) would have shown a row with (apparently) BillID = 12345 and CLI = '02077778888'. Actually, CLI would have been '020777788888 '
Can you explain how '02077778888vvv' would match with the database field value of '02077778888' ?
'Howard
-
Can you explain how '02077778888vvv' would match with the database field value of '02077778888' ?
'Howard
In my previous post, I used 'v' to indicate a space ... a holdover from the old days where spaces were commonly indicated by using a kind of upside-down delta (▼) I'm suggesting that you inserted a row with CLI set to '02077778888' [eg "INSERT INTO Records (BillID, Cli) Values (12345, '02077778888')" ] but, because the CLI column was set to 14 characters, the field was actually stored as '02077778888▼▼▼' (3 spaces tacked on the end) So searching for '02077778888' didn't work, what you needed to do was search for '02077778888▼▼▼', which you did by dimming @cli as varchar(14). Does that make sense? I often get caught out by fields that are right-padded to the cell-width.
-
In my previous post, I used 'v' to indicate a space ... a holdover from the old days where spaces were commonly indicated by using a kind of upside-down delta (▼) I'm suggesting that you inserted a row with CLI set to '02077778888' [eg "INSERT INTO Records (BillID, Cli) Values (12345, '02077778888')" ] but, because the CLI column was set to 14 characters, the field was actually stored as '02077778888▼▼▼' (3 spaces tacked on the end) So searching for '02077778888' didn't work, what you needed to do was search for '02077778888▼▼▼', which you did by dimming @cli as varchar(14). Does that make sense? I often get caught out by fields that are right-padded to the cell-width.
Thanks for the clarification. However I didn't use char - it was always varchar so it would not have spaces appended?
'Howard
-
Thanks for the clarification. However I didn't use char - it was always varchar so it would not have spaces appended?
'Howard
I've seen cases (depending on specific implimentation of SQL) where a supposed varchar will pad. But it's only a suggestion - and easy enough to test, I'd think :)