Oracle SQL宏与分析函数异常：字段选择影响查询过滤结果

初始SQL宏实现 #

以下SQL宏Test_SQL_Macro_Analytic_M可根据P_ID参数返回全部3行或单行数据，逻辑基于基础数据拼接与LAG分析函数：

create or replace function Test_SQL_Macro_Analytic_M(P_ID in integer) return clob sql_macro is
begin
  return 'select * 
            from (select ID, lag(ID, 1) over (order by ID) as Prev_ID
                    from (select 1 ID from dual union all 
                          select 2 ID from dual union all 
                          select 3 ID from dual))
           where ID = nvl(P_ID, ID)';
end Test_SQL_Macro_Analytic_M;

• 调用示例：传入null返回全部行，传入2返回单行；从子查询获取P_ID进行关联查询时，初始逻辑运行正常。

修改后的宏逻辑 #

调整宏结构，去掉外层子查询后，宏定义如下：

create or replace function Test_SQL_Macro_Analytic_M(P_ID in integer) return clob sql_macro is
begin
  return 'select ID, lag(ID, 1) over (order by ID) as Prev_ID
            from (select 1 ID from dual union all 
                  select 2 ID from dual union all 
                  select 3 ID from dual)
           where ID = nvl(P_ID, ID)';
end Test_SQL_Macro_Analytic_M;

异常现象 #

执行以下查询时，仅选择M.ID能正确返回单行数据：

with P as (select 2 P_ID from dual)
select M.ID from P, Test_SQL_Macro_Analytic_M(P_ID => P.P_ID) M

但同时选择M.ID和M.Prev_ID时，会返回全部3行数据，不符合“无论选择哪些字段都仅返回单行”的预期。

解决方法 #

使用CROSS JOIN LATERAL显式指定关联逻辑，即可得到符合预期的结果：

with P as (select 2 P_ID from dual)
select M.ID, M.Prev_ID
  from P
cross join lateral (select * from Test_SQL_Macro_Analytic_M(P_ID => P.P_ID)) M

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