Failing pushdown for string literals in select list

[exaSR]

Possibly related to exasol/oracle-virtual-schema#26 ?

Virtual Schema for exasol fails on queries like this:

select 'VS_EXA_META'  as vs_name, root_name, object_name, raw_object_size, created, last_commit
from  VS_EXA_META.EXA_DBA_OBJECT_SIZES 
where object_name = 'CENSORED_NAME' 
limit 1;

with error

[Code: 0, SQL State: 04000] 
Adapter generated invalid pushdown query for virtual table EXA_DBA_OBJECT_SIZES:
Data type mismatch in column number 1 (1-indexed). Expected CHAR(14) ASCII, but got VARCHAR(14) UTF8.
(pushdown query: IMPORT FROM EXA AT "EXA_XXX" STATEMENT 'SELECT ''VS_EXA_META'', 
"EXA_DBA_OBJECT_SIZES"."ROOT_NAME", "EXA_DBA_OBJECT_SIZES"."OBJECT_NAME", 
"EXA_DBA_OBJECT_SIZES"."RAW_OBJECT_SIZE", "EXA_DBA_OBJECT_SIZES"."CREATED", 
"EXA_DBA_OBJECT_SIZES"."LAST_COMMIT" FROM "SYS"."EXA_DBA_OBJECT_SIZES" WHERE 
"EXA_DBA_OBJECT_SIZES"."OBJECT_NAME" = 'CENSORED_NAME'' LIMIT 1')

(again customer is looking for a workaround instead of properly reporting the problem)

[exaSR]

aside from the functionality issue I'm not sure pushing constants into select lists is a good idea, regarding performance / transfer amount

[ckunki]

Which version of Exasol database is used?
Which version of the Exasol Virtual Schemas is used?

[exaSR]

Virtual Schema on Exaosl 7.1.29, remote Exasol 8.29.3 (or both very close).
Adapter version -- no idea.

If you really need those for reproducing the Issue, I can find out.

[ckunki]

exasol/oracle-virtual-schema#26 mentions adapter property IMPORT_DATA_TYPES, documented for VSCJDBC, which means

• it was available for all virtual schemas, including Exasol virtual schema.
• did support values EXASOL_CALCULATED and FROM_RESULT_SET
• is deprecated since VSCJDBC version 12.0.0

Since then, character set UTF-8 is the default and can no longer be configured.
Potentially, this causes an issue when pushing constants into select lists as shown above.

[exaSR]

@ckunki - I'm a little surprised by the way, that the error message above does not include IMPORT INTO (...column names and types...) ... I thought that was the initial reason for the type mismatch?

[exaSR]

Another afterthought -- depending on which datatype shows up on which component,

• we should allow to load ASCII into UTF-8
• we might just TRY to load UTF-8 into ASCII and throw a DataException once a high-bit byte is found

So maybe the fix should be on database side rather the virtual schema.

[kaklakariada]

Minimal reproduction: select 'fixed_value' as col, name from %s where name = 'name'

Adapter generated invalid pushdown query for virtual table TAB: Data type mismatch in column number 1 (1-indexed). Expected CHAR(11) ASCII, but got VARCHAR(11) UTF8. (pushdown query: IMPORT FROM EXA AT "EXA_CONNECTION" STATEMENT 'SELECT ''fixed_value'', "TAB"."NAME" FROM "SOURCE_SCHEMA"."TAB" WHERE "TAB"."NAME" = ''name''')