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.
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Timur
May I answer in french ?
C'est certainement dû à un mécanisme de cast. Sur ta 5ème requete tu récupère 'resul' à cause du right 5. Si tu remplace 5 par N, tu récupère N caractères ...
la fonction COALESCE rend les mêmes service que isnull, retourne des résultats plus conformes à ton attente, et est préférable ...
Cordialement
---------------------------------------------------------- Omnia vanitas
"glb" a écrit dans le message de news:
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.
May I answer in french ?
C'est certainement dû à un mécanisme de cast. Sur ta 5ème requete tu
récupère 'resul' à cause du right 5. Si tu remplace 5 par N, tu récupère N
caractères ...
la fonction COALESCE rend les mêmes service que isnull, retourne des
résultats plus conformes à ton attente, et est préférable ...
Cordialement
----------------------------------------------------------
Omnia vanitas
"glb" <guirec.lebars@theglobalfund.org> a écrit dans le message de
news:ebL5uTlyFHA.1960@TK2MSFTNGP10.phx.gbl...
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.
C'est certainement dû à un mécanisme de cast. Sur ta 5ème requete tu récupère 'resul' à cause du right 5. Si tu remplace 5 par N, tu récupère N caractères ...
la fonction COALESCE rend les mêmes service que isnull, retourne des résultats plus conformes à ton attente, et est préférable ...
Cordialement
---------------------------------------------------------- Omnia vanitas
"glb" a écrit dans le message de news:
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.
glb
C'est en fait un problème de conversion implicite. La "valeur" NULL, je devrais plutôt dire une absence de valeur, est typée !
Démonstration par l'exemple :
declare @test char(2) select @test = null select isnull(@test, 100) --------> Ici le résultat est amusant
est différent de :
declare @test int select @test = null select isnull(@test, 100)
Bien sûr je peux obtenir le résultat attendu par d'autres moyens. Mais la fonction IsNull existe et est fréquemment utilisée, il est toujours intéressant de savoir qu'il faut s'en méfier....
"Timur" wrote in message news:
May I answer in french ?
C'est certainement dû à un mécanisme de cast. Sur ta 5ème requete tu récupère 'resul' à cause du right 5. Si tu remplace 5 par N, tu récupère N caractères ...
la fonction COALESCE rend les mêmes service que isnull, retourne des résultats plus conformes à ton attente, et est préférable ...
Cordialement
---------------------------------------------------------- Omnia vanitas
"glb" a écrit dans le message de news:
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.
C'est en fait un problème de conversion implicite.
La "valeur" NULL, je devrais plutôt dire une absence de valeur, est typée !
Démonstration par l'exemple :
declare @test char(2)
select @test = null
select isnull(@test, 100) --------> Ici le résultat est amusant
est différent de :
declare @test int
select @test = null
select isnull(@test, 100)
Bien sûr je peux obtenir le résultat attendu par d'autres moyens. Mais la
fonction IsNull existe et est fréquemment utilisée, il est toujours
intéressant de savoir qu'il faut s'en méfier....
"Timur" <fgregoire@enlever_ceci.freesurf.frzzz> wrote in message
news:eOLhtamyFHA.1856@TK2MSFTNGP12.phx.gbl...
May I answer in french ?
C'est certainement dû à un mécanisme de cast. Sur ta 5ème requete tu
récupère 'resul' à cause du right 5. Si tu remplace 5 par N, tu récupère N
caractères ...
la fonction COALESCE rend les mêmes service que isnull, retourne des
résultats plus conformes à ton attente, et est préférable ...
Cordialement
----------------------------------------------------------
Omnia vanitas
"glb" <guirec.lebars@theglobalfund.org> a écrit dans le message de
news:ebL5uTlyFHA.1960@TK2MSFTNGP10.phx.gbl...
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.
C'est en fait un problème de conversion implicite. La "valeur" NULL, je devrais plutôt dire une absence de valeur, est typée !
Démonstration par l'exemple :
declare @test char(2) select @test = null select isnull(@test, 100) --------> Ici le résultat est amusant
est différent de :
declare @test int select @test = null select isnull(@test, 100)
Bien sûr je peux obtenir le résultat attendu par d'autres moyens. Mais la fonction IsNull existe et est fréquemment utilisée, il est toujours intéressant de savoir qu'il faut s'en méfier....
"Timur" wrote in message news:
May I answer in french ?
C'est certainement dû à un mécanisme de cast. Sur ta 5ème requete tu récupère 'resul' à cause du right 5. Si tu remplace 5 par N, tu récupère N caractères ...
la fonction COALESCE rend les mêmes service que isnull, retourne des résultats plus conformes à ton attente, et est préférable ...
Cordialement
---------------------------------------------------------- Omnia vanitas
"glb" a écrit dans le message de news:
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.
Synopsis
- Statement 5 : Troncature automatique (normal)
Pour vous convaincre :
Declare @str varchar(5)
Set @str ='hello world!' Select @str
hello
Set @str = null select isnull(@str, 'La valeur est à Null')
La va
"glb" a écrit dans le message de news:
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.
- Statement 5 :
Troncature automatique (normal)
Pour vous convaincre :
Declare @str varchar(5)
Set @str ='hello world!'
Select @str
hello
Set @str = null
select isnull(@str, 'La valeur est à Null')
La va
"glb" <guirec.lebars@theglobalfund.org> a écrit dans le message de
news:ebL5uTlyFHA.1960@TK2MSFTNGP10.phx.gbl...
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.
Set @str = null select isnull(@str, 'La valeur est à Null')
La va
"glb" a écrit dans le message de news:
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.
Fred BROUARD
Hi,
The usage of ISNULL is non normative fonction and does special job regarding to what microsoft decide to do with.
Instead of using MS function use standard ISO SQL function wich will give you the corect result :
select COALESCE(right(null, 5), 'Result if null')
Result if null
A +
-- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation ********************* http://www.datasapiens.com ***********************
glb a écrit:
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.
Hi,
The usage of ISNULL is non normative fonction and does special job regarding to
what microsoft decide to do with.
Instead of using MS function use standard ISO SQL function wich will give you
the corect result :
select COALESCE(right(null, 5), 'Result if null')
Result if null
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
glb a écrit:
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.
The usage of ISNULL is non normative fonction and does special job regarding to what microsoft decide to do with.
Instead of using MS function use standard ISO SQL function wich will give you the corect result :
select COALESCE(right(null, 5), 'Result if null')
Result if null
A +
-- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation ********************* http://www.datasapiens.com ***********************
glb a écrit:
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.
