Problem where value in column matches column name
darryllee opened this issue · 3 comments
Problem was driving me crazy until I realized what it was and I worked around it by renaming the Admin column to Administrator:
example.csv:
Space,Space Type,Id Type,Id,View,Admin
AAPM,global,group,darryl,Yes,Yes
AD,global,group,joe,Yes,Yes
ADSKB,global,group,admin,Yes,Yes
ADSKB,global,user,sysadmin,Yes,Yes
ADV,global,group,space-admin,Yes,Yes
AE,global,user,marty,Yes,Yes
AEE,global,group,admin,Yes,Yes
AEE,global,user,sysadmin,Yes,Yes
% q -H -d ',' 'select space,id from ./example.csv where id not in ("sysadmin","admin")'
AD,joe
ADSKB,admin
ADV,space-admin
AE,marty
AEE,admin
Even a simple query comes up empty:
q -H -d ',' 'select space,id from ./example.csv where id="admin"'
Fully quoted CSV has the same issue.
@darryllee thanks for reporting this.
This is indeed a crazy one :)
The root reason for this is the fact that sqlite (the engine behind q) handles double-quoted strings as identifiers first, and only then falls back to a string literal.
Converting the queries to use single-quotes for string literals forces sqlite to treat them as string-literals:
$ q -H -d ',' "select space,id from ./example.csv where id not in ('sysadmin','admin')"
AAPM,darryl
AD,joe
ADV,space-admin
AE,marty
$ q -H -d ',' "select space,id from ./example.csv where id='admin'"
ADSKB,admin
AEE,admin
$
Notice that the string-literals are surrounded by single-quotes, and for convenience, the entire query is double-quoted and not single-quoted.
The reference to sqlite's docs about this subject - https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted