Hi Tom, I've tried your SQL statements with 4.3.1, and the initial one does indeed work in 4.3.1 and later. The view definition that includes a CAST statement still fails (due to an bug in CastParseNode for which I'll post a patch shortly).
By the way, the way I tested this (and an easy way to test various things with Phoenix) is by using the included sandbox. You can checkout out, build, and run a fully-contained Phoenix installation as follows: $ git clone https://git-wip-us.apache.org/repos/asf/phoenix.git $ cd phoenix $ git checkout v4.3.1 $ mvn clean install -DskipTests $ ./bin/phoenix_sandbox.py Once the sandbox has started up, it'll print out the command that you can use to access it via sqlline.py. - Gabriel On Sat, Jul 18, 2015 at 3:39 AM Tom Grayson <[email protected]> wrote: > 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:* '[email protected]' <[email protected]> > *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 > > >
