RE: [sqlite] SQLite version 3 design question: '500'=500?
Yep. basically our "type less" string fields should have user definable
operator overload functions. Sounds like a big change that I doubt DRH would
implement anytime soon but it would definitely solve some of these
integer/numeric/string/datetime/etc.. conversion/comparisons. We would also
have to agree on what would constitute a constant custom data type
declaration.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Thursday, May 13, 2004 11:41 AM
To: Thomas, Basil
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?
Not only comparison, but all expressions - arithmetic etc.
[EMAIL PROTECTED]
com
To
13/05/2004 16:33 [EMAIL PROTECTED],
[EMAIL PROTECTED]
cc
[EMAIL PROTECTED]
Subject
RE: [sqlite] SQLite version 3
design question: '500'=500?
I think the point the SQL assumes is that everything is a string unless you
tell it otherwise. How you tell it otherwise can be explicitly done or
implicitly done.
SQLite seems to be making implicit data conversions when in fact the user
may mean something else. Therefore each implicit data conversion should
also
have an explicit
data conversion to tell SQLite EXACTLY how to interpret this data type.
Most
relational systems have an enforced schema that dictates each allowable
data
type and how these
data types can be converted from one type to another. SQLite which is
basically type less, should allow us to add functions to overload the
comparison operators so that we could convert/compare explicitly for each
of
our custom data types since we know exactly what we want . This should be
optional and would probably impact performance but at least we would get
the
right answer whenever we queried for the custom data types that we have
defined.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Thursday, May 13, 2004 11:18 AM
To: Fred Williams
Cc: [EMAIL PROTECTED]
Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?
Not terribly interested in pursuing this, but that is precisely the point -
letting the engine handle the 'technical' things means it needs to know the
types of data in it. If the engine doesn't do it, the user has to do it,
adding complexity and producing less reliable systems.
"Fred Williams"
<[EMAIL PROTECTED]
on.net>To
<[EMAIL PROTECTED]>
13/05/2004 15:12 cc
<[EMAIL PROTECTED]>
Subject
RE: [sqlite] SQLite version 3
design question: '500'=500?
I guess you read different books than I. The intent was to have the
language engine do the required type conversions freeing the user from
knowledge of such "technical" things. Like all languages it evolved, and
has not remained "simple."
Fred
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 13, 2004 8:51 AM
> To: Fred Williams
> Cc: [EMAIL PROTECTED]
> Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?
>
>
>
>
>
>
>
> Funny, I thought SQL was a more down to earth version of E. F. Codd's
> mathematical model 'Relational Algebra', with some concepts taken from
> Relational Calculus.
>
> To say types and strong typing weren't part of the original SQL concept
> isn't right. Typing gives data semantics.
>
> Vijay
>
...
--
RE: [sqlite] SQLite version 3 design question: '500'=500?
Not only comparison, but all expressions - arithmetic etc.
[EMAIL PROTECTED]
com
To
13/05/2004 16:33 [EMAIL PROTECTED],
[EMAIL PROTECTED]
cc
[EMAIL PROTECTED]
Subject
RE: [sqlite] SQLite version 3
design question: '500'=500?
I think the point the SQL assumes is that everything is a string unless you
tell it otherwise. How you tell it otherwise can be explicitly done or
implicitly done.
SQLite seems to be making implicit data conversions when in fact the user
may mean something else. Therefore each implicit data conversion should
also
have an explicit
data conversion to tell SQLite EXACTLY how to interpret this data type.
Most
relational systems have an enforced schema that dictates each allowable
data
type and how these
data types can be converted from one type to another. SQLite which is
basically type less, should allow us to add functions to overload the
comparison operators so that we could convert/compare explicitly for each
of
our custom data types since we know exactly what we want . This should be
optional and would probably impact performance but at least we would get
the
right answer whenever we queried for the custom data types that we have
defined.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Thursday, May 13, 2004 11:18 AM
To: Fred Williams
Cc: [EMAIL PROTECTED]
Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?
Not terribly interested in pursuing this, but that is precisely the point -
letting the engine handle the 'technical' things means it needs to know the
types of data in it. If the engine doesn't do it, the user has to do it,
adding complexity and producing less reliable systems.
"Fred Williams"
<[EMAIL PROTECTED]
on.net>To
<[EMAIL PROTECTED]>
13/05/2004 15:12 cc
<[EMAIL PROTECTED]>
Subject
RE: [sqlite] SQLite version 3
design question: '500'=500?
I guess you read different books than I. The intent was to have the
language engine do the required type conversions freeing the user from
knowledge of such "technical" things. Like all languages it evolved, and
has not remained "simple."
Fred
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 13, 2004 8:51 AM
> To: Fred Williams
> Cc: [EMAIL PROTECTED]
> Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?
>
>
>
>
>
>
>
> Funny, I thought SQL was a more down to earth version of E. F. Codd's
> mathematical model 'Relational Algebra', with some concepts taken from
> Relational Calculus.
>
> To say types and strong typing weren't part of the original SQL concept
> isn't right. Typing gives data semantics.
>
> Vijay
>
...
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
This e-mail may
be privileged and/or confidential, and the sender does not waive any
related rights and obligations. Any distribution, use or copying of this
e-mail or the information it contains by other than an intended recipient
is unauthorized. If you received this e-mail in error, please advise me (by
return e-mail or otherwise) immediately.
Ce courrier électronique est confidenti
RE: [sqlite] SQLite version 3 design question: '500'=500?
Ditto that.
[EMAIL PROTECTED]
com
To
13/05/2004 16:33 [EMAIL PROTECTED],
[EMAIL PROTECTED]
cc
[EMAIL PROTECTED]
Subject
RE: [sqlite] SQLite version 3
design question: '500'=500?
I think the point the SQL assumes is that everything is a string unless you
tell it otherwise. How you tell it otherwise can be explicitly done or
implicitly done.
SQLite seems to be making implicit data conversions when in fact the user
may mean something else. Therefore each implicit data conversion should
also
have an explicit
data conversion to tell SQLite EXACTLY how to interpret this data type.
Most
relational systems have an enforced schema that dictates each allowable
data
type and how these
data types can be converted from one type to another. SQLite which is
basically type less, should allow us to add functions to overload the
comparison operators so that we could convert/compare explicitly for each
of
our custom data types since we know exactly what we want . This should be
optional and would probably impact performance but at least we would get
the
right answer whenever we queried for the custom data types that we have
defined.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Thursday, May 13, 2004 11:18 AM
To: Fred Williams
Cc: [EMAIL PROTECTED]
Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?
Not terribly interested in pursuing this, but that is precisely the point -
letting the engine handle the 'technical' things means it needs to know the
types of data in it. If the engine doesn't do it, the user has to do it,
adding complexity and producing less reliable systems.
"Fred Williams"
<[EMAIL PROTECTED]
on.net>To
<[EMAIL PROTECTED]>
13/05/2004 15:12 cc
<[EMAIL PROTECTED]>
Subject
RE: [sqlite] SQLite version 3
design question: '500'=500?
I guess you read different books than I. The intent was to have the
language engine do the required type conversions freeing the user from
knowledge of such "technical" things. Like all languages it evolved, and
has not remained "simple."
Fred
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 13, 2004 8:51 AM
> To: Fred Williams
> Cc: [EMAIL PROTECTED]
> Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?
>
>
>
>
>
>
>
> Funny, I thought SQL was a more down to earth version of E. F. Codd's
> mathematical model 'Relational Algebra', with some concepts taken from
> Relational Calculus.
>
> To say types and strong typing weren't part of the original SQL concept
> isn't right. Typing gives data semantics.
>
> Vijay
>
...
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
This e-mail may
be privileged and/or confidential, and the sender does not waive any
related rights and obligations. Any distribution, use or copying of this
e-mail or the information it contains by other than an intended recipient
is unauthorized. If you received this e-mail in error, please advise me (by
return e-mail or otherwise) immediately.
Ce courrier électronique est confidentiel et protégé. L'expéditeur ne
renonce pas au
RE: [sqlite] SQLite version 3 design question: '500'=500?
I guess you read different books than I. The intent was to have the language engine do the required type conversions freeing the user from knowledge of such "technical" things. Like all languages it evolved, and has not remained "simple." Fred > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 13, 2004 8:51 AM > To: Fred Williams > Cc: [EMAIL PROTECTED] > Subject: RE: [sqlite] SQLite version 3 design question: '500'=500? > > > > > > > > Funny, I thought SQL was a more down to earth version of E. F. Codd's > mathematical model 'Relational Algebra', with some concepts taken from > Relational Calculus. > > To say types and strong typing weren't part of the original SQL concept > isn't right. Typing gives data semantics. > > Vijay > ... - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] SQLite version 3 design question: '500'=500?
Funny, I thought SQL was a more down to earth version of E. F. Codd's
mathematical model 'Relational Algebra', with some concepts taken from
Relational Calculus.
To say types and strong typing weren't part of the original SQL concept
isn't right. Typing gives data semantics.
Vijay
"Fred Williams"
<[EMAIL PROTECTED]
on.net>To
"Darren Duncan"
13/05/2004 14:23 <[EMAIL PROTECTED]>,
<[EMAIL PROTECTED]>
cc
Subject
RE: [sqlite] SQLite version 3
design question: '500'=500?
I seem to remember many, many moons ago when studying SQL for the first
time, learning that SQL syntax treats everything as character data no
matter
what the DB column data type is.
Also, I think this was to make the SQL syntax more "natural English
language" like. I believe the original intent of SQL syntax was to be
simple enough even management could understand it. Naturally that didn't
work :-)
Fred
> -Original Message-
> From: Darren Duncan [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 13, 2004 3:39 AM
> To: [EMAIL PROTECTED]
> Subject: Re: [sqlite] SQLite version 3 design question: '500'=500?
>
>
> At 8:19 PM -0400 5/12/04, D. Richard Hipp wrote:
> >The development team is making progress on SQLite version 3.0.
> >But we've run across an interesting puzzle. What should be
> >returned by this:
> >
> > SELECT '500'=500;
> >
> >Is the result "0" or "1"? In other words, what happens when
> >you compare a number to a string that looks like that number.
> >Are they equal or not?
>
> In my experience, strings are implicitely cast as numbers when used
> in a numerical context, which includes comparisons with either
> literal numbers or numerical columns. So I vote for the above two
> items having the result "TRUE".
>
> Take this for example, pretending that "bar" is a numerical column
> that does not have a uniqueness constraint.
>
> INSERT INTO foo (bar) VALUES (5);
> INSERT INTO foo (bar) VALUES ('5');
>
> SELECT bar FROM foo WHERE bar = 5;
> SELECT bar FROM foo WHERE bar = '5';
>
> With most database engines that I'm aware of, both of the insert
> statements will work whether the column is a string or a number, and
> both select statements will return 2 rows (assuming table empty
> before we started). That just seems normal.
>
> Also, such behaviour will benefit lazy programmers that generate SQL
> from code because they can just put '' around all literal values
> regardless of the data type, and it will just work.
>
> As for what the SQL standard says ...
>
> For reference:
> SQL-2003 Foundation, 6.12 "", p201
> SQL-2003 Foundation, 8.2 "", p375
>
...
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: RE: [sqlite] SQLite version 3 design question: '500'=500?
I agree that compatibility is what counts... Felipe Lopes Em 12 May 2004, Shawn Anderson escreveu: >I agree, I would like to see compatibility with results from other SQL >engines... > >Shawn > >-Original Message- >From: Keith Herold [mailto:[EMAIL PROTECTED] >Sent: Wednesday, May 12, 2004 8:38 PM >To: [EMAIL PROTECTED] >Subject: RE: [sqlite] SQLite version 3 design question: '500'=500? > >In MS SQL 2000, through the query analyzer > > SELECT '500' = 500 > >returns 500 . > > SELECT 500 = '500' > >returns > Server: Msg 170, Level 15, State 1, Line 1 > Line 1: Incorrect syntax near '='. > >Beyond these, I have no preference on whether they are true or false; I am >less worried about what the programming languages say as far as PERL, etc. >I would rather the return look like the majority vote on what the 'other' >SQL engines/manufacturers do. > >--Keith > >> -Original Message- >> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] >> Sent: Wednesday, May 12, 2004 5:20 PM >> To: [EMAIL PROTECTED] >> Subject: [sqlite] SQLite version 3 design question: '500'=500? >> >> >> The development team is making progress on SQLite version 3.0. But >> we've run across an interesting puzzle. What should be returned by >> this: >> >> SELECT '500'=500; >> >> Is the result "0" or "1"? In other words, what happens when you >> compare a number to a string that looks like that number. >> Are they equal or not? >> >> Other languages return a mixture of results here. Strings and number >> compare equal in AWK, Perl, PHP, Tcl, and SQLite version 2. String >> and numbers are not equal in Python and Ruby. >> >> Based on my experience, I would choose to make strings and numbers >> equal. But there are complications to that approach in SQLite 3.0. >> SQLite 3 supports manifest typing with 4 basic types: NULL, NUMERIC, >> TEXT, and BLOB. Objects sort in that order: NULLs first, followed by >> NUMERICs in numerical order, then TEXT in a user-defined collating >> sequence and finally BLOBs in memcmp() order. So '500' occurs at a >> completely different place in the sort order from 500. If comparison >> operators are to be consistent with sort order, the following must be >> true: >> >> 500 < 600 >> 600 < '500' >> >> But if that is the case, then clearly, '500' != 500. So unless >> somebody can come up with a better idea, SQLite version 3.0 will >> return "0" for the following: >> >> SELECT '500'=500; >> >> On the other hand, the following two statements will return "1" (or >> true): >> >> SELECT '500'+0=500; >> SELECT '500'=(500||''); >> >> Note that in other SQL engines, it is an error to compare a string to >> an integer (I think - somebody please correct me if I am wrong) so we >> cannot get any guidance there. >> >> Your thoughts? >> -- >> D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 >> >> >> - >> To unsubscribe, e-mail: [EMAIL PROTECTED] >> For additional commands, e-mail: [EMAIL PROTECTED] >> > >- >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] > >- >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] > >-- Felipe Lopes. _ Voce quer um iGMail protegido contra vírus e spams? Clique aqui: http://www.igmailseguro.ig.com.br Ofertas imperdíveis! Link: http://www.americanas.com.br/ig/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] SQLite version 3 design question: '500'=500?
I have another suggestion which may help with the comparison for sorting issue, but you may find it a bit outrageous. Essentially, I suggest making SQLite a little bit less typeless. You already have multiple underlying data type representations as I recall, such as numbers, character strings, and binary strings. Currently, this type is bound to each individual value stored in a table column. What I suggest instead is have typed columns where all values stored in that column are represented in the same way; eg numbers or strings. You would have as many possible column types as you have underlying representations. If you do this, then you no longer have any problem in figuring out how to sort. All values in a column declared as a character string will sort as their character representations. Likewise, all values in a number column will sort as numbers. You will not have to cast between a string and a number when sorting. Now, even with "typed" columns as I mention, they are still largely typeless compared to other databases. Fields are not fixed width, do not have maximum lengths, are stored internally as character strings, have a distinct null value, and so on. When a literal string is compared to a column, or assigned to it, then the literal will be implicitly cast to the same data type as the column. Such a solution should be more or less backwards compatible, quite easy and/or simple to implement, and simplify a number of things. It also provides a bit more strictness for those of us who want it. (For example, if I declare a numerical column, I would be happy for all values in that column to be treated like numbers regardless of the syntax used to insert the values.) The main drawback I see is that this change may break some applications which assume numbers-treated-as-numbers and characters-treated-as-characters are stored in the same column at the same time. But then again, I can't think of any real world example where this would be the case. So what do you think? -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] SQLite version 3 design question: '500'=500?
At 8:19 PM -0400 5/12/04, D. Richard Hipp wrote:
The development team is making progress on SQLite version 3.0.
But we've run across an interesting puzzle. What should be
returned by this:
SELECT '500'=500;
Is the result "0" or "1"? In other words, what happens when
you compare a number to a string that looks like that number.
Are they equal or not?
In my experience, strings are implicitely cast as numbers when used
in a numerical context, which includes comparisons with either
literal numbers or numerical columns. So I vote for the above two
items having the result "TRUE".
Take this for example, pretending that "bar" is a numerical column
that does not have a uniqueness constraint.
INSERT INTO foo (bar) VALUES (5);
INSERT INTO foo (bar) VALUES ('5');
SELECT bar FROM foo WHERE bar = 5;
SELECT bar FROM foo WHERE bar = '5';
With most database engines that I'm aware of, both of the insert
statements will work whether the column is a string or a number, and
both select statements will return 2 rows (assuming table empty
before we started). That just seems normal.
Also, such behaviour will benefit lazy programmers that generate SQL
from code because they can just put '' around all literal values
regardless of the data type, and it will just work.
As for what the SQL standard says ...
For reference:
SQL-2003 Foundation, 6.12 "", p201
SQL-2003 Foundation, 8.2 "", p375
8.2 Syntax Rule #3 says:
The declared types of the corresponding fields of the two
s shall be comparable.
6.12 Syntax Rule #6 says that a comparison between numeric data
(EN,AN) and character data (C,VC,FC,CL) is syntactically valid
without restriction (Y).
There are probably more references to this matter, which may be
important, but it looks to me that the standard says you can compare
numbers and strings which look like them.
At 11:44 PM -0700 5/12/04, Kevin Van Vechten wrote:
Another interesting data point, note the difference between ' and ".
Welcome to psql 7.4, the PostgreSQL interactive terminal.
kevin=# select '500'=500;
?column?
--
t
(1 row)
kevin=# select "500"=500;
ERROR: column "500" does not exist
Were you surprised?
Both the SQL standard and most SQL implementations that I know of
always treat '' as literal strings and "" as optional identifier
delimiters (their presence makes the identifier name case-sensitive,
whereas a bareword identifier is case-insensitive, and delimited
identifiers can also contain nearly any character, including
whitespace) such as column or table names.
For reference:
SQL-2003 Foundation, 5.3 "", p143
SQL-2003 Foundation, 5.4 "Names and identifiers", p151
SQL-2003 Foundation, 6.6 "", p183
Mind you, MySQL is a bit different in that it uses `` as the
identifier delimiter, unless that's just an option in addition to "".
Technically, I think, the SQL standard says you can use almost
anything as the delimiter, though "" is still the convention.
So '' only should be a string literal. See also B'' for quoting bit
strings as ones and zeros, and X'' for quoting binary strings as
hexits, says the standard.
In case you want to do this research yourself, copies of the entire
almost-final (indistinguishable delta) SQL-2003 spec are here in PDF
form, from which I quote (and treat as gospel):
http://www.wiscorp.com/SQLStandards.html
-- Darren Duncan
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] SQLite version 3 design question: '500'=500?
Similar for Sybase ASE 12.5 (wasn't it MSSQL ancestor ?): SELECT '500' = 500 500 === 500 SELECT 500 = '500' Incorrect syntax near '=' I think "SELECT '500' = 500" is not a comparison here, just selection of constant 500 with '500' as column name, just like "select 500 as '500'". Regards, Jarek > -Original Message- > From: Shawn Anderson [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 13, 2004 2:50 AM > To: [EMAIL PROTECTED] > Subject: RE: [sqlite] SQLite version 3 design question: '500'=500? > > > I agree, I would like to see compatibility with results from other SQL > engines... > > Shawn > > -Original Message- > From: Keith Herold [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 12, 2004 8:38 PM > To: [EMAIL PROTECTED] > Subject: RE: [sqlite] SQLite version 3 design question: '500'=500? > > In MS SQL 2000, through the query analyzer > >SELECT '500' = 500 > > returns 500 . > >SELECT 500 = '500' > > returns > Server: Msg 170, Level 15, State 1, Line 1 > Line 1: Incorrect syntax near '='. > > Beyond these, I have no preference on whether they are true > or false; I am > less worried about what the programming languages say as far > as PERL, etc. . > I would rather the return look like the majority vote on what > the 'other' > SQL engines/manufacturers do. > > --Keith > > > -Original Message- > > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, May 12, 2004 5:20 PM > > To: [EMAIL PROTECTED] > > Subject: [sqlite] SQLite version 3 design question: '500'=500? > > > > > > The development team is making progress on SQLite version 3.0. But > > we've run across an interesting puzzle. What should be returned by > > this: > > > > SELECT '500'=500; > > > > Is the result "0" or "1"? In other words, what happens when you > > compare a number to a string that looks like that number. > > Are they equal or not? > > > > Other languages return a mixture of results here. Strings > and number > > compare equal in AWK, Perl, PHP, Tcl, and SQLite version 2. String > > and numbers are not equal in Python and Ruby. > > > > Based on my experience, I would choose to make strings and numbers > > equal. But there are complications to that approach in > SQLite 3.0. > > SQLite 3 supports manifest typing with 4 basic types: NULL, > NUMERIC, > > TEXT, and BLOB. Objects sort in that order: NULLs first, > followed by > > NUMERICs in numerical order, then TEXT in a user-defined collating > > sequence and finally BLOBs in memcmp() order. So '500' occurs at a > > completely different place in the sort order from 500. If > comparison > > operators are to be consistent with sort order, the > following must be > > true: > > > > 500 < 600 > > 600 < '500' > > > > But if that is the case, then clearly, '500' != 500. So unless > > somebody can come up with a better idea, SQLite version 3.0 will > > return "0" for the following: > > > > SELECT '500'=500; > > > > On the other hand, the following two statements will return "1" (or > > true): > > > > SELECT '500'+0=500; > > SELECT '500'=(500||''); > > > > Note that in other SQL engines, it is an error to compare a > string to > > an integer (I think - somebody please correct me if I am > wrong) so we > > cannot get any guidance there. > > > > Your thoughts? > > -- > > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > > > > > - > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] SQLite version 3 design question: '500'=500?
Another interesting data point, note the difference between ' and ". Welcome to psql 7.4, the PostgreSQL interactive terminal. kevin=# select '500'=500; ?column? -- t (1 row) kevin=# select "500"=500; ERROR: column "500" does not exist On May 12, 2004, at 5:19 PM, D. Richard Hipp wrote: Note that in other SQL engines, it is an error to compare a string to an integer (I think - somebody please correct me if I am wrong) so we cannot get any guidance there. Your thoughts? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] SQLite version 3 design question: '500'=500?
Actually, I don't think MS SQL's reaction is really all that stupid. In the statement SELECT '500' = 500, I believe it is treating '500' as a field name, and assigning it the value 500. On the other hand, the statement SELECT 500 = '500' would in fact fail, because you can't use an integer as a field name without having it in quotes. Correct me if I'm wrong, but I believe this is why MS SQL returned the results that it did. Eric Pankoke Founder Point Of Light Software http://www.polsoftware.com "Lighting The Path To Glory" -Original Message- From: Andrew Piskorski [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 8:15 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] SQLite version 3 design question: '500'=500? On Wed, May 12, 2004 at 05:37:49PM -0700, Keith Herold wrote: > In MS SQL 2000, through the query analyzer > >SELECT '500' = 500 > > returns 500 . > >SELECT 500 = '500' > > returns > Server: Msg 170, Level 15, State 1, Line 1 > Line 1: Incorrect syntax near '='. Well that's sure inordinately stupid behavior. The test "A = B" should give the same result as "B = A", at least! In Oracle 8.1.7.4, string '5' does equal integer 5, but you seem to have to ask it via a case statement: SQL> select 5 = 5 from dual; ERROR at line 1: ORA-00923: FROM keyword not found where expected SQL> select case when 5 = '5' then 1 else 0 end as bool from dual; BOOL -- 1 SQL> select case when '5' = 5 then 1 else 0 end as bool from dual; BOOL -- 1 > > But if that is the case, then clearly, '500' != 500. So > > unless somebody can come up with a better idea, SQLite > > version 3.0 will return "0" for the following: > > > > SELECT '500'=500; > > > > On the other hand, the following two statements will return > > "1" (or true): > > > > SELECT '500'+0=500; > > SELECT '500'=(500||''); That sounds ok to me. Wouldn't it be clearer to have explicit cast statements though, rather than doing weird no-ops like "||''" solely to force a type conversion? Also, since you're introducing manifest typing, it would probably be very handy to have good boolean tests both for "is X currently of type Y?" and "CAN X be of type Y?". E.g., "CAN this thing be an integer?", where by "can" I mean, "Is it currently an integer, or if it is currently stored as a some other type, can it be LOSSLESSLY converted to an integer?" Some languages (e.g., S-Plus), make the latter oddly difficult. (Not THAT difficult; in S you can write that "can be integer" test in 5 lines, but those 5 lines are also somewhat easy to get wrong.) I think that in any system where objects can automatically change type, both the those sorts of "can be" boolean tests should also be built in. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] SQLite version 3 design question: '500'=500?
Results from Firebird 1.5 (thanks for the syntax, Andrew)... SQL> select '500' = 500; Statement failed, SQLCODE = -104 Dynamic SQL Error -SQL error code = -104 -Token unknown - line 1, char 14 -= SQL> select 500 = '500'; Statement failed, SQLCODE = -104 Dynamic SQL Error -SQL error code = -104 -Token unknown - line 1, char 12 -= SQL> select 500 = 500; Statement failed, SQLCODE = -104 Dynamic SQL Error -SQL error code = -104 -Token unknown - line 1, char 12 -= SQL> select case when 5 = '5' then 1 else 0 end as bool from t1; BOOL 1 SQL> select case when '5' = 5 then 1 else 0 end as bool from t1; BOOL 1 e - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] SQLite version 3 design question: '500'=500?
In MS SQL 2000, through the query analyzer SELECT '500' = 500 returns 500 . SELECT 500 = '500' returns Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '='. Beyond these, I have no preference on whether they are true or false; I am less worried about what the programming languages say as far as PERL, etc. . I would rather the return look like the majority vote on what the 'other' SQL engines/manufacturers do. --Keith > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 12, 2004 5:20 PM > To: [EMAIL PROTECTED] > Subject: [sqlite] SQLite version 3 design question: '500'=500? > > > The development team is making progress on SQLite version > 3.0. But we've run across an interesting puzzle. What should > be returned by this: > > SELECT '500'=500; > > Is the result "0" or "1"? In other words, what happens when > you compare a number to a string that looks like that number. > Are they equal or not? > > Other languages return a mixture of results here. Strings > and number compare equal in AWK, Perl, PHP, Tcl, and SQLite > version 2. String and numbers are not equal in Python and Ruby. > > Based on my experience, I would choose to make strings and > numbers equal. But there are complications to that approach > in SQLite 3.0. SQLite 3 supports manifest typing with 4 > basic types: NULL, NUMERIC, TEXT, and BLOB. Objects sort in > that order: NULLs first, followed by NUMERICs in numerical > order, then TEXT in a user-defined collating sequence and > finally BLOBs in memcmp() order. So '500' occurs at a > completely different place in the sort order from 500. If > comparison operators are to be consistent with sort order, > the following must be true: > > 500 < 600 > 600 < '500' > > But if that is the case, then clearly, '500' != 500. So > unless somebody can come up with a better idea, SQLite > version 3.0 will return "0" for the following: > > SELECT '500'=500; > > On the other hand, the following two statements will return > "1" (or true): > > SELECT '500'+0=500; > SELECT '500'=(500||''); > > Note that in other SQL engines, it is an error to compare > a string to an integer (I think - somebody please correct > me if I am wrong) so we cannot get any guidance there. > > Your thoughts? > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] SQLite version 3 design question: '500'=500?
Hello, On 12 may 2004, at 20:19, D. Richard Hipp wrote: SELECT '500'=500; Is the result "0" or "1"? In other words, what happens when you compare a number to a string that looks like that number. Are they equal or not? I vote for "0". Regards, -- Tito - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
