Here is some additional examples of the weirdness I get. Firstly lets show the contents of the table:
select * from dv_routing_zones; 8200|1|1152530317|5 8300|4|1152530318|1 8400|5|1152530321|1 8500|11|1152530325|1 If I now set INC key = 1 just before the call to sqlite3BtreeMoveto I get this behaviour. ---------- MNP_ROUTE_MASK ----------- 2007-04-09 18:02:34[INFO] HDR-Offset = `2' 2007-04-09 18:02:34[INFO] Serial type [0]=`21' 2007-04-09 18:02:34[INFO] Col[0]=`8300' ( 4 bytes ) 2007-04-09 18:02:34[INFO] ********************* 2007-04-09 18:02:34[INFO] Search result = 0 2007-04-09 18:02:34[INFO] INC key = 1 2007-04-09 18:02:34[INFO] cursor stay not next 2007-04-09 18:02:34[INFO] HDR-Offset = `3' 2007-04-09 18:02:34[INFO] Serial type [0]=`21' 2007-04-09 18:02:34[INFO] Serial type [1]=`1' 2007-04-09 18:02:34[INFO] Col[0]=`8400' ( 4 bytes ) 2007-04-09 18:02:34[INFO] Col[1]=`3' ( 1 bytes ) 2007-04-09 18:02:34[INFO] >>>>>>>>>>>>>>NO MATCH!<<<<<<<<<<<<<<<< 2007-04-09 18:02:34[INFO] ------------------------------------------------- 2007-04-09 18:02:34[INFO] ---------- MNP_ROUTE_MASK ----------- 2007-04-09 18:02:34[INFO] HDR-Offset = `2' 2007-04-09 18:02:34[INFO] Serial type [0]=`21' 2007-04-09 18:02:34[INFO] Col[0]=`8200' ( 4 bytes ) 2007-04-09 18:02:34[INFO] ********************* 2007-04-09 18:02:34[INFO] Search result = 0 2007-04-09 18:02:34[INFO] INC key = 1 2007-04-09 18:02:34[INFO] cursor stay not next 2007-04-09 18:02:34[INFO] HDR-Offset = `3' 2007-04-09 18:02:34[INFO] Serial type [0]=`21' 2007-04-09 18:02:34[INFO] Serial type [1]=`1' 2007-04-09 18:02:34[INFO] Col[0]=`8200' ( 4 bytes ) 2007-04-09 18:02:34[INFO] Col[1]=`1' ( 1 bytes ) 2007-04-09 18:02:34[INFO] <<<<<<<<<<<<<<<MATCH!>>>>>>>>>>>>>>>>>> This time the first lookup if functioning properly where as with the previous post it did not. However now the second lookup is not working again. The cursor landed '8200' where it should have landed on the index entry '8300'. Am I not understandding how sqlite3BtreeMoveto works or does this look weird? To show another example of a lookup where inc key is set to 0 and where we are looking for '8400' the lookup works fine again: ---------- MNP_ROUTE_MASK ----------- 2007-04-09 18:09:36[INFO] HDR-Offset = `2' 2007-04-09 18:09:36[INFO] Serial type [0]=`21' 2007-04-09 18:09:36[INFO] Col[0]=`8400' ( 4 bytes ) 2007-04-09 18:09:36[INFO] ********************* 2007-04-09 18:09:36[INFO] Search result = 0 2007-04-09 18:09:36[INFO] INC key = 0 2007-04-09 18:09:36[INFO] cursor stay not next 2007-04-09 18:09:36[INFO] HDR-Offset = `3' 2007-04-09 18:09:36[INFO] Serial type [0]=`21' 2007-04-09 18:09:36[INFO] Serial type [1]=`1' 2007-04-09 18:09:36[INFO] Col[0]=`8400' ( 4 bytes ) 2007-04-09 18:09:36[INFO] Col[1]=`3' ( 1 bytes ) 2007-04-09 18:09:36[INFO] <<<<<<<<<<<<<<<MATCH!>>>>>>>>>>>>>>>>>> pompomJuice wrote: > > Hi. > > I am using sqlite in an unusual way. I’m using it as a lookup table which > failed miserably when I realized upon implementation that I could get a > maximum of 300-400 lookups per second using a conventional "select * from > table where column = key" type query. Clearly there was overhead involved > that I did not anticipate when I planned on using SQLite as a lookup > table. The thing is though the maintenance of the lookup tables etc. works > very nicely with SQLite and I simply cannot bring myself to give up that > part of my program. > > So I went and manually accessed the btree back end to do my lookups which > was very successful. However, upon discovering some other issue also > discussed in this forum I upgraded and now I am getting some weird issues. > My lookups are sometimes not finding the records when using v3.3.15/14 as > they were in 3.3.12. I have checked everything and I simply cannot find > the problem. The kicker is that if I access the DB with $sqlite3 and do a > lookup on the entry via a simple select statement it works fine: > > select * from dv_routing_zones where mnp_route_mask = '8300'; > 8300|4|1152530318|1 > > So you ask why am I writing this post clearly my implementation must be > broken. It has to be! This is where the weirdness comes in. Let me show > some output from my program: > > ---------- MNP_ROUTE_MASK ----------- > 2007-04-09 17:16:35[INFO] HDR-Offset = `2' > 2007-04-09 17:16:35[INFO] Serial type [0]=`21' > 2007-04-09 17:16:35[INFO] Col[0]=`8300' ( 4 bytes ) > 2007-04-09 17:16:35[INFO] ********************* > 2007-04-09 17:16:35[INFO] Search result = 0 > 2007-04-09 17:16:35[INFO] INC key = 0 > 2007-04-09 17:16:35[INFO] cursor stay not next > 2007-04-09 17:16:35[INFO] HDR-Offset = `3' > 2007-04-09 17:16:35[INFO] Serial type [0]=`21' > 2007-04-09 17:16:35[INFO] Serial type [1]=`1' > 2007-04-09 17:16:35[INFO] Col[0]=`8200' ( 4 bytes ) > 2007-04-09 17:16:35[INFO] Col[1]=`1' ( 1 bytes ) > 2007-04-09 17:16:35[INFO] >>>>>>>>>>>>>>NO MATCH!<<<<<<<<<<<<<<<< > 2007-04-09 17:16:35[INFO] > ------------------------------------------------- > 2007-04-09 17:16:35[INFO] ---------- MNP_ROUTE_MASK ----------- > 2007-04-09 17:16:35[INFO] HDR-Offset = `2' > 2007-04-09 17:16:35[INFO] Serial type [0]=`21' > 2007-04-09 17:16:35[INFO] Col[0]=`8200' ( 4 bytes ) > 2007-04-09 17:16:35[INFO] ********************* > 2007-04-09 17:16:35[INFO] Search result = 0 > 2007-04-09 17:16:35[INFO] INC key = 0 > 2007-04-09 17:16:35[INFO] cursor stay not next > 2007-04-09 17:16:35[INFO] HDR-Offset = `3' > 2007-04-09 17:16:35[INFO] Serial type [0]=`21' > 2007-04-09 17:16:35[INFO] Serial type [1]=`1' > 2007-04-09 17:16:35[INFO] Col[0]=`8200' ( 4 bytes ) > 2007-04-09 17:16:35[INFO] Col[1]=`1' ( 1 bytes ) > 2007-04-09 17:16:35[INFO] <<<<<<<<<<<<<<<MATCH!>>>>>>>>>>>>>>>>>> > > Basically what is going on here is that above the ****** I print the key > that I pass to sqlite3BtreeMoveto and then below the ****** I print they > key that the cursor landed on. The thing that I cannot understand is why > in the first lookup the search result returned 0 when clearly the cursor > is pointing to a value that does not match they key that I fed it. In v > 3.3.12 the search result returns "-1" which I condition upon to do a > sqlite3BtreeNext which then causes a correct lookup. > > Is there some other aspect such as for example "incKey" that causes btree > lookups to function differently? I desperately need help here. > > Regards, > > Werner. > > > > > > > > > > > -- View this message in context: http://www.nabble.com/sqlite3BtreeMoveto-broke-between-3.3.12-%3E3.3.14-15-tf3547873.html#a9904386 Sent from the SQLite mailing list archive at Nabble.com. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
