select 
    srcnode.*,
    anl.*,
    dstnode.*,
    link.metric linked, 
    canlink1.*,
    canlink3.*,
    canlink.*
from nodeT srcnode
join nodeLinkTypeT nlt on nlt.isDefault = 'y'
join allowedNodeLinkT anl on srcnode.nodeTypeId = anl.srcNodeTypeId
join nodeT dstnode on anl.dstNodeTypeId = dstnode.nodeTypeId and srcnode.id != dstnode.id

left join nodeLinkT link on nlt.id = link.nodeLinkTypeId
    and srcnode.id = link.srcNodeId
    and dstnode.id = link.dstNodeId
    and link.metric = 1

left join zzzIntsT canlink1 on canlink1.n = 1
    and link.metric is null
    and anl.singleSrc = 'n'
    and (select count(distinct n1.nodeTypeId)
            from nodeLinkT nl1
            join nodeT n1 on nl1.srcNodeId = n1.id
            where nl1.nodeLinkTypeId = nlt.id
                and nl1.dstNodeId = srcnode.id
                and nl1.metric = 1) =
        (select count(distinct n2.nodeTypeId)
            from nodeLinkForkSrcV nl2
            join nodeT n2 on nl2.srcNodeId = n2.id
            where nl2.nodeLinkTypeId = nlt.id
                and nl2.dstNodeId1 = srcnode.id
                and nl2.metric1 = 1
                and nl2.dstNodeId2 = dstnode.id
                and nl2.metric2 = 1)

left join zzzIntsT canlink3 on canlink3.n = 1
    and link.metric is null
    and anl.singleSrc = 'y'
-- check if already linked to the node of the same type
    and not exists(
        select 1
        from nodeLinkT nl3
        join nodeT n3 on nl3.srcNodeId = n3.id
        where nl3.nodeLinkTypeId = nlt.id
            and nl3.dstNodeId = dstnode.id
            and nl3.metric = 1
            and n3.nodeTypeId = srcnode.nodeTypeId)
-- check if srcnode.id and dstnode.id have common parent node, with special conditions
    and exists(
        select 1
        from nodeLinkForkSrcV nl4
        join nodeT n4 on nl4.srcNodeId = n4.id
        join allowedNodeLinkT anl4 on n4.nodeTypeId = anl4.srcNodeTypeId
        where nl4.nodeLinkTypeId = nlt.id
            and nl4.dstNodeId1 = srcnode.id
            and nl4.metric1 = 1
            and nl4.dstNodeId2 = dstnode.id
            and nl4.metric2 = 1
            and anl4.dstNodeTypeId = dstnode.nodeTypeId
            and anl4.singleSrc = anl.singleSrc
            and (anl4.essential = 'y'
-- if the link is not essential make sure we are not stepping over the srcnode.id
                or not exists(
                    select 1
                    from nodeLinkChainTransitionV exilink41
                    where nl4.nodeLinkTypeId = exilink41.nodeLinkTypeId
                        and nl4.srcNodeId = exilink41.srcNodeId
                        and nl4.dstNodeId1 = exilink41.dstNodeId))
-- and make sure we use the nearest common parent, i.e. there are no more common parents up the tree
            and not exists(
                select 1
                from nodeLinkChainTransitionV exilink42
                where nl4.nodeLinkTypeId = exilink42.nodeLinkTypeId
                    and nl4.srcNodeId = exilink42.srcNodeId
                    and nl4.dstNodeId2 = exilink42.dstNodeId))
left join zzzIntsT canlink on canlink.n = 1 and (canlink1.n = 1 or canlink3.n = 1)
left join templateT srcTemplate on srcnode.id = srcTemplate.dstNodeId
left join templateT dstTemplate on dstnode.id = dstTemplate.dstNodeId
where srcTemplate.dstNodeId is null
    and dstTemplate.dstNodeId is null
    and srcnode.id = 30
    and dstnode.id = 25


    and (select count(distinct n1.nodeTypeId)
            from nodeLinkT nl1
            join nodeT n1 on nl1.srcNodeId = n1.id
            where nl1.nodeLinkTypeId = nlt.id
                and nl1.dstNodeId = srcnode.id
                and nl1.metric = 1) =
        (select count(distinct n2.nodeTypeId)
            from nodeLinkForkSrcV nl2
            join nodeT n2 on nl2.srcNodeId = n2.id
            where nl2.nodeLinkTypeId = nlt.id
                and nl2.dstNodeId1 = srcnode.id
                and nl2.metric1 = 1
                and nl2.dstNodeId2 = dstnode.id
                and nl2.metric2 = 1)

