N1QL query dropping records after join with a subquery

SQL++
1

The Below Query is dropping records when i join 2 N1QL sub queries - We are using couchbase and using N1QL queries.

Full Query - 
select
t3.appName,
t3.uuid_proj as uuid,
t3.description,
t3.env,
t3.productStatus
from      
               ( select 
                              t1.uuid as uuid_proj ,
                              t1.appName as appName ,
                              t1.description as description,
                              t2.env as env,
                              t2.productStatus as productStatus
                              from 
                                             (
                                             select 
                                             api_external.uuid ,
                                             api_external.data.appName , 
                                             api_external.data.description 
                                             from `api_external` 
                                             where type = 'partnerApp' 
                                             and data.companyId = '70a149da27cc425da86cba890bf5b143' )t1
                              join 
                                             (
                                             select 
                                             api_external.data.env,
                                             api_external.data.productStatus,
                                             api_external.data.partnerAppId
                                             from 
                                             `api_external` 
                                             where type = 'integration' )t2
                              on t1.uuid = t2.partnerAppId 
               ) as t3 

join ( 
                              select t4.uuid as uuid_agg , min(t5.env) as env
                              from
                              (select api_external.uuid   from `api_external` where type = 'partnerApp' and data.companyId = '70a149da27cc425da86cba890bf5b143' )as t4 join
                              (select api_external.data.env, api_external.data.partnerAppId from `api_external` where type = 'integration' ) as t5
                              on t4.uuid = t5.partnerAppId
                              group by t4.uuid 
               ) as t6 
on
t3.uuid_proj = t6.uuid_agg and t3.env = t6.env

As you see it has 2 sub queries - The below subquery gives 16 records -

select    
                               t1.uuid as uuid_proj 
                               from 
                                              (
                                              select 
                                              api_external.uuid ,
                                              api_external.data.appName , 
                                              api_external.data.description 
                                              from `api_external` 
                                              where type = 'partnerApp' 
                                              and data.companyId = '70a149da27cc425da86cba890bf5b143' )t1
                               join 
                                              (
                                              select 
                                              api_external.data.env,
                                              api_external.data.productStatus,
                                              api_external.data.partnerAppId
                                              from 
                                              `api_external` 
                                              where type = 'integration' )t2
                               on t1.uuid = t2.partnerAppId
                               group by t1.uuid

Also the other subquery also gives 16 records -

 select t4.uuid as uuid_agg , min(t5.env) as env
                                from
                                (select api_external.uuid   from `api_external` where type = 'partnerApp' and data.companyId = '70a149da27cc425da86cba890bf5b143' )as t4 join
                               (select api_external.data.env, api_external.data.partnerAppId from `api_external` where type = 'integration' ) as t5
                               on t4.uuid = t5.partnerAppId
                               group by t4.uuid

By Logic join of both the queries on the same grain UUID must also give 16 records . But it gives only 1 . What am i doing wrong Please help

2

It should work. You should check data each join result and make sure it matched t3.uuid_proj = t6.uuid_agg and t3.env = t6.env.
With out data it is hard to pin point what is wrong.
First remove and t3.env = t6.env. and see what you get.
See LEFT JOIN and which document is matching MISSING and try switch JOIN (i.e. as if RIGHT JOIN ) and checkout.

Also try t3, t6 as CTE and then join and see if that gives results.

Will you able to post the each subquery results as you mentioned 16 documents.

3

Each subquerys give 16 records but join gives only 1
I am working with the dba team who will contact the couchbase vendor.

5

Track via MB-55059 , In mean time try this and see if gives right results.

WITH ct3 AS (select t1.uuid as uuid_proj ,
                    t1.appName as appName ,
                    t1.description as description,
                    t2.env as env,
                    t2.productStatus as productStatus
              FROM ( select
                           api_external.uuid ,
                           api_external.data.appName ,
                           api_external.data.description
                      from `api_external`
                      where type = 'partnerApp'
                            and data.companyId = '70a149da27cc425da86cba890bf5b143' ) t1
              JOIN ( select
                                api_external.data.env,
                                api_external.data.productStatus,
                                api_external.data.partnerAppId
                      from `api_external`
                      where type = 'integration' ) t2
               ON t1.uuid = t2.partnerAppId ),

    ct6 AS ( select t4.uuid as uuid_agg , min(t5.env) as env
             FROM (select api_external.uuid
                   from `api_external`
                   where type = 'partnerApp' and data.companyId = '70a149da27cc425da86cba890bf5b143' ) as t4
             JOIN (select api_external.data.env, api_external.data.partnerAppId
                   from `api_external`
                   where type = 'integration' ) as t5
              ON t4.uuid = t5.partnerAppId
              GROUP BY t4.uuid
              )
SELECT t3.appName,
       t3.uuid_proj as uuid,
       t3.description,
       t3.env,
       t3.productStatus
FROM ct3 AS t3
JOIN ct6 AS t6   ON t3.uuid_proj = t6.uuid_agg and t3.env = t6.env;
6

Wooowww this works. You guys are amazing

7

Thank you - @vsr1 . This really works

8

Simplified query will be

CREATE INDEX ix1 ON api_external(data.companyId, uuid, data.appName, data.description) WHERE type = "partnerApp";
CREATE INDEX ix2 ON api_external(data.partnerAppId, data.env, data.productStatus) WHERE type = "integration";


WITH ct3 AS (SELECT t1.uuid, t1.data.appName, t1.data.description,
                    t2.data.env, t2.data.productStatus
              FROM api_external AS t1
              JOIN api_external AS t2 ON t1.uuid = t2.data.partnerAppId
              WHERE t1.type = "partnerApp"
                    AND t1.data.companyId = "70a149da27cc425da86cba890bf5b143"
                    AND t2.type = "integration"
                    AND t2.data.partnerAppId IS NOT NULL),
    ct6 AS ( SELECT t4.uuid AS uuid_agg , MIN(t5.data.env) AS env
             FROM api_external AS t4
             JOIN api_external AS t5 ON t4.uuid = t5.data.partnerAppId
             WHERE t4.type = "partnerApp"
                   AND t4.data.companyId = "70a149da27cc425da86cba890bf5b143"
                   AND t5.type = "integration"
                   AND t5.data.partnerAppId IS NOT NULL
             GROUP BY t4.uuid)
SELECT t3.*
FROM ct3 AS t3
JOIN ct6 AS t6 ON t3.uuid = t6.uuid_agg and t3.env = t6.env;

Much simpler equivalent query.
If same results see following works . After JOIN get all the fields of results of MIN env record each group

SELECT m[1].*
FROM api_external AS t4
JOIN api_external AS t5 ON t4.uuid = t5.data.partnerAppId
WHERE t4.type = "partnerApp"
       AND t4.data.companyId = '70a149da27cc425da86cba890bf5b143'
       AND t5.type = "integration"
       AND t5.data.partnerAppId IS NOT NULL
GROUP BY t4.uuid
LETTING m = MIN([t5.data.env, {t4.uuid, t4.data.appName, t4.data.description,
                    t5.data.env, t5.data.productStatus}]);