Breakdown Queries Broken in 1.8.0

[akval...@gmail.com]

We are getting a strange error running breakdown queries in v1.8.0. Breakdown queries used to work with v1.7.xx. We are running PostgreSQL on the backend.

Here is the error from the db log file:

2024-09-19 09:10:21.205 CDT [2517502] ERROR:  syntax error at or near "fr" at character 52
2024-09-19 09:10:21.205 CDT [2517502] STATEMENT:   select  count (distinct item_count) as item_count fr  ) a

Here is the error from the wildfly log:

2024-09-19 11:31:46,397 ERROR [edu.harvard.i2b2.crc.dao.CRCDAO] (Thread-897) Error while executing sql: org.postgresql.util.PSQLException: ERROR: syntax error at or near "fr"
  Position: 52
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2712)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2400)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:367)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
at edu.harvard.i2b2.crc.dao.setfinder.QueryResultGenerator.generateResult(QueryResultGenerator.java:302)
at edu.harvard.i2b2.crc.dao.setfinder.QueryExecutorHelperDao.runGenerator(QueryExecutorHelperDao.java:802)
at edu.harvard.i2b2.crc.dao.setfinder.QueryExecutorHelperDao.callResultGenerator(QueryExecutorHelperDao.java:710)
at edu.harvard.i2b2.crc.dao.setfinder.QueryExecutorHelperDao.executeQuery(QueryExecutorHelperDao.java:382)
at edu.harvard.i2b2.crc.dao.setfinder.QueryExecutorDao.executeSQL(QueryExecutorDao.java:564)
at edu.harvard.i2b2.crc.ejb.ExecRunnable.processQueryRequest(ExecRunnable.java:369)
at edu.harvard.i2b2.crc.ejb.ExecRunnable.run(ExecRunnable.java:199)
at java.lang.Thread.run(Thread.java:750)

[Jeff Klann]

Could you share the details of a breakdown in which you're seeing this error? Including the QT_BREAKDOWN_PATH information would be helpful.

Or does this happen on all the built-in breakdowns?

Thanks,

Jeff Klann

[akval...@gmail.com]

Hi Jeff,

This occurs with all our break down queries, which used to work. They are working in i2b2 1.8.1, so I am working on migrating our version rather than spending any time on trying to figure out why these are not working. We have also found other bugs (or maybe enhancements?) between the old UI and the new UI, specifically when it comes to term searching.

For example: When searching by domain (say procedures) the web UI uses the c_dimcode from the table_access, rather than the c_fullname, which broke our metadata. We had the impression that c_fullname was used for metadata and c_dimcode was used for the crc cell. This was a simple fix in that we just need to ensure the c_fullname = c_dimcode in table_access.

Second, the c_protected_access = 'Y' still requires the metadata table to exist. This is only an issue on our dev environment were we toggle versions of metadata on and off and sometimes forget to delete a row from the table_access when we are no longer building that version of the metadata.

We are still having other issues with search, where the message log claims to find terms, but then fails to render anything in the window. This is unique to our metadata (ENACT's appears to work). I will follow up with more details as I debug them. Since this only seems to affect our metadata (based on our internal EHR), simply using the published ENACT metadata (which is great!) sadly will not work for us.

-Andrew

[Jeff Klann]

We're looking into the bugs you reported. For the second issue, can you explain in a little more detail what you're trying to do?  When you say you toggle metadata on and off, are you changing C_VISUALATTRIBUTES in TABLE_ACCESS? When you say "c_protected_access = 'Y' still requires the metadata table to exist", what do you mean? That even hidden metadata rows that are protected create errors?

Thanks,

Jeff

[Andrew Vallejos]

Hi Jeff,

"When you say you toggle metadata on and off, are you changing C_VISUALATTRIBUTES in TABLE_ACCESS? When you say "c_protected_access = 'Y' still requires the metadata table to exist", what do you mean? That even hidden metadata rows that are protected create errors"

Let's say we have two versions of metadata, i2b2metdata.meta_vitals and i2b2metadata.meta_vitals_new. Both tables would have rows within TABLE_ACCESS, with c_protected_access being used to hide the table that we don't want present. In this example, let's say meta_vitals. So meta_vitals has c_protected_access set to 'Y'. Then we approve the new table and so meta_vitals_new, then becomes meta_vitals, and then in TABLE_ACCESS c_protected_access is set to 'N' for meta_vitals and 'Y' for meta_vitals_new. This is fine.

The problem arises when someone then does a search for a term across all categories. This throws an error because i2b2 expects meta_vitals_new to exist, even though we don't want anyone to be able to use it. Does that make sense?

-Andrew

--
You received this message because you are subscribed to a topic in the Google Groups "i2b2 Install Help" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/i2b2-install-help/iVH2aFswVpI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to i2b2-install-h...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/i2b2-install-help/b8678c35-3570-4e11-81ca-dddbec23d3afn%40googlegroups.com.

[Vaibhav Talap]

Hello Andrew,

We have replicated the same use case of meta_vitals and meta_vitals_new with c_protected_access as mentioned in the earlier thread.
We are able to search the terms after updating c_protected_access to 'Y' for in old and new web clients.
If possible, could you please elaborate more on the issue you are facing?


Thanks
Persistent Systems Support team.

[Jeff Klann]

I'm also not able to reproduce this. I created a table_access entry for a nonexistent table and set C_PROTECTED_ACCESS to 'Y' and I was able to search all categories with no errors. Can you provide more detail?

Also, are you still facing issue #3 (where ontology search fails) even when c_dimcode=c_fullname in table_access? This resolved the problem for all search issues we were having internally.

Thanks,

Jeff

[akval...@gmail.com]

Sorry for the delay, got sidetracked on other projects.

Everything seems to have resolved itself either though updating the dimcode or updating to 1.8.1.

Thanks!