RFC Beginning digit in column name

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

RFC Beginning digit in column name

ender-3
<copyed from a co-developer>
Dear developers,

a while back, our project decided to switch from mysql to usage of
various SQL backends, among this sqlite3 as our favorite. Now recently
we encountered a problem, just a tiny one, but one we are not able to
solve with all used SQl backends in a common sense.

The problem arised within a table that had a column called '0_uid'. As
you might guess, the problem comes from the beginning digit in the
column name. I looked arround quite a while and learned, that it was
pure luck that we haven't had problems with that so far - as we relied
upon an SQL extension that mySQL offered. After some reading and trying
I found out, that sqlite3 also was able to handle such columns, but the
column name has to be quoted. Ok, that sounds fair - BUT I had to learn
one sad thing... mySQL and sqlite3 don't agree upon a common subset of
symbols to quote. Whilest the one accepts ' and ", the other relies upon `.

So my simple feature request would be: allow '`' as a quoting symbol -
as mySQL does. Or - what would be as helpful as the other idea - allow
unquoted column names with leading digits - as mySQL does.

<snip>


Also see ticket # 1337  http://www.sqlite.org/cvstrac/tktview?tn=1337

Reply | Threaded
Open this post in threaded view
|

Re: RFC Beginning digit in column name

Austin Ziegler
On 8/9/05, ender <[hidden email]> wrote:

> <copyed from a co-developer>
> Dear developers,
>
> a while back, our project decided to switch from mysql to usage of
> various SQL backends, among this sqlite3 as our favorite. Now recently
> we encountered a problem, just a tiny one, but one we are not able to
> solve with all used SQl backends in a common sense.
>
> The problem arised within a table that had a column called '0_uid'. As
> you might guess, the problem comes from the beginning digit in the
> column name. I looked arround quite a while and learned, that it was
> pure luck that we haven't had problems with that so far - as we relied
> upon an SQL extension that mySQL offered. After some reading and trying
> I found out, that sqlite3 also was able to handle such columns, but the
> column name has to be quoted. Ok, that sounds fair - BUT I had to learn
> one sad thing... mySQL and sqlite3 don't agree upon a common subset of
> symbols to quote. Whilest the one accepts ' and ", the other relies upon `.
>
> So my simple feature request would be: allow '`' as a quoting symbol -
> as mySQL does. Or - what would be as helpful as the other idea - allow
> unquoted column names with leading digits - as mySQL does.

> Also see ticket # 1337  http://www.sqlite.org/cvstrac/tktview?tn=1337

I think that supporting '`' (backquote) would be a bad idea. Then
again, MySQL ignores so much of the SQL standard that I think that
importing any idea from MySQL is a bad idea.

-austin
--
Austin Ziegler * [hidden email]
               * Alternate: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: RFC Beginning digit in column name

Nuno Lucas
In reply to this post by ender-3
[09-08-2005 19:05, ender escreveu]
[...]
> So my simple feature request would be: allow '`' as a quoting symbol -
> as mySQL does. Or - what would be as helpful as the other idea - allow
> unquoted column names with leading digits - as mySQL does.

Does MySQL allows [0_xy] to specify column names?

If I remember correctly, that is the standard SQL way to have columns
with spaces in the middle, and sqlite allows columns starting with a
digit in this way...


Regards,
~Nuno Lucas
Reply | Threaded
Open this post in threaded view
|

Re: RFC Beginning digit in column name

Darren Duncan
At 8:46 PM +0100 8/9/05, Nuno Lucas wrote:

>[09-08-2005 19:05, ender escreveu]
>[...]
>>  So my simple feature request would be: allow '`' as a quoting symbol -
>>  as mySQL does. Or - what would be as helpful as the other idea - allow
>>  unquoted column names with leading digits - as mySQL does.
>
>Does MySQL allows [0_xy] to specify column names?
>
>If I remember correctly, that is the standard SQL way to have columns
>with spaces in the middle, and sqlite allows columns starting with a
>digit in this way...
>
>Regards,
>~Nuno Lucas

The SQL standard has both delimited and bareword identifiers.

Bareword identifiers can only be letters, numbers, and underscore,
and start with a letter, to my knowledge; they are also
case-insensitive.

