Sybase SA12: Possible error in the execution of SQL-query

[former_SQLA_member1694881]

Sybase SQL Anywhere 12.0.1.4127

This script to reproduce the problem:

CREATE TABLE "DBA"."TEST" (
        "ID" INTEGER NOT NULL,
        "FIELD1" CHAR(10) NULL,
        "FIELD2" CHAR(10) NULL,
        PRIMARY KEY ( "ID" )
)
go

CREATE FUNCTION "DBA"."MYFUNC1"(in cVar char(10)) 
RETURNS char(10)
DETERMINISTIC
BEGIN
 DECLARE cRetOut char(10);
 SET cRetOut = cVar;
 RETURN cRetOut;
END
go


Execute SQL-query
select distinct
 ID,
 FIELD1,
 FIELD2
from dba.TEST
order by dba.MYFUNC1(FIELD2)


why the error occurs:

Could not execute statement. Function
or column reference to 'dba' in the
ORDER BY clause is invalid
SQLCODE=-854, ODBC 3 State="42000"

although if you run the same query, but in the section "order by", specify the 
UDF without owner, there is no error:
select distinct
 ID,
 FIELD1,
 FIELD2
from dba.TEST
order by MYFUNC1(FIELD2)


At the same time in Sybase SA 8.0.3.5594 both SQL-query executed without 
error.

[Breck_Carter]

FWIW 16.0.0.1915 has the same symptom... it's hard to imagine an explanation that does not contain the word "bug" 🙂

[former_SQLA_member1694881]

So that this error will be corrected ?

[jeff_albion]

Thank you for the bug report. I have opened this as CR #771044 with our development group.

[former_SQLA_member1694881]

The fact that the problem is that I do not support - very much it is expensive.

Maybe then You (developers ASA) can issue an updated release SQLA1201_Developer.exe which will include the latest fixes ?

P.S. The current release includes SQLA1201_Developer.exe only EBF 12.0.1.4134

[former_SQLA_member1694881]

Can You give a link to this fix (EBF) or a link to SQLA1201_Developer.ehe which includes this fix ?

[reimer_pods]

The good news: SQL Anywhere can show you how to download that EBF:
Sybase Central -> Help -> SQL Anywhere 12 -> Check for Updates

The bad news: you'll need a valid login (S-ID) for the SAP Service Marketplace.
Lacking that I don't know an alternative.

[VolkerBarth]

Check for Updates

Though that seems often not to point out to the newest EBFs... E.g. I have 12.0.1.4216 (SP86) installed on my box and the Check tells me that there is no newer version, although 12.0.1.4231 (SP88) for that platform (Windows) has been available since early March 2015...

[former_SQLA_member1694881]

It turned out there has an update that fixes this bug ? What is his number (build) ?

[former_SQLA_member1694878]

CR#771044 was fixed in 16.0.0.2017 and 12.0.1.4165. 16.0 SP21 and greater should have this fix, as well as 12.0 SP81 and later (for Windows).

[Breck_Carter]

    ================(Build #4165  - Engineering Case #771044)================

    The server may have incorrectly returned the error: 'Function or column reference 
    to '<user-name>' in the ORDER BY clause is invalid', if a select statement 
    used a user-defined function with an owner name in the ORDER BY clause; e.g. 
    ORDER BY <user-name>.<function-name(...). this="" has="" been="" fixed.="" <="" pre="">