Quoted keywords cannot be used as column values

[qs5779]

Let me first show the output for one of your examples.

$ sqlitis "SELECT id FROM foo WHERE id = 128"
select([foo.c.id]).where(text('id') == 128)

That looks good to me, so what is the issue with this?
$ sqlitis "SELECT ckey, cval FROM tinfo WHERE ckey = version"
select([tinfo.c.ckey, tinfo.c.cval]).where(text('ckey'))

There seems to be no comparison.

[qs5779]

okay, apparently it doesn't like the fact that the ckey column could contain the word version, which in my very humble opinion still means it is broken.

$ sqlitis "SELECT ckey, cval FROM tinfo WHERE ckey = dbvers"
select([tinfo.c.ckey, tinfo.c.cval]).where(text('ckey') == text('dbvers'))

[pglass]

Hi @qs5779,

That looks good to me, so what is the issue with this?
$ sqlitis "SELECT ckey, cval FROM tinfo WHERE ckey = version"
select([tinfo.c.ckey, tinfo.c.cval]).where(text('ckey'))

There are two issues here:

1. Your SQL query is invalid. Since version is a string (text or varchar), it must be quoted:

   • SELECT ckey, cval FROM tinfo WHERE ckey = version (invalid)
   • SELECT ckey, cval FROM tinfo WHERE ckey = 'version' (valid)

2. There is still a bug. version is a keyword in some SQL dialects (at least sqlparse thinks so). Even with quoting sqlitis doesn't recognize 'version' as a string.

A workaround is to use either double-quotes ("version") or backticks (`version`). However these are not typically valid SQL queries (because double-quotes and backticks are for reserved words / identifiers)

$ sqlitis 'SELECT ckey, cval FROM tinfo WHERE ckey = "version"'
select([tinfo.c.ckey, tinfo.c.cval]).where(text('ckey') == "version")
$ sqlitis 'SELECT ckey, cval FROM tinfo WHERE ckey = `version`'
select([tinfo.c.ckey, tinfo.c.cval]).where(text('ckey') == text('version'))