On Sunday, 29 March, 2015 10:31, Luuk <luuk34 at gmail.com> inquired:
>On 19-3-2015 16:02, Simon Slavin wrote:
>> On 19 Mar 2015, at 2:56pm, Paul <devgs at ukr.net> wrote:
>>> Maybe this question was already asked and explained.
>>> Or maybe it is documented somewhere (could not fiund it).
>>> Sorry, if this is the case, but why does
>>> SELECT '' = x'';
>>> yields 0?
>> 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."?
Because it is obvious? The affinity of the column does not control the type of
data stored, it is merely a preference of the datatype which will be used if
and only if it can:
SQLite version 3.8.9 2015-03-23 21:32:50
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x (x text, y blob);
sqlite> insert into x values ('x', 'x');
sqlite> insert into x values ('x', 0x78);
sqlite> insert into x values ('x', X'78');
sqlite> select x, y, typeof(x), typeof(y), x==y from x;
x|x|text|text|1
x|120|text|integer|0
x|x|text|blob|0
sqlite>
So, the first insert inserts text values into each column (because text is
provided). The second inserts text and an integer (because that is what is
provided). The third text and a blob (because that it what is provided). You
will note that SQLite is well aware of the type of the data stored. You will
also note that the test for equality is only true where the type of the data
compared is the same, even though in all cases the actual data is the single
byte 0x78.
You may further note that you can use cast(thing as type) to do type
conversions which will result in comparable datatypes:
sqlite> select x, cast(y as text), typeof(x), typeof(cast(y as text)),
x==cast(y as text) from x;
x|x|text|text|1
x|120|text|text|0
x|x|text|text|1
sqlite> select cast(x as blob), cast(y as blob), typeof(cast(x as blob)),
typeof(cast(y as blobl)), cast(x as blob)==cast(y as blob) from x;
x|x|blob|blob|1
x|120|blob|blob|0
x|x|blob|blob|1
Do this help your understanding?
---
Theory is when you know everything but nothing works. Practice is when
everything works but no one knows why. Sometimes theory and practice are
combined: nothing works and no one knows why.
