select search_StartSession('1', null) and search_SetTag(9) and search_SetTag(31) and search_SetTag(88224);
select search_SetAttributePrice(96198, 'UAH', null, 150);
call search_Search();













































































select search_StartSession('1', null) and search_SetTag(9) and search_SetTag(30) and search_SetTag(88224);
call search_Search();

select search_StartSession('1', null) and search_SetTag(9) and search_SetTag(31) and search_SetTag(88224);
call search_Search();

select search_StartSession('1', null) and search_SetTag(9) and search_SetTag(88224);
call search_Search();

select search_StartSession('1', null) and search_SetTag(9) and search_SetTag(30) and search_SetTag(31) and search_SetTag(88224);
call search_Search();


select 
    sr.*, 
    o.address, 
    (select n.id 
    from nodeLinkT nl 
    join searchCriterionV sc on nl.srcNodeId = sc.nodeId 
    join nodeV n on sc.nodeId = n.id and n.typeCode = 'submissiontype'
    where nl.dstNodeId = o.id) submission_id
from searchResultV sr 
join objectT o on sr.nodeId = o.id;


select 
    owt.srcId,
    owt.srcTypeCode, 
    owt.srcTypeName,
    owt.srcInternalTypeId,
    owt.srcName,
    owt.srcAttributeCode,
    owt.objectId,
    (select min(n.id)
    from nodeLinkOwnsV nl 
    join searchCriterionV sc on nl.srcNodeId = sc.nodeId 
    join nodeV n on sc.nodeId = n.id and n.typeCode = 'submissiontype'
    where nl.dstNodeId = owt.srcId) submission_id
from objectWithTagLinkedV owt
join searchResultV sr on owt.objectId = sr.nodeId;

where owt.objectId = 96206;


select 
    owt.srcId,
    owt.srcTypeCode, 
    owt.srcTypeName,
    owt.srcInternalTypeId,
    owt.srcName,
    owt.srcAttributeCode,
    owt.objectId,
    tlc.criterionId
from objectWithTagLinkedV owt
join searchResultV sr on owt.objectId = sr.nodeId
left join tagLinkCriterionV tlc on owt.srcId = tlc.tagId and tlc.criterionTypeCode = 'submissiontype';

where owt.objectId = 96206;


select 
    owt.srcId,
    owt.srcTypeCode, 
    owt.srcTypeName,
    owt.srcInternalTypeId,
    owt.srcName,
    owt.srcAttributeCode,
    owt.objectId,
    (select min(tlc.criterionId) from tagLinkCriterionV tlc where owt.srcId = tlc.tagId and tlc.criterionTypeCode = 'submissiontype') criterionId
from objectWithTagLinkedV owt
join searchResultV sr on owt.objectId = sr.nodeId;

where owt.objectId = 96206;



drop view if exists tagLinkCriterionV;

create view tagLinkCriterionV as
select 
    nl.dstNodeId tagId,
    n.id criterionId,
    n.typeCode criterionTypeCode
from nodeLinkOwnsV nl 
join searchCriterionV sc on nl.srcNodeId = sc.nodeId 
join nodeV n on sc.nodeId = n.id
where nl.metric = 1;




































































select
    n.id,
    n.nodeTypeId,
    n.forceSingleNode,
    n.name,
    n.isRoot,
    nt.name typeName,
    nt.code typeCode,
    nt.internalTypeId,
    nt.canEditType,
    nt.canEditNode,
    nt.canHavePhoto,
    nt.canHaveRatingPoints,
    nt.canLinkFrom,
    nt.canLinkTo,
    nt.canSearch,
    nt.displayInObject,
    nt.displayInObjectList,
    nt.searchAnd,
    nt.requiredForObject,
    nt.sorter,
    nt.languageId,
    n.added,
    n.addedUserId,
    n.addedUserName,
    n.addedIp,
    n.changed,
    n.changedUserId,
    n.changedUserName,
    n.changedIp
from _nodeV n
join nodeTypeV nt on n.nodeTypeId = nt.id;




































































































select 
    n.id,
    n.name,
    strict_criterion_link.dstNodeId,
    strict_criterion.nodeId,
    nonstrict_criterion_link.dstNodeId,
    nonstrict_criterion.nodeId,
    nonstrict_tag.id

from nodeT n
join nodeLinkTypeT nlt on nlt.isDefault = 'y'
join objectT o on n.id = o.id
join objectStatusT os on o.statusId = os.id

join nodeLinkT strict_criterion_link on nlt.id = strict_criterion_link.nodeLinkTypeId
    and n.id = strict_criterion_link.dstNodeId
    and strict_criterion_link.metric = 1
join searchCriterionT strict_criterion on strict_criterion_link.srcNodeId = strict_criterion.nodeId
    and strict_criterion.nodeTypeId is not null
    and strict_criterion.internalTypeId = 'tag'

                                        left join nodeLinkT nonstrict_criterion_link on nlt.id = nonstrict_criterion_link.nodeLinkTypeId 
                                            and n.id = nonstrict_criterion_link.dstNodeId 
                                            and nonstrict_criterion_link.metric = 1
                                        left join searchCriterionT nonstrict_criterion on nonstrict_criterion_link.srcNodeId = nonstrict_criterion.nodeId 
                                            and nonstrict_criterion.sessionId = strict_criterion.sessionId
                                            and nonstrict_criterion.nodeTypeId is null
                                            and nonstrict_criterion.internalTypeId = 'tag'
                                        left join tagT nonstrict_tag on nonstrict_criterion.nodeId = nonstrict_tag.id

where strict_criterion.sessionId = @session_id_
    and os.public = 'y';







select search_StartSession('1', null) 
    and search_SetTag(24) 
    and search_SetTag(25) 
    and search_SetTag(30) 
    and search_SetTag(31) 
    and search_SetTag(32) 
    and search_SetTag(46) 
    and search_SetAttributeNumeric(6789, 40, 100);

select search_StartSession('1', null) 
    and search_SetTag(24) 
    and search_SetTag(25) 
    and search_SetTag(30) 
    and search_SetTag(31) 
    and search_SetTag(32) 
    and search_SetTag(46) 
    and search_SetTag(47) 
    and search_SetAttributeNumeric(6789, 40, 100);

select search_StartSession('1', null) 
    and search_SetTag(24) 
    and search_SetTag(25) 
    and search_SetTag(30) 
    and search_SetTag(31) 
    and search_SetTag(32) 
    and search_SetTag(46) 
    and search_SetTag(47) 
    and search_SetAttributeNumeric(6789, 40, 100)
    and search_SetAttributeNumeric(37273, 2, 6);

                                select search_StartSession('1', null) 
                                    and search_SetTag(9) 
                                    and search_SetTag(25) 
                                    and search_SetTag(26) 
                                    and search_SetTag(30) 
                                    and search_SetTag(31) 
                                    and search_SetTag(32) 
                                    and search_SetTag(88224) 
                                    and search_SetAttributeNumeric(96199, 10, 80);

                                select search_StartSession('1', null) 
                                    and search_SetTag(9) 
                                    and search_SetTag(25) 
                                    and search_SetTag(26) 
                                    and search_SetTag(30) 
                                    and search_SetTag(31) 
                                    and search_SetTag(32) 
                                    and search_SetTag(88224) 
                                    and search_SetAttributeNumeric(96199, null, 80);

call search_Search();



