> On 29 Mar 2015, at 5:31pm, Luuk <luuk34 at gmail.com> wrote:
>
> On 19-3-2015 16:02, Simon Slavin wrote:
>>
>> One is a string. The other is a BLOB. SQLite doesn't even get as far as
>> testing the contents, it knows they are of different types.
>
> C:\temp>sqlite3
> SQLite version 3.8.8.3 2015-02-25 13:29:11
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table test (x string, y blob);
> sqlite> insert into test values ('x','x');
> sqlite> select * from test;
> x|x
> sqlite> select x,y, x=y from test where x=y;
> x|x|1
> sqlite>
>
> Can you comment on:
> "SQLite doesn't even get as far as testing the contents, it knows they are of
> different types."
You explicitly put strings into both columns. The two values are the same even
though you declared the columns differently. As I told you before, if you want
to see what type something is, use typeof(thing).
sqlite> create table testsb (x string, y blob);
sqlite> insert into testsb values ('x', 'x');
sqlite> select x, y, typeof(x), typeof(y), x=y from testsb;
x|x|text|text|1
So far, the same as you did. Now ...
sqlite> insert into testsb values ('x', x'78');
sqlite> insert into testsb values (x'78', x'78');
sqlite> select x, y, typeof(x), typeof(y), x=y from testsb;
x|x|text|text|1
x|x|text|blob|0
x|x|blob|blob|1
Column type doesn't matter:
sqlite> create table testbb (x blob, y blob);
sqlite> insert into testbb values ('x', 'x');
sqlite> insert into testbb values ('x', x'78');
sqlite> insert into testbb values (x'78', x'78');
sqlite> select x, y, typeof(x), typeof(y), x=y from testbb;
x|x|text|text|1
x|x|text|blob|0
x|x|blob|blob|1
It has just occurred to me that you might not know what in SQLite columns have
affinities, not fixed types. You can store any type of value in any column.
SQLite will convert one to another only on the route of TEXT --> FLOAT -->
INTEGER. For more on this see section 2.0 of
<https://www.sqlite.org/datatype3.html>
Simon.
