GaussDB PL/SQL 常量折叠时通用计划出现invalid input syntax for type numeric

by

最近同事在搞GaussDB里，在oracle一个plsql中的for循环部分报错sqlerrm:invalid input syntax for type numeric， 但是该SQL在oracle执行正常，经分析在是GaussDB的在plsql中的通用计划(generat_mode) plan 导致。下面我简单演示。

gauss=# select version();
                                                      version
-------------------------------------------------------------------------------------------------------------------
 gaussdb (GaussDB Kernel 505.2.0 build 82d715e8) compiled at 2024-09-20 00:15:22 commit 9967 last mr 19883 release
(1 row)

set auto_explain_level=notice;
set enable_auto_explain=on;

declare
v1 varchar2(20);
v2 number;
begin
    v1:='a';
    for i in 1..6 loop
	    raise notice '%',i;
        select case when v1='a' then i else  to_number(v1) end from dual into v2;
    end loop;
exception when others then
    raise notice 'v1:%,sqlerrm:%',v1,sqlerrm;
end;
/

默认输出

gauss=# declare
gauss-# v1 varchar2(20);
gauss-# v2 number;
gauss-# begin
gauss$#     v1:='a';
gauss$#     for i in 1..6 loop
gauss$#             raise notice '%',i;
        select case when v1='a' then i else  to_number(v1) end from dual into v2;
    end loop;
exception when others then
    raise notice 'v1:%,sqlerrm:%',v1,sqlerrm;
gauss$# 
/gauss$#
NOTICE:  1
NOTICE:  2
NOTICE:  3
NOTICE:  4
NOTICE:  5
NOTICE:  6
NOTICE:  v1:a,sqlerrm:invalid input syntax for type numeric: "a"
ANONYMOUS BLOCK EXECUTE

第6次执行报错

set auto_explain_level=notice;
set enable_auto_explain=on;

gauss=# SET
gauss=#
gauss=# declare
gauss-# v1 varchar2(20);
gauss-# v2 number;
gauss-# begin
gauss$#     v1:='a';
gauss$#     for i in 1..6 loop
gauss$#             raise notice '%',i;
gauss$#         select case when v1='a' then i else  to_number(v1) end from dual into v2;
gauss$#     end loop;
gauss$# exception when others then
    raise notice 'v1:%,sqlerrm:%',v1,sqlerrm;
gauss$# gauss$# end;
/gauss$#
NOTICE:  1
NOTICE:
QueryPlan

----------------------------NestLevel:0----------------------------
Query Text: select case when v1='a' then i else  to_number(v1) end from dual
Name: anbob
Subquery Scan on dual  (cost=0.00..0.02 rows=1 width=0)
  Output: 1::numeric
  ->  Result  (cost=0.00..0.01 rows=1 width=0)
        Output: 'X'::text


CONTEXT:  SQL statement "select case when v1='a' then i else  to_number(v1) end from dual"
PL/pgSQL function inline_code_block line 8 at SQL statement
NOTICE:
----------------------------NestLevel:0----------------------------
duration: 0.000 s

CONTEXT:  SQL statement "select case when v1='a' then i else  to_number(v1) end from dual"
PL/pgSQL function inline_code_block line 8 at SQL statement
NOTICE:  2
NOTICE:
QueryPlan
...
...
...
NOTICE:  5
NOTICE:
QueryPlan

----------------------------NestLevel:0----------------------------
Query Text: select case when v1='a' then i else  to_number(v1) end from dual
Name: anbob
Subquery Scan on dual  (cost=0.00..0.02 rows=1 width=0)
  Output: 5::numeric
  ->  Result  (cost=0.00..0.01 rows=1 width=0)
        Output: 'X'::text


CONTEXT:  SQL statement "select case when v1='a' then i else  to_number(v1) end from dual"
PL/pgSQL function inline_code_block line 8 at SQL statement
NOTICE:
----------------------------NestLevel:0----------------------------
duration: 0.000 s

CONTEXT:  SQL statement "select case when v1='a' then i else  to_number(v1) end from dual"
PL/pgSQL function inline_code_block line 8 at SQL statement
NOTICE:  6
NOTICE:  v1:a,sqlerrm:invalid input syntax for type numeric: "a"
ANONYMOUS BLOCK EXECUTE

第6次执行报错，也没有输出转换。

