刚来博客园，有什么不对，需要改进的地方，欢迎各位同道指出来，谢谢。
今天在使用数据库时出现意外操作,将一张表的数据删除了。仔细查看SQL语句，发现问题。将问题类推到Northwind中。我们使用两个表,Employees和Products
SQL语句如下:
select * from Products where CategoryID in (select CategoryID from Employees)
不看表结构是看不出什么问题的，不清楚的看一下表结构。执行上面的SQL语句,你将能看到所有的产品记录。
当你执行上面语句的"select CategoryID from Employees",将会提示一个错误提示:
消息 207，级别 16，状态 3，第 1 行
列名 'CategoryID' 无效。
表Employees中根本就没有CategoryID列,而微软忽略了括号中的错误,执行前面的语句。如果是应用在删除，更新，那后果将是不可现象的。我曾为此付出了代价。
这个应该就是微软SQL的BUG吧,我们需要特别注意。
在SQL2000企业管理器,SQL2000的查询分析器,SQL2005的Management Studio Express中均有此Bug。
posted @ 2008-07-30 17:12
心儿醉 xinerzhui 阅读(1688)
评论(39) 编辑 收藏 网摘 所属分类:
数据库
发表评论
我用2005开发版，没发现你说的问题，执行 select * from Products where CategoryID in (select CategoryID from Employees)
同样提示
消息 207，级别 16，状态 1，第 1 行
列名 'CategoryID' 无效。
有意思的问题，在我的management studio里面查询，也会有这个问题，要好好地研究一下。
#5楼[
楼主]2008-07-30 17:36 |
@鬼不灵
那我改下字体颜色,黑色可以吧.
select * from products as a where exists(select * from Employees where Employees.CategoryID = a.CategoryID)
Microsoft SQL Server Management Studio 9.00.1399.00
有楼主说的问题！
确实应该注意一下。
#8楼[
楼主]2008-07-30 17:45 |
@试失换个写法
这个不行,会直接提示:
消息 207，级别 16，状态 3，第 1 行
列名 'CategoryID' 无效。
看了表结构才知道，Employees 表里面没有 CategoryID字段。为什么不报错呢。错误被吃掉了。
select * from Products where CategoryID in (select CategoryID from Employees)
select * from Products where CategoryID in (select EmployeeID from Employees)
我比较了一下这两个语句的执行计划，不完全一样。建议lz分析一下执行计划。
因为我还不能完全看懂执行计划。只能看出来两个执行计划是不一样的。呵呵。
在SQL Server 2000中确实是这样子的
--引用--------------------------------------------------
鬼不灵: 字体太费眼了。。没法看
--------------------------------------------------------
@!--
SQL SERVER 2005技术内幕里讲过的。因为CategoryID为Products表中的字段，所以后面的子句中的Category实际上是使用的Products表而非Employees表中的字段，所以这样的查询也就有了意义。解决的办法就是前面加每个表名，这样就可以避免这样的问题了
我觉得还是@young5335分析得有道理，
在select * from Products where CategoryID in (select CategoryID from Employees)中，
因为Employees表中没有名为CategoryID的字段，那么它实际上就用的Products表中当前行的CategoryID值。
#16楼[
楼主]2008-07-30 21:19 |
young5335说的有道理.以后写多表查询SQL语句的时候都要带上表名了.
这句似乎不能算作是bug吧，等同于
select * from Products a where a.CategoryID in (select a.CategoryID from Employees b)
语法就是这样分析过来的，只不过是一般人从来不这样想而已！
@songsgroup
我也是开发版的2005，但是不会出错误提示。
我怀疑和兼容级别有关，但是测试了全部级别，还是会得出无错误的提示。
或许在用户连接的其他参数中有一个开关来控制这种语法是如何来分析的了！
--引用--------------------------------------------------
Wuya: 我觉得还是@young5335分析得有道理，
在select * from Products where CategoryID in (select CategoryID from Employees)中，
因为Employees表中没有名为CategoryID的字段，那么它实际上就用的Products表中当前行的CategoryID值。
--------------------------------------------------------
同样支持这个观点。
楼主错了...
比较一下下面这两句就明白了..
select * from Products AS A where CategoryID in (select A.CategoryID from Employees AS B)
select * from Products AS A where CategoryID in (select B.CategoryID from Employees AS B)
微软的是按第一句理解的...没有错..因此查出来是A的全部...
在内部查询中一定在注意加限定名...
而一般人的理解是第2句.但这样是有错的..因为B中没有CategoryID..
我的2005里沒有這樣的問題
版本號：
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
如果语法的理解与“一般”人所期望的不同也不算是BUG， 那什么才算是BUG？
括号是有优先级的，这就是一般人的理解，括号里CategoryID竟然是指向括号外的对象，这个太违反直觉了。
@Klesh Wong
那只能说大部分的人并不能正确的理解SQL的这一段语法而已！实际上在
BOL的SQL语法exists讲解中多处提到了类似的用法。
各种语言中容易导致错误，和很多人期望的不同的地方也不少了吧？
我还是对于部分人不存在这个问题是由于何种方式导致的感兴趣。
#31楼[
楼主]2008-07-31 13:17 |
--引用--------------------------------------------------
zoti: 我的2005里沒有這樣的問題
版本號：
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--------------------------------------------------------
那意思是说,微软已意识到这一可能会导致问题的语法了？
@steven.zhan
括号就代表这是一个子查询
因为 SQL Server没有规定括号里面是不是属于括号里的表，才要提到common sense。
对于括号的common sense，就是括号的最高优先级，最里面的括号会被最先被执行。这是来自于数学，被广泛应用于编程，甚至说所有语言上面基本原则。
这种违反基本原则的行为不是说SQL Server没有规定就代表合理，完全不是理由。
我不认为对于ambiguous column必须做显势规定，括号已经暗示了，select CategoryID from Employees 应该被优先理解与执行。从语议上来讲已经完全可以断定CategoryID必须来自于Employees，它应该是一个“子查询”。
@Klesh Wong
select * from Products where CategoryID in (select CategoryID from Employees)
如果按照正常理解的话，Products和Employees两个表都有名为“CategoryID”的列，那么这样两个表在做联合查询的时候，对于这种ambiguous column一般需要显示的指定，当然不是必须，有些情况下不指定也可以通过。
像这句sql，没有指定，Employees里面没有CategoryID，那么SQL SERVER理解CategoryID是Products里面的并没有错误。这里没有所谓的common sense，a就是a，b就是b，在容易出现语义不清时，没有指定而造成SQL SERVER理解错误，不能算bug。SQL SERVER并没有规定（）里面的列必须属于括号里面的表。
@Klesh Wong
怎么说呢，这就是看如何理解这句SQL而已：
select * from Products a where a.CategoryID in (select a.CategoryID from Employees b) 这一句是符合SQL的标准语法的，而且也符合逻辑。
按照一般人的common sense，那是不是按照(select a.CategoryID from Employees b) 这一句首先执行就会出错了呢？如果按照你说的一般人的common sense，恐怕永远也不会写出类似这个逻辑的简洁正确的SQL语句了！
实际上就是一个由于表的别名没有明确引用，SQL自动按照没有引用别名的情况
到两个表中去匹配，不符合一般人错误的common sense罢了！
这个问题其实也揭示一小知识点，子查询不是完全按（）嵌套由内向外来执行。
当然，我还是很想知道为什么有的情况下SQL会抛出错误！这个比争论到底是不是bug有趣多了。我和上面23楼提到的版本完全一致，但是执行不带别名的那句不存在任何问题。
比较奇怪的是如果在Employees 中添加字段CategoryID执行下面这句：
select * from Products where CategoryID in (select CategoryID from Employees )
却又不报“列名 'CategoryID' 不明确”的错误，缺省的情况下，又当作（）内的Employees字段来优先解析执行了。
这样说来，还是很令人困扰的！
变成修改了，晕啊
@roberto
呵呵，这就是违反了common sense的结果了。
不管子查询实际上是不是从内向外执行，但外部看起来一定要符合里内向外执行的顺序。
我在2000及2005下都没有问题，都没有报错。不知道1楼的是怎么弄的，
#38楼[
楼主]2008-08-01 09:58 |
@michael_fei
这个要报错才正常,一般理解下该SQL语句是有问题的。
从执行计划看，好像首先是先看CategoryID 是否为Employees 表里的字段，如果是，就等同这种写法：select * from Products a where CategoryID in (select b.CategoryID from Employees b)
如果不是属于Employees表，就会看是否为Products 表里字段，如果是：
就是等同这种写法:select * from Products a where CategoryID in (select a.CategoryID from Employees b)
都不是，那么就会报错。