select * from searchResultV where sessionId = 5330;



    set @session_id = search_CurrentId();
    select @session_id;

    -- start transaction;
    
    set @1 = __search_RectifyCriteria(@session_id);

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

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

    select count(*) into @count_numerics
    from searchCriterionT sc
    join searchCriterionNumericT scn on sc.id = scn.searchCriterionId
    where sc.sessionId = @session_id;
    select @count_numerics;

    select count(*) into @count_datetimes
    from searchCriterionT sc
    join searchCriterionDatetimeT scd on sc.id = scd.searchCriterionId
    where sc.sessionId = @session_id;
    select @count_datetimes;

    -- set search_objects = __search_CheckRequiredForObject(@session_id);

    delete from searchResultT where sessionId = @session_id;

    set @1 = __search_SetCachedResult(@session_id);

    call __search_SearchTags(@session_id, @count_notnull, @count_null);

    set @2 = __search_SetDefaultResult(@session_id);
    
    set @3 = __search_RectifyCriteriaPost(@session_id);
    
    call __search_Prepare(@session_id);
                                    -- drop temporary table if exists searchUpdateExtraObjectsData;
                                    -- create temporary table searchUpdateExtraObjectsData(
                                    --     tagId int unsigned not null primary key,
                                    --     cnt int unsigned not null default 0
                                    -- );

                                    -- drop temporary table if exists searchObjectTemp;
                                    -- create temporary table searchObjectTemp(objectId int unsigned not null primary key);
                                    
                                    -- insert searchUpdateExtraObjectsData(tagId, cnt)
                                    -- select tagresult.nodeId, 0
                                    -- from searchResultT tagresult
                                    -- where tagresult.sessionId = @session_id;

    -- CONST: sum_null_tolerance_ is 0.001
    call __search_SearchObjects(@session_id, @count_notnull, @sum_null, 0.001, @count_numerics, @count_datetimes);
    -- call __search_SearchObjects(@session_id, @count_notnull, @sum_null, 0.001, @count_numerics, @count_datetimes);
                                    set @session_id_ = @session_id;
                                    set @count_notnull_ = @count_notnull;
                                    set @sum_null_ = @sum_null;
                                    set @sum_null_tolerance_ = 0.001;
                                    set @count_numerics_ = @count_numerics;
                                    set @count_datetimes_ = @count_datetimes;

                                    set @limit_dataset = 
                                        if(
                                            @count_notnull_ > 0, 
                                            constI('search.limit.criteria.notempty'), 
                                            constI('search.limit.criteria.empty'));
                                    select @limit_dataset;

                                    set @count_numerics = greatest(@count_numerics_, 1);
                                    set @count_datetimes = greatest(@count_datetimes_, 1);

                                    if @count_notnull_ > 0 then

                                        -- NOTE: searchObjectTemp created in __search_Prepare
                                        insert searchObjectTemp(objectId)
                                        select n.id
                                        from nodeT n
                                        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
                                        join objectT o on n.id = o.id
                                        join objectStatusT os on o.statusId = os.id

                                        join nodeLinkT strict_criterion_link on nlt.id = strict_criterion_link.nodeLinkTypeId
                                            and n.id = strict_criterion_link.dstNodeId
                                            and strict_criterion_link.metric = 1
                                        join searchCriterionT strict_criterion on strict_criterion_link.srcNodeId = strict_criterion.nodeId                        
                                            and strict_criterion.nodeTypeId is not null
                                            and strict_criterion.internalTypeId = 'tag'

                                        where strict_criterion.sessionId = @session_id_
                                            and os.public = 'y'

                                        group by n.id

                                        having count(distinct strict_criterion.nodeTypeId) = @count_notnull_
                                        order by o.rating desc, n.changed desc;
                                        -- limit @limit_dataset;

                                    else

                                        -- NOTE: searchObjectTemp created in __search_Prepare
                                        insert searchObjectTemp(objectId)
                                        select n.id
                                        from nodeT n
                                        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
                                        join objectT o on n.id = o.id
                                        join objectStatusT os on o.statusId = os.id

                                        where os.public = 'y'

                                        group by n.id

                                        order by o.rating desc, n.changed desc;
                                        -- limit @limit_dataset;

                                    end if;

                                    insert ignore searchResultT(sessionId, nodeId, relevancy)
select 
    @session_id_, 
    sot.objectId, 
                /* sum(nonstrict_tag.factor),
                -- count(nonstrict_tag.id),
                -- count(distinct nonstrict_tag.id),
                -- group_concat(nonstrict_tag.id),
                sum(nonstrict_tag.factor) / greatest(count(nonstrict_tag.id) / greatest(count(distinct nonstrict_tag.id), 1.0), 1.0) sum_factor,
                count(scn.searchCriterionId),
                count(distinct scn.searchCriterionId),
                -- group_concat(scn.searchCriterionId),
                count(scd.searchCriterionId),
                count(distinct scd.searchCriterionId),
                -- group_concat(scd.searchCriterionId),
                coalesce(
                    sum(nonstrict_tag.factor) / greatest(count(nonstrict_tag.id) / greatest(count(distinct nonstrict_tag.id), 1.0), 1.0), 
                    @sum_null_tolerance_) / @sum_null_ _1,
                count(distinct scn.searchCriterionId) / @count_numerics _2,
                count(distinct scd.searchCriterionId) / @count_datetimes _3, */ 
    (
        (coalesce(
            sum(nonstrict_tag.factor) / greatest(count(nonstrict_tag.id) / greatest(count(distinct nonstrict_tag.id), 1.0), 1.0), 
            0) / @sum_null_ -- sum_null_ must be sanitized to be != 0
        + (count(distinct scn.searchCriterionId) / @count_numerics)
        + (count(distinct scd.searchCriterionId) / @count_datetimes))
    / 3) relevancy
from searchObjectTemp sot
join nodeLinkTypeT nlt on nlt.isDefault = 'y'

left join nodeLinkT nonstrict_criterion_link on nlt.id = nonstrict_criterion_link.nodeLinkTypeId 
    and sot.objectId  = nonstrict_criterion_link.dstNodeId 
    and nonstrict_criterion_link.metric = 1
left join searchCriterionT nonstrict_criterion on nonstrict_criterion_link.srcNodeId = nonstrict_criterion.nodeId 
    and nonstrict_criterion.sessionId = @session_id_
    and nonstrict_criterion.nodeTypeId is null
    and nonstrict_criterion.internalTypeId = 'tag'
left join tagT nonstrict_tag on nonstrict_criterion.nodeId = nonstrict_tag.id

left join attributeValueNumericT avn on sot.objectId = avn.objectId
left join searchCriterionT scn_ on avn.attributeId = scn_.nodeId and scn_.sessionId = @session_id_
left join searchCriterionNumericT scn on scn_.id = scn.searchCriterionId
    and avn.value >= coalesce(scn.valueLow, avn.value)
    and avn.value <= coalesce(scn.valueHigh, avn.value)

left join attributeValueDatetimeT avd on sot.objectId = avd.objectId
left join searchCriterionT scd_ on avd.attributeId = scd_.nodeId and scd_.sessionId = @session_id_
left join searchCriterionDatetimeT scd on scd_.id = scd.searchCriterionId
    and avd.value >= coalesce(scd.valueLow, avd.value)
    and avd.value <= coalesce(scd.valueHigh, avd.value)

group by sot.objectId;

    call __search_UpdateExtraObjects(@session_id);

    call __search_UpdateSortCache(@session_id);

    call __search_Finalize(@session_id);
                                    -- update searchResultT sr join searchUpdateExtraObjectsData sueod on sr.nodeId = sueod.tagId set
                                    --     sr.extraObjects = sueod.cnt
                                    -- where sr.sessionId = @session_id;

                                    -- drop temporary table searchObjectTemp;

                                    -- drop temporary table searchUpdateExtraObjectsData;

    -- commit;














































































































select n.id, n.name, nrl.*
from _nodeRecommendLink5V nrl
join nodeT n on nrl.srcNodeId = n.id
where nrl.dstNodeId = 206255;


select n.id, n.name, nrl.*
from _nodeRecommendLink6V nrl
join nodeT n on nrl.srcNodeId = n.id
where 1=1
--    and nrl.srcNodeTypeId >= 10
--    and nrl.srcNodeTypeId < 17
    and nrl.dstNodeId = 206255
order by n.id;


-- 
update allowedNodeLinkT set essential = 'y' where srcNodeTypeId = 3 and dstNodeTypeId = 7;

select n.id, n.name, nrl.*
from _nodeRecommendLink5V nrl
join nodeT n on nrl.srcNodeId = n.id
where nrl.dstNodeId = 96947;

select n.id, n.name, nrl.*
from _nodeRecommendLink6V nrl
join nodeT n on nrl.srcNodeId = n.id
where nrl.dstNodeId = 96947;
--


SELECT sTagV.id,
       sTagV.typeCode,
       sTagV.name,
       sTagV.uofmPre,
       sTagV.uofmPost,
       sTagV.ancestryNamesLang
       -- na.tagName AS ancestryParts
       -- match(sTagV.tagNamesFullSearch) against ('миколаївка* друг*' in boolean mode) rel
FROM nodeRecommendLinkV nrl
INNER JOIN tagForAdminV sTagV ON nrl.srcNodeId = sTagV.id
LEFT JOIN nodeAncestryRawV na ON na.nodeId = sTagV.id
AND na.languageId = sTagV.languageId
WHERE ((nrl.dstNodeId=206255)
       -- AND (sTagV.tagNamesSearch LIKE CONCAT('%', string_Normalize('тер'), '%')))
       AND (sTagV.tagNamesSearch LIKE '%терн%'))
       -- AND match(sTagV.tagNamesFullSearch) against (CONCAT(string_Normalize('миколаївка друга'), '*') in boolean mode))
       -- AND match(sTagV.tagNamesFullSearch) against ('миколаївка* друг*' in boolean mode))
    AND (sTagV.geoRoot = 1)
