RE: Problems with casts and TO_DATE in WHERE clauses in views

[Tom Grayson]

No one has responded to my question, so I'll be more direct: Does the SQL code 
below work in Phoenix 4.3.1 or 4.4? I don't have one of these installations to 
test, so I'd greatly appreciate a response from the list.

Tom

From: Tom Grayson
Sent: Tuesday, July 14, 2015 11:11 PM
To: 'user@phoenix.apache.org' <user@phoenix.apache.org>
Subject: Problems with casts and TO_DATE in WHERE clauses in views

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