Checking if a range contains a value broken in PQexecPrepared (works in psql)

Question

I have this (rather ugly, generated) prepared statement to fetch some game data. I try to check if a value ($3) is contained in spawn_level_range (which is an int4range) by doing $3<@quests.spawn_level_range:

SELECT quests.id,
       quests.base_attack,
       quests.base_strg,
       quests.base_accy,
       quests.base_hp,
       quests.name,
       quests.task,
       quests.image_url,
       quests.spawn_chance
FROM quests
WHERE (((quests.server_id=$1)
  AND ((quests.channel_id='all') OR (quests.channel_id=$2)))
  AND ($3<@quests.spawn_level_range))
ORDER BY RANDOM()
LIMIT 1;

This exact query works perfectly when pasted into psql when I prepend:

prepare test (varchar, varchar, int) AS

then run it with:

execute test('669105577238069249', '682205516667158549', 1);

However, for some reason, it just does not work in libpq.

When running the statement with PQexecPrepared, it raises the error:

ERROR:  malformed range literal: "1"
DETAIL:  Missing left parenthesis or bracket.

(note that 1 is what I'm trying to bind $3 to)

It seems like it's trying to interpret $3 as a range (rather than an integer) – which seems like a bug to me.

score 1 · Accepted Answer · answered Jun 19 '20 at 04:41

In your prepared statement, you explicitly declare the third parameter to be an integer.

In your PQprepare call (that you didn't show) you must have neglected to set the paramTypes argument to indicate the types of the parameters, so they are all unknown to PostgreSQL, and it infers the data type from the context.

Now there are two operators <@ for ranges:

anyrange <@ anyrange
anyelement <@ anyrange

Not knowing which one you want, PostgreSQL's data type resolution rules prefer the operator that has the same data type on both sides.

There are two possible solutions:

specify the correct type in the paramTypes argument of PQprepare
add an explicit type cast to the query: CAST ($3 AS integer)

Checking if a range contains a value broken in PQexecPrepared (works in psql)

1 Answers1