search

cytomine / Cytomine-core

Cytomine-Core is the main web server implementing the Cytomine API
http://doc.cytomine.org
Apache License 2.0
70 stars 29 forks source link

Runtime exception when fetching annotations created by a job with no term or multiple terms #1136

Closed elodieburtin closed 5 years ago

elodieburtin commented 5 years ago

When requesting GET /api/annotation.json?project={idProject}&showImage=true&multipleTerm=true&users={idUserJob} or GET /api/annotation.json?project={idProject}&showImage=true&noTerm=true&users={idUserJob}, the system replies with a Grails Runtime Exception.

If removing query parameter showImage=true, it works fine. However, it fails again if adding showUser=true (did not test all others parameters though). The issue only appears if the users query parameter includes userJobs, there is no issue with regular users.

Stack Trace for GET /api/annotation.json?project={idProject}&showImage=true&multipleTerm=true&users={idUserJob}:

org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "ai" Position: 20 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254) at net.bull.javamelody.JdbcWrapper.doExecute(JdbcWrapper.java:396) at net.bull.javamelody.JdbcWrapper$StatementInvocationHandler.invoke(JdbcWrapper.java:135) at net.bull.javamelody.JdbcWrapper$DelegatingInvocationHandler.invoke(JdbcWrapper.java:282) at be.cytomine.ontology.AnnotationListingService.selectGenericAnnotation(AnnotationListingService.groovy:81) at be.cytomine.ontology.AnnotationListingService.executeRequest(AnnotationListingService.groovy:57) at be.cytomine.ontology.AnnotationListingService.listGeneric(AnnotationListingService.groovy:51) at GrailsMelodyGrailsPlugin$_closure4_closure16_closure17.doCall(GrailsMelodyGrailsPlugin.groovy:184) at be.cytomine.api.ontology.RestAnnotationDomainController.createRequest(RestAnnotationDomainController.groovy:409) at be.cytomine.api.ontology.RestAnnotationDomainController.doSearch(RestAnnotationDomainController.groovy:217) at be.cytomine.api.ontology.RestAnnotationDomainController.search(RestAnnotationDomainController.groovy:126) at grails.plugin.cache.web.filter.PageFragmentCachingFilter.doFilter(PageFragmentCachingFilter.java:198) at grails.plugin.cache.web.filter.AbstractFilter.doFilter(AbstractFilter.java:63) at net.bull.javamelody.JspWrapper.invoke(JspWrapper.java:151) at net.bull.javamelody.JdbcWrapper$DelegatingInvocationHandler.invoke(JdbcWrapper.java:282) at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:203) at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:181) at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53) at cytomine.web.APIAuthentificationFilters.doFilter(APIAuthentificationFilters.groovy:46) at grails.plugin.springsecurity.web.authentication.RequestHolderAuthenticationFilter.doFilter(RequestHolderAuthenticationFilter.java:49) at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:82) 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:748)

Stack trace for /api/annotation.json?project={idProject}&noTerm=true&users={idUserJob}&showUser=true:

org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "u" Position: 20 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254) at net.bull.javamelody.JdbcWrapper.doExecute(JdbcWrapper.java:396) at net.bull.javamelody.JdbcWrapper$StatementInvocationHandler.invoke(JdbcWrapper.java:135) at net.bull.javamelody.JdbcWrapper$DelegatingInvocationHandler.invoke(JdbcWrapper.java:282) at be.cytomine.ontology.AnnotationListingService.selectGenericAnnotation(AnnotationListingService.groovy:81) at be.cytomine.ontology.AnnotationListingService.executeRequest(AnnotationListingService.groovy:57) at be.cytomine.ontology.AnnotationListingService.listGeneric(AnnotationListingService.groovy:51) at GrailsMelodyGrailsPlugin$_closure4_closure16_closure17.doCall(GrailsMelodyGrailsPlugin.groovy:184) at be.cytomine.api.ontology.RestAnnotationDomainController.createRequest(RestAnnotationDomainController.groovy:409) at be.cytomine.api.ontology.RestAnnotationDomainController.doSearch(RestAnnotationDomainController.groovy:217) at be.cytomine.api.ontology.RestAnnotationDomainController.search(RestAnnotationDomainController.groovy:126) at grails.plugin.cache.web.filter.PageFragmentCachingFilter.doFilter(PageFragmentCachingFilter.java:198) at grails.plugin.cache.web.filter.AbstractFilter.doFilter(AbstractFilter.java:63) at net.bull.javamelody.JspWrapper.invoke(JspWrapper.java:151) at net.bull.javamelody.JdbcWrapper$DelegatingInvocationHandler.invoke(JdbcWrapper.java:282) at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:203) at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:181) at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53) at cytomine.web.APIAuthentificationFilters.doFilter(APIAuthentificationFilters.groovy:46) at grails.plugin.springsecurity.web.authentication.RequestHolderAuthenticationFilter.doFilter(RequestHolderAuthenticationFilter.java:49) at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:82) 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:748)

geektortoise commented 5 years ago

Note : First generated request is

SELECT a.id as id, ai.original_filename as originalfilename 
FROM algo_annotation a , algo_annotation_term aat, algo_annotation_term aat2 
WHERE true
AND a.id = aat.annotation_ident
 AND a.id = aat2.annotation_ident
 AND aat.id <> aat2.id 
 AND aat.term_id <> aat2.term_id 
AND a.project_id = 333
AND a.user_id IN (907018)
ORDER BY  a.id desc

a join with image instance table is missing