Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

2019-03-17 Thread Keith Medcalf
On Sunday, 17 March, 2019 11:19, niklas wrote: >I agree that correlated subqueries in general seem more natural and >are probably also less likely to have the performance pessimizations >noticed with joins. >But I might also want to use the column, or in case of a correlated >subquery, the colu

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

2019-03-17 Thread Simon Slavin
On 17 Mar 2019, at 5:19pm, niklas wrote: > Is this a recent change in Sqlite or have I misunderstood something? The > Sqlite documentation still does not seem to say that column aliases can be > used in the WHERE clause at least. You are correct in two things. Column aliases cannot be relied

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

2019-03-17 Thread niklas
I agree that correlated subqueries in general seem more natural and are probably also less likely to have the performance pessimizations noticed with joins. But I might also want to use the column, or in case of a correlated subquery, the column alias, in the WHERE clause and previously that has n

Re: [sqlite] Rowid alias and description of query result: suspected tiny bug

2019-03-17 Thread Keith Medcalf
On Sunday, 17 March, 2019 15:00, Yannick Duchêne wrote: >Aside, what also surprised me just a moment ago and I never >noticed before, is that although I can refer to ROWID (not aliased) >anywhere in a query, it seems to not work properly in `using(rowid)`, if >the ROWID is automatically generat

Re: [sqlite] Rowid alias and description of query result: suspected tiny bug

2019-03-17 Thread Yannick Duchêne
On Sun, 17 Mar 2019 14:27:19 -0600 "Keith Medcalf" wrote: > > specifies the name you want to appear for the first column. The other names > for the other four columns are free to be whatever they want ... that is, > without an AS clause, the column names are not guaranteed to be anything > o

Re: [sqlite] Rowid alias and description of query result: suspected tiny bug

2019-03-17 Thread Keith Medcalf
On Sunday, 17 March, 2019 12:30, Yannick Duchêne wrote: >Hi people, it’s a long time I did not get there. >I’m currently to create and use an SQLite DB with the Python biding >APSW. For each row returned, I always retrieve the description with >`Cursor.getdescription()`. Surprisingly, during a

[sqlite] Rowid alias and description of query result: suspected tiny bug

2019-03-17 Thread Yannick Duchêne
Hi people, it’s a long time I did not get there. I’m currently to create and use an SQLite DB with the Python biding APSW. For each row returned, I always retrieve the description with `Cursor.getdescription()`. Surprisingly, during a test, I noticed the name of a column as returned in the desc