@Simon: Thanks for pointing that out! The difference in the effects of using single vs. double quotes in the select query is still the same though.
@Richard: Thanks for taking time to explain this to me and for pointing me to the comment in the source code! I've found another case where, depending on the context, a string in the ORDER BY clause is either interpreted as a column name or a string (see below). CREATE TABLE test (c0); SELECT * FROM test ORDER BY 'asdf'; SELECT (0) INTERSECT SELECT * FROM test ORDER BY 'asdf' In the first query, ORDER BY 'asdf' is interpreted as a string and does not cause any error, while in the second query it causes "1st ORDER BY term does not match any column in the result set". As you pointed out, there is no point in having a constant expression in such contexts, and I assume that this case is also related to backward compatibility. Best, Manuel Am Sa., 27. Apr. 2019 um 22:14 Uhr schrieb Richard Hipp <d...@sqlite.org>: > On 4/27/19, Manuel Rigger <rigger.man...@gmail.com> wrote: > > Thanks for your quick and helpful reply! So if I understood correctly, > > there is no way to ensure that a string is not interpreted as a column in > > an arbitrary expression, right? > > String literal is always just a string literal in an arbitrary > expression. String literals can only be interpreted as an identifier > in a context where only an identifier is allowed, such as after > "CREATE TABLE", for example. > > The one exception to the above is that if the argument to CREATE INDEX > is an expression that is comprised of a single string literal, then > that string literal is interpreted as a column name rather than as an > expression. This is done for historical compatibility, and because > there is no point in having an index on a constant expression. See > the implementation at > https://www.sqlite.org/src/artifact/61655dad911a?ln=1319-1341 > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
