Re: [sqlite] Proposed removal of (mis-)feature
Eugene Wee wrote: >> >> If it does not have any compatibility with any other database, >> ONLY then I say that one should drop #3 quoting convention. >> > I believe that MySQL allows double quotes to be used as string > delimiters by default, but then this would not be a compatibility break > with MySQL since MySQL also supports the standard use of single quotes > for this purpose by default, and does not have the same "identifier, > else a string" behaviour anyway. > It was added for MySQL compatibility. They support both single and double quotes as literal string delimiters. MySQL used nonstandard backquotes to delimit identifiers. MySQL has since added a ANSI_QUOTES mode which changes this behavior. From their documentation: "If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted only within single quotes because a string quoted within double quotes is interpreted as an identifier." I also vote to have this (mis)-feature dropped (in fact I'm pretty sure I have suggested this several times in the past). If it can't be dropped outright, then at least add a pragma to disable the extensions and accept only standard quoting. The current behavior encourages users to use non-standard quoting to get useful error messages (see http://article.gmane.org/gmane.comp.db.sqlite.general/35531 for an example). Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed removal of (mis-)feature
> 3. Double-quoted names fall back to being string literals if > there is no matching table or column name. > > In retrospect, (3) seems to be a bad idea. It is accident-prone and > leads to all kinds of confusion. For example, if double-quotes are > being used correctly (which is to say to quote table or column names) > but a misspelling occurs in the name, the token reverts to being a > string literal rather than throwing an error. Or if a double-quoted > string really is being used as a string literal, but later a new > column is added to a table that has the same name as the string text, > the string literal will suddenly take on the value of the column... > So I'm giving some thought to removing feature (3) above and > disallowing double-quoted string literals. Yes, absolutely, get rid of it, for exactly the reasons you mention above. It's currently a ridiculous situation where adding a column to a table can change the output of existing queries, and that a misspelled column name doesn't return an error. Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed removal of (mis-)feature
Hi, palmer ristevski wrote: > He is my vote. > I like the fact that conventions #1 and #2 make things compatible with other > databases. > Does #3, by chance, do the same with another database not mentioned > (eg. Oracle). > > If it does not have any compatibility with any other database, > ONLY then I say that one should drop #3 quoting convention. > > Palmer > I believe that MySQL allows double quotes to be used as string delimiters by default, but then this would not be a compatibility break with MySQL since MySQL also supports the standard use of single quotes for this purpose by default, and does not have the same "identifier, else a string" behaviour anyway. Oh, and +1 to removing #3, if it matters now after so many votes in that direction :) Regards, Eugene ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed removal of (mis-)feature
Yes, please remove #3. Now is a particularly good time for it seeing as the 3.6 line is quite new. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed removal of (mis-)feature
D. Richard Hipp wrote: > 3. Double-quoted names fall back to being string literals if > there is no matching table or column name. > > So I'm giving some thought to removing feature (3) above and > disallowing double-quoted string literals. My concern is, though, > that this might break many existing applications. > > What opinion do you, gentle users, have of this matter? I think you should go ahead and make the change you propose, and soon as possible. But release that change as version 3.7.0 so it is more clear to people that this is a significant change as far as the user experience goes, that they should realize this might break something if they were relying on the old broken behaviour, and that they need to fix their own code. I don't agree with having a pragma to turn on the old behavior, but I do like the idea of issuing warnings when it appears that a user may be relying on the old behavior; these warnings can help them track down what areas of their code need to be fixed. Or alternately make a 3.6.x series release first that adds warnings but keeps the old behaviour, then make a 3.7.0 release that keeps the warnings (modified if needed) but drops the bad behaviour. Note that any warnings like this should be possible to turn off when the user knows that their code is compliant and their string literals shouldn't be checked anymore for similarity to column names etc. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed removal of (mis-)feature
He is my vote. I like the fact that conventions #1 and #2 make things compatible with other databases. Does #3, by chance, do the same with another database not mentioned (eg. Oracle). If it does not have any compatibility with any other database, ONLY then I say that one should drop #3 quoting convention. Palmer > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Date: Thu, 7 Aug 2008 13:26:07 -0400 > Subject: [sqlite] Proposed removal of (mis-)feature > > String literals in SQL are suppose to be enclosed in single-quotes - > Pascal-style. Double-quotes are used around table and/or column names > in cases where the name would otherwise be a keyword or when the name > contains non-standard characters. > > But SQLite tries to be flexible and accommodating. To this end, it > accepts some non-standard quoting mechanisms: > > 1. Names can be enclosed in [...] for compatibility with Access > and SQLServer. > 2. Names can be enclosed in grave accents for compatibility with > MySQL. > 3. Double-quoted names fall back to being string literals if > there is no matching table or column name. > > In retrospect, (3) seems to be a bad idea. It is accident-prone and > leads to all kinds of confusion. For example, if double-quotes are > being used correctly (which is to say to quote table or column names) > but a misspelling occurs in the name, the token reverts to being a > string literal rather than throwing an error. Or if a double-quoted > string really is being used as a string literal, but later a new > column is added to a table that has the same name as the string text, > the string literal will suddenly take on the value of the column. It > seems like we have one or two problem reports per month on this > mailing list that involve people using double-quoted names where they > should be using single-quoted string literals instead. > > So I'm giving some thought to removing feature (3) above and > disallowing double-quoted string literals. My concern is, though, > that this might break many existing applications. > > What opinion do you, gentle users, have of this matter? > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Get more from your digital life. Find out how. http://www.windowslive.com/default.html?ocid=TXT_TAGLM_WL_Home2_082008 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed removal of (mis-)feature
+1 for removal of quote munging. RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 sqlite>select level from sqlGuruOMeter where name="Ron Wilson"; 2 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Thursday, August 07, 2008 1:26 PM To: General Discussion of SQLite Database Subject: [sqlite] Proposed removal of (mis-)feature String literals in SQL are suppose to be enclosed in single-quotes - Pascal-style. Double-quotes are used around table and/or column names in cases where the name would otherwise be a keyword or when the name contains non-standard characters. But SQLite tries to be flexible and accommodating. To this end, it accepts some non-standard quoting mechanisms: 1. Names can be enclosed in [...] for compatibility with Access and SQLServer. 2. Names can be enclosed in grave accents for compatibility with MySQL. 3. Double-quoted names fall back to being string literals if there is no matching table or column name. In retrospect, (3) seems to be a bad idea. It is accident-prone and leads to all kinds of confusion. For example, if double-quotes are being used correctly (which is to say to quote table or column names) but a misspelling occurs in the name, the token reverts to being a string literal rather than throwing an error. Or if a double-quoted string really is being used as a string literal, but later a new column is added to a table that has the same name as the string text, the string literal will suddenly take on the value of the column. It seems like we have one or two problem reports per month on this mailing list that involve people using double-quoted names where they should be using single-quoted string literals instead. So I'm giving some thought to removing feature (3) above and disallowing double-quoted string literals. My concern is, though, that this might break many existing applications. What opinion do you, gentle users, have of this matter? D. Richard Hipp [EMAIL PROTECTED] ___ 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] Proposed removal of (mis-)feature
+1 in favor of removing non-standard quoting mechanism #3. - Original Message From: D. Richard Hipp <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Thursday, August 7, 2008 10:26:07 AM Subject: [sqlite] Proposed removal of (mis-)feature String literals in SQL are suppose to be enclosed in single-quotes - Pascal-style. Double-quotes are used around table and/or column names in cases where the name would otherwise be a keyword or when the name contains non-standard characters. But SQLite tries to be flexible and accommodating. To this end, it accepts some non-standard quoting mechanisms: 1. Names can be enclosed in [...] for compatibility with Access and SQLServer. 2. Names can be enclosed in grave accents for compatibility with MySQL. 3. Double-quoted names fall back to being string literals if there is no matching table or column name. In retrospect, (3) seems to be a bad idea. It is accident-prone and leads to all kinds of confusion. For example, if double-quotes are being used correctly (which is to say to quote table or column names) but a misspelling occurs in the name, the token reverts to being a string literal rather than throwing an error. Or if a double-quoted string really is being used as a string literal, but later a new column is added to a table that has the same name as the string text, the string literal will suddenly take on the value of the column. It seems like we have one or two problem reports per month on this mailing list that involve people using double-quoted names where they should be using single-quoted string literals instead. So I'm giving some thought to removing feature (3) above and disallowing double-quoted string literals. My concern is, though, that this might break many existing applications. What opinion do you, gentle users, have of this matter? D. Richard Hipp [EMAIL PROTECTED] ___ 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] Proposed removal of (mis-)feature
Henceforth and forward: Let the standards violator suffer his just rewards!" :-) Dr., your just trying to supply the worlds greatest database, not take a bunch of slovenly coders to raise... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of D. Richard Hipp Sent: Thursday, August 07, 2008 12:26 PM To: General Discussion of SQLite Database Subject: [sqlite] Proposed removal of (mis-)feature String literals in SQL are suppose to be enclosed in single-quotes - Pascal-style. Double-quotes are used around table and/or column names in cases where the name would otherwise be a keyword or when the name contains non-standard characters. But SQLite tries to be flexible and accommodating. To this end, it accepts some non-standard quoting mechanisms: 1. Names can be enclosed in [...] for compatibility with Access and SQLServer. 2. Names can be enclosed in grave accents for compatibility with MySQL. 3. Double-quoted names fall back to being string literals if there is no matching table or column name. In retrospect, (3) seems to be a bad idea. It is accident-prone and leads to all kinds of confusion. For example, if double-quotes are being used correctly (which is to say to quote table or column names) but a misspelling occurs in the name, the token reverts to being a string literal rather than throwing an error. Or if a double-quoted string really is being used as a string literal, but later a new column is added to a table that has the same name as the string text, the string literal will suddenly take on the value of the column. It seems like we have one or two problem reports per month on this mailing list that involve people using double-quoted names where they should be using single-quoted string literals instead. So I'm giving some thought to removing feature (3) above and disallowing double-quoted string literals. My concern is, though, that this might break many existing applications. What opinion do you, gentle users, have of this matter? D. Richard Hipp [EMAIL PROTECTED] ___ 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] Proposed removal of (mis-)feature
On Thu, Aug 7, 2008 at 2:13 PM, Kees Nuyt <[EMAIL PROTECTED]> wrote: > > On Thu, 7 Aug 2008 13:26:07 -0400, drh wrote: > > [snip] > >>So I'm giving some thought to removing feature (3) above and >>disallowing double-quoted string literals. My concern is, though, >>that this might break many existing applications. >> >>What opinion do you, gentle users, have of this matter? > > I agree (3) should be removed. If it would break any of my > applications (which I doubt), I'll gladly repair it, because > I would have done a bad job, not you. > -- > ( Kees Nuyt > ) > c[_] > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > I cant count how many times I've run into this because I mis-typed a double quoted table name and had it interpreted as a string. I vote for removal because it 1) creates confusion about the right way to do stuff and 2) Creates error messages which are utterly meaningless for debugging. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed removal of (mis-)feature
+3 on removal of #3 Noah --- On Thu, 8/7/08, D. Richard Hipp <[EMAIL PROTECTED]> wrote: From: D. Richard Hipp <[EMAIL PROTECTED]> Subject: [sqlite] Proposed removal of (mis-)feature To: "General Discussion of SQLite Database" Date: Thursday, August 7, 2008, 12:26 PM String literals in SQL are suppose to be enclosed in single-quotes - Pascal-style. Double-quotes are used around table and/or column names in cases where the name would otherwise be a keyword or when the name contains non-standard characters. But SQLite tries to be flexible and accommodating. To this end, it accepts some non-standard quoting mechanisms: 1. Names can be enclosed in [...] for compatibility with Access and SQLServer. 2. Names can be enclosed in grave accents for compatibility with MySQL. 3. Double-quoted names fall back to being string literals if there is no matching table or column name. In retrospect, (3) seems to be a bad idea. It is accident-prone and leads to all kinds of confusion. For example, if double-quotes are being used correctly (which is to say to quote table or column names) but a misspelling occurs in the name, the token reverts to being a string literal rather than throwing an error. Or if a double-quoted string really is being used as a string literal, but later a new column is added to a table that has the same name as the string text, the string literal will suddenly take on the value of the column. It seems like we have one or two problem reports per month on this mailing list that involve people using double-quoted names where they should be using single-quoted string literals instead. So I'm giving some thought to removing feature (3) above and disallowing double-quoted string literals. My concern is, though, that this might break many existing applications. What opinion do you, gentle users, have of this matter? D. Richard Hipp [EMAIL PROTECTED] ___ 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 CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed removal of (mis-)feature
On Thu, 7 Aug 2008 13:26:07 -0400, drh wrote: [snip] >So I'm giving some thought to removing feature (3) above and >disallowing double-quoted string literals. My concern is, though, >that this might break many existing applications. > >What opinion do you, gentle users, have of this matter? I agree (3) should be removed. If it would break any of my applications (which I doubt), I'll gladly repair it, because I would have done a bad job, not you. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed removal of (mis-)feature
Remove it. Better now than never. Mike > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Im Auftrag von D. > Richard Hipp > Gesendet: Donnerstag, 7. August 2008 19:26 > An: General Discussion of SQLite Database > Betreff: [sqlite] Proposed removal of (mis-)feature > > String literals in SQL are suppose to be enclosed in > single-quotes - Pascal-style. Double-quotes are used around > table and/or column names in cases where the name would > otherwise be a keyword or when the name contains non-standard > characters. > > But SQLite tries to be flexible and accommodating. To this > end, it accepts some non-standard quoting mechanisms: > > 1. Names can be enclosed in [...] for compatibility with > Access and SQLServer. > 2. Names can be enclosed in grave accents for > compatibility with MySQL. > 3. Double-quoted names fall back to being string > literals if there is no matching table or column name. > > In retrospect, (3) seems to be a bad idea. It is > accident-prone and leads to all kinds of confusion. For > example, if double-quotes are being used correctly (which is > to say to quote table or column names) but a misspelling > occurs in the name, the token reverts to being a string > literal rather than throwing an error. Or if a double-quoted > string really is being used as a string literal, but later a > new column is added to a table that has the same name as the > string text, the string literal will suddenly take on the > value of the column. It seems like we have one or two > problem reports per month on this mailing list that involve > people using double-quoted names where they should be using > single-quoted string literals instead. > > So I'm giving some thought to removing feature (3) above and > disallowing double-quoted string literals. My concern is, > though, that this might break many existing applications. > > What opinion do you, gentle users, have of this matter? > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > 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] Proposed removal of (mis-)feature
On Thu, Aug 07, 2008 at 01:26:07PM -0400, D. Richard Hipp wrote: > 3. Double-quoted names fall back to being string literals if > there is no matching table or column name. > [..] > So I'm giving some thought to removing feature (3) above and > disallowing double-quoted string literals. My concern is, though, > that this might break many existing applications. > > What opinion do you, gentle users, have of this matter? A major part of the traffic on the #sqlite IRC channel is about explaining to people why using "" is a really really bad idea. Any application doing "" now is prone to breakage as described in your post. Consider this a vote for removal :) Cheers, Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed removal of (mis-)feature
I am quite mixed, because I've had people who are working on substantial apps have things happen which came down to this problem. So, on the one hand, I wish it had never been this way, but, on the other hand, if it changes I'm going to hear about it. My best hope would be a PRAGMA or some other solution which allows this to be tweaked, but I can see why that won't get a lot of buy-in from your end. Why I like this solution is that it might allow an app like Gears to deprecate the string-based double-quotes, while providing some transition period where you can still use them, but see warnings somewhere (where? Well, I haven't thought that far ahead). Failing that, it would be nice to have a compile-time option, or even just some easy way we can touch the code to revert the change. I will admit that apps like Gears have special needs relative to many of SQLite's other users. -scott On Thu, Aug 7, 2008 at 10:26 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > String literals in SQL are suppose to be enclosed in single-quotes - > Pascal-style. Double-quotes are used around table and/or column names > in cases where the name would otherwise be a keyword or when the name > contains non-standard characters. > > But SQLite tries to be flexible and accommodating. To this end, it > accepts some non-standard quoting mechanisms: > >1. Names can be enclosed in [...] for compatibility with Access > and SQLServer. >2. Names can be enclosed in grave accents for compatibility with > MySQL. >3. Double-quoted names fall back to being string literals if > there is no matching table or column name. > > In retrospect, (3) seems to be a bad idea. It is accident-prone and > leads to all kinds of confusion. For example, if double-quotes are > being used correctly (which is to say to quote table or column names) > but a misspelling occurs in the name, the token reverts to being a > string literal rather than throwing an error. Or if a double-quoted > string really is being used as a string literal, but later a new > column is added to a table that has the same name as the string text, > the string literal will suddenly take on the value of the column. It > seems like we have one or two problem reports per month on this > mailing list that involve people using double-quoted names where they > should be using single-quoted string literals instead. > > So I'm giving some thought to removing feature (3) above and > disallowing double-quoted string literals. My concern is, though, > that this might break many existing applications. > > What opinion do you, gentle users, have of this matter? > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > 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] Proposed removal of (mis-)feature
On Thu, Aug 7, 2008 at 1:26 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> String literals in SQL are suppose to be enclosed in single-quotes -
> Pascal-style. Double-quotes are used around table and/or column names
> in cases where the name would otherwise be a keyword or when the name
> contains non-standard characters.
>3. Double-quoted names fall back to being string literals if
> there is no matching table or column name.
>
> In retrospect, (3) seems to be a bad idea. It is accident-prone and
The first time I saw an issue related to this on the mailing list, I thought
that it should be fixed somehow.
The main reason it's been kept was for backward compatibility reasons. I
believe that one of the more recent proposed solutions was to make it a
PRAGMA or something, which was struck down because it would only make the
already-complex parser code *more* complicated.
Is there some way to have SQLite's parser take note of when condition #3
results in a literal, and output a warning or something? Something like
fprintf(stderr, "Warning: The query ''%s' has an invalid table/column name
'%s' delimited by double quotes (\"). I am assuming it should have been a
string literal delimited by single quotes ('). This behavior will be removed
in the future; please update your SQL statements.");
Or perhaps, to be shorter,
fprintf(stderr, "Warning: The query ''%s' has an invalid table/column name
'%s' delimited by double quotes (\"). See
http://www.sqlite.org/blah/blah/blah.";);
Warnings could be allowed for "properly" by e.g. having a callback
registered, or adding a new void(*xWarning)(char*str) entry in the VFS.
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed removal of (mis-)feature
+2 on removal of #3. --- On Thu, 8/7/08, D. Richard Hipp <[EMAIL PROTECTED]> wrote: From: D. Richard Hipp <[EMAIL PROTECTED]> Subject: [sqlite] Proposed removal of (mis-)feature To: "General Discussion of SQLite Database" Date: Thursday, August 7, 2008, 12:26 PM String literals in SQL are suppose to be enclosed in single-quotes - Pascal-style. Double-quotes are used around table and/or column names in cases where the name would otherwise be a keyword or when the name contains non-standard characters. But SQLite tries to be flexible and accommodating. To this end, it accepts some non-standard quoting mechanisms: 1. Names can be enclosed in [...] for compatibility with Access and SQLServer. 2. Names can be enclosed in grave accents for compatibility with MySQL. 3. Double-quoted names fall back to being string literals if there is no matching table or column name. In retrospect, (3) seems to be a bad idea. It is accident-prone and leads to all kinds of confusion. For example, if double-quotes are being used correctly (which is to say to quote table or column names) but a misspelling occurs in the name, the token reverts to being a string literal rather than throwing an error. Or if a double-quoted string really is being used as a string literal, but later a new column is added to a table that has the same name as the string text, the string literal will suddenly take on the value of the column. It seems like we have one or two problem reports per month on this mailing list that involve people using double-quoted names where they should be using single-quoted string literals instead. So I'm giving some thought to removing feature (3) above and disallowing double-quoted string literals. My concern is, though, that this might break many existing applications. What opinion do you, gentle users, have of this matter? D. Richard Hipp [EMAIL PROTECTED] ___ 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] Proposed removal of (mis-)feature
I'm +1 on this change. While I have not run into it as a feature or mis-feature, I would concur that it is ambiguous and problematic. A simple clear syntax benefits everyone. BTW, Thanks for all you effort to produce this wonder software. Best regards, -Steve W D. Richard Hipp wrote: > String literals in SQL are suppose to be enclosed in single-quotes - > Pascal-style. Double-quotes are used around table and/or column names > in cases where the name would otherwise be a keyword or when the name > contains non-standard characters. > > But SQLite tries to be flexible and accommodating. To this end, it > accepts some non-standard quoting mechanisms: > > 1. Names can be enclosed in [...] for compatibility with Access > and SQLServer. > 2. Names can be enclosed in grave accents for compatibility with > MySQL. > 3. Double-quoted names fall back to being string literals if > there is no matching table or column name. > > In retrospect, (3) seems to be a bad idea. It is accident-prone and > leads to all kinds of confusion. For example, if double-quotes are > being used correctly (which is to say to quote table or column names) > but a misspelling occurs in the name, the token reverts to being a > string literal rather than throwing an error. Or if a double-quoted > string really is being used as a string literal, but later a new > column is added to a table that has the same name as the string text, > the string literal will suddenly take on the value of the column. It > seems like we have one or two problem reports per month on this > mailing list that involve people using double-quoted names where they > should be using single-quoted string literals instead. > > So I'm giving some thought to removing feature (3) above and > disallowing double-quoted string literals. My concern is, though, > that this might break many existing applications. > > What opinion do you, gentle users, have of this matter? > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > 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
[sqlite] Proposed removal of (mis-)feature
String literals in SQL are suppose to be enclosed in single-quotes - Pascal-style. Double-quotes are used around table and/or column names in cases where the name would otherwise be a keyword or when the name contains non-standard characters. But SQLite tries to be flexible and accommodating. To this end, it accepts some non-standard quoting mechanisms: 1. Names can be enclosed in [...] for compatibility with Access and SQLServer. 2. Names can be enclosed in grave accents for compatibility with MySQL. 3. Double-quoted names fall back to being string literals if there is no matching table or column name. In retrospect, (3) seems to be a bad idea. It is accident-prone and leads to all kinds of confusion. For example, if double-quotes are being used correctly (which is to say to quote table or column names) but a misspelling occurs in the name, the token reverts to being a string literal rather than throwing an error. Or if a double-quoted string really is being used as a string literal, but later a new column is added to a table that has the same name as the string text, the string literal will suddenly take on the value of the column. It seems like we have one or two problem reports per month on this mailing list that involve people using double-quoted names where they should be using single-quoted string literals instead. So I'm giving some thought to removing feature (3) above and disallowing double-quoted string literals. My concern is, though, that this might break many existing applications. What opinion do you, gentle users, have of this matter? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
