Problem where value in column matches column name

[darryllee]

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.

[harelba]

@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.

[harelba]

The reference to sqlite's docs about this subject - https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted

[darryllee]

Ahhh. . Quoting.

Thank you! And more generally, thank you for q. It is truly a game-changer for a lot of work I've been doing lately!!

On Sun, Jun 6, 2021 at 1:06 AM Harel Ben-Attia @.***> wrote:

The reference to sqlite's docs about this subject - https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/harelba/q/issues/260#issuecomment-855357851, or unsubscribe https://github.com/notifications/unsubscribe-auth/AANODJXDZE64ANJD2LZXNFLTRMUBDANCNFSM46EHV2EA .

--

--Darryl Lee @.***>