更换dual为sys_dummy

 declare
  v1 varchar2(20);
  v2 number;
  begin
      v1:='1';
      for i in 1..6 loop
	  raise notice '%',i;
          select case when v1='1' then 1 else to_number(v1) end from sys_dummy into v2;
      end loop;
  exception when others then
      raise notice 'v1:%,sqlerrm:%',v1,sqlerrm;
  end;
  /
...
...
...
NOTICE:  5
NOTICE:
QueryPlan

----------------------------NestLevel:0----------------------------
Query Text: select case when v1='1' then 1 else to_number(v1) end from sys_dummy
Name: anbob
Subquery Scan on sys_dummy  (cost=0.00..0.02 rows=1 width=0)
  Output: 1::numeric
  ->  Result  (cost=0.00..0.01 rows=1 width=0)
        Output: 'X'::text


CONTEXT:  SQL statement "select case when v1='1' then 1 else to_number(v1) end from sys_dummy"
PL/pgSQL function inline_code_block line 7 at SQL statement
NOTICE:
----------------------------NestLevel:0----------------------------
duration: 0.000 s

CONTEXT:  SQL statement "select case when v1='1' then 1 else to_number(v1) end from sys_dummy"
PL/pgSQL function inline_code_block line 7 at SQL statement
NOTICE:  6
NOTICE:
QueryPlan

----------------------------NestLevel:0----------------------------
Query Text: select case when v1='1' then 1 else to_number(v1) end from sys_dummy
Name: anbob
Subquery Scan on sys_dummy  (cost=0.00..0.03 rows=1 width=0)
  Output: CASE WHEN (($10 <param value: 1 type: varchar>)::text = '1'::text) THEN 1::numeric ELSE numeric_in(textout(($10 <param value: 1 type: varchar>)::text), 0::oid, (-1)) END
  ->  Result  (cost=0.00..0.01 rows=1 width=0)
        Output: 'X'::text


CONTEXT:  SQL statement "select case when v1='1' then 1 else to_number(v1) end from sys_dummy"
PL/pgSQL function inline_code_block line 7 at SQL statement
NOTICE:
----------------------------NestLevel:0----------------------------
duration: 0.000 s

CONTEXT:  SQL statement "select case when v1='1' then 1 else to_number(v1) end from sys_dummy"
PL/pgSQL function inline_code_block line 7 at SQL statement
ANONYMOUS BLOCK EXECUTE

第6次的投影列则是带参数的case when表达式.

原因

这是因为GAUSS DB在第6次执行时会转换为通用计划gplan，需要提前转换所有参数可用，但是‘a’无法转换numbric 报错，之前5次是cplan所以没有报错。

解决方法

1，修改应用逻辑，修正转换无效number的可能行

2，使用hint use_cplan， 始终使用cplan，防止Plsql 使用gplan。

declare
v1 varchar2(20);
v2 number;
begin
    v1:='a';
    for i in 1..6 loop
	    raise notice '%',i;
        select /*+use_cplan*/ case when v1='a' then i else  to_number(v1) end from dual into v2;
    end loop;
exception when others then
    raise notice 'v1:%,sqlerrm:%',v1,sqlerrm;
end;
/

CONTEXT:  SQL statement "select /*+use_cplan*/ case when v1='a' then i else  to_number(v1) end from dual"
PL/pgSQL function inline_code_block line 8 at SQL statement
NOTICE:  6
NOTICE:
QueryPlan

----------------------------NestLevel:0----------------------------
Query Text: select /*+use_cplan*/ case when v1='a' then i else  to_number(v1) end from dual
Name: anbob
Subquery Scan on dual  (cost=0.00..0.02 rows=1 width=0)
  Output: 6::numeric
  ->  Result  (cost=0.00..0.01 rows=1 width=0)
        Output: 'X'::text


CONTEXT:  SQL statement "select /*+use_cplan*/ case when v1='a' then i else  to_number(v1) end from dual"
PL/pgSQL function inline_code_block line 8 at SQL statement
NOTICE:
----------------------------NestLevel:0----------------------------
duration: 0.000 s

CONTEXT:  SQL statement "select /*+use_cplan*/ case when v1='a' then i else  to_number(v1) end from dual"
PL/pgSQL function inline_code_block line 8 at SQL statement
ANONYMOUS BLOCK EXECUTE

注：该问题在postgresql,opengauss中并不存在。

Leave a Comment