search

smallAreaHealthStatisticsUnit / rapidInquiryFacility

The Rapid Inquiry Facility (RIF) helps epidemiologists and public health researchers in environmental health activities.
GNU Lesser General Public License v3.0
14 stars 5 forks source link

ERROR: column "smoothed_relative_risk" is of type double precision but expression is of type character varying #140

Closed peterhambly closed 5 years ago

peterhambly commented 5 years ago

Error from R in statistics service when using Swedish data. Weill investigate covariate data further

16:29:47.261 [pool-8-thread-1] INFO  org.sahsu.rif.generic.util.CommonLogger : [org.sahsu.rif.services.datastorage.common.StatisticsProcessing]:
Statistics service called: POST http://localhost:8080/statistics/service/script returned a response status of 500 Internal Server Error
16:29:47.265 [pool-8-thread-1] ERROR org.sahsu.rif.generic.util.CommonLogger : [org.sahsu.rif.services.datastorage.common.StatisticsProcessing]:
Received a failure response from the Statistics Service. Response as a string is 

    POST http://localhost:8080/statistics/service/script returned a response status of 500 Internal Server Error

The Entity from the Response is 

    Trying JDBC connection using driver class org.postgresql.Driver, lib dir C:\Program Files\Apache Software Foundation\Tomcat 8.5\webapps\rifServices\WEB-INF\lib, URL jdbc:postgresql://localhost:5432/sahsuland, user peter
... JDBC connection established 
Querying by JDBC: SELECT rif40_sql_pkg.rif40_startup() 
Connected to DB
connectToDb exitValue: 0
About to fetch extract table
JDBC EXTRACT TABLE NAME: rif_studies.s67_extract
Querying by JDBC: select * from rif_studies.s67_extract 
JDBC Saving extract frame to: scratchSpace/d1-100/s67/data/tmp_s67_extract.csv
JDBC rif_studies.s67_extract numberOfRows=24336==
About to get adjacency matrix
Querying by JDBC: SELECT * FROM rif40_xml_pkg.rif40_GetAdjacencyMatrix( 67 ) 
rif40_GetAdjacencyMatrix numberOfRows=101==
Saving adjacency matrix to: scratchSpace/d1-100/s67/data/tmp_s67_adjacency_matrix.csv
About to smooth
Covariates: LOW_EDUCATION_CATEGORICAL
Removed: 0 of 470 non adjacent areas
Bayes smoothing with BYM model type, adjusted
Posterior probability calculated
callPerformSmoothingActivity exitValue: 0
performSmoothingActivity() OK:  0 
typeof(result$area_id[1]) ---->  integer ; check.integer(result$area_id[1]):  FALSE ; result$area_id[1]:  1.07.60 
About to save to table
In saveDataFrameToDatabaseTableSaving data frame to: scratchSpace/d1-100/s67/data/tmp_s67_map.csv
Creating temporary table: peter.tmp_s67_map
Replace INF with NA for temporary table: peter.tmp_s67_map
Replace NAN with NA for temporary table: peter.tmp_s67_map
Replace "" with NA for temporary table: peter.tmp_s67_map
About to write temporary table: peter.tmp_s67_map; first 10 rows
   area_id gid gid_rowindex username study_id inv_id band_id genders
1  1.07.60  NA           NA       NA       67     67      71       1
2  1.07.60  NA           NA       NA       67     67      71       2
3  1.07.60  NA           NA       NA       67     67      71       3
4  1.07.61  NA           NA       NA       67     67       1       1
5  1.07.61  NA           NA       NA       67     67       1       2
6  1.07.61  NA           NA       NA       67     67       1       3
7  1.07.63  NA           NA       NA       67     67      77       1
8  1.07.63  NA           NA       NA       67     67      77       2
9  1.07.63  NA           NA       NA       67     67      77       3
10 1.07.64  NA           NA       NA       67     67      58       1
   direct_standardisation adjusted observed expected lower95 upper95
1                       0        1        0        0      NA      NA
2                       0        1        0        0      NA      NA
3                       0        1        0        0      NA      NA
4                       0        1        0        0      NA      NA
5                       0        1        0        0      NA      NA
6                       0        1        0        0      NA      NA
7                       0        1        0        0      NA      NA
8                       0        1        0        0      NA      NA
9                       0        1        0        0      NA      NA
10                      0        1        0        0      NA      NA
   relative_risk smoothed_relative_risk posterior_probability
