parameterized query fails if 'containing' parameter is longer than field

[Hamish Moffatt]

I've got a table containing a VARCHAR, and if I query for that field 'containing' a parameter value longer than the VARCHAR, it fails with an error.

If I use a literal in the SQL, it works though.

$ /opt/firebird/bin/isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database 'localhost:/tmp/test.fdb';
SQL> create table test_table (
CON>     test_field varchar(10)
CON> );
SQL> commit ;

SQL> select * from test_table where test_field containing 'very_long_string';
SQL> 

Using a parameterized query from Python, though:

import fdb
con = fdb.connect('localhost:/tmp/test.fdb')
cur = con.cursor()
for row in cur.execute("select * from test_table where cast(test_field as varchar(50)) containing ?", ("string_longer_than_10_characters",)):
    print(row)

$ python3 test.py
Traceback (most recent call last):
  File "/home/hamish/test.py", line 5, in <module>
    for row in cur.execute("select * from test_table where test_field containing ?", ("string_longer_than_10_characters",)):
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/fdb/fbcore.py", line 3695, in execute
    self._ps._execute(parameters)
  File "/usr/lib/python3/dist-packages/fdb/fbcore.py", line 3364, in _execute
    self.__tuple2xsqlda(self._in_sqlda, parameters)
  File "/usr/lib/python3/dist-packages/fdb/fbcore.py", line 3145, in __tuple2xsqlda
    raise ValueError("Value of parameter (%i) is too long,"
ValueError: Value of parameter (0) is too long, expected 10, found 32

Though I have also seen this fail with "arithmetic exception, numeric overflow, or string truncation\n- string right truncation\n- expected length 10, actual 33".

Why does the query with parameter behave differently to the literal? And would you expect this to work?

Thanks

Hamish

[Pavel Cisar]

Hi,

does this also fail with firebird-driver instead fdb?

regards
Pavel Cisar
IBPhoenix

[Dimitry Sibiryakov]

'Hamish Moffatt' via firebird-support wrote 19.08.2025 9:03:
> Why does the query with parameter behave differently to the literal? And would
> you expect this to work?

Server must guess size of parameter and then is bound with this guess.
It is expected to fail in every Firebird version except, may be, version 6.0
where server accept data in parameters as is.

--
WBR, SD.

[Mark Rotteveel]

If you use a parameter, the maximum length of the parameter is decided
by the type (and its length) of the expression you're comparing against.
Given you're comparing against a VARCHAR(10), the maximum length that
the server accepts for the parameter is 10. If you're using a literal,
the length is decided by the literal itself.

This is a limitation in Firebird itself (though I believe Dimitry S. did
some work in Firebird 6 that allows a driver to send a longer value, but
then the driver needs to be modified to do so, and not enforce the
maximum as well, which the Python driver does, judging by the error
message).

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 19/08/2025 10:09, Pavel Cisar wrote:
> Hi,
>
> does this also fail with firebird-driver instead fdb?

I'd expect firebird-driver and fdb to behave the same, as ultimately
this is a server-side limitation.

Mark
--
Mark Rotteveel

[Hamish Moffatt]

On 19/8/25 18:09, Pavel Cisar wrote:
> Hi,
>
> does this also fail with firebird-driver instead fdb?

I didn't try, but it fails in IBExpert and Qt too. fdb was just the
easiest way I had to test a parameterized query.

Hamish

[Hamish Moffatt]

I don't understand why the expression can't just evaluate as false, as
it does when using a literal. This seems like a bug.

Just to add to my original report, this occurs when testing for equality
(=), not just using CONTAINING, and presumably other functions too.

I worked around it by casting the column to a larger VARCHAR first.


Hamish

[Dimitry Sibiryakov]

'Hamish Moffatt' via firebird-support wrote 20.08.2025 7:44:
> I don't understand why the expression can't just evaluate as false, as it does
> when using a literal. This seems like a bug.

In this case you really should test Firebird 6.

--
WBR, SD.

[Mark Rotteveel]

On 20/08/2025 07:44, 'Hamish Moffatt' via firebird-support wrote:
> On 19/8/25 18:28, 'Mark Rotteveel' via firebird-support wrote:
>> This is a limitation in Firebird itself (though I believe Dimitry S.
>> did some work in Firebird 6 that allows a driver to send a longer
>> value, but then the driver needs to be modified to do so, and not
>> enforce the maximum as well, which the Python driver does, judging by
>> the error message).
>
>
> I don't understand why the expression can't just evaluate as false, as
> it does when using a literal. This seems like a bug.

It can't "just" evaluate to false, because the server does not accept a
value longer than the field, and if you send a longer value it rejects
it with a string truncation error. This is a limitation for how the
server works with parameters: it reserves memory based on the data type
it's compared against and cannot accept longer values.

There is no way to make it "just" evaluate to false, other than by weird
hacks that could result in incorrect evaluation.

For example, a naive solution would for the client to set to null for
too long lengths, but then the expression evaluates to NULL, not FALSE,
and NOT NULL is also NULL (so a `column_name not containing ?` would
result in the wrong result). Not to mention wrong results for ordering
comparisons like < and >, or complex operations like SIMILAR TO.

Alternatively, the server could do some magic when accepting too long
values, and instead accept the truncated value and somehow record it's a
truncated value, and let each individual use of the variable determine
what to do (raise an error, yield true or false, etc), but that is
complex, and would still have edge cases where the processing could be
wrong (again, especially around ordering comparisons, SIMILAR TO).

But as I said, AFAIK, with changes in Firebird 6, it should now be
possible for the server to accept longer values (though I haven't tried
this myself yet).

> Just to add to my original report, this occurs when testing for equality
> (=), not just using CONTAINING, and presumably other functions too.

Yes, because this how it works for *all* parameters, independent of
their site of use. The truncation is either handled client-side (it
won't send a longer value), or server-side (the client sends a longer
value, and the server rejects it when reading the parameters *before*
actual query execution).

> I worked around it by casting the column to a larger VARCHAR first.

It would probably be better to cast the parameter to a larger value,
e.g. `cast(? as varchar(100))`.

Mark
--
Mark Rotteveel

[Hamish Moffatt]

I don't understand this - the parameter is already longer than the
column I'm comparing to.


Thanks,

Hamish

[Mark Rotteveel]

The parameter is maximum as long as the column you're comparing to. So,
if you want to be able to set a longer *value* for that parameter, you
need to cast. If you want that, you should cast the parameter, which -
AFAIK - is more efficient than casting the column like you said you did.

Mark
--
Mark Rotteveel

[Hamish Moffatt]

OK, thanks, that works.

My original query actually involves multiple columns compared against
the same parameter:

SELECT * FROM TABLE WHERE VARCHAR_50_COLUMN CONTAINING :TERM
OR VARCHAR_20_COLUMN CONTAINING :TERM ...

This fails when :TERM is longer than 20. Is that also as expected?


Hamish

[Mark Rotteveel]

On 20/08/2025 14:39, 'Hamish Moffatt' via firebird-support wrote:
> OK, thanks, that works.
>
> My original query actually involves multiple columns compared against
> the same parameter:
>
> SELECT * FROM TABLE WHERE VARCHAR_50_COLUMN CONTAINING :TERM
> OR VARCHAR_20_COLUMN CONTAINING :TERM ...
>
> This fails when :TERM is longer than 20. Is that also as expected?

Firebird doesn't have named parameters. That is simulated by your
driver. In reality, each occurrence of `:TERM` is rewritten by your
driver to `?`, and those are handled as separate parameters. You need to
cast each occurrence of `:TERM`, e.g. `cast(:TERM as varchar(100))`.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Hamish Moffatt' via firebird-support wrote 20.08.2025 14:39:
> My original query actually involves multiple columns compared against the same
> parameter:

Firebird doesn't support named parameters so these parameters are not the same.

--
WBR, SD.