GROUP BY nrl.srcNodeId
ORDER BY sTagV.name;
-- ORDER BY sTagV.sorter, sTagV.name;






select cnrs.*, n.name, n.parentNodeTypeCount 
from cacheNodeRecommendSumT cnrs 
join nodeT n on cnrs.srcNodeId = n.id 
where cnrs.dstNodeId = 206251 and n.parentNodeTypeCount = cnrs.nodeTypeIdDistinctCount;


select srcnode.id, srcnode.name, srcnode.parentNodeTypeCount
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
where srcnode.parentNodeTypeCount =
    (select count(distinct n2.nodeTypeId)
        from nodeLinkForkSrcV nl2
        join nodeT n2 on nl2.srcNodeId = n2.id
        where nl2.nodeLinkTypeId = 1
            and nl2.dstNodeId1 = srcnode.id
            and nl2.metric1 = 1
            and nl2.dstNodeId2 = dstnode.id
            and nl2.metric2 = 1)
and dstnode.id = 206251;


select * 
from objectWithTagV
where objectId = 206251;



select
    t.id srcId,
    t.typeId srcTypeId,
    t.typeCode srcTypeCode,
    t.typeName srcTypeName,
    t.internalTypeId srcInternalTypeId,
    t.name srcName,
    t.attributeDataType srcAttributeDataType,
    t.attributeCode srcAttributeCode,
    t.uofmPre srcUofmPre,
    t.uofmPost srcUofmPost,

    nrl.linked,
--    canlink.n canLink,

    o.id objectId
from objectT o
join _nodeRecommendLink5V nrl on o.id = nrl.dstNodeId
join tagV t on nrl.srcNodeId = t.id
-- left join zzzIntsT canlink on canlink.n = 1 and nrl.linked is null
/* left join attributeV a on t.id = a.id
left join attributeDatetimeStorageFormatV adsf on a.id = adsf.id and t.languageId = adsf.languageId
left join attributeValueNumericT avn on a.id = avn.attributeId and o.id = avn.objectId
left join attributeValueDatetimeT avd on a.id = avd.attributeId and o.id = avd.objectId
left join attributeValueTextT avt on a.id = avt.attributeId and o.id = avt.objectId and t.languageId = avt.languageId */
where o.id = 206250;


select
    -- nlt.id nodeLinkTypeId,
    -- nlt.code nodeLinkTypeCode,
    srcnode.id srcNodeId,
    srcnode.nodeTypeId srcNodeTypeId,
    srcnode.name,
    -- dstnode.id dstNodeId,
    -- dstnode.nodeTypeId dstNodeTypeId,
    anl.essential,
    anl.singleSrc,
    -- link.metric linked,
    -- cache.srcNodeId,
    -- forkallow.essential,
    fork.srcNodeId,
    fork.metric1,
    fork.intermediate1,
    fork.intermediateRecommendSingle1 intRecSng1,
    fork.intermediateRecommendMultiple1 intRecMlt1,
    fork.metric2,
    fork.intermediate2,
    fork.intermediateRecommendSingle2 intRecSng2,
    fork.intermediateRecommendMultiple2 intRecMlt2
    
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

join nodeLinkForkSrcV fork on nlt.id = fork.nodeLinkTypeId
    and srcnode.id = fork.dstNodeId1
    and dstnode.id = fork.dstNodeId2
    and fork.metric1 = 1
    and fork.metric2 = 1

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 nodeT forknode on link.metric is null and fork.srcNodeId = forknode.id 
    left join allowedNodeLinkT forkallow on link.metric is null 
        and forknode.nodeTypeId = forkallow.srcNodeTypeId 
        and srcnode.nodeTypeId = forkallow.dstNodeTypeId
        and anl.singleSrc = forkallow.singleSrc
