[sqlite] union, order by, and a phantom record

[Poor Yorick]

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

-- 
Poor Yorick