SELECT contant value is Cassandra

Question

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?

Canadian

Answer 1

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.

Pest

Thanks for the help. Sadly this won't help in what I'm trying to achieve. My use case is simple, I need to link queries made against multiple DBMS to an external ID. Works fine is SQL and even in MongoDB, not here. Seems like I have two choices, store a table containing dumb hardcoded IDs or get the ID out of the query – Canadian

Answer 2

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.

Nellienellir

Doesn't work. I always get "No viable input after (" error with this example. – Monika