Major problems caused by Oracle's or operator

During work, I found that a data computing result was abnormal. I checked the original data and did not find any problems. I then compared it with the system computing result. I was shocked: It was a lot bigger, so, check the program and find the following calls:

 

SelectTT. bc1610
FromTj_v_pfi_bc1610_bc1620 TT
 WhereTo_char (TT. tj_date, 'yyyymmm ') = '2016'
AndTT. regionid = 999.
OrTT. regionidIn(SelectB. regionid
FromTj_performance_region_sub B
WhereB.Type= 0
AndB. parentregionid = 999)

 

The result is indeed abnormal When execution is taken out. The check and analysis result shows that it is actually a problem with the where condition. The OR operator is used in the condition. Due to carelessness, they are not noticed, the result exception is changed to the SQL statement shown below, and the test is normal:

 

SelectNvl (Sum(Tt. bc1610), 0)
FromTj_v_pfi_bc1610_bc1620 TT
 WhereTo_char (TT. tj_date, 'yyyymmm ') = '2016'
And(Tt. regionid = 999
OrTT. regionidIn(SelectB. regionid
FromTj_performance_region_sub B
WhereB.Type= 0
AndB. parentregionid = 999 ));

 

The only difference is that the first sentence does not include the two or conditions.

 

At work, there are many places where or is used. If or is in parallel with other conditions, you must pay attention to the application of or.