Different result of differnet join type

I am facing a wierd issue: i was executing the following two queries:
SELECT *
FROM (
SELECT *
FROM (
SELECT *
FROM ssb_lineorder
LIMIT 10000) AS cte0
INNER JOIN ssb_ddate ON cte0.ssb_lineorder.lo_orderdate = ssb_ddate.d_datekey) AS cte1
JOIN ssb_part USE NL ON cte1.cte0.ssb_lineorder.lo_partkey = ssb_part.p_partkey
WHERE ssb_part.p_size > 10

and

SELECT *
FROM (
SELECT *
FROM (
SELECT *
FROM ssb_lineorder
LIMIT 10000) AS cte0
INNER JOIN ssb_ddate ON cte0.ssb_lineorder.lo_orderdate = ssb_ddate.d_datekey) AS cte1
JOIN ssb_part USE HASH(BUILD) ON cte1.cte0.ssb_lineorder.lo_partkey = ssb_part.p_partkey
WHERE ssb_part.p_size > 10

These two are exactly the same except the USE clause. According to my knowledge of relational DBMS, the results should be exactly the same. but I am getting different result: 1 for the first query, 7972 for the second query.

I am wondering if I misunderstood the n1ql mechenism ?

There should not be any different. https://blog.couchbase.com/ansi-join-support-n1ql/

  1. LIMIT inside without order by can cause inconsistent results. 1 vs 7972 that is way off.

As this data dependent you need to debug that,
Execute UI and go to Plan Text tab and take look where ItemsIn#, ItemsOut# each operator and take look where things gone wrong. cc @bingjie.miao

There is no OUTER JOIN try this

CREATE INDEX ix1 ON ssb_part(p_size, p_partkey);
CREATE INDEX ix2 ON ssb_lineorder(lo_partkey, lo_orderdate);
CREATE INDEX ix3 ON ssb_ddate(d_datekey);

SELECT *
FROM ssb_part AS sp
JOIN ssb_lineorder AS sl ON  sp.p_partkey = sl.lo_partkey
JOIN ssb_ddate AS sd ON sl.lo_orderdate = sd.d_datekey
WHERE sp.p_size > 10

SELECT *
FROM ssb_part AS sp
JOIN ssb_lineorder AS sl USE HASH (PROBE) ON  sp.p_partkey = sl.lo_partkey
JOIN ssb_ddate AS sd USE HASH (PROBE) ON sl.lo_orderdate = sd.d_datekey
WHERE sp.p_size > 10 ;