SQL Server Performance Forum – Threads Archive

Incomprehensible SELECT

Hey guys,
here is the astonishing behaviour of a query that returns the same records whatever be it the WHERE clause … It is a simple table with "A" being "int NULL" no default value, no indexes, database just dbcc checkdb-ed. Compatibility level is 8.0, and the connection from QA is, as usual, with the set_ansi_defaults, and concat_nulls_yields_null doesn’t to affect. select A
from myTable
where A=0 result is the same as select A
from myTable
where A=” the same result as select A
from myTable
where A=’ ‘ … same result as …..! select A
from myTable
where A=’ ‘ but obviously different (thanks God) from select A
from myTable
where A IS NULL Am I becoming stupid by the second? Or that’s just the way it should behave??

Does any of the queries where you compare A to something other than Null return any records? If the A field on all rows is NULL, then no rows should be returned. In that case, when you do WHERE A IS NULL, then the query should return all rows. I just tested, and you will not get an error comparing an INT field to an zero-length string, or to a string of blanks. This may be dependent on your ANSI NULLS setting – sorry but I don’t know the details. This does not depend on the nullability of the field: I tested against an identity field and the query just returned no rows, without error.

Hi Adriaan
the problem is that ALL queries -except the last one- return all records with 0 in that field (A) All of them behave the same way!

Can you post DDL and data to reproduce? ———————–
–Frank
http://www.insidesql.de
———————–

I ran some tests, and it does look as if an INT that on the surface has a value of zero does satisfy both zero-length string and blank string criteria (Win2000, SQL 2K, SP3). However, if you use SELECT * FROM MyTable WHERE A = CAST(” as INT), no more rows should be returned. You could call this a bug, but why are you comparing an INT field with a string value in the first place?

Adriaan, you are right and your solution is great I think if confirmed that would be a MAJOR bug or at least a major "NON-EXPECTED" behaviour of SQL Server … Firstly, usually its not my issue to develop the SELECTs (this time it was one of the developers here who came up with it) and I have no control over all SELECTs developed here. We are running a business which deals with databases with compatibility level 6.5 and some with 8.0 (loooong story). Problems with "blanks", "nulls", "zeroes" and so on, are common rule when switchin between compatibility levels.( 8.0, sp3a) So, I understand the developer issuing a SELECT that wanted to catch up all ” or 0 or ‘ ‘ different records. As it is an INT field, the conditions with ” would not be in order, but let’s suppose he was not knowing exactly the type of that particular field. Secondly, what I found really strange -and astonishing- is ::
a) the query is well formed -no syntax error, of course- , havent tried to save it into a stored procedure (syntax and type casting then would be checked, I guess)
b) if you set WHERE A= ‘ ‘ (any "decent" length of blanks here) it works! and additionally it returns the SAME records that accomplish the rule WHERE A=0 !!! (u can run BOTH queries and get the same result) Frank, u can try by yourself, set a table, one "INT" field NULL and fill it up with 0’s, then query it the way I did it here. I have tried that with different tables/different databases and it works just the same …

You had better take control over the rules that the developers in your situation have to abide with. There’s just no excuse for not using the proper data types, and you see here what ‘unexpected’ behaviour you get in that scenario – this behaviour isn’t unexpected at all. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] The fact that you can silently compare an INT value against a string is perhaps a liability that Microsoft should remove from SQL Server. But then again they’re not typing anything in ASP, at least as far as I can see from what others are woking on, so it’s up to us to uphold some standards.

I don’t think that a bug. See what this produces
select
cast(‘ ‘ as int) ———–
0 (1 row(s) affected)
And the explanation to that miracle can be found documented in BOL when you read up on data type precedence rules. Varchar has a lower precendence and thus will be converted to int. That in turn means that all but the last query are treated equally. That is
select A
from myTable
where A=0
———————–
–Frank
http://www.insidesql.de
———————–

…sorry, hit some button too early. Still Adriaan’s question remains valid why your developer compares numerical data to strings? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————–

