SQL Server Query
-
Actually, it is a subtle bug, see Rob's reply.
Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. -Brian Kernighan
I don't think that it's very subtle. I knew what the bug was before I even looked at the code in the post. Using underscore as a wild card character is not very well hidden in the documentation either. Wherever you look up the like operator in MSDN, it's mentioned. If one looked up that percent can be used as a wildcard, it would be hard to miss the other wildcard character.
--- b { font-weight: normal; }
-
But and fixed is in bold. You hadn't fixed the bug, you didn't even know the bug was your fault, you thought it was in SQL, you were looking for (and received) help solving the problem. Now that you have the solution you should post it.
It was a team effort. Tim supplied the bug, I supplied a fix. ;P
-
I don't think that it's very subtle. I knew what the bug was before I even looked at the code in the post. Using underscore as a wild card character is not very well hidden in the documentation either. Wherever you look up the like operator in MSDN, it's mentioned. If one looked up that percent can be used as a wildcard, it would be hard to miss the other wildcard character.
--- b { font-weight: normal; }
Guffa wrote:
Using underscore as a wild card character is not very well hidden in the documentation either. Wherever you look up the like operator in MSDN, it's mentioned. If one looked up that percent can be used as a wildcard, it would be hard to miss the other wildcard character.
you explained why its subtle. :)
-Prakash
-
Why are you not using sp_depends[^]?
Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. -Brian Kernighan
Another reason I can't use sp_depends, is that there are a number of stored procedures that build query strings dynamically or having them otherwise wrapped in an 'EXEC' statement. Will sp_depends find instances of tables so enclosed? Besides stored procedures, I also need to search jobs (understanding that the tables will be different); the search problem would be the same.
-
I don't think that it's very subtle. I knew what the bug was before I even looked at the code in the post. Using underscore as a wild card character is not very well hidden in the documentation either. Wherever you look up the like operator in MSDN, it's mentioned. If one looked up that percent can be used as a wildcard, it would be hard to miss the other wildcard character.
--- b { font-weight: normal; }
If you read the rest of my posts, you would see that I regulary have to switch between multiple database systems. Of these systems, using a '%' character as a multi-character wildcard seems to be common, however, I am used to using a '?' character as a single character wildcard. Hence the confusion on my part. Since we do not always read all documentation available, what was subtle to one may be glaringly obvious to others.
-
The underscore character is also a wildcard that matches any single character in a "like" query. So it's you... try
SELECT o.name, c.number, c.text FROM sysobjects AS o INNER JOIN syscomments AS c ON o.id = c.id WHERE (o.xtype = 'P') and c.text like '%supplier!_price%' order by 1 ESCAPE '!'Rob Graham wrote:
The underscore character is also a wildcard
:wtf: that's just plain fncked up.
-
Guffa wrote:
Using underscore as a wild card character is not very well hidden in the documentation either. Wherever you look up the like operator in MSDN, it's mentioned. If one looked up that percent can be used as a wildcard, it would be hard to miss the other wildcard character.
you explained why its subtle. :)
-Prakash
-
If you read the rest of my posts, you would see that I regulary have to switch between multiple database systems. Of these systems, using a '%' character as a multi-character wildcard seems to be common, however, I am used to using a '?' character as a single character wildcard. Hence the confusion on my part. Since we do not always read all documentation available, what was subtle to one may be glaringly obvious to others.
I see. So when the code doesn't work the way you expect, what do you do? Do you think: "Hm... this command doesn't work the way that I expect, perhaps I should look it up in the documentation to see how it really works."? No. Of course not. As you can not possibly be wrong, the logical conclusion is of course that there is a bug in the database software. ;)
--- b { font-weight: normal; }
-
I see. So when the code doesn't work the way you expect, what do you do? Do you think: "Hm... this command doesn't work the way that I expect, perhaps I should look it up in the documentation to see how it really works."? No. Of course not. As you can not possibly be wrong, the logical conclusion is of course that there is a bug in the database software. ;)
--- b { font-weight: normal; }
Guffa,I think you're being a bit padantic here. In the original post, Tim did allow for it to be his error rather than a bug. I would argue that even if one did read the docs (and BOL is only marginally better than MSDN), given the context of the _, it would be very easy to overlook it's meaning, and be confused by the results. The "bug is just like if(x=1) instead of if(x==1), both are a case of overooking an "obvious" error because of the context of the error...:rose:
-
Mr.Prakash wrote:
you explained why its subtle.
So if the documentation clearly describes how something works, and you use it wrong anyway, it's subtle?
--- b { font-weight: normal; }
no it was not the wrong usage, else it would have given compiler error or sql error. he missed the understanding of _ for a moment.
-Prakash
-
Rob Graham wrote:
The underscore character is also a wildcard
:wtf: that's just plain fncked up.
Maybe so, but is been that way since the earliest versions of SQL (and Sybase before that). Also applies to Microsoft Access, and is ANSI 92 standard [^]
-
Guffa,I think you're being a bit padantic here. In the original post, Tim did allow for it to be his error rather than a bug. I would argue that even if one did read the docs (and BOL is only marginally better than MSDN), given the context of the _, it would be very easy to overlook it's meaning, and be confused by the results. The "bug is just like if(x=1) instead of if(x==1), both are a case of overooking an "obvious" error because of the context of the error...:rose:
-
Rob Graham wrote:
Guffa,I think you're being a bit padantic here.
You mean pedantic. ;)
--- b { font-weight: normal; }
LOL. I'm temtpted to report the post as abuse, but correcting Rob's spelling of pedantic is too funny. :-D
Chris Meech I am Canadian. [heard in a local bar] I agree with you that my argument is useless. [Red Stateler] Hey, I am part of a special bread, we are called smart people [Captain See Sharp] The zen of the soapbox is hard to attain...[Jörgen Sigvardsson] I wish I could remember what it was like to only have a short term memory.[David Kentley]
-
Rama Krishna Vavilala wrote:
Why are you not using sp_depends
Because I didn't know about it until now... thank you! :-D
sp_depends sucks that's why - it tends to miss things. Your query is better. Your underscore in the query is matching whitespace, but two false-positives isn't so bad.
"Quality Software since 1983!" http://www.smoothjazzy.com/
-
Thank you... I have to regularly switch between SQLServer, Oracle, Ingres, and a third party product, and don't necessary remember all of the nuances of each... Every day we learn something new, we are the richer for it...
I don't know why it can't just use regex. That's bugged me for years.
"Quality Software since 1983!" http://www.smoothjazzy.com/ - see the "Programming" section for (freeware) JazzySiteMaps, a simple application to generate .Net and Google-style sitemaps!
-
Not exactly a subtle bug, but, perhaps a bug in SQLServer... I am trying to track down usage of a table before making changes to the application to ensure I don't break anything else. To find all of the stored procedures that use the table in question, I use the following query:
SELECT o.name, c.number, c.text FROM sysobjects AS o INNER JOIN syscomments AS c ON o.id = c.id WHERE (o.xtype = 'P') and c.text like '%supplier_price%' order by 1The query returns a number of stored procedures, but at least two of them to not have the text 'supplier_price' in them; what they have is 'supplier price'. So... is this a bug in SQLServer, or am I querying incorrectly? TimProbably LIKE behaves like STUFF('%_%') ? That's why probably the query found 'supplier price' - replaced '_' with . Why using spaces in sp anyway? Much better, IMHO, to use first a prefix indicating the area of usage, followed by the purpose of sp, such as: SA_CreateInvoice (SA = SAles) WR_RetrieveItems (WR = WareHouse) PR_DeleteOrder (PR = PRoduction) AC_CreateRegistration (AC = ACcounting) and so on. (Actually, is FT = Fatturazione instead of sales, MG = Magazzino instead of warehouse etc. - I'm working for an italian company :) ) This is the kind of convention we use when naming stored procedures (tables, triggers etc).