Task #3961 (closed)
Bug: select ... in (:ids) possibly limited
| Reported by: | jamoore | Owned by: | |
|---|---|---|---|
| Priority: | blocker | Milestone: | OMERO-Beta4.3 |
| Component: | ORM | Version: | n.a. |
| Keywords: | n.a. | Cc: | jason, jburel, atarkowska |
| Resources: | n.a. | Referenced By: | n.a. |
| References: | n.a. | Remaining Time: | 0.0d |
| Sprint: | 2011-01-27 (4) |
Description
Some databases have limits on the number of ids which can be passed to an in clause. If more ids are passed, some possible options are:
- throw an exception
- iterate over the list in batches
- ...
Change History (8)
comment:1 Changed 9 years ago by jmoore
- Cc jburel atarkowska added
- Sprint set to 2011-01-27 (4)
comment:2 Changed 9 years ago by jmoore
- Status changed from new to accepted
comment:3 Changed 9 years ago by jmoore
- Remaining Time set to 1
comment:4 Changed 9 years ago by jmoore
Quite a bit of discussion on the forums / bug trackers, but not much in the way of corrections, especially not for HQL (one patch that doesn't apply exists for Criteria).
comment:5 Changed 9 years ago by jmoore
Here are the values which don't match ":id":
~/code/git $ git grep -E '[[:space:]]in[[:space:]]\(.*:.*\)' -- '*.py' '*.java' | grep -v :ids
components/insight/SRC/org/openmicroscopy/shoola/env/data/OMEROGateway.java: sql += "where g.name in (:names)";
components/insight/SRC/org/openmicroscopy/shoola/env/data/OMEROGateway.java: sb.append(" and link.child.id in (:childIDs)");
(4 more times)
components/insight/SRC/org/openmicroscopy/shoola/env/data/OMEROGateway.java: sb.append(" and img.details.owner.id in (:ownerIds)");
components/insight/SRC/org/openmicroscopy/shoola/env/data/OMEROGateway.java: +" where m.parent.id in (:gids)", p);
components/server/src/ome/logic/MetadataImpl.java: sb.append(" and ann.ns is not null and ann.ns in (:include)");
components/server/src/ome/logic/MetadataImpl.java: sb.append(" and (ann.ns is null or ann.ns not in (:exclude))");
components/server/src/ome/logic/MetadataImpl.java: sb.append(" and ann.ns is not null and ann.ns in (:include)");
components/server/src/ome/logic/MetadataImpl.java: sb.append(" and (ann.ns is null or ann.ns not in (:exclude))");
components/server/src/ome/logic/PojosImpl.java: + "where img in (:list)";
components/server/src/ome/logic/PojosImpl.java: + "where p in (:list)";
components/server/src/ome/services/delete/AnnotationDeleteSpec.java: and.and(alias + ".ns in (:excludes)");
components/server/src/ome/services/delete/BaseDeleteSpec.java: * delete ROOT2 where id in (select ROOT2.id from C join C.b ROOT1 join b.a ROOT0 where ROOT0.id = :id)
components/server/src/ome/services/search/SearchAction.java: qb.and(String.format("%sid in (:%s) ", path, unique));
components/server/src/ome/services/sessions/SessionManagerImpl.java: sb.append("and s.userAgent in (:agents) ");
components/tools/OmeroPy/src/omero/gateway/__init__.py: "exists ( select gem from GroupExperimenterMap as gem where gem.child = e.id and gem.parent.id in (:gids)) order by e.omeName"
(1 more time)
components/tools/OmeroPy/test/integration/search.py: "where im.id in (:oids) " \
components/tools/OmeroPy/test/integration/tickets2000.py: "and ev.id in (select id from Event where experimenter.id=:uid and time > :start and time < :end)"
components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: sql = "select g from ExperimenterGroup as g where g.name not in (:default_names)"
components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: "where tag.id in (:tids)"
(4 more times)
components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: "and a.details.group.id=:gid and (a.ns not in (:nss) or a.ns is null) "
(5 more times)
comment:6 Changed 9 years ago by jmoore
- Owner jmoore deleted
- Status changed from accepted to new
comment:7 Changed 9 years ago by jmoore
- Remaining Time changed from 1 to 0
- Resolution set to fixed
- Status changed from new to closed
Added new method: SqlAction.rewriteHql which gets a chance to modify any HQL. At the moment, it's looking for (:ids) where the "ids" argument is a list with more than 500 items. This will need some cleaning up and refactoring, but seems to work. Commit will occur with the story.
comment:8 Changed 7 years ago by jmoore <josh@…>
(In [b1e65fc735c3902d624eafa0ff889c72ccead5f1/ome.git] on branch develop) Add HQL rewrite to SqlAction for large in-clauses (Fix #3961)
- omero.db.temp_ids
- handling of other names: childID, etc.
- test postgres dialect
- try performance difference for PG
Note: See
TracTickets for help on using
tickets.
You may also have a look at Agilo extensions to the ticket.
Initial list of locations using this notation (not all of them would necessarily have 1000+ ids):
components/blitz/src/ome/services/blitz/impl/RoiI.java: "r.image i join fetch r.shapes where r.id in (:ids)"; components/blitz/src/ome/services/blitz/impl/RoiI.java: this.and("r.id in (:ids)"); components/blitz/src/ome/services/blitz/impl/ScriptI.java: qb.and("o.id not in (:ids) "); components/blitz/src/ome/services/blitz/impl/TimelineI.java: + "and share.id in (:ids) " components/blitz/test/ome/services/blitz/test/DeleteITest.java: sql = "select i from Annotation as i where i.id in (:ids)"; components/blitz/test/ome/services/blitz/test/DeleteITest.java: sql = "select i from Annotation as i where i.id in (:ids)"; components/insight/SRC/org/openmicroscopy/shoola/env/data/OMEROGateway.java: buf.append(" and owner.id in (:ids)"); components/insight/SRC/org/openmicroscopy/shoola/env/data/OMEROGateway.java: buf.append("where owner.id in (:ids)"); components/insight/SRC/org/openmicroscopy/shoola/env/data/OMEROGateway.java: sb.append("where ail.child.id in (:ids)"); components/model/resources/ome/util/actions/default.properties:sql_action.id_not_in=and id not in (:ids) components/server/src/ome/formats/OMEROMetadataStore.java: "where i.id in (:ids)", p); components/server/src/ome/logic/MetadataImpl.java: sb.append("where channel.id in (:ids)"); components/server/src/ome/logic/MetadataImpl.java: sb.append("where ann.id in (:ids)"); components/server/src/ome/logic/MetadataImpl.java: sb.append(" and ann.id not in (:ids)"); components/server/src/ome/logic/MetadataImpl.java: sb.append(" and ann.id not in (:ids)"); components/server/src/ome/logic/PojosImpl.java: + " link where link.parent.id in (:ids)" components/server/src/ome/logic/PojosImpl.java: + "where pdl.parent.id in (:ids) order by dil.child.id"); components/server/src/ome/logic/RenderingSettingsImpl.java: "where pix.id in (:ids)"; components/server/src/ome/logic/RenderingSettingsImpl.java: "where i.id in (:ids)"; components/server/src/ome/logic/RenderingSettingsImpl.java: "where p.id in (:ids)"; components/server/src/ome/logic/RenderingSettingsImpl.java: "where s.id in (:ids)"; components/server/src/ome/logic/RenderingSettingsImpl.java: "where d.id in (:ids)"; components/server/src/ome/logic/RenderingSettingsImpl.java: "where p.id in (:ids)"; components/server/src/ome/logic/RenderingSettingsImpl.java: "rdef.pixels.id in (:ids) and " + components/server/src/ome/logic/RenderingSettingsImpl.java: "rdef.pixels.id in (:ids) and " + components/server/src/ome/services/RawPixelsBean.java: "where p.id in (:ids)", new Parameters().addIds(pixelsIds)); components/server/src/ome/services/ThumbnailCtx.java: "where r.details.owner.id = :id and r.pixels.id in (:ids)", components/server/src/ome/services/ThumbnailCtx.java: "and r.pixels.image.id in (:ids)", components/server/src/ome/services/ThumbnailCtx.java: "and r.pixels.id in (:ids)", components/server/src/ome/services/ThumbnailCtx.java: "and t.pixels.id in (:ids)", params); components/server/src/ome/services/ThumbnailCtx.java: "and t.pixels.id in (:ids)", params); components/server/src/ome/services/ThumbnailCtx.java: "select p from Pixels as p where id in (:ids)", parameters); components/server/src/ome/services/delete/AnnotationDeleteSpec.java: qb.and("child.id in (:ids)"); components/server/src/ome/services/delete/DeleteBean.java: + "where image.id in (:ids) )"); components/server/src/ome/services/delete/DeleteBean.java: + "where image.id in (:ids)"); components/server/src/ome/services/query/CollectionCountQueryDefinition.java: + (check("ids") ? "where target.id in (:ids)" : "") components/server/src/ome/services/query/PojosGetImagesQueryDefinition.java: qb.and("img.id in (:ids)"); components/server/src/ome/services/query/PojosGetImagesQueryDefinition.java: qb.and("ds.id in (:ids)"); components/server/src/ome/services/query/PojosGetImagesQueryDefinition.java: qb.and("prj.id in (:ids)"); components/server/src/ome/services/query/PojosLoadHierarchyQueryDefinition.java: sb.append("where this.id in (:ids)"); components/server/src/ome/services/sharing/BlobShareStore.java: .createQuery("select id, data from Share where id in (:ids)"); components/server/src/ome/services/sharing/ShareBean.java: qb.and("share2.id in (:ids) and share2.id in "); components/server/src/ome/services/sharing/ShareBean.java: qb.and("share.id in (:ids)"); components/server/src/ome/services/sharing/ShareBean.java: + "where e.id in (:ids)", new Parameters() components/server/src/ome/services/sharing/ShareBean.java: * "join fetch sh.details.owner where sh.id in (:ids) ", components/server/src/ome/services/sharing/ShareBean.java: + "join fetch sh.owner where sh.id in (:ids) ", components/server/test/ome/server/itests/sharing/SharingTest.java: String sql = "select im from Image im where im.id in (:ids) order by im.name"; components/server/test/ome/server/utests/QueryBuilderMockTest.java: qb.and("img.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("select p from WellSample as p where p.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("select p from Image as p where p.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("select p from WellSample as p where p.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("select p from Image as p where p.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: String sql = "select i from Image as i where i.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: String sql = "select i from Image as i where i.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: String sql = "select i from Plate as i where i.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql = "select d from Shape as d where d.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql = "select d from Shape as d where d.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql = "select i from Annotation as i where i.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql = "select i from Annotation as i where i.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("select p from WellSample as p where p.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("select p from Image as p where p.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("select i from Annotation as i where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("select p from WellSample as p where p.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("select p from Image as p where p.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("select i from Annotation as i where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql = "select i from Image i where i.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql = "select d from Dataset d where d.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql = "select i from Image i where i.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: String sql = "select a from Annotation as a where a.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("select i from Annotation as i where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql = "select i from Annotation as i where i.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql = "select i from Annotation as i where i.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql = "select i from Annotation as i where i.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql = "select i from Annotation as i where i.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql = "select i from Annotation as i where i.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql = "select i from Annotation as i where i.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql = "select i from Annotation as i where i.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql = "select i from Annotation as i where i.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql += "where a.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sql += "where a.id in (:ids)"; components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: sb.append("where i.id in (:ids)"); components/tools/OmeroJava/test/integration/DeleteServiceTest.java: String sql = "select a from Annotation as a where a.id in (:ids)"; components/tools/OmeroJava/test/integration/RenderingSettingsServiceTest.java: "where p.id in (:ids)"; components/tools/OmeroPy/src/omero/gateway/__init__.py: sql = "select e from ExperimenterGroup as e where e.id in (:ids)" components/tools/OmeroPy/src/omero/gateway/__init__.py: sql = "select e from ExperimenterGroup as e where e.id in (:ids)" components/tools/OmeroPy/src/omero/gateway/__init__.py: sql = "select im from Image im join fetch im.details.owner join fetch im.details.group where im.id in (:ids) order by im.name" components/tools/OmeroPy/src/omero/gateway/__init__.py: sql = "select ds from Dataset ds join fetch ds.details.owner join fetch ds.details.group where ds.id in (:ids) order by ds.name" components/tools/OmeroPy/src/omero/gateway/__init__.py: sql = "select pr from Project pr join fetch pr.details.owner join fetch pr.details.group where pr.id in (:ids) order by pr.name" components/tools/OmeroPy/src/omero/gateway/__init__.py: sql = "select pl from Plate pl join fetch pl.details.owner join fetch pl.details.group where pl.id in (:ids) order by pl.name" components/tools/OmeroPy/src/omero/util/cleanse.py: "select o.id from %s as o where o.id in (:ids)" % self.object_type, components/tools/OmeroPy/test/integration/ishare.py: sql = "select im from Image im where im.id in (:ids) order by im.name" components/tools/OmeroPy/test/integration/ishare.py: "where ds.id in (:ids) order by ds.name" components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: sql = "select e from Experimenter as e where e.id in (:ids)" components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: sql = "select e from ExperimenterGroup as e where e.id in (:ids)" components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: sql = "select im from Image im join fetch im.details.owner join fetch im.details.group where im.id in (:ids) order by im.name" components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: sql = "select ds from Dataset ds join fetch ds.details.owner join fetch ds.details.group where ds.id in (:ids) order by ds.name" components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: sql = "select pr from Project pr join fetch pr.details.owner join fetch pr.details.group where pr.id in (:ids) order by pr.name" components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: sql = "select sc from Screen sc join fetch sc.details.owner join fetch sc.details.group where sc.id in (:ids) order by sc.name" components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: sql = "select pl from Plate pl join fetch pl.details.owner join fetch pl.details.group where pl.id in (:ids) order by pl.name" components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: "where wl.id in (:ids)" components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: sql = "select a from TagAnnotation a where a.id in (:ids) " components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: sql = "select a from CommentAnnotation a where a.id in (:ids) " components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: sql = "select a from FileAnnotation a where a.id in (:ids) " components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: "where ds.id in (:ids) order by ds.name" components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: sql = "select im from Image im join fetch im.details.owner join fetch im.details.group where im.id in (:ids) order by im.name" components/tools/OmeroWeb/omeroweb/webclient/webclient_gateway.py: "where e.id in (:ids) order by e.omeName"