SQL Server Query
-
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? Tim -
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? TimWhy 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
-
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? TimThe 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 '!' -
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
Rama Krishna Vavilala wrote:
Why are you not using sp_depends
Because I didn't know about it until now... thank you! :-D
-
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 '!'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...
-
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? TimSubtle Bugs is a forum to post examples of interesting, aggravating and subtle bugs that you've found and fixed. Do not post programming questions in this forum. This forum is purely for amusement and discussions and all actual programming questions will be removed.
-
Subtle Bugs is a forum to post examples of interesting, aggravating and subtle bugs that you've found and fixed. Do not post programming questions in this forum. This forum is purely for amusement and discussions and all actual programming questions will be removed.
Well, since I perceived this as a bug in SQLServer, wrongly it seems... it seemed appropriate to post it here. From the header for the area: post the bugs you've found It was not intended as a programming question, merely an informative message. If the powers-that-be do not like the message, they can remove it. Tim
-
Subtle Bugs is a forum to post examples of interesting, aggravating and subtle bugs that you've found and fixed. Do not post programming questions in this forum. This forum is purely for amusement and discussions and all actual programming questions will be removed.
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
-
Well, since I perceived this as a bug in SQLServer, wrongly it seems... it seemed appropriate to post it here. From the header for the area: post the bugs you've found It was not intended as a programming question, merely an informative message. If the powers-that-be do not like the message, they can remove it. Tim
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.
-
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.
And if anyone is reading this thread, they have a partial solution... To quote myself: if the powers-that-be do not like the message, they can remove it.
-
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