Delimited identifiers can have any characters in them, including
spaces, and they are delimited with double-quotes (") usually.

The standard may allow other delimiting characters, but I'm not sure.
I imagine that back-ticks (`) may be safe to support if they aren't
already used by SQL in some other way.  You certainly don't want to
use single-quotes (') as those are always literal string delimiters.
You also don't want to use brackets ([]) as those are used by the
standard for array indices.

I suggest for simplicity that SQLite simply support single-quotes for
string delimiters and double-quotes for identifiers; clearly distinct
and simple.

-- Darren Duncan
Reply | Threaded
Open this post in threaded view
|

Re: RFC Beginning digit in column name

Austin Ziegler
In reply to this post by Nuno Lucas
On 8/9/05, Nuno Lucas <[hidden email]> wrote:
> [09-08-2005 19:05, ender escreveu]
> [...]
>> So my simple feature request would be: allow '`' as a quoting
>> symbol - as mySQL does. Or - what would be as helpful as the
>> other idea - allow unquoted column names with leading digits - as
>> mySQL does.
> Does MySQL allows [0_xy] to specify column names?

I don't think so.

> If I remember correctly, that is the standard SQL way to have
> columns with spaces in the middle, and sqlite allows columns
> starting with a digit in this way...

I'm not sure that it's a standard. I know Microsoft SQL Server and
Access DB use it, but from what I recall of the SQL92 standard, only
double quotes ("0_xy") are permitted for case-sensitive or otherwise
abnormal table and column names. Tables specified without this are
to be treated as uppercase only.

-austin
--
Austin Ziegler * [hidden email]
               * Alternate: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: RFC Beginning digit in column name

Kurt Welgehausen
In reply to this post by Nuno Lucas
> Does MySQL allows [0_xy] to specify column names?
>
> If I remember correctly, that is the standard SQL way to have columns
> with spaces in the middle, and sqlite allows columns starting with a
> digit in this way...

It's not standard; it's a Microsoft extension.

Regards
Reply | Threaded
Open this post in threaded view
|

Re: RFC Beginning digit in column name

ender-3
In reply to this post by Austin Ziegler
Austin Ziegler wrote:

>On 8/9/05, ender <[hidden email]> wrote:
>
>So my simple feature request would be: allow '`' as a quoting symbol -
>as mySQL does. Or - what would be as helpful as the other idea - allow
>unquoted column names with leading digits - as mySQL does.
>  
>
>
>  
>
>>Also see ticket # 1337  http://www.sqlite.org/cvstrac/tktview?tn=1337
>>    
>>
>
>I think that supporting '`' (backquote) would be a bad idea. Then
>again, MySQL ignores so much of the SQL standard that I think that
>importing any idea from MySQL is a bad idea.
>
>-austin
>  
>

What are your reasons for thinking supporting '`' is a bad idea?

Reply | Threaded
Open this post in threaded view
|

Re: RFC Beginning digit in column name

Austin Ziegler
On 8/10/05, ender <[hidden email]> wrote:

> Austin Ziegler wrote:
>>On 8/9/05, ender <[hidden email]> wrote:
>>> So my simple feature request would be: allow '`' as a quoting symbol -
>>> as mySQL does. Or - what would be as helpful as the other idea - allow
>>> unquoted column names with leading digits - as mySQL does.
>>> Also see ticket # 1337  http://www.sqlite.org/cvstrac/tktview?tn=1337
>> I think that supporting '`' (backquote) would be a bad idea. Then
>> again, MySQL ignores so much of the SQL standard that I think that
>> importing any idea from MySQL is a bad idea.
> What are your reasons for thinking supporting '`' is a bad idea?

1. It's not part of the standard.
2. It's not part of the standard.
3. MySQL does it. Therefore, no one else should do it, because:
4. It's not part of the standard (and most of what MySQL does is not
part of the standard).

If you've learned "SQL" on MySQL, you haven't learned SQL. You've
learned MySQL. The same applies to people who want to do [tablename]
-- it's a Microsoftism that (IMO) should be *de-supported* in SQLite
because it's ugly and wrong.

The only quotes that SQL92 supports are ' and "; ' indicates data, "
indicates literal interpretation of table and column names. Not `, not
[].

If MySQL does it and it's not part of the standard, I'm opposed to it.

-austin
--
Austin Ziegler * [hidden email]
               * Alternate: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: RFC Beginning digit in column name

Jay Sprenkle
On 8/10/05, Austin Ziegler <[hidden email]> wrote:
> > What are your reasons for thinking supporting '`' is a bad idea?
>
> 1. It's not part of the standard.

LOL!

Standards are great! Everybody should have several. ;)
Reply | Threaded
Open this post in threaded view
|

RE: RFC Beginning digit in column name

Marcus Welz
In reply to this post by Austin Ziegler
Hello there,

I think that adhering to standards is a Very Good Thing(tm). They are
standards for a reason. Deviations can introduce ambiguity, confusion,
complexity, vendor lock-ins and all sorts of other headaches.

That said, however, I believe that "no, because it's not the standard" isn't
exactly what a customer wants to hear. Then again "no" isn't what a customer
wants to ever hear in almost any context, but I digress..

Customers / users may wonder "Well, they (e.g. MySQL) can do it, why can't
you?" And I think in a fair number of situations, if possible to implement
at a reasonable amount of cost the answer should be "We can, but be warned,
it is NOT the standard."

