I have a dataset of about 300 rows which have parent-child relations.
Due to factors unrelated to the issue I build the rows by zipping JSON
arrays with values from each column.
Then I run a simplest recursive query on it to get the whole tree(ends
up being ~4 levels).
Problem: the query takes 3000 ms (3 seconds) on my machine to complete.
If I create a real table with the SAME structure, insert the SAME data
into it, and run the SAME query, get the SAME result back, it takes
10-15 ms (200-300 TIMES faster).
I attached both queries(don't require schema to run)
/*query1*/
WITH
------------------------build a in memory table with parent-child relations
from 3 json arrays _which have the same size_
"objects_in_memory" AS (
SELECT "id"."value" AS "id",
"parent"."value" AS "parent",
"code"."value" AS "code"
FROM
JSON_EACH('[-9007199249913308,-9007199249913307,-9007199249913305,-9007199249913294,-9007199249912401,-9007199249912399,-9007199249912355,-9007199249912354,-9007199249910861,-9007199249910860,-9007199249906658,-9007199249804236,-9007199249804235,-9007199249804234,-9007199249804233,-9007199249804232,-9007199249804231,-9007199249804230,-9007199249804229,-9007199249804228,-9007199249804227,-9007199249804226,-9007199249804225,-9007199249804224,-9007199249804223,-9007199249804222,-9007199249804221,-9007199249804220,-9007199249804219,-9007199249804218,-9007199249804217,-9007199249804216,-9007199249804215,-9007199249804214,-9007199249804213,-9007199249804212,-9007199249804211,-9007199249804210,-9007199249804209,-9007199249804208,-9007199249804207,-9007199249804206,-9007199249804205,-9007199249804204,-9007199249804203,-9007199249804202,-9007199249804201,-9007199249804200,-9007199249804199,-9007199249804198,-9007199249804197,-9007199249804196,-9007199249804195,-9007199249804194,-9007199249804193,-9007199249804192,-9007199249804191,-9007199249804190,-9007199249804189,-9007199249804188,-9007199249804187,-9007199249804186,-9007199249804185,-9007199249804184,-9007199249804183,-9007199249804182,-9007199249804181,-9007199249804180,-9007199249804179,-9007199249804178,-9007199249804177,-9007199249804176,-9007199249804175,-9007199249804174,-9007199249804173,-9007199249804172,-9007199249804171,-9007199249804170,-9007199249804169,-9007199249804168,-9007199249804167,-9007199249804166,-9007199249804165,-9007199249804164,-9007199249804163,-9007199249804162,-9007199249804161,-9007199249804160,-9007199249804159,-9007199249804158,-9007199249804157,-9007199249804156,-9007199249804155,-9007199249804154,-9007199249804153,-9007199248223119,-9007199247893113,-9007199247893112,-9007199247893111,-9007199247893110,-9007199247893109,-9007199247893108,-9007199247893107,-9007199247893106,-9007199247893105,-9007199247893104,-9007199247893103,-9007199247893102,-9007199247893101,-9007199247893100,-9007199247893099,-9007199247893098,-9007199247893097,-9007199247893096,-9007199247893095,-9007199247893094,-9007199247893093,-9007199247893092,-9007199247893091,-9007199247893090,-9007199247893089,-9007199247893088,-9007199247893087,-9007199247893086,-9007199247893085,-9007199247893084,-9007199247893083,-9007199247893082,-9007199247893081,-9007199247893080,-9007199247893079,-9007199247893078,-9007199247893077,-9007199247893076,-9007199247893075,-9007199247893074,-9007199247893073,-9007199247893072,-9007199247893071,-9007199247893070,-9007199247893069,-9007199247893068,-9007199247893067,-9007199247893066,-9007199247893065,-9007199247893064,-9007199247893063,-9007199247893062,-9007199247893061,-9007199247893060,-9007199247893059,-9007199247893058,-9007199247893057,-9007199247893056,-9007199247893055,-9007199247893054,-9007199247893053,-9007199247893052,-9007199247893051,-9007199247893050,-9007199247893049,-9007199247893048,-9007199247893047,-9007199247893046,-9007199247893045,-9007199247893044,-9007199247893043,-9007199247893042,-9007199247893041,-9007199247893040,-9007199247893039,-9007199247893038,-9007199247893037,-9007199247893036,-9007199247893035,-9007199247893034,-9007199247893032,-9007199247893031,-9007199247893030,-9007199247893029,-9007199247893028,-9007199247893027,-9007199247893026,-9007199247893025,-9007199247893024,-9007199247893023,-9007199247893022,-9007199247893021,-9007199247893020,-9007199247893019,-9007199247893018,-9007199247893017,-9007199247893016,-9007199247893015,-9007199247893014,-9007199247893013,-9007199247893012,-9007199247893011,-9007199247893010,-9007199247893009,-9007199247893008,-9007199247893007,-9007199247893006,-9007199247893005,-9007199247893004,-9007199247893003,-9007199247893002,-9007199247893001,-9007199247893000,-9007199247892999,-9007199247892998,-9007199247892997,-9007199247892996,-9007199247892995,-9007199247892994,-9007199247892993,-9007199247892992,-9007199247892991,-9007199247892990,-9007199247892989,-9007199247892988,-9007199247892987,-9007199247892986,-9007199247892985,-9007199247892984,-9007199247892983,-9007199247892982,-9007199247892981,-9007199247892980,-9007199247892979,-9007199247892978,-9007199247892977,-9007199247892976,-9007199247892975,-9007199247892974,-9007199247892973,-9007199247892972,-9007199247892971,-9007199247892970,-9007199247892969,-9007199247892968,-9007199247892967,-9007199247892966,-9007199247892965,-9007199247892964,-9007199247892963,-9007199247892962,-9007199247892961,-9007199247892960,-9007199247892959,-9007199247892958,-9007199247892957,-9007199247892956,-9007199247892955,-9007199247892950,-9007199247892949,-9007199247892948,-9007199247892947,-9007199247892946,-9007199247892945,-9007199247892944,-9007199247892943,-9007199247892942,-9007199247892941,-9007199247892940,-9007199247892939,-9007199247892938,-9007199247892937,-9007199247892936,-9007199247892935,-9007199247892934,-9007199247892932,-9007199247892931,-9007199247892930,-9007199247892929,-9007199247892928,-9007199247892926,-9007199247892925,-9007199247892924,-9007199247892923,-9007199247892922,-9007199247892921,-9007199247892920,-9007199247892919,-9007199247892918,-9007199247892917,-9007199247892916,-9007199247892915,-9007199247892914,-9007199247892913,-9007199247892912,-9007199247892911,-9007199247892910,-9007199247892909,-9007199247892908,-9007199247892907,-9007199247892906,-9007199247892905,-9007199247892904,-9007199247892903,-9007199247892902,-9007199247892901,-9007199247892900,-9007199247892899,-9007199247892898,-9007199247892897,-9007199247892896,-9007199247872746,-9007199247756498,-9007199247199839,-9007199247188113,-9007199247183787,-9007199247167606,-9007199247073706,-9007199247044103,-9007199247044102]')
AS "id"
INNER JOIN
JSON_EACH('[-9007199249912399,-9007199249912399,-9007199249912399,-9007199249911428,-9007199249906658,-9007199249906658,-9007199249906658,-9007199249906658,-9007199249912401,-9007199249912401,null,
null,
-9007199249804236,-9007199249804236,-9007199249804236,-9007199249804233,-9007199249804232,-9007199249804231,-9007199249804232,-9007199249804229,-9007199249804232,-9007199249804227,-9007199249804232,-9007199249804225,-9007199249804225,-9007199249804233,-9007199249804222,-9007199249804221,-9007199249804222,-9007199249804219,-9007199249804222,-9007199249804222,-9007199249804216,-9007199249804216,-9007199249804236,-9007199249804213,-9007199249804212,-9007199249804211,-9007199249804212,-9007199249804209,-9007199249804212,-9007199249804207,-9007199249804212,-9007199249804205,-9007199249804212,-9007199249804203,-9007199249804212,-9007199249804201,-9007199249804212,-9007199249804199,-9007199249804212,-9007199249804197,-9007199249804212,-9007199249804195,-9007199249804212,-9007199249804193,-9007199249804212,-9007199249804191,-9007199249804213,-9007199249804189,-9007199249804188,-9007199249804189,-9007199249804186,-9007199249804189,-9007199249804184,-9007199249804189,-9007199249804182,-9007199249804189,-9007199249804180,-9007199249804189,-9007199249804178,-9007199249804189,-9007199249804176,-9007199249804189,-9007199249804174,-9007199249804189,-9007199249804172,-9007199249804189,-9007199249804170,-9007199249804189,-9007199249804168,-9007199249804213,-9007199249804166,-9007199249804166,-9007199249804213,-9007199249804163,-9007199249804162,-9007199249804163,-9007199249804160,-9007199249804213,-9007199249804213,-9007199249804213,-9007199249804236,-9007199249804155,-9007199249804236,-9007199247892934,-9007199249913307,-9007199247893113,-9007199247893113,-9007199247893111,-9007199247893113,-9007199247893109,-9007199247893113,-9007199249913307,-9007199247893106,-9007199247893106,-9007199247893104,-9007199247893106,-9007199247893102,-9007199247893106,-9007199249913307,-9007199247893099,-9007199247893099,-9007199247893097,-9007199247893099,-9007199247893095,-9007199247893099,-9007199249913307,-9007199247893092,-9007199247893092,-9007199247893090,-9007199247893092,-9007199247893088,-9007199247893092,-9007199249913307,-9007199247893085,-9007199247893085,-9007199247893083,-9007199247893085,-9007199247893081,-9007199247893085,-9007199249913307,-9007199247893078,-9007199247893078,-9007199247893076,-9007199247893078,-9007199247893074,-9007199247893078,-9007199249913307,-9007199247893071,-9007199247893071,-9007199247893069,-9007199247893071,-9007199247893067,-9007199247893071,-9007199249913307,-9007199247893064,-9007199247893064,-9007199247893062,-9007199247893064,-9007199247893060,-9007199247893064,-9007199249913307,-9007199247893057,-9007199247893057,-9007199247893055,-9007199247893057,-9007199247893053,-9007199247893057,-9007199249913307,-9007199247893050,-9007199247893050,-9007199247893048,-9007199247893050,-9007199247893050,-9007199249913307,-9007199247893044,-9007199247893043,-9007199249913307,-9007199247893041,-9007199247893041,-9007199247893039,-9007199247893041,-9007199247893037,-9007199247893041,-9007199249913307,-9007199249913308,-9007199247893032,-9007199247893032,-9007199247893030,-9007199247893032,-9007199247893028,-9007199247893032,-9007199249913308,-9007199247893025,-9007199247893025,-9007199247893023,-9007199247893025,-9007199247893021,-9007199247893025,-9007199249913308,-9007199247893018,-9007199247893018,-9007199247893016,-9007199247893018,-9007199247893014,-9007199247893018,-9007199249913308,-9007199247893011,-9007199247893011,-9007199247893009,-9007199247893011,-9007199247893007,-9007199247893011,-9007199249913308,-9007199247893004,-9007199247893004,-9007199247893002,-9007199247893004,-9007199247893000,-9007199247893004,-9007199249913308,-9007199247892997,-9007199247892997,-9007199247892995,-9007199247892997,-9007199247892993,-9007199247892997,-9007199249913308,-9007199247892990,-9007199247892990,-9007199247892988,-9007199247892990,-9007199247892986,-9007199247892990,-9007199249913308,-9007199247892983,-9007199247892983,-9007199247892981,-9007199247892983,-9007199247892979,-9007199247892983,-9007199249913308,-9007199247892976,-9007199247892976,-9007199247892974,-9007199247892976,-9007199247892972,-9007199247892976,-9007199249913308,-9007199247892969,-9007199247892968,-9007199247892969,-9007199249913308,-9007199247892965,-9007199247892964,-9007199249913308,-9007199247892962,-9007199247892962,-9007199247892960,-9007199247892962,-9007199247892958,-9007199247892962,-9007199249913308,-9007199249910861,-9007199247892950,-9007199249910861,-9007199247892948,-9007199249910861,-9007199247892946,-9007199249910861,-9007199247892944,-9007199249910861,-9007199247892942,-9007199249910861,-9007199247892940,-9007199249910861,-9007199247892938,-9007199249910861,-9007199247892936,-9007199249910861,-9007199249910861,-9007199247892932,-9007199249910861,-9007199249910861,-9007199247892929,-9007199249910860,-9007199247892926,-9007199249910860,-9007199247892924,-9007199249910860,-9007199247892922,-9007199249910860,-9007199247892920,-9007199249910860,-9007199247892918,-9007199249910860,-9007199247892916,-9007199249910860,-9007199247892914,-9007199249910860,-9007199247892912,-9007199249910860,-9007199247892910,-9007199249910860,-9007199247892908,-9007199249910860,-9007199247892906,-9007199249912401,-9007199247892904,-9007199247892904,-9007199249912401,-9007199249912401,-9007199249906658,-9007199249906658,-9007199249906658,-9007199249906658,-9007199249913294,-9007199249804236,-9007199247892897,-9007199247756498,-9007199247892897,-9007199247892896,-9007199247199839,-9007199247199839,-9007199247183787]')
AS "parent" ON "id"."key" == "parent"."key"
INNER JOIN
JSON_EACH('["RP010-0000044-14-02","RP010-0000044-14-01","RP010-0000044-14-09","RP010-0000213-14-09","RP010-0000044-16","RP010-0000044-14","RP010-0000044-01","RP010-0000044-02","RP010-0000044-16-01","RP010-0000044-16-02","RP010-0000044","TP010-0000862","TP010-0000862-01","TP010-0000862-02","TP010-0000862-14","TP010-0000862-14-01","TP010-0000862-14-01-01","TP010-0000862-14-01-01-10","TP010-0000862-14-01-02","TP010-0000862-14-01-02-10","TP010-0000862-14-01-03","TP010-0000862-14-01-03-10","TP010-0000862-14-01-20","TP010-0000862-14-01-20-10","TP010-0000862-14-01-20-77","TP010-0000862-14-02","TP010-0000862-14-02-01","TP010-0000862-14-02-01-10","TP010-0000862-14-02-02","TP010-0000862-14-02-02-10","TP010-0000862-14-02-03","TP010-0000862-14-02-20","TP010-0000862-14-02-20-10","TP010-0000862-14-02-20-77","TP010-0000862-16","TP010-0000862-16-01","TP010-0000862-16-01-01","TP010-0000862-16-01-01-10","TP010-0000862-16-01-02","TP010-0000862-16-01-02-10","TP010-0000862-16-01-03","TP010-0000862-16-01-03-10","TP010-0000862-16-01-04","TP010-0000862-16-01-04-10","TP010-0000862-16-01-05","TP010-0000862-16-01-05-10","TP010-0000862-16-01-06","TP010-0000862-16-01-06-10","TP010-0000862-16-01-07","TP010-0000862-16-01-07-10","TP010-0000862-16-01-08","TP010-0000862-16-01-08-10","TP010-0000862-16-01-09","TP010-0000862-16-01-09-10","TP010-0000862-16-01-10","TP010-0000862-16-01-10-10","TP010-0000862-16-01-20","TP010-0000862-16-01-20-10","TP010-0000862-16-02","TP010-0000862-16-02-01","TP010-0000862-16-02-01-10","TP010-0000862-16-02-02","TP010-0000862-16-02-02-10","TP010-0000862-16-02-03","TP010-0000862-16-02-03-10","TP010-0000862-16-02-04","TP010-0000862-16-02-04-10","TP010-0000862-16-02-05","TP010-0000862-16-02-05-10","TP010-0000862-16-02-06","TP010-0000862-16-02-06-10","TP010-0000862-16-02-07","TP010-0000862-16-02-07-10","TP010-0000862-16-02-08","TP010-0000862-16-02-08-10","TP010-0000862-16-02-09","TP010-0000862-16-02-09-10","TP010-0000862-16-02-10","TP010-0000862-16-02-10-10","TP010-0000862-16-02-20","TP010-0000862-16-02-20-10","TP010-0000862-16-08","TP010-0000862-16-08-01","TP010-0000862-16-08-02","TP010-0000862-16-09","TP010-0000862-16-09-01","TP010-0000862-16-09-01-77","TP010-0000862-16-09-02","TP010-0000862-16-09-02-77","TP010-0000862-16-10","TP010-0000862-16-50","TP010-0000862-16-51","TP010-0000862-30","TP010-0000862-30-00","TP010-0000862-63","RP010-0000044-16-01-09-10","RP010-0000044-14-01-01","RP010-0000044-14-01-01-10","RP010-0000044-14-01-01-22","RP010-0000044-14-01-01-22-01","RP010-0000044-14-01-01-23","RP010-0000044-14-01-01-23-01","RP010-0000044-14-01-01-77","RP010-0000044-14-01-02","RP010-0000044-14-01-02-10","RP010-0000044-14-01-02-22","RP010-0000044-14-01-02-22-01","RP010-0000044-14-01-02-23","RP010-0000044-14-01-02-23-01","RP010-0000044-14-01-02-77","RP010-0000044-14-01-03","RP010-0000044-14-01-03-10","RP010-0000044-14-01-03-22","RP010-0000044-14-01-03-22-01","RP010-0000044-14-01-03-23","RP010-0000044-14-01-03-23-01","RP010-0000044-14-01-03-77","RP010-0000044-14-01-04","RP010-0000044-14-01-04-10","RP010-0000044-14-01-04-22","RP010-0000044-14-01-04-22-01","RP010-0000044-14-01-04-23","RP010-0000044-14-01-04-23-01","RP010-0000044-14-01-04-77","RP010-0000044-14-01-05","RP010-0000044-14-01-05-10","RP010-0000044-14-01-05-22","RP010-0000044-14-01-05-22-01","RP010-0000044-14-01-05-23","RP010-0000044-14-01-05-23-01","RP010-0000044-14-01-05-77","RP010-0000044-14-01-06","RP010-0000044-14-01-06-10","RP010-0000044-14-01-06-22","RP010-0000044-14-01-06-22-01","RP010-0000044-14-01-06-23","RP010-0000044-14-01-06-23-01","RP010-0000044-14-01-06-77","RP010-0000044-14-01-07","RP010-0000044-14-01-07-10","RP010-0000044-14-01-07-22","RP010-0000044-14-01-07-22-01","RP010-0000044-14-01-07-23","RP010-0000044-14-01-07-23-01","RP010-0000044-14-01-07-77","RP010-0000044-14-01-08","RP010-0000044-14-01-08-10","RP010-0000044-14-01-08-22","RP010-0000044-14-01-08-22-01","RP010-0000044-14-01-08-23","RP010-0000044-14-01-08-23-01","RP010-0000044-14-01-08-77","RP010-0000044-14-01-09","RP010-0000044-14-01-09-10","RP010-0000044-14-01-09-22","RP010-0000044-14-01-09-22-01","RP010-0000044-14-01-09-23","RP010-0000044-14-01-09-23-01","RP010-0000044-14-01-09-77","RP010-0000044-14-01-10","RP010-0000044-14-01-10-10","RP010-0000044-14-01-10-22","RP010-0000044-14-01-10-22-01","RP010-0000044-14-01-10-23","RP010-0000044-14-01-10-77","RP010-0000044-14-01-11","RP010-0000044-14-01-11-22","RP010-0000044-14-01-11-22-01","RP010-0000044-14-01-20","RP010-0000044-14-01-20-10","RP010-0000044-14-01-20-21","RP010-0000044-14-01-20-21-10","RP010-0000044-14-01-20-22","RP010-0000044-14-01-20-22-10","RP010-0000044-14-01-20-77","RP010-0000044-14-01-80","RP010-0000044-14-02-01","RP010-0000044-14-02-01-10","RP010-0000044-14-02-01-22","RP010-0000044-14-02-01-22-01","RP010-0000044-14-02-01-23","RP010-0000044-14-02-01-23-01","RP010-0000044-14-02-01-77","RP010-0000044-14-02-02","RP010-0000044-14-02-02-10","RP010-0000044-14-02-02-22","RP010-0000044-14-02-02-22-01","RP010-0000044-14-02-02-23","RP010-0000044-14-02-02-23-01","RP010-0000044-14-02-02-77","RP010-0000044-14-02-03","RP010-0000044-14-02-03-10","RP010-0000044-14-02-03-22","RP010-0000044-14-02-03-22-01","RP010-0000044-14-02-03-23","RP010-0000044-14-02-03-23-01","RP010-0000044-14-02-03-77","RP010-0000044-14-02-04","RP010-0000044-14-02-04-10","RP010-0000044-14-02-04-22","RP010-0000044-14-02-04-22-01","RP010-0000044-14-02-04-23","RP010-0000044-14-02-04-23-01","RP010-0000044-14-02-04-77","RP010-0000044-14-02-05","RP010-0000044-14-02-05-10","RP010-0000044-14-02-05-22","RP010-0000044-14-02-05-22-01","RP010-0000044-14-02-05-23","RP010-0000044-14-02-05-23-01","RP010-0000044-14-02-05-77","RP010-0000044-14-02-06","RP010-0000044-14-02-06-10","RP010-0000044-14-02-06-22","RP010-0000044-14-02-06-22-01","RP010-0000044-14-02-06-23","RP010-0000044-14-02-06-23-01","RP010-0000044-14-02-06-77","RP010-0000044-14-02-07","RP010-0000044-14-02-07-10","RP010-0000044-14-02-07-22","RP010-0000044-14-02-07-22-01","RP010-0000044-14-02-07-23","RP010-0000044-14-02-07-23-01","RP010-0000044-14-02-07-77","RP010-0000044-14-02-08","RP010-0000044-14-02-08-10","RP010-0000044-14-02-08-22","RP010-0000044-14-02-08-22-01","RP010-0000044-14-02-08-23","RP010-0000044-14-02-08-23-01","RP010-0000044-14-02-08-77","RP010-0000044-14-02-09","RP010-0000044-14-02-09-10","RP010-0000044-14-02-09-22","RP010-0000044-14-02-09-22-01","RP010-0000044-14-02-09-23","RP010-0000044-14-02-09-23-01","RP010-0000044-14-02-09-77","RP010-0000044-14-02-10","RP010-0000044-14-02-10-22","RP010-0000044-14-02-10-22-01","RP010-0000044-14-02-10-77","RP010-0000044-14-02-11","RP010-0000044-14-02-11-22","RP010-0000044-14-02-11-22-01","RP010-0000044-14-02-20","RP010-0000044-14-02-20-10","RP010-0000044-14-02-20-21","RP010-0000044-14-02-20-21-10","RP010-0000044-14-02-20-22","RP010-0000044-14-02-20-22-10","RP010-0000044-14-02-20-77","RP010-0000044-14-02-80","RP010-0000044-16-01-01","RP010-0000044-16-01-01-10","RP010-0000044-16-01-02","RP010-0000044-16-01-02-10","RP010-0000044-16-01-03","RP010-0000044-16-01-03-10","RP010-0000044-16-01-04","RP010-0000044-16-01-04-10","RP010-0000044-16-01-05","RP010-0000044-16-01-05-10","RP010-0000044-16-01-06","RP010-0000044-16-01-06-10","RP010-0000044-16-01-07","RP010-0000044-16-01-07-10","RP010-0000044-16-01-08","RP010-0000044-16-01-08-10","RP010-0000044-16-01-09","RP010-0000044-16-01-10","RP010-0000044-16-01-10-10","RP010-0000044-16-01-12","RP010-0000044-16-01-20","RP010-0000044-16-01-20-10","RP010-0000044-16-02-01","RP010-0000044-16-02-01-10","RP010-0000044-16-02-02","RP010-0000044-16-02-02-10","RP010-0000044-16-02-03","RP010-0000044-16-02-03-10","RP010-0000044-16-02-04","RP010-0000044-16-02-04-10","RP010-0000044-16-02-05","RP010-0000044-16-02-05-10","RP010-0000044-16-02-06","RP010-0000044-16-02-06-10","RP010-0000044-16-02-07","RP010-0000044-16-02-07-10","RP010-0000044-16-02-08","RP010-0000044-16-02-08-10","RP010-0000044-16-02-09","RP010-0000044-16-02-09-10","RP010-0000044-16-02-10","RP010-0000044-16-02-10-10","RP010-0000044-16-02-20","RP010-0000044-16-02-20-10","RP010-0000044-16-10","RP010-0000044-16-10-10","RP010-0000044-16-10-11","RP010-0000044-16-50","RP010-0000044-16-51","RP010-0000044-30","RP010-0000044-63","RP010-0000044-80","RP010-0000044-81","RP010-0000213-14-09-77","TP010-0000862-81","RP010-0000044-80-01","TP010-0000862-81-10","RP010-0000044-80-02","RP010-0000044-81-10","RP010-0000044-80-01-02","RP010-0000044-80-01-01","RP010-0000044-80-02-01"]')
AS "code" ON "id"."key" == "code"."key"
),
------------------------run a recursive top-bottom query against it
"objects_hierarchy_top_bottom" AS (
--initial
SELECT "object"."id",
"object"."code",
"object"."parent"
FROM "objects_in_memory" AS "object"
WHERE "object"."code" IN (SELECT 'TP010-0000862')
UNION ALL
--recursive
SELECT "child"."id",
"child"."code",
"child"."parent"
FROM "objects_hierarchy_top_bottom" AS "parent"
INNER JOIN "objects_in_memory" AS "child" ON "child"."parent" ==
"parent"."id"
)
SELECT * FROM objects_hierarchy_top_bottom/*query2*/
----------------------Create real table--------------------------------
DROP TABLE IF EXISTS objects;
CREATE TABLE objects(
id INTEGER,
parent INTEGER,
code TEXT
);
WITH
------------------------SAME table from query1--------------------------------
"objects_in_memory" AS (
SELECT "id"."value" AS "id",
"parent"."value" AS "parent",
"code"."value" AS "code"
FROM
JSON_EACH('[-9007199249913308,-9007199249913307,-9007199249913305,-9007199249913294,-9007199249912401,-9007199249912399,-9007199249912355,-9007199249912354,-9007199249910861,-9007199249910860,-9007199249906658,-9007199249804236,-9007199249804235,-9007199249804234,-9007199249804233,-9007199249804232,-9007199249804231,-9007199249804230,-9007199249804229,-9007199249804228,-9007199249804227,-9007199249804226,-9007199249804225,-9007199249804224,-9007199249804223,-9007199249804222,-9007199249804221,-9007199249804220,-9007199249804219,-9007199249804218,-9007199249804217,-9007199249804216,-9007199249804215,-9007199249804214,-9007199249804213,-9007199249804212,-9007199249804211,-9007199249804210,-9007199249804209,-9007199249804208,-9007199249804207,-9007199249804206,-9007199249804205,-9007199249804204,-9007199249804203,-9007199249804202,-9007199249804201,-9007199249804200,-9007199249804199,-9007199249804198,-9007199249804197,-9007199249804196,-9007199249804195,-9007199249804194,-9007199249804193,-9007199249804192,-9007199249804191,-9007199249804190,-9007199249804189,-9007199249804188,-9007199249804187,-9007199249804186,-9007199249804185,-9007199249804184,-9007199249804183,-9007199249804182,-9007199249804181,-9007199249804180,-9007199249804179,-9007199249804178,-9007199249804177,-9007199249804176,-9007199249804175,-9007199249804174,-9007199249804173,-9007199249804172,-9007199249804171,-9007199249804170,-9007199249804169,-9007199249804168,-9007199249804167,-9007199249804166,-9007199249804165,-9007199249804164,-9007199249804163,-9007199249804162,-9007199249804161,-9007199249804160,-9007199249804159,-9007199249804158,-9007199249804157,-9007199249804156,-9007199249804155,-9007199249804154,-9007199249804153,-9007199248223119,-9007199247893113,-9007199247893112,-9007199247893111,-9007199247893110,-9007199247893109,-9007199247893108,-9007199247893107,-9007199247893106,-9007199247893105,-9007199247893104,-9007199247893103,-9007199247893102,-9007199247893101,-9007199247893100,-9007199247893099,-9007199247893098,-9007199247893097,-9007199247893096,-9007199247893095,-9007199247893094,-9007199247893093,-9007199247893092,-9007199247893091,-9007199247893090,-9007199247893089,-9007199247893088,-9007199247893087,-9007199247893086,-9007199247893085,-9007199247893084,-9007199247893083,-9007199247893082,-9007199247893081,-9007199247893080,-9007199247893079,-9007199247893078,-9007199247893077,-9007199247893076,-9007199247893075,-9007199247893074,-9007199247893073,-9007199247893072,-9007199247893071,-9007199247893070,-9007199247893069,-9007199247893068,-9007199247893067,-9007199247893066,-9007199247893065,-9007199247893064,-9007199247893063,-9007199247893062,-9007199247893061,-9007199247893060,-9007199247893059,-9007199247893058,-9007199247893057,-9007199247893056,-9007199247893055,-9007199247893054,-9007199247893053,-9007199247893052,-9007199247893051,-9007199247893050,-9007199247893049,-9007199247893048,-9007199247893047,-9007199247893046,-9007199247893045,-9007199247893044,-9007199247893043,-9007199247893042,-9007199247893041,-9007199247893040,-9007199247893039,-9007199247893038,-9007199247893037,-9007199247893036,-9007199247893035,-9007199247893034,-9007199247893032,-9007199247893031,-9007199247893030,-9007199247893029,-9007199247893028,-9007199247893027,-9007199247893026,-9007199247893025,-9007199247893024,-9007199247893023,-9007199247893022,-9007199247893021,-9007199247893020,-9007199247893019,-9007199247893018,-9007199247893017,-9007199247893016,-9007199247893015,-9007199247893014,-9007199247893013,-9007199247893012,-9007199247893011,-9007199247893010,-9007199247893009,-9007199247893008,-9007199247893007,-9007199247893006,-9007199247893005,-9007199247893004,-9007199247893003,-9007199247893002,-9007199247893001,-9007199247893000,-9007199247892999,-9007199247892998,-9007199247892997,-9007199247892996,-9007199247892995,-9007199247892994,-9007199247892993,-9007199247892992,-9007199247892991,-9007199247892990,-9007199247892989,-9007199247892988,-9007199247892987,-9007199247892986,-9007199247892985,-9007199247892984,-9007199247892983,-9007199247892982,-9007199247892981,-9007199247892980,-9007199247892979,-9007199247892978,-9007199247892977,-9007199247892976,-9007199247892975,-9007199247892974,-9007199247892973,-9007199247892972,-9007199247892971,-9007199247892970,-9007199247892969,-9007199247892968,-9007199247892967,-9007199247892966,-9007199247892965,-9007199247892964,-9007199247892963,-9007199247892962,-9007199247892961,-9007199247892960,-9007199247892959,-9007199247892958,-9007199247892957,-9007199247892956,-9007199247892955,-9007199247892950,-9007199247892949,-9007199247892948,-9007199247892947,-9007199247892946,-9007199247892945,-9007199247892944,-9007199247892943,-9007199247892942,-9007199247892941,-9007199247892940,-9007199247892939,-9007199247892938,-9007199247892937,-9007199247892936,-9007199247892935,-9007199247892934,-9007199247892932,-9007199247892931,-9007199247892930,-9007199247892929,-9007199247892928,-9007199247892926,-9007199247892925,-9007199247892924,-9007199247892923,-9007199247892922,-9007199247892921,-9007199247892920,-9007199247892919,-9007199247892918,-9007199247892917,-9007199247892916,-9007199247892915,-9007199247892914,-9007199247892913,-9007199247892912,-9007199247892911,-9007199247892910,-9007199247892909,-9007199247892908,-9007199247892907,-9007199247892906,-9007199247892905,-9007199247892904,-9007199247892903,-9007199247892902,-9007199247892901,-9007199247892900,-9007199247892899,-9007199247892898,-9007199247892897,-9007199247892896,-9007199247872746,-9007199247756498,-9007199247199839,-9007199247188113,-9007199247183787,-9007199247167606,-9007199247073706,-9007199247044103,-9007199247044102]')
AS "id"
INNER JOIN
JSON_EACH('[-9007199249912399,-9007199249912399,-9007199249912399,-9007199249911428,-9007199249906658,-9007199249906658,-9007199249906658,-9007199249906658,-9007199249912401,-9007199249912401,null,
null,
-9007199249804236,-9007199249804236,-9007199249804236,-9007199249804233,-9007199249804232,-9007199249804231,-9007199249804232,-9007199249804229,-9007199249804232,-9007199249804227,-9007199249804232,-9007199249804225,-9007199249804225,-9007199249804233,-9007199249804222,-9007199249804221,-9007199249804222,-9007199249804219,-9007199249804222,-9007199249804222,-9007199249804216,-9007199249804216,-9007199249804236,-9007199249804213,-9007199249804212,-9007199249804211,-9007199249804212,-9007199249804209,-9007199249804212,-9007199249804207,-9007199249804212,-9007199249804205,-9007199249804212,-9007199249804203,-9007199249804212,-9007199249804201,-9007199249804212,-9007199249804199,-9007199249804212,-9007199249804197,-9007199249804212,-9007199249804195,-9007199249804212,-9007199249804193,-9007199249804212,-9007199249804191,-9007199249804213,-9007199249804189,-9007199249804188,-9007199249804189,-9007199249804186,-9007199249804189,-9007199249804184,-9007199249804189,-9007199249804182,-9007199249804189,-9007199249804180,-9007199249804189,-9007199249804178,-9007199249804189,-9007199249804176,-9007199249804189,-9007199249804174,-9007199249804189,-9007199249804172,-9007199249804189,-9007199249804170,-9007199249804189,-9007199249804168,-9007199249804213,-9007199249804166,-9007199249804166,-9007199249804213,-9007199249804163,-9007199249804162,-9007199249804163,-9007199249804160,-9007199249804213,-9007199249804213,-9007199249804213,-9007199249804236,-9007199249804155,-9007199249804236,-9007199247892934,-9007199249913307,-9007199247893113,-9007199247893113,-9007199247893111,-9007199247893113,-9007199247893109,-9007199247893113,-9007199249913307,-9007199247893106,-9007199247893106,-9007199247893104,-9007199247893106,-9007199247893102,-9007199247893106,-9007199249913307,-9007199247893099,-9007199247893099,-9007199247893097,-9007199247893099,-9007199247893095,-9007199247893099,-9007199249913307,-9007199247893092,-9007199247893092,-9007199247893090,-9007199247893092,-9007199247893088,-9007199247893092,-9007199249913307,-9007199247893085,-9007199247893085,-9007199247893083,-9007199247893085,-9007199247893081,-9007199247893085,-9007199249913307,-9007199247893078,-9007199247893078,-9007199247893076,-9007199247893078,-9007199247893074,-9007199247893078,-9007199249913307,-9007199247893071,-9007199247893071,-9007199247893069,-9007199247893071,-9007199247893067,-9007199247893071,-9007199249913307,-9007199247893064,-9007199247893064,-9007199247893062,-9007199247893064,-9007199247893060,-9007199247893064,-9007199249913307,-9007199247893057,-9007199247893057,-9007199247893055,-9007199247893057,-9007199247893053,-9007199247893057,-9007199249913307,-9007199247893050,-9007199247893050,-9007199247893048,-9007199247893050,-9007199247893050,-9007199249913307,-9007199247893044,-9007199247893043,-9007199249913307,-9007199247893041,-9007199247893041,-9007199247893039,-9007199247893041,-9007199247893037,-9007199247893041,-9007199249913307,-9007199249913308,-9007199247893032,-9007199247893032,-9007199247893030,-9007199247893032,-9007199247893028,-9007199247893032,-9007199249913308,-9007199247893025,-9007199247893025,-9007199247893023,-9007199247893025,-9007199247893021,-9007199247893025,-9007199249913308,-9007199247893018,-9007199247893018,-9007199247893016,-9007199247893018,-9007199247893014,-9007199247893018,-9007199249913308,-9007199247893011,-9007199247893011,-9007199247893009,-9007199247893011,-9007199247893007,-9007199247893011,-9007199249913308,-9007199247893004,-9007199247893004,-9007199247893002,-9007199247893004,-9007199247893000,-9007199247893004,-9007199249913308,-9007199247892997,-9007199247892997,-9007199247892995,-9007199247892997,-9007199247892993,-9007199247892997,-9007199249913308,-9007199247892990,-9007199247892990,-9007199247892988,-9007199247892990,-9007199247892986,-9007199247892990,-9007199249913308,-9007199247892983,-9007199247892983,-9007199247892981,-9007199247892983,-9007199247892979,-9007199247892983,-9007199249913308,-9007199247892976,-9007199247892976,-9007199247892974,-9007199247892976,-9007199247892972,-9007199247892976,-9007199249913308,-9007199247892969,-9007199247892968,-9007199247892969,-9007199249913308,-9007199247892965,-9007199247892964,-9007199249913308,-9007199247892962,-9007199247892962,-9007199247892960,-9007199247892962,-9007199247892958,-9007199247892962,-9007199249913308,-9007199249910861,-9007199247892950,-9007199249910861,-9007199247892948,-9007199249910861,-9007199247892946,-9007199249910861,-9007199247892944,-9007199249910861,-9007199247892942,-9007199249910861,-9007199247892940,-9007199249910861,-9007199247892938,-9007199249910861,-9007199247892936,-9007199249910861,-9007199249910861,-9007199247892932,-9007199249910861,-9007199249910861,-9007199247892929,-9007199249910860,-9007199247892926,-9007199249910860,-9007199247892924,-9007199249910860,-9007199247892922,-9007199249910860,-9007199247892920,-9007199249910860,-9007199247892918,-9007199249910860,-9007199247892916,-9007199249910860,-9007199247892914,-9007199249910860,-9007199247892912,-9007199249910860,-9007199247892910,-9007199249910860,-9007199247892908,-9007199249910860,-9007199247892906,-9007199249912401,-9007199247892904,-9007199247892904,-9007199249912401,-9007199249912401,-9007199249906658,-9007199249906658,-9007199249906658,-9007199249906658,-9007199249913294,-9007199249804236,-9007199247892897,-9007199247756498,-9007199247892897,-9007199247892896,-9007199247199839,-9007199247199839,-9007199247183787]')
AS "parent" ON "id"."key" == "parent"."key"
INNER JOIN
JSON_EACH('["RP010-0000044-14-02","RP010-0000044-14-01","RP010-0000044-14-09","RP010-0000213-14-09","RP010-0000044-16","RP010-0000044-14","RP010-0000044-01","RP010-0000044-02","RP010-0000044-16-01","RP010-0000044-16-02","RP010-0000044","TP010-0000862","TP010-0000862-01","TP010-0000862-02","TP010-0000862-14","TP010-0000862-14-01","TP010-0000862-14-01-01","TP010-0000862-14-01-01-10","TP010-0000862-14-01-02","TP010-0000862-14-01-02-10","TP010-0000862-14-01-03","TP010-0000862-14-01-03-10","TP010-0000862-14-01-20","TP010-0000862-14-01-20-10","TP010-0000862-14-01-20-77","TP010-0000862-14-02","TP010-0000862-14-02-01","TP010-0000862-14-02-01-10","TP010-0000862-14-02-02","TP010-0000862-14-02-02-10","TP010-0000862-14-02-03","TP010-0000862-14-02-20","TP010-0000862-14-02-20-10","TP010-0000862-14-02-20-77","TP010-0000862-16","TP010-0000862-16-01","TP010-0000862-16-01-01","TP010-0000862-16-01-01-10","TP010-0000862-16-01-02","TP010-0000862-16-01-02-10","TP010-0000862-16-01-03","TP010-0000862-16-01-03-10","TP010-0000862-16-01-04","TP010-0000862-16-01-04-10","TP010-0000862-16-01-05","TP010-0000862-16-01-05-10","TP010-0000862-16-01-06","TP010-0000862-16-01-06-10","TP010-0000862-16-01-07","TP010-0000862-16-01-07-10","TP010-0000862-16-01-08","TP010-0000862-16-01-08-10","TP010-0000862-16-01-09","TP010-0000862-16-01-09-10","TP010-0000862-16-01-10","TP010-0000862-16-01-10-10","TP010-0000862-16-01-20","TP010-0000862-16-01-20-10","TP010-0000862-16-02","TP010-0000862-16-02-01","TP010-0000862-16-02-01-10","TP010-0000862-16-02-02","TP010-0000862-16-02-02-10","TP010-0000862-16-02-03","TP010-0000862-16-02-03-10","TP010-0000862-16-02-04","TP010-0000862-16-02-04-10","TP010-0000862-16-02-05","TP010-0000862-16-02-05-10","TP010-0000862-16-02-06","TP010-0000862-16-02-06-10","TP010-0000862-16-02-07","TP010-0000862-16-02-07-10","TP010-0000862-16-02-08","TP010-0000862-16-02-08-10","TP010-0000862-16-02-09","TP010-0000862-16-02-09-10","TP010-0000862-16-02-10","TP010-0000862-16-02-10-10","TP010-0000862-16-02-20","TP010-0000862-16-02-20-10","TP010-0000862-16-08","TP010-0000862-16-08-01","TP010-0000862-16-08-02","TP010-0000862-16-09","TP010-0000862-16-09-01","TP010-0000862-16-09-01-77","TP010-0000862-16-09-02","TP010-0000862-16-09-02-77","TP010-0000862-16-10","TP010-0000862-16-50","TP010-0000862-16-51","TP010-0000862-30","TP010-0000862-30-00","TP010-0000862-63","RP010-0000044-16-01-09-10","RP010-0000044-14-01-01","RP010-0000044-14-01-01-10","RP010-0000044-14-01-01-22","RP010-0000044-14-01-01-22-01","RP010-0000044-14-01-01-23","RP010-0000044-14-01-01-23-01","RP010-0000044-14-01-01-77","RP010-0000044-14-01-02","RP010-0000044-14-01-02-10","RP010-0000044-14-01-02-22","RP010-0000044-14-01-02-22-01","RP010-0000044-14-01-02-23","RP010-0000044-14-01-02-23-01","RP010-0000044-14-01-02-77","RP010-0000044-14-01-03","RP010-0000044-14-01-03-10","RP010-0000044-14-01-03-22","RP010-0000044-14-01-03-22-01","RP010-0000044-14-01-03-23","RP010-0000044-14-01-03-23-01","RP010-0000044-14-01-03-77","RP010-0000044-14-01-04","RP010-0000044-14-01-04-10","RP010-0000044-14-01-04-22","RP010-0000044-14-01-04-22-01","RP010-0000044-14-01-04-23","RP010-0000044-14-01-04-23-01","RP010-0000044-14-01-04-77","RP010-0000044-14-01-05","RP010-0000044-14-01-05-10","RP010-0000044-14-01-05-22","RP010-0000044-14-01-05-22-01","RP010-0000044-14-01-05-23","RP010-0000044-14-01-05-23-01","RP010-0000044-14-01-05-77","RP010-0000044-14-01-06","RP010-0000044-14-01-06-10","RP010-0000044-14-01-06-22","RP010-0000044-14-01-06-22-01","RP010-0000044-14-01-06-23","RP010-0000044-14-01-06-23-01","RP010-0000044-14-01-06-77","RP010-0000044-14-01-07","RP010-0000044-14-01-07-10","RP010-0000044-14-01-07-22","RP010-0000044-14-01-07-22-01","RP010-0000044-14-01-07-23","RP010-0000044-14-01-07-23-01","RP010-0000044-14-01-07-77","RP010-0000044-14-01-08","RP010-0000044-14-01-08-10","RP010-0000044-14-01-08-22","RP010-0000044-14-01-08-22-01","RP010-0000044-14-01-08-23","RP010-0000044-14-01-08-23-01","RP010-0000044-14-01-08-77","RP010-0000044-14-01-09","RP010-0000044-14-01-09-10","RP010-0000044-14-01-09-22","RP010-0000044-14-01-09-22-01","RP010-0000044-14-01-09-23","RP010-0000044-14-01-09-23-01","RP010-0000044-14-01-09-77","RP010-0000044-14-01-10","RP010-0000044-14-01-10-10","RP010-0000044-14-01-10-22","RP010-0000044-14-01-10-22-01","RP010-0000044-14-01-10-23","RP010-0000044-14-01-10-77","RP010-0000044-14-01-11","RP010-0000044-14-01-11-22","RP010-0000044-14-01-11-22-01","RP010-0000044-14-01-20","RP010-0000044-14-01-20-10","RP010-0000044-14-01-20-21","RP010-0000044-14-01-20-21-10","RP010-0000044-14-01-20-22","RP010-0000044-14-01-20-22-10","RP010-0000044-14-01-20-77","RP010-0000044-14-01-80","RP010-0000044-14-02-01","RP010-0000044-14-02-01-10","RP010-0000044-14-02-01-22","RP010-0000044-14-02-01-22-01","RP010-0000044-14-02-01-23","RP010-0000044-14-02-01-23-01","RP010-0000044-14-02-01-77","RP010-0000044-14-02-02","RP010-0000044-14-02-02-10","RP010-0000044-14-02-02-22","RP010-0000044-14-02-02-22-01","RP010-0000044-14-02-02-23","RP010-0000044-14-02-02-23-01","RP010-0000044-14-02-02-77","RP010-0000044-14-02-03","RP010-0000044-14-02-03-10","RP010-0000044-14-02-03-22","RP010-0000044-14-02-03-22-01","RP010-0000044-14-02-03-23","RP010-0000044-14-02-03-23-01","RP010-0000044-14-02-03-77","RP010-0000044-14-02-04","RP010-0000044-14-02-04-10","RP010-0000044-14-02-04-22","RP010-0000044-14-02-04-22-01","RP010-0000044-14-02-04-23","RP010-0000044-14-02-04-23-01","RP010-0000044-14-02-04-77","RP010-0000044-14-02-05","RP010-0000044-14-02-05-10","RP010-0000044-14-02-05-22","RP010-0000044-14-02-05-22-01","RP010-0000044-14-02-05-23","RP010-0000044-14-02-05-23-01","RP010-0000044-14-02-05-77","RP010-0000044-14-02-06","RP010-0000044-14-02-06-10","RP010-0000044-14-02-06-22","RP010-0000044-14-02-06-22-01","RP010-0000044-14-02-06-23","RP010-0000044-14-02-06-23-01","RP010-0000044-14-02-06-77","RP010-0000044-14-02-07","RP010-0000044-14-02-07-10","RP010-0000044-14-02-07-22","RP010-0000044-14-02-07-22-01","RP010-0000044-14-02-07-23","RP010-0000044-14-02-07-23-01","RP010-0000044-14-02-07-77","RP010-0000044-14-02-08","RP010-0000044-14-02-08-10","RP010-0000044-14-02-08-22","RP010-0000044-14-02-08-22-01","RP010-0000044-14-02-08-23","RP010-0000044-14-02-08-23-01","RP010-0000044-14-02-08-77","RP010-0000044-14-02-09","RP010-0000044-14-02-09-10","RP010-0000044-14-02-09-22","RP010-0000044-14-02-09-22-01","RP010-0000044-14-02-09-23","RP010-0000044-14-02-09-23-01","RP010-0000044-14-02-09-77","RP010-0000044-14-02-10","RP010-0000044-14-02-10-22","RP010-0000044-14-02-10-22-01","RP010-0000044-14-02-10-77","RP010-0000044-14-02-11","RP010-0000044-14-02-11-22","RP010-0000044-14-02-11-22-01","RP010-0000044-14-02-20","RP010-0000044-14-02-20-10","RP010-0000044-14-02-20-21","RP010-0000044-14-02-20-21-10","RP010-0000044-14-02-20-22","RP010-0000044-14-02-20-22-10","RP010-0000044-14-02-20-77","RP010-0000044-14-02-80","RP010-0000044-16-01-01","RP010-0000044-16-01-01-10","RP010-0000044-16-01-02","RP010-0000044-16-01-02-10","RP010-0000044-16-01-03","RP010-0000044-16-01-03-10","RP010-0000044-16-01-04","RP010-0000044-16-01-04-10","RP010-0000044-16-01-05","RP010-0000044-16-01-05-10","RP010-0000044-16-01-06","RP010-0000044-16-01-06-10","RP010-0000044-16-01-07","RP010-0000044-16-01-07-10","RP010-0000044-16-01-08","RP010-0000044-16-01-08-10","RP010-0000044-16-01-09","RP010-0000044-16-01-10","RP010-0000044-16-01-10-10","RP010-0000044-16-01-12","RP010-0000044-16-01-20","RP010-0000044-16-01-20-10","RP010-0000044-16-02-01","RP010-0000044-16-02-01-10","RP010-0000044-16-02-02","RP010-0000044-16-02-02-10","RP010-0000044-16-02-03","RP010-0000044-16-02-03-10","RP010-0000044-16-02-04","RP010-0000044-16-02-04-10","RP010-0000044-16-02-05","RP010-0000044-16-02-05-10","RP010-0000044-16-02-06","RP010-0000044-16-02-06-10","RP010-0000044-16-02-07","RP010-0000044-16-02-07-10","RP010-0000044-16-02-08","RP010-0000044-16-02-08-10","RP010-0000044-16-02-09","RP010-0000044-16-02-09-10","RP010-0000044-16-02-10","RP010-0000044-16-02-10-10","RP010-0000044-16-02-20","RP010-0000044-16-02-20-10","RP010-0000044-16-10","RP010-0000044-16-10-10","RP010-0000044-16-10-11","RP010-0000044-16-50","RP010-0000044-16-51","RP010-0000044-30","RP010-0000044-63","RP010-0000044-80","RP010-0000044-81","RP010-0000213-14-09-77","TP010-0000862-81","RP010-0000044-80-01","TP010-0000862-81-10","RP010-0000044-80-02","RP010-0000044-81-10","RP010-0000044-80-01-02","RP010-0000044-80-01-01","RP010-0000044-80-02-01"]')
AS "code" ON "id"."key" == "code"."key"
)
INSERT INTO objects select * from objects_in_memory;
--------------------------Recursive query real
table---------------------------------------------
WITH
"objects_hierarchy_top_bottom" AS (
--initial
SELECT "object"."id",
"object"."code",
"object"."parent"
FROM "objects" AS "object"
WHERE "object"."code" IN (SELECT 'TP010-0000862')
UNION ALL
--initial
SELECT "child"."id",
"child"."code",
"child"."parent"
FROM "objects_hierarchy_top_bottom" AS "parent"
INNER JOIN "objects" AS "child" ON "child"."parent" == "parent"."id"
)
SELECT * FROM objects_hierarchy_top_bottom_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