Well, one of those "silly" rules of Mr. Murphy was something like ::
"The solution of a problem changes the problem" … Actually, here we were discussing problem A which was "unexpected behaviour", "bug", "astonished user (and DBA)" or whatever you want to call it … Problem B "the changed-by-solution problem" is like "why your developer compares numerical data to strings?" (sic) Let’s don’t hide problem A trying to redirect it to problem B. To me, sincerely, is a very WEIRD way to behave, sincerely. I like SQL Server but not to the point of being BLIND to those "to me" issues … I might have "weird" developers who up to a point can miss field definition, that’s another subject. And another subject would be to have control over "ALL" selects issued in my company (which are only checked when they have problems, the common rule … ) though I’d like to! I agree: all developers should know exactly the data they are dealing with. So far, I insist, that would be another point. But this "automatic cast with no warning" from the SQL side is really a weak point.

Okay, trying to answer Problem A (the explanation wasn’t quite right): Are you familiar with Excel? It’s quite the same in Excel. An empty cell in Excel will convert to a 0. That is, I guess, because of keeping some kind of clarity on a worksheet while ensuring that complex calculations won’t fail because of a missing numeric value in a cell. So, what would you expect to be returned by your query? A data type conversion error? Hm, while this might be the most logical to happen, it doesn’t. And it doesn’t because Microsoft choose to implement it that way. Btw, it does not only work with INT, but also with FLOAT or REAL <pre> SELECT CAST(” AS FLOAT), CAST(” AS REAL) —————————————————– ———————— 0.0 0.0 (1 row(s) affected) </pre> But not with DECIMAL and NUMERIC <pre> SELECT CAST(” AS DECIMAL(8,2)), CAST(” AS NUMERIC(8,2)) Server: Nachr.-Nr. 8114, Schweregrad 16, Status 5, Zeile 2 Fehler beim Konvertieren von Datentyp varchar in numeric. </pre> So, I must correct myself. The main reason is that it is by design. The data type precedence rules might come into play or not, who knows. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Sad, but true. ———————– –Frank http://www.insidesql.de</a> ———————–

What I do expect, actually is something like this ::
——————————————————————————–
Servidor: mensaje 245, nivel 16, estado 1, lÃnea 1 (spanish version, sorry)
Syntax error converting the varchar value ‘you are not working’ to a column of data type int.
——————————————————————————– which is EXACTLY what happens when I issue the query with this WHERE :: —————-
select A
from myTable
where A = ‘you are not working’
—————— In terms of DATA TYPE -and this is my point- what is the difference between this string : ‘ ‘ (some blanks here) and this one : ‘you are not working’ ???????
I think this is crucial to bring some light to this discussion …

