When running the following query through Teiid against an HSQL database:
SELECT CAST(N_NATIONKEY AS VARCHAR(100)) FROM NATION
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. HSQL doesn't support VARCHAR without length.
Note: this query was generated by Tableau, and there's no easy way to change it to use the LFT function, as suggested by Steven in https://developer.jboss.org/thread/267816