Given the following example table and data:
create table x(id integer);
with recursive
src(id)
as
(select
1
union all select
id + 1
from
src
where
id < 5)
insert into
x(id)
select
id
from
src
;
My goal is to select each row from table x, and the sum total, in the
same query. The following returns the results correctly as expected:
with
x_all
as
(select
id
from
x),
x_sum
as
(select
sum(id)
from
x_all)
select
*
from
x_all
union all select
*
from
x_sum
;
/*
id
----------
1
2
3
4
5
15
*/
However when doing an explain query plan I see that the source "x"
table is scanned twice where I would have expected it to only be
scanned once:
selectid,order,from,detail
1,0,0,"SCAN TABLE x"
2,0,0,"SCAN TABLE x"
0,0,0,"COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)"
Is this expected? For my real query the x_all data is relatively
complicated with lots of joins, and I was wondering if it is absolutely
necessary for SQLite to be doing double the amount of work it needs to?
Mark.
--
Mark Lawrence
