Self join query find nothing

SQL++
1

I have a table called events with index built on field ‘tId’.
The following query searched nothing.

SELECT *
FROM `events` ev
  JOIN `events` ev2
  ON KEY ev2.tId
  FOR ev
WHERE ev.tId = 4
LIMIT 2;

However, without self join it can search some results.

SELECT *
FROM `events` ev
WHERE ev.tId = 4
LIMIT 2;

What mistake did I make?

2

It looks like ev2.tId is numeric and it matches to META(ev).id which is string, so change query and index to

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;
3

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.

4

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