select *
            from nodeLinkT nl1
            join nodeT n1 on nl1.srcNodeId = n1.id
            where nl1.nodeLinkTypeId = 1
                and nl1.dstNodeId = 30
                and nl1.metric = 1

        select *
            from nodeLinkForkSrcV nl2
            join nodeT n2 on nl2.srcNodeId = n2.id
            where nl2.nodeLinkTypeId = 1
                and nl2.dstNodeId1 = 30
                and nl2.metric1 = 1
                and nl2.dstNodeId2 = 25
                and nl2.metric2 = 1



























select search_StartSession('1', null) and search_SetTag(9);
select search_StartSession('1', null) and search_SetTag(9) and search_SetTag(30);
select search_StartSession('1', null) and search_SetTag(9) and search_SetTag(13) and search_SetTag(30);
select search_StartSession('1', null) and search_SetTag(9) and search_SetTag(25) and search_SetTag(30) and search_SetTag(32) and search_SetTag(47);
select search_StartSession('1', null) and search_SetTag(9) and search_SetTag(32) and search_SetTag(47);
select search_StartSession('1', null) and search_SetTag(9) and search_SetTag(25) and search_SetTag(32) and search_SetTag(47);
select search_StartSession('1', null) and search_SetTag(9) and search_SetTag(25) and search_SetTag(30);
select search_StartSession('1', null) and search_SetTag(9) and search_SetTag(25) and search_SetTag(30) and search_SetTag(33);
select search_StartSession('1', null) and search_SetTag(9) and search_SetTag(25) and search_SetTag(30) and search_SetTag(35);

23438746212893437
    set @1 = __search_RectifyCriteria(23438746212893437);

    select count(distinct sc.nodeTypeId) 
    -- into count_notnull
    from searchCriterionT sc
    where sc.sessionId = 23438746212893437
        and sc.nodeTypeId is not null
        and sc.internalTypeId = 'tag';

    select count(*), coalesce(sum(t.factor), 0) 
    -- into count_null, sum_null
    from searchCriterionT sc
    join tagT t on sc.nodeId = t.id
    where sc.sessionId = 23438746212893437
        and sc.nodeTypeId is null
        and sc.internalTypeId = 'tag';

    select count(*) 
    -- into count_numerics
    from searchCriterionT sc
    join searchCriterionNumericT scn on sc.sessionId = scn.sessionId and sc.nodeId = scn.nodeId
    where sc.sessionId = 23438746212893437;

    select count(*) 
    -- into count_datetimes
    from searchCriterionT sc
    join searchCriterionDatetimeT scd on sc.sessionId = scd.sessionId and sc.nodeId = scd.nodeId
    where sc.sessionId = 23438746212893437;

--    set search_objects = __search_CheckRequiredForObject(23438746212893437);

    delete from searchResultT where sessionId = 23438746212893437;

    call __search_SearchTags(23438746212893437, 3, 0);

    set @2 = __search_SetDefaultResult(23438746212893437);
    
    set @3 = __search_RectifyCriteriaPost(23438746212893437);
    
    -- CONST: sum_null_tolerance_ is 1
    call __search_SearchObjects(23438746212893437, 3, 0, 1, 0, 0);
    
