Usability problem with quoted names

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Usability problem with quoted names

Alexander J. Kozlovsky
Hi!

If I use "some double-quoted stuff" in my query, this one can have
two absolutely different meanings. Usually it is column name, but if I
wrote this string with error, it silently converts into string
literal.

I think, this silent behaviour is not very good. If it happens
inside deeply nested subquery or inside aggregate function
(e.g. having count(distinct "cusotmer_id") = 1 ) the query still works,
but produces incorrect result.

IMHO, it is better if double quoted string always mean "column name",
and single quoted string stand for 'literal value'



Best regards,
 Alexander                          mailto:[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Usability problem with quoted names

Teg-3
Hello Alexander,

Why not use "?" then fill it in the the actual value later? Quoting
and having to actually look at values to make sure they were legal
gave me no end of problems now I:

     CMutexAutolock  Locker(DbLock);
     CDBAutoClose    Closer(&Db);
     CDBAutoCommit   Commit(&Db);

     hr = Db.Compile("INSERT OR IGNORE INTO GP_List1(GP_Name,GP_Count,GP_Date) VALUES (?,?,?);");

     Db.BindText(1,(*iLoop).first.c_str());
     Db.BindText(2,(*iLoop).second.c_str());
     Db.BindInteger(3,time(NULL));

     hr = Db.Step(nResults);
     if( FAILED(hr))
     {
           Db.FreeError();
           assert(0);
           break;
     }
     Db.ResetBindings();
     Db.Finish();

I yanked out a bunch of code but, I think you can figure out the
SQLite names for the functions from the wrapper names.

I never pass any kind of quoted values any more.

C
     
Tuesday, December 13, 2005, 5:27:13 PM, you wrote:

AK> Hi!

AK> If I use "some double-quoted stuff" in my query, this one can have
AK> two absolutely different meanings. Usually it is column name, but if I
AK> wrote this string with error, it silently converts into string
AK> literal.

AK> I think, this silent behaviour is not very good. If it happens
AK> inside deeply nested subquery or inside aggregate function
AK> (e.g. having count(distinct "cusotmer_id") = 1 ) the query still works,
AK> but produces incorrect result.

AK> IMHO, it is better if double quoted string always mean "column name",
AK> and single quoted string stand for 'literal value'



AK> Best regards,
AK>  Alexander                        
AK> mailto:[hidden email]




--
Best regards,
 Teg                            mailto:[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Usability problem with quoted names

Alexander J. Kozlovsky
Teg wrote:

> Why not use "?" then fill it in the the actual value later? Quoting
> and having to actually look at values to make sure they were legal
> gave me no end of problems now I:
>   ...
> I never pass any kind of quoted values any more.

I'm sorry, my previous post is quite obscure. This is because my English
is not very good. Now I try to explain it in more clear terms:

1. SQLite has a feature of double-quoted column names. It is good
   feature and is part of SQL standard.

2. Double-quoting allows columns with internationalized names.
   I teach SQL-92 to russian beginner students, and use SQLite
   in this course. Many students like gives russian names to columns,
   and such columns must be double-quoted.

3. If student misprint non-English column name in complex query,
   SQLite don't report about error, but treat this misprinted name
   as string literal. This SQLite behaviour can introduce very
   subtle bugs, if error happens in subquery or aggregate function.

4. This is because SQLite allows two absolutely different meaning of
   double-quoted strings. It may be column name OR string literal.
   I think this is misfeature.
   
5. Good system must have brittle behaviour in respect of programmer
   errors. If program (or SQL query) contains error, system must
   report about this error as soon as possible. Instead, SQLite
   current behaviour hide this class of errors, and introduce bug.

6. I propose deprecation of double-quoted string literal in next
   SQLite version, and then (after some deprecation period)
   completely remove this misfeature.


Best regards,
 Alexander                            mailto:[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Usability problem with quoted names

D. Richard Hipp
In reply to this post by Alexander J. Kozlovsky
Alexander Kozlovsky <[hidden email]> wrote:
> Many students like gives russian names to columns,
> and such columns must be double-quoted.
>

Have you actually tried this?  SQLite is suppose to allow
the use of column names containing non-roman characters
without the use of double-quotes.  The rule in SQLite is
that a column name (or table name) can be any sequence of
characters that does not include US-ASCII punctuation or
control characters and does not begin with a digit.

If you are seeing otherwise, then file a bug report.

Note also that if you names do include punctuation, then you
can enclose them in [...] instead of "..." and they will
never be mistaken for literals.

--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Usability problem with quoted names

Alexander J. Kozlovsky
D. Richard Hipp wrote:

> The rule in SQLite is
> that a column name (or table name) can be any sequence of
> characters that does not include US-ASCII punctuation or
> control characters and does not begin with a digit.

It is cool! I have not seen this in SQLite documentation

> Note also that if you names do include punctuation, then you
> can enclose them in [...] instead of "..." and they will
> never be mistaken for literals.

Yes, it solves the problem completely.

Thanks for response!


Best regards,
 Alexander                            mailto:[hidden email]