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

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

Re: [sqlite] Groups in C API

Hello, On 2018-01-29 18:08, Stephan Buchert wrote: But then I have a related question: to get my hands on each row in SELECTs with GROUP BY I could write an aggregate extension function. How do I see there, when a group ends and a new one starts? I.e. How do I implement the xStep and xFinal C

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

> On Jan 29, 2018, at 11:13 AM, Danny Milosavljevic > 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

[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

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

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

[sqlite] 3.22 'planner omits unused left joins'

SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE t1 (a INTEGER PRIMARY KEY); sqlite> CREATE TABLE t2 (a INTEGER PRIMARY KEY); sqlite> CREATE TABLE t3

Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins'

The "unused left join" is not "unused". Use "explain" instead of "explain query plan" and you will see that the left join is performed in both cases. The difference is that t2 has an INTEGER PRIMARY KEY and a lookup there is apparently not printed out in the query plan. Table t3 has an explicit

Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins'

Are you sure you’re using 3.22 Gunter? I can’t see any trace of t2 in the first explain (with query plan omitted). From: Hick Gunter Sent: 29 January 2018 12:24 To: 'SQLite mailing list' Subject: Re: [sqlite] [EXTERNAL] 3.22

Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins'

Sorry, meant to post this SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE t1 (a INTEGER PRIMARY KEY); sqlite> CREATE TABLE t2 (a INTEGER PRIMARY

Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins'

I am still stuck with 3.7.14.1 here, so maybe the QP is different WRT joins that are not required to satisfy the select field list. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von x Gesendet: Montag, 29. Jänner 2018 14:23

Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins'

SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE t1 (a INTEGER PRIMARY KEY); sqlite> CREATE TABLE t2 (a INTEGER PRIMARY KEY); sqlite> CREATE TABLE t3

Re: [sqlite] Groups in C API

On 29 Jan 2018, at 3:19pm, Stephan Buchert wrote: > is there a way to know when a group ends and the next starts? No. Not even SQLite knows this. Sorry. You have to monitor the group column in your own software. Simon.

[sqlite] sqlite3 3220000 does not build with -DSQLITE_OMIT_AUTHORIZATION

Hi all, last sqlite 322 does not build with -DSQLITE_OMIT_AUTHORIZATION because shell.c in sqlite3_expert_new use it. I suggest the next patch to solve the problem. Regards diff --git a/shell.c b/shell.c --- a/shell.c +++ b/shell.c @@ -7702,7 +7702,9 @@ sqlite3expert

[sqlite] Website security issue

Hello, How can i report a security issue on the website please ? Sincerely -- Cordialement, Georges Taupin Consultant en sécurité informatique Rennes g.taupi...@gmail.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Website security issue

On 1/29/18, Georges Taupin wrote: > > How can i report a security issue on the website please ? > Send an email - either to the public mailing list, or directly to me. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing

Re: [sqlite] [EXTERNAL] Groups in C API

What do you mean with groups ending and starting? The GROUP BY clause has the effect of returning one record per group (i.e. distinct tuple of the group expression), with the accumulated values belonging to that group. Each result record is therefore in a separate group. SQLite will (except

[sqlite] Groups in C API

When processing SELECT ... statements having a GROUP BY clause, i.e. SELECT ... GROUP BY ...; in C, i.e. with a loop like rc=sqlite3_step(stmt); while rc==SQLITE_ROW { ... rc=sqlite3_step(stmt); } is there a way to know when a group ends and the next starts? I have this of course if the

[sqlite] Groups in C API

Ok, I should have tested this before asking. I had assumed that "If the SELECT statement is *a non-aggregate query*, then each expression in the result expression list is evaluated for each row in the dataset filtered by the WHERE clause" on https://www.sqlite.org/lang_select.html#resultset

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

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

Re: [sqlite] [EXTERNAL] Groups in C API

SQLite is special WRT to non-aggregate columns in aggregate queries. Most other engines will not allow columns that are neither group fields nor aggregated fields. SQLite OTOH does, and it promises that these fields are "related" to the group tuple. Consider: CREATE TABLE letters (type