Sybase SA12, 16: Error query optimization with UDF

former_SQLA_member1694881 · ‎2013 Nov 25

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 ?

former_SQLA_member1694881 · ‎2014 Apr 04

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

former_SQLA_member1694878 · ‎2013 Nov 25

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.

former_SQLA_member1694881 · ‎2014 Apr 08

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.