Hi,
I'm trying to prepare a statement for "SELECT a FROM t ORDER BY ?" and
then sqlite3_bind_int the parameter to 1 (on sqlite 3.19.3).
Expected result: Orders result by column "a", in ascending order.
Observed result: Orders in some strange order.
I also tried sqlite3_bind_int64, didn't change the result.
Should this use case work?
To reproduce:
OK case (prints 2 and then 5):
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
int main() {
sqlite3* db;
sqlite3_stmt* stmt;
printf("%s\n", sqlite3_libversion());
if (sqlite3_open(":memory:", &db) != SQLITE_OK ||
sqlite3_exec(db, "CREATE TABLE t(a int);"
"INSERT INTO t(a) VALUES (5);"
"INSERT INTO t(a) VALUES (2);", NULL, 0, NULL) !=
SQLITE_OK ||
sqlite3_prepare_v2(db, "SELECT a FROM t ORDER BY 1", -1, &stmt, 0)
!= SQLITE_OK ||
sqlite3_step(stmt) != SQLITE_ROW)
abort();
printf("%d\n", sqlite3_column_int(stmt, 0));
if (sqlite3_step(stmt) != SQLITE_ROW)
abort();
printf("%d\n", sqlite3_column_int(stmt, 0));
return 0;
}
Not OK case (prints 5 and then 2):
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
int main() {
sqlite3* db;
sqlite3_stmt* stmt;
printf("%s\n", sqlite3_libversion());
if (sqlite3_open(":memory:", &db) != SQLITE_OK ||
sqlite3_exec(db, "CREATE TABLE t(a int);"
"INSERT INTO t(a) VALUES (5);"
"INSERT INTO t(a) VALUES (2);", NULL, 0, NULL) !=
SQLITE_OK ||
sqlite3_prepare_v2(db, "SELECT a FROM t ORDER BY ?", -1, &stmt, 0)
!= SQLITE_OK ||
sqlite3_bind_int(stmt, 1, 1) != SQLITE_OK ||
sqlite3_step(stmt) != SQLITE_ROW)
abort();
printf("%d\n", sqlite3_column_int(stmt, 0));
if (sqlite3_step(stmt) != SQLITE_ROW)
abort();
printf("%d\n", sqlite3_column_int(stmt, 0));
return 0;
}
Also OK but not that useful:
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
int main() {
sqlite3* db;
sqlite3_stmt* stmt;
printf("%s\n", sqlite3_libversion());
if (sqlite3_open(":memory:", &db) != SQLITE_OK ||
sqlite3_exec(db, "CREATE TABLE t(a int);"
"INSERT INTO t(a) VALUES (5);"
"INSERT INTO t(a) VALUES (2);", NULL, 0, NULL) !=
SQLITE_OK ||
sqlite3_prepare_v2(db, "SELECT a FROM t ORDER BY 1, ?", -1, &stmt,
0) != SQLITE_OK ||
sqlite3_bind_int(stmt, 1, 1) != SQLITE_OK ||
sqlite3_step(stmt) != SQLITE_ROW)
abort();
printf("%d\n", sqlite3_column_int(stmt, 0));
if (sqlite3_step(stmt) != SQLITE_ROW)
abort();
printf("%d\n", sqlite3_column_int(stmt, 0));
return 0;
}
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