*/
/*
    left join cacheNodeRecommendSumT cache on 1=1
        and link.metric is null
        and srcnode.id = cache.srcNodeId 
        and dstnode.id = cache.dstNodeId
        and srcnode.parentNodeTypeCount != cache.nodeTypeIdDistinctCount
*/
left join zzzIntsT z on z.n = 0
    and link.metric is null
    and anl.singleSrc = 'n'
    and srcnode.parentNodeTypeCount !=
    (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 exceptNodeLinkNodeNodeTypeT exceptNodeNodeType on srcnode.id = exceptNodeNodeType.srcNodeId 
    and dstnode.nodeTypeId = exceptNodeNodeType.dstNodeTypeId

where 1=1
    -- and cache.srcNodeId is null
    and link.metric is null
    and z.n is null
    -- and z1.n is null
    -- and z2.n is null
    -- and fork.intermediate2 = 0
    -- and fork.intermediateRecommend1 = 0

    and exceptNodeNodeType.srcNodeId is null

-- and srcnode.nodeTypeId >= 10
-- and srcnode.nodeTypeId < 17
and dstnode.id = 206255
order by srcnode.id, fork.srcNodeId;





































































































SELECT tag_Update('88224', NULL, NULL, 'y', NULL, 'y', NULL, 'y', 1, '0', 'y');

    set @tag_id_ = 88224;
    set @sorter_ = null;
    set @can_search_ = null; set @reset_can_search_ = 'y';
    set @display_in_object_ = null; set @reset_display_in_object_ = 'y';
    set @display_in_object_list_ = null; set @reset_display_in_object_list_ = 'y';
    set @factor_ = 1;
    set @rating_points_ = '0';
    set @enabled_ = 'y';

    set @1 = __tag_ValidateId(@tag_id_);

/*    set @result = __tag_Update(
                    @tag_id_, 
                    @sorter_, 
                    @can_search_, @reset_can_search_, 
                    @display_in_object_, @reset_display_in_object_, 
                    @display_in_object_list_, @reset_display_in_object_list_, 
                    @factor_, 
                    @rating_points_,
                    @enabled_);*/
                set @1 = __node_Update(@tag_id_, null);
                
                update tagT set
                    sorter = coalesce(@sorter_, sorter),
                    canSearchRaw = if(@reset_can_search_ = 'y', null, coalesce(@can_search_, canSearchRaw)),
                    displayInObjectRaw = if(@reset_display_in_object_ = 'y', null, coalesce(@display_in_object_, displayInObjectRaw)),
                    displayInObjectListRaw = if(@reset_display_in_object_list_ = 'y', null, coalesce(@display_in_object_list_, displayInObjectListRaw)),
                    factor = __tag_ClampFactor(coalesce(@factor_, factor)),
                    ratingPoints = __tag_ClampRatingPoints(coalesce(@rating_points_, ratingPoints)),
                    enabled = coalesce(@enabled_, enabled)
                where id = @tag_id_;
                
                set @2 = __tag_UpdateDefaults(@tag_id_, null);

    set @2 = nodeLink_UpdateStart();
    set @3 =  __nodeLink_UpdateWorkSet(@tag_id_, 1, 1, 0); -- give a chance to update cache of objects
    
--    set @4 = nodeLink_UpdateEnd();
                select __nodeWorkset_GetLevel();
                if __nodeWorkset_GetLevel() = 1 then
                    set @1 = __nodeLink_UpdateRefreshExtraLinks();
                    
                    -- FORWARD: __node_UpdateCache is defined in _030_node
                    set @2 = __node_UpdateCache();
                    
                    set @3 = __nodeLink_UpdateCache();
                    
                    set @4 = __nodeLink_UpdateRecommendCache1();

                    -- make an exception for edited object - if there is 1 object, update its address and ratings right away
                    if (select count(*) from zzzNodeWorksetV nw join objectT o on nw.nodeId = o.id where nw.isMain = 1) = 1 then
                        select o.id into node_id from zzzNodeWorksetV nw join objectT o on nw.nodeId = o.id where nw.isMain = 1 limit 1;

                        -- FORWARD: __object_UpdateAddressesAll and __object_UpdateRatingsAll are defined in _080_object
                        set @1 = node_id is not null and __object_UpdateAddressesAll(node_id); -- and __object_UpdateRatingsAll(node_id);
                    end if;
                    
                    -- put into the queue for the maintenance routine to work with
                    insert zzzNodeQueueT(nodeId, isLinked)
                    select nw.nodeId, nw.isLinked
                    from zzzNodeWorksetV nw
                    where nw.isMain = 1;

                    -- set flag for maintenance routine
                    set @5 = const_SetI('search.cache.update', 1);
                end if;
                set @6 = __nodeWorkset_End();








































































select 
    n.id,
    n.name,
    n.nodeTypeId,
    nrl.*
from _nodeRecommendLink5V nrl
join nodeT n on nrl.srcNodeId = n.id
where nrl.dstNodeId = 206207;






select 
    srcnode.id, 
    dstnode.id, 
    count(distinct node.nodeTypeId)
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

join nodeLinkForkSrcV links on links.nodeLinkTypeId = nlt.id
    and links.dstNodeId1 = srcnode.id
    and links.dstNodeId2 = dstnode.id
    and links.metric1 = 1
    and links.metric2 = 1
join nodeT node on links.srcNodeId = node.id

-- where srcnode.id = 30
group by srcnode.id, dstnode.id
limit 2;

select 
    srcnode.id, 
    dstnode.id, 
    count(distinct node.nodeTypeId)
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

join nodeLinkForkSrcV links on links.nodeLinkTypeId = nlt.id
    and links.dstNodeId1 = srcnode.id
    and links.dstNodeId2 = dstnode.id
    and links.metric1 = 1
    and links.metric2 = 1
join nodeT node on links.srcNodeId = node.id

where dstnode.id = 30
group by srcnode.id, dstnode.id;

-- --------------------------

create table cacheNodeRecommendSumT (
    id bigint unsigned not null primary key auto_increment,
    srcNodeId int unsigned not null,
    dstNodeId int unsigned not null,
    nodeTypeIdDistinctCount int unsigned not null default 0,
    constraint src_dst_uq unique(srcNodeId, dstNodeId),
    constraint src_fk foreign key (srcNodeId) references nodeT(id) on delete cascade on update no action,
    constraint dst_fk foreign key (dstNodeId) references nodeT(id) on delete cascade on update no action
) engine=InnoDB;



delimiter $$$

drop function if exists __nodeLink_UpdateRecommendCache$$$

create function __nodeLink_UpdateRecommendCache(src_node_id int unsigned, dst_node_id int unsigned) returns int unsigned
deterministic
begin
    if src_node_id is not null and dst_node_id is not null then
        insert cacheNodeRecommendSumT(srcNodeId, dstNodeId, nodeTypeIdDistinctCount)
        select 
            srcnode.id, 
            dstnode.id, 
            count(distinct node.nodeTypeId)
        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

        join nodeLinkForkSrcV links on links.nodeLinkTypeId = nlt.id
            and links.dstNodeId1 = srcnode.id
            and links.dstNodeId2 = dstnode.id
            and links.metric1 = 1
            and links.metric2 = 1
        join nodeT node on links.srcNodeId = node.id

        where anl.singleSrc = 'n'
            and srcnode.id = src_node_id
            and dstnode.id = dst_node_id
        group by srcnode.id, dstnode.id
        on duplicate key update
            cacheNodeRecommendSumT.nodeTypeIdDistinctCount = values(nodeTypeIdDistinctCount);
    elseif src_node_id is not null then
        insert cacheNodeRecommendSumT(srcNodeId, dstNodeId, nodeTypeIdDistinctCount)
        select 
            srcnode.id, 
            dstnode.id, 
            count(distinct node.nodeTypeId)
        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

        join nodeLinkForkSrcV links on links.nodeLinkTypeId = nlt.id
            and links.dstNodeId1 = srcnode.id
            and links.dstNodeId2 = dstnode.id
            and links.metric1 = 1
            and links.metric2 = 1
        join nodeT node on links.srcNodeId = node.id

        where anl.singleSrc = 'n'
            and srcnode.id = src_node_id
        group by srcnode.id, dstnode.id
        on duplicate key update
            cacheNodeRecommendSumT.nodeTypeIdDistinctCount = values(nodeTypeIdDistinctCount);
    elseif dst_node_id is not null then
        insert cacheNodeRecommendSumT(srcNodeId, dstNodeId, nodeTypeIdDistinctCount)
        select 
            srcnode.id, 
            dstnode.id, 
            count(distinct node.nodeTypeId)
        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

        join nodeLinkForkSrcV links on links.nodeLinkTypeId = nlt.id
            and links.dstNodeId1 = srcnode.id
            and links.dstNodeId2 = dstnode.id
            and links.metric1 = 1
            and links.metric2 = 1
        join nodeT node on links.srcNodeId = node.id

        where anl.singleSrc = 'n'
            and dstnode.id = dst_node_id
        group by srcnode.id, dstnode.id
        on duplicate key update
            cacheNodeRecommendSumT.nodeTypeIdDistinctCount = values(nodeTypeIdDistinctCount);
    end if;
    
    return row_count();
end$$$

delimiter ;


        select 
            srcnode.id, 
            dstnode.id, 
            count(distinct node.nodeTypeId)
        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

        join nodeLinkForkSrcV links on links.nodeLinkTypeId = nlt.id
            and links.dstNodeId1 = srcnode.id
            and links.dstNodeId2 = dstnode.id
            and links.metric1 = 1
            and links.metric2 = 1
        join nodeT node on links.srcNodeId = node.id

        where anl.singleSrc = 'n'
--            and dstnode.id in (96222, 206207)
            and dstnode.id in (96902)
        group by srcnode.id, dstnode.id;


select 
    n.id,
    n.name,
    n.nodeTypeId,
    nrl.essential,
    nrl.singleSrc,
    nrl.linked,
    nrl.canLink
from _nodeRecommendLink4V nrl
join nodeT n on nrl.srcNodeId = n.id
where nrl.dstNodeId = 96222
order by n.id desc;

select 
    n.id,
    n.name,
    n.nodeTypeId,
    nrl.essential,
    nrl.singleSrc,
    nrl.linked,
    nrl.canLink
from _nodeRecommendLink4V nrl
join nodeT n on nrl.srcNodeId = n.id
where nrl.dstNodeId = 96223
order by n.id desc;

select 
    n.id,
    n.name,
    n.nodeTypeId,
    nrl.essential,
    nrl.singleSrc,
    nrl.linked,
    nrl.canLink
from _nodeRecommendLink4V nrl
join nodeT n on nrl.srcNodeId = n.id
where nrl.dstNodeId = 96224
order by n.id desc;

select * 
from objectWithTagV
where objectId = 96222


/*
select
    nlt.id nodeLinkTypeId,
    nlt.code nodeLinkTypeCode,
    srcnode.id srcNodeId,
    srcnode.nodeTypeId srcNodeTypeId,
    dstnode.id dstNodeId,
    dstnode.nodeTypeId dstNodeTypeId,
    anl.essential,
    anl.singleSrc,
    link.metric linked,
    canlink.n canLink
*/

                                            select
                                                srcnode.id, 
                                                srcnode.name,
                                                srcnode.nodeTypeId,
                                                anl.essential,
                                                anl.singleSrc,
                                                link.metric linked,
                                                canlink.n 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 = srcnode.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
                                            left join exceptNodeLinkNodeNodeTypeT exceptNodeNodeType on srcnode.id = exceptNodeNodeType.srcNodeId 
                                                and dstnode.nodeTypeId = exceptNodeNodeType.dstNodeTypeId
                                            where srcTemplate.dstNodeId is null
                                                and dstTemplate.dstNodeId is null
                                                and exceptNodeNodeType.srcNodeId is null
                                                and (canlink.n = 1 or link.metric = 1)
                                            and dstnode.id = 96222
                                            -- and dstnode.id = 96223
                                            -- and dstnode.id = 96224
                                            order by srcnode.id desc;


                                            select 
                                                srcnode.id, 
                                                srcnode.name,
                                                srcnode.nodeTypeId,
                                                anl.essential,
                                                anl.singleSrc,
                                                link.metric linked,
                                                canlink.n canLink,

                                                fork.srcNodeId,
                                                fork.dstNodeId1,
                                                fork.dstNodeId2

                                            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

                                            join nodeLinkForkSrcV fork on nlt.id = fork.nodeLinkTypeId
                                                and srcnode.id = fork.dstNodeId1
                                                and dstnode.id = fork.dstNodeId2
                                                and fork.metric1 = 1
                                                and fork.metric2 = 1
                                                -- and link.metric is null

                                            join nodeT forknode on fork.srcNodeId = forknode.id 
                                            join allowedNodeLinkT forkallow on forknode.nodeTypeId = forkallow.srcNodeTypeId 
                                                and srcnode.nodeTypeId = forkallow.dstNodeTypeId
                                                and anl.singleSrc = forkallow.singleSrc

                                            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 nodeLinkChainTransitionV srctrans on link.metric is null
                                                and anl.singleSrc = 'y'
                                                and forkallow.essential = 'n'
                                                and nlt.id = srctrans.nodeLinkTypeId
                                                and fork.srcNodeId = srctrans.srcNodeId
                                                and fork.dstNodeId1 = srctrans.dstNodeId
                                                
                                            left join nodeLinkChainTransitionV dsttrans on link.metric is null
                                                and anl.singleSrc = 'y'
                                                and nlt.id = dsttrans.nodeLinkTypeId
                                                and fork.srcNodeId = dsttrans.srcNodeId
                                                and fork.dstNodeId2 = dsttrans.dstNodeId
                                                
                                            /*
                                                left join nodeLinkForkSrcV exifork on nlt.id = exifork.nodeLinkTypeId
                                                    and srcnode.id = exifork.dstNodeId1
                                                    and dstnode.id = exifork.dstNodeId2
                                                    and exifork.metric1 = 1
                                                    and exifork.metric2 = 1
                                                    and exifork.srcNodeId > fork.srcNodeId
                                            */
                                            left join templateT srcTemplate on srcnode.id = srcTemplate.dstNodeId
                                            left join templateT dstTemplate on dstnode.id = dstTemplate.dstNodeId
                                            left join exceptNodeLinkNodeNodeTypeT exceptNodeNodeType on srcnode.id = exceptNodeNodeType.srcNodeId 
                                                and dstnode.nodeTypeId = exceptNodeNodeType.dstNodeTypeId

                                            left join zzzIntsT canlink on canlink.n = 1 and link.metric is null

                                            where 1=1
                                                and (anl.singleSrc = 'y' or
                                                    ((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)))
                                                -- and (link.metric = 1 or fork.metric2 = 1)
                                                and srcTemplate.dstNodeId is null
                                                and dstTemplate.dstNodeId is null
                                                and exceptNodeNodeType.srcNodeId is null
                                                and srctrans.midNodeId is null
                                                and dsttrans.midNodeId is null
                                                -- and exifork.srcNodeId is null
                                            and dstnode.id = 96222
                                            -- and dstnode.id = 96223
                                            -- and dstnode.id = 96224
                                            -- group by srcnode.id, dstnode.id
                                            -- having count(*) > 1
                                            order by srcnode.id desc;

                                            select 
                                                srcnode.id, 
                                                srcnode.name,
                                                srcnode.nodeTypeId,
                                                anl.essential,
                                                anl.singleSrc,
                                                link.metric linked,
                                                canlink.n canLink,

                                                fork.srcNodeId,
                                                fork.dstNodeId1,
                                                fork.dstNodeId2

                                            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

                                            join nodeLinkForkSrcV fork on nlt.id = fork.nodeLinkTypeId
                                                and srcnode.id = fork.dstNodeId1
                                                and dstnode.id = fork.dstNodeId2
                                                and fork.metric1 = 1
                                                and fork.metric2 = 1
                                                -- and link.metric is null

                                            join nodeT forknode on fork.srcNodeId = forknode.id 
                                            join allowedNodeLinkT forkallow on forknode.nodeTypeId = forkallow.srcNodeTypeId 
                                                and srcnode.nodeTypeId = forkallow.dstNodeTypeId
                                                and anl.singleSrc = forkallow.singleSrc

                                            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 nodeLinkChainTransitionV srctrans on link.metric is null
                                                and anl.singleSrc = 'y'
                                                and forkallow.essential = 'n'
                                                and nlt.id = srctrans.nodeLinkTypeId
                                                and fork.srcNodeId = srctrans.srcNodeId
                                                and fork.dstNodeId1 = srctrans.dstNodeId
                                                
                                            left join nodeLinkChainTransitionV dsttrans on link.metric is null
                                                and anl.singleSrc = 'y'
                                                and nlt.id = dsttrans.nodeLinkTypeId
                                                and fork.srcNodeId = dsttrans.srcNodeId
                                                and fork.dstNodeId2 = dsttrans.dstNodeId
                                                
                                            /*
                                                left join nodeLinkForkSrcV exifork on nlt.id = exifork.nodeLinkTypeId
                                                    and srcnode.id = exifork.dstNodeId1
                                                    and dstnode.id = exifork.dstNodeId2
                                                    and exifork.metric1 = 1
                                                    and exifork.metric2 = 1
                                                    and exifork.srcNodeId > fork.srcNodeId
                                            */
                                            left join templateT srcTemplate on srcnode.id = srcTemplate.dstNodeId
                                            left join templateT dstTemplate on dstnode.id = dstTemplate.dstNodeId
                                            left join exceptNodeLinkNodeNodeTypeT exceptNodeNodeType on srcnode.id = exceptNodeNodeType.srcNodeId 
                                                and dstnode.nodeTypeId = exceptNodeNodeType.dstNodeTypeId

                                            left join zzzIntsT canlink on canlink.n = 1 and link.metric is null

                                            where 1=1
                                                and (link.metric is not null or 
                                                        (anl.singleSrc = 'y' or
                                                            (srcnode.parentNodeTypeCount 
                                                                =
                                                            (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))
                                                        )
                                                    )
                                                -- and (link.metric = 1 or fork.metric2 = 1)
                                                and srcTemplate.dstNodeId is null
                                                and dstTemplate.dstNodeId is null
                                                and exceptNodeNodeType.srcNodeId is null
                                                and srctrans.midNodeId is null
                                                and dsttrans.midNodeId is null
                                                -- and exifork.srcNodeId is null
                                            -- and dstnode.id = 96222
                                            and dstnode.id = 96223
                                            -- and dstnode.id = 96224
                                            group by srcnode.id, dstnode.id
                                            /*group by srcnode.id, srcnode.parentNodeTypeCount, dstnode.id
                                            having anl.singleSrc = 'y' or
                                                    (srcnode.parentNodeTypeCount = count(distinct forknode.nodeTypeId))*/
                                            order by srcnode.id desc;

    
    
    
    
select 
    srcnode.id, 
    srcnode.name,
    srcnode.nodeTypeId,
    anl.essential,
    anl.singleSrc,
    link.metric linked,
    canlink.n canLink,

    fork.srcNodeId,
    fork.dstNodeId1,
    fork.dstNodeId2

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

join nodeLinkForkSrcV fork on nlt.id = fork.nodeLinkTypeId
    and srcnode.id = fork.dstNodeId1
    and dstnode.id = fork.dstNodeId2
    and fork.metric1 = 1
    and fork.metric2 = 1

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 nodeT forknode on link.metric is null and fork.srcNodeId = forknode.id 
left join allowedNodeLinkT forkallow on link.metric is null 
    and forknode.nodeTypeId = forkallow.srcNodeTypeId 
    and srcnode.nodeTypeId = forkallow.dstNodeTypeId
    and anl.singleSrc = forkallow.singleSrc

left join templateT srcTemplate on srcnode.id = srcTemplate.dstNodeId
left join templateT dstTemplate on dstnode.id = dstTemplate.dstNodeId
left join exceptNodeLinkNodeNodeTypeT exceptNodeNodeType on srcnode.id = exceptNodeNodeType.srcNodeId 
    and dstnode.nodeTypeId = exceptNodeNodeType.dstNodeTypeId
/*
left join nodeLinkForkSrcV no_fork on nlt.id = no_fork.nodeLinkTypeId
    and srcnode.id = no_fork.dstNodeId1
    and dstnode.id = no_fork.dstNodeId2
    and no_fork.metric1 = 1
    and no_fork.metric2 = 1
    and fork.srcNodeId > no_fork.srcNodeId
*/
left join zzzIntsT canlink on canlink.n = 1 and link.metric is null

where 1=1
    and (link.metric is not null 
        or (
                anl.singleSrc = 'y'
                and fork.intermediate2 = 0
                and (forkallow.essential = 'y' or fork.intermediate1 = 0)
        )
        or (
                anl.singleSrc = 'n'
                and srcnode.parentNodeTypeCount =
                (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)
        )
    )
    and srcTemplate.dstNodeId is null
    and dstTemplate.dstNodeId is null
    and exceptNodeNodeType.srcNodeId is null
--    and no_fork.srcNodeId is null
-- and dstnode.id = 96221
-- and dstnode.id = 96222
-- and dstnode.id = 96223
-- and dstnode.id = 96224
and dstnode.id = 206207
-- group by srcnode.id, dstnode.id
order by srcnode.id desc;



select 
    srcnode.id, 
    srcnode.name,
    srcnode.nodeTypeId,
    anl.essential,
    anl.singleSrc,
    link.metric linked,
    canlink.n canLink,

    fork.srcNodeId,
    fork.dstNodeId1,
    fork.dstNodeId2

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 cacheNodeRecommendSumT cache on srcnode.id = cache.srcNodeId and dstnode.id = cache.dstNodeId

join nodeLinkForkSrcV fork on nlt.id = fork.nodeLinkTypeId
    and srcnode.id = fork.dstNodeId1
    and dstnode.id = fork.dstNodeId2
    and fork.metric1 = 1
    and fork.metric2 = 1

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 nodeT forknode on link.metric is null and fork.srcNodeId = forknode.id 
left join allowedNodeLinkT forkallow on link.metric is null 
    and forknode.nodeTypeId = forkallow.srcNodeTypeId 
    and srcnode.nodeTypeId = forkallow.dstNodeTypeId
    and anl.singleSrc = forkallow.singleSrc

left join templateT srcTemplate on srcnode.id = srcTemplate.dstNodeId
left join templateT dstTemplate on dstnode.id = dstTemplate.dstNodeId
left join exceptNodeLinkNodeNodeTypeT exceptNodeNodeType on srcnode.id = exceptNodeNodeType.srcNodeId 
    and dstnode.nodeTypeId = exceptNodeNodeType.dstNodeTypeId
/*
left join nodeLinkForkSrcV no_fork on nlt.id = no_fork.nodeLinkTypeId
    and srcnode.id = no_fork.dstNodeId1
    and dstnode.id = no_fork.dstNodeId2
    and no_fork.metric1 = 1
    and no_fork.metric2 = 1
    and fork.srcNodeId > no_fork.srcNodeId
*/
left join zzzIntsT canlink on canlink.n = 1 and link.metric is null

where 1=1
    and (link.metric is not null 
        or (
                anl.singleSrc = 'y'
                and fork.intermediate2 = 0
                and (forkallow.essential = 'y' or fork.intermediate1 = 0)
        )
        or (
                anl.singleSrc = 'n'
                and srcnode.parentNodeTypeCount = cache.nodeTypeIdDistinctCount
        )
    )
    and srcTemplate.dstNodeId is null
    and dstTemplate.dstNodeId is null
    and exceptNodeNodeType.srcNodeId is null
--    and no_fork.srcNodeId is null
-- and dstnode.id = 96221
-- and dstnode.id = 96222
-- and dstnode.id = 96223
-- and dstnode.id = 96224
and dstnode.id = 206207
-- group by srcnode.id, dstnode.id
order by srcnode.id desc;

/*
    select * from nodeLinkChainTransitionV where dstNodeId = 96222 and srcNodeId = 3;
    select * from nodeLinkForkSrcV where dstNodeId1 in (27, 28, 29) and dstNodeId2 = 96222;
    select 
        fork.srcNodeId,
        fork.dstNodeId1,
        fork.dstNodeId2,
        chain1.midNodeId,
        chain2.midNodeId
    from nodeLinkForkSrcV fork 
    left join nodeLinkChainTransitionV chain1 on fork.srcNodeId = chain1.srcNodeId and fork.dstNodeId1 = chain1.dstNodeId
    left join nodeLinkChainTransitionV chain2 on fork.srcNodeId = chain2.srcNodeId and fork.dstNodeId2 = chain2.dstNodeId
    where fork.dstNodeId1 in (27, 28, 29) 
        and fork.dstNodeId2 = 96222;

    select * from nodeLinkT where dstNodeId in (25, 26, 27, 28, 29, 30, 31, 32, 96222);
*/

select
        (select count(distinct n1.nodeTypeId)
            from nodeLinkT nl1
            join nodeT n1 on nl1.srcNodeId = n1.id
            where nl1.nodeLinkTypeId = 1
                and nl1.dstNodeId = 30
                and nl1.metric = 1) c11,
        (select count(distinct n2.nodeTypeId)
            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 = 96222
                and nl2.metric2 = 1) c12,

        (select count(distinct n1.nodeTypeId)
            from nodeLinkT nl1
            join nodeT n1 on nl1.srcNodeId = n1.id
            where nl1.nodeLinkTypeId = 1
                and nl1.dstNodeId = 31
                and nl1.metric = 1) c21,
        (select count(distinct n2.nodeTypeId)
            from nodeLinkForkSrcV nl2
            join nodeT n2 on nl2.srcNodeId = n2.id
            where nl2.nodeLinkTypeId = 1
                and nl2.dstNodeId1 = 31
                and nl2.metric1 = 1
                and nl2.dstNodeId2 = 96222
                and nl2.metric2 = 1) c22,

        (select count(distinct n1.nodeTypeId)
            from nodeLinkT nl1
            join nodeT n1 on nl1.srcNodeId = n1.id
            where nl1.nodeLinkTypeId = 1
                and nl1.dstNodeId = 32
                and nl1.metric = 1) c31,
        (select count(distinct n2.nodeTypeId)
            from nodeLinkForkSrcV nl2
            join nodeT n2 on nl2.srcNodeId = n2.id
            where nl2.nodeLinkTypeId = 1
                and nl2.dstNodeId1 = 32
                and nl2.metric1 = 1
                and nl2.dstNodeId2 = 96222
                and nl2.metric2 = 1) c32;














































































select search_StartSession('1', null);
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) and search_SetTag(31) and search_SetTag(52);
select search_StartSession('1', null);

