BUG #18139: Causing FATAL error on database using function last() or first()
The following bug has been logged on the website:
Bug reference: 18139
Logged by: Paul Gazagne
Email address: pyrotarlu74@gmail.com
PostgreSQL version: 14.9
Operating system: (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
Description:
How to reproduce the error :
Setup table and dataset :
CREATE TABLE test (
id int4 not null,
value numeric(20, 6) NULL,
value_t text NULL
);
INSERT INTO test (id)
VALUES (1);
INSERT INTO test (id,value,value_t)
VALUES (2,2,'it is 2');
INSERT INTO test (id)
VALUES (3);
INSERT INTO test (id,value,value_t)
VALUES (4,4,'it is 4');
INSERT INTO test (id)
VALUES (5);
If I use the function last/first on this dataset, if the first rows parsed
by the function contain null values, it cause FATAL error on database.
Examples :
These queries work fine :
select
last(value_t,value) as thatsok
from test t
where id between 2 and 4;
select
last(value_t,value) as thatsok
from test t
where id between 2 and 5;
select
first(value_t,value) as thatsok
from test t
where id between 2 and 4;
select
first(value_t,value) as thatsok
from test t
where id between 2 and 5;
These ones cause FATAL error :
select
last(value_t,value) as fatal_error
from test t
where id between 1 and 5;
select
first(value_t,value) as fatal_error
from test t
where id between 1 and 5;
PG Bug reporting form <noreply@postgresql.org> writes:
If I use the function last/first on this dataset, if the first rows parsed
by the function contain null values, it cause FATAL error on database.
There is no last() nor first() function built into Postgres.
I surmise that you are using a buggy extension, in which case
you need to report this to the extension's author.
regards, tom lane
On Thu, Sep 28, 2023 at 11:33 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 18139
Logged by: Paul Gazagne
Email address: pyrotarlu74@gmail.com
PostgreSQL version: 14.9
Operating system: (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
Description:If I use the function last/first on this dataset,
This isn't a bug since these functions don't exist and all of your examples
error out on that account. You need to provide the definition of the
function you are using - and the bug likely belongs to whomever wrote the
function.
The seemingly equivalent COALESCE "function" does indeed produce the
expected type mismatch error for all those queries.
select
coalesce(value_t,value) as thatsok
from test t
where id between 2 and 4;
ERROR: COALESCE types text and numeric cannot be matched
LINE 2: coalesce(value_t,value) as thatsok
David J.