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.