Hello,
I have been using sql server for many years and have always been happy with
it (and I am still). I have been totally surprised when I discover a bug
yesterday.
You may not call it a bug but it's at least an unexpected comportment from
my developer's point of view.
Here is how to reproduce :
-- STATEMENT 1
select ISNULL(null, 'Result if null')
-- Returns 'Result if null', I am OK
-- STATEMENT 2
select right(null, 5)
-- Returns NULL, I am OK
-- STATEMENT 3
select ISNULL(right(null, 5), 'Result if null')
-- As I have just replaced null value of the STATEMENT 1
-- with the STATEMENT 2 (also evaluated as null) I am expecting
-- to get the same result as STATEMENT 1 but instead I get an empty
string...?
-- STATEMENT 4
select right(CONVERT(varchar, null), 5)
--> Returns NULL, I am OK
select ISNULL(right(CONVERT(varchar, null), 5), 'Result if null')
-- As I have just replaced null value of the STATEMENT 1
-- with the STATEMENT 4 (also evaluated as null) I am expecting
-- to get the same result as STATEMENT 1 or at least an empty
-- string like STATEMENT 3, but instead I get 'Resul'...?
I would be glad if someone could explain me.
For info I am using SQL server 2000 sp3.
Many thanks,
Guirec Le Bars.ISNULL invariably returns the datatype of its first argument. In your
STATEMENT 3 you haven't specified a type for the first argument. The
NULL keyword is typless so the RIGHT expression implictly converts it
and returns a zero length string. That means the second argument is
also implicitly converted to a zero length string and that is the
result you see.
Note that if you change ISNULL to COALESCE you will get the full string
returned: 'Result if null'. That's because COALESCE, unlike ISNULL,
uses the type precedence rules to determine what type to return.
The return types are documented in the ISNULL and COALESCE topics in
BOL. Precedence is documented under the Datatype Precedence topic.
Similarly
select ISNULL(right(CONVERT(varchar, null), 5), 'Result if null')
truncates the string because the first argument determines the type and
size. Again, change ISNULL to COALESCE and you'll get the whole string.
The theme here is Implicit Conversion. Avoid implicit conversions
wherever possible by specifying the datatypes explicitly - that
includes when using the NULL keyword.
David Portas
SQL Server MVP
--|||then what should be the result of this
select ISNULL(right(null, 25), 100)
A surprise
--
Regards
R.D
--Knowledge gets doubled when shared
"David Portas" wrote:
> ISNULL invariably returns the datatype of its first argument. In your
> STATEMENT 3 you haven't specified a type for the first argument. The
> NULL keyword is typless so the RIGHT expression implictly converts it
> and returns a zero length string. That means the second argument is
> also implicitly converted to a zero length string and that is the
> result you see.
> Note that if you change ISNULL to COALESCE you will get the full string
> returned: 'Result if null'. That's because COALESCE, unlike ISNULL,
> uses the type precedence rules to determine what type to return.
> The return types are documented in the ISNULL and COALESCE topics in
> BOL. Precedence is documented under the Datatype Precedence topic.
> Similarly
> select ISNULL(right(CONVERT(varchar, null), 5), 'Result if null')
> truncates the string because the first argument determines the type and
> size. Again, change ISNULL to COALESCE and you'll get the whole string.
> The theme here is Implicit Conversion. Avoid implicit conversions
> wherever possible by specifying the datatypes explicitly - that
> includes when using the NULL keyword.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi David,
What you are telling me is that NULL and right(NULL , 5) and
right(CONVERT(varchar, NULL), 5) are of 3 different datatypes even if they
are all evaluated as NULL values?
You can imagine that I consider this as an unexpected behavior...
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128589655.573516.172420@.z14g2000cwz.googlegroups.com...
> ISNULL invariably returns the datatype of its first argument. In your
> STATEMENT 3 you haven't specified a type for the first argument. The
> NULL keyword is typless so the RIGHT expression implictly converts it
> and returns a zero length string. That means the second argument is
> also implicitly converted to a zero length string and that is the
> result you see.
> Note that if you change ISNULL to COALESCE you will get the full string
> returned: 'Result if null'. That's because COALESCE, unlike ISNULL,
> uses the type precedence rules to determine what type to return.
> The return types are documented in the ISNULL and COALESCE topics in
> BOL. Precedence is documented under the Datatype Precedence topic.
> Similarly
> select ISNULL(right(CONVERT(varchar, null), 5), 'Result if null')
> truncates the string because the first argument determines the type and
> size. Again, change ISNULL to COALESCE and you'll get the whole string.
> The theme here is Implicit Conversion. Avoid implicit conversions
> wherever possible by specifying the datatypes explicitly - that
> includes when using the NULL keyword.
> --
> David Portas
> SQL Server MVP
> --
>|||Same principle applies but in this case the implicit conversion and
truncation of a numeric is displayed as an asterisk, as happens with
any conversion of a numeric to a string of insufficient length. Verify
the return type like this:
SELECT ISNULL(RIGHT(NULL, 25), 100) AS x INTO T
Result:
Server: Msg 2731, Level 16, State 1, Line 1
Column 'x' has invalid width: 0.
Apparently in your original query the return type is then implicitly
cast to a non-zero length string for display. That's an interesting
feature but not especially important I think because any non-trivial
use of that expression would require a conversion to some other usable
datatype anyway.
David Portas
SQL Server MVP
--|||You have to distinguish between the NULL *keyword* and the "value" of
NULL (strictly NULL isn't a proper value at all but for brevity I'm
calling it such here). NULL values are valid in any datatype but the
keyword "NULL" isn't a value at all - it's just a literal keyword that
can be used to generate NULL values in expressions. When an expression
contains the NULL keyword the actual datatype returned is determined by
the other parts of the expression. This is to be expected, given the
nature of SQL's typing. Otherwise we would need separate keywords for
each type of NULL (or the language could disallow the use of the NULL
keyword without an explicit "constructor" function, which would perhaps
be a better solution).
So yes, of course there are many possible datatypes for a NULL value.
If there were not then NULLs would be unusable. Your confusion comes
from the fact that you are using implicit conversion in your
expressions. Implicit conversion is the root of many programming errors
and should generally be avoided.
David Portas
SQL Server MVP
--|||Yes, there are a lot of NULL related issues that are unexpected in SQL.
Quite often it is hard to make any sense out of it, but most of us are ok
with "just do it, that is how it is".
The reason is NULLs in a relational table is fundamentally flawed since
logically, NULLs are not values and they have no type. ( Since they are not
typed values, and operations are applicable only to typed values, by
definition, NULLs cannot be in any operations. ) Due to this and certain
other reasons, relational model absolutely rejects anything that has to do
with NULLs because it is illogical.
However, SQL considers them as markers for representing the lack of a value,
but implements them as values in a table. In other words, in a table or
tabular expression, NULLs assume the type of the column's declared type and
associates itself with the operators of the type. Therefore, every SQL
implementation has a vague concept of "typed NULLs". Thus NULLs in an
integer column will be evaluated with respect to integer operators, NULLs in
a character column will be evaluated in character operations and so forth.
There are several inconsistencies related to NULLs, some of them very
subtle. When you use an operator whose operands are two distinctly typed
values, the evaluation of NULLs become suspect and arbitrary. It becomes
more ambiguous when NULLs are used in scalar expressions where operands are
not explicitly declared.
In SQL Server, the answer lies with how ISNULL treats null-able expressions.
SELECT NULL
SELECT RIGHT( NULL, 5 )
SELECT RIGHT(CONVERT(VARCHAR, NULL), 5)
SELECT ISNULL( NULL, 'Result if null')
SELECT ISNULL( RIGHT( NULL, 5 ), 'Result if null')
SELECT ISNULL( RIGHT(CONVERT(VARCHAR, NULL), 5), 'Result if null')
SELECT COALESCE( NULL, 'Result if null')
SELECT COALESCE( RIGHT( NULL, 5 ), 'Result if null')
SELECT COALESCE( RIGHT(CONVERT(VARCHAR, NULL), 5), 'Result if null')
You might also want to look at the distinction between ISNULL and COALESCE
at: www.aspfaq.com/2532
Anith
