Upgrading to XNAT 1.8.9 breaks subject listing with custom demographics
Tom Doel
This works in XNAT 1.8.8.2, but upgrading to XNAT 1.8.9, 1.8.9.1, 1.8.9.2 causes the subject table listing to break. This is true for the subject listing on the project page, and also through Browse > Data > Subjects.
It seems that the additional custom DisplayFields cause XNAT to generate an invalid SQL query, which gives an error in sql.log ( ERROR: syntax error at or near "FROM" ...). If that column is added to the table, then the table simply shows Failed to create search results.
I've created a minimal custom schema example to illustrate and posted the error logs below.
<xs:schema targetNamespace="http://nrg.wustl.edu/custom" xmlns:custom="http://nrg.wustl.edu/custom" xmlns:xnat="http://nrg.wustl.edu/xnat" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
<xs:import namespace="http://nrg.wustl.edu/xnat" schemaLocation="../xnat/xnat.xsd"/>
<xs:element name="CustomDemographics" type="custom:customDemographics"/>
<xs:complexType name="customDemographics">
<xs:complexContent>
<xs:extension base="xnat:demographicData">
<xs:sequence>
<xs:element name="customProperty">
<xs:simpleType>
<xs:restriction base="xs:string"/>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
</xs:schema>
<DisplayField id="customId" header="Custom Field" visible="true" searchable="true" data-type="string">
<DisplayFieldElement name="Field1" schema-element="custom:customDemographicData.customProperty"/>
</DisplayField>
...more standard display document xml...
<DisplayVersion versionName="listing" ...etc...
<DisplayFieldRef id="customId"/>
...etc...
Errors in sql.log:
DROP VIEW IF EXISTS displayfields_xnat_subjectData;
CREATE OR REPLACE VIEW displayfields_xnat_subjectData AS SELECT SEARCH.xnat_subjectData4 AS subject_id, SEARCH.xnat_subjectData4 AS subjectid, '/archive/subjects/' || SEARCH.xnat_subjectData4 AS uri, SEARCH.xnat_subjectData_meta_data330 AS insert_date, SEARCH.table22_login AS insert_user, SEARCH.xnat_demographicData_xnat_demographicData4 AS gender, CASE SEARCH.xnat_demographicData_xnat_demographicData4 WHEN 'male' THEN 'M' WHEN 'female' THEN 'F' ELSE 'U' END AS gender_text, SEARCH.xnat_demographicData_xnat_demographicData5 AS handedness, CASE LOWER(SEARCH.xnat_demographicData_xnat_demographicData5) WHEN 'left' THEN 'L' WHEN 'right' THEN 'R' WHEN 'ambidextrous' THEN 'A' WHEN 'r' THEN 'R' WHEN 'l' THEN 'L' WHEN 'a' THEN 'A' END AS handedness_text, CAST(FLOOR(COALESCE(SEARCH.xnat_demographicData_xnat_demographicData2,EXTRACT(YEAR FROM SEARCH.xnat_demographicData_xnat_demographicData1))) AS INTEGER) AS dob, SEARCH.xnat_demographicData_xnat_demographicData3 AS age, CASE WHEN SEARCH.xnat_demographicData_xnat_demographicData3 > 89 THEN '90+' ELSE (SEARCH.xnat_demographicData_xnat_demographicData3 / 5) * 5 || '-' || (SEARCH.xnat_demographicData_xnat_demographicData3 / 5) * 5 + 4 END AS age_bracket, SEARCH.xnat_demographicData_xnat_demographicData8 AS educ, SEARCH.xnat_demographicData_xnat_demographicData6 AS ses, xnat_mrSessionData_COUNT AS mr_count, SEARCH.xnat_investigatorData70 AS invest_csv, SUB_PROJECTS_PROJECTS AS projects, SEARCH.xnat_subjectData5 AS project, SEARCH.xnat_subjectData6 AS sub_group, COALESCE(SEARCH.xnat_subjectData7, SEARCH.xnat_subjectData4) AS subject_label, SEARCH.xnat_projectParticipant11 AS shared_label, SUBJECT_IDS_ADDIDS AS add_ids, SEARCH.xnat_demographicData_xnat_demographicData10 AS race, SEARCH.xnat_demographicData_xnat_demographicData16 AS ethnicity, SEARCH.custom_customDemographicData_custom_customDemographicData1 AS customid, '<i class="fa fa-eye"</i>' AS ohif, SEARCH.xnat_subjectData_meta_data327 AS QUARANTINE_STATUS FROM (SELECT DISTINCT ON (xnat_subjectData4) * FROM (SELECT xnat_subjectData.id AS xnat_subjectData4, table12.status AS xnat_subjectData_meta_data327, table12.insert_date AS xnat_subjectData_meta_data330, table22.login AS table22_login, table32.lastname AS xnat_investigatorData70, xnat_subjectData.project AS xnat_subjectData5, xnat_subjectData._group AS xnat_subjectData6, xnat_subjectData.label AS xnat_subjectData7, table42.label AS xnat_projectParticipant11, table00.xnat_abstractdemographicdata_id AS xnat_demographicData_abstractdemographicdata_EXT_xnat__9454670c, table20.insert_date AS xnat_demographicData_demographicdata_info_xnat_demogra_91807c1f, xnat_demographicData.gender AS xnat_demographicData_xnat_demographicData4, xnat_demographicData.handedness AS xnat_demographicData_xnat_demographicData5, xnat_demographicData.yob AS xnat_demographicData_xnat_demographicData2, xnat_demographicData.dob AS xnat_demographicData_xnat_demographicData1, xnat_demographicData.age AS xnat_demographicData_xnat_demographicData3, xnat_demographicData.education AS xnat_demographicData_xnat_demographicData8, xnat_demographicData.ses AS xnat_demographicData_xnat_demographicData6, xnat_demographicData.race AS xnat_demographicData_xnat_demographicData10, xnat_demographicData.ethnicity AS xnat_demographicData_xnat_demographicData16, table11.xnat_abstractdemographicdata_id AS custom_customDemographicData_demographicdata_EXT_abstr_39ab2b9, table31.insert_date AS custom_customDemographicData_customdemographicdata_inf_ff356234, custom_customDemographicData.customproperty AS custom_customDemographicData_custom_customDemographicData1, xnat_mrSessionData_COUNT AS xnat_mrSessionData_COUNT, SUB_PROJECTS.PROJECTS AS SUB_PROJECTS_PROJECTS, SUBJECT_IDS.ADDIDS AS SUBJECT_IDS_ADDIDS FROM xnat_subjectData xnat_subjectData LEFT JOIN xnat_subjectData_meta_data table12 ON xnat_subjectData.subjectData_info=table12.meta_data_id LEFT JOIN xdat_user table22 ON table12.insert_user_xdat_user_id=table22.xdat_user_id LEFT JOIN xnat_investigatorData table32 ON xnat_subjectData.investigator_xnat_investigatordata_id=table32.xnat_investigatordata_id LEFT JOIN xnat_projectParticipant table42 ON xnat_subjectData.id=table42.subject_id LEFT JOIN (SELECT xnat_mrSessionData_COUNT.xnat_subjectAssessorData23, COUNT(*) AS xnat_mrSessionData_COUNT FROM (SELECT table20.id AS xnat_mrSessionData0, table10.subject_id AS xnat_subjectAssessorData23, table20.date AS xnat_experimentData26 FROM (SELECT SEARCH.* FROM (SELECT DISTINCT ON (xnat_mrSessionData0) * FROM (SELECT table20.id AS xnat_mrSessionData0, table40.status AS xnat_mrSessionData_meta_data1049 FROM xnat_mrSessionData xnat_mrSessionData LEFT JOIN xnat_imageSessionData table00 ON xnat_mrSessionData.id=table00.id LEFT JOIN xnat_subjectAssessorData table10 ON table00.id=table10.id LEFT JOIN xnat_experimentData table20 ON table10.id=table20.id LEFT JOIN xnat_mrSessionData_meta_data table40 ON xnat_mrSessionData.mrSessionData_info=table40.meta_data_id) SECURITY WHERE
(
( (xnat_mrSessionData_meta_data1049 = 'active') OR (xnat_mrSessionData_meta_data1049 = 'locked') OR (xnat_mrSessionData_meta_data1049 = 'quarantine')) AND
( (xnat_mrSessionData_meta_data1049 = 'active') OR (xnat_mrSessionData_meta_data1049 = 'locked') OR (xnat_mrSessionData_meta_data1049 = 'quarantine')) AND
( (xnat_mrSessionData_meta_data1049 = 'active') OR (xnat_mrSessionData_meta_data1049 = 'locked') OR (xnat_mrSessionData_meta_data1049 = 'quarantine')))) SECURITY LEFT JOIN xnat_mrSessionData SEARCH ON SECURITY.xnat_mrSessionData0=SEARCH.id) xnat_mrSessionData LEFT JOIN xnat_imageSessionData table00 ON xnat_mrSessionData.id=table00.id LEFT JOIN xnat_subjectAssessorData table10 ON table00.id=table10.id LEFT JOIN xnat_experimentData table20 ON table10.id=table20.id) xnat_mrSessionData_COUNT GROUP BY xnat_mrSessionData_COUNT.xnat_subjectAssessorData23) AS xnat_mrSessionData_COUNT ON xnat_subjectData.id=xnat_mrSessionData_COUNT.xnat_subjectAssessorData23 LEFT JOIN SUB_PROJECTS SUB_PROJECTS ON xnat_subjectData.id=SUB_PROJECTS.id LEFT JOIN SUBJECT_IDS SUBJECT_IDS ON xnat_subjectData.id=SUBJECT_IDS.subject_id LEFT JOIN xnat_demographicData ON xnat_subjectData.demographics_xnat_abstractdemographicdata_id=xnat_demographicData.xnat_abstractdemographicdata_id LEFT JOIN xnat_abstractDemographicData table00 ON xnat_demographicData.xnat_abstractdemographicdata_id=table00.xnat_abstractdemographicdata_id LEFT JOIN xnat_demographicData_meta_data table20 ON xnat_demographicData.demographicData_info=table20.meta_data_id FROM custom_customDemographicData custom_customDemographicData LEFT JOIN xnat_demographicData table01 ON custom_customDemographicData.xnat_abstractdemographicdata_id=table01.xnat_abstractdemographicdata_id LEFT JOIN xnat_abstractDemographicData table11 ON table01.xnat_abstractdemographicdata_id=table11.xnat_abstractdemographicdata_id LEFT JOIN custom_customDemographicData_meta_data table31 ON custom_customDemographicData.customDemographicData_info=table31.meta_data_id) SEARCH ORDER BY xnat_subjectData4) SEARCH ORDER BY (SEARCH.xnat_subjectData4) ;
'
org.postgresql.util.PSQLException: ERROR: syntax error at or near "FROM"
Position: 6568
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2510)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2245)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:311)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:447)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:368)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:309)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:295)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:272)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:267)
at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193)
at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193)
at org.nrg.xft.db.PoolDBUtils$Transaction.execute(PoolDBUtils.java:1118)
at org.nrg.xnat.initialization.tasks.CreateOrUpdateDatabaseViews.callImpl(CreateOrUpdateDatabaseViews.java:90)
at org.nrg.xnat.initialization.tasks.AbstractInitializingTask.call(AbstractInitializingTask.java:31)
at org.nrg.xnat.initialization.tasks.AbstractInitializingTask.call(AbstractInitializingTask.java:18)
at org.nrg.xnat.initialization.InitializingTasksExecutor$CheckTasks.runTask(InitializingTasksExecutor.java:61)
at org.nrg.xnat.task.AbstractXnatRunnable.run(AbstractXnatRunnable.java:33)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:750)
Tom Doel
</DisplayField>
Timothy Olsen
I'll put it on one of our test servers and take a look.
--
You received this message because you are subscribed to the Google Groups "xnat_discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xnat_discussi...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/xnat_discussion/1a7a8d21-74db-499d-bbf6-a3dc9df7880en%40googlegroups.com.