Question Re: Sybase SA12, 16: Error query optimization with UDF in Technology Q&A

Sybase SA12, 16: Error query optimization with UDF

former_SQLA_member1694881 — Mon, 25 Nov 2013 07:15:03 GMT

Hi All,

There are two tables:

CREATE TABLE "DBA"."TEST1" (
        "ID1" INTEGER NOT NULL,
        "CSTR" CHAR(10) NULL,
        PRIMARY KEY ( "ID1" ASC )
);

and

CREATE TABLE "DBA"."TEST2" (
        "ID2" INTEGER NOT NULL,
        "SUBITEM" INTEGER NULL,
        "ORGANIZNAME" CHAR(180) NOT NULL,
        PRIMARY KEY ( "ID2" ASC )
);

There are two UDF: query - with ... select

CREATE FUNCTION dba.FTest1(in nVal integer)
RETURNS Long VarChar
DETERMINISTIC
BEGIN
 DECLARE ret Long VarChar;

 with recursive CHECK1(ID2, SUBITEM) as 
 (
 (select 
   TEST2.ID2, 
   TEST2.SUBITEM 
  from dba.TEST2
  where TEST2.ID2 = nVal
  )
 union all
 (select 
   TEST2.ID2, 
   TEST2.SUBITEM 
  from dba.TEST2
  join CHECK1 on (TEST2.SUBITEM = CHECK1.ID2 and TEST2.SUBITEM is not null))  
 )
 select LIST(ID2) into ret from CHECK1;

 RETURN ret;
END
go

and simple query

CREATE FUNCTION dba.FTest2(in nVal integer)
RETURNS Long VarChar
DETERMINISTIC
BEGIN
 DECLARE ret Long VarChar;
 select LIST(TEST2.ID2) into ret from dba.TEST2;
 RETURN ret;
END
go

Perform in ISQL for Sybase SA 12.0.1.3924 (and younger) or Sybase SA 16.0.0.1324 request:

select dba.FTest1(TEST1.ID1) from dba.TEST1

all executed without error.

Perform in ISQL for Sybase SA 12.0.1.3942 (or higher) or on Sybase SA 16.0.0.1644 the same request:

select dba.FTest1(TEST1.ID1) from dba.TEST1

when the error occurs: Could not execute statement. The correlation name 'TEST1' not found SQLCODE=-142, ODBC 3 State="42S02" Line 1, column 1

While, if the same request to other UDF:

select dba.FTest2(TEST1.ID1) from dba.TEST1

perform at Sybase SA 12.0.1.3942 (or higher) or at Sybase SA 16.0.0.1644 the error does not occur. The only difference here is that in dba.FTest1 query "with ... select", and in dba.FTest2 request without "with ... select". The same error does not occur if, instead dba.FTest1 (TEST1.ID1) perform dba.FTest1(<any number="">).

Q: Why is this error has been happening in recent versions of Sybase SA 12.x and 16.x ? This is a bug in the Sybase SA or is it my fault ? Indeed, in earlier versions of Sybase SA 12.x and 16.x query dba.FTest1 (TEST1.ID1) without errors.

I noticed one more thing: when the query

select TEST1.ID1 as X1, dba.FTest1(X1) from dba.TEST1

I receive an error: Could not execute statement. The column 'X1' not found SQLCODE=-143, ODBC 3 State="42S22" Line 1, column 1

This error occurs on all versions Sybase SA 12.x and 16.x. Although the same request to another UDF

select TEST1.ID1 as X1, dba.FTest2(X1) from dba.TEST1

without errors.

Again, it is not clear why in the case dba.FTest1 column 'X1' was not found. Why this error happens ?

Re: Sybase SA12, 16: Error query optimization with UDF

Former Member — Mon, 25 Nov 2013 13:04:37 GMT

The 'correlation name not found' error appears to be a bug introduced with a previous change in this area. Thanks for the bug report, CR# 751771 has been opened to address this issue. To work around this issue, you can change the function definition of FTest1 to NOT DETERMINISTIC.

Re: Sybase SA12, 16: Error query optimization with UDF

former_SQLA_member1694881 — Tue, 26 Nov 2013 03:22:49 GMT

2 Mikel Rychliski

Thanks for the answer.

And what can you say about the error "The column not found" in the query

select TEST1.ID1 as X1, dba.FTest1(X1) from dba.TEST1

Or the same reason these errors as they arise ?

Re: Sybase SA12, 16: Error query optimization with UDF

former_SQLA_member1694881 — Wed, 11 Dec 2013 09:41:14 GMT

In Sybase SA EBF 22109: 12.0.1 SP66 Build 3994 these errors not corrections.

Re: Sybase SA12, 16: Error query optimization with UDF

Former Member — Wed, 11 Dec 2013 09:54:40 GMT

This bug was has not been fixed as of build 3994, but will be available in a future EBF/SP.

Re: Sybase SA12, 16: Error query optimization with UDF

former_SQLA_member1694881 — Fri, 04 Apr 2014 07:48:34 GMT

This bug is fixed in updates "EBF 22637: 12.0.1 SP70 Build 4085" and "EBF 22519: 16.0 SP9 Build 1823".

Re: Sybase SA12, 16: Error query optimization with UDF

VolkerBarth — Fri, 04 Apr 2014 08:40:05 GMT

FWIW: When someone like Mikel leaves a CR note, you can usually have a look at the according (public) description here (by adapting the fitting CR number):

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

