Uploaded image for project: 'OpenIDM'
  1. OpenIDM
  2. OPENIDM-14067

500 response for numeric queryFilter comparisons with PostgreSQL repo type: explicit table


    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 7.0.0
    • Fix Version/s: 7.0.0
    • Component/s: None
    • Labels:
    • Target Version/s:
    • Verified Version/s:
    • Story Points:
    • Sprint:
      2020.01 - IDM


      Reproduction using PostgreSQL with explicit tables as repo:

      1) We add a "testnum" integer column to the managed user table:

      CREATE TABLE openidm.managed_user (
          objectid VARCHAR(38) NOT NULL,
          rev VARCHAR(38) NOT NULL,
          username VARCHAR(255),
          password VARCHAR(511),
          testnum INT,

      2) We declare the column in repo.jdbc.json (using the sample repo.jdbc-mysql-explicit-managed-user.json:

      "explicitMapping" : {
          "managed/user": {
              "table" : "managed_user",
              "objectToColumn": {
                  "_id": "objectid",
                  "_rev" : "rev",
                  "userName": "username",
                  "password": "password",
                  "testnum": {"column" : "testnum", "type" : "NUMBER"},

      3) We create a new user with the curl below:

      curl --header "Content-Type: application/json" --header "X-OpenIDM-Password: openidm-admin" --header "X-OpenIDM-Username: openidm-admin" --data '{"userName": "rsutter", "telephoneNumber": "6669876987", "givenName": "rick", "description": "Just another user", "testnum": 3, "sn": "sutter", "mail": "rick@example.com", "password": "Th3Password"}' --request POST "http://localhost:8080/openidm/managed/user?_action=create"
          "_id": "06958be8-4ade-4b67-a477-ddb9b455466a",
          "_rev": "0",
          "userName": "rsutter",
          "testnum": 3,

      4) We query the new user using  _queryFilter=testnum+lt+5:

      curl --header "Content-Type: application/json" --header "X-OpenIDM-Password: openidm-admin" --header "X-OpenIDM-Username: openidm-admin" --request GET "http://localhost:8080/openidm/managed/user?_queryFilter=testnum+lt+5"
          "code": 500,
          "reason": "Internal Server Error",
          "message": "DB reported failure executing query."

      Additional Notes:

      Stack trace:

      WARNING: Resource exception: 500 Internal Server Error: "DB reported failure executing query."
      org.forgerock.json.resource.InternalServerErrorException: DB reported failure executing query.
      	at org.forgerock.openidm.repo.jdbc.impl.query.TableQueries.query(TableQueries.java:325)
      	at org.forgerock.openidm.repo.jdbc.impl.ExplicitTableHandler.query(ExplicitTableHandler.java:562)
      	at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoServiceHandleQuery.handle(JDBCRepoServiceHandleQuery.java:259)
      	at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoServiceHandleQuery.handleQuery(JDBCRepoServiceHandleQuery.java:183)
      	at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.lambda$handleQuery$8(JDBCRepoService.java:637)
      	at org.forgerock.openidm.metrics.MetricsCollector.time(MetricsCollector.java:112)

      PostgreSQL error message:

      ERROR: operator does not exist: integer < character varying
        Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
        Position: 725



          Issue Links



              • Assignee:
                jason.vincent jason vincent
                brayden.roth-white Brayden Roth-White
              • Votes:
                0 Vote for this issue
                2 Start watching this issue


                • Created: