[sqlite] Why is empty string not equal to empty blob?
> 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). > > ~ > ~ > ~ > ~ > > Simon. Thank you guys. I follow this list so I may keep informed to the happenins with SQLite for my project. I have been having an issue on how to handle a defined DATE column. Be it NUMBER, or TEXT with the sqlite-jdbc. If its value is TEXT and I try to retrieve with getDate() a parsing error will occur if its format is not correct, but if I handle it with getString() no problem, don't care. These responses helped to clarify what is going on and how to deal with mixed type column content. typeof(thing). :) danap.
[sqlite] Why is empty string not equal to empty blob?
On 29-3-2015 18:53, Simon Slavin wrote:
>
>> On 29 Mar 2015, at 5:31pm, Luuk 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)
[sqlite] Why is empty string not equal to empty blob?
On 19-3-2015 16:02, Simon Slavin wrote:
>
> On 19 Mar 2015, at 2:56pm, Paul 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."
?
[sqlite] Why is empty string not equal to empty blob?
> On 29 Mar 2015, at 5:31pm, Luuk 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
Simon.
[sqlite] Why is empty string not equal to empty blob?
On Sunday, 29 March, 2015 10:31, Luuk inquired:
>On 19-3-2015 16:02, Simon Slavin wrote:
>> On 19 Mar 2015, at 2:56pm, Paul 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.
[sqlite] Why is empty string not equal to empty blob?
* Paul: > 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? In SQLite, string literals have manifest type string. This is different from SQL, where string literals have unknown type and are automatically cast as needed. sqlite> SELECT '1' = 1; 0 I think SQL92 requires the expression to be true.
[sqlite] Why is empty string not equal to empty blob?
On 2015-03-19 05:32 PM, Paul wrote: >>On 19 Mar 2015, at 3:19pm, Paul wrote: >> >>> This may cause very nasty hard-to-find bugs, since SQLite allows to store >>> any content inside BLOB field: >>> >>> sqlite> create table foo(a int, b int, primary key(a, b)); >>> sqlite> insert into foo(a, b) VALUES(1, ''), (1, x''); >>> sqlite> select *, length(b) from foo; >> True, but the content which is stored is not of BLOB type unless you stored >> a BLOB. Do >> >> select *, typeof(b), length(b) from foo; >> >> and you'll find that you get out what you put in. >> > Yeah, that's true. But still, this can cause nasty bugs. > It feels unnatural, because there is implicit conversion from string to int: I hear what you are saying Paul, but there is no comparison where different types (especially involving blobs) can ever be confused in a way that one iteration will survive a Uniqueness test and another won't. At least, I thought there wouldn't be, but from your post I decided to check anyway, trying all different kinds of ways to "trick" SQLite into duplicating a key by using casts and the like. One of my test scripts attached hereunder, but as of now I am unsuccessful. Someone else might be able to find a hole, but I couldn't break it in any way - thus no reason yet to think any kind of bug exists. The question remains as to whether one may regard an empty blob to be equal to zero or not, or indeed an empty string. I don't think so, but other opinions may exist. -- CREATE TABLE foo(a int, b int, PRIMARY KEY(a, b)); INSERT INTO foo(a, b) VALUES(1, ''), (1, x''); INSERT INTO foo(a, b) VALUES(2, 255), (2, x'FF'); INSERT INTO foo(a, b) VALUES(3, CAST(x'7F' AS INT)), (3, x'7F'); SELECT T1.*, length(T1.b) AS L1, T2.*, length(T2.b) AS L2 FROM foo AS T1 JOIN foo AS T2 ON T1.b=T2.b WHERE 1; -- a b L1 a b L2 -- - - -- - - -- -- 1 01 0 -- 1 01 0 -- 2 25532 255 3 -- 2 0xFF 12 0xFF1 -- 3 0 13 0 1 -- 3 0x7F 13 0x7F1 --Item Stats: Item No: 5 Query Size (Chars): 118 -- Result Columns:6 Result Rows: 6 -- VM Work Steps: 154 Rows Modified: 0 -- Full Query Time: -- --- --- --- --. -- Query Result: Success. -- SELECT T1.*, length(T1.b) AS L1, T2.*, length(T2.b) AS L2 FROM foo AS T1 JOIN foo AS T2 ON CAST(T1.b AS INT)=CAST(T2.b AS INT) WHERE 1; -- ab L1 a b L2 -- -- -- - - -- -- 1 01 0 -- 1 01 0 -- 1 02 0xFF 1 -- 1 03 0 1 -- 1 03 0x7F 1 -- 1 01 0 -- 1 01 0 -- 1 02 0xFF 1 -- 1 03 0 1 -- 1 03 0x7F 1 -- 2255 32 2553 -- 20xFF 11 0 -- 20xFF 11 0 -- 20xFF 12 0xFF 1 -- 20xFF 13 0 1 -- 20xFF 13 0x7F 1 -- 30 11 0 -- 30 11 0 -- 30 12 0xFF 1 -- 30 13 0 1 -- 30 13 0x7F 1 -- 30x7F 11 0 -- 30x7F 11 0 -- 30x7F 12 0xFF 1 -- 30x7F 13 0 1 -- 30x7F 13 0x7F 1 --Item Stats: Item No: 6 Query Size (Chars): 144 -- Result Columns:6 Result Rows: 26 -- VM Work Steps: 472 Rows Modified: 0 -- Full Query Time: -- --- --- --- --. -- Query Result: Success. -- UPDATE foo SET b=CAST(b AS INT) WHERE 1; -- 2015-03-19 18:25:18.614 | [ERROR] UNIQUE constraint failed: foo.a, foo.b -- Script Stats: Total Script Execution Time: 0d 00h 00m and 00.031s -- Total Script Query Time: 0d 00h 00m and 00.004s -- Total Database Rows Changed: 6 -- Total Virtual-Machine Steps: 851 -- Last executed Item Index:7 -- Last Script Error: Script Failed in Item 6: UNIQUE constraint failed: foo.a, foo.b -- -- 2015-03-19 18:25:18.615 | [Info] Script failed - Rolling back... -- 2015-03-19
[sqlite] Why is empty string not equal to empty blob?
> On 19 Mar 2015, at 3:19pm, Paul wrote: > > > This may cause very nasty hard-to-find bugs, since SQLite allows to store > > any content inside BLOB field: > > > > sqlite> create table foo(a int, b int, primary key(a, b)); > > sqlite> insert into foo(a, b) VALUES(1, ''), (1, x''); > > sqlite> select *, length(b) from foo; > > True, but the content which is stored is not of BLOB type unless you stored a > BLOB. Do > > select *, typeof(b), length(b) from foo; > > and you'll find that you get out what you put in. > Yeah, that's true. But still, this can cause nasty bugs. It feels unnatural, because there is implicit conversion from string to int: sqlite> select * from foo where a = '1'; a b -- -- 1 1 sqlite> select * from foo where a = '1.0'; a b -- -- 1 1 sqlite> select * from foo where a = 1.01; a b -- -- 1 1 yet not to BLOB.
[sqlite] Why is empty string not equal to empty blob?
> On 3/19/15, Paul 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? > > > > Because it has never before occurred to the developers that somebody > would compare a String to a Blob an expect them to be equal to one > another. This may cause very nasty hard-to-find bugs, since SQLite allows to store any content inside BLOB field: sqlite> create table foo(a int, b int, primary key(a, b)); sqlite> insert into foo(a, b) VALUES(1, ''), (1, x''); sqlite> select *, length(b) from foo; a b length(b) -- -- -- 1 0 1 0 And now, using sqlite3_column_blob() + sqlite3_column_bytes() I 'see' two empty blobs, kind of...
[sqlite] Why is empty string not equal to empty blob?
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?
Suppose I have data in FOO table. By what means can I tell
what is inside of my_blob column of FOO, when
SELECT length(my_blob) FROM FOO;
returns 0 for both empty string('') and empty blob (x'') alike?
And what is the reason behind this difference?
Thanks,
Paul
[sqlite] Why is empty string not equal to empty blob?
On 19 Mar 2015, at 3:19pm, Paul wrote: > This may cause very nasty hard-to-find bugs, since SQLite allows to store any > content inside BLOB field: > > sqlite> create table foo(a int, b int, primary key(a, b)); > sqlite> insert into foo(a, b) VALUES(1, ''), (1, x''); > sqlite> select *, length(b) from foo; True, but the content which is stored is not of BLOB type unless you stored a BLOB. Do select *, typeof(b), length(b) from foo; and you'll find that you get out what you put in. Simon.
[sqlite] Why is empty string not equal to empty blob?
On 19 Mar 2015, at 2:56pm, Paul 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.
Although the two types have many things in common and can be treated the same
for many operations, they're different. Like INTEGER and REAL. Though INTEGER
and REAL fail the '=' test you use above, which I accept makes things confusing.
> Suppose I have data in FOO table. By what means can I tell
> what is inside of my_blob column of FOO, when
>
> SELECT length(my_blob) FROM FOO;
>
> returns 0 for both empty string('') and empty blob (x'') alike?
SELECT typeof(my_blob),length(my_blob) FROM FOO;
> And what is the reason behind this difference?
Harder for me to answer unless I know why you think they should be the same.
Simon.
[sqlite] Why is empty string not equal to empty blob?
As a general rule of thumb, if things are different type, they can't be equal.
One already knows the column type. To compare apples to apples, one
would cast to convert them.
sqlite> SELECT '' = x'';
0
sqlite> SELECT cast('' as blob) = x'';
1
Perhaps some confusion comes from how numbers are stored and compared?
On Thu, Mar 19, 2015 at 11:19 AM, Paul wrote:
>> On 3/19/15, Paul 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?
>> >
>>
>> Because it has never before occurred to the developers that somebody
>> would compare a String to a Blob an expect them to be equal to one
>> another.
>
> This may cause very nasty hard-to-find bugs, since SQLite allows to store any
> content inside BLOB field:
>
> sqlite> create table foo(a int, b int, primary key(a, b));
> sqlite> insert into foo(a, b) VALUES(1, ''), (1, x'');
> sqlite> select *, length(b) from foo;
> a b length(b)
> -- -- --
> 1 0
> 1 0
>
> And now, using sqlite3_column_blob() + sqlite3_column_bytes() I 'see' two
> empty blobs, kind of...
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
[sqlite] Why is empty string not equal to empty blob?
On 3/19/15, Paul 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? > Because it has never before occurred to the developers that somebody would compare a String to a Blob an expect them to be equal to one another. -- D. Richard Hipp drh at sqlite.org
