Hi again,
On my 3 level of with example, my systems seems to "hang" if :
- I put a "distinct" keyword in goods definition,
- or if I replace "union all" per a simple "union" .
** failure mode 1 (no union all in the neighbors) **
with digits(z, lp) AS (
select '1' as z, 1 as lp
UNION ALL SELECT
CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
y(r) as (WITH RECURSIVE col(c) AS (
select 81 as c
union all
select c-1 from col where c>1
) select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union select r, (((r-1)/3) % 3) * 3
+ ((r-1)/27) * 27 + lp
+ ((lp-1) / 3) * 6 from y, digits)
, goods (c,n) as (select * from neighbors where r <>n)
,input(sud) AS (
VALUES(
'1....7.9..3..2...8..96..5....53..9...1..8...26....4...3......1..4......7..7...3..'
)
),
/* The tricky bit. */
x(s, ind) AS (
SELECT sud, instr(sud, '.') FROM input
UNION ALL
SELECT
substr(s, 1, ind-1) || z || substr(s, ind+1),
instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
FROM x, digits AS z
WHERE ind>0
AND NOT EXISTS (
SELECT 1 FROM goods AS lp
WHERE ind=lp.c and z.z = substr(s, n, 1)
)
)
SELECT * FROM x WHERE ind=0;
** failure mode 2 (distinct in the goods definition) **
with digits(z, lp) AS (
select '1' as z, 1 as lp
UNION ALL SELECT
CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
y(r) as (WITH RECURSIVE col(c) AS (
select 81 as c
union all
select c-1 from col where c>1
) select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union all select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union all select r, (((r-1)/3) % 3) * 3
+ ((r-1)/27) * 27 + lp
+ ((lp-1) / 3) * 6 from y, digits)
, goods (c,n) as (select distinct * from neighbors where r <>n)
,input(sud) AS (
VALUES(
'1....7.9..3..2...8..96..5....53..9...1..8...26....4...3......1..4......7..7...3..'
)
),
/* The tricky bit. */
x(s, ind) AS (
SELECT sud, instr(sud, '.') FROM input
UNION ALL
SELECT
substr(s, 1, ind-1) || z || substr(s, ind+1),
instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
FROM x, digits AS z
WHERE ind>0
AND NOT EXISTS (
SELECT 1 FROM goods AS lp
WHERE ind=lp.c and z.z = substr(s, n, 1)
)
)
SELECT * FROM x WHERE ind=0;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
