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 ?

asked 25 Nov '13, 07:15

Stalker's gravatar image

Stalker
23081221
accept rate: 0%

edited 25 Nov '13, 07:18


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

permanent link

answered 04 Apr '14, 07:48

Stalker's gravatar image

Stalker
23081221
accept rate: 0%

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.

(04 Apr '14, 08:40) Volker Barth
Replies hidden
1

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.

(04 Apr '14, 14:40) Ivan T. Bowman

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 ...

(07 Apr '14, 10:50) Stalker

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...

(07 Apr '14, 11:10) Volker Barth
1

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 :)

(07 Apr '14, 11:33) Breck Carter

...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.

(07 Apr '14, 11:36) Breck Carter

Have you watched the query execution plans ?

(11 Apr '14, 06:05) Stalker
showing 2 of 7 show all flat view

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.

permanent link

answered 25 Nov '13, 13:04

Mikel%20Rychliski's gravatar image

Mikel Rychliski
2.1k1641
accept rate: 30%

edited 11 Dec '13, 09:50

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 ?

(26 Nov '13, 03:22) Stalker

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

(11 Dec '13, 09:41) Stalker
Replies hidden
1

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

(11 Dec '13, 09:54) Mikel Rychliski

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.

permanent link

answered 08 Apr '14, 10:53

Stalker's gravatar image

Stalker
23081221
accept rate: 0%

2Breck Carter

Have you watched the query execution plans ?

(17 Apr '14, 10:44) Stalker

2Breck Carter

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

(10 May '14, 09:13) Stalker
Replies hidden
1

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.

(10 May '14, 15:43) Breck Carter

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).

(12 May '14, 03:55) Volker Barth
Comment Text Removed

Then look for these files please.

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

(12 May '14, 04:32) Stalker

Here's the status of your own answer:

alt text

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...

(12 May '14, 05:08) Volker Barth

2Breck Carter:

Then look for these files please.

(12 May '14, 10:51) Stalker
showing 2 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×207
×86
×61
×27

question asked: 25 Nov '13, 07:15

question was seen: 2,552 times

last updated: 12 May '14, 10:51