'SELECT ... WHERE' fails when column name doesn't end with a digit

[stevegt]

This has got to be a bug in either the code or docs -- I can't for the life of me figure out how to get a simple SELECT with WHERE clause to work:

$ csvq -v
csvq version 1.15.2

$ cat test.csv 
order,board,status
12345,a1,cut

$ csvq 'select * from test where order = 12345'
[L:1 C:26] syntax error: unexpected token "order"

$ csvq 'select * from test where "order" = 12345'
Empty RecordSet

I've tried several other variations of quoting but haven't hit a working combination yet -- the furthest I've gotten so far is that empty recordset. But go test -v ./... passes. I created the above test file on Linux using VIM -- attaching a copy here.

test.csv

[stevegt]

Whups -- looks like a parser bug. Works when column names end in a digit, so could be identifier isn't being lexed or parsed correctly. Also a test case bug, 'cause all of the test case column names do end in digits, so this would not have been caught:

$ cat test.csv 
order,board,status
12345,a1,cut

$ csvq 'select * from test where order = 12345'
[L:1 C:26] syntax error: unexpected token "order"

$ cat test2.csv 
order1,board,status
12345,a1,cut

$ csvq 'select * from test2 where order1 = 12345'
+--------+-------+--------+
| order1 | board | status |
+--------+-------+--------+
| 12345  | a1    | cut    |
+--------+-------+--------+

[mithrandie]

ORDER is a keyword in SQL, so you can't use it as a column name as it is. Reserved words, including keywords, must be enclosed in specific characters. You can use Grave Accents(U+0060 ` ), or Quotation Marks(U+0022 ") if –ansi-quotes is specified, as enclosing characters.

$ csvq 'select * from test where `order` = 12345'

$ csvq —ansi-quotes 'select * from test where "order" = 12345'

[stevegt]

Oh. My. I am a total idiot. And here I was digging around in parser.y. That's what I get for avoiding writing any new SQL for the last 14 years -- I had totally forgotten the part about avoiding keywords. I also see now that sqlite doesn't do any better with the error message -- it just says Error: near "order": syntax error in that situation.

Now I know why I had so much trouble creating that csv in the first place. ;-)

Closing this one, and thanks.