1             NA                     NA             0.4742756
2             NA                     NA             0.4742756
3             NA                     NA             0.4742756
4             NA                     NA             0.4743906
5             NA                     NA             0.4743906
6             NA                     NA             0.4743906
7             NA                     NA             0.4744001
8             NA                     NA             0.4744001
9             NA                     NA             0.4744001
10            NA                     NA             0.4743907
   posterior_probability_upper95 posterior_probability_lower95
1                             NA                            NA
2                             NA                            NA
3                             NA                            NA
4                             NA                            NA
5                             NA                            NA
6                             NA                            NA
7                             NA                            NA
8                             NA                            NA
9                             NA                            NA
10                            NA                            NA
   residual_relative_risk residual_rr_lower95 residual_rr_upper95 smoothed_smr
1                78.11820            9160.745           364349.29           NA
2                78.11820            9160.745           364349.29           NA
3                78.11820            9160.745           364349.29           NA
4                44.73413            4302.547           171139.15           NA
5                44.73413            4302.547           171139.15           NA
6                44.73413            4302.547           171139.15           NA
7                16.71187            1188.573            47286.52           NA
8                16.71187            1188.573            47286.52           NA
9                16.71187            1188.573            47286.52           NA
10               16.98288            1229.618            48919.10           NA
   smoothed_smr_lower95 smoothed_smr_upper95
1                     0                   NA
2                     0                   NA
3                     0                   NA
4                     0                   NA
5                     0                   NA
6                     0                   NA
7                     0                   NA
8                     0                   NA
9                     0                   NA
10                    0                   NA
Creating study_id index on temporary table
SQL> CREATE INDEX s67_ind_study_id ON peter.tmp_s67_map(study_id)Creating area_id index on temporary table
SQL> CREATE INDEX s67_ind_area_id ON peter.tmp_s67_map(area_id)Creating genders index on temporary table
SQL> CREATE INDEX s67_ind_genders ON peter.tmp_s67_map(genders)Created indices on temporary table
saveDataFrameToDatabaseTable finishing
In saveDataFrameToDatabaseTableSaving data frame to: scratchSpace/d1-100/s67/data/tmp_s67_map.csv
Creating temporary table: peter.tmp_s67_map
Replace INF with NA for temporary table: peter.tmp_s67_map
Replace NAN with NA for temporary table: peter.tmp_s67_map
Replace "" with NA for temporary table: peter.tmp_s67_map
About to write temporary table: peter.tmp_s67_map; first 10 rows
   area_id gid gid_rowindex username study_id inv_id band_id genders
1  1.07.60  NA           NA       NA       67     67      71       1
2  1.07.60  NA           NA       NA       67     67      71       2
3  1.07.60  NA           NA       NA       67     67      71       3
4  1.07.61  NA           NA       NA       67     67       1       1
5  1.07.61  NA           NA       NA       67     67       1       2
6  1.07.61  NA           NA       NA       67     67       1       3
7  1.07.63  NA           NA       NA       67     67      77       1
8  1.07.63  NA           NA       NA       67     67      77       2
9  1.07.63  NA           NA       NA       67     67      77       3
10 1.07.64  NA           NA       NA       67     67      58       1
   direct_standardisation adjusted observed expected lower95 upper95
1                       0        1        0        0      NA      NA
2                       0        1        0        0      NA      NA
3                       0        1        0        0      NA      NA
4                       0        1        0        0      NA      NA
5                       0        1        0        0      NA      NA
6                       0        1        0        0      NA      NA
7                       0        1        0        0      NA      NA
8                       0        1        0        0      NA      NA
9                       0        1        0        0      NA      NA
10                      0        1        0        0      NA      NA
   relative_risk smoothed_relative_risk posterior_probability
1             NA                     NA             0.4742756
2             NA                     NA             0.4742756
3             NA                     NA             0.4742756
4             NA                     NA             0.4743906
5             NA                     NA             0.4743906
6             NA                     NA             0.4743906
7             NA                     NA             0.4744001
8             NA                     NA             0.4744001
9             NA                     NA             0.4744001
10            NA                     NA             0.4743907
   posterior_probability_upper95 posterior_probability_lower95
