On 11/11/17, R Smith <rsm...@rsweb.co.za> wrote:
>
> Further to this, an Identifier can remain unquoted (plain text), except:
> ....
> - when it is the same as an SQLite Keyword.
Correct.
Unfortunately, we do occasionally add new keywords. The most recent
example is version 3.8.3 (2014-02-03) when we added support for common
table expressions, which required two new keywords: WITH and
RECURSIVE.
In order to make sure that new keywords do not break legacy
applications that might be using those keywords as identifiers, the
parser is rather forgiving of the misuse of keywords as identifiers.
Whenever a keyword token is encounter in a context where an identifier
would make sense but the keyword would be a syntax error, the token
can be used as an identifier. This kind of thing is discouraged,
since it can result in SQL that is confusing to human readers, but it
does have the virtual of preserving backwards compatibility.
So, for example, even though WITH and RECURSIVE are now keywords, you
can still say:
CREATE TABLE t1(with,recursive);
SELECT with FROM t1 WHERE recursive=1;
I repeat: Even though you can do this, you should not.
I have observed that identifiers in SQLite databases on Macs and
iPhones always start with the letter Z. There are no SQL keywords
that begin with Z, so I'm guessing the initial Z in Mac/iOS
identifiers is to avoid the possibility of any future keyword
collisions.
I also observe that many programmers familiar with SQL-Server put all
identifiers inside [...]. As far as I know, SQL-Server and SQLite are
the only database engines that support this syntax. Putting all
identifiers inside [...] helps to prevent problems in the case that
new keywords get added in the future, just alike prepending Z to all
identifiers does on Mac/iOS.
--
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
