[sqlite] union, order by, and a phantom record
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
>
[sqlite] union, order by, and a phantom record
On 2/8/16, Richard Hipp wrote: > On 2/8/16, Poor Yorick wrote: >> The following query produces a third phantom record on my system: > > Running "PRAGMA automatic_index=OFF;" might alleviate the symptoms > your are experiencing, until we can get a proper fix published. > The bug appears to be quite a bit more serious. A proposed fix has been checked into trunk. Please try out the trunk to see if that works better for you. We will continue to analyze the problem and write tests in the meantime. The latest "snapshot" at https://www.sqlite.org/download.html contains the proposed fix. -- D. Richard Hipp drh at sqlite.org
[sqlite] union, order by, and a phantom record
On 2016-02-08 19:15, Richard Hipp wrote: > On 2/8/16, Richard Hipp wrote: >> On 2/8/16, Poor Yorick wrote: >>> The following query produces a third phantom record on my system: >> >> Running "PRAGMA automatic_index=OFF;" might alleviate the symptoms >> your are experiencing, until we can get a proper fix published. >> > > The bug appears to be quite a bit more serious. A proposed fix has > been checked into trunk. Please try out the trunk to see if that > works better for you. We will continue to analyze the problem and > write tests in the meantime. > > The latest "snapshot" at https://www.sqlite.org/download.html contains > the proposed fix. My working code has already morphed into something that isn't triggering the bug, but it's gratifying to see a proposed fix so quickly, as I'm likely to hit it again during this project. -- Yorick
[sqlite] union, order by, and a phantom record
On 2/8/16, Poor Yorick wrote:
> The following query produces a third phantom record on my system:
Running "PRAGMA automatic_index=OFF;" might alleviate the symptoms
your are experiencing, until we can get a proper fix published.
>
>
> = 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
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
drh at sqlite.org
[sqlite] union, order by, and a phantom record
On 2/8/16, R Smith wrote: > Definitely a bug: I distilled the OP's code into an easy repeatable test > case - https://www.sqlite.org/src/tktview/d06a25c84454a372be4e4c970c3c4d4363197219 -- D. Richard Hipp drh at sqlite.org
[sqlite] union, order by, and a phantom record
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
