Re: [sqlite] Possible bug with union and join.

On 12/22/16, David Raymond wrote: > Problem appears to be coming from an automatic index. Thanks for the insight, David. Automatic indexes do appear to be a factor, but not the only factor. The script below shows different answers depending on whether the VIEW is

Re: [sqlite] Possible bug with union and join.

Problem appears to be coming from an automatic index. sqlite> select * from data left join id_map using (id); --EQP-- 2,0,0,SCAN TABLE map_integer --EQP-- 3,0,0,SCAN TABLE map_text --EQP-- 1,0,0,COMPOUND SUBQUERIES 2 AND 3 (UNION ALL) --EQP-- 0,0,0,SCAN TABLE data --EQP-- 0,1,1,SEARCH SUBQUERY 1

Re: [sqlite] How does indexes on exprressions work on multi-column indexes?

On Thu, Dec 22, 2016 at 1:21 PM, Jens Alfke wrote: > > > On Dec 21, 2016, at 7:47 PM, Gelin Yan wrote: > > > > the json_extract part didn't work. > > > > Query plan indicated "search table t_expr using index pid>? and pid > That’s going to happen with

[sqlite] Possible bug with union and join.

Hi everyone. When I was working with sqlite3 I've found weird behavior of JOIN clause when I was trying to merge table with union of tables. Let me explain this using database with reproduced problem. Database dump: CREATE TABLE map_integer (id INTEGER PRIMARY KEY, name TEXT); INSERT INTO

[sqlite] Possible bug with union and join.

Hi everyone. When I was working with sqlite3 I've found weird behavior of JOIN clause when I was trying to merge table with union of tables. Let me explain this using database with reproduced problem. Database dump: CREATE TABLE map_integer (id INTEGER PRIMARY KEY, name TEXT); INSERT INTO