On 11/06/2012 21:48, Larry Brasfield wrote: > On June 11, rick wrote: >> Something I noticed today: >> >> sqlite> select * FROM words WHERE word = "id"; >> <nothing> >> >> sqlite> select * FROM words WHERE word = 'id'; >> 13556|id >> >> sqlite> .schema >> CREATE TABLE words ( >> id integer primary key, >> word varchar(64) >> ); >> >> Yes, it can be explained: <http://www.sqlite.org/lang_keywords.html> says: >> >> 'keyword' A keyword in single quotes is a string literal. >> "keyword" A keyword in double-quotes is an identifier >> >> So, "id" is interpreted as a column name, not as the string literal 'id', >> unless the value in the double quotes is not an identifier (column name). >> >> I think this is quite nasty behaviour. Hope it prevents someone making the >> same mistake! > > You should take up the nastiness issue with the authors of the SQL-92 > standard and its successors. Supporting the convention you just noticed > is the least surprising behavior for those who have learned SQL as > defined, and hence is not generally regarded as a nasty feature.
Well, I'm not entirely a casual user, but I don't know the SQL-92 standard or it's successors. I'm an engineer, and sqlite for me is a tool. Above query was used a long time in a MySQL DB, where it works as (I) expected. I just hope to warn others against naively using double quotes. Something, by the way, that MySQL does as I expect, although probably not exactly according to '(Second Informal Review Draft) ISO/IEC 9075:1992, Database Language SQL- July 30, 1992'. If it's so easy to make mistakes like this the standard could be considered a poor one. > Good luck. hm. rick _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
