In the version of Sybase SA 22.214.171.12442 and above was an error of the query, which was formally the fix in SA 126.96.36.19985. The initial description of this problems, see the topic Sybase SA12, 16: Error query optimization with UDF.
It seems that the correction of this error is non-inline query optimization with UDF with "with".
SQL-code tables, UDF, the request is in zip-files (with the execution plan of the query).
File "plan_sa_188.8.131.5224.zip" - is a query execution plan in SA 184.108.40.20624, in it as you see there is an execution plan for "Main Query" and "SubQ1" (an execution plan for dba.FTest1).
File "plan_sa_220.127.116.1185.zip" - is a query execution plan in SA 18.104.22.16885, in it as you see there is only the execution plan for "Main Query", but there is no implementation plan for dba.FTest1 ("SubQ1").
For comparison, add another file plan_sa_22.214.171.12424.NOT_DETERMINISTIC.zip - this query execution plan in SA 126.96.36.19924 on condition that dba.FTest1 enabled option "NOT DETERMINISTIC". In it you see there is only the execution plan for "Main Query", but there is no implementation plan for dba.FTest1 ("SubQ1").
If we compare the running of the query plans plan_sa_188.8.131.5224.NOT_DETERMINISTIC.zip and plan_sa_184.108.40.20685.zip we see that they are almost identical. The impression is that the correction of this error in 220.127.116.1185 for similar UDF simply substitute option "NOT DETERMINISTIC" real and inline-optimization occurs.
asked 13 May '14, 04:12
After revisiting my bypass optimiztion statement and the original issue reported in the previous thread, the reason there is no in-lining in this case is directly due to the fix to the original problem.
It is now determined that it was incorrect to inline this function because it has a recursive common table expression and inlining of such UDFs is now disabled for queries with common table expressions.
What is your concern with this Stalker? All plans do complete in under 20 µsecs.
The difference is mostly associated with the fact that for 2 of those plans, the optimization method was "Bypassed heuristic" which treats the function as a simple expression.
Given the optimization time is a whopping 679.14µsecs it seems to be a reasonable trade-off to bypass optimization. And as a trade-off I would suggest it was a pretty good one given the net gain in the resulting times: