On 26/02/2009 9:45 PM, John Machin wrote:
> On 26/02/2009 8:23 PM, Marian Aldenhoevel wrote:
>> Hi,
>>
>> I am having a strange problem with a sqlite3 database. See the following
>> transcript:
>>
>> > sqlite3 kdb "select * from kfz where kfznr=48482364;"
>> > 48482364|48|0|0C|00|00|0||20|5B93|1746294314|||0|GP-T 1006|0
>>
>> kfznr is the primary key, so this is to be expected. Now two queries as
>> fired from the application code:
>>
>> > sqlite3 kdb "select * from kfz where CRC32=-797785824;"
>> > 48482364|48|0|0C|00|00|0||20|5B93|-797785824|||0|GP-T 1006|0
>> > 20209001|20|1|3C|00|32|24999||13|CE42|-797785824|||0|FL-HH 11|1
>> > 20209001|20|1|3C|00|32|24999||13|CE42|-797785824|||0|FL-HH 11|1
>>
>> > kdb "select * from kfz where CRC32=-1509747892;"
>> > 48482364|48|0|0C|00|00|0||20|5B93|-1509747892|||0|GP-T 1006|0
>> > 20209667|20|1|3C|00|32|202880||99|4FBD|-1509747892|||0|FL-AK 98|1
>> > 20209667|20|1|3C|00|32|202880||99|4FBD|-1509747892|||0|FL-AK 98|1
>>
>> What could cause 48482364 to show up in both results with a different
>> value for CRC32?
>
> Another problem: the second and third results from each of query 2 and
> query 3 are identical; this should not be so because the first column is
> a primary key.
>
> Note that kfznr is allegedly TEXT ... interesting question: is it
> possible to have two rows, one where typeof(kfznr) is text and the other
> is integer AND they are not considered equal for primary key purposes?
>
> Try repeating all 3 queries with
> select rowid, typeof(kfznr), * from ...
So having re-read all about affinity, and done a few desultory tests,
maybe that's not the problem. Maybe it's a WYSI*NOT*WYG problem:
C:\devel\sqlite3>sqlite3
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo (bar text primary key, zot integer);
sqlite> insert into foo values('1234', 1);
sqlite> insert into foo values(1234, 1);
SQL error: column bar is not unique
sqlite> insert into foo values(4567, 2);
sqlite> insert into foo values('4567', 3);
SQL error: column bar is not unique
sqlite> insert into foo values('4567' || X'00', 3);
sqlite> insert into foo values('4567' || X'0000', 4);
sqlite> select rowid, typeof(bar), quote(bar), length(bar), * from foo;
1|text|'1234'|4|1234|1
2|text|'4567'|4|4567|2
3|text|'4567'|4|4567|3
4|text|'4567'|4|4567|4
sqlite> -- UH OH!!!
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