Some proprietary extensions are rather handy, too, and may want be be
considered. I don't really think "`" and "[]" are useful nor pretty, but I
also don't think that it would be unethical to support it. On the other
hand, MySQL's "SQL_CALC_FOUND_ROWS" keyword and the "FOUND_ROWS()" function
for instance have made my life easy in the past. (SQL_CALC_FOUND_ROWS in a
query counts how many rows there would have been returned with the LIMIT
clause omitted, great for pagination if you want to display something like
"Rows 1 - 15, 400 total" all while running a complex query (with a whole
slew of JOINS and WHERE conditions) only once.

The short of it all: I don't know much about SQLite's internal architecture,
besides glossing over the web site documentation, but my suggestion is to
add a new PRAGMA statement ("PRAGMA tolerate_broken_sql = 1" :-), which
should be _disabled_ by default, but when enabled would honor proprietary
extensions such as "[]" and "`" for quotation, digits in column names, and
who knows, maybe even some of the other extensions and vendor specific
functions, etc.

It would allow numerous projects to more easily migrate away from MySQL to
SQLite without needing to clean up all their broken SQL instantly, and it
would allow projects that want to stick with MySQL to also offer a SQLite
version with little effort.

How off the wall am I with this?

-m

-----Original Message-----
From: Austin Ziegler [mailto:[hidden email]]
Sent: Wednesday, August 10, 2005 2:44 PM
To: [hidden email]
Subject: Re: [sqlite] RFC Beginning digit in column name

On 8/10/05, ender <[hidden email]> wrote:

> Austin Ziegler wrote:
>>On 8/9/05, ender <[hidden email]> wrote:
>>> So my simple feature request would be: allow '`' as a quoting symbol -
>>> as mySQL does. Or - what would be as helpful as the other idea - allow
>>> unquoted column names with leading digits - as mySQL does.
>>> Also see ticket # 1337  http://www.sqlite.org/cvstrac/tktview?tn=1337
>> I think that supporting '`' (backquote) would be a bad idea. Then
>> again, MySQL ignores so much of the SQL standard that I think that
>> importing any idea from MySQL is a bad idea.
> What are your reasons for thinking supporting '`' is a bad idea?

1. It's not part of the standard.
2. It's not part of the standard.
3. MySQL does it. Therefore, no one else should do it, because:
4. It's not part of the standard (and most of what MySQL does is not
part of the standard).

If you've learned "SQL" on MySQL, you haven't learned SQL. You've
learned MySQL. The same applies to people who want to do [tablename]
-- it's a Microsoftism that (IMO) should be *de-supported* in SQLite
because it's ugly and wrong.

