Troubleshooting
Problem
Query not returning results
Symptom
Prior to the Netezza upgrade, the query would return a result. Now it doesn't.
select *
from (select * from edw_person_master where person_key = 707219 and patient_ind = 1 ) person
inner join (select person_id, alias from (select person_id, alias, updt_dt_tm, active_ind, row_number() over (partition by person_id order by active_ind desc, end_effective_dt_tm desc, updt_dt_tm desc) rank_ from ads_v500_stage..person_alias where alias_pool_cd = 3110551 and alias is not null and
data_status_cd=25
/* Explicit check for presence in epic_patient is not performed due to Cerner-only test cases */
and alias not in (select id_hx from ads_epic_stage..identity_id_hx where id_type_hx = 1 and identity_new_id is not null)
) sub where rank_=1) mrn on person. cerner_person_id = mrn.person_id;
Cause
Starting in NPS 7.0.4 we introduced changes for Single-expr NOT IN queries to be SQL ANSI compliant and return correct results. By turning enable_notin_optimization to OFF/FALSE means switching back to pre-7.0.4 behavior.
Diagnosing The Problem
2.
select person_id, alias, updt_dt_tm, active_ind, row_number() over
(
partition by person_id order by active_ind desc, end_effective_dt_tm desc, updt_dt_tm desc
)
rank_ from ads_v500_stage..person_alias where alias_pool_cd = 3110551 and alias is not null and data_status_cd=25 /* Explicit check for presence in epic_patient is not performed due to Cerner-only test cases */ and alias not in
(
select id_hx from
ads_epic_stage..identity_id_hx where id_type_hx = 1 and identity_new_id is not null
)
.
3.
select person_id, alias from
(
select person_id, alias, updt_dt_tm, active_ind, row_number() over
(
partition by person_id order by active_ind desc, end_effective_dt_tm desc, updt_dt_tm desc
)
rank_ from ads_v500_stage..person_alias where alias_pool_cd = 3110551 and alias is not null and data_status_cd=25 /* Explicit check for presence in epic_patient is not performed due to Cerner-only test cases */ and alias not in
(
select id_hx from
ads_epic_stage..identity_id_hx where id_type_hx = 1 and identity_new_id is not null
)
)
The first one returned too many results to return. The second one returned nothing. The final one returned one row.
Resolving The Problem
I've checked this issue and made below test case:
drop table mytest01 ;
drop table mytest02 ;
create table mytest01 (col1 varchar(10) not null ) ;
insert into mytest01 values ('a');
create table mytest02 (col1 varchar(10) ) ;
insert into mytest02 values ('b');
insert into mytest02 values (null) ;
select * from mytest01 where col1 not in (select * from mytest02) ;
I could see the same symptom as yours.
In above test case I expected the result 'a' but Netezza produced no data.
When I change one optimizer related parameter enable_notin_optimization to false, above query returned correct answer.
I also tested your query and checked the query produced the result with parameter setting of "set enable_notin_optimization=false".
Please consider two options to specify above parameter in session level or NPS level.
1. To do set that parameter in session level, please set that parameter at the position right after connecting to Netezza using:
set enable_notin_optimization=false;
2. To do set that parameter in NPS level, we need to change
/nz/data/postgresql.conf so that below line is shown:
enable_notin_optimization=false
And need to restart NPS.
Product Synonym
pda netezza
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21684015