----- Original Message -----
From: "Robert Simpson" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Tuesday, September 19, 2006 2:19 PM
Subject: RE: [sqlite] Quotation handling bug?
Single quotes are supposed to be used for string literals, and double
quotes/brackets for identifiers such as table names and column names.
SELECT 'ID' FROM 'MYTABLE' is selecting the literal string 'ID', not the
column.
I am not positive, but I think if you use single quotes around something,
SQLite will first try and treat it like a literal -- and if the SQL parser
is expecting an identifier where you've placed a literal, it will try and
re-evaluate it as an identifier instead. So since the statement CREATE
TABLE 'MYTABLE' ('ID' INTEGER PRIMARY KEY) contains literals in places
identifiers are expected, SQLite treats them as identifiers instead of
literals.
Conversely, SELECT 'ID' FROM 'MYTABLE' is ambiguous in that 'ID' could
mean
the literal string 'ID' or could mean an identifier. In a SELECT clause
the
string is first evaluated as a literal, and since literals are allowed in
the return columns of a SELECT, the literal code path is taken and there
is
no need to try and evaluate it as an identifier. The FROM 'MYTABLE'
portion
is parsed later, but literals aren't allowed as a target in a FROM clause,
so 'MYTABLE' is treated as an identifier.
In short ... Don't use single-quotes around identifiers. Use
single-quotes
for string literals, and use double-quotes or brackets around identifiers
so
your code is more readable and explicit.
Robert
That's very helpful. Thanks.
Best regards,
He Shiming
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