select search_StartSession('1', null) and search_SetTag(30);

call search_Search();



select search_StartSession('1', null);
select search_SetTag(30) and search_SetTag(9);
select search_SetTag(30) and search_SetTag(9) and search_SetTag(32);
select search_SetTag(9) and search_SetTag(25) and search_SetTag(30);
select search_SetTag(9) and search_SetTag(25) and search_SetTag(30) and search_SetTag(32);

    set @session_id = search_CurrentId();
    select @session_id;
    
    set @1 = __search_RectifyCriteria(@session_id);

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

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

    select count(*) into @count_numerics
    from searchCriterionT sc
    join searchCriterionNumericT scn on sc.id = scn.searchCriterionId
    where sc.sessionId = @session_id;
    select @count_numerics;

    select count(*) into @count_datetimes
    from searchCriterionT sc
    join searchCriterionDatetimeT scd on sc.id = scd.searchCriterionId
    where sc.sessionId = @session_id;
    select @count_datetimes;

    -- set search_objects = __search_CheckRequiredForObject(@session_id);

    select * from searchResultT where sessionId = @session_id;
    delete from searchResultT where sessionId = @session_id;

    set @1 = __search_SetCachedResult(@session_id);

    call __search_SearchTags(@session_id, @count_notnull, @count_null);
                        insert ignore searchResultT(sessionId, nodeId)
                        select @session_id, n.id
                        from nodeT n
                        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
                        join tagT t on n.id = t.id and t.canSearch = 'y'
                        join nodeLinkT nl1 on nl1.nodeLinkTypeId = nlt.id
                            and nl1.dstNodeId = n.id
                            and nl1.metric = 1
                        join searchCriterionT sc1 on nl1.srcNodeId = sc1.nodeId and sc1.sessionId = @session_id
                        left join nodeLinkT nl2 on nl2.nodeLinkTypeId = nlt.id
                            and nl2.dstNodeId = n.id
                            and nl2.metric = 1
                        left join tagT t2 on nl2.srcNodeId = t2.id
                            and t2.canSearch = 'y'
                        left join nodeT n2 on t2.id = n2.id
                        group by n.id
                        having count(distinct n2.nodeTypeId) = count(distinct sc1.nodeTypeId);


select sr.*, n.name, n.nodeTypeId from searchResultV sr join nodeT n on sr.nodeId = n.id;
select sr.*, n.name, n.nodeTypeId from searchResultV sr join nodeT n on sr.nodeId = n.id where n.nodeTypeId not in (8, 21);

-- -----------------------------------------------------------------------
    select 
        result_node.id,
        result_node.name,
        result_node.nodeTypeId
    from searchResultT sr
    join tagT result_tag on sr.nodeId = result_tag.id and sr.sessionId = @session_id
    join nodeT result_node on sr.nodeId = result_node.id
    join nodeTypeT result_node_type on result_node.nodeTypeId = result_node_type.id and result_node_type.searchAnd = 'n'

    where exists(
        select 1
        from nodeT object_node
        join objectT object on object_node.id = object.id
        join objectStatusT os on object.statusId = os.id and os.public = 'y'
        join nodeLinkTypeT nlt on nlt.isDefault = 'y'

        join nodeLinkT result_link on nlt.id = result_link.nodeLinkTypeId
            and object_node.id = result_link.dstNodeId
            and result_link.metric = 1

        join nodeLinkT criterion_link on nlt.id = criterion_link.nodeLinkTypeId
            and object_node.id = criterion_link.dstNodeId
            and criterion_link.metric = 1
        join searchCriterionT criterion on criterion_link.srcNodeId = criterion.nodeId

        join searchCriterionT source_criterion on criterion.sessionId = source_criterion.sessionId
            and source_criterion.nodeTypeId is not null
            and source_criterion.internalTypeId = 'tag'

        where criterion.sessionId = sr.sessionId
            and criterion.nodeTypeId != result_node.nodeTypeId
            and source_criterion.nodeTypeId != result_node.nodeTypeId
            and result_link.srcNodeId = result_node.id
        
        group by object_node.id
        having count(distinct source_criterion.nodeTypeId) = count(distinct criterion.nodeTypeId)
    );
-- -----------------------------------------------------------------------
-- !!!!!!
        call __search_Prepare(@session_id);

        insert searchCriterionNumericTemp(objectId, nodeId)
        select avn.objectId, avn.attributeId
        from attributeValueNumericT avn 
        join objectT o on avn.objectId = o.id
        join objectStatusT os on o.statusId = os.id
        join searchCriterionT sc on avn.attributeId = sc.nodeId
        join searchCriterionNumericT scn on sc.id = scn.searchCriterionId
        where sc.sessionId = @session_id
            and os.public = 'y'
            and avn.value >= coalesce(scn.valueLow, avn.value)
            and avn.value <= coalesce(scn.valueHigh, avn.value);

        -- NOTE: searchCriterionDatetimeTemp created in __search_Prepare
        insert searchCriterionDatetimeTemp(objectId, nodeId)
        select avd.objectId, avd.attributeId
        from attributeValueDatetimeT avd 
        join objectT o on avd.objectId = o.id
        join objectStatusT os on o.statusId = os.id
        join searchCriterionT sc on avd.attributeId = sc.nodeId
        join searchCriterionDatetimeT scd on sc.id = scd.searchCriterionId
        where sc.sessionId = @session_id
            and os.public = 'y'
            and avd.value >= coalesce(scd.valueLow, avd.value)
            and avd.value <= coalesce(scd.valueHigh, avd.value);

        set @count_numerics = greatest(@count_numerics, 1);
        set @count_datetimes = greatest(@count_datetimes, 1);

        insert ignore searchResultT(sessionId, nodeId, relevancy)
        select 
            @session_id, 
            n.id, 
            (
                (coalesce(sum(nonstrict_tag.factor), 0.001) / @sum_null -- sum_null_ must be sanitized to be != 0
                + (count(numeric_criterion.nodeId) / @count_numerics)
                + (count(datetime_criterion.nodeId) / @count_datetimes))
            / 3) relevancy
        from nodeT n
        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
        join objectT o on n.id = o.id
        join objectStatusT os on o.statusId = os.id

        join nodeLinkT strict_criterion_link on nlt.id = strict_criterion_link.nodeLinkTypeId
            and n.id = strict_criterion_link.dstNodeId
            and strict_criterion_link.metric = 1
        join searchCriterionT strict_criterion on strict_criterion_link.srcNodeId = strict_criterion.nodeId                        
            and strict_criterion.nodeTypeId is not null
            and strict_criterion.internalTypeId = 'tag'

        left join nodeLinkT nonstrict_criterion_link on nlt.id = nonstrict_criterion_link.nodeLinkTypeId 
            and n.id = nonstrict_criterion_link.dstNodeId 
            and nonstrict_criterion_link.metric = 1
        left join searchCriterionT nonstrict_criterion on nonstrict_criterion_link.srcNodeId = nonstrict_criterion.nodeId 
            and nonstrict_criterion.sessionId = strict_criterion.sessionId
            and nonstrict_criterion.nodeTypeId is null
            and nonstrict_criterion.internalTypeId = 'tag'
        left join tagT nonstrict_tag on nonstrict_criterion.nodeId = nonstrict_tag.id
        
        left join searchCriterionNumericTemp numeric_criterion on n.id = numeric_criterion.objectId

        left join searchCriterionDatetimeTemp datetime_criterion on n.id = datetime_criterion.objectId

        where strict_criterion.sessionId = @session_id
            and os.public = 'y'

        group by n.id

        having count(distinct strict_criterion.nodeTypeId) = @count_notnull and relevancy >= 0.001
        order by relevancy desc, o.rating desc;
--        limit limit_dataset;

    insert searchUpdateExtraObjectsData(tagId, cnt)
    select result_node.id, 1
    from searchResultT sr
    join tagT result_tag on sr.nodeId = result_tag.id and sr.sessionId = @session_id
    join nodeT result_node on sr.nodeId = result_node.id
    join nodeTypeT result_node_type on result_node.nodeTypeId = result_node_type.id and result_node_type.searchAnd = 'n'
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'

    join nodeLinkT result_link on nlt.id = result_link.nodeLinkTypeId
        and result_node.id = result_link.srcNodeId
        and result_link.metric = 1

    join nodeT object_node on result_link.dstNodeId = object_node.id
    join objectT object on object_node.id = object.id
    join objectStatusT os on object.statusId = os.id and os.public = 'y'

    join nodeLinkT criterion_link on nlt.id = criterion_link.nodeLinkTypeId
        and object_node.id = criterion_link.dstNodeId
        and criterion_link.metric = 1
    join searchCriterionT criterion on sr.sessionId = criterion.sessionId
        and criterion_link.srcNodeId = criterion.nodeId
        and criterion.nodeTypeId != result_node.nodeTypeId

    join searchCriterionT source_criterion on sr.sessionId = source_criterion.sessionId
        and source_criterion.nodeTypeId is not null
        and source_criterion.internalTypeId = 'tag'
        and source_criterion.nodeTypeId != result_node.nodeTypeId

    join searchCriterionT type_criterion on sr.sessionId = type_criterion.sessionId and result_node.nodeTypeId = type_criterion.nodeTypeId

    left join searchCriterionT no_criterion on sr.sessionId = no_criterion.sessionId and sr.nodeId = no_criterion.nodeId

    where no_criterion.nodeId is null

    group by result_node.id
    having count(distinct source_criterion.nodeTypeId) = count(distinct criterion.nodeTypeId)
    on duplicate key update
        searchUpdateExtraObjectsData.cnt = 1;


    insert searchUpdateExtraObjectsData(tagId, cnt)
    select result_node.id, 1
    from searchResultT sr
    join tagT result_tag on sr.nodeId = result_tag.id and sr.sessionId = @session_id
    join nodeT result_node on sr.nodeId = result_node.id
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'

    join nodeLinkT result_link on nlt.id = result_link.nodeLinkTypeId
        and result_node.id = result_link.srcNodeId
        and result_link.metric = 1

    join nodeT object_node on result_link.dstNodeId = object_node.id
    join searchResultT object_result on sr.sessionId = object_result.sessionId and object_node.id = object_result.nodeId
    join objectT object on object_node.id = object.id
    join objectStatusT os on object.statusId = os.id and os.public = 'y'

    left join searchCriterionT no_criterion on sr.sessionId = no_criterion.sessionId and sr.nodeId = no_criterion.nodeId

    where no_criterion.nodeId is null

    group by result_node.id
    on duplicate key update
        searchUpdateExtraObjectsData.cnt = 1;
-- !!!!!!

