Couchbase不同位置嵌套循环WHERE子句导致查询结果不一致问题咨询

你对N1QL执行机制的判断没有偏差，基于传统关系数据库规则推导的「两个查询逻辑完全等价」的结论完全成立。结果出现巨大差异，是N1QL优化器和传统RDBMS行为不一致的谓词下推逻辑导致的，不是你写的查询有逻辑错误。

• 从关系代数层面看，两个查询没有任何语义区别：内连接的交换律、结合律决定，只要过滤条件属于连接完成后的单表判断，无论放在连接同层级还是最外层派生表外，最终结果都应当完全一致。传统关系数据库的优化器会严格守住派生表的语义边界，不会随意跨层调整执行顺序。
• 第一个查询结果异常的触发点：N1QL优化器会把和ssb_part同层级、跟在JOIN语句后面的ssb_part.p_size > 10过滤条件，直接下推到你指定的嵌套循环（USE NL）连接的执行逻辑里，甚至跳过你定义的派生表层级限制，直接打乱连接的执行顺序：
  优化器实际执行时，会先对ssb_part表应用p_size >10过滤，把过滤后的结果集当成嵌套循环的驱动端，反过来匹配左侧cte1的内容；甚至会把你最内层定义的ssb_lineorder LIMIT 10000的执行位置，挪到整个连接流程的后面。这种改写完全打破了你从内层LIMIT向外层层递推结果的执行预期，最后自然只匹配到1条符合条件的行。
  你写的USE NL强制嵌套循环提示，反而给了优化器更大的驱动端选择自由度，进一步放大了这种执行逻辑的偏移。
• 第二个查询结果符合预期的逻辑：你把过滤条件写在最外层的cte2派生表外面，N1QL优化器没法突破派生表的封闭边界把谓词下推到内部连接节点里，只能严格按照你写的层级顺序执行：
  1. 先从ssb_lineorder取出LIMIT 10000的基础行集生成cte0
  2. 关联ssb_ddate生成cte1
  3. 通过嵌套循环连接ssb_part拿到全量匹配行生成cte2
  4. 最后在最外层应用p_size >10过滤，得到7972条结果。

• 用N1QL写查询时，如果有强制JOIN提示、内层LIMIT这类对执行顺序有明确要求的逻辑，尽量把单表过滤条件放在最外层写，不要和同层级的JOIN语句放在一起，减少优化器随意调整执行顺序的概率。
• 不要因为这次的结果差怀疑自己对SQL通用规则的理解：这是N1QL优化器和传统关系型数据库的行为差异，不是你对查询逻辑的认知出了问题。

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