@s.tedrahedron posted a new topic, subject "Self join query find nothing". It read:

   I have a table called `events` with index built on field ‘tId'.  
    The following query searched nothing.
    [code] 
        SELECT *
        FROM `events` ev
          JOIN `events` ev2
          ON KEY ev2.tId
          FOR ev
        WHERE ev.tId = 4
        LIMIT 2;
        
    [/code]
    
    However, without self join it can search some results.
    [code] 
        SELECT *
        FROM `events` ev
        WHERE ev.tId = 4
        LIMIT 2;
        
    [/code]
    
    What mistake did I make?

To which @vsr1 replied

    It looks like ev2.tId is numeric and it matches to META(ev).id which is string, so change query and index to
    [code] 
        CREATE INDEX ix1 ON events(TOSTRING(ev2.tId))
        SELECT *
        FROM `events` ev
          JOIN `events` ev2
          ON KEY TOSTRING(ev2.tId)
          FOR ev
        WHERE ev.tId = 4
        LIMIT 2;
    [/code]

After which, @s.tedrahedron added

    Thanks vsr1
    
    It still seaches nothing.  
    Yes tId is numeric and they are all integers.  
    I wonder why I should convert event.tId to String.

Then @vsr1 said

    At present N1QL joins are based on document key (Next release will have support for ANSI joins). The above query using Index Join.  
    In Index JOIN you need to have Index on ON KEY.  
    It takes META(ev).id (FOR ev ) and matches with ON KEY expression using above index  
    produces META(ev2).id and JOINS that document.
    
    So in that process META(ev).id is STRING and ev2.tId is numeric. They will not match (to match the types also need to be matched, no implicit conversion, so doing explicit conversion).
    
    If you are not getting anything you might not have relation ship through document key. or document with key “4” may not exist
    
    dzone.com
    
    ### JOIN Faster With Couchbase Index JOINs - DZone Database
    
    Take a look at Couchbase's index JOINs — a way to perform JOINs quickly and without parents having references to their children documents.
    
    dzone.com
    
    ### Visually Explaining N1QL JOINs - DZone Database
    
    If you're using Couchbase, you likely know of N1QL. See how N1QL supports, or can mimic, seven classic SQL JOINs as well as some charts to show what you're JOINing.


{'source': 'discourse', 'url': 'https://forums.couchbase.com/t/self-join-query-find-nothing/15844', 'title': 'Self join query find nothing', 'views': 1394, 'has_accepted_answer': True, 'word_count': 269, 'like_count': 0, 'reply_count': 1, 'participant_count': 2, 'posts': 4, 'tags': '', 'author': '@s.tedrahedron', 'participants': '@s.tedrahedron, @vsr1', 'image_url': [None], 'created_at': '2018-02-15T08:49:41.782Z', 'last_posted_at': '2018-02-15T14:04:34.834Z'}