SELECT contant value is Cassandra

5
votes

I'm trying to do a simple select in Cassandra CQL3 containing a hardcoded value (constant) under a constant column name and I simply can't get it working

Here's the query

SELECT 1 as "id"

Works fine in all kinds of DBMS I use but throws this error here:

Error: ResponseError: line 1:7 no viable alternative at input '1' (SELECT [1]...)

What's the correct syntax?

2
cassandracqlcql3

2 Answers

6
votes

Unfortunately, CQL is not SQL, and queries like this do not work in cqlsh as they do in their relational counterparts. The DataStax SELECT documentation indicates that a selector must be one of:

  1. column name
  2. WRITETIME (column name)
  3. TTL (column name)
  4. function

Now while a SELECT 1 as id query may not work, there are other, slightly more useful things that do. For instance, if I need to quickly generate a UUID, I can do so with the following query:

aploetz@cqlsh:stackoverflow> SELECT uuid() FROM system.local;

 system.uuid()
--------------------------------------
 a55c17f7-d19d-4531-85be-75551e3fd546

(1 rows)

This works the way it does for two reasons:

  1. The SELECT clause invokes the uuid() function.

  2. The system.local table only ever contains a single row. If you ran this SELECT against another table, it would return as many UUIDs as there were CQL rows.

1
votes

I have found an extraordinarily icky way to do this.

SELECT blobAsBigInt(bigintAsBlob(1)) AS c FROM table;

will give you a constant column of value 1. If you want to try this all by itself, make sure to

SELECT blobAsBigInt(bigintAsBlob(1)) AS c FROM table LIMIT 1;

or you will never hear the end of it.