Error using SIMILAR TO search condition on a proxy table

[cigaras]

Lets say I have a simple proxy table:

CREATE EXISTING TABLE prx AT 'remote_server;;dba;tbl';

If I filter it with SIMILAR TO:

SELECT * FROM prx WHERE prx.col SIMILAR TO 'a'

I get following error on SA version 17.0.9.4897:

Could not execute statement.
Server '': e:\\1709_rc2\\sqla\\src\\db\\engine\\omni\\qdexpr.c:1622
Query
Decomposition:  Unknown Node Type
SQLCODE=-660, ODBC 3 State="HY000"
Line 1, column 1

If I change SIMILAR TO to LIKE I do not get the error. Same error on version 16.0.0.2614. Any ideas?

[VolkerBarth]

So what's your remote server class - another SQL Anywhere server? (The proxy location with semicolon as delimiter puzzles me...)

FWIW, you can "debug" remote data access with the help of the "cis_option" option. I guess that a LIKE condition is passed through to the remote server, a "SIMILAR TO" expression probably not...

[Breck_Carter]

I'm guessing you are using IQ, and it doesn't support SIMILAR TO (at least, IQ 15.2 doesn't): Sybase IQ 15.2 > Reference: Building Blocks, Tables, and Procedures > SQL Language Elements

In other words, SQL Anywhere is pushing the entire query over to IQ, where LIKE will work but not SIMILAR TO.

If the table isn't too big, and/or it's static, one workaround would be to copy (INSERT SELECT) the entire contents into a local (non-proxy) SQL Anywhere table and then do the SIMILAR TO.

Or, pull SOME of the table across (keep the other predicates in the WHERE but remove SIMILAR TO) and then do the SIMILAR TO.