3615847 - sql anywhere error -660: data type conversion is not possible due to misinterpretation of "||" operator during query preparation: IQ

String concatenation || is replaced by the + concatenation operator when accessing IQ remote tables (RDA/CLASS 'IQODBC')
RDA's DEV query preparation ignores PRD's implicit type casting raising error:
Data type conversion is not possible. Second operand to + (ADD) operator must be a valid arithmetic expression. 

When executing SQL with fields that are concatenated with different data types, IQ will implicitly convert compatible data types to be the same.
This implicit conversion is a built-in behavior when run on the local IQ Server(IQ PROD).

The Same SQL, when executed from DEV server, using the remote server setup to access the PRD tables remotely.
The SQL is executed as is, without any implicit conversions, and the different datatypes in the concatenation then fail. 

This can be used to reproduce
1- In IQ Server A
create table Tab1(colA int, colB varchar(100)) ;
insert tab1 values(1, 'abcd') ;
commit;
 
select colA || colB from Tab1
Ok
 
 
2- In IQ server B :
CREATE SERVER IQ_A 
    CLASS 'IQODBC' 
    USING 'Driver=SAP IQ;Server=IQ_srv_A;Host=xxx:2640;Database=iqdemo';
CREATE EXTERNLOGIN dba TO IQ_A  
REMOTE LOGIN dba IDENTIFIED BY <dba_pwd_in_IQ_A> ;
create existing table  Tab1 at 'IQ_A.iqdemo.dba.Tab1' ;
 
select colA || colB from Tab1
 
Not OK. Error :
There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Server 'IQ2': [SAP][ODBC Driver][SAP IQ]Data type conversion is not
possible. Second operand to + (ADD) operator must be a valid arithmetic
expression. 
-- (dflib/dfe_ArithmeticExpression.cxx 304)
SQLCODE=-660, ODBC 3 State="HY000"
 
 
In srvlog of IQ Server B with debugging (cis_option=7) :
 
I. 06/03 07:15:59.848817 Start DEBUG Remote Query
I. 06/03 07:16:00.140695 The query is being processed in FULL PASSTHRU mode.
 
I. 06/03 07:16:00.140921 The Original Statement is
        select Tab1.colA || Tab1.colB from Tab1
I. 06/03 07:16:00.140941 The Virtual Statement is
        select vt_1.expr_1 from vt_1
I. 06/03 07:16:00.140952 The Remote Statement for vt_1 is
        select Tab1.colA || Tab1.colB from Tab1
I. 06/03 07:16:00.634063 Execute (IQ2):
SELECT t1."colA" + t1."colB" FROM log2b."dba"."Tab1" t1
I. 06/03 07:16:05.021878 End DEBUG Remote Query

The ISO/ANSI SQL string concatenation operator || has been replaced by the + operator (supported by SAP Adaptive Server Enterprise)

SAP IQ 16.1 SP05 PL15

sql anywhere error -660, data type conversion, || operator, query preparation, sybase iq, remote data access, rda, saodbc, odbc driver, multiplex architecture, standalone nodes, libdbodbc17.so, ||, +, concatenation, string, strings, operator, valid, arithmetic, expression , KBA , BC-SYB-IQ , Sybase IQ , Problem