Presto: Date Type query in Cassandra is giving Codec not found error

[ChethanUK]

Presto Cassandra Test

Cassandra Version: [cqlsh 5.0.1 | Cassandra 3.11.4 | CQL spec 3.4.4]
PrestoSQL Version: 307

Content of cassandra.properties:

connector.name=cassandra
cassandra.contact-points=localhost

First I installed Cassandra in New Ubuntu
Then in cqlsh: Created tables following tables

cqlsh:testdb> create table testdb.employee (id int, name text, dob date, PRIMARY KEY (id));
cqlsh:testdb> create table testdb.dept (id int, name text, dod date, PRIMARY KEY (id));
cqlsh:testdb> create table testdb.mapper (e_id int, d_id int, PRIMARY KEY (e_id, d_id));
cqlsh:testdb> create table testdb.student (id int, name text, dob timestamp, PRIMARY KEY (id));
cqlsh:testdb> create table testdb.school (id int, name text, dod timestamp, PRIMARY KEY (id));

Then Inseting Data into Cassandra:

cqlsh:testdb> insert into testdb.employee (id, name, dob) values (1, 'somi', '1988-01-01');
cqlsh:testdb> insert into testdb.employee (id, name, dob) values (2, 'name1', '1986-11-21');
cqlsh:testdb> insert into testdb.employee (id, name, dob) values (3, 'abc', '1989-01-01');
cqlsh:testdb> insert into testdb.dept (id, name, dod) values (1, 'software', '2017-01-01');
cqlsh:testdb> insert into testdb.dept (id, name, dod) values (2, 'data', '2019-01-01');
cqlsh:testdb> insert into testdb.mapper (e_id, d_id) values (1,1);
cqlsh:testdb> insert into testdb.mapper (e_id, d_id) values (2,2);
cqlsh:testdb> insert into testdb.mapper (e_id, d_id) values (3,1);
cqlsh:testdb> insert into testdb.student (id, name, dob) values (1, 'somi', '1988-01-01');
cqlsh:testdb> insert into testdb.student (id, name, dob) values (2, 'nilu', '1986-01-01');
cqlsh:testdb> insert into testdb.student (id, name, dob) values (3, 'abc', '1989-01-01');
cqlsh:testdb> insert into testdb.school (id, name, dod) values (1, 'software', '2017-01-01');
cqlsh:testdb> insert into testdb.school (id, name, dod) values (2, 'data', '2019-01-01');

In Presto CLI: When I tried querying date type, PrestoSQL is not working properly it's giving errors

When I try to get all the data

presto:testdb> select * from dept;

Query 20190404_131345_00011_ubw24, FAILED, 1 node
Splits: 116 total, 64 done (55.17%)
0:01 [0 rows, 0B] [0 rows/s, 0B/s]

Query 20190404_131345_00011_ubw24 failed: Codec not found for requested operation: ['org.apache.cassandra.db.marshal.SimpleDateType' <-> com.datastax.driver.core.LocalDate]]

presto:testdb> describe dept;
 Column |  Type   | Extra | Comment
--------+---------+-------+---------
 id     | integer |       |
 dod    | date    |       |
 name   | varchar |       |
(3 rows)

Error: Codec not found for requested operation: ['org.apache.cassandra.db.marshal.SimpleDateType' <-> com.datastax.driver.core.LocalDate]

But, if we select only id (which is an integer) then query works properly:

presto:testdb> select id from dept;
 id
----
  2
  1
(2 rows)

Querying to select the data with '>' particular date also not working..

presto:testdb> select id from dept where dod > '2018-01-01';
Query 20190404_132443_00014_ubw24 failed: line 1:31: '>' cannot be applied to date, varchar(10)
select id from dept where dod > '2018-01-01'

Also there is Codec not found for requested operation

presto:testdb> select id from dept where dod > date '2018-01-01';

Query 20190404_132455_00015_ubw24, FAILED, 1 node
Splits: 115 total, 53 done (46.09%)
0:01 [0 rows, 0B] [0 rows/s, 0B/s]
**Query 20190404_132455_00015_ubw24 failed: Codec not found for requested operation: ['org.apache.cassandra.db.marshal.SimpleDateType' <-> com.datastax.driver.core.LocalDate]**

Datetime need to casted into timestamp to query

presto:testdb> select * from school where dod > '2018-01-01';
**Query 20190404_133039_00018_ubw24 failed: line 1:32: '>' cannot be applied to timestamp, varchar(10)**

Since I cast into timestamp its working

presto:testdb> select * from school where dod > timestamp '2018-01-01';
 id |           dod           | name
----+-------------------------+------
  2 | 2019-01-01 00:00:00.000 | data
(1 row)

[ebyhr]

Please specify cassandra.protocol-version=V4 in cassandra.properties.

[ChethanUK]

Working:

presto:testdb> select * from dept;
 id |    dod     |   name   
----+------------+----------
  1 | 2017-01-01 | software 
  2 | 2019-01-01 | data     
(2 rows)

Query 20190404_144906_00003_thvdf, FINISHED, 1 node
Splits: 273 total, 273 done (100.00%)
0:04 [2 rows, 2B] [0 rows/s, 0B/s]

Thanks!. @ebyhr

What about timestamp?

select * from school where dod > '2018-01-01';
Query 20190404_145258_00005_thvdf failed: line 1:32: '>' cannot be applied to timestamp, varchar(10)
select * from school where dod > '2018-01-01'

[ebyhr]

I think it needs cast.

select * from school where dod > timestamp '2018-01-01';

[findepi]

As discussed offline, this is answered.

Action items.

• https://github.com/prestosql/presto/issues/591 -- better exception messages
• https://github.com/prestosql/presto/issues/278 -- best possible out-of-the-box feature availability

Thanks @ebyhr @ChethanUK