views:

26

answers:

2

Hi

I am having an inconsistent issue being produced on one of my servers whereby I have the following

Select * 
from SomeVarcharTable v
join SomeIntTable i on i.MyInt=v.MyVarchar
Where v.Id = SomeID

The "MyVarChar" column is surprisingly of type varchar, and "MyInt" is of type int.

Whats curious is that when I run this on my development instance of Sql 2000, I receive no error.

When I run this on my Live instance of Sql 2000 I receive the following error:

Syntax error converting the varchar value '1.4' to a column of data type int.

Does anyone know of any server settings which might cause this. I have gone so far as to do a restore of my production database to my development system and the error doesn't occur.

I wondered whether it was something to do with the sql query engine, but this was working without an issue a few days ago.

Also, I am aware that this is not best practice, and that I could fix the above query by this:

Select * 
from SomeVarcharTable v
join SomeIntTable i on cast(i.MyInt as varchar)=v.MyVarchar
Where v.Id = SomeID

I am more interested at this stage in wondering why the issue has been caused, so I can educate others in my company

Thanks in advance

Mark.

+3  A: 

I believe, when you are trying to compare a left hand INT value with the right side VARCHAR, the sql tries to implicitly convert the VARCHAR to an INT value and then compare. If it cannot implicitly convert varchar to INT, then it throws the above error which you are encountering.

integer data type has a higher precedence over a varchar data type. Since the integer data type has a higher precedence, the varchar data type is implicitly converted by SQL Server to an integer data type, and not the other way around.

Sachin Shanbhag
This kind of error is another good reason (besides finding out performance issues with queries before moving them to prod) why it is a bad idea to develop against a small test database. You don't know what data is in your prod datbases unless you develop against a database that has been refreshed frequently from prod. Of course just the fact that I wanted to join a varchar to an int would be huge red-flag to me that the database design needed refactoring.
HLGEM
+3  A: 

This is a data issue. The live environment contains data in the varchar column that is not a valid integer, whereas presumably your dev box doesn't and when the join is being done, it is attempting to convert the VARCHAR to an INTEGER to match on the MyInt column. If MyVarchar contains any value that is not a valid integer, then you will get that error.

The issue is you are storing values in VARCHAR that you are treating in your query as being a valid INTEGER. The fact you are storing them in a VARCHAR instead of an INTEGER column, which of course would be the ideal, indicates in your system that they are not intended to always be valid integers and that's a problem.

So, you either need to:
1) change MyVarchar to be an integer column and only allow valid ints to be stored
2) CAST MyInt to a VARCHAR in the join as you are doing (not great for performance)
3) don't try to join when MyVarchar is not a valid integer (equally not great for performance) e.g.

Select * 
from SomeVarcharTable v
join SomeIntTable i on i.MyInt=v.MyVarchar
Where v.Id = SomeID
    AND v.MyVarchar NOT LIKE '%[^0-9]%' -- ignore rows where MyVarchar is not a valid int


4) create a new INTEGER column, alongside MyVarchar, and store the MyVarchar values in there that are valid integers, and use that to join instead. You'd be having some duplicate data, but performance-wise you'd at least be able to index and join on matching types without any workarounds.

Update: Check out this reference on data type precedence. Note INTEGER has a higher precedence than VARCHAR, hence is why VARCHARs are converted to INTEGERs.

AdaTheDev
Thanks for the info. What I dont understand though is SQLs sudden decision to throw a wobbly over it. As I have said before previously this wasnt an issue. Could you think of any server setting that might have caused this. I did wonder if it was statistics telling the query engine to do something different. To clarify I am running the exact same query on my dev and live box with the same data, my live server throws an error, my dev one does not.
harrisonmeister
I can't see how it would differ in behaviour TBH with the same data (I've added a link btw to data type precedence). e.g. Try "SELECT CAST('1.4' AS INTEGER)" on both servers - you should get the same error on both.
AdaTheDev
I just tried `set ansi_warnings off` and `set arithabort off` and neither of them hid the error so I don't think there's any such setting in play.
Martin Smith
I found the issue. Basically Sachin Shanbhag got it right, in that our query was only ever working by chance and choosing to do the implicit conversion of the varchar first. Turns out a Non-Clustered index was created on the SomeIntTable on the MyInt column which made SQL change the ordering of the query, and in turn use the DataType Precedence ordering as described by yourself and others.Just nice to know what caused it, and means I now have something I can do to fix it!
harrisonmeister