It just tells what you have noticed (though for v16, it sounds different?):

Fixed Version Build Resolution Date Closure Code (If Appropriate)
16.0.0 1839 05 Mar 2014 Fixed
12.0.1 4081 05 Mar 2014 Fixed

Description
A user defined function with a SELECT statement containing a common table expression could have been incorrectly inlined. This has been fixed.

Re: Sybase SA12, 16: Error query optimization with UDF

Former Member — Fri, 04 Apr 2014 14:40:33 GMT

As an interim workaround, you can add a construct to your UDF that prevents it from being inlined. A variable declaration can be used; I've typically added "IF 1=0 THEN END IF;". In principle in the future either of those could be inlined in the future if the server can determine they don't affect the inlining of the function but they should work for now.

Re: Sybase SA12, 16: Error query optimization with UDF

former_SQLA_member1694881 — Mon, 07 Apr 2014 10:50:36 GMT

1) It is unclear how 05 Mar 2014 may be 16.0.0.1839, when 10 Mar 2014 released 16.0.0.1823 ?

2) Performs "ISQL-Plan Viewer" on Sybase SA 12.0.1.3924 (and younger) request:

select dba.FTest1(TEST1.ID1) from dba.TEST1

The query itself runs fine without errors. In "Plan Viewer" I see the query plan for "Main Query" and "SubQ1" (query plan when the dba.FTest1).

Performs "ISQL-Plan Viewer" on Sybase SA 12.0.1.4085 request:

select dba.FTest1(TEST1.ID1) from dba.TEST1

The query itself runs fine without errors.

BUT in the "Plan Viewer" I can only see the query plan for "Main Query" and see no "SubQ1" (query plan when the dba.FTest1).

Q: How much in 12.0.1.4085 in "Plan Viewer" does not show the query execution plan dba.FTest1 (SubQ1) ?

P.S. What is interesting, if dba.FTest1 option "DETERMINISTIC" replaced by "NOT DETERMINISTIC" and then in 12.0.1.3924 in "Plan Viewer" does not show the query execution plan dba.FTest1 (SubQ1). 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.

Correct me if I'm wrong ...

Re: Sybase SA12, 16: Error query optimization with UDF

VolkerBarth — Mon, 07 Apr 2014 11:10:03 GMT

1) It is unclear how 05 Mar 2014 may be 16.0.0.1839, when 10 Mar 2014 released 16.0.0.1823

The one is the "fixing date", the other a release date - so it should not come as a surprise that an error is fixed some time before it is released, and that in the meantime a further EBF is released...

Re: Sybase SA12, 16: Error query optimization with UDF

Breck_Carter — Mon, 07 Apr 2014 11:33:08 GMT

Please post the *.saplan file for the plan that you are talking about... to repeat, please ensure the file you post is the one you are talking about (no offense intended, but there's been a lot of posted code in this forum lately that is fundamentally different from the associated discussions 🙂

Re: Sybase SA12, 16: Error query optimization with UDF

Breck_Carter — Mon, 07 Apr 2014 11:36:43 GMT

...plus, history shows that 16.0.0.1839 is unlikely ever to be released; what WILL be released is some subsequent build that passes QA, and it will include everything fixed as of build 1839.

Re: Sybase SA12, 16: Error query optimization with UDF

former_SQLA_member1694881 — Tue, 08 Apr 2014 10:53:17 GMT

2Breck Carter:

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.

Re: Sybase SA12, 16: Error query optimization with UDF

former_SQLA_member1694881 — Fri, 11 Apr 2014 06:05:38 GMT

Have you watched the query execution plans ?

Re: Sybase SA12, 16: Error query optimization with UDF

former_SQLA_member1694881 — Thu, 17 Apr 2014 10:44:39 GMT

2Breck Carter

Have you watched the query execution plans ?

Re: Sybase SA12, 16: Error query optimization with UDF

former_SQLA_member1694881 — Sat, 10 May 2014 09:13:52 GMT

2Breck Carter

Have you watched the query execution plans for Sybase SA 12.0.1.3924 and Sybase SA 12.0.1.4085 ?

Re: Sybase SA12, 16: Error query optimization with UDF

Breck_Carter — Sat, 10 May 2014 15:43:51 GMT

No, I haven't looked at the files... on April 4 you marked this question as "answered" so it's not clear what you expect.

Re: Sybase SA12, 16: Error query optimization with UDF

VolkerBarth — Mon, 12 May 2014 03:55:15 GMT

Furthermore, in case the mentioned EBFs have not fixed the problem from you point of view (in contrast to the statement within your own "accepted answer"), then please explain this clearly.

If they have fixed the problem but you have noticed a different one, then please ask a new question (and link it to this one, if this makes sense).

Re: Sybase SA12, 16: Error query optimization with UDF

former_SQLA_member1694881 — Mon, 12 May 2014 04:32:18 GMT

Then look for these files please.

I have not noticed the issue as noted "answered" or did it wrong.

Re: Sybase SA12, 16: Error query optimization with UDF

VolkerBarth — Mon, 12 May 2014 07:08:14 GMT

Here's the status of your own answer:

As marked, the answer is "accepted" by you - and that will tell us that your question has been answered (or your problem has been solved) in an appropriate manner.

If that is not true, then please "unmark" that answer (if that is possible after that timespan, I don't know)...

As to the files: Sorry, I don't understand the issue , I'll leave it to others...