• Sign In
  1. Messages

Sorting fails in MS2 module Compare

Installation Forum (Inactive)

View Message

view list print
Sorting fails in MS2 module Compare tvaisar  2015-07-16 06:58
Status: Closed
 
Hi,

we have just installed a new instance of Labkey server 15.1 with postgres and start to feel out way around. Ran into issue when comparing multiple sets of proteomics data. Selecting few runs and comparing them (any comparison - ProteinProphet, Search Engine, etc. has the same issue) works fine, but when I try to sort on any column, the column returns with blank content - other columns for other samples are OK, but anything related to the samples which column was used to sort is blank. Going into Admin Console and looking at Errors - this is what I get.
Any suggestions?
Thanks,
Tomas Vaisar

ERROR Table 2015-07-16 06:30:24,302 t: http-bio-8080-exec-21 : SQL [19289]
    SELECT *
    FROM (
    SELECT
    InstanceCount AS InstanceCount,
    ctq$Proteins$.SeqId$BestName AS SeqId_fs_BestName,
    ctq$Proteins$.SeqId$BestGeneName AS SeqId_fs_BestGeneName,
    ctq$Proteins$.SeqId$FirstGENE_NAME AS SeqId_fs_FirstGENE_NAME,
    ctq$Proteins$.SeqId$FirstSWISS_PROT AS SeqId_fs_FirstSWISS_PROT,
    ctq$Proteins$.SeqId$FirstSWISS_PROT_ACCN AS SeqId_fs_FirstSWISS_PROT_ACCN,
    ctq$Proteins$.SeqId$FirstGEN_INFO AS SeqId_fs_FirstGEN_INFO,
    ctq$Proteins$.SeqId$SeqId AS SeqId_fs_SeqId,
    SortPattern AS SortPattern
    FROM (SELECT pvt.NormalizedId, pvt.ProteinCount, pvt.Proteins,
    MAX(pvt.CTAGG_MIN_ProteinGroupId) AS CTAGG_MIN_ProteinGroupId,
    MAX(pvt.CTAGG_COUNT_ProteinGroupId) AS CTAGG_COUNT_ProteinGroupId,
    (0) AS InstanceCount,
    (0) AS SortPattern
    FROM (
    SELECT aggf.NormalizedId, aggf.ProteinCount, aggf.Proteins,
    CTAGG_MIN_ProteinGroupId,
    CTAGG_COUNT_ProteinGroupId
    FROM (
    SELECT * FROM (
    SELECT
    grp.NormalizedId AS NormalizedId,
    grp.ProteinCount AS ProteinCount,
    grp.Proteins AS Proteins,
    grp.P763629254fs_ProteinProphetFileId_fs_Run AS P763629254fs_ProteinProphetFileId_fs_Run,
    grp.CTAGG_MIN_ProteinGroupId AS CTAGG_MIN_ProteinGroupId,
    grp.CTAGG_COUNT_ProteinGroupId AS CTAGG_COUNT_ProteinGroupId
    FROM (SELECT src.NormalizedId,
    src.ProteinCount,
    src.Proteins,
    src.P763629254fs_ProteinProphetFileId_fs_Run,
    MIN(src.ProteinGroupId) AS CTAGG_MIN_ProteinGroupId,
    COUNT(src.ProteinGroupId) AS CTAGG_COUNT_ProteinGroupId
    FROM (
    SELECT
    (SELECT COUNT (DISTINCT SeqId) FROM bogusTable n, ms2.proteingroupmemberships pgm WHERE n.ProteinGroupId = pgm.ProteinGroupId and n.NormalizedId = bogusTable.NormalizedId) AS ProteinCount,
    bogusTable.ProteinGroupId AS ProteinGroupId,
    bogusTable.NormalizedId AS NormalizedId,
    bogusTable.NormalizedId AS Proteins,
    bogusTable$ProteinGroupId_fs_ProteinProphetFileId$.run AS P763629254fs_ProteinProphetFileId_fs_Run
    FROM (SELECT * FROM (SELECT * FROM bogusTable) x
    WHERE ((ProteinGroupId IN (SELECT ProteinGroupId FROM (SELECT
    ProteinGroupMemberships.proteingroupid AS ProteinGroupId
    FROM ms2.proteingroupmemberships ProteinGroupMemberships ) x)))) bogusTable
    
        LEFT OUTER JOIN ms2.proteingroups bogusTable$ProteinGroupId$ ON (bogusTable.ProteinGroupId = bogusTable$ProteinGroupId$.rowid)
    
        LEFT OUTER JOIN ms2.proteinprophetfiles bogusTable$ProteinGroupId_fs_ProteinProphetFileId$ ON (bogusTable$ProteinGroupId$.proteinprophetfileid = bogusTable$ProteinGroupId_fs_ProteinProphetFileId$.rowid)
    ) AS src
    GROUP BY src.NormalizedId,
    src.ProteinCount,
    src.Proteins,
    src.P763629254fs_ProteinProphetFileId_fs_Run) grp
    ) AS agg
    ) AS aggf
    ) AS pvt
    GROUP BY pvt.NormalizedId, pvt.ProteinCount, pvt.Proteins) ctq
    
        LEFT OUTER JOIN
        (
            SELECT child.NormalizedId,
                array_to_string(array_agg(child$SeqId$.seqid), '{@~^') AS SeqId$SeqId,
                array_to_string(array_agg(child$SeqId$.protsequence), '{@~^') AS SeqId$ProtSequence,
                array_to_string(array_agg(child$SeqId$.hash), '{@~^') AS SeqId$Hash,
                array_to_string(array_agg(child$SeqId$.description), '{@~^') AS SeqId$Description,
                array_to_string(array_agg(child$SeqId$.sourceid), '{@~^') AS SeqId$SourceId,
                array_to_string(array_agg(child$SeqId$.sourceversion), '{@~^') AS SeqId$SourceVersion,
                array_to_string(array_agg(child$SeqId$.insertdate), '{@~^') AS SeqId$InsertDate,
                array_to_string(array_agg(child$SeqId$.changedate), '{@~^') AS SeqId$ChangeDate,
                array_to_string(array_agg(child$SeqId$.sourcechangedate), '{@~^') AS SeqId$SourceChangeDate,
                array_to_string(array_agg(child$SeqId$.sourceinsertdate), '{@~^') AS SeqId$SourceInsertDate,
                array_to_string(array_agg(child$SeqId$.orgid), '{@~^') AS SeqId$OrgId,
                array_to_string(array_agg(child$SeqId$.mass), '{@~^') AS SeqId$Mass,
                array_to_string(array_agg(child$SeqId$.bestname), '{@~^') AS SeqId$BestName,
                array_to_string(array_agg(child$SeqId$.bestgenename), '{@~^') AS SeqId$BestGeneName,
                array_to_string(array_agg(child$SeqId$.length), '{@~^') AS SeqId$Length,
                array_to_string(array_agg(child$SeqId$.deleted), '{@~^') AS SeqId$Deleted,
                array_to_string(array_agg(child$SeqId$.sourceid), '{@~^') AS SeqId$Source,
                array_to_string(array_agg(child$SeqId$.seqid), '{@~^') AS SeqId$CustomAnnotations,
                array_to_string(array_agg((SELECT MIN(Identifier) FROM prot.identifiers i, prot.identtypes it WHERE i.IdentTypeId = it.IdentTypeId AND it.Name = 'IPI' AND i.SeqId = child$SeqId$.SeqId)), '{@~^') AS SeqId$FirstIPI,
                array_to_string(array_agg((SELECT MIN(Identifier) FROM prot.identifiers i, prot.identtypes it WHERE i.IdentTypeId = it.IdentTypeId AND it.Name = 'GeneName' AND i.SeqId = child$SeqId$.SeqId)), '{@~^') AS SeqId$FirstGENE_NAME,
                array_to_string(array_agg((SELECT MIN(Identifier) FROM prot.identifiers i, prot.identtypes it WHERE i.IdentTypeId = it.IdentTypeId AND it.Name = 'SwissProt' AND i.SeqId = child$SeqId$.SeqId)), '{@~^') AS SeqId$FirstSWISS_PROT,
                array_to_string(array_agg((SELECT MIN(Identifier) FROM prot.identifiers i, prot.identtypes it WHERE i.IdentTypeId = it.IdentTypeId AND it.Name = 'SwissProtAccn' AND i.SeqId = child$SeqId$.SeqId)), '{@~^') AS SeqId$FirstSWISS_PROT_ACCN,
                array_to_string(array_agg((SELECT MIN(Identifier) FROM prot.identifiers i, prot.identtypes it WHERE i.IdentTypeId = it.IdentTypeId AND it.Name = 'GI' AND i.SeqId = child$SeqId$.SeqId)), '{@~^') AS SeqId$FirstGEN_INFO
            FROM (SELECT DISTINCT NormalizedId, SeqId FROM bogusTable n, ms2.proteingroupmemberships pgm WHERE n.ProteinGroupId = pgm.ProteinGroupId) child
        LEFT OUTER JOIN prot.sequences child$SeqId$ ON (child.SeqId = child$SeqId$.seqid)
            GROUP BY child.NormalizedId
        ) ctq$Proteins$ ON (ctq.Proteins = ctq$Proteins$.NormalizedId)) x
    
    ORDER BY InstanceCount DESC, SortPattern DESC
    LIMIT 5001

    org.labkey.api.data.SqlExecutingSelector$ExecutingResultSetFactory.handleSqlException(SqlExecutingSelector.java:436)
    org.labkey.api.data.BaseSelector.handleResultSet(BaseSelector.java:268)
    org.labkey.api.data.SqlExecutingSelector.getResultSet(SqlExecutingSelector.java:121)
    org.labkey.api.data.TableSelector.getResults(TableSelector.java:274)
    org.labkey.api.data.TableSelector$1.call(TableSelector.java:289)
