Search code examples
cassandracqlcql3

SELECT contant value is Cassandra

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?

Solution

  • 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.