子查询含聚合函数 win magic 改写的 SQL 正确性问题

问题现象

最小化用例复现 OceanBase 数据库问题版本的错误计划。

1. 创建测试表 AC05。

    CREATE TABLE "AC05" (
    "AAZ203" NUMBER(20) CONSTRAINT "AC05_OBNOTNULL_1679078210338447" NOT NULL ENABLE,
    "AAC001" NUMBER(20) CONSTRAINT "AC05_OBNOTNULL_1679078210338467" NOT NULL ENABLE,
    "AAB001" NUMBER(20),
    "AAC050" VARCHAR2(2) CONSTRAINT "AC05_OBNOTNULL_1679078210338475" NOT NULL ENABLE,
    "AAE160" VARCHAR2(4) CONSTRAINT "AC05_OBNOTNULL_1679078210338478" NOT NULL ENABLE,
    "AAE742" VARCHAR2(50),
    "AAE743" VARCHAR2(300),
    "AAE035" NUMBER(8) CONSTRAINT "AC05_OBNOTNULL_1679078210338483" NOT NULL ENABLE,
    "AAE140" VARCHAR2(3) CONSTRAINT "AC05_OBNOTNULL_1679078210338488" NOT NULL ENABLE,
    "AAC066" VARCHAR2(3) CONSTRAINT "AC05_OBNOTNULL_1679078210338491" NOT NULL ENABLE,
    "AAC313" VARCHAR2(8) CONSTRAINT "AC05_OBNOTNULL_1679078210338494" NOT NULL ENABLE,
    "AAC314" VARCHAR2(300) CONSTRAINT "AC05_OBNOTNULL_1679078210338496" NOT NULL ENABLE,
    "AAZ159" NUMBER(20) CONSTRAINT "AC05_OBNOTNULL_1679078210338498" NOT NULL ENABLE,
    "AAE002" NUMBER(6) CONSTRAINT "AC05_OBNOTNULL_1679078210338501" NOT NULL ENABLE,
    "AAE013" VARCHAR2(1000),
    "AAZ649" NUMBER(20) CONSTRAINT "AC05_OBNOTNULL_1679078210338505" NOT NULL ENABLE,
    "AAE860" VARCHAR2(100) CONSTRAINT "AC05_OBNOTNULL_1679078210338508" NOT NULL ENABLE,
    "AAE859" NUMBER(14) CONSTRAINT "AC05_OBNOTNULL_1679078210338510" NOT NULL ENABLE,
    "AAE011" VARCHAR2(100) CONSTRAINT "AC05_OBNOTNULL_1679078210338513" NOT NULL ENABLE,
    "AAZ692" VARCHAR2(50) CONSTRAINT "AC05_OBNOTNULL_1679078210338515" NOT NULL ENABLE,
    "AAE036" NUMBER(14) CONSTRAINT "AC05_OBNOTNULL_1679078210338517" NOT NULL ENABLE,
    "AAB034" VARCHAR2(20) CONSTRAINT "AC05_OBNOTNULL_1679078210338520" NOT NULL ENABLE,
    "AAB360" VARCHAR2(6) CONSTRAINT "AC05_OBNOTNULL_1679078210338522" NOT NULL ENABLE,
    "AAB359" VARCHAR2(6) CONSTRAINT "AC05_OBNOTNULL_1679078210338525" NOT NULL ENABLE,
    "AAF018" VARCHAR2(6) CONSTRAINT "AC05_OBNOTNULL_1679078210338527" NOT NULL ENABLE,
    "AAA431" VARCHAR2(20) CONSTRAINT "AC05_OBNOTNULL_1679078210338530" NOT NULL ENABLE,
    "AAZ673" NUMBER(20),
    "AAA027" VARCHAR2(6) CONSTRAINT "AC05_OBNOTNULL_1679078210338534" NOT NULL ENABLE,
    "AAA508" VARCHAR2(200) CONSTRAINT "AC05_OBNOTNULL_1679078210338537" NOT NULL ENABLE,
    "AAA350" NUMBER(20),
    CONSTRAINT "PK_AC05_1" PRIMARY KEY ("AAZ203", "AAC001")
    ) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
    partition by hash("AAC001") partitions 64;
   

