Teiid drops VARCHAR length, causing an HSQL syntax error

[gsteinberg]

When running the following query through Teiid:

    SELECT CAST(N_NATIONKEY AS VARCHAR(100)) FROM NATION

against an HSQL database, Teiid drops the length from the query, sending to HSQL the query

    SELECT CAST(N_NATIONKEY AS VARCHAR) FROM NATION

which causes an HSQL syntax error (it doesn't support VARCHAR without length).

When running the same query against Oracle, the query it creates is

    SELECT SELECT to_char(N_NATIONKEY) FROM NATION

which works, but doesn't alter the field length.

Does anyone know if there is a way around this?

Thanks,

Gabi Steinberg.

[shawkins]

Teiid effectively ignores constraints on datatypes.  The parsing support was added for DDL, but I don't see an explicit reference in the docs that they are effectively ignored in casts so one will need to be added.

The HSQL issue is a bug.  The translator needs to be aware that the cast requires a length.

In general you'd want to use the the left function.  You can also log an enhancement for Teiid to respect the length for a varchar cast.

[gsteinberg]

Thanks, Steven.

I will create a bug for the HSQL issue.  Unfortunately this query was created by Tableau, and I doubt I can convince it to use the LEFT function instead of a CAST to VARCHAR,