Does not work inline query optimization using UDF-DETERMINISTIC from "with" in SA 12

[former_SQLA_member1694881]

In the version of Sybase SA 12.0.1.3942 and above was an error of the query, which was formally the fix in SA 12.0.1.4085. 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_12.0.1.3924.zip" - is a query execution plan in SA 12.0.1.3924, in it as you see there is an execution plan for "Main Query" and "SubQ1" (an execution plan for dba.FTest1).

File "plan_sa_12.0.1.4085.zip" - is a query execution plan in SA 12.0.1.4085, 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_12.0.1.3924.NOT_DETERMINISTIC.zip - this query execution plan in SA 12.0.1.3924 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_12.0.1.3924.NOT_DETERMINISTIC.zip and plan_sa_12.0.1.4085.zip we see that they are almost identical. The impression is that the correction of this error in 12.0.1.4085 for similar UDF simply substitute option "NOT DETERMINISTIC" real and inline-optimization occurs.

[Former Member · Accepted solution]

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.

http://search.sybase.com/kbx/changerequests?bug_id=751771

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.