/*
    select 
        concat(object_node.id, ' - ', object_node.name) names, 
--        object_node.name, 
--        substr(group_concat(distinct criterion_node.name), 1, 40) criteria,
        group_concat(distinct criterion_node.name) sources,
        concat(count(distinct source_criterion.nodeTypeId), ' -|- ', count(distinct criterion.nodeTypeId), ' -|- ', count(distinct source_criterion.nodeTypeId) = count(distinct criterion.nodeTypeId)) result
    from nodeT object_node
    join objectT object on object_node.id = object.id
    join objectStatusT os on object.statusId = os.id and os.public = 'y'
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'

    join nodeLinkT result_link on nlt.id = result_link.nodeLinkTypeId
        and object_node.id = result_link.dstNodeId
        and result_link.metric = 1

    join nodeLinkT criterion_link on nlt.id = criterion_link.nodeLinkTypeId
        and object_node.id = criterion_link.dstNodeId
        and criterion_link.metric = 1
    join searchCriterionT criterion on criterion_link.srcNodeId = criterion.nodeId
    join nodeT criterion_node on criterion.nodeId = criterion_node.id

    join searchCriterionT source_criterion on criterion.sessionId = source_criterion.sessionId
        and source_criterion.nodeTypeId is not null
        and source_criterion.internalTypeId = 'tag'

    where criterion.sessionId = @session_id
        and criterion.nodeTypeId != 11
        and source_criterion.nodeTypeId != 11
        and result_link.srcNodeId in (25, 29)
    
    group by object_node.id
    having count(distinct source_criterion.nodeTypeId) = count(distinct criterion.nodeTypeId)\G
*/


    set @2 = __search_SetDefaultResult(@session_id);
                        insert searchResultT(sessionId, nodeId, collapsed)
                        select @session_id, o.id, 'n'
                        from objectT o
                        join searchCriterionT sc on o.id = sc.nodeId
                        join objectStatusT os on o.statusId = os.id
                        where sc.sessionId = @session_id
                            and os.public = 'y';
    
    set @3 = __search_RectifyCriteriaPost(@session_id);
    
    call __search_Prepare(@session_id);

    -- CONST: @sum_null_tolerance_ is 0.001
    call __search_SearchObjects1(@session_id, @count_notnull, @sum_null, 0.001, @count_numerics, @count_datetimes);
                        set @count_numerics_ = greatest(@count_numerics, 1);
                        set @count_datetimes_ = greatest(@count_datetimes, 1);
                        set @sum_null_tolerance_ = 0.001;
                        
                        drop temporary table if exists searchCriterionNumericTemp;
                        create temporary table searchCriterionNumericTemp(
                            objectId int unsigned not null,
                            nodeId int unsigned not null,
                            primary key(objectId, nodeId)
                        );
                        
                        drop temporary table if exists searchCriterionDatetimeTemp;
                        create temporary table searchCriterionDatetimeTemp(
                            objectId int unsigned not null,
                            nodeId int unsigned not null,
                            primary key(objectId, nodeId)
                        );
                        
                        insert searchCriterionNumericTemp(objectId, nodeId)
                        select avn.objectId, avn.attributeId
                        from attributeValueNumericT avn 
                        join objectT o on avn.objectId = o.id
                        join objectStatusT os on o.statusId = os.id
                        join searchCriterionT sc on avn.attributeId = sc.nodeId
                        join searchCriterionNumericT scn on sc.id = scn.searchCriterionId
                        where sc.sessionId = @session_id
                            and os.public = 'y'
                            and avn.value >= coalesce(scn.valueLow, avn.value)
                            and avn.value <= coalesce(scn.valueHigh, avn.value);

                        insert searchCriterionDatetimeTemp(objectId, nodeId)
                        select avd.objectId, avd.attributeId
                        from attributeValueDatetimeT avd 
                        join objectT o on avd.objectId = o.id
                        join objectStatusT os on o.statusId = os.id
                        join searchCriterionT sc on avd.attributeId = sc.nodeId
                        join searchCriterionDatetimeT scd on sc.id = scd.searchCriterionId
                        where sc.sessionId = @session_id
                            and os.public = 'y'
                            and avd.value >= coalesce(scd.valueLow, avd.value)
                            and avd.value <= coalesce(scd.valueHigh, avd.value);


                        select 
                            n.id, 
                            sum(t5.factor),
                            count(scn7.nodeId),
                            count(scd9.nodeId),
                            (
                                (coalesce(sum(t5.factor), @sum_null_tolerance_) / @sum_null 
                                + (count(scn7.nodeId) / @count_numerics_)
                                + (count(scd9.nodeId) / @count_datetimes_)) 
                            / 3) relevancy
                        from nodeT n
                        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
                        join objectT o on n.id = o.id
                        join objectStatusT os on o.statusId = os.id
                        
                        left join nodeLinkT nl5 on nlt.id = nl5.nodeLinkTypeId 
                            and n.id = nl5.dstNodeId 
                            and nl5.metric = 1
                        left join searchCriterionT sc5 on nl5.srcNodeId = sc5.nodeId 
                            and sc5.sessionId = @session_id
                            and sc5.nodeTypeId is null
                            and sc5.internalTypeId = 'tag'
                        left join tagT t5 on sc5.nodeId = t5.id
                        
                        left join searchCriterionNumericTemp scn7 on n.id = scn7.objectId

                        left join searchCriterionDatetimeTemp scd9 on n.id = scd9.objectId

                        where os.public = 'y'
                        group by n.id
                        having relevancy >= @sum_null_tolerance_;
    
    call __search_UpdateExtraObjects(@session_id);
                        if exists(select 1 from searchCriterionT where sessionId = @session_id) then
                            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 nodeLinkT nl on nlt.id = nl.nodeLinkTypeId
                                and t.id = nl.srcNodeId
                                and nl.metric = 1
                            join searchTemp st on nl.dstNodeId = st.nodeId
                            join nodeLinkT nl2 on nlt.id = nl2.nodeLinkTypeId 
                                and nl.dstNodeId = nl2.dstNodeId
                                and nl2.metric = 1
                            join searchCriterionT criterion2 on tagresult.sessionId = criterion2.sessionId
                                and nl2.srcNodeId = criterion2.nodeId
                            where tagresult.sessionId = @session_id
                            group by tagresult.nodeId
                            on duplicate key update cnt = 1;
                        else
                            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 nodeLinkT nl on nlt.id = nl.nodeLinkTypeId
                                and t.id = nl.srcNodeId
                                and nl.metric = 1
                            join searchTemp st on nl.dstNodeId = st.nodeId
                            where tagresult.sessionId = @session_id
                            group by tagresult.nodeId
                            on duplicate key update cnt = 1;
                        end if;

    -- CONST: @sum_null_tolerance_ is 0.001
    call __search_SearchObjects2(@session_id, @count_notnull, @sum_null, 0.001, @count_numerics, @count_datetimes);
                        set @limit_dataset = if(@count_notnull > 0, 500, 10);
                        select @limit_dataset;

                        if @count_notnull > 0 then
                            -- NOTE: searchTemp created in __search_Prepare
                            insert ignore searchResultT(sessionId, nodeId, relevancy)
                            select @session_id, st.nodeId, st.relevancy
                            from searchTemp st
                            join nodeLinkTypeT nlt on nlt.isDefault = 'y'
                            join objectT o on st.nodeId = o.id
                            join nodeLinkT nl3 on nlt.id = nl3.nodeLinkTypeId
                                and st.nodeId = nl3.dstNodeId
                                and nl3.metric = 1
                            join searchCriterionT sc3 on nl3.srcNodeId = sc3.nodeId                        
                                and sc3.nodeTypeId is not null
                                and sc3.internalTypeId = 'tag'
                                and sc3.sessionId = @session_id
                            group by st.nodeId, st.relevancy
                            having count(distinct sc3.nodeTypeId) = @count_notnull
                            order by st.relevancy desc, o.rating desc
                            limit @limit_dataset;
                        else 
                            -- NOTE: searchTemp created in __search_Prepare
                            insert ignore searchResultT(sessionId, nodeId, relevancy)
                            select @session_id, st.nodeId, st.relevancy
                            from searchTemp st
                            join objectT o on st.nodeId = o.id
                            order by st.relevancy desc, o.rating desc
                            limit @limit_dataset;
                        end if;

    call __search_UpdateSortCache(@session_id);

    call __search_Finalize(@session_id);
                        update searchResultT sr join searchUpdateExtraObjectsData sueod on sr.nodeId = sueod.tagId set
                            sr.extraObjects = sueod.cnt
                        where sr.sessionId = @session_id;











create table testTemp(
    nodeId int unsigned not null primary key,
    relevancy double not null default 0.0,
    rating double not null default 0.0,
    index(relevancy, rating)
) engine=MEMORY;


set @@max_heap_table_size = 100 * 1024 * 1024;

insert testTemp(nodeId, relevancy, rating)
select i1.n + i2.n * 10000, rand(), rand()
from zzzInts10000V i1
join zzzInts100V i2 on 1=1;



create table testTemp2(
    nodeId int unsigned not null primary key,
    relevancy double not null default 0.0,
    index(relevancy)
) engine=MEMORY;


set @@max_heap_table_size = 100 * 1024 * 1024;

insert testTemp2(nodeId, relevancy)
select i1.n + i2.n * 10000, rand()
from zzzInts10000V i1
join zzzInts100V i2 on 1=1;







create table testTemp3(
    nodeId int unsigned not null primary key,
    relevancy double not null default 0.0,
    rating double not null default 0.0,
    index using btree (relevancy, rating)
) engine=MEMORY;


set @@max_heap_table_size = 100 * 1024 * 1024;

insert testTemp3(nodeId, relevancy, rating)
select i1.n + i2.n * 10000, rand(), rand()
from zzzInts10000V i1
join zzzInts100V i2 on 1=1;



create table testTemp4(
    nodeId int unsigned not null primary key,
    relevancy double not null default 0.0,
    index using btree (relevancy)
) engine=MEMORY;


set @@max_heap_table_size = 100 * 1024 * 1024;

insert testTemp4(nodeId, relevancy)
select i1.n + i2.n * 10000, rand()
from zzzInts10000V i1
join zzzInts100V i2 on 1=1;









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)