Hi everybody,
I found a strange behaviour of SQLite (3.6.19) when it relies on a
custom collation during a SELECT execution. So let me explain the
matter from the beginning:
I've got the following simple table:
CREATE TABLE Genre (
id INTEGER NOT NULL UNIQUE,
name VARCHAR(20) NOT NULL UNIQUE COLLATE MYCOLLATE,
image_id INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_Genre FOREIGN KEY (image_id)
REFERENCES Image (id)
ON DELETE SET NULL
ON UPDATE CASCADE);
-- Indeces
CREATE INDEX Genre_name_idx ON Genre(name);
and I fill it as follows:
INSERT INTO Genre VALUES(0,'',0);
INSERT INTO Genre VALUES(1,'Blues',0);
INSERT INTO Genre VALUES(2,'Classic Rock',0);
INSERT INTO Genre VALUES(3,'Country',0);
INSERT INTO Genre VALUES(4,'Dance',0);
INSERT INTO Genre VALUES(5,'Disco',0);
INSERT INTO Genre VALUES(6,'Funk',0);
INSERT INTO Genre VALUES(7,'Grunge',0);
INSERT INTO Genre VALUES(8,'Hip-Hop',0);
INSERT INTO Genre VALUES(9,'Jazz',0);
...
Then, when I try to execute:
SELECT id FROM Genre WHERE name = 'Jazz'
it doesn't yield any results. On the other hand, when I execute the
following statement:
INSERT INTO Genre(name,image_id) VALUES('Jazz',0)
SQLite correctly returns this errors:
SQLITE_CONSTRAINT[19]: constraint failed
SQLITE_CONSTRAINT[19]: column name is not unique
So my first thought was: my collation method has some bugs! So I used
the NOCASE bult-in collate:
name VARCHAR(20) NOT NULL UNIQUE COLLATE NOCASE
and it works fine. The next step was to try again MYCOLLATE and
compare all results of MYCOLLATE method with the NOCASE ones. To do
that I write the following piece of code:
int MyClass::xCompare (void* v, int iLen1, const void* str1, int
iLen2, const void* str2)
{
//MYCOLLATE
std::wstring s1;
std::wstring s2;
CStringConverter::FromUTF8toUCS2 ((const BYTE *) str1,iLen1, s1);
CStringConverter::FromUTF8toUCS2 ((const BYTE *) str2,iLen2, s2);
int iRes = CCollationStringComparer::Compare(s1.length(),
s1.c_str(), s2.length(), s2.c_str());
//NOCASE
int r = sqlite3StrNICmp(
(const char *)str1, (const char *)str2, (iLen1<iLen2)?iLen1:iLen2);
UNUSED_PARAMETER(v);
if( 0==r ){
r = iLen1-iLen2;
}
bool bNegative = (iRes < 0) && (r < 0);
bool bZero = (iRes == 0) && (r == 0);
bool bPositive = (iRes > 0) && (r > 0);
assert(bNegative || bZero || bPositive);
return iRes;
}
and I registered it:
sqlite3_create_collation(mpDB,”MYCOLLATE", SQLITE_UTF8, NULL,
MyClass::xCompare);
As you can see the assert condition ensures me that the two methods
yield the same results.
That's the strange behaviour: no assert is ever triggered even if the
SELECT statement keeps yielding no result; but if I change
return iRes; with
return r;
it works fine! I point out that CCollationStringComparer::Compare
always returns 1,0,-1 whereas sqlite3StrNICmp returns different
values (but always with the same sign!).
Any ideas about what's happening? Could it be a SQLite bug?
Cheers
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
