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]
-----------------------------------------------------------------------------
