September 7, 2012 at 1:06 pm
Andrew-495157 (9/7/2012)
@SSCertifiableThere is a consistency within SQL issue here which I don't understand
Why did the conversion NEVER fail with the value 'AB' is in the column?
This value has been there since the table was populated - it was part of some test data
The value 'August 2012' was inserted recently in a new row - and the conversion promptly failed when the SP was next run
I would appreciate your explantion of this?
PS Row-by-row for clarity of explanation, not as a mechanism.
I was working on a proof and I think I have an answer to your question. Are you by chance doing a select top x???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 7, 2012 at 1:09 pm
Andrew-495157 (9/7/2012)
@SSCertifiableThere is a consistency within SQL issue here which I don't understand
Why did the conversion NEVER fail with the value 'AB' is in the column?
This value has been there since the table was populated - it was part of some test data
The value 'August 2012' was inserted recently in a new row - and the conversion promptly failed when the SP was next run
I would appreciate your explantion of this?
PS Row-by-row for clarity of explanation, not as a mechanism.
I'm going to have to ask you to show me as the following code snippit fails with the same error you reported above:
DECLARE @v1 NVARCHAR(10) = 'AB',
@v2 INT = 10;
SELECT 1 WHERE @v1 = @v2;
September 7, 2012 at 1:26 pm
Sean Lange (9/7/2012)
I think is the one Lynn was thinking of.http://msdn.microsoft.com/en-us/library/ms190309.aspx
Implicit conversions, which is what you have when datatypes don't match (like int and varchar) will always attempt to use the higher datatype in precedence. Look at the list and you will notice that int is much than varchar. This is one of the reasons you should not allow implicit conversions. If you want to compare to a varchar value you should make your parameter's datatype match.
Luis Cazares (9/7/2012)
Lynn Pettis (9/7/2012)
Andrew-495157 (9/7/2012)
Yes - I understand conversion and castingI wonder if you would re-read my previous post? To confirm what is in it.
What is to re-read? The conversion by SQL Server was from nvarchar to int, that's why you got the error.
There is a hierarchy to the datatypes which SQL Server uses when doing implicit conversions. Unfortunately I am having problems with my google-fu in trying to find the appropriate reference in Books Online to provide that information to you.
To help Lynn with the explanation, here's the reference to BOL http://msdn.microsoft.com/en-us/library/ms190309.aspx
Read carefully as it might be confusing (highest precedence is lowest value).
I want to thank you both for finding this reference.
September 7, 2012 at 1:43 pm
I can not only replicate this I can explain why it is happening. It is a bit strange but is further proof that implicit conversions should be avoided. When selecting top x the optimizer will stop processing when it has enough rows to satisfy the top condition or it runs out of rows, whichever comes first.
This will provide absolute proof that it is in fact the implicit conversion that is causing your issue.
This discussion would not be complete without discussing how order by can affect your query. In fact the point of this exercise is to NOT use order by and let the query engine figure out what order it wants to give us the rows. This is crucial to demonstrate the issue at hand. If you want to have a longer discussion about order by we can do that but for this example the table is small so the engine will most likely return the rows in the order they were inserted. Keep in mind that if we introduce order by to this query it will always fail.
if exists(select * from sys.objects where object_id = object_id('MyTable'))
drop table MyTable
create table MyTable
(
SomeKey int identity primary key,
SomeValue varchar(10)
)
--please note the order of insertion is critical in demonstrating this behavior.
insert MyTable
select '44' union all
select '10' union all
select 'AB' union all
select '33'
--this one will work because we have the implicit conversion of 10 to a varchar datatype.
--This is pretty obvious this will work but we will leave it in as a mark of completeness.
declare @MyVarchar varchar(10) = 10
select * from MyTable where SomeValue = @MyVarchar
--this one WILL work, there will be people that will disagree. This shouldn't work.
--Keep in mind how the engine retrieves rows. We are looking for the row with the value 10.
--If we did not use top 1 this would return the row with a value of 10 and throw an exception on the next row.
--Wait a minute...what did I just say? This query would in fact return a partial resultset if you remove the top 1.
--Try it on your own
declare @MyInt int = 10
select top 1 * from MyTable where SomeValue = @MyInt
--now we take out the top 1. This will crash because it tries the implicit conversion on EVERY row.
--I wrapped this up in a try catch so the script will continue with the rest of the explanation when this bombs.
--Let's change to 44 for clarity.
--Look at the results here...
set @MyInt = 44
select * from MyTable where SomeValue = @MyInt
--just to demonstrate the order the engine will return our rows
select * from MyTable
go
print 'This is now a new batch'
--Now we want to get a row that was inserted BEFORE our search value.
--To accomplish this let us now search for 33
declare @MyInt int = 33
select top 1 * from MyTable where SomeValue = @MyInt
Holy cow!!! It crashed!!! It can't convert the value 'AB' to an int????
Remember the logic for top. It looked through rows 44 and 10 doing an implicit conversion of SomeValue to an int hoping to find 33.
Then it crashed on 'AB'.
Morale of the story. Do NOT allow for implicit conversion of datatypes, especially when dealing with datatypes that are not very flexible (int, datetime, etc)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 7, 2012 at 2:30 pm
Thanks everyone.
Putting the peices together I think the answer is this:
The fragment of the SP causing the error is this:
SELECT Job_Bag
FROM Job_Bag
WHERE Job_Bag.Description_Reference = @OrderID
ANDJob_Bag.Cust_Order_No = @OrderReference
* Cust_Order_No (and so @OrderReference) is not unique
* It so happens that the SP has never been run on the test database with a value for @OrderReference with the same value as that in the row with the Description_Reference value 'AB'
* When the SP was run with a value of @OrderReference, let us say "X", which happened not to be in the Production database - the SP inserted a row with value "August 2012" for Description_Reference
* The next time the SP ran with a value of @OrderReference = "X" the conversion error occurred
This would imply that when executing the code SQL first applied theJob_Bag.Cust_Order_No = @OrderReference
clause, and then the Description_Reference = @OrderID clause. Otherwise the "AB" value would always have caused the error.
I don't know enough detail about SQL query plans to say that this would always be the case.
However I think the principle behind how SQL creates and stores query plans means that the clauses could be applied in either order depending on a number of factors.
I am glad to say that I did not create the SP (or the table).
As a .Net developer I believe in Strong Typing and wish that SQL 2008 did too...
September 7, 2012 at 2:55 pm
Andrew-495157 (9/7/2012)
Thanks everyone.Putting the peices together I think the answer is this:
The fragment of the SP causing the error is this:
SELECT Job_Bag
FROM Job_Bag
WHERE Job_Bag.Description_Reference = @OrderID
ANDJob_Bag.Cust_Order_No = @OrderReference
* Cust_Order_No (and so @OrderReference) is not unique
* It so happens that the SP has never been run on the test database with a value for @OrderReference with the same value as that in the row with the Description_Reference value 'AB'
* When the SP was run with a value of @OrderReference, let us say "X", which happened not to be in the Production database - the SP inserted a row with value "August 2012" for Description_Reference
* The next time the SP ran with a value of @OrderReference = "X" the conversion error occurred
This would imply that when executing the code SQL first applied theJob_Bag.Cust_Order_No = @OrderReference
clause, and then the Description_Reference = @OrderID clause. Otherwise the "AB" value would always have caused the error.
I don't know enough detail about SQL query plans to say that this would always be the case.
However I think the principle behind how SQL creates and stores query plans means that the clauses could be applied in either order depending on a number of factors.
I am glad to say that I did not create the SP (or the table).
As a .Net developer I believe in Strong Typing and wish that SQL 2008 did too...
So the easy solution is to declare your parameter with the same datatype as the column you are trying to use in your comparison.
I can say it until I am blue in the face but the execution plan has absolutely nothing to do with this at all. Your code has a bug in it because of a datatype mismatch. That is not a fault of sql server. It is a fault of the developer of the proc.
I know what you mean about strong typing but there are also some implicit conversions that happen in .NET with strings.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 7, 2012 at 4:37 pm
The problem with the SP code is indeed the reliance on SQL implicit conversion. Easily fixed. Easily undersood.
The interesting issue is why, like many defects, it only emerged after the code apparently ran OK for a long time.
And how tricky it was to identify (the SP is a lot longer than the code fragment)
And the explanation for that is, I hope, the one I gave
And the reason for the explanation is the way SQL executes the code.
Like many defects - the learning is not in the defect itself or fixing it - which is usually trivial.
The learning is a better insight into the tools used - in this case SQL.
Anyhow - I await your explanation as to why the SP ran correctly for a long time - and suddenly failed.
September 7, 2012 at 4:55 pm
" There are also implicit conversions in .net"
Assuming you don't mean VB.net Option Explicit, or the Implicit keyword.
And ingoring the VAR debate.....
Can you give some examples of implicit conversions in .Net?
September 7, 2012 at 5:46 pm
Andrew-495157 (9/7/2012)
The problem with the SP code is indeed the reliance on SQL implicit conversion. Easily fixed. Easily undersood.The interesting issue is why, like many defects, it only emerged after the code apparently ran OK for a long time.
And how tricky it was to identify (the SP is a lot longer than the code fragment)
And the explanation for that is, I hope, the one I gave
And the reason for the explanation is the way SQL executes the code.
Like many defects - the learning is not in the defect itself or fixing it - which is usually trivial.
The learning is a better insight into the tools used - in this case SQL.
Anyhow - I await your explanation as to why the SP ran correctly for a long time - and suddenly failed.
It isn't a defect. The reason the code ran for so long was that the implicit conversion from nvarchar to int was working until it hit the data (the string 'August 2012') that caused the implicit data conversion to fail.
If you want to say there was a defect, the defect was the person(s) that wrote the procedure with the implicit data conversion.
September 7, 2012 at 5:50 pm
As I mentioned before there was already the value "AB" in a row in the table which cannot be converted to a string.
So one might expect the code to fail every time.
September 7, 2012 at 6:18 pm
Andrew-495157 (9/7/2012)
As I mentioned before there was already the value "AB" in a row in the table which cannot be converted to a string.So one might expect the code to fail every time.
Obviously that piece of data was never returned during processing or it would have.
September 7, 2012 at 6:23 pm
Run the following and post the results:
SELECT
Job_Bag.Description_Reference,
Job_Bag.Cust_Order_No
FROM
Job_Bag
WHERE
Job_Bag.Description_Reference = 'August 2012' or
Job_Bag.Description_Reference = 'AB';
September 8, 2012 at 10:00 am
Hi Lynn
Sorry for delay in replying - beautiful sunny Saturday here in the UK
The result of the "OR" code is that a resultset containg all, and only, the rows with either the Description_Reference 'August 2012' or the Description_Reference 'AB'
Which is what I would expect.
I am pretty clear now on the defect, and why the defect took so long to show up.
As you may have gathered the value 'August 2012' was not ever suppsed to occur - all the the Description_Reference values in ther original real life domain would have been textual-numeric.
Unfortunately real life changed...
The SPs are quite ancient - and the search is now on for any other implicit conversions in them.
As I sald earlier the good thing about defects is that you learn more about the tools you use.
In my case I unlearnt something I "knew" - that in a WHERE clause SQL carries out a conversion of a search condition before comparing values with column data.
This turns out to be untrue.
You might have though the 'now unlearned way' more efficent as the conversion would be deteminstically done once for each search condition requirng implicit conversion. Whereas SQL actually non-determinsitcally carries out n conversions (depending how many column values can convert before a non-convertable value is hit).
Etiher way can cause issues. So as ever - Strongly Type and Strongly Test is the name of the game.
September 8, 2012 at 2:01 pm
I asked for the results of the query, where is it?
Are you afraid to post the results?
September 8, 2012 at 2:14 pm
I don't understand.
I described EXACTLY the results.
Not sure how else to post them?
I
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply