The issue here is that casting from an UNSIGNED_LONG to TIMESTAMP isn't
currently supported in Phoenix. This seems like a bug, could you log a JIRA
ticket for it?
This doesn't have anything to do with the use of a view, you'll get the
same behavior when using a normal table that contains an UNSIGNED_LONG
field. The reason that your original statement works (where you're casting
a literal to a timestamp) is that the literal is a BIGINT, and not an
UNSIGNED_LONG.
As a workaround, you can cast the UNSIGNED_LONG to a BIGINT first, and then
cast it to a TIMESTAMP, i.e.
select a, b, ts, CAST(CAST(ts AS BIGINT) AS TIMESTAMP) from vt;
- Gabriel
On Fri, Mar 13, 2015 at 1:58 AM Sergey Belousov <[email protected]>
wrote:
> Hi All
>
> I have some weird thing going on and hope somebody can help here.
>
> HBase shell:
>
> create 't','f1'
> put
> 't',"\x00\x00\x00\x01\x00\x00\x00\x01\x00\x00\x01L\x0Fz,\
> x1E",'f1:c1','test'
>
> sqlline:
>
> CREATE VIEW vT
> ( a UNSIGNED_INT NOT NULL
> ,b UNSIGNED_INT NOT NULL
> ,ts UNSIGNED_LONG NOT NULL
> CONSTRAINT pk PRIMARY KEY (a, b, ts))
> AS SELECT * FROM "t"
> DEFAULT_COLUMN_FAMILY ='f1';
>
> select a, b, ts, CAST(1426188807198 AS TIMESTAMP) from vt;
> +----+----+----------------+------------------------------+
> | A | B | TS | TO_TIMESTAMP(*1426188807198*) |
> +----+----+----------------+------------------------------+
> | 1 | 1 | *1426188807198 *| 2015-03-12 19:33:27.198 |
> +----+----+----------------+------------------------------+
>
> but
>
> select a, b, ts, CAST(ts AS TIMESTAMP) from vt;
> Error: ERROR 203 (22005): Type mismatch. UNSIGNED_LONG and TIMESTAMP for TS
> (state=22005,code=203)
>
> So questions is
> 1. Why CAST from VIEW does not work and should it?
> 2. TO_TIMESTAMP internal function? (I can not do TO_TIMESTAMP() in query)
>
> Thank you
> SB
>
