JBDI - Eating up part of the SQL statement-CodePudding

This is my JDBI method

@Override
@SqlQuery("SELECT * FROM "   TABLE_NAME   " WHERE "   COLUMN_CAMPAIGN_ID   " = :campaignId AND "   COLUMN_STATUS
                  " = 0 AND "   COLUMN_STAGE_ID   " IS NULL AND "   COLUMN_MODIFICATION_DATE
                  " < NOW() - INTERVAL '<timeInterval>'")
List<Idea> getDraftIdeasBeforeTime(@Bind("campaignId") long campaignId,
                @Define("timeInterval") String timeInterval);

On execution, it breaks with the following error

ERROR [2021-10-22 10:06:00,041] com.addons.advanced.jobs.DraftIdeasJob: Exception received while trying to delete draft ideas : org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: org.postgresql.util.PSQLException: ERROR: argument of AND must be type boolean, not type timestamp without time zone
  Position: 127 [statement:"SELECT * FROM ideas WHERE campaign_id = :campaignId AND status = 0 AND stage_id IS NULL AND modification_date < NOW() - INTERVAL '<timeInterval>'", located:"SELECT * FROM ideas WHERE campaign_id = :campaignId AND status = 0 AND stage_id IS NULL AND modification_date ", rewritten:"/* IdeasDaoJdbi.getDraftIdeasBeforeTime */ SELECT * FROM ideas WHERE campaign_id = ? AND status = 0 AND stage_id IS NULL AND modification_date ", arguments:{ positional:{}, named:{campaignId:10}, finder:[]}]

As you can see, post location, it only considers the up until ... AND modification_date and ignores what comes after.

Why is this happening?

CodePudding user response：

You need to escape < (change to \\<) symbol in your query since it is the same as used by template engine.

EDIT: Adding to the answer for more specificity, the issue is at

" < NOW() - INTERVAL '<timeInterval>'"

which needs to be

" \\< NOW() - INTERVAL '<timeInterval>'"