更新时 case 语句中的 Sybase 15 子字符串错误
在 Adaptive Server Enterprise/15.0.3/EBF 17157 ESD#3/P/x86_64/Enterprise Linux/ase1503/2726/64-bit/FBO/ 上运行
以下代码不应输入子字符串大小写，但我看到的是 Sybase错误 536。
这是一种优化形式，无论实际值如何，它都会评估所有路径？
我们可以解决这个问题，但想知道为什么吗？
declare @test float
declare @test1 char(10)
create table #TestTable
(
Dno int,
Code varchar(10)
)
Insert into #TestTable values (1,'code')
set @test1 = 'ddd'
print 'test'
select @test = case
when (1=1) then 2
when (1=0) then (select Dno FROM #TestTable WHERE Code = substring('abc',1,charindex(@test1,'a')-1) AND Dno = 1)
else 10
end
select @test
drop table #TestTable
Running on Adaptive Server Enterprise/15.0.3/EBF 17157 ESD#3/P/x86_64/Enterprise Linux/ase1503/2726/64-bit/FBO/
The below code should never enter the substring case, however I am presented with a Sybase Error 536.
Is this a form of optimization where it evaluates all paths regardless of the actual value??
We can work around this but wish to know why?
declare @test float
declare @test1 char(10)
create table #TestTable
(
Dno int,
Code varchar(10)
)
Insert into #TestTable values (1,'code')
set @test1 = 'ddd'
print 'test'
select @test = case
when (1=1) then 2
when (1=0) then (select Dno FROM #TestTable WHERE Code = substring('abc',1,charindex(@test1,'a')-1) AND Dno = 1)
else 10
end
select @test
drop table #TestTable
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
是的。
看来您可能误解了优化查询的本质以及优化查询的要求。显然，您认为有时应该执行一些“代码路径”，而不是其他的。不可能将已编码的“代码路径”排除在优化器的视线之外。虽然它优化了查询，但在处理 SQL 方面，它只是一个编译器，而不是优化器。
当查询被优化时，整个查询路径被确定（抛开在选择一种可能性之前评估多种可能性这一事实）、评估、检查和编译。 CASE 无关紧要。为了运行任何子查询，必须评估和编译该子查询。您的特定子查询永远不会执行这一事实与代码无关。
出于多种原因，最好将永远不会执行的代码放置在程序之外。查询树中的分支是否被执行是在运行时确定的。人类可以看到它永远不会被激活，但优化器还没有那个级别的人工智能（优化器可以排除此类代码将是一个伟大的日子）。
Yes.
It appears that you may be misunderstanding the nature of, and what is required of, optimising the query. Apparently you think there is some "code path" that should be executed sometimes, and not others. It is not possible to exclude a coded "code path" from the gaze of the optimiser. Although it optimises queries, in terms of handling SQL, it is just a compiler, not an optimiser.
When the query is optimised, the entire query path are determined (setting aside the fact that many possibilities are evaluated before one is chosen), evaluated, checked, and compiled. The CASE is irrelevant. In order for ANY subquery to run that subquery has to be evaluated and compiled. The fact the your particular subquery will never execute is irrelevant to the code.
For many reasons, it is a good idea to place code that will never execute, outside the program. Whether the branch in the query tree will be executed or not, is a determination at runtime. Humans can see that it will never be activated, but the optimiser does not yet have that level of AI (it will be a grand day when an optimiser can exclude such code).