Definitely a bug: I distilled the OP's code into an easy repeatable test
case -
-------------------------------------------------------
create table t(id integer primary key autoincrement, a, b, c);
insert into t values
(3,1 ,'name' ,'Imogen')
,(5,1 ,'gender' ,'female')
,(6,1 ,'son' ,'Guiderius')
,(7,1 ,'son' ,'Arvirargus');
select distinct t.*
from t
join t as t1 on t.a=t1.a and t1.a = 1
where t.b = 'name'
union
select distinct t.*
from t
join t as t1 on t.a=t1.a and t1.a = 1
where t.b = 'gender'
order by t.id
-- Expected
-- id | a | b | c
-- --- | --- | -------- | --------
-- 3 | 1 | name | Imogen
-- 5 | 1 | gender | female
-- Returned
-- id | a | b | c
-- --- | --- | -------- | --------
-- 3 | 1 | name | Imogen
-- 5 | 1 | gender | female
-- 5 | 1 | 1 | female
drop table t;
-----------------------------------------------------------
If you change almost anything, it works. Remove the "t.a=t1.a and" from
the first select, and it works.
Remove the order clause and it works.
Cheers,
Ryan
On 2016/02/08 11:12 PM, Poor Yorick wrote:
> The following query produces a third phantom record on my system:
>
>
> ===== start script =====
> package require sqlite3
>
> sqlite3 [namespace current]::db :memory:
>
> db eval {
> create table if not exists eav (
> id integer primary key autoincrement
> ,entity numeric
> ,attribute text
> ,value
> )
> ; insert into eav values
> (3 ,1 ,'name' ,'Imogen')
> ,(5 ,1 ,'gender' ,'female')
> ,(6,1 ,'son' ,'Guiderius')
> ,(7,1 ,'son' ,'Arvirargus')
> }
>
> set pattern0 1
> set report2 name
> set report3 gender
>
> puts [db eval {
> select distinct eav.* from eav
> join eav as eav0 on eav.entity == eav0.entity
> and eav0.entity == :pattern0 where eav.attribute == :report2
> union
> select distinct eav.* from eav
> join eav as eav0 on eav.entity == eav0.entity
> and eav0.entity == :pattern0 where eav.attribute == :report3
> order by eav.id
> }]
> ===== end script =====
>
> The result is:
>
> 3 1 name Imogen 5 1 gender female 5 1 1 female
>
