T-SQL bug? CURSOR does not respect TABLESAMPLE

huangapple go评论46阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年6月8日 03:20:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/76426488.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定