Column quoting problem leads to silent select failure

General
#1

I’m seeing some strange behavior when using gorm and cockroach v2.0.0 on Linux, and have reproduced it directly using the sql repl interface.

I created a table, using the same syntax generated by the postgres driver in gorm:
create table test.foo ("name" text NOT NULL, "user" bigint, "ref" bigint)

The admin UI shows the table as this (note that some columns have quotes):

CREATE TABLE foo (
	"name" STRING NOT NULL,
	"user" BIGINT NULL,
	ref BIGINT NULL,
	FAMILY "primary" ("name", "user", ref, rowid)
)

Note the difference between ‘user’ and ‘ref’ column representation. In another table I tested, ‘user’ is quoted but another 4 columns are not. Now if I insert a row containing a NULL for user, then this returns 0 rows and no error:
select * from test.foo where user IS NULL;

It also returns 0 rows for “IS NOT NULL”. No error is returned, but it does work by adding quotes to the field name, ie select * from test.foo where "user" IS NULL;. This is pretty confusing behavior. Why are quotes kept for some columns but not others? Also seems that it should return an error rather than silently fail if it doesn’t believe that the column exists.

#2

Hi Valient!
Thank you for your interest in CockroachDB.

You just stumbled upon an oddity in the PostgreSQL dialect that CockroachDB is supporting.

The key idea to understand what is going on is that PostgreSQL’s dialect supports the “USER” keyword as an alias for the current_user() function. So your example query really means SELECT * FROM test.foo WHERE current_user() IS NULL, which obviously never matches.

Likewise, any keyword that’s also valid as an identifier for database/table/column name must be quoted in order to lose its special role as keyword.

For this reason, we recommend new code to always quote simple identifiers. As an alternative you can qualify the identifier – if your identifier is qualified, the special keyword recognition does not kick in. For example SELECT * FROM test.foo WHERE test.foo.user IS NULL would work (also WHERE foo.user).

For what it’s worth, PostgreSQL would also show this odd behavior.

You can also always check what is going on with the EXPLAIN statement.

This behavior is indeed unfortunate and perhaps we should consider issuing a warning when using the word “user” in a query, or indeed any other built-in function that masquerades as a keyword.

#3

I filed https://github.com/cockroachdb/cockroach/issues/24921 to track this UX pain.