RE: [sqlite] bizarre query problem
--- Brett Keating <[EMAIL PROTECTED]> wrote: > 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. If you had inserted the text with the null terminator as a blob, then sqlite would have reported it as follows: SQLite version 3.3.17 Enter ".help" for instructions sqlite> create table t1(a text); sqlite> insert into t1 values(x'504F5000'); sqlite> select * from t1; POP sqlite> select a, typeof(a), hex(a), length(a) from t1 where a LIKE '%POP'; POP|blob|504F5000|4 But I guess sqlite has to take your word for it for UTF strings when you supply a length that is wrong. The other option is that sqlite could convert UTF strings with embedded nulls to blobs. Not sure what is the better option. 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] -
RE: [sqlite] bizarre query problem
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] -
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 > POP|text|3|0058_The Mighty 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] -
RE: [sqlite] bizarre query problem
It said that the hex function was not found so I skipped that. Msica independiente|text|19|0056_People Get Ready1_test1.wma POP|text|3|0057_The Mighty Ship1_test1.wma POP|text|3|0058_The Mighty 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. Thanks, Brett -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 22, 2007 9:07 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] bizarre query problem Joe Wilson <[EMAIL PROTECTED]> wrote: > Just for kicks, what happens on both platforms when you issue: > > select genre, length(genre), hex(genre), filename > from objects where media_type=1; > Make that: select genre, typeof(genre), length(genre), hex(genre), filename from objects where media_type=1; -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] bizarre query problem
Joe Wilson <[EMAIL PROTECTED]> wrote: > Just for kicks, what happens on both platforms when you issue: > > select genre, length(genre), hex(genre), filename > from objects where media_type=1; > Make that: select genre, typeof(genre), length(genre), hex(genre), filename from objects where media_type=1; -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] bizarre query problem
Just for kicks, what happens on both platforms when you issue: select genre, length(genre), hex(genre), filename from objects where media_type=1; as well as: select count(*) from objects where genre LIKE '%POP%'; > I have a bizarre problem. Here is an example of something I tried in > sqlite3 3.3.8: > > sqlite> select genre,filename from objects where media_type=1; > query abbreviated... > Msica independiente|0056_People Get Ready1_test1.wma > POP|0057_The Mighty Ship1_test1.wma > POP|0058_The Mighty Quinn1_test1.wma > query abbreviated... > sqlite> select genre,filename from objects where genre='POP'; > sqlite> > > So basically, no results are returned from the second query although > clearly there are items in the list with genre='POP'. > > This problem only happens on Linux. On Windows, the query returns the > results as expected... Which makes it yet more bizarre. Get the Yahoo! toolbar and be alerted to new email wherever you're surfing. http://new.toolbar.yahoo.com/toolbar/features/mail/index.php - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] bizarre query problem
Nothing you've mentioned is out of the ordinary. I would expect the same behavior on both platforms. Can you post the complete schema, and the exact query that exhibits the problem? (And perhaps a couple of insert statements into the objects table). Without this I don't think anyone can recreate the problem. --- Brett Keating <[EMAIL PROTECTED]> wrote: > I have a bizarre problem. Here is an example of something I tried in > sqlite3 3.3.8: > > sqlite> select genre,filename from objects where media_type=1; > query abbreviated... > Msica independiente|0056_People Get Ready1_test1.wma > POP|0057_The Mighty Ship1_test1.wma > POP|0058_The Mighty Quinn1_test1.wma > query abbreviated... > sqlite> select genre,filename from objects where genre='POP'; > sqlite> > > So basically, no results are returned from the second query although > clearly there are items in the list with genre='POP'. > > This problem only happens on Linux. On Windows, the query returns the > results as expected... Which makes it yet more bizarre. Got a little couch potato? Check out fun summer activities for kids. http://search.yahoo.com/search?fr=oni_on_mail=summer+activities+for+kids=bz - To unsubscribe, send email to [EMAIL PROTECTED] -
