英文:
T-SQL bug? CURSOR does not respect TABLESAMPLE
问题
今天遇到了这个问题。代码如下,表格采样部分已注释掉。运行它,得到预期的结果为5000050000。
-- 在 SQL Server 2014 中进行测试
使用 tempdb;
如果对象_ID('dbo.ints','U')不为空
删除表 ints;
创建表 ints(x bigint NOT NULL);
与 MakeInts 一起
(
选择 CAST(1 AS bigint) AS x
联合全部
选择 x + 1 AS x
从 MakeInts
其中 x < 100000
)
插入到 ints
选择 *
从 MakeInts
选项 (MAXRECURSION 0);
声明 crsr 光标 只读
为 ints 中选择 x
--表格采样 (5 percent)
;
声明 @x bigint;
声明 @sum bigint = 0;
打开 crsr;
获取下一个从 crsr 到 @x;
当 (@@fetch_status <> -1) 时
开始
如果 (@@fetch_status <> -2)
开始
设置 @sum += @x;
结束
获取下一个从 crsr 到 @x;
结束
关闭 crsr;
取消分配 crsr;
打印 @sum;
现在取消注释表格采样并再次运行。结果仍然是5000050000。
我是不是忽视了一些非常明显的东西?
(Martin Smith 的评论指出 tablesample 在页面级别进行采样,而我的表足够小,可以适应一页。注意到这一点,已经修改了上面的代码,使其生成一个更大的表。但这并不能解决问题)
英文:
Ran into this today. Code as follows, with tablesample commented out. Run it, get the expected results of 5000050000.
-- tested in SQL Server 2014
use tempdb;
IF OBJECT_ID('dbo.ints', 'U') IS NOT NULL
DROP TABLE ints;
CREATE TABLE ints(x bigint NOT NULL);
WITH MakeInts AS
(
SELECT CAST(1 AS bigint) AS x
UNION ALL
SELECT x + 1 AS x
FROM MakeInts
WHERE x < 100000
)
INSERT INTO ints
SELECT *
FROM MakeInts
OPTION (MAXRECURSION 0);
DECLARE crsr CURSOR READ_ONLY
FOR select x
from ints
--tablesample (5 percent)
;
DECLARE @x bigint;
DECLARE @sum bigint = 0;
OPEN crsr;
FETCH NEXT FROM crsr INTO @x;
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
set @sum += @x;
END
FETCH NEXT FROM crsr INTO @x;
END
CLOSE crsr;
DEALLOCATE crsr;
print @sum;
Now comment tablesample back in and run again. Same result of 5000050000.
Am I overlooking something completely obvious?
(Martin Smith's comment pointed out that tablesample samples at the page level and my table was small enough to fit on one page. Good catch, and amended code above so it makes a much larger table. Doesn't fix though)
答案1
得分: 1
This behaviour isn't documented on the TABLESAMPLE
details so is somewhat speculative but certainly the demo in the question would indicate that the option is just being silently ignored.
For a dynamic cursor I can see why this might be the case. Dynamic cursor plans need to support moving both forwards and backwards so this would require storing some additional cursor state for just this edge case and add complexity to the fetch next.
It does seem maybe a bug that it doesn't just silently change the type to static though (as it would if you added an order by x
for example).
But in any event if you explicitly request static you do get the table sampling applied before the rows are inserted to the worktable.
英文:
This behaviour isn't documented on the TABLESAMPLE
details so is somewhat speculative but certainly the demo in the question would indicate that the option is just being silently ignored.
For a dynamic cursor I can see why this might be the case. Dynamic cursor plans need to support moving both forwards and backwards so this would require storing some additional cursor state for just this edge case and add complexity to the fetch next.
It does seem maybe a bug that it doesn't just silently change the type to static though (as it would if you added an order by x
for example).
But in any event if you explicitly request static you do get the table sampling applied before the rows are inserted to the worktable.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论