Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Cannot Reproduce
-
1.3.00
-
None
-
None
-
Rank:0|i001bz:
-
Queries
Description
I ran the exact same query twice and got a different patient count. The 2 query ids and names are below. I don't see any difference in the generated sql:
921 Massachusetts@14:06:21
920 Massachusetts@14:03:43
The first run gave me a bad count of 977996, the second gave a correct count of 817230.
I did not flush the buffer_cache or shared_pool between the rerun.
select count(distinct(patient_num)) from observation_fact where concept_cd in (
'DEM|ZIPCODE:055',
'DEM|ZIPCODE:010',
'DEM|ZIPCODE:011',
'DEM|ZIPCODE:012',
'DEM|ZIPCODE:013',
'DEM|ZIPCODE:014',
'DEM|ZIPCODE:015',
'DEM|ZIPCODE:016',
'DEM|ZIPCODE:017',
'DEM|ZIPCODE:018',
'DEM|ZIPCODE:019',
'DEM|ZIPCODE:020',
'DEM|ZIPCODE:021',
'DEM|ZIPCODE:022',
'DEM|ZIPCODE:023',
'DEM|ZIPCODE:024',
'DEM|ZIPCODE:025',
'DEM|ZIPCODE:026',
'DEM|ZIPCODE:027')
- 817230
select count(1) from observation_fact where concept_cd in (
'DEM|ZIPCODE:055',
'DEM|ZIPCODE:010',
'DEM|ZIPCODE:011',
'DEM|ZIPCODE:012',
'DEM|ZIPCODE:013',
'DEM|ZIPCODE:014',
'DEM|ZIPCODE:015',
'DEM|ZIPCODE:016',
'DEM|ZIPCODE:017',
'DEM|ZIPCODE:018',
'DEM|ZIPCODE:019',
'DEM|ZIPCODE:020',
'DEM|ZIPCODE:021',
'DEM|ZIPCODE:022',
'DEM|ZIPCODE:023',
'DEM|ZIPCODE:024',
'DEM|ZIPCODE:025',
'DEM|ZIPCODE:026',
'DEM|ZIPCODE:027')
- 817230
Here is the generated sql from the first run of the query which returned the wrong count of 977996:
"(CLOB) INSERT INTO i2b2demodata.QUERY_GLOBAL_TEMP (patient_num, panel_count)
SELECT patient_num, 1 FROM (
SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_num FROM i2b2demodata.observation_fact WHERE (((concept_cd IN (select concept_cd from i2b2demodata.concept_dimension c where concept_path LIKE '\i2b2\Demographics\Zip3 codes\Massachusetts%')) ))
group by patient_num having count >=1) t
<*>
INSERT INTO i2b2demodata.DX (patient_num) SELECT * FROM (
SELECT DISTINCT t.patient_num FROM i2b2demodata.QUERY_GLOBAL_TEMP t WHERE panel_count = 1
) q
"
Here is the generated sql from the second run of the query that returned the correct patient count of 817230:
"(CLOB) INSERT INTO i2b2demodata.QUERY_GLOBAL_TEMP (patient_num, panel_count)
SELECT patient_num, 1 FROM (
SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_num FROM i2b2demodata.observation_fact WHERE (((concept_cd IN (select concept_cd from i2b2demodata.concept_dimension c where concept_path LIKE '\i2b2\Demographics\Zip3 codes\Massachusetts%')) ))
group by patient_num having count >=1) t
<*>
INSERT INTO i2b2demodata.DX (patient_num) SELECT * FROM (
SELECT DISTINCT t.patient_num FROM i2b2demodata.QUERY_GLOBAL_TEMP t WHERE panel_count = 1
) q
"
921 Massachusetts@14:06:21
920 Massachusetts@14:03:43
The first run gave me a bad count of 977996, the second gave a correct count of 817230.
I did not flush the buffer_cache or shared_pool between the rerun.
select count(distinct(patient_num)) from observation_fact where concept_cd in (
'DEM|ZIPCODE:055',
'DEM|ZIPCODE:010',
'DEM|ZIPCODE:011',
'DEM|ZIPCODE:012',
'DEM|ZIPCODE:013',
'DEM|ZIPCODE:014',
'DEM|ZIPCODE:015',
'DEM|ZIPCODE:016',
'DEM|ZIPCODE:017',
'DEM|ZIPCODE:018',
'DEM|ZIPCODE:019',
'DEM|ZIPCODE:020',
'DEM|ZIPCODE:021',
'DEM|ZIPCODE:022',
'DEM|ZIPCODE:023',
'DEM|ZIPCODE:024',
'DEM|ZIPCODE:025',
'DEM|ZIPCODE:026',
'DEM|ZIPCODE:027')
- 817230
select count(1) from observation_fact where concept_cd in (
'DEM|ZIPCODE:055',
'DEM|ZIPCODE:010',
'DEM|ZIPCODE:011',
'DEM|ZIPCODE:012',
'DEM|ZIPCODE:013',
'DEM|ZIPCODE:014',
'DEM|ZIPCODE:015',
'DEM|ZIPCODE:016',
'DEM|ZIPCODE:017',
'DEM|ZIPCODE:018',
'DEM|ZIPCODE:019',
'DEM|ZIPCODE:020',
'DEM|ZIPCODE:021',
'DEM|ZIPCODE:022',
'DEM|ZIPCODE:023',
'DEM|ZIPCODE:024',
'DEM|ZIPCODE:025',
'DEM|ZIPCODE:026',
'DEM|ZIPCODE:027')
- 817230
Here is the generated sql from the first run of the query which returned the wrong count of 977996:
"(CLOB) INSERT INTO i2b2demodata.QUERY_GLOBAL_TEMP (patient_num, panel_count)
SELECT patient_num, 1 FROM (
SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_num FROM i2b2demodata.observation_fact WHERE (((concept_cd IN (select concept_cd from i2b2demodata.concept_dimension c where concept_path LIKE '\i2b2\Demographics\Zip3 codes\Massachusetts%')) ))
group by patient_num having count >=1) t
<*>
INSERT INTO i2b2demodata.DX (patient_num) SELECT * FROM (
SELECT DISTINCT t.patient_num FROM i2b2demodata.QUERY_GLOBAL_TEMP t WHERE panel_count = 1
) q
"
Here is the generated sql from the second run of the query that returned the correct patient count of 817230:
"(CLOB) INSERT INTO i2b2demodata.QUERY_GLOBAL_TEMP (patient_num, panel_count)
SELECT patient_num, 1 FROM (
SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_num FROM i2b2demodata.observation_fact WHERE (((concept_cd IN (select concept_cd from i2b2demodata.concept_dimension c where concept_path LIKE '\i2b2\Demographics\Zip3 codes\Massachusetts%')) ))
group by patient_num having count >=1) t
<*>
INSERT INTO i2b2demodata.DX (patient_num) SELECT * FROM (
SELECT DISTINCT t.patient_num FROM i2b2demodata.QUERY_GLOBAL_TEMP t WHERE panel_count = 1
) q
"