Dear Richard, the rather complicated script below used to print a tree:
node1|node2|node3|node4|node5
1||||
1|11|||
1|12|||
1|12|121||
1|13|||
and with yesterday's pre-release snapshot it does not yield any result.
A clue is this depends on the size of the query, not so much on the
logic.
If you reduce the number of anticipated levels, by leaving out the
last four lines, the output is alright.
Hope this causes no headache, E. Pasma
.version
SQLite 3.23.0 2018-03-22 12:00:43
dd568c27b1d7656388ea5b4132cc0265aedd7348d265d8e8c7412b00b28a31aa
zlib version 1.2.3
gcc-4.0.1 (Apple Inc. build 5465)
create table node (node integer primary key, parent integer)
;
insert into node values (1,0),(11,1),(12,1),(13,1),(121,12)
;
create index node_parent on node (parent)
;
create table bit (bit integer primary key)
;
insert into bit values(0),(1)
;
SELECT node1, node2, node3, node4, node5
/* level 1 is root */
FROM (SELECT node AS node1 FROM node WHERE parent=0)
/* level 2 */
JOIN (SELECT bit AS bit1 FROM bit)
LEFT JOIN (SELECT node AS node2, parent AS parent2 FROM node)
ON bit1 AND parent2=node1
/* level 3 */
JOIN (SELECT bit AS bit2 FROM bit)
ON bit2<=CASE WHEN bit1 THEN CASE WHEN node2 THEN 1 ELSE -1 END
ELSE 0 END
LEFT JOIN (SELECT node AS node3, parent AS parent3 FROM node)
ON bit2 AND parent3=node2
/* level 4 */
JOIN (SELECT bit AS bit3 FROM bit)
ON bit3<=CASE WHEN bit2 THEN CASE WHEN node3 THEN 1 ELSE -1 END
ELSE 0 END
LEFT JOIN (SELECT node AS node4, parent AS parent4 FROM node)
ON bit3 AND parent4=node3
/* level 5 */
JOIN (SELECT bit AS bit4 FROM bit)
ON bit4<=CASE WHEN bit3 THEN CASE WHEN node4 THEN 1 ELSE -1 END
ELSE 0 END
LEFT JOIN (SELECT node AS node5, parent AS parent5 FROM node)
ON bit4 AND parent5=node4
;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
