Bind variables are case insensitive

[nickhristov]

I have the following java code:

ImmutableMap<String, Object> parameters = ImmutableMap.of("ldId", testRequest.getLdId(), "device_created_on", testRequest.getDate());
Statement statement = new SimpleStatement("SELECT * FROM location_data.location_data WHERE ld_id = :ldId AND device_created_on = :device_created_on", parameters);
ResultSet resultSet = session.execute(statement);

When I kick this off, I get a error from the server:

Caused by: com.datastax.driver.core.exceptions.InvalidQueryException: SQL error (yb/ql/ptree/process_context.cc:181): Invalid Arguments. SQL error (yb/ql/ptree/process_context.cc:181): Invalid Arguments. Runtime error (yb/cqlserver/cql_message.cc:66): Bind variable "ldid" not found
SELECT * FROM location_data.location_data WHERE ld_id = :ldId AND device_created_on = :device_created_on
^^^^^^
 (error -304)

Notice that the bind variable that it is looking for is ldid, while the variable name which I have in the statement is ldId.

When I make all variables lowercase, the query works.

[robertpang]

@nickhristov Yes. The bind variable name in a query is treated like other table or column identifiers. When they are unquoted, they are case-insensitive (and canonicalized to lowercase). So when you bind to the variable, please use lowercase.

Alternatively, if you want to preserve case, you may use double-quoted identifier like:

Statement statement = new SimpleStatement("SELECT * FROM location_data.location_data WHERE ld_id = :\"ldId\" AND device_created_on = :device_created_on", parameters);

[nickhristov]

I think it is fine for bind variables to be case insensitive. But I still feel this is a bug. If the behavior with cassandra is the same, then I guess you guys can close the issue.

However, if bind variables are to be case-insensitive, then they should be case insensitive in both the statement and the binding parameter key set. In other words, I would have expected that the behavior is to actually re-write (lower-case) the bind variables in both places: both the map and the statement.

In such cases, the client will be completely agnostic to whether the variables should be all lower cased or not. In fact, the client can be mostly oblivious to case sensitivity issue: it will work in most cases. The only case where this would not work is if some client mixes up casing (e.g. they put in "someVariable" and "somevariable" in the same statement with different semantics and put two keys in the parameters, one "somevariable" and one "someVariable").

Anyway, here is what I would consider to cause the list surprise in the application programmer. Let's take as an example some[v|V]ariable:

1. statement : someVariable, bind parameter someVariable. - WORKS
2. statement : somevariable, bind parameter somevariable. - WORKS
3. statement : somevariable, bind parameter someVariable. - ERROR, messed up parameter binding. Technically, if that does work, it wouldn't be a huge problem, except in the edge case discussed above, where two variables with different casing have different semantics.
4. statement : someVariable, bind parameter somevariable. - ERROR, like above.

Again, if (1) works like cassandra, then I guess this is not a bug, just learnings on my side.

[robertpang]

@nickhristov We tested Apache Cassandra and it has the same behavior. In fact, other databases such as Oracle have the same behavior (except that Oracle canonicalizes an unquoted bind variable identifier to uppercase instead).

This different handling of the variable name in the statement and in the bind call can be a bit confusing. I believe the rationale behind is that inside a statement, the bind variable is specified by an identifier with a choice of quoted or unquoted identifier, whereas in the bind call the variable is specified by name directly. There will be a slight performance penalty to let the latter be specified as an identifier and to parse it and I guess there just isn't a strong need for that.