SQL执行时表间关联顺序引起报错

读懂执行计划不仅能帮助我们优化SQL，还能帮我们排查错误。
今天开发的过程中碰到了一个错误，通过数据无法定位问题。打开执行计划就比较明确了。

原始SQL大致如下：

select jiraid ,sum(a1),sum(a2),sum(a3)
from (
select a.jiraid
       ,nvl(b.预估开发成本_元,0) a1
       ,nvl(b.实际开发成本_元,0) a2
       ,nvl(b.实际成本_元,0) a3
  from tb_jira_all a
  join dma_jira_info b
    on a.jiraid = b.jiraid
 where a.projectname = '预计2023上半年jira'
 ) 
 t group by t.jiraid;

报错内容：ORA-01722: invalid number

如果一个操作数字类型的函数操作了字符串，就会报这个错误。
例如：select 1+ 'a' from dual;

通过单独sum每个字段返回的值，发现都是正确的。但是sum(a1),sum(a2) 这样就报错。

然后尝试在子查询里添加rownum >=1 就没问题。

报错的执行计划：

 Plan Hash Value  : 2054375 

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Rows   | Bytes     | Cost | Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               |    338 |     73008 | 2078 | 00:00:25 |
|   1 |   SORT GROUP BY NOSORT   |               |    338 |     73008 | 2078 | 00:00:25 |
|   2 |    MERGE JOIN            |               |  42004 |   9072864 | 2078 | 00:00:25 |
|   3 |     SORT JOIN            |               | 135504 |  25610256 | 2068 | 00:00:25 |
|   4 |      VIEW                | VW_GBC_5      | 135504 |  25610256 | 2068 | 00:00:25 |
|   5 |       HASH GROUP BY      |               | 135504 | 834162624 | 2068 | 00:00:25 |
|   6 |        TABLE ACCESS FULL | DMA_JIRA_INFO | 135504 | 834162624 | 2064 | 00:00:25 |
| * 7 |     SORT JOIN            |               |    339 |      9153 |   10 | 00:00:01 |
| * 8 |      TABLE ACCESS FULL   | TB_JIRA_ALL   |    339 |      9153 |    9 | 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 7 - access("A"."JIRAID"="ITEM_1")
* 7 - filter("A"."JIRAID"="ITEM_1")
* 8 - filter("A"."PROJECTNAME"='预计2023上半年jira')


Note
-----
- dynamic sampling used for this statement

添加rownum>=1 的执行计划，添加了这个就不会报错了

 Plan Hash Value  : 2286831999 

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Rows   | Bytes     | Cost | Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               |   1092 |   6569472 | 5025 | 00:01:01 |
|   1 |   HASH GROUP BY          |               |   1092 |   6569472 | 5025 | 00:01:01 |
|   2 |    VIEW                  |               |   4689 |  28209024 | 2073 | 00:00:25 |
|   3 |     COUNT                |               |        |           |      |          |
| * 4 |      FILTER              |               |        |           |      |          |
| * 5 |       HASH JOIN          |               |   4689 |  28992087 | 2073 | 00:00:25 |
| * 6 |        TABLE ACCESS FULL | TB_JIRA_ALL   |    339 |      9153 |    9 | 00:00:01 |
|   7 |        TABLE ACCESS FULL | DMA_JIRA_INFO | 135504 | 834162624 | 2064 | 00:00:25 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 4 - filter(ROWNUM>=1)
* 5 - access("A"."JIRAID"="B"."JIRAID")
* 6 - filter("A"."PROJECTNAME"='预计2023上半年jira')


Note
-----
- dynamic sampling used for this statement

从第一个执行计划看，先走了 DMA_JIRA_INFO 表的汇总操作。 然后跟 TB_JIRA_ALL 做关联。
但是逻辑里是使用 TB_JIRA_ALL 过滤数据的， 过滤之前被sum的字段里含有字符串。无法转换成数据。