"ORDER BY ?" sqlite3_bind_int OK, but does weird things

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

"ORDER BY ?" sqlite3_bind_int OK, but does weird things

Danny Milosavljevic
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: "ORDER BY ?" sqlite3_bind_int OK, but does weird things

Clemens Ladisch
Danny Milosavljevic wrote:
> 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.

Ordering by column number works only for a plain integer.  A parameter
might not be an integer, so it is parsed differently, even if the
actual value later ends up being an integer.

So the effect is that you are sorting by a constant value: it's the
same for all rows, so the order of the output rows is undefined.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: "ORDER BY ?" sqlite3_bind_int OK, but does weird things

Jens Alfke-2
In reply to this post by Danny Milosavljevic


> On Jan 29, 2018, at 11:13 AM, Danny Milosavljevic <[hidden email]> wrote:
>
> Should this use case work?

Nope. The ORDER BY clause can affect the query plan and the generated bytecode, so it's not something you can change in a binding. You have to compile a new statement with a different ORDER BY clause.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: "ORDER BY ?" sqlite3_bind_int OK, but does weird things

Keith Medcalf
In reply to this post by Danny Milosavljevic

No.  Column name and table referents (identifiers) must be specified explicitly (as part of the command) and MUST NOT be bound parameters.  You are asking to sort by the value 1 for all rows, which means that the output is in "visitation order" since the ORDER BY value is the same for all rows ...

That is to say that you cannot use a bound parameter (? for example) it the locations that have a ? in the following query, and have that bound parameter refer to an index name, column name, or table name (or any defined identifier whatsoever) -- it will only refer to the "value" given the bound parameter or result in a syntax error if a "value" is not permitted at that part of the syntax:

SELECT ? from ? INDEXED BY ? WHERE ? = 17 GROUP BY ? ORDER BY ?;

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Danny Milosavljevic
>Sent: Monday, 29 January, 2018 12:14
>To: [hidden email]
>Subject: [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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] "ORDER BY ?" sqlite3_bind_int OK, but does weird things

Hick Gunter
In reply to this post by Danny Milosavljevic
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:[hidden email]] Im Auftrag von Danny Milosavljevic
Gesendet: Montag, 29. Jänner 2018 20:14
An: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users