#1465 TO_CHAR not work into view

[Thibault Joseph]

When I create a view on a table, if I use TO_CHAR into the view definition, HSQLDB doesn't find the column.
With an example :

I create a table MY_TABLE

CREATE TABLE MY_TABLE (
NUMBER_COL NUMBER(12,0));

If I create the view where I do a TO_CHAR on NUMBER_COL

create view MY_VIEW(CHAR_COL) 
 AS SELECT TO_CHAR(NUMBER_COL)
FROM MY_TABLE;

I have this error :

Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: NUMBER_COL
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.ParserDQL.readTableName(Unknown Source)
    at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
    at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
    at org.hsqldb.ParserDQL.XreadJoinedTableAsView(Unknown Source)
    at org.hsqldb.ParserDQL.XreadJoinedTableAsSubqueryOrNull(Unknown Source)
    at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
    at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
    at org.hsqldb.ParserDQL.XreadJoinedTableAsView(Unknown Source)
    at org.hsqldb.ParserDDL.compileCreateView(Unknown Source)
    at org.hsqldb.ParserDDL.compileCreate(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)

But, if I create the view without TO_CHAR, it work :

create view MY_VIEW(CHAR_COL) 
 AS SELECT NUMBER_COL
FROM MY_TABLE;

[Fred Toussi]

This should work in the next version. Please get the latest snapshot from the support page and check it. http://hsqldb.org/repos/org/hsqldb/hsqldb/SNAPSHOT/

[Fred Toussi]

Ticket moved from /p/hsqldb/feature-requests/335/

[Thibault Joseph]

I have test the latest SNAPSHOT and it work, thanks.