phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gabriel Reid <gabriel.r...@gmail.com>
Subject Re: Problems with casts and TO_DATE in WHERE clauses in views
Date Sun, 19 Jul 2015 15:36:20 GMT
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 <tgrayson@bluemetal.com> 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:* '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
>
>
>

Mime
View raw message