search

harelba / q

q - Run SQL directly on delimited files and multi-file sqlite databases
http://harelba.github.io/q/
GNU General Public License v3.0
10.15k stars 419 forks source link

Error result when text content is C1,C2.... #294

Open weishengkui opened 2 years ago

weishengkui commented 2 years ago

CONTENT:

worthless       C1      adjective
worthwhile      B2      adjective
worthy  C1      adjective
would   B1      modal verb

q -t 'select count(distinct(c1)) from CEFR.txt where c2 = "C1"' result: 0

harelba commented 2 years ago

wow, that's an interesting bug... thanks for reporting it. I'll take a deeper look

harelba commented 2 years ago

Hi, sorry for the late reply.

The reason that the query returns 0 as the result is due to the fact that the string literal C1 is double-quoted and not single-quoted. In sqlite (the underlying engine/syntax of q), double-quoted strings are treated as identifiers (e.g. column names in this case), and not as string literals, which means that the original example query essentially compared between the value of column c2 to the value of column c1, returning an empty set.

Running the same query with single-quotes returns the correct results. 

$ q -t "select count(distinct(c1)) from CEFR.txt where c2 = 'C1'"
2

I've changed the wrapping of the entire query to double-quotes as well in this query, to prevent the need escape the single-quotes.

Hope that helps Harel

harelba commented 2 years ago

@weishengkui - Can you update whether or not this fixed your issue?