This orders the results by the constant expression bound to the parameter, which *may* mean that they are returned in the order that arises from the selected query plan *if* the sort is "stable" (i.e. records that collate the same are returned in input order).
Binding a number here is NOT equivalent to referencing columns from the expression list by number. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Danny Milosavljevic Gesendet: Montag, 29. Jänner 2018 20:14 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] "ORDER BY ?" sqlite3_bind_int OK, but does weird things 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
