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 13 years ago by jmoore
- Cc jburel atarkowska added
- Sprint set to 2011-01-27 (4)
comment:2 Changed 13 years ago by jmoore
- Status changed from new to accepted
comment:3 Changed 13 years ago by jmoore
- Remaining Time set to 1
comment:4 Changed 13 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 13 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 13 years ago by jmoore
- Owner jmoore deleted
- Status changed from accepted to new
comment:7 Changed 13 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 12 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
Initial list of locations using this notation (not all of them would necessarily have 1000+ ids):