sql server 2005 isnull的BUG？

[wqyitian]

CREATE TABLE [dbo].[ISNULL_TEST](
[name] [varchar](5) COLLATE Chinese_PRC_CI_AS NULL,
[status] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

insert into ISNULL_TEST values('AAAA','');
insert into ISNULL_TEST values('BBBB',NULL);
insert into ISNULL_TEST values('CCCC','123');

然后查询：
SELECT name, status, ISNULL(status, 0) AS Expr1
FROM ISNULL_TEST
WHERE (ISNULL(status, 0) = 0)

你会发现ISNULL()在where后面的运行和select后面的运行是不同的。
下面是查询结果
name status expr1
AAAA '' ''
BBBB NULL 0
这就让我很迷惑，在oracle中''和null是一样处理的。

[Zoezs]

这样就不会有AAAA的数据了。
SELECT    name, status, ISNULL(status, 0) AS Expr1 
FROM        ISNULL_TEST 
WHERE    status is null

[claro]

select * from ISNULL_TEST
/*
name	status
AAAA	
BBBB	NULL
CCCC	123
*/
SELECT    name, status, ISNULL(status, 0) AS Expr1
FROM        ISNULL_TEST
WHERE    (ISNULL(status, 0) = 0)
/*
name	status	Expr1
AAAA		
BBBB	NULL	0
*/
SELECT    name, status, ISNULL(status,'A') AS Expr1,NULLIF(status,NULL) AS Expr2
FROM        ISNULL_TEST
/*
name	status	Expr1	Expr2
AAAA			
BBBB	NULL	A	NULL
CCCC	123	123	123
*//*
在MS中''和NULL,'NULL'是不一样的。
ISNULL_TEST 中AAAA的status不为NULL，返回''值，
BBBB的status为NULL，返回NULL值。
跟你的结果一样，有什么问题？

[大P]

SELECT name, status, ISNULL(status, 0) AS Expr1
FROM ISNULL_TEST
WHERE status is null or status=0



SELECT name, status, ISNULL(status, 0) AS Expr1
FROM ISNULL_TEST
where (ISNULL(status, 0) = 0)

--这2个含义才是一样的~

[htl258_Tony]

CREATE TABLE [dbo].[ISNULL_TEST]( 
[name] [varchar](5) COLLATE Chinese_PRC_CI_AS NULL, 
[status] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] 

insert into ISNULL_TEST values('AAAA',''); 
insert into ISNULL_TEST values('BBBB',NULL); 
insert into ISNULL_TEST values('CCCC','123'); 

--然后查询： 
SELECT    name, status, ISNULL(status, 0) AS Expr1 
FROM        ISNULL_TEST 
WHERE    (ISNULL(status,'') = '') 

/*
name  status                                             Expr1
----- -------------------------------------------------- --------------------------------------------------
AAAA                                                     
BBBB  NULL                                               0

(2 行受影响)
*/

可以变通处理。

[claro]

select * 
from ISNULL_TEST 
where status = ''
/*
name	status
AAAA	
*/

select * 
from ISNULL_TEST 
where status is NULL
/*
name	status
BBBB	NULL
*/
--楼主再想想吧，别被Oracle和MSsql弄混了。

[Zoezs]

这个问题没搞清楚，

isnull在where后面居然把''也认为是null。

等高人来吧。

[大P]

SELECT    name, status, ISNULL(status, 0) AS Expr1 
FROM        ISNULL_TEST 
WHERE  status is null or status=0 


SELECT    name, status, ISNULL(status, 0) AS Expr1 
FROM        ISNULL_TEST 
where  (ISNULL(status, 0) = 0) 

--问题是status里没有0 ...哈哈~等待高人解答~

[fcuandy]

结果很成功，哪有什么bug?

isnull(x,p)
当x为null时，取p,否则取x
相当于
case when x is null then p else x end

或者用basic描述为
if isnull(x) = true then
取 p
else
取 x
end if

你的语句 where isnull(status,0)=0 意思是取status为null或者0的记录，当然，会取到1,2两条记录。
因为2的status就为null.
为什么说1是''也会被取到呢？
因为isnull(x,p) 会将两个参数转换为一致的类型，即p转为x, 精度也与x一致. 而你写的是 isnull(status,0) 其实等同于 isnull(status,'0')
在where后的 isnull(status,'0') = 0 的比较中, varchar与int比较 根具精度转换时取高精确度（即低精准度数据隐式转为高精准度类型）的原则， 则尝试将isnull(status,'0') 的结果再转为 int.
这一点，你通过个简单的测试就能发现， 比如把第三条记录的 123 改为 a123, 就会报错.
因为 isnull('a123','0') 结果为 'a123' 再与 int 0 比较， a123 无法转换为int, 因此报错。

明白天上面两点， 对于第一条记录 isnull('','0') 因为 ''不是null,所以结果为 '' ,当 where '' = 0 这个条件比较时，尝试把''转换为int, 而''转为int就是0
所以结果中会有 1,2两条记录。

至于select的结果，为什么第一条记录不是0而是 '', 你看明白了上面我说的话，就清楚了。因为 isnull('','0') 得到的是 '', 它不需要与int数字运算，所以不会转换为int,就继续保留为int.

对于varchar类型，null与''是有区别的，这在大多数语言中也是的。你说oracle是null与''相同，只是oracle内部做了一些处理而已。

我们来看''与null的存储。
大家知道字串（其实是所有数据类型）在存储时都会有个地址，并分配一定空间 ''就是已经分配了地址，但所占空间为1个字符，用c++来说，就是只有一个字串中止符\0. 也就是对''这个值来说，变量有两个信息: 1 变量地址，2 占用空间，因为它没有值，所以只有一个终止符。
而null,表示是变量或字段并没有分配地址。

[fcuandy]

在我上面的描述中
因为isnull(x,p) 会将两个参数转换为一致的类型，即p转为x, 精度也与x一致. 而你写的是 isnull(status,0) 其实等同于 isnull(status,'0')

准确的说，应该是这样:

因为status为varchar(50)

isnull(status,0) 其实真正表示的应该是 isnull(status,cast('0' as varchar(50))

因为有隐式转换在操作里面，这些操作都被sqlserver替你完成了。 这是初学者不容易搞清楚的地方。

[fcuandy]

至于select的结果，为什么第一条记录不是0而是 '', 你看明白了上面我说的话，就清楚了。因为 isnull('','0') 得到的是 '', 它不需要与int数字运算，所以不会转换为int,就继续保留为int.


就继续保留为 ''

上面手误

[htl258_Tony]

[Quote=引用 13 楼 fcuandy 的回复:]
结果很成功，哪有什么bug?

isnull(x,p)
当x为null时，取p,否则取x
相当于
case when x is null then p else x end

或者用basic描述为
if isnull(x) = true then
取 p
else
取 x
end if

你的语句 where isnull(status,0)=0 意思是取status为null或者0的记录，当然，会取到1,2两条记录。
因为2的status就为null.
为什么说1是''也会被取到呢？
因为isnull(x,p) 会将两个参数转换为一致的类型…
[/Quote]高人

[fcuandy]

其实说了这么多，主要为了表明两点意思， 这个问题的产生，在于t-sql的两个特性：
总结一下说就是:

(1)isnull(x,p) 操作
结果类型与 x一致，精度，长度都一样。可以参考

declare @n decimal(10,2)
select isnull(@n,'10')
/*
10.00
*/
select isnull(@n,'aa')
/*
这个会报错，因为'aa'无法转换为decimal(10,2)
*/

(2)t-sql中的运算，当操作数类型不同时，为减少或避免转换中的损失，底精准度类型会尝试转换为高精准度类型再运算（其实大多数语言都有这个特性，比如basic中double+int结果为double） 我说所的精准度是比术语（高/低）精度更广一点的概念， 因为没找到什么合适的词，暂这样说了。
datetime>int>varchar...
decimal>int>varchar

也就是说 datetime + int 结果为 datetime
varchar + int 结果为 int
..
结果为什么，意思就是要尝试把不同的类型转换为什么。

[dj3688]

学习

[claro]

[Quote=引用 15 楼 fcuandy 的回复:]
至于select的结果，为什么第一条记录不是0而是 '', 你看明白了上面我说的话，就清楚了。因为 isnull('','0') 得到的是 '', 它不需要与int数字运算，所以不会转换为int,就继续保留为int.


就继续保留为 ''

上面手误
[/Quote]
累了吧。帮顶。

[claro]

o(∩_∩)o...哈哈，估计fcuandy 是一口气写完的，哈哈。

[Zoezs]

[Quote=引用 13 楼 fcuandy 的回复:]
结果很成功，哪有什么bug?

isnull(x,p)
当x为null时，取p,否则取x
相当于
case when x is null then p else x end

或者用basic描述为
if isnull(x) = true then
取 p
else
取 x
end if

你的语句 where isnull(status,0)=0 意思是取status为null或者0的记录，当然，会取到1,2两条记录。
因为2的status就为null.
为什么说1是''也会被取到呢？
因为isnull(x,p) 会将两个参数转换为一致的类型…
[/Quote]
厉害。

[软件搬运工]

不错,学习了!

[fcuandy]

[Quote=引用 20 楼 claro 的回复:]
o(∩_∩)o...哈哈，估计fcuandy 是一口气写完的，哈哈。
[/Quote]

现在基本也只回答这种不用动脑的个人看法类问题了，其它的，别人粘贴太快了，哈

[wangwuxu]

太强啦 ！

[daidaiboy]

写的太好了，帮顶！