Le 20.10.2010 12:32, Mathieu Schroeter a écrit :
I've made a small program with this output (please, look at
the attached C code _first_):
Mmmh.. sorry but the previous attached code has at least one error
and some parts commented. And now I see that without EXPLAIN QUERY PLAN,
the comparison callback is called with the second query.
There is way in order to have the second query using the index with
ORDER BY? It is strange that it is not the case..
Thanks,
Mathieu SCHROETER
#include <stdio.h>
#include <string.h>
#include "sqlite3.h"
static int
cmp (void *data,
int len1, const void *d1,
int len2, const void *d2)
{
printf (".");
return -memcmp (d1, d2, 2);
}
int
main (void)
{
sqlite3 *db;
sqlite3_stmt *stmt;
char *err = NULL;
unlink ("./tmp.db");
sqlite3_open ("./tmp.db", &db);
sqlite3_create_collation (db, "foobar", SQLITE_UTF8, NULL, cmp);
sqlite3_exec (db, "CREATE TABLE tmp (id INTEGER PRIMARY KEY AUTOINCREMENT,
data BLOB);"
"CREATE TABLE lol (id INTEGER);"
"CREATE INDEX idx_foobar ON tmp (data COLLATE foobar);",
NULL, NULL, &err);
printf ("insert into tmp\n");
sqlite3_exec (db, "INSERT INTO tmp (data) values (CAST(x'0100' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0400' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0050' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'1800' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0090' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0105' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0000' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0001' AS TEXT));",
NULL, NULL, &err);
printf ("\ninsert into lol\n");
sqlite3_exec (db, "INSERT INTO lol (id) values (1);"
"INSERT INTO lol (id) values (2);"
"INSERT INTO lol (id) values (3);"
"INSERT INTO lol (id) values (4);"
"INSERT INTO lol (id) values (5);"
"INSERT INTO lol (id) values (6);"
"INSERT INTO lol (id) values (7);"
"INSERT INTO lol (id) values (8);",
NULL, NULL, &err);
if (err)
{
fprintf (stderr, "%s\n", err);
sqlite3_free (err);
goto out;
}
sqlite3_prepare_v2 (db, "EXPLAIN QUERY PLAN "
"SELECT data "
"FROM ("
"SELECT id AS id0 "
"FROM tmp "
") INNER JOIN tmp "
"ON id0 = tmp.id "
"ORDER BY data COLLATE foobar;",
-1, &stmt, NULL);
printf ("step for first query\n");
while (sqlite3_step (stmt) == SQLITE_ROW)
printf ("%10s | %10s : %s\n",
sqlite3_column_text (stmt, 0),
sqlite3_column_text (stmt, 1),
sqlite3_column_text (stmt, 2));
sqlite3_finalize (stmt);
printf ("\nstep for second query\n");
sqlite3_prepare_v2 (db, "EXPLAIN QUERY PLAN "
"SELECT data "
"FROM ("
"SELECT id AS id0 "
"FROM lol "
") INNER JOIN tmp "
"ON id0 = tmp.id "
"ORDER BY data COLLATE foobar;",
-1, &stmt, NULL);
while (sqlite3_step (stmt) == SQLITE_ROW)
printf ("%10s | %10s : %s\n",
sqlite3_column_text (stmt, 0),
sqlite3_column_text (stmt, 1),
sqlite3_column_text (stmt, 2));
sqlite3_finalize (stmt);
sqlite3_close (db);
out:
unlink ("./tmp.db");
return 0;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
