Good morning
While answering question on stackoverflow
<http://stackoverflow.com/a/42197036/6336479>I have noticed a bug related
to UPDATE using correlated sub-query.
The demonstration code can be found in the attached file bug_report.sql and
the results of the demonstration with some comments added are in
bug_reports.txt.
I'm using SQLite 3.9.2 on windows 64 bit.
Thanks
Dudu Markovitz
.mode column
.header on
.echo on
select sqlite_version()
;
create table mytable (id int,group_id int,IsHeader int,active int)
;
insert into mytable (id,group_id,IsHeader,active) values
(1,100,1,1)
,(2,100,0,0)
,(3,100,0,0)
,(4,100,0,1)
,(5,100,0,1)
;
select * from mytable
;
update mytable
set Active = (select 1-h.Active
from mytable as h
where h.IsHeader = 1
-- and h.group_id = mytable.group_id
)
;
select * from mytable
;
update mytable
set Active = (select 1-h.Active
from mytable as h
where h.IsHeader = 1
-- and h.group_id = mytable.group_id
)
;
select * from mytable
;
update mytable
set Active = (select 1-h.Active
from mytable as h
where h.IsHeader = 1
-- and h.group_id = mytable.group_id
)
;
select * from mytable
;
update mytable
set Active = (select 1-h.Active
from mytable as h
where h.IsHeader = 1
and h.group_id = mytable.group_id
)
;
select * from mytable
;
update mytable
set Active = (select 1-h.Active
from mytable as h
where h.IsHeader = 1
and h.group_id = mytable.group_id
)
;
select * from mytable
;
update mytable
set Active = (select 1-h.Active
from mytable as h
where h.IsHeader = 1
and h.group_id = mytable.group_id
)
;
select * from mytable
;
select sqlite_version()
;
sqlite_version()
----------------
3.9.2
create table mytable (id int,group_id int,IsHeader int,active int)
;
insert into mytable (id,group_id,IsHeader,active) values
(1,100,1,1)
,(2,100,0,0)
,(3,100,0,0)
,(4,100,0,1)
,(5,100,0,1)
;
select * from mytable
;
id group_id IsHeader active
---------- ---------- ---------- ----------
1 100 1 1
2 100 0 0
3 100 0 0
4 100 0 1
5 100 0 1
/**************************************************************************************
* Update without correlation (note the commented condition) works as expected.
* All rows, including the header, get the same value.
* If header active=1 then all rows become active=0.
* If header active=0 then all rows become active=1.
***************************************************************************************/
update mytable
set Active = (select 1-h.Active
from mytable as h
where h.IsHeader = 1
-- and h.group_id = mytable.group_id
)
;
select * from mytable
;
id group_id IsHeader active
---------- ---------- ---------- ----------
1 100 1 0
2 100 0 0
3 100 0 0
4 100 0 0
5 100 0 0
update mytable
set Active = (select 1-h.Active
from mytable as h
where h.IsHeader = 1
-- and h.group_id = mytable.group_id
)
;
select * from mytable
;
id group_id IsHeader active
---------- ---------- ---------- ----------
1 100 1 1
2 100 0 1
3 100 0 1
4 100 0 1
5 100 0 1
update mytable
set Active = (select 1-h.Active
from mytable as h
where h.IsHeader = 1
-- and h.group_id = mytable.group_id
)
;
select * from mytable
;
id group_id IsHeader active
---------- ---------- ---------- ----------
1 100 1 0
2 100 0 0
3 100 0 0
4 100 0 0
5 100 0 0
/**************************************************************************************
* Update with correlation seems to have a bug.
* The header get diffrent value from the rest of the rows.
* Please note that since all the rows in this demo have the same group_id,
* nothing had logically changed.
***************************************************************************************/
update mytable
set Active = (select 1-h.Active
from mytable as h
where h.IsHeader = 1
and h.group_id = mytable.group_id
)
;
select * from mytable
;
id group_id IsHeader active
---------- ---------- ---------- ----------
1 100 1 1
2 100 0 0
3 100 0 0
4 100 0 0
5 100 0 0
update mytable
set Active = (select 1-h.Active
from mytable as h
where h.IsHeader = 1
and h.group_id = mytable.group_id
)
;
select * from mytable
;
id group_id IsHeader active
---------- ---------- ---------- ----------
1 100 1 0
2 100 0 1
3 100 0 1
4 100 0 1
5 100 0 1
update mytable
set Active = (select 1-h.Active
from mytable as h
where h.IsHeader = 1
and h.group_id = mytable.group_id
)
;
select * from mytable
;
id group_id IsHeader active
---------- ---------- ---------- ----------
1 100 1 1
2 100 0 0
3 100 0 0
4 100 0 0
5 100 0 0
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
