Re: [sqlite] Quoting "id" versus 'id' in query

[Black, Michael (IS)]

That's the nice thing about standards...there are so many to choose from...:-(



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quoting "id" versus 'id' in query

[Simon Slavin]

On 12 Jun 2012, at 9:52am, rick  wrote:

> 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.

SQL (note that I'm talking about the same SQL-92 standard Larry referred to) 
does not use the same convention of double-quoting text strings that you are 
used to from other programming languages.  It's something you need to get used 
to when using SQL no matter which SQL engine you use.  MySQL's use of double 
quotes is something special for MySQL, not normal for SQL.

We used to have to deal with different string quoting rules for FORTRAN, COBOL, 
ALGOL, BASIC, PASCAL, and LISP.  Now almost everything uses non-directional 
double-quotes.  Kids these days have it easy.  And get off my lawn.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quoting "id" versus 'id' in query

[rick]

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";
>> 
>>
>> sqlite> select * FROM words WHERE word = 'id';
>> 13556|id
>>
>> sqlite> .schema
>> CREATE TABLE words (
>>  id  integer primary key,
>>  wordvarchar(64)
>> );
>>
>> Yes, it can be explained:  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

Re: [sqlite] Quoting "id" versus 'id' in query

[Pavel Ivanov]

> You have overstated the requirements upon SQL identifiers.  Quoting via
> surrounding double-quote is only necessary if the identifier would not meet
> the definition of a "regular identifier", loosely understood to be a letter
> followed by letter, digit or underscore characters.

Also quoting is required if your identifier is the same as one of
SQLite's keywords (like "select", "table", "from" etc).


Pavel


On Mon, Jun 11, 2012 at 4:04 PM, Larry Brasfield
 wrote:
> On June 11, rick wrote:
>>
>> Yes, it can be explained:  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).
>
>
> You have overstated the requirements upon SQL identifiers.  Quoting via
> surrounding double-quote is only necessary if the identifier would not meet
> the definition of a "regular identifier", loosely understood to be a letter
> followed by letter, digit or underscore characters.  Hence, the text "id"
> without any quotes would be a valid SQL identifier.
>
> From http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt :
>
>   ::=
>                
>              | 
>
>   ::= 
>
>          ::=
>               [ {  |  }... ]
>
>
>          ::= !! See the Syntax Rules
>
>          ::=
>                
>              | 
>
> 1) An  is one of:
>
>            a) A ; or
>
>            b) A character that is identified as a letter in the character
>              repertoire identified by the               tion> or by the ; or
>
>            c) A character that is identified as a syllable in the char-
>              acter repertoire identified by the               specification> or by the ; or
>
>            d) A character that is identified as an ideograph in the char-
>              acter repertoire identified by the               specification> or by the .
>
> --
> Larry Brasfield
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quoting "id" versus 'id' in query

[Larry Brasfield]

On June 11, rick wrote:

Yes, it can be explained:  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).


You have overstated the requirements upon SQL identifiers.  Quoting via
surrounding double-quote is only necessary if the identifier would not meet
the definition of a "regular identifier", loosely understood to be a letter
followed by letter, digit or underscore characters.  Hence, the text "id"
without any quotes would be a valid SQL identifier.

From http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt :

  ::=

  | 

  ::= 

  ::=
   [ {  |  
}... ]



  ::= !! See the Syntax Rules

  ::=

  | 

1) An  is one of:

a) A ; or

b) A character that is identified as a letter in the character
  repertoire identified by the  or by the ; or

c) A character that is identified as a syllable in the char-
  acter repertoire identified by the  or by the ; or

d) A character that is identified as an ideograph in the char-
  acter repertoire identified by the  or by the .

--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quoting "id" versus 'id' in query

[Larry Brasfield]

On June 11, rick wrote:

Something I noticed today:

sqlite> select * FROM words WHERE word = "id";


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:  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.


Good luck.
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Quoting "id" versus 'id' in query

[rick]

Something I noticed today:

sqlite> select * FROM words WHERE word = "id";


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:  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!

rick

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users