N1QL子查询关联后数据丢失问题求助

我们在使用Couchbase的N1QL查询时，遇到子查询关联后数据丢失的问题：两个单独执行各返回16条记录的子查询，按UUID关联后实际仅返回1条记录，以下是详细情况：

完整查询语句 #

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

单独执行的子查询及结果 #

两个子查询单独执行时均返回16条记录：

第一个子查询 #

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

第二个子查询 #

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 

问题原因 #

核心问题出在关联条件的逻辑错误：

• 第二个子查询（t6）按uuid_agg分组后，用min(t5.env)只保留了每个UUID对应的最小env值，因此每个UUID在t6中仅存在1条记录（对应最小env）。
• 第一个子查询（t3）返回的是每个UUID对应的所有env记录（可能一个UUID对应多个不同的env值）。
• 当用t3.uuid_proj = t6.uuid_agg and t3.env = t6.env关联时，只有t3中env值恰好等于该UUID最小env的记录能匹配成功，其他env值不等于最小值的记录都会被过滤。如果只有1个UUID的某条记录env等于它的min(env)，最终就只会返回1条结果。

修正方案 #

根据需求不同，有两种常见修正方式：

1. 保留所有符合条件的记录，同时标记最小env：使用LEFT JOIN替代INNER JOIN，将t6的min(env)作为字段关联到t3，无需用env等值匹配。示例：

select
t3.appName,
t3.uuid_proj as uuid,
t3.description,
t3.env,
t3.productStatus,
t6.min_env
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 
LEFT JOIN ( 
                              select t4.uuid as uuid_agg , min(t5.env) as min_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

2. 仅保留每个UUID对应最小env的记录：如果需求确实是只保留每个UUID最小env的那条记录，可以直接在t3的子查询中过滤，无需额外关联t6，简化查询逻辑：

SELECT t1.uuid AS uuid_proj,
       t1.appName,
       t1.description,
       t2.env,
       t2.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
WHERE (t1.uuid, t2.env) IN (SELECT t4.uuid, MIN(t5.env)
                            FROM (SELECT api_external.uuid
                                  FROM `api_external`
                                  WHERE type = 'partnerApp'
                                    AND data.companyId = '70a149da27cc425da86cba890bf5b143') t4
                            JOIN (SELECT api_external.data.env, api_external.data.partnerAppId
                                  FROM `api_external`
                                  WHERE type = 'integration') t5 ON t4.uuid = t5.partnerAppId
                            GROUP BY t4.uuid)

内容的提问来源于stack exchange，提问作者hemanth