##sample1
无法解析 json 数据库直接crash. 分析函数，定位到BUG
升级数据库软件，问题得到规避
########sample 2 优化器解析or 导致结果不一样
--测试1 原有的查询顺序，查到空行。
SQL> SELECT coltablename,DONO,colupdateable,colupdateable
FROM SEBMDEV.DATAOBJECT_LIBRARY
WHERE DONO = 'addRoleTaskList'
AND (colupdateable is null or coltablename is null)
AND (colupdateable is null or colupdateable = '1');
no rows
used time: 1.484(ms). Execute id is 632801.
SQL> set autotrace on
SQL> SELECT coltablename,DONO,colupdateable,colupdateable
2 FROM SEBMDEV.DATAOBJECT_LIBRARY
3 WHERE DONO = 'addRoleTaskList'
4 AND (colupdateable is null or coltablename is null)
5 AND (colupdateable is null or colupdateable = '1');
0 | CSCN2 | DATAOBJECT_LIBRARY | INDEX33555513 | 1
used time: 1.306(ms). Execute id is 0.
--测试2 SQL2 调整了 and 执行顺序，可以查到5条记录,开发想知道差异在哪里
try 。可以临时规避，看起来跟连接先后顺序有关:
SELECT coltablename,DONO,colupdateable,colupdateable
FROM SEBMDEV.DATAOBJECT_LIBRARY
WHERE DONO = 'addRoleTaskList'
AND (colupdateable is null or colupdateable = '1') <-
AND (colupdateable is null or coltablename is null) <-
;
SQL> SELECT coltablename,DONO,colupdateable,colupdateable
FROM SEBMDEV.DATAOBJECT_LIBRARY
WHERE DONO = 'addRoleTaskList'
AND (colupdateable is null or colupdateable = '1')
AND (colupdateable is null or coltablename is null)
;
0 | CSCN2 | DATAOBJECT_LIBRARY | INDEX33555513 | 1
used time: 1.255(ms). Execute id is 0.
SQL> set autotrace off
SQL> SELECT coltablename,DONO,colupdateable,colupdateable
2 FROM SEBMDEV.DATAOBJECT_LIBRARY
3 WHERE DONO = 'addRoleTaskList'
4 AND (colupdateable is null or colupdateable = '1')
5 AND (colupdateable is null or coltablename is null)
6 ;
LINEID COLTABLENAME DONO COLUPDATEABLE COLUPDATEABLE
---------- ------------ --------------- ------------- -------------
1 r addRoleTaskList NULL NULL
2 r addRoleTaskList NULL NULL
3 r addRoleTaskList NULL NULL
4 r addRoleTaskList NULL NULL
5 p addRoleTaskList NULL NULL
used time: 1.443(ms). Execute id is 632804.
官方回复：
应该是已知问题，调整下OPTIMIZER_OR_NBEXP，不要包含16就行
OPTIMIZER_OR_NBEXP
select name,value,sys_value,file_value,type,description from v$parameter
where name='OPTIMIZER_OR_NBEXP';
NAME VALUE SYS_VALUE FILE_VALUE TYPE DESCRIPTION
0 OPTIMIZER_OR_NBEXP 29 29 29 SESSION Flag of or-expression optimization method
SP_SET_PARA_VALUE(1,'OPTIMIZER_OR_NBEXP',13);
达梦厂家分析后如下：此问题为已知的BUG问题，
参数OPTIMIZER_OR_NBEXP包含16时，在生成计划上出现逻辑错误导致结果集错误
--OPTIMIZER_OR_NBEXP参数值中不包含16时，能正常返回结果集
目前达梦方已修改代码。除了外层判断，内层优化时也需要判断，屏蔽不可优化的nbexp。##可以修改参数OPTIMIZER_OR_NBEXP 不包含16可规避此问题##
后续达梦已在25年版本上已修复此BUG。