September 6, 2011 at 3:06 am
Hi, I am getting a very strange sql server evaluation.
I have a table with bit columns with nulls allowed or not allowed. If I say : Select * from [MyTable] where Booleancolumn = ''
SQL Server will bring back values where Booleancolumn = 0
Check this :
SELECT CASE WHEN Cast(0 AS bit) = ' ' THEN 1 ELSE 0 END
and
SELECT CASE WHEN 0 = '' THEN 1 ELSE 0 END
Both evaluate to true, which should not be the case.
Please help urgently.
September 6, 2011 at 3:54 am
what you are seeing here is the implicit conversion that takes place when comparing different data types.
so in this case an empty string ('') is implicity converted to a 0 so it evaluates as a true.
Also it is worth noting that an empty string does not equal NULL, to do this with your code you will need.
SELECT CASE WHEN Cast(0 AS bit) = NULL THEN 1 ELSE 0 END
September 6, 2011 at 5:44 am
steveb. (9/6/2011)
... an empty string does not equal NULL, to do this with your code you will need.
SELECT CASE WHEN Cast(0 AS bit) = NULL THEN 1 ELSE 0 END
This is confusing, Steve - what are you trying to say? Try this:
SELECT CASE WHEN Cast(NULL AS bit) = NULL THEN 1 ELSE 0 END
SELECT CASE WHEN Cast(NULL AS bit) IS NULL THEN 1 ELSE 0 END
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 6, 2011 at 5:55 am
I had the code backwards, thanks Chris for clearing that up.
September 7, 2011 at 1:56 am
Hi Guys,
I am fine with the implicit conversion,
But if I say the following, I should not be getting records back.
Lets say I have a table called MyTable.
Id INT,
Description varchar(10)
BooleanColumn bit
Select * from [MyTable] where BooleanColumns = ''
or even
Select * from [MyTable] where BooleanColumns = ' '
will bring records back if the BooleanColumn value = 0 (false)
Thanks so much for your quick replies
September 7, 2011 at 2:10 am
andy.fensham (9/7/2011)
But if I say the following, I should not be getting records back.
You should be, because of the conversions. CAST(' ' AS BIT) = 0
Lets say I have a table called MyTable.
Id INT,
Description varchar(10)
BooleanColumn bit
Select * from [MyTable] where BooleanColumns = ''
or even
Select * from [MyTable] where BooleanColumns = ' '
will bring records back if the BooleanColumn value = 0 (false)
Yes, it will. Because SQL can't compare a boolean and a string, so it has to convert the string to bit first.
SELECT CAST(' ' AS bit)
That returns 0, so the string, when converted to bit does match rows where the bit column is 0.
SQL has bit columns, they're numeric type columns, not boolean. 0 is not automatically false, it's just 0. If I wanted, I could use 0 for true and 1 for false in an app.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2011 at 2:26 am
andy.fensham (9/7/2011)
...I am fine with the implicit conversion...
The implicit conversion of an empty string to a BIT datatype value 0 is still causing you some confusion - I'd suggest you're not fine with it! There are some datatype conversions which, when performed implicitly, can cause confusion and unintended results. This is one of them. Conversion between string datatypes and date/time datatypes is another. If you are unsure of the consequences on an implicit conversion, then don't rely on it - use an explicit conversion.
DECLARE @MatchVariable VARCHAR(5)
SET @MatchVariable = ''
Select * from [MyTable] where BooleanColumns = CAST(CASE
WHEN @MatchVariable = '1' THEN 1
WHEN @MatchVariable = '0' THEN 0
ELSE NULL END AS BIT)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 7, 2011 at 3:54 am
Out of interest, what are you *expecting* to get back when you compare a Boolean column to an empty string?
September 7, 2011 at 4:01 am
I think the confusion lies in assuming that a bit data type = a boolean data type which is not the case, as SQL server does not handle boolean data types.
September 8, 2011 at 9:04 am
I agree with the implicit conversion,
so 0 is equal to empty string
but 0 should not be equal to a string with 10 spaces, so I find the implicit conversion not very consistent.
We have never before compared different data types...we would always explicitly convert...I was just surprised with the results.
Thanks so much for the help everyone,
Andy
September 8, 2011 at 9:18 am
andy.fensham (9/8/2011)
but 0 should not be equal to a string with 10 spaces, so I find the implicit conversion not very consistent.
As far as SQL is concerned, a string with 10 spaces is an empty string. SQL always ignores trailing spaces when converting or comparing strings (ANSI rules). It has to, to be able to sensible compare columns with different length char types.
If it shouldn't be 0, then what should it be? The only thing that converts to 1 is the string '1' (with or without trailing spaces) and a bit has no other possible values, so what should the string with 10 spaces be? A data type conversion error (that's the only option other than 0 or 1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2011 at 11:55 pm
Valid point....agree
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy