Data Type Precedence
-
We're running SQL Server 2000 in 6.5 compatibility mode for 5 years now. The main reason was the client (PowerBuilder 6.5) driver performance issues with 8.0 mode. Recently our client application was upgraded to the latest PB version and we decided to change compatibility mode to 8.0 (with plans to upgrade to SQL 2005 in the future). Client application was supposedly thoroughly tested
Yesterday we have set our production SQL server compatibility mode to 8.0 only to turn it off after production batch job have failed.
There is no performance issues associated with 8.0 mode but rather application errors.
The problem arises when implicit conversion from varchar to integer datatype is performed.
Select *
From Table1
Where Col1 = 1
Col1 is varchar(5) and contains numbers and strings
The fix is to run:
Select *
From Table1
Where Col1 = ‘1’
Here is the issue:
SQL 6.5 was fairly strict with type checking.
Specifically, there was no implicit conversion between strings and numbers. Thus any such attempt yielded a compilation error.
Implicit conversions were added in SQL 7. In SQL 7 the rule for these conversions said that a literal was converted to the type of the column.
This was modified in SQL 2000, so that there is now strict data-type precedence. varchar is always converted to integer in a case like this.
Apparently, the 6.5 compatibility mode gave us the SQL 7 behavior, and apparently someone let buggy piece of code to slip into our application after we migrated to SQL 2000.
One solution is to perform a detailed application code review to make sure no implicit data type conversions are used, strings are compared to strings and integers to integers.
Such analyzes can take a long time.
We’re trying to see if we can stay in 8.0 compatibility mode but set the database connection option to give us the SQL 7 behavior in regards to implicit conversion.
Thanks
Ed
-
One solution is to perform a detailed application code review to make sure no implicit data type conversions are used, strings are compared to strings and integers to integers.
Regardless of evironment, this should always be done.
Good coding practices tells you to always be specific and never rely on implicit default behaviours.
As per the example you showed, the first example is 'buggy' due to the fact that the types don't match, where the 2nd example is the correct one. There is no excuse to say that example 1 'used to work' since it's 'wrong' in the first place.
IMO, it's better to bite and do 'the right thing' straight up (ie review and correct implicit code to explicit) than to waste time and energy looking for a workaround that shouldn't be necessary in the first place.
The behaviors you describe is a perfect example of the downside of doing 'lazy coding' - a similar 'surprise' was for those who in 6.5 relied on a GROUP BY to always be sorted in a predictable way without the coder having to specify an ORDER BY, which severely broke in 7.0 when this implicit behaviour drastically changed. The solution in that case was pretty simple - always write what you want should happen (ie ORDER BY), though it involved the same problem you're facing now - review old code and correct 'old habits'.
The point I'm trying to make is that more typing is better than less. Always be explicit.
/Kenneth
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply