Problems with casts and TO_DATE in WHERE clauses in views

[Tom Grayson <tg...@bluemetal.com>]

I'm using Phoenix 4.2.2 and am having problems with using either a CAST or the TO_DATE function in WHERE clauses in views. The view query is apparently parsed into an invalid syntax that will not execute. Possibly these are related to bug PHOENIX-1646<https://issues.apache.org/jira/browse/PHOENIX-1646> (Views and functional index expressions may lose information when stringified), which is fixed in Phoenix 4.3, but I'm not sure this bug is relevant. Is there a workaround for 4.2? Here are some simple examples of the issue.

create table t (d date primary key);

-- TO_DATE
-- The query below works, although it returns no rows.
select *
from t
where d > to_date('2015-07-09', 'yyyy-MM-dd');

-- Create a view based on the query above.
create view v as
select *
from t
where d > to_date('2015-07-09', 'yyyy-MM-dd');

-- The query below fails with the error:
-- ERROR 203 (22005): Type mismatch. DATE and VARCHAR for D > '2015-07-09 00:00:00.000' [SQL State=22005, DB Errorcode=203]
select * from v;

-- The query below for the view statement returns:
-- SELECT * FROM "T" WHERE D > '2015-07-09 00:00:00.000'
select view_statement
from system.catalog
where table_name = 'V'
and view_statement is not null;

-- CAST
-- The query below works, although it returns no rows.
select *
from t
where cast(d as bigint) > 0;

-- Create a view based on the query above.
create view w as
select *
from t
where cast(d as bigint) > 0;

-- The query below fails with the error:
-- ERROR 605 (42P00): Syntax error. Unknown function: "TO_LONG". [SQL State=42P00, DB Errorcode=605]
select * from w;

-- The query below for the view statement returns:
-- SELECT * FROM "T" WHERE TO_LONG(D) > 0
select view_statement
from system.catalog
where table_name = 'W'
and view_statement is not null;

Tom Grayson