Strange …… I am reviewing my original posts (I work with HTML OFF) and I see the following ….. When I write ‘ followed by "any given quantity of blanks" and ‘ again, it collapses to ” (no blanks displayed between ‘ ‘ …. so, ” looks the same as ‘ ‘ (any quantity of blanks here, I guess HTML is collapsing this … as a result, the original post has 2 selects that look the same …… when, theoretically they should look like this :: ————————————–
select A
from myTable
where A=” … same result as …..! select A
from myTable
where A=’__________’ (assuming _ as a blank space) ————————————— So, blank spaces are not displayed here either as they should be: tricky characters -and ironically- when they are the main subject -probably- of this topic.

It doesn’t matter whether you have 0, 1 or 8,000 blanks. I will always convert to 0. Try it out!
However, once you type in just one single letter, it isn’t an empty string anymore and thus you’ll get this 8114 error. ———————–
–Frank
http://www.insidesql.de
———————–

Damn. wanted to post this one, too
select cast(replicate(‘ ‘,7999)+” as int)
select cast(replicate(‘ ‘,7999)+’A’ as int) ———–
0 (1 row(s) affected) Server: Nachr.-Nr. 245, Schweregrad 16, Status 1, Zeile 2
Syntaxfehler beim Konvertieren des varchar-Wertes ‘… ———————–
–Frank
http://www.insidesql.de
———————–

Yep, that seems to be the rule of "automatic conversion" Frank, when u said "It doesn’t matter whether you have 0, 1 or 8,000 blanks. I will always convert to 0." I assume u wanted to say "It will always convert …" or will you do it for me and my "silly developers" ? <img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />

…. and even funnier (to me) is that :: select A
from myTable
where A=’___00000000000000_______’ (keep assuming _ for blank spaces) or select A
from myTable
where A=’0′ return the same result …. so, there is an automatic casting here as well, as ‘1’ and 1 will work just the same select A
from myTable
where A=’1′ select A
from myTable
where A=1 sorry for being so fussy!!

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by wabbash Yep, that seems to be the rule of "automatic conversion" Frank, when u said "It doesn’t matter whether you have 0, 1 or 8,000 blanks. I will always convert to 0." I assume u wanted to say "It will always convert …" or will you do it for me and my "silly developers" ? <img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ /> <hr height="1" noshade id="quote"></blockquote id="quote"> A silly typo. Nope, I won’t do your homework for you [<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————–

quote:Originally posted by wabbash …. and even funnier (to me) is that ::
return the same result …. so, there is an automatic casting here as well, as ‘1’ and 1 will work just the same select A
from myTable
where A=’1′ select A
from myTable
where A=1

That’s right, but now data type precedence rules definitely come into play.
The ‘1’ can implicitely and explicitely be converted to an integer. ———————–
–Frank
http://www.insidesql.de
———————–

Thanks Frank, I sincerely appreciate your kind effort. I will try to convince "my ( ) developers" -fill the ( ) with the appropiate adjective that suit your work environment- with some funny explanation like "You know the HOLY GHOST is behind that door, so don’t open it!" … and will write some kind of info paper with the "data type precedence rules and other related herbs" which sometimes can lead to confusion -at least from the side of this silly DBA and his ( ) fellows- <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />)

Haha, after all SQL Server is still a MS product. So, why do you expect it to behave rational? [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————–

Maybe to finish this, take a look at BOL for CAST and CONVERT. There you can read:

quote:
SQL Server returns an error message when non-numeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal.

Well, getting sophisticated, it does NOT say, that it returns an error when an empty string is converted to anything numerical less than DECIMAL or NUMERIC, which is what we have seen above.
Somehow q.e.d.
———————–
–Frank
http://www.insidesql.de
———————–

"…. after all SQL Server is still a MS product. So, why do you expect it to behave rational? " No no no no, don’t start with this, it would lead me to win the September, October and November Forum contest with my silly posts and, so far, I am a bit embarrassed with that possibility!

About data types, well I was looking at the code behind some ASP pages just a couple of hours ago, and you know what – I don’t ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever (repeat when necessary) want to work in ASP.

And by the way, SELECT ISNUMERIC(”) returns 0, not 1. On the one hand that is correct but inconsistent, on the other hand it’s something I can live with.

Just for curiosity, try this
SELECT CAST(” AS DATETIME) ———————–
–Frank
http://www.insidesql.de
———————–

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Just for curiosity, try this <pre> SELECT CAST(” AS DATETIME) </pre> ———————– –Frank http://www.insidesql.de</a> ———————– <hr height="1" noshade id="quote"></blockquote id="quote"> Again consistent because these all return the same result: SELECT CAST(” AS DATETIME) SELECT CAST(0 AS DATETIME) SELECT CONVERT(DATETIME, 0) SELECT CONVERT(DATETIME, ”) I’m still not very happy, how are you? [<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />]

Actually it would have been better to raise an error than silently convert to 0. But then again, I’m somewhat used to this behaviour because I do very much with Excel. ———————–
–Frank
http://www.insidesql.de
———————–

I do a lot in Access and I get errors all the time if I’m not careful. You can greatly reduce the number of errors by using Variant type variables, but then you can only keep your fingers crossed that the results are anywhere near predictable. We do lots of calculations on dates, and you really do not want to use a Variant there. Now if only the Date type variable in VBA would accept Nulls, that would take away a lot of the work-arounds we have to write. That and Jet handling dates as regionally formatted dates on query statements, without having to re-format it into MM/DD/YYYY. I really shudder to think how we’re ever going to cope with stored procedures in .NET – let’s hope it is at least more predictable than all this.

quote:Originally posted by Adriaan I do a lot in Access and I get errors all the time if I’m not careful. You can greatly reduce the number of errors by using Variant type variables, but then you can only keep your fingers crossed that the results are anywhere near predictable. We do lots of calculations on dates, and you really do not want to use a Variant there.

I would only rely on integer operations.

quote:
I really shudder to think how we’re ever going to cope with stored procedures in .NET – let’s hope it is at least more predictable than all this.

Are you serious?
I fear the implementation of the CLR in SQL Server 2005 will make things more worse. Think of all those newbies with a procedural language background who now don’t need to learn the SQL set-, or multibag-based approach, but rather can simply go on with their procedural thinking and programm SQL Server. Not a nice vision, if you ask me. [V] Just my $0,02 cents. ———————–
–Frank
http://www.insidesql.de
———————–

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan I do a lot in Access and I get errors all the time if I’m not careful. You can greatly reduce the number of errors by using Variant type variables, but then you can only keep your fingers crossed that the results are anywhere near predictable. We do lots of calculations on dates, and you really do not want to use a Variant there. <hr height="1" noshade id="quote"></blockquote id="quote"> I would only rely on integer operations. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> I really shudder to think how we’re ever going to cope with stored procedures in .NET – let’s hope it is at least more predictable than all this. <hr height="1" noshade id="quote"></blockquote id="quote"> Are you serious? I fear the implementation of the CLR in SQL Server 2005 will make things more worse. Think of all those newbies with a procedural language background who now don’t need to learn the SQL set-, or multibag-based approach, but rather can simply go on with their procedural thinking and programm SQL Server. Not a nice vision, if you ask me. [V] Just my $0,02 cents. ———————– –Frank http://www.insidesql.de</a> ———————– <hr height="1" noshade id="quote"></blockquote id="quote"> Yes, that was putting it backwards, I should have said "… how we’re ever going to cope with .NET in stored procedures …" [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] And you are so right about what procedural programming practices might do to SQL Server systems. If you could see some of the stuff programmers do in VBA that they could do so much more easily in SQL (even in Jet-SQL) …

Hey guys, it looks like you are having fun with ASP, Access, CAST, EXCEL and the best of all statements "Actually it would have been better to raise an error than silently convert to 0 " Hey, Frank you stole my idea! Is it a bug? is it a UFO? is it a SILENT PAIN IN THE ASS?? For those who, LUCKILY, don’t work either with Excel nor ASP BUT sometimes with Access, this is really painful … and yes, Adriaan, variant types are not the best choice …. all the other stuff about "future telling" is just for experts, so far … <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />) keep having fun!

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by wabbash Hey, Frank you stole my idea! Is it a bug? is it a UFO? is it a SILENT PAIN IN THE ASS?? <hr height="1" noshade id="quote"></blockquote id="quote"> It wasn’t your idea. Search the google groups and you’ll find threads back from 2002 and 2003 on this behaviour. [<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————–

Should I find "SILENT PAIN IN THE ASS" with "ASP","Excel" and "CAST" altogether?

http://www.google.de/search?hl=de&ie=UTF-8&q=SILENT+PAIN+IN+THE+ASS&btnG=Google-Suche&meta= http://www.google.de/search?hl=de&ie=UTF-8&q=SILENT+PAIN+IN+THE+ASS+excel&meta=</a> http://www.google.de/search?hl=de&ie=UTF-8&q=SILENT+PAIN+IN+THE+ASS+excel+ASP&meta=</a> http://www.google.de/search?hl=de&ie=UTF-8&q=SILENT+PAIN+IN+THE+ASS+excel+ASP+cast&meta=</a> [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] I was talking about this empty string to 0 conversion. You’re lucky that Brad obviously didn’t turn on the forum bad word filter [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————–

Finally found what I’m looking for (btw, I like the early U2 music [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]) [email protected]>mailto<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />[email protected]</a> ———————– –Frank http://www.insidesql.de</a> ———————–

Frank, for some of us that is the more recent U2 repertoire.

Showing my age ?!? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————–

No, showing MINE.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

Okay, here’s mine. Wrapped for convience 0x293A207373656E697375622072756F7920666F20746F6E2073277 461687420747562202C38363931206E69206E726F62207361772049 Shouldn’t take too long to find out [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————–

SELECT REVERSE(CAST(0x293A207373656E697375622072756F7920666F20746F6E2073277461687420747562202C38363931206E69206E726F62207361772049 AS VARCHAR(100))) tells me you’re 3 years younger than I am, and you’re right: it is probably not my business [8D]

14 minutes only? So it was too easy [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] Btw, that is my profile info on sqlteam.com. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————–

‘ />] Btw, that is my profile info on sqlteam.com. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————– <hr height="1" noshade id="quote"></blockquote id="quote"> Oh you tease, I didn’t see it immediately so it took significantly less than 14 minutes.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />][<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />][}<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />][xx(] Sorry, I’ve never visited sqlteam.com [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

No, I don’t tease.
First time I saw something something like this, it took me much longer to find out. ———————–
–Frank
http://www.insidesql.de
———————–

Well Frank, I’ve been learning a lot here at SQL Server Performance – that might have a thing or two to do with it.

]]>