Can you post the output of this command when you compile sqlite 3.4.2 with code warrior for your test.sqlite database?
explain SELECT a FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101; With the sqlite 3.5.1 shell compiled with gcc 4.1.1 I see: 0|Goto|0|25| 1|Integer|0|0|# One 2|OpenRead|0|2| 3|SetNumColumns|0|4| 4|Integer|0|0|# idx_One 5|OpenRead|1|4|keyinfo(3,BINARY,BINARY) 6|Integer|99|0| 7|IsNull|-1|22| 8|Integer|100|0| 9|IsNull|-2|22| 10|Integer|101|0| 11|IsNull|-3|22| 12|MakeRecord|3|0|ddd 13|MemStore|0|0| 14|MoveGe|1|22| 15|MemLoad|0|0| 16|IdxGE|1|22|+ 17|IdxRowid|1|0| 18|MoveGe|0|0| 19|Column|0|0|# One.a 20|Callback|1|0| 21|Next|1|15| 22|Close|0|0| 23|Close|1|0| 24|Halt|0|0| 25|Transaction|0|0| 26|VerifyCookie|0|2| 27|TableLock|0|2|One 28|Goto|0|1| 29|Noop|0|0| Just for the heck of it, can you also provide the code warrior/3.4.2 output for these commands as well? -- select case 2 -- Getting all columns works explain SELECT * FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101; -- select case 3 -- Not using whole index works explain SELECT a FROM One WHERE b2 = 100 and b3 = 101; -- select case 4 -- Getting one column, in the index, works explain SELECT b1 FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101; --- Marco Bambini <[EMAIL PROTECTED]> wrote: > To be really sure I rewrote the example in C linked to the official > sqlite 3.4.2. > Here it is my source code: > > #include <stdio.h> > #include <stdlib.h> > #include <string.h> > #include "sqlite3.h" > > int main(void) > { > sqlite3 *db = NULL; > int rc = SQLITE_OK; > char sql[256]; > char **result; > int i, nrow, ncol; > > // open db > rc = sqlite3_open("test.sqlite", &db); > if (rc != SQLITE_OK) goto abort; > > // create table > rc = sqlite3_exec(db, "CREATE TABLE One (a varchar primary key, b1 > integer, b2 integer, b3 integer, z varchar);", NULL, 0, NULL); > if (rc != SQLITE_OK) goto abort; > > // create index > rc = sqlite3_exec(db, "CREATE UNIQUE INDEX idx_One ON One(b1, b2, > b3);", NULL, 0, NULL); > if (rc != SQLITE_OK) goto abort; > > // insert loop > for (i=1; i<=100; i++) > { > snprintf(sql, 256, "INSERT INTO One VALUES ('%d', %d, %d, %d, > 'A');", i, i+1, i+2, i+3); > rc = sqlite3_exec(db, sql, NULL, 0, NULL); > if (rc != SQLITE_OK) goto abort; > } > > // query test 1 > rc = sqlite3_get_table(db, "SELECT a FROM One WHERE b1 = 99 AND b2 = > 100 and b3 = 101;", &result, &nrow, &ncol, NULL); > if (rc != SQLITE_OK) goto abort; > > for(i=0; i<ncol; ++i) > { > printf(result[i]); > printf("\t\t"); > } > printf("\n"); > > for(i=0; i<ncol*nrow; ++i) > { > printf(result[ncol+i]); > printf("\t\t"); > if (i % ncol == 0) printf("\n"); > } > > // free table > sqlite3_free_table(result); > > // close db > sqlite3_close(db); > > printf("simple test finished!\n"); > return 0; > > abort: > printf("%s\n", sqlite3_errmsg(db)); > if (db != NULL) sqlite3_close(db); > return -1; > } > > On Windows (not on Mac!) it returns 99 instead of the correct 98 value. > Anyone can confirm that on Windows? > > Thanks a lot. > --- > Marco Bambini > http://www.sqlabs.net > http://www.sqlabs.net/blog/ > http://www.sqlabs.net/realsqlserver/ > > > > On Oct 30, 2007, at 5:15 PM, [EMAIL PROTECTED] wrote: > > > Marco Bambini <[EMAIL PROTECTED]> wrote: > >> > >> I am experiencing a very strange issue in sqlite 3.4.2 (only with the > >> Win32 version, OSX and linux works fine). > >> > >> I wonder if there was a bug in the 3.4.2 version that I should fix... > >> Please note that I cannot upgrade to the latest 3.5.x versions... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------