On 29-3-2015 18:53, Simon Slavin wrote:
>
>> 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
>
Thanks for the explanation,
i get it, it works different from what expect,
and different compared to MySQL (and Oracle)