ERROR ExceptionUtil 2015-07-16 06:30:24,304 http-bio-8080-exec-21 : Exception detected and logged to mothership:
org.springframework.jdbc.BadSqlGrammarException: ExecutingSelector; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: relation "bogustable" does not exist
  Position: 1871
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:276)
    at org.labkey.api.data.ExceptionFramework$1.translate(ExceptionFramework.java:37)
    at org.labkey.api.data.ExceptionFramework$1.translate(ExceptionFramework.java:31)
    at org.labkey.api.data.SqlExecutingSelector$ExecutingResultSetFactory.handleSqlException(SqlExecutingSelector.java:441)
    at org.labkey.api.data.BaseSelector.handleResultSet(BaseSelector.java:268)
    at org.labkey.api.data.SqlExecutingSelector.getResultSet(SqlExecutingSelector.java:121)
    at org.labkey.api.data.TableSelector.getResults(TableSelector.java:274)
    at org.labkey.api.data.TableSelector$1.call(TableSelector.java:289)
    at org.labkey.api.data.TableSelector$1.call(TableSelector.java:286)
    at org.labkey.api.data.AsyncQueryRequest$1.run(AsyncQueryRequest.java:109)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "bogustable" does not exist
  Position: 1871
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:406)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:286)
    at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at org.labkey.api.data.dialect.StatementWrapper.executeQuery(StatementWrapper.java:916)
    at org.labkey.api.data.SqlExecutingSelector$ExecutingResultSetFactory.executeQuery(SqlExecutingSelector.java:372)
    at org.labkey.api.data.SqlExecutingSelector$ExecutingResultSetFactory.getResultSet(SqlExecutingSelector.java:322)
    at org.labkey.api.data.BaseSelector.handleResultSet(BaseSelector.java:254)
    ... 6 more
 
 