The only quotes that SQL92 supports are ' and "; ' indicates data, "
indicates literal interpretation of table and column names. Not `, not
[].

If MySQL does it and it's not part of the standard, I'm opposed to it.

-austin
--
Austin Ziegler * [hidden email]
               * Alternate: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: RFC Beginning digit in column name

John LeSueur
In reply to this post by ender-3
Marcus Welz wrote:

>Hello there,
>
>I think that adhering to standards is a Very Good Thing(tm). They are
>standards for a reason. Deviations can introduce ambiguity, confusion,
>complexity, vendor lock-ins and all sorts of other headaches.
>
>That said, however, I believe that "no, because it's not the standard" isn't
>exactly what a customer wants to hear. Then again "no" isn't what a customer
>wants to ever hear in almost any context, but I digress..
>
>Customers / users may wonder "Well, they (e.g. MySQL) can do it, why can't
>you?" And I think in a fair number of situations, if possible to implement
>at a reasonable amount of cost the answer should be "We can, but be warned,
>it is NOT the standard."
>
>Some proprietary extensions are rather handy, too, and may want be be
>considered. I don't really think "`" and "[]" are useful nor pretty, but I
>also don't think that it would be unethical to support it. On the other
>hand, MySQL's "SQL_CALC_FOUND_ROWS" keyword and the "FOUND_ROWS()" function
>for instance have made my life easy in the past. (SQL_CALC_FOUND_ROWS in a
>query counts how many rows there would have been returned with the LIMIT
>clause omitted, great for pagination if you want to display something like
>"Rows 1 - 15, 400 total" all while running a complex query (with a whole
>slew of JOINS and WHERE conditions) only once.
>
>The short of it all: I don't know much about SQLite's internal architecture,
>besides glossing over the web site documentation, but my suggestion is to
>add a new PRAGMA statement ("PRAGMA tolerate_broken_sql = 1" :-), which
>should be _disabled_ by default, but when enabled would honor proprietary
>extensions such as "[]" and "`" for quotation, digits in column names, and
>who knows, maybe even some of the other extensions and vendor specific
>functions, etc.
>
>  
>
I think that isn't a bad idea necessarily, but in general, more pragmas
means more configuration, means
more mysterious settings that need to be
explained/documented/maintained. SQLite is against requiring
configuration.

>It would allow numerous projects to more easily migrate away from MySQL to
>SQLite without needing to clean up all their broken SQL instantly, and it
>  
>
But if it's supported in sqlite, they'll never migrate away from it.
Supporting something that's
non-standard because it makes it easier to migrate code isn't, to me,
enough justification. Supporting
something that's non-standard because it makes it easier to do something
important can be a good
idea. Fixing your sql to be standard is something you need to do
whenever you switch away from
mysql, no matter what you're migrating to.

>would allow projects that want to stick with MySQL to also offer a SQLite
>version with little effort.
>
>How off the wall am I with this?
>
>-m
>  
>
John LeSueur
Reply | Threaded
Open this post in threaded view
|

RE: RFC Beginning digit in column name

nedbatchelder
In reply to this post by ender-3
Dr. Hipp isn't showing his hand here, but the timeline indicates that he
added support for MySQL-style backquote quoting on Saturday:
http://www.sqlite.org/cvstrac/chngview?cn=2591

--Ned.
http://nedbatchelder.com
 

-----Original Message-----
From: Marcus Welz [mailto:[hidden email]]
Sent: Wednesday, 10 August, 2005 4:23 PM
To: [hidden email]
Subject: RE: [sqlite] RFC Beginning digit in column name

Hello there,

I think that adhering to standards is a Very Good Thing(tm). They are
standards for a reason. Deviations can introduce ambiguity, confusion,
complexity, vendor lock-ins and all sorts of other headaches.

That said, however, I believe that "no, because it's not the standard" isn't
exactly what a customer wants to hear. Then again "no" isn't what a customer
wants to ever hear in almost any context, but I digress..

Customers / users may wonder "Well, they (e.g. MySQL) can do it, why can't
you?" And I think in a fair number of situations, if possible to implement
at a reasonable amount of cost the answer should be "We can, but be warned,
it is NOT the standard."

Some proprietary extensions are rather handy, too, and may want be be
considered. I don't really think "`" and "[]" are useful nor pretty, but I
also don't think that it would be unethical to support it. On the other
hand, MySQL's "SQL_CALC_FOUND_ROWS" keyword and the "FOUND_ROWS()" function
for instance have made my life easy in the past. (SQL_CALC_FOUND_ROWS in a
query counts how many rows there would have been returned with the LIMIT
clause omitted, great for pagination if you want to display something like
"Rows 1 - 15, 400 total" all while running a complex query (with a whole
slew of JOINS and WHERE conditions) only once.

The short of it all: I don't know much about SQLite's internal architecture,
besides glossing over the web site documentation, but my suggestion is to
add a new PRAGMA statement ("PRAGMA tolerate_broken_sql = 1" :-), which
should be _disabled_ by default, but when enabled would honor proprietary
extensions such as "[]" and "`" for quotation, digits in column names, and
who knows, maybe even some of the other extensions and vendor specific
functions, etc.

It would allow numerous projects to more easily migrate away from MySQL to
SQLite without needing to clean up all their broken SQL instantly, and it
would allow projects that want to stick with MySQL to also offer a SQLite
version with little effort.

How off the wall am I with this?

-m

-----Original Message-----
From: Austin Ziegler [mailto:[hidden email]]
Sent: Wednesday, August 10, 2005 2:44 PM
To: [hidden email]
Subject: Re: [sqlite] RFC Beginning digit in column name

On 8/10/05, ender <[hidden email]> wrote:

> Austin Ziegler wrote:
>>On 8/9/05, ender <[hidden email]> wrote:
>>> So my simple feature request would be: allow '`' as a quoting symbol -
>>> as mySQL does. Or - what would be as helpful as the other idea - allow
>>> unquoted column names with leading digits - as mySQL does.
>>> Also see ticket # 1337  http://www.sqlite.org/cvstrac/tktview?tn=1337
>> I think that supporting '`' (backquote) would be a bad idea. Then
>> again, MySQL ignores so much of the SQL standard that I think that
>> importing any idea from MySQL is a bad idea.
> What are your reasons for thinking supporting '`' is a bad idea?

1. It's not part of the standard.
2. It's not part of the standard.
3. MySQL does it. Therefore, no one else should do it, because:
4. It's not part of the standard (and most of what MySQL does is not
part of the standard).

If you've learned "SQL" on MySQL, you haven't learned SQL. You've
learned MySQL. The same applies to people who want to do [tablename]
-- it's a Microsoftism that (IMO) should be *de-supported* in SQLite
because it's ugly and wrong.

The only quotes that SQL92 supports are ' and "; ' indicates data, "
indicates literal interpretation of table and column names. Not `, not
[].

If MySQL does it and it's not part of the standard, I'm opposed to it.

-austin
--
Austin Ziegler * [hidden email]
               * Alternate: [hidden email]