2

I am trying to select data from cassandra db using below query but it is failing-

SELECT id from keyspace.table where code=123 and toTimestamp(now()) >= some_date;

Error- SyntaxException: line 1:103 no viable alternative at input '(' (...table where code=123 and [toTimestamp](...)

Looks like toTimestamp(now()) is causing the issue. Can someone plz suggest what is the issue and solution to this? Thanks.

Alex Ott
  • 80,552
  • 8
  • 87
  • 132
Yogesh
  • 21
  • 4
  • Could you add your table schema to the question please. – mikea Jun 10 '20 at 10:03
  • Actually it worked by swapping date condition like this - SELECT id from keyspace.table where code=123 and some_date <= toTimestamp(now()); – Yogesh Jun 10 '20 at 11:49

1 Answers1

0

You can't use functions in the WHERE statement. So the only workaround is to get current time inside your application, and pass it to the query. This request is tracked as CASSANDRA-8488.

But in reality, your query should have condition on some column, not on the calculated value.

Alex Ott
  • 80,552
  • 8
  • 87
  • 132
  • Actually it worked by swapping date condition like this - SELECT id from keyspace.table where code=123 and some_date <= toTimestamp(now()); – Yogesh Jun 10 '20 at 11:49
  • @Yogesh anyway, it won’t work until mentioned Jira is implemented – Alex Ott Jun 10 '20 at 11:50
  • @AlexOtt i think `some_date` is his column name. – Ersoy Jun 10 '20 at 12:09
  • Sorry guys if above query is creating confusion as I was trying to make general query. Here is the original query and it worked after swapping the said condition - SELECT originator_id from my_keyspace.vod_originator_sid_map where effective_start_date <= toTimestamp(now()) ALLOW FILTERING – Yogesh Jun 10 '20 at 12:16
  • anyway, `WHERE` condition doesn't support functions – Alex Ott Jun 10 '20 at 12:36
  • @Aaron without ALLOW FILTERING query isn't getting executed. Gives an error - InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING" Any suggestions as I am new to Cassandra. – Yogesh Jun 11 '20 at 05:10
  • 1
    @Yogesh Yes, you probably need to rebuild your table based on your query pattern. And in this case you'll likely need a time "bucket." Take a look at my answer to this question: https://stackoverflow.com/questions/57106586/range-query-on-clustering-key – Aaron Jun 11 '20 at 13:40
  • @Yogesh I recommend to take DS201 & DS220 courses at https://academy.datastax.com/ – Alex Ott Jun 11 '20 at 14:32