> -----Original Message-----
> From: He Shiming [mailto:[EMAIL PROTECTED]
> Sent: Monday, September 18, 2006 10:02 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Quotation handling bug?
>
> Hi,
>
> I think I found a bug in sqlite version 3.3.7. The steps to
> reproduce it is
> as follows. I've tested it on Windows only.
>
> C:\Something>sqlite3 newdb.db
> CREATE TABLE 'MYTABLE' ('ID' INTEGER PRIMARY KEY);
> INSERT INTO 'MYTABLE' ('ID') VALUES(1);
> INSERT INTO 'MYTABLE' ('ID') VALUES(2);
> INSERT INTO 'MYTABLE' ('ID') VALUES(3);
>
> This is pretty straightfoward. But when I try to fetch the data out...
> SELECT 'ID' FROM 'MYTABLE' WHERE 'ID'=2; // no result
> SELECT 'ID' FROM 'MYTABLE' WHERE ID=2; // result is ID
> SELECT ID FROM 'MYTABLE' WHERE ID=2; // result is 2
> SELECT 'MYTABLE'.'ID' FROM 'MYTABLE' WHERE 'MYTABLE'.'ID'=2;
> // result is 2
>
> I guess, to make it safer, I'll have to use the last one.
> However, the
> behavior or the first one and the second one looks like
> malfunctioning. The
> four queries should produce completely equivalent results,
> which is "2". Or
> is it something I did wrong?
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
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