--    call __search_UpdateExtraObjects(23438746212893437);
                    drop temporary table if exists searchUpdateExtraObjectsData;
                    create temporary table searchUpdateExtraObjectsData(
                        tagId int unsigned not null primary key,
                        cnt int unsigned not null default 0
                    ) engine=MEMORY;
                    
                    insert searchUpdateExtraObjectsData(tagId, cnt)
                    select tagresult.nodeId, 0
                    from searchResultT tagresult
                    where tagresult.sessionId = 23438746212893437;
                    
                -- all searchAnd tags
                    insert searchUpdateExtraObjectsData(tagId, cnt)
                    select tagresult.nodeId, 1
                    from searchResultT tagresult
                    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
                    join tagT t on tagresult.nodeId = t.id
                    join nodeT n on t.id = n.id
                    join nodeTypeT nt on n.nodeTypeId = nt.id
                    where tagresult.sessionId = 23438746212893437
                        and nt.searchAnd = 'y'
                        and exists (select 1 
                                from nodeLinkT nl 
                                join searchResultT objectresult on nl.dstNodeId = objectresult.nodeId
                                join objectT o on objectresult.nodeId = o.id
                                where nl.nodeLinkTypeId = nlt.id and nl.srcNodeId = t.id and nl.metric = 1)
                    on duplicate key update cnt = 1;

                -- all non searchAnd tags, whose type is not selected
                    insert searchUpdateExtraObjectsData(tagId, cnt)
                    select tagresult.nodeId, 1
                    from searchResultT tagresult
                    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
                    join tagT t on tagresult.nodeId = t.id
                    join nodeT n on t.id = n.id
                    join nodeTypeT nt on n.nodeTypeId = nt.id
                    where tagresult.sessionId = 23438746212893437
                        and nt.searchAnd = 'n'
                        and exists (select 1 
                                    from nodeLinkT nl 
                                    join searchResultT objectresult on nl.dstNodeId = objectresult.nodeId
                                    join objectT o on objectresult.nodeId = o.id
                                    where objectresult.sessionId = tagresult.sessionId
                                        and nl.nodeLinkTypeId = nlt.id 
                                        and nl.srcNodeId = t.id 
                                        and nl.metric = 1)
                        and not exists (select 1
                                    from searchCriterionT criteria
                                    where criteria.sessionId = tagresult.sessionId
                                        and criteria.nodeTypeId = nt.id)
                    on duplicate key update cnt = 1;

                -- all non searchAnd tags, whose type is selected, regardless if there are any objects
                    insert searchUpdateExtraObjectsData(tagId, cnt)
                    select tagresult.nodeId, 1
                    from searchResultT tagresult
                    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
                    join tagT t on tagresult.nodeId = t.id
                    join nodeT n on t.id = n.id
                    join nodeTypeT nt on n.nodeTypeId = nt.id
                    where tagresult.sessionId = 23438746212893437
                        and nt.searchAnd = 'n'
                        and exists (select 1
                                    from searchCriterionT criteria
                                    where criteria.sessionId = tagresult.sessionId
                                        and criteria.nodeTypeId = nt.id)
                    on duplicate key update cnt = 1;

                    update searchResultT sr join searchUpdateExtraObjectsData sueod on sr.nodeId = sueod.tagId set
                        sr.extraObjects = sueod.cnt
                    where sr.sessionId = 23438746212893437;

                    drop temporary table searchUpdateExtraObjectsData;




    call __search_UpdateSortCache(23438746212893437);



select id, name, criterionId, resultId, collapsed  from searchTagV where criterionId is not null or resultId is not null;






-- !!!!!!!!!!!!!!!!!!!!!!!            
select *

from nodeT n
where (select count(distinct nf2.nodeTypeId) 
            from nodeFilt_ nf2) =  
        (select count(distinct nf3.nodeTypeId) 
            from nodeLinkT nl3 
            join nodeFilt_ nf3 on nl3.srcNodeId = nf3.nodeId 
            where nl3.dstNodeId = n.id)
-- !!!!!!!!!!!!!!!!!!!!!!!            

select 
    n.id, 
    n.name

from nodeT n
join nodeLinkT nl on n.id = nl.dstNodeId
join nodeFilt_ nf on nl.srcNodeId = nf.nodeId
group by n.id
having count(distinct nf.nodeTypeId) = (select count(distinct nff.nodeTypeId) from nodeFilt_ nff)

            
            
-- 

select *

from nodeT n
join nodeLinkT nodeLink on n.id = nodeLink.dstNodeId
join nodeFilt_ nodeFilt on nodeLink.srcNodeId = nodeFilt.nodeId 
where (select count(distinct nf.nodeTypeId)
        from nodeFilt_ nf) = 
    (select count(distinct nf.nodeTypeId) 
        from nodeLinkT nl 
        join nodeFilt_ nf on nl.srcNodeId = nf.nodeId 
        where nl.dstNodeId = n.id)

-- 

select n.id, n.name, count(*)

from nodeT n
join nodeLinkT nodeLink on n.id = nodeLink.dstNodeId
join nodeFilt_ nodeFilt on nodeLink.srcNodeId = nodeFilt.nodeId 
where (select count(distinct nf.nodeTypeId)
        from nodeFilt_ nf) = 
    (select count(distinct nf.nodeTypeId) 
        from nodeLinkT nl 
        join nodeFilt_ nf on nl.srcNodeId = nf.nodeId 
        where nl.dstNodeId = n.id)
group by n.id


-- 
select *

from nodeT n
join nodeLinkT nodeLink on n.id = nodeLink.dstNodeId
join nodeFilt_ nodeFilt on nodeLink.srcNodeId = nodeFilt.nodeId 
where (select count(distinct nf.nodeTypeId)
        from nodeFilt_ nf) = 
    (select count(distinct nf.nodeTypeId) 
        from nodeLinkT nl 
        join nodeFilt_ nf on nl.srcNodeId = nf.nodeId 
        where nl.dstNodeId = n.id)















/*
drop table if exists nodeFilt_;
create table nodeFilt_(
    nodeId int unsigned not null primary key,
    nodeTypeId int unsigned not null,
    index(nodeTypeId, nodeId)
) engine=InnoDB;

create trigger nodeFilt_bi before insert on nodeFilt_ for each row
set new.nodeTypeId = (select n.nodeTypeId from nodeT n where n.id = new.nodeId);

insert nodeFilt_(nodeId) values (15), (19);

*/