Jon (LabKey DevOps) responded:  2015-07-17 14:30
Hi Tomas,

The error indicates that the table itself doesn't exist or isn't being referenced correctly. However, I'm not seeing a problem with sorting as you stated.

In my local instance and our demo here (https://www.labkey.org/project/home/CPAS/labelfreedemo/begin.view?), I'm able to select a few runs, click Compare -> then an option, then get to the table and sort any column there without issue.

Can you give us a step-by-step repro of what you're doing? Can you access the link above and see if you get the same problem there as well?

Regards,

Jon
 
tvaisar responded:  2015-07-17 14:53
Hi Jon,
As I indicated we have a new installation of Labkey server (just updated to 15.2) with posgreSQL. I uploaded several runs of Comet searches processed by TPP without any problem. I then select several of them and go to Compare > Protein Prophet. I then set ProteinProphet probablity to >= 0.95, PeptideProphet probablity to >=0.9, Compare by Run, then "for each run select "show the protein...."(2nd option) and lastly I check "Normalize groups". Then I click "Compare" and I get correct view and can manipulate it (e.g. modify it adding columns etc.), I can export to excel and get correct output in Excel, BUT when I click on any column header to "sort" the returned table misses all data in all column for a given sample/pep.xml.
I just did it with your demo data per link you provided and there it works as expected - I get the data sorted as selected. So the result is somehow specific to our system.

Tomas
 
tvaisar responded:  2015-07-17 15:05
Jon,

I have found the reason for the issue I am seeing. There is actually no problem - the only problem is that the behavior of Labkey server has changed for this function (we were stuck on version 9.2 with our old system). In the 9.2 version the Compare puts the highest/lowest value (according to descending/ascending sort) to the top of the column. In the 15.2 version the empty cells are sorted to the top of the column in both descending and ascending resulting in empty space for a given sample at the top. In our case it happened to be more than 1000 rows and therefore it did not show on the screen. It happens the same way on your system as well.
I would think it is kind of unfortunate feature, but it is not a technical problem.

Sorry for confusion,

Tomas
 
Jon (LabKey DevOps) responded:  2015-07-17 15:10
Hi Tomas,

Thanks for letting us know! I'll let our developers know about this so they can look into making a feature change within the MS2 module to accommodate for this.

Regards,

Jon
 
Jon (LabKey DevOps) responded:  2015-07-17 15:49
Logged as https://www.labkey.org/issues/home/Developer/issues/details.view?issueId=23780 on developer side.
 
tvaisar responded:  2015-07-17 16:01
Thanks Jon,
I should note that it does the same in the "Search Engine Protein" option for Compare. It seems that it is generic for all Compare.

Tomas
 
Jon (LabKey DevOps) responded:  2024-02-29 10:39
https://www.labkey.org/issues/home/Developer/issues/details.view?issueId=23780 has been marked as a WontFix. Sorry for any inconvenience this may cause.