Hello!
On Friday 30 October 2009 22:16:27 Simon Slavin wrote:
> > I think the text '1' must
> > be equal to numeric 1 always like to standart de-facto for RDBMS.
>
>
> Personally I think that 1 and 1.0 are the same, and that '1' is never
> the same as either. Some people and some languages feel that 1 is
> never the same as 1.0. It's all a point of view.
Now SQLite think that 1 is equal to '1' in some causes and think
different in other.
$ sqlite3
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test(a text);
sqlite> insert into test values(1);
sqlite> insert into test values('1');
sqlite> select * from test where a=1;
1
1
sqlite> select * from test where a='1';
1
1
$ sqlite3
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test(a int);
sqlite> insert into test values(1);
sqlite> insert into test values('1');
sqlite> select * from test where a=1;
1
1
sqlite> select * from test where a='1';
1
1
As you can see above 1 is always equal to '1'.
And integer is automatically converted to float:
select 1.0=1
1
But numeric is not converted to text before check:
sqlite> select 1='1';
0
sqlite> select 1.1='1.1';
0
Do you know some language where 1='1' or 1!='1' randomly?
The correct way is to convert numeric values to text before check
of equality.
SQLite datatyping is similar to Tcl by ideology. As example in Tcl:
$ tclsh8.5
tclsh8.5 [/tmp]expr {1==1.0?1:0}
1
tclsh8.5 [/tmp]expr {1=="1.0"?1:0}
1
tclsh8.5 [/tmp]expr {1=="1"?1:0}
1
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
