Solved

Integer has implicit conversion?

Posted on 2016-07-25
19
51 Views
1 Endorsement
Last Modified: 2016-07-27
This is SQL 2014.

I have a table with a column of integer.  I see zeros in it.

I did this and I get the rows with value of zero in that column

select * from loads 
where SettledWeight = 0 

Open in new window


I did this and I get the same values even tho that field is all zero. My coworker says it's because for an integer ‘’, blank, or null are 0.

Is this correct?

select * from loads 
where SettledWeight = ''

Open in new window

1
Comment
Question by:Camillia
  • 6
  • 4
  • 3
  • +3
19 Comments
 
LVL 16

Expert Comment

by:Brian Pringle
Comment Utility
You can specify the default by using the "DEFAULT" keyword when you created the column.  It is 0 by default, unless you change it.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
. My coworker says it's because for an integer ‘’, blank, or null are 0.

that statement is partially true in mysql, but not in ms sql server, at least not that I  aware.
if the field is varchar, containing '000', for example, it will match, as that string value can silently by casted to the integer value 0
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
The field is integer, null. I populated the table from the prod database.

Yeah, I can set it to default but wondering why ='' and =0 both bring back the same values.
0
 
LVL 16

Assisted Solution

by:Brian Pringle
Brian Pringle earned 125 total points
Comment Utility
Theoretically, they shouldn't...  However, because it is an integer data type, then the only valid values are whole numbers and null.  It will try to cast that '' string value to whatever is legal.  In this case, it casts it to 0.  If you don't want that to happen, the only option is to pick another data type.
0
 
LVL 16

Expert Comment

by:Brian Pringle
Comment Utility
Think of Excel.  If you try to add a blank cell and a cell with a number in it, you will get the number instead of an error.  This is by design to allow you to add them together.  If it did not automatically case the '' fields, then you would get an error when you try to sum the column.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
accordibg to the docs, inconsistent

SQL Server returns an error message when nonnumeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal.

https://msdn.microsoft.com/en-us/library/ms187928.aspx

away from a pc, i cannot test on a sqlfiddle or so...
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
This still brought back values

 select * from loads
where cast (SettledWeight as int) = ''
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
I brought the data back from SQl 2005 and that field is numeric(10,0) in the old database. Not sure if that would make a difference.

The zeros I see are correct but just want to understand why =0 and ='' bring back data. I would think ='' shoudn't bring back anything.
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 250 total points
Comment Utility
WOW! Good question and something you just don't think about, till you run across it.
SQL Server is implicitly converting the '' to a 0 based on Data Type Precedence.
https://msdn.microsoft.com/en-us/library/ms190309.aspx
Here is some test code to see what it is doing.
CREATE TABLE testloads (Id INT IDENTITY, SettledWeight INT, SomethingElse VARCHAR(50));
INSERT INTO dbo.testloads ( SettledWeight, SomethingElse )
SELECT * FROM (VALUES  ( 0, 'z' ), (NULL, 'y'), (' ', 'x'), ('', 'w')) x(W,V)

select * from testloads 
where SettledWeight = 0

select * from testloads 
where SettledWeight = ''

select * from testloads 
where SettledWeight IS NULL

DROP TABLE dbo.testloads;

Open in new window

Query Results
1
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 7

Author Comment

by:Camillia
Comment Utility
Thanks, Chris! this is great. let me take a look and run some tests.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 125 total points
Comment Utility
If data types don't match, SQL must implicitly (automatically) convert data so that the types match.  SQL uses rules of data precedence for this; you can find the complete list in Books Online.

When int and [var]char must be used together, both must first be converted to int.  Therefore:
SELECT '4' + 1
always yields 5 and never '41'.

When a blank is implicitly converted to int, it becomes 0.
SELECT CAST('' AS int)
An empty string is not part of the domain (valid values) for an integer column, thus it would be an invalid comparison.

My coworker says it's because for an integer ‘’, blank, or null are 0.

No.  NULL is never equal to any other value (barring use of SET ANSI_NULLS OFF, which you should never use anyway).
.
1
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
i see my collegues found the answer
however, it must be clearly said that trying to use intcol = '' is NEVER to be used, like any other implicit conversion, in general
2
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
Good comment from Guy, implicit conversions should be avoided everywhere as a matter of good coding practice.  You should always know your data and explicitly convert when necessary so you know what the results should be.
*edit
And also from Scott, NULL is not the same as 0 and SQL does not treat it that way.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
Thanks, guys. Let me read.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
I disagree with "always" avoiding implicit conversion.  Indeed, for date/time/datetimes, I believe you should always use character values, thus forcing implicit conversion, when you know the literal value will be valid.

For example, I would go so far as to do this to compare for current day:
WHERE date_column >= CONVERT(char(8), DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0), 112) AND
      date_column < CONVERT(char(8), DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0), 112)

Yes, SQL will be forced to implicitly convert the strings to dates, but that's far better than coding explicit conversions or potentially forcing the column to be implicitly converted to a "higher" data type, which is the biggest thing to avoid, because that prevents best use of potential indexes.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
and I allow to disagree there, a bit
in most application coding, you would define those parameters as typed, and not as ad-hoc sql string. both only to avoid sql injection, but also for query execution plan sharing (saving server resources and saving compilation time...)
but discussing all this would lead far off the iriginal question.
1
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
Late to this 'party' let me just add my opinion about implicit conversions: If they are very straight forward and there is no straight forward way to write data in the natural type it's okay to use them, you don't always do them from a client side frontend language with paramterization, ad hoc queries are often good enough, in case a customer trusts developer code, he can also trust developer ad hoc queries instead of parameterized prepared statements, you should only not trust on anything external. Besides parameterization does not hinder XSS attacks or anything else executing what is stored in a database.

A high quality implicit conversion is one not depending on settings, eg with dates a 'YYYYMMDD' string is converted to the right date no matter whether the date setting is YMD or not. Also no matter if separators are slash, hyphen or dots.

In this case of comparing int field to empty string I'd also rather expected an error and not an implicit conversion. The only thing you can do is avoid it, I don't see an option causing the server to react throwing an error instead of converting '' to 0.

No points please, just a side note. Finally the idea to introduce a STRICT setting to SQL Server is looked at and discussed here, if you like to get ideas on what to look out for, as SQL Server does not do so: http://www.sommarskog.se/strict_checks.html
I'd support the idea to introduce such a feature to MSSQL, just to stress it out once more: This is NOT a setting you can actually do, just ideas on what could be done. Implicit conversions of strings to ints are a topic in there, too.

Bye, Olaf.
1
 
LVL 7

Author Comment

by:Camillia
Comment Utility
Thanks, Olaf.

Side note, I love your "Bye, Olaf" signature :)
0
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
Thanks, Camilia,

this signature has it's root as counterpoint to all those lengthy signatures people used to have and sometimes still have with all kinds of pointers to sites and other stuff.

Here at EE you can put all such info into a profile, so no need for such signatures anyway, but it got a habit.

Bye, Olaf.
1

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now