1                             NA                            NA
2                             NA                            NA
3                             NA                            NA
4                             NA                            NA
5                             NA                            NA
6                             NA                            NA
7                             NA                            NA
8                             NA                            NA
9                             NA                            NA
10                            NA                            NA
   residual_relative_risk residual_rr_lower95 residual_rr_upper95 smoothed_smr
1                78.11820            9160.745           364349.29           NA
2                78.11820            9160.745           364349.29           NA
3                78.11820            9160.745           364349.29           NA
4                44.73413            4302.547           171139.15           NA
5                44.73413            4302.547           171139.15           NA
6                44.73413            4302.547           171139.15           NA
7                16.71187            1188.573            47286.52           NA
8                16.71187            1188.573            47286.52           NA
9                16.71187            1188.573            47286.52           NA
10               16.98288            1229.618            48919.10           NA
   smoothed_smr_lower95 smoothed_smr_upper95
1                     0                   NA
2                     0                   NA
3                     0                   NA
4                     0                   NA
5                     0                   NA
6                     0                   NA
7                     0                   NA
8                     0                   NA
9                     0                   NA
10                    0                   NA
Creating study_id index on temporary table
SQL> CREATE INDEX s67_ind_study_id ON peter.tmp_s67_map(study_id)Creating area_id index on temporary table
SQL> CREATE INDEX s67_ind_area_id ON peter.tmp_s67_map(area_id)Creating genders index on temporary table
SQL> CREATE INDEX s67_ind_genders ON peter.tmp_s67_map(genders)Created indices on temporary table
saveDataFrameToDatabaseTable finishing
SQL> UPDATE  rif_studies.s67_map  a
 SET direct_standardisation=b.direct_standardisation,adjusted=b.adjusted,observed=b.observed,expected=b.expected,lower95=b.lower95,upper95=b.upper95,relative_risk=b.relative_risk,smoothed_relative_risk=CASE WHEN b.smoothed_relative_risk  = "NAN" THEN NULL ELSE b.smoothed_relative_risk END, posterior_probability=CASE WHEN b.posterior_probability  = "NAN" THEN NULL ELSE b.posterior_probability END, posterior_probability_upper95=CASE WHEN b.posterior_probability_upper95  = "NAN" THEN NULL ELSE b.posterior_probability_upper95 END, posterior_probability_lower95=CASE WHEN b.posterior_probability_lower95  = "NAN" THEN NULL ELSE b.posterior_probability_lower95 END, residual_relative_risk=CASE WHEN b.residual_relative_risk  = "NAN" THEN NULL ELSE b.residual_relative_risk END, residual_rr_lower95=CASE WHEN b.residual_rr_lower95  = "NAN" THEN NULL ELSE b.residual_rr_lower95 END, residual_rr_upper95=CASE WHEN b.residual_rr_upper95  = "NAN" THEN NULL ELSE b.residual_rr_upper95 END, smoothed_smr=CASE WHEN b.smoothed_smr  = "NAN" THEN NULL ELSE b.smoothed_smr END, smoothed_smr_lower95=CASE WHEN b.smoothed_smr_lower95  = "NAN" THEN NULL ELSE b.smoothed_smr_lower95 END, smoothed_smr_upper95=CASE WHEN b.smoothed_smr_upper95  = "NAN" THEN NULL ELSE b.smoothed_smr_upper95 END
 FROM peter.tmp_s67_map b WHERE a.study_id=b.study_id AND a.band_id=b.band_id AND a.inv_id=b.inv_id AND a.genders=b.genders 
 AND a.area_id=b.area_id
