Behaviour change with oracle Remote Tables in 16.0.0.2003

[thomas_duemesnil]

Since i upgrade my installation to 16.0.0.2003 I have a problem with my functions accessing proxy tables on a oracle server.

I could bring down the problem to the following lines

if exists ( select 1 from <PROXYTABLE> where <PROXYTABLE>.SATZART = 'F' ) then
    message 'Found'
else
    message 'Not found'
end IF


This has worked for years and now i get the following error back from oracle 

SQLCODE = -660
SQLSTATE = WO005
ERRORMSG() = Server 'MYSERVER': [Sybase][SQL Anywhere - Oracle][Oracle]ORA-00923: Schlüsselwort FROM nicht an erwarteter Stelle gefunden

I have set the cis_option to 7 on my system and could see that the Query above is executed as follows
The query is being processed in FULL PASSTHRU mode
The Original Statement is 
    (select first 1 from <PROXYTABLE> where <PROXYTABLE>.SATZART = 'F')
The Virtual Statement is 
    select vt_1.expr_1 from vt_1

The Remote Statement for vt_1 is 
    (select first 1 from <PROXYTABLE> where <PROXYTABLE>.SATZART = 'F')

Execute (MYSERVER): 
     SELECT FIRST 1 FROM SA001.<PROXYTABLE> t1  WHERE t1."SATZART" = 'F'


When I execute the query from iSql i get SQLCODE=122, ODBC 3 State=01000 Not deterministic result set
When I add a order clause to the statement above 
select FIRST 1 from <PROXYTABLE> where <PROXYTABLE>.SATZART = 'F' 
order by <PROXYTABLE>.REFNR


I can reproduce the error [Sybase][SQL Anywhere - Oracle][Oracle]ORA-00923:
Schlüsselwort FROM nicht an erwarteter Stelle gefunden
SQLCODE=-660, ODBC 3 State="HY000"
The same Query without the FIRST keyword works fine in my case. 
select 1 from <PROXYTABLE> where <PROXYTABLE>.SATZART = 'F' 
order by <PROXYTABLE>.REFNR

:::SQL 
CREATE SERVER "MYSERVER" CLASS 'ORAODBC' USING 'FIBU';


FIBU is a SYSTEM DSN which uses the SQL Anywhere 16 - Oracle driver in Version 16.00.00.2003
Will this be fixed ? When ?

[VolkerBarth]

Being no Oracle user, is "SELECT FIRST ..." allowed in Oracle at all?

(BTW: I have not found a syscapability for FIRST/TOP, just in case there could have been a slight (and unexpected) change in their settings that could then lead to different syntax sent to the remote server...)

[thomas_duemesnil]

I don't now how you can come up always with a idea that can get you forward.

I check my System with the following Query

select sc.capid, sc.srvid, sc.capvalue, sn.capname, ss.srvclass 
from syscapability sc 
join syscapabilityname sn on sn.capid = sc.capid 
join sysserver ss on ss.srvid = sc.srvid 
where ss.srvname = 'MYSERVER'


I can see nothing that is dealing with FIRST or TOP in the column capname.

[VolkerBarth]

I don't now how you can come up always with a idea that can get you forward.

Well, it's just my impression/experience that these caps do have some impact on the statement syntax that can be sent to the remote server...

I had just checked with an (older) v12 server, and I had not found any entry that looks like a row-limitation feature but I thought that v16 might have newer entries - according to your query, it has not, either.

---

What about "SELECT FIRST ..." as valid Oracle syntax?

[Breck_Carter]

As far as I can tell from your description, SQL Anywhere is adding a bogus FIRST keyword where it is not needed. This "feels like" a fix gone awry; e.g., some other context required a FIRST... no, wait, Engineering never has senior moments, do they? 🙂

If you want a quick fix, backing out to the previous EBF is one alternative; another is using a workaround that fools SQL Anywhere into omitting the bogus FIRST.

With non-proxy SQL Anywhere queries, IF EXISTS ( SELECT 1 can be rewritten as IF EXISTS ( SELECT * without any penalty because the select list is never actually required for EXISTS or NOT EXISTS. With proxy tables, however, one never really knows what query will actually be sent; i.e., which side of the proxy boundary the EXISTS will be computed when dealing with Oracle and its ilk.

In your case, you might try if exists ( select <proxytable>.SATZART from <proxytable> where <proxytable>.SATZART = 'F' ) because no matter how it's actually implemented by the proxy software, it can't be much different from SELECT 1 (can it? oh silly me, of course it can, that's why we test 🙂

Of course, it you do this in many locations, backing out may be more attractive.

[thomas_duemesnil]

Select FIRST is not valid for oracle. And without a order by clause it is not valid in SQLA !

[thomas_duemesnil]

I have tested some of your ideas but the optimizer showed always a select FIRST 1 result if i use it in the exists context. I have replace the two positions with

select count(*) into n....; if n > 0 then

This curred my problem until the next EBF will cure the problem. The tables only hold a couple of rows.

BTW. Anybody recognized that all string values from ORACLE come back right padded with spaces to the full declared lenght since SQLA 16 ? I like to use that stuff.....

[Breck_Carter]

> BTW. Anybody recognized that all string values from ORACLE come back right padded with spaces to the full declared lenght since SQLA 16 ? I like to use that stuff.....

A search of the V16 Help on "oracle" reveals nothing about string padding in any of the "SQL Anywhere 16 - Changes and Upgrading" sections.

Please post a new message with simple queries that demonstrate the before-and-after behavior, with exact SQL Anywhere and Oracle version numbers... that would be greatly appreciated! Lots of people work with Oracle as remote databases.

[VolkerBarth]

FWIW, may this be documented in this FAQ here? - I'd like to link to the according CR website page but alas, they won't let me anymore...

---

I should not mourn that much... - given Jeff's direct URLs to the most current version-specific EBF Readme's, one can see that this CR 691040 has been fixed in 12.0.1.3748 and has not been fixed in v16 EBFs so I'd expect it has been already corrected in the v16 GA...

[Former Member]

Thank you from reporting this Thomas

This does appear to be a problem introduced early in version 12 and we are investigating this. It is clear the syntax getting passed to the remote server is incompatible and difficult to workaround in some cases.