2. 创建测试表 AC05_GS。

     CREATE TABLE "AC05_GS" (
     "ID" NUMBER(18) CONSTRAINT "AC05_GS_OBNOTNULL_1679070980235136" NOT NULL ENABLE,
     "AAE400" VARCHAR2(50),
     "AAC001" NUMBER(10),
     "AAC051" NUMBER(18) CONSTRAINT "AC05_GS_OBNOTNULL_1679070980235158" NOT NULL ENABLE,
     "AAC050" VARCHAR2(3) CONSTRAINT "AC05_GS_OBNOTNULL_1679070980235162" NOT NULL ENABLE,
     "AAE160" VARCHAR2(50),
     "AAC003" VARCHAR2(50),
     "AAC002" VARCHAR2(25),
     "AAB001" NUMBER(8),
     "AAC008" VARCHAR2(3),
     "AAC031" VARCHAR2(3),
     "AAC039" VARCHAR2(3),
     "AAC058" VARCHAR2(8),
     "AAE035" DATE CONSTRAINT "AC05_GS_OBNOTNULL_1679070980235175" NOT NULL ENABLE,
     "AAC061" NUMBER(18),
     "AAC054" VARCHAR2(3),
     "AAE011" VARCHAR2(100),
     "AAE036" DATE,
     "AAE140" VARCHAR2(3),
     "AAE013" VARCHAR2(100),
     "AAE100" VARCHAR2(3),
     "SPR" VARCHAR2(20),
     "SPRQ" DATE,
     "BAZ001" NUMBER(16) CONSTRAINT "AC05_GS_OBNOTNULL_1679070980235191" NOT NULL ENABLE,
     "BAZ002" NUMBER(16),
     "BZE300" NUMBER(16),
     "AAB034" VARCHAR2(20),
     CONSTRAINT "PK_AC05_GS" PRIMARY KEY ("BAZ001")
     ) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0;
   

3. 创建视图 AC07。

    CREATE VIEW "AC07" AS (
    SELECT
    "AC05"."AAC001" AS "AAC001",
    "AC05"."AAB001" AS "AAB001",
    "AC05"."AAC050" AS "AAC050",
    TO_DATE("AC05"."AAE035",'yyyymmdd hh24miss') AS "AAE035" FROM "AC05" WHERE ("AC05"."AAE140" in ('410', '420')))
    UNION ALL (
    SELECT
    "AC05_GS"."AAC001" AS "AAC001",
    "AC05_GS"."AAB001" AS "AAB001",
    "AC05_GS"."AAC050" AS "AAC050",
    "AC05_GS"."AAE035" AS "AAE035" FROM "AC05_GS");
   

4. 灌入数据之后执行如下语句:

   EXPLAIN EXTENDED SELECT  
   aac001,
   aab001,
   aae035 as cbsj, 
   (SELECT /*+unnest*/ MIN(aae035) FROM AC07 
   WHERE aac001 = a.aac001 AND aab001 = a.aab001 AND aac050 LIKE '2%' AND aae035 >= a.aae035 ) tbsj 
   FROM ac07 a WHERE aac001 = '2023700645' AND aab001 = 20480788 AND aac050 like '1%' ORDER BY aae035;
   

   输出结果如下:

问题 SQL 特征

1. 出问题 SQL 包含一个子查询。

2. 子查询语句中不包含 join 表和窗口函数, 以及 group by、having、limit 子句。

3. 子查询中 select 列表中只包含一个类型为 min/max/count/sum 之一的聚合函数列。

4. 子查询中的 from 表需要同时存在于父查询 from 表中。

问题原因

在这个问题 SQL 中子查询的条件 aac001 = a.aac001 AND aab001 = a.aab001 AND aac050 LIKE '2%' AND aae035 >= a.aae035 被下推到视图 ac07 内之后, win magic 改写时会比较子查询内的 ac07 视图与外层查询的 ac07 视图，发现两者的view_ref_id 相同，认为两个视图相同, 实际两个视图是不一样的, 因为优化器只看了视图的 ref id, 并没有看视图里面的所有条件, 导致改写错误。 目前的修复方式是如果这个视图发生了改写 (这里下推了条件下去), 就移除它的视图属性, 认为是一个普通的子查询，而不是用户定义的视图。

问题的风险及影响

在有问题的 OceanBase 数据库版本中, 执行子查询中含聚合函数且包含过滤条件的 SQL 存在正确性问题，执行结果不正确。

影响的版本

OceanBase 数据库企业版 V2.2.77 BP16 (oceanbase-2.2.77-116000022023021412) 以及之前的 BP 版本、V3.1.2 BP11 (oceanbase-3.1.2-111000052023010412) 以及之前的 BP 版本。

解决方法及规避方式

• 解决方法如下: 升级至问题已修复版本,目前已修复为 OceanBase 数据库企业版 V2.2.7 BP17 (oceanbase-2.2.77-117000112023051914) 及之后版本、V3.2.x 及之后版本。

  注意

  目前 OceanBase 数据库企业版 V3.1 BP12 版本暂未发版。

• 规避方法: 使用 OceanBase 数据库的 hint 提示 /+ NO_REWRITE/ 禁止改写。