Actually this wasn't the issue after all... Indices have nothing to do with it.
The genre was being inserted from two different sources. It is a UTF-16 string, and in one case it was being inserted with a null terminator, and in another case it was not. Since I used "sqlite3_bind_text16" and specified a length that included the null terminator, it was stored in the database with that null terminator. Unfortunately when I do this, the string that I get back from the database is of length -1 compared to what I inserted. So for example if genre is "Rock" and I inserted "Rock0" where 0 is null terminator, I get "Rock" back and not "Rock0." Note below that POP is reported as 3 characters long, but was inserted as 4 with a null terminator. Interestingly enough, sqlite3 will give me two copies of POP when I ask for unique genres, if I insert a value as "POP" and another value as "POP0." So in a sense this was merely user error but also an interesting idiosyncracy of the sqlite3 database. Thanks, Brett -----Original Message----- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 23, 2007 11:41 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] bizarre query problem --- Brett Keating <[EMAIL PROTECTED]> wrote: > Msica independiente|text|19|0056_People Get Ready1_test1.wma > POP|text|3|0057_The Mighty Ship1_test1.wma 0058_The Mighty > POP|text|3|Quinn1_test1.wma > > Anyway, it turns out the problem was caused by creating an index on > the genre field. If I don't create an index, it works normally for > both OSes. Clearly there's a problem. Can you try testing with the latest version of sqlite? Assuming it is not already fixed, consider making a small test case and filing a ticket: http://www.sqlite.org/cvstrac/tktnew ________________________________________________________________________ ____________Get the free Yahoo! toolbar and rest assured with the added security of spyware protection. http://new.toolbar.yahoo.com/toolbar/features/norton/index.php ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------