CATCH ERROR IN QUERY! SQL>  UPDATE  rif_studies.s67_map  a
 SET direct_standardisation=b.direct_standardisation,adjusted=b.adjusted,observed=b.observed,expected=b.expected,lower95=b.lower95,upper95=b.upper95,relative_risk=b.relative_risk,smoothed_relative_risk=CASE WHEN b.smoothed_relative_risk  = "NAN" THEN NULL ELSE b.smoothed_relative_risk END, posterior_probability=CASE WHEN b.posterior_probability  = "NAN" THEN NULL ELSE b.posterior_probability END, posterior_probability_upper95=CASE WHEN b.posterior_probability_upper95  = "NAN" THEN NULL ELSE b.posterior_probability_upper95 END, posterior_probability_lower95=CASE WHEN b.posterior_probability_lower95  = "NAN" THEN NULL ELSE b.posterior_probability_lower95 END, residual_relative_risk=CASE WHEN b.residual_relative_risk  = "NAN" THEN NULL ELSE b.residual_relative_risk END, residual_rr_lower95=CASE WHEN b.residual_rr_lower95  = "NAN" THEN NULL ELSE b.residual_rr_lower95 END, residual_rr_upper95=CASE WHEN b.residual_rr_upper95  = "NAN" THEN NULL ELSE b.residual_rr_upper95 END, smoothed_smr=CASE WHEN b.smoothed_smr  = "NAN" THEN NULL ELSE b.smoothed_smr END, smoothed_smr_lower95=CASE WHEN b.smoothed_smr_lower95  = "NAN" THEN NULL ELSE b.smoothed_smr_lower95 END, smoothed_smr_upper95=CASE WHEN b.smoothed_smr_upper95  = "NAN" THEN NULL ELSE b.smoothed_smr_upper95 END
 FROM peter.tmp_s67_map b WHERE a.study_id=b.study_id AND a.band_id=b.band_id AND a.inv_id=b.inv_id AND a.genders=b.genders 
 AND a.area_id=b.area_id ; error:  Error in .local(conn, statement, ...): execute JDBC update query failed in dbSendUpdate (ERROR: column "smoothed_relative_risk" is of type double precision but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 234)
 ; JDBC error 
CATCH ERROR IN QUERY! SQL>  UPDATE  rif_studies.s67_map  a
 SET direct_standardisation=b.direct_standardisation,adjusted=b.adjusted,observed=b.observed,expected=b.expected,lower95=b.lower95,upper95=b.upper95,relative_risk=b.relative_risk,smoothed_relative_risk=CASE WHEN b.smoothed_relative_risk  = "NAN" THEN NULL ELSE b.smoothed_relative_risk END, posterior_probability=CASE WHEN b.posterior_probability  = "NAN" THEN NULL ELSE b.posterior_probability END, posterior_probability_upper95=CASE WHEN b.posterior_probability_upper95  = "NAN" THEN NULL ELSE b.posterior_probability_upper95 END, posterior_probability_lower95=CASE WHEN b.posterior_probability_lower95  = "NAN" THEN NULL ELSE b.posterior_probability_lower95 END, residual_relative_risk=CASE WHEN b.residual_relative_risk  = "NAN" THEN NULL ELSE b.residual_relative_risk END, residual_rr_lower95=CASE WHEN b.residual_rr_lower95  = "NAN" THEN NULL ELSE b.residual_rr_lower95 END, residual_rr_upper95=CASE WHEN b.residual_rr_upper95  = "NAN" THEN NULL ELSE b.residual_rr_upper95 END, smoothed_smr=CASE WHEN b.smoothed_smr  = "NAN" THEN NULL ELSE b.smoothed_smr END, smoothed_smr_lower95=CASE WHEN b.smoothed_smr_lower95  = "NAN" THEN NULL ELSE b.smoothed_smr_lower95 END, smoothed_smr_upper95=CASE WHEN b.smoothed_smr_upper95  = "NAN" THEN NULL ELSE b.smoothed_smr_upper95 END
 FROM peter.tmp_s67_map b WHERE a.study_id=b.study_id AND a.band_id=b.band_id AND a.inv_id=b.inv_id AND a.genders=b.genders 
 AND a.area_id=b.area_id ; error:  Error in .local(conn, statement, ...): execute JDBC update query failed in dbSendUpdate (ERROR: column "smoothed_relative_risk" is of type double precision but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 234)
 ; JDBC error 

----------------- End of Statistics failure details -------------------

Test case: SYDVAST Test 4.zip

devilgate commented 5 years ago

What branch are you in? I feel like I saw that a few days ago and fixed it.

peterhambly commented 5 years ago

State-1-study-setup-modal-errors

If it is in master I will update and re-test

bparkes commented 5 years ago

I think this cropped up because there are no cases in the comparison area (hence the 'expected' are all zero). It just needs the addition of as.numeric() to convert the NA to numeric. I can sort this if Martin hasn't already, let me know...

devilgate commented 5 years ago

No, I don't think I did fix this. I must have just seen something similar. Sorry for the confusion.

bparkes commented 5 years ago

Fix applied to State